Oracle数据库入门之多表连接与子查询

Oracle数据库入门之多表连接与子查询

　　Oracle表连接

　　概述：SQL/Oracle使用表连接从多个表中查询数据

　　格式：select 字段列表 from table1,table2 where table1.column1=table2.column2;

　　说明：当被连接的多个表中存在同名字段时，必须在该字段前加上“table.”作为前缀

　　如果没有限定where连接条件，否则就会出现笛卡尔集的不现实或没有实用意义的结果

　　举例：select empno, ename, sal, emp.deptno, dname, loc from emp, dept;--这时得到的就是笛卡尔集。此时得到70条记录

　　select empno, ename, sal, emp.deptno, dname, loc from emp, dept where emp.deptno=dept.deptno;--得到14条记录

　　类型：Oracle8i之前的表连接(支持SQL92标准)：等值连接(Equijoin)、非等值连接(Non—Equijoin)、自连接(Self join)

　　外连接(Outer join)：左外连接、右外连接

　　Oracle9i新引入的连接形式(支持SQL99规范)：交叉连接(Cross join)、自然连接(Natural join)、使用Using或On子句建立连接

　　内连接(Inner join)、外连接(Outer join)：左外连接、右外连接、全外连接

　　补充：多表连接中可使用AND操作符增加查询条件，使用表别名可以简化查询，使用表名(表别名)前缀可提高查询效率

　　而连接n个表，则至少需要n-1个连接条件。如select a.ename, a.deptno, b.dname from emp a, dept b where a.deptno=b.deptno;

　　样本：hr帐户也是Oracle自带的样本帐户，里面有很多样本表，其数据量及复杂程度都要比scott强一些

　　hr帐户缺省是锁定的，可以在OEM中将其解锁并设密码。其中的employees表是scott中的emp表的增强版

　　另外还有departments和locations表的数据看起来都比较真实。这三个表通常用于复杂的多表连接查询

　　等值连接(Equijoin)

　　举例：select empno, ename, emp.deptno, dname from emp, dept where emp.deptno=dept.deptno;

　　非等值连接(Non—Equijoin)

　　问题：如何查得每个员工的工资等级

　　举例：select empno, ename, sal, grade 工资等级 from emp,salgrade where sal between losal and hisal;

　　外连接(Outer join)

　　概述：外连接运算符为(+)。使用外连接可以看到参与连接的某一方不满足连接条件的记录

　　传统的外连接分为左外连接和右外连接两种。实际开发中，外连接的使用频率要比等值连接少一些

　　语法：select 字段列表 from table1,table2 where table1.column1(+)=table2.column2;

　　select 字段列表 from table1,table2 where table1.column1=table2.column2(+);

　　说明：table1.column1(+)=table2.column2;--显示所有符合条件的记录，同时table2中不符合连接条件的记录也会显示出来

　　table1.column1=table2.column2(+);--显示所有符合条件的记录，同时table1中不符合连接条件的记录也会显示出来

　　补充：在SQL99规范中，内连接(Inner Join)只返回满足连接条件的数据，而外连接还返回不满足连接条件的行

　　分类：左外联接(Left Outer Join)：即两个表在连接过程中除返回满足连接条件的行以外，还返回左表中不满足条件的行

　　右外联接(Right Outer Join)：即两个表在连接过程中除返回满足连接条件的行以外，还返回右表中不满足条件的行

　　满外联接(Full Outer Join)：即两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的行

　　举例：select employee_id, last_name, salary, department_id, department_name

　　form employees left join departments using(department_id);--返回107行

　　自连接(Self join)

　　概述：自连接诶本质就是把一个表当作两个表来使用，只是定义不同的别名而已

　　平时很少使用自连接，但有时确实只有采用自连接的方式才能解决某些问题

　　问题：如何查得每个员工及其上司的工号和姓名

　　举例：select a.empno, a.ename, a.mgr, b.ename from emp a, emp b where a.mgr=b.empno;

