快捷導(dǎo)航

MySQL中的連接查詢使用方式

更新時間：2025年11月20日 09:27:32 作者：Afison

文章介紹了MySQL中的連接查詢（JOIN）,包括INNER?JOIN、LEFT?JOIN、RIGHT?JOIN和FULL?JOIN（通過UNION實(shí)現(xiàn)）,通過示例和解釋,文章說明了連接查詢在數(shù)據(jù)規(guī)范化、提高查詢效率和滿足復(fù)雜查詢需求方面的應(yīng)用

1.為什么使用連接查詢

數(shù)據(jù)規(guī)范化：

數(shù)據(jù)庫設(shè)計時通常會將數(shù)據(jù)拆分到不同的表中，以減少數(shù)據(jù)冗余和提高數(shù)據(jù)一致性。這種方法稱為規(guī)范化。
例如，將用戶信息存儲在一個表中，將訂單信息存儲在另一個表中。這時，如果你需要獲取某用戶的訂單信息，就需要使用連接查詢將這兩個表的數(shù)據(jù)組合在一起。

提高查詢效率：

通過使用連接查詢，可以減少重復(fù)的數(shù)據(jù)存儲，優(yōu)化數(shù)據(jù)的管理和查詢效率。
比如，一個表中存儲用戶ID，另一個表中存儲用戶詳細(xì)信息，查詢時只需要連接用戶ID和詳細(xì)信息表即可獲取完整信息，而不需要在單個表中存儲冗余數(shù)據(jù)。

復(fù)雜查詢需求：

在實(shí)際應(yīng)用中，很多查詢需求都涉及多個表的數(shù)據(jù)。例如，報表生成、數(shù)據(jù)分析、統(tǒng)計等都需要從多個表中提取相關(guān)數(shù)據(jù)。
連接查詢可以實(shí)現(xiàn)這些復(fù)雜的查詢需求，通過合并相關(guān)表的數(shù)據(jù)來滿足業(yè)務(wù)邏輯。

2.主要類型的連接查詢

INNER JOIN

僅返回兩個表中滿足連接條件的記錄。

SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

說明：這將返回所有用戶和他們的訂單，前提是用戶和訂單在兩個表中都有匹配。

LEFT JOIN (或 LEFT OUTER JOIN)

返回左表中的所有記錄，即使右表中沒有匹配的記錄。如果右表沒有匹配，則結(jié)果為NULL。

SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

說明：這將返回所有用戶以及他們的訂單。如果某個用戶沒有訂單，則相應(yīng)的訂單ID將為NULL。

RIGHT JOIN (或 RIGHT OUTER JOIN)

返回右表中的所有記錄，即使左表中沒有匹配的記錄。如果左表沒有匹配，則結(jié)果為NULL。

SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
users. Username

說明：這將返回所有訂單以及對應(yīng)的用戶。如果某個訂單沒有匹配的用戶，則相應(yīng)的用戶名將為NULL。

FULL JOIN (或 FULL OUTER JOIN)

返回左表和右表中的所有記錄，如果沒有匹配則返回NULL。MySQL不直接支持FULL JOIN，可以通過UNION實(shí)現(xiàn)。

SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
UNION
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
users. Username

3.測試連接查詢

student 表：

存儲學(xué)生的基本信息。
uid：學(xué)生的唯一標(biāo)識（主鍵），自動遞增。
name：學(xué)生姓名。
age：學(xué)生年齡。
sex：學(xué)生性別，使用枚舉類型，值可以是'M'或'W'。

course 表：

存儲課程的基本信息。
cid：課程的唯一標(biāo)識（主鍵），自動遞增。
cname：課程名稱。
credit：課程學(xué)分。

exame 表：

存儲考試信息，包括學(xué)生、課程和考試成績。
uid：學(xué)生ID，對應(yīng)student表中的uid。
cid：課程ID，對應(yīng)course表中的cid。
time：考試時間。
score：考試成績。
PRIMARY KEY(uid,cid)：聯(lián)合主鍵，確保每個學(xué)生在每門課程中只有一個成績記錄。

CREATE TABLE student(
uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED NOT NULL,
sex ENUM('M','W') NOT NULL);

CREATE TABLE course(
cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
cname VARCHAR(50) NOT NULL,
credit TINYINT UNSIGNED NOT NULL);

CREATE TABLE exame(
uid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
time DATE NOT NULL,
score FLOAT NOT NULL,
PRIMARY KEY(uid,cid));

