MySQL——聚合函数&多表查询

CUBD进阶

查询结果插入

根据查询结果一次性插入多条记录（常用）

insert into tb_name(属性) select...

将select的结果集插入数据表中，select选出的属性要和插入的属性个数和类型都要一一对应

DROP TABLE IF EXISTS stu_test;
create table if not exists stu_test(
  id int primary key auto_increment,
  name VARCHAR(20) comment '姓名', 
  age INT comment '年龄', 
  email VARCHAR(20) comment '邮箱', 
  sex varchar(1) comment '性别', 
  mobile varchar(20) comment '手机号' 
);
-- 将学生表stu中的所有数据复制到stu_test 
insert into stu_test(name, email) select name, qq_mail from stu where name is not null and qq_mail is not null; 
select * from stu_test;

聚合函数

聚合查询指的是把行之间的数据聚集起来，与列无关。

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zz6G6DO0-1661621671443)(C:\Users\zhang’xia’lin\AppData\Roaming\Typora\typora-user-images\image-20220616153543538.png)]

count(属性)

1）count(*) 相当于 select(*) 效率比较低，全表扫描，统计总行数

2）count(任意数值)，效果等同于 count(*) 相当于在临时表中创建了一列属性，值都是count中填入的数值，统计一下当前表中有多少行，速度比 count(*) 快，因为它会使用索引

3） count(属性)，去除该属性中所有值为null的行，只统计不为null个数

select count(*) from stu;
-- 6
select count(0) from stu;
-- 6
select count(qq_mail) from stu;
-- 3
select count(name) from stu;
-- 6

sum(属性)

注：根据聚合函数得到的属性可以起别名

select sum(math) as `全班数学总成绩` from exam_result;

group by

一般聚合函数搭配group by分组查询使用

select中使用group by可以对指定的列进行分组查询

select column1, sum(column2), .. from table group by column1,column3;

使用 GROUP BY 进行分组查询时，要求：

• SELECT 指定的字段必须是“分组依据字段”
• 其他字段若想出现在SELECT 中则必须包含在聚合函数中

create table emp( 
	id int primary key auto_increment, 
	name varchar(20) not null, 
	role varchar(20) not null comment '雇员角色：老师，市场部，班主任', 
	salary numeric(11,2) 
);

insert into emp(name, role, salary) values 
('张三','teacher', 1000.20), 
('李四','teacher', 2000.99), 
('孙悟空','teacher', 999.11), 
('猪无能','class_mate', 333.5), 
('沙和尚','class_mate', 700.33), 
('隔壁老王','market', 12000.66); 

查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

先按照role进行分组，分组后使用avg等聚合函数对不同的组求值

注：group by可以使用select中的别名，因为它是先得到分组的临时表后才聚合查询的

having

having和group by搭配使用，having是对聚合的结果进行查询

GROUP BY 子句进行分组以后查询必须用having，不能用where。而分组之前的查询可以用where。

例：统计三种岗位的平均工资，保留平均工资>400的记录（聚合之后，已经求出平均值，用having）

统计三种岗位的平均工资，去除name=张三的薪资，保留平均工资>400的记录（name=张三是在聚合之前，select阶段，还未分组）

select role, avg(salary) 
from emp 
where name != '张三'
group by role
having avg(salary) > 400;

执行流程：先过滤where条件，对过滤条件之后的临时表进行分组，最后avg聚合

联合查询(多表查询)

多表查询是对多张表的数据取笛卡尔积，即遍历第一张表，取出一条记录和第二张表的每一条记录进行组合，得到一个更大的表，因此多表查询就是多张表的排列组合。

多表查询结果是两张表的组合，列数：夺标的列数之和，行数：多表的行数之积

注意：关联查询可以对关联表使用别名。

内连接

就是多表的笛卡尔积，根据关联关系严格筛选结果，内连接一定都是多个表同时存在关联数据的情况，合并后不会出现表1存在数据，表2没有数据的记录，这种记录关联之后就不会显示

如：学生表中有张三，但他没参加考试，成绩表中没有他的成绩。当查看学生基本信息及其成绩时，内连接学生与成绩表，但不会出现有关张三的记录

-- 内连接写法(推荐)
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 内连接条件（两张表的关联关系） and/where 其他条件（筛选条件）; 
-- 笛卡尔积写法
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

多表查询和子查询都一步步来，不要总想一步到位，一次就把正确的sql写出来

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, 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),
-- tellme
(80, 7, 2),(92, 7, 6);

-- 1.查询许仙同学的成绩
-- a.先进行两个表的内连接，此时student.id = score_student_id =》 每个同学的成绩
select name,score from student inner join score on student.id = score.student_id;

-- b.加上过滤条件，此时查询的是许仙同学的成绩,name = '许仙'
select name,score from student inner join score on student.id = score.student_id
where name = '许仙';

