MySQL - 对数据表进行“增删查改”的进阶操作 - 细节狂魔

文章目录

前文知识点回顾

上篇文章MySQL - 对数据表进行“增删查改”的基础操作讲到：
“增删查改” 又称 CURD操作。
C - create
U - update
R - refer
D - delete

1、新增操作（insert关键字）

命令格式 ：
insert into 表名 values (列的值，列的值…);

【插入的列值个数，取决于你数据表的列数】
新增操作可以指定插入的列：
insert into 表名 （列名，列名…） values (列的值，列的值…);

新增操作还可以一次插入多行记录：
insert into 表名 values (列的值，列的值…),(列的值，列的值…),…;

查询 （select ：关于 select 的操作，都不会影响到数据库中的原始数据）

1、全列查找 ：
select * from 表名;

2、指定列查找
select 列名,列名… from 表名；

3、指定表达式
select 表达式 from 表名;
注意：这里的表达式：往往是列和列之间的运算，并且得到的结果是一个临时表，其值不会受到原始数据类型的影响，MySQL会自动适应，将其打印出来。

4、给表达式指定别名
select 表达式 as 别名 from 表名;【此处的别名就相当于查询结果的临时表的列名】

5、去重 ：
select distinct 列名 from 表名;【根据指定列的值，进行去重】

6、排序
单列排序：select 列名 from 表名 order by 列名/表达式/表达式的别名 排序规则（asc / desc）;
asc ：升序 ； desc ： 降序

多列排序：select 列名,列名… from 表名 order by 列名, 列名,列名… 排序规则（asc / desc）;
以第一列为准，进行排序。
如果指定的 第一列中有两个数据相同，这这两个数据，谁在上，谁在下。由指定的第二列对应的两行的列值的大小所决定；如果第二列恰巧也是相同，则有第三列的对应的两行的列值的大小所决定，以此类推。
其目的：就是排序的顺序是可以预期的。
多列排序的效果 和 单列排序是一样的，只能保证 指定的第一个列是有序的。

7、条件查询
select 列名 from 表名 where 条件;
where 后面的条件，起着刷选数据的作用：会遍历 服务器数据库中数据表的记录/数据，依次代入条件中，如果满足条件，将这个记录保留，反之，就直接跳过。

条件 涉及到 一些运算符：> 、<、=、 <=> 、is null、is not null、like、between and 、 in(…)
还有一些逻辑符 ：and（与）、or（或） 、not（非）【and 的优先级 比 or 高】
另外注意：where 后面的条件中不能包含别名

8、分页查询
select 列名 from 表名 limit N offset M；
从 第 M 条记录开始，一个返回 N个记录。【M是从0开始，也就是说 第一条记录对应的就是零】

修改 - update

update 表名 set 列名 = 值,列名 = 值… where 条件;
此处的修改是针对“满足where条件”的记录进行的。
【update的修改是会作用硬盘上的原始数据（原始数据改变了），条件千万一定要仔细写！！！！（避免误改）】
如果 没有 where 条件的限制，update 操作会将 指定的列，每一行的数据都修改。

删除 - delete

delete from 表名 where 条件;
此处的删除是针对“满足where条件”的记录进行的。
delete删除操作是会作用硬盘上的原始数据（原始数据是真的没了），条件千万一定要仔细写！！！！（避免误删）】
如果 没有 where 条件的限制，delete 操作会将数据表中的所有数据清空。

正文 - 数据库的约束

约束类型

约束就是我们的数据库在使用的时候，对于里面能够存储的数据所提出的要求和限制。
程序员就可以借助约束来完成更好的校验，

1、not null - 指示某列不能存储 NULL 值。

如果使用 not null 指示某列不能存储 NULL 值时，尝试往这里插入空值，就会直接报错。
简单来说，not null 用于创建数据表时，指定 列的插入值的情况不能为空值，必须是具体的数据。

2、unique - 保证某列的每行必须有唯一的值。

数据唯一：尝试插入重复的值，也会报错，

3、default - 规定没有给列赋值时的默认值。

修改 / 指定 某一列的默认值。

4、primary key - 主键约束 - 日常开发中最常使用的约束！也是最重要的约束

primary key 主键约束 相当于 not null 和 unique 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键约束，相当于数据的唯一身份标识，类似身份证号码。

创建表的时候，很多时候都需要指定的主键。

5、foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键，描述的是 两个表之间的关联关系，表1 里的数据 必须在 表2 中存在

