MySQL資料庫學習筆記（六）----MySQL多表查詢之外鍵、表串連、子查詢、索引

標籤：des   blog   http   io   os   ar   使用   java   for   

本章主要內容：

• 一、外鍵
  
• 二、表串連
  
• 三、子查詢
  
• 四、索引

一、外鍵：

• 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的姓名。

分析的道理同上。

其實，上面的兩個查詢結果都是下面這個查詢結果的yibufe：

三、子查詢：

作用：某些情況下，當進行查詢的時候，需要的條件是另外一個select 語句的結果，這個時候，就要用到子查詢。

定義：為了給主查詢（外部查詢）提供資料而首先執行的查詢（內部查詢）被叫做子查詢。也就是說，先執行子查詢，根據子查詢的結果，再執行主查詢。

關鍵字：用於子查詢的關鍵字主要包括 IN、NOT IN、EXIST、NOT EXIST、=、<>等（符號“<>”的意思是：不等於）。

備忘：MySQL從4.1開始才支援SQL的子查詢。一般說子查詢的效率低於串連查詢（因為子查詢至少需要查詢兩次，即至少兩個select語句。子查詢嵌套也多，效能越低）。表串連都可以用子查詢替換，但反過來說卻不一定。

我們一下面的這張員工表舉例：

?

1、舉例：查詢月薪最高的員工的名字

上面的例子中，我們就是先通過彙總函式查出最高的月薪，然後根據這個值查出對應員工的名字。

2、舉例：查詢出每個部門的平均月薪

上面的例子中，先將部門進行分類（前提是部門不可為空），然後分別單獨求出各類中的薪水平均值。

註：這裡我們沒有用到子查詢，因為比較麻煩。

3、舉例：查詢月薪比平均月薪高的員工的名字（我們知道，整體的平均工資是250）

疑問：如果要查詢比部門平均工資高的員工，該怎麼寫呢？下面的這種寫法是錯誤的：

四、索引

主要內容如下：

• 1、索引的概念
• 2、普通索引
• 3、唯一索引
• 4、主鍵索引
• 5、全文索引
• 6、刪除、禁用索引
• 7、設計索引的原則

關於索引，推薦的學習連結：

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html（大牛）

http://blog.csdn.net/cuidiwhere/article/details/8452997

http://www.cnblogs.com/cq-home/p/3482101.html

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的前十個字元。

• 利用最左首碼。
• 根據搜尋的關鍵字建立多列索引。
• 不要過度索引。維護索引需要成本。

MySQL資料庫學習筆記（六）----MySQL多表查詢之外鍵、表串連、子查詢、索引