mysql学习第三天练习（多表连接）

-- 多表连接
-- 写一条查询语句，查询员工姓名、部门名称、工作地点
select ename,dname,loc
from emp,dept 
where emp.deptno = dept.deptno

-- 练习1
-- 1、写一个查询，显示所有员工姓名、部门编号、部门名称
select ename,dept.deptno,dname
from emp,dept
where emp.deptno = dept.deptno
-- 2、写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工
-- 姓名，工作地点，奖金
select ename,loc,comm 
from emp,dept
where emp.deptno = dept.deptno
and loc = 'CHICAGO'
and comm is not null
-- 3、写一个查询，显示所有姓名中含有A字符的员工姓名、工作地点
select ename,loc 
from emp,dept 
where emp.deptno = dept.deptno 
and ename like '%A%'


-- 查询每个员工的姓名，工资，工资等级
select ename,sal,grade
from emp,salgrade
where sal>=losal
and sal<=hisal

select ename,sal,grade
from emp,salgrade
where sal between losal and hisal

-- 练习2
-- 1、查询每个员工的编号，姓名，工资等级，按照工资等级进
-- 行升序排列
select empno,ename,grade,loc
from emp,dept,salgrade 
where emp.deptno = dept.deptno 
and sal>=losal
and sal<=hisal
order by grade

-- 2、自身连接 
-- 查询每个员工的姓名和直接上级姓名
select t1.ename 员工姓名,t2.ename 直接上级姓名
from emp t1,emp t2
where t1.mgr = t2.empno


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

-- 3、交叉连接
select *
from emp
cross join dept

-- 4、自然连接
select *
from emp
natural join dept

-- 5、using子句
select *
from emp
join dept
using(deptno)

-- 6、on子句
select *
from emp
join dept
on emp.deptno = dept.deptno
join salgrade 
on sal between losal and hisal
where emp.deptno = 10

-- 查询员工及所在部门信息，要求把没有员工的部门也显示出来
select *
from emp
join dept
on emp.deptno = dept.deptno

select *
from emp,dept
where emp.deptno = dept.deptno


-- 7、查询员工及所在部门信息，要求把没有员工的部门也显示出来
select *
from dept
left join emp
on dept.deptno = emp.deptno


select *
from emp
right join dept
on emp.deptno = dept.deptno


-- 查询员工的姓名及上级的姓名，要求把没有上级的员工姓名也显示出来
select t1.ename 姓名,t2.ename 经理姓名
from emp t1
left join emp t2
on t1.mgr = t2.empno


-- 练习4
-- 使用sql-99写法，完成如下练习
-- 1、创建一个员工表和部门表的交叉连接
select *
from emp
cross join dept
-- 2、使用自然连接，显示入职日期在80年5月1日之后的
-- 员工姓名、部门名称、入职日期
select ename,dname,hiredate
from emp
natural join dept
where hiredate >= '1980-05-01'
-- 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 sal between losal and hisal
where loc = 'CHICAGO'
-- 5、使用左连接，查询每个员工姓名，经理姓名，没有经历的king也要显示出来
select e.ename,m.ename
from emp e
left join emp m
on e.mgr = m.empno