【sql查询与优化】4.删除插入与更新

注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。

案例中可能会用到的表:
EMP表的详细:


查询所有信息，

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------

      1110 张三                 主管                     3322 12-3月 -14      5200                    20

      1111 李四                 销售                     3321 03-11月-15      3400        500         30

      1112 王五                 销售                     3321 25-4月 -12      4400        800         30

      1113 赵二                 后勤                     3320 30-5月 -11      3450                    40

      1114 李磊磊               会计                     3319 22-12月-15      2500                    50

      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30

      1116 林建国               主管                     3322 22-1月 -16      5700                    20

      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40

      1118 沈倩                 会计                     3319 06-5月 -10      2100                    50
已选择9行。

DEPT表的详细:

查询所有信息

SQL> select * from dept t;

DEPTNO    DNAME
--------         --------
3322       管理部门

3321       销售部门

3320       后勤部门

3319       金融部门

1.插入新纪录
我们先建立测试表，各列都有默认值

create table test(
 c1 varchar2(10) default '默认1',
 c2 varchar2(10) default '默认2',
 c3 varchar2(10) default '默认3',
 c4 date default sysdate
);

新增数据如下:

insert into test(c1,c2,c3) values(default,null,'手输值');

然后查询test表所有数据:

SQL> select * from test;

C1           C2          C3           C4
------------ ----------- ----------- --------------
默认1                    手输值      07-4月 -16

注意:
(1)如果insert语句中没有含默认值的列，则会添加默认值，如c4列。
(2)如果包含有默认值的列，需要用default关键字，才会添加默认列，如c1列。
(3)如果已显示设定了NULL或其他值，则不会在生成默认值，如c2、c3列。
建立表时，有时明明设定了默认值，可生成的数据还是NULL，原因在于我们在代码中不知不觉地加入了NULL。

2.阻止对某几列插入
我们建立的表中c4列默认为sysdate，这种列一般是为了记录数据生成的时间，不允许手动录入。那么系统控制不到位，或者管理不到位，经常会有手动录入的情况发生，怎么办？

我们可以建立一个不包含c4的列的view，新增数据时通过这个view就可以。

create or replace view v_test as select c1,c2,c3 from test;

视图已创建。

insert into v_test(c1,c2,c3) values('手输c1',NULL,'勿改c4');

已创建一行。

查看数据:

SQL> select * from test;

C1                   C2                   C3                   C4
-------------------- -------------------- -------------------- --------------
默认1                                     手输值               07-4月 -16
手输c1                                    勿改c4               07-4月 -16

注意:通过view新增数据，不能再使用关键字default。

3.复制表的定义及数据
我们可以用以下语句复制表test:

create table test2 as select * from test;

也可以先复制表的定义，再新增数据:

create table test2 as select * from test where 1=2;

注意:复制的表不包含默认值等约束信息，使用这种方式复制表后，需要重建默认值以及索引和约束等信息。

看看我们复制的test2:

SQL> desc test2

 名称         否为空? 类型
 ------------ -------- -------------
 C1                     VARCHAR2(10)
 C2                     VARCHAR2(10)
 C3                     VARCHAR2(10)
 C4                     DATE

复制表之后就可以新增数据了:

SQL> insert into test2 select * from test;

已创建2行。

SQL> select * from test2;

C1                   C2                   C3                   C4
-------------------- -------------------- -------------------- --------------
默认1                                     手输值               07-4月 -16
手输c1                                    勿改c4               07-4月 -16


4.用with check option限制数据录入
当约束条件比较简单时，可以直接加在表中，如工资必须大于0:

SQL> alter table emp add constraints ch_sal check(sal>0);

表已更改。

但是有一些复杂或者特殊的约束条件是不能这样放在表里的，如雇用日期大于当前日期:

SQL> alter table emp add constraints ch_hiredate check(hiredate>=sysdate);
alter table emp add constraints ch_hiredate check(hiredate>=sysdate)
                                                            *
第 1 行出现错误:
ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误

这时我们可以使用加了with check option 关键字的view来达到目的。
下面的实例中，我们限制了不符合内联视图条件的数据(sysdate+1)：

SQL> insert into
         (select empno,ename,hiredate
           from emp
          where hiredate <= sysdate with check option)
          values
          (9999,'test',sysdate+1);
      from emp
           *
