《Mysql必知必會》讀書筆記

《Mysql必知必會》(MySQL Crash Course)內容很豐富，mysql的各個方面都涉及到了，從基本的SQL增刪改查到觸發器、事務以及安全管理和效能提升，本書都做了精簡的介紹。
相比於另外兩本相關的初學者讀物——《SQL基礎教程》MICK著和《MySQL與MariaDB學習指南》。這本書可能更適合想對mysql有全面瞭解的（比如對mysql的高階功能）或者想成為資料庫管理員的人閱讀。

簡要介紹

Ref: http://dev.mysql.com/

1. 本書的配套網站MySQL Crash Course，原作者很厲害，畢竟他之前就出了一本堪稱全世界用得最多的一本SQL教程-- Sams Teach Yourself SQL in 10 Minutes( 中文版《SQL必知必會》，人民郵電出版社出版)
2. 資料庫(database) 是儲存有組織的資料的容器(通常是一個文 件或一組檔案，而資料庫軟體應稱為DBMS（資料庫管理系統）
3. 模式(schema) && 資料庫(database)? —— 一般情況下，模式用作資料庫的同義詞，模式(schema) 關於資料庫和表的佈局及特性的資訊
4. 記錄(record) && 行(row)？—— 在很大程度上，這兩個術語是可以互相 替代的，但從技術上說，行才是正確的術語
5. SQL(發音為字母S-Q-L或sequel)是結構化查詢語言(Structured Query Language)的縮寫。
6. DBMS可分為兩類:一類為基於共享檔案系統的DBMS，另一類為基於客戶機—伺服器的DBMS。
   
   • 前者如Microsoft Access和FileMaker等，用於桌面用途，通常不用於高階或更關鍵的應用
   • 後者如MySQL、Oracle以及Microsoft SQL Server等資料庫是基於客戶機—服 務器的資料庫。
7. 寫SQL語句的慣例——關鍵詞都大寫，表明列名小寫

基本用法

SQL基本用法可參考筆記1和筆記2，這裡僅就個別的點總結下

1. 多用SHOW, HELP SHOW, SHOW GRANTS, SHOW ERRORS, SHOW WARNINGS
   
   • DESCRIBE customers;是 SHOW COLUMNS FROM customers;的快捷方式
   • INFORMATION_SCHEMA
2. 在處理SQL語句時，其中所有空格都被忽略。
3. 查詢結果預設是升序排序(從A到Z)
4. !=等同於<>操作符
5. SQL(像多數語言一樣)在處理OR操作符前，優先處理AND操作符！！！
   
   • 最好使用圓括號明確地分組相應的操作符
   • WHERE id=1002 OR id=1003 AND price>=10 這個WHERE字句的計算次序呢？
6. MySQL支援使用NOT對IN、BETWEEN和 EXISTS子句取反，這與多數其他DBMS允許使用NOT對各種條件 取反有很大的差別。
7. 從技術上說，LIKE是謂詞而不是操作符。
8. 雖然似乎%萬用字元可以匹配任何東西，但有一個例外，即NULL。 即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作為產品名的行
9. MySQL正規表示式
   
   • 用REGEXP代替LIKE
   • 為了匹配特殊字元，必須用\\為前導。\\-表示查詢-，\\.表示查詢.。如WHERE name REGEXP '\\.'
   • \ && \\ ? 多數正規表示式實現使用單個反斜槓轉義特殊字元， 以便能使用這些字元本身。但MySQL要求兩個反斜槓(MySQL 自己解釋一個，正規表示式庫解釋另一個)。
   • 常用的字元類(character class)， 如[:alnum:]匹配任意字母和數字(同[a-zA-Z0-9]), [:alpha:]匹配任意字元(同[a-zA-Z])
   • 定位元字元: [[:<:]]和[[:>:]]分別匹配詞的開始和結尾
10. LIKE和REGEXP 的不同在於，LIKE匹配整個串而REGEXP匹配子串。利用定位 符，通過用^開始每個表示式，用$結束每個表示式，可以使 REGEXP的作用與LIKE一樣。
11. 多數DBMS使用+或||來實現拼接， MySQL則使用Concat()函式來實現
12. SUM(), MIN(), MAX()會忽略列值為NULL的行。
13. COUNT()函式有兩種使用方式。
    
    • 使用COUNT(*)對錶中行的數目進行計數，不管表列中包含的是空 值(NULL)還是非空值。
    • 使用COUNT(column)對特定列中具有值的行進行計數，忽略NULL值，如果指定列名，則DISTINCT只能用於COUNT()。DISTINCT 不能用於COUNT(*)，因此不允許使用COUNT(DISTINCT)， 否則會產生錯誤。類似地，DISTINCT必須使用列名，不能用於計算或表示式。
14. 分組
    
    • 如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列 中有多行NULL值，它們將分為一組。
    • 使用WITHROLLUP關鍵字，可以得到每個分組以 及每個分組彙總級別(針對每個分組)的值, GROUP BY id WITH ROLLUP
    • WHERE在資料 分組前進行過濾，HAVING在資料分組後進行過濾
15. ANSI SQL規範首選INNER JOIN語法。此外， 儘管使用WHERE子句定義聯結的確比較簡單，但是使用明確的 聯結語法能夠確保不會忘記聯結條件，有時候這樣做也能影響 效能。
16. UNION中的每個查詢必須包含相同的列、表示式或聚集函式(不過各個列不需要以相同的次序列出)。
17. 在INSERT和INTO之間新增關鍵字LOW_PRIORITY，指示MySQL 降低INSERT語句的優先順序, INSERT LOW_PRIORITY INTO
18. 如果用UPDATE語句更新多行，並且在更新這些行中的一行或多行時出一個現錯誤，則整個UPDATE操作被取消 (錯誤發生前更新的所有行被恢復到它們原來的值)。為即使是發
    生錯誤，也繼續進行更新，可使用IGNORE關鍵字，UPDATE IGNORE customers...
19. 如果僅想在一個表不存在時建立它，應該在表名後給出IF NOT EXISTS
20. last_insert_id()函式可以獲取AUTO_INCREMENT對應的值
21. 需要知道的引擎
    
    • InnoDB是一個可靠的事務處理引擎，它不支援全文字搜尋;
    • MEMORY在功能等同於MyISAM，但由於資料儲存在記憶體(不是磁碟)中，速度很快(特別適合於臨時表);
    • MyISAM是一個效能極高的引擎，它支援全文字搜尋， 但不支援事務處理。
22. RENAME TABKE xx TO yy;
23. CREATE OR REPLACE VIEW xxx, 檢視可以巢狀，但不能索引，也不能有關聯的觸發器

高階用法

1. 全文字搜尋

1. 兩個最常使用的引擎為MyISAM和InnoDB， 前者支援全文字搜尋，而後者不支援
2. 全文字搜尋在效能、明確控制和智慧化的結果(例如，一個特殊詞的搜尋將會返回包含該詞的所有行，而不區分 包含單個匹配的行和包含多個匹配的行)方面很有優勢
3. 為了進行全文字搜尋，必須索引被搜尋的列，而且要隨著資料的改 變不斷地重新索引, FULLTEXT(note_text)的表示對note_text進行索引
4. 使用兩個函式Match()和Against()執行全文字搜尋， 其中Match()指定被搜尋的列，Against()指定要使用的搜尋表示式
5. 除非使用BINARY方式， 否則全文字搜尋不區分大小寫。
6. 全文字搜尋的一 個重要部分就是對結果排序。具有較高等級的行先返回(因為這些行很 可能是你真正想要的行)。
7. Match()和Against() 用來建立一個計算列(別名為rank)，此列包含全文字搜尋計算出的等級值。
8. 文字中詞靠前的行的等級值比詞靠後的 行的等級值高。
9. 查詢擴充套件——利用查詢擴充套件，能找出可能相關的結果，即使它們並不精確包含所 查詢的詞。
   
   • WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION)
   • WHERE Match(note_text) Against('anvils')
