SQL经典实例（四）插入、更新和删除

插入默认值

定义表的某些列的默认值：

create table D (id integer default 0);

所有的数据库都支持使用default关键字来显式地为某一列指定默认值：

insert into D values(default);

Oracle 8i数据库及更早的版本不支持default关键字，因此没办法为某一列显式地插入默认值。
如果所有的列都预设了默认值，MySQL允许制定一个空白的values列表为所有列创建预设的默认值：

MySQL

insert into D values();

如果数据表中某些列没有设定默认值，而某些列设定了默认值，那么在插入数据的时候之哟啊不把预设了默认值的列写入insert列表，就可以方便地为其插入默认值。考虑如下表：

create table DD (id integer default 0, foo varchar(10));

在insert列表中只指定foo列：

insert into DD (foo) values ('Bar');

也可以使用null值覆盖默认值：

insert into DD (id, foo) values (null, 'Brighten');

复制数据到另一个表

insert into dept_east (deptno, dname, loc)
select deptno, dname, loc
    from dept
where loc in ('NEW YORK', 'BOSTON');

注意在insert列表后没有values关键字。

复制表定义

为DEPT表创建一个副本DEPT_2，但是只要表结构，不复制数据：

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

多表插入

Oracle可以使insert all或者insert first语法
Oracle

insert all
    when loc in ('NEW YORK', 'BOSTON') then
        into dept_east (deptno, dname, loc) values (deptno, dname, loc)
    when loc in ('CHICAGO') then
        into dept_mid (deptno, dname, loc) values (deptno, dname, loc)
    else 
        into dept_west (deptno, dname, loc) values (deptno, dname, loc)
select deptno, dname, loc
    from dept;

insert all与insert first的区别就是：一旦WHEN-THEN-ELSE的结果为真，insert first会立即结束评估，insert all则会逐一评估所有的条件，而不论前面的测试结果是否为真，所以使用insert all可能把同一行数据插入到多个表中。
也就是说，当使用insert first时，如果满足某一个when或else条件，判断过程就会立即返回，不会再继续评估其他判断条件是否成立，保证每次过程都只有一条记录插入到一张表中。

禁止插入特定列

如果想要阻止用户或者错误的软件应用程序在某些列中插入数据，可以创建一个视图，只暴露那些你希望暴露的列，然后强制所有的insert语句都被传送到该视图。向一个简单视图插入数据，数据库服务器会把它转换为针对基础表的插入操作。
例如，创建如下视图：

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

执行下列插入语句

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

会被翻译成：

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

当相关行存在时更新记录

例如，如果一个员工出现在EMP_BONUS表中， 将他的工资（在EMP表中）上涨20%。

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

也可以使用exists关键字：

update emp
    set sal = sal*1.2
  where exists (select null 
                    from emp_bonus
                 where emp.empno = emp_bonus.empno);

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

MySQL & Oracle

update emp set (e.sal, e.comm) = (select ns.sal, ns.sal/2
                                    from new_sal ns
                                  where ns.deptno = e.deptno)
    where exists (select null
                    from new_sal ns
                  where ns.deptno = e.deptno);

Oracle 更新内嵌视图

update (
    select e.sal as emp_sal, e.comm as emp_comm,
        ns.sal as ns_sal, ns.sal/2 as ns_comm
      from emp e, new_sal ns
    where e.deptno = ns.deptno
) set emp_sal = ns_sal, emp_comm = ns_comm;

合并记录

如果想根据相关记录是否已经存在来插入、更新或删除一个表的记录，例如，如果记录存在，则更新它，如果不存在，则插入一条新纪录；如果更新之后的记录不满足某个条件，则删除它。
考虑如下条件来修改emp_commission表：
1） 如果emp_commission表的员工数据在emp表里也存在相关记录，则更新业务提成comm为1000；
2）对于所有可能会把comm列更新为1000的员工，如果他们的sal低于2000，则删除相关记录（他们不应该存在于emp_commission表中；
3）否则，就要从emp表中取出相应的empno，ename，deptno并插入到emp_commission表。

Oracle

merge into emp_commission ec
using (select * from emp) emp
   on (ec.empno = emp.empno)
 when matched then
      update set ec.comm = 1000
      delete where (sal < 2000)
 when not matched then
      insert (ec.empno, ec.ename, ec.deptno, ec.comm)
      values (emp.empno, emp.ename, emp.deptno, emp.comm);

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

想从表里删除一些记录，因为在另一个表里不存在与这些记录相匹配的数据。例如，一些员工所属的部门其实并不存在，你希望删除这些员工。

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

或者

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

删除重复记录

考虑如下表dupes数据：

对于每一组重复的名字，你希望保留任意一个ID,并删除其余的。

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

《SQL经典实例》第四章