当前位置:   article > 正文

MySQL表增删改查(进阶)

MySQL表增删改查(进阶)


一、前言

本篇文章是MySQL表里增删改查的进一步应用,相较之前简单的增删改查会难上一些。本篇主要围绕数据库约束、表的设计、新增、查询来学习。


提示:以下是本篇文章正文内容

二、数据库约束

数据库约束,就是创建表的时候,给这个表指定一些规则,后续插入/修改/删除都要保证数据是能够遵守这些规则的。而引入规则,是为了进行更强的数据检查或校验。当然,约束能够引入更多的检查操作,但是同时,也会带来额外的系统开销。所以,数据库约束是牺牲了执行效率,换来了开发效率

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

说明:PRIMARY KEY 是主键。这是数据库中最重要的约束,表示一个记录的身份标识(如身份证,学号)。 FOREIGN KEY 是外键,描述两个表之间的关联关系。 CHECK 是指定一个具体的条件,在MySQL中并不支持。

下面通过代码来了解数据库约束。

  • NOT NULL
create table student (id int, name varchar(20));
  • 1

在这里插入图片描述

create table student2 (id int not null, name varchar(20));
  • 1

在这里插入图片描述
可以看到,在查看表结构时,原本id在Null一列经过not null修饰从YES变成了NO,说明id不再允许为Null。

在这里插入图片描述
当插入id为null时,就会提示错误。

  • DEFAULT
create table student (id int, name varchar(20));
  • 1

在这里插入图片描述

create table student (id int, name varchar(20) default '无名氏');
  • 1

在这里插入图片描述
可以看到,查询表结构时,name的default这一列经过default修饰从NULL变成了无名氏。
在这里插入图片描述
在只插入id时,name默认为无名氏。

  • UNIQUE
create table student (id int, name varchar(20));
  • 1

在这里插入图片描述

create table student (id int unique, name varchar(20));
  • 1

在这里插入图片描述
可以看到,id 在被unique修饰后,在查看表结构时,Key这一列变成了UNI,表示唯一。
在这里插入图片描述
在插入id同为1的数据之后,MySQL就会报错,提示重复插入id为1。说明在加上unique约束之后,后续进行插入/修改的时候,都会先进行查询,看看当前这个值是否以及存在。

  • PRIMARY KEY
    在这里插入图片描述

在这里插入图片描述
可以看到,id被primary key修饰后,Null变成了NO,Key变成了PRI。这表明primary key修饰的字段是非空且唯一的
主键,是用来作为一个记录的身份标识。通常会使用一个xxx id 这样的列作为主键。一个表里面只能有一个主键。除了基础的使用之外,还有一种情况,就是联合主键,这个主键是由多个列联合构成的(暂不讨论)。
在这里插入图片描述

上面我们说到,主键是不可以重复的。那么如何保证它不重复?MySQL自身给我们提供了一种机制 “自增主键”。

create table student (id int primary key auto_increment, name varchar(20) );
  • 1

通过自增主键,当前的id就不需要用户自己指定了,数据库会自行分配,按照“自增”的方式来分配。当然也可以手动插入id,但是要确保插入的结果是不重复的。
在这里插入图片描述
每次使用null的方式插入自增主键的时候,数据库会根据当前这一列的最大值,在这个基础上,继续进行递增。而且这里的递增是不会重复利用之前的值的,也就是说若最大id为20,即使删除了id为20的这一行,自增还是会从id为21开始。

上述自增主键,只能在单个数据库下生效。如果数据库是由多个MySQL服务器构成的”集群“,此时,自增主键就无法生效了。
MySQL服务器构成的集群:当表里的数据特别多,一台机器存不下,就需要使用多个机器,把表里的数据一分为二,每台机器存一半。此时,这两个机器的主键,就是各自自增各自的了。这就可能导致,第一个机器中的数据的id和第二个机器的数据的id重复了。实际上在业务中引入的”主键“是希望不会重复的。这其实就是所谓”分布式“。
确保一个分布式系统中,能够存在唯一的id。业界也有一些分布式系统生产唯一id的算法,但是都大同小异。有点儿类似于身份证号:位置信息+时间信息+其它编号。其算法主要由三部分构成(时间戳+主机编号+随机因子)。
首先把主键,设置成字符串类型。
1. 时间戳
时间戳,确保不同时刻生成的数据的id是不同的。
2. 主机编号
同一时刻生成的多个数据只要这几个数据是在不同主机上,仍然是可以通过主机编号确保不重复的。
3. 随机因子
同一时刻,同一个主机上生成了多个数据(随机数),这多个数据id 再通过随机因子进行区分。

