mysql多表连接查询

#查询姓名 部门名 地点
select emp.ename ,dept.dname,dept.LOC
from emp,dept
where emp.deptno=emp.deptno

#简化
select ename ,dname,loc,emp.deptno
from emp,dept
where emp.deptno=dept.deptno

#注意：多表查询是不同列名可以省略表名，多个表的相同列必须指定 表名.列名

n个表查询，至少有n-1个连接条件

#优化：表别名:一旦起表别名，就必须用别名来代替原来的表名

select ename ,dname,loc,e.deptno
from emp e,dept d
where e.deptno=d.deptno

#等值连接
查询工作地点在NEW YORK的员工编号，姓名，部门编号，工作地点
select empno,ename,e.deptno,loc
from emp e,dept d
where e.deptno=d.deptno and loc=‘NEW YORK’

#不等值连接
查询每个员工的姓名，工资，工资等级
select ename,sal,grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal

#练习
1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，
按照工资等级进行升序排序。
select empno,ename,sal,grade,loc
from emp e,salgrade s,dept d
where e.deptno=d.deptno and e.sal BETWEEN s.losal and s.hisal
order by s.grade

#自身连接
SELECT worker.ename 员工名,manager.ename 直接领导名
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;

#外连接：左外、右外
SELECT worker.ename 员工名,manager.ename 直接领导名
FROM emp worker
LEFT JOIN emp manager
on worker.mgr = manager.empno;

#练习
查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，
经理编号
select w.ename ‘员工名’ ,w.empno ‘员工编号’, m.ename ‘经理名’, m.empno ‘经理编号’
from emp w,emp m,dept
where w.mgr=m.empno and w.deptno=dept.deptno
and loc in(‘NEW YORK’,‘CHICAGO’)

#查询员工姓名 部门名
select ename ,dname
from emp,dept
where emp.deptno=dept.deptno

等价于

select ename ,dname
from emp join dept
on emp.deptno=dept.deptno

#外连接：左右表记录不匹配时，以左表还是右表信息为准？

#左外连接（显示左边表的符合条件的记录）
查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

select ename ,e.deptno,dname
from emp e left join dept
on e.deptno=dept.deptno

#右外连接（显示右边表的符合条件的记录）
查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来

select ename ,dept.deptno,dname
from dept left join emp e
on e.deptno=dept.deptno

等价于

select ename ,dept.deptno,dname
from emp e right join dept
on e.deptno=dept.deptno

1.创建一个员工表和部门表的交叉连接。
select e.,d.
from emp e cross join dept d
2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
select ename,dname,hiredate
from emp natural join dept

等价于
select ename,dname,hiredate
from emp ,dept
where emp.deptno=dept.deptno

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
select ename,dname,loc
from emp join dept using(deptno)
where loc=‘CHICAGO’

4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
select ename,dname,loc,grade
from emp join dept
on emp.deptno=dept.deptno
join salgrade
on emp.sal between losal and hisal
where loc=‘CHICAGO’

等价于
select ename,dname,loc,grade
from emp , dept,salgrade
where emp.deptno=dept.deptno and
emp.sal between losal and hisal and
loc=‘CHICAGO’
5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
select w.ename , m.ename
from emp w left join emp m
on w.mgr=m.empno

6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
select w.ename , m.ename
from emp m right join emp w
on w.mgr=m.empno

1.显示员工SMITH的姓名，部门名称，直接上级名称
select w.ename ‘员工名’ ,dname ‘部门名’,m.ename ‘经理名’
from emp w,dept d,emp m
where w.deptno=d.deptno and w.mgr=m.empno and w.ename=‘SMITH’

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
select ename,dname,sal,grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal BETWEEN s.losal and s.hisal
and s.grade>4

3.显示员工KING和FORD管理的员工姓名及其经理姓名。
select w.ename ‘员工名’ ,m.ename ‘经理名’
from emp w,emp m
where w.mgr=m.empno and m.ename in(‘KING’,‘FORD’)

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
select w.ename ‘员工名’ ,w.hiredate ‘员工入职时间’,
m.ename ‘经理名’ ,m.hiredate ‘经理入职时间’
from emp w, emp m
where w.mgr=m.empno and w.hiredate<m.hiredate