1.插入查询

作用：新建一张新表，把旧表中的指定列数据导入到新表中。

语法：

insert into 目标表名 [(列名[,列名...])] select 列名[,列名...] from 旧表名;

• insert into... : 表示 新表中指定的要插入的列
• select... : 表示 要从旧表中查询出来的列

示例：创建一个数据库，在该库中，有一个 student 表，在该表中有一些学生记录：

此时创建了一个新的表 student2，该表中无数据记录：

想要将 student 表中的数据导出到 student2 表中，有三种方法：

1. 一条一条的重新插入一遍
2. 把原来的数据导出来，然后把表名改一下，再改入到目标表中
3. 使用 insert into select 语句

我们使用的是第三种方法。

2.聚合查询

2.1聚合函数

聚合函数是SQL中内置的一些函数，常见的统计总数、计算平均值等操作，可以使用聚合函数来实现，常见的聚合函数有：

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量(统计记录的行数)
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

之前学习的表达式查询，是针对数据表中的某一行记录中的列与列之间进行运算的，而聚合函数本质上是针对数据表中某一列的行和行之间进行运算的。

• COUNT()

count() 核心作用是统计 “非 NULL 的行数”。

语法：

select count(*) from 表名;
--统计满足条件的所有行（无论列值是否为 NULL），是 SQL 标准中最基础、最通用的行数统计方式

select count(1) from 表名;
--这里的 1 是一个常量值（你也可以写成 2、'a' 等任意常量），数据库会为每一行生成
--这个常量（永远非NULL），然后统计这些常量的数量 —— 本质还是统计行数

select count(列名[,列名...]) from 表名;
--指定查询某一列的行数

示例：有一个 exam 表，表中有一些记录：

统计表中记录的数量/行数：

如果指定统计某一列的行数，那么如果有 NULL 值，是不会被统计的：

• SUM()

sum() 核心作用是：计算指定列中所有非 NULL 数值的总和。

语法：

select sum(列名) from exam;

示例：计算所有学生语文成绩的总分

也可以为结果集的 sum(chinese) 列起一个别名：

当统计所有学生的英语成绩时，由于有一个同学的成绩是NULL值，那么这个NULL值是不会参与运算的，否则英语成绩总分最终结果是NULL(NULL与任何值运算结果都是NULL)，不合理： 

注意：聚合函数也可以加where子句等：

sum()函数运算的数据类型必须是数字，否则无意义：

• AVG()

核心作用是：计算指定数值列中所有非 NULL 值的算术平均值。

语法：

select avg(列名/表达式) from 表名;

示例：计算所有学生的语文成绩平均值

计算三门成绩的平均值：（参数是表达式）

• MAX()、MIN()

核心作用分别是：
MAX(列名)：找出指定列中所有非 NULL 值的最大值；
MIN(列名)：找出指定列中所有非 NULL 值的最小值。

语法：

select max(列名) from 表名;

select min(列名) from 表名; 

示例：找出语文成绩最高分和英语成绩的最低分

通过上述的例子说明，多个聚合函数可以同时使用。

在同一列可以使用不同的聚合函数：

2.2 GROUP BY 子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。

需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段(要对哪个列进行分组)”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

语法：

select colum1,聚合函数(colum2),... from 表名 group by colum1,colum3;

示例：创建一个表 emp，该表中有一些记录：

查询不同角色工资的平均值 —— 也就是 role角色列就是指定要使用 group by 子句的列，根据这个列，即根据不同的职位查询平均值：(MySQL内部先分组再计算)

我们可以使用 ROUND(数值，小数点位数) 函数在求平均值的同时规定小数点的位数：

也可以对平均工资升序排序：

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

2.3 HAVING 

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING。

注意：where 是对表中每一行的真实数据进行过滤的，而 having 是对 group by 之后，即分组之后，计算出来的结果进行过滤的，也就是说 having 是针对结果集中的数据进行过滤的，并不是表中真正的记录，而是通过聚合函数计算得出来的，例如，平均工资。

