《尚硅谷》MySQL系統課程一共6天，下面介紹第3天的學習內容，主要涉及進階6連接查詢（sql99語法）、進階7子查詢和進階8分頁查詢，干貨滿滿，跟著課程的進度來的，可能篇幅略長，但是看完一定會有識訓，那我們現在開始吧，

進階6：連接查詢（sql99語法）

語法：

分類：

代碼分析：

進階7：子查詢

含義：

分類：

代碼分析：

進階8：分頁查詢 ★

應用場景：

語法：

特點：

代碼分析：

進階6：連接查詢（sql99語法）

語法：

select 查詢串列

from 表1 別名 【連接型別】

join 表2 別名 

on 連接條件

【where 篩選條件】#該版本可以區分連接條件和篩選條件

【group by 分組】

【having 篩選條件】

【order by 排序串列】

分類：

1、內連接（★）：inner

2、外連接

左外(★):left 【outer】

右外(★)：right 【outer】

全外：full【outer】

3、交叉連接：cross

代碼分析：

一、內連接

語法：

select 查詢串列

from 表1 別名

inner join 表2 別名

on 連接條件;

分類：

等值連接條件是等值的

非等值連接條件是非等值的

自連接根據表1的變數連接表1

特點：

①添加排序ORDER BY、分組GROUP BY、篩選HAVING/WHERE

②inner可以省略

③篩選條件放在where后面，連接條件放在on后面，提高分離性，便于閱讀

④inner join連接和sql92語法中的等值連接效果是一樣的，都是查詢多表的交集

1.等值連接

#1、等值連接

#案例1.查詢員工名、部門名--可以調換表的順序-屬性`department_id`是唯一的，

SELECT last_name,department_name

FROM employees e

INNER JOIN departments d

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

SELECT last_name,department_name

FROM departments d

JOIN  employees e

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

#案例2.查詢名字中包含e的員工名和工種名（添加篩選）

SELECT last_name,job_title

FROM employees e

INNER JOIN jobs j

ON e.`job_id`=  j.`job_id`

WHERE e.`last_name` LIKE '%e%';

#案例3. 查詢部門個數>3的城市名和部門個數（添加分組+篩選）

#①查詢每個城市的部門個數-添加別名

#②在①結果上篩選滿足條件的

SELECT city,COUNT(*) 部門個數

FROM departments d

INNER JOIN locations l

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

GROUP BY city

HAVING COUNT(*)>3; #添加篩選條件

#案例4.查詢哪個部門的員工個數>3的部門名和員工個數，并按個數降序（添加排序）

#①查詢每個部門的員工個數

SELECT COUNT(*),department_name

FROM employees e

INNER JOIN departments d

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

GROUP BY department_name

#② 在①結果上篩選員工個數>3的記錄，并排序

SELECT COUNT(*) 個數,department_name

FROM employees e

INNER JOIN departments d

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

GROUP BY department_name

HAVING COUNT(*)>3

ORDER BY COUNT(*) DESC; #按照降序排列

#案例5.查詢員工名、部門名、工種名，并按部門名降序（添加三表連接）

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

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;

2.非等值連接

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

SELECT salary,grade_level

FROM employees e

JOIN job_grades g

ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#案例2：查詢工資級別的個數>20的個數，并且按工資級別降序

SELECT COUNT(*),grade_level

FROM employees e

