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

红太狼 2023-10-14 00:16 47阅读 0赞

![d476f781800f4ea18c352de178f73766.jpeg][]

**目录**

一、表的设计

1、一对一

2、一对多

3、多对多

二、新增

三、查询

1、聚合查询

（1）聚合函数：

（2） group by 子句

（3）having

2、联合查询

(1)内连接

(2)外连接

(3)自链接

(4)子查询

(5)合并查询

四、总结

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

## 一、表的设计 ##

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

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

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

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

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

### 1、一对一 ###

![bcdd2b7fddb04adca8bc0122d546aa68.png][]

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

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

![9a32afccf14b45e8adacc0bef3d76203.png][]

这三种方法 都是可以的

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

### 2、一对多 ###

![58d0b3df2ba44895ae8581eb15dc61de.png][]

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

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

![6f6054abb0954e43b537f2f2c3002b52.png][]

这种设计方式，并不推荐

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

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

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

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

![04c3e89bb7c344f389032d8cd8f691b1.png][]

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

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

### 3、多对多 ###

![e631555c70544f299649b9b316062c77.png][]

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

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

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

![9ec6ab6742e945e3b1b5e61a927c35a1.png][]

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

## 二、新增 ##

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

**语法：**

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

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

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

并向 student 中插入三条数据：

![167a940b3cc34e9baa75db33350101c1.png][]

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

![c57a3303a97e4cbda6dd34846193948f.png][]

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

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

## 三、查询 ##

### 1、聚合查询 ###

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

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

#### **（1）聚合函数：** ####

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

![65597569f8d44b5caf48e2dfff7b457d.png][]

使用示例：

-- 统计数学成绩总分
    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 语句 是指定一个列，按照这个列进行分组，这一列中数值相同的行，会被放到一组**

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

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

![9caeeaf6545d4d5e918e1c761869a298.png][]

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

![300540be78854091b54d5a590a96fdca.png][]

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

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

#### （3）having ####

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

**1、分组之前的条件： where**

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

![7a79b22965ba4c0ab4e1963eeb76270f.png][]

**2、分组之后的条件 : having**

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

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

![a6649d1f08594a55ae2f3d629206cade.png][]

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

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

![668c5fdde55a4b34846a7987ba03db91.png][]

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

### 2、联合查询 ###

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

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

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

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

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

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

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

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

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

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

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

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

#### (1)内连接 ####

**语法：**

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

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

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

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

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

![d14c558fdd404226ad718a43ef1ab6a4.png][]

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

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

![a60bbb9f70b04cdc91924a46b391605b.png][]

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

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

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

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

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

> （1）我们先把 score 表和 student 表进行笛卡尔积
> 
> （2）指定链接条件 此处是按照 学生id 进行筛选
> 
> （3）根据需求，进一步的添加条件 此处按照名字为 “ 许仙 ”再来进行筛选
> 
> （4）针对查询的结果的列，进行精简

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

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

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

> (1) 两个表进行笛卡尔积
> 
> (2) 指定链接条件
> 
> (3) 这里要求的是每个学生的总成绩，就需要按照学生维度进行 group by
> 
> (4) 需要搭配聚合函数，针对分数进行进一步计算

![4429645d7bb945fe980d682b4ff51261.png][] **示例：列出同学的名字，课程名字，分数**

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

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

![6ec119055a9145ffad20d848f6808f80.png][]

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

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

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

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

> （1）直接只写 join 没有 on 则是完整的笛卡尔积
> 
> （2）使用 on 表示链接条件
> 
> （3）结合需求，加上其它的条件
> 
> （4）针对 列 进行精简

![e80141466e8144c98e11adbab069a2d2.png][]

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

#### (2)外连接 ####

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

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

![22cc7abf6dde4a4ebcbe3f1401aad526.png][]

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

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

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

![d37312ac992a4fedbf49d3316d59717f.png][]

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

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

![b17a3c06e7bf49739474aff99e2a3cf2.png][]

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

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

![5c7c16f354c441f1933d07acb619628f.png][]

![8186fe7b4e6c4119811cbc556ae4a669.png][]

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