示例：查询平均工资大于8000，小于150万的职位：

显示平均工资低于1500的角色和它的平均工资：

总结：where 用在 from 表名 之后，也就是分组之前，having 跟在 group by 子句之后；如果需求要对真实数据进行过滤，同时也需要对分组之后的结果进行过滤，那么在合适的位置插入 where 和 having 即可。

3.联合查询

联合查询也叫表连接查询，联合查询步骤：

1. 首先确定哪几张表要参与查询
2. 对目标表取笛卡尔积
3. 根据表与表之间的主外键关系，确定连接条件join/where
4. 确定对整个结果集的过滤条件where(可能需要可能不需要)
5. 精简查询字段，得到想要的结果

设计数据时把表进行拆分，即拆分成多个表记录数据，这是为了消除表中的字段的依赖关系，例如部分函数依赖、传递依赖；这时会导致一条SQL查出来的数据是不完整的(数据来自不同的表)，于是就需要多表联合查询，把关系中的数据全部查询出来，在一个数据行中显示详细的信息。

（联合查询时可以对关联表使用别名。）

图例解释：

那么联合查询时MySQL是如何执行的？

—— 对多张表的数据取笛卡尔积(全排列结果集)

示例：有 class 和 stu 两张表：

此时当我们使用一条SQL语句查询出来的结果是不完整的，无法显示完整的信息，例如查询stu表中记录时只能显示班级编号而无法显示班级名，那么此时就需要使用联合查询：

语法：

select 列名 from 表名1,表名2,... ;

通过对查询结果的观察，两张表取笛卡尔积之后，有些数据是无效的数据，那么如何过滤掉这些无效的数据呢？

—— 通过连接条件过滤掉笛卡尔积中的无效数据：两个表之间是有主外键关系的，只需要判断两个表中主外键字段是否相等即可。而此题中，这两张表是通过 class_id 来建立主外键关系的，即为连接条件。

联合查询的连接方式有：内连接、外连接 和 自连接。

3.1 内连接

语法：

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

• 第二种书写方式是标准的方式，但是第一种更常用。

注意以下查询错误的原因：where 连接条件中的 class_id 和 id 字段这样写 MySQL 是无法分辨当前语句中这些字段应该取自哪张表的，如果是两张表中都有 class_id 这个列，那么就更加分不清了；因此，应该使用 表名.列名 的方式来表达，从而解决这个问题。

通过联合查询内连接 连接条件过滤出笛卡尔积中的有效数据，得出正确的结果集：

可以通过指定列查询，来精简结果集：查询列表中还是通过 表名.列名 的方式指定要查询的字段：

通过给表名起别名的方式来简化SQL语句：

使用内连接的标准写法：inner 可省略；join 两边是参与查询的表；on 后面跟的是连接条件。

(上述的where写法：where的左侧是要参与查询的表，右侧是连接条件)

示例2：有以下的这些表：

• 现在查询王五同学的成绩：

首先确定要联合查询的是学生表和成绩表，取两张表的笛卡尔积：

确定连接条件：学生表和成绩表是根据 student_id 作为主外键关系，因此连接条件是保证该列值相同（即对笛卡尔积进行过滤找出有效的数据），并且要的是名字为王五的同学的成绩，所以过滤条件为 name=王五； 【此题就需要过滤条件的】

最终的结果集就是从笛卡尔积中过滤出来的有效数据，即正确结果：

最后精简查询列表的字段：学生名，成绩(保留题目要求的结果)

或者使用标准写法：

可以增加一个课程名称：

示例3：查询所有同学的总成绩及个人信息

首先确定要参与的是学生表和成绩表：其中总成绩需要使用聚合函数，而使用之前需要进行分组查询group by —— 分组时使用学生编号id分组(姓名可能会有重名)。

先取得两张表的笛卡尔积，根据主外键关系确定连接条件 student_id，然后再根据连接条件过滤出有效的笛卡尔积数据：