　　SQL99连接语法

　　概述：SQL1999规范中规定的连接查询语法。两个以上的表进行连接时应依次分别指定相临的两个表之间的连接条件

　　语法：select 字段列表

　　from table1

　　[cross join table2]|

　　[natural join table2]|

　　[join table2 using(字段名)]|

　　[join table2 on(table1.column_name=table2.column_name)]|

　　[(left|right|full outer)join table2 on(table1.column_name=table2.column_name)];

　　[cross join table3]|

　　[natural join table3]|

　　[join table3 using(字段名)]|

　　[join table3 on(table2.column_name=table3.column_name)]|

　　[(left|right|full outer)join table3 on(table2.column_name=table3.column_name)]...;

　　说明：如果感觉新语法比较混乱，在没有强制性要求的时候，完全可以使用旧语法，二者不存在效率上的差异

　　交叉连接(Cross join)

　　概述：Cross join产生了一个笛卡尔集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件

　　举例：select empno, ename, dname from emp a cross join dept b;--交叉连接实际上没有太大的意义

　　自然连接(Natural join)

　　概述：Natural join基于两个表中的全部同名列建立连接。从两个表中选出同名列的值均对应相等的所有行

　　如果两个表中同名列的数据类型不同，则出错。而且不允许在参照列上使用表名或别名作为前缀

　　举例：select demno, ename, sal, deptno, dname from emp natural jon dept;

　　Using子句

　　概述：如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求

　　可以在连接时使用USING子句来设置用于等值连接的列(参照列)名

　　同样不允许在参照列上使用表名或别名作为前缀

　　举例：select empno, ename, sal, deptno, dname form emp join dept using(deptno);

　　On子句

　　概述：如果要参照非同名的列进行等值连接，或想设置任意的连接条件，可以使用ON子句

　　举例：select empno, ename, sal, emp.deptno, dname from emp join dept on(emp.deptno=dept.deptno);

　　子查询(Sub Query)

　　概述：子查询在主查询前执行一次，主查询使用子查询的结果。比如查询所有比张三工资高的员工信息

　　子查询分为单行子查询(返回一行结果)和多行子查询(返回多行结果)两大类

　　语法：select 字段列表 form table where 表达式 operator(select 字段列表 from table);

　　比如select * from emp where sal>(select sal from emp where empno=7654);

　　注意：基于未知值的查询应考虑使用子查询。子查询必须包含在括号内

　　建议将子查询放在比较运算符的右侧，以增强可读性。除非进行Top—N分析，否则不要在子查询中使用ORDER BY子句

　　对单行子查询可以使用单行记录比较运算符。而对多行子查询则只能使用多行记录比较运算符

　　空值：如果子查询未返回任何行，则主查询也不会返回任何结果

　　比如select * from where sal>(select sal from emp where empno=8888);--不会返回任何结果

　　多值：如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

　　比如select * from emp where sal>(select avg(sal) from group by deptno);--非法

　　TopN查询(TopN分析)

　　概述：即获得按照某种规则排序之后的前n条的记录。Oracle中通常采用子查询的方式实现TOPN查询

　　其实子查询可以认为是查到了一个临时表，或没有名字的临时视图

　　语法：select 字段列表 from (select 字段列表 from table order by 排序字段) where rownum<=n;

　　举例：select * from (select * from emp order by sal desc) where rownum <=5;

　　伪列rownum

　　概述：SELECT查询结果中会隐含的增加一个字段rownum，即伪列。rownum用起来很灵活，但也很容易出错

　　rownum伪列并不是数据表中或者子查询的虚拟表中真实存在的列，它只是查询结果中的一个伪列

　　它标记的是符合查询条件的结果的编号，第一条记录的rownum值为1，第二条记录的rownum值为2

　　可以理解为，符合查询条件的第一行记录编号为1，符合查询条件的第二行记录编号为2

　　例一：select * from emp where rownum>=5;--它执行后的结果是没有返回值