JOIN job_grades g

ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`

GROUP BY grade_level

HAVING COUNT(*)>20

ORDER BY grade_level DESC;

3.自連接

 #案例1：查詢員工的名字、上級的名字

 SELECT e.last_name,m.last_name

 FROM employees e

 JOIN employees m

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

 #案例2：查詢姓名中包含字符k的員工的名字、上級的名字

 SELECT e.last_name,m.last_name

 FROM employees e

 JOIN employees m

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

 WHERE e.`last_name` LIKE '%k%';

二、外連接

應用場景：用于查詢一個表中有，另一個表沒有的記錄

特點：

1、外連接的查詢結果為主表中的所有記錄

如果從表中有和它匹配的，則顯示匹配的值

如果從表中沒有和它匹配的，則顯示null

外連接查詢結果 = 內連接結果 + 主表中有而從表沒有的記錄

2、左外連接，left join左邊的是主表

右外連接，right join右邊的是主表

3、左外和右外交換兩個表的順序，可以實作同樣的效果

4、全外連接 = 內連接的結果 + 表1中有但表2沒有的 + 表2中有但表1沒有的

 #引入：查詢男朋友 不在男神表的的女神名

 SELECT * FROM beauty;

 SELECT * FROM boys;

 #左外連接-查找沒有女神的男神名

 SELECT b.*,bo.*

 FROM boys bo

 LEFT OUTER JOIN beauty b

 ON b.`boyfriend_id` = bo.`id`

 WHERE b.`id` IS NULL;

 #左外連接-查找沒有男朋友的女神名

 SELECT b.name,bo.*

 FROM beauty b

 LEFT OUTER JOIN boys bo

 ON b.`boyfriend_id` = bo.`id`

 WHERE bo.`id` IS NULL;

 #右外連接-查找沒有男朋友的女神名

 SELECT b.name,bo.*

 FROM boys bo

 RIGHT OUTER JOIN beauty b

 ON b.`boyfriend_id` = bo.`id`

 WHERE bo.`id` IS NULL;

 #案例1：查詢哪個部門沒有員工

 #左外

 SELECT d.*,e.employee_id

 FROM departments d

 LEFT OUTER JOIN employees e

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

 WHERE e.`employee_id` IS NULL;

 #右外

 SELECT d.*,e.employee_id

 FROM employees e

 RIGHT OUTER JOIN departments d

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

 WHERE e.`employee_id` IS NULL;

 #全外（不執行，因為MySQL不支持）

 USE girls;

 SELECT b.*,bo.*

 FROM beauty b

 FULL OUTER JOIN boys bo

 ON b.`boyfriend_id` = bo.id;

 #交叉連接

 SELECT b.*,bo.*

 FROM beauty b

 CROSS JOIN boys bo;

sql92和 sql99大pk

①功能：sql99支持的較多

②可讀性：sql99實作連接條件和篩選條件的分離，可讀性較高

【案例講解】外連接-多表連接

#一、查詢編號>3的女神的男朋友資訊，如果有則列出詳細，如果沒有，用null填充

#注意審題，beauty表左連接

SELECT bo.*,b.name,b.id

FROM boys bo

LEFT OUTER JOIN beauty b

ON bo.id = b.boyfriend_id

WHERE b.id > 3; 

SELECT b.id,b.name,bo.*

FROM beauty b

LEFT OUTER JOIN boys bo

ON b.`boyfriend_id` = bo.`id`

WHERE b.`id` > 3;

#二、查詢哪個城市沒有部門----等價于部門id為空

USE myemployees;

SELECT city   #, COUNT(department_name)

FROM locations l

LEFT OUTER JOIN departments d

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

GROUP BY city

HAVING COUNT(department_name)=0;

SELECT city      #,d.*

FROM departments d

RIGHT OUTER JOIN locations l 

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

WHERE  d.`department_id` IS NULL;

#三、查詢部門名為SAL或IT的員工資訊

USE myemployees;

#最好還是顯示d.`department_id`，因為不是一一對應department_name

SELECT e.*,d.department_name,d.`department_id`

FROM employees e

FROM employees e

LEFT OUTER JOIN departments d

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

WHERE department_name = SAL OR department_name = IT;

SELECT e.*,d.department_name,d.`department_id`

FROM departments  d

LEFT JOIN employees e

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

WHERE d.`department_name` IN('SAL','IT');

SELECT * FROM departments

WHERE `department_name` IN('SAL','IT');

進階7：子查詢

含義：

出現在其他陳述句（不僅僅是查詢陳述句）中的select陳述句，稱為子查詢或內查詢

外部的查詢陳述句，稱為主查詢或外查詢

分類：

按子查詢出現的位置：

1、select后面：

僅僅支持標量子查詢

2、from后面：

支持表子查詢

3、where或having后面：★

標量子查詢（單行） √

列子查詢（多行） √

行子查詢

4、exists后面（相關子查詢）

表子查詢

按結果集的行列數不同：

標量子查詢（結果集只有一行一列）

列子查詢（結果集只有一列多行）

行子查詢（結果集有一行多列）

表子查詢（結果集一般為多行多列）

代碼分析：

一、where或having后面

1、標量子查詢（單行子查詢）

2、列子查詢（多行子查詢）

3、行子查詢（多列多行）

特點：

①子查詢放在小括號內

②子查詢一般放在條件的右側

③標量子查詢，一般搭配著單行運算子使用

> < >= <= = <>

列子查詢，一般搭配著多行運算子使用

in、any/some、all

④子查詢的執行優先于主查詢執行，主查詢的條件用到了子查詢的結果

1.標量子查詢★

#案例1：誰的工資比 Abel 高?

#①查詢Abel的工資

SELECT salary

FROM employees

WHERE last_name = 'Abel'

#②查詢員工的資訊，滿足 salary>①結果

SELECT *

FROM employees

WHERE salary>(

    SELECT salary

    FROM employees

    WHERE last_name = 'Abel'

);

#案例2：回傳job_id與141號員工相同，salary比143號員工多的員工 姓名，job_id 和工資

#①查詢141號員工的job_id

SELECT job_id

FROM employees

WHERE employee_id = 141

#②查詢143號員工的salary

SELECT salary

FROM employees

WHERE employee_id = 143

#③查詢員工的姓名，job_id 和工資，要求job_id=①并且salary>②

SELECT last_name,job_id,salary

FROM employees

WHERE job_id = (

    SELECT job_id

    FROM employees

    WHERE employee_id = 141

) AND salary>(

    SELECT salary

    FROM employees

    WHERE employee_id = 143

);

#案例3：回傳公司工資最少的員工的last_name,job_id和salary

#①查詢公司的 最低工資

SELECT MIN(salary)

FROM employees

#②查詢last_name,job_id和salary，要求salary=①

SELECT last_name,job_id,salary

FROM employees

WHERE salary=(

    SELECT MIN(salary)

    FROM employees

);

#案例4：查詢最低工資大于50號部門最低工資的部門id和其最低工資

#①查詢50號部門的最低工資

SELECT  MIN(salary)

FROM employees

WHERE department_id = 50

#②查詢每個部門的最低工資

SELECT MIN(salary),department_id

FROM employees

GROUP BY department_id

#③ 在②基礎上篩選，滿足min(salary)>①

SELECT MIN(salary),department_id

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(

    SELECT  MIN(salary)

    FROM employees

    WHERE department_id = 50

);



#非法使用標量子查詢

SELECT MIN(salary),department_id

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(

    SELECT  salary

    FROM employees

    WHERE department_id = 250

);

2.列子查詢（多行子查詢）★

多行比較運算子	含義
IN/NOT IN	等于串列中的任意一個
ANY/SOME	和子查詢回傳的某一個值比較
ALL	和子查詢回傳的所有值比較

體會ANY和ALL的區別，最大值MAX和最小值MIN，

#案例1：回傳location_id是1400或1700的部門中的所有員工姓名

#①查詢location_id是1400或1700的部門編號-查詢結果是單例多行

SELECT DISTINCT department_id

FROM departments

WHERE location_id IN(1400,1700)

#②查詢員工姓名，要求部門號是①串列中的某一個

SELECT last_name

FROM employees

WHERE department_id IN(

    SELECT DISTINCT department_id

    FROM departments

    WHERE location_id IN(1400,1700)

);

#或

SELECT last_name

FROM employees

WHERE department_id = ANY(

    SELECT DISTINCT department_id

    FROM departments

    WHERE location_id IN(1400,1700)

);


#NOT IN 等價于 <> ALL

SELECT last_name

FROM employees

WHERE department_id <> ALL(

    SELECT DISTINCT department_id

    FROM departments

    WHERE location_id IN(1400,1700)

);

#案例2：回傳其它工種中比job_id為‘IT_PROG’工種任一工資低的員工的員工號、姓名、job_id 以及salary

#①查詢job_id為‘IT_PROG’部門任一工資

SELECT DISTINCT salary

FROM employees

WHERE job_id = 'IT_PROG'

#②查詢員工號、姓名、job_id 以及salary，salary<(①)的任意一個

SELECT last_name,employee_id,job_id,salary

FROM employees

WHERE salary < ANY(

    SELECT DISTINCT salary

    FROM employees

    WHERE job_id = 'IT_PROG'

) AND job_id <> 'IT_PROG';#區分其他工種

#或

SELECT last_name,employee_id,job_id,salary

FROM employees

WHERE salary < (

    SELECT MAX(salary)

    FROM employees

    WHERE job_id = 'IT_PROG'

) AND job_id <> 'IT_PROG';

#案例3：回傳其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工   的員工號、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary

FROM employees

WHERE salary < ALL(

    SELECT DISTINCT salary

    FROM employees

    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或

SELECT last_name,employee_id,job_id,salary

FROM employees

WHERE salary < (

    SELECT MIN(salary)

    FROM employees

    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

3、行子查詢（結果集一行多列或多行多列）-用的較少

#案例：查詢員工編號最小并且工資最高的員工資訊-具備兩個限制條件的員工不一定存在

#用行子查詢，前提是兩個條件都是等于，大于

SELECT * 

FROM employees

WHERE (employee_id,salary)=(

    SELECT MIN(employee_id),MAX(salary)

    FROM employees

);

#以前的方法

#①查詢最小的員工編號

SELECT MIN(employee_id)

FROM employees

#②查詢最高工資

SELECT MAX(salary)

FROM employees

#③查詢員工資訊

SELECT *

FROM employees

WHERE employee_id=(

    SELECT MIN(employee_id)

    FROM employees

)AND salary=(

    SELECT MAX(salary)

    FROM employees

);

二、select后面

說明：僅僅支持標量子查詢（一行一列）

#案例：查詢每個部門的員工個數--想要在原來表的基礎上加一列“個數”

SELECT d.*,(

    SELECT COUNT(*)

    FROM employees e

    WHERE e.department_id = d.`department_id`

 ) 個數

 FROM departments d;

#案例2：查詢員工號=102的部門名-也可以用其他方式代替，因此這個功能榷訓了

SELECT (

    SELECT department_name    #,e.department_id

    FROM departments d

    INNER JOIN employees e

    ON d.department_id=e.department_id

    WHERE e.employee_id=102    

) 部門名;

三、from后面

說明：將子查詢結果充當一張表，要求必須起別名

#案例：查詢每個部門的平均工資的工資等級

#①查詢每個部門的平均工資

SELECT AVG(salary),department_id

FROM employees

GROUP BY department_id

SELECT * FROM job_grades;

#②連接①的結果集和job_grades表，篩選條件平均工資 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`

