MySQL的增删改查(进阶)

赏心悦目

1.数据库约束

什么是数据库约束?

我们希望在储存数据的时候,我们所储存的数据得是靠谱的,换句话说,假如我们的数据上限是 100 那么你就不能存入 101 这个数据,简而言之就是对数据进行范围规范.

MySQL提供了一些机制,辅助我们自动的依赖程序,对数据进行检查,这类检查数据的机制,就是'约束'.一旦把约束确定好了,后续我们对数据进行增,删,改,的时候MySQL就会自动的对修改的数据做出检查,看看你此处的这个数据是否靠谱,如果不靠谱,不满足约束,就会直接报错

1.1 NOT NULL

我们现在不加任何的约束创建了一个表,现在我们向表中加入一个空值

现在这个表是允许这样做的

现在我们重新创建一个名为 students 的一个表,并且我们给它加入 not null 约束

not null约束,是给列名来指定的,写在列的后面

如上图所示

此时我们可以看到,我们给name进行了 not null 修饰,此时name这一列就不允许为 null

给 id 这个列添加 null 是可以的

但是我们给 name 这个列加入 null 就会报错

当然了,我们也不能把数据修改为null

1.2 UNIQUE

unique:不允许存在两行数据在指定列上重复

此处的不能重复指的是指定列不同行之间,即

同一行,不同列之间可以重复

不同行,不同列之间也可以重复

若我们不加任何约束去创建一个表,那么这个表中是可以有重复的数据的

现在我们用 unique 来修饰 id 这一列,可以看出,现在 id 这一列不能重复.

那么MySQL怎么知道 id 是否重复呢?

MySQL在对于有unique约束的数据,在插入的时候就会先进行查询,同理修改也是这个样子.

那么对于MySQL来说要进行unique约束,那么所消耗的资源也是会更多的.

1.3 DEFAULT

我们先创建一个表,不加任何的约束,然后我们单独写入 id 这一列,值为 1 .

这时我们发现我们没有指定 name 的值,但是此时 name 的默认值为 NULL .

我们把上一个表删除,重新创建一个表,此时我们用 DEFAULT 约束,规定 name 这一列的默认值为 "未命名" ,那么此时 name 这一列的默认值如上图所示

1.4 PRIMARY KEY

主键约束

主键就是一条数据的身份标识

而对于主键而言,有两个要求

1.非空

2.不能重复

3.对于数据库而言,一个表只能有一个主键

这样 id 这个列就成为了一个主键

如上图所示,如果我们插入两个相同的 id 则会报上面的这个错误

当然了,若 id 为 null 也会报上面的这个错误.

如果一个表中定义了两个主键约束,那么就会报如上所示的这个警告.

主键只能有一个,但是主键可以对应一个列或者多个列

多个列的这种情况叫做联合主键

PRIMAARY KEY AUTO_INCREMENT

主键往往是一个整数类型的 id ,要求不能重复,那么如果我们要插入的数据很多,我们到最后都不记得我们插到哪里了,那总不能一个一个去试吧,基于此MySQL给我们提供了自增主键.

自增主键:允许客户端,在插入数据的时候,不手动指定主键的值,而是交给MySQL自行分配,确保分配出来的这个主键的值,是和之前不重复的

此时,这里的 id 就是一个自增主键,但是请注意

AUTO_INCREMENT 必须搭配整数类型的主键来使用

在上图中我们插入了一个 id 是 null 的数据,在这里由于 id 是自增主键,所以在这里 null 的意思是:让数据库自行分配一个 id

当然了,自增主键也是可以自己设定的

我们在手动插入一个 id 后,若继续使用自增主键,则会按照新的顺序来进行.

那么问题来了,这个 id 数据库是怎么分配的?

MySQL会记录当前 id 中的最大值,下一个分配的 id ,就会在之前最大值的基础上,继续自增

1.5 FOREIGN KEY

外键约束

在这个代码中,需要明确交代,谁(哪个表的哪一列)要受到谁(哪个表的哪一列)的约束

创建外键约束的时候,是修改子表的代码,父表代码是不受影响的.

那这样做有什么效果呢?

这就保证了

1.在子表中受到约束的那一列的数据在插入或者修改的时候要在父表中对应的那一列中存在.

修改也是一样的,若是子表中受约束的那一列与父表中的不一样,则会报相同的错误.

针对这种带有外键约束的 插入 或者 修改,就会触发查找操作,在父表中进行查询.

2.删除/修改 父表中的记录,就需要看看这个记录是否在子表中被使用了.如果被使用了,则不能进行删除或者修改

由于classId = 5 的这一条记录 在子表中没有被关联,所以是可以删除的.

但由于我们在子表中关联了父表中的 classId = 1,所以此时删除父表中classId = 1 的这一条记录就会失败.

那么当然了,若是此时删除父表,那便也会直接失败.

所以在有外键约束的情况下若是想要删除父表则必须先删除子表

简而言之:

父表的外键约束的那一列需要有PROMARY KEY / UNIQUE 约束

新增

插入查询结果

insert into 表名 [(column[,column ...])] select ...

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

此操作就相当于复制粘贴

使用条件:此处select查询的结果得和你要插入的那个表得要对应(也就是 列的数目 类型 约束 要一致)

2.1 聚合查询

我们之前学的表达式查询,实际上是要对列与列之间进行操作

而此处的聚合查询,本质上是在对行与行之间进行操作

2.1.1 聚合函数

在SQL中为了让我们更好的完成一些操作,SQL为我们提供了一些聚合函数

