天天看點
傳回

SQL語句（四）聯表查詢

目錄

  • 一、關聯查詢的分類
    • 按年代分
    • 按功能分
  • 二、sql92文法的連接配接
    • 文法
    • 1. 簡單應用
    • 2. 為表起别名
    • 3. 加入篩選
    • 4. 加入分組
    • 5. 三表連接配接
    • 6. 非等值連接配接
    • 7. 自連接配接
  • 三、sql99文法的連接配接
    • 連接配接類型分類
    • 1. 内連接配接（INNER JOIN）
    • 2. 外連接配接
    • 3. 交叉連接配接

sql92

:僅僅支援内連接配接

sql99

【推薦】:支援

内連接配接

+

外連接配接（左外，右外）

交叉連接配接

  • 内連接配接
    • 等值連接配接
    • 非等值連接配接
    • 自連接配接
  • 外連接配接
    • 左外連接配接
    • 右外連接配接
    • 全外連接配接
  • 交叉連接配接 

SELECT	查詢清單
FROM 	待連結的多個表
WHERE 	連接配接條件 [和篩選條件]

這裡的連接配接條件寫字段相等關系，如

e.department_id = d.id

等

查詢

員工名

和對應的

部門名

 
SELECT 	last_name 員工名,department_name 部門名
FROM 	employees, departments
WHERE 	employees.department_id=departments.department_id;
# 連接配接條件為employees.department_id=departments.department_id
SQL語句（四）聯表查詢

SELECT 	查詢清單
FROM 	表名 AS 别名, ...
WHERE 	連接配接條件等

員工名

，

工種号

工種名

 
SELECT 	last_name,e.job_id,job_title
FROM 	employees AS e, jobs AS j
WHERE 	e.job_id = j.job_id;

由于兩個表中都有job_id這個字段，是以在

SELECT

中需要指明是哪張表

有獎金的

員工名

部門名

 
SELECT	e.last_name, d.department_name
FROM	employees e, departments d
WHERE 	e.department_id = d.department_id
		AND	e.commission_pct IS NOT NULL; <--加入的篩選條件

位于的城市的城市名中第二個字元為o

的

部門名

和

城市名

 
SELECT city,department_name
FROM locations l,departments d
WHERE d.location_id=l.location_id
		AND city LIKE '_o%';

每個城市

部門個數

 
SELECT 	 city,COUNT(*) 部門個數
FROM 	 locations l,departments d
WHERE 	 l.location_id=d.location_id
GROUP BY city
SQL語句（四）聯表查詢

每個工種

工種名

員工個數

，并

按員工個數降序

 
SELECT 	job_title,COUNT(*) 員工個數
FROM 	jobs j,employees e
WHERE 	j.job_id=e.job_id
GROUP BY job_title
ORDER BY 員工個數 DESC;

和兩表連接配接是基本相同的，在

WHERE

語句中加入一個連接配接條件即可

擷取所有員工的

員工名

部門名

所在城市

 
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
		AND d.location_id = l.location_id;
SQL語句（四）聯表查詢

員工的工資

工資級别

job_grades

表：

SQL語句（四）聯表查詢
SELECT 	salary, grade_level
FROM 	employees e, job_grades j
WHERE 	e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;
SQL語句（四）聯表查詢

查詢員工名及其對應上級的名稱

SELECT 	e.last_name 員工,m.last_name 上級
FROM 	employees e, employees m
WHERE 	e.manager_id=m.employee_id;
SQL語句（四）聯表查詢
SELECT 查詢清單
FROM 表1别名 
	【連接配接類型】 join 表2 别名 
	on 連接配接條件
where xxx

  • 内連接配接：

    inner

    sql92

     的等值連接配接是等效的
  • 外連接配接：
    • 左外：

      left [outer]
    • 右外：

      right [outer]
    • 全外：

      full [outer]
  • 交叉連接配接：

    cross

擷取所有的

員工名

和其對應的

部門名

 
SELECT e.last_name, d.department_name
FROM employees e INNER JOIN departments d
		ON e.department_id = d.department_id;

部門個數>3

城市名

部門個數

 
SELECT city, COUNT(1) 部門個數
FROM departments d INNER JOIN locations l
		ON d.location_id = l.location_id
GROUP BY city
HAVING 部門個數 > 3;

查詢員工名、部門名、工種名，并按部門名排序【三表連接配接】

SELECT last_name, department_name, job_title
FROM employees e INNER JOIN departments d 
		ON e.department_id = d.department_id
	INNER JOIN jobs j
		ON e.job_id = j.job_id
ORDER BY department_name DESC;

查詢員工的工資級别【非等值連接配接】

SELECT last_name, salary, grade_level
FROM employees e INNER JOIN job_grades j
	ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY salary;

查詢員工名即其對應的上級名【自連接配接】

SELECT e.last_name 員工名, m.last_name 上級名
FROM employees e INNER JOIN employees m
	ON e.manager_id = m.employee_id;
SQL語句（四）聯表查詢

由上面的例子可以看出，使用sql99的内連接配接

(INNER JOIN)

即可實作sql92的所有連接配接操作了。

作用：查詢

一個表有

另一個表沒有

的記錄

先使用

girls.sql

生成對應的資料，對應的檔案可以在https://www.bilibili.com/video/BV12b411K7Zu?from=search&seid=2415880702283399133 這個b站視訊的評論區中找到。

此時我們可以獲得如下幾張表：

SQL語句（四）聯表查詢

然後我們可以分别使用内連接配接和外連接配接分别連接配接

beauty

boys

這兩張表，檢視結果的差異：

SELECT *
FROM beauty b INNER JOIN boys y
	ON b.boyfriend_id = y.id;
SQL語句（四）聯表查詢

外連接配接（左外）：

SELECT *
FROM beauty b LEFT JOIN boys y
	ON b.boyfriend_id = y.id;
SQL語句（四）聯表查詢

可以發現，當

左表

（即

beauty

表）的

boyfriend_id

字段找不到

boys

表中對應的

id

進行連接配接時，它也仍會保留這一記錄，而右表（即

boys

表）的記錄則全部設定為

Null

。

一句話來說，就是左表的資料不管是否滿足連接配接條件，都至少會保留在最終查詢集的一條記錄之中。

查找男朋友不在男神表的女神名【左外連接配接】：

SELECT `name`, boyName
FROM beauty b LEFT JOIN boys y
	ON b.boyfriend_id = y.id
WHERE y.id IS NULL;

【右外連接配接】

SELECT `name`, boyName
FROM boys y RIGHT JOIN beauty b
	ON b.boyfriend_id=y.id
WHERE y.id IS NULL;
SQL語句（四）聯表查詢
SELECT `name`, boyName
FROM beauty b RIGHT JOIN boys y
	ON b.boyfriend_id=y.id
WHERE b.id IS NULL;
           
SELECT department_name, COUNT(*)
FROM departments d LEFT JOIN employees e
	ON d.department_id = e.department_id
GROUP BY department_name
HAVING COUNT(*)=0;
           

SELECT b.`name`, y.boyName
FROM beauty b CROSS JOIN boys y;
sql 内連接配接 外連接配接 等值連接配接 交叉連接配接
上一篇: mycat學習日記：關于聯表查詢
下一篇: 一對多聯表查詢

繼續閱讀