【MySQL】MySQL表的增删改查(进阶版)

发布时间:2023-12-21 11:30

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

那我们废话不多说,直接进入主体!

MySQL表的增删改查进阶

  • 一、表的设计
    • 1.一对一
    • 2.一对多
    • 3.多对多
  • 二、新增
  • 三、查询
    • 1.聚合查询
      • ①.count查询数据数据量
      • ②.sum求和(类似于excel表求和)
      • ③.group by


一、表的设计

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

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

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

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

1.一对一

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

student表(学生id,学生姓名,学生班级....)其中有user_id

user表(用户账户,密码...)其中有student_id

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

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

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

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

根据这个对应关系,就可以随时找到某个账户对应的学生是谁,也能找到某个学生对应的账户是啥了。
\"【MySQL】MySQL表的增删改查(进阶版)_第1张图片\"


2.一对多

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

student表(学号,姓名....)

class表(班级编号,班级名称....)

来来来,这次我们的表是student表和class表,我们来造句:一个学生应该处于一个班级中,一个班级可以包含多个学生(一对多)。

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

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

\"【MySQL】MySQL表的增删改查(进阶版)_第2张图片\"

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

\"【MySQL】MySQL表的增删改查(进阶版)_第3张图片\"

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

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


3.多对多

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

student表(学号,姓名...)

grade表(课程编号,课程名称...)

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

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

比如:

student表:

student表 学号 姓名
2040123456 张三
2040124567 李四
2040125678 王五

grade表:

grade表 课程编号 课程名称
1001 高等数学
1002 大学物理
1003 计算机网络

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

学生-课程关联表 学号 课程编号
2040123456 1001
2040123456 1002
2040124567 1001

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

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

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

\"【MySQL】MySQL表的增删改查(进阶版)_第4张图片\"

二、新增

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

在进行说明之前,我们先创建两个表:

//创建一个书库表
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));

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

先看两个表中在还没查询放入之前的内容:
\"【MySQL】MySQL表的增删改查(进阶版)_第5张图片\"

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

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

当我们执行完之后,我们发现我们mybook表中就有了books中的数据了:
\"【MySQL】MySQL表的增删改查(进阶版)_第6张图片\"

但是这种操作要在前提前实现,那就是:两个表的查询出来的列数和类型必须是匹配的,比如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([DISTINCT] expr) 返回查询到的数据的 数量
SUM([DISTINCT] expr) 返回查询到的数据的 总和,不是数字没有意义
AVG([DISTINCT] expr) 返回查询到的数据的 平均值,不是数字没有意义
MAX([DISTINCT] expr) 返回查询到的数据的 最大值,不是数字没有意义
MIN([DISTINCT] expr) 返回查询到的数据的 最小值,不是数字没有意义

①.count查询数据数据量

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

上面这两个查询结果是一样的,都会查询到3,因为我们上面的books表中是三个数据,而且id也是三个都有,查询结果:
\"【MySQL】MySQL表的增删改查(进阶版)_第7张图片\"

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

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

查询一下,发现其实他是不会计算的:
\"【MySQL】MySQL表的增删改查(进阶版)_第8张图片\"

②.sum求和(类似于excel表求和)

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

 select sum(price)from books;

查询结果:
\"【MySQL】MySQL表的增删改查(进阶版)_第9张图片\"

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

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

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

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

\"【MySQL】MySQL表的增删改查(进阶版)_第10张图片\"

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

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

查询结果:
\"【MySQL】MySQL表的增删改查(进阶版)_第11张图片\"

然后剩下的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);

查询一下表中如图所示:
\"【MySQL】MySQL表的增删改查(进阶版)_第12张图片\"

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

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

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

\"【MySQL】MySQL表的增删改查(进阶版)_第13张图片\"

然后得到的结果是这样的:
\"【MySQL】MySQL表的增删改查(进阶版)_第14张图片\"

这里注意了:

一个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表的增删改查(进阶版)_第15张图片\"


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

还有一件事:

\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号