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、自连接

　　　　　　　　　　 Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。：示例如下

+----+-------+--------+-----------+

| Id | Name  | Salary | ManagerId |

+----+-------+--------+-----------+

| 1  | Joe   | 70000  | 3         |

| 2  | Henry | 80000  | 4         |

| 3  | Sam   | 60000  | NULL      |

| 4  | Max   | 90000  | NULL      |

+----+-------+--------+-----------+

　　　　　　　　　　给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+

| Employee |

+----------+

|   Joe    |

+----------+

　　　　　　　　　　　　需求：查看所有员工名字以及其对应的经理名字

# 在查询的时候跟以往的不太一样，这个不是先连接，是先执行外面的语句先
select Name as Employee    
from Employee as a 
where salary > (      
    select Salary 
    from Employee as b 
    where b.Id = a.ManagerId
);