1. 視圖

視圖是虛擬的表，它是動態檢索的部分。使用視圖的原因：避免重復的SQL語句；使用表的部分而不是全部；限制用戶只能訪問表的部分以保護數據；更改數據格式和表示。
視圖要唯一命名；可以用視圖創建視圖；需要管理創建視圖的權限。

-- case1: 簡化SQL語句,為3表的連接創建視圖
CREATE VIEW ProductCustomers AS
SELECT cust_nam， cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_idAND OrderItems.order_num = Orders.order_num;SELECT cust_nem, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';-- csae2: 重新格式化數據，為格式化的查詢創建視圖
CREATE VIEW VendorLocations AS
SELECT CONCAT(RTRIM(vend_name), ' (', RTRIM(vend_country), ')') AS vend_title
FROM Vendors
ORDER BY vend_name;SELECT * FROM VendorLocations;-- case3: 過濾數據
CREATE VIEW CustomersEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;SELECT * FROM CustomerEmailList;-- case4: 為使用計算字段的查詢創建視圖
CREATE VIEW OrderItemExpanded AS
SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price
FROM OrderItems;SELECT *
FROM OrderItesExpanded
WHERE order_num = 20008;

2. 存儲過程

存儲過程（Stored Procedure）是一組為了完成特定功能而預先編譯并存儲在數據庫中的 SQL 語句集合。它類似于編程語言中的函數，有輸入輸出參數，可以接收用戶傳遞的數據，經過一系列的邏輯處理后返回結果。
存儲過程的作用：封裝復雜操作，向應用側減少操作步驟，從而減少出錯可能，有利于保證數據一致性；存儲過程內部變動對應用側無感知，基礎數據訪問被限制從而保證安全性；存儲過程以編譯形式存儲，DBMS工作量小，性能有所提高。

2.1 創建存儲過程

創建存儲過程涉及多行，為了避免默認分隔符 “;” 終止存儲過程的定義，需要使用DELIMITER操作符臨時修改分隔符，定義完成后再改回來。存儲過程中的局部變量應聲明放在最前面。
– v_rows是存儲過程中的一個整型變量，參數ListCOunt保存為存儲過程返回值。存儲過程的執行結果先保存在v_rows中，再賦值給返回參數ListCOunt

DELIMITER //
CREATE PROCEDURE MailingListCount(OUT ListCount INT)
BEGINDECLARE v_rows INT DEFAULT 0;SELECT COUNT(*) INTO v_rowsFROM CustomersWHERE cust_email IS NOT NULL;SET ListCount = v_rows;
END //
DELIMITER ;-- 查看已經創建的存儲過程
show procedure status;-- 刪除存儲過程
drop procedure xxx;

2.2 執行存儲過程

SET @ListCount = 0;
CALL MailingListCount(@ListCount);
SELECT @ListCount;

3. 游標

游標主要用于從結果集中相鄰地取出數據。游標只能讀取，不能更新和刪除。
游標主要是在存儲過程、函數或觸發器內部使用，并且它們是局部對象，僅在定義它們的塊內可見，不具備全局的視圖或系統表來直接展示所有已創建的游標。
游標的定義沒有執行數據檢索操作。打開游標操作才實際執行檢索操作。

-- 僅僅檢索一行
CREATE PROCEDURE CurTest()
BEGINDECLARE v_cust_id char(10);DECLARE v_cust_name char(50);DECLARE CustCursor CURSORFORSELECT cust_id, cust_name FROM CustomersWHERE cust_country = 'USA';OPEN CustCursor;FETCH CustCursor INTO v_cust_id, v_cust_name;SELECT v_cust_id, v_cust_name;CLOSE CustCursor;
END;-- 檢索多行
CREATE PROCEDURE CurTest()
BEGINDECLARE v_cust_id char(10);DECLARE v_cust_name char(50);DECLARE done INT;DECLARE CustCursor CURSORFORSELECT cust_id, cust_name FROM CustomersWHERE cust_country = 'USA';DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN CustCursor;read_loop: LOOPFETCH CustCursor INTO v_cust_id, v_cust_name;IF done THENLEAVE read_loop;END IF;SELECT CONCAT('Customer ID: ', v_cust_id, ', Name: ', v_cust_name);END LOOP;CLOSE CustCursor;
END;

