只知道删除单张表的数据？不知道删除多张表的数据？

^O^——

已于 2023-03-04 08:21:10 修改

阅读量3.5k

点赞数 3

于 2023-03-02 00:44:13 首次发布

本文链接：https://blog.csdn.net/weixin_62332711/article/details/129248364

版权

一些废话

可能在某某一天，你在删除表的数据的时候，不想一张表一张表的去删除，想把两个表的数据同时删除；然后你就会去搜索，然后你就很有很有很有很有可能会看到 me 的这篇优质（呸！！！）文章了~

嗯，没错，是这么个道理......

本文章赞助商：DataGrip（不是广告哇！！！Σ( ° △ °|||)︴）。

一、删除多表数据

我这里有两张表，分别是学生表（student）和学生成绩表（achievement）。

/*1、学生表*/
create table student(
    ID int,
    Name varchar(20)
);

/*给学生表插入数据*/
insert into student(ID, Name)
values (1,'海绵宝宝'),
       (2,'派大星'),
       (3,'章鱼哥'),
       (4,'蟹老板'),
       (5,'痞老板'),
       (6,'珊迪'),
       (7,'小蜗');



/*2、学生成绩表*/
create table achievement(
    ID int primary key auto_increment,
    Mark int
);

/*给学生成绩表插入数据*/
insert into achievement(ID, Mark)
values (1,91),
       (2,97),
       (3,88),
       (4,87),
       (5,84),
       (6,80),
       (7,81);

/*查询两张表的数据*/
select * from student;
select * from achievement;

输出结果：

student（学生表）

achievement（学生成绩表）

然后，我有个需求：要删除两个表中关于“小蜗”的记录。

如果你没有学过多张表的数据删除的话，你就会分别从两张表中去删除：

delete from student where Name = '小蜗';

delete from achievement where ID = 7;

当你学了多张表的数据删除的话，你就可以把两个表的数据根据条件连接起来进行删除：

语法：delete 表1,表2 from 表1,表2 where 条件；

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Student.Name = '小蜗';

要注意的点：

1.多张表删除数据，from 前面和 from 后面要接删除的表名（要删除几张表 from 前后就写几张表的表名，表名前后个数要保持一致）；

如下：

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Student.Name = '小蜗';

2.多张表删除数据，不能给表取别名，只能用表名，如果有 where 连接条件也只能用表名来进行连接。

/*查询两张表的数据*/

select * from student;
select * from achievement;

删除成功！！！

输出结果：

student（学生表）

achievement（学生成绩表）

“小蜗” -1。喵~

小蜗是只猫！！！震惊我 100 年！！！😱😱😱

二、删除多表数据（两张表有外键关系）

打个预防针：这里面会涉及到外键知识，只想看两张表有外键关系的删除多表数据，直接看 2.2、解决方法就可以了。

2.1、设置外键

方法一：SQL 语句

语法：alter table 子表 add constraint 外键的名称 foreign key(子表中要建立成外键的字段名) references 父表(父表中外键要关联的字段名);

子表：建立外键的表；

外键的名称：给你要的外键取个名字；

父表：外键所关联的表；

因为要演示两张表有外键关系的删除，所以我把 student（学生表）的 ID 字段设置为外键字段，关联 achievement（学生成绩表）的主键 ID 字段；

alter table student add constraint fk_student_ID foreign key(ID) references achievement(ID);

如图：

注意，科普一下：外键不一定要是另一个表的主键（primary key：主键约束），也可以是另一个表的唯一值（unique：唯一约束）。

因为外键和另一张表所关联的字段，值是不能重复（而主键约束是一行数据的唯一标识，非空且唯一；唯一约束也能保证该字段的所有数据都是唯一、不重复的）；

并且，建立外键最重要的两点是：

☝️、双方字段类型要保持一致，字段类型长度可以不一致。

演示：

我把 student（学生表）要建立成外键的字段 ID 改成 tinyint 小整形类型，外键所关联的achievement（学生成绩表）的字段主键 ID 还是 int 类型。

/*学生表*/
create table student(
ID tinyint,
Name varchar(20)
);

/*学生成绩表*/
create table achievement(
ID int primary key auto_increment,
Mark int
);

插入外键：

alter table student add constraint fk_student_ID foreign key(ID) references achievement(ID);

会看到，他报错了！！！报的是外键约束不正确的错误！！！

[HY000][1005] Can't create table `test2`.`student` (errno: 150 "Foreign key constraint is incorrectly formed")

✌️、双方数据要保持一致，具体演示点击下面链接：

