[大数据]【MySQL】MySQL表的增删改查（进阶版）

📌前言：本篇博客介绍MySQL数据库增删改查的进阶版，学习MySQL之前要先安装好MySQL，如果还没有安装的小伙伴可以看看博主前面的博客，里面有详细的安装教程。

那我们废话不多说，直接进入主体！🚀🚀🚀

一、表的设计

所谓的"数据库设计"和"表的设计"其实就是根据实际问题，然后将表创建出来
，也就是说，如果给你一个问题曾经，该如何设计数据库，如何设计表？

一个典型通用的方法就是：先找到这个场景中涉及到的"实体"，然后再来分析"实体之间的关系"，实体即为对象，可以视为是需求中的有些关键性名词。

比如说：我们要创建一个学生管理系统，有三个表，一个表示学生的基本信息；一个表示班级的基本信息；还有一个表示学生学习的课程的基本信息。

这三个表中，我们要找到他们的实体，很多时候，实体和实体之间并不是孤立的，而是存在对应关系，这种对应关系也需要体现在表中，而实体之间的关系，就有下面几种：

1.一对一

首先是这种一对一的关系，以学校的教务系统为例，我们会有以下这两个表：

student表（学生id，学生姓名，学生班级....）其中有user_id

user表（用户账户，密码...）其中有student_id

两者的对应关系其实就是小学生造句一样：一个账户对应一个学生，一个学生也只有一个账号。

当我们要在数据库中表示这种一对一的关联关系，有两种方法：

📌 方法1.可以把这两个实体用一张表来表示。

📌方法2.可以用两张表来表示，其中一张表包含另一个表的id。

根据这个对应关系，就可以随时找到某个账户对应的学生是谁，也能找到某个学生对应的账户是啥了。

2.一对多

那么一对多，其实也蛮好理解，还是以教务系统为例，这次我们的两个表分别是：

student表（学号，姓名....）

class表（班级编号，班级名称....）

来来来，这次我们的表是student表和class表，我们来造句：一个学生应该处于一个班级中，一个班级可以包含多个学生（一对多）。

然后在数据库中表示一对多的关系，就有两种典型的方案：

📌 方法1：在班级表中，新增一列，表示这个班级里的学生id都有啥。下图中，就是在class表中新增一列，然后张三等人就在这个班的学生列表。

📌 方法2：班级表不变，在学生表中，新增一列classid，也就是所在班级。如下图，就是在student表中增加一个学生班级，这里更容易体现一对多的关系。

那么对于一对多，是不是两种方法都可以呢？

实际上，在MySQL中，表示这种一对多的关系的时候，只能采用方案二，不能用方案一。因为MySQL中没有提供类似于"数组"这样的类型，所以我们存不了如方案一中新增的一列学生。但并不是所有数据库没有，像Redis的数据库就有数组类型，就可以考虑方案一这种方式表示。

3.多对多

然后就是多对多，经过上面两个，相信大家也知道多对多是什么样子的了，这里我们还是用两个表表示这种关系：

student表（学号，姓名...）

grade表（课程编号，课程名称...）

然后我们来继续造句：一个学生可以选择多门课程，一个课程可以包含多名学生。

然后在数据库中，这种多对多的关系，只需要一招：使用关联表来表示两个实体的关系。

比如：

📑student表：

📑grade表：

然后创建一个关联表，就是有学生学号，又有课程编号的表：

这样子我们就可以清楚的了解他们的对应情况了，学号为2040123456的同学选择了课程编号为1001，1002的课程；课程编号为1001的课程，包含了学号为2040123456和2040124567的同学，就体现了这种多对多的关系了。

更简单的说法就是，张三李四选择了高等数学课，高等数学课上有张三李四。通过上面关联表的学号列，就可以看见每个同学选了哪些课。

有的时候，为了更方便的表示/找到实体之间的关系，尤其是针对复杂的场景，还可以通过话ER图的方式去表示，在学校的数据库考试中，就经常会出现，必考，而且至少是一个大题。

二、新增

说完了表的设计，就是希望大家可以在脑海中有一个这种设计想法，然后就到我们的语句了，在之前我们都已经清楚增删改查是怎么样进行了，那么这里的进阶版又有什么不同呢，我们往下看：

在进行说明之前，我们先创建两个表：

//创建一个书库表
create table books(id int,name varchar(50),author varchar(20),price decimal(4,2));

//书库中的数据
insert into books values(1001,'老人与海狮','张三',29.99);
insert into books values(1002,'美女与椰树','李四',48.88);
insert into books values(1003,'云边有个小面包','王五',36.80);

//创建一个我的书的表
 create table mybook(id int,name varchar(50),author varchar(20),price decimal(4,2));

在我们建完表之后，我们就可以了说明我们这里的新增了：我们可以通过查询一个表的结果去放入另一个表。