![4d4389ce76884cf7b8a87cc7050dc523.png][]

![0915caa9635847898643bc119bce8b06.png][]全外连接

![9e62722378e14252978fb1cd1e8a1446.png][]

![7059b8422d754fbdad339a31cf832d94.png][]

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

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

#### (3)自链接 ####

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

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

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

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

> （1）算出笛卡尔积
> 
> （2）加上链接条件，此处可以使用 学生id 作为链接条件，也可以使用 课程id
> 
> （3）再加上进一步的业务中的条件，让左侧的表只保存 course\_id = 3 的记录，同时，让右侧的表保存 course\_id = 1的记录
> 
> （4）再针对列进行精简

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

![6deeef217e7c485c8c633fb006b93cf2.png][]

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

![03f914d38cf9434d87c8adbc16b5b54c.png][]

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

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

![d7221c3aef0b480f945b703dcf7c7d9b.png][]

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

最后的答案如下：

![72387a1120df4780a5df76606af25e34.png][]

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

#### (4)子查询 ####

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

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

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

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

![108b69df36384f7590b52015990a8397.png][]

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

![7197392d954849d68a9a522026d01999.png][]

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

![1a3ac7ec620540448ba307523203991f.jpeg][]如果想要出现两个张三，可以使用 union all

![a2ed04ca18474bc1a7b90326518937fe.png][]

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

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

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

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

## 四、总结 ##

![ebac92214b3f4273884c8e16bf2d0fa7.png][]