-- c.不使用join关键字
select name,score from student,score where student.id = score.student_id
and name = '许仙';

-- 2.查询每个同学的总成绩和他们的个人信息(sn,name,class_id)
select sum(score) as '总成绩',sn,name,classes_id from student 
inner join score on student.id = score.student_id
group by score.student_id;
-- 注意分清count和sum

-- 3.查询每个同学的学号，姓名，选修的课程名称，该课程的分数
select student.sn,student.name,course.name,score.score
from student 
inner join score on student.id = score.student_id
inner join course on course.id = score.course_id;
-- 或
select student.sn,student.name,course.name,score.score
from student, score, course
where student.id = score.student_id and course.id = score.course_id;

外连接

两表查询时，若某个表存在空数据，连接后结果仍显示出来

-- 左外连接，表1完全显示，表2中不匹配的数据就显示为null
select 字段名 from 表名1 left join 表名2 on 连接条件; 

-- 右外连接，表2完全显示，表1中不匹配的数据就显示为null
select 字段 from 表名1 right join 表名2 on 连接条件;

自连接

自连接是指在同一张表连接自身进行查询，即自己和自己连接。

一般MySQL筛选数据时都是不同行的列之间进行数据的比较，涉及到同一张表中行数据的筛选，用到自连接。

例：查询所有成绩优于java成绩的成绩信息

-- a. 进行两各表的自连接，去除不匹配的记录
select s1.student_id,s1.score, s2.score,s2.student_id
from score s1 inner join score as s2 on s1.student_id = s2.student_id;

-- b. 在课程表中找到课程名字为Java的课程的id
select id from course where name = 'Java';

-- c. 在自连接表中过滤出所有成绩 > Java成绩的记录
select s1.student_id,s1.score, s2.score,s2.student_id
from score s1 inner join score as s2 on s1.student_id = s2.student_id
where s2.course_id = (select id from course where name = 'Java') 
	and s1.score > s2.score;

查询成绩表中’计算机原理’成绩优于’java’的成绩信息

-- s1作为结果表展示，s1查找计算机原理
-- s2作为对比表，表示Java
select s1.*, s2.score,s2.student_id
from score s1 inner join score as s2 on s1.student_id = s2.student_id
where s1.course_id = (select id from course where name='计算机原理')
​    and s2.course_id = (select id from course where name = 'Java')
​    and s1.score>s2.score;

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

**单行子查询：**返回一行记录的子查询

例：查询与“不想毕业” 同学的同班同学：

-- a. 查找不想毕业同学的班级id
select classes_id from student where name='不想毕业';
-- b. 根据查找出来的classes_id=1去查找其他同班同学
select * from student where classes_id = 1;
-- c. 组装a，b两步的sql
select *
from student
where classes_id = (select classes_id from student where name='不想毕业');

多行子查询：

返回多行记录的子查询，根据返回的多条记录筛选结果，可以使用 in / not in 或 exists / not exists

例：查询语文或英文的成绩信息

-- 查询语文和英文的课程编号
select id from course where name = '语文' or name = '英文';
-- 写法一：in
select * from score
where course_id in (select id from course where name = '语文' or name = '英文'); 
-- 写法二：exists
select * from score 
where exists (select id from course where (name = '语文' or name = '英文') and score.course_id = course.id); 

多行子查询使用in和exists的区别：

第一种写法in，执行过程只有俩次查询，先执行内部查询，根据内部查询的结果筛选外部条件，子查询的结果会缓存在内存中，适用于子查询结果集较小的情况，效率比较高，但需使用内存空间

第二种写法exists，比较耗时，每次都是从外部查询出记录和内部查询匹配，内部子查询不会产生临时表，不耗费内存空间，适用于子查询结果集比较大，且内存放不下的情况。

临时表：

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

select ... from 表1， (select ... from ...) as tmp where 条件

查询出所有成绩比‘中文系2019级3班’的平均分高的所有成绩信息

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

-- UNION：去除重复数据 
select ... from ... where 条件 
union 
select ... from ... where 条件 

-- UNION ALL：不去重 
select ... from ... where 条件 
union all 
select ... from ... where 条件

SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having > select > distinct > order by > limit

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gsghfnxF-1661621671446)(img/image-20220701002539282.png)]

leetcode练习：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eR5veRcY-1661621671446)(C:\Users\zhang’xia’lin\AppData\Roaming\Typora\typora-user-images\image-20220616211548039.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6KKTpRpD-1661621671447)(img/image-20220617161010822.png)]

– UNION ALL：不去重
select … from … where 条件
union all
select … from … where 条件

SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having > select > distinct > order by > limit



[外链图片转存中...(img-gsghfnxF-1661621671446)]

leetcode练习：

[外链图片转存中...(img-eR5veRcY-1661621671446)]

[外链图片转存中...(img-6KKTpRpD-1661621671447)]