#### 第14章：
#### MySQL
#### 14.1 MySQL 介紹
MySQL 是一個小型關系數據庫管理系統。MySQL8已出，有興趣的可以自己學習。
##### MySQL 的優勢
* 速度：運行速度快。
* 價格：MySQL對多數人來說是免費的。
* 容易使用：與其他大型數據庫的設置和管理相比，其復雜度較低，易于學習。
* 可移植性：能夠工作在眾多不同的系統平臺上，例如windows，linux，unix，mac os等。
* 豐富的接口：提供了用于C/C++、Java、Perl、PHP、Python、Ruby和Tcl等語言的API。
* 支持查詢語言：MySQL可以利用標準SQL語法和支持ODBC的應用程序。
* 安全性和連接性：十分靈活和安全的權限和密碼系統，允許基于主機的驗證。連接到服務器時，所有的密碼傳輸均采用加密形式，從而保證了密碼安全。并且由于MySQL是網絡化的，因此可以在因特網上的任何地方訪問，提高數據共享效率。
##### MySQL 5.7 的性功能
1. 支持JSON
JSON是一種存儲信息的格式，可以很好的替代XML。從MySQL5.7.8版本開始，MySQL將支持JSON，而在此版本之前，只能通過string之類的通用形式來存儲JSON文件，這樣做的缺陷很明顯，就是必須要自行確認和解析數據、忍受更新數據困難和在執行插入時較慢的速度。
2. 性能和可擴展性
改進InnoDB的可擴展性和`臨時表`的性能，從而實現更快的網絡和大數據加載等操作。
3. 改進復制以提高可用性的性能
改進復制包括多源復制、多從線程增強、在線GTIDs和增強的半同步復制。
4. 性能模式提供更好的視角
增加了許多新的監控功能，以減少空間和過載，使用新的SYS模式顯著提高易用性。
5. 安全
以安全為第一宗旨，提供了很多新的功能，從而保證數據庫的安全。
6. 優化
重寫了大部分解析器、優化器和成本模型，這提高了可維護性、可擴展性和性能。
7. GIS
MySQL5.7全新的功能，包括InnoDB空間索引，使用Boots.Geometry，同時提高完整性和標準符合性。
#### 14.2 MySQL數據庫基本操作
##### 顯示所有數據庫
MySQL安裝完成之后，將會在其data目錄下(配置的數據存放目錄)創建幾個必須的數據庫，可以使用`SHOW DATABASES;`語句來查看對當前所有存在的數據庫。
~~~
mysql> SHOW DATABASES;
?
+----------------------+
|Database ? ? ? ? ? ? |
+----------------------+
|information_schema ? |
|musql ? ? ? ? ? ? ? |
|performance_schema ? |
|sakila ? ? ? ? ? ? ? |
|test ? ? ? ? ? ? ? ? |
|world ? ? ? ? ? ? ? |
+---------------------+
6 rows in set ? (0.04 sec)
~~~
查看到有6個數據庫，其中mysql庫是必須的，可以利用test庫做測試工作。
##### 創建數據庫
語法
~~~
CTEATE DATABASE database_name
~~~
例如:
~~~
mysql>CREATE DATABASE test_db;
~~~
~~~
mysql>SHOW databases;
+----------------------+
|Database ? ? ? ? ? ? |
+----------------------+
|information_schema ? |
|musql ? ? ? ? ? ? ? |
|performance_schema ? |
|sakila ? ? ? ? ? ? ? |
|test ? ? ? ? ? ? ? ? |
|test_db ? ? ? ? ? ? | ? //這里有了test_db庫
|world ? ? ? ? ? ? ? |
+---------------------+
7 rows in set ? (0.05 sec)
~~~
##### 刪除數據庫
刪除數據庫是將已經存在的數據庫從磁盤空間上清除，數據庫中的數據也將一同被刪除。
語法:
~~~
DROP DATABASE database_name;
~~~
例如:
~~~
mysql>DROP DATABASE test_db;
~~~
~~~
mysql>SHOW CREATE DATABASE test_db\G
ERROR 1049 (42000):Unkonwn database 'test_db' //表示test_db庫已不存在，刪除成功
ERROR:
No query specified
~~~
#### 14.3 數據庫存儲引擎
數據庫存儲引擎是數據庫底層軟件組件，數據庫管理系統使用數據引擎進行創建、查詢、更新和刪除數據操作。不同的數據庫引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎可以獲得特定的功能。
MySQL5.7支持的存儲引擎有：InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOlE等。(對于普通的線性生產數據的存儲，盡量使用InnoDB)。
##### InnoDB 存儲引擎
InnoDB 是事務性數據庫的首選引擎，支持事務安全表(ACID)，支持行鎖和外鍵。是默認的存儲引擎，主要特性有:
* InnoDB給MySQL提供了具有`提交`、`回滾`和`崩潰恢復能力`的事務安全(ACID兼容)存儲引擎。InnoDB鎖定在行級并且也在SELECT語句中提供一個類似Oracle的非鎖定讀。這些功能增加了多用戶部署和性能。在SQL查詢中，可以自由地將InnoDB類型的表與其他MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。
* InnoDB是為處理巨大數據量的最大性能設計。它的CPU效率可能是任何其他基于磁盤的關系型數據庫引擎不能匹敵的。
* InnoDB存儲引擎完全與MySQL服務器整合，InnoDB存儲引擎在主內存中緩存數據和索引而維持它自己的緩沖池。InnoDB將它的表和索引存在一個邏輯表空間中，表空間可以包含數個文件(或原始磁盤分區)。InnoDB表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。
* InnoDB支持外鍵完整性約束(FOREIGN KEY)
存儲表中的數據時，每張表的存儲都按主鍵順序存放，如果沒有顯性地在表定義時指定主鍵，InnoDB會為每一行生成一個6B的ROWID，并以此作為主鍵。
* InnoDB被用在眾多需要高性能的大型數據庫站點上。
InnoDB不創建目錄，使用InnoDB時，MySQL將在MySQL數據目錄下創建一個名為ibdata1的10MB大小的自動擴展數據文件，以及名為ib\_logfile0和ib\_logfile1的5MB大小的日志文件(二進制事務日志文件)。
##### MyISAM存儲引擎
MyISAM基于ISAM存儲引擎，并對其進行擴展。MyISAM擁有較高的插入、查詢速度，但不支持事務。主要特性有：
* 大文件(達63位文件長度)在支持大文件的文件系統和操作系統上被支持。
* 當把刪除、更新及插入操作混合使用的時候，動態尺寸的行產生更少碎片。這要通過合并相鄰被刪除的塊，以及若下一個塊被刪除，就擴展到下一個塊來自動完成。
* 每個MyISAM表最大索引數是64，可以通過重新編譯來改變。每個最大索引列是16個。
* 最大的鍵長度是1000B，這也可以通過編譯來改變。對于鍵長度超過250B的情況，一個超過1024B的鍵將用上。
* BLOB和TEXT列可以被索引。
* NULL值被允許在索引的列中。這個值占每個鍵的0~1個字節。
* 所有數字鍵值以高字節優先被存儲以允許一個更高的索引壓縮。
* 每表一個AUTO\_INCREMENT列的內部處理。INSERT和UPDATE操作自動更新這一列。這使得AUTO\_INCREMENT列更快(至少10%)。在序列頂的值被刪除之后就不能再利用。
* 可以把數據文件和索引文件放在不同的目錄。
* 每個字符列可以有不同的字符集。
* 有VARCHAR的表可以固定或動態記錄長度。
* VARCHAR和CHAR列可以多達64KB。
MyISAM引擎創建數據庫，將產生3個文件。文件的名字以表的名字開始，擴展名指出文件類型：frm文件存儲表定義，數據文件的擴展名為,MYD(MYData)，索引文件的擴展名是.MYI（MYIndex）。
##### MEMORY存儲引擎
MEMORY存儲引擎表中的數據存儲到內存中，為查詢和引用其他表數據提供快速訪問。主要特性：
* MEMORY表的每個表都可以有多達32個索引，每個索引16列，以及500B最大鍵長度。
* MEMORY存儲引擎執行HASH和BTREE索引。
* 可以在一個MEMORY表中有非唯一鍵。
* MEMORY不支持BLOB或TEXT列。
* MEMORY支持AUTO\_INCREMENT列和對可包含NULL值的列的索引。
* MEMORY表在所有客戶端之間共享（就像其他任何非TEMPORARY表）。
* MEMORY表內容被存在內存中，內存是MEMORY表和服務器在查詢處理時的空閑中創建的內部表共享。
* 當不再需要MEMORY表的內容時，要釋放被MEMORY表使用的內存，應該執行DELETE FROM或TRUNCATE TABLE，或者刪除整個表（使用DROP TABLE）。
| 功能 | MyISAM | Memory | InnoDB | Archive |
| --- | --- | --- | --- | --- |
| 存儲限制 | 256TB | RAM | 64TB | None |
| 支持事務 | No | No | Yes | No |
| 支持全文索引 | Yes | No | No | No |
| 支持數索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | Yes | No | No |
| 支持數據緩存 | No | N/A | Yes | No |
| 支持外鍵 | No | No | Yes | No |
存儲引擎比較
如果要提供提交、回滾和崩潰恢復能力的事務安全（ACID兼容）能力，并要求實現并發控制，InnoDB是個很好的選擇。(建議新手使用InnoDB)。
#### 14.4 數據表的基本操作
數據表屬于數據庫，在操作數據表之前應當選擇數據庫。使用"USE "指定需要操作的數據庫。
##### 創建數據表
語法：
~~~
CREATE TABLE 表名
(
字段名1，數據類型[列級別約束條件] [默認值],
字段名2，數據類型[列級別約束條件] [默認值],
字段名3，數據類型[列級別約束條件] [默認值],
......
[表級別約束條件]
);
~~~
例如創建一個員工表：
~~~
CREATE TABLE tb_empl
(
id ? INT(11), ? ? ? ? ? ? //員工編號
name VARCHAR(25), ? ? ? ? //員工名稱
deptId INT(11), ? ? ? ? ? //所在部門編號
salary FLOAT ? ? ? ? ? ? //工資
);
~~~
使用"SHOW TABLES" 查看所有數據表：
~~~
mysql>SHOW TABLES;
+-----------------------------+
|Tables_in_ test_db ? ? ? ? ? |
+-----------------------------+
|tb_empl ? ? ? ? ? ? ? ? ? ? |
+-----------------------------+
1 row in set (0.00 sec)
~~~
##### 主鍵約束
主鍵是一列或者多列的組合，可以唯一標識表中的一行記錄。要求不能為空，且主鍵列的數據唯一。可以結合外鍵定義多個表之間的關系，并可以加快數據庫查詢的速度。
1. 在定義列的時候可以指定主鍵，語法：
~~~
CREATE TABLE tb_empl
(
id ? INT(11) PRIMARY KEY, ? ? ? ? ? ? // 主鍵
name VARCHAR(25), ? ? ? ?
deptId INT(11), ? ? ? ? ?
salary FLOAT ? ? ? ? ? ?
);
~~~
1. 在定義完所有列之后指定主鍵，語法：
~~~
CREATE TABLE tb_empl
(
id ? INT(11), ? ? ? ? ?
name VARCHAR(25), ? ? ? ?
deptId INT(11), ? ? ? ? ?
salary FLOAT,
PRIMARY KEY(id) ? ? ? ? ? ? ? ? ? ? ? // 指定主鍵
);
~~~
多列(多字段)聯合主鍵
~~~
CREATE TABLE tb_empl
(
id ? INT(11), ? ? ? ? ?
name VARCHAR(25), ? ? ? ?
deptId INT(11), ? ? ? ? ?
salary FLOAT,
PRIMARY KEY(name,deptId) ? ? ? ? ? ? ? ? ? ? ? // 指定組合主鍵
);
~~~
##### 外鍵約束(生產環境視情況使用)
外鍵用來在兩個表的數據之間建立關聯，它可以是一列或多列。一個表可以有一個或多個外鍵。外鍵對應的是參照完整性，一個表的外鍵可以是空值，如果不是空值，則每一個外鍵必須對應另一個表的中主鍵的某個值。
外鍵：首先它是表中的一個字段，它可以不是本表的主鍵，但對應另一個表的主鍵。外鍵主要作用是保證數據引用的完整性，定義外鍵后，不允許刪除在另一個表中具有關聯關系的行。外鍵的作用是保證數據的`一致性`、`完整性`。
~~~
CREATE TABLE tb_emp5
(
id ? INT(11) PRIMARY KEY, ? ? ? ? ?
name VARCHAR(25), ? ? ? ?
deptId INT(11), ? ? ? ? ?
salary FLOAT,
CONSTRAINT fk FOREIGN KEY(deptId) REFERENCES tb_dept1(id) ? ? ? ? ? ?
);
~~~
創建tb\_emp5表，添加名為fk的外鍵約束，外鍵為deptId依賴主表tb\_dept1的主鍵id。
##### 使用非空約束
非空約束指定字段不能為空。對于使用了非空約束的字段，如果用戶在添加數據時沒有指定值，數據庫會報錯。
~~~
CREATE TABLE tb_emp6
(
id ? INT(11) PRIMARY KEY, ? ? ? ? ?
name VARCHAR(25) NOT NULL, ? ? ? // NOT NULL 非空約束 ? ? ?
deptId INT(11), ? ? ? ? ?
salary FLOAT,
);
~~~
##### 唯一約束
唯一約束要求該列唯一，允許為空，但也只能出現一個空值。唯一約束可以保證一列或者幾列不出現重復值。
1. 在定義列后直接指定唯一約束
~~~
CREATE TABLE tb_emp6
(
id ? INT(11) PRIMARY KEY, ? ? ? ? ?
name VARCHAR(25) UNIQUE, ? ? ? // 唯一約束
location VARCHAR(50)
);
~~~
2. 在定義完所有列后指定唯一約束
~~~
CREATE TABLE tb_emp6
(
id ? INT(11) PRIMARY KEY, ? ? ? ? ?
name VARCHAR(25), ? ?
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name) //指定唯一約束
);
~~~
##### 默認約束
默認約束指定某列的默認值。
~~~
CREATE TABLE tb_emp7
(
id ? INT(11) PRIMARY KEY, ? ? ? ? ?
name VARCHAR(25),
deptId INT(11) DEFAULT 1111, ? //指定默認值為1111 ? ?
location VARCHAR(50),
);
~~~
之后向tb\_emp7表插入數據時，新插入記錄如果沒有指定部門編號，則默認都為1111。
##### 設置表的屬性自動增加
在每次插入新記錄的時候，系統自動生成字段自動增加的主鍵值。使用AUTO\_INCREMENT關鍵字來實現。
~~~
CREATE TABLE tb_emp8
(
id ? INT(11) PRIMARY KEY AUTO_INCREMENT, ? ? ? ? ? //設置id為主鍵并插入時自動自增
name VARCHAR(25),
deptId INT(11) DEFAULT 1111, ?
location VARCHAR(50),
);
~~~
##### 查看數據表結構
基本查看語句`DESCRIBE/DESC`用于查看表結構 ：
~~~
mysql>DESCRIBE tb_dept1;
+----------+-----------+-----------+----------+--------+----------+
|Field ? ? |Type ? ? ? |Null ? ? ? |Key ? ? ? |Default |Extra ? ? |
+----------+-----------+-----------+----------+--------+----------+
|id ? ? ? |int(11) ? |No ? ? ? ? |PRI ? ? ? |NULL ? | ? ? ? ? |
|name ? ? |varchar(22)|No ? ? ? ? | ? ? ? ? |NULL ? | ? ? ? ? |
|location |varchar(50)|YES ? ? ? | ? ? ? ? |NULL ? | ? ? ? ? |
+----------+-----------+-----------+----------+--------+----------+
?
~~~
* NULL：表示該列是否可以存儲NULL值。
* Key：表示該列是否有索引。PRI表示該列是表主鍵一部分；UNI表示該列有UNIQUE索引的一部分；MUL表示在列中某個值允許出現多次。
* Default：表示該列是否有默認值，如果有的話值是多少。
* Extra：表示可以獲取的與給定列有關的附加信息，例如AUTO\_INCREMENT等。
詳細查看語句`SHOW CREATE TABLE 表名\G`用來顯示創建表時的CREATE TABLE語句。
##### 修改表名
語法：
~~~
ALTER TABLE <舊表名> RENAME [TO] <新表名>
~~~
~~~
ALTER TABLE tb_dept3 RENAME tb_deptment3;
~~~
將tb\_dept3表名修改為tb\_deptment3
##### 修改字段的數據類型
語法：
~~~
ALTER TABLE <表名> MODIFY <字段名> <數據類型>
~~~
~~~
ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
~~~
將tb\_dept1表的name字段改為VARCHAR數據類型。
##### 修改字段名
語法:
~~~
ALTER TABLE <表名> CHANGE <舊字段名> <新字段名> <數據類型>
~~~
~~~
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
~~~
將tb\_dept1表中location字段改為loc字段并指定數據類型為VARCHAR;
##### 添加字段
語法：
~~~
ALTER TABLE <表名> ADD <字段名> <數據類型> [約束條件] [FIRST|AFTER 已存在字段名]
~~~
FIRST將新添加的字段設置為表的第一個字段，AFTER將新添加的字段指定到已存在的字段之后。
1. 添加無完整性約束條件的字段
~~~
ALTER TBALE tb_dept1 ADD managerId INT(10);
~~~
1. 添加有完整性約束條件的字段
~~~
ALTER TBALE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;
~~~
1. 在表的第一列添加字段
~~~
ALTER TBALE tb_dept1 ADD column2 int (12) FIRST;
~~~
1. 在表的指定列后添加一個字段
~~~
ALTER TBALE tb_dept1 ADD column3 int (11) AFTER name;
~~~
##### 刪除字段
語法：
~~~
ALTER TABLE <表名> DROP <字段名>
~~~
~~~
ALTER TABLE tb_dept1 DROP column2;
~~~
##### 修改字段的排列位置
~~~
ALTER TABLE <表名> MODIFY <字段1> <數據類型> FIRST|AFTER<字段2>
~~~
~~~
ALTER TABLE tbdept1 MODIFY column1 VARCHAR(12) FIRST;
~~~
##### 更改表的存儲引擎
語法:
~~~
ALTER TABLE <表名> ENGINE=<更改后的引擎名>
~~~
~~~
ALTER TABLE tb_deptment3 ENGINE=MyISAM;
~~~
##### 刪除外鍵約束
語法：
~~~
ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>
~~~
~~~
ALTER TABLE tb_emp9 DROP FOREGIN KEY fk;
~~~
刪除名為fk的外鍵。
### 刪除數據表
語法:
~~~
DROP TABLE [IF EXISITS] 表1，表2，...表n；
~~~
~~~
DROP TABLE IF EXISTS tb_dept2;
~~~
#### 14.5 數據類型
MySQL支持多種數據類型，主要有數值類型、日期/時間類型和字符串類型。
##### 整數類型
數值型數據主要用來存儲數字，MySQL提供了多種數值數據類型，不同的數據類型提供不同的取值范圍，可以存儲的值范圍越大，其所需要的存儲空間也會越大。整數類型可以添加自增約束條件AUTO\_INCREMENT。
| 類型名稱 | 說明 | 存儲需求 |
| --- | --- | --- |
| TINYINT | 很小的整數 | 1個字節 |
| SMALLINT | 小的整數 | 2個字節 |
| MEDIUMINT | 中等大小的整數 | 3個字節 |
| INT（INTEGER） | 普通大小的整數 | 4個字節 |
| BIGINT | 大整數 | 8個字節 |
MySQL中的整數類型數據類型
| 數據類型 | 有符號 | 無符號 |
| --- | --- | --- |
| TINYINT | \-128~127 | 0~255 |
| SMALLINT | \-32768~32767 | 0~65535 |
| MEDIUMINT | \-8388608~8388607 | 0~16777215 |
| INT（INTEGER） | \-2147483648~2147483647 | 0~4294967295 |
| BIGINT | \-9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
不同整數類型的取值范圍
##### 浮點類型和定點數類型
MySQL使用浮點數和定點數來表示小數。
| 類型名稱 | 說明 | 存儲需求 |
| --- | --- | --- |
| FLOAT | 單精度浮點數 | 4個字節 |
| DOUBLE | 雙精度浮點 | 8個字節 |
| DECIMAL(M,D) ，DEC | 壓縮的"嚴格"定點數 | M+2個字節 |
MySQL中的小數類型
DECIMAL類型不同于FLOAT和DOUBLE，DECIMAL實際是以串存放的。DECIMAL可能的最大值范圍與DOUBLE一樣，但其有效取值范圍由M和D的值決定。
FLOAT類型的取值范圍:
有符號：-3.4020823466E+38 ~ -1.175494351E-38
無符號：0和1.175494351E-38 ~ 3.402823466E+38
DOUBLE類型的取值范圍：
有符號：-1.7976931348623157E+308 ~ -2.2250738585072014E-308
無符號：0和2.225738585072014E-308 ~ 1.7976931348623157E+308
##### 日期和時間類型
| 類型名稱 | 日期格式 | 日期范圍 | 存儲需求 |
| --- | --- | --- | --- |
| YEAR | YYYY | 1901~2155 | 1個字節 |
| TIME | HH:MM:SS | \-838:59:59 ~ 838:59:59 | 3個字節 |
| DATE | YYYY:MM:DD | 1000-01-01 ~ 9999-12-31 | 3個字節 |
| DATETIME | YYYY:MM:DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8個字節 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | 4個字節 |
日期和時間類型可以進行時間轉化，如YEAR插入時候是01會識別為1901。
##### 文本字符串類型
字符串類型用來存儲字符串數據，除了可以存儲字符串之外，還可以存儲其他數據，如圖片和聲音的二進制數據。
| 類型名稱 | 說明 | 存儲需求 |
| --- | --- | --- |
| CHAR(M) | 固定長度非二進制字符串 | M字節，1<=M<=255 |
| VARCHAR(M) | 變長非二進制字符串 | L+1字節,在此L<=M 和1<=M<=65535 |
| TINYTEXT | 非常小的非二進制字符串 | L+1字節，在此L<2^8 |
| TEXT | 小的非二進制字符串 | L+2字節，在此L<2^16 |
| MEDIUMTEXT | 中等大小的非二進制字符串 | L+3字節，在此L<2^24 |
| LONGTEXT | 大的非二進制字符串 | L+4字節，在此L<2^32 |
| ENUM | 枚舉類型，只能有一個枚舉字符串值 | 1或2個字節，取決于枚舉值的數目(最大值65535) |
| SET | 一個設置，字符串對象可以有零個或多個SET成員 | 1，2，3，4或8個字節，取決于集合成員的數量(最多64個成員) |
CHAR類型和VARCHAR類型
CHAR(M)為固定長字符串，在定義時指定字符串列長，M的范圍是0-255。
VARCHAR(M)是長度可變的字符串,M表示最大列長度，范圍是0-65535。`實際占用空間是字符串實際長度+1`。
| 插入值 | CHAR(4) | 存儲需求 | VARCHAR(4) | 存儲需求 |
| --- | --- | --- | --- | --- |
| "" | " " | 4個字節 | "" | 1個字節 |
| "ab" | "ab " | 4個字節 | "ab" | 3個字節 |
| "abc" | "abc" | 4個字節 | "abc" | 4個字節 |
| "abcd" | "abcd" | 4個字節 | "abcd" | 5個字節 |
| "abcdef" | "abcd" | 4個字節 | "abcd" | 5個字節 |
CHAR(4)和VARCHAR(4)的區別
ENUM類型與SET類型
ENUM類型與SET類型的主要區別是，ENUM類型只能在其值中選擇一個，而SET類型可以在其值中選擇多個。
ENUM類型語法格式
~~~
字段名 ENUM("值1","值2","值3",..."值n")
~~~
SET類型語法格式
~~~
SET("值1","值2","值3",..."值n")
~~~
##### 二進制字符串類型
| 類型名稱 | 說明 | 存儲需求 |
| --- | --- | --- |
| BIT(M) | 位字段類型 | d大約(M+7)/8個字節 |
| BITARY(M) | 固定長度二進制字符串 | M個字節 |
| VARBINARY(M) | 可變長度二進制字符串 | M+1個字節 |
| TINYBLOB(M) | 非常小的BLOB | L+1 字節，在此L<2^8 |
| MEDIUMBLOB(M) | 小BLOB | L+2 字節，在此L<2^16 |
| MEDIUMBLOB(M) | 中等大小的BLOB | L+3 字節，在此L<2^24 |
| LONGBLOB(M) | 非常大的BLOB | L+4 字節，在此L<2^32 |
MySQL中的二進制字符串類型
#### 14.6 運算符
運算符經常用存在SELECT的查詢條件上。
##### 算數運算符
| 運算符 | 作用 |
| --- | --- |
| + | 加法 |
| \- | 減法 |
| \* | 乘法 |
| / | 除法 |
| % | 求余 |
~~~
mysql> SELECT num, num+10,num-3+5;
+-------+---------+--------+
|num |num+10 |nun-3+5 |
+-------+---------+--------+
|64 |74 |66 |
+-------+---------+--------+
1 row in set (0.00 sec)
~~~
##### 比較運算符
比較運算符的結果總是1、0或者NULL。比較結果正確返回1，錯誤返回0。
| 運算符 | 作用 |
| --- | --- |
| \= | 等于 |
| | 安全等于 |
| <>(!=) | 不等于 |
| <= | 小于等于 |
| \>= | 大于等于 |
| \> | 大于 |
| IS NULL | 是否為NULL |
| IS NOT NULL | 是否不為NULL |
| LEAST | 取最小值 |
| GREATEST | 取最大值 |
| BETWEEN AND | 是否在兩值之間 |
| ISNULL | 與IS NULL 作用相同 |
| IN | 是否是IN列中的一個值 |
| NOT IN | 是否不是IN列中的一個值 |
| LIKE | 通配符匹配 |
| REGEXP | 正則表達式匹配 |
~~~
mysql> SELECT 'good'<='god',1<=2;
+-------------+-----------+
|'good'<='god'|1<=2 |
+-------------+-----------+
|0 |1 |
+-------------+-----------+
1 row in set (0.00 sec)
~~~
##### 邏輯運算符
| 運算符 | 作用 |
| --- | --- |
| NOT 或 ！ | 邏輯非 |
| AND 或 && | 邏輯與 |
| OR 或 || | 邏輯或 |
| XOR | 邏輯異或(找不同) |
~~~
mysql> SELECT 1 AND -1,1 AND 0;
+---------+----------+
|1 AND -1 |1 AND 0 |
+---------+----------+
|1 |0 |
+---------+----------+
1 row in set (0.00 sec )
~~~
運算符AND或&&操作所有操作數均為非零值、并且不為NULL時，計算結果為1；當一個或者多個操作數為0時，所得結果為0，其余情況返回值為NULL。
##### 位運算符
| 運算符 | 作用 |
| --- | --- |
| | | 位或 |
| & | 位與 |
| ^ | 位異或 |
| << | 位左移 |
| \>> | 位右移 |
| ~ | 位取反，反轉所有比特 |
~~~
mysql> SELECT 10 | 15, 9 | 4 | 2;
+------------+-------------+
|10 | 15 | 9 | 4 | 2 |
+------------+-------------+
|15 | 15 |
+------------+-------------+
1 row in set (0.00 sec)
~~~
10的二進制為1010，15的二進制為1111，位或計算后為1111，就是15。
9的二進制為1001，4的二進制為0100，2的二進制為0010，位或計算后為1111，就是15。
#### 14.7 MySQL函數
MySQL提供了眾多功能強大、方便易用的函數。使用這些函數，可以極大地提高用戶對數據庫的管理效率。
生產環境里由于效率問題不推薦使用函數。
數學函數，例如：
ABS(X)返回絕對值
~~~
mysql>SELECT ABS(2),ABS(-3.3),ABS(-33);
+----------+---------+----------+
|ABS(2) |ABS(-3.3)|ABS(-33) |
+----------+---------+----------+
|2 |3.3 |33 |
+----------+---------+----------+
1row in set (0.01 sec)
~~~
#### 14.8 表數據的操作
MySQL數據表最重要的操作就是對數據的增、刪、改、查。
##### 查詢數據
MySQL從數據庫中查詢數據的基本語句為SELECT語句。語法:
~~~
SELECT [*|字段列表(逗號分隔)] FROM <表1>，<表2>...
WHERE [<表達式>
<GROUP BY>
<HAVING>
<ORDER BY>
<LIMIT>
]
~~~
含義如下：
* \[\*|字段列表(逗號分隔)\] 包含星號通配符選擇字段列表，表示查詢的字段，其中字段列至少包含一個字段名稱，如果要查詢多個字段，多個字段之間用逗號隔開，最后一個字段不要加逗號。
* FROM ，...表示查詢數據的來源，可以是一個或者多個表。
* WHERE子句是可選項，如果選擇該選項，將限定查詢行必須滿足的條件。
* 該句子告訴MySQL如何顯示查詢出來的數據，并按照指定的字段分組。
* 該句子告訴MySQL按照什么樣的順序顯示查詢出來的數據，可以進行的排序有：升序(ASC)，降序(DESC)。
* 該句子告訴MySQL每次查詢出來的數據條數。
##### 查詢所有字段數據：
~~~
SELECT * FROM 表名;
~~~
使用 \* 通配符將返回所有列
~~~
mysql> SELECT * FROM fruits;
+-------+--------+----------+
|f_id |s_id | f_name |
+-------+--------+----------+
|a1 |101 |apple |
|a2 |103 |apricot |
|b1 |101 |blackberry|
+-------+--------+----------+
~~~
##### 查詢指定字段
~~~
mysql> SELECT f_id FROM fruits;
+-------+
|f_id |
+-------+
|a1 |
|a2 |
|b1 |
+-------+
~~~
##### 查詢多個字段
~~~
mysql> SELECT f_id,s_id FROM fruits;
+-------+--------+
|f_id |s_id |
+-------+--------+
|a1 |101 |
|a2 |103 |
|b1 |101 |
+-------+--------+
~~~
##### 查詢指定記錄
數據庫中包含大量的數據，根據特殊要求，可能只需要查詢表中的指定數據，即對數據進行過濾。語法:
~~~
SELECT 字段名1，字段名2，...字段名n FROM 表名 WHERE 查詢條件
~~~
| 操作符 | 說明 |
| --- | --- |
| \= | 相等 |
| <>，!= | 不相等 |
| < | 小于 |
| <= | 小于或者等于 |
| \> | 大于 |
| \>= | 大于或者等于 |
| BETWEEN | 位于兩者之間 |
例子：
~~~
SELECT f_name,s_id FROM fruits WHERE s_id = 101 AND f_id = a1;
~~~
##### 帶IN關鍵字查詢
IN操作符用來查詢滿足指定范圍內的條件的記錄。例子：
~~~
mysql>SELECT s_id,f_id FROM fruits WHERE s_id IN (101,102) ORDER BY s_id;
~~~
##### 帶BETWEEN AND的范圍查找
BETWEEN AND 用來查詢某個范圍內的值。需要操作兩個參數，即開始值和結束值。例子：
~~~
SELECT s_id,f_id FROM fruits WHERE s_id BETWEEN 101 AND 103;
~~~
##### 帶LIKE的字符匹配查詢
1. 百分號通配符'%'匹配任意長度的字符，甚至包括零字符。
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id like "10%";
+-------+--------+
|f_id |s_id |
+-------+--------+
|a1 |101 |
|a2 |103 |
|b1 |101 |
+-------+--------+
~~~
匹配右邊。
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id like "%3%";
+-------+--------+
|f_id |s_id |
+-------+--------+
|a2 |103 |
+-------+--------+
~~~
左右都匹配。
1. 下劃線通配符'\_'，一次只能匹配一個字符。
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id like "__3";
~~~
查詢sid以3結尾一共有三個字符的數據。
##### 查詢空值
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id IS NULL;
~~~
查詢s\_id為NULL的數據。
##### AND 多條件查詢
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id = 103 AND f_id != a2;
~~~
查詢s\_id為103 ，f\_id 不為a2的數據。
##### OR 多條件查詢
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id = 103 AND s_id = 101;
~~~
查詢s\_id為103或者s\_id為101的數據。
##### 查詢去除重復值
使用`DISTINCT`關鍵字只是MySQL消除重復的記錄值。
~~~
mysql> SELECT DISTINCT 字段名 FROM 表名;
~~~
##### 對查詢結果排序
SELECT語句中通過`ORDER BY` 子句對查詢結果排序。默認升序。
~~~
mysql> SELECT s_id FORM fruits ORDER BY s_id;
~~~
##### 多列排序
有時候需要先按s\_id排序，再按f\_id排序，就需要多列排序。
~~~
mysql> SELECT s_id,f_id FORM fruits ORDER BY s_id,f_id;
~~~
##### 指定升降序
ORDER BY 子句默認為升序。升序使用ASC關鍵字，降序使用DESC關鍵字。
~~~
mysql> SELECT s_id,f_id FORM fruits ORDER BY s_id DESC; //指定降序
~~~
##### 分組查詢
分組查詢是對查詢數據按照某個或多個字段進行分組。使用 `GROUP BY` 關鍵字。
GROUP BY 通常和集合函數一起使用：例如MAX()、MIN()、COUNT()、SUM()、AVG()。
~~~
mysql> SELECT s_id,COUNT(*) as Tatal FROM fruits GROUP BY s_id;
+----------+-----------+
| s_id |Tatal |
+----------+-----------+
|101 |3 |
|102 |2 |
|103 |3 |
|104 |3 |
+----------+-----------+
~~~
##### HAVING 過濾分組
GOURP BY 可以和HAVING一起限定顯示記錄所需滿足的條件，只有滿足記錄才會被顯示。
~~~
mysql> SELECT s_id,COUNT(*) as Tatal FROM fruits GROUP BY s_id HAVING COUNT(*) > 2;
+----------+-----------+
| s_id |Tatal |
+----------+-----------+
|101 |3 |
|103 |3 |
|104 |3 |
+----------+-----------+
~~~
##### 在GROUP BY 子句中使用 WITH ROLLUP
使用`WITH ROLLUP`關鍵字之后，在所有查詢出來的分組記錄之后增加一條記錄，該記錄計算查詢出的所有記錄的總和，即統計記錄數量。
~~~
mysql> SELECT s_id,COUNT(*) as Tatal FROM fruits GROUP BY s_id WITH ROLLUP;
+----------+-----------+
| s_id |Tatal |
+----------+-----------+
|101 |3 |
|102 |2 |
|103 |3 |
|104 |3 |
|NULL |11 |
+----------+-----------+
~~~
##### 多字段分組
使用`GROUP BY` 可以對多個字段進行分組，分組層次從左到右，即先按第1個字段分組，然后在第1個字段值相同的記錄中，再根據第2個字段的值進行分組，以此類推。
~~~
mysql> SELECT * FROM fruits group by s_id ,f_name;
+--------+--------+-----------+--------+
|f_id |s_id |f_name |f_price |
+--------+--------+-----------+--------+
|a1 |101 |apple |5.20 |
|b1 |101 |blackberry |10.20 |
|c0 |101 |banana |3.20 |
|t1 |102 |grape |5.30 |
|t2 |102 |orange |11.20 |
............
~~~
##### GROUP BY和ORDER BY 一起使用
~~~
mysql> SELECT * FROM fruits group by s_id ,f_name ORDER BY f_price;
~~~
##### LIMIT 限制查詢結果的數量
SELECT 返回所有匹配的行，可能是表中所有行。當只需要其中一行或前幾行時，使用`LIMIT`關鍵字。
語法：
~~~
LIMIT [位置偏移量], 行數
~~~
~~~
mysql> SELECT * FROM fuits LIMIT 4, 3;
~~~
查詢第5行開始的3條記錄。
##### 連接查詢
連接是關系型數據庫模型的主要特點。連接查詢是關系數據庫中最重要的查詢，主要包括內連接、外連接等。通過連接運算符可以實現多個表查詢。
##### 內連接
使用`INNER JOIN`關鍵字，查詢一個或者多個表中的數據，只有滿足條件的記錄才能出現再結果關系中。
~~~
mysql> SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
~~~
查詢suppliers的s\_id,fruits的s\_id、f\_name、f\_price，條件是suppliers的s\_id=fruits的s\_id。
這時候的`ON`等同于其他句子里`WHERE`的作用。
兩張表都是一張表需要連接的情況屬于特殊的內連接:
~~~
mysql>SELECT f1.f_id,f1.f_name FROM fruits as f1,fruits as f2 where f1.s_id = f2.s_id AND f2.f_id = 'a1';
~~~
`as`是為表名取別名。
##### 外連接查詢
外連接將查詢多個表中相關聯的行，返回查詢集合不僅包含符合連接條件的行，而且還包括左表(左外連接或左連接)、右表(右外連接或右連接)或兩個連接表(全外連接)中所有數據行。
* LEFT JOIN（左連接）：返回包括左表中的所有記錄和右表中連接字段相等的記錄。
* RIGHT JOIN（右連接）：返回包括右表的所有記錄和左邊中連接字段相等的記錄。
左連接例子：
創建orders表
~~~
CREATE TABLE orders
(
o_num int NOT NULL AUTO_INCREMENT,
o_date datetime NOT NULL,
c_id int NOT NULL,
PRIMARY KEY (o_num)
)
~~~
插入記錄
~~~
INSERT INTO orders(o_num,o_date,c_id) VALUES
(30001,"2008-07-01",10001),
(30002,"2008-09-01",10003),
(30003,"2008-09-21",10004),
(30004,"2008-10-03",10005),
(30005,"2008-09-11",10001),
~~~
左連接
~~~
mysql>SELECT customers.c_id,orders.o_num FROM customers LEFT JOIN orders ON customers.c_id = orders.c_id;
+---------+------------+
|c_id |o_num |
+---------+------------+
|10001 |30001 |
|10001 |30005 |
|10002 |NULL |
|10003 |30002 |
|10004 |30003 |
+---------+------------+
~~~
顯示了5條記錄，在customers.c\_id = orders.c\_id的條件下，customers一共有五條記錄全部顯示出來了，其中10002這條記錄，將左表customers的記錄展示了出來，右表orders沒有匹配上。所以這條記錄查詢的右表orders的o\_num用空值NULL展示。
右連接與左連接相反，有表顯示全部行，左表沒有匹配上的控制NULL替代。
##### 子查詢
子查詢指一個查詢語句嵌套在另一個查詢語句內部的查詢。
##### 帶ANY、SOME關鍵字的子查詢
帶有`任何一個`的意義。
~~~
mysql> SELECT * FROM tb11 WHERE num1 > ANY(SELECT num2 FROM tb12);
~~~
查詢出tb11表中num1大于任何一個tb12表中num2的記錄。
`SOME`關鍵字和`ANY`關鍵字是同義詞。
##### 帶ALL關鍵字的子查詢
帶有`每一個/所有`的意義
~~~
mysql> SELECT * FROM tb11 WHERE num1 > ALL(SELECT num2 FROM tb12);
~~~
查詢出tb11表中num1大于每一個tb12表中num2的所有記錄。
##### 帶EXITST關鍵字的子查詢
用于判斷子查詢是否至少返回一行，如果至少返回一行，EXITST結果為true。則外層查詢語句將進行查詢。
~~~
mysql> SELECT * FROM tb11 WHERE EXITST (SELECT num2 FROM tb12);
~~~
如果SELECT num2 FROM tb12有結果，則外層SELECT \* FROM tb11 語句將進行查詢。
`NO EXITST` 與`EXITST`使用方法相同，結果相反。
##### 帶有IN關鍵字的子查詢
IN關鍵字進行查詢時，內查詢僅僅返回一個數據列，為外層查詢提供比較操作。
~~~
mysql> SELECT c_id FROM orders WHERE o_num IN (SELECT O_num FROM orderitems WHERE f_id = 'c0');
~~~
查找orders的o\_num字段，要求等于orderitems中f\_id = 'c0' 記錄的o\_num字段。
##### 合并查詢結果
UNION關鍵字用于合并數據類型和列數相同的查詢。默認刪除重復記錄，帶ALL時不刪除重復記錄也不排序。
語法:
~~~
SELECT column ,.... FROM table1
UNION [ALL]
SELECT column ,.... FORM table2
~~~
##### 為表和字段取別名
表別名語法：
~~~
表名 [as] 別名
~~~
字段別名語法：
~~~
列名 [as] 別名
~~~
##### 正則表達式匹配
正則表達式匹配結果使用REGEXP關鍵字，其無法使用索引提高效率。有興趣的同學可以自己練習使用REGEXP匹配查詢結果。
##### 插入數據
使用INSERT語句插入數據,要求指定表名稱和插入到新記錄中的值(有默認值的可以不用)。
語法：
~~~
INSERT INTO table_name (column_list) VALUES (values_list)
~~~
table\_name為表名，column\_list為需要插入的列(逗號分隔)，values\_list為需要插入的數據(逗號分隔)。
column\_list與values\_list需要數量相等并一一對應。
~~~
mysql> INSERT INTO person (id,name) VALUES (1,"張三");
Query OK, 1 row affected (0.00 sec)
~~~
一次插入多條。
~~~
mysql> INSERT INTO person (id,name) VALUES (1,"張三"),(2,"李四"),(3,"趙財神")....;
Query OK, 1 row affected (0.05 sec)
~~~
將查詢的結果插入到表中。
~~~
mysql>INSERT INTO table_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE(condition)
~~~
##### 更新數據
對表中原有數據進行更新操作使用`UPDATE`關鍵字。
語法：
~~~
UPDATE table_name SET field1=new-value1 [ WHERE <condition> ]
~~~
例子：
~~~
mysql> UPDATE person set age = 15 , name = 'LiXiao' WHERE id = 11;
~~~
##### 刪除數據
從數據表中刪除數據使用DELETE語句，允許WHERE子句指定刪除條件。
語法：
~~~
DELETE FROM table_name [ WHERE <condition> ]
~~~
例子：
~~~
mysql>DELETE FROM person WHERE id = 111;
~~~
#### 14.9 索引
索引用于快速查找出在某個列中有特定值的行。不使用索引，MySQL必須從第1條記錄開始讀完整個表，直接找出相關的行。表越大，查詢數據花費時間越多。如果要查詢的列有一個索引，MySQL能快速到達某個位置去搜尋數據文件，而不必查看所有數據。索引是對數據庫表中一列或多列的值進行排序的一種結構，使用索引可提高數據庫中特定數據的查詢速度。
##### 索引的含義和特點
索引是一個單獨的、存儲在磁盤上的數據庫結構，它們包含著對數據表里所有記錄的引用記錄。使用索引用于快速找出某個或多個列中有一特定值的行，對相關列使用索引是提高查詢操作速度的最佳途徑。
索引的有點：
1. 通過創建唯一索引，可以保證數據表中每一行數據的唯一性。
2. 可以大大加快數據的查詢速度，這也是創建索引的最主要的原因。
3. 在實現數據的參考完整性方面，可以加快表與表之間的連接。
4. 在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間。
索引的不利：
1. 創建索引和維護索引要耗費時間，并且隨著數據量的增加所耗費的時間也會增加。
2. 索引需要占磁盤空間，除了數據表占用數據空間之外，每一個索引還要占一定的物理空間，如果有大量索引，索引文件可能逼數據文件尺寸更快達到最大文件尺寸。
3. 當對表中的數據進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了數據的維護速度。
##### 索引的分類
##### 1.普通索引和唯一索引
普通索引是MySQL中的基本索引型，允許在定義索引的列中插入重復值和空值。
唯一索引，索引的值必須唯一，但允許有空值。如果是組合索引，則組合索引的多列值必須唯一。主鍵索引是一種特殊的唯一索引，不允許有空值。
##### 2.單列索引和組合索引
單列索引就是一個索引只包含單個列，一個表可以有多個單列索引。
組合索引指在表的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段時（最左前綴原則），索引才會被使用。
##### 3\. 全文索引
全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和控制。可以在TEXT、CHAR、VARCHAR類型上建立全文索引，只有MyISAM引擎可以使用。
##### 4\. 空間索引
空間索引是對空間數據類型的字段建立的索引，有4種：GEOMETRY、POINT、LINESTRING、POLYGON。有興趣的同學可以自己學習。
##### 索引的設計原則
索引設計不合理或者缺少索引會對數據庫和應用程序的性能造成障礙。高效的索引對于獲得良好的性能非常重要。
1. 索引并非越多越好，一個表中如有大量索引，不僅占用磁盤空間，而且會影響INSERT、DELETE、UPDATE等語句的性能，因為當表中的數據更改的同時，索引也會進行調整和更新。
2. 避免對經常更新的表進行過多的索引，并且索引的列盡可能少。而對經常用于查詢的字段應該創建索引，但要避免添加不必要的字段。
3. 數據量小的表最好不要使用索引，由于數據較少，查詢花費的時間可能比遍歷索引的時間還短，索引可能不會產生優化效果。
4. 在條件表達式中經常用到的不同值較多的列上建立索引，在不同值很少的列上不要建立索引。比如性別只有”男，女“兩個不同值，就無須建立索引。這會嚴重降低效率。
5. 當唯一性是某種數據本身的特征，指定唯一索引。
6. 在頻繁進行排序或分組的列上建立索引，如果排序待排序的列有多個，可以在這些列上建立組合索引。
##### 創建普通索引
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication) //建立普通索引
)
~~~
`EXPLAIN`語句查看索引是否正在使用。
~~~
mysql> explain select * from book where year_publication = 1990 \g;
*** 1. row ***
id: 1
select_type:SIMPLE
table:book
type:ref
possible_keys:year_publication
key:year_publication
key_len:1
ref:const
rows:1
Extra:
1 row in set (0.05 sec)
~~~
EXPLAIN語句各行解釋
* select\_type：指定所使用的SELECT查詢類型，有SIMPLE、PRIMARY、UNION、SUBQUERY等。
* table：指定數據庫讀取的數據表名稱。
* type：指定了本數據表與其他數據表之間的關聯關系，有system、const、eq\_ref、ref、range、index和ALL。
* possible\_keys：給出MySQL在搜索數據記錄時可選的各個索引。
* key：MySQL實際選擇的索引。
* key\_len：給出索引按字節計算的長度，key\_len越小，越快。
* ref：給出了關聯關系中另一個數據表里的數據列的名字。
* extra：提供了與操作相關的信息。
##### 創建唯一索引
使用`UNIQUE`語句
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
UNIQUE INDEX UniIdx(bookid) //建立唯一索引
)
~~~
##### 建立單列索引
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX SingleIdx(bookname) //建立單列索引
)
~~~
##### 建立組合索引
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX MultiIdx(bookid,bookname) //建立組合索引，由兩個字段組成
)
~~~
最左前綴原則：WHERE子句查詢時候從左到右查詢才能命中索引。例如索引MultiIdx使用最左前綴的命中的情況：
~~~
mysql>SELECT * FROM book WHERE bookid = 1 AND bookname = '語文教材';
mysql>SELECT * FROM book WHERE bookid = 1;
~~~
索引MultiIdx未使用最左前綴的命中的情況：
~~~
mysql>SELECT * FROM book WHERE bookname = '語文教材' AND bookid = 1;
mysql>SELECT * FROM book WHERE bookname = '語文教材';
~~~
##### 全文索引
全文索引需要MyISAM引擎
~~~
mysql> CREATE TABLE t4
(
id INT NOT NULL,
name VARCHAR(255) NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;
~~~
##### 空間索引
有興趣的同學可以自行查閱資料學習。
##### 在已經存在的表上創建索引
1. 使用ALTER TABLE 語句創建
~~~
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (column[length]) [ASC|DESC]
~~~
~~~
mysql>ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
~~~
1. 使用CREATE TABLE 語句創建
~~~
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column[length]) [ASC|DESC]
~~~
~~~
mysql>CREATE INDEX bkNameIdx ON book(bookname);
~~~
##### 刪除索引
1. 使用ALTER TABLE 語句刪除
~~~
ALTER TABLE table_name DROP INDEX inex_name;
~~~
~~~
mysql>ALTER TABLE book DROP INDEX UniqidIdx;
~~~
1. 使用DROP INDEX 語句刪除
~~~
DROP INDEX index_name ON table_name;
~~~
~~~
mysql> DROP INDEX bkAuAndInfoidx On book;
~~~
##### 查看指定表中創建的索引
使用`SHOW INDEX` 語句
~~~
mysql> SHOW INDEX FROM book \G
*** 1. Row ***
Table: book
Non_unique:1
Key_name:year_publication
Seq_in_index:1
Column_name:year_publication
cardinality:0
Sub_part:NULL
Packed:NULL
NULL:
Index_type:BTREE
Comment:
Index_comment:
~~~
參數含義：
* Table：表示創建索引的表。
* Non\_unique：1待表是非唯一索引，0代表是唯一索引。
* Key\_name：表示索引的名稱。
* Seq\_in\_index：表示該字段在索引中的位置，單列索引該值為1，組合索引為每個字段在索引定義中的順序號。
* Column\_name：表示定義索引的列字段。
* Sub\_part：表示索引的長度。
* Null：表示該字段是否能為空值。
* Index\_type：索引類型。
#### 14.10 存儲過程及觸發器
##### 存儲過程
存儲過程就是一條或者多條SQL語句的集合。有興趣的同學可以自己學習。
~~~
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits；
...
END;
~~~
##### 觸發器
MySQL觸發器和存儲過程一樣，是嵌到MySQL的一段程序。有興趣的同學可以自行學習。
觸發器是個特殊的存儲過程，不同的是，執行存儲過程需要使用CALL語句調用，而觸發器不需要，只要當一個預定義的事件發生，就會被MySQL自動調用。用于滿足復雜的業務規則或要求。比如：可以根據客戶當前的賬戶狀態，控制是否允許插入新訂單。
#### 14.11 用戶管理
MySQL是一個多用戶數據庫，具有強大的訪問控制系統，可以為不同的用戶指定允許的權限。
##### 權限表
MySQL服務器通過權限表來控制用戶對數據庫的訪問，權限表存放在MySQL數據庫中。存儲賬戶權限信息表主要有：user、db、host、tables\_priv、columns\_priv和procs\_priv。
user表是全局級的，其中字段主要分類:
1. 用戶列：用戶記錄和校對。
2. 權限列：決定用戶權限。
3. 安全列：用戶權限的更多配置。
4. 資源控制列：限制用戶使用的資源。
db表和host表，存儲了用戶對某個數據庫的操作權限，字段分類：
1. 用戶列：表示某個主機用戶對某個庫的操作權限。
2. 權限列：決定用戶權限。
#### 14.12 MySQL日志
MySQL日志記錄了MySQL數據庫日常操作和錯誤信息。日志分類：
1. 錯誤日志：記錄MySQL服務的啟動、運行、停止MySQL服務時出現的問題。
2. 查詢日志：記錄建立的客戶端連接和執行的語句。
3. 二進制日志：記錄所有更改數據的語句，可以用于數據復制。
4. 慢查詢日志：記錄所有執行時間超過long\_query\_time的所有查詢或不使用索引的查詢。
##### 二進制日志配置
在my.ini中
~~~
[mysqld]
log-bin [=path/[filename]] //配置二進制日志地址、名稱
expire_logs_days = 10 //配置過期自動刪除文件時間，單位日
max_binlog_size = 100M //配置單個二進制日志大小，超過會發生滾動新建另一個二進制日志
~~~
##### 錯誤日志
在my.ini中
~~~
[mysqld]
log-error=[path/filename]
//如果沒有指定錯誤日志路徑，會默認在數據庫目錄里，文件名叫hostname.err
~~~
##### 通用查詢日志
在my.ini中
~~~
[mysqld]
log=[path/filename]
//如果沒有指定錯誤日志路徑，會默認在數據庫目錄里，文件名叫hostname.log
~~~
##### 慢查詢日志
在my.ini或者my.cnf中
~~~
[mysqld]
log-slow-queries=[path/filename]
//如果沒有指定錯誤日志路徑，會默認在數據庫目錄里，文件名叫hostname-slow.log
long_query_time=n //指定慢日志記錄時間
~~~
慢日志經常用來優化數據查詢性能。
#### 14.13 MySQL體系結構與存儲引擎
MySQL體系結構可以分為兩層，`MySQL Server層`和`存儲引擎層`。MySQL Server層包括`連接層`和`SQL`層。
應用程序通過接口來連接MySQL。最先連接處理的是**連接層**，連接層包括`通信協議`、`線程處理`、`用戶名密碼認證`三個部分。通信協議負責檢測客戶端版本是否兼容MySQL服務端。線程處理是指每一個連接請求都會分配一個對應的線程，相當于一條SQL對應一個線程，一個線程對應一個邏輯CPU，并會在多個邏輯CPU之間切換(多個控制流之間切換)。用戶名密碼認證驗證賬號和密碼。
**SQL層**包括權限判斷、查詢緩存、解析器、預處理、查詢優化器、緩存、執行計劃。
權限判斷可以驗證用戶對某個庫、表、行的權限。緩存查詢通過`query cache`進行操作，如果在query cache中，則直接返回結果給客戶端(生產環境中建議關閉)。查詢解析器針對SQL語句進行解析，判斷語法是否正確。預處理器對解析器無法解析的語義進行處理。優化器對SQL進行改寫和相應的優化，并生成最優的執行計劃。然后調用程序的API接口通過引擎層訪問數據。
**存儲引擎層**是MySQL數據庫區別于其他數據庫最核心的一點。
##### Query Cache 詳解
生產中建議關閉Query Cache，因為它只能緩存靜態數據，一旦數據發生變化，經常讀寫，Query Cache就沒有必要。
關閉的方法：
將query\_cache\_type設置成off。
##### InnoDB體系結構
InnoDB體系結構實際上是由內存結構、線程、磁盤文件這三層組成。
這里的線程就是一條數據從內存到磁盤的過程。
:-: 
InnoDB體系結構
##### InnoDB 存儲結構
InnoDB邏輯存儲單元主要分為表空間、段、區和頁。
層級關系為table-->segment-->extent(64個page，1MB)->page。
:-: 
InnoDB存儲結構
1. 表空間
InnoDB存儲引擎表中所有數據都是存儲在表空間中。表空間中的`系統表空間`以ibdata1來命名，在安裝數據庫初始化數據時系統會創建一個ibdata1的表空間文件，它會存儲所有數據的信息以及回滾段(undo)的信息。MySQL5.6之后，undo表空間可以通過參數單獨設置存儲位置了，可從ibdata1中獨立出來。Innodb\_data\_file\_path負責定義系統表空間的路徑、初始化、自動擴展策略。數據庫默認的自動擴展大小是64MB。
數據庫默認的ibdata1的大小是10MB，這里建議不要使用10MB的默認大小，在遇到高并發事務時，會受到不小的影響。建議把ibdata1的初始值調整為1GB。
除了系統表空間，還有`獨立表空間`，設置參數innodb\_file\_per\_table=1即可。目前MySQL默認使用的都是獨立表空間文件，就是每個表就有自己的表空間文件，而不用存儲在ibdata1中。獨立表空間存儲對應表的B+樹數據、索引和插入緩沖等信息，其余信息還是存儲在默認表空間。
獨立表空間的每個表都有自己的表空間，并且可以實現表空間的轉移，回收表空間也很方便。不好的地方在于每個表文件都有.frm和.ibd文件兩個文件描述符，如果單表增長過快就很容易出現性能問題。
`共享表空間`的數據和文件放在一起方便管理。但是共享表空間無法在線回收空間，共享表空間想要回收，需要將InnoDB表中的數據備份、刪除原表，然后再把數據導回到與原表結構一樣的新表中。統計分析、日志類系統不適合用共享表空間。
綜合考慮，獨立表空間效率、性能比共享表空間高一些。默認使用獨立表空間。
`臨時表空間`是把臨時表數據從系統表空間抽離出來形成自己的獨立表空間，默認大小為12MB。
`通用表空間`是多個表放在同一個表空間中，可以根據活躍度劃分表，存放在不同的磁盤上，減少metadata的存儲開銷。生產上很少使用。
1. 段
表空間由段組成，也可以把一個表理解為多個段。通常有數據段、回滾段、索引段等。每個段由N個區和32個零散頁組成，段空間擴展是以區為單位進行擴展的。
2. 區
區是由連續的頁組成的，是物理上連續分配的一段空間，每個區的大小固定是1MB。
3. 頁
InnoDB的最小物理存儲分配單位是page，由數據回滾頁等。一般情況下，一個區由64個連續頁組成，頁默認大小是16KB。一個page頁會默認預留1/16的空間用于更新數據。一個頁最少可以存兩行數據。虛擬最小行和虛擬最大行用來限定記錄的范圍，以此來保證B+tree節點是雙向鏈表結構。
:-: 
1頁的結構
`整體頁的結構`有記錄頁頭信息的、記錄狀態信息和首個記錄位置的、虛擬行記錄限定記錄邊界的、存儲實際行數據信息的、空閑空間、存放記錄的相對位置的、保證頁完整寫入磁盤的。
1. 行
頁里記錄著行記錄的信息，InnoDB存儲引擎是面向行的。也就是數據是按照行記錄的。
##### 內存結構
MySQL內存結構分為`SGA(系統全局區)`和`PGA(程序緩存區)`。可以通過數據庫內存參數分配。
系統全局區(SGA)設置參數:
1. innodb\_buffer\_pool
用途：用來緩存innoDB表的數據、索引、插入緩沖、數據字典等信息。
2. innodb\_log\_buffer
用途：事務在內存的緩沖，即redo log buffer的大小。
3. Query Cache
用途：高速查詢緩存，生產建議關閉。
4. key\_buffer\_size
用途：只用于MyISAM存儲引擎表，緩存MyISAM存儲。
5. innodb\_additional\_mem\_pool\_size
用途：用來保存數據字典信息和其他內部數據結構的內存池的大小。MySQL5.7.4后被移除了。
程序緩存區(PGA)設置參數：
1. sort\_buffer\_size
用途：主要用于SQL語句在內存中的臨時排序。
2. join\_buffer\_size
用途：表連接使用，用于BKA(一種join連接優化)。
3. read\_buffer\_size
用途：表順序掃描的緩存，只能應用于MyISAM表存儲引擎。
4. read\_rnd\_buffer\_size
用途：MySQL隨機讀緩沖區大小，用于做mrr(一種對主鍵索引集合排序的優化)。
特殊：
1. tmp\_table\_size
用途：SQL語句在排序或者分組時沒有用到索引，就會使用臨時表空間。
2. max\_heap\_table\_size
用途：管理heap、memory引擎表。
##### Buffer 狀態及其鏈表結構
page是InnoDB磁盤I/O的最小單位，數據是存放在page中，對應到內存就是一個個buffer。
buffer分三個狀態：
* free buffer：空閑的buffer
* clean buffer：內存和磁盤數據一致的buffer
* dirty buffer：內存中新寫入的信息還未刷進磁盤的buffer
因此由三個不同的buffer集合形成了三條雙向鏈表：
* free list：空閑的buffer鏈表
* lru list：內存與磁盤一致最近用到的buffer鏈表
* flush list：將內存和磁盤數據不一致的buffer形成鏈表方便線程將其刷到磁盤。
##### 各大線程及其作用
InnoDB存儲引擎屬于多線程模型，后臺有多種線程，負責處理不同的任務。
`master線程`是主線程，優先級最高。內部有主循環loop、后臺循環background loop、刷新循環flush loop、暫停循環suspend loop。
主循環loop每1s操作：
1. 日志緩沖刷新到磁盤，即使這個事務還沒有提交。
2. 刷新臟頁到磁盤。
3. 執行合并插入緩沖的操作。
4. 產生checkpoint（檢查點，一種刷臟數據的機制）。
5. 清除無用的table cache。
6. 如果沒有用戶活動就切換到后臺循環
主循環loop每10s操作：
1. 日志緩沖刷新到磁盤，即使事務還沒有提交。
2. 執行合并插入緩沖的操作。
3. 刷新臟頁到磁盤。
4. 刪除無用的undo頁。
5. 產生checkpoint。
`read thread` 線程是數據庫讀線程。
`write thread` 線程是數據庫寫線程。
`redo log thread` 線程負責把日志緩沖刷新到redo log文件中。
`change buffer thread` 線程負責把插入緩沖中的內容刷新到磁盤。
##### 內存刷新機制
MySQL講究`日志先行`，就是一條DML語句(對表操作的語句)進入數據庫后，都會先寫日志，再寫數據文件。
1. redo log
redo log 是重做日志文件。用于記錄事務操作的變化，記錄的是數據修改后的值，不管事務是否提交都會記錄下來。用來保證數據完整性。默認情況下至少有兩個redo log文件，在磁盤上用ib\_logfile(0~N)命名。
redo log 刷到磁盤的條件：
* 通過innodb\_flush\_log\_at\_trx\_commit參數來控制將redo log buffer中的數據寫入redo log文件。
* master thread：每秒進行刷新。
* redo log buffer：超過一半時會進行刷新。
2. binlog
DML語句即會寫redo log文件，也會寫binlog二進制文件。用于備份恢復和主從復制。由sysc\_binlog參數決定，事務提交后，MySQL會讓Filesystem自行決定什么時候同步，或者等cache滿了之后才同步到磁盤。
總結臟頁的刷新條件：
1. 重做日志ib\_logfile文件寫滿后，會執行checkpoint觸發臟頁刷新。
2. 通過innodb\_max\_dirty\_pages\_pct參數控制。指的在buffer pool中臟頁所占百分比達到設置之后進行刷新。
3. 通過innodb\_adaptive\_flushing參數控制。該參數影響每秒刷新臟頁的數目。
##### InnoDB三大特性
1. 插入緩沖
插入緩沖的作用是把普通索引上的DML操作從隨機I/O變成順序I/O，提高I/O效率。
2. 兩次寫
兩次寫保證寫入的安全性，防止在MySQL實例發生宕機時，InnoDB發生數據頁部分頁寫的問題。
3. 自適應哈希
InnoDB存儲引擎有一個機制，可以監控索引的搜索，如果InnoDB注意到查詢可以通過建立哈希索引得到優化，就會自動完成這件事。
#### 14.14 事務的隔離級別
InnoDB有4中隔離級別，默認是可重復讀。
1. 讀未提交。在其中一個事務里讀到其他事務未提交的變化。這種讀取稱為臟讀。
2. 讀已提交。在其中一個事務里讀到其他事務已經提交的變化。這種讀取也稱為不可重復讀，允許發生幻讀現象。
3. 可重復讀。在其中一個事務結束前，可以反復讀取事務剛開始看到的數據，并一致不會發生變化，避免了臟讀、不可重復讀、幻讀。
4. 串行。在每個讀的數據行上都加表級共享鎖，每次寫數據都加表級排它鎖。這樣會造成并發能力降低，但是保證了數據完整性。不建議使用在生產環境中。
臟讀：其中一個事務讀到其他事務還未提交的數據。
不可重復讀：在其中一個事務中讀取到其他事務針對舊數據的修改記錄。
幻讀：在其中一個事務中，讀到了其他事務新增的數據。
##### InnoDB的鎖類型
1. 讀鎖（共享鎖）
一個事務獲取一個數據行的讀鎖，其他事務可以獲得該行對應的讀鎖，不能獲得寫鎖。
2. 寫鎖（排他鎖）
一個事務獲取了一個數據行的寫鎖，其他事務不能再獲取該行得其他鎖。
3. MDL鎖
一個會話開啟了事務后自動開啟一個MDL鎖 (表級別)，其他會話不能進行DDL操作(庫級操作)。
4. 意向鎖
意向鎖是表級鎖，分為`意向共享鎖`和`意向排他鎖`。意向共享鎖是在給一個數據行加共享鎖之前獲取的；意向排他鎖是在給一個數據行加排他鎖之前獲取的。
##### InnoDB行鎖種類
1. 單行記錄的鎖
用于在單行操作時候加鎖。
2. 間隙鎖(Gap lock)
為了避免幻讀，加入了間隙鎖，鎖定記錄數據的范圍，不包含記錄本身。即不允許在此范圍內插入任何數據，常用于where指定范圍的語句。
3. Next-key locks
是記錄鎖和間隙鎖的組合，當InnoDB掃描鎖記錄時，會先對選中的索引記錄加上記錄鎖，再對索引記錄兩邊的間隙上加上間隙鎖。
4. 鎖等待和死鎖
鎖等待是指一個事務過程中產生的鎖，其他事務需要等待上一個事務釋放鎖后才能使用該資源。一直不釋放超過鎖等待時間會報鎖超時錯誤。
死鎖是指兩個或兩個以上的事務在執行過程中爭搶資源造成的一種相互等待的現象，例如：
A會話執行update tt set name ='aaa' where score=60
B會話執行update tt set name ='a' where score=70
A會話執行update tt set name ='bb' where score=70
B會話執行update tt set name ='aa' where score=60
InnoDB存儲引擎可以自動檢測死鎖，并自動回滾該事務。