一 : 外鍵

現(xiàn)在有兩個(gè)表category分類(lèi)表

| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| cid   | varchar(32)  | NO   | PRI | NULL    |       |
| cname | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

與product商品表

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | varchar(32) | NO   | PRI | NULL    |       |
| pname       | varchar(40) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       |
| category_id | varchar(32) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

其中在product表中的字段category_id存放的是 category表中cid(主鍵)的信息列稱(chēng)為外鍵. 此時(shí)分類(lèi)稱(chēng)為主表,'cid'稱(chēng)為主鍵,product稱(chēng)為從表,category_id稱(chēng)為外鍵,通過(guò)主表的主鍵和從表的外鍵來(lái)描述主外鍵關(guān)系,呈現(xiàn)就是一對(duì)多的關(guān)系.

外鍵的特點(diǎn) :

• 從表外鍵的值是對(duì)主表主鍵的引用.
• 從表外鍵類(lèi)型,必須與主表主鍵類(lèi)型一致.

聲明外鍵約束

alter table 從表 add [constraint][外鍵名稱(chēng)] foreign key (從表字段名) references 主表(主表的主鍵)

外鍵名稱(chēng) 用于刪除外鍵約束的,一般建議_fk結(jié)尾

alter table 從表 drop foregin key 外鍵名稱(chēng)

使用外鍵目的是為了保證數(shù)據(jù)的完整性,刪除的時(shí)候會(huì)有約束.

對(duì)例子進(jìn)行外鍵約束

alter table product add foreign key(category_id) references category(cid);

從表不能夠添加(更新),主表中不存在的數(shù)據(jù).
主表不能夠刪除(更新),從表中已經(jīng)使用的數(shù)據(jù).

二 : 多表之間的關(guān)系

表與表數(shù)據(jù)之間的關(guān)系.

• 一對(duì)多關(guān)系 :

產(chǎn)品與產(chǎn)品類(lèi)別, 一個(gè)產(chǎn)品對(duì)應(yīng)一種類(lèi)別,一個(gè)產(chǎn)品類(lèi)別包含多種產(chǎn)品,舉一個(gè)例子來(lái)說(shuō), 《蟻人》只屬于漫威系列,《雷神》也只屬于漫威系列,但是漫威宇宙還包含很多很多系列電影.

• 多對(duì)多關(guān)系 :

大學(xué)老師與學(xué)生的關(guān)系,一個(gè)學(xué)生可以從不同老師那里學(xué)習(xí)到知識(shí),相對(duì)的一個(gè)老師可以教多個(gè)學(xué)生.

多對(duì)多關(guān)系建表原則 : 需要?jiǎng)?chuàng)建第三張表,中間表中至少兩個(gè)字段,這兩個(gè)字段分別作為外鍵指向各自一方的主鍵,也就是將一個(gè)多對(duì)多拆分成兩個(gè)一對(duì)多.

• 一對(duì)一關(guān)系:

在實(shí)際開(kāi)發(fā)中應(yīng)用不多,一對(duì)一可以用一張表完成.
外鍵唯一 : 主表的主鍵和從表的外鍵( 唯一 ),形成主外鍵關(guān)系,unique
外鍵是主鍵 : 主表的主鍵和從表的主鍵,形成主外鍵關(guān)系.

三 : 多表查詢

建立多對(duì)多,訂單表與商品表

訂單表

create table orders(
    oid varchar(32) primary key,
    totalprice double
);

訂單項(xiàng)表

create table orderitem(
    oid varchar(50),
    pid varchar(50)
);

聯(lián)合主鍵

alter table orderitem add primary key(oid,pid);

訂單表和訂單項(xiàng)表主外鍵關(guān)系

alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);

商品表和訂單項(xiàng)表的主外鍵關(guān)系

alter table orderitem add constraint orderitem_product_fk foreign key(pid) references product(pid);

多對(duì)多關(guān)系構(gòu)圖

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | varchar(32) | NO   | PRI | NULL    |       |
| pname       | varchar(40) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       |
| category_id | varchar(32) | YES  | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

1
|
|
|
∞

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid   | varchar(50) | NO   | PRI | NULL    |       |
| pid   | varchar(50) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

∞
|
|
|
1

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| oid        | varchar(32) | NO   | PRI | NULL    |       |
| totalprice | double      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

準(zhǔn)備數(shù)據(jù)

+------+-----------------+-------+-------------+
| pid  | pname           | price | category_id |
+------+-----------------+-------+-------------+
| p001 | 蘋(píng)果筆記本      | 14000 | c001        |
| p002 | 蘋(píng)果手機(jī)        |  9000 | c001        |
| p003 | 手寫(xiě)板          |  5000 | c001        |
| p004 | JACK JONES      |   800 | c002        |
| p005 | 耐克            |   600 | c002        |
| p006 | 阿迪達(dá)斯        |   440 | c002        |
| p007 | 李寧            |   200 | c002        |
| p008 | 香奈兒          |   800 | c003        |
| p009 | 蘭蔻            |  1000 | c003        |
+------+-----------------+-------+-------------+

+------+-----------+
| cid  | cname     |
+------+-----------+
| c001 | 電子      |
| c002 | 服飾      |
| c003 | 化妝品    |
+------+-----------+