外键约束语法：create table 子表名(列名 类型,列名 类型,…  列名 类型, foreign key (子表中置为外键的 列名) references 父表名(依赖的父表中列名));

上面所讲的都是针对子表的操作，下面我们来针对一下父表的操作。
为什么要针对父表进行操作，是因为 外键约束同样也在约束父表。

外键约束的工作原理

拓展

6、check - 保证列中的值符合指定的条件。对于MySQL数据库，对check子句进行分析，但是忽略check子句 - （了解）

指定一个条件，通过条件来对值进行判定。
但是 MySQL并不支持这个。

表的设计

所谓的“数据库设计” 和 “表的设计”其实就是根据实际问题场景，把表给创建出来。
【我觉得这个设计问题，就和 java 的 类 和 对象，差不多。将一个现实问题抽象一个类，这个类具有我们解决问题的属性和方法，再通过实例化去使用它。来解决我们实际问题】

但凡是和“设计”鱼贯的的话题，都比较抽象，一般来说都是都需有一定的经验。
很显然对于我们这些还未踏入职场的新人来说，肯定是缺乏的。

一般是给我们一个问题场景，让我们思考如何设计数据库，如何设计表。
一个典型的通用的办法：先找出这个场景中涉及到的“实体”，然后再来分析“实体之间的关系”。
【实体 就类似 java 中 对象，一些操作，是需要对象与对象之间的相互作用】
实体就可以视为是需求中的一些关键性的名词。
下面讲一个典型的场景

实体之间的关系

实体之间的关系主要可以分为以下几种：
1、一对一的关系【一个钥匙，开一扇门】
2、一对多的关系【一个钥匙，开多扇门】
3、多对多的关系【n 个钥匙，开m扇门】
4、没有关系【就是两个互不相干的表】-
分析实体之前的关系，一个关键性技巧就是造句。
不要想太复杂，就是小学语文的造句，给你几个词，用它造一个句子。

1、一对一的关系【一个钥匙，开一扇门】

一对一的关系很好理解：就是一一对应的关系。
就以上面讲的 学生管理系统为例。
学生的基本信息简称学生表（学号，学生姓名，学生班级。。。。）

校园网，想必大家都很熟悉，每个人都有一个学生账号，那么我们就需要一个 账号管理的 user 表（账户姓名，账号，密码…）

提取关键名词： 学生 和 账户
造句：一个账户对应到一个学生，一个学生只有一个账户。
这个就是一对一的关系。

那么如何在数据库中表示这种一对一的关系呢？
方法一; 可以把这两个实体用一张表表示
【就是这个表概括 学生表 和 user 表的 所有信息】

方法二：用两张表表示：一张表包含了另一张表的 列的信息。（外键约束）
【根据这个对应的关系，就可以随时找到某个账户所对应的学生是谁，也就能找到这个学生的对应账户】

一对多的关系【一个钥匙，开多扇门】

假设我们有一个学生表 （学号，姓名…）；一个 班级表（班级编号，班级名称…）
这就是一个 一对多的关系
还是造句： 一个 学生 属于一个 班级，一个班级可以包括多个学生。
一对多 就可以看作 一个班级 有多名学生。

在数据库中表示一对多的关系，也有两种方法
方法一：
就跟前面的举例子是一样的：在学生表中添加一个列 classId 用来表示学生所在的班级编号。

方法二：
就跟前面的举例子是一样的：在班级表新增一列来表示 这个班级的学生id。

对于 MySQL来说， 表示一对多关系的时候，只能采用方法一。
方法二之所以不能使用，是因为MySQL中没有数组这个类型，所以无法用代码表现出方法二。

如果你用的是 Redis 这样的数据库，就有数组类型，就可以考虑使用方法二来表示。

多对多的关系【n 个钥匙，开m扇门】

这种多对多的关系，在数据库表示的方式只有一种：使用一个关联表 来表示两个表之间的关系。

总结

这些实体之间的关系，你可以理解为一个技巧，一种思维。就像辅助外挂一样。
另外有的时候，为了耿芳变的 表示/找到实体之间的关系（尤其是针对比较复杂的场景），还可以通过画ER 图的方式来表示。【不要问我 ER ，我只是知道有这个东西】

对数据表进行“增删查改”的进阶操作

新增 - 进阶操作

和 查询操作 结合在一起的操作。
把从上一个表中的查询结果，作为下一个表要插入的数据。
语法格式：insert into 表B select * from 表 A;
将表A的遍历结果插入表B 中。