当然,这套方案也不能够100%确保,生成的id一定是唯一的。关键在于,同一时刻,同一个主机上,连续两次随机因子恰好相同,这是当然有可能的,只是概率很低。

  • FOREIGN KEY
    在这里插入图片描述
    在这里插入图片描述
    上面分别创建了一个班级表和学生表。可以看到,class表的classId为1,2,3。而学生却出现了classId为5的情况,然而并没有classId为5的班级,显然这样的数字是不合理的。所以,为了解决这样的问题,引入了外键约束。这样学生表中的classId都要在班级表中存在,就可以用外键约束来进行校验了
 create table class (classId int primary key, name varchar(20));
  • 1
 create table student (id int primary key, name varchar(20), classId int, foreign key (classId) references class (classId));
  • 1

在这里插入图片描述
此时就要求,本表中的这个列的数据必须在引用的外面的表的对应列中存在。注意这里的约束与前面有所不同,外键约束,是要写到最后面的,而前面的列都定义好了之后,在最后面通过foreign key 创建外键约束。

这个情况下,也可以认为,班级表约束了学生表。
把班级表这种约束别的表的表,称为”父表“(parent table)
把学生表这种被别的表约束的表,称为”子表“(child table)

当父表中不存在子表需要插入的数据:
在这里插入图片描述
在插入时会先检查父表中是否存在数据,若不存在则插入失败。

子表存在父表关联的信息时,父表信息不能修改:
在这里插入图片描述
其实,外键约束是双向的。说是,父亲约束儿子,其实儿子也在约束父亲。子表对于父表也是有约束的。
想要删除父表的记录,就必须先删除子表对应的数据,确保子表中没有数据引用父表的记录,才能真正执行删除。尝试删除/修改父表中的记录,也会先查询子表,看看当前这个结果是否在子表中被引用,如果被引用了,就会删除失败。

使用外键约束的时候,操作子表时要查询父表,而操作父表时也要查询子表,这就伴随了很多的查询操作。如果表的数据很多,查询操作就会非常低效。为了让上述查询更高效一些,往往就需要要求子表中的列和父表中被引用的列,都要带有”索引“。
在这里插入图片描述
上述表为何创建失败?其实就是因为表里未添加主键,也就没有索引。所谓索引也可以理解成目录。
在这里插入图片描述

关于外键,还有一个经典的问题。如果做一个电商网站,有商品表和订单表两个表。
商品表(goodsId,name,price…)
订单表(orderId,goodsId,time…)
此处商品表和订单表就可以使用外键约束了,那么当商品要下架,如何完成这里的操作呢?其实我们只需在商品表的字段中添加isOnline(1表示商品在线,0表示商品下线),这样即使商品下架,只需要将商品上架信息标注为0,而无需考虑删除表中数据或者无法删除的因素。且历史订单数据是会是始终存在的,所以即使商品没了,订单也不能没。这种删除方式就是所谓的”逻辑删除“。

其实不仅仅是数据库,很多场景也会用到逻辑删除的思路。如电脑上的文件,你进行删除,也是逻辑删除,不是真正的删除。你把文件删除掉,其实也就是在系统中,把硬盘上的对应的盘块数据标记成无效了(也不是真正的删除)。删除文件+清空回收站 都是没用的,理论上都是可以恢复的。如果后面电脑不能用了,需要针对硬盘进行更彻底的删除(物理删除),也就是把硬盘砸了(0.0)。
所以,其实drop database在计算机硬盘上,也是逻辑删除,数据也是有很大概率能够恢复回来的。但是,不能保证数据100%恢复,所以还是尽量不要随便drop database。

  • CHECK
    MySQL使用时并不会报错,但是同时也忽略该约束(不支持)。

三、表的设计

设计,往往是和”经验“有一定关联的,谈到”数据库设计“就是根据需求,来把你需要的表给创建出来(有几个表,表里有啥…)

表的设计主要有两个步骤:
1. 先根据需求,找到实体
一些关键性质的对象,梳理清楚需求,提取出关键的名字。一般来说,每个实体,都得安排一个表。

