--=================================================
--SQL 基礎(chǔ) --> 數(shù)據(jù)處理 (DML 、 RETURNING 、 MERGE INTO)
--=================================================
?
一、常用的 DML 語(yǔ)句及事物處理
??? 向表中插入數(shù)據(jù) ( INSERT )
??? 更新表中數(shù)據(jù) ( UPDATE )
??? 從表中刪除數(shù)據(jù) ( DELETE )
??? 將表中數(shù)據(jù)和并 ( MERGE )
??? 控制事務(wù) ( TRANSACTION )
?
二、 DML 可以在下列條件下執(zhí)行 :
??? 向表中插入數(shù)據(jù)
??? 修改現(xiàn)存數(shù)據(jù)
??? 刪除現(xiàn)存數(shù)據(jù)
??? 事務(wù)是由完成若干項(xiàng)工作的 DML 語(yǔ)句組成的。
?
三、插入數(shù)據(jù)
??? INSERT 語(yǔ)句語(yǔ)法 :
??? INSERT INTO table [(column [, column...] ) ]
??? VALUES ( value [, value...] );
?
??? 使用這種語(yǔ)法一次只能向表中插入一條數(shù)據(jù)。
??? 為每一列添加一個(gè)新值。
??? 按列的默認(rèn)順序列出各個(gè)列的值。
??? 在 INSERT 子句中隨意列出列名和他們的值。
??? 字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。
?
??? -- 查看 emp 表的表結(jié)構(gòu)
?????? 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 。
?????????? 注意此處省略了列列表，當(dāng)列的列表被省略時(shí)，則 values 關(guān)鍵字中應(yīng)當(dāng)為所有的字段列提供列值
?????????? SQL > INSERT INTO emp VALUES ( 100 , 'Jack' , 'manager' ,null,null, 20000 , 10 );
?
?????????? 1 row created .
?
??? 2. 插入指定的值
?????? SYSDATE 記錄當(dāng)前系統(tǒng)的日期和時(shí)間。
?????????? 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. 在列中使用單引號(hào)和雙引號(hào)
?????? -- 單引號(hào)的使用
?????????? SQL > INSERT INTO emp VALUES
?????????? ? 2 ? ( 1238 , 'Dan' , 'Malley''K' , '' , sysdate , 2900 , 20 );
?
?????????? 1 row created . ???
?????? ???
?????? -- 雙引號(hào)的使用 ?
?????????? 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. 從其它表中拷貝數(shù)據(jù)（利用子查詢向表中插入數(shù)據(jù)）
?????? 在 INSERT 語(yǔ)句中加入子查詢。
?????? 不必書(shū)寫(xiě) VALUES 子句。
?????? 子查詢中的值列表應(yīng)與 INSERT 子句中的列名對(duì)應(yīng)
?????? -- 克隆表結(jié)構(gòu) ???
?????????? 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. 利用替代變量向表中插入數(shù)據(jù)
?????????? 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 .
?????????????????
四、更新數(shù)據(jù) ( UPDATE )
??? 語(yǔ)法：
?????? UPDATE table
?????? SET column = value [, column = value, ...]
?????? [WHERE ? condition] ;
???
??? 可以一次更新多條數(shù)據(jù)。
??? 使用 WHERE 子句指定需要更新的數(shù)據(jù)，如果省略 WHERE 子句，則表中的所有數(shù)據(jù)都將被更新。
???
??? 1. 直接更新
?????? SQL > UPDATE emp SET salary = salary + 100 WHERE ename = 'SCOTT' ;
?
?????? 1 row updated .
?
??? 2. 在 UPDATE 語(yǔ)句中使用子查詢
?????? SQL > UPDATE emp SET salary = ?
?????? ? 2 ????? ( SELECT salary FROM emp WHERE ename = 'SCOTT' )
?????? ? 3 ? WHERE empno = 7839 ;
?
?????? 1 row updated .
?
??? 3. 使用多列子查詢來(lái)修改記錄：
?????? SQL > UPDATE emp SET ( job , salary )
?????? ? 2 ????? = ( SELECT job , salary FROM emp WHERE ename = 'SCOTT' )
?????? ? 3 ? WHERE ename = 'Jack' ;
?
?????? 1 row updated .
?
五、刪除數(shù)據(jù)
??? 使用 DELETE 語(yǔ)句從表中刪除數(shù)據(jù)。
??? DELETE [FROM] table [WHERE condition] ;
?
??? 1. 使用 WHERE 子句指定刪除的記錄，如果省略 WHERE 子句，則表中的全部數(shù)據(jù)將被刪除。
?????? SQL > DELETE FROM emp WHERE empno = 1234 ;
?
?????? 1 row deleted .
?
??? 2. 在 DELETE 中使用子查詢
?????? 在 DELETE 中使用子查詢，使刪除基于另一個(gè)表中的數(shù)據(jù)。
?????? SQL > DELETE FROM emp
?????? ? 2 ? WHERE deptno =
?????? ? 3 ????? ( SELECT deptno FROM scott . dept WHERE dname = 'ACCOUNTING' );
?
?????? 4 rows deleted .
?
六、在 DML 語(yǔ)句中使用 WITH CHECK OPTION
??? 子查詢可以用來(lái)指定 DML 語(yǔ)句的表和列
??? WITH CHECK OPTION 關(guān)鍵字可以防止更改不在子查詢中的行
???
??? --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 截?cái)啾?
??? 刪除所有數(shù)據(jù)，保留表結(jié)構(gòu)
??? TRUNCATE TABLE 語(yǔ)句不能回滾
??? ??? SQL > TURNCATE TABLE emp ;
???
八、使用默認(rèn)值
??? 顯式默認(rèn)值
??? 使用 DEFAULT 關(guān)鍵字表示默認(rèn)值
??? 可以使用顯示默認(rèn)值，控制默認(rèn)值的使用
??? 顯示默認(rèn)值可以在 INSERT 和 UPDATE 語(yǔ)句中使用
?????????? 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 .
?
?????????? -- 自動(dòng)使用默認(rèn)值
?????????? SQL > INSERT INTO tb ( orderid ) SELECT 10 FROM DUAL ;
?
?????????? 1 row created .
?
?????????? -- 指定新值來(lái)覆蓋默認(rèn)值
?????????? SQL > INSERT INTO tb SELECT 20 , 'No Delivery' , '28-MAY-10' FROM DUAL ;
?
?????????? 1 row created .
?
?????????? -- 使用 default 關(guān)鍵字來(lái)設(shè)置為默認(rèn)值
?????????? 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 子句返回聚合函數(shù)的結(jié)果集 ?????????????
?????? 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 ???
??? 將一個(gè)表中的行合并到另一個(gè)表中
??? MERGE INTO 語(yǔ)法：
?????? 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 );
??????
??? -- 創(chuàng)建演示環(huán)境
?????? SQL > CONN hr / hr ;
?????? Connected .
??? -- 從 hr.job_history 提取唯一的數(shù)據(jù)并復(fù)制到新表 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 中的記錄當(dāng) job_hs 中存在時(shí)，則更新相關(guān)項(xiàng)，否則插入到 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 使用注意事項(xiàng)
??? ??? MERGE INTO 子句應(yīng)指明需要合并的目的表
?????? USING ... ON 子句用于表之間的連接
?????? WHEN MATCHED THEN 子句指明當(dāng)條件滿足時(shí)則對(duì)目的表執(zhí)行何種操作 ( 此處是 UPDATE 操作 )
?????? WHEN NOT MATCHED THEN 子句指明當(dāng)條件不滿足時(shí)對(duì)目的表執(zhí)行何種操作 ( 此處是 INSERT 操作 )
?
十一、更多
Oracle 用戶、對(duì)象權(quán)限、系統(tǒng)權(quán)限
? SQL 基礎(chǔ) --> ROLLUP 與 CUBE 運(yùn)算符實(shí)現(xiàn)數(shù)據(jù)匯總
? SQL 基礎(chǔ) --> 層次化查詢 (START BY ... CONNECT BY PRIOR)
? SQL 基礎(chǔ) --> 視圖 (CREATE VIEW)
? Oracle 常用目錄結(jié)構(gòu) (10g)
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