小拓展

另外，还可以给后面的 select 指定一些其他的 ：条件，排序，limit，去重…
插入的数据，实际就是select的执行结果的临时表。
【注意，这种操作语法上，只要类型匹配，就能插入。但是不一定有意义】

查询 - 进阶

聚合查询

前面讲的基础查询带表达式的操作是针对列与列之间的关联运算。
而这里的聚合查询，是针对行与行之间的数据进行了关联操作。
聚合查询：就是把 多行的数据给进行了关联操作。

常见的聚合函数

说到聚合查询，就不得不提到聚合函数。
MySQL 内置一些聚合函数，可以让我们直接来使用。【如下图所示】

【expr - 表达式、avg -> average - 平均值、 distinct - 去重（可以添加，进去数据去重）】

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

聚合函数 ：count

返回查询到的数据的 数量。换个说法 查询结果有多少行。
命令格式：select count(列名 / *) from 表名
*：表示 全列查询
列名：表示 指定列查询

聚合函数 ： sum

sum的功能就是求和，很Excel里面的求和非常像！
将某一列的若干行数据进行相加。（NULL 值 不算）
== 语法格式： select sum(列名/表达式) from 表名;
avg、max 、min 这个聚合函数的用法都是一样的。（NULL 值 不算）

group by子句

select 中使用 group by 子句可以对指定列进行分组查询。需要满足：使用 group by 进行分组查
询时，select 指定的字段必须是“分组依据字段”(相同的字段为一组)，其他字段若想出现在select 中则必须包含在聚合函数中。

准备工作

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

having 操作 是月 group by 配套使用的。

针对分组之后，得到的结果。可以通过having 来进行制定条件。

group by 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 where 语句，而需要用having。
注意！上面红色句子的 “group by 子句进行分组以后” 的 “以后”两个字！！！
意思是如果数据先进行了分组之后，是不能使用where来制定条件的，只能是有having 来 制定条件。
反过来理解：在对数据进行分组之前，是可以使用 where 来制定条件的。

where 和 having的区别：
where 是在数据分组之前进行制定条件，来筛选数据。
having 是根据 分组之后的数据，进行指定条件，筛选每个分组中数据。

联合查询 - 多表查询 - 重点

联合查询：把多个表的记录 一起合并，一起进行查询。
联合查询又称 多表查询。

多表查询是整个SQL语句中最复杂的部分，也是笔试和面试中最常考的部分。
但是在实际开发中，一般是禁止使用多表查询的。【也就是面试造绿巨人，重拳出击，工作中就是班纳，静静摸鱼】
另外，可以告诉你们的是：大厂就喜欢这种调调，靠你一些很偏的知识点。【比如：算法】

想要理解 多表查询，就得必须知道一个重要的运算：笛卡尔积（多表查询中核心操作）。

像平面坐标系，就是笛卡尔提出的。笛卡尔就是一个知名的数据家，专门研究几何的。
笛卡尔积 也就是一个数学上运算。
笛卡尔积的计算其实非常简单，就是一个类似于排列组合。

接下来要讲的 多表查询 就是基于 笛卡尔积 的 基础上 进行的。

---

那么，如何在在SQL 中进行笛卡尔积(多表查询)操作呢？
下面我们就来执行一个最简单的多表查询（笛卡尔积运算）。
命令格式：select 列名，列名/ *  from 表名，表名…

现在我们熟悉笛卡尔积运算方式【最简单的多表查询】的基础上，我们就开始着手实现更为复杂的多表查询。

多表查询的更复杂使用情况

笛卡尔积这个操作，虽然执行效率不高，但是本身确实是一个功能挺好用的操作
可以借助笛卡尔积，来完成一些复杂的操作。

实例1：查询同学姓名 和 对应的班级名字

准备工作 - 上面那个还算简单的，下面才是重头戏

创建下面4张表：

复制拷贝到客户端里。（将前面的表都删除掉，只留鼓囊才我们创建的4个表）

四张表的数据情况如下：

四张表的结构如下：

在这个场景中，涉及到的主题，主要有三个：学生、班级、课程。
学生 和 班级 是一对多的关系【一个学生只能属于一个班级，一个班级可以包括多个学生】
学生 和 课程 是多对多的关系【一个学生可以选着多门课程，一门课程可以包含多个学生】
而处理多对多的关系，运用的是建立一张关系表，score 分数表就是这样的情况。
分数表“：表示一个学生选择了那门课，并且该门课的成绩是多少、