2. 梳理清楚实体之间的关系
多个实体之间,需要理清楚关系。不同的关系下,有不同的设计表的方式。

而表的设计主要有三种关系

  • 一对一
    教务系统,需要表示的一个概念,学生(实体),需要一个学生表来描述这个实体。还有一个概念,账户(实体),需要一个账户表来描述这个实体。

学生表:学号、姓名、班级、联系方式、入学时间…
账户表:账户名、密码、注册时间、上次登录时间、登录地点…

一个学生,只能有一个账户。(学生不能有多号)
一个账户,也只能给一个学生使用。(一个账户不能给多个学生使用)

于是,在一对一的关系下,表结构就有一下两种设计方案:

方案一:创建一个大表,把两个表的信息合并起来。
学生账户表(accountId,accountName,password,…studentName,…)
如果这两个表都很简单(列很少)可以考虑合并,如果这两个表都很复杂(列很多)不建议合并。

方案二:分两个表,使用 id 来引用过来,建立联系。
student(studentId,studentName…)
account(accountId,userName,password…,studentId)
可以使用studentId建立两表之间的联系。

  • 一对多
    教务系统中,有一个实体学生,还有一个实体班级。

    一个班级,可以包含多个学生。
    一个学生,只能从属一个班级。

    针对一对多设计表,也存在两种方案。

    方案一:
    在这里插入图片描述

    但由于MySQL的类型中,不支持”数组“类型,因此这个方案在MySQL中行不通。只有像Redis这样的数据库才支持数组类型(List),就可以使用类似的方式来表示。

    方案二:
    在这里插入图片描述

  • 多对多
    和刚才差不多,通过造句,把实体之间的关系,往固定句式里套。教务系统,学生是一个实体,课程也是一个实体。

一个学生,可以选择多门课程。
一个课程,也可以被多个学生选择。

在这里插入图片描述

所以,确认实体的关系,就需要我们造句,往里套,这类似一个定式,按照固定套路来。
在这里插入图片描述

四、新增

insert into table_name [(column [,column...])] select...
  • 1

这是一个组合技,把插入语句和查询语句结合到一起。以查询结果,来作为插入的值。把查询到的”临时数据“转换成”永久数据“。
在这里插入图片描述
当然也可以指定表中的列进行插入,但是查询出来的结果集合,列数/类型 要和 插入表的结构匹配。
SQL中经常有这种”套娃“操作。

五、多表查询

1.聚合查询

select name,chinese+math+english from...
  • 1

上面这串代码是拿三个列来进行计算的。
聚合查询,就是在进行“行和行”之间的运算。通过”聚合函数“来进行操作,这是SQL提供的库函数。

函数说明
COUNT( [DISTINCT] expr )返回查询到的数据的 数量
SUM( [DISTINCT] expr )返回查询到的数据的 总和,不是数字没有意义
AVG( [DISTINCT] expr )返回查询到的数据的 平均值,不是数字没有意义
MAX( [DISTINCT] expr )返回查询到的数据的 最大值,不是数字没有意义
MIN( [DISTINCT] expr )返回查询到的数据的 最小值,不是数字没有意义

在这里插入图片描述
虽然这个地方不是通过聚合函数获取到的,但是这个数据只是在客户端里方便看到,如果是编程的话,还是通过聚合函数进行比较好,使用聚合函数还可以进行一些条件判断。
下列举例说明:

例子1:

数据表:
在这里插入图片描述

操作:

在这里插入图片描述
这个操作相当于先执行selct * from student,
再使用 count 来计算结果的行数。
在这里插入图片描述
如果查询的列带有NULL,则NULL不计入行数。但如果使用count(*)的时候,即使是全是NULL的行,也会被记录。

例子2:

数据表:
在这里插入图片描述

操作:

在这里插入图片描述
sum函数会把数字类型都相加起来,但是含义NULL的行会忽略掉,在SQL中NULL和其它数字进行运算,结果一般都是NUL。

剩下的AVG、MAX、MIN都是类似的。
在这里插入图片描述

若要查询数学成绩最低的同学:
在这里插入图片描述
由数据表可知,数学成绩最低的同学并不是张三。由此可见,在使用聚合函数的时候,列和列之间的顺序以及被“打散”了。正常来说,一行数据的每个列都是对应的(共同构成同一条记录),如果查询中包含聚合函数和非聚合的列,则列各自是各自的。大部分情况下,聚合的列和非聚合的列,不能在一起配合使用,除非语句中带有group by的情况。
在这里插入图片描述
那么查询数学成绩最低的同学,可以使用order by加上limit。

