0%

13.2.8 DML语句语法 3.delete from语句

13.2.8 DML语句语法 3.delete from语句

delete from语句用于删除指定数据表的记录。使用delete from语句删除时不需要指定列名,因为总是整行地删除。
使用delete from语句可以一次删除多行,删除哪些行采用where子句限定,只删除满足where条件的记录。
当没有where子句限定时将会把表里的全部记录删除。

语法格式

delete from语句的语法格式如下:

1
2
delete form table_name
[where condition];

部分删除

可以使用where条件来限定只删除指定记录,如下SQL语句所示:

1
2
delete from student_table2
where student_id<10;

运行结果:

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
mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 1 | 张三 | 2 |
| 9 | xyz | NULL |
| 10 | abx | NULL |
| 11 | abx | NULL |
| 13 | 可以插入 | NULL |
| 15 | 可以插入 | 1 |
+------------+--------------+--------------+
6 rows in set

mysql> delete from student_table2
where student_id<10;
Query OK, 2 rows affected

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 10 | abx | NULL |
| 11 | abx | NULL |
| 13 | 可以插入 | NULL |
| 15 | 可以插入 | 1 |
+------------+--------------+--------------+
4 rows in set
mysql>

全部删除

如下SQL语句将会把student_table2表中的记录全部删除:

1
delete from student_table2;

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 10 | abx | NULL |
| 11 | abx | NULL |
| 13 | 可以插入 | NULL |
| 15 | 可以插入 | 1 |
+------------+--------------+--------------+
4 rows in set

mysql> delete from student_table2;
Query OK, 4 rows affected

mysql> select * from student_table2;
Empty set

mysql>

存在外键约束的情况

当主表记录被从表记录参照时,主表记录不能被删除,只有先将从表中参照主表记录的所有记录全部删除后,才可删除主表记录

删除主表记录时级联删除从表对应记录

定义外键约束时定义了主表记录和从表记录之间的级联删除on delete cascade,这样删除主要记录时,从表的相关记录也被删除。

删除主表记录将从表对应记录设为null

on delete set null用于指定当主表记录被删除时,从表中参照该记录的从表记录把外键列的值设为null

修改表定义以支持级联删除

表的定义如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
show create table student_table2;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(255) DEFAULT NULL,
`java_teacher` int(11) DEFAULT NULL,
PRIMARY KEY (`student_id`),
KEY `java_teacher` (`java_teacher`),
CONSTRAINT `student_table2_ibfk_1`
FOREIGN KEY (`java_teacher`)
REFERENCES `teacher_table2` (`teacher_id`)
ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

这个表目前还不支持级联删除

其中KEY java_teacher (java_teacher),表示索引.

删除原有的外键

1
2
alter table student_table2
drop foreign key student_table2_ibfk_1;

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> alter table student_table2
drop foreign key student_table2_ibfk_1;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student_table2;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(255) DEFAULT NULL,
`java_teacher` int(11) DEFAULT NULL,
PRIMARY KEY (`student_id`),
KEY `java_teacher` (`java_teacher`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到外键定义已经删除掉了,KEY这项还没删除掉,我猜它是索引(因为可以用删除索引的语法删除)

重新添加外键使用默认索引

1
2
3
4
alter table student_table2
add foreign key(java_teacher)
references teacher_table2(teacher_id)
on delete cascade ;#也可以用on delete set null

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> alter table student_table2
add foreign key(java_teacher)
references teacher_table2(teacher_id)
on delete cascade ;#也可以用on delete set null
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
-> show create table student_table2;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(255) DEFAULT NULL,
`java_teacher` int(11) DEFAULT NULL,
PRIMARY KEY (`student_id`),
KEY `java_teacher` (`java_teacher`),
CONSTRAINT `student_table2_ibfk_1`
FOREIGN KEY (`java_teacher`)
REFERENCES `teacher_table2` (`teacher_id`)
ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql>

好的现在级联删除设置完毕

疑问

修改外键约束时,一定要先删除原来的外键约束,然后在添加新的外键约束来实现吗

测试级联删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select * from student_table2;
Empty set

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 1 | 孙悟空 |
| 2 | 美猴王 |
+------------+--------------+
2 rows in set

mysql> insert into student_table2
values(null,'小明',1);
Query OK, 1 row affected

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 16 | 小明 | 1 |
+------------+--------------+--------------+
1 row in set
mysql>

由于表student_table2java_teacher属性参照teacher_table2teacher_id属性.
当删除表teacher_table2teacher_id=1的记录时,将会级联删除student_table2表中java_teacher=1的记录
效果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> delete from teacher_table2
where teacher_id=1;
Query OK, 1 row affected

mysql> select * from student_table2;
Empty set

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 2 | 美猴王 |
+------------+--------------+
1 row in set
mysql>

原文链接: 13.2.8 DML语句语法 3.delete from语句