Mysql系列003：sql必知必会（面试必备）

⭐sql语句必会

---

增删改查(CRUD)

+----+-----------+------+--------+---------+--------+-----------+
| id | name      | age  | height | gender  | cls_id | is_delete |
+----+-----------+------+--------+---------+--------+-----------+
|  1 | 小明      |   18 |    180 | female  |      1 |           |
|  2 | 小月月    |   18 |    180 | female  |      2 |           |
|  3 | 王语嫣    |   29 |    185 | male    |      1 |           |
|  4 | 刘德华    |   59 |    175 | male    |      2 |           |
|  5 | 黄圣依    |   38 |    160 | female  |      1 |           |
|  6 | 凤姐      |   38 |    160 | female  |      1 |           |
|  7 | 王祖贤    |   28 |    150 | secret  |      2 |           |
|  8 | 周杰伦    |   36 |    175 | male    |      1 |           |
|  9 | 程金宝    |   27 |    181 | male    |      2 |           |
| 10 | 刘亦菲    |   25 |    166 | female  |      2 |           |
| 11 | 金星      |   33 |    162 | neutral |      3 |           |
| 12 | 静静      |   12 |    160 | female  |      4 |           |
| 13 | 郭德纲    |   12 |    170 | male    |      4 |           |
| 14 | 潘玮柏    |   34 |    176 | female  |      5 |           |
+----+-----------+------+--------+---------+--------+-----------+

⭐增⭐

📚全列插入

– insert [into] 表名 values (…);
– 向classes表中插入一个班级

insert into classes 
values (0, '极客5班');

– 向students表中插入一个学生信息
– 主键字段 可以用0 null default 占位

insert into students 
values (0, '嘻嘻', 18, 188, 'male', 2, null);

– 枚举中的下标从1开始

insert into students 
values (default, 'cxx', 22, 176, '1', 5, 0);

📚部分插入

– insert [into] 表名(列1, …) values (…)

insert into students(name, age) 
values ('aaa', 19)

– 多行插入

insert into students(name, height) 
values ('aaa', 19),('aaaaa', 18);

⭐改⭐

– update 表名 set 列1=值1, 列2=值2, 列3=值3 … where 条件判断;
– 不加where全部修改，无实际意义
– 所有学生的性别修改成男

update students 
set gender = 'male';

– 所有学生的性别修改成秘密

update students 
set gender = '4'

– 将id为3的学生性别修改成女

update students 
set gender = 'female' 
where id = 3;

– 将id为4的学生名字修改成阿斯顿

update students 
set name='阿斯顿' 
where id=4;

– 将id为5的学生，名字修改成舒克，性别修改成男

update students 
set name='舒克', gender='male' 
where id=5;

⭐删⭐

📚物理删除

– delete from 表名 where 条件;
– 删除名字是舒克的学生

delete from students 
where name='舒克';

📚逻辑删除

– 用一个字段来表示某条字段已经不能使用
– 修改is_delete字段 bit类型

update students 
set is_delete=1 
where name='舒克';

⭐查⭐

📚基本查询

– 查询所有学生信息

select * from students;

– 查询指定列

– 可以用as设置别名 as可加可不加
– select 字段1 as 别名1, 字段2 as 别名2 from 数据表名 where …;

select name '名字', age '年龄' 
from students 
where id=1;

– select 表名.字段, 表名.字段 … from 表名;

select s.name, s.age 
from students s 
where id=4;

– 消除重复行：distinct

select distinct gender '性别' 
from students;

📚条件查询

– 比较运算符
– select … from 表名 where …
– =
– 查询等于18岁的信息

select * from students 
where age=18;

– >
– 查询大于18岁的信息

select * from students 
where age>18;

– <
– 查询小于18岁的信息

select * from students
where age<18;

– !=
– 查询不等于18岁的信息

select * from students
where age!=18;

– 逻辑运算符
– and
– 18岁到28之间的所有学生信息

select * from students
where age>18 and age<28;

– 18岁以上的女生信息

select * from students
where age>18 and gender='female';

– or
– 查看18岁以上或者身高大于170的男生

select * from students
where gender='male' and (age>18 or height>170);

– not
– 不在18岁以上的女生的信息

select * from students
where not age>18 and gender='female';

– 模糊查询（效率低）
– like
– %: 替换1个或多个
– _: 替换一个
– 查询姓名中以"M"开始的学生信息

select * from students
where name like 'M%';

– 查询姓名中有"M"字的学生信息

select * from students
where name like '%M%';

– 查询名字有4个字的的学生信息

select * from students
where name like '____';

– 查询名字有5个字的学生信息

select * from students
where name like '_____';

– 查询名字至少有5个的学生信息

select * from students
where name like '_____%';

– rlike: 正则查询
– 查询以"Z"开始的学生信息

select * from students
where name rlike '^Z';

– 查询以"Z"开始, 以"e"结尾的信息

select * from students
where name rlike '^Z.*?e$';

📚范围查询

– in(1, 3, 8)表示在一个非连续的范围内
– 查询 年龄为18, 27, 34的同学信息

select * from students
where age in(18, 27, 34);

– not in 不连续的范围之内
– 查询 年龄不在18, 27, 34的同学信息

select * from students
where age not in(18, 27, 34);

