《MySQL学习笔记》数据库增删查改（进阶）

曾经终败给现在 2024-04-23 15:35 71阅读 0赞

**目录**

一、数据库的约束

?NOT NULL约束

?UNIQUE(唯一约束）

?DEFAULT：默认值约束

?PRIMARY KEY：主键约束

?FOREIGN KEY：外键约束

?由外键约束而延伸出来的问题——电商

?check约束（可以直接对表中的值进行限制） （了解）

二、表的设计

三、新增

四、聚合查询

?聚合函数（不区分大小写）

?分组查询（GROUP BY）

五、联合查询

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

## 一、数据库的约束 ##

> 约束类型（以下不区分大小写）
> 
>  *  **NOT NULL - 指示某列不能存储 NULL 值。**
>  *  **UNIQUE - 保证某列的每行必须有唯一的值。**
>  *  **DEFAULT - 规定没有给列赋值时的默认值。**
>  *  
>  *  **PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录**
>  *  **FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。**
>  *  **CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句**  
>

### **?NOT NULL约束** ###

**初始情况下，一个表允许为NULL**

![9e7ecc5afa9948a7964747359c44c90e.png][]

**如果我们在创建表的时候，指定某指定某列不为空，则该表的这列的值不能为空——即插入空值就会报错**

![ccdf56bb083f4eeda793374f4149a594.png][]

### ?UNIQUE(唯一约束） ###

> 默认情况下，表里的数据是可以重复的，当如果该列的数据被unique修饰，则该列的数据不能重复，如果插入重复的数据就会报错

![265d30e929e94c03b2d3c104660b52d8.png][]

### **?DEFAULT：默认值约束** ###

> 当某一列的值为空时，默认的默认值为NULL，但我们可以通过default约束来修改这里的默认值的取值

![7c0a9bbe56954fcba9a1c56ceaa265db.png][]

### **?PRIMARY KEY：主键约束**    ###

> 首先我们要知道 primary key 主键，这是我们在设计表的时候，一个非常重要的列
> 
> 主键表示一条记录的身份标识，用来区分这条记录和别的记录
> 
> 他有以下特点
> 
> **1）不能为空， 相当于 NOT NULL**
> 
> **2）不能重复， 相当于 UNIQUE**
> 
> **3) 一个表中只能有一个主键**

**?栗子**

![616ff0aa4d344e0e9a53c1177cade270.png][]

**~~~我是分割线~~~~**

![6e012a1e2d834eb2b8354d6e341dd4be.png][]

**?栗子**

**由于主键必须自己填，还不能重复，MySQL为了方便大家填写主键，内置了一个功能——自动主键（从1开始依次进行累加），帮助我们自动生成主键的值。**

> \-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
> 
>   
> **id int primary key auto\_increment**

![fdad28250945486388c0884463640074.png][]

### ?**FOREIGN KEY：外键约束** ###

外键用于关联其他表的主键或唯一主键，它描述了两张表之间的关联关系

**其中负责约束的一方，称为父表。被约束的一方，称为子表。**

> **foreign key (字段名) references 父表(列);** 
> 
> **其中foreign key(字段名) 填写的是本表(子表)的外键， references 父表(列) 填写的是其他表的主键**

比如我们要表示学生表和班级表的对应关系，那么班级编号就是一个关键点

一般来说，都是负责约束的一方（父表）先进行构造，再来构建约束的一方（子表），想想看是先有了约束条件才能被约束

![f43774d2290a4a628d984a875495f13f.png][]

> 眼尖的伙伴可能会发现父表和子表好像都有一个classId，不是说列名不能重复吗？
> 
> **在同一张表内，列名的确不能重复，但这是在两个不同的表中呀！在这里我们学生表中的classId是外键，关联了班级表的主键classId。**

那两个表产生关联和约束，有什么用呢？

?**首先当你在被约束表（子表）中插入数据的时候，在外键约束下，每次你进行插入或修改操作的时候，都会触发在负责约束的父表中的查询。只有该外键在父表中存在时才能插入或修改成功，不信你看~~~**

