MySQL難點語法——連接

本篇涉及的數據表格可以自行查閱上篇《MySQL難點語法——子查詢》

MySQL的數據表格之間有三種連接方式：等值連接、外連接、自連接。以下是通過舉例介紹這三種連接方式

　　1、等值連接

　　　　　　等值連接一般適用於主表和外表的連接（主表的主鍵和外表的外鍵相等）

　　　　　　　　需求：1.查看所有員工的所在部門的名稱

select s_emp.id 員工,last_name 員工名稱,s_dept.id 部門編號, name 部門名稱
from s_dept, s_emp
where s_dept.id = s_emp.dept_id;

　　　　　　　　需求：2.查詢平均工資大於1200的部門,並顯示這些部門的名字

select s_dept.id, name
from s_emp, s_dept
where s_emp.dept_id=s_dept.id
group by dept_id
    having avg(salary) > 1200;

　　　　　　　　需求：3.查看所有員工的所在區功能變數名稱稱

select last_name, s_dept.id, s_region.name
from s_emp, s_dept, s_region
where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id

　　　　　　　　需求：4.查看員工的id，last_name，salary，部門名字，區功能變數名稱字， ​ 這些員工有如下條件：薪資大於Chang所在區域的平均工資或者跟Chang員工不在同個部門 ​

select s_emp.id, last_name, salary, s_dept.name, s_region.name
from s_emp, s_dept, s_region
where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id
and (salary > (         # chang所在區域的部門的平均工資
    select avg(salary) 
    from s_emp
    where dept_id in (        # chang所在區域的部門
        select id
        from s_dept
        where region_id=(         # Chang所在的區域
            select region_id
            from s_emp, s_dept
            where s_emp.dept_id=s_dept.id and last_name="chang"
        )
    ) or last_name != "chang")
);

　　　　　　　　需求：5.查看員工工資高於所在部門平均工資的員工的id和名字

select s_emp.id, last_name
from s_emp, s_dept, ( # 每個部門的平均薪資表
    select dept_id, avg(salary) as avg_salary 
    from s_emp 
    group by dept_id) as avg_table
where s_emp.dept_id=s_dept.id
    and s_dept.id=avg_table.dept_id     # 部門編號與部門薪資表連接
    and salary > avg_salary;

　　　　　　　　需求：6.查詢工資大於41號部門平均工資的員工,並且該員工所在部門的平均工資也要大於41號部門的平均工資,顯示出當前員工所在部門的平均工資以及該員工所在部門的名字 ​

select e.dept_id, n.avg_salary, d.name
from s_emp as e, s_dept as d, (   # 部門薪資表
            select dept_id, 
                avg(salary) as avg_salary 
            from s_emp group by dept_id) as n
where e.dept_id=d.id and d.id=n.dept_id 
and n.dept_id != 41   # 大於就不存在41號部門
and salary > (      # 大於41號部門的平均薪資
    select avg_salary 
    from (     # 部門薪資表
        select dept_id, 
            avg(salary) as avg_salary 
        from s_emp group by dept_id) as n 
    where dept_id =41)

　　　2、外連接

　　　　　　外連接又可以分為兩種連接方式：左連接、右連接

　　　　　　　　左連接： 表1 left join 表2 ... on 表與表的關聯關係，on後面接的是某個條件，不一定是兩個表的等值關係(表1數據是完整的)

　　　　　　　　　　需求：查看客戶名以及其對應的銷售人員名

select c.name, e.last_name
from s_customer as c left join s_emp as e    # 左連接
on e.id=c.sales_rep_id;

　　　　　　　　右連接： 表1 right jion 表2 ....on 表與表的關聯關係，on後面接的是某個條件，不一定是兩個表的等值關係(表2的數據是完整的)

　　　　　　　　　　需求：查看客戶名以及其對應的銷售人員名

select c.name, e.last_name
from s_emp as e right join s_customer as c    # 右連接
on e.id=c.sales_rep_id;

　　　　3、自連接

　　　　　　　　　　顧名思義就是自身表格關聯——表格存在一個外鍵關聯到自身的主鍵，向員工表中就有自己的領導，但領導本身又是一名員工：示例如下

　　　　　　　　　　　　需求：查看所有員工名字以及其對應的經理名字

select id, last_name
from s_emp
where manager_id = id