1 多表關(guān)系

?? 一對(duì)一關(guān)系

比如：一個(gè)人有一個(gè)身份證，一個(gè)身份證對(duì)應(yīng)一個(gè)人；實(shí)現(xiàn)原則：在任一表中添加唯一外鍵，指向另一方主鍵；在實(shí)際開(kāi)發(fā)中遇到一對(duì)一情況比較少，遇到一對(duì)一關(guān)系一般合并表。在下圖中，可以將兩個(gè)表根據(jù) id 合并：

?? 一對(duì)多/多對(duì)一關(guān)系

比如：一個(gè)部門(mén)有多個(gè)員工，一個(gè)員工只能對(duì)應(yīng)一個(gè)部門(mén)；實(shí)現(xiàn)原則：在多的一方建立外鍵，指向另一方的主鍵， 示意圖如下：

?? 多對(duì)多關(guān)系

比如：一個(gè)學(xué)生可以修多個(gè)課程，一個(gè)課程也可以被多個(gè)學(xué)生選擇；實(shí)現(xiàn)原則：多對(duì)多關(guān)系實(shí)現(xiàn)需要借助第三張中間表。中間表至少包含兩個(gè)字段，將多對(duì)多的關(guān)系，折成一對(duì)多的關(guān)系，中間表至少要有兩個(gè)外鍵，這兩個(gè)外鍵分別指向原來(lái)兩個(gè)表的主鍵， 示意圖如下：

2 外鍵約束（FOREIGN KEY）

2.1 外鍵約束說(shuō)明

?? 簡(jiǎn)介：

  MySQL 外鍵約束是表的一種特殊字段，經(jīng)常與主鍵約束一起使用。對(duì)于兩個(gè)具有關(guān)聯(lián)關(guān)系的表而言，相關(guān)聯(lián)字段中主鍵所在的表就是主表（父表），外鍵所在的表就是從表（子表）。

  外鍵約束用來(lái)建立主表與從表的關(guān)聯(lián)關(guān)系，為兩個(gè)表的數(shù)據(jù)建立連接，約束兩個(gè)表中數(shù)據(jù)的一致性和完整性。

?? 舉例說(shuō)明：

  在下圖中，從表中的部門(mén)號(hào)受主表中的主鍵部門(mén)號(hào)的范圍限制， 即，從表中的部門(mén)號(hào)列只能取值為1001、1002或1003。

?? 定義外鍵的規(guī)則：

1. 主表必須已經(jīng)存在于數(shù)據(jù)庫(kù)中，或者是當(dāng)前正在創(chuàng)建的表（即在創(chuàng)建外鍵時(shí)，主表必須存在）；
2. 必須為主表定義主鍵；
3. 主鍵不能包含空值，但允許在外鍵中出現(xiàn)空值；
4. 在主表的表名后面指定列名或列名的組合，這個(gè)列或者組合必須是主表的主鍵或候選鍵；
5. 外鍵中列的數(shù)目必須和主表的主鍵中列的數(shù)目相同（通俗解釋：主鍵的列數(shù)必須與外鍵的列數(shù)相同）；
6. 外鍵中列的數(shù)據(jù)類(lèi)型必須和主表主鍵中對(duì)應(yīng)列的數(shù)據(jù)類(lèi)型相同。

2.2 外鍵約束的創(chuàng)建

?? 1.在創(chuàng)建表時(shí)設(shè)置外鍵約束

  在 create table 語(yǔ)句中，使用 foreign key 關(guān)鍵字來(lái)指定外鍵。具體語(yǔ)法格式如下：

[constraint <外鍵名>] foreign key 字段名1 [, 字段名2, ...] 
references <主表名> 主鍵列1, [, 主鍵列2, ...]

?? 2.修改表時(shí)添加外鍵約束

  這樣做的前提是：從表中外鍵的列中的數(shù)據(jù)必須與主鍵中主鍵列中的數(shù)據(jù)一致或沒(méi)有數(shù)據(jù)。 語(yǔ)法如下：

alter table <數(shù)據(jù)表名>
add constraint <外鍵名> foreign key(<列名>)
references <主表名> (<列名>);

