【数据库】聚合查询和联合查询
本文共 17246 字，大约阅读时间需要 57 分钟。
查询
1.聚合查询
1.1 聚合函数
| 函数 | 说明 |
|---|---|
| COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的 总和，不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的 平均值，不是数字没有意义 |
| MAX([DISTINCT] expr) | 返回查询到的数据的 最大值，不是数字没有意义 |
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值，不是数字没有意义 |
案例：
- 准备表
- COUNT
-- 统计班级人数 select count(*) 人数 from exam_result; -- 执行过程：先 select * from exam_reslt 再对结果进行count统计 select count(name) from exam_result; -- 执行过程：先 select name from exam_reslt 再对结果进行count统计 select count(10) from exam_result; -- 统计数学不及格人数 select count(*) 数学不及格人数 from exam_result where math<60;-- 统计有英语成绩的学生人数 insert into exam_result(id,name) values (11,"翠翠"); select count(english) from exam_result; -- 结果依旧是10 英语成绩为 NULL 的不计数注意: count() 括号中可以写*/列名/表达式/常量，默认结果不去重，去重加distinct count() 时，为 NULL 的数据不会计入结果
- SUM
-- 统计数学成绩总分 select sum(math) from exam_result; -- 779.2
- AVG
-- 统计数学平均分 select avg(math) from exam_result; -- 77.92000
- MAX
-- 统计数学最高分 select avg(math) from exam_result; -- 98.5
- MIN
-- 统计数学最低分 select avg(math) from exam_result; -- 43.5
1.2 GROUP BY子句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。
需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。
GROUP BY 可以帮助我们进行更复杂的统计，一般在线下环境中使用。
-- 将指定列相同的记录合并在一起 select column1, sum(column2), .. from table group by column1,column3;
案例：
-- 职工表 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; 
线上环境：直接给用户访问的服务器。对于效率、稳定性等要求较高。
线下环境：公司内部使用的数据库。对于执行效率要求不高。eg：数据报表等
1.3 HAVING
GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING。
-- 显示平均工资低于1500的角色和它的平均工资 select role,avg(salary) from emp group by role having avg(salary)<1500;
2.联合查询
实际开发中往往数据来自不同的表，所以需要多表联合查询。
多表查询是对多张表的数据取笛卡尔积：
注意：关联查询可以对关联表使用别名。 测试数据：
drop table if exists classes; drop table if exists student; drop table if exists course; drop table if exists score; create table classes ( id int primary key auto_increment, -- 自增主键 插入数据时，数据库会自动根据上一条的结果来递增增加id值 name varchar(20), `desc` varchar(100)); create table student ( id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) , classes_id int); create table course( id int primary key auto_increment, name varchar(20)); create table score( score decimal(3, 1), student_id int, course_id int); insert into classes(name, `desc`) values ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), ('中文系2019级3班','学习了中国传统文学'), ('自动化2019级5班','学习了机械自动化'); 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), ('83223','tellme',null,2), ('09527','老外学中文','foreigner@qq.com',2); insert into course(name) values ('Java'), ('中国传统文化'), ('计算机原理'), ('语文'), ('高阶数学'), ('英文'); 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), -- 好好说话 (80, 7, 2),(92, 7, 6); -- tellmestudent 和 classes 是一对多关系：一个班级可以有多个学生，一个学生只能在一个班级student 和 score 是多对多关系：m个学生在n门课中进行了考试score表：记录某个学生的某一门课的分数 班级表 classes：
学生表 student： 
课程表 course： 
成绩表 score： 
使用 join on 时，当连接方式是“内连接”的场景，表的顺序无影响，其他连接方式有影响。
2.1 内连接
语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
（1） 查询“许仙”同学的成绩
-- 要找成绩，先查看 score 表中的 score 列，发现 score 表中无学生姓名-- 但是有学生 id ，在 student 中 id 可以对应 name select score.score from student join score on student.id = score.student_id and student.name='许仙'; select score.score from student,score where student.id = score.student_id and student.name='许仙';-- 解释分析sql语句 select student.id,score.student_id,score.score,student.name from student,score; -- 1.两张表做笛卡尔积结果如下：+----+------------+-------+-----------------+| id | student_id | score | name |+----+------------+-------+-----------------+| 1 | 1 | 70.5 | 黑旋风李逵 || 2 | 1 | 70.5 | 菩提老祖 || 3 | 1 | 70.5 | 白素贞 || 4 | 1 | 70.5 | 许仙 || 5 | 1 | 70.5 | 不想毕业 || 6 | 1 | 70.5 | 好好说话 || 7 | 1 | 70.5 | tellme || 8 | 1 | 70.5 | 老外学中文 || 1 | 1 | 98.5 | 黑旋风李逵 || 2 | 1 | 98.5 | 菩提老祖 || 3 | 1 | 98.5 | 白素贞 || 4 | 1 | 98.5 | 许仙 || 5 | 1 | 98.5 | 不想毕业 || 6 | 1 | 98.5 | 好好说话 || 7 | 1 | 98.5 | tellme || 8 | 1 | 98.5 | 老外学中文 || 1 | 1 | 33.0 | 黑旋风李逵 || 2 | 1 | 33.0 | 菩提老祖 || 3 | 1 | 33.0 | 白素贞 || 4 | 1 | 33.0 | 许仙 || 5 | 1 | 33.0 | 不想毕业 || 6 | 1 | 33.0 | 好好说话 || 7 | 1 | 33.0 | tellme || 8 | 1 | 33.0 | 老外学中文 || 1 | 1 | 98.0 | 黑旋风李逵 || 2 | 1 | 98.0 | 菩提老祖 || 3 | 1 | 98.0 | 白素贞 || 4 | 1 | 98.0 | 许仙 || 5 | 1 | 98.0 | 不想毕业 || 6 | 1 | 98.0 | 好好说话 || 7 | 1 | 98.0 | tellme || 8 | 1 | 98.0 | 老外学中文 || 1 | 2 | 60.0 | 黑旋风李逵 || 2 | 2 | 60.0 | 菩提老祖 || 3 | 2 | 60.0 | 白素贞 || 4 | 2 | 60.0 | 许仙 || 5 | 2 | 60.0 | 不想毕业 || 6 | 2 | 60.0 | 好好说话 || 7 | 2 | 60.0 | tellme || 8 | 2 | 60.0 | 老外学中文 || 1 | 2 | 59.5 | 黑旋风李逵 || 2 | 2 | 59.5 | 菩提老祖 || 3 | 2 | 59.5 | 白素贞 || 4 | 2 | 59.5 | 许仙 || 5 | 2 | 59.5 | 不想毕业 || 6 | 2 | 59.5 | 好好说话 || 7 | 2 | 59.5 | tellme || 8 | 2 | 59.5 | 老外学中文 || 1 | 3 | 33.0 | 黑旋风李逵 || 2 | 3 | 33.0 | 菩提老祖 || 3 | 3 | 33.0 | 白素贞 || 4 | 3 | 33.0 | 许仙 || 5 | 3 | 33.0 | 不想毕业 || 6 | 3 | 33.0 | 好好说话 || 7 | 3 | 33.0 | tellme || 8 | 3 | 33.0 | 老外学中文 || 1 | 3 | 68.0 | 黑旋风李逵 || 2 | 3 | 68.0 | 菩提老祖 || 3 | 3 | 68.0 | 白素贞 || 4 | 3 | 68.0 | 许仙 || 5 | 3 | 68.0 | 不想毕业 || 6 | 3 | 68.0 | 好好说话 || 7 | 3 | 68.0 | tellme || 8 | 3 | 68.0 | 老外学中文 || 1 | 3 | 99.0 | 黑旋风李逵 || 2 | 3 | 99.0 | 菩提老祖 || 3 | 3 | 99.0 | 白素贞 || 4 | 3 | 99.0 | 许仙 || 5 | 3 | 99.0 | 不想毕业 || 6 | 3 | 99.0 | 好好说话 || 7 | 3 | 99.0 | tellme || 8 | 3 | 99.0 | 老外学中文 || 1 | 4 | 67.0 | 黑旋风李逵 || 2 | 4 | 67.0 | 菩提老祖 || 3 | 4 | 67.0 | 白素贞 || 4 | 4 | 67.0 | 许仙 || 5 | 4 | 67.0 | 不想毕业 || 6 | 4 | 67.0 | 好好说话 || 7 | 4 | 67.0 | tellme || 8 | 4 | 67.0 | 老外学中文 || 1 | 4 | 23.0 | 黑旋风李逵 || 2 | 4 | 23.0 | 菩提老祖 || 3 | 4 | 23.0 | 白素贞 || 4 | 4 | 23.0 | 许仙 || 5 | 4 | 23.0 | 不想毕业 || 6 | 4 | 23.0 | 好好说话 || 7 | 4 | 23.0 | tellme || 8 | 4 | 23.0 | 老外学中文 || 1 | 4 | 56.0 | 黑旋风李逵 || 2 | 4 | 56.0 | 菩提老祖 || 3 | 4 | 56.0 | 白素贞 || 4 | 4 | 56.0 | 许仙 || 5 | 4 | 56.0 | 不想毕业 || 6 | 4 | 56.0 | 好好说话 || 7 | 4 | 56.0 | tellme || 8 | 4 | 56.0 | 老外学中文 || 1 | 4 | 72.0 | 黑旋风李逵 || 2 | 4 | 72.0 | 菩提老祖 || 3 | 4 | 72.0 | 白素贞 || 4 | 4 | 72.0 | 许仙 || 5 | 4 | 72.0 | 不想毕业 || 6 | 4 | 72.0 | 好好说话 || 7 | 4 | 72.0 | tellme || 8 | 4 | 72.0 | 老外学中文 || 1 | 5 | 81.0 | 黑旋风李逵 || 2 | 5 | 81.0 | 菩提老祖 || 3 | 5 | 81.0 | 白素贞 || 4 | 5 | 81.0 | 许仙 || 5 | 5 | 81.0 | 不想毕业 || 6 | 5 | 81.0 | 好好说话 || 7 | 5 | 81.0 | tellme || 8 | 5 | 81.0 | 老外学中文 || 1 | 5 | 37.0 | 黑旋风李逵 || 2 | 5 | 37.0 | 菩提老祖 || 3 | 5 | 37.0 | 白素贞 || 4 | 5 | 37.0 | 许仙 || 5 | 5 | 37.0 | 不想毕业 || 6 | 5 | 37.0 | 好好说话 || 7 | 5 | 37.0 | tellme || 8 | 5 | 37.0 | 老外学中文 || 1 | 6 | 56.0 | 黑旋风李逵 || 2 | 6 | 56.0 | 菩提老祖 || 3 | 6 | 56.0 | 白素贞 || 4 | 6 | 56.0 | 许仙 || 5 | 6 | 56.0 | 不想毕业 || 6 | 6 | 56.0 | 好好说话 || 7 | 6 | 56.0 | tellme || 8 | 6 | 56.0 | 老外学中文 || 1 | 6 | 43.0 | 黑旋风李逵 || 2 | 6 | 43.0 | 菩提老祖 || 3 | 6 | 43.0 | 白素贞 || 4 | 6 | 43.0 | 许仙 || 5 | 6 | 43.0 | 不想毕业 || 6 | 6 | 43.0 | 好好说话 || 7 | 6 | 43.0 | tellme || 8 | 6 | 43.0 | 老外学中文 || 1 | 6 | 79.0 | 黑旋风李逵 || 2 | 6 | 79.0 | 菩提老祖 || 3 | 6 | 79.0 | 白素贞 || 4 | 6 | 79.0 | 许仙 || 5 | 6 | 79.0 | 不想毕业 || 6 | 6 | 79.0 | 好好说话 || 7 | 6 | 79.0 | tellme || 8 | 6 | 79.0 | 老外学中文 || 1 | 7 | 80.0 | 黑旋风李逵 || 2 | 7 | 80.0 | 菩提老祖 || 3 | 7 | 80.0 | 白素贞 || 4 | 7 | 80.0 | 许仙 || 5 | 7 | 80.0 | 不想毕业 || 6 | 7 | 80.0 | 好好说话 || 7 | 7 | 80.0 | tellme || 8 | 7 | 80.0 | 老外学中文 || 1 | 7 | 92.0 | 黑旋风李逵 || 2 | 7 | 92.0 | 菩提老祖 || 3 | 7 | 92.0 | 白素贞 || 4 | 7 | 92.0 | 许仙 || 5 | 7 | 92.0 | 不想毕业 || 6 | 7 | 92.0 | 好好说话 || 7 | 7 | 92.0 | tellme || 8 | 7 | 92.0 | 老外学中文 |+----+------------+-------+-----------------+select student.id,score.student_id,score.score student.name from student,score where student.id = score.student_id;-- 2.增加筛选条件，结果显示 id 相同的数据，如下：+----+------------+-------+-----------------+| id | student_id | score | name |+----+------------+-------+-----------------+| 1 | 1 | 70.5 | 黑旋风李逵 || 1 | 1 | 98.5 | 黑旋风李逵 || 1 | 1 | 33.0 | 黑旋风李逵 || 1 | 1 | 98.0 | 黑旋风李逵 || 2 | 2 | 60.0 | 菩提老祖 || 2 | 2 | 59.5 | 菩提老祖 || 3 | 3 | 33.0 | 白素贞 || 3 | 3 | 68.0 | 白素贞 || 3 | 3 | 99.0 | 白素贞 || 4 | 4 | 67.0 | 许仙 || 4 | 4 | 23.0 | 许仙 || 4 | 4 | 56.0 | 许仙 || 4 | 4 | 72.0 | 许仙 || 5 | 5 | 81.0 | 不想毕业 || 5 | 5 | 37.0 | 不想毕业 || 6 | 6 | 56.0 | 好好说话 || 6 | 6 | 43.0 | 好好说话 || 6 | 6 | 79.0 | 好好说话 || 7 | 7 | 80.0 | tellme || 7 | 7 | 92.0 | tellme |+----+------------+-------+-----------------+-- 3.再增加删选条件 name='许仙'
select student.id,student.sn,student.name,student.qq_mail,student.classes_id,sum(score.score) from student join score on student.id = score.student_id group by score.student_id;-- 解释分析sql语句-- 1.显示两张表笛卡尔积结果 select student.id,student.sn,student.name,student.qq_mail,student.classes_id,score.score from student,score;-- 2.增加筛选条件，结果显示 id 相同的数据，如下： select student.id,student.sn,student.name,student.qq_mail,student.classes_id,score.score from student,score where student.id = score.student_id;-- 3. 分组
（3） 查询所有同学的成绩，及同学的个人信息
select student.id,student.sn,student.name,score.score,course.name from student,course,score where score.student_id=student.id and course.id=student.id;
线上环境中一般禁止进行多表查询，尤其是两个（多个）表中数据多时，多表查询计算笛卡尔积开销非常大。但是线下环境是可以进行多表查询的（因为使用人数少）。
2.2 外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示位左外连接；右侧的表完全显示为右外连接。
语法：
-- 左外连接，表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件;-- 右外连接，表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件;
案例：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
-- “老外学中文”同学 没有考试成绩，也显示出来了 图1 select * from student stu left join score sco on stu.id=sco.student_id;-- 对应的右外连接 图2 select * from score sco right join student stu on stu.id=sco.student_id;-- 学生表、成绩表、课程表3张表关联查询 图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
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;-- 也可以使用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 = '计算机原理';
2.4 子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
2.4.1 单行子查询
单行子查询：返回一行记录的子查询。
案例：查询与“不想毕业” 同学的同班同学
-- 1.先查询"不想毕业"的同学所在 classes_id-- 2.再查询哪些同学也是这个 classes_id select * from student where classes_id= (select classes_id from student where name='不想毕业');
例如：查询与“不想毕业” 或 “好好说话”同学的同班同学
select * from student where classes_id= (select classes_id from student where name='不想毕业' or name='好好说话');
2.4.2 多行子查询
多行子查询：返回多行记录的子查询。
案例：查询“语文”或“英文”课程的成绩信息
1）[NOT] IN关键字
-- 1.通过课程名找到课程 id-- 2.在分数表中匹配所有 course_id 与上面相符的记录-- 先执行子查询 把子查询结果放在内存中,再执行主查询,拿着内存中的数据辅助进行筛选主表中的记录-- 适用于子查询结果比较少,主查询比较大的情况-- 使用IN select * from score where course_id in (select id from course where name='语文' or name='英文');-- 使用 NOT IN select * from score where course_id not in (select id from course where name!='语文' and name!='英文');
2）[NOT] EXISTS关键字
-- 先执行主查询 得到的结果再和子查询结果进行jion操作,即多表联合查询-- 适用于子查询结果比较多(尤其是在内存中不方便缓存"子查询结果"时)的情况-- 使用 EXISTS select * from score where exists (select score.course_id from course where (name='语文' or name='英文') and course.id = score.course_id); -- 使用 NOT EXISTS select * from score where not exists (select score.course_id from course where (name!='语文' and name!='英文') and course.id = score.course_id);-- 第一种（EXISTS） 更常用，相对来说效率也更高一些。
在from子句中使用子查询
在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
案例：查询所有比“中文系2019级3班”平均分高的成绩信息
-- 获取“中文系2019级3班”的平均分，将其看作临时表 SELECT avg( sco.score ) avgsco ,stu.name 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 ) avgsco 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.avgsco;
2.5 合并查询
在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致(列)。
1）union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：查询id小于3，或者名字为“英文”的课程
select * from course where id<3 union select * from course where name='英文';-- 或者使用or来实现 select * from course where id<3 or name='英文'
2）union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程
select * from course where id<3 union all select * from course where name='Java'; -- 结果Java出现两次
补充
计算机访问内存消耗时间一般是 ps~ns 级别
计算机访问磁盘消耗时间一般是 us 级别 计算机访问网络消耗时间一般是 ms~s 级别关系型数据库，有很多的约束，也要按照一定的格式来组织，操作时候需要进行一系列的效验。
对于一个大型系统，性能瓶颈很可能出现在数据库方面。
转载地址：http://hhjwi.baihongyu.com/
你可能感兴趣的文章
MySQL server has gone away 问题的解决方法
查看>>
MySQL十大优化技巧
查看>>
PHP中文件读写操作
查看>>
php开发常识b_01
查看>>
PHP单例模式
查看>>
PHP项目设计
查看>>
memcache的安装及管理
查看>>
git 传输
查看>>
创建新项目
查看>>
印刷工艺- 喷墨印刷
查看>>
印刷工艺流程
查看>>
印刷业ERP启蒙
查看>>
Java8 Lambda表达式使用集合（笔记）
查看>>
Java魔法师Unsafe
查看>>
spring cloud java.lang.NoClassDefFoundError: javax/servlet/http/HttpServletRequest
查看>>
Centos系统安装MySQL（整理）
查看>>
postgresql计算两点距离（经纬度地理位置）
查看>>
postgres多边形存储--解决 Points of LinearRing do not form a closed linestring
查看>>
postgresql+postgis空间数据库总结
查看>>
spring 之 Http Cache 和 Etag（转）
查看>>