一:聚合查询:行操作
聚合查询就是“行与行”之间的运算,这里的运算限制,只能通过:“聚合函数”来进行操作,可以理解成SQL提供的一些“库函数”。
1:count
select count(*) from 表名
(*)替换成具体的列(列名),如果该列中含有null,不计入count
(*)查询,就算表里全为null的行,也会记录起来
2:sum
①select sum(chinese) from exam_result;
chinese那一列全部相加,sum()函数中null会忽略掉,但在SQL中null与其他数字进行运算时,结果一般都是null。
②select sum(name) from exam_result;
sql把字符串当做数字进行算术运算,都会尝试把字符串转化为数字,但是汉字不行
警告,但是不影响程序能继续跑
3:min()
查询整个表里语文成绩最低的同学 :select min(chinese) from exam_result;
等价写法:select chinese from exam_result where chinese not null order by limit 1;
4:avg() 和 gruop by
误区:没有“使用聚合的列”也可以和聚合函数完的列成为同一行数据
例如,按照职业计算其平均薪资
select role,avg(salary) from emp group by role; //这里的显示顺序不能认为是否存在任何顺序
5:having
分组之前(group by)的条件用where 来表示 , 分组之后用having来表示,用法同where
①:select role , avg(salary) from emp where name != '张三' gruop by role; //句意:把“张三”剔除后,各职业的平均薪资(分组前剔除)
②:select role avg(salary) from emp where name != '张三' group by role having avg(salary) < 20000; //句意:去除'张三'后,分组后,平均薪资水平低于20000的role
总结使用:举例,分组操作往往都是跟聚合函数一同使用的
二:联合查询--多表查询(重点)
注:多表联合核心操作就是笛卡尔积,比如两个表进行联合查询,那么就先让这两个表进行笛卡尔积,再加以限制条件,来实现一些需求中的查询结果,如果使用两个大号的表来进行笛卡尔积,危险操作,会产生大量的运算和IO,容易把数据库搞挂。
进行笛卡尔积的两个表,至少有一列是相关的,不然无意义
1:笛卡尔积:排列组合
select * from student , class;
显而易见,上述表中是一张由两张小表组合成的大表,但其中有一些不合理的数据,现在我们加以约束条件
select * from student , class where student.classId = class.classId ;
如果有两个以上的表进行笛卡尔积,那么就两两进行,ABC中 :A和B先进行笛卡尔积,(AB)在和C进行。
2:笛卡尔积的精简
(1)笛卡尔积:select * from student , score;
(2)指定连接条件,精简结果 : select * from student , score where student.id = score.student_id;
(3):再根据需求,补充其他条件
select * froms student , score where student.id = score.student_id and student.name = ‘许仙’;
(4):针对上面的列,在进行精简
select student.name , score.name from student , score where student.id = score.student_id and student.name = ‘许仙’;
3:多表查询另一种写法(join... on...)
(1)计算笛卡尔积:select * from student join score;
(2)指定连接条件:select * from student join score on student.id = score.student_id;
(3)指定其他条件:select * from student join score on student.id = score.student_id and student.name = '许仙';
(4)针对列进行精简:select student.name , score.score from student join score on student.id = score.student_id and student.name = '许仙';
4:联合查询和聚合查询的结合使用
(1):
写法①:select student.name , sum(score.score) as tatal from student , score where student.id = score.student_id gruop by student.name;
写法②:select student.name ,sum(score.score) as total from student join score on student.id = score.student_id group by student.name;
//as tatal 是为了让表显示更简洁
(2):错误写法
select * from student , score where student.id = score.student_id group by student.name;
分组后,不对列进行调整,得到的结果就是每个分组的第一条记录,无意义。切记切记。
5:三个表进行笛卡尔+聚合
(1)三表笛卡尔
(2)指定连接条件
A,B,C 找一个桥梁 比如B表:B中不仅含有跟A相关联的列,B也含有与C相关联的列,那么就让B作为一个桥梁来建立三者的联系。
即:表student 和 表score先笛卡尔 连接条件 student.id = score.student_id
上述结果,再和course表计算笛卡尔积 连接条件 score.course_id = course.id
select * from student , score , course where student.id = score.student_id and score.course _id = course.id;
结果如下表:显示行数大大减少
(3)指定其它条件
此处无
(4)针对列进行精简
写法①:select student.name , course.name , score.score from student , score , course where student.id = score.student_id and score.course _id = course.id;
写法②:select student.name , course.name ,score.score from student join score on student.id = score.student_id and join course on score.course_id = course.id;
三:内外连接
1:外连接
两个表的数据是一一对应的,学生表的任何一个记录都能在分数表中体现出来,反之亦如此,此时内连接和外连接的结果是一样的(因为两个表的id可以一一对的上,如果此处不理解是什么意思往下看)
对这两个表进行修改:
(1)左外连接
select * from student left join score on student.id = score.studentId;
解释:左外连接就是以左表为主,保证左侧的表的每一个记录都显示在最终的结果中,如果这个记录在右表中没有匹配,那就把对应的列填充成NULL;
(2)右外连接:
select * from studnet right join score on student.id = score.studentId;
解释:与左外连接相反
(3)全外连接
MySQL不支持全外连接,甲骨文公司的oracle才可以,此处不做详细介绍
2:内连接
写法①:select * from student , score where student.id = score.studnetId;
写法②:select * from student (inner) join score on student.id = score.studentId;
// inner join 就是内连接额意思,此处的inner可以省略
(1)以下面两张表为例:内连接,得到的结果必然是两个表的交集
3:自连接
解释:表自己与自己进行笛卡尔积
作用:有时候想要进行查询,但条件是行与行之间,此时就可以通过自连接把行关系转化为列关系
利弊:在开发过程当中,如果某个地方一定要用到自连接才能解决问题,就要思考一下是否是表设计的不合理。
应用场景:显示 “课程3” 比 “课程1” 分数高的 同学 的 信息
(1):查表
select * from score;
想要完成这个应用场景的实现(不同科目的比较),就需要比较行与行之间的大小(sql中无法做到),就只能把行转化为列了
(2):select * from score,score;
查询的表名相同,我们要给这个表起不同的别名可以
(3):select * from score as s1 ,score as s2 where s1.student_id = s2.student_id ;
(4):再进一步去筛选
select * from score as s1 ,score as s2 where s1.student_id = s2.student_id and s1.course _id = 3 and s2.course_id = 1;
(5):select * from score as s1 ,score as s2 where s1.student_id = s2.student_id and s1.course _id = 3 and s2.course_id = 1 and s1.score > s2.score;
(6):如果还想知道同学的姓名,那就,在此基础上在笛卡尔积一个学生表
select student.name from score as s1 ,score as s2 , student where s1.student_id = s2.student_id and s1.course _id = 3 and s2.course_id = 1 and s1.score > s2.score;
(7):总结——在这个笛卡尔积的过程中,过程记录太冗杂了,用到了三个表,实际开发中不建议这么搞,容易搞挂数据库。
四:子查询
1:套娃结构
解释:把多个简单的sql,合并成一个复杂的sql,俄罗斯套娃,实际开发非常不建议这么去做,但是我们得看得懂
应用举例:查询与“不想毕业”同学的同班同学
(1)select * from student;
(2)先找到“不想毕业”的 班级id,再拿着这个班级id来进行查询其它同学
select classes_id from student where name = '不想毕业';
(3)再找同学
select name from student where name = '不想毕业' and classes_id = (select classes_id from student where name = '不想毕业');
有点像数学中的参数带入的感觉。
五:合并查询
1:关键字union
select 1 union select 2;
解释:要求两个select 查询的结果集,列数和类型要匹配,列名不影响,最终的列名就是第一个select 的列名
应用举例:查询id < 3 ,或者名字为“英文”的课程
select * from course where name = '英文' union select * from course where id < 3;
注:
①:在这个场景中,我们可以发现用关键字or也可以解决,但是如果所查询的是两个不同的表就必须要用union这个关键字了
②:union 还可以对自动查询的结果进行去重,union all 不会去重。