3. 視圖-觸發器-存盤程序-索引-有解無憂

視圖
觸發器
存盤程序
- 游標
- 例外處理句柄
索引
- 單列索引
- 組合索引
- 索引底層原理
  - 哈希表
  - 平衡二叉樹
  - BTree
  - B+Tree
存盤引擎
- - 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，