复杂查询语句的使用#复杂查询语句的使用#1.查询语句的使用使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据. 1.1相关子查询可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询 .where中可以包含一个select语句的子查询 .where中可以包含in,exists语句 .最多可以嵌套16层 .层次过多会影响性能 例简单子查询实例 查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目 (按规定只能以一个单位来申请) SQL create table univ_subject 2 ( 3 name varchar2(12) not null, 4 per_id number not null, 5 dept_name varchar2(20) 6 ); SQL insert into univ_subject values(gaoqianjing,1001,信息工程系); SQL insert into univ_subject values(wangbing,1002,物理系); SQL insert into univ_subject values(liming,1003,化学系); = SQL create table colle_subject 2 ( 3 colle_name varchar2(20), 4 per_id number 5 ); SQL insert into colle_subject values(电子研究所,1001); SQL insert into colle_subject values(物理研究所,1005); = SQL select name,per_id,dept_name from univ_subject where per_id in 2 (select per_id from colle_subject); NAME PER_ID DEPT_NAME - - - gaoqianjing 1001 信息工程系1.2外连接 例外连接实例 招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent_skill中同样有该学生 的信息。现在要全部列出所有学生,如果某个学生在表student_skill中就有其特长信息,并显示特长信息,如果 某个学生没有特长就显示特长问空. SQL create table students 2 ( 3 st_id varchar2(20), 4 name varchar2(10), 5 age number(2), 6 tol_score number(3) 7 ) ;SQL insert into students values(973231,wangbindu,22,501);SQL insert into students values(973232,zhuzhijing,21,538);SQL insert into students values(973233,gaojing,21,576);=SQL create table student_skill 2 ( 3 st_id varchar2(20), 4 skill varchar2(20) 5 );SQL insert into student_skill values(973231,篮球);SQL insert into student_skill(st_id) values(973232);SQL insert into student_skill values(973233,足球);=SQL select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+) order by a.st_id;ST_ID NAME AGE TOL_SCORE SKILL- - - - - -973231 wangbindu 22 501 篮球973232 zhuzhijing 21 538973233 gaojing 21 576 足球1.3自我连接自我连接是在同一个表或视图内进行条件连接.例自我连接实例查询每个雇员的名字和该雇员的经理的名字:SQL select e1.ename| work for |e2.ename Employees and their Managers 2 from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;Employees and their Managers-SMITH work for FORDALLEN work for BLAKEWARD work for BLAKEJONES work for KINGMARTIN work for BLAKEBLAKE work for KINGCLARK work for KINGSCOTT work for JONESTURNER work for BLAKEADAMS work for SCOTTJAMES work for BLAKEFORD work for JONESMILLER work for CLARK1.4UNION , INTERSECT及 MINUSUNION: 可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)具体语法:select .unionallselect.=INTERSECT: 返回两个表中相同的信息具体语法:select .intersectselect.=MINUS : 返回一个表中出现的信息具体语法:select .minusselect.例1UNION操作实例SQL select st_id from students 2 union 3 select st_id from student_skill;ST_ID-973231973232973233例2INTERSECT操作实例列出有特长的学生的学号SQL select st_id from students 2 intersect 3 select st_id from student_skill;ST_ID-973231973233例3MINUS操作实例列出没有特长学生的学号select st_id from studentsminusselect st_id from student_skill;ST_ID-9732322.创建复杂的视图许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.2.1分组视图例1简单的分组视图SQL create or replace view dept_tot as 2 select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b 3 where a.deptno=b.deptno group by a.dname;查看已建立。SQL select * from dept_tot;DEPT TOTAL_SAL- -ACCOUNTING 8750RESEARCH 10875SALES 9400例2带复杂函数视图SQL create or replace view itemtot as 2 select persion,sum(amount) itemtot from ledger 3 where actiondate between 4 to_date(01-MAR-1901,dd-mon-yyyy) and 5 to_date(31-MAR-1901,dd-mon-yyyy) 6 and action in(bought,raid) group by persion;2.2合计视图例合计函数视图实例SQL create or replace view emp_no1 as 2 select deptno,sum(sal) 工资和,sum(comm) 总和 3 from scott.emp group by deptno;SQL select * from emp_no1;DEPTNO 工资和