MySQL表的增删查改（3）——复杂情况

1. 表的设计

三大范式：

一对一：如一个学生可以有一个学号，一个学号只能被一个学生拥有

一对多：如一个学生只能属于一个班级，但一个班级可以有多个学生（这种方案在MySQL中是不可行的，但在有些数据库（如Redis中）能够支持列表，如下图：

多对多：如一个学生可以选择多门课程，一个课程可以被多个学生选择

2. 新增

插入查询结果（即将表1的查询结果插入到表2中，表1得到的结果集合（列数、类型、顺序）要和insert into后面的表2相匹配，列的名字不要求相同）

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

示例：

--只有表名不同的两个表，将表1中数据根据筛选条件新增到表2中
create table student(id int, name varchar(20));
create table student2(id int, name varchar(20));
insert into student values (1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(100,'zhaoliu');
insert into student2 select * from student where id < 50;


--表明不同，列名不同的情况，也可以成功新增
create table student(id int, name varchar(20));
create table student2(classId int, className varchar(20));


--表名不同，列名类型相同，但顺序不同
create table student(id int, name varchar(20));
create table student2(name varchar(20), id int);
insert into student2(id, name) select * from student; --两种方法均可
insert into student2 select name, id from student;

3. 查询

3.1 聚合查询

3.1.1 聚合函数

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

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

示例：

count

--统计班级里共有多少同学
select count(*) from student;
select count(0) from student;

--统计班级收集到的qq_mail有多少个，为NULL的数据不会计入结果
select count(qq_mail) from student;

---

 sum

--统计数学成绩总分
select sum(math) from exam_result;

--不及格(<60)同学的总分，没有结果返回null
select sum(math) from exam_result where math < 60;

tip：需要确保进行求和的列是数字，如果是字符串就会出现问题

虽然能将是数字的字符串转换出来，但是原则上不应该对字符串进行求和操作，如：字符串类型的手机号/身份证号...这些转换成数字进行求和操作是无意义的

---

avg

--计算平均总分
select avg(chinese + math + english) 平均总分 from exam_result;

max

--返回英语最高分
select max(english) from exam_result;

min

--返回 >70 分以上的数学最低分
select min(math) from exam_result where math > 70;

3.1.2 group by 子句

使用 group by 指定一个列，就会把 列的值 相同的 行 归到一组中，分完组之后，还可以针对每个组分别进行聚合查询

语法：

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

示例：

mysql> select * from emp;
+------+--------+-----------+--------+
| id   | name   | role      | salary |
+------+--------+-----------+--------+
|    1 | 张三   | 程序员    |  10000 |
|    2 | 李四   | 程序员    |  11000 |
|    3 | 王五   | 程序员    |  12000 |
|    4 | 赵六   | 产品      |   8000 |
|    5 | 田七   | 产品      |   9000 |
|    6 | 周八   | 老板      | 100000 |
+------+--------+-----------+--------+
6 rows in set (0.00 sec)

---

搭配条件使用的情况：

1）分组之前的条件：查询每个岗位的平均薪资，除去张三这个人

2）分组之后的条件：查询每个岗位的平均薪资，排除平均薪资超过5w的记录

3）一个查询同时包含分组前条件和分组后条件：查询每个岗位的平均薪资，排除张三，也排除平均薪资超过5w的结果

3.1.3 having

group by 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要用having，例如上面例2）和例3）。

3.2 联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询

多表查询是对多张表的数据取笛卡尔积：

tip：关联查询可以对关联表使用别名

 进行多表查询时，通常会通过一定的条件把笛卡尔积中的“合法数据”筛选出来，这样的条件就称为“连接条件”。

示例：初始化测试数据

-- 删除重复表
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 
('计算机系2024级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2024级3班','学习了中国传统文学'),
('自动化2024级5班','学习了机械自动化');


insert into student(sn, name, qq_mail, classes_id) values 
('09982','刘一','liuyi@qq.com',1), 
('00835','陈二',null,1), 
('00391','张三',null,1), 
('00031','李四','lisi@qq.com',1), 
('00054','王五',null,1), 
('51234','赵六','zhaoliu@qq.com',2), 
('83223','孙七',null,2), 
('09527','周八','zhouba@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),
-- 孙七
(80, 7, 2),(92, 7, 6);

3.2.1 内连接

语法：

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

示例：

（1） 查询“李四”同学的成绩

查询步骤：

a) 分析问题，了解清楚要查询内容在哪些表中存在（该流程使用 , where 的方式演示）

此处查询内容在 学生表 和 分数表 中

b) 对这两个表进行笛卡尔积

c) 指定连接条件，筛选掉无效数据

d) 根据需要进一步指定条件，对数据再筛选（新加的条件往往通过 and 的方式 和 连接条件放在一起）

 e) 针对查询中的列进行精简

