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