MySQL使用連接查詢（JOIN）是為了從多個相關表中獲取數據。連接查詢是一種強大且常用的操作，可以根據某些條件將兩張或多張表中的數據組合在一起，返回一個聯合結果集。

1.為什么使用連接查詢

1. 數據規范化：

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

2. 提高查詢效率：

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

3. 復雜查詢需求：

   • 在實際應用中，很多查詢需求都涉及多個表的數據。例如，報表生成、數據分析、統計等都需要從多個表中提取相關數據。
   • 連接查詢可以實現這些復雜的查詢需求，通過合并相關表的數據來滿足業務邏輯。

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)：

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

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

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

RIGHT JOIN (或 RIGHT OUTER JOIN)：

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

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

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

FULL JOIN (或 FULL OUTER JOIN)：

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

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.測試連接查詢

1. student 表：

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

1. course 表：

• 存儲課程的基本信息。
• cid：課程的唯一標識（主鍵），自動遞增。
• cname：課程名稱。
• credit：課程學分。

1. exame 表：

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

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));

插入一些數據

-- 插入數據到 student 表
INSERT INTO student (name, age, sex) VALUES 
('Alice', 20, 'W'),
('Bob', 21, 'M'),
('Charlie', 22, 'M'),
('David', 20, 'M'),
('Eve', 21, 'W');-- 插入數據到 course 表
INSERT INTO course (cname, credit) VALUES 
('Mathematics', 3),
('Physics', 4),
('Chemistry', 3),
('Biology', 2),
('Computer Science', 3);-- 插入數據到 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);

?

?

?

?內連接

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.屬性名 滿足某些條件;

預置條件：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 區分大表 和 小表，按照數據量來區分，小表永遠是整表掃描，然后去大表搜索 // 從student小表中取出所有的a.uid，然后拿著這些uid去exame大表中搜索 // 對于inner join內連接，過濾條件寫在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這邊的表所有的數據顯示出來，在右表中不存在相應數據，則顯示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;

內連接結果如下

左連接結果如下

?問題，為啥左連接沒有把左表全部信息顯示，左連接和內連接結果一樣

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

原因分析：

先用b.cid把b表過濾下

?這個內連接和外連接毫無區別，是一樣的。所以外連接要把過濾條件寫到on中

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

右連接查詢

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

// 把right這邊的表所有的數據顯示出來，在左表中不存在相應數據，則顯示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-》會產生一張中間表存儲結果供外面的sql來查詢

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