Oracle | DDL&約束&DCL&TCL

標籤：check   表達   提交   comm   資料完整性   一致性   整數   ber   刪除資料庫   

• DDL（Data Definition Language，資料定義語言 (Data Definition Language)），用於建立/修改/刪除資料庫的各種對象，如表、視圖、索引、同義字、序列等。DDL語句主要包括 CREATE 建立、ALTER 修改、TRUNCATE 截斷 、DROP 刪除。
• 約束CONSTRAINT，用於限制表的資料的類型，主要包括非空約束 NOT NULL、唯一約束 UNIQUE、主鍵約束 PRIMARY KEY、檢查約束 CHECK、外鍵約束 FOREIGN KEY。
• DCL（Data Control Language，資料控制語言），用於授予/回收訪問資料庫的許可權。DCL 語句即GRANT 授予、REVOKE 回收。
• TCL（Transaction Control Language，事務控制語言），用於事務的提交/復原。TCL語句包括 COMMIT 提交、ROLLBACK 復原，SAVEPOINT 儲存點。

Oracle資料庫解鎖賬戶

alter user <賬戶名> account unlock;

Oracle 資料類型

• NUMBER：整數
• DATE：日期
• CLOB：大文本資料，可存4G文本資料
• BLOB：位元據，可存圖片、音頻、視頻、文字，最大4G。
• VARCHAR：固定長度字串，標準sql資料類型
• VARCHAR2：可變長度字串，oracle特有。
  • 字串：‘abc‘
  • 字串串連符：||

資料偽列

• ROWID：資料庫中每一行都有一個行地址，rowid偽列返回該行地址。rowId值可以唯一標識資料庫中的一行。
• ROWNUM：返回一個數值代表行的次序。通過使用rownum，使用者可以限制查詢返回的行數。

DDL

資料定義語言 (Data Definition Language)DDL，主要用於建立/修改/刪除資料庫的各種對象，如表、視圖、索引、同義字、序列等。DDL語句主要包括 CREATE 建立、ALTER 修改、DROP 刪除。

表：TABLE建立表

CREATE TABLE <表名>(    <列名> <資料類型> [<DEFAULT 預設值>] [<約束>],    <列名> <資料類型> [<DEFAULT 預設值>] [<約束>],    ...    [CONSTRAINT <約束名> <約束類型>(約束列)])

修改表

修改表中列

1.增加列：

ALTER TABLE <表名> ADD （    <列名> <資料類型> [<DEFAULT 預設值>] [<資料行層級條件約束>],    <列名> <資料類型> [<DEFAULT 預設值>] [<資料行層級條件約束>],    ...)

2.修改列：

ALTER TABLE <表名> MODIFY （    <列名> <資料類型> [<DEFAULT 預設值>] [<資料行層級條件約束>],    <列名> <資料類型> [<DEFAULT 預設值>] [<資料行層級條件約束>],    ...)

3.刪除列：

ALTER TABLE <表名> DROP COLUMN <列名>

4.重新命名列：

ALTER TABLE <表名> RENAME <舊名> TO <新名>

修改表中屬性：

1.添加約束：

ALTER TABLE <表名> ADD CONSTRAINT <約束名> <約束類型>(約束列);

2.刪除約束：

ALTER TABLE <表名> DROP CONSTRAINT <約束名>;

截斷表

TRUNCATE TABLE <表名>

刪除表

1.刪除表：

DROP TABLE <表名>

2.截斷表：

複製表

1.複製表的結構與資料：

CREATE TABLE <表名> AS     SELECT <目標列運算式>    FROM <表>    WHERE <查詢條件>

2.僅複製表的結構：WHERE 1 = 2

CREATE TABLE <表名> AS     SELECT <目標列運算式>    FROM <表>    WHERE 1 = 2;

重新命名

RENAME <舊名稱> TO <新名稱>

視圖：VIEW

視圖 (VIEW)：虛擬表，不佔物理空間。

建立視圖

CREATE [OR REPLACE] [FORCE] VIEW <視圖名>[(列名1, 列2,...)]AS SELECT...[WITH CHECK OPTION][WITH READ ONLY];

FORCE：強制建立視圖
WITH CHECK OPTION：檢查約束視圖
WITH READ ONLY：唯讀視圖

刪除視圖

DROP VIEW <視圖名>

索引：INDEX

索引：加快查詢速度

建立索引

CREATE [UNIQUE] INDEX <索引名>ON <表名> (<列名> [ASC | DESC], ... )[INITRANS n][MAXTRANS n][PCTFREE n][TABLESPACE <資料表空間名>][STORAGE storage][NOSORT]

UNIQUE：唯一索引
INITRANS：初始事務入口數
MAXTRANS：最大事務入口數
PCTFREE：索引資料區塊空閑空間的百分比
NOSORT：不排序

修改索引

重建索引

ALTER INDEX <索引名> REBUILD;

合并索引

ALTER INDEX <索引名> COALESCE;

刪除索引

DROP INDEX <索引名>

同義字：SYNONYM

同義字：資料庫物件的一個別名，操作許可權不變，簡化對象訪問。

建立同義字

CREATE [PUBLIC] SYNONYM <表名同義字>FOR <使用者>.<表名>

刪除同義字

DROP [PUBLIC] SYNONYM <同義字>

序列：SEQUENCE

序列 (SEQUENCE)： 序號產生器，為表中的行自動產生序號，產生一組等間隔的數值(類型為數字)。不佔用磁碟空間，佔用記憶體。

其主要用途是產生表的主索引值，可以在插入語句中引用，也可以通過查詢檢查當前值，或使序列增至下一個值。

建立序列

