七：連接配接查詢（内連接配接、外連接配接）

#進階6：連接配接查詢

說明：又稱多表查詢，當查詢語句涉及到的字段來自于多個表時，就會用到連接配接查詢

笛卡爾乘積現象：表1 有m行，表2有n行，結果=m*n行

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

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

分類：

    按年代分類：

    1、sql92标準:僅僅支援内連接配接

            内連接配接：

                 等值連接配接

                 非等值連接配接

                 自連接配接

  2、sql99标準【推薦】：支援内連接配接+外連接配接（左外和右外）+交叉連接配接

    按功能分類：

        内連接配接：

              等值連接配接

             非等值連接配接

             自連接配接

        外連接配接：

             左外連接配接

             右外連接配接

             全外連接配接  

      交叉連接配接

#引入案例

#查詢女神名和對應的男神名

SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyName FROM boys,beauty

WHERE beauty.boyfriend_id= boys.id;

#---------------------------------sql92标準------------------

#一、内連接配接

/*

文法:

select 查詢清單

from 表1 别名,表2 别名

where 連接配接條件

and 篩選條件

group by 分組清單

having 分組後篩選

order by 排序清單

執行順序：

1、from子句

2、where子句

3、and子句

4、group by子句

5、having子句

6、select子句

7、order by子句

#一）等值連接配接

① 多表等值連接配接的結果為多表的交集部分

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

③ 多表的順序沒有要求

④一般需要為表起别名

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

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

SELECT NAME,boyName 

FROM boys,beauty

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

SELECT last_name,department_name

FROM employees,departments

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

#2、為表起别名

①提高語句的簡潔度

②區分多個重名的字段

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

#查詢員工名、工種号、工種名

SELECT e.last_name,e.job_id,j.job_title

FROM employees  e,jobs j

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

#3、兩個表的順序是否可以調換

FROM jobs j,employees e

#4、可以加篩選

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

SELECT last_name,department_name,commission_pct

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%';

#5、可以加分組

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

SELECT COUNT(*) 個數,city

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

#6、可以加排序

#案例：查詢每個工種的工種名和員工的個數，并且按員工個數降序

SELECT job_title,COUNT(*)

FROM employees e,jobs j

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

GROUP BY job_title

ORDER BY COUNT(*) DESC;

#7、可以實作三表連接配接？

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

SELECT last_name,department_name,city

FROM employees e,departments d,locations l

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

AND city LIKE 's%'

ORDER BY department_name DESC;

#二）非等值連接配接

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

SELECT salary,grade_level

FROM employees e,job_grades g

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

AND g.`grade_level`='A';

select salary,employee_id from employees;

select * from job_grades;

CREATE TABLE job_grades

(grade_level VARCHAR(3),

 lowest_sal  int,

 highest_sal int);

INSERT INTO job_grades

VALUES ('A', 1000, 2999);

VALUES ('B', 3000, 5999);

VALUES('C', 6000, 9999);

VALUES('D', 10000, 14999);

VALUES('E', 15000, 24999);

VALUES('F', 25000, 40000);

#三）自連接配接

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

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name

FROM employees e,employees m

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

#------------------------SQL99文法

文法：

SELECT 查詢清單                                                              select  查詢清單

FROM 表名1 别名                                                              from  表1 别名

【INNER】 JOIN  表名2 别名                                           【inner】jion  表2 别名 on 連接配接條件

ON 連接配接條件                                                                     【inner】join  表3 别名 on 連接配接條件

WHERE 篩選條件                                                               where 分組前的篩選條件

GROUP BY 分組清單                                                         GROUP BY 分組清單

HAVING 分組後篩選                                                           HAVING 分組後篩選

ORDER BY 排序清單;                                                        ORDER BY 排序清單;

SQL92和SQL99的差別：

    SQL99，使用JOIN關鍵字代替了之前的逗号，并且将連接配接條件和篩選條件進行了分離，提高閱讀性！！！

#①簡單連接配接

#案例：查詢員工名和部門名

FROM departments d 

 JOIN  employees e 

ON e.department_id =d.department_id;

#②添加篩選條件

#案例1：查詢部門編号>100的部門名和所在的城市名

FROM departments d

JOIN locations l

ON d.`location_id` = l.`location_id`

WHERE d.`department_id`>100;

#③添加分組+篩選

SELECT COUNT(*) 部門個數,l.`city`

ON d.`location_id`=l.`location_id`

GROUP BY l.`city`;

#④添加分組+篩選+排序

#案例1：查詢部門中員工個數>10的部門名，并按員工個數降序

SELECT COUNT(*) 員工個數,d.department_name

FROM employees e

JOIN departments d

ON e.`department_id`=d.`department_id`

GROUP BY d.`department_id`

HAVING 員工個數>10

ORDER BY 員工個數 DESC;

#案例：查詢部門編号在10-90之間的員工的工資級别，并按級别進行分組

SELECT * FROM sal_grade;

SELECT COUNT(*) 個數,grade

JOIN sal_grade g

ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`

WHERE e.`department_id` BETWEEN 10 AND 90

GROUP BY g.grade;

#案例：查詢員工名和對應的上司名

SELECT e.`last_name`,m.`last_name`

JOIN employees m

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

#二、外連接配接      

說明：查詢結果為主表中所有的記錄，如果從表有比對項，則顯示比對項；如果從表沒有比對項，則顯示null

應用場景：一般用于查詢主表中有但從表沒有的記錄

特點：

1、外連接配接分主從表，兩表的順序不能任意調換

2、左連接配接的話，left join左邊為主表

   右連接配接的話，right join右邊為主表

from 表1 别名

left / right / full 【outer】 join 表2 别名                             注意 ：full 為全連

on 連接配接條件

where 篩選條件;

USE girls;

#案例1：查詢所有女神記錄，以及對應的男神名，如果沒有對應的男神，則顯示為null

#左連接配接

SELECT b.*,bo.*

FROM beauty b

LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

#右連接配接

FROM boys bo

RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`;

#案例2：查哪個女神沒有男朋友

SELECT b.`name`

LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`

WHERE bo.`id`  IS NULL;

RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`

#案例3：查詢哪個部門沒有員工，并顯示其部門編号和部門名

SELECT COUNT(*) 部門個數

作者：zhang-X​，轉載請注明原文連結​