SQL語言基礎(資料控制語言)

DML 可以在下列條件下執行:

Ø   向表中插入資料

Ø   修改現存資料

Ø   刪除現存資料

事務是由完成若干項工作的DML 語句組成的。

1-1 insert語句

INSERT INTO  table [(column [, column...])]
VALUES  (value [, value...]);

    使用這種語法一次只能向表中插入一條資料。

1-1-1 插入資料

為每一列新增一個新值。

按列的預設順序列出各個列的值。

在 INSERT 子句中隨意列出列名和他們的值。

字元和日期型資料應包含在單引號中。

INSERT INTO departments(department_id, department_name,
                        manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);

指定插入的值

SYSDATE 記錄當前系統的日期和時間

INSERT INTO employees (employee_id,
                 first_name, last_name,
                 email, phone_number,
                 hire_date, job_id, salary,
                 commission_pct, manager_id,
                 department_id)
VALUES     (113,
                 'Louis', 'Popp',
                 'LPOPP', '515.124.4567',
                 SYSDATE, 'AC_ACCOUNT', 6900,
                 NULL, 205, 100);

1-1-2 從其它表中複製資料

INSERT INTO sales_reps(id, name, salary, commission_pct)
  SELECT employee_id, last_name, salary, commission_pct
  FROM   employees
  WHERE  job_id LIKE '%REP%';

1-1-3  在 DML 語句中使用 WITH CHECK OPTION 關鍵字

INSERT INTO  (SELECT employee_id, last_name, email,
                 hire_date, job_id, salary
              FROM   employees
              WHERE  department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
        TO_DATE('07-JUN-99', 'DD-MON-RR'),
        'ST_CLERK', 5000);

1-1-4 合併語句

按照指定的條件執行插入或更新操作

如果滿足條件的行存在，執行更新操作；否則執行插入操作:

避免多次重複執行插入和刪除操作

提高效率而且使用方便

可以使用 merge 語句，根據指定的條件進行插入或更新操作

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);

在對錶COPY_EMP 使用merge 語句，根據指定的條件從表EMPLOYEES 中插入或更新資料

MERGE INTO copy_emp  c
  USING employees e
  ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET
     c.first_name     = e.first_name,
     c.last_name      = e.last_name,
     ...
     c.department_id  = e.department_id
WHEN NOT MATCHED THEN
 INSERT VALUES(e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number, e.hire_date, e.job_id,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id);

1-2  UPDATE 語句

使用 UPDATE 語句更新資料，可以一次更新多條資料

UPDATE  table
SET  column = value [, column = value, ...]
[WHERE   condition];

1-2-1 更新語句

使用 WHERE 子句指定需要更新的資料

UPDATE   copy_emp
SET      department_id = 110
where employee_id = 50

1-2-2  在 UPDATE 語句中使用子查詢

更新 114 號員工的工作和工資使其與 205 號員工相同

UPDATE   employees
SET      job_id  = (SELECT  job_id
                    FROM    employees
                    WHERE   employee_id = 205),
         salary  = (SELECT  salary
                    FROM    employees
                    WHERE   employee_id = 205)
WHERE    employee_id    =  114;

1-3 delete語句

使用 DELETE 語句從表中刪除資料

DELETE [FROM]	  table
[WHERE	  condition];

1-3-1 刪除資料

使用WHERE 子句指定刪除的記錄

DELETE FROM departments
 WHERE  department_name = 'Finance';

如果省略 WHERE 子句，則表中的全部資料將被刪除

1-3-2  在 DELETE 中使用子查詢

在 DELETE 中使用子查詢，使刪除基於另一個表中的資料。

DELETE FROM employees
WHERE  department_id =
                       (SELECT department_id
                        FROM   departments
                        WHERE  department_name LIKE '%Public%');

1-4 資料庫事務

資料庫事務由以下的部分組成

Ø   一個或多個DML 語句

Ø   一個 DDL 語句

Ø   一個 DCL 語句

資料庫事務起至標誌

以第一個 DML 語句的執行作為開始

以下面的其中之一作為結束:

Ø   COMMIT 或 ROLLBACK 語句

Ø   DDL 或 DCL 語句（自動提交）

Ø   使用者會話正常結束

Ø   系統異常終止

1-4-1  COMMIT 和 ROLLBACK 語句

使用 COMMIT 和 ROLLBACK 語句 , 我們可以 :

Ø   確保資料完整性。

Ø   資料改變被提交之前預覽。

Ø   將邏輯上相關的操作分組。

UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

1-4-2 事務程式

自動提交在以下情況中執行:

Ø   DDL 語句。

Ø   DCL 語句。

Ø   不使用 COMMIT 或 ROLLBACK 語句提交或回滾，正常結束會話。

會話異常結束或系統異常會導致自動回滾。

提交或回滾前的資料狀態

Ø   改變前的資料狀態是可以恢復的

Ø   執行 DML 操作的使用者可以透過 SELECT 語句查詢之前的修正

Ø   其他使用者不能看到當前使用者所做的改變，直到當前使用者結束事務。

Ø   DML 語句所涉及到的行被鎖定， 其他使用者不能操作

提交後的資料狀態

Ø   資料的改變已經被儲存到資料庫中。

Ø   改變前的資料已經丟失。

Ø   所有使用者可以看到結果。

Ø   鎖被釋放， 其他使用者可以操作涉及到的資料。

Ø   所有儲存點被釋放。

使用 ROLLBACK 語句可使資料變化失效:

Ø   資料改變被取消。

Ø   修改前的資料狀態可以被恢復。

Ø   鎖被釋放。

DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.