1.什么是连接查询?(b)
很多时候我们需要查询的数据并不是来源于同一张表,而是来源于多张表,而这种一个查询需要对多张表进行操作,就成为连接查询。
2.如何进行表的连接查询?
连接查询有两种方式:sql 1992(1992年发布的标准) 和sql 1999(1999年发布的标准)
3.sql1992:(a)
3.1 语法:
select table1.column, table2.column
from table1, table2
where table1.column1 = table2.column2;
注:1.在 where 子句中写入连接条件
2.在 where 子句中写入连接条件当多个表中有重名列时,必须在列的名字前加上表名作为前缀(可使用表别名)
3.2 连接类型:
1.笛卡尔积 (表*表)
例: select * from emp,dept;
emp表中的每一条数据会分别与dept表的每一条数据连接
2.等值连接 (列=列)
例:查询员工的部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno
3.非等值连接 (列!=列)
–查询员工的薪水等级
select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal
4.自连接 (表自己连接自己)
–查询员工上司的姓名:以”xxx的上司是xxx”方式显示
select e1.ename||’的上司是’|| e2.ename from emp e1,emp e2 where e1.mgr = e2.empno
5.外连接 (在满足一张表的内容都显示的基础上,连接另外一张表,如果连接匹配则正常显示,连接不匹配,另外一张表补null)
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno (+)
“(+)” 在等号右边,我们称这次查询为“左外连接查询”
“(+)” 在等号左边,我们成这次查询为“右外连接查询”
“(+)” 在等号哪一边,哪张表就补null
3.3sql 1992 缺点:
1.表的连接条件和表的过滤条件都放在where子句中,当条件过多时容易混淆
2.外连接的“左外连接查询”、“右外连接查询”不太容易辩别
4.sql1999(a)
4.1 语法:
select table1.column, table2.column from table1 连接类型 join table2 on 连接条件
在sql1999 中弥补了sql1992的缺点,使用 join关键字 连接 on关键字进行连接条件判断,where进行过滤。并且外连接的“左外连接查询”、“右外连接查询”分别使用left outer join、right outer join
4.2 连接类型:
1.cross join 交叉连接(笛卡尔积)
select * from emp cross join dept
2.natural join 自然连接,找到两张表中具有相同名称列,进行等值连接
select * from emp natural join dept
3.inner join 内连接 (inner关键字可以省略)
–等值连接
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno where e.sal > 2000
–非等值连接
select e.ename,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal
–自连接
select e1.ename || ‘的上司是’ || e2.ename from emp e1 inner join emp e2 on e1.mgr = e2.empno
–外连接(outer关键字可以省略)
4.left outer join
select * from emp e left outer join dept d on e.deptno = d.deptno
5.right outer join
select * from emp e right outer join dept d on e.deptno = d.deptno
6.full outer join
select * from emp e full outer join dept d on e.deptno = d.deptno
5.如何进行多表连接?(a)
例:
--sql 1992 select e.ename, e.sal, d.dname, s.grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal --sql 1999 select e.ename, e.sal, d.dname, 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
6.子查询(a):
6.1 什么是子查询?
在sql中允许多层嵌套。而子查询,即嵌套在其他查询中的查询。
理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。
–子查询要用括号括起来
–将子查询放在比较运算符的右边(增强可读性)
6.2 子查询的种类(a)
1.在where中使用的子查询
--查询部门名称为research、sales select * from emp where deptno in (select deptno from dept where dname in ('research', 'sales')) --查询有哪些人的薪水是在整个雇员的平均薪水之上的 select ename,empno, sal, sal+nvl(comm,0) from emp where sal+nvl(comm,0)>(select avg(sal+nvl(comm,0)) from emp); --查在雇员中有哪些人是经理人 select empno, ename from emp where empno in ( select distinct mgr from emp ); --找出部门编号为20的所有员工中收入最高的职员 select * from emp where sal >= all( select sal from emp where deptno = 20) and deptno = 20
2.在from后面使用子查询
--我们要求每个部门平均薪水的等级 select t1.deptno, t1.savg, s.grade from (select deptno, avg(sal) savg from emp group by deptno) t1 join salgrade s on t1.savg between s.losal and s.hisal --求每个部门薪水的平均等级 select t1.deptno, avg(t1.grade) from (select e.deptno, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal) t1 group by t1.deptno