前面的聚合,是把表的所有行都聚合在一起。但是有时候,我们可以把所有行分成若干组,每一组再分别进行聚合。

group by 的效果就是把指定的列,值相同的记录,划分到一组,针对这些组就可以分别进行聚合查询了。

数据表:
在这里插入图片描述

查询每个岗位的平均薪资:

在这里插入图片描述
这里是按照role来group by的。

针对分组查询,也是可以使用条件的,可以对查询结果进行筛选。
分组之前的条件,使用where来表示。
分组之后的条件,使用having来表示。

例子1:

统计每个岗位的平均薪资,但是除去“张三”。
所以在分组之前,先把“张三”除去。
在这里插入图片描述

例子2:

统计每个岗位的平均薪资,除去平均薪资大于15000的情况。
平均薪资,需要在分组之后,才能计算。
在这里插入图片描述

所以,分组操作往往是和聚合一起使用的。

2.联合查询

前面的查询,都是针对一张表进行查询。而多表查询要比单表查询更加复杂。

2.1 内连接

内连接语法:

select 字段 from1 别名1 [inner] join2 别名2 on 连接条件 and 其他条件;
select 字段 from1 别名1,2 别名2 where 连接条件 and 其他条件;
  • 1
  • 2

提示:内连接的inner可以省略
在学习联合查询之前,我们需要先学习“笛卡尔积”。
在这里插入图片描述
那么在SQL中,笛卡尔积是怎么样的?其实,就是表之间进行简单的“排列组合”,将表1的所有列挨个加上表2的所有列。
在这里插入图片描述

同时我们发现进行笛卡尔积之后,有些行的数据出现了不相符的情况,那么这些数据就是笛卡尔积之后无效的数据。
在这里插入图片描述
所谓的“多表联合查询”,核心操作就是进行笛卡尔积。比如使用两个表进行联合查询,就是先把两个表进行笛卡尔积,然后再指定一些条件,来实现需求中的一些查询。
再实际开发中,笛卡尔积(多表查询)一定要慎重使用,使用之前一定要评估好笛卡尔积的规模。如果基于两个很大的表进行笛卡尔积,这也是一个危险操作,会产生大量的运算和IO,可能会把数据库搞挂。

联合查询的四个要领:

1. 笛卡尔积
2. 指定连接条件
3. 指定其它条件
4. 针对列进行精简/表达式运算/聚合查询

提示:关联查询可以对关联表起别名
在进行联合操作前,先插入数据:

insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

接下来,举几个例子来演示。

例子1:

查询“许仙”同学的成绩
“许仙”这个名字是在学生表里面的,而成绩是在分数表里面的,所以需要用到联合查询。

第一步:进行笛卡尔积

在这里插入图片描述

第二步:指定连接条件

在这里插入图片描述

第三步:根据其它需求,补充其它条件
条件:找“许仙”同学的成绩

在这里插入图片描述

第四步:针对上面的列,进行精简

在这里插入图片描述

其实,多表查询还有另外一种写法:
步骤一:进行笛卡尔积

在这里插入图片描述

步骤二:指定连接条件

在这里插入图片描述

步骤三:指定其它条件

在这里插入图片描述

步骤四:针对列进行精简

在这里插入图片描述

例子2:

查询所有同学的总成绩,及同学的个人信息。

此处所求是每个同学的总成绩,所以应按照同学的名字/id进行group by。

步骤一:进行笛卡尔积

在这里插入图片描述

步骤二:指定连接条件

在这里插入图片描述

步骤三:补充其它条件

由于此处是需要知道所有同学的成绩,因此不用对同学进行进一步筛选。

步骤四:针对列进行精简 / 表达式 / 聚合查询
在这里插入图片描述
也可以使用第二种语法表示:
在这里插入图片描述

建议初学者学习联合查询这种复杂SQL时,最好一步一步写,避免出错时难以发现。

例子3:

查询所有同学的成绩,及同学的个人信息。

这里,同学的名字在学生表里,课程名字在课程表里,分数在分数表里。

步骤一:进行笛卡尔积

在这里插入图片描述

步骤二:指定连接条件

