--=================================================
--SQL 基礎 --> 數據處理 (DML 、 RETURNING 、 MERGE INTO)
--=================================================
?
一、常用的 DML 語句及事物處理
??? 向表中插入數據 ( INSERT )
??? 更新表中數據 ( UPDATE )
??? 從表中刪除數據 ( DELETE )
??? 將表中數據和并 ( MERGE )
??? 控制事務 ( TRANSACTION )
?
二、 DML 可以在下列條件下執行 :
??? 向表中插入數據
??? 修改現存數據
??? 刪除現存數據
??? 事務是由完成若干項工作的 DML 語句組成的。
?
三、插入數據
??? INSERT 語句語法 :
??? INSERT INTO table [(column [, column...] ) ]
??? VALUES ( value [, value...] );
?
??? 使用這種語法一次只能向表中插入一條數據。
??? 為每一列添加一個新值。
??? 按列的默認順序列出各個列的值。
??? 在 INSERT 子句中隨意列出列名和他們的值。
??? 字符和日期型數據應包含在單引號中。
?
??? -- 查看 emp 表的表結構
?????? SQL > DESC emp ;
?????? ? Name ????????????????????????????????????????????????? Null ? ??? Type
?????? ? ----------------------------------------------------- -------- -----------------------
?????? ? EMPNO ???????????????????????????????????????????????? NOT NULL NUMBER ( 4 )
?????? ? ENAME ????????????????????????????????????????????????????????? VARCHAR2 ( 30 )
?????? ? JOB ??????????????????????????????????????????????????????????? VARCHAR2 ( 9 )
?????? ? MGR ??????????????????????????????????????????????????????????? NUMBER ( 4 )
?????? ? HIREDATE ?????????????????????????????????????????????????????? DATE
?????? ? SALARY ???????????????????????????????????????????????????????? NUMBER ( 8 , 2 )
?????? ? DEPTNO ???????????????????????????????????????????????????????? NUMBER ( 2 )
?
??? 1. 向表中插入空值
?????? 隱式方式 : 在列名表中省略該列的值。
?????????? SQL > INSERT INTO emp ( empno , ename , job , salary ) ??? -- 列出部分列名
?????????? ? 2 ? VALUES ( 1234 , 'Frank' , 'saleman' , 8000 );
?
?????????? 1 row created .
?
?????????? SQL > SELECT ? * FROM emp WHERE ename = 'Frank' ;
?
????????????? ? EMPNO ENAME ????????????????????????? JOB ????????????? MGR HIREDATE ????? SALARY ???? DEPTNO
?????????? ---------- ------------------------------ --------- ---------- --------- ---------- ----------
????????????? ? 1234 Frank ????????????????????????? saleman ????????????????? ???????????? 8000
?
?????? 顯示方式 : 在 VALUES 子句中指定空值 NULL 。
?????????? 注意此處省略了列列表，當列的列表被省略時，則 values 關鍵字中應當為所有的字段列提供列值
?????????? SQL > INSERT INTO emp VALUES ( 100 , 'Jack' , 'manager' ,null,null, 20000 , 10 );
?
?????????? 1 row created .
?
??? 2. 插入指定的值
?????? SYSDATE 記錄當前系統的日期和時間。
?????????? SQL > INSERT into EMP ( empno , ename , job , hiredate , salary )
?????????? ? 2 ? VALUES ( 1235 , 'Tony' , 'boy' , sysdate , 7000 );
?
?????????? 1 row created .
?
?????????? SQL > ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss' ;
?
?????????? Session altered .
?
?????????? SQL > SELECT * FROM emp WHERE ename = 'Tony' ;
?
????????????? ? EMPNO ENAME ?????????? JOB ????????????? MGR HIREDATE ??????????????? SALARY ???? DEPTNO
?????????? ---------- --------------- --------- ---------- ------------------- ---------- ----------
????????????? ? 1235 Tony ??????????? boy ????????????????? 2010 - 06 - 28 13 : 48 : 59 ?????? 7000
?
?????? 插入特定的日期值
?????????? SQL > INSERT INTO emp
?????????? ? 2 ? VALUES ( 1236 , 'Ben' , 'IT' ,null, TO_DATE ( 'JUN 28 2010' , 'MON DD YYYY' ), 3000 , 20 );
???
??? 3. 在列中使用單引號和雙引號
?????? -- 單引號的使用
?????????? SQL > INSERT INTO emp VALUES
?????????? ? 2 ? ( 1238 , 'Dan' , 'Malley''K' , '' , sysdate , 2900 , 20 );
?
?????????? 1 row created . ???
?????? ???
?????? -- 雙引號的使用 ?
?????????? SQL > INSERT INTO emp VALUES
?????????? ? 2 ? ( 1239 , 'Dane' , 'A "Big" L' , '' , sysdate , 2900 , 20 );
?
?????????? 1 row created .
?
?????????? SQL > SELECT * FROM emp WHERE ename LIKE 'Dan%' ;
?
????????????? ? EMPNO ENAME ????????????? JOB ????????????? MGR HIREDATE ??????????????? SALARY ???? DEPTNO
?????????? ---------- ------------------ --------- ---------- ------------------- ---------- ----------
????????????? ? 1238 Dan ??????????????? Malley ' K ???????????? 2010-06-28 14:04:35 ?????? 2900 ???????? 20
????????????? ? 1239 Dane ?????????????? A "Big" L ??????????? 2010-06-28 14:07:12 ?????? 2900 ???????? 20
????????????? ?
??? 4. 從其它表中拷貝數據（利用子查詢向表中插入數據）
?????? 在 INSERT 語句中加入子查詢。
?????? 不必書寫 VALUES 子句。
?????? 子查詢中的值列表應與 INSERT 子句中的列名對應
?????? -- 克隆表結構 ???
?????????? SQL > CREATE TABLE test AS SELECT * FROM scott . emp WHERE 0 = 1 ;
?
?????????? Table created .
?
?????? -- 使用 select 子句插入新值
?????????? SQL > INSERT INTO test SELECT * FROM scott . emp ;
?
?????????? 21 rows created .
?
?????? -- 使用 select 子句插入部分列值
?????????? SQL > INSERT INTO test ( empno , ename , job , salary )
?????????? ? 2 ? SELECT empno , ename , job , salary
?????????? ? 3 ? FROM scott . emp
?????????? ? 4 ? WHERE ename LIKE 'Dan%' ;
?
?????????? 2 rows created .
?
??? 5. 利用替代變量向表中插入數據
?????????? SQL > INSERT INTO test ( empno , ename , job , salary )
?????????? ? 2 ? VALUES (& empno , '&ename' , '&job' ,& salary );
?????????? Enter value for empno : 1240
?????????? Enter value for ename : Andy
?????????? Enter value for job : Singer
?????????? Enter value for salary : 3600
?????????? old ?? 2 : VALUES (& empno , '&ename' , '&job' ,& salary )
?????????? new ?? 2 : VALUES ( 1240 , 'Andy' , 'Singer' , 3600 )
?
?????????? 1 row created .
?????????????????
四、更新數據 ( UPDATE )
??? 語法：
?????? UPDATE table
?????? SET column = value [, column = value, ...]
?????? [WHERE ? condition] ;
???
??? 可以一次更新多條數據。
??? 使用 WHERE 子句指定需要更新的數據，如果省略 WHERE 子句，則表中的所有數據都將被更新。
???
??? 1. 直接更新
?????? SQL > UPDATE emp SET salary = salary + 100 WHERE ename = 'SCOTT' ;
?
?????? 1 row updated .
?
??? 2. 在 UPDATE 語句中使用子查詢
?????? SQL > UPDATE emp SET salary = ?
?????? ? 2 ????? ( SELECT salary FROM emp WHERE ename = 'SCOTT' )
?????? ? 3 ? WHERE empno = 7839 ;
?
?????? 1 row updated .
?
??? 3. 使用多列子查詢來修改記錄：
?????? SQL > UPDATE emp SET ( job , salary )
?????? ? 2 ????? = ( SELECT job , salary FROM emp WHERE ename = 'SCOTT' )
?????? ? 3 ? WHERE ename = 'Jack' ;
?
?????? 1 row updated .
?
五、刪除數據
??? 使用 DELETE 語句從表中刪除數據。
??? DELETE [FROM] table [WHERE condition] ;
?
??? 1. 使用 WHERE 子句指定刪除的記錄，如果省略 WHERE 子句，則表中的全部數據將被刪除。
?????? SQL > DELETE FROM emp WHERE empno = 1234 ;
?
?????? 1 row deleted .
?
??? 2. 在 DELETE 中使用子查詢
?????? 在 DELETE 中使用子查詢，使刪除基于另一個表中的數據。
?????? SQL > DELETE FROM emp
?????? ? 2 ? WHERE deptno =
?????? ? 3 ????? ( SELECT deptno FROM scott . dept WHERE dname = 'ACCOUNTING' );
?
?????? 4 rows deleted .
?
六、在 DML 語句中使用 WITH CHECK OPTION
??? 子查詢可以用來指定 DML 語句的表和列
??? WITH CHECK OPTION 關鍵字可以防止更改不在子查詢中的行
???
??? --deptno 列不在 select 列表中，故不能被更新
?????? SQL > INSERT INTO ???
?????? ? 2 ????? ( SELECT empno , ename , job , mgr , salary FROM emp
?????? ? 3 ?????? WHERE deptno = 20 WITH CHECK OPTION )
?????? ? 4 ? VALUES ( 1250 , 'Smith' , 'Clerk' , 7902 , 3000 );
?????????? ( SELECT empno , ename , job , mgr , salary FROM emp
????????????????????????????????????????????? *
?????? ERROR at line 2 :
?????? ORA - 01402 : view WITH CHECK OPTION where - clause violation
???
七、 TRUNCATE TABLE 截斷表
??? 刪除所有數據，保留表結構
??? TRUNCATE TABLE 語句不能回滾
??? ??? SQL > TURNCATE TABLE emp ;
???
八、使用默認值
??? 顯式默認值
??? 使用 DEFAULT 關鍵字表示默認值
??? 可以使用顯示默認值，控制默認值的使用
??? 顯示默認值可以在 INSERT 和 UPDATE 語句中使用
?????????? SQL > CREATE TABLE tb ??
?????????? ? 2 ? (
?????????? ? 3 ????? orderid INT PRIMARY KEY ,
?????????? ? 4 ????? status VARCHAR2 ( 20 ) DEFAULT 'Delivery' NOT NULL,
?????????? ? 5 ????? last_update DATE DEFAULT sysdate
?????????? ? 6 ? );
?
?????????? Table created .
?
?????????? -- 自動使用默認值
?????????? SQL > INSERT INTO tb ( orderid ) SELECT 10 FROM DUAL ;
?
?????????? 1 row created .
?
?????????? -- 指定新值來覆蓋默認值
?????????? SQL > INSERT INTO tb SELECT 20 , 'No Delivery' , '28-MAY-10' FROM DUAL ;
?
?????????? 1 row created .
?
?????????? -- 使用 default 關鍵字來設置為默認值
?????????? SQL > SELECT * FROM tb ;
?
?????????? ?? ORDERID STATUS ?????????????? LAST_UPDA
?????????? ---------- -------------------- ---------
????????????????? 10 Delivery ???????????? 28 - JUN - 10
????????????????? 20 No Delivery ????????? 28 - MAY - 10
?
?????????? SQL > UPDATE tb SET status = DEFAULT WHERE orderid = 20 ;
?
?????????? 1 row updated .
?
?????????? SQL > SELECT * FROM tb ;
?
?????????? ?? ORDERID STATUS ?????????????? LAST_UPDA
?????????? ---------- -------------------- ---------
????????????????? 10 Delivery ???????????? 28 - JUN - 10
????????????????? 20 Delivery ???????????? 28 - MAY - 10
?
九、 RETURNING 子句
??? 使用 RETURNING 子句返回聚合函數的結果集 ?????????????
?????? SQL > VARIABLE avg_salary NUMBER
?????? SQL > UPDATE emp SET salary = salary + 100
?????? ? 2 ? RETURNING AVG ( salary ) INTO : avg_salary ;
?
?????? 16 rows updated .
?
?????? SQL > PRINT avg_salary ;
?
?????? AVG_SALARY
?????? ----------
?????? ? 2554.6875
?
十、 MERGE INTO ???
??? 將一個表中的行合并到另一個表中
??? MERGE INTO 語法：
?????? MERGE INTO table_name table_alias
?????? USING ( table|view|sub_query ) alias
?????? ON (join condition )
?????? WHEN MATCHED THEN
?????????? UPDATE SET
?????????? col1 = col_val1 ,
?????????? col2 = col2_val
?????? WHEN NOT MATCHED THEN
?????????? INSERT ( column_list )
?????????? VALUES ( column_values );
??????
??? -- 創建演示環境
?????? SQL > CONN hr / hr ;
?????? Connected .
??? -- 從 hr.job_history 提取唯一的數據并復制到新表 job_hs 中
?????? SQL > CREATE TABLE job_hs AS
?????? ? 2 ? SELECT employee_id , start_date , end_date , job_id , department_id ? FROM job_history jh
?????? ? 3 ? WHERE end_date =
?????? ? 4 ????? ( SELECT MAX ( end_date ) FROM job_history WHERE employee_id = jh . employee_id );
?
?????? Table created .
?
?????? SQL > SELECT * FROM job_hs ORDER BY employee_id ;
?
?????? EMPLOYEE_ID START_DAT END_DATE ? JOB_ID ???? DEPARTMENT_ID
?????? ----------- --------- --------- ---------- -------------
????????????? 101 28 - OCT - 93 15 - MAR - 97 AC_MGR ?????????????? 110
????????????? 102 13 - JAN - 93 24 - JUL - 98 IT_PROG ?????????????? 60
????????????? 114 24 - MAR - 98 31 - DEC - 99 ST_CLERK ????????????? 50
????????????? 122 01 - JAN - 99 31 - DEC - 99 ST_CLERK ????????????? 50
????????????? 176 01 - JAN - 99 31 - DEC - 99 SA_MAN ??????????????? 80
????????????? 200 01 - JUL - 94 31 - DEC - 98 AC_ACCOUNT ??????????? 90
????????????? 201 17 - FEB - 96 19 - DEC - 99 MK_REP ??????????????? 20
??? -- 將 hr.employees 中的記錄當 job_hs 中存在時，則更新相關項，否則插入到 job_hs 表中 ??????
?????? SQL > MERGE INTO job_hs h
?????? ? 2 ? USING employees e
?????? ? 3 ? ON ( h . employee_id = e . employee_id )
?????? ? 4 ? WHEN MATCHED THEN
?????? ? 5 ? UPDATE
?????? ? 6 ????? SET
?????? ? 7 ???????? start_date = sysdate ,
?????? ? 8 ???????? end_date = sysdate + 100 ,
?????? ? 9 ???????? job_id = e . job_id ,
?????? ? 10 ???????? department_id = e . department_id
?????? ? 11 ? WHEN NOT MATCHED THEN
?????? ? 12 ????? INSERT ( h . employee_id , h . start_date , h . end_date , h . job_id , h . department_id )
?????? ? 13 ????? VALUES ( e . employee_id , e . hire_date , sysdate , e . job_id , e . department_id );
?????? ?
?????? ? SQL > SELECT COUNT (*) FROM job_hs ;
?
?????? ? COUNT (*)
?????? ----------
?????????? ?? 107
?????????? ??
??? MERGE INTO 使用注意事項
??? ??? MERGE INTO 子句應指明需要合并的目的表
?????? USING ... ON 子句用于表之間的連接
?????? WHEN MATCHED THEN 子句指明當條件滿足時則對目的表執行何種操作 ( 此處是 UPDATE 操作 )
?????? WHEN NOT MATCHED THEN 子句指明當條件不滿足時對目的表執行何種操作 ( 此處是 INSERT 操作 )
?
十一、更多
? SQL 基礎 --> ROLLUP 與 CUBE 運算符實現數據匯總
? SQL 基礎 --> 層次化查詢 (START BY ... CONNECT BY PRIOR)
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
