多表链接
连接是在多个表之间通过一定的连接条件,使表之间发生关联,进而能从多个表之间获取数据。
语法为
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
笛卡尔积: 第一个表中的所有行和第二个表中的所有行都发生连接。
为了避免笛卡尔积的产生,通常需要在WHERE子句中包含一个有效的连接条件
接下来我们详细学习这些连接条件:
等值连接
select * from emp,dept where emp.deptno=dept.deptno;
image
image
select empno, ename , emp.deptno, loc from emp, dept where emp.deptno=dept.deptno and loc = 'NEW YORK';
限制歧义列名
在用到多个表时可以使用表名作前缀来限定列;
通过使用表前缀可以提高性能;
select emp.empno, emp.ename , emp.deptno, dept.loc from emp, dept where emp.deptno=dept.deptno and loc = 'NEW YORK';
通过使用列的别名可以区分来自不同表但是名字相同的列
select e.empno as 员工编号, e.ename 姓名,d.dname 部门名称
from emp e, dept d where e.deptno=d.deptno;
image
练习:
写一个查询,显示所有员工姓名,部门编号,部门名称。
select e.ename,e.empno,e.job from emp e,dept d where e.deptno = d.deptno;
写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
select e.ename,d.loc,e.comm from emp e,dept d where e.deptno = d.deptno and d.loc = 'CHICAGO' and comm is not null;
写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
select e.ename,d.loc from emp e,dept d where e.deptno = d.deptno and e.ename like '%A%';
非等值连接
image
select e.e
name 员工姓名, e.sal 工资 , s.grade 工资等级 from emp e, salgrade s where e.sal between s.losal and s.hisal;
两个表以上的链接
image
多表连接的写法分析
分析要查询的列都来自于哪些表,构成FROM子句;
分析这些表之间的关联关系,如果表之间没有直接关联关系,而是通过另一个中间表关联,则也要在FROM子句中补充中间关联表;
接下来在WHERE子句中补充表之间的关联关系,通常N个表,至少要有N-1个关联关系;
分析是否还有其它限制条件,补充到WHERE子句的表关联关系之后,作为限制条件;
根据用户想要显示的信息,补充SELECT子句。
分析是否有排序要求,如果排序要求中还涉及到其它表,则也要进行第2步补充排序字段所在的表,并且添加表之间的关联关系;
select e.empno 编号,e.ename 姓名,e.sal 员工薪水,s.grade 工资等级,d.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;
image
自连接
查询每个员工的姓名和直接上级姓名
select w.ename 员工姓名, m.ename 领导姓名 from emp w, emp m where w.mgr = m.empno;
image
select w.ename 员工姓名 ,w.empno 员工编号,m.ename 领导姓名,m.empno 领导编号 from emp w,emp m,dept d where w.mgr = m.empno and d.deptno = w.deptno and d.loc in('NEW YORK','CHICAGO');
交叉连接
创建一个员工表和部门表的交叉连接。
select e.empno, e.ename, d.dname from emp e cross join dept d;
自然连接
select e.empno, e.ename, d.dname from emp e natural join dept d;
相比于等值连接,等值连接直接指明连接条件, 自然连接是自己找(两个表字段名相同,自动添加), 自然连接无法控制用哪些字段连接
外连接
左外链接
左外连接以FROM子句中的左边表为基表,该表所有行数据按照连接条件无论是否与右边表能匹配上,都会被显示出来。
查询所有雇员姓名,部门编号,部门名称,包括没有部门的员工也要显示出来
select e.ename,e.deptno, d.dname from emp e left outer join dept d on(e.deptno = d.deptno);
image
右外链接
查询所有雇员姓名,部门编号,部门名称,包括没有员工的部门也要显示出来
select e.ename, e.deptno, d.dname from emp e right outer join dept d on (e.deptno = d.deptno);
image
创建一个员工表和部门表的交叉连接。
select e.ename,d.dname from emp e cross join dept d;
使用自然连接,显示入职日期在80年5月1日之后的员工姓名,部门名称,入职日期
select e.ename,d.dname,e.hiredate from emp e natural join dept d where e.hiredate>'1980-05-01';
使用左连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
select w.ename 员工,m.ename 经理 from emp w left outer join emp m on (w.mgr = m.empno);
使用右连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
select w.ename 员工,m.ename 经理 from emp m right outer join emp w on (w.mgr = m.empno);
显示员工SMITH的姓名,部门名称,直接上级名称
select e.ename 员工姓名, d.dname 部门名称, h.ename 上级名称 from emp e, emp h,dept d where e.deptno=d.deptno and e.mgr=h.empno and e.ename='SMITH';
显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级。
select e.ename 员工姓名, d.dname 部门名称, e.sal 工资,s.grade 工资等级 from emp e, salgrade s,dept d where e.deptno=d.deptno and e.sal between s.losal and s.hisal and s.grade>4;
显示员工KING和FORD管理的员工姓名及其经理姓名。
SELECT w.ename 员工姓名,m.ename 领导姓名 FROM emp w,emp m WHERE w.mgr = m.empno AND m.ename in ('KING','FORD');
显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加时间比经理早。
SELECT w.ename 员工姓名,w.hiredate 参加工作时间,m.ename 经理名,m.hiredate 参加工作时间 FROM emp w,emp