第 3 行出现错误:
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

发现报错了，因为里面有关键字“with check option”,所以insert的数据不符合其中的条件(hiredate<=sysdate)时，不就允许使用insert。
我们通过报错信息可以看到，语句(select empno,ename,hiredate from emp where hiredate <= sysdate with check option)被当做一个视图处理。

当我们插入的数据符合条件(hiredate<=sysdate)时，就允许使用insert。

SQL> insert into
         (select empno,ename,hiredate
           from emp
          where hiredate <= sysdate with check option)
          values
          (9999,'test',sysdate-1);

已创建 1 行。
查看一下:

SQL> select * from emp e where e.hiredate like sysdate-1;

     EMPNO ENAME    JOB        MGR        HIREDATE       SAL         COMM     DEPTNO
---------- -------- ---------- ---------- -------------- ---------- -------   -------
      9999 test                           06-4月 -16

5.多表插入语句
多表插入语句分为以下四种:
(1)无条件的insert
(2)有条件insert all
(3)转置insert
(4)有条件insert first
首先建立两个测试用表:

create table emp1 as select empno,ename,job from emp where 1=2;
create table emp2 as select empno,ename,deptno from emp where 1=2;

无条件insert:

SQL> insert all
         into emp1(empno,ename,job) values(empno,ename,job)
         into emp2(empno,ename,deptno) values(empno,ename,deptno)
     select empno,ename,job,deptno from emp where deptno in (20,30);

已创建10行。

查看插入的数据：

SQL> select * from emp1;

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      1110 张三                 主管
      1111 李四                 销售
      1112 王五                 销售
      1115 张少丽               销售
      1116 林建国               主管

SQL> select * from emp2;

     EMPNO ENAME                    DEPTNO
---------- -------------------- ----------
      1110 张三                         20
      1111 李四                         30
      1112 王五                         30
      1115 张少丽                       30
      1116 林建国                       20

因为没有加条件，所以会同时向两个表中插入数据，且两个表中插入的数据条数一样。

有条件insert all:

SQL> delete emp1;
delete emp2;
insert all
    when job in('销售','会计') then
    into emp1(empno,ename,job) values(empno,ename,job)
    when deptno in ('20','30') then
    into emp2(empno,ename,deptno) values(empno,ename,deptno)
select empno,ename,job,deptno from emp;

已创建10行。

我们查看两表的数据:

SQL> select * from emp1;

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      1111 李四                 销售
      1112 王五                 销售
      1114 李磊磊               会计
      1115 张少丽               销售
      1118 沈倩                 会计

SQL> select * from emp2;

     EMPNO ENAME                    DEPTNO
---------- -------------------- ----------
      1110 张三                         20
      1111 李四                         30
      1112 王五                         30
      1115 张少丽                       30
      1116 林建国                       20
当增加条件后，就会按照条件插入。如“李四”、“王五”、“张少丽”数据在两表中都有。

insert first就不一样:

SQL> delete emp1;
delete emp2;
/*有条件 insert first*/
insert first
    when job in('销售','会计') then
    into emp1(empno,ename,job) values(empno,ename,job)
    when deptno in ('20','30') then
    into emp2(empno,ename,deptno) values(empno,ename,deptno)
select empno,ename,job,deptno from emp;

已创建10行。

我们查看两表的数据:

SQL> select * from emp1;

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      1111 李四                 销售
      1112 王五                 销售
      1114 李磊磊               会计
      1115 张少丽               销售
      1118 沈倩                 会计

SQL> select * from emp2;

     EMPNO ENAME                    DEPTNO
---------- -------------------- ----------
      1110 张三                         20
      1116 林建国                       20
insert first语句中，当地一个表符合条件后，第二个表将不再插入对应的行，表emp2中不再有与表emp1相同的数据“李四”、“王五”、“张少丽”，这就是insert first与insert all的不同之处。

转置insert与其说是一个分类，不如算作“insert all”的一个用法。

SQL> create table t2 (d varchar(10),des varchar(50));

表已创建。

SQL> create table t1 as
          select '熊样，精神不佳' as d1,
                 '猫样，温驯听话' as d2,
                 '狗样，神气活现' as d3,
              '鸟样，向往明天' as d4,
              '花样，愿你快乐像花儿一样' as d5
           from dual;