2.3 外鍵約束實(shí)操：一對(duì)多關(guān)系

?? 下面使用下面這段代碼創(chuàng)建兩個(gè)表，dept 作主表存儲(chǔ)部門(mén)信息，emp 作從表存儲(chǔ)員工信息，主鍵約束與外鍵約束詳細(xì)見(jiàn)代碼注釋：

-- 創(chuàng)建部門(mén)表（主表）
CREATE TABLE IF NOT EXISTS dept
(
    deptno VARCHAR(20) PRIMARY KEY, -- 部門(mén)號(hào)
    NAME   VARCHAR(20)              -- 部門(mén)名字
);

-- 創(chuàng)建員工表（從表）
CREATE TABLE IF NOT EXISTS emp
(
    eid     VARCHAR(20) PRIMARY KEY,                                 -- 員工編號(hào)
    ename   VARCHAR(20),                                             -- 員工名字
    age     INT,                                                     -- 員工年齡
    dept_id VARCHAR(20),                                             -- 員工所屬部門(mén)
    CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept (deptno) -- 外鍵約束
)

?? 結(jié)果: 外鍵約束創(chuàng)建成功，圖示表明 emp 表的 eid 受到主表 dept 的主鍵 deptno 外鍵約束。

?? 下面我們給已經(jīng)創(chuàng)建好的兩個(gè)表添加數(shù)據(jù)，用于 驗(yàn)證外鍵約束的作用， 相關(guān)代碼如下圖：

因此我們將最后一條更正為 ‘1004’，更正后的代碼如下：

-- 1. 添加主表數(shù)據(jù)
INSERT INTO dept
VALUES ('1001', '研發(fā)部'),
       ('1002', '銷(xiāo)售部'),
       ('1003', '財(cái)務(wù)部'),
       ('1004', '人事部');

-- 2. 添加從表數(shù)據(jù)
INSERT INTO emp
VALUES ('1', 'Nezuko627', 20, '1001'),
       ('2', 'Whisper', 19, '1001'),
       ('3', '大頭呢', 21, '1001'),
       ('4', '小牛馬', 21, '1002'),
       ('5', '小鵬', 25, '1002'),
       ('6', '幾何心涼', 25, '1002'),
       ('7', '草帽路飛', 17, '1003'),
       ('8', '娜美', 16, '1003'),
       ('9', '喬巴', 5, '1004');

創(chuàng)建好的兩張表如下圖：

?? 在表中刪除數(shù)據(jù):

主表中的數(shù)據(jù)被從表依賴時(shí)不能刪除， 比如，dept 表中的 deptno 中的所有數(shù)據(jù)都被 emp 表中的 dept_id 所依賴了，因此，dept 表中的數(shù)據(jù)不可刪除；

從表中是數(shù)據(jù)可以隨意刪除。 代碼及刪除后的表數(shù)據(jù)如下：

2.4 刪除外鍵約束

  當(dāng)一個(gè)表中不需要外鍵約束時(shí)，就需要從表中刪除。外鍵一旦刪除，就會(huì)解除和主表的關(guān)聯(lián)關(guān)系

?? 語(yǔ)法格式：

alter table <表名> drop foreign key <外鍵約束名>;

2.5 外鍵約束實(shí)操：多對(duì)多關(guān)系

  多對(duì)多關(guān)系相對(duì)來(lái)說(shuō)更加復(fù)雜，簡(jiǎn)單的來(lái)說(shuō)，就是需要：使用一張中間表作為從表來(lái)關(guān)聯(lián)其余的主表。 如下圖所示：

下面我們根據(jù)圖示來(lái)創(chuàng)建這三個(gè)表，并添加相應(yīng)的外鍵約束，相關(guān)代碼如下：

-- 1. 創(chuàng)建主表1 學(xué)生表 student
CREATE TABLE IF NOT EXISTS student
(
    sid  INT PRIMARY KEY AUTO_INCREMENT, -- 學(xué)號(hào)
    name VARCHAR(20),                    -- 姓名
    age  INT                             -- 年齡
);

