oracle sql 基础（三）：数据操纵语言（insert、update、delete、merge）

scuess

　　数据操纵语言，Data manipulation language，检称DML，主要包括检索(SELECT)、插入(INSERT)、更新(UPDATE)、删除(DELETE)，是SQL的一个核心部分。一条DML将开始一个事务，接下来的DML都是同一事务中的语句，直到提交(COMMIT)或回滚(ROLLBACK)。下面我们来逐一介绍下ORACLE中的插入、更新、删除和合并(MERGE)的语法及实例解析。
　　一、INSERT 语句
　　1、INSERT 语句的语法
　　插入单行记录语法：INSERT INTO table [(column [, column...])]VALUES (value [,value...]);
　　该语句用VALUES子句添加行到列表中，一次仅一行。在INSERT子句中字段列表不是必须的，若不用字段列表，值必须按照表中字段的默认顺序排列。为使语句更清楚，在INSERT子句中使用字段列表。字符和日期值应该放在单引号中，数字值不需要，若使用了单引号，可能发生数字值的隐氏转换。
　　插入子查询结果（可多行）语法：INSERT INTO table [(column [, column...])] subquery;
　　在INSERT子句的字段列表中列的数目和数据类型必须与子查询中的列的数目及其数据类型相匹配。插入子查询数据分两种情况：一种从相关的一个表或者多个表查询需要插入的数据；另一种是从DUAL表查询特定的数据、子查询，取得相应的数据。
　　2、INSERT 语句的例子
　　由于公司新组建了一个部门，需要在部门表(DEPT)中插入一条数据，部门号为50，部门名为数据中心(DATACENTRE)，部门所在地为中国(CHINA)；并在员工表(EMP)中插入该部门领导的信息，员工号为7950，姓名为SJZH，岗位、领导编号和其他部门管理者一样，入职时间为今天，工资为岗位为管理者薪资的平均值，奖金为空，所在部门编号为50。INSERT 语句的实现代码如下

　　

　　
INSERT INTO dept (deptno,dname,loc)
　　
VALUES (50,'DATACENTRE','CHINA');
　　

　　
INSERT INTO emp
　　
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
　　
SELECT 7950,
　　'SJZH',
　　'MANAGER',
　　(SELECT DISTINCT mgr FROM emp WHERE job='MANAGER'),
　　SYSDATE,
　　(SELECT avg(sal) FROM emp WHERE job='MANAGER'),
　　NULL,
　　50
　　
FROM dual;
　　


INSERT 实例　　二、UPDATE 语句
　　1、UPDATE 语句的语法
　　用值更新语法：UPDATE table SET column=value[,column=value,...] [WHERE condition];
　　通常，用主键标识一个单个的行，如果用其他的列，可能会出乎意料的引起另一些行被更新。若没加约束条件，会导致整列被更新。
　　用子查询更新语法：UPDATE table SET column=subquery[,column=subquery,...] [WHERE condition];
　　可以基于目标表或其他表更新列。主键是该表中的唯一约束，不能重复，外键是该表中的字段与另外一个表的主键字段名相同的情况下设置的一种约束，主键约束或外键约束不满足的时候，数据无法更新或者插入。
　　2、UPDATE 语句的例子
　　现在，我们要从其他部门调两个人到新建的部门（部门编号50，部门领导编号为7950），一个是分析师SCOTT，薪酬不变；一个是工资最低的办事员，薪酬提高到和办事员薪酬排名倒二的人持平。UPDATE实现代码如下

　　

　　
UPDATE emp
　　
SET mgr=7590,deptno=50
　　
WHERE empno=7788;
　　

　　
UPDATE emp
　　
SET mgr=7590,deptno=50,
　　sal=(SELECT sal
　　FROM (SELECT sal,rownum no
　　FROM (SELECT job,sal
　　FROM emp
　　WHERE job='CLERK'
　　ORDER BY sal
　　)
　　WHERE job='CLERK'
　　)
　　WHERE no=2
　　)
　　
WHERE sal=(SELECT sal
　　FROM (SELECT * FROM emp ORDER BY sal)
　　WHERE job='CLERK' and rownum=1);
　　


