mysql语法太难记住了_MySQL难点语法——连接

本篇涉及的数据表格可以自行查阅上篇《MySQL难点语法——子查询》

MySQL的数据表格之间有三种连接方式：等值连接、外连接、自连接。以下是通过举例介绍这三种连接方式

1、等值连接

等值连接一般适用于主表和外表的连接(主表的主键和外表的外键相等)

需求：1.查看所有员工的所在部门的名称

selects_emp.id 员工,last_name 员工名称,s_dept.id 部门编号, name 部门名称froms_dept, s_empwhere s_dept.id = s_emp.dept_id;

需求：2.查询平均工资大于1200的部门,并显示这些部门的名字

selects_dept.id, namefroms_emp, s_deptwhere s_emp.dept_id=s_dept.idgroup bydept_idhaving avg(salary) > 1200;

需求：3.查看所有员工的所在区域名称

selectlast_name, s_dept.id, s_region.namefroms_emp, s_dept, s_regionwhere s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id

需求：4.查看员工的id，last_name，salary，部门名字，区域名字，​这些员工有如下条件：薪资大于Chang所在区域的平均工资或者跟Chang员工不在同个部门​

selects_emp.id, last_name, salary, s_dept.name, s_region.namefroms_emp, s_dept, s_regionwhere s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.idand (salary >( # chang所在区域的部门的平均工资select avg(salary)froms_empwhere dept_id in( # chang所在区域的部门selectidfroms_deptwhere region_id=( # Chang所在的区域selectregion_idfroms_emp, s_deptwhere s_emp.dept_id=s_dept.id and last_name="chang"

)

)or last_name !="chang")

);

需求：5.查看员工工资高于所在部门平均工资的员工的id和名字

selects_emp.id, last_namefroms_emp, s_dept, ( # 每个部门的平均薪资表select dept_id, avg(salary) asavg_salaryfroms_empgroup by dept_id) asavg_tablewhere s_emp.dept_id=s_dept.idand s_dept.id=avg_table.dept_id # 部门编号与部门薪资表连接and salary > avg_salary;

需求：6.查询工资大于41号部门平均工资的员工,并且该员工所在部门的平均工资也要大于41号部门的平均工资,显示出当前员工所在部门的平均工资以及该员工所在部门的名字​

selecte.dept_id, n.avg_salary, d.namefrom s_emp as e, s_dept asd, ( # 部门薪资表selectdept_id,avg(salary) asavg_salaryfrom s_emp group by dept_id) asnwhere e.dept_id=d.id and d.id=n.dept_idand n.dept_id != 41# 大于就不存在41号部门and salary >( # 大于41号部门的平均薪资selectavg_salaryfrom( # 部门薪资表selectdept_id,avg(salary) asavg_salaryfrom s_emp group by dept_id) asnwhere dept_id =41)

2、外连接

外连接又可以分为两种连接方式：左连接、右连接

左连接： 表1 left join 表2 ... on 表与表的关联关系，on后面接的是某个条件，不一定是两个表的等值关系(表1数据是完整的)

需求：查看客户名以及其对应的销售人员名

selectc.name, e.last_namefrom s_customer as c left join s_emp ase # 左连接on e.id=c.sales_rep_id;

右连接： 表1 right jion 表2 ....on 表与表的关联关系，on后面接的是某个条件，不一定是两个表的等值关系(表2的数据是完整的)

需求：查看客户名以及其对应的销售人员名

selectc.name, e.last_namefrom s_emp as e right join s_customer asc # 右连接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 asEmployeefrom Employee asawhere salary >(selectSalaryfrom Employee asbwhere b.Id =a.ManagerId

);