MySQL —— 聚合查询，分组查询 与 团结查询
聚合函数常见的统计总数、计算平手值等操作，可以使用聚合函数来实现，常见的聚合函数有：
函数阐明count()统计数据总数sum()求和avg()求均匀值max()求最大值min()求最小值 留意凡是涉及运算的，数据库会主动掉 NULL 值
留意NULL 是不到场比较 max 与 min 的
解析：
以此表为例
https://i-blog.csdnimg.cn/direct/d251ddba42b543098922b3f26a029255.png
count()
count()，会统计数据总数
使用 count(*) 会查询一共有多少条数据行
https://i-blog.csdnimg.cn/direct/30340d537c984a148414b187955c8bba.png
使用 count(列名)，会统计该列有多少行数据
https://i-blog.csdnimg.cn/direct/81bc04f0106043199ea9e774c5f9881a.png
如果列中有NULL 值，则不会被统计在内
https://i-blog.csdnimg.cn/direct/338c86ad40704b27a062b257adb79f59.png
建议使用 count(*) 来统计数据行，这是SQL 尺度提出的。
sum()
如果运算中有NULL 值会主动过滤NULL，因为NULL 颠末运算后为 NULL这个数据是没有意义的，所以数据库的开发者们进行了如许的运算设计。
https://i-blog.csdnimg.cn/direct/0e2833851ee9466a865f3bac8fcd680c.png
如果运算遇到非数字型数据，则无法进行运算，会报告诫：
https://i-blog.csdnimg.cn/direct/98e09884a76440feb952aa3c9e8d7d43.png
留意可以使用表达式，但是如果想分别求每一列的总分还是要分开写的。
https://i-blog.csdnimg.cn/direct/52bc00175f8444c88ffc830be368e269.png
avg()
https://i-blog.csdnimg.cn/direct/c4a61676c3b94f3d8c15729143cac470.png
max() 与 min()
https://i-blog.csdnimg.cn/direct/123c67f8bdf9414d836172798052004e.png
留意NULL 是不到场比较的
https://i-blog.csdnimg.cn/direct/4435c3928b914504b985b9c59c3bcf8d.png
实践
1.统计班级共有多少同砚
select count(name) from exam;
2.统计班级收集的 math 数学成绩数据 有多少个
select count(math) from exam;
3.统计数学成绩总分
select sum(math) from exam;
4.统计所有数学成绩不及格 （< 60） 的同砚的数学总分
select sum(math) from exam where math < 60;
5.统计三科的均匀总分
select avg(chinese + math + english) 三科平均分 from exam;
6.返回英语最高分
select max(english) from exam;
7.返回 > 70 分以上的数学最低分
select min(math) from exam where math > 70;
分组查询
group by
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满意：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出如今SELECT 中则必须包含在聚合函数中。
select column1, sum(column2), .. from table group by column1,column3;
演示表：
https://i-blog.csdnimg.cn/direct/367480aef9da4dc681eeedccbbc82034.png
计算每种职位的均匀工资：
https://i-blog.csdnimg.cn/direct/2ede060207dd4db9b5409ec1f8dabfb2.png
这里的实验顺序是先分组再计算。
拓展 round
可以使用 round(数值,小数点后的位数) 来指定命值的形式：
group by 后面可以跟 order by 子句
https://i-blog.csdnimg.cn/direct/5bbf9ef104ad4689a2bf116216d53047.png
练习：
查询每个角色的最高工资、最低工资和均匀工资
select role 职位, max(salary) 最高工资, min(salary) 最低工资 from emp group by 职位;
https://i-blog.csdnimg.cn/direct/d36b0a1757544985a4857e2df76cbc01.png
having
GROUP BY 子句进行分组以后，需要对分组效果再进行条件过滤时，不能使用 WHERE 语句，而需要用
HAVING
where 是对表中每一行的真实数据进行过滤的
having 是在 group by 之后，对计算效果进行过滤的。
所以两个实验顺序是不一样的，having 可以使用别名来过滤
演示：
显示均匀工资低于1500的角色和它的均匀工资
select role 职位, avg(salary) 平均工资 from emp group by 职位 having 平均工资 < 1500;
https://i-blog.csdnimg.cn/direct/1de8c950b7c04636b9ef2a757414b2b7.png
团结查询
现实开发中往往数据来自差别的表，所以需要多表团结查询。多表查询是对多张表的数据取笛卡尔积：
https://i-blog.csdnimg.cn/direct/39a2c717dcf9483ea32d4339649dc3f6.png
笛卡尔积现实上就是对数据进行全排列，举个例子，有两张表，其中一张表的一条数据要和另一张表的所有的数据进行组合：
https://i-blog.csdnimg.cn/direct/db03d49776fd49c896634cc38952ca2e.png
我们也可以通过 SQL 代码来查看笛卡尔积：select * from table_name1, table_name2;
![在这https://i-blog.csdnimg.cn/direct/c5bd5ff006a946568919e8a4dff7ce9f.png
通过观察我们得知上面全排列的数据不全是精确的，那我们如果过滤掉这些无效的数据，从而获取精确的数据？
请看下面揭晓
内毗连
语法格式：select 字段 from 表1 别名1, 表2 别名2 where 条件; 或者 select 字段 from 表1 别名1 join 表2 别名2 on 条件;
两个表之间存在主外键关系的话，只需要判断这两个表中主外键字段是否雷同即可。
查询列表的字段 可以使用 表名.列名
我们可以通过给表名取字段的方式来镌汰我们的书写量。
演示：
select s.student_id, s.sn, s.name, s.mail, c.name from student s, class c where s.class_id = c.class_id;
https://i-blog.csdnimg.cn/direct/220873abc02b43809f39bc008ef590fc.png
select s.student_id, s.sn, s.name, s.mail, c.name from student s inner join class c on s.class_id = c.class_id;
https://i-blog.csdnimg.cn/direct/7234b40fb2264fc49960ae7417cd4247.png
https://i-blog.csdnimg.cn/direct/0ada1025b56c4badb5176af2fb64dedd.png
当你给表取了别名之后，那就将表名的地方全部更换成别名，否则 where 子句会辨认不出。
团结查询的步调：
起首确定查询中涉及哪些表，然后对这些表取笛卡尔积，再确定毗连条件与过滤条件，最后简化语句（使用别名）
实践:
查询白素贞的成绩：
起首确定需要哪些表：门生表和成绩表，取笛卡尔积：
https://i-blog.csdnimg.cn/direct/1096c7efc0e9469bbfb762a212f9c904.png
然后确定毗连条件：student_id 是雷同的
确定过滤条件：姓名是白素贞
简化 sql 语句，将student 取 stu , score 取 sco
select stu.name, sco.score from student stu, score sco where name = '白素贞' and stu.student_id = sco.student_id;
select stu.name, sco.score from student stu join score sco on name = '白素贞' and stu.student_id = sco.student_id;
https://i-blog.csdnimg.cn/direct/be36ae7c355a442e96e4f11ac81063f6.png
查询所有同砚的总成绩，及同砚的个人信息：
起首确定需要什么表：门生表，成绩表；然后取笛卡尔积：
https://i-blog.csdnimg.cn/direct/9f558b12af7343d386fec0c7c1a9a32f.png
然后确定毗连条件与过滤条件：起首是由于需要的是总成绩，所以要使用聚合函数 sum()，那么就要使用到 分组查询 group by 子句，接着成绩表和门生表的毗连是 student_id 要雷同
这里要留意分组的依据，我们是对成绩表进行分组的，成绩表有门生的 id 和 成绩，那就应该是要按门生的 id 作为分组的依据。
最后简化 sql 语句 将student 取 stu , score 取 sco
select stu.name, stu.mail, sum(sco.score) from student stu, score sco where stu.student_id = sco.student_id group by sco.student_id;
https://i-blog.csdnimg.cn/direct/f350759fefa7491badb3c486a43fc17c.png
查询所有同砚的总成绩，及同砚的个人信息 以及 门生所在的班级信息：
起首确定要几张表：门生表，班级表 以及 成绩表，然后取笛卡尔积：
https://i-blog.csdnimg.cn/direct/0259939d64544203a2ec05901c32fc48.png
然后确定毗连条件与过滤条件：门生表和班级表的联系是 class_id 雷同，门生表和成绩表的联系是 student_id 雷同，总成绩就和上面的方式一样使用 sum() 通过 student_id 来进行分组。
然后简化 sql 语句：
select stu.sn 学号, stu.name 姓名, stu.mail 邮箱, sum(sco.score) 总成绩, c.name from student stu, score sco, class c where stu.student_id = sco.student_id and stu.class_id
= c.class_id group by sco.student_id;
https://i-blog.csdnimg.cn/direct/7d8176a434334e6abd997d7f4a623fe5.png
外毗连
外毗连分为左外毗连和右外毗连。如果团结查询，左侧的表完全显示就是左外毗连；右侧的表完全显示就是右外毗连。
语法：左外毗连： select 字段名 from 表名1 left join 表名2 on 毗连条件; 与 右外毗连： select 字段 from 表名1 right join 表名2 on 毗连条件;
大家来看一下下面两张表，你会发现 3班 是没有门生的。
https://i-blog.csdnimg.cn/direct/92ee379d6c44401cb9700d24f3d6a907.png
https://i-blog.csdnimg.cn/direct/4e9f7706cb504843a290b5ba0f61e3d8.png
如今我们基于上述的式子，演示左外毗连：select * from class c left join student s on c.class_id = s.class_id;
https://i-blog.csdnimg.cn/direct/0ede5010bf5f4826af9d224b04ccde00.png
纵然 3 班是没有同砚的，但是3班这个字段还是会显示出来，只是对应的门生列表为空。
如今我们插入一个没有班级的门生数据：
https://i-blog.csdnimg.cn/direct/eb636b8b49e84ff5bd1c785dfa90b8d8.png
然后我们来演示右外毗连：select * from class c right join student s on c.class_id = s.class_id; 这里会将 student 表全部显示，纵然有门生没有班级这个数据。
https://i-blog.csdnimg.cn/direct/91cf4b69cd9d49cca9e39aca4a37818c.png
进行外毗连如果遇到没有数据的时候，数据库会使用 NULL 添补。
自毗连
自毗连是指在同一张表毗连自身进行查询。
语法：select * from 表名1 别名1, 表名1 别名2;
留意一定要起别名，不然MySQL 无法辨认：
https://i-blog.csdnimg.cn/direct/5e3721fe974b4339870a15664f97ea28.png
一样平常自毗连会用在自己要和自己比较的时候
演示:
查询哪些门生的 Java 成绩 比 计算机原理要低：可以先查出Java 和 计算机原理的 course_id
select * from score s1, score s2 where s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score;
https://i-blog.csdnimg.cn/direct/78b7e336fbd148bfa10f6a18cba27c55.png
子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询：
根本语法：select * from table_name where 列名 = (select 列名 from table_name where 条件);
查询与“白素贞” 同砚的同班同砚：select * from student where class_id = (select class_id from student where name = '白素贞');
https://i-blog.csdnimg.cn/direct/fee06e7dcd994b2189d062796cd96b03.png
多行子查询：
IN
in 之条件到过就是在不在 in 括号的字段范围内
语法：select * from table_name1 where 列名 in (select * from table_name2);
举例：查询 Java 和 计算机的成绩
select * from score where course_id in (select course_id from course where name = 'Java' or name = '计算机原理');
https://i-blog.csdnimg.cn/direct/c0bff819d3bb4b42a21f54930639b34a.png
EXISTS
exists 表示存在，如果 exists 后面括号中的查询语句，如果返回的是空效果集，那就类似flase ，不会实验外层的查询，如果返回的是 true ,就会实验外层的查询。
语法：select * from table_name where exists (select * from table_name);
留意如果集合是 select null; 集合不为空（empty），只是集合内容是 null
https://i-blog.csdnimg.cn/direct/775aef2325344eea9594380d403f752b.png
演示：
https://i-blog.csdnimg.cn/direct/68188e03b39c48f5aab0b36e717a560e.png
合并查询
合并查询可以将多个效果集合并。
使用UNION和UNION ALL时，前后查询的效果集中，字段需要一致。
union 【会去重】
该操作符用于取得两个效果集的并集。当使用该操作符时，会主动去掉效果集中的重复行。
union all 【不会去重】
该操作符用于取得两个效果集的并集。当使用该操作符时，不会去掉效果集中的重复行。
查询id小于3，或者名字为“英文”的课程：
https://i-blog.csdnimg.cn/direct/cf12005bbbb44759b80081aef9286f60.png
https://i-blog.csdnimg.cn/direct/0a9524fe0c70460784fd9ce1a3eb3d27.png
https://i-blog.csdnimg.cn/direct/b80b226a990f41ccada0d134bb03c8e8.png
在单表查询时，推荐使用 or， 多表查询时可以使用 union 或者 union all
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]