– between … and … 表示在一个连续的范围内
– 查询 年龄是18~34之间的同学信息

select * from students
where age between 18 and 34;

– not between … and … 表示不在一个连续的范围内
– 查询 年龄不是18~34之间的同学信息

select * from students
where age not between 18 and 34;

📚空判断

– 判空 is null
– 判断身高为空的学生信息

select * from students
where age is null;

– 判断身高非空的学生信息

select * from students
where height is not null;

📚排序

– order by 单个字段（默认：从小到大排序, 省略了asc）
– desc 从大到小排序

– 查询年龄在18~34之间的男性，并按照年龄的从小到大排序

select * from students
where gender='male' and (age between 18 and 34)
order by age;

– 查询年龄在18~34之间的女性，身高从大到小排序

select * from students
where gender='female' and (age between 18 and 34)
order by height desc; 

– order by 多个字段
– 查询年龄在18到34岁之间的女性，身高从大到小排序，如果身高相同情况下按照年龄大小排序

select * from students
where (age between 18 and 34) and gender='female'
 order by height desc, age desc;

– 查询年龄在18到34岁之间的女性，身高从大到小排序，如果身高相同情况下按照年龄大小排序，如果年龄相同情况下按照id从小到大排序

select * from students
where (age between 18 and 34) and gender='female'
order by height desc, age desc, id;

– 按照年龄从小到大排序，身高从大到小排序

select * from students
order by age, height desc;

📚聚合

– count 总数
– 查询有多少男生，多少女生

select count(*) '男生' from students
where gender='male';
select count(*) '女生' from students
where gender='female';

– 查询学生总数

select count(*) '学生总数' from students; 

– max 最大值
– 查询最大的年龄

select max(age) '最大年龄' from students;

– 查询男生年龄最大的

select max(age) '最大年龄'
from students
where gender='male'; 		

– min 最小值
– 查询最小的年龄

select min(age) '最小年龄'
from students;

– sum 求和
– 计算所有男生的年龄和

select sum(age) '男生年龄和'
from students
where gender='male';

– avg 平均值
– 计算男生平均年龄

select avg(age) '男生平均年龄'
from students
where gender='male';

– round(num, 2) 保留2位小数
– 计算男生平均年龄，保留2位小数

select round(avg(age), 2) '男生平均年龄'
from students
where gender='male';

📚分组

– group by
– 按照性别分组

select gender '性别'
from students
group by gender;

– 按照年龄进行分组

select age '年龄'
from students
group by age;

– 计算每种性别中的人数

select gender, count(*) '人数'
from students
group by gender;

– 查询每种性别中最大的年龄

select gender, max(age) '最大年龄'
from students
group by gender; 

– 查询每种性别的平均年龄，并且保存2位小数

select gender, round(avg(age, 2)) '平均年龄'
from students
group by gender;

– 计算男性的人数

select gender, count(*) '男性人数'
from students
where gender='male'
group by gender;

– group_concat(…)
– 查询同种性别中的学生姓名

select gender '性别', group_concat(name) '姓名'
from students
group by gender;

– having
– 查询平均年龄超过30岁的性别，以及姓名 having avg(age)>30

select gender,age
from students
group by gender
having avg(age)>30;

– 查询性别人数超过2人得性别，并显示其名字

select gender,group_concat(name)
from students
group by gender
having count(*)>2;

📚分页

– limit start, count 限制查询出来数据的个数

– 查询前5个数据

select * from students
limit 5;

– 查询id=(6~10)的排序

select * from students
limit 5, 5;

📚连接查询

– inner join … on
– 笛卡尔查询

select * from students
inner join classes; 

– 查询有对应班级的学生信息

select * from students s
inner join classes c
on s.cls_id=c.id; 

– 仅显示学生姓名，班级

select s.name '姓名', c.name '班级'
from students s
inner join classes c
on s.cls_id=c.id; 

– 显示学生的全部信息和班级

select s.*, c.name
from students
inner join classes c
on s.cls_id=c.id; 

– 在以上的查询中，将班级姓名显示在第一列

select c.name '班级', s.name '姓名'
from students s
inner join classes c 
on s.cls_id=c.id; 

– 查询 有能够对应班级的学生以及班级信息，按照班级进行排序

select c.name '班级', s.name '姓名' 
from students s 
inner join classes c 
on s.cls_id=c.id order by c.name; 

– 当同一个班级时，按照学生的ID从小到大进行排序

select c.name '班级', s.* 
from students s 
inner join classes c 
on s.cls_id=c.id 
order by c.name, id; 

– left join … on
– 查询每位学生对应的信息

select * from students s 
left join classes c 
on s.cls_id=c.id;

– 查询没有对应班级信息的学生 (where 和 having)

select * from students s 
left join classes c on s.cls_id=c.id 
where c.name is null;

select * from students s 
left join classes c on s.cls_id=c.id 
having c.name is null;

– right join … on (一般不用)
– 将数据表名字互换位置，用left join实现

select * from students 
right join classes 
on students.cls_id=classes.id;

📚子查询

– 标量子查询
– 查询出高于平均身高的信息

select * from students 
where height > (select avg(age) from students);

– 查询最高的男生信息

select * from students 
where gender='male' and height=(select max(height) from students);	

---

🌺感谢收看🌺