面试被暴虐，含泪整理了 MySQL增删查改2.0

写在前面

这篇博客是基于基本的 MySQL增删查改1.0 的进阶版本，这几天面试被暴虐，毕竟好久没用了，于是决定从新复习一遍！！！

一、查询

like

% 可以匹配多个字符

_ 匹配一个字符

null

如果想要查询某个字段为 null，需要使用 <=> 或者 is，而不是直接使用 null。

limit

limit 查询前几条数据

搭配其他 select 任意功能使用

offset（偏移量）

搭配 limit 使用

也可以省略 offset，相当于把 offset 的位置放在第一个位置

• 牛客练习题

二、约束

为什么要有约束类型？
符合一定条件的数据才能插入到数据库中，提高了靠谱性！！！

类型	作用
not null	指定某列不能为空
unique	保证每一行必须有唯一值
default	规定给没有赋值时的默认值
primary key（主键）	not null 和 unique 的结合，保证两列或者多列有唯一值
foreign key（外键）	保证一个表中的数据匹配另一个表中的值的参照完整性
check（mysql 5 不支持）	保证列中符合指定条件

not null

• 建表，将 blogId 设置为不为 not null。
  
• 插入数据为 null 试试

• 不为 null 试试

unique

• 建表，将 blogId 设置为 unique

• 插入 blogId=1 数据

• 再次插入 blogId=1 数据

default

• 建表，将 blogId 设置为 default

• 插入数据，blogId 不为 null

• 插入数据， blogId 为 null

和预想的不一样，实际上需要如下操作才会触发默认值

• 只插入 comment

primary key（主键）

主键：某一列记录的唯一身份标识

• 建表，将 userId 设置为 primary key

• 插入数据

• 插入重复数据

前面说过， primary key 相当于 unique + nut null

自增主键

主键不能重复，如果每次人工设置的话，那么成本是非常大的，因此有了自增主键

• 建表，将 userId 设置为 primary key auto_increment

• 插入四条 userId 为 null 的数据，查看变化

• 插入 userId 为 4 的数据

• 插入 userId 为 5 的数据

• 插入 userId 为 10 的数据，在插入一条 userId 为 null 的数据，查看变化

• 如何把自增主键还原为 1？

1. 删表重建表

2. truncate 表名

区别：删表重建表多了一些指令，而 truncate 直接一条指令完成了删表重建表。

foreign key

• 建立两张表，学生表（student）和 班级表（class），每个学生都有自己的班级； 如果学生表中某个学生的班级字段在班级表中没有，那么说明是错误的。因此可以用外键约束来解决这种问题。

• 在 class 表中添加数据

• 在 student 表中添加数据

• 目前来看都没错误，当插入 classId 为 10，也就是 class 表中不存在 classId = 10 时，会发生什么？

• 当想要删除 class 表中的老年大学2班 ，但是老年大学2班 中有陈大爷，那到底能不能删除呢？

• 因此想要删除班级，先要删除班级内的人，才能删除班级，如下

另一中方式实现自增主键

在前面说过 primary key 等价于 not null + unique，在 primary key 后加入 auto_increment 实现自增主键，因此也可以在 not null + unique + auto_increment 实现自增主键。

三、表的设计

关于表的设计，分为以下几个步骤

1. 首先要找实体，也就是 Java 中的找对象；
   例如设计一个简单的教务管理系统，教务管理系统中肯定需要 学生，课程，班级 这三个实体。实体找好了，那么怎么设计表呢？
2. 明确实体的属性
   学生表 ： 学生id，学生姓名，性别
   课程表 ：课程id，课程名字
   班级表：班级id，班级名字
3. 找到表与表之间的关系
   学生表与课程表：一个学生能选多门课，一门课也能被多个学生选择（一对多）
   学生表与班级表：一个学生只能有一个班级，一个班级可以有多个学生（多对多）
   课程表与班级表：…（没有关系）
4. 根据表与表之间的关系设置外键