10. 布林文字搜尋
    
    • 即使沒有FULLTEXT索引也可以使用, 但這是一種非常緩慢的操作
    • WHERE Match(note_text) Against('heavy' IN BOLLEAN MODE)
    • WHERE Match(note_text) Against('heavy -rope*' IN BOLLEAN MODE)匹配包含heavy但不包含任意以rope開始的詞的行
    • 全文字布林操作符-排除一個詞，而*是截斷操作符(可想象為用於詞尾的一個萬用字元)
    • 在布林方式中，不按等級值降序排序返回的行。
11. 在索引全文字資料時，短詞被忽略且從索引中排除。短詞定義為 那些具有3個或3個以下字元的詞(如果需要，這個數目可以更改)
12. MySQL帶有一個內建的非用詞(stopword)列表，這些詞在索引全文字資料時總是被忽略。
13. MySQL規定了一條50%規則，如果一個詞出現在50%以上 的行中，則將它作為一個非用詞忽略。50%規則不用於IN BOOLEAN MODE。
14. 忽略詞中的單引號。例如，don't索引為dont。

2. 儲存過程

1. 儲存過程簡單來說，就是為以後的使用而儲存 的一條或多條MySQL語句的集合
2. MySQL稱儲存過程的執行為呼叫，因此MySQL執行儲存過程的語句為CALL。
3. mysql命令列客戶機的分隔符
   
   • 預設的MySQL語句分隔符為;
   • DELIMITER //告訴命令列實用程式使用//作為新的語句結束分隔符
   • 除\符號外，任何字元都可以用作語句分隔符