先看两个表中在还没查询放入之前的内容：

然后我们执行查询books表并把查询结果放入mybook表中：

insert into mybook select * from books;
//Query OK, 3 rows affected (0.00 sec)
//Records: 3  Duplicates: 0  Warnings: 0

当我们执行完之后，我们发现我们mybook表中就有了books中的数据了：

但是这种操作要在前提前实现，那就是：两个表的查询出来的列数和类型必须是匹配的，比如book表第一个是id，那mybook表的第一个也是id。这样才能放进去合适的位置。

当然如果你在创建mybook表的时候，id和name的位置弄反了，但是又像查询放入，我们也可以在执行命令的时候换位置查询得到结果：

insert into mybook select name,id,author,price from books;
//Query OK, 3 rows affected (0.00 sec)
//Records: 3  Duplicates: 0  Warnings: 0

另外，这种新增操作也可以给后面的select指定一些其他条件，比如排序，limit，去重等，实际上，插入的是select执行结果的临时表，但插入这些数据到另一个表的时候，另一个表就已经把数据存储到数据库服务器的硬盘上了。

三、查询

接下来，又到了我们的查询操作了，SQL中最复杂的就是查询操作了，这里我们的进阶版中，又有下面这几个查询：

1.聚合查询

首先是聚合查询，聚合查询顾名思义，就是把多行之间的数据给进行聚合起来，比如这books表中有多少本书这里的。

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

①.count查询数据数据量

select count(*) from books;
select count(name) from books;

上面这两个查询结果是一样的，都会查询到3，因为我们上面的books表中是三个数据，而且id也是三个都有，查询结果：

但是这里我们有一个疑问，就是如果我们这个数据为空，count还计算吗，这里我们可以尝试一下，也就是新增一条含null的数据：

insert into books values(1004,'玛卡巴卡','赵六',null);

查询一下，发现其实他是不会计算的：

②.sum求和（类似于excel表求和）

这里的求和就是类似于excel表的求和，就是把该列的数据都加起来，比如说我要把书库中的书都买下来需要多少钱，我们就可以用这个函数：

 select sum(price)from books;

查询结果：

? 值得一提的是，sum这函数是对数据进行操作的，不对字符串进行操作，并且还可以通过表达式的形式进行操作：

比如说我觉得都买一本不行，我还要都买两本，送另一份给我朋友：

 select sum(price)*2 from books;//表达式求值
 select sum(price*2) from books;//聚合函数里面的参数也可以进行操作

同时我们也可以尝试查询一下名字这种字符串，当我们查询的时候会出现结果，但是也会报警告，同时我们可以用show warnings;来查看警告信息：

再者，我们的聚合查询也是查询，所以我们同样可以叠加条件语句等，去筛选我们需要的结果，比如说：比30块低的书我看不起，我不想买，我就买30块以上的书：

select sum(price) from books where price>30.00;

查询结果：

然后剩下的max，min，还有avg就是筛选最大最小值，平均值等，和这里的基本上类似，就不展示了。

③.group by

group by是一个分组操作，是根据行的值，对数据进行分组，把值相同的行分为一组。比如在调查各行各业的工资的时候，我们应该先分出各个职业，再查询他们这一职业的工资情况。

我们用一个调查工资表来进行说明：

//创建一个工资表
mysql>create table payroll(id int,name varchar(50),role varchar(50),salary decimal(7,2));

//填入数据
mysql> insert into payroll values(1,'马匀','服务员',1000.20),
    -> (2,'马花藤','游戏陪玩',2000.99),
    -> (3,'孙悟空','游戏角色',999.11),
    -> (4,'猪悟能','游戏角色',333.50),
    -> (5,'沙和尚','游戏角色',700.33),
    -> (6,'隔壁老王','董事长',12000.66);

查询一下表中如图所示：

然后我们来运用group by以及聚合函数去查询：

select role,max(salary),min(salary),avg(salary) from payroll group by role;

这里就要理解一下了，注意：这里的操作就是需要按照岗位，来进行分组，group by role根据role这一列进行分组：

然后得到的结果是这样的：

这里注意了：

一个sql的执行顺序，具体的执行顺序，和我们书写的顺序并不完全一致。

同时这里还可以应用上一个having，当我们计算如avg这种平均值的时候，有时候数据中存在null的数据，这样的数据是不会影响我们的平均值的，但是当我们用group by分组之后，还需要条件筛选，就要用到having。

? GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING。

比如要显示平均工资低于1500的角色和它的平均工资：

select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;
//在group by之后加上having以及条件

查询结果：

🚩以上便上MySQL表的增删改查（进阶版），接下来的一篇文章应该就是MySQL表之联合查询（多表查询）了，本来想在本篇中写完联合查询，但发现联合查询的细节很多，就留到下一节了。欢迎关注。一起学习，共同努力！

还有一件事：