從MySQL到ORM(三)：連接、存儲過程和用戶許可權

一、聯結表

　　數據仍使用前文中的數據。

　　1、子查詢

　　作為子查詢的SELECT語句只能查詢單個列。企圖檢索多個列將返回錯誤。

-- 作為查詢條件使用
-- 查看TNT2訂單對應的客戶ip（order表）
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');
-- 根據TNT2訂單的客戶ip查看客戶信息（cust表）
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2'));
-- 作為計算欄位使用
-- 計算每個客戶的訂單數據
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

　　2、創建鏈接

圖片示例	鏈接方式	關鍵字	語句示例
	內連接	INNER JOIN ... ON	SELECT <select_list> FROM A INNER JOIN B ON A.key = B.key
	左外連接	LEFT JOIN ... ON	SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key
	左外連接	LEFT JOIN ... ON ... WHERE B.key IS NULL	SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL
	右外連接	RIGHT JOIN ... ON	SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key
	右外連接	RIGHT JOIN ... ON ... WHERE A.key IS NULL	SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key WHERE A.key IS NULL
	全外連接	UNION	SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key UNION SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key
	全外連接	(WHER IS NULL) UNION ... (WHER IS NULL)	SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL UNION SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key WHERE A.key IS NULL

 　　用例：

-- 笛卡爾積：n * m
-- SELECT * FROM vendors, products;
SELECT orders.cust_id, cust_name FROM orders CROSS JOIN customers;-- 沒有WHERE子句
-- 內連接
-- SELECT * FROM vendors, products WHERE vendors.vend_id = products.vend_id;
SELECT * FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id;
-- 左外連接(a)，根據A表相應欄位取列
SELECT orders.cust_id, cust_name FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id;
-- 左外連接b，只取A表中無B表相應欄位的列
SELECT vendors.vend_id, vend_name, vend_country  FROM vendors LEFT JOIN products on vendors.vend_id = products.vend_id where products.vend_id IS NULL ORDER BY vend_id;
-- 右外連接(a)，同左外連接(a)
SELECT orders.cust_id, cust_name FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id;
-- 右外連接(b)，同左外連接(b)
SELECT customers.cust_id, cust_name FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL;

-- 全外連接，兩者id相等的行和不相等的行都保留
SELECT customers.cust_id, orders.order_num FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id
UNION
SELECT customers.cust_id, orders.order_num FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id;
-- 全外連接，只保留兩者id不相等的行，這裡兩個WHERE子句一樣是因為customers的id欄位完全包含orders中的欄位
SELECT customers.cust_id, orders.order_num FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL
UNION
SELECT customers.cust_id, orders.order_num FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL

　　3、視圖

　　視圖是虛擬的表。它只包含使用時動態檢索數據的查詢，換言之，視圖存儲查詢語句。保存查詢語句可以使用視圖。視圖的一些規則：

　　- 與表名一樣，視圖必須唯一命名。

　　- 視圖數目沒有限制。

　　- 為了創建視圖，必須具有足夠的訪問許可權。

　　- 視圖可以嵌套。

　　- 視圖查詢中不要使用GROUP BY。

　　- 視圖不能索引，也不能有關聯的觸發器或預設值。

-- 創建視圖
-- CREATE VIEW viewname AS SELECT *
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;

-- 執行視圖查詢時會首先執行視圖
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id ='FB';
SELECT * FROM orderitemsexpanded WHERE order_num = 20009;
-- 刪除視圖
-- DROP VIEW viewname;
DROP VIEW productcustomers;

二、存儲過程

　　1、存儲過程

　　1.簡介

　　存儲過程是存儲在資料庫目錄中的一段聲明性SQL語句。它像是編程語言中的函數或者可執行代碼塊。通過聲明一段可執行的SQL語句，既可以避免一行行的輸入SQL語句，又可以將這段SQL語句重覆使用。

　　存儲過程的優點：

　　- 通常存儲過程有助於提高應用程式的性能。

　　- 存儲過程有助於減少應用程式和資料庫伺服器之間的流量，因為應用程式不必發送多個冗長的SQL語句，而只能發送存儲過程的名稱和參數。

　　- 存儲的程式對任何應用程式都是可重用的和透明的。

　　- 存儲的程式是安全的。

　　存儲過程的缺點：

　　- 如果使用大量存儲過程，那麼使用這些存儲過程的每個連接的記憶體使用量將會大大增加。

　　- 存儲過程的構造使得開發具有複雜業務邏輯的存儲過程變得更加困難。

　　- 很難調試存儲過程。

　　- 開發和維護存儲過程並不容易。

　　2.使用

-- 創建存儲過程
DELIMITER // -- DELIMITER // 和DELIMITER;用於劃分一塊範圍來聲明存儲過程
CREATE PROCEDURE GetAllProducts()-- CRAET PROCEDURE 創建一個存儲過程
   BEGIN-- 存儲過程的主體的開始
   DECLARE id INT(20) DEFAULT 1003;-- DECLARE variable datatype(size) DEFAULT value,聲明局部變數；它只能在BEGIN和END之間生效
    SET id = 1001;-- 變數賦值
   SELECT * FROM products WHERE vend_id = id;-- SQL語句
   END //-- 存儲過程的主體的結束，結尾用//
DELIMITER ;

-- 調用存儲過程
CALL GetAllProducts();
-- 刪除存儲過程
DROP PROCEDURE IF EXISTS `GetAllProducts`;