然后按照学生的id进行分组查询，并在查询列表中使用 sum()聚合函数计算每个同学成绩的总分：

示例4：查询所有同学每门课的成绩及同学的个人信息

首先确定要查询的表是 学生表、课程表及成绩表，然后取这些表的笛卡尔积：

确定连接条件：根据表之间的主外键关系，确定学生表与成绩表之间通过 student_id 列建立关系，课程表与成绩表通过 course_id 列建立关系：

根据过滤条件，提取笛卡尔积中有效的数据，得出正确的结果：

最后精简查询字段：

使用标准写法：

3.2 外连接

外连接分为左外连接和右外连接。

如果联合查询，左侧的表完全显示就说是左外连接；右侧的表完全显示就说是右外连接。

语法：

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;

-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

示例1：在某个数据库中，有这样两张表：

当使用内连接时，是不会有 D 班的数据记录的，因为在 stu 表中没有来自D班的学生记录：

但是如果使用外连接，则可以查询出来关于D班的记录，只不过学生记录是空的：

此时是使用右外连接right join，因为class表可以完整显示出来，所以是以 join 右边的表 为基准。

解析：

此时如果向 stu 表中添加一条学生班级编号为6的新纪录，

那么就会以join左边的表为基准，即左外连接 left join，因为stu表中的数据能够全部显示，而class表中因为没有编号为5的班级与stu表中的记录进行匹配，因此不能够全部显示，用NULL填充：

示例2：查询没有考试成绩的同学

有以下的四张表：

要查询的是没有成绩的同学，那么说明该同学在学生表中有记录，但是在成绩表中没有该同学对应的记录，那么参与查询的表就是 学生表和成绩表，这两张表通过 student_id 建立关系，即 student_id 列作为连接条件过滤掉笛卡尔积的无效数据，且使用左外连接，如下图，王八同学就是没有成绩的学生：

此时将名字为王八的同学过滤出来(过滤条件)，最终得出的结果就是目标的结果：

说明：除了左外连接和右外连接外，其实还有全外连接 FULL JOIN ，但MySQL不支持全外连接。

3.3 自连接

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

在MySQL中，常规的表设计只能在一行的列与列之间进行运算，而无法做到在行与行之间进行运算：

但是行与行之间的运算可以通过聚合函数做到，还有就是自连接。

自连接可以把行转化成列，在查询的时候使用where条件进行过滤，从而实现行与行之间的运算功能。

示例：查询所有计算机原理成绩比通信原理成绩高的学生成绩信息

要查询出所有计算机成绩高于通信原理成绩的学生成绩，首先确定需要参与自连接的是成绩表，因为需要让某一个学生的计算机原理成绩与通信原理成绩进行比较，那么就需要有一张score表专门获取计算机原理的成绩，另一张表专门获取通信原理的成绩，因此取成绩表的笛卡尔积：

以上的错误是因为表名重复了，在同一个查询中多次引用同一个表(自连接)时，必须为每个表指定不同的别名，否则MySQL无法区分它们。为score表指定不同别名s1和s2后再取笛卡尔积：

(通过自连接后将行转化成了列)

确定连接条件：两张score表是同一张表，即两张表的内容一定是一样的，那么两张表通过 student_id 建立联系，student_id 列值必须相等，然后过滤出有效的数据：

以下表中两张表的结果集的每一行都对应了同一个学生。

根据课程表找出了计算机对应的编号是1，通信原理对应的编号是2：

那么我们要进一步对通过连接条件过滤的笛卡尔积进行过滤，筛选出编号为1和2的成绩，而现在有两张表s1和s2，有两种筛选方式：s1表专门筛选计算机原理 1，s2表专门筛选通信原理 2，或者反过来。现在以 s1--1 和 s2--2 的方式：

最后查询出计算机原理成绩高于通信原理成绩的同学：

结果集是一个空表，说明没有同学的计算机原理成绩高于通信原理成绩。

