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 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
-------------- --------------- ---------------- ----------------- -------------------------------
熊样，精神不佳 猫样，温驯听话 狗样，神气活现 鸟样，向往明天 花样，愿你快乐像花儿一样
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 dusing (select employee_id,salary,department_id from hr.employess where department_id=80) son (d.employee_id=s.employss_id)when matched then update set d.bonus = d.bonus + s.salary * 0.01 delete where (s.salary > 8000) when not matched then insert (d.employee_id,d.bonus) values (s.employee_id,s.salary * 0.01) where (s.salary<=8000)
这里有以下几个要点:
(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 awhere 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 awhere 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排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)