SQL必知必會 筆記 第二十二章 瞭解進階SQL特性，sql必知

SQL必知必會 筆記 第二十二章 瞭解進階SQL特性，sql必知
22.1約束

為正確地進行關聯式資料庫設計，需要一種方法來保證只在表中插入合法的資料。例如，如果Orders表格儲存體訂單資訊，OrderItems表格儲存體訂單詳細內容，應該保證Orderitems中引用的任何訂單ID存在於Orders中。類似地，在Orders表中引用的任意使用者必須存在於Customers表中。
雖然可以在插入新行時進行檢查，但最好不要這樣做，原因如下：
（1）如果在客戶機層面上實施資料庫完整性規則，則每個客戶機都被迫要實施這些規則，但很可能會有一些客戶機不實施這些規則。
（2）在執行UPDATE和DELETE操作時，也必須實施這些規則。
（3）執行客戶機端檢查是非常耗時的，而DBMS執行這些檢查會相對高效。
約束（constraint）管理如何插入或處理資料庫資料的規則。
DBMS通過在資料庫表上施加約束來實施參考完整性。大多數約束是在表定義中定義的。

22.1.1主鍵

主鍵是一種特殊的約束，它用來保證一個列（或一組列）中的值是唯一的，並且永不改動。換句話說，表中的一個列（或多個列）的值唯一標識表中的行。
表中任意列只要滿足以下條件，都可以用於主鍵：
（1）任意兩行的主索引值都不相同。
（2）每行都具有一個主索引值（即列中不允許NULL值）。
（3）包含主索引值的列不修改或更新。
（4）主索引值不能重用。如果從表中刪除某一行，其主索引值不分配給新行。
定義主鍵的一種方法是建立它，如下所示：

CREATE TABLE Vendors{     vend_id        CHAR(10)        NOT NULL PRIMARY KEY,     vend_name      CHAR(50)        NOT NULL,     vend_address   CHAR(50)        NULL,     vend_city      CHAR(50)        NULL,     vend_state     CHAR(5)         NULL,     vend_zip       CHAR(10)        NULL,     vend_country   CHAR(50)        NULL};

修改為主鍵

ALTER TABLE VendorsADD CONSTRAINT PRIMARY KEY(vend_id);

22.1.2外鍵

外鍵是表中的一個列，其值必須在另一個表的主鍵中列出。外鍵是保證參考完整性的一個極重要的成分。
例如：Orders表對每個錄入到系統的訂單包含一行。客戶資訊儲存在Customers表中。Orders表中的訂單通過客戶ID與Customers表中特定行相關聯。客戶ID為Customers表的主鍵；每個客戶都有唯一的ID。訂單號為Orders表的主鍵；每個訂單都有唯一的訂單號。
定義外鍵的的一種方法

CREATE TABLE Orders{     order_num      INTEGER        NOT NULL PRIMARY KEY,     order_date     DATETIME       NOT NULL,     cust_id        CHAR(10)       NOT NULL REFERENCES Customers(cust_id)};

也可以使用ALTER TABLE語句中也難怪CONSTRAINT文法來完成

ALTER TABLE CustomersADD CONSTRAINTFOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

外鍵可協助防止意外的刪除：除協助保證參考完整性外，外鍵還有另一個重要的作用。在定義外鍵後，DBMS不允許刪除在另一個表中具有關聯線的行。

22.1.3唯一約束

唯一約束用來保證一個列中的資料唯一。它們類似於主鍵，但存在幾個重要的區別：
（1）表可包含多個唯一約束，但每個表只允許一個主鍵。
（2）唯一約束列可包含NULL值。
（3）唯一約束列可修改或更新。
（4）唯一約束列的值可重複使用。
（5）與主鍵不一樣，唯一約束不能用來定義外鍵。
唯一約束的文法類似於其他約束的文法。唯一約束既可以用UNIQUE關鍵字在表定義中定義，也可以用單獨的CONSTRAINT定義。

22.1.4檢查約束

檢查約束用來保證一個列中的資料滿足一組指定的條件。檢查約束的外掛程式用途為：
（1）檢查最小或最大值。
（2）指定範圍。
（3）只允許特定的值。
定義時給出約束：

