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 ;
|
运行结果:
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_table2
的java_teacher
属性参照teacher_table2
的teacher_id
属性.
当删除表teacher_table2
中teacher_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语句