　　执行时先取出结果集中的，或者说是数据表中的第一条记录，并标记第一条记录的编号为1

　　判断后得知1小于5，不符合条件。随后便过滤掉这条记录了，接着判断下一条记录是不是符合条件

　　于是就又取出下一条记录，下一条记录的rownum还是从1开始。而rownum永远是从1开始的，结果可想而知

　　接着的下一条记录的编号还是1。即照此情形下去，记录的编号永远不会符合大于等于5的条件

　　也就是说在这条SQL语句的环境下，rownum永远也不会大于等于5。所以就不能指望用rownum进行区间排序

　　也就是说rownum>=5 and rownum <=10是永远也不会成立的。这就是所谓的TopN分析

　　例二：select * from emp where rownum<=5 order by sal desc;

　　该句执行后并不会得到预期的结果。虽然也会得到5条记录，但并不是工资降序排列后的前5个值

　　它返回的是emp表中的前5行记录，只不过显示的时候是按照工资进行降序排列之后的效果

　　执行时会先对where条件进行过滤，过滤后得到了原表中的前5条记录。然后再对表的前5条记录排序并输出

　　很显然这并不是我们想要得到的。我们希望的是先排序，排好了顺序之后再获取前面的5行信息

　　但若写成select * from emp order by sal desc where rownum<=5;的话，是不符合select语法的，会出错

　　所以只能通过子查询的方式在一条语句中结合rownum伪列来实现TopN查询

　　分页：在JavaWeb编程中，经常会遇到分页显示的问题。有时需要在某一页显示一个区间的记录

　　比如显示第21条到第30条记录。在这种情况下，单纯的TopN查询显然不能满足要求

　　这时可以让子查询中的伪列变成一个真实存在的列，或者说让它变成能够进行比较运算的真实的列

　　述一：select rownum, a.* from (select * from emp order by sal desc) a;

　　这里如果将a.*写成*的话，就会出现缺失表达式的错误。而子查询不是真实的表，所以只能靠它的别名

　　它的运行结果是显示原emp中的所有记录，而且还多出了一列ROWNUM的记录，列值是从1到14的连续数字

　　此时的rownum还是虚的，仍然不能执行where rownum>=5 and rownum<=10的区间排序

　　因为rownum实际上是等于本次查询14行记录中的每一行记录的伪列号

　　从第一行开始永远等于1，如果不符合大于等于5的条件的话，第一行记录就会被过滤掉了

　　而下一行记录的伪列号还是从1开始的，便又会出现“例一”中的结果，所以此时仍不能进行区间排序

　　述二：这时可以给rownum起一个别名，如myno。然后再把刚才的整条语句作为一个子查询

　　即select * from (select rownum myno, a.* from (select * from emp order by sal desc) a);

　　整个括号括起来的又充当了一个子查询。这个子查询会得到n+1条记录，其中第一条记录是myno字段

　　这时的myno就是一个实际存在的结果了。如果把子查询当作一个真实的表，这个表中应该有n+1个字段

　　都是真实存在的字段，一个字段叫myno，其它的是原来emp中的所有字段

　　然后在语句中将查询条件where myno>=5 and myno<=10写在该语句的后面。整句就如“模板”中句子

　　这时再执行整条语句，得到的就是预期中的结果，即emp中工资排名在第5到第10名之间的员工信息

　　模板：select * from (select rownum myno, a.* from (select * from emp order by sal desc) a) where myno>=5 and myno<=10;

　　这就是在Oracle中利用TopN查询实现分页显示效果的SQL语句，也可以把当前的语法格式当作一个模板来记住

　　子查询可以当作是一个表，假想这个表是物理存在的，里面有n+1个真实存在的字段，其中一个字段叫myno

　　如果这时把条件改成where rownum>=5 and rownum<=10，那么执行结果是：未选定行或没有选定任何内容

　　因为这个rownum指的根本不是子查询中的返回的结果rownum，而是这一次主查询中又得到的一个伪列