-- 2. 創(chuàng)建主表2 課程表 course
CREATE TABLE IF NOT EXISTS course
(
    cid   INT PRIMARY KEY, -- 課程號(hào)
    cname VARCHAR(20)      -- 課程名
);

-- 3. 創(chuàng)建中間表 額外添加一個(gè)成績(jī)
CREATE TABLE IF NOT EXISTS score
(
    sid   INT,   -- 學(xué)號(hào)
    cid   INT,   -- 課程號(hào)
    score DOUBLE -- 成績(jī)
);

-- 4. 添加外鍵約束
ALTER TABLE score
    ADD FOREIGN KEY (sid) REFERENCES student (sid);

ALTER TABLE score
    ADD FOREIGN KEY (cid) REFERENCES course (cid);

-- 5. 添加數(shù)據(jù)
INSERT INTO student
VALUES (1, 'Whisper', 18),
       (2, '大頭呢', 19),
       (3, 'Nezuko', 20);

INSERT INTO course
VALUES (1, '程序設(shè)計(jì)'),
       (2, '大學(xué)英語(yǔ)'),
       (3, '高等數(shù)學(xué)');

INSERT INTO score
VALUES (1, 1, 98),
       (2, 2, 86),
       (2, 1, 95),
       (2, 3, 75),
       (3, 2, 99),
       (3, 3, 76);

?? 結(jié)果：

?? 在表中刪除數(shù)據(jù): 中間從表可以隨便刪除和修改，但是兩邊的主表受從表依賴的數(shù)據(jù)不能刪除或修改。

3 多表聯(lián)合查詢

3.1 聯(lián)合查詢的簡(jiǎn)介和分類(lèi)

?? 簡(jiǎn)介：

  多表查詢就是同時(shí)查詢兩個(gè)或兩個(gè)以上的表，主要用于展示一對(duì)多、多對(duì)多的數(shù)據(jù)，需要展示的數(shù)據(jù)來(lái)自于多張表。

?? 分類(lèi)概述：

3.2 聯(lián)合查詢數(shù)據(jù)準(zhǔn)備

  接下來(lái)準(zhǔn)備多表查詢需要的數(shù)據(jù)。需要注意的是， 外鍵約束只影響增刪，對(duì)多表查詢并無(wú)影響！ 數(shù)據(jù)準(zhǔn)備代碼如下：

-- 創(chuàng)建部門(mén)表
CREATE TABLE IF NOT EXISTS dept
(
    deptno VARCHAR(20) PRIMARY KEY, -- 部門(mén)號(hào)
    name   VARCHAR(20)              -- 部門(mén)名字
);

-- 創(chuàng)建員工表
CREATE TABLE IF NOT EXISTS emp
(
    eid     VARCHAR(20) PRIMARY KEY, -- 員工編號(hào)
    ename   VARCHAR(20),             -- 員工名字
    age     INT,                     -- 年齡
    dept_id varchar(20)              -- 部門(mén)號(hào)
)

-- 給部門(mén)表添加數(shù)據(jù)
INSERT INTO dept VALUES ('1001', '研發(fā)部');
INSERT INTO dept VALUES ('1002', '銷(xiāo)售部');
INSERT INTO dept VALUES ('1003', '財(cái)務(wù)部');
INSERT INTO dept VALUES ('1004', '人事部');

-- 給員工表添加數(shù)據(jù)
INSERT INTO emp VALUES ('1', '喬巴', 20, '1001');
INSERT INTO emp VALUES ('2', '路飛', 21, '1001');
INSERT INTO emp VALUES ('3', '禰豆子', 23, '1001');
INSERT INTO emp VALUES ('4', '漩渦鳴人', 18, '1001');
INSERT INTO emp VALUES ('5', '春野櫻', 85, '1002');
INSERT INTO emp VALUES ('6', '洛克李', 33, '1002');
INSERT INTO emp VALUES ('7', '皮卡丘', 50, '1002');
INSERT INTO emp VALUES ('8', '胖丁', 60, '1003');
INSERT INTO emp VALUES ('9', '喵喵', 58, '1003');
INSERT INTO emp VALUES ('10', '淼淼', 3, '1005');