---

多表查询的另一个写法 join on

a) 分析问题，了解清楚要查询内容在哪些表中存在

b) 对这两个表进行笛卡尔积

c) 指定连接条件，筛选掉无效数据（通过 on 关键字）

d) 根据需要进一步指定条件，对数据再筛选

e) 针对查询中的列进行精简

相比于直接多个表 , where 的方式来说，join on 的方式支持的功能更多一些，在实际开发中，两种写法都需掌握

（2）查询所有同学的总成绩，及同学的个人信息（每个同学都有多门课程，把多门课程的分数加在一起）

tip：此处分数不是按照 列 来表示的，而是按照 行！同时还需要搭配 分组 操作把每个同学的成绩相加

a) 分析问题，了解清楚要查询内容在哪些表中存在（学生表、分数表）

b) 对这两个表进行笛卡尔积

c) 指定连接条件，筛选掉无效数据

d) 针对上述数据进行 分组聚合

---

使用  join on 的方式：

（3）查询所有同学的成绩，及同学的个人信息（列出同学姓名、课程名字、课程分数）

tip：查出来的都是有成绩的同学，“周八”同学没有显示

a) 分析问题，了解清楚要查询内容在哪些表中存在

需针对 student、course、score 三个表计算笛卡尔积

b) 对这三个表进行笛卡尔积

c) 指定连接条件，筛选掉无效数据

e) 针对查询中的列进行精简

---

也可使用 join on 的方式来写，更能清楚的体现出联合查询的过程：

tip：虽然可以拿任意个表进行笛卡尔积操作，但是由于笛卡尔积可能会产生大量的“中间结果”，就会对性能影响很大，甚至严重的可能会将数据库搞挂，因此，多表联合查询 使用时要慎重，需要先对当前这样的多表查询大概会涉及到多少数据量操作，有一个“预估”。

3.2.2 外连接

外连接分为左外连接和右外连接，如果联合查询，左侧的表完全显示就认为是左外连接；右侧的表完全显示就认为是右外连接

语法：

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

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

示例：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需显示

示例数据：

, where 只能表示内连接，不能表示外连接

内连接查询结果，只包含两个表中同时具备的数据

在 join 前面加上 inner 就是内连接，平常可以省略不写

---

外连接就是给 join 前面加上 left / right 表示外连接（不支持 from 多个表）

左外连接：

左连接就是以左表为基准，能够确保左表中的每个记录都出现在最终结果中，如果左表中的记录在右表中没有对应的记录，此时就会把右表中的相关字段填为 NULL

右外连接

原理同上 左连接

全外连接：（MySQL不支持全外连接）

3.2.3 自连接

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

示例：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

通过上面两个表，可以发现学号为 1 和学号为 3 的同学满足条件

而 sql 中进行的条件查询是针对两个 列 进行比较的，不能比较两个 行

自连接本质上就能把 行关系 转换成 列关系（典型的数学思维：将未知问题转成已知问题）

将成绩表直接笛卡尔积：

错误信息：表的别名不唯一

这里需要给这个表前后分别起不同的别名，再进行连接

此时需要指定连接条件，筛选掉无效数据（此处连接条件不能选则课程id，因为课程id是问题条件，也不能选成绩，若碰巧遇到相同的成绩就会有误差，只能选学生id作为连接条件）

 

 进一步条件筛选

上面结果说明，score表中，只有三个同学同时具有 计算机原理 和 Java 课程成绩

再进一步筛选，发现当前要查询的条件是针对 两行，而不是 两列，就可以考虑使用自连接进行转换

 自连接的时候，表非常大，连接的开销也会非常庞大，容易把数据库搞挂！

3.2.3 子查询

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

简单理解就是套娃，本来就一个需求，需要通过多个 sql 来完成，但现在偏要把多个 sql 合并成一个，这种做法是和软件开发的基本原则（化繁为简）背道而驰的，所以不推荐使用

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

查询“王五”同学的同班同学

可以通过以下两步完成该任务

使用自连接的方式

 

很明显，这样的代码看起来更加复杂，可读性很差

---

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

示例：查询“语文”或“英语”课程的成绩信息

1. [NOT]IN 关键字

此时不能使用 = > < 这样的运算符直接比较了

可以使用in

2. [NOT]EXISTS 关键字

exists 非常消耗时间，背后会触发大量的硬盘 IO 操作，更不推荐使用！

---

3.2.5 合并查询

在实际应用中，为了合并多个 select 的执行结果，可以使用结合操作符 union、union all

使用 union 和 union all 时，前后查询的结果集中，字段需要一致

1. union

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

示例：查询 id 小于 3，或者名字为“英语”的课程

2. union all

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

示例：查询 id 小于 3，或者名字为 “Java” 的课程