目录
PS：distinct（去重） VS group by（分组）
③（常见面试题）为什么 where 不能用别名，而 having 和 group by 能用别名？
PS：插入查询结果
把一张表查询出的结果，插入到另一张表中。要求两张表的列数和列类型要匹配。
前面讲的所有select规则在此处都适用~
1.聚合查询
- 聚合查询：行和行之间进行运算。
- 带表达式查询：列和列之间进行运算。
常⻅的聚合函数（SQL里内置的函数）有：
它们都是针对表中某个列的所有行来进行运算的~
MySQL的函数，相当于Java里的方法：
聚合函数需要设置参数，在查询中可以使用多次聚合函数，可以多个聚合函数一起使用。
不是数字的数据，直接舍弃。
1.1.count函数
返回查询到的数据的条数。
用法一：
能查询出所有null和非null的数据总和。推荐使用，最标准，MySQL、SQL Server、Oracle都能使用。
用法二：
能查询出所有null和非null的数据总和。兼容性不是很好，MySQL能用，其他数据库不一定能用。
用法三：
count(列名)，查询当前字段中非null的数据总和。不推荐使用，查询结果极不稳定。
建议写成：
用法四：
count(表达式)。
count(1)1就是常数表达式。
在不同的count统计场景下，要使用不同的count查询。
若要查询表中非null的数据总和，建议使用count(*)配合where条件使用。
1.2.sum函数
返回查询到的数据的总和，不是数字没有意义。
PS：sum函数执行逻辑——只会统计有效的数据
判断数据是否为有效数据：
- 若是有效数据：会将其加入已经计算的结果集中。
- 若不是有效数据（NULL或非数值型（比如varchar型））：会将其看作0，并加入已经计算的结果集中。
PS：sum函数查询 VS 表达式求和查询
若要求和查询的数据中包含NULL：
- sum函数：会把除去NULL外的其他数据进行求和运算。
- 表达式求和：直接得到和为NULL。
1.3.avg函数
返回查询到的数据的平均值，不是数字没有意义，会将含有不合规范数据的一行数据整体舍弃掉。
1.4.max函数
返回查询到的数据的最⼤值，不是数字没有意义。
1.5.min函数
返回查询到的数据的最⼩值，不是数字没有意义。
PS：ifnull函数
ifnull函数是 MySQL 控制流函数之⼀，它接收两个参数，如果第一个参数不是 NULL，则返回第⼀个参数，否则返回第⼆个参数。
ifnull(参数1,参数2);
使用ifnull函数的实例——解决遗留问题：总成绩为null的问题
另外的解决⽅案：使⽤⾮空约束或默认值 0 来解决。
注：
- 在公司里多使用分布式多份部署，一份程序部署到了多台服务器，处理能力会是原来的多倍，但没有办法把MySQL分成多份。
- so~MySQL的资源很紧缺，而程序的资源相对不紧缺。在MySQL中应尽量不使用表达式和函数（它们应该是在Java程序里）。
所以这种问题最好在建表时就设置好非空约束，从而避免ifnull查询，减轻MySQL负担。
2.分组查询group by
select 中使⽤ group by ⼦句可以对指定列进⾏分组查询。
需要满⾜：使⽤ group by 进⾏分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在 select 中则必须包含在聚合函数中。
select 列名1 [,sum(列名2)...] from 表名 group by 列名1 [,列名3];PS：distinct（去重） VS group by（分组）
①创建测试数据
②distinct使用
基本语法：
select distinct 列名 [,列名...] from 表名;a.单列去重
根据 aid（文章 ID）去重：
b.多列（两列及以上）去重
根据 aid（文章 ID）和 uid（用户 ID）联合去重：
c.聚合函数 + distinct
计算 aid 去重之后的总条数：
③group by使用
基本语法：
select 列名 [,列名...] from 表名 group by 列名a.单列去重
根据 aid（文章 ID）去重：
与 distinct 相比 group by 可以显示更多的列，而 distinct 只能展示去重的列。
b.多列去重
根据 aid（文章 ID）和 uid（用户 ID）联合去重：
c.聚合函数 + group by
统计每个 aid 的总数量：
从上述结果可以看出，使用 group by 和 distinct 加 count 的查询语义是完全不同的，distinct + count 统计的是去重之后的总数量，而 group by + count 统计的是分组之后的每组数据的总数。
④distinct 和 group by的区别
官方文档在描述 distinct 时提到：在大多数情况下 distinct 是特殊的 group by：
但二者还是有一些细微的不同的，比如以下几个：
区别1：查询结果集不同
1）当使用 distinct 去重时，查询结果集中只有去重列信息。当试图添加非去重字段查询时，SQL 会报错：
2）而使用 group by 排序可以查询一个或多个字段：
区别2：使用业务场景不同
1）统计去重之后的总数量需要使用 distinct。使用 distinct 统计某列去重之后的总数量：
2）而统计分组明细，或在分组明细的基础上添加查询条件时，就得使用 group by 了。使用 group by 统计分组之后数量大于 2 的文章：
区别3：性能不同
1）如果去重的字段有索引，那么 group by 和 distinct 都可以使用索引，此情况它们的性能是相同的。
2）而当去重的字段没有索引时，distinct 的性能就会高于 group by，因为在 MySQL 8.0 之前，group by 有一个隐藏的功能会进行默认的排序，这样就会触发 filesort 从而导致查询性能降低。
PS：group by 注意事项
以下内容基于MySQL5.7 InnoDB 数据库引擎。
①group by 之后不能加 where
要求语法顺序：
- select
- from
- where
- group by
- having
- order by
- limit
select 执行顺序：
- from 阶段
- where 阶段
- group by 阶段
- having 阶段
- select 阶段
- order by 阶段
- limit 阶段
②having 或 group by 可单独使用。
③（常见面试题）为什么 where 不能用别名，而 having 和 group by 能用别名？
having 和 group by 可使用别名。 where 不能使用别名。order by 可使用别名。
- where不能用别名，这和MySQL语句执行顺序有关。在执行where查询时，select还没执行完，因此where中想要用还未执行完的select中的别名是不行的。
- order by 能使用别名是因为 select 是在 order by 之前。
- 若按上述逻辑，在 group by 和 having 中也不能使用别名~but：
在 group by 和 having 中能使用别名是因为MySQL为了使用方便，在5.7.5之后做了扩展，允许在 group by 和 having 中使用别名，官方文档中有对应说明。
PS：having VS where
- having能用别名，where不能用别名。
- where能用select中未出现的列进行条件筛选，而having不行。
-- 准备测试表及数据：
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2) -- numeric是decimal更细化的一个类型，用法和decimal类似
);
insert into emp(name, role, salary) values
('⻢云','服务员', 1000.20),
('⻢化腾','游戏陪玩', 2000.99),
('孙悟空','游戏⻆⾊', 999.11),
('猪⽆能','游戏⻆⾊', 333.5),
('沙和尚','游戏⻆⾊', 700.33),
('隔壁⽼王','董事⻓', 12000.66);
2.1.分组条件查询having
group by⼦句进⾏分组以后，需要对分组结果再进⾏条件过滤时，不能使⽤where语句，⽽需要用having。
- group by是在where之后，它的执行顺序也是在where之后。
- having是在group by之后，它的执行顺序也是在group by之后。
2.2.SQL查询关键字执行顺序
SQL 查询执⾏先后顺序： group by > having > order by > limit。
3.联表查询（多表查询）
3.1.前置知识——笛卡尔积
笛卡尔积⼜称直积，表示为 X*Y，⽐如 A 表中的数据为 m ⾏，B 表中的数据有 n ⾏，那么 A 和 B 做笛卡尔积，结果为 m*n ⾏。
要进行联表查询，首先得到笛卡尔积，再去除掉无效数据，得到有效数据。
⽐如以下表，⼀个⼈只能在⼀个班级，它们的笛卡尔积就有 9 个：
排除掉脏数据，有效的数据⾏信息有：
| 用户表-编号 | 用户表-名称 | 用户表-班级id | 班级表-编号 | 班级表-班级名 | 班级表-状态 |
| s1 | 张三 | c1 | c1 | Java31 | 正常 |
| s2 | 李四 | c2 | c2 | Java33 | 正常 |
| s3 | 王五 | c3 | c3 | Java30 | 已毕业 |
若有3张表，表中数据行分别为a行、b行、c行，那么三张表做笛卡尔积，结果为a*b*c行......
创建数据库和表：
-- 创建数据库
drop database if exists java33;
create database java33 default character set 'utf8mb4';
-- 切换数据库
use java33;
-- 创建班级表
drop table if exists class;
create table class(
id int primary key auto_increment comment '班级编号',
classname varchar(250) not null comment '班级名称'
);
-- 创建学⽣表
drop table if exists student;
create table student(
id int primary key auto_increment comment '学生编号',
sn varchar(50) comment '学号',
username varchar(250) not null comment '学生名称',
`mail` varchar(250) comment '邮箱',
class_id int,
foreign key (class_id) references class(id)
);
-- 创建课程表
drop table if exists course;
create table course(
id int primary key auto_increment comment '课程编号',
name varchar(250) not null
);
-- 成绩表
drop table if exists score_table;
create table score_table(
id int primary key auto_increment comment '成绩编号',
score decimal(4,1),
student_id int not null,
course_id int not null,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);添加测试数据：
-- 班级表添加数据
insert into class(id,classname) values(1,'Java班级'),(2,'C++班级');
-- 课程表添加数据
insert into course(id,name) values(1,'计算机'),(2,'英语');
-- 学⽣表添加数据
insert into student(id,sn,username,mail,class_id)
values(1,'CN001','张三','zhangsan@qq.com',1),
(2,'CN002','李四','lisi@qq.com',2),
(3,'CN003','王五','wangwu@qq.com',1);
-- 成绩表添加数据
insert into score_table(id,score,student_id,course_id) values(1,90,1,1),(2,59,1,2),(3,65,2,1),(4,NULL,2,2);
3.2.内连接
内连接侧重于两个表之间的共性，它的作⽤是使⽤联接，⽐较两个（或多个）表之间的共有数据，然后进⾏返回。
⽐如要查询学⽣的成绩，涉及到两张表：学⽣表和成绩表，使⽤内连接查询的数据是下图的红⾊部分：
3.2.1.内连接语法
select * from 表名1 [inner|cross] join 表名2 [on 过滤条件] [where 过滤条件];
-- inner 内连接
-- cross 交叉
-- join 加入（内连接关键字，必须要有）
-- on 从语法上可以省略，如果省略 on 得到的是多表的笛卡尔积，会含有无效数据
-- where 根据业务场景来决定是否要加内连接的写法有以下 4 种：
- select * from 表名1 join 表名2 [on 过滤条件] [where 过滤条件]; (两张表、多张表建议使用)（主要掌握）
- select * from 表名1 inner join 表名2 [on 过滤条件] [where 过滤条件];
- select * from 表名1 cross join 表名2 [on 过滤条件] [where 过滤条件];
- select * from 表名1, 表名2 [where 过滤条件]; （不支持on过滤条件）(两张表建议使用)（主要掌握）
3.2.2.实战
①查询张三的成绩
a.进行内连接查询（笛卡尔积）
b.去掉无效的数据（on过滤条件）
c.查询张三的成绩
写法一：
写法二：
写法三：
写法四：
改进：不要使用*，只查询需要的列：
若多张表中有相同字段名，得区分开不同的表名中的字段名，使用别名会简单些。
不强制要求给表起别名，但建议都加上别名。
表的别名通常是使用表前缀的缩写。
②查询每个⼈的成绩和个⼈信息
③查询每个⼈的总成绩和个人信息（姓名）
a.查询学生表和成绩表（笛卡尔积）
b.去掉无效数据(关联字段)
c.使用聚合函数 sum 统计总成绩
- 如果成绩是多列存储，使用表达式。
- 如果成绩是多行存储，使用聚合函数sum()。
d.根据学生（id）分组查询每个人的
④查询每个⼈成绩+科⽬名+个⼈信息
-- 1.联表查询（3张表）
select * from 表名1 join 表名2 join 表名3;
-- 2.过滤掉笛卡尔积中的无效数据
select * from 表名1 join 表名2 [on 过滤条件] join 表名3 [on 过滤条件];
⑤查询学生的成绩和科目及个人信息，要求根据相同科目显示在一起，并按照成绩降序排列，并将null替换成0
- a.实现3张表的联查join（笛卡尔积）
- b.得到笛卡尔积中的有效数据 join...on...join...on
- c.将科目显示到一块（对行顺序有要求）使用排序：order by c.name
- d.上一个排序的规则上，加联合排序：order by c.name, st.score desc
- e.select ifnull(st.score, 0)
3.2.3.内连接查询的"问题"
我们发现学⽣表有 3 个⽤户，然⽽使⽤内连接查询的时候，王五同学的数据⼀直没被查询到，王五同学可能是考完试转班过来的，所以只有学⽣表有数据，其他表没有数据。但即使这样，我们也不能漏⼀个⼈，如果其他表为空，成绩可以是 NULL 或者 0，但不能遗漏，这个时候就需要使⽤外连接了。
3.3.外连接
外连接包括内连接和其他⾄少⼀张表的所有满⾜条件的信息，外连接包括：
- 左（外）连接
- 右（外）连接
其中左连接查询的内容如下图红⾊部分：
右连接如下图红⾊部分：
3.3.1.左连接
左连接表示以左边的表（表1）为主查询数据。
表1中的所有数据都会被查询出来，表2只会查询出和表1相关联的数据。
select * from 表名1 left join 表名2 on 连接条件;
-- 必须加 on查询所有人的成绩：
查询所有人的个人信息+课程名+分数：（3张表要把中间关联表放在中间位置）
3.3.2.右连接
右连接表示以右边的表（表2）为主查询数据。
表2中所有的数据都会被查询出来，表1只会查询出和表2相关联的数据。
select * from 表名1 right join 表名2 on 连接条件;
3.3.3.左连接 VS 右连接
- 左连接：查询出左表中的所有数据，和右表中关联的数据。
- 右连接：查询出右表中的所有数据，和左表中关联的数据。
所有的左连接都能使用右连接来实现，所有的右连接都能使用左连接来实现。
如果查询的是两张表，只需要调换两个表的位置，就可以轻松实现左连接和右连接的互换，因此掌握一种外连接来查询即可。
3.3.4.on 和 where 的区别
- 在内连接中 on 可以省略；在外连接（左/右连接）中 on 不能省略【若在外连接中就想不要 on 只查询出笛卡尔积，则可以写上 on 后加一个永远为 true 的无意义的表达式，如 on 1=1】。
- on 在内连接中的执行效果和在外连接中的执行效果不一样：left join on 查询不能过滤掉左表中的数据，能过滤掉右表中的数据；而内连接 on 查询能过滤掉全局数据。
- 在外连接中 on 和 where 的作用也不一样：过滤笛卡尔积的条件放在on后，其他多个查询条件放在where后。
select * from student where 1=1 and username='xxx' and score='xxx' and password='xxx' and ...;
若查询条件是可选项，可以在where后加上永远为true的条件表达式，这样就可以不用管username、score、password...谁是第一个，谁是第二个...因为第一个前有and，就不用管where后第一个条件表达式的问题了。
--->①on 在内连接和外连接中的作用不一样
- 内连接 on 查询能过滤掉全局数据。
- 外连接 on 查询不会对主表中的数据过滤，只会对次表中的数据过滤。
- left join on 查询不能过滤掉左表（主表）中的数据，能过滤掉右表（次表）中的数据。
- right join on 查询不能过滤掉右表（主表）中的数据，能过滤掉左表（次表）中的数据。
on 在内连接中的作用：
on 只过滤笛卡尔积中的无意义数据：
on 过滤全局列表中的数据：
on 在外连接中的作用：
--->②在外连接中 on 和 where 的作用不一样
- 内连接2张表查询时，多个查询条件可以一股脑都放在on后。
- 外连接2张表查询时，过滤笛卡尔积的条件放在on后，其他多个查询条件放在where后。
3.4.自连接
⾃连接是指在同⼀张表连接⾃身进⾏查询。⾃连接就是查询同⼀张表的内查询。
select *|列名 [,列名...] from 表名 [as] 别名1, 表名 [as] 别名2 where 别名1.字段名=别名2.字段名 [,...];查询英语成绩<计算机成绩的数据：
- 都是查询成绩，自查询（2张成绩表进行查询）【笛卡尔积】。
- 添加where条件：保证2张成绩表都是一个人的成绩【where st1.student_id=st2.student_id】表1和表2里都是既有英语成绩又有计算机成绩。
- 添加where条件：指定表1为英语表，表2为计算机表。
- 添加where条件：英语成绩<计算机成绩的数据。
- 自查询是根据业务id进行笛卡尔积过滤。
- 非自查询是根据主键id进行笛卡尔积过滤。
还可以使⽤内连接join语法来查询：
3.5.子查询（嵌套查询）
⼦查询是指嵌⼊在其他 sql 语句中的 select 语句，即将当前查询的结果作为另一个查询的where选项。也叫嵌套查询。
①查询张三的同班同学
- 查询张三的班级 id。
- 查询学生表，where 条件是张三的班级 id。
②查询计算机或英语的成绩
- 查询计算机或英语的科目 id。
- 查询成绩表，where 条件是科目 id 等于计算机 id 或等于英语 id。
- = 查询需要一个具体确定的值。
- in 查询可以是一个或多个值，且满足任意一个将返回 true。
③查询分数比Java班平均分高的学生
- 根据 Java 班查询出班级 id。
- 根据班级 id 查询出这个班所有的学⽣ id。
- 根据学⽣ id 查询出 Java 班的所有成绩列表。
- 使用聚合函数计算出 Java 班的平均成绩。
- 查询成绩表，添加where条件：成绩>上一步得到的平均成绩。
3.6.合并查询
合并查询⽤于合并结果集相同的两张（多张）表，它有两个关键字： union 和 union all。
①union 使用
进行结果集的合并且执行去重，只保留重复数据中的一条数据。
查询 id ⼩于 3 和名字为英语的课程：
②union all 使用
该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，不会去掉结果集中的重复⾏。
查询 id ⼩于 3，或者名字为“英语”的课程：
378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