FROM (

    SELECT AVG(salary) ag,department_id

    FROM employees

    GROUP BY department_id

) ag_dep

INNER JOIN job_grades g

ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists后面（相關子查詢）--一般可以用IN代替

語法：exists(完整的查詢陳述句)--判斷有沒有值

結果：1或0

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000); #0

#案例1：查詢有員工的部門名

#in

SELECT department_name

FROM departments d

WHERE d.`department_id` IN(

    SELECT department_id

    FROM employees

)

#exists-先執行外查詢，然后用內查詢去過濾資料

SELECT department_name

FROM departments d

WHERE EXISTS(

    SELECT *

    FROM employees e

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

);

#案例2：查詢沒有女朋友的男神資訊

#in

SELECT bo.*

FROM boys bo

WHERE bo.id NOT IN(

    SELECT boyfriend_id

    FROM beauty

)

#exists

SELECT bo.*

FROM boys bo

WHERE NOT EXISTS(

    SELECT boyfriend_id

    FROM beauty b

    WHERE bo.`id`=b.`boyfriend_id`

);

【案例講解】子查詢

無答案版，可以先自己寫一遍代碼：

1. 查詢和Zlotkey相同部門的員工姓名和工資

2.查詢工資比公司平均工資高的員工的員工號，姓名和工資，