表已创建。

看一下t1现在的数据:

SQL> select * from t1;

D1        D2              D3                D4                D5
--------------  --------------- ----------------  ----------------- -------------------------------
熊样，精神不佳  猫样，温驯听话  狗样，神气活现    鸟样，向往明天    花样，愿你快乐像花儿一样

/*转置insert*/

SQL> insert all
     into t2(d,des) values('周一',d1)
     into t2(d,des) values('周二',d2)
     into t2(d,des) values('周三',d3)
     into t2(d,des) values('周四',d4)
     into t2(d,des) values('周五',d5)
     select d1,d2,d3,d4,d5 from t1;

已创建5行。

SQL> select * from t2;

D    DES
------- -----------------------
周一    熊样，精神不佳
周二    猫样，温驯听话
周三    狗样，神气活现
周四    鸟样，向往明天
周五    花样，愿你快乐像花儿一样

可以看到，转置insert的实质就是把不同列的数据插入到同一表的不同行中。


6.用其他表中的值更新
我们对表emp新增字段dname，然后把dept.dname更新至emp中:

SQL> alter table emp add dname varchar2(50) default 'noname';

表已更改。

我们这里只更新(20：管理部门，30：销售部门)的数据。其他未更新的部门(如40:后勤部门)名称应该保持为'noname'不变。

出学oracle的人常把语句直接写为:

SQL> update emp p
         set p.dname =
         (select dname
          from dept d where p.mgr=d.deptno
          and d.dname in ('管理部门','销售部门'));

已更新10行。

SQL> select empno,ename,deptno,dname from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO      DNAME
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------  ----------

      1110 张三                 主管                     3322 12-3月 -14      5200                    20       管理部门

      1111 李四                 销售                     3321 03-11月-15      3400        500         30       销售部门

      1112 王五                 销售                     3321 25-4月 -12      4400        800         30       销售部门

      1113 赵二                 后勤                     3320 30-5月 -11      3450                    40

      1114 李磊磊               会计                     3319 22-12月-15      2500                    50

      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30       销售部门

      1116 林建国               主管                     3322 22-1月 -16      5700                    20       管理部门

      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40

      1118 沈倩                 会计                     3319 06-5月 -10      2100                    50
已选择9行。

可以看到，这个语句是对 全表做更新，而不是需求所说的部门(20：管理部门，30：销售部门)，而且因为部门(40:后勤部门)等没有匹配到数据，danme均被更新为NULL值。

可以想象，在生产环境中，大量的数据被清空或改错是多么严重的行为！原因在于该语句中少了必要的过滤条件。

正确的思路是要加上限定条件:
先回滚一下

SQL> roll back;

回退已完成。

然后加限定条件

SQL> update emp p
         set p.dname =
         (select dname
          from dept d where p.mgr=d.deptno
          and d.dname in ('管理部门','销售部门'))
     where exists
         (select d.dname from dept d
            where d.deptno = p.mgr
            and d.dname in ('管理部门','销售部门'));

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO      DNAME
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------  ----------

      1110 张三                 主管                     3322 12-3月 -14      5200                    20       管理部门

      1111 李四                 销售                     3321 03-11月-15      3400        500         30       销售部门

      1112 王五                 销售                     3321 25-4月 -12      4400        800         30       销售部门

      1113 赵二                 后勤                     3320 30-5月 -11      3450                    40       noname

      1114 李磊磊               会计                     3319 22-12月-15      2500                    50       noname

      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30       销售部门

      1116 林建国               主管                     3322 22-1月 -16      5700                    20       管理部门

      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40       noname

      1118 沈倩                 会计                     3319 06-5月 -10      2100                    50       noname

已选择9行。

除了20和30两个部门之外，其它部门的dname均没有被更新， 还是默认值“noname”。

7.合并记录
我们通过合并的实例来介绍一下merge into的使用。

首先建立测试表:

SQL> create table bonuses (employee_id number,bonus number default 100);

表已创建。

insert into bonuses
    (employee_id)
    (select e.employee_id
        from hr.employees e,oe.orders o
     where e.employee_id = o.sales_rep_id
     group by e.employee_id);