?? 數(shù)據(jù)表如下：

3.3 交叉聯(lián)合查詢

?? 簡(jiǎn)介：

• 交叉聯(lián)合查詢返回被連接的兩個(gè)表所有數(shù)據(jù)行的 笛卡兒積；
• 笛卡爾積可以理解成一張表的每行去和另一張表的任意一行進(jìn)行匹配；
• 如果A表有m行數(shù)據(jù)，B表有n行數(shù)據(jù)，則返回m*n行數(shù)據(jù)；
• 笛卡爾積會(huì)產(chǎn)生很多冗余的數(shù)據(jù)， 可以通過(guò)其他查詢?cè)谠摷匣A(chǔ)上進(jìn)行條件篩選。

?? 語(yǔ)法：

select * from 表1, 表2, 表3...;

?? 實(shí)現(xiàn)：

SELECT * FROM emp, dept;

3.4 內(nèi)連接查詢

?? 簡(jiǎn)介： 內(nèi)連接查詢求 多張表的交集。 示意圖如下：

?? 語(yǔ)法：

-- 隱式內(nèi)連接（SQL92標(biāo)準(zhǔn)）
select * from A, B where 條件;

-- 顯式內(nèi)連接（SQL99標(biāo)準(zhǔn)）
select * from A inner join B on 條件;

?? 實(shí)現(xiàn)：

查詢每個(gè)部門(mén)的所屬員工

-- 隱式內(nèi)連接方式
SELECT * FROM dept, emp WHERE dept.deptno = emp.dept_id;
-- 顯示內(nèi)連接方式
SELECT * FROM dept INNER JOIN emp ON dept.deptno = emp.dept_id;

查詢研發(fā)部門(mén)的所屬員工

-- 隱式內(nèi)連接方式
SELECT * FROM dept, emp WHERE dept.deptno = emp.dept_id AND dept.name = '研發(fā)部';
-- 顯示內(nèi)連接方式
SELECT * FROM dept INNER JOIN emp ON dept.deptno = emp.dept_id AND dept.name = '研發(fā)部';

查詢每個(gè)部門(mén)的員工數(shù)，并升序排序

-- 隱式內(nèi)連接方式
SELECT dept.name, count(*) 'count'
FROM dept,
     emp
WHERE dept.deptno = emp.dept_id
GROUP BY dept.deptno
ORDER BY count ASC;

-- 顯示內(nèi)連接式
SELECT dept.name, count(*) 'count'
FROM dept
         INNER JOIN emp ON dept.deptno = emp.dept_id
GROUP BY dept.deptno
ORDER BY count ASC;

查詢?nèi)藬?shù)大于等于3的部門(mén)，并按照人數(shù)降序排序

-- 隱式內(nèi)連接方式
SELECT dept.name, count(emp.eid) count
FROM dept,
     emp
WHERE dept.deptno = emp.dept_id
GROUP BY dept.deptno
HAVING count >= 3
ORDER BY count DESC;

-- 顯式內(nèi)連接方式
SELECT dept.name, count(emp.eid) count
FROM dept
         JOIN
     emp
     ON dept.deptno = emp.dept_id
GROUP BY dept.deptno
HAVING count >= 3
ORDER BY count DESC;

3.5 外連接查詢

  外連接分為左外連接、右外連接、滿外連接。需要注意的是，在Oracle中有 full join，但是在 mysql 中對(duì) full join 支持不友好，可以使用 union 來(lái)達(dá)到目的。

通俗說(shuō)，在mysql中滿外連接可以通過(guò)求左外連接與右外連接的并集實(shí)現(xiàn)。

?? 來(lái)看幾個(gè)例子吧：

1?? 查詢哪些部門(mén)有員工，哪些部門(mén)沒(méi)有員工

分析： 使用左外連接，將左表所有數(shù)據(jù)輸出，右表對(duì)應(yīng)的數(shù)據(jù)輸出，沒(méi)有則補(bǔ) null。

SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.dept_id;

2?? 查詢哪些員工有對(duì)應(yīng)的部門(mén)，哪些沒(méi)有

