MySQL 数据库 【增删查改（二）】

目录

一、表的设计 

1、一对一

2、一对多 

3、多对多

二、新增

 三、查询

1、聚合查询

（1）聚合函数：

（2） group by 子句

（3）having

 2、联合查询

(1)内连接

(2)外连接 

(3)自链接

 (4)子查询

(5)合并查询

四、总结

---

一、表的设计 

此处只是讨论一些比较基本的设计表的方法原则

设计数据库的表，就需要先把实体和关系梳理清楚

实体：从需求场景中，提炼出一些 关键性 的名词（可以理解成Java中的对象）

关系：是实体和实体之间的 关联关系

数据库这里的关系只有四种：

1、一对一

一个人可以有一个身份证号

一个身份证号只能归一个人所有

这三种方法 都是可以的 

---

2、一对多 

 一个同学 只能存在于一个班级中

一个班级，可以包含多个同学

 这种设计方式，并不推荐

因为MySQL 这里的类型，并没有提供  '数组' 这样的类型

如果想要这么做，就需要按照一定的格式把多个 student 构造成一个字符串

这样的过程比较繁琐，也比较低效

但是有些数据库（redis）支持 数组 这样的类型，就可以采取上述的方法来设计了

按照 classId 来查询 student 就能够知道这个表里面的每个班级里包含哪些同学 

---

3、多对多

 一个同学 ，可以选择多门课程来学习

一门课程 ，也可以被多个同学来选择

 往往表示多对多关系，需要引入一个关联表

---

二、新增

把insert 和 select 两个操作合并在一起了

语法：

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

 把 select 查询出来的结果数据，插入到另一个表中

我们先重新创建两个表 分别为 student 和 student2

并向 student 中插入三条数据：

 然后再运用上述语法，将 student 中的数据转移到 studen2 中

 此处 select 查询的记过 得和插入的表能对应（列的数目，类型，约束得匹配

---

 三、查询

1、聚合查询

表达式查询，本质上是在用 列 和 列 之间进行运算

还有的时候，我们需要进行 " 行 和 行 "之间的运算(聚合查询)

（1）聚合函数：

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

 使用示例：

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;、-- 返回英语最高分
SELECT MAX(english) FROM exam_result;-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

注意：在sql 中，要求聚合函数和 () 必须是紧紧挨在一起的

sum 是进行求和，会把这一列的若干行，按照 double 的方式进行累加。（会尝试把这一列的每一行的数据先转成 double ） 

---

（2） group by 子句

有的时候，希望把数据进行分组来进行计算，这个时候就要用到 group by 语句

group by 语句 是指定一个列，按照这个列进行分组，这一列中数值相同的行，会被放到一组

每个分组中，都可以使用上述的聚合函数来进行计算

我们重新创建一个员工表，并向其中插入几个数据：

现在，我们想统计每一个岗位的平均薪资，此时就可以使用 group by 子句 

 在分组查询中，select 指定的列，必须是当前group by 指定的列，如果select 想用到其它的列，其它的列必须放到聚合函数中，否则直接写，此时查询的结果无意义！！！

---

（3）having

分组查询，也是可以搭配条件来使用的

1、分组之前的条件： where

示例：求每个岗位的平均薪资，但是除掉张三

2、分组之后的条件 : having 

示例： 求每个岗位的平均薪资，但是除去平均薪资超过10000 的

分组之后的条件也是搭配聚合函数来使用的

 3、一个SQL 中，可以同时包含分组前的条件和分组后的条件

示例：求每个岗位的平均薪资，但是除掉张三和除去平均薪资超过10000 的

---

 2、联合查询

联合查询也叫做多表查询，是查询中最复杂的一种写法

联合查询就是给你多个表，结合多个表的数据，进行一些综合性更强的查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

针对任意的两张表都可以计算笛卡尔积，但是一般来说，如果两个表没有任何的关系，那么计算的结果也是无意义的

笛卡尔积 就是把两个表里的记录 按照排列组合的方式 构成了一个更大的表

笛卡尔积的列数  就是原来两个表的列数之和

笛卡尔积的行数  就是原来两个表的行数之积 

笛卡尔积是一个非常低效的操作，尤其是表的本身的记录比较多的情况

同样的 多表联合查询也是非常低效的

因此在实际开发中，使用联合查询一定要慎重

计算笛卡尔积的时候，会出现一些不太靠谱的数据，如果此时我们加上链接条件，那么筛选出来的数据就都是合理的数据了

---

(1)内连接

语法：

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

我们现在有四张表，包含了三个实体：学生、班级、课程

其中包含的关系有：学生和班级：一对多的关系、学生和课程：多对多的关系

分数表，就相当于学生和课程之间的这个关联表

我们先使用学生表和班级表进行笛卡尔积

 此时可以看到，现在得到的这些结果就是笛卡尔积的结果

这个时候，如果 class_id 和 id 是一样的，那么我们就称这个数据是合法的数据

此时，我们的笛卡尔积在加上一些必要的条件后，就构成了多表联合查询

