3.9 数据库的修改 3.9.1 删除

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 2.5k 阅读时长 ≈ 2 分钟

3.9 数据库的修改

目前为止我们的注意力集中在对数据库的信息抽取上。现在我们将展示如何用SQL来增加、删除和修改信息。

3.9.1 删除

删除指定元组

删除请求的表达与査询非常类似。我们只能删除整个元组,而不能只删除某些属性上的值。SQL用如下语句表示删除:

1
2
delete from R
where P;

其中P代表一个谓词,R代表一个关系。 delete语句首先从关系R中找出所有使P(t)为真的元组t,然后把它们从R中删除。

删除所有元组

如果省略where子句,则R中所有元组将被删除。

一个delete只能删除一个关系

注意delete命令只能作用于一个关系。如果我们想从多个关系中删除元组,必须在每个关系上使用一条delete命令。
where子句中的谓词可以和select命令的where子句中的谓词一样复杂。在另一种极端情况下, where子句可以为空,请求:

1
delete from instructor;

将删除instructor关系中的所有元组。 instructor关系本身仍然存在,知识它变成空的关系而已了.
下面是SQL删除请求的一些例子:
从instructor关系中删除与Finance系教师相关的所有元组。

1
2
delete from instructor
where dept_name ='Finance';
1
2
3
mysql> delete from instructor
where dept_name ='Finance';
Query OK, 2 rows affected

删除所有工资在13000美元到15000美元之间的教师。

1
2
delete from instructor
where salary between 13000 and 15000;
1
2
3
mysql> delete from instructor
where salary between 13000 and 15000;
Query OK, 0 rows affected

从instructor关系中删除所有在位于 Watson大楼的系工作的教师元组。

1
2
3
4
5
6
delete from instructor
where dept_name in(
    select dept_name
    from department
    where building ='Watson'
);
1
2
3
4
5
6
7
mysql> delete from instructor
where dept_name in(
    select dept_name
    from department
    where building ='Watson'
);
Query OK, 3 rows affected

此delete请求首先找出所有位于Watson大楼的系,然后将属于这些系的instructor元组全部删除。

delete的where子句中可以嵌套查询

注意,虽然我们一次只能从一个关系中删除元组,但是通过在delete的where子句中嵌套select-from-where,我们可以引用任意数目的关系。 delete请求可以包含嵌套的select,该select引用待删除元组的关系。
例如,假设我们想删除工资低于大学平均工资的教师记录,可以写出如下语句

1
2
3
4
delete from instructor
where salary < (
select avg(salary) from instructor
);

该delete语句首先测试instructor关系中的每一个元组,检查其工资是否小于大学教师的平均工资。然后删除所有符合条件的元组,即所有低于平均工资的教师。在执行任何删除之前先进行所有元组的测试是至关重要的,因为若有些元组在其余元组未被测试前先被删除,则平均工资将会改变,这样delete的最后结果将依赖于元组被处理的顺序

MySQL写法

上面的代码在MySQL里测试会报错:You can't specify target table 'instructor' for update in FROM clause
因为在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，
解决办法是，将select得到的结果，再通过中间表select一遍，这样就规避了错误，

1
2
3
4
5
6
delete from instructor
where salary < (
    select avg_salary from (
        select avg(salary) as avg_salary from instructor
    ) I
);
1
2
3
4
5
6
7
mysql> delete from instructor
where salary < (
    select avg_salary from (
        select avg(salary) as avg_salary from instructor
    ) I
);
Query OK, 5 rows affected