select * from bonuses order by employee_id;
commit;

语句及解释如下:

merge into bonuses d
using (select employee_id,salary,department_id
       from hr.employess
       where department_id=80) s
on (d.employee_id=s.employss_id)
/*匹配条件d.employee_id=s.employee_id*/
when matched then/*当d表中存在与S对应数据时进行更新或删除*/
  update
     set d.bonus = d.bonus + s.salary * 0.01
  /*where只能出现一次，如果在这里加了where，delete后面的where就无效*/
  delete
     where (s.salary > 8000) /*删除时，只更新s.salary>8000时的数据*/
when not matched then/*当表中不存在与s对应的数据时进行新增*/
     insert
        (d.employee_id,d.bonus)
     values
        (s.employee_id,s.salary * 0.01)
     where (s.salary<=8000)/*新增时，只更新s.salary<=8000时的数据，注意这里与以前不同，是d表中不存在对应数据时才新增*/

这里有以下几个要点:
(1)语句是merge into bonuses，所以在这个语句里只能更改bonuses的数据，不能更改using后面那些表的数据
(2)更新/删除/插入这三个操作是同时进行的，不分先后。
(3)在merge into语句里不能更新join列
(4)注意上面的注释:当有delete语句时，update后面不能有where过滤条件。这时update的范围是:匹配到的数据减去删除的数据。在本例中就是范围(d.employee_id=s.employss_id)减去范围(s.salary > 8000)。

8.删除违反参照完整性的记录
删除主表的数据，但是有外键的数据在另一个表中依然存在，这个时候会因为数据违反完整性而报错，这个时候删除主表外键躲在的子表中的数据之后，再去删除主表的数据。

9.删除重复的记录
因为是手动录入程序，所以经常会产生重复的数据，这是就需要删除多余的数据，示例如下:

SQL> create table dupes (id integer,name varchar(10));

表已创建。

然后

insert into dupes values (1,'NBA');
insert into dupes values (2,'DNA');
insert into dupes values (3,'DNA');
insert into dupes values (4,'SINA');
insert into dupes values (5,'SUN');
insert into dupes values (6,'SUN');
insert into dupes values (7,'SUN');

可以看到，('DNA'、'SUN')中这两个人的数据重复，现在要求表中name重复的数据只保留一行，其它的删除。

删除数据有好几种方法，下面只介绍三种方法(处理数据需谨慎，要确认更改结果后再提交):

方法一:通过name相同、id不同的方式来判断

delete
   from dupes a
where exists (select NULL from dupes b where b.name = a.name and b.id > a.id);

利用这种方式删除数据时需要建立组合索引:

create index idx_name_id on dupes(name,id);

方法二:用rowid来代替其中的id

delete
   from dupes a
where exists (select NULL from dupes b where b.name = a.name and b.rowid > a.rowid);

因为不需要关联id列，我们只需要建立单列索引:

create index idx_name on dupes(name);

方法三:通过分析函数根据name分组生成序号，然后删除序号大于1的数据。
我们也可以用分析函数取出重复的数据后删除。下面先看生成的序号:

SQL> select rowid as rid,
         name,
         row_number() over(partition by name order by id) as seq
       from dupes
     order by 2,3;

RID                NAME                        SEQ
------------------ -------------------- ----------
AAADmPAAEAAAAQfAAB DNA                           1
AAADmPAAEAAAAQfAAC DNA                           2
AAADmPAAEAAAAQfAAA NBA                           1
AAADmPAAEAAAAQfAAD SINA                          1
AAADmPAAEAAAAQfAAE SUN                           1
AAADmPAAEAAAAQfAAF SUN                           2
AAADmPAAEAAAAQfAAG SUN                           3

已选择7行。

取出序号后，再删除seq>1的语句即可:

delete
    from dupes
  where rowid in (select rid
                 from (select rowid as rid,
                       row_number() over(partition by name order by id)as seq
                       from dupes)
                  where seq > 1);

当然还有其他写法，大家自己研究。

注:row_number() over()
语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。
示例：
xlh           row_num
1700              1
1500              2
1085              3
710               4

row_number() OVER(PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
转载请注明出处：http://blog.csdn.net/acmman/article/details/51392417