MySQl表的增删查改（聚合查询+联合查询）

野性酷女 2024-04-20 07:12 94阅读 0赞

**目录**

1. 数据库约束

1.1 null约束

1.2 unique：唯一约束

1.3 default：默认值约束

1.4 primary key：主键约束

1.5 foreign key：外键约束

1.6 check约束（了解）

2. 表的设计

3. 新增(查询结果，插入另一个表)

4. 查询

4.1 聚合查询

4.1.1 聚合函数

4.1.2 group by 子句

4.1.3 having

4.2 联合查询

4.2.1 内连接（常用）

4.2.2 外连接(左外+右外+全外)

4.2.3 自连接

4.2.4 子查询

4.2.5 合并查询(union)

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

## **1. 数据库约束** ##

> 约束：对于数据库中的记录做出更详细的检查
> 
> **（1）not null：**指示**某列不能存储null值**
> 
> **（2）unique：唯一约束**
> 
> 保证某列的每行必须有唯一值
> 
> **（3）default：默认值约束**
> 
> 规定没有给列赋值时的默认值
> 
> **（4）primary key：主键约束**
> 
> 确保某列（或多个列的结合）有唯一的标识
> 
> 有助于更容易快速找到表中的一个特定的记录
> 
> **（5）foreign key：外键约束**
> 
> 保证一个表中的数据匹配另一表中的值的参照完整性
> 
> （6）check：保证列中的值符合指定的条件

### **1.1 null约束** ###

> **not null 要求指定列，非空**
> 
> 创建表时如果约束这列非空，那么新增时，这列就不可以为空

![359e4987f0ad45d4aa40d524e532e989.png][]

### **1.2 unique：唯一约束** ###

> **unique 值唯一，每次插入、修改数据，都会针对当前数据在表里进行查找，看这个值是否能查到，如果能查到，那么不是唯一的，就会导致插入或修改失败**
> 
> **如果使用unique后，先查询，再插入！！！ 所以效率比较低**

![6485b6fbaf634e0da17ba14f31e7110c.png][]

entry 在数据结构出现过，Map时entry叫做入口、条目

Map没有实现Iterable接口，没法直接遍历，所以把键值对转换成一个Set ，entrySet

### **1.3 default：默认值约束** ###

> **default：作用是修改默认值**
> 
> **本来表的默认值是null，可以使用 default 把默认值修改成其他值**

![117038fe14664eee82d0c536d5ac6520.png][]

### **1.4 primary key：主键约束**  ###

> **primary key 主键 针对每一天记录，作为身份标识**
> 
> **主键要保证唯一性，主键也不能为空**

![773a800b425f4d14a2c907332e98fc43.png][]

![174ef9c4eebf47a7869695a4a6c28516.png][]

**? 使用主键，需要给这个列设置一个唯一的值，我们自己进行分配值的工作，并且还要保证值不会重复，这样会比较麻烦，MySQL提供了一个机制，叫自增主键，来方便分配值**

> **自增主键 跟在primary key 后面 （primary key auto\_increment） ，**
> 
> **通过这样的机制，来给主键设置值**

![9a9d8b6ba9084f75ad9cafe9ffd11544.png][]![4f399b062fed437d9e8901428851034d.png][]

>  **自增主键，都是从最大值之后进行增加的**
> 
> （虽然这样可能会有空间上的浪费，但从大量数据看，这点浪费不影响）

![384ad63669fe4ba48922a03882e05d83.png][]

### **1.5 foreign key：外键约束** ###

> **外键 foreign key：将两种表联系起来**
> 
> **........foreign key (当前表的那一列) references 另外一张表(那一列);**
> 
> ![795a6f819ede4814988246d21667e601.png][]
> 
> 约束别的表的表，叫父表（parent，例子中的班级表）
> 
> 被约束的表，叫子表（child，例子中的学生表）

**（1）创建班级表，创建外键约束的学生表**

![9546032ae39c41d9a1d332e110b8548e.png][]

**（2）给学生表插入数据，失败**

