一、常用的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);
2.插入指定的值
SYSDATE 記錄目前系統的日期和時間。
SQL> INSERT into EMP(empno,ename,job,hiredate,salary)
2 VALUES(1235,'Tony','boy',sysdate,7000);
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.
--雙引号的使用
2 (1239,'Dane','A "Big" L','',sysdate,2900,20);
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.利用替代變量向表中插入資料
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)
四、更新資料(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;
3.使用多列子查詢來修改記錄：
SQL> UPDATE emp SET(job,salary)
2 = (SELECT job,salary FROM emp WHERE ename = 'SCOTT')
3 WHERE ename = 'Jack';
五、删除資料
使用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 );
--自動使用預設值
SQL> INSERT INTO tb(orderid) SELECT 10 FROM DUAL;
--指定新值來覆寫預設值
SQL> INSERT INTO tb SELECT 20, 'No Delivery','28-MAY-10' FROM DUAL;
--使用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.
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操作)
![JDBC連接配接資料庫（statement）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)