一、插入 insert 操作:
1. 复制表结构但不新增数据:
-- 复制表结构但不插入数据 create table emp_new as select * from emp where 1 = 2; select * from emp_new;
![]()
2. 利用 with check option,配合视图,可以为插入数据操作做一些限制:
执行报错:ORA-01402:视图 WITH CHECK OPTIDN违反 where子句-- with check optiom 限制数据的插入 insert into (select empno, deptno, empname, empsalary, empdesc from emp where emp.deptno <> 'dept02'-- with check option ) values('emp008', 'dept02', 'Ross', 7000, '对行业发展趋势有较强的洞察力,有统筹全局能力');
3. 多表插入:
这里讲三种多表插入:a. 无条件 insert;b. 有条件 insert;c. insert first。
- 无条件 insert all
先将 emp 和 emp_bak 清空,再执行下边 sql:
此语是将 emp 的数据同时插入到 emp_new1、emp_new2 表里,执行 select * from emp_new1 的结果如下(emp_new2 的数据集也是如此):-- 无条件 insert all insert all into emp_new1 into emp_new2 select * from emp;
- 有条件 insert all
-- 有条件 insert all insert all when empsalary < 5000 then into emp_new1 when empsalary > 3000 then into emp_new2 select * from emp;
执行上边 sql 后,工资(empsalary)小于 5000 的员工信息插入 emp_new1,工资(empsalary)大于 3000 的员工信息插入 dept_new2。其中 empsalary 为 4000 的同时插入了 emp_new1 和 emp_new2,有时候需要插入指定的表,这个在下边讲:
- insert first
insert first 如果前边有条件符合,后边的表就不会插入对应的行:
-- insert first insert first when empsalary < 5000 then into emp_new1 when empsalary > 3000 then into emp_new2 when empsalary > 3000 then into emp_new3 select * from emp;
![]()
emp_new2 不会插入 empsalary 为 4000 的这条记录,emp_new3 没有记录插入。
二、更新 update 操作:
1. 利用 select 子查询进行 update,需要注意避免全表更新:
数据准备,先将 emp_new1 中所有记录的 empdesc 置为 “未填写”
现在准备根据 emp_new1 表中的记录去更新 emp 表中 empno 对应记录的 empdesc,好多人会写成如下 sql:
-- 利用 select 进行 update 进行了全表更新 update emp set emp.empdesc = (select empdesc from emp_new1 where emp.empno = emp_new1.empno );
上边的结果说明,这个 update 操作的 sql 进行了全表扫描。对 empno 没有匹配到的记录,均被更新为 null。事实上应该加上 where 语句才是正确的:
update emp set emp.empdesc = (select empdesc from emp_new1 where emp.empno = emp_new1.empno ) where exists (select 1 from emp_new1 where emp.empno = emp_new1.empno )
![]()
2. merge into 语句:
针对上边的语句,可以用 merge into 语句
运行结果和 1 中结果一样,如果做判断,推荐使用 merge into 方法。因为 merge into 只访问一次 emp_new1。-- merge into 的实现方法 merge into emp using (select * from emp_new1 ) e on (e.empno = emp.empno) when matched then update set emp.empdesc = e.empdesc;
三、删除 delete 操作
1. 删除重复记录:
方法有很多种,这里只说一种,利用分析函数分组,判断 分组序号是否大于 1.
delete from emp where rowid in (select rid from (select rowid as rid, row_number() over(partition by empsalary order by empno asc) as seq from emp ) where seq > 1 );
但是,如果需要删除完全相同的两条数据中的一条,需要在 partition by 后边加上所有列名,否则删除哪条数据并不确定,删掉记录是和 order by 语句相关的。