含義：又稱為多表查詢，當查詢的字段來自多個表時，就會用到連接查詢

笛卡爾乘積現象

表1 有m行，表2有n行，結果為m*n行

發生原因：沒有有效的連接條件。

如何避免：添加有效的連接條件。

案例：在boys表中匹配beauty表中女生的男朋友

以下的方法錯誤，會出現笛卡爾乘積現象。

正確解法：

SELECT

NAME,

boyName

FROM

boys,

beauty

WHERE beauty.`boyfriend_id` = boys.`id` ;

結果：

6.1 連接查詢的分類

按年代分類：SQL192標準：僅僅支持內連接

SQL199標準【推薦】：支持內連接+外連接(左外和右外)+交叉連接

按功能分類：

1.內連接：等值連接

非等值連接

自連接

2.外連接：左外連接

右外連接

全外連接

交叉連接

6.2 SQL192連接

1、等值連接

語法：①多表等值連接的結果為多表的交集部分

②n表連接，至少需要n-1個連接條件

③多表的順序沒有要求

④一般需要為表起別名

⑤可以搭配前面介紹的所有子句使用，比如排序、分組、篩選

案例1：查詢女神和對應的男神名

SELECT

NAME,

boyName

FROM

boys,

beauty

WHERE beauty.`boyfriend_id` = boys.`id` ;

案例2：查詢部門名和對應的部門名

SELECT

last_name,

department_name

FROM

employees,

departments

WHERE employees.`department_id` = `departments`.`department_id` ;

(1) 為表起別名提高語句的簡潔度

區分多個重名的字段

注意：如果為表起了別名，則查詢的字段就不能使用原來的表名去限定

案例：查詢員工名、工種號、工種名

SELECT

e.last_name,

e.job_id,

j.job_title

FROM

employees e,

jobs j

WHERE e.`job_id` = j.`job_id`;

兩個表的順序可以調換：

下面代碼和上面功能一樣

SELECT

e.last_name,

e.job_id,

j.job_title

FROM

jobs j,

employees e

WHERE e.`job_id` = j.`job_id`;

(2)可以加篩選

案例1：查詢有獎金的員工名、部門名

SELECT

last_name,

department_name

FROM

employees e,

departments d

WHERE e.`department_id` = d.`department_id`

AND e.`commission_pct` IS NOT NULL ;

案例2：查詢城市名中第二個字符為o的部門

SELECT

department_name,

city

FROM

departments d,

locations l

WHERE d.`location_id` = l.`location_id`

AND city LIKE '_o%' ;

結果：

(3)可以加分組

案例1：查詢每個城市的部門個數

SELECT

COUNT(*) AS "個數",

city

FROM

departments d,

locations l

WHERE d.`location_id` = l.`location_id`

GROUP BY city ;

案例2：查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資

SELECT

department_name,

d.manager_id,

MIN(salary)

FROM

departments d,

employees e

WHERE d.`department_id` = e.`department_id`

AND commission_pct IS NOT NULL

GROUP BY department_name,d.`manager_id` ;

(4)可以加排序

案例：查詢每個工種的工種名和員工的個數

SELECT

job_title,

COUNT(*)

FROM

employees e,

jobs j

WHERE e.`job_id` = j.`job_id`

GROUP BY job_title

ORDER BY COUNT(*) DESC ;

(5)可以實現三表連接

案例：查詢員工名、部門名和所在的城市

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

2、非等值連接

語法：

案例1：查詢員工的工資和工資級別

SELECT

salary,

grade_level

FROM

employees e,

job_grades g

WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;

工作原理：

用employees表中的salary去和job_grades對比，比如查詢24000的所在的等級，先用24000和1000及2999對比，如果介于這之間，則為等級A，依次分析，最終得到24000的等級為E。

3、自連接

語法：

案例：查詢員工名和上級的名稱

同一張表查兩次：比如查找Neena的上級是誰。

對上面分析的進一步拆分：

兩次查詢相當于把同一張表分別看著員工表和領導表，然后通過manager_id等于employee_id來查詢。

本題解答：

SELECT

e.employee_id,

e.last_name AS "員工",

m.employee_id,

m.last_name AS "上級"

FROM

employees e,

employees m

WHERE e.`manager_id` = m.`employee_id` ;

小測試

1.顯示員工的最大工資，工資平均值

SELECT MAX(salary),AVG(salary) FROM employees;

2.查詢員工表的employees_id,job_id,last_name,按department_id降序，salary升序

SELECT

employee_id,

job_id,

last_name

FROM

employees

ORDER BY department_id DESC,salary ASC ;

3.查詢員工表的job_id中包含a和e的，并且a在e的前面

SELECT

job_id

FROM

employees

WHERE job_id LIKE '%a%e%' ;

4.

SELECT

s.name,

g.name,

r.score

FROM

student s,

grade g,

result r

WHERE s.id = r.studentNo AND g.id = s.gradeid ;

5.顯示當前日期，以及去前后空格，截取子字符串的函數

6.顯示所有員工的姓名，部門號和部門名稱

SELECT

last_name,

d.department_id,

department_name

FROM

employees e,

departments d

WHERE e.`department_id` = d.`department_id` ;

7.查詢90號部門員工的job_id和90號部門的location_id

SELECT

job_id,

location_id

FROM

employees e,

departments d

WHERE e.`department_id` = d.`department_id` AND e.`department_id` = 90 ;

8.選擇所有有獎金的員工的last_name ,department_id ,location_id ,city

SELECT

last_name,

department_name,

l.location_id,

city

FROM

employees e,

departments d,

locations l

WHERE e.`department_id` = d.`department_id`

AND d.`location_id` = l.`location_id`

AND e.`commission_pct` IS NOT NULL ;

9.選擇city在Toronto工作的員工的last_name ,job_id ,department_id ,department_name

SELECT

last_name,

job_id,

d.department_id,

department_name

FROM

employees e,

departments d,

locations l

WHERE e.`department_id` = d.`department_id`

AND d.`location_id` = l.`location_id`

AND city = 'Toronto' ;

10.查詢每個工種、每個部門的部門號、工種號和最低工資

SELECT

department_name,

job_title,

MIN(salary) AS "最低工資"

FROM

employees e,

departments d,

jobs j

WHERE e.`department_id` = d.`department_id`

AND j.`job_id` = e.`job_id`

GROUP BY department_name,job_title ;

11.查詢每個國家下的部門個數大于2的國家編號

SELECT

country_id,

COUNT(*) 部門個數

FROM

departments d,

locations l

WHERE d.`location_id` = l.`location_id`

GROUP BY country_id

HAVING COUNT(*) > 2 ;

12.選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結果類似于下面的格式

SELECT

e.`last_name` employees,

e.`employee_id` "Emp#",

m.`last_name`,

m.`employee_id` = "Mgr#"

FROM

employees e,

employees m

WHERE e.`manager_id` = m.`employee_id`

AND e.`last_name` = "kochhar" ;

總結

以上是生活随笔為你收集整理的mysql创表的工种_[MySQL基础]七、连接查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。