COUNT

我们以名字查询并计数也是可以的

那如果说我们插入了一个 null 值

此时按照名字来查询并计数就不能统计到namenull的这种情况了

SUM

SUM 进行求和,会把这一列的若干行,按照 double 的方式累加.(就会尝试把这一行数据先转成 double)

现在之所以能成功是因为chinese这一列的类型是数字,那么假如不是数字呢?

不是数字当然也就不能转换为double

这里出现了七个警告,那么我们如何具体查看警告的内容呢?

SHOW WARNINGS

以上的这些聚合函数使用方法大致相同,但有的时候我们更希望将这样的一些数据分组进行操作.

GROUP BY字句

指定一个列,按照这一列进行分组.这一列中,数值相同的行,会被分到一组.每个分组中都可以使用上述聚合函数来进行计算.

我们创建了如上图的这样一张表,使用了 GROUP BY 语句,使得最后的结果依据 ROLE 来进行分类.

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

分组查询,也是可以搭配条件来使用的,此时分为两种情况

1. 分组之前的条件: where
2. 分组之后的条件: having

例:除去张三后的平均值

为了接下来更好的举例,我们加入了 '老板' 这个职业.

例:分组后除去平均薪资超过两万的职业

这里我们看到,分组之后的条件,也是需要搭配聚合函数来使用的.

• 3.一个SQL中可以同时包含分组前的条件与分组后的条件

例:除去张三后的平均值并分组后除去平均薪资超过两万的职业

3.1 联合查询(多表查询)

给你多个表,结合多个表的数据,进行一些综合性更强的查询.(笛卡尔积)

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

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

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

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

笛卡尔积,是一个非常低效的操作,尤其是表的本身记录比较多的情况=>多表联合查询,也是非常低效的.

但是需要注意的是,在以上的排练组合中,有些数据是合理的,有些数据是不合理的,就比如:张三,李四 他们两个在classid为1的班级里,所以他们是合理的数据,而王五,赵六 他们两个在classid为2的班级里,所以他们与classid为1的数据经排列组合组合在一起的新数据是不合理的.

那么如果我们在计算笛卡尔积的时候加入: 班级表的classid = 学生表的classid 这个条件 ,那么我们所经过笛卡尔积得到的数据就都是合理的,此时:使笛卡尔积合理的这个条件我们称之为连接条件.

为了演示,我们创建了如上四张表

主体分别是:学生,班级,课程

学生与班级:一对多

学生与课程:多对多

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

(我们之前说过,表示多对多之间的关系的时候是需要一个关联表在中间去进行一个表示)

我们现在来具体的看一下这几张表中的具体数据

学生表

教室表

课程表

成绩表

那么我们首先计算一下学生表与教室表的笛卡尔积

select * from student, classes;

(ps:实际上在数据库中求笛卡尔积的语法是非常简单的)

我们可以看到,上述的这个笛卡尔积中有无意义的数据,那么我们如何将连接条件加入进去呢?

select * from student, classes where student.classes_id = classes.id;

注意:在多表查询的条件下,再去指定条件或者列名的时候,往往通过 表名.列名 的方式指定的.

这样写是因为,进行联合查询的这两个表里,有些列名可能是一样的,如果列名没有重复的,直接写列名也不是不可以,但是建议带上表名,这样可增加代码的可读性

笛卡尔积 + 必要的连接条件 => 多表联合查询.

3.1.1 内连接

select 字段 表1 as 别名1,表2 as 别名2 where 连接条件 and 其他条件;

案例

1.查询许仙同学的成绩

分析:

许仙同学:学生表

成绩:成绩表

那么首先,将这两个表进行笛卡尔积

其次,找关联关系

进一步的添加条件

2.想知道每个班级多少人

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

分析:

主体:同学+成绩

增加一点点细节

4.列出同学的名字+课程名字+分数

select * from student stu join score sco on stu.id=sco.student_id;

前面多个表, 使用 逗号 来分割,现在使用 join 来分割

前面连接条件通过 where 指定, 现在使用 on 来指定

内连接

没有王五

3.1.2 外连接

左外连接

除了展示右连接之外,同时我们还可以看到,左右连接是相对的.

3.1.3 自连接

本质是自己与自己做笛卡尔积,本质是把"行之间的关系"转换成"列之间的关系"(主要是因为SQL只能比较列和列,不能比较行和行)

案例:

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

分析:

我们从上述的成绩表中可以看到,各科的成绩都是行与行之间的关系,而在SQL中仅支持列与列之间进行比较,所以在这里我们要将行与行之间的关系转换为列与列之间的关系.此时就会用到"自连接"

但是如果你直接这样写是会报错的:表名不唯一,言外之意就是:进行笛卡尔积的两张表的表名不能相同.

这也好办,我们可以给表起一个别名

如上图所示

这样就完成了

3.1.4 子查询

一般不推荐使用

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

案例:查询与"不想毕业"同学的同班同学

1.

2.

正常情况下是分两步完成的

进行自连接之后的SQL语句

3.1.5 合并查询 (union/union all)

把多个 select 查询得到的结果集合,合并成一个集合

注意:能合并的前提是要可以对应(类型,列数等)(和列名没关系)

但是上述我们在合并两张表的时候,每一个表中都有一个 张三 但是合并之后就只有一个了,那么如果我们想要看到两个张三,就要用到 union all 了

or 与 union 的区别

or 也是把一些结果合并起来,只不过是局限在一个表里

union 针对任意两个表中的数据进行合并