【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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

光仔December

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值