4. SHOW PROCEDURE STATUS, SHOW PROCEDURE STATUS LIKE 'xxx', SHOW CREATE PROCEDURE xxx
5. 所有MySQL變數都必須以@開始, set total=1; select @total;
6. MySQL支援IN(傳遞給儲存過程)、OUT(從存 儲過程傳出，如這裡所用)和INOUT(對儲存過程傳入和傳出)型別的參 數。
7. 簡單範例，呼叫儲存過程則鍵入call productpricing;即可

mysql
DELIMITER //
CREATE PROCEDURE productpricing() COMMENT 'xxxx'
BEGIN
SELECT Avg(xx) AS yy INTO zz
FROM aa;
-- comment
DECLAR total DECIMAL(8,2);
IF taxable THEN
SELECT xx INTO yy;
END IF;
END //
DELIMITER ;

3. 遊標

1. 遊標(cursor)是一個儲存在MySQL伺服器上的資料庫查詢， 它不是一條SELECT語句，而是被該語句檢索出來的結果集。
2. SELECT -- MySQL檢索操作返回一組稱為結果集的行
3. 不像多數DBMS，MySQL遊標只能用於儲存過程(和函式)。
4. 使用遊標
   
   • 宣告（定義）--> 開啟使用 --> 取出資料 --> 關閉
5. 如果你不明確關閉遊標，MySQL將會在到達END語 句時自動關閉它
6. DECLARE語句的釋出存在特定的次序。 用DECLARE語句定義的區域性變數必須在定義任意遊標或控制程式碼 之前定義，而控制程式碼必須在遊標之後定義。不遵守此順序將產 生錯誤訊息。
7. 通常REPEAT語句的語法使它更適合於對遊標進行迴圈
8. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   
   • 這條語句定義了一個CONTINUE HANDLER，它是在條件出現時被執行的程式碼。
   • 這裡，它指出當SQLSTATE '02000'出現時，SET done=1。SQLSTATE '02000'是一個未找到條件，當REPEAT由於沒有更多的行供迴圈而不能繼 續時，出現這個條件。
9. 簡單例子

CREATE PROCEDURE processorders()
BEGIN
-- Declare local variable
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- Declare the cursor
DECLARE ordernumbers CURSOR FOR SELECT order_num FROM ordres;
DECLARE CONINTUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
(order_num int, total decimal(8,2))
-- Open the cursor
OPEN ordernumbers;
-- Loop through all rows
REPEAT
-- get order number
FETCH ordernumbers INTO o;
-- get the total for the order
CALL ordertotal(o, 1, t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num, total) VALUES(o, t);
-- End of loop
UNTIL DONE END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END;

4. 觸發器

1. 觸發器是MySQL響應以下任意語句而 自動執行的一條MySQL語句(或位於BEGIN和END語句之間的一組語句):
   
   • DELETE
   • INSERT
   • UPDATE
2. 每個表最多支援6個觸發器(每條INSERT、UPDATE 和DELETE的之前和之後)。
3. 觸發器名必 須在每個表中唯一，但不是在每個資料庫中唯一。
4. DROP TRIGGER
5. CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'
   
   • FOR EACH ROW對每個插入執行
6. 只有表才支援觸發器，檢視不支援(臨時表也不 支援)。
7. INSERT
   
   • 在INSERT觸發器程式碼內，可引用一個名為NEW的虛擬表，訪問被 插入的行;
   • 對於AUTO_INCREMENT列，NEW在INSERT執行之前包含0, 在INSERT 執行之後包含新的自動生成值。
   • CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
8. DELETE
   
   • 在DELETE觸發器程式碼內，你可以引用一個名為OLD的虛擬表，訪 問被刪除的行
   • OLD中的值全都是隻讀的，不能更新
