DELETE 语句中的子查询

如同前面提到的其他语句一样，子查询还可以同 DELETE 语句一起使用。

1 实例

现在有与 teachers 表拥有相同结构和数据的 teachers_bkp 表。

下面的示例将从 teachers 表中删除所有 age（年龄）小于 20 岁的老师：

DELETE FROM teachers 
WHERE id IN ( 
            SELECT id 
            FROM teachers_bkp 
            WHERE age < 20 
        );

执行后 teachers 表数据为：

mysql> DELETE FROM teachers
    -> WHERE id IN (
    ->             SELECT id
    ->             FROM teachers_bkp
    ->             WHERE age < 20
    ->         );
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM teachers;
+----+------------------+---------------------------+-----+---------+
| id | name             | email                     | age | country |
+----+------------------+---------------------------+-----+---------+
|  1 | Eastern Heretic  | [e-mail] |  20 | CN      |
|  2 | Northern Beggar  | [e-mail]    |  21 | CN      |
|  3 | Western Venom    | [e-mail]     |  28 | USA     |
|  4 | Southern Emperor | [e-mail]   |  21 | CN      |
+----+------------------+---------------------------+-----+---------+
4 rows in set (0.00 sec)

2. 总结

其基本语法如下所示：

DELETE FROM table_name
WHERE column_name OPERATOR 
   (SELECT column_name
   FROM table_name  [WHERE] )

练习题：DELETE 子查询

现在有与 courses 表拥有相同结构和数据的 courses_bkp 表。现在要将 courses 表中所有 student_number（学生人数） 大于等于 200 的课程删除掉。