分析： 使用右外連接，將右表所有數(shù)據(jù)輸出，左表對(duì)應(yīng)的數(shù)據(jù)輸出，沒(méi)有則補(bǔ) null。

SELECT * FROM dept RIGHT OUTER JOIN emp ON dept.deptno = emp.dept_id;

3?? 利用 union 關(guān)鍵字實(shí)現(xiàn)求左外連接和右外連接的并集

分析： 使用滿外連接，將左右表所有數(shù)據(jù)輸出，對(duì)應(yīng)位置沒(méi)有則均補(bǔ) null。

本質(zhì)： union 實(shí)質(zhì)上是將兩表之間合并并且去重。

SELECT *
FROM dept
         LEFT OUTER JOIN emp ON dept.deptno = emp.dept_id
UNION
SELECT *
FROM dept
         RIGHT OUTER JOIN emp ON dept.deptno = emp.dept_id;

3.6 子查詢

3.6.1 子查詢說(shuō)明與實(shí)操

?? 簡(jiǎn)介：

  子查詢就是 在一個(gè)完整的查詢語(yǔ)句中，嵌套若干個(gè)不同功能的小查詢， 從而一起完成復(fù)雜的查詢的一種編寫(xiě)方式

?? 子查詢的返回結(jié)果分為如下四種：

• 單行單列：一個(gè)具體列的內(nèi)容，可以理解為一個(gè)單值數(shù)據(jù)；
• 單行多列：返回一行數(shù)據(jù)中多個(gè)列的內(nèi)容；
• 多行單列：返回多行記錄之中同一列的內(nèi)容，相當(dāng)于給出了操作范圍；
• 多行多列：查詢返回的結(jié)果是一張臨時(shí)表。

1?? 查詢年齡最大的員工信息，顯示該員工的員工號(hào)、員工名字和年齡

分析： 利用子查詢返回 單行單列——即查詢最大年齡

SELECT eid, ename, age
FROM emp
WHERE age = (SELECT max(age) FROM emp);

2?? 查詢研發(fā)部和銷(xiāo)售部的員工信息，包含員工號(hào)、姓名

方式1： 利用關(guān)聯(lián)查詢方式，先連接兩張表后再根據(jù)條件進(jìn)行查詢

SELECT eid, ename
FROM dept
         JOIN emp ON dept.deptno = emp.dept_id AND (name in ('銷(xiāo)售部', '研發(fā)部'));

方式2： 利用子查詢返回 多行單列——即先查詢研發(fā)部與銷(xiāo)售部的部門(mén)號(hào)，然后再以此為條件查詢員工信息

SELECT eid, ename
FROM emp
WHERE dept_id IN (SELECT deptno FROM dept WHERE name IN ('銷(xiāo)售部', '研發(fā)部'));

3?? 查詢研發(fā)部30歲以下員工的所有信息（指員工表）

方式1： 利用關(guān)聯(lián)查詢方式，先連接兩張表后再根據(jù)條件進(jìn)行查詢

SELECT *
FROM dept
         JOIN emp ON dept.deptno = emp.dept_id AND (dept.name = '研發(fā)部' AND emp.age < 30);

方式2： 利用子查詢返回 多行多列——即先在部門(mén)表中查詢研發(fā)部信息，然后在員工表中查詢小于30歲的員工信息，最后將兩個(gè)查詢的結(jié)果進(jìn)行關(guān)聯(lián)查詢

SELECT *
FROM (SELECT *
      FROM dept
      WHERE name = '研發(fā)部') T1
         JOIN (SELECT *
               FROM emp
               WHERE age < 30) T2 ON T1.deptno = T2.dept_id;

3.6.2 子查詢中的關(guān)鍵字

3.6.2.1 ALL關(guān)鍵字

?? 語(yǔ)法格式:

SELECT ... FROM ... WHERE c > ALL(查詢語(yǔ)句);

?? 說(shuō)明：

1. ALL 與子查詢返回的所有值比較；
2. ALL 可以與 = > >= < <= <>結(jié)合使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的所有數(shù)據(jù)；