如果先该父表中创建了子表中该外键所对应的关联主键，就可以插入了

![1ed705cf30494be7be91350f756a424f.png][]

>  **父表对子表的限制是不能随意的插入和修改，就比如说人家父表中根本没有这个班级，那你在子表中还怎么插入到这个班级中。**
> 
> 同样的负责约束的父表也会收到被约束的子表的限制，子表对父表的限制是不能随意的修改和删除，就比如说人家子表中的学生都在这个班级中了，你在父表中突然把这个班级的编号给改了、甚至直接给删除了，这合理吗？

![f686a990980b4cd6885e7ee5a39f416d.png][]

**?注意**

**外键约束要搭配索引，我们上面的是主键，主键自动带索引**

![0c1bf11a27dc4152bc2e1247b390ac78.png][]

### **?由外键约束而延伸出来的问题——电商** ###

> **商品表（good\_id, name, price)**
> 
> **订单表（order\_id, time, good\_id)**
> 
> 这里面其实也存在着外键约束，商品表对订单表就有着约束。其中商品表是父表，订单表是子表。你不能订商品表中没有的商品（good\_id），商品表中的商品也不能随意的下架（可能人家还在订购你这个商品呢）

?那么想象一下这样一个情景：在一家店铺中，你下过了很多订单，订单表中已经有了一些商品数据，比如你买了一堆衣服，衣服的good\_id是100。然后过了一段时间，要换季了，需要之前的商品给下架。**在上述外键约束的条件下，如何删除商品表中100的记录。**

> 我们可以进行假的删除===》逻辑上的删除（物理上还存在）
> 
> 可以在商品表中设置一个判断商品是否下架的字段
> 
> **商品表（good\_id, name, price, ok);**
> 
> **约定ok为1的时候表示商品在线**
> 
> **约定ok为0的时候表示商品下架**

### **?check约束（可以直接对表中的值进行限制）** **（了解）**  ###

![151b828a8faf440a8249d2982d26ba7b.png][]

## 二、表的设计 ##

> 在工作中，我们通常会根据一些时间的业务场景，来设计表——主要就是确定有几个表，每个表干什么，每个表有哪几个字段
> 
> **比如当面试官问你：你的项目里的数据库是如何设计的？**
> 
> **其实面试官是想让你回答，你的数据库里有哪几个表，每个表是干什么的，以及表中有哪些字段**

在需求中，我们要学会提取出需求中的实体，实体可以认为是："关键性的名词"，一般来说每个实体都对应着一张表，我们要看到实体与实体间的对应关系——即表与表之间的对应关系

这些关系可以分为：一对一、一对多、多对多、没关系

![319ea625ea074c3d90d28fdbecb2371a.jpeg][]

**一对一**

![52a24751be5e41ff807ba29ca23190e9.png][]

**一对多（和 多对一 一个意思）**

![28e926d458c741219d8aa326f54db2ec.png][]

**多对多**

![a5143bc261ff4203a26bc4d8298c3f92.png][]

**总结**

![505a7c52c7964d69a35daea60df610ae.png][]

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

## 三、新增 ##

把查询结果作为新增的数据

> **insert into 表名 (列名, 列名....) select 列名 from 表名;**
> 
> **insert into 表一 select \* from 表二;**
> 
> **insert into 表一 select \* from 表二 where 条件**

> 先执行查询查询操作，查询出来的结果，插入到另外一个表里
> 
> 这里需要注意的是：**我们要保证查询结果的临时表的列和要插入的表的列要匹配上**

**?栗子**

![a22e8984894747a089643673161f8d71.png][]

**?栗子**

![3ed97dd3ae3d48f78021e42c0843f4d3.png][]

?栗子

![307340ed2e3f48a99226a1c3cc9f467b.png][]

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

## 四、聚合查询 ##

###    **?聚合函数（不区分大小写）** ###

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

