sql学习

创建表格

--  mysql
--  建立a, b, c三个表
create table a(
	name varchar(10)
)

create table b(
	name varchar(10),
	height decimal(5, 2)
)

create table c(
	id int,
	name varchar(20),
	age tinyint UNSIGNED
);

insert语句

insert into c values(12, '张飞', 30)   --这里的字段必须是全字段

insert into c(id, name, age) values(1, '李四', 12);  --这里的字段不一定是全字段, 但是要匹配

create database myba;

drop database myba;

-- 一条insert插入多条数据

-- 全字段
insert into c values('张三', 182.2, 45),
('李四', 184.6, 34),
('李小龙', 167, 23)

-- 指定字段
insert into c(height, age) values(123, 34),
(167, 67),
(178, 45)

select语句

-- select 简单查询
select * from c;

--  指定字段和顺序
select age, height from c;

update语句

--  update修改数据

--  修改全部记录的字段数据
update c set age = 50

--  根据条件修改字段数据
update c set name = '颜辉', age = 25 where height > 175;

--  修改name为颜辉的记录为李白
update c set name = '李白' where name = '颜辉';

--  age大于30的话, 涨200岁
update c set age = age + 200 where age > 30;

delete语句

--  delete删除记录
--  删除表中age为200的记录
delete from c where age = 250;

--  删除所有的记录
delete from c;

--  truncate删除表的所有记录, 固定语法, 不带条件
truncate table c

delete和truncate的区别:

1. 删除所有记录的话truncate的速度比较快
2. 自增长字段恢复从1开始, 要用truncate

drop删表

-- 删表drop
drop table a;

-- 判断表是否存在删除, 有删除, 没有就什么也不做
drop table if exists a;

字段约束关键字

--  常用字段的约束
-- 1. 主键(primary key): 值不能重复, auto_increment代表值自动增长
-- 2. 非空(not null): 此字段不允许填写空值
-- 3. 唯一(unique): 此字段的值不允许重复
-- 4. 默认值(default): 当不填写此值会用默认值, 如果填写则以填写为准
-- 用法, 在创建表的是时候, 定义字段类型后面, 空格添加约束条件

create table d(
id int unsigned primary key auto_increment, -- 主键自增长
name varchar(10), 
age tinyint default 10   -- 默认为10
)

insert into d(id, name) values(1, '李四'), (2, '张三'); -- age 默认为10
insert into d(name, age) values('李四', 23), ('张三', 34); --  id从1自增长

-- 插入的时候指定id的值
insert into d (id , name, age) values(23, '颜辉', 45);
-- 再插入记录的时候不指定id的值(需要指定字段)
insert into d( name , age) values('老王', 34)  -- 此时id是从上个指定id的数开始自增长, 也就是从23开始;

-- 如果不指定字段, 主键自增长字段的值可以用占位符, 0或者null
insert into d values (0, 'lihai', 23) ,(null, '康熙', 34);

-- 此时存在自增长字段id, 如果这时候delete删除表的话, 这时再插入记录的话, id是继续接着自增长的
delete from d;
truncate table d;



--  非空约束, 插入的时候不能为空, 否则会报错
create table e(
id int unsigned,
name varchar(10) not null,
age int
);

insert into e values(1, '张三', 20);
insert into e(id, age) values(3, 34); -- 报错


-- 唯一, unique
create table f(
id int, 
name varchar(10) unique,
age int
)

insert into f values(1, '张三', 20);
insert into f values(2, '张三', 56);  -- 报错

-- 默认值, default(当这个字段不填写时会默认, 填写的话以填写的为准, 这里就不写sql举例) 

数据准备

-- 第二阶段
 
-- 准备数据
/* 如果学生表students存在，就删除学生表students */
drop table if exists students;

/* 创建学生表students */
create table students (
  studentNo varchar(10) primary key, /*学号，主键，值不能重复*/
  name varchar(10), /*姓名*/
  sex varchar(1), /*性别*/
  hometown varchar(20), /*家乡*/
  age tinyint, /*年龄*/
  class varchar(10), /*班级*/
  card varchar(20) /*身份证号码*/
);

/* 向学生表students插入数据 */
insert into students values
('001', '王昭君', '女', '北京', '30', '1班', '110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班', '310102199104262354'),
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'),
('004', '白起', '男', '安徽', '35', '4班', '340202198505177654'),
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'),
('006', '孙尚香', '女', '河北', '25', '1班', '130502199506137654'),
('007', '百里玄策', '男', '山西', '39', '2班', '140102198107277654'),
('008', '小乔', '女', '河南', '25', '3班', null),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'),
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'),
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655');