3.查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資

4. 查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名

5. 查詢在部門的location_id為1700的部門作業的員工的員工號

6.查詢管理者是King的員工姓名和工資

7.查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列，列名為姓.名

#1. 查詢和Zlotkey相同部門的員工姓名和工資

#①查詢Zlotkey的部門

SELECT department_id

FROM employees

WHERE last_name = 'Zlotkey'

#②查詢部門號=①的姓名和工資

SELECT last_name,salary

FROM employees

WHERE department_id = (

    SELECT department_id

    FROM employees

    WHERE last_name = 'Zlotkey'

)

#2.查詢工資比公司平均工資高的員工的員工號，姓名和工資，

#①查詢平均工資

SELECT AVG(salary)

FROM employees

#②查詢工資>①的員工號，姓名和工資，

SELECT last_name,employee_id,salary

FROM employees

WHERE salary>(

    SELECT AVG(salary)

    FROM employees

);

#3.查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資

#①查詢各部門的平均工資--新表

SELECT AVG(salary),department_id

FROM employees

GROUP BY department_id

#②連接①結果集和employees表，進行篩選

SELECT employee_id,last_name,salary,e.department_id

FROM employees e

INNER JOIN (

    SELECT AVG(salary) ag,department_id

    FROM employees

    GROUP BY department_id

) ag_dep

