本章主要内容：
一、外鍵
二、表連接配接
三、子查詢
四、索引
一、外鍵：
1、什麼是外鍵
2、外鍵文法
3、外鍵的條件
4、添加外鍵
5、删除外鍵
1、什麼是外鍵：
主鍵：是唯一辨別一條記錄，不能有重複的，不允許為空，用來保證資料完整性
外鍵：是另一表的主鍵, 外鍵可以有重複的, 可以是空值，用來和其他表建立聯系用的。是以說，如果談到了外鍵，一定是至少涉及到兩張表。例如下面這兩張表：
上面有兩張表：部門表(dept)、員工表(emp)。Id=Dept_id，而Dept_id就是員工表中的外鍵：因為員工表中的員工需要知道自己屬于哪個部門，就可以通過外鍵Dept_id找到對應的部門，然後才能找到部門表裡的各種字段資訊，進而讓二者相關聯。是以說，外鍵一定是在從表中建立，進而找到與主表之間的聯系；從表負責維護二者之間的關系。
我們先通過如下指令把部門表和職工表建立好，友善後面的舉例：
create table department(
id int primary key auto_increment,
name varchar(20) not null,
description varchar(100)
);
create table employee(
id int primary key auto_increment,
name varchar(10) not null,
gender varchar(2) not null,
salary float(10,2),
age int(2),
gmr int,
dept_id int
);
然後把兩張表的資料填好，顯示效果如下：
部門表：
員工表：
2、外鍵的使用需要滿足下列的條件：(這裡涉及到了InnoDB的概念)
1. 兩張表必須都是InnoDB表，并且它們沒有臨時表。
注：InnoDB是資料庫的引擎。MySQL常見引擎有兩種：InnoDB和MyISAM，後者不支援外鍵。
2. 建立外鍵關系的對應列必須具有相似的InnoDB内部資料類型。
3. 建立外鍵關系的對應列必須建立了索引。
4. 假如顯式的給出了CONSTRAINT symbol，那symbol在資料庫中必須是唯一的。假如沒有顯式的給出，InnoDB會自動的建立。
面試題：你的資料庫用什麼存儲引擎？差別是？
答案：常見的有MyISAM和InnoDB。
MyISAM：不支援外鍵限制。不支援事務。對資料大批量導入時，它會邊插入資料邊建索引，是以為了提高執行效率，應該先禁用索引，在完全導入後再開啟索引。
InnoDB：支援外鍵限制，支援事務。對索引都是單獨處理的，無需引用索引。
3、添加外鍵的文法：
有兩種方式：
方式一：在建立表的時候進行添加
方式二：表已經建立好了，繼續修改表的結構來添加外鍵
【方式一】在建立表的時候進行添加
[CONSTRAINT symbol] FOREIGN KEY [id] (從表的字段1)
REFERENCES tbl_name (主表的字段2)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}
上面的代碼是同一行，中括号裡的内容是可選項。
解釋如下：
CONSTRAINT symbol：可以給這個外鍵限制起一個名字，有了名字，以後找到它就很友善了。如果不加此參數的話，系統會自動配置設定一個名字。
FOREIGN KEY：将從表中的字段1作為外鍵的字段。
REFERENCES：映射到主表的字段2。
ON DELETE後面的四個參數：代表的是當删除主表的記錄時，所做的約定。
RESTRICT(限制)：如果你想删除的那個主表，它的下面有對應從表的記錄，此主表将無法删除。
CASCADE(級聯)：如果主表的記錄删掉，則從表中相關聯的記錄都将被删掉。
SET NULL：将外鍵設定為空。
NO ACTION：什麼都不做。
注：一般是RESTRICT和CASCADE用的最多。
【方式二】表已經建立好了，繼續修改表的結構來添加外鍵。
我們在第一段中内容中已經将表建好了，資料也填充完了，現在來給從表(員工表)添加外鍵，讓它與主表(部門表)相關聯。代碼舉例如下：
ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id);
代碼解釋：
ALTER TABLE employee：在從表employee中進行操作；
ADD FOREIGN KEY(dept_id)：将從表的字段dept_id添加為外鍵；
REFERENCES department(id)：映射到主表department當中為id的字段。
運作上方代碼後，我們通過navicat來看一下外鍵有沒有添加成功：
上圖中，選中表employee，單擊紅框部分的“設計表”按鈕，界面如下：
上圖中就可以看到我們建立的外鍵了，而且系統預設給這個外鍵起了個名字：employee_ibfk_1。預設規則是RESTRICT。緊接着來給外鍵設定值：
上圖中，我們打開員工表，然後給外鍵設定值，1代表宣傳部，2代表秘書部。
然後我們回到主表(部門表)，此時如果想删除id為1的宣傳部，會彈出如下提示：(因為外鍵的預設規則為RESTRICT)
4、删除外鍵：(通過sql語句的方式)
我們在navicat中可以通過圖形界面的方式删除外鍵，也可以通過sql語句來删除。
(1)擷取外鍵名：
如果在指令行中不知道外鍵的名字，可以通過檢視表的定義找出外鍵的名稱：
show create table emp;
運作效果如下：
其實我們在表的資訊中也可以看到：(注意書寫指令的格式)
(2)删除外鍵：
alter table emp drop foreign key 外鍵名;
二、表連接配接(join)
我們以下面的兩張表舉例：作為本段内容的例子
department部門表：
employee員工表：
其中，外鍵對應關系為：employee.dept_id=department.id。employee.leader中的數字的含義為：生命壹号的leader是生命二号，生命二号沒有leader，生命叁号的leader是生命壹号。
1、内連接配接：隻列出比對的記錄
文法：
SELECT … FROM join_table
[INNER] JOIN join_table2
[ON join_condition]
WHERE where_definition
解釋：隻列出這些連接配接表中與連接配接條件相比對的資料行。INNER可以不寫，則預設為内連接配接。[ON join_condition]裡面寫的是連接配接的條件。
舉例：
select e.name,d.name from employee e inner join department d on e.dept_id=d.id;
等價于：
select e.name,d.name from employee e,department d where e.dept_id=d.id;
運作效果：
2、外連接配接：
外連接配接分類：
左外連接配接(LEFT [OUTER] JOIN)
右外連接配接(RIGHT [OUTER] JOIN)
全外連接配接(FULL [OUTER] JOIN) 注：MySQL5.1的版本暫不支援
文法：
SELECT … FROM join_table1
(LEFT | RIGHT | FULL) [OUTER] JOIN join_table2
ON join_condition
WHERE where_definition
解釋：
不僅列出與連接配接條件(on)相比對的行，還列出左表table1(左外連接配接)、或右表table2(右外連接配接)、或兩個表(全外連接配接)中所有符合WHERE過濾條件的資料行。一般都是用左連接配接或者外連接配接。
其中，[OUTER]部分可以不寫，(LEFT | RIGHT | FULL)部分要寫其中一個。
2、1左外連接配接：左表列出全部，右表隻列出比對的記錄。
舉例：
2、2右外連接配接：右表列出全部，左表隻列出比對的記錄。
舉例：
3、交叉連接配接：
文法：
SELECT … FROM join_table1 CROSS JOIN join_table2;
沒有ON子句和WHERE子句，它傳回的是連接配接表中所有資料行的笛卡爾積。
笛卡爾積舉例：假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}
其結果集合中的資料行數等于第一個表中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。
等價于：(薦)
SELECT … FROM table1, table2;
舉例：
4、自連接配接：參與連接配接的表都是同一張表。(通過給表取别名虛拟出兩張表)
注：非常重要，在JavaWeb中的目錄樹中用的特别多。
舉例：查詢出員工姓名和其leader的姓名(類似于求節點及其父節點)
我們來詳細解釋一下上面的代碼。對于同一張employee表，我們把e1作為員工表，e2作為上司表。首先把全部的員工列出來(基于左外連接配接)，然後找到我們所需要的條件：員工的經理id(e1.leader)等于經理表的id(e2.id)。
舉例：查詢出所有leader的姓名。
分析的道理同上。
其實，上面的兩個查詢結果都是下面這個查詢結果的一部分：
三、子查詢：
作用：某些情況下，當進行查詢的時候，需要的條件是另外一個select 語句的結果，這個時候，就要用到子查詢。
定義：為了給主查詢(外部查詢)提供資料而首先執行的查詢(内部查詢)被叫做子查詢。也就是說，先執行子查詢，根據子查詢的結果，再執行主查詢。
關鍵字：用于子查詢的關鍵字主要包括 IN、NOT IN、EXIST、NOT EXIST、=、<>等(符号“<>”的意思是：不等于)。
備注：MySQL從4.1開始才支援SQL的子查詢。一般說子查詢的效率低于連接配接查詢(因為子查詢至少需要查詢兩次，即至少兩個select語句。子查詢嵌套也多，性能越低)。表連接配接都可以用子查詢替換，但反過來說卻不一定。
我們一下面的這張員工表舉例：
1、舉例：查詢月薪最高的員工的名字
上面的例子中，我們就是先通過聚合函數查出最高的月薪，然後根據這個值查出對應員工的名字。
2、舉例：查詢出每個部門的平均月薪
上面的例子中，先将部門進行分類(前提是部門不能為空)，然後分别單獨求出各類中的薪水準均值。
注：這裡我們沒有用到子查詢，因為比較麻煩。
3、舉例：查詢月薪比平均月薪高的員工的名字(我們知道，整體的平均工資是250)
疑問：如果要查詢比部門平均工資高的員工，該怎麼寫呢？下面的這種寫法是錯誤的：
四、索引
主要内容如下：
1、索引的概念
2、普通索引
3、唯一索引
4、主鍵索引
5、全文索引
6、删除、禁用索引
7、設計索引的原則
關于索引，推薦的學習連結：
1、索引的概念：
索引是資料庫中用來提高查詢性能的最常用工具。
所有MySQL列類型都可以被索引，對相關列使用索引是提高SELECT操作性能的最佳途徑。索引用來快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹的形式儲存。
在使用以下操作符時，都會用到相關列上的索引：
>、=、<=、<>、IN、 BETWEEN
LIKE 'pattern'(pattern不能以通配符開始，即通配符不能放前面，即使放在了前面，索引也無效)
注：索引的值因為不斷改變，是以是它需要維護的。如果資料量較少，建議不用索引。
2、normal普通索引(第一種索引)
方式一：直接建立索引：
文法：
CREATE INDEX 索引名 ON 表名(列名[(length)]…);
舉例：
然後，我們在表中可以看到新建立的索引：(我們可以在這個navicat的可視化界面中修改索引類型)
方式二：修改表時添加索引
文法：
ALTER TABLE 表名 ADD INDEX [索引名] (列名[(length)]…);
方式三：建立表的時候指定索引：
CREATE TABLE 表名 ( 表名 (
[...],
INDEX [索引名] (列名[(length)]…);
注意：如果要建立索引的列的類型是CHAR、VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定length。
3、unique 唯一索引：(第二種索引)
這種索引和前面的“普通索引”基本相同，但有一個差別：索引列的所有值都必須唯一。例如可以将身份證号作為索引。
建立方式和上方的普通索引類似。即：将普通索引的“index”改為“unique index”。
4、主鍵索引(一種特殊的唯一索引)
主鍵是一種特殊的唯一索引，一般在建立表的時候指定。在 MYSQL 中，當你建立主鍵時，主鍵索引同時也已經建立起來了，不必重複設定。
記住：一個表隻能有一個主鍵，也即隻有一個主鍵索引。
5、FULLTEXT全文索引：(第三種索引)
MySQL從3.2版開始支援全文索引和全文檢索。在MySQL中，全文索引的索引類型為FULLTEXT。
MySQL5.0版本隻有MyISAM存儲引擎支援FULLTEXT，并且隻限于CHAR、VARCHAR和TEXT類型的列上建立。
注：全文索引維護起來很吃力，是以了解即可。
建立方式和上方的普通索引類似。即：将普通索引的“index”改為“fulltext index”。
6、删除、禁用索引:
一般使用“删除”，不使用“禁用”。
删除索引：
文法：
DROP INDEX 索引名 ON 表名
對于MyISAM表在做資料大批量導入時，它會邊插入資料邊建索引。是以為了提高執行效率，應該先禁用索引，在完全導入後，再開啟索引。而InnoDB表對索引都是單獨處理的，無需禁用索引。
禁用索引：
ALTER TABLE 表名 DISABLE KEYS;
打開索引：
ALTER TABLE 表名 ENABLE KEYS;
7、設計索引的原則：
最适合索引的列是出現在WHERE子句中的列，或連接配接子句(on語句)中指定的列，而不是出現在SELECT後的列。
索引列的值中，不相同的數目越多，索引的效果越好。
使用短索引：對于CHAR和VARCHAR列，隻用它的一部分來建立索引，可以節省索引空間，也會使查詢更快捷。
如：CREATE INDEX part_of_name ON employees(name(10)); 這個句子中指定的length長度為10，就是使用短索引，也就是說取name的前十個字元。
利用最左字首。
根據搜尋的關鍵字建立多列索引。
不要過度索引。維護索引需要成本。