![b77b66e3c91d48f3a0d762fa5f0d4f97.png][]

**（3）给班级表插入数据，然后再给学生表插入数据，成功**

![c2ed316e173a4b659c9b424639ecaf69.png][]

>  **这里也要求，子表中引用父表的这一列，必须是primary key 或者 unique**

**（4）删除或修改父表的数据，失败**

![8d81a8139bf546238e4845b29edc1226.png][]

> **修改或删除记录也要看当前记录是否被子表引用，如果引用不能随便修改删除，**
> 
> **如果没有引用就可以修改或删除**

### **1.6 check约束（了解）** ###

> **check 不支持mysql**
> 
> 通过check中的表达式，对某些要求可以更加灵活实现

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

## **2. 表的设计** ##

> **（1）一对一**

**比如网上银行系统 ，一个客户，只有一个账号；一个账号也对应一个账号**

设计方式可以有多种

**1）把所有信息放入一个表中**

customer-account（身份证号，姓名，手机号，密码，用户名）

**2）在客户表中，加上一个账号id**

customer（customerId，姓名，手机号，身份证号，**accountId**）

**account**（accountId，用户名，密码）

**3）反之，也可以在账号表中加客户id**

**customer**（customerId，姓名，手机号，身份证号）

account（accountId，用户名，密码，**customerId**）

> **（2）一对多**

**一个银行经理，可以专属多个客户**

**一个客户，只能属于一个银行经理**

**1）设计一个客户表，一个银行经理表，客户表加银行经理id进行联系**

![14de7d9488ce44d5858dc20a28735c8a.png][]

**2）设计一个客户表，一个银行经理表，银行经理表加客户id进行联系（****此方法行不通****）**

![685ae5654dac4859bfe0e9eb3cc39468.png][]

**但是需要注意的是第二中这样的写法是不行的，因为customerld这里每行放多个，**

**在MySQL中是没有数组类型的，所以不能这样写**

> **（3）多对多**

**比如 一个客户，可以购买多个理财产品**

**一个理财产品，也可以由多个客户购买**

**1）创建一个客户表，一个理财产品表，一个关联表**

![e189c6a91bda474b9a1ec0c5eb5e9834.png][]

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

## **3. 新增(查询结果，插入另一个表)** ##

> **可以把查询的结果，插入到另一个另一个表中**
> 
> **create table 表2 第一个表查询结果;**
> 
> **(也可以插入部分，通过条件筛选，但是注意列的个数和类型要匹配)**

![aaded0047a00439dadde5f6cce75890f.png][]

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

## **4. 查询** ##

### **4.1 聚合查询** ###

> **聚合查询，是把行和行放在一起进行计算**

#### **4.1.1 聚合函数** ####

<table style="width:650px;"> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>count()</th> 
   <td>返回查询到的数据的&nbsp; 数量（单查行不计空值，查全部*计空值）</td> 
  </tr> 
  <tr> 
   <th>sum()</th> 
   <td>返回查询到的数据的 总和，不是数字没有意义</td> 
  </tr> 
  <tr> 
   <th>avg()</th> 
   <td>返回查询到的数据的 平均值</td> 
  </tr> 
  <tr> 
   <th>max()</th> 
   <td>返回查询到的数据的 最大值</td> 
  </tr> 
  <tr> 
   <th> <p>min()</p> </th> 
   <td>返回查询到的数据的 最小值</td> 
  </tr> 
 </tbody> 
</table>

####  ####

下面看两个例子![b7bf583f8ebf44adb7bc890abd6d80bd.png][]

#### **4.1.2 group by 子句** ####

> **group by子句：可以对指定列进行分组查询，指定的字段必须是‘’分组依据字段‘’，其他字段若想出现必须包含在聚合函数中**

**创建建一个emp表**

![4efe611f167d496fb195351823ed9743.png][]

**（1）查询每个岗位的人数**

**思路 先查询都有什么岗位，也就是对岗位role进行分组，然后查询每个岗位的人数**

![b296312d40294af7a70b5811a796bc69.png][]

