MySQL数据查询--进阶操作（超详细）！！

本文链接：https://blog.csdn.net/2301_80133875/article/details/142062940

一：聚合查询：行操作

聚合查询就是“行与行”之间的运算，这里的运算限制，只能通过：“聚合函数”来进行操作，可以理解成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）：总结——在这个笛卡尔积的过程中，过程记录太冗杂了，用到了三个表，实际开发中不建议这么搞，容易搞挂数据库。