?? 操作：

?? 查詢年齡大于 1003 部門(mén)所有員工的員工信息

SELECT *
FROM emp
WHERE age > ALL (SELECT age FROM emp WHERE dept_id = '1003');

3.6.2.2 ANY 與 SOME關(guān)鍵字

?? 語(yǔ)法格式:

SELECT ... FROM ... WHERE c > ANY(查詢語(yǔ)句);

?? 說(shuō)明：

• ANY 與子查詢返回的所有值比較，與 ALL 不同的是，ANY 只需要滿足查詢語(yǔ)句中的任一值符合即可；
• ANY 可以與 = > >= < <= <>結(jié)合使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的任一數(shù)據(jù)；
• SOME 與 ANY作用一樣，可以理解成ANY的別名。

?? 操作：

?? 查詢年齡大于 1003 部門(mén)任一員工的員工信息

SELECT *
FROM emp
WHERE age > ANY (SELECT age FROM emp WHERE dept_id = '1003');

3.6.2.3 IN關(guān)鍵字

?? 語(yǔ)法格式:

SELECT ... FROM ... WHERE c IN(查詢語(yǔ)句);

?? 說(shuō)明：

1. IN 關(guān)鍵字，用于判斷某個(gè)記錄的值，是否在指定集合中；
2. 在 IN 關(guān)鍵字前加 NOT 可以取非。

?? 操作：

?? 查詢研發(fā)部和銷(xiāo)售部的員工信息

SELECT *
FROM emp
WHERE dept_id IN (SELECT deptno FROM dept WHERE name = '研發(fā)部' OR name = '銷(xiāo)售部');

3.6.2.4 EXISTS關(guān)鍵字

?? 語(yǔ)法格式:

SELECT ... FROM ... WHERE EXISTS(查詢語(yǔ)句);

?? 說(shuō)明：

1. 該子查詢?nèi)绻辽俜祷匾恍袛?shù)據(jù)，則該EXISTS()返回true，外層查詢執(zhí)行；
2. 該子查詢?nèi)绻麤](méi)有數(shù)據(jù)返回，則該EXISTS()返回false，外層查詢不執(zhí)行。

Tips：EXISTS關(guān)鍵字運(yùn)算效率比IN高，實(shí)際開(kāi)發(fā)中更推薦使用。

?? 操作：

?? 查詢公司是否有大于60歲的員工，有則輸出

SELECT *
FROM emp
WHERE EXISTS (SELECT * FROM emp WHERE age > 60);

是不是困惑？為什么查詢結(jié)果中有年齡小于60歲的呢？ 其實(shí) EXISTS 只是判斷子查詢中是否有返回?cái)?shù)據(jù)，在查詢中，判斷出有大于60歲的員工，因此 外層查詢實(shí)際上是查詢了所有員工的信息。

我們可以 通過(guò)取別名的方式，一條一條取數(shù)據(jù)，讓外查詢來(lái)決定條件是否成立，來(lái)達(dá)到只取年齡大于60歲的員工信息。

3.7 自關(guān)聯(lián)查詢

?? 簡(jiǎn)介：

  MySQL有時(shí)在信息的查詢時(shí)需要進(jìn)行對(duì)表自身進(jìn)行關(guān)聯(lián)查詢。即一張表和自己關(guān)聯(lián)，將一張表當(dāng)成多張表來(lái)使用。自相關(guān)查詢時(shí)必須給表取別名。

?? 語(yǔ)法：

-- 方式1
select 字段列表 from 表1 別名1, 表1 別名2 where 條件;

-- 方式2
select 字段列表 from 表1 別名1 [left] join 表1 別名2 on 條件;

?? 使用場(chǎng)景舉例：

比如員工表，員工中既有老板又有普通員工，這時(shí)候就可以通過(guò)自關(guān)聯(lián)查詢的方式，將員工表分成多個(gè)來(lái)進(jìn)行關(guān)聯(lián)查詢，查詢員工的老板是誰(shuí)… …

寫(xiě)在最后

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)多表操作通關(guān)的文章就介紹到這了,更多相關(guān)MySQL多表操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論