**（2）查询每个岗位的平均薪资**

**思路 先查有哪些岗位，也就是对岗位进行分组，然后再对每种岗位求平均工资**

**求最高工资，和最低工资**

![0c49cf5839a1489b9b44b71d15f1ff5e.png][]

![d5ab0dd8b5094d3b92589b9f77d198f3.png][]

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

#### **4.1.3 having** ####

> **group by 子句进行分组后，****如果要对分组结果进行条件筛选时，不可以使用where语句，而要使用having**
> 
> **分组查询，还可以指定条件，需要区别的是**
> 
> **分组之前，使用条件筛选； 使用where**
> 
> **分组之后，进行条件筛选； 使用having**

**（1）查询每个岗位的平均薪资（除去 张三 这个记录）**

**思路 先除去张三，再进行分组（也就是分组之前，进行条件筛选）**

![b389b7f48fa8462d955ee6c913957862.png][]

**（2）查询平均薪资 > 10000的岗位**

**先分组，计算平均薪资，然后再找到平均薪资大于10000的岗位**

**（也就是先分组，再进行条件筛选）**

![c095f803aabe48ebb36a124f3edce17d.png][]

**（3）求除去张三之外，每个岗位的平均薪资，并且保留平均薪资 > 10000的岗位**

![05627279bbff47cd80c354a2002bc734.png][]

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

### **4.2 联合查询** ###

> 实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
> 
> **笛卡尔积，是两个表记录，排列组合的结果**

笛卡尔的行数是一个相乘的关系，如果原来行数很多，相乘后行数就会更多

在进行联合查询的过程，就是在进行计算笛卡尔积的过程

当表比较大时，如果多表查询，机会比较低效，很可能会是危险操作

**笛卡尔积的列数，是两个表的列数之和**

**笛卡尔积的行数，是两个表的行数之积**

**联合查询/多表查询 = 笛卡尔积 + 连接条件 + 其他条件（根据其他的需求）**

**联合查询：相当于把数据先变多，再变少的过程**

创建四张表

![306920bf082a43cc818cb35d8266e7a0.png][]

#### **4.2.1 内连接（常用）** ####

> **select 字段 from 表1 join 表2 on 连接条件 and 其他条件;**
> 
> **select 字段 from 表1,表2 where 连接条件 and 其他条件**

> 内连接，方法四步走
> 
> **（1）进行笛卡尔积**
> 
> select \* from 表1,表2;
> 
> **（2）添加连接条件，去除无用信息**
> 
> select \* form 表1,表2 where 连接条件
> 
> **（3）添加其他条件，进行信息筛选**
> 
> select \* form 表1,表2 where 连接条件 and 其他条件;
> 
> **（4）精简列，去除无用列**
> 
> select 字段 form 表1,表2 where 连接条件 and 其他条件;

**（1）查询“许仙”同学的成绩**

**学生表中查名字，分数表中查成绩**

**思路1：根据许仙同学的名字，找到学号，再根据学号找到对应的分数**

**思路2：学生表和分数表笛卡尔积，根据条件进行筛选**

**使用where做**

![49ee97bf36f848b5be8c9b930acd2b4a.png][]

**使用join...on...做**

![290d0d982e9741028a68ec2e74cff0ad.png][]

**（2）查询所有同学的总成绩，及同学的个人信息**

**思路 按照同学身份进行分组，然后依次每次求和**

![c6053dce6b664f088c5b69ff6c63c839.png][]

**（3）查询所有同学的成绩，及同学的个人信息**

**查询出每个同学的成绩，带有课程信息**

**同学名在学生表，课程名在课程表，分数在分数表中**

此时就要三张表的笛卡尔积

**使用where**

![6207e923e83248f89f64f15f3109293b.png][]

**使用 join...on...**

![79ad26acfac34c81b16f47668f352d52.png][]

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

#### **4.2.2 外连接(左外+右外+全外)** ####

