1. 連接操作的基本概念

連接的本質(zhì)

連接操作的核心是將兩個或多個表中的數(shù)據(jù)基于某種關(guān)聯(lián)條件組合在一起，形成一個新的結(jié)果集。

2. 各種連接類型的詳細(xì)解析

2.1 INNER JOIN（內(nèi)連接）- 只取交集

將兩表作為參考對象，根據(jù)on后給出的兩表的條件將兩表連接起來。結(jié)果則是兩表同時滿足on后的條件的部分才會列出。

實際應(yīng)用場景：

• 查詢有部門的員工信息

• 查詢已下單的客戶信息

• 查詢有成績的學(xué)生信息

2.2 LEFT JOIN（左連接）- 保留左表全部

左連接以左表為基準(zhǔn)，根據(jù)ON后給出的兩表的條件將兩表連接起來。結(jié)果會將左表所有的查詢信息列出，而右表只列出ON后條件與左表滿足的部分。如果右表沒有匹配的記錄，則右表字段顯示為NULL。左連接全稱為左外連接，是外連接的一種。

關(guān)鍵特點：

• 左表的每一條記錄都會出現(xiàn)在結(jié)果中

• 右表可能有多條記錄匹配左表的一條記錄（一對多關(guān)系）

• 右表不匹配時，相關(guān)字段填充NULL

示例:

力扣175題，報告 Person 表中每個人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，則報告為 null 。以 任意順序返回結(jié)果表。

SELECT 
      Person.lastName,Person.firstName,Address.city,Address.state
FROM 
      Person
LEFT JOIN 
      Address 
ON 
      Person.personId = Address.personId;
 

實際應(yīng)用場景：

• 查詢所有員工信息（包括沒有部門的員工）

• 統(tǒng)計所有產(chǎn)品的銷售情況（包括未銷售的產(chǎn)品）

• 查詢所有客戶的訂單信息（包括未下單的客戶）

2.3 RIGHT JOIN（右連接）- 保留右表全部

右連接以右表為基準(zhǔn)，根據(jù)ON后給出的兩表的條件將兩表連接起來。結(jié)果會將右表所有的查詢信息列出，而左表只列出ON后條件與右表滿足的部分。如果左表沒有匹配的記錄，則左表字段顯示為NULL。右連接全稱為右外連接，是外連接的一種。

實際應(yīng)用場景：

• 查詢所有部門信息（包括沒有員工的部門）

• 統(tǒng)計所有類別的商品（包括沒有商品的類別）

• 查詢所有供應(yīng)商的供貨情況（包括未供貨的供應(yīng)商）

2.4 FULL JOIN（全連接）

全連接返回兩個表的所有記錄，相當(dāng)于左連接和右連接的并集。MySQL不直接支持FULL JOIN，但可以通過UNION組合LEFT JOIN和RIGHT JOIN來實現(xiàn)。

實際應(yīng)用場景：

• 需要同時查看兩個表的完整信息

• 數(shù)據(jù)對比分析

• 數(shù)據(jù)完整性檢查

2.5 CROSS JOIN（交叉連接）

交叉連接返回兩個表的笛卡爾積，即左表的每一行與右表的每一行組合。

示例：

-- 創(chuàng)建示例表
CREATE TABLE colors (
    color_name VARCHAR(20)
);

CREATE TABLE sizes (
    size_name VARCHAR(20)
);

-- 交叉連接：生成所有顏色和尺寸的組合
SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;

2.6  NATURAL JOIN （自然連接）

自然連接自動根據(jù)兩個表中相同名稱的列進(jìn)行連接，不需要指定連接條件。

示例：

-- 假設(shè)兩個表都有相同的列名 id 和 name
SELECT *
FROM table1
NATURAL JOIN table2;

2.7 多表連接

同時連接三個或更多表。

-- 連接三個表：訂單、客戶、產(chǎn)品
SELECT o.order_id, c.customer_name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

2.8 使用 USING 子句

當(dāng)連接的兩個表有相同名稱的列時，可以使用 USING 簡化語法。

SELECT 列名
FROM 表1
JOIN 表2 USING (共同列名);

