多表连接初步
引出
•思考如下问题？
–写一条查询语句，查询员工姓名、部门名称、工作地点？
写一个查询显示员工姓名 员工编号 部门名称 地址
笛卡尔积
selectemp.ename,emp.empno,emp.deptno,dept.deptno,dept.dname
from emp,dept
order by ename
--上述情况叫笛卡尔积现象
等值连接
利用关联连接相等漏选掉多余数据
--多表连接实际上是利用意义相同的字段相同来漏选掉多余数据
select ename,empno,dname
from emp,dept
where emp.deptno=dept.deptno
order by ename
限制歧义别名
用 表名.列名
selectename,empno,emp.deptno,dept.deptno,dname
from emp,dept
where emp.deptno=dept.deptno
order by ename
练习1：
•1.写一个查询，显示所有员工姓名，部门编号，部门名称。
•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 INSTR(ename,'a')>0;
SELECT ename,loc FROM emp,dept
WHERE emp.deptno=dept.deptno
and ename like '%A%';
表别名
可以为表定义别名,上面的语句可以精简为：
两种写法 tablename as 别名 |tablename 别名
SELECT ename,loc,e.deptno
FROM emp as e,dept as d
WHERE e.deptno=d.deptno
and ename like '%A%';
多于两个表的连接及非等值连接
查询员工编号，员工姓名，工作岗位名称，工作地点，月工资，薪资等级
select e.empno,e.ename,e.job,d.LOC,e.sal,s.GRADE
from emp e,dept d,salgrade s
where e.deptno=d.DEPTNO
and e.sal BETWEEN s.LOSAL and s.HISAL;
多表连接的写法
1、分析查询结果都来自哪些表 产生from子句
2、分析表之间的关联条件 产生where子句（注意重复字段问题）
如果还有其他的限制条件上，就继续向where子句中追加
3、如果涉及排序的，把order by写上
4、分析要显示哪些字段 产生select语句
自身连接
•思考：查询每个员工的姓名和直接上级姓名？（把自己分身，利用表别名）
查询每个员工的姓名和直接上级姓名？
这种自已跟自己连接的写法叫自身连接
select e.ename 员工姓名,m.ENAME 主管姓名
from emp e,emp m
where e.mgr=m.empno;
sql99写法
美国国家标准协会（ANSI）的SQL：1999标准的连接语法。
交叉连接（笛卡尔积）
select e.,d.
from emp e,dept d;
sql99写法
select e.,d.
from emp e
cross join dept d;
例子：
a表 学生表(a,b,c,c)
b表 课程（java,oracle,jquery,c）
每个班的学生的选课系统
NATURAL JOIN 自然连接(了解，两种写法对比写)
缺点：无法控制用哪些字段连接。
两个表里字段名相同的，自动加到连接条件里。
select e.,d.
from emp e,dept d
where e.deptno=d.deptno;
等价sql99写法
select e.,d.
from emp e
NATURAL join dept d;
自然连接时是利用字段名称和类型都相同的字段进行连接。
用using子句(建议了解)
（可以控制用哪些字段进行连接）
用using子句的写法，指定用哪个字段将两个表连接起来。
select e.,d.
from emp e
join dept d using (deptno);
外连接(重点掌握)
引出：查询所有员工的姓名及其主管姓名，没有主管的也要显示出来
左外连接
（以左边为基准，显示左边所有数据，如果右边没有对应数据，就补充一个空数据）
from emp e,emp m
查询所有员工的姓名及其主管姓名，没有主管的也要显示出来
select e.ENAME,m.ENAME
from emp e
LEFT OUTER JOIN emp m
on e.mgr=m.empno;
结果是：没有主管的也能显示出数据
右外连接
查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来
select e.ENAME,d.DEPTNO,d.DNAME
from emp e
right join dept d
on e.deptno=d.deptno;
where连接条件注意事项
–在 WHERE子句中书写连接条件。
–如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。
–N个表相连时，至少需要N－1个连接条件。
练习4
•使用SQL-99写法，完成如下练习
•1.创建一个员工表和部门表的交叉连接。
select e.,d.
from emp e cross join dept d;
2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
select e.ENAME,d.DNAME,e.HIREDATE
from emp e
NATURAL join dept d
where e.hiredate>'1980-05-01';
作业
练习2
•1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select empno,ename,sal,salgrade.grade,dept.loc
from emp,dept,salgrade
where emp.deptno=dept.deptno
and sal between losal and hisal
order by salgrade.grade
练习3
•1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
select e.ename,e.empno,m.ename,m.empno,loc
from emp e,emp m,dept d
where e.mgr = m.empno
and e.deptno=d.deptno
and (loc = 'NEW YORK' or loc = 'CHICAGO')
练习4
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
•6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
课后作业
3.显示员工KING和FORD管理的员工姓名及其经理姓名。
select e.empno,e.ename,m.ename
from emp e,emp m
where
(e.mgr=m.empno and e. mgr in(select empno from emp where ename in('KING','FORD') ))
网友评论