> 外连接反为左外连接和右外连接
> 
> （1） 左外连接，表1完全显示
> 
> **select 字段 from 表名1 left join 表名2 on 连接条件;** 
> 
> （2）右外连接，表2完全显示
> 
> **select 字段 from 表名1 right join 表名2 on 连接条件;**

![caad8bf23c934a9884611ab63a9dfa19.png][]下面创建下面这两个信息不一一对应的两个表

![926f1441638a41bfa8e516def7f4a29d.png][] **看一下内外连接的区别**

![0d1b632cae524e8dbeaad9b068cb90f6.png][]

> 如果两张表，完全重合，内外连接没有区别
> 
> 如果两张表，只有一部分重合，**内连接就是获取到‘交集’；**
> 
> ![1b0a401953b6443c815eea99b7dc6e40.png][]

![1f2ccbbe144d41febfd87a6d8579999e.png][]

>  **左外连接和右外连接，主要是看表的先后顺序**
> 
> **在join左侧还是右侧**
> 
> select \* from **student** left join score； 左外连接 student
> 
> select \* from score right join **student**； 右外连接 student

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

#### **4.2.3 自连接** ####

> 自连接是指在同一张表连接自身进行查询(**自己和自己进行笛卡尔积**)
> 
> **思路 ‘‘行’’转为‘’列‘’**

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

![87000d76d93c47d08cf53591757d84e0.png][]

下面写一下，自连接的过程分析

![98d930a6e1ff4e79a874ac0d0f4529c7.png][]

> **自连接的代价**
> 
> **1.运行的开销**
> 
> **2.可读性的成本**

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

#### **4.2.4 子查询** ####

> 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
> 
> **本质就是把多个查询语句，给组合成一个查询语句**
> 
> **用一个查询的结果的临时表，基于这个临时表再发起另一组查询**

**（1）单行子查询 返回一行记录的子查询**

**查询与“不想毕业”同学的同班同学**

![3ce1c88f57a341c3a12a46941c092e85.png][]

> 子查询可以理解为套娃，一层套一层，但如果套娃太复杂，就会非常影响理解

**（2）多行子查询 返回多行记录的子查询**

> **如果子查询，返回的结构是多条记录，就可以使用in来进行子查询**

**查询“语文”或“英文”课程的成绩信息**

![3b0c53485ba74e7999a0d85bcfc9dd1f.png][]

> 多行子查询 除了可以使用in之外，
> 
> 也可以使用exists ，**exists写法，执行效率和可读性都远低于in写法**
> 
> 但是在特殊情况下，exists可以带头in
> 
> **in的子查询结构是保存在内存中，**
> 
> **如果子查询结果集特别大，内存放不下，就需要使用exists**

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

#### **4.2.5 合并查询(union)** ####

> **使用union或者union all 来完成把多个查询的结果集合，合并到一起** 
> 
>  **union 在合并时，如果合并时有重复数据，去重**
> 
>  **union all 在合并时，如果合并时有重复的数据，不去重**

**查询id小于3，或者名字为“英文”的课程**

![59edc7ac7a2440b99bc37c2187c716c2.png][]

> **union 可以 将不同的表合并在一块查询**
> 
> **or 只能在同一张表中查询**

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