CREATE SEQUENCE <序列名>[INCREMENT BY n][START WITH n][MAXVALUE n | NOMAXVALUE][MINVALUE n | NOMINVALUE][CYCLE | NOCYCLE][CACHE n | NOCACHE];

INCREMENT BY：定義步長
START WITH：定義序列初始值
MAXVALUE / MINVALUE：定義序列最大值/最小值
NOMAXVALUE：預設最值
CYCLE / NOCYCLE：表示序列達到限定值後是否迴圈
CACHE n：對序列進行記憶體緩衝，提前產生n個序號存入緩衝，預設n=20
NOCACHE：不對序列進行記憶體緩衝

序列的兩個偽列

• NEXTVAL：序列中的下一個有效值
• CURRVAL：序列當前值

修改序列

ALTER SEQUENCE [<使用者>.]<序列名>[INCREMENT BY n][START WITH n][MAXVALUE n | NOMAXVALUE][MINVALUE n | NOMINVALUE][CYCLE | NOCYCLE][CACHE n | NOCACHE];

刪除序列

DROP SEQUENCE <序列名>

約束-CONSTRAINT

約束CONSTRAINT，用於限制表的資料的類型。

CONSTRAINT <約束名> <約束類型>(約束列)

非空約束：NOT NULL

非空約束：NOT NULL

<列名> <資料類型> NOT NULL

唯一約束：UNIQUE

唯一約束：UNIQUE

<列名> <資料類型> UNIQUE

CONSTRAINT uk_cloumn UNIQUE(<列名>);

主鍵約束：PRIMARY KEY

主鍵約束：PRIMARY KEY，非空+唯一

CONSTRAINT pk_cloumn PRIMARY KEY(<列名>);

聯合主鍵：

CONSTRAINT pk_cloumn PRIMARY KEY(<列名1>,<列名2>,...);

檢查約束：CHECK

CONSTRAINT ck_cloumn CHECK(<列名> IN (值1,值2...))

CONSTRAINT ck_cloumn CHECK(<列名> BETWEEN 值1 AND 值2)

外鍵約束：FOREIGN KEY

CONSTRAINT fk_cloumn FOREIGN KEY (<列名1>,<列名2>...) REFERENCES <表名>(<列名1>,<列名2>...) ON DELETE <CASCADE|SET NULL> 

資料庫安全-DCL

資料控制語言DCL，用於授予/回收使用者/角色訪問資料庫的許可權，DCL語句即 GRANT 授予、REVOKE 回收。

授予許可權：GRANT

GRANT <許可權>ON <物件類型> <對象名>TO <使用者>[WITH GRANT OPTION]

WITH GRANT OPTION：可傳播許可權，即將該許可權再授予其他使用者。

收回許可權：REVOKE

REVOKE <許可權>ON <物件類型> <對象名>FROM <使用者> [CASCADE|RESTRICT]

CASCADE：級聯
RESTRICT：限定

角色：ROLE

角色(ROLE)：一組資料庫操作許可權的集合。

1.建立角色：

CREATE ROLE <角色名稱>

2.為角色授權：

GRANT <許可權>ON <物件類型> <對象名>TO <角色>

3.將角色授權給其他角色或使用者：

GRANT <角色1>ON <物件類型> <對象名>TO <角色2>,<使用者>

事務-TCL

事務：用於保證資料完整性。由一組DML語句組成，這組DML語句要麼全部成功，要麼全部失敗。

事務特性：ACID

• 原子性（atomicity）：一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
• 一致性（consistency）：事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
• 隔離性（isolation）：一個事務的執行不能被其他事務幹擾。即一個事務內部的操作及使用的資料對並發的其他事務是隔離的，並發執行的各個事務之間不能互相干擾。
• 持久性（durability）：指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他動作或故障不應該對其有任何影響。

事務鎖

事務並發不一致性導致的問題：

• 幻讀：事務T1讀取一條指定where條件的語句，返回結果集。此時事務T2插入一行新記錄並commit，恰好滿足T1的where條件。然後T1使用相同的條件重新查詢，結果集中可以看到T2插入的記錄，這條新紀錄就是幻想。
• 不可重複讀取：事務T1讀取一行記錄，緊接著事務T2修改了T1剛剛讀取的記錄並commit，然後T1重新查詢，發現與第一次讀取的記錄不同，這稱為不可重複讀取。
• 髒讀：事務T1更新了一行記錄，還未提交所做的修改，這個T2讀取了更新後的資料，然後T1執行復原操作，取消剛才的修改，所以T2所讀取的行就無效，也就是髒資料。

--於是引入事務鎖，以保證資料的完整性。

四種隔離等級：

• 序列化
• 可重複讀
• 讀已提交
• 讀未提交

交易處理-TCL

事務控制語言TCL，用於事務的提交/復原。TCL語句即 COMMIT 提交、ROLLBACK 復原，SAVEPOINT 儲存點。

提交：COMMIT

事務提交 COMMIT：將事務中對資料庫的修改進行永久儲存。

• 顯式提交：需要主動提交SQL語句對於資料庫的修改，未提交之前可以rollback。如DML操作。
• 隱式提交：SQL語句執行結束自動認可，無法rollback。如DDL，DCL。

復原：ROLLBACK

復原 ROLLBACK：取消事務中對資料庫進行的修改。

儲存點：SAVEPOINT

savepoint <savepoint_name> 

復原至儲存點

rollback to savepoint <savepoint_name>

資料庫設計範式

實際設計原則：根據業務儘可能的減少多表查詢，不必完全遵循設計範式。

第一範式：確保表中每個欄位都不可拆分。

第二範式：消除了部分函數依賴；確保表中每列都與主鍵相關（主要針對聯合主鍵）。

第三範式：消除了傳遞函數依賴；確保每列與主鍵直接相關，而不是間接相關。

Oracle | DDL&約束&DCL&TCL