UPDATE 实例　　三、DELETE 语句
　　1、DALETE 语句的语法
　　删除记录语法：DELETE [FROM] table [WHER condition];
　　若没有指定WHERE子句，表中的所有数据将被删除，只有表的结构被保留，清空表的另一种更有效的方法是用TRUNCATE语句。
　　删除的条件中可以使用子查询，子查询可以是针对任意表的，可以不是被删除行的表。在提交DELETE之前，多看一眼，自己到底有没有加条件，条件是否是唯一约束，是否真的是要删除条件约束的数据。
　　2、DELETE 语句的例子
　　我们要把部门表DEPT的运营部(OPERATIONS)取消，首先得删除员工表表EMP中所有运营部的员工信息，然后再删除DEPT中运营部的记录。若员工表表中还有该部门的员工，直接在部门表中删除该部门会因违反完整性约束而报错。DELETE实现代码如下

　　

　　
DELETE FROM emp
　　
WHERE deptno=(SELECT deptno
　　FROM dept
　　WHERE dname='OPERATIONS');
　　

　　
DELETE FROM dept WHERE dname='OPERATIONS';
　　


DELETE 实例　　四、MERGE 语句
　　1、MERGE 语句的语法
　　MERGE语句，可以称其为“融合”语句，它相当于插入与更新语句的综合。通常是对两个表进行操作，是执行对目的表的更新操作还是执行对目的表的插入操作，取决于基于ON子句中的条件。MERGE有以下特点：避免分散更新，增进性能和易用性，在数据仓库应用中有用。MERGE合并的语法如下
　　

MERGE INTO table_nametable_alias       ---指定你正在更新或插入的目的表　　
USING (
table|view|sub_query) alias     ---指定要用于更新或插入的数据源　　

ON (join condition)              ---类似于表连接条件，在此条件下即可更新也可进行插入　　

WHEN MATCHED THEN                ---当满足条件时，更新该条记录到目的表中　　
UPDATE
　　SET col1 = col1_val[,col2 = col2_val...]
　　
WHEN NOT MATCHED THEN            ---当不满足条件时，插入该条记录到目的表中
　　
INSERT (column_list)
　　
VALUES (column_values);
　　

　　在数据仓库环境中，你可能有一个大的事实表和一个较小维数的表，小表中的行需要有条件地插入到大的事实表中，比如处理来自多个源的数据工作时，其中的一些可能是完全相同的，需要有条件地添加或修改行。在这种情况下，MERGE语句是有用的。
　　2、MERGE 语句的例子
　　现在，要把员工表EMP_COPY的数据合并到员工表EMP中，通过匹配EMP_COPY表的EMPNO列和EMP表的EMPNO列。如果找到了一个匹配，用EMP_COPY表中匹配行的列值更新EMP表中匹配的列值。如果相匹配行没有找到，EMP_COPY表中匹配行的列值被插入到COPY_EMP表中。

　　

　　
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno in (10,40);
　　
INSERT INTO emp_copy (empno,ename,job,mgr,hiredate,sal,comm,deptno)
　　
VALUES (7899,'ASD','CLERK',7839,To_Date('2017-1-23','yyyy-mm-dd'),2000,0,10);
　　
UPDATE emp_copy SET sal=1900 WHERE empno=7934;
　　

　　
MERGE INTO emp e
　　
USING emp_copy c
　　
ON (e.empno=c.empno)
　　
WHEN MATCHED THEN
　　
UPDATE
　　
SET e.ename=c.ename,
　　e.job=c.job,
　　e.mgr=c.mgr,
　　e.hiredate=c.hiredate,
　　e.sal=c.sal,
　　e.comm=c.comm,
　　e.deptno=c.deptno
　　
WHEN NOT MATCHED THEN
　　
INSERT
　　
VALUES
　　
(c.empno,c.ename,c.job,c.mgr,c.hiredate,c.sal,c.comm,c.deptno);
　　


MERGE 实例