• 交叉查詢

兩表之間的乘機(jī),不常用

select * from A,B;

• 內(nèi)連查詢

隱式內(nèi)連接

select * from A,B where 條件 ; 

mysql> select * from category,product where cid = category_id;
+------+-----------+------+-----------------+-------+-------------+
| cid  | cname     | pid  | pname           | price | category_id |
+------+-----------+------+-----------------+-------+-------------+
| c001 | 電子      | p001 | 蘋(píng)果筆記本      | 14000 | c001        |
| c001 | 電子      | p002 | 蘋(píng)果手機(jī)        |  9000 | c001        |
| c001 | 電子      | p003 | 手寫(xiě)板          |  5000 | c001        |
| c002 | 服飾      | p004 | JACK JONES      |   800 | c002        |
| c002 | 服飾      | p005 | 耐克            |   600 | c002        |
| c002 | 服飾      | p006 | 阿迪達(dá)斯        |   440 | c002        |
| c002 | 服飾      | p007 | 李寧            |   200 | c002        |
| c003 | 化妝品    | p008 | 香奈兒          |   800 | c003        |
| c003 | 化妝品    | p009 | 蘭蔻            |  1000 | c003        |
+------+-----------+------+-----------------+-------+-------------+

顯示內(nèi)連接

select * from A inner join B on 條件;

mysql> select distinct cname from category c inner join product p on c.cid = p.category_id;
+-----------+
| cname     |
+-----------+
| 電子      |
| 服飾      |
| 化妝品    |
+-----------+

• 外連接查詢

我們往 類(lèi)別表與商品表分別添加兩條數(shù)據(jù)

 insert into category(cname,cid) values('甜品',5);

 insert into product(pid,pname,price,category_id) values('p010','甜筒',14,null);

左外連接 : left outer join

select * from A left outer join B on 條件

右外連接 : right outer join

select * from A right outer join B on 條件

mysql> select * from category c left outer join product p on c.cid = p.category_id;
+------+-----------+------+-----------------+-------+-------------+
| cid  | cname     | pid  | pname           | price | category_id |
+------+-----------+------+-----------------+-------+-------------+
| 5    | 甜品      | NULL | NULL            |  NULL | NULL        |
| c001 | 電子      | p001 | 蘋(píng)果筆記本      | 14000 | c001        |
| c001 | 電子      | p002 | 蘋(píng)果手機(jī)        |  9000 | c001        |
| c001 | 電子      | p003 | 手寫(xiě)板          |  5000 | c001        |
| c002 | 服飾      | p004 | JACK JONES      |   800 | c002        |
| c002 | 服飾      | p005 | 耐克            |   600 | c002        |
| c002 | 服飾      | p006 | 阿迪達(dá)斯        |   440 | c002        |
| c002 | 服飾      | p007 | 李寧            |   200 | c002        |
| c003 | 化妝品    | p008 | 香奈兒          |   800 | c003        |
| c003 | 化妝品    | p009 | 蘭蔻            |  1000 | c003        |
+------+-----------+------+-----------------+-------+-------------+
10 rows in set (0.00 sec)

mysql> select * from category c right outer join product p on c.cid = p.category_id;
+------+-----------+------+-----------------+-------+-------------+
| cid  | cname     | pid  | pname           | price | category_id |
+------+-----------+------+-----------------+-------+-------------+
| c001 | 電子      | p001 | 蘋(píng)果筆記本      | 14000 | c001        |
| c001 | 電子      | p002 | 蘋(píng)果手機(jī)        |  9000 | c001        |
| c001 | 電子      | p003 | 手寫(xiě)板          |  5000 | c001        |
| c002 | 服飾      | p004 | JACK JONES      |   800 | c002        |
| c002 | 服飾      | p005 | 耐克            |   600 | c002        |
| c002 | 服飾      | p006 | 阿迪達(dá)斯        |   440 | c002        |
| c002 | 服飾      | p007 | 李寧            |   200 | c002        |
| c003 | 化妝品    | p008 | 香奈兒          |   800 | c003        |
| c003 | 化妝品    | p009 | 蘭蔻            |  1000 | c003        |
| NULL | NULL      | p010 | 甜筒            |    14 | NULL        |
+------+-----------+------+-----------------+-------+-------------+

注意觀察上面左連接與右連接的查詢結(jié)果分析其聯(lián)系.
內(nèi)連接 : 查詢兩個(gè)表交集
左外連接 : 左表全部以及兩個(gè)表的交集
右外連接 : 右表全部以及兩個(gè)表的交集

四 : 子查詢

一條select語(yǔ)句結(jié)果作為另一條語(yǔ)句的一部分(查詢條件,查詢結(jié)果,表等).

mysql> select * from product where category_id = (select cid from category where cname = '電子');
+------+-----------------+-------+-------------+
| pid  | pname           | price | category_id |
+------+-----------------+-------+-------------+
| p001 | 蘋(píng)果筆記本      | 14000 | c001        |
| p002 | 蘋(píng)果手機(jī)        |  9000 | c001        |
| p003 | 手寫(xiě)板          |  5000 | c001        |
+------+-----------------+-------+-------------+