[23000][1452] Cannot add or update a child row: a foreign key constraint fails (`test2`.`#sql-1238_5_^O^——的博客-CSDN博客

方法二：图形化界面

1、点击要设置成为外键的表（子表），右键 Modify Table...；

2、选择 ①Foreign Keys，点击 ②“+” 号图标；

3、

①Name：给外键取个名称；

②Traget table：父表名称；

③Update rule 和 ④Delete rule：外键的更新和删除行为；

3.1.

然后在 Columns：下方，点击 ①“+” 号图标添加外键相关联的两个字段名（点击 ②“-” 号图标删除），③Form：子表的外键字段名；④To：父表中子表外键所关联的字段名；最后，点击 ④Execute 就创建完成啦。

还有要补充的一点是：如果你在创建外键后，外键起不到任何效果的话，可以参考该文章：MySQL 数据库创建不了外键约束_^O^——的博客-CSDN博客

通过这两种方法的其中一种方法建立好外键关系后。

当我从子表 student（学生表）中去删除两张表数据的时候，可以删除成功：

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Student.ID = 7;

如下：在两张表中 ID 为 7 的数据都被删除了。

子表（student：学生表）

父表（achievement：学生成绩表）

可当我从父表 achievement（学生成绩表）中去删除两张表数据的时候，就不可以删除成功勒：

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Achievement.ID = 6;

那是因为，在添加了外键之后，再删除/更新父表的数据时会产生约束行为，也就是删除/更新行为。

语法：

在外键约束的基础语法的基础上 + on update 删除/更新行为的语法 on delete 删除/更新行为的语法;

删除/更新行为有以下 5 种语法，简单介绍一下😎：

①no action（创建了外键约束默认的删除/更新行为）：不允许删除/更新父表的数据；

②restrict：跟 no action 效果一样；

③cascode：允许删除/更新父表的数据；

④set null：允许删除/更新父表的数据，但会把外键字段的值变成 null（前提是该外键的字段是允许为 null 的，不能设置成 not null）；

演示：删除父表 ID 为 7 的值。

提示：如果要修改外键默认的删除/更新行为（no action），最好先把之前的外键删除，再添加删除/更新行为（不然会出现外键重复）。

语法：alter table 子表 drop foreign key 外键名称;

演示：

alter table student drop foreign key fk_student_ID;

1、删除原先的外键；

alter table student drop foreign key fk_student_ID;

2、把默认的 no action 删除/更新行为改成 set null；

alter table student add constraint fk_student_ID foreign key(ID) references achievement(ID) on update set null on delete set null;

原先表数据：

父表（achievement：学生成绩表）

子表（student：学生表）

3、删除父表 ID 为 7 的值；

delete from achievement where ID = 7;

删除后的表数据：

父表（achievement：学生成绩表）

子表（student：学生表）

可以看到因为父表 ID 为 7 的删除了值，所以子表（student：学生表）中对应的 ID 为 7 的外键值也变成 null 了。

⑤set default：允许删除/更新父表的数据，但会把外键字段的值变成 default 默认的值；

（但是，InnoDB 引擎是不支持的；而 MySQL 数据库只有 InnoDB 引擎才允许使用外键，所以 set default 对 MySQL 数据库不起作用）；

演示：

1、在图形化界面把 Update rule 和 Delete rule 外键的更新和删除行为改为 set default 会报错；

2、使用 SQL 语句进行修改删除/更新行为也报错了；

2.2、解决方法

方法一（简单版）：关闭外键约束

删除前表的数据：

子表（student：学生表）

父表（achievement：学生成绩表）

1、关闭数据库的外键约束；
set foreign_key_checks = 0;

2、再删除多张表的数据；

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Achievement.ID = 6;

3、删除完之后开启数据库的外键约束；
set foreign_key_checks = 1;

删除后表的数据：

子表（student：学生表）

父表（achievement：学生成绩表）

“珊迪” -1。

方法二（麻烦版）：修改删除/更新行为

第一种：图形化界面

1、选择建立了外键的表（student：学生表），右键 Modify Table...；

2、点击 ①Foreign Keys，点击 ②之前插入的外键约束；

3、把 ①Update rule：和 ②Delete rule：的选项全都改成 cascode（允许删除/更新父表的数据），然后点击 ③Execute（如果不可以修改就删除之前的外键，再重新新建外键，修改 ①Update rule：和 ②Delete rule：的选项）；

4、删除两张表中 ID 为 5 的数据。

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Achievement.ID = 5;

删除前表的数据：

子表（student：学生表）

父表（achievement：学生成绩表）

删除后表的数据：

子表（student：学生表）

父表（achievement：学生成绩表）

“痞老板” -1。

第二种：图形化界面+SQL语句（说了和没说一样）

1、选择建立了外键的表（student：学生表），右键 Modify Table...；

2、点击 ①Foreign Keys，点击 ②之前插入的外键约束，点击 ③“-” 号删除该外键；

3、使用 SQL 语句把默认的 no action 删除/更新行为改成 cascode；

alter table student add constraint fk_student_ID foreign key(ID) references achievement(ID) on update cascade on delete cascade;

4、删除两张表中 ID 为 4 的数据。

delete Student, Achievement from Student, Achievement where Student.ID = Achievement.ID and Achievement.ID = 4;

删除前表的数据：

子表（student：学生表）

父表（achievement：学生成绩表）

删除后表的数据：

子表（student：学生表）

父表（achievement：学生成绩表）

“蟹老板” -1。我喜欢钱💰~

好了，删到最后，就只剩下了邻居三人组了，拜拜啦~

完。。。

^O^——

关注

3
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
只知道删除单张表的数据？不知道删除多张表的数据？

可能在某某一天，你在表删除表数据的时候，不想一张表一张表的去删除，想把两个表的数据同时删除；然后你就会去搜索，然后你就很有很有很有很有可能会看到 me 的这篇优质（呸！！！）文章了~嗯，没错，是这么个道理....../*1、学生表*/ID int,/*给学生表插入数据*/values (1,'海绵宝宝'),(2,'派大星'),(3,'章鱼哥'),(4,'蟹老板'),(5,'痞老板'),(6,'珊迪'),(7,'小蜗');/*2、学生成绩表*/
复制链接

扫一扫