使用标准方式 join on 表示：

3.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。子查询是把一条SQL语句的查询结果，当作另一条SQL语句的查询条件，可以嵌套很多层。

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

示例：查询赵六同学的同班同学

可以看出子查询是由多条SQL语句组成的(嵌套)，其实子查询就是将一条一条单独的SQL语句拼接为一条SQL语句(把其他的SQL语句的结果当作条件)，最后得出查询结果。由于该嵌套的层级没有固定的限制，如果多层嵌套查询效率是不可控的，工作中谨慎使用。

（可以把上述的子查询叫做内层查询，之外的叫做外层查询）

如果难以理解，我们可以分步骤来：

1.查询赵六同班同学，需要知道他的班级，即为3班：

2.得知了赵六同学的班级，就可以查询他的同班同学了：

那么将上述两步拼接就是：

select * from student where class_id = (select class_id from student where name = '赵六');

找出赵六的同班同学自然是不包含赵六自己本身的，那么可以再加一个过滤条件：

以上示例的子查询是单行子查询，也就是只返回一行记录的子查询，即返回一个对象，例如就像找出赵六班级的那一条子查询语句（内层查询），只返回一行记录，即 3 给外层查询继续查询：

3.4.2 多行子查询：返回多行记录的子查询

单行子查询返回一行记录，那么多行子查询就是返回多行的子查询，即返回的是一个集合，集合中包含多个对象。

[NOT] IN关键字

示例：查询“高等数学”或“通信原理”课程的成绩信息

首先获取高数和通信原理课程的编号：

然后根据得到的两种课程的编号，查询出成绩信息：

想要将上述两个SQL查询拼接成一个SQL语句，由于在获取两个课程的编号信息时，也就是内层的查询结果是多行的，那么就是多行的子查询，返回的结果是一个集合，那么我们可以使用SQL中的 [NOT] IN ：该操作符表示的是如果变量在一个列表IN()中，那么返回真，否则取反。

如果想要查询的是除了高数和通信原理之外的成绩信息，那么使用 not in ：

示例2：查询出有重复成绩记录的同学信息

插入了三条重复记录：

首先需要对每个同学的不同成绩进行分类，即分组查询同一个学生，同一门课程，同样的成绩这三个列；如以下的SQL语句，将每个同学不同的课程成绩清楚的查询出来：

然后我们可以通过聚合函数 count(*) 来统计每条记录出现的次数，继而可以统计出出现重复成绩信息的记录：

接着根据以上的查询的结果，筛选出 count(*) > 1 的记录即为有重复成绩信息的记录：

最后，我们可以在上述的语句基础上加上一个外层查询，就变成一个多行子查询：

[NOT] EXISTS 关键字

语法：

select * from 表名 where exists (select * from 表...);

• exists：后面括号中的查询语句，如果有结果返回，则执行外层的查询，如果返回的是一个空结果集，则不执行外层查询。

示例：查询学生信息当学生编号1存在时

如果内层查询返回结果是空结果集，那么外层查询不会被查询，直接返回空结果集：

那么可以使用 not exists 来避免以上的情况：not exists 是如果内层查询返回结果是空结果集，那么对它取反，则最终就可以执行外层查询了。

说明：准确的说法是，当返回空结果集时，外层查询的最终结果取决于所使用的比较操作符(exists 还是 not exists)。

当内层查询查询的是 NULL 时，也表示返回的是有结果的结果集，因为 NULL 无论怎么运算结果都是NULL，不是空结果集，不是没有结果。

在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个 子查询当做一个临时表使用（也就是说子查询在from子句中使用，即将子查询当作一个表来使用，这个表是一个临时表）。

如以下，这个结果集在临时表中，是由学生表和课程表组合而成：

示例：查询所有比“3班”平均分高的成绩信息

首先我们要先算出3班成绩的平均分：