4. 約束

約束是管理插入或者處理數據庫數據的規則，DBMS通過在數據庫表上施加約束來實現引用完整性。OrderItems表的order_num字段引用了Orders表的order_num字段，OrderItems表中的order_num字段值一定要出現在Orders表的order_num列中，這就是引用完整性。

4.1 主鍵約束

-- 創建主鍵方法一：
CREATE TABLE Vendors
(vend_id CHAR(10) NOT NULL PRIMARY KEY,...
)-- 創還能主鍵方法二：
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY(vend_id);

4.2 外鍵約束

外鍵是表中的一列，其值必須在另一表的主鍵中。比如Orders表的cust_id可以作為外鍵，其值在Customers表中cust_id列中，而cust_id列式Customers表的主鍵。再如OrderItems表的order_num列可以作為外鍵，而order_num列又是Orders表的主鍵。設置外鍵的作用是，外鍵取值范圍限定在其作為主鍵的表所給出的那些主鍵值上。

-- 定義外鍵約束方法一：
CREATE TABLE Orders
(order_num INT NOT NULL PRIMARY KEY,order_date DATETIME NOT NULL,cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);
-- 定義外鍵方法二：
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFRENCES Customers(cust_id);

4.3 唯一約束

唯一約束和和主鍵的區別：唯一約束允許NULL值；唯一約束允許修改或者更新；唯一約束列值可以重用；唯一約束不能用來定義外鍵；

-- 定義唯一約束之一
CREATE TABLE Employees
(employee_id INT NOT NULL PRIMARY KEY,secure_no INT DEFULT 000,phone char(11) DEFAULT '',name char(10) DEFAULT '',age INT DEAFULT 18,UNIQUE(secure_no, phone)
)-- 定義唯一約束之二：命名的唯一約束
ALERT TABLE Employees
ADD CONSTRAINT unique_constraint
UNIQUE (secure_no, phone);-- 定義唯一約束之三：匿名的唯一約束
ALERT TABLE Employees
ADD UNIQUE(secure_no, phone);-- 刪除唯一約束，唯一約束本質是唯一索引
ALTER TABLE employees
DROP INDEX unique_constraint;

4.4 檢查約束

檢查約束用于確保插入的值滿足檢查的條件。

-- case1: 檢查字段quantity值大于0
CREATE TABLE OrderItems
(order_num INT NOT NULL,order_item INT NOT NULL,prod_id CHAR(10) NOT NULL,quantity INT NOT NULL CHECK (quantity > 0),item_price MONEY NOT NULL
);-- case2：檢查性別是否只包含字母M和字母F
ALTER TABLE employee
ADD CONSTRAINT chk_gender CHECK (gender IN ('M', 'F'));

5. 觸發器

當特定的數據庫操作發生時要執行一些額外的工作，就需要使用觸發器。觸發器是綁定到單個表的。執行對表的特定操作會觸發觸發器的操作。觸發器的用途：操作執行前數據統一格式；記錄某個表的變動到另一個表；進行額外驗證；產生計算列的值；更新時間戳。
注意點：觸發器操作可以在其所綁定的操作之前或者之后執行，MySQL不允許在觸發器操作對同一張表在綁定操作后再進行操作。比如只能在插入操作之前把要插入的數據轉為大寫。

-- 插入前轉為大寫
CREATE TRIGGER customer_state
BEFORE INSERT ON Customers
FOR EACH ROW
BEGIN-- 在插入之前將 cust_state 轉換為大寫SET NEW.cust_state = UPPER(NEW.cust_state);
END;