示例：

-- 使用 USING 替代 ON
SELECT e.name, d.name
FROM employees e
JOIN departments d USING (department_id);

2.9 不等值連接

使用非等值條件進(jìn)行連接。

-- 創(chuàng)建工資等級表
CREATE TABLE salary_grades (
    grade VARCHAR(10),
    min_salary INT,
    max_salary INT
);

-- 不等值連接：確定每個員工的工資等級
SELECT e.name, e.salary, sg.grade
FROM employees e
JOIN salary_grades sg ON e.salary BETWEEN sg.min_salary AND sg.max_salary;

3. 力扣題目練習(xí)

3.1 找出連續(xù)出現(xiàn)的數(shù)字（第180題）

題目描述：

        找出所有至少連續(xù)出現(xiàn)三次的數(shù)字。

        返回的結(jié)果表中的數(shù)據(jù)可以按 任意順序 排列。

        結(jié)果格式如下面的例子所示：

-- 使用自連接
SELECT DISTINCT l1.num AS ConsecutiveNums
FROM  Logs l1
join Logs l2 on l1.id = l2.id -1 AND l1.num = l2.num
join Logs l3 on l1.id = l3.id -2 AND l1.num = l3.num

3.2 超過經(jīng)理收入的員工（第181題）

題目描述：

編寫解決方案，找出收入比經(jīng)理高的員工。以 任意順序 返回結(jié)果表。結(jié)果格式如下所示。

SELECT a.name AS Employee
FROM Employee a,Employee b
WHERE a.managerId = b.id
AND a.salary > b.salary

3.3 上升的溫度（第197題）

題目描述：

編寫解決方案，找出與之前（昨天的）日期相比溫度更高的所有日期的 id 。

返回結(jié)果 無順序要求 。

SELECT b.id
FROM Weather a,Weather b
WHERE a.Temperature < b.Temperature AND DATEDIFF (a.RecordDate,b.RecordDate) = -1

3.4  部門工資前三高的所有員工(第185題)

題目描述：

公司的主管們感興趣的是公司每個部門中誰賺的錢最多。一個部門的 高收入者 是指一個員工的工資在該部門的 不同 工資中 排名前三 。

編寫解決方案，找出每個部門中 收入高的員工 。

以 任意順序 返回結(jié)果表。

返回結(jié)果格式如下所示。

使用 JOIN 和子查詢，公司里前 3 高的薪水意味著有不超過 3 個工資比這些值大。

SELECT d.name as Department,e1.name as Employee,e1.salary AS Salary
FROM Employee e1
join Department d on  e1.departmentId = d.id
WHERE 3 >(
    SELECT count(DISTINCT e2.Salary)
    FROM Employee e2
    WHERE e2.salary > e1.salary
    AND e1.departmentId =  e2.departmentId
)

3.5 體育館的人流量（第601題）

題目描述：

編寫解決方案找出每行的人數(shù)大于或等于 100 且 id 連續(xù)的三行或更多行記錄。

返回按 visit_date 升序排列 的結(jié)果表。

查詢結(jié)果格式如下所示。

SELECT distinct s1.*
FROM Stadium s1, Stadium s2, Stadium s3
where(
    (s1.id = s2.id-1 AND s1.id = s3.id-2) or
    (s1.id = s2.id-1 AND s1.id = s3.id+1) or
    (s1.id = s2.id+1 AND s1.id = s3.id+2 )
)
AND s1.people >= 100
AND s2.people >= 100
AND s3.people >= 100
ORDER BY s1.visit_date;

通過自連接把Stadium表當(dāng)成三條獨立記錄s1、s2、s3，讓它們按id相鄰排成“三連號”，從而把任何連續(xù)三條記錄都拉出來，再統(tǒng)一過濾這三條記錄的people都不低于100，最后去重并按s1.visit_date排序，得到的就是“人數(shù)連續(xù)三天及以上破百”的那些天里，所有滿足條件的日期記錄。

總結(jié)

到此這篇關(guān)于MySQL的多種連接方式的文章就介紹到這了,更多相關(guān)MySQL多種連接方式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論