ON e.department_id = ag_dep.department_id

WHERE salary > ag_dep.ag ;

#4. 查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名

#①查詢姓名中包含字母u的員工的部門

SELECT  DISTINCT department_id

FROM employees

WHERE last_name LIKE '%u%'

#②查詢部門號=①中的任意一個的員工號和姓名

SELECT last_name,employee_id

FROM employees

WHERE department_id IN(

    SELECT  DISTINCT department_id

    FROM employees

    WHERE last_name LIKE '%u%'

);

#5. 查詢在部門的location_id為1700的部門作業的員工的員工號

#①查詢location_id為1700的部門

SELECT DISTINCT department_id

FROM departments 

WHERE location_id  = 1700

#②查詢部門號=①中的任意一個的員工號

SELECT employee_id

FROM employees

WHERE department_id =ANY(

    SELECT DISTINCT department_id

    FROM departments 

    WHERE location_id  = 1700

);

#6.查詢管理者是King的員工姓名和工資

#①查詢姓名為king的員工編號

SELECT employee_id

FROM employees

WHERE last_name  = 'K_ing'

#②查詢哪個員工的manager_id = ①

SELECT last_name,salary

FROM employees

WHERE manager_id IN(

    SELECT employee_id

    FROM employees

    WHERE last_name  = 'K_ing'

);

#7.查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列，列名為 姓.名

#①查詢最高工資

SELECT MAX(salary)

FROM employees

#②查詢工資=①的姓.名

SELECT CONCAT(first_name,last_name) "姓.名"

FROM employees

WHERE salary=(

    SELECT MAX(salary)

    FROM employees

);

進階8：分頁查詢 ★

應用場景：

當要顯示的資料，一頁顯示不全，需要分頁提交sql請求

語法：

select 查詢串列

from 表

【join type join 表2

on 連接條件

where 篩選條件

group by 分組欄位

having 分組后的篩選

order by 排序的欄位】

limit 【offset,】size;

其中：

offset——要顯示條目的起始索引（起始索引從0開始）

size——要顯示的條目個數

特點：

①limit陳述句放在查詢陳述句的最后

②公式：已知要顯示的頁數 page，每頁的條目數size，則limit (page-1)*size,size;

代碼分析：

#案例1：查詢前五條員工資訊

SELECT * FROM  employees LIMIT 0,5;

SELECT * FROM  employees LIMIT 5;#起始索引為0可以省略

#案例2：查詢第11條——第25條

SELECT * FROM  employees LIMIT 10,15;

#案例3：有獎金的員工資訊，并且工資較高的前10名顯示出來

SELECT

    *

FROM

    employees

WHERE commission_pct IS NOT NULL

ORDER BY salary DESC

LIMIT 10 ;

說明：記錄學習筆記，發現有錯誤歡迎指正！轉載請聯系我，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/264554.html

標籤：其他

上一篇：平衡二叉樹的插入

下一篇：spring cloud添加分布式事務seata1.4.0

day03 MySQL連接查詢（sql99語法）&子查詢&分頁查詢

進階6：連接查詢（sql99語法）

語法：

分類：

代碼分析：

進階7：子查詢

含義：

分類：

代碼分析：

進階8：分頁查詢 ★

應用場景：

語法：

特點：

代碼分析：