#連接查詢
概念：查詢的字段來自于多個表
語法：
select字段1，字段2
from表1，表2
【where連接條件】
笛卡爾乘積：
產(chǎn)生原因：沒有加連接條件箱舞，導(dǎo)致結(jié)果為表1的行數(shù)*表2 的行數(shù)
解決方法：添加上有效的`myemployees`
分類：
一、傳統(tǒng)模式的多表連接
等值連接(where)——非等值連接
二、sql99推出的標(biāo)準(zhǔn)朋截，使用join關(guān)鍵字實現(xiàn)連接
內(nèi)連接——外連接
三、自連接
#一吧黄、傳統(tǒng)模式的多表連接
#也叫等值連接
特點有
①表的順序沒有要求
②n表連接部服，至少需要n-1個連接條件
③一般需要為表起別名，這樣可以提高語句簡潔度拗慨，并且防止字段有歧義
④可以添加分組廓八、排序、篩選赵抢，一起混合使用
多表查詢
#①案例1：查詢員工名剧蹂、部門名
SELECT employees.last_name,departments.department_id
from employees,departments
WHERE? employees.department_id = departments.department_id
#②為表起別名
SELECT e.last_name,d.department_id
from employees e,departments d
WHERE? e.department_id = d.department_id
SELECT e.last_name,d.department_name
FROM employees? e,departments d
WHERE e.department_id=d.department_id;
#③添加篩選條件
#案例：查詢 工資>5000的工種名和員工名、工資
SELECT job_title,last_name,salary
from employees e,jobs j
WHERE e.job_id = j.job_id and salary > 5000
order by salary desc
#④添加分組和篩選
#01案例：查詢每個部門的員工個數(shù)和部門名
SELECT count(*),department_name
from employees e,departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id
#⑤排序
#01案例：查詢每個部門的擁有5個員工以上員工個數(shù)和部門名,且逆序
SELECT count(*),department_name
from employees e,departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id
HAVING count(*) > 5
ORDER BY count(*) DESC
#⑥ 三表連接
#案例：查詢員工名烦却、部門名和所在城市
SELECT e.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
#1.顯示所有員工的姓名宠叼，部門號和部門名稱。
SELECT e.last_name,e.department_id,d.department_name
from employees e,departments d
WHERE e.department_id = d.department_id
#2.查詢90號部門員工的job_id和90號部門的location_id
SELECT e.job_id,location_id,e.department_id
from employees e,departments d
WHERE e.department_id = d.department_id
and e.department_id = 90
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal? INT,
highest_sal INT);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
#2.非等值連接
#案例1：查詢員工的工資以及對應(yīng)的工資級別
SELECT salary,grade_level
FROM
employees e,
job_grades j
WHERE
e.salary BETWEEN j.lowest_sal
AND j.highest_sal
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#案例1 ：查詢員工名、部門名
select last_name,department_name from employees e
inner join departments d on e.department_id = d.department_id
#案例2：查詢有獎金的員工名冒冬、部門名
select last_name,department_name from employees e
inner join departments d on e.department_id = d.department_id
where commission_pct is not null
#案例3：查詢城市名伸蚯、員工名和部門名
SELECT city,last_name,department_name
FROM employees e
inner join departments d on e.department_id = d.department_id
inner join locations l on l.location_id = d.location_id
#案例1：查詢沒有男朋友的女神名稱
use girls
select b.name from beauty b
left outer join boys o on b.boyfriend_id = o.id
#案例2：查詢哪個城市沒有部門
select l.city from locations l
left outer join departments d on l.location_id = d.location_id
where d.department_id is null
#三、自連接
#案例：查詢員工名和上級領(lǐng)導(dǎo)的名字
select e.last_name,m.manager_id from employees e
inner join employees m on e.employee_id = m.manager_id
where m.manager_id is not null
#三简烤、查詢部門名為SAL或IT的員工信息
select e.*,d.department_name,d.department_id
from employees e
left join departments d on e.department_id = d.department_id
where d.department_name in(SAL,IT)
SELECT e.*,d.department_name,d.department_id
FROM employees e
RIGHT JOIN departments d ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN('SAL','IT');
#四剂邮、選擇指定員工的姓名，員工號横侦，以及他的管理者的姓名和員工號挥萌，結(jié)果類似于下面的格式
/*
employees Emp# manager ? Mgr#
kochhar 101 ? ? king ? 100
*/
SELECT e.last_name employees,
e.`employee_id` "Emp#",
m.last_name manager,
m.employee_id "Mgr#"
FROM employees e
INNER JOIN employees m ON e.`manager_id`=m.employee_id
WHERE e.`last_name`='kochhar';
