oracle sql 基礎（三）：資料操縱語言

標籤：順序   單引號   開始   完整   應用   執行個體   欄位名   commit   table   

       資料操縱語言，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,       50FROM 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實現代碼如下

----更新分析師SCOTT的員工資訊UPDATE emp SET mgr=7590,deptno=50WHERE 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實現代碼如下

-----刪除部門OPERATIONS的所有員工資訊DELETE FROM empWHERE deptno=(SELECT deptno              FROM dept              WHERE dname=‘OPERATIONS‘);-----刪除部門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表中。

----構造一個用於合并的員工表emp_copyCREATE 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;----把員工表EMP_COPY的資料合併到員工表EMP中MERGE INTO emp eUSING emp_copy cON (e.empno=c.empno)WHEN MATCHED THENUPDATESET e.ename=c.ename, --由於ON條件中已有e.empno=c.empno，故此處不能出現該句。    e.job=c.job,    e.mgr=c.mgr,    e.hiredate=c.hiredate,    e.sal=c.sal,    e.comm=c.comm,    e.deptno=c.deptnoWHEN NOT MATCHED THENINSERTVALUES(c.empno,c.ename,c.job,c.mgr,c.hiredate,c.sal,c.comm,c.deptno);

MERGE 執行個體

 

oracle sql 基礎（三）：資料操縱語言