ORACLE查询练习(二)
(select empno from emp where ename = 'KING') e2 whe
re e1.mgr = e2.empno;
你可以用EXISTS 写同样的查询，你只要把外部查询一栏移到一个像下面这样的子查询环境
中就可以了：
select ename from emp e
where exists (select 0 from emp where e.mgr = empno and ename = 'KING');
当你在一个WHERE 子句中写EXISTS 时，又等于向最优化传达了这样一条信息，即你想让外
部查询先运行，使用每一个值来从内部查询（假定：EXISTS＝由外而内）中得到一个值。
B.异表子查询作为条件
a.select * from studentExam where studentid=( select studentid from student whe
re name='吴丽丽');
b.select * from studentexam where studentid in (
select studentid from student) order by studentid;
c.select * from student where studentid in (select studentid from studentexam w
here mark>80);
3.select studentexam.mark,studentexam.studentid as seid, student.studentid,stud
ent.name from studentexam,student where student.studentid=studentexam.studentid;
过滤分组:
顺序为先分组,再过滤,最后进行统计(实际值).
select studentid,count(*) as highpasses from studentexamwhere mark>70group by s
假使我们不想通过数据表中的实际值,而是通过聚合函数的结果来过过滤查询的结果.
select studentid,avg(mark) as averagemarkfrom studentexamwhere avg(mark)<50 or
avg(mark)>70group by studentid;(此句错误,where 句子是不能用聚合函数作条件的)此
时要用having.
select studentid,avg(mark) from studentexam group by studentid having avg(mark)
>70 or avg(mark)<50;
select studentid,avg(mark) from studentexam where studentid in(1,7,9,5)group by
studentid having avg(mark)>70;(先分组,再过滤,再having 聚合,最后再统计).
select studentid ,avg(mark) as averagemarkfrom studentexamwhere examid in(5,8,1
1)group by studentidhaving avg(mark)<50 or avg(mark)>70;
返回限定行数查询:
select name from student where rownum<=10;
oracle 中使用rownum 关键字指定,但该关键字必须在where 子句中与一个比较运算符一起
指定,而不能与order by 一起配合便用,因为rownum 维护的是原始行号.如果需要用group
by\order by 就用子句查询作表使用的方法:
select studentid,averagemark from(select studentid,avg(mark) as averagemarkfrom
studentexamgroup by studentid order by averagemark desc)where rownum<=10;