实战1：查询“许仙”的成绩

首先，“许仙”是一个学生的姓名。我们就需要在学生表中获取到学生的姓名 和 id

通过“许仙”的学生id，在分数表中获取到他的成绩

这里就需要针对学生表和分数表进行笛卡尔积，来确定许仙的分数。
【分析这样的问题，就需要先想清楚，要查询的数据都来自于那里】
下面我们先对分数表和学生表进行笛卡尔积。【下面的操作属于危险操作，这里只是示范。数据量还算少的】

其实也很好理解：学生有8行记录，分数表有20行记录，8 * 20 == 160
下面我们就需要进行筛选，选出我们所需要的数据。

拓展：join 关键字 实现 多表查询

上面的那种多表查询的写法是直接法 from 多张表。【推荐使用 offer 写法】
除此之外，还有一种写法：就是基于 join 这样的关键之，也能实现多表查询。
语法格式：select 列名… / * from 表名 join 表名 on 筛选条件;
【PS1：join 这些写法用的非常少，而且进行多张表查询时，写法会更加复杂（表的个数大于2）。后面我会举例子来展示一下效果】
【PS2：join 也有着它的作用，可以实现 from 写法实现不了的功能】

实战2：查询所有同学的总成绩

这个案例要在多表查询的基础上，再加上一个聚合查询。
对于这个操作，我们还是像上面那样，进行数据的筛选，筛选出有效数据。

此时，我们就可以通过这张表得知：每个同学的每个科目的额分数。
并且 每个同学的分数是按照行的方式来排列的。【行与行之间的运算，就会涉及到聚合查询】
首先，我们需要按照学号进行分组【将一个人的所有课程的成绩分为一组】。
即：使用 group by 进行分组。

实战3：查询所有同学的每门成绩

这里不仅要查询出同学的名字，还有课程名字，以及分数
这个时候就涉及到三张表的联合查询了。
【同学名字 》》 学生表，课程名字》》课程表，分数》》分数表】

内外连接（join关键字的特有的功能）- 拓展：join 多表的实现实例3

自连接

自连接：一个表，自己和自己进行笛卡尔积。
属于 sql 中的一种奇淫巧技。
虽然用的不多，只是用来处理一些特殊场景的问题。

那么，问题来了：什么时候需要使用自连接？

我们就需要了解自连接的本质：其实就是把 行与行之间的比较关系 转换成 列与列之间的比较关系

为什么要这样做呢？
这是因为 sql 指定条件，都是按照列与列之间进行制定的

但是呢！有一些特殊情况你是做不到的。

记住！自连接的关键所在就是能把行与行的关系转化成列与列之间的关系。
但是！自连接还是建立在笛卡尔积的基础上，所以执行的效率还是不高、

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
换个说法：把多个 select 合并成一个。
简单来说就是套娃！是一种非常反人类的操作，So Sick！
套娃的写法，就需要在人的脑子装很多东西。
人脑的内存空间是是非常小的，硬盘很大，CPU 对于 返点数计算非常弱，但是对于图像意识非常强。
人脑同时一时刻一共能维护几个变量。
据研究受过专业的训练的人大概是7个（想想递归）
Java编程中，经常谈到的“封装”，都是顺着人脑的特点来展开的。
封装的目的就是让人脑一次只关注一个点，不用考虑过多细节。
如果需要考虑细节，进入细节里面考虑，又不必考虑上层的逻辑。
java里面的一些代码太过复制的时候，就需要将其拆分成多个方法，多个类来实现。
子查询就是做着与Java 编程相反的事情：将拆分好的代码给合并成一个。
使得代码的阅读性大大降低。所以这不是一个推荐用的操作。放心以后在工作中，公司也不会让你用的。【面试可能会问。大概知道有个程序就行了】

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

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

[not] exists关键字 - 也是针对多行子查询的情况

对于 exists 关键字，我们只需要知道 它属于执行效率低，又难以理解的东西。记住这个就行了
有兴趣的，自己百度。

合并查询

合并多个select的执行结果.
可以使用集合操作符 union，union all。
【union 在 c中，表示一种叫做联合体。即：用一块内存表示不同内存的含义】
通过 union 把两个 sql 的查询结果合并到一起。
合并的前提是：两个 sql 查询的列 是 对应的。
【你可以理解为同一个表，或者 两个表的每一列的类型都是相对应的】

union 操作 会自动的进行去重。【保持数据的唯一性】
union all 是不会去重的。