[d476f781800f4ea18c352de178f73766.jpeg]: https://img-blog.csdnimg.cn/d476f781800f4ea18c352de178f73766.jpeg
[bcdd2b7fddb04adca8bc0122d546aa68.png]: https://img-blog.csdnimg.cn/bcdd2b7fddb04adca8bc0122d546aa68.png
[9a32afccf14b45e8adacc0bef3d76203.png]: https://img-blog.csdnimg.cn/9a32afccf14b45e8adacc0bef3d76203.png
[58d0b3df2ba44895ae8581eb15dc61de.png]: https://img-blog.csdnimg.cn/58d0b3df2ba44895ae8581eb15dc61de.png
[6f6054abb0954e43b537f2f2c3002b52.png]: https://img-blog.csdnimg.cn/6f6054abb0954e43b537f2f2c3002b52.png
[04c3e89bb7c344f389032d8cd8f691b1.png]: https://img-blog.csdnimg.cn/04c3e89bb7c344f389032d8cd8f691b1.png
[e631555c70544f299649b9b316062c77.png]: https://img-blog.csdnimg.cn/e631555c70544f299649b9b316062c77.png
[9ec6ab6742e945e3b1b5e61a927c35a1.png]: https://img-blog.csdnimg.cn/9ec6ab6742e945e3b1b5e61a927c35a1.png
[167a940b3cc34e9baa75db33350101c1.png]: https://img-blog.csdnimg.cn/167a940b3cc34e9baa75db33350101c1.png
[c57a3303a97e4cbda6dd34846193948f.png]: https://img-blog.csdnimg.cn/c57a3303a97e4cbda6dd34846193948f.png
[65597569f8d44b5caf48e2dfff7b457d.png]: https://img-blog.csdnimg.cn/65597569f8d44b5caf48e2dfff7b457d.png
[9caeeaf6545d4d5e918e1c761869a298.png]: https://img-blog.csdnimg.cn/9caeeaf6545d4d5e918e1c761869a298.png
[300540be78854091b54d5a590a96fdca.png]: https://img-blog.csdnimg.cn/300540be78854091b54d5a590a96fdca.png
[7a79b22965ba4c0ab4e1963eeb76270f.png]: https://img-blog.csdnimg.cn/7a79b22965ba4c0ab4e1963eeb76270f.png
[a6649d1f08594a55ae2f3d629206cade.png]: https://img-blog.csdnimg.cn/a6649d1f08594a55ae2f3d629206cade.png
[668c5fdde55a4b34846a7987ba03db91.png]: https://img-blog.csdnimg.cn/668c5fdde55a4b34846a7987ba03db91.png
[7d414c12a50941afb34ad3e26e27c240.png]: https://img-blog.csdnimg.cn/7d414c12a50941afb34ad3e26e27c240.png
[d14c558fdd404226ad718a43ef1ab6a4.png]: https://img-blog.csdnimg.cn/d14c558fdd404226ad718a43ef1ab6a4.png
[a60bbb9f70b04cdc91924a46b391605b.png]: https://img-blog.csdnimg.cn/a60bbb9f70b04cdc91924a46b391605b.png
[0d9fb21480ac4a399531e66bce346e14.png]: https://img-blog.csdnimg.cn/0d9fb21480ac4a399531e66bce346e14.png
[4429645d7bb945fe980d682b4ff51261.png]: https://img-blog.csdnimg.cn/4429645d7bb945fe980d682b4ff51261.png
[6ec119055a9145ffad20d848f6808f80.png]: https://img-blog.csdnimg.cn/6ec119055a9145ffad20d848f6808f80.png
[e80141466e8144c98e11adbab069a2d2.png]: https://img-blog.csdnimg.cn/e80141466e8144c98e11adbab069a2d2.png
[22cc7abf6dde4a4ebcbe3f1401aad526.png]: https://img-blog.csdnimg.cn/22cc7abf6dde4a4ebcbe3f1401aad526.png
[d37312ac992a4fedbf49d3316d59717f.png]: https://img-blog.csdnimg.cn/d37312ac992a4fedbf49d3316d59717f.png
[b17a3c06e7bf49739474aff99e2a3cf2.png]: https://img-blog.csdnimg.cn/b17a3c06e7bf49739474aff99e2a3cf2.png
[5c7c16f354c441f1933d07acb619628f.png]: https://img-blog.csdnimg.cn/5c7c16f354c441f1933d07acb619628f.png
[8186fe7b4e6c4119811cbc556ae4a669.png]: https://img-blog.csdnimg.cn/8186fe7b4e6c4119811cbc556ae4a669.png
[4d4389ce76884cf7b8a87cc7050dc523.png]: https://img-blog.csdnimg.cn/4d4389ce76884cf7b8a87cc7050dc523.png
[0915caa9635847898643bc119bce8b06.png]: https://img-blog.csdnimg.cn/0915caa9635847898643bc119bce8b06.png
[9e62722378e14252978fb1cd1e8a1446.png]: https://img-blog.csdnimg.cn/9e62722378e14252978fb1cd1e8a1446.png
[7059b8422d754fbdad339a31cf832d94.png]: https://img-blog.csdnimg.cn/7059b8422d754fbdad339a31cf832d94.png
[6deeef217e7c485c8c633fb006b93cf2.png]: https://img-blog.csdnimg.cn/6deeef217e7c485c8c633fb006b93cf2.png
[03f914d38cf9434d87c8adbc16b5b54c.png]: https://img-blog.csdnimg.cn/03f914d38cf9434d87c8adbc16b5b54c.png
[d7221c3aef0b480f945b703dcf7c7d9b.png]: https://img-blog.csdnimg.cn/d7221c3aef0b480f945b703dcf7c7d9b.png
[72387a1120df4780a5df76606af25e34.png]: https://img-blog.csdnimg.cn/72387a1120df4780a5df76606af25e34.png
[108b69df36384f7590b52015990a8397.png]: https://img-blog.csdnimg.cn/108b69df36384f7590b52015990a8397.png
[7197392d954849d68a9a522026d01999.png]: https://img-blog.csdnimg.cn/7197392d954849d68a9a522026d01999.png
[1a3ac7ec620540448ba307523203991f.jpeg]: https://img-blog.csdnimg.cn/1a3ac7ec620540448ba307523203991f.jpeg
[a2ed04ca18474bc1a7b90326518937fe.png]: https://img-blog.csdnimg.cn/a2ed04ca18474bc1a7b90326518937fe.png
[ebac92214b3f4273884c8e16bf2d0fa7.png]: https://img-blog.csdnimg.cn/ebac92214b3f4273884c8e16bf2d0fa7.png