好好吃飯,好好休息,聽著很簡單,實際落實缺失不那麼容易。 繼續回顧MySql的多表查詢之1999語法 #二,SQL1999語法 語法: SELECT 查詢列表 FROM 表1 別名 【連接類型】 JOIN 表2 別名 ON 鏈接條件 【WHERE 篩選條件】 【GROUP BY 分組】 【HAVIN ...
好好吃飯,好好休息,聽著很簡單,實際落實缺失不那麼容易。
繼續回顧MySql的多表查詢之1999語法
#二,SQL1999語法 語法: SELECT 查詢列表 FROM 表1 別名 【連接類型】 JOIN 表2 別名 ON 鏈接條件 【WHERE 篩選條件】 【GROUP BY 分組】 【HAVING 篩選條件】 【ORDER BY 排序列表ASC|DESC】 分類(連接類型): 內連接(★): INNER 外聯結 左外(★):LEFT 【OUTER】 右外(★):RIGHT 【OUTER】 全外:FULL 【OUTER】 交叉連接:CROSS
===============================================================================
一、內連接 語法: SELECT 查詢列表 FROM 表1 別名 INNER JOIN 表2 別名 ON 連接條件 【WHERE 篩選條件】 【GROUP BY 分組】 【HAVING 篩選分組】 【ORDER BY 排序列表 ASC|DESC】 分類: 等值 非等值 自連接 特點: ①添加排序,分組,篩選 ②INNER可以省略 ③篩選條件放在WHERE後面,連接條件放在ON後面,提高分離性,便於閱 讀。 ④INNER JOIN連接和SQL1992語法中的等值連接效果是一樣的,都是查 詢交集部分。
#1.等值連接
#案例1.查詢員工名,部門名。交換連接條件不影響結果。
SELECT last_name AS 員工名,department_name AS 部門名
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
SELECT last_name AS 員工名,department_name AS 部門名
FROM employees d
INNER JOIN departments e
ON e.department_id=d.department_id;
#案例2.查詢名字中包含e的員工和工種名(篩選)
SELECT last_name AS 員工名,job_title AS 工種名
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';
#案例3.查詢部門個數>3的城市名和部門個數。(分組+篩選)
分步:先把每個城市的部門個數查出來,在篩選滿足條件的。
SELECT city AS 城市,COUNT(*) AS 個數
FROM locations l
INNER JOIN departments d
ON l.location_id=d.location_id
GROUP BY city
HAVING COUNT(*) > 3;
#案例4.查詢哪個部門員工個數>3的部門名和員工個數,並按個數降序排序。
分步:
1.查詢每個部門的員工個數,
2.在上面的結果上篩選出員工數大於3的記錄,
3.把員工數排序
SELECT COUNT(*) AS 員工個數,d.department_name AS 部門名
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY d.department_name
HAVING COUNT(*) > 3
ORDER BY 員工個數 DESC;
#案例5.查詢員工名,部門名,工種名,並按部門名降序。三表連接註意條件。
SELECT last_name AS 員工名,department_name AS 部門名,
job_title AS 工種名
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 d.department_name DESC;
#2.非等值連接。範圍(間接)
#案例1.查詢員工的工資級別。
SELECT salary AS 月薪,grade_level AS 工資等級
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
#案例2.查詢每個工資的級別的個數>20,並且按降序排列。
SELECT j.grade_level AS 等級,COUNT(*) 個數
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY j.grade_level
HAVING COUNT(*) > 20
ORDER BY 個數 DESC;
#3.自連接
#案例1.查詢員工的名字,上級的名字。
SELECT e.last_name AS 員工名,m.last_name AS 上級名
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;
#案例2.查詢員工的名字,上級的名字,名字中包含字元k。
SELECT e.last_name AS 員工名,m.last_name AS 上級名
FROM employees e
INNER 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中有但表一沒有的(Mysql不支持)
引入:
SELECT * FROM beauty;
SELECT * FROM boys;
#引入:查詢男朋友不在男神表的女神名
SELECT b.NAME,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;
#左外連接
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;
#把beauty表中的id為10行的boyfriend_id從4改到6.才能查詢出如下結果。
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE b.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.department_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不支持)語法結構演示
先把兩張表的交集查出來,在把缺失的填充查出來,在把其他無關聯的查出來
語法結構示例
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
#交叉連接,使用1999的語法標準實現的
笛卡爾乘積
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
#SQL1992語法 與 SQL 1999語法 相比
功能:SQL1999支持的較多
可讀性:SQL1999實現連接條件和篩選條件的分離,可讀性提高
總結:
SELECT <select_list>
FROM A
INNER JOIN B
ON A.KEY=B.KEY;
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.KEY=B.KEY;
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.KEY=B.KEY;
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.KEY=B.KEY
WHERE B.KEY IS NULL;
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.KEY=B.KEY
WHERE A.KEY IS NULL;
SELECT <select_list>
FROM A
FULL JOIN B
ON A.KEY=B.KEY;
SELECT <select_list>
FROM A
FULL JOIN B
ON A.KEY=B.KEY
WHERE A.KEY IS NULL
OR B.KEY IS NULL;
簡單的多表查詢已結束,相信看到這裡的基本對簡單多表連接應該手到擒來,至於看不懂的emmm...
用我們楊老師的話就是,自己去寫個二三十遍就有感覺了。o(^▽^)o。
高考結束,大學生活的開始,世界是屬於你們的倒計時開啟了...不管你們選擇什麼專業,只要是你自己選擇的,一定要相信自己會在這個專業留下一筆....。