-- 查询学生表
select name, hometown from students;

as别名

-- 给字段起别名  语法:  字段 as 别名/ 字段 空格 别名

select card as 身份证, name as 姓名, sex as 性别 from students;

select card  身份证, name  姓名, sex  性别 from students;

-- 给表起别名 语法:  表名 as 别名/ 表名 空格 别名

select * from students as stu;

select * from students stu;

查找去重

-- 消除重复记录 语法: select distinct 字段 from 表名 

select distinct sex from students  -- 此时只显示两条记录, 男,女

select distinct sex, class from students  -- 针对sex, class两个字段同时相同的重复数据过滤

select + where条件

-- where子句 因为经常用到, 这里简单写下

select card, name from students where sex = '男'

-- 比较运算符 等于 = ; 大于 > ; 大于等于 >= ; 不等于 != 或 <>

select * from students where age < 30;

-- 逻辑运算符 and(与)  条件同时具备
select * from students where age < 30 and sex = '女';

-- 逻辑运算符 或(or)   条件只具备一条就可
select * from students where age < 30 or sex = '男';

-- 逻辑运算符 非(not)  不具备这个条件
select * from students where not hometown != '天津';

-- 模糊查询 like  %表示任意多个任意字符; _表示一个任意字符
select * from students where name like '孙%';  -- 找一个姓孙的同学

select * from students where name like '孙_'; -- 找一个姓孙并且名是一个字的同学, 找孙尚香就是'孙__'

select * from students where name like '%乔'; -- 找一个名里带乔字的同学

select * from students where name like '%白%'; -- 找一个名字里带白的同学, 不管白在什么位置

select * from students where name like '__'; -- 找一个姓名为两个字的同学

-- 接下来是范围查询
-- in (.., ..) 在不连续的范围内

select * from students where hometown in ('北京', '天津', '上海'); 
-- 查询在北京, 天津, 上海的学生; 也可以用or来查询, 但是太繁琐

-- between .. and .. 在连续的范围内
select * from students where age between 25 and 30; -- 查询>=25以及<=30年龄的学生

select * from students where not age between 20 and 30; -- 查询20到30年龄以外的学生

空判断

-- 空判断 在数据库里null和''是不同的

-- select * from students where card = null (错误的查询方式 , = 后面必须是一个明确的值)

select * from students where card is null; -- 查询身份证号为null的学生 用is

select * from students where card is not null; -- 查询身份证不为null的学生

-- 关于空判断的查询语句比较特殊, 重点记下

update/delete + where条件

-- where也可以用于update和delete语句

update students set name = '宴会' where sex = '男'; -- 将男生的名字全部改为宴会

delete from students where name like '%会%' and age > 25; -- 名字带会的且年龄大于25的删除

排序

-- 排序 order by ... asc/desc   (asc升序, desc降序)

select * from students order by age asc; -- 按年龄从小到大排序查询学生, asc是order by的默认排序方式, 可省略

select * from students order by age desc; -- 按年龄从大到小排序查询学生

-- 查询所有男学生记录, 按age从大到小排序, 年龄相同时, 再按studentNo从小到大排
select * from students where sex  = '男' order by age desc, studentNo 
-- 排序优先级, 是按照字段顺序, studentNo的asc省略了, where在order by之前

聚合函数

-- 聚合函数(聚合函数不能用在where语句之中)

-- count总记录数 count() 括号里面填*或者字段名, 结果是一样的, 因为count查询的数量, 和筛选的条件是一致的
select count(*) from students;  -- 查看学生的总数

select count(name) from students; -- 跟上一条是一样的

select count(distinct class) from students -- 查询这些学生一共在几个班

select count(*) from students where sex = '女';  -- 查询女学生一共有多少个

数学函数

-- max(字段名)   查询某字段的最大值
select max(age) from students;  -- 查询学生中最大的年龄;

select max(age) from students where sex = '男'; -- 查询男生中最大的年龄

-- min(字段名) 查询某字段的最大值  (跟max用法一样, 意义相反, 这里就不写sql实例了)

-- sum(字段名) 查询某字段的总和
select sum(age) from students where class = '1班';  -- 查询1班学生的年龄总和

-- avg(字段名) 查询某字段的平均值  avg会忽略值为null的字段, 不参与计算

select avg(age) from students where sex = '女'; -- 查询女生的平均年龄

-- 接下来是数据分组