<table> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>COUNT([DISTINCT] expr)</td> 
   <td>返回查询到的数据的 数量</td> 
  </tr> 
  <tr> 
   <td>SUM([DISTINCT] expr)</td> 
   <td>返回查询到的数据的 总和，不是数字没有意义</td> 
  </tr> 
  <tr> 
   <td>AVG([DISTINCT] expr)</td> 
   <td>返回查询到的数据的 平均值，不是数字没有意义</td> 
  </tr> 
  <tr> 
   <td>MAX([DISTINCT] expr)</td> 
   <td>返回查询到的数据的 最大值，不是数字没有意义</td> 
  </tr> 
  <tr> 
   <td>MIN([DISTINCT] expr)</td> 
   <td>返回查询到的数据的 最小值，如果不是数字就没有意义</td> 
  </tr> 
 </tbody> 
</table>

**?栗子**

![8aa46d8f5b314604aa9535bbb442659a.png][]

### ?分组查询（GROUP BY）    ###

> 把表格的若干行，分成好几组~~，指定某一列作为分组的依据。在分组时，如果该列的值相同，则被归为一组。
> 
> 分成多个组之后，还可以针对每个组，分别使用聚合函数

![fe1e2a9813d446269c7615ee51bc119e.png][]

> 在进行聚合查询的时候，也能指定条件进行筛选
> 
> 1）在聚合之前，进行筛选——即先筛选，再聚合==》where 子句
> 
> 2）再聚合之后，进行筛选——即先聚合，再筛选==》having 子句

**?栗子**

![01363d4db1b44717a551fc91aba002d0.png][]

上述的过程就是

> **1、先按照where的条件进行筛选**
> 
> **2、把筛选出来的结果按照group by来分组**
> 
> **3、最后调用聚合函数avg、max**

**?栗子**

![1ba166ff6fd847b0bb10f6e38120a841.png][]

**?栗子**

同时再前面和后面进行筛选（查询平均分大于80的情况）去除张三

![308d6fee20a04621aab582298ca28ee6.png][]

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

## 五、联合查询 ##

> 联合查询也叫多表查询，把多个表的记录合并到一起，综合进行查询~~~
> 
> 联合查询的核心概念——笛卡儿积（把要查询的各个表中的记录进行排列组合，穷举出所有可能的情况~~~

![4cdf05aa48e94ba6be780a0ff7037a2d.png][]

![0bf02187303e4167a5cf4fb65e7453b8.jpeg][]

> **sql中计算笛卡儿积**
> 
> **select \* from 表1，表2;**

![91777443553246ad8339fad298a73223.png][]

**?栗子**

**比如你现在有以下几张表，你想要查询许仙同学的成绩**

**许仙是名字，student表中有名字**

**成绩是分数，score表中有分数**

![0536d081f4de43758f744478c52d1745.png][]

**第一步：建立计算产生关联的这两个表的笛卡儿积**

![b5fec85e85354663a6b11c874d0bc4a3.png][]

**第二步：初步筛选**

![b1ebd107899b4eb290ca3a7992520d92.png][]

**在筛选的时候，我们要注意多张表的列名，很可能是一样的！！可以通过：表名.列名的方式来做区分**

![2e159b1c94cc4df4ac9b1868a3b9117d.png][]

**第三步：按名字进行进一步的筛选**

> 当我们初步过滤掉无效数据后，剩下的数据已经清晰多了~~每个同学，每门课的成绩~~
> 
> 接下来，在对名字进行筛选，我们要的是许仙同学的成绩

![2fc1cc98c430474f8b3d9ba81ba0ff1e.png][]

第四步：省略不必要的列，只保留必要的列

![7da40482275742b59f654301df4553e8.png][]

**总结**

![f78c5dffba144679a8d977e4a802d147.jpeg][]

