《SQL经典实例》四——插入、更新和删除

第四章：插入、更新和删除

4.1插入新纪录

你希望向某个表中插入一条新记录。例如，你想插入一条新记录到 DEPT 表里。DEPTNO 值应该为 50，DNAME 设为 PROGRAMMING，而 LOC 则是 BALTIMORE。

insert into dept(deptno,dname,loc)
values(50,'programming','batltomore')

4.2 插入默认值

可以定义表的某些列的默认值。你想在插入一行的时候使用预设的默认值，而不是指定的
值。考虑下面的表。

create table D (id integer default 0)

你希望插入 0，并且不想显式地在 INSERT 语句的 VALUES 列表里指定 0。你只希望显式地插入默认值，而不管预设的默认值是什么。
所有数据库都支持使用 DEFAULT 关键字来显式地为某一列指定默认值，部分数据库还提供了其他方法来解决这一问题。

insert into D values (default)

4.3 使用Null覆盖默认值

想要插入一列，该列有默认值，但你想将其设置为 Null 而不是默认值。考虑如下的表。
create table D (id integer default 0, foo VARCHAR(10))
你希望为 ID 列插入 Null。

insert into d (id, foo) values (null, 'Brighten')

直接在values中指定值。

4.4　复制数据到另一个表

你希望把 DEPT 表的部分数据复制到 DEPT_EAST 表。假设 DEPT_EAST 表已经被创建好了，其结构与 DEPT 表相同（有同样的列和数据类型），而且该表当前不含任何数据。

在 INSERT 语句后面附加一个用来检索目标数据的查询语句。
insert into dept_east (deptno,dname,loc)
select deptno,dname,loc
 from dept
 where loc in ( 'NEW YORK','BOSTON' )

4.5　复制表定义

你希望为 DEPT 表创建一个副本，命名为 DEPT_2。但是，你只想复制它的表结构，而不复制数据。
使用 CREATE TABLE 语句和一个不返回任何数据的子查询。

create table dept_2 
as 
select *
from dept
where 1 = 0

使用 Create Table As Select ( 简写为 CTAS) 语句时，除非为 WHERE 子句指定一个不可能为真的条件，否则，查询结果集将会被写入新表。本例中，WHERE 子句后面的表达式 1=0 会导致查询不返回任何结果。因此，上述 CTAS 语句的执行结果就是一张空表，该表的列取决于 SELECT 子句的查询结果。

## 4.6　多表插入
你希望把 DEPT 表的数据分别插入到 DEPT_EAST 表、DEPT_WEST 表和 DEPT_MID 表。这 3 个表与 DEPT 表的结构相同（相同的列和数据类型），并且当前不含任何数据。

MY SQL尚不支持多表插入，等我再查查资料吧。

4.7　禁止插入特定列

你希望一个程序插入数据到 EMP 表，但只允许它插入 EMPNO、ENAME 和 JOB 列。
创建一个视图，只暴露那些你希望暴露的列。然后强制所有 INSERT 语句都被传送到该
视图。
例如，下面的语句创建了一个视图，暴露出 EMP 表的三个列。

create view new_emps as
select empno, ename, job
 from emp

向一个简单视图插入数据，数据库服务器会把它转换为针对基础表的插入操作。例如，下
面的 INSERT 语句。

insert into new_emps
 (empno ename, job)
 values (1, 'Jonathan', 'Editor')

会被翻译成：

insert into emp
 (empno ename, job)
 values (1, 'Jonathan', 'Editor')

4.8　更新记录

你想更新一个表的部分记录或者全部记录。例如，你可能希望为部门编号为 20 的员工统
一加薪 10%。

update emp
set SAL=sal*1.1
where deptno=20

4.9　当相关行存在时更新记录

你想更新一个表的部分行，而更新条件取决于另一个表中是否有与之相关的行。例如，如
果一个员工出现在 EMP_BONUS 表中，你希望把他的工资（在 EMP 表中）上涨 20%。

update emp
set SAL=sal*1.2
where empno in(select empno from emp_bonus)

4.10 使用另一个表的数据更新记录