三张表进行笛卡尔积,相当于表A和表B先进行笛卡尔积(第一个连接条件),而表A和表B进行笛卡尔积的结果再和表C进行笛卡尔积(第二个连接条件),最终得到的就是三张表的笛卡尔积。
在这里插入图片描述
而student表和course表里没有“有关联的列”,所以无法直接算笛卡尔积。所以连接条件只能从student表与score表,score表与course表下手。
在这里插入图片描述

步骤三:指定其它条件
此处和例子2一样,也是不需要指定条件的。

步骤四:针对列进行 精简 / 表达式 / 聚合

在这里插入图片描述
也可以用第二种方式写:
在这里插入图片描述
提示:在多表查询中,前面涉及到的去重、排序、limit 也是同样适用的

2.2 外连接

外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。

外连接语法:

-- 左外连接,表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
-- 全外连接,表1,表2完全显示
select 字段 from 表名1 outer join 表名2 on 连接条件;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

外连接也是多表查询的一种体现形式。上面所学的写法叫做内连接。

给出以下数据表:

在这里插入图片描述

内连接:

在这里插入图片描述

左外连接:

在这里插入图片描述

右外连接:

在这里插入图片描述
由于这两个表的数据都是一 一对应的,学生表中的任何一个记录都能在分数表中体现,当然,分数表中的每个记录也能在学生表中体现出来。所以,此时的内连接和外连接,结果是一样的。

给出数据表:

在这里插入图片描述
此时的数据,就不是一 一对应了。
在这里插入图片描述
进行左外连接:

在这里插入图片描述
进行右外连接:

在这里插入图片描述
进行全外连接:

在这里插入图片描述
可以看到,MySQL是不支持全外连接的。但是,在Oracle是支持全外连接的。

2.3 自连接

自连接是指在同一张表连接自身进行查询

自连接用的很少,这算是一种奇淫技巧了。自连接需要在同一张表,自己和自己进行笛卡尔积。有的时候,想要进行条件查询,条件一定是列和列之间,而不能是行和行之间指定条件。所以,想要针对行之间指定条件,就可以通过自连接,把行关系转成列关系。

当然,这个操作的代价也是很高的,所以需要慎重。如果实际开发中遇到某个地方,必须用到内连接才能解决,那么就需要考虑一下,是不是表的结构设计的不科学。

例子:

若要查询“计算机原理”比“Java”成绩高的成绩信息

在这里插入图片描述

在这个例子中,想要完成不同科目的比较,就需要比较行之间的大小,而SQL无法直接做到,就只能把行转成列,用到自连接。

在这里插入图片描述
提示:进行自连接时,表名需要指定别名,否则两个表名相同会报错!

指定连接条件:

在这里插入图片描述

根据条件筛选:

筛选出左表course_id为3,右表course_id为1的记录(或者左为1,右为3),且course_id为3的成绩大于course_id为1的成绩。

在这里插入图片描述

3.子查询

子查询是指嵌入在其它SQL语句的select语句,也叫嵌套查询

例子:查询“不想毕业”同学的同班同学

如果按照正常的步骤:

在这里插入图片描述

首先确认“不想毕业”同学所在的班级:

在这里插入图片描述

然后再查询班级里不叫“不想毕业”的同学:

在这里插入图片描述

用子查询来完成:

在这里插入图片描述
当然,这种把语句更加复杂化的操作,是不利于我们人脑的,所以日常开发中并不推荐使用子查询。

4.合并查询

在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all。使用UNION和UNION ALL时,前后查询的结果集中,字段需要一致。

合并查询,就是把两个查询的结果合集,合并成一个集合。使用union关键字完成。即select 1 union select 2,但是要求两个select查询的结果集,列数和类型要匹配,列名不影响,最终的列名就是第一个select的列名。

  • union
    该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。

  • union all
    该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。

例子:查询名字带有英文的记录表以及id 小于3的记录表
在这里插入图片描述
当然,使用or也可以完成上述查询。但是,union可以不局限于同一张表,可以针对不同表操作。

六、总结

本篇学习了 约束、表的设计、新增、查询,其中查询包括聚合查询以及联合查询。其中,有很多内核知识,是需要我们掌握的。 在实际开发中,其实更常用的SQL语句,是最基础的增删改查。但是在面试的时候,面试官会喜欢考察联合查询的知识。所以,也需我们耐下心来学习以上知识。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/花生_TL007/article/detail/672090
推荐阅读
相关标签
  

闽ICP备14008679号