[359e4987f0ad45d4aa40d524e532e989.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/d997eded10a64f1daf34907de4b191a1.png
[6485b6fbaf634e0da17ba14f31e7110c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/81007135665340abbf5378970ac5cec9.png
[117038fe14664eee82d0c536d5ac6520.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/d8ab0c503d4e4da48aaa0a74e01ddbbe.png
[773a800b425f4d14a2c907332e98fc43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/4aca1944abae4483899002995e4da609.png
[174ef9c4eebf47a7869695a4a6c28516.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/855c3acda159458c9eaef964d03314da.png
[9a9d8b6ba9084f75ad9cafe9ffd11544.png]: https://img-blog.csdnimg.cn/9a9d8b6ba9084f75ad9cafe9ffd11544.png
[4f399b062fed437d9e8901428851034d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f75384fbe25e4461b85c1b01c1fbe95a.png
[384ad63669fe4ba48922a03882e05d83.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e2f5c7a496f04001835ffeb48d1612bd.png
[795a6f819ede4814988246d21667e601.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/5576ba186632457d84500c9852973965.png
[9546032ae39c41d9a1d332e110b8548e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f732c2442cea40de95900be1887a3f11.png
[b77b66e3c91d48f3a0d762fa5f0d4f97.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f1ded9c73fec424ebd58bf4908c92ff1.png
[c2ed316e173a4b659c9b424639ecaf69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/611d3cccbf194e1b99af37e94c638130.png
[8d81a8139bf546238e4845b29edc1226.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/cb347d6fe5c445fb97f0791a530f6f3e.png
[14de7d9488ce44d5858dc20a28735c8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/3b51620fb3844ef8a9ffed0ae5fc043f.png
[685ae5654dac4859bfe0e9eb3cc39468.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/2681cfed714041f8ae0629531f0b32c4.png
[e189c6a91bda474b9a1ec0c5eb5e9834.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/3afc16dde600458284983ada8353df59.png
[aaded0047a00439dadde5f6cce75890f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/aad5a2257dfc4cf08084e00b55d01f54.png
[b7bf583f8ebf44adb7bc890abd6d80bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e2803367aa90463ab822e94e049e2035.png
[4efe611f167d496fb195351823ed9743.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/7b1ac498d9ae4847806d334f06760f57.png
[b296312d40294af7a70b5811a796bc69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/2a4cbe16cdbf4a32be0f50cdbac4e727.png
[0c49cf5839a1489b9b44b71d15f1ff5e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/50079bcfac7845afb97b56f4ef3c3d70.png
[d5ab0dd8b5094d3b92589b9f77d198f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/4190838f255148f0914ad758ae90f7d3.png
[b389b7f48fa8462d955ee6c913957862.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/338e5f1d20394546b25b2df55560ba47.png
[c095f803aabe48ebb36a124f3edce17d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/45e9999c162341a9a8ffe316c3837d70.png
[05627279bbff47cd80c354a2002bc734.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/3818c13535734026963c545d45f737ac.png
[306920bf082a43cc818cb35d8266e7a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e3457f76a56e4ab98fe962291e17d10f.png
[49ee97bf36f848b5be8c9b930acd2b4a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f44c7948e71f45fe8563bef4a9d6c90e.png
[290d0d982e9741028a68ec2e74cff0ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/97c730168805454f89916dae06bdeb22.png
[c6053dce6b664f088c5b69ff6c63c839.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/6370158d39014111be71deb71272bbb4.png
[6207e923e83248f89f64f15f3109293b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/960741787c2949e2871d4b5f80d14f24.png
[79ad26acfac34c81b16f47668f352d52.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/5e7681c3cb4b4c44a93ed366b2c7addb.png
[caad8bf23c934a9884611ab63a9dfa19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/362bbb7ee1634daa939f5c1813b54d79.png
[926f1441638a41bfa8e516def7f4a29d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/7d4e09dac84149269b25a46a08ecd81a.png
[0d1b632cae524e8dbeaad9b068cb90f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/2debad084a024a738dee12c52d6640e2.png
[1b0a401953b6443c815eea99b7dc6e40.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/7b3b6d0d7a5840068054fd91e563b9f0.png
[1f2ccbbe144d41febfd87a6d8579999e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/551a8fd861a24098b34e7618ef28582a.png
[87000d76d93c47d08cf53591757d84e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/68d335664156463cb3e160e8e93f3d14.png
[98d930a6e1ff4e79a874ac0d0f4529c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/0450ae115f194c2692834a34af19ec69.png
[3ce1c88f57a341c3a12a46941c092e85.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e2ee3bd481fd468a8c58b45fbf63b489.png
[3b0c53485ba74e7999a0d85bcfc9dd1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/877f57c8544e493db91dfbc8b500140c.png
[59edc7ac7a2440b99bc37c2187c716c2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/9aa2801066e74ab191a7b5cdb7a8fd5b.png