如果是单个表进行查询，条件中直接写列名即可

如果是多个表进行查询，条件中最好写作 ' 表名. 列名' 的形式，这是因为进行联合查询的这两个表里，有些列名可能是一样的，如果列名没有重复的，此时直接写列名也不是不可以，但是最好还是带上表名

示例：查询许仙同学的成绩

" 许仙 " 来自于 student 表 ，' 成绩 ' 来自于 score 表

（1）我们先把 score  表和 student 表进行笛卡尔积

（2）指定链接条件  此处是按照 学生id 进行筛选

（3）根据需求，进一步的添加条件  此处按照名字为 “ 许仙 ”再来进行筛选

（4）针对查询的结果的列，进行精简

 多表查询，本质上就是把所有的情况都类出来再筛选出合适的，因此，多表查询效率是比较低的

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

每个同学，可以有多门课程，这几门课的成绩，是按照行来组织的

(1) 两个表进行笛卡尔积

(2) 指定链接条件

(3) 这里要求的是每个学生的总成绩，就需要按照学生维度进行 group by

(4)  需要搭配聚合函数，针对分数进行进一步计算 

 示例：列出同学的名字，课程名字，分数

现在，我们需要通过 student , course , score 三张表进行联合查询

这里 student 和 course 是多对多的关系，score 是它们之间的一个关联表，因此三个表进行笛卡尔积筛选数据，就需要两个链接条件

此处进行三张表的笛卡尔积，由于此时的表比较小，所以执行笛卡尔积的过程其实是非常快的，但是此处显示了几秒才结束，这个是控制台打印的缘故 

此处能够写出链接条件 是因为这两个实体之间存在关联关系，如果是没有关系的两个表，这种条件就写不了

前面多个表，我们使用 逗号 来分割，现在使用 join 来分割，前面链接条件通过 where 指定，现在使用 on 来指定

 示例：查询许仙同学的成绩

（1）直接只写 join 没有 on 则是完整的笛卡尔积

（2）使用 on 表示链接条件

（3）结合需求，加上其它的条件

（4）针对 列 进行精简 

 

---

(2)外连接 

那么内连接和外连接有什么区别呢？

我们现在假设有这么两张表：

此时，左侧表的每一条记录，都能在右侧找到对应的，右侧表的记录也能在左侧找到 

此时针对这两个表，进行内连接和外连接，结果完全相同

当这两个表里面的数据对不上的时候，内连接就和外连接产生差别了

例如这种情况，此时王五同学在 score 表中没有数据， id 为 4的同学，在学生表中，也没有数据 

内连接的时候，产生的结果一定是两个表中都存在的数据（公共的部分）

 

 外连接，在MySQL 里，有两种情况，一种叫 左外连接 ，一种叫 右外连接 left join / right join

左外连接就是以左侧表为主，左侧表的每个记录， 都会存在于最终结果里，如果遇到了左侧表中存在，但是右侧表中不存在的数据，此时就会把对应的 列 填成空值(王五的成绩是空)

 

同理，右外连接就是以右侧的表为主，右侧表的每个记录，都会存在于最终结果里，如果遇到了右侧表中存在，左侧表中不存在的数据，也会把对应的列填成 NULL 

 

全外连接

这个操作，MySQL 不支持，但是Oracle 能支持 

---

(3)自链接

 自链接 本质上是自己和自己做笛卡尔积

本质是把 行 之间的关系，转换成 列 之间的关系

SQL 中，编写条件，条件都是 列 和 列 之间进行比较，但是SQL无法进行 行 和 行 之间的比较

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

（1）算出笛卡尔积

（2）加上链接条件，此处可以使用 学生id 作为链接条件，也可以使用 课程id 

（3）再加上进一步的业务中的条件，让左侧的表只保存 course_id = 3 的记录，同时，让右侧的表保存 course_id = 1的记录

（4）再针对列进行精简

score 表是按照 行 来组织多个课程的成绩的

因此，在这里 我们要将 行 转换成  列（把未知问题，转换成已知问题）

我们会发现，当直接对score 表及其自己进行笛卡尔积的时候，会进行报错

这是因为，select 的时候，多个表的名字不能相同，因此需要起一个别名，也是使用 as 起别名

 

这个时候，score 就可以进行笛卡尔积了

 最后的答案如下：

---

 (4)子查询

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

本质上就是把多个SQL合并成一个SQL 

 示例：查询与 "不想毕业" 同学同班的同学 

分成两步完成，是比较推荐的做法 

 子查询就是将两个SQL语句合并在一起

SQL 支持这么写，但是并不建议，因为可读性低

IN 关键字

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

EXISTS 关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

---

(5)合并查询

 把多个 select 查询得到的结果合并成一个集合，关键字：union / union all

如果想要出现两个张三，可以使用 union all  

 能合并的前提是这两个查询的结果集的 列 得对应

之前学的 or 也是把一些结果合并起来，但是只能局限在一个表里里面

union 则是针对任意两个表的数据进行合并

---

四、总结