Mysql-多表连接-练习题3

01.内连接-等值连接

1.写一个查询，显示所有员工姓名，部门编号，部门名称。

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

2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select e.ename,d.loc,e.comm from emp e,dept d where d.loc ='CHICAGO' and e.comm is not null

3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

select e.ename,d.loc from emp e,dept d where ename like '%A%' and e.deptno=d.deptno

4.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

select e.deptno,e.ename,e.sal,s.grade,d.loc from emp e,dept d,salgrade s where e.deptno =d.deptno and e.sal BETWEEN s.losal and s.hisal order by s.grade ASC

02.内连接-自身连接

每个员工及对应领导的名字

-- 员工表里的经理编号等于经理表的员工编号
select w.ename 员工编号,m.ename 经理编号 from emp w,emp m where w.mgr = m.empno

5.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

select w.ename as 员工姓名 ,w.empno as 员工编号 ,m.ename as 经理姓名,m.empno as 经理编号 
from emp w,emp m,dept d 
where w.deptno = d.deptno and w.mgr = m.empno and  d.loc in('NEW YORK','CHICAGO')

03.内连接-交叉连接 CROSS JOIN（笛卡尔积）

-- 交叉连接 CROSS JOIN
select * from emp CROSS join dept;

04.内连接-自然连接 NATURAL JOIN

select * from emp NATURAL JOIN dept;
-- 等价于
select * from emp,dept where emp.deptno =dept.deptno

05.内连接-USING 子句

如果两个表的关联字段名是一样的，就可以使用Using来建立关系，简洁明了。如果不一样，只能用On了哦~

-- 比自然连接更具体指向那一列
select * from emp join dept USING(deptno);

06.内连接-join on 子句

select e.ename,d.loc,e.sal from emp e join dept d on(e.deptno =d.deptno) where sal>1500;

以上都是内连接：符合连接条件数据查询出来，都叫内连接
外连接：符号连接条件查询出来，不符合连接条件也查询出来

07.外连接（左外连接，右外连接，全外连接）

查询所有部门下的员工信息

dept表

emp表

40号部门底下没有员工，不能说明40号部门不存在！！所以只能用外连接，外连接分为3种情况!

左外链接(重点) `LEFT JOIN 或者 LEFT OUTER JOIN`

-- 左外连接，左表会把全部数据显示出来（不符合也显示出来），右表把符合连接条件数据显示出来
select * from  dept  LEFT  JOIN emp on(emp.deptno =dept.deptno)

左外链接

右外链接 `RIGHT JOIN .. on 或者 RIGHT OUTER JOIN ..on`

-- 右外连接  OUTER JOIN
select * from  emp  RIGHT  JOIN dept on(emp.deptno =dept.deptno)

右外连接

全外链接 `full JOIN` （Mysql 没有，Orcale有）

select * from  dept  full JOIN emp on(emp.deptno =dept.deptno)

使用SQL-99写法，完成如下练习

6.创建一个员工表和部门表的交叉连接。

select * from emp CROSS join dept;

7.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

select emp.ename,dept.dname,emp.hiredate 
from emp NATURAL JOIN dept 
where emp.hiredate>'1980-05-01';

8.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

select emp.ename,dept.dname,dept.loc 
from emp join dept USING(deptno) 
where loc='CHICAGO';

9.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级 三表连接

select e.ename,d.dname,d.loc,s.grade from emp e 
join dept d on(e.deptno =d.deptno)
join salgrade s on(e.sal BETWEEN s.losal and s.hisal)
where d.loc='CHICAGO'

10.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select w.ename as 员工姓名, m.ename as 经理编号 
from emp w LEFT JOIN emp m 
on w.mgr = m.empno

11.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select w.ename as 员工姓名, m.ename as 经理编号 
from emp m RIGHT JOIN emp w 
on w.mgr = m.empno

12.显示员工SMITH的姓名，部门名称，直接上级名称

select w.ename as 员工表,m.ename as 经理表,d.dname as 部门名称 
from emp w LEFT JOIN emp m on(w.mgr = m.empno)  LEFT JOIN dept d on(w.deptno =d.deptno)
where w.ename ='SMITH'

13.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

select e.ename,d.loc,e.sal,s.grade 
from emp e LEFT JOIN dept d on(e.deptno =d.deptno) LEFT JOIN salgrade s on(e.sal BETWEEN losal and hisal)
where s.grade >4

14.显示员工KING和FORD管理的员工姓名及其经理姓名

select w.ename as 员工姓名,m.ename as 经理姓名 from emp w LEFT JOIN emp m on(w.mgr = m.empno
where  m.ename in('KING','FORD')

15.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

-- 左外链接
select w.ename as 员工姓名,w.hiredate as 员工参加工作时间, m.ename as 经理姓名, m.hiredate as 经理参加工作时间

from emp w LEFT JOIN emp m on(w.mgr = m.empno)

where w.hiredate < m.hiredate


-- 自连接
select  e.ename 员工姓名,e.HIREDATE 员工参加工作时间,m.ENAME 经理姓名,m.HIREDATE 经理参加工作时间

from emp e,emp m

where e.mgr=m.empno

and e.HIREDATE<m.HIREDATE