MySQL數(shù)據(jù)庫：第八章：連接查詢

回退至Mysql數(shù)據(jù)庫理論與實戰(zhàn)

#進(jìn)階7：連接查詢

理解：查詢語句中涉及到的字段來自于多張表，將這種查詢稱為多表連接查詢
語法：select 查詢列表 from 表名1,表名2;
引入案例：
select name,boyname from beauty,boys;
select name,boyname from beauty,boys where boyfriend_id = boys.id;
笛卡爾乘積：
現(xiàn)象：表1和表2連接，結(jié)果為兩表的完全連接結(jié)果，數(shù)據(jù)不正確
表1m行，表2 n行，結(jié)果為：m*n 行
產(chǎn)生原因：沒有有效的連接條件
解決辦法：添加兩個表的連接條件
★ 找到兩個表的關(guān)聯(lián)關(guān)系。兩個表的關(guān)聯(lián)列的意思肯定是一樣，但名稱不一定一樣！一般通過主外鍵列進(jìn)行關(guān)聯(lián)。
連接查詢的分類 ★：
sql92語法：
內(nèi)連接
等值連接
非等值連接
自連接
外連接（支持的不太好，mysql壓根不支持）
sql99語法：
內(nèi)連接
等值連接
非等值連接
自連接
外連接
左外連接
右外連接
全外連接（mysql不支持）

#---------------------------------Sql92語法--------------------------------
#內(nèi)連接
#一、等值連接

語法：
select 查詢列表
from 表名1 別名1,表名2 別名2
where 別名1.關(guān)聯(lián)列 = 別名2.關(guān)聯(lián)列
and 篩選條件
group by 分組字段
having 分組后的篩選條件
order by 排序;
特點(diǎn)：
1、多表連接時，一般為表起別名，提高語句的簡潔性
a 、別名要短于 表名
b 、一旦為表起了別名，則只能使用別名限定，不能使用表名限定了!
2、表是否可以調(diào)換順序
答案：可以！不分主次表！
3、等值連接查詢，查詢的結(jié)果為兩個表的交集部分
4、n表連接，至少需要n-1個連接條件

#①簡單的兩表連接
#案例：查詢員工名和部門名

SELECT e.last_name,d.department_name
FROM departments d,employees e
WHERE e.`department_id` = d.`department_id`;

#②添加篩選條件
#案例1：查詢部門編號>100的部門名和所在的城市名

SELECT `department_name`,`city`
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND d.`department_id`>100;

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

SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`= d.`department_id`
AND e.`commission_pct` IS NOT NULL;

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

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

#③添加分組+篩選 【難點(diǎn)】
#案例1：查詢每個城市的部門個數(shù)

SELECT COUNT(*) 部門個數(shù),city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

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

SELECT MIN(salary) 最低工資,department_name
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name

#案例3：查詢部門中員工個數(shù)>10的部門名

SELECT COUNT(*),department_name
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>10;

#④添加分組+篩選+排序
#案例1：查詢部門中員工個數(shù)>10的部門名，并按部門名降序

SELECT COUNT(*),department_name
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>10
ORDER BY department_name DESC;

#案例2：查詢每個工種的員工個數(shù)和工種名，并按個數(shù)降序

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

#⑤三表連接

#案例：查詢員工名、部門名、城市名
SELECT last_name,department_name,city
FROM departments d,employees e,locations l
WHERE d.`department_id` = e.`department_id`
AND d.`location_id` = l.`location_id`;

#二、非等值連接
select 查詢列表
from 表名1 別名1,表名2 別名2
where 非等值的連接條件
【and 篩選條件
group by 分組字段
having 分組后的篩選條件
order by 排序】;

#案例：查詢員工的姓名、工資、工資級別
SELECT last_name,salary,grade
FROM employees e,sal_grade g
WHERE e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
ORDER BY grade;

#三、自連接
#案例：查詢每個員工的員工名和領(lǐng)導(dǎo)名

SELECT 員工表.last_name,領(lǐng)導(dǎo)表.last_name
FROM employees 員工表,employees 領(lǐng)導(dǎo)表
WHERE 員工表.`manager_id` = 領(lǐng)導(dǎo)表.`employee_id`;
SELECT last_name FROM employees WHERE employee_id = 149;

#1.查詢 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;

#2. 選擇所有有獎金的員工的

#last_name , department_name , 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;

#sal_grade的創(chuàng)建語句

USE  myemployees;
DROP TABLE IF EXISTS sal_grade;
CREATE TABLE sal_grade (
    id INT PRIMARY KEY AUTO_INCREMENT,
    min_salary DOUBLE ,
    max_salary DOUBLE,
    grade CHAR
);
INSERT INTO sal_grade VALUES(NULL,2000,3999,'A');
INSERT INTO sal_grade VALUES(NULL,4000,5999,'B');
INSERT INTO sal_grade VALUES(NULL,6000,9999,'C');
INSERT INTO sal_grade VALUES(NULL,10000,12999,'D');
INSERT INTO sal_grade VALUES(NULL,13000,14999,'E');
INSERT INTO sal_grade VALUES(NULL,15000,99999,'F');

#--------------------------------------------------SQL99語法--------------------------
#內(nèi)連接
#一、等值連接

語法：
select 查詢列表
from 表 別名1
【inner】 join 表 別名2
on 別名1.關(guān)聯(lián)列 = 別名2.關(guān)聯(lián)列
where 篩選條件
group by 分組
having 分組后條件
order by 條件;
區(qū)別：
①sql99語法，使用join連接，并且通過on添加連接條件，語義性更強(qiáng)！
連接條件和篩選條件進(jìn)行了分離，提高維護(hù)性和分離性！
特點(diǎn)：
1、多表連接時，一般為表起別名，提高語句的簡潔性
a 、別名要短于 表名
b 、一旦為表起了別名，則只能使用別名限定，不能使用表名限定了!
2、表是否可以調(diào)換順序
答案：可以！不分主次表！
3、等值連接查詢，查詢的結(jié)果為兩個表的交集部分
4、n表連接，至少需要n-1個連接條件

#案例：查詢部門中員工個數(shù)>10的部門名，并按部門名降序
SELECT COUNT(*) 員工個數(shù),department_name
FROM employees e
INNER JOIN departments d ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING 員工個數(shù)>10
ORDER BY department_name DESC;

#二、非等值連接

SELECT FLOOR(RAND()*77+1) 隨機(jī)名字;
#案例：查詢員工的姓名、工資、工資級別
SELECT last_name,salary,grade
FROM employees e
JOIN sal_grade g
ON  e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`;

#三、自連接

#案例：查詢員工號為165的領(lǐng)導(dǎo)的名字和領(lǐng)導(dǎo)工資
SELECT m.last_name,m.salary
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`employee_id` = 165;

#外連接

語法：
select 查詢列表
from 表 別名1
left|right|full 【outer】 join 表 別名2
on 別名1.關(guān)聯(lián)列 = 別名2.關(guān)聯(lián)列
WHERE 篩選條件
GROUP BY 分組
HAVING 分組后條件
ORDER BY 條件;
功能：查詢主表中的所有記錄，如果從表有和主表匹配的信息，則顯示匹配信息。否則顯示null
一般適合查詢主表中有，但從表中沒有的記錄
外連接的結(jié)果=內(nèi)連接結(jié)果+主表有從表沒有的！
特點(diǎn)：
1、左連接 left join，左邊的表就是主表
右連接 right join，右邊的表就是主表
從一定角度上講，左連接和右連接可以通過調(diào)換兩表順序，最終實現(xiàn)同樣的效果！

#案例1 ：查詢哪個部門沒有員工
#左連接：
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#右連接
SELECT d.*
FROM   employees e
RIGHT JOIN departments d ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#案例2：查詢城市名包含a字符的哪個城市沒有部門，并按城市名降序
SELECT city
FROM locations l
LEFT JOIN departments d ON l.`location_id` = d.`location_id`
WHERE city LIKE '%a%'
AND d.`department_id` IS NULL
ORDER BY city DESC;
#右連接
SELECT city
FROM departments d
RIGHT JOIN locations l  ON l.`location_id` = d.`location_id`
WHERE city LIKE '%a%'
AND d.`department_id` IS NULL
ORDER BY city DESC;