文章目錄
- 一、介紹
- 二、DML:數據操縱語言
- 三、DDL:數據定義語言
- 四、DCL:數據控制語言
- 五、TCL:事務控制語言
- 六、SELECT 查詢時鎖定
一、介紹
在Oracle數據庫中,掌握和運用DML、DDL、DCL和TCL語句是必不可少的技能。
這些語句在數據庫的增刪改查、結構設計、權限控制和事務管理中發揮著重要作用。
通過實際例子,我們將更深入地理解這些語句的應用。
二、DML:數據操縱語言
DML(Data Manipulation Language)用于對數據庫中的數據進行增刪改查操作。主要包括以下幾種語句:
INSERT:用于向表中插入新數據。
UPDATE:用于修改表中現有數據。
DELETE:用于刪除表中的數據。
SELECT:用于查詢表中的數據。
注意:
在Oracle數據庫中,當你插入數據后,需要使用COMMIT語句來提交修改,以使更改永久生效。
如果你在插入數據后發現有問題或錯誤,可以在未發生COMMIT之前使用ROLLBACK語句來回滾剛才的插入操作。
通過使用ROLLBACK語句,你可以撤銷最近的未提交更改,將數據庫恢復到插入操作之前的狀態。這為你提供了在數據插入出現問題時糾正錯誤的機會。
但請注意,如果你在插入數據后已經執行了COMMIT語句,那么你就無法使用ROLLBACK語句撤銷該插入操作。這是因為COMMIT語句將更改永久保存到數據庫中,無法回滾。
INSERT案例:
假設我們有一個"employees"表,包含員工信息。我們可以使用DML語句進行數據的增刪改查操作:
1.INSERT語句:向"employees"表中插入一條新記錄,如:
INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30);
或者
INSERT INTO employees VALUES (1, 'John Doe', 30);
ROLLBACK;–撤銷剛才的插入
COMMIT;插入生效
2.UPDATE語句:修改"employees"表中現有記錄的年齡,如:
UPDATE employees SET age = 31 WHERE id = 1;
ROLLBACK;–撤銷剛才的插入
COMMIT;插入生效
3.DELETE語句:刪除"employees"表中的某條記錄,如:
DELETE FROM employees WHERE id = 1;
ROLLBACK;–撤銷剛才的插入
COMMIT;插入生效
4.TRUNCATE語句,刪除"employees"表中的所有記錄,如:
TRUNCATE TABLE employees;
注意:
TRUNCATE語句不能回滾,一旦執行,表中的數據將永久刪除。
TRUNCATE語句將重置表的計數器,所以在執行TRUNCATE后,表中的自增字段將從起始值重新開始計數。
TRUNCATE語句是一種高效的清空表數據的方法,特別適用于需要清空整個表而不需要逐行刪除數據的場景。
5.SELECT語句:查詢"employees"表中的數據,如:
SELECT * FROM employees WHERE age > 25;
三、DDL:數據定義語言
DDL(Data Definition Language)是數據庫管理系統中用于定義和管理數據庫對象的語言。它包括創建、修改和刪除數據庫、表、索引、視圖等對象。
CREATE:用于創建數據庫對象,如表、視圖、索引等。
ALTER:用于修改數據庫對象的結構。
DROP:用于刪除數據庫對象。
1.CREATE TABLE: 創建表
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),age INT,salary DECIMAL(10,2)
);
2.ALTER TABLE: 修改表結構
ALTER TABLE employeesADD COLUMN email VARCHAR(100);
3.DROP TABLE: 刪除表
DROP TABLE employees;
4.CREATE INDEX: 創建索引
CREATE INDEX idx_employees_name ON employees (name);
5.ALTER INDEX: 修改索引
ALTER INDEX idx_employees_name RENAME TO idx_employees_last_name;
- DROP INDEX: 刪除索引
DROP INDEX idx_employees_last_name;
7.CREATE VIEW: 創建視圖
CREATE VIEW employee_view ASSELECT id, name, age FROM employees WHERE age > 30;
8.ALTER VIEW: 修改視圖
ALTER VIEW employee_view ASSELECT id, name, age, salary FROM employees WHERE age > 25;
9.DROP VIEW: 刪除視圖
DROP VIEW employee_view;
四、DCL:數據控制語言
DCL(Data Control Language)是數據庫管理系統中用于控制數據庫訪問權限和事務操作的語言。它包括授權、回收權限以及管理事務等操作。
GRANT:用于授予用戶或角色訪問數據庫的權限。
REVOKE:用于收回用戶或角色的訪問權限。
GRANT:授權用戶或用戶組訪問權限
GRANT SELECT, INSERT ON employees TO user1;
REVOKE:回收用戶或用戶組的訪問權限
REVOKE INSERT ON employees FROM user1;
五、TCL:事務控制語言
TCL(Transaction Control Language)用于管理數據庫中的事務,確保數據的完整性和一致性。它允許用戶對數據庫中的操作進行邏輯分組,并在需要時進行提交或回滾。
COMMIT:用于提交事務,將修改永久保存到數據庫中。
ROLLBACK:用于回滾事務,撤銷對數據庫的修改。
SAVEPOINT:用于在事務中設置保存點,以便在必要時回滾到特定狀態。
1.COMMIT:將事務中的所有修改保存到數據庫,成功提交事務。
BEGIN TRANSACTION;
UPDATE customer SET balance = balance - 100 WHERE id = 1;
INSERT INTO transaction (customer_id, amount) VALUES (1, -100);
COMMIT;
2.ROLLBACK:撤銷事務中的所有修改,回滾到事務開始前的狀態。
BEGIN TRANSACTION;
UPDATE customer SET balance = balance - 100 WHERE id = 1;
INSERT INTO transaction (customer_id, amount) VALUES (1, -100);
ROLLBACK;
3.SAVEPOINT 和 ROLLBACK TO SAVEPOINT:設置和回滾到保存點(一個特定時間點的事務狀態)。
BEGIN TRANSACTION;
UPDATE customer SET balance = balance - 100 WHERE id = 1;
SAVEPOINT savepoint1;
UPDATE customer SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;
六、SELECT 查詢時鎖定
FOR UPDATE這個語句常常用于防止并發時多個事務同時修改或讀取同一條記錄,
以保證數據的安全性和一致性,它將鎖定所選行,直到事務結束時才會釋放該行的鎖定。
FOR UPDATE 語句鎖定所選行的示例:
BEGIN TRANSACTION;-- 選取行并加鎖
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;-- 在事務中修改所選行
UPDATE my_table SET value = 'new value' WHERE id = 1;COMMIT;
在以上示例中,SELECT * FROM my_table WHERE id = 1 FOR UPDATE 會選中 ID 為 1 的行并把它們所在的行鎖定,以避免其他事務同時修改該行。在事務結束時,這個鎖定會被釋放。