插入一些數(shù)據(jù)

-- 插入數(shù)據(jù)到 student 表
INSERT INTO student (name, age, sex) VALUES 
('Alice', 20, 'W'),
('Bob', 21, 'M'),
('Charlie', 22, 'M'),
('David', 20, 'M'),
('Eve', 21, 'W');

-- 插入數(shù)據(jù)到 course 表
INSERT INTO course (cname, credit) VALUES 
('Mathematics', 3),
('Physics', 4),
('Chemistry', 3),
('Biology', 2),
('Computer Science', 3);

-- 插入數(shù)據(jù)到 exame 表
INSERT INTO exame (uid, cid, time, score) VALUES 
(1, 1, '2024-01-10', 85.5),
(1, 2, '2024-01-11', 90.0),
(2, 1, '2024-01-9', 88.0),
(2, 3, '2024-01-10', 92.5),
(3, 4, '2024-01-12', 75.0),
(3, 5, '2024-01-9', 80.0),
(4, 2, '2024-01-12', 85.0),
(4, 3, '2024-01-12', 87.5),
(5, 1, '2024-01-11', 89.0),
(5, 5, '2024-01-11', 95.0);

內(nèi)連接

SELECT a.屬性名1,a.屬性名2,...,b,屬性名1,b.屬性名2... FROM table_name1 a inner join table_name2 b on a.id = b.id where a.屬性名滿足某些條件;

預(yù)置條件：uid:1 cid:2
select score from exame where uid=1 and cid=2;
select a.uid,a.name,a.age,a.sex from student a where a.uid=1;
select c.score from exame c where c.uid=1 and c.cid=2;

連接兩張表查詢

// on a.uid=c.uid 區(qū)分大表和小表，按照數(shù)據(jù)量來區(qū)分，小表永遠(yuǎn)是整表掃描，然后去大表搜索 // 從student小表中取出所有的a.uid，然后拿著這些uid去exame大表中搜索 // 對于inner join內(nèi)連接，過濾條件寫在where的后面和on連接條件里面，效果是一樣的

select a.uid,a.name,a.age,a.sex,c.score from student a
inner join exame c on a.uid=c.uid where c.uid=1 and c.cid=2;

select b.cid,b.cname,b.credit from course b where b.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.uid=1 and c.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.cid=2 and c.score>=90.0;

select b.cid,b.cname,b.credit,count(*)
from exame c
inner join course b on c.cid=b.cid
where c.score>=90.0
group by c.cid
having c.cid=2;//分組以后的過濾

select b.cid,b.cname,b.credit,count(*) cnt
from exame c
inner join course b on c.cid=b.cid
where c.score>=90.0
group by c.cid
order by cnt;

外連接查詢

左連接查詢

SELECT a.屬性名列表, b.屬性名列表 FROM table_name1 a LEFT [OUTER] JOIN table_name2 b on a.id = b.id;

// 把left這邊的表所有的數(shù)據(jù)顯示出來，在右表中不存在相應(yīng)數(shù)據(jù)，則顯示NULL
select a.* from User a left outer join Orderlist b on a.uid=b.uid where
a.orderid is null;

例子：

select a.*,b.* from student a left join exame b on a.uid=b.uid;

找出沒有考過試的

select a.*,b.* from student a left join exame b on a.uid=b.uid where b.cid is null;

內(nèi)連接結(jié)果如下

左連接結(jié)果如下

問題，為啥左連接沒有把左表全部信息顯示，左連接和內(nèi)連接結(jié)果一樣

若把where條件放到連接條件on后面

原因分析：

先用b.cid把b表過濾下

這個內(nèi)連接和外連接毫無區(qū)別，是一樣的。所以外連接要把過濾條件寫到on中

外連接查不存在的場景，還帶有一定的限制條件，限制條件加到on的連接條件后面，where的過濾條件后面寫null判空。

右連接查詢

SELECT a.屬性名列表, b.屬性名列表 FROM table_name1 a LEFT [OUTER] JOIN table_name2 b on a.id = b.id;

// 把right這邊的表所有的數(shù)據(jù)顯示出來，在左表中不存在相應(yīng)數(shù)據(jù)，則顯示NULL
select a.* from User a right outer join Orderlist b on a.uid=b.uid where
b.orderid is null;

select * from student where uid not in (select distinct uid from exame);

select distinct uid from exame-》會產(chǎn)生一張中間表存儲結(jié)果供外面的sql來查詢

not in 對于索引的命中并不高

總結(jié)

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线