MYSQL表的增删改查进阶
- 4. 查询
- 4.1 聚合查询
- 4.1.1 聚合函数
- 4.1.2 group by字句
- 4.1.3 having
- 4.2 联合查询
- 4.2.1 内连接
- 4.2.2 外连接
- 4.2.3 自连接
- 4.2.4 子查询
- 4.2.5 合并查询
4. 查询
4.1 聚合查询
4.1.1 聚合函数
常见的统计总数、计算平均值等操作，可以使用聚合函数来实现，常见的聚合函数有：
函数 | 说明 |
count | 返回查询到的数据的数量 |
sum | 返回查询到的数据的总和，不是数字没有意义 |
avg | 返回查询到的数据的平均值，不是数字没有意义 |
max | 返回查询到的数据的最大值，不是数字没有意义 |
min | 返回查询到的数据的最小值，不是数字没有意义 |
案例：
- count
-- 统计班级一共有多少学生
select count(*) from student;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
select count(0) from student;
+----------+
| count(0) |
+----------+
| 4 |
+----------+-- 统计班级收集的qq_mail 有多少个，qq_mail为null的数据不会计入结果
select * from student;
+----+------+--------+---------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+--------+---------+------------+
| 1 | 1 | 张三 | NULL | 1 |
| 3 | 2 | 李四 | NULL | 1 |
| 4 | 3 | 王五 | NULL | 1 |
| 5 | 4 | 赵六 | NULL | 2 |
+----+------+--------+---------+------------+
select count(qq_mail) from student;
+----------------+
| count(qq_mail) |
+----------------+
| 0 |
+----------------+- sum
-- 统计数学成绩总分
select sum(math) from exam_result;
-- 不及格<60 的总分，没有结果，返回null
select sum(math) from exam_result where math<60;- avg
-- 统计平均总分
select avg(chinese+math+english) 平均总分 from exam_result;- max
-- 返回英语最高分
select max(english) from exam_result;- min
-- 返回大于70分的数学最低分
select min(math) from exam_result where math>70;4.1.2 group by字句
select中使用group by子句可以指定列进行分组查询。
需要满足： 使用group by 进行分组查询时，select 指定的字段必须是"分组依据字段"，其他字段若想出现在select中则必须包含在聚合函数中。
select column1,sum(column2),... from table group by column1,column3;案例：
- 准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric (11,2)
);
insert into emp (name,role,salary) values
('赵四','服务员',1000.20),
('马六','游戏陪练',2000.99),
('孙悟空','游戏角色',999.11),
('猪悟能','游戏角色',333.5),
('沙和尚','游戏角色',700.33),
('王五','董事长',12000.66);- 查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
+--------------+-------------+-------------+--------------+
| role | max(salary) | min(salary) | avg(salary) |
+--------------+-------------+-------------+--------------+
| 服务员 | 1000.20 | 1000.20 | 1000.200000 |
| 游戏角色 | 999.11 | 333.50 | 677.646667 |
| 游戏陪练 | 2000.99 | 2000.99 | 2000.990000 |
| 董事长 | 12000.66 | 12000.66 | 12000.660000 |
+--------------+-------------+-------------+--------------+4.1.3 having
group by 子句进行分组后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要使用having
- 显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;
+--------------+-------------+-------------+-------------+
| role | max(salary) | min(salary) | avg(salary) |
+--------------+-------------+-------------+-------------+
| 服务员 | 1000.20 | 1000.20 | 1000.200000 |
| 游戏角色 | 999.11 | 333.50 | 677.646667 |
+--------------+-------------+-------------+-------------+4.2 联合查询
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
注意：关联查询可以对关联表使用别名，语法：表名 [as] 别名。如果表使用了别名，查询字段，也需要是表的别名。
表的别名+字段别名结合使用（几乎都要使用）
初始化测试数据：
create database java43 character set utf8mb4;
use java43;
drop table if exists classes;
create table classes (
id int primary key auto_increment,
name varchar(20),
`desc` varchar(100)
);
insert into classes(name,`desc`) values
('计算机系2019级1班','学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械原理');
drop table if exists student;
create table student (
id int primary key auto_increment,
sn int unique,
name varchar(20) default 'unknown',
qq_mail varchar(20),
classes_id int,
foreign key (classes_id) references classes(id)
);
-- id为1和2的班级，分别有5个和3个学生
insert into student(sn,name,qq_mail,classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','只想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83232','tellme',null,2),
('09527','学中文','chinese@qq.com',2);
-- 创建课程表
drop table if exists course;
create table course (
id int primary key auto_increment,
name varchar(20)
);
insert into course(name) values
('java'),
('中国传统文化'),
('计算机原理'),
('语文'),
('高等数学'),
('英语');
drop table if exists score;
create table score (
id int primary key auto_increment,
score decimal(3,1),
student_id int,
course_id int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
insert into score(score,student_id,course_id) values
-- 黑旋风李逵
(70.5,1,1),( 98.5,1 ,3 ),(33 ,1 ,5 ),(98 ,1 ,6 ),
-- 菩提老祖
( 60,2 ,1 ),(59.5 ,2 ,5 ),
-- 白素贞
(33 ,3 ,1 ),(68 ,3 ,3 ),( 99,3 ,5 ),
-- 许仙
(67,4 ,1 ),( 23,4 ,3 ),( 56, 4,5 ),(72 ,4 ,6 ),
-- 只想毕业
( 81, 5,1 ),(37 ,5 ,5 ),
-- 好好说话
( 56,6 ,2 ),(43 ,6 ,4 ),(79 ,6 ,6 ),
-- tellme
(80 ,7 ,2 ),( 92, 7,6 );4.2.1 内连接
语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;- 班级表和学生表笛卡尔积查询：不在某个班级的学生，也关联显示了（产生了错误多余的数据）
select c.id c_id,c.name c_name,s.id s_id,s.name s_name,s.classes_id from classes c,student s;- 需要使用关联条件，来连接两张表
select c.id c_id,c.name c_name,s.id s_id,s.name s_name,s.classes_id from classes c,student s where c.id=s.classes_id;
select c.id c_id,c.name c_name, s.id s_id,s.name s_name,s.classes_id from classes c join student s on c.id=s.classes_id;案例：
（1）查询许仙同学的成绩
select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='许仙';
-- 或者
select sco.score from student stu,score sco where stu.id=sco.student_id and stu.name='许仙';（2）查询所有同学的总成绩，及同学的个人信息：
-- 成绩表对学生表是多对一的关系，查询总成绩是根据成绩表的同学id来进行分组的
select
stu.sn,
stu.name,
stu.qq_mail,
sum(sco.score)
from
student stu
join score sco on stu.id=sco.student_id
group by
sco.student_id;
+-------+-----------------+-----------------+----------------+
| sn | name | qq_mail | sum(sco.score) |
+-------+-----------------+-----------------+----------------+
| 9982 | 黑旋风李逵 | xuanfeng@qq.com | 300.0 |
| 835 | 菩提老祖 | NULL | 119.5 |
| 391 | 白素贞 | NULL | 200.0 |
| 31 | 许仙 | xuxian@qq.com | 218.0 |
| 54 | 只想毕业 | NULL | 118.0 |
| 51234 | 好好说话 | say@qq.com | 178.0 |
| 83232 | tellme | NULL | 172.0 |
+-------+-----------------+-----------------+----------------+
7 rows in set (0.00 sec)（3）查询所有同学的成绩，及个人信息：
-- 查询出来的都是有成绩的同学，“学中文”同学却没有显示
select * from student stu join score sco on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
select
stu.id,
stu.sn,
stu.name,
stu.qq_mail,
sco.score,
sco.course_id,
cou.name
from
student stu
join score sco on stu.id=sco.student_id
join course cou on sco.course_id=cou.id
order by
stu.id
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
| id | sn | name | qq_mail | score | course_id | name |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 70.5 | 1 | java |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 98.5 | 3 | 计算机原理 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 33.0 | 5 | 高等数学 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 98.0 | 6 | 英语 |
| 2 | 835 | 菩提老祖 | NULL | 60.0 | 1 | java |
| 2 | 835 | 菩提老祖 | NULL | 59.5 | 5 | 高等数学 |
| 3 | 391 | 白素贞 | NULL | 33.0 | 1 | java |
| 3 | 391 | 白素贞 | NULL | 68.0 | 3 | 计算机原理 |
| 3 | 391 | 白素贞 | NULL | 99.0 | 5 | 高等数学 |
| 4 | 31 | 许仙 | xuxian@qq.com | 67.0 | 1 | java |
| 4 | 31 | 许仙 | xuxian@qq.com | 23.0 | 3 | 计算机原理 |
| 4 | 31 | 许仙 | xuxian@qq.com | 56.0 | 5 | 高等数学 |
| 4 | 31 | 许仙 | xuxian@qq.com | 72.0 | 6 | 英语 |
| 5 | 54 | 只想毕业 | NULL | 81.0 | 1 | java |
| 5 | 54 | 只想毕业 | NULL | 37.0 | 5 | 高等数学 |
| 6 | 51234 | 好好说话 | say@qq.com | 56.0 | 2 | 中国传统文化 |
| 6 | 51234 | 好好说话 | say@qq.com | 43.0 | 4 | 语文 |
| 6 | 51234 | 好好说话 | say@qq.com | 79.0 | 6 | 英语 |
| 7 | 83232 | tellme | NULL | 80.0 | 2 | 中国传统文化 |
| 7 | 83232 | tellme | NULL | 92.0 | 6 | 英语 |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+4.2.2 外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示，我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
语法：
-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段名 from 表名1 right join 表名2 on 连接条件;案例：
查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
-- "学中文"同学没有考试成绩，也显示出来了
select * from student stu left join score sco on stu.id=sco.student_id;
-- 对应的右外连接为：
select * from score sco right join student stu on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
select
stu.id,
stu.sn,
stu.name,
stu.qq_mail,
sco.score,
sco.course_id,
cou.name
from
student stu
left join score sco on stu.id=sco.student_id
left join course cou on sco.course_id=cou.id
order by
stu.id;4.2.3 自连接
自连接是指在同一张表连接自身查询。
案例：
显示所有“计算机原理”成绩比Java成绩高的成绩信息
-- 先查询“计算机原理”和“Java”课程的id
select id ,name from course where name='java' or name='计算机原理';
-- 再查询成绩表中，"计算机原理"成绩比“Java”成绩高的信息
select
s1.*
from
score s1,
score s2
where
s1.student_id=s2.student_id
and s1.score<s2.score
and s1.course_id=1
and s2.course_id=3;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 21 | 70.5 | 1 | 1 |
| 27 | 33.0 | 3 | 1 |
+----+-------+------------+-----------+
-- 也可以使用join on语句来进行自连接查询
select
s1.*
from
score s1
join score s2 on s1.student_id=s2.student_id
and s1.score<s2.score
and s1.course_id=1
and s2.course_id=3;以上查询只显示了成绩信息，并且是分步执行的。要显示学生及成绩信息，并在一条语句显示：
select
stu.*,
s1.score Java,
s2.score 计算机原理
from
score s1
join score s2 on s1.student_id=s2.student_id
join student stu on s1.student_id=stu.id
join course c1 on s1.course_id=c1.id
join course c2 on s2.course_id=c2.id
and s1.score<s2.score
and c1.name='java'
and c2.name='计算机原理';
+----+------+-----------------+-----------------+------------+------+-----------------+
| id | sn | name | qq_mail | classes_id | Java | 计算机原理 |
+----+------+-----------------+-----------------+------------+------+-----------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 70.5 | 98.5 |
| 3 | 391 | 白素贞 | NULL | 1 | 33.0 | 68.0 |
+----+------+-----------------+-----------------+------------+------+-----------------+4.2.4 子查询
子查询是指嵌入在其他SQL语句中的select语句，也叫做嵌套查询
- 单行子查询： 返回一行记录的子查询
查询与“只想毕业”同学一个班级的学生：
select * from student where classes_id =(select classes_id from student where name='只想毕业');
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素贞 | NULL | 1 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 |
| 5 | 54 | 只想毕业 | NULL | 1 |
+----+------+-----------------+-----------------+------------+- 多行子查询： 返回多行记录的子查询
案例：查询“语文”或“英语”课程的成绩信息
1.[NOT] IN关键字：
-- 使用in
select * from score where course_id in(select id from course where name='语文' or name='英语');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 37 | 43.0 | 6 | 4 |
| 24 | 98.0 | 1 | 6 |
| 33 | 72.0 | 4 | 6 |
| 38 | 79.0 | 6 | 6 |
| 40 | 92.0 | 7 | 6 |
+----+-------+------------+-----------+
-- 使用 not in
select * from score where course_id not in(select id from course where name!='语文' or name!='英语');可以多列包含：
-- 插入重复的分数：score,student_id,course_id列重复
insert into score (score,student_id,course_id) values
-- 黑旋风李逵
(70.5,1,1),(98.5,1,3),
-- 菩提老祖
(60,2,1);
-- 查询重复的分数
select
*
from
score
where
(score,student_id,course_id) in (select score,student_id,course_id
from score
group by score,student_id,course_id
having count(0)>1);- [not] exists关键字：
-- 使用exists
select * from score sco where exists (select sco.id from course cou where (name='语文' or name='英语') and cou.id=sco.course_id);
-- 使用not exists
select * from score sco where not exists (select sco.id from course cou where (name !='语文' or name !='英语') and cou.id=sco.course_id);- 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
查询所有比“中文系2019级3班”平均分高的成绩信息：
-- 获取“中文系2019级3班”的平均分，将其看作临时表
select
avg(sco.score)score
from
score sco
join student stu on sco.student_id =stu.id
join classes cls on stu.classes_id =cls.id
where
cls.name = '中文系2019级3班';查询成绩表中，比以上临时表平均分高的成绩：
select
*
from
score sco,
(
select
avg(sco.score) score
from
score sco
join student stu on sco.student_id = stu.id
join classes cls on stu.classes_id = cls.id
where
cls.name = '中文系2019级3班'
) tmp
where
sco.score>tmp.score;4.2.5 合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union 、union all.使用union和union all时，前后查询的结果集中，字段需要一致。
- union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：查询id小于3，或者名字为“英语”的课程：
select * from course where id<3
union
select * from course where name='英语';
-- 或者使用or来实现
select * from course where id<3 or name='英语';- union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id<3,或者名字为“java”的课程
select * from course where id<3
union all
select * from course where name='英语';
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
