3. 視圖-觸發器-存儲過程-索引

視圖視圖（view）是一種虛擬存在的表，是一個邏輯表，本身並不包含數據。作為一個select語句保存在數據字典中的。通過視圖，可以展現基表的部分數據；視圖數據來自定義視圖的查詢中使用的表，使用視圖動態生成。意義簡單：方便操作，特別是查詢操作，減少複雜的SQL語句，增強可讀性；安全：資料庫授權 ...

視圖
觸發器
存儲過程
- 游標
- 異常處理句柄
索引
存儲引擎
- - MyISAM的B+Tree
  - InnoDB的B+Tree

視圖

視圖（view）是一種虛擬存在的表，是一個邏輯表，本身並不包含數據。作為一個select語句保存在數據字典中的。通過視圖，可以展現基表的部分數據；視圖數據來自定義視圖的查詢中使用的表，使用視圖動態生成。

意義

簡單：方便操作，特別是查詢操作，減少複雜的SQL語句，增強可讀性；
安全：資料庫授權命令不能限定到特定行和特定列，視圖可以把許可權限定到行列級別；

個人建議：使用視圖建議不要考慮修改視圖的操作，視圖的優勢在於查詢。

# 創建視圖，視圖名字不能和表名一致
create view 視圖名(列名1, 列名2, ...) as (查詢表達式)
# 視圖要修改細節的話刪除了重新創建就好了
drop view if exists 視圖名

觸發器

它是與表事件相關的特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發。

原理: 觸發器所依附的表稱為基本表，當觸發器表上發生select/update/delete等操作時，會自動生成兩個臨時的表（new表和old表，只能由觸發器使用）。發生insert操作時，新的內容會被插入到new表中；發生delete操作時，舊的內容會被移到old表中；在update操作時，舊的內容會被移到old表中，新的內容會出現在new表中。

# 創建觸發器
CREATE TRIGGER 觸發器名稱 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名/視圖名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno
# 查看觸發器
SHOW TRIGGERS
# 刪除觸發器
DROP TRIGGER 觸發器名稱

存儲過程

存儲過程：（PROCEDURE）是事先經過編譯並存儲在資料庫中的一段SQL語句的集合。

DELIMITER $$

CREATE

    PROCEDURE 資料庫名.存儲過程名([in變數名 類型,out 參數 2，inout 參數3, ...])
    
	BEGIN
		[DECLARE 變數名 類型 [DEFAULT 值];]
		存儲過程的語句塊;
	END$$

DELIMITER ;

游標

游標的作用就是用於對查詢資料庫所返回的記錄進行遍歷，以便進行相應的操作；

DECLARE 游標名 CURSOR FOR (查詢語句);

異常處理句柄

DECLARE (continue/exit) HANDLE FOR 異常名稱(ID) 語句

實例：

DELIMITER $$

CREATE
    PROCEDURE de()
	-- 存儲過程體
	BEGIN
		-- DECLARE聲明 用來聲明變數的
		DECLARE assignee_id INT;
		DECLARE founder_id INT;
		DECLARE x INT;
		DECLARE cur CURSOR FOR SELECT assignee_id, founder_id FROM task_assign;
		DECLARE CONTINUE HANDLER FOR 1329 SET x = 500;
		OPEN cur;
		WHILE TRUE DO
		FETCH cur into assignee_id, founder_id;
		SELECT assignee_id, founder_id;
		END WHILE;
		CLOSE cur;
		SELECT x;
	END$$

DELIMITER ;

調用存儲過程

CALL demo8([in參數],@[out參數]);

所有參數預設設置為IN

搜索存儲過程

# 顯示所有的存儲過程
SHOW PROCEDURE STATUS;
# 顯示特定資料庫的存儲過程
SHOW PROCEDURE STATUS WHERE db = 資料庫名字 AND NAME = 存儲過程的名字;
# 模糊搜索存儲過程
SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

獲取存儲過程的源碼

SHOW CREATE PROCEDURE 存儲過程名;

刪除存儲過程

DROP PROCEDURE 存儲過程名;

索引

單列索引

索引類型

NORMAL：普通的索引類型，相當於目錄。
UNIQUE：唯一索引，一旦建立唯一索引，那麼整個列中將不允許出現重覆數據。
- 每個表的主鍵列，有且僅有一個主鍵索引，是特殊的唯一索引。
  - 不出現重覆
  - 不能為NULL
  - 可以選擇自動遞增
- 每張表可以有多個唯一索引，但只能有一個主鍵索引。
SPATIAL：空間索引，空間索引是對空間數據類型的欄位建立的索引，MYSQL中的空間數據類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON，不常用。
FULLTEXT：全文索引（MySQL 5.6 之後InnoDB才支持），它作為模糊匹配的一種優秀的解決方案，使用的效率要比使用like %更高，並且支持多種匹配方式。只有欄位的數據類型為 char、varchar、text 及其系列才可以建全文索引。

全文索引的使用

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (body));

INSERT INTO articles VALUES
	(NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
	(NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
	(NULL,'Optimising MySQL','In this tutorial we will show ...'),
	(NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
	(NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
	(NULL,'MySQL Security', 'When configured properly, MySQL ...');

使用全文索引進行模糊匹配：

SELECT * FROM articles WHERE MATCH (body) AGAINST ('database');

它的效率遠高於以下這種寫法：

SELECT * FROM articles WHERE body like '%database%';

組合索引

將多個欄位索引組合為一個索引

索引底層原理

通過數據結構（表內部的規律），降低資料庫的IO成本，提高數據的檢索效率；但是相應是索引會占據額外的磁碟空間，而為了維持表內部的規律，也會降低更新表的效率。

哈希表

散列表(哈希表)：key的值通過哈希函數直接映射為value所在的地址。

點擊查看源網頁

優點：

查詢效率非常快，能達到O(1)

缺點：

Hash索引僅僅能滿足“=”，“in”查詢條件，不能使用範圍查詢。
Hash碰撞問題(計算出的哈希值一致)
不能用部分索引鍵來搜索，因為組合索引必須要有全部的索引才能計算出對應的哈希值

平衡二叉樹

平衡二叉樹查找的效率是log₂N，查詢節點1需要IO讀取（4，2）節點。

BTree

由於InnoDB存儲引擎一次可以讀取一頁的數據量（預設16K），而降低二叉樹的高度又可以減少查詢次數，這樣就可以將平衡二叉樹改為平衡多叉樹，進而減少磁碟的IO次數。

樹中每個結點最多含有m個孩子（m >= 2）
除根結點和葉子結點外，其它每個結點至少有[ceil(m / 2)]個孩子。(向上取整)
若根結點不是葉子結點，則至少有2個孩子。
所有葉子結點都出現在同一層。
每個非終端結點中包含有n個鍵值信息： (P1，K1，P2，K2，P3，......，Kn，Pn+1)。其中：
1. Ki (i=1...n)為鍵值，且鍵值按順序升序排序K(i-1)< Ki。
2. Pi為指向子樹根的結點，且指針P(i)指向的子樹中所有結點的鍵值均小於Ki，但都大於K(i-1)。
3. 鍵值的個數n必須滿足： [ceil(m / 2)-1] <= n <= m-1。