• 先从班级表中根据班级查询出班级编号
• 根据班级编号在学生表中找出3班的所有学生及学生编号
• 最后根据学生编号在成绩表中查询出3班的平均分
• 从上述的分析中，确定参与查询的成绩表是学生表、班级表、成绩表，然后根据连接条件筛选出笛卡尔积并且根据过滤条件得出查询目标结果集合，最后计算出平均分。

以上的查询的结果就是一个临时表，接下来要用学生的真实成绩与这个临时表中记录的平均分作比较，那么需要将这条查询作为一个子查询在from子句中使用，即作为一个表，并且由于这条子查询/ 表 很长，我们可以为这个表取一个别名，然后再做比较，得出比3班平均分高的所有同学：

3.5 合并查询

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

union

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

示例：查询id小于4，或者名字为‘王八’的学生

使用 union 操作符可以将两个语句合并（并集）：

但是，上述的操作是在一个表中操作的，在单表中，还是推荐使用 or 去连接不同的查询条件：

在多表中，就没办法使用 or ，如果最终结果是从多个表中获取到的，必须要用到 union 来进行合并：

示例：先创建一个和 student 表结构一样的表 stu：

然后往 stu 表中添加几行记录：

此时想要查询这两张表的结果，使用 union 操作符合并查询：

如果合并查询这两张表的指定查询的列不同，可以查询成功，但是这个结果没有意义，这种情况需要人工去规避：

示例2：此时的 stu 表中有两条记录和 student 表中的记录一样

那么使用 union 操作符进行合并查询时，会自动去重，与 student表重复的记录不会出现在结果集中：

union all

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

我们以上述的例子展示，确实不会去重，只是简单的把两个表的记录合并在一起查询出来：

4.练习

设计图书管理系统，包含学生和图书信息，且图书可以进行分类，学生可以在一个时间范围内借阅 图书，并在这个时间范围内归还图书。

要求：

1. 涉及以上场景的数据库表，并建立表关系。
2. 查询某个分类下的图书借阅信息。
3. 查询在某个时间之后的图书借阅信息。
4. 查询图书借阅周期在某个时间范围内的图书借阅信息（图书借阅周期与查询时间范围有交 集）。

根据要求：

• 需要一个图书类别表(id，类别名称)
• 图书表(id，名称，作者，价格，图书数量，图书状态(是否借出)，类别id)
• 学生表(id，学号，姓名，)
• 借阅记录表(id，学生id，图书id，借出时间，归还时间，借阅状态)
• 需要使用到 枚举类型enum

---------------------------------------------------------------------------------------------------------------------------------

5.一条SQL语句中各部分的执行顺序：

select distinct id,name,avg(age) 
from student 
join class on student.class_id = class.id 
where class_id = 1 
group by student.id 
having avg(age) > 0 
order by student.id asc 
limit 100;

执行顺序为：

1. FROM student JOIN class ON ...：从 student 表和 class 表中，根据 class_id 进行连接，生成初始数据集。
2. WHERE class_id = 1：过滤出 class_id 等于 1 的行。
3. GROUP BY student.id：按 student.id 对数据进行分组。
4. HAVING AVG(age) > 0：过滤掉平均年龄不大于 0 的分组。
5. SELECT DISTINCT id, name, AVG(age)：选择 id、name 列，并计算每组的平均年龄，同时去除重复行。
6. ORDER BY id ASC：按 id 列升序排列结果。
7. LIMIT 100：只返回前 100 条记录。

整理成表格即为：

总结：

• SQL 核心执行顺序口诀：从（FROM）连（JOIN）筛（WHERE）分（GROUP BY），聚（HAVING）选（SELECT）排（ORDER BY）限（LIMIT）；
• WHERE和HAVING的核心区别：WHERE筛单行（分组前）、不能用聚合函数；HAVING筛分组（分组后）、可以用聚合函数；
• 书写顺序可以灵活调整（只要符合语法），但数据库始终按 “逻辑执行顺序” 处理，这是排查 SQL 错误的关键。