你想使用另一个表的值来更新当前的表。例如，现在有一个 NEW_SAL 表，存储了部分员工调整后的工资。DEPTNO 列是 NEW_SAL 表的主键。
你希望使用 NEW_SAL 表的数据来更新 EMP 表中部分员工的工资和业务提成。如果 EMP 表中的 DEPTNO 列和 NEW_SAL 表中的 DEPTNO 列相匹配，则将 EMP表中的 SAL 列更新为 NEW_SAL 表中的 SAL 列，EMP 表中的 COMM 列更新为 NEW_SAL 表中 SAL列的 50%。

update emp e set(sal,comm)=(select ns.sal,ns.sal/2
                            from nem_sal ns
                            where ns.deptno=e.deptno)
                            
where exists(select null   #WHERE 字句中增加关联子查询，确保其他行不会被误改为NULL，只修改匹配上的行
                    from nem_sal ns
                    where ns.deptno=emp.deptno)

## 4.11　合并记录
你想根据相关记录是否已经存在来插入、更新或删除一个表的记录。（如果记录存在，则
更新它；如果不存在，则插入一条新记录；如果更新之后的记录不满足某个条件，则删除
它。）例如，你希望按照如下的条件来修改 EMP_COMMISSION 表。
• 如果EMP_COMMISSION表的员工数据在EMP表里也存在相关记录，则更新其业务提成（COMM）
为 1000。
• 对于所有可能会把 COMM 列更新为 1000 的员工，如果他们的 SAL 低于 2000，则删除相关
记录（他们不应该存在于 EMP_ COMMISSION 表中）。
• 否则，就要从 EMP 表取出相应的 EMPNO、ENAME 和 DEPTNO，并插入 EMP_COMMISSION 表。

4.12　删除全表记录

把 EMP 表中的全部记录都删除掉。

delete from emp

4.13 删除指定记录

使用 DELETE 语句和 WHERE 子句，其中 WHERE 子句用于指定要删除的行。例如，下面的语句将删除部门编号为 10 的全部员工数据。

delete from emp where deptno = 10

4.14删除单行记录

例如，如下语句将删除员工 CLARK（EMPNO 等于
7782）的数据。

delete from emp where empno = 7782

4.15　删除违反参照完整性的记录

你想从表里删除一些记录，因为在另一个表里不存在与这些记录相匹配的数据。例如，一
些员工所属的部门其实并不存在，你希望删除这些员工。
使用 NOT EXISTS 谓词和子查询来确认部门编号的有效性。

delete from emp
 where not exists (
 select * from dept
 where dept.deptno = emp.deptno
)

或者，也可以使用 NOT IN 谓词。

delete from emp
where deptno not in (select deptno from dept)

4.16　删除重复记录

select * from dupes order by 1
 ID NAME
---------- ----------
 1 NAPOLEON
 2 DYNAMITE
 3 DYNAMITE
 4 SHE SELLS
 5 SEA SHELLS
 6 SEA SHELLS
 7 SEA SHELLS

对于每一组重复的名字，例如 SEA SHELLS，你希望保留任意一个 ID，并删除其余的。不论删除 5 和 6，或者 5 和 7，或者 6 和 7，最终你只想要一条 SEA SHELLS 记录。

使用子查询和诸如 MIN 这样的聚合函数，任意选择并保留一个 ID（本例中 NAME 相同的情况下只有最小的 ID 会被保留）。

 delete from dupes
 where id not in ( select min(id)
 from dupes
 group by name )

4.17　删除被其他表参照的记录

考虑如下所示的 DEPT_ACCIDENTS 表，该表的每一行数据代表一起制造业生产事故。每一行都记录了发生事故的部门以及事故的类型。

select * from dept_accidents
 DEPTNO ACCIDENT_NAME
---------- --------------------
 10 BROKEN FOOT
 10 FLESH WOUND
 20 FIRE
 20 FIRE
 20 FLOOD
 30 BRUISED GLUTE

找出发生过 3 次以上事故的部门，然后再删除在上述部门工作的员工。

 delete from emp
2 where deptno in ( select deptno
3 from dept_accidents
4 group by deptno
5 having count(*) >= 3 )