-- 設置參數
DELIMITER //
CREATE PROCEDURE GetProductsByVendId(IN id INT(20), OUT outcome VARCHAR(20))-- 參數預設首碼是IN，即只允許調用函數時給參數傳遞值；OUT則表示將存儲過程的運行結果傳遞出去,它是個單值參數;
    BEGIN
    SELECT count(*) INTO outcome FROM products WHERE vend_id = id;
    END //
DELIMITER ;
CALL GetProductsByVendId(1003, @outcome);-- 不直接列印結果，而是把結果傳遞給了全局變數@outcome
SELECT @outcome;-- 設置全局變數可以用SET variable = value;

-- 條件語句
/* IF condition THEN 
    sql
ELSEIF condition THEN
    sql
...
ELSE 
    sql
END IF */

DELIMITER //
CREATE PROCEDURE GetPriceLevel(IN prod_id CHAR(10), OUT price_level VARCHAR(20))
    BEGIN
    DECLARE price DECIMAL(8,2);
    SELECT prod_price INTO price FROM products WHERE products.prod_id = prod_id;
    
    IF price <= 5 THEN
        SET price_level = 'cheap';
    ELSEIF (price > 5 AND price <= 10) THEN
        SET price_level = 'ordinary';
    ELSE
        SET price_level = 'expensive';
    END IF;
    END //
DELIMITER ;
CALL GetPriceLevel('ANV01', @price_list);
SELECT @price_list;

-- 迴圈語句
/* WHILE condition DO
   statements
END WHILE */
-- 函數 略
-- 游標 https://www.yiibai.com/mysql/cursor.htm

　　2、事務處理

　　1.概念

　　事務處理(transaction processing)用於保證SQL操作的完整性。它提供一種處理機制來應對SQL或者其它環境因素可能造成的異常結果。事務處理中的幾個術語：

　　- 事務(transaction)，指一組SQL語句。

　　- 回退(rollback)，指撤銷指定SQL語句的過程。

　　- 提交(commit)，指將未存儲的SQL語句結果些人資料庫表。

　　- 保留點(savepoint)，指事務處理中設置的臨時占位符(placeholder)，它可以回退。

　　事務處理用來管理(可以回退)INSERT、UPDATE和DELETE語句，不能回退SELECT語句，也不能回退CREATE或者DROP操作。

　　在MySQL中，事務開始使用COMMIT或ROLLBACK語句開始工作和結束。開始和結束語句的SQL命令之間形成了大量的事務。

　　2.ACID特性

　　事務有以下四個標準屬性的縮寫ACID，通常被稱為：

　　- 原子性: 指事務是一個不可再分割的工作單元，事務中的操作要麼都發生，要麼都不發生。

　　- 一致性: 在事務開始之前和事務結束以後，資料庫的完整性約束沒有被破壞。這是說資料庫事務不能破壞關係數據的完整性以及業務邏輯上的一致性。

　　- 隔離性: 多個事務併發訪問時，事務之間是隔離的，一個事務不應該影響其它事務運行效果。

　　- 持久性: 在事務完成以後，該事務所對資料庫所作的更改便持久的保存在資料庫之中，並不會被回滾。

　　3.簡單用例

-- MySQL預設出錯自動回滾，沒出錯自動提交
BEGIN;
SAVEPOINT place;
INSERT INTO orders VALUES(20010, '2005-09-08 00:00:00', 10001);
ROLLBACK;-- ROLLBACK place
COMMIT

　　在COMMIT提交之前，ROLLBACK語句來撤銷事務中所做的每一項工作，即便工作是成功的也可以撤銷，它可以一直撤銷到事務的開始。在COMMIT提交之後，只能使用DELETE，INSERT或者UPDATE語句，ROLLBACK是不行的。

三、訪問控制和用戶許可權管理

　　內容摘自MySQL教程(https://www.yiibai.com/mysql)

　　1、訪問控制

　　當客戶端連接到伺服器時，MySQL訪問控制有兩個階段：

　　- 連接驗證：連接到MySQL資料庫伺服器的客戶端需要有一個有效的用戶名和密碼。此外，客戶端連接的主機必須與MySQL授權表中的主機相匹配。

　　- 請求驗證：當連接成功建立後，對於客戶端發出的每個語句，MySQL會檢查客戶端是否具有足夠的許可權來執行該特定語句。 MySQL能夠檢查資料庫，表和欄位級別的許可權。

　　MySQL安裝程式自動創建一個名為mysql的資料庫。 mysql資料庫包含五個主要的授權表(user、db、host、tables_priv和columns_priv)，它們存儲了不同級別上對用戶許可權的設置。並可通過GRANT和REVOKE等語句間接操作這些表。

　　2、用戶許可權管理

　　MySQL8以上提供了role對象進行多用戶許可權管理。

/* GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]
*/

-- 創建單個用戶賬戶
-- CREATE USER username@localhost IDENTIFIED BY passwd
CREATE USER dbadmin@localhost IDENTIFIED BY 'pwd123';
-- 查看該用戶許可權
SHOW GRANTS FOR dbadmin@localhost;
-- 賦予該用戶操作許可權
GRANT ALL PRIVILEGES ON test.customer TO dbadmin@localhost;-- 可以到mysql.table_priv授權表中查看設置
-- 更新
FLUSH PRIVILEGES;
SHOW GRANTS FOR dbadmin@localhost;
SELECT * FROM mysql.user;
-- 刪除許可權
REVOKE ALL PRIVILEGES ON test.customer FROM dbadmin@localhost;

　　dbadmin@localhost用來指定賬戶名和主機地址。

　　GRANT子句設置用戶許可權，包括對資料庫級、表級、欄位、查詢語句等的設置。