[9e7ecc5afa9948a7964747359c44c90e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/45e551737fea460e905a29241fbcfa0f.png
[ccdf56bb083f4eeda793374f4149a594.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/84b658716d4548799eb3d5971d82f39b.png
[265d30e929e94c03b2d3c104660b52d8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/2901a049bef44e3cb214a463a7d62654.png
[7c0a9bbe56954fcba9a1c56ceaa265db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/4ab319942b764ce886fe36c31b5f02be.png
[616ff0aa4d344e0e9a53c1177cade270.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/0d3cf451011b474ab20fda7d536720d9.png
[6e012a1e2d834eb2b8354d6e341dd4be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/6a7390a3f4c54a438c4ba3dbff0d2cd9.png
[fdad28250945486388c0884463640074.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/7b62680e5a4d423ba15d109ded0c4ef2.png
[f43774d2290a4a628d984a875495f13f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/d233e4fcdb2c404f8978b66dbe7701e0.png
[1ed705cf30494be7be91350f756a424f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/ca906cdccd45428592ea51ca41f64235.png
[f686a990980b4cd6885e7ee5a39f416d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/d4a87fb5ee0e43f1a722159e35e073a6.png
[0c1bf11a27dc4152bc2e1247b390ac78.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/4140754ade0d4c8fb3427ad1d020e0a2.png
[151b828a8faf440a8249d2982d26ba7b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/759443c9d9674026a635d2a6f206b8c0.png
[319ea625ea074c3d90d28fdbecb2371a.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/24f6d746cb9d4802bb4680e1f34ebe58.jpeg
[52a24751be5e41ff807ba29ca23190e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/55151fd65c7c4f2fbed5a30949cb6289.png
[28e926d458c741219d8aa326f54db2ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/1dcf4002296f41a8b58ade86bf82378c.png
[a5143bc261ff4203a26bc4d8298c3f92.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/b7b2596386e74a90991271776006ddde.png
[505a7c52c7964d69a35daea60df610ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/6c051ea1fa3d4d359f0244e8bea1da74.png
[a22e8984894747a089643673161f8d71.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/16c6ac18766f435ca4660f715b4c6c9c.png
[3ed97dd3ae3d48f78021e42c0843f4d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/059ca5fc36734f02ae761e5b8a3a897e.png
[307340ed2e3f48a99226a1c3cc9f467b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/45d99551ee5b45f8af9766177d3b985e.png
[8aa46d8f5b314604aa9535bbb442659a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/9f4b11737bdf41f3a959af9826a69b15.png
[fe1e2a9813d446269c7615ee51bc119e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/e04e0f9295064575a46d70afb5fe7183.png
[01363d4db1b44717a551fc91aba002d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/ed40d95eebb240979c2898cad4bce23e.png
[1ba166ff6fd847b0bb10f6e38120a841.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/c89b162e708147ba9f27d04ff2af923c.png
[308d6fee20a04621aab582298ca28ee6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/0610a38ceebf4d299e977e29a2c1cb4a.png
[4cdf05aa48e94ba6be780a0ff7037a2d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/8ae285d24d4f4956a39951ab0dbc6500.png
[0bf02187303e4167a5cf4fb65e7453b8.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/58dfba3701174e75804d096bc4ecdfac.jpeg
[91777443553246ad8339fad298a73223.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/dd21931e73df4445978cb93b0ec44dd9.png
[0536d081f4de43758f744478c52d1745.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/f6f4460b9e5843e88a5a5b51175bffb4.png
[b5fec85e85354663a6b11c874d0bc4a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/9a4dbc3b521e4898a3ff337b0203c343.png
[b1ebd107899b4eb290ca3a7992520d92.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/60f380fd967c4827a4fdcf0870d4698b.png
[2e159b1c94cc4df4ac9b1868a3b9117d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/ab37c06e37b34480bd317db6b470b5f9.png
[2fc1cc98c430474f8b3d9ba81ba0ff1e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/221a88f793b04f0a9720c08d611bab62.png
[7da40482275742b59f654301df4553e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/5d63f9ca25054d2aa2d19039767bf538.png
[f78c5dffba144679a8d977e4a802d147.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/a8d1769e6db14d099936051bc8490561.jpeg