9. UPDATE
   
   • 在UPDATE觸發器程式碼中，你可以引用一個名為OLD的虛擬表訪問 以前(UPDATE語句前)的值，引用一個名為NEW的虛擬表訪問新 更新的值;
   • CREATE TRIGGER updatevendeor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(New.ven_state);
10. 進一步介紹
    
    • MySQL觸發器中不支援CALL語句。這表示不能從觸發 器內呼叫儲存過程。所需的儲存過程程式碼需要複製到觸發器內。
    • 使用觸發器， 把更改(如果需要，甚至還有之前和之後的狀態)記錄到另一個 表非常容易。
    • 應該用觸發器來保證資料的一致性(大小寫、格式等)。
11. 簡單舉例
    sql
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
values(OLD.order_num, OLD.order-date, OLD.cust_id);
END;

5. 事務處理

1. 事務處理(transaction processing)可以用來維護資料庫的完整性，它 保證成批的MySQL操作要麼完全執行，要麼完全不執行。
2. 術語
   
   • 事務(transaction)指一組SQL語句;
   • 回退(rollback)指撤銷指定SQL語句的過程;
   • 提交(commit)指將未儲存的SQL語句結果寫入資料庫表;
   • 保留點(savepoint)指事務處理中設定的臨時佔位符(placeholder)，你可以對它釋出回退(與回退整個事務處理不同)。
3. 不能回退CREATE或DROP操作。事務處理塊中可以使用這兩條語句，但如果你執行回退，它們不會被撤銷。
4. 一般的Mysql語句是隱含提交(implicit commit)，即提交(寫或儲存)操作是自動進行的。
   START TRANSACTION;
DELETE FROM orderitems WHERE order_num=20120;
DELETE FROM orders WHERE order_num=2001;
COMMIT;

5. SAVEPOINT delete1;, ROLLBACK TO delete1;

   • 保留點在事務處理完成(執行一條ROLLBACK或 COMMIT)後自動釋放
   • RELEASE SAVEPOINT可以明確地釋放保留點。

6. autocommit標誌決定是否自動提交更改, SET autocommit=0 --> 指示Mysql不自動提交

   • autocommit標誌是針對每個連線而不是服 務器的。

深入瞭解

1. 字元和校對
   
   • 字符集為字母和符號的集合;
   • 編碼為某個字符集成員的內部表示;
   • 校對為規定字元如何比較的指令。
2. SHOW CHARACTER SET;, SHOW COLLATION;
   
   • SHOW VARIABLES LIKE 'character%';
   • SHOW VARIABLES LIKE 'collation%';
3. 使用Cast()或Convert()函式，可在字符集之間進行轉化
4. SHOW GRANTS FOR yourname;
5. SET PASSWORD=Password('hello');
6. 為了保證所有資料被寫到磁碟(包括索引 資料)，可能需要在進行備份前使用FLUSH TABLES語句。
7. 資料庫維護
   
   • ANALYZE TABLE tblname; 用來檢查表鍵是否正確
   • CHECK TABLE tblname1, tblname2; 可選CHANGED,EXTENED, FAST, MEDIUM, QUICK
   • CHECK TABLE支援一系列的用於MyISAM表的方式
     
     • CHANGED 檢查自最後一次檢查以來改動過的表
     • EXTENDED 執行最徹底的檢查
     • FAST 只檢查未正常關閉的表
     • MEDIUM 檢查所有被刪 除的連結並進行鍵檢驗
     • QUICK 只進行快速掃描
   • REPAIR TABLE tblname3; 這條語句不應該經常使用，如果 需要經常使用，可能會有更大的問題要解決
   • OPTIMIZE TABLE tblname4; 可用來在刪除大量資料後回收所用空間
8. mysqld 的--safe-mode 裝載減去某些最佳配置的伺服器
9. MySQL的主要日誌檔案：錯誤日誌、查詢日誌、二進位制日誌、緩慢查詢日誌
10. SHOW PROCESSLIST可以顯示所有活動的程式(以及它們的執行緒ID和執行時間)。
11. 使用EXPLAIN語句讓MySQL解釋它將如何執行一條SELECT語句
12. 有的操作(包括INSERT)支援一個可選的DELAYED關鍵字，如果使用它，將把控制立即返回給呼叫程式，並且一旦有可能就實際 執行該操
13. LIKE很慢，一般來說，最好是使用FULLTEXT而不是LIKE。