学生表与班级表是一对多的关系，因此需要在班级表或者学生表中设置一个外键保证他们之间的关联关系

班级表如下：

班级id	班名
1	老年大学1班
2	老年大学2班
3	老年大学3班

学生表如下：

学生id	姓名	班级id
1	王大爷	1
2	李大爷	1
3	陈大爷	2
4	赵大爷	3

根据学生表中的 班级id 字段就能确定学生所属班级

学生表和课程表是多对多的关系，需要添加一张中间表来记录他们的关系

课程表如下：

课程id	课程名
1	如何俘获老伴芳心
2	王阿姨教你交际舞
3	我的前 60 年
4	我的余生

学生表如下：

学生id	姓名	班级id
1	王大爷	1
2	李大爷	1
3	陈大爷	2
4	赵大爷	3

中间表如下：

学生 id	课程 id
1	1
1	2
2	3
3	3
4	4

根据中间表就能找出某位学生选了哪些课程

还有一对一的关系，就像一个学生只能有一张床，一张床也只能有一个学生，因此可以在学生表中添加 床号 字段

学生id	姓名	班级id	床号
1	王大爷	1	10101
2	李大爷	1	10102
3	陈大爷	2	10103
4	赵大爷	3	10104

四、插入

• 建表，插入数据；

• 建另外一张表，查询插入

• 注意：需要保证列名、列名的类型相同

五、查询

聚合查询

聚合函数

函数	说明
count	返回查询到的数据的数量
sum	返回查询到的数据的和，不是数字没有意义
avg	返回查询到的数据的平均值，不是数字没有意义
max	返回查询到的数据的最大值，不是数字没有意义
min	返回查询到的数据的最小值，不是数字没有意义

• 建表，插入数据

• 使用 count

• 使用 sum

• 使用 avg

• 使用 max

• 使用 min

group by

• 建表插入数据

• 根据角色查询平均工资

• 查询最大值最小值等

• 剔除 小谢 的薪资 这个相当于先筛选再计算

• 先计算再筛选（使用 having）

联合查询

• 建多张表，插入多条数据

• 将 student 表和 score 表联合起来（笛卡尔积）

内容比较多，查看两张表的行数

发现 联合表的行数 = student 表的行数 * score 表的行数 联合表的列数 = student 表的列数 +score
表的列数 如果数据太多，那么这种查询的速度是非常慢的！

• 查询 黑旋风李逵 的所有课程成绩

• 查询所有同学的总成绩、平均成绩

联合查询步骤（以查询黑旋风李逵的成绩为例）：

1. 找出所需信息都在哪些表中；（查询同学的成绩需要 student 表、score 表和 course 表）
2. 针对这些表进行笛卡尔积；（将 student 表、 score 表和 course 表进行笛卡尔积）
3. 筛选条件；（student.id = score.student_id 和 course.id = score.course_id)
4. 加上其他条件，得到需求结果。（name = ‘黑旋风李逵’）

内连接

上述中的 student 表和 score 表使用 where 或者 join on 都是内连接

外连接

• 重新建表，并插入数据

>

• 使用内连接观察

• 使用左外连接和右外连接

• 理解外连接

自连接

一般来说 SQL 语句只能比较列和列之间的关系进行筛选，比如 select * from score where chinese > math；
如果想通过比较行和行之间的关系来筛选，就需要 自连接。
也就是把该表自己对自己进行笛卡尔积。

子查询

单行子查询（=）

• 建表，插入数据

• 查询 黑旋风李逵 的同班同学有哪些

多行子查询（in / not in）

• 建表，插入数据

• 查询语文成绩或英文成绩

in

not in

合并查询

union（去重）

• 建表，插入数据

• 使用 union 查询 id < 3 或者 name = 语文 的结果

• 使用 or 能达到同样的效果

• 去重效果表现

union all （不去重）

• 建表，插入数据

• 使用 union 查询 id < 3 或者 name = 语文 的结果

• 不去重表现