MySQL數(shù)據(jù)庫多表操作通關指南(外鍵約束和多表聯(lián)合查詢)
1 多表關系
?? 一對一關系
比如：一個人有一個身份證，一個身份證對應一個人；實現(xiàn)原則：在任一表中添加唯一外鍵，指向另一方主鍵；在實際開發(fā)中遇到一對一情況比較少，遇到一對一關系一般合并表。在下圖中，可以將兩個表根據(jù) id 合并：
?? 一對多/多對一關系
比如：一個部門有多個員工，一個員工只能對應一個部門；實現(xiàn)原則：在多的一方建立外鍵，指向另一方的主鍵， 示意圖如下：
?? 多對多關系
比如：一個學生可以修多個課程，一個課程也可以被多個學生選擇；實現(xiàn)原則：多對多關系實現(xiàn)需要借助第三張中間表。中間表至少包含兩個字段，將多對多的關系，折成一對多的關系，中間表至少要有兩個外鍵，這兩個外鍵分別指向原來兩個表的主鍵， 示意圖如下：
2 外鍵約束（FOREIGN KEY）
2.1 外鍵約束說明
?? 簡介：
MySQL 外鍵約束是表的一種特殊字段，經(jīng)常與主鍵約束一起使用。對于兩個具有關聯(lián)關系的表而言，相關聯(lián)字段中主鍵所在的表就是主表（父表），外鍵所在的表就是從表（子表）。
外鍵約束用來建立主表與從表的關聯(lián)關系，為兩個表的數(shù)據(jù)建立連接，約束兩個表中數(shù)據(jù)的一致性和完整性。
?? 舉例說明：
在下圖中，從表中的部門號受主表中的主鍵部門號的范圍限制， 即，從表中的部門號列只能取值為1001、1002或1003。
?? 定義外鍵的規(guī)則：
- 主表必須已經(jīng)存在于數(shù)據(jù)庫中，或者是當前正在創(chuàng)建的表（即在創(chuàng)建外鍵時，主表必須存在）；
- 必須為主表定義主鍵；
- 主鍵不能包含空值，但允許在外鍵中出現(xiàn)空值；
- 在主表的表名后面指定列名或列名的組合，這個列或者組合必須是主表的主鍵或候選鍵；
- 外鍵中列的數(shù)目必須和主表的主鍵中列的數(shù)目相同（通俗解釋：主鍵的列數(shù)必須與外鍵的列數(shù)相同）；
- 外鍵中列的數(shù)據(jù)類型必須和主表主鍵中對應列的數(shù)據(jù)類型相同。
2.2 外鍵約束的創(chuàng)建
?? 1.在創(chuàng)建表時設置外鍵約束
在 create table 語句中，使用 foreign key 關鍵字來指定外鍵。具體語法格式如下：
[constraint <外鍵名>] foreign key 字段名1 [, 字段名2, ...] references <主表名> 主鍵列1, [, 主鍵列2, ...]
?? 2.修改表時添加外鍵約束
這樣做的前提是：從表中外鍵的列中的數(shù)據(jù)必須與主鍵中主鍵列中的數(shù)據(jù)一致或沒有數(shù)據(jù)。 語法如下：
alter table <數(shù)據(jù)表名> add constraint <外鍵名> foreign key(<列名>) references <主表名> (<列名>);
2.3 外鍵約束實操：一對多關系
?? 下面使用下面這段代碼創(chuàng)建兩個表，dept 作主表存儲部門信息，emp 作從表存儲員工信息，主鍵約束與外鍵約束詳細見代碼注釋：
-- 創(chuàng)建部門表（主表）
CREATE TABLE IF NOT EXISTS dept
(
deptno VARCHAR(20) PRIMARY KEY, -- 部門號
NAME VARCHAR(20) -- 部門名字
);
-- 創(chuàng)建員工表（從表）
CREATE TABLE IF NOT EXISTS emp
(
eid VARCHAR(20) PRIMARY KEY, -- 員工編號
ename VARCHAR(20), -- 員工名字
age INT, -- 員工年齡
dept_id VARCHAR(20), -- 員工所屬部門
CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept (deptno) -- 外鍵約束
)
?? 結(jié)果: 外鍵約束創(chuàng)建成功，圖示表明 emp 表的 eid 受到主表 dept 的主鍵 deptno 外鍵約束。
?? 下面我們給已經(jīng)創(chuàng)建好的兩個表添加數(shù)據(jù)，用于 驗證外鍵約束的作用， 相關代碼如下圖：
因此我們將最后一條更正為 ‘1004’，更正后的代碼如下：
-- 1. 添加主表數(shù)據(jù)
INSERT INTO dept
VALUES ('1001', '研發(fā)部'),
('1002', '銷售部'),
('1003', '財務部'),
('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ù)被從表依賴時不能刪除， 比如，dept 表中的 deptno 中的所有數(shù)據(jù)都被 emp 表中的 dept_id 所依賴了，因此，dept 表中的數(shù)據(jù)不可刪除；
從表中是數(shù)據(jù)可以隨意刪除。 代碼及刪除后的表數(shù)據(jù)如下：
2.4 刪除外鍵約束
當一個表中不需要外鍵約束時，就需要從表中刪除。外鍵一旦刪除，就會解除和主表的關聯(lián)關系
?? 語法格式：
alter table <表名> drop foreign key <外鍵約束名>;
2.5 外鍵約束實操：多對多關系
多對多關系相對來說更加復雜，簡單的來說，就是需要：使用一張中間表作為從表來關聯(lián)其余的主表。 如下圖所示：
下面我們根據(jù)圖示來創(chuàng)建這三個表，并添加相應的外鍵約束，相關代碼如下：
-- 1. 創(chuàng)建主表1 學生表 student
CREATE TABLE IF NOT EXISTS student
(
sid INT PRIMARY KEY AUTO_INCREMENT, -- 學號
name VARCHAR(20), -- 姓名
age INT -- 年齡
);
-- 2. 創(chuàng)建主表2 課程表 course
CREATE TABLE IF NOT EXISTS course
(
cid INT PRIMARY KEY, -- 課程號
cname VARCHAR(20) -- 課程名
);
-- 3. 創(chuàng)建中間表 額外添加一個成績
CREATE TABLE IF NOT EXISTS score
(
sid INT, -- 學號
cid INT, -- 課程號
score DOUBLE -- 成績
);
-- 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, '程序設計'),
(2, '大學英語'),
(3, '高等數(shù)學');
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)合查詢的簡介和分類
?? 簡介：
多表查詢就是同時查詢兩個或兩個以上的表，主要用于展示一對多、多對多的數(shù)據(jù)，需要展示的數(shù)據(jù)來自于多張表。
?? 分類概述：
3.2 聯(lián)合查詢數(shù)據(jù)準備
接下來準備多表查詢需要的數(shù)據(jù)。需要注意的是， 外鍵約束只影響增刪，對多表查詢并無影響！ 數(shù)據(jù)準備代碼如下：
-- 創(chuàng)建部門表
CREATE TABLE IF NOT EXISTS dept
(
deptno VARCHAR(20) PRIMARY KEY, -- 部門號
name VARCHAR(20) -- 部門名字
);
-- 創(chuàng)建員工表
CREATE TABLE IF NOT EXISTS emp
(
eid VARCHAR(20) PRIMARY KEY, -- 員工編號
ename VARCHAR(20), -- 員工名字
age INT, -- 年齡
dept_id varchar(20) -- 部門號
)
-- 給部門表添加數(shù)據(jù)
INSERT INTO dept VALUES ('1001', '研發(fā)部');
INSERT INTO dept VALUES ('1002', '銷售部');
INSERT INTO dept VALUES ('1003', '財務部');
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)合查詢
?? 簡介：
- 交叉聯(lián)合查詢返回被連接的兩個表所有數(shù)據(jù)行的 笛卡兒積；
- 笛卡爾積可以理解成一張表的每行去和另一張表的任意一行進行匹配；
- 如果A表有m行數(shù)據(jù)，B表有n行數(shù)據(jù)，則返回m*n行數(shù)據(jù)；
- 笛卡爾積會產(chǎn)生很多冗余的數(shù)據(jù)， 可以通過其他查詢在該集合基礎上進行條件篩選。
?? 語法：
select * from 表1, 表2, 表3...;
?? 實現(xiàn)：
SELECT * FROM emp, dept;
3.4 內(nèi)連接查詢
?? 簡介： 內(nèi)連接查詢求 多張表的交集。 示意圖如下：
?? 語法：
-- 隱式內(nèi)連接（SQL92標準） select * from A, B where 條件; -- 顯式內(nèi)連接（SQL99標準） select * from A inner join B on 條件;
?? 實現(xià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ā)部門的所屬員工
-- 隱式內(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ā)部';
查詢每個部門的員工數(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的部門，并按照人數(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 中對 full join 支持不友好，可以使用 union 來達到目的。
通俗說，在mysql中滿外連接可以通過求左外連接與右外連接的并集實現(xiàn)。
?? 來看幾個例子吧：
1?? 查詢哪些部門有員工，哪些部門沒有員工
分析： 使用左外連接，將左表所有數(shù)據(jù)輸出，右表對應的數(shù)據(jù)輸出，沒有則補 null。
SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.dept_id;
2?? 查詢哪些員工有對應的部門，哪些沒有
分析： 使用右外連接，將右表所有數(shù)據(jù)輸出，左表對應的數(shù)據(jù)輸出，沒有則補 null。
SELECT * FROM dept RIGHT OUTER JOIN emp ON dept.deptno = emp.dept_id;
3?? 利用 union 關鍵字實現(xiàn)求左外連接和右外連接的并集
分析： 使用滿外連接，將左右表所有數(shù)據(jù)輸出，對應位置沒有則均補 null。
本質(zhì)： union 實質(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 子查詢說明與實操
?? 簡介：
子查詢就是 在一個完整的查詢語句中，嵌套若干個不同功能的小查詢， 從而一起完成復雜的查詢的一種編寫方式
?? 子查詢的返回結(jié)果分為如下四種：
- 單行單列：一個具體列的內(nèi)容，可以理解為一個單值數(shù)據(jù)；
- 單行多列：返回一行數(shù)據(jù)中多個列的內(nèi)容；
- 多行單列：返回多行記錄之中同一列的內(nèi)容，相當于給出了操作范圍；
- 多行多列：查詢返回的結(jié)果是一張臨時表。
1?? 查詢年齡最大的員工信息，顯示該員工的員工號、員工名字和年齡
分析： 利用子查詢返回 單行單列——即查詢最大年齡
SELECT eid, ename, age FROM emp WHERE age = (SELECT max(age) FROM emp);
2?? 查詢研發(fā)部和銷售部的員工信息，包含員工號、姓名
方式1： 利用關聯(lián)查詢方式，先連接兩張表后再根據(jù)條件進行查詢
SELECT eid, ename
FROM dept
JOIN emp ON dept.deptno = emp.dept_id AND (name in ('銷售部', '研發(fā)部'));
方式2： 利用子查詢返回 多行單列——即先查詢研發(fā)部與銷售部的部門號，然后再以此為條件查詢員工信息
SELECT eid, ename
FROM emp
WHERE dept_id IN (SELECT deptno FROM dept WHERE name IN ('銷售部', '研發(fā)部'));
3?? 查詢研發(fā)部30歲以下員工的所有信息（指員工表）
方式1： 利用關聯(lián)查詢方式，先連接兩張表后再根據(jù)條件進行查詢
SELECT *
FROM dept
JOIN emp ON dept.deptno = emp.dept_id AND (dept.name = '研發(fā)部' AND emp.age < 30);
方式2： 利用子查詢返回 多行多列——即先在部門表中查詢研發(fā)部信息，然后在員工表中查詢小于30歲的員工信息，最后將兩個查詢的結(jié)果進行關聯(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 子查詢中的關鍵字
3.6.2.1 ALL關鍵字
?? 語法格式:
SELECT ... FROM ... WHERE c > ALL(查詢語句);
?? 說明：
- ALL 與子查詢返回的所有值比較；
- ALL 可以與 = > >= < <= <>結(jié)合使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的所有數(shù)據(jù)；
?? 操作：
?? 查詢年齡大于 1003 部門所有員工的員工信息
SELECT * FROM emp WHERE age > ALL (SELECT age FROM emp WHERE dept_id = '1003');
3.6.2.2 ANY 與 SOME關鍵字
?? 語法格式:
SELECT ... FROM ... WHERE c > ANY(查詢語句);
?? 說明：
- ANY 與子查詢返回的所有值比較，與 ALL 不同的是，ANY 只需要滿足查詢語句中的任一值符合即可；
- ANY 可以與 = > >= < <= <>結(jié)合使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的任一數(shù)據(jù)；
- SOME 與 ANY作用一樣，可以理解成ANY的別名。
?? 操作：
?? 查詢年齡大于 1003 部門任一員工的員工信息
SELECT * FROM emp WHERE age > ANY (SELECT age FROM emp WHERE dept_id = '1003');
3.6.2.3 IN關鍵字
?? 語法格式:
SELECT ... FROM ... WHERE c IN(查詢語句);
?? 說明：
- IN 關鍵字，用于判斷某個記錄的值，是否在指定集合中；
- 在 IN 關鍵字前加 NOT 可以取非。
?? 操作：
?? 查詢研發(fā)部和銷售部的員工信息
SELECT * FROM emp WHERE dept_id IN (SELECT deptno FROM dept WHERE name = '研發(fā)部' OR name = '銷售部');
3.6.2.4 EXISTS關鍵字
?? 語法格式:
SELECT ... FROM ... WHERE EXISTS(查詢語句);
?? 說明：
- 該子查詢?nèi)绻辽俜祷匾恍袛?shù)據(jù)，則該EXISTS()返回true，外層查詢執(zhí)行；
- 該子查詢?nèi)绻麤]有數(shù)據(jù)返回，則該EXISTS()返回false，外層查詢不執(zhí)行。
Tips：EXISTS關鍵字運算效率比IN高，實際開發(fā)中更推薦使用。
?? 操作：
?? 查詢公司是否有大于60歲的員工，有則輸出
SELECT * FROM emp WHERE EXISTS (SELECT * FROM emp WHERE age > 60);
是不是困惑？為什么查詢結(jié)果中有年齡小于60歲的呢？ 其實 EXISTS 只是判斷子查詢中是否有返回數(shù)據(jù)，在查詢中，判斷出有大于60歲的員工，因此 外層查詢實際上是查詢了所有員工的信息。
我們可以 通過取別名的方式，一條一條取數(shù)據(jù)，讓外查詢來決定條件是否成立，來達到只取年齡大于60歲的員工信息。
3.7 自關聯(lián)查詢
?? 簡介：
MySQL有時在信息的查詢時需要進行對表自身進行關聯(lián)查詢。即一張表和自己關聯(lián)，將一張表當成多張表來使用。自相關查詢時必須給表取別名。
?? 語法：
-- 方式1 select 字段列表 from 表1 別名1, 表1 別名2 where 條件; -- 方式2 select 字段列表 from 表1 別名1 [left] join 表1 別名2 on 條件;
?? 使用場景舉例：
比如員工表，員工中既有老板又有普通員工，這時候就可以通過自關聯(lián)查詢的方式，將員工表分成多個來進行關聯(lián)查詢，查詢員工的老板是誰… …
寫在最后
到此這篇關于MySQL數(shù)據(jù)庫多表操作通關的文章就介紹到這了,更多相關MySQL多表操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mysql數(shù)據(jù)NULL避坑指南,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
這篇文章主要介紹了解決mysql 默認排序規(guī)則的坑，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看不看2021-02-02
今天小編就為大家分享一篇關于優(yōu)化InnoDB表BLOB,TEXT列的存儲效率，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
pymysql.err.DataError:(1264, ")異常的有效解決方法(最新推薦)
遇到pymysql.err.DataError錯誤時,錯誤代碼1264通常指的是MySQL數(shù)據(jù)庫中的Out of range value for column錯誤,這意味著你嘗試插入或更新的數(shù)據(jù)超過了對應數(shù)據(jù)庫列所允許的范圍,這篇文章主要介紹了pymysql.err.DataError:(1264, ")異常的有效問題,需要的朋友可以參考下2024-05-05
這篇文章主要介紹了mysql 定時任務的實現(xiàn)與使用方法,結(jié)合實例形式分析了MySQL定時任務的相關原理、創(chuàng)建及使用方法,需要的朋友可以參考下2019-11-11
詳解mysql中字符串轉(zhuǎn)為數(shù)字的三種方法
這篇文章主要為大家詳細介紹了mysql中字符串轉(zhuǎn)為數(shù)字的三種常用方法,文中的示例代碼講解詳細,具有一定的借鑒價值,感興趣的小伙伴可以跟隨小編一起學習一下2023-11-11
這篇文章主要介紹MySQL時間類型和模式 MySQL會在存儲時將數(shù)據(jù)值轉(zhuǎn)換為UTC標準時間來存儲，讀取時再轉(zhuǎn)為當前時間。如果你的時區(qū)沒有發(fā)生改變，則該值就是你存儲的值，如果你改變了時區(qū)，讀取到的值就會發(fā)生變化。這個特性不會對DATETIME生效,需要的朋友可以參考一下2021-09-09
本節(jié)主要介紹了mysql如何增加修改字段類型及刪除字段類型,需要的朋友可以參考下2014-07-07
MySQL中查詢?nèi)罩九c慢查詢?nèi)罩镜幕緦W習教程
這篇文章主要介紹了MySQL中查詢?nèi)罩九c慢查詢?nèi)罩镜幕緦W習教程,文中還提到了MySQL自帶的Mysqldumpslow日志分析工具的使用,需要的朋友可以參考下2015-12-12
本文主要介紹了MySQL跨服務器關聯(lián)查詢的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01