- 13.2.8 DML语句语法 3.delete from语句
- 删除主表记录时级联删除从表对应记录
- 删除主表记录将从表对应记录设为null
- 修改表定义以支持级联删除
13.2.8 DML语句语法 3.delete from语句
delete from语句用于删除指定数据表的记录。使用delete from语句删除时不需要指定列名，因为总是整行地删除。
使用delete from语句可以一次删除多行，删除哪些行采用where子句限定，只删除满足where条件的记录。
当没有where子句限定时将会把表里的全部记录删除。
语法格式
delete from语句的语法格式如下：
delete form table_name
[where condition];
|
部分删除
可以使用where条件来限定只删除指定记录，如下SQL语句所示：
delete from student_table2
where student_id<10;
|
运行结果:
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表中的记录全部删除：
delete from student_table2;
|
运行结果:
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。
修改表定义以支持级联删除
表的定义如下:
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),表示索引.
删除原有的外键
alter table student_table2
drop foreign key student_table2_ibfk_1;
|
结果如下:
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这项还没删除掉,我猜它是索引(因为可以用删除索引的语法删除)
重新添加外键使用默认索引
alter table student_table2
add foreign key(java_teacher)
references teacher_table2(teacher_id)
on delete cascade ;#也可以用on delete set null
|
运行结果:
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>
|
好的现在级联删除设置完毕
疑问
修改外键约束时,一定要先删除原来的外键约束,然后在添加新的外键约束来实现吗
测试级联删除
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_table2的java_teacher属性参照teacher_table2的teacher_id属性.
当删除表teacher_table2中teacher_id=1的记录时,将会级联删除student_table2表中java_teacher=1的记录。
效果如下:
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>
|