CREATE TABLE OrderItems{     order_num     INTEGER     NOT NULL,     order_item    INTEGER     NOT NULL,     prod_id       CHAR(10)    NOT NULL,     quantity      INTEGER     NOT NULL CHECK(quantity > 0),     item_price    MONEY       NOT NULL};

為檢查名為gender的列只包含M或F，可編寫如下的ALTET TABLE語句

ADD CONSTRAINT CHECK (gender LIKE '[MF]');

22.2索引

索引用來排序資料以加快搜尋和排序操作的速度。理解索引的最好辦法是想象一本書後的索引。
可以在一個或多個列上定義索引，使DBMS儲存其內容的一個排過序的列表。在定義了索引後，DBMS用與使用書的索引類似的方法是用它。DBMS搜尋排過序的索引，找出匹配的位置，然後檢索這些行。
在開始建立索引前，應該記住以上內容：
（1）索引改善檢索操作的效能，但降低資料插入、修改和刪除的效能。在執行這些操作時，DBMS必須動態地更新索引。
（2）索引資料可能要佔用大量的儲存空間。
（3）並非所有資料都適合於索引。唯一性不好的資料從索引得到的好處不比具有更多可能值的資料從索引得到的好處多。
（4）索引用於資料過濾和資料排序。如果你經常以某種特定的順序排序資料，則該資料可能是索引的備選。
（5）可以在索引中定義多個列。這樣的索引僅在以州加城市的順序排序時有用。如果想按城市排序，則這種索引沒有用處。
索引用CREATE INDEX語句建立。下面的語句在Products表的產品名列上建立一個簡單的索引：

CREATE INDEX prod_name_indON PRODUCTS(prod_name);

索引必須唯一命名。這裡的索引名在關鍵字CREATE INDEX之後定義。ON用來指定被索引的表，而索引中包含的列在表名後的圓括弧中給出。
檢查索引：索引的效率隨表資料的增加或改變而變化。許多資料庫管理員發現，過去建立的某個理想的索引在幾個月的資料處理後可能不理想了。最好頂起檢查索引，並根據需要對索引進行調整。

22.3觸發器

觸發器是特殊的預存程序，它在特定的資料庫活動發生時自動執行。觸發器可以與特定表上的INSERT、UPDATE和DELETE操作相關聯。
與預存程序不一樣，觸發器與單個的表相關聯。與Orders表上的INSERT操作相關聯的觸發器只在Orders表中插入行時執行。類似地，Customers表上的INSERT和UPDATE操作的觸發器只在表上出現這些操作時執行。
觸發器內的代碼具有以下資料的訪問權：
（1）INSERT操所中的所有新資料；
（2）UPDATE操作中的所有新資料和舊資料；
（3）DELETE操作中刪除的資料。
觸發器的一些常見用途：
（1）保證資料一致。
（2）基於某個表的變動在其他表上執行活動。
（3）進行額外的驗證並根據需要回退資料。
（4）計算計算資料行的值或更新時間戳記。
建立一個觸發器，它對所有INSERT和UPDATE操作，轉換Customers表中的cust_state列為大寫。

CREATE TRIGGER customer_stateAFTER INSERT OR UPDATEFOR EACH ROWBEGINUPDATE CustomersSET cust_state = Upper(cust_state)WHERE Customers.cust_id = :OLD.cust_idEND;

約束比觸發器更快：一般來說，約束的處理比觸發器快，因此在可能的時候，應該盡量使用約束。

22.4資料庫安全

大多數的DBMS都給管理員提供了管理機制，可利用管理機制授予或限制對資料的訪問。
任何安全系統的基礎都是使用者授權和身份確認。這是一種處理，通過這種處理對使用者進行確認，以保證他是有權使用者，允許他執行他試圖執行的操作。有的DBMS為此目的結合使用了作業系統的安全措施，而有的維護自己的使用者及密碼列表，還有一些結合使用外部目錄服務伺服器。
需要保護的操作：
（1）對資料庫管理功能的訪問。
（2）對特定資料庫或表的訪問。
（3）訪問的類型。
（4）僅通過視圖或預存程序對錶進行訪問。
（5）建立多層次的安全措施，從而允許多種基於登入的訪問和控制。
（6）限制系統管理使用者帳號的能力。
安全性通過SQL的GRANT和REVOKE語句來管理，不過，大多數DBMS提供了互動管理使用程式，這些使用程式在內部使用GRANT和REVOKE語句。