还有这种骚操作

可以使用子查询的位置：where select having from 

1、WHERE 

select * from emp where sal>(select sal from emp where ename='SCOTT')

2、SELECT 

select empno,ename,sal,(select job from emp where empno=7839) 第四列 from emp;

3、HAVING

select deptno,avg(sal) from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);

4、FROM

select  * from (select empno,ename,sal from emp)；

子查询注意的10个问题

1、子查询的小括号不要忘

2、子查询的书写风格

3、使用子查询的位置：where,select having ,from

4、不可以使用的子查询的位置：group by 

5、强调，from 后面的子查询

6、主查询和子查询可以不是同一张表

7、一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

8、一般先执行子查询，再执行主查询；但相关子查询例外

9、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

10、注意：子查询中是NULL值问题

子查询

比SCOTT的工资高的员工信息

1、查询SCOTT的工资

select sa; from emp where ename='SCOTT';

2、比SCOTT工资高的员工信息

select * from emp where sal>3000;

子查询代码

select * from emp where sal >(select sal from emp where ename='SCOTT');

declare

cursor cplayer is select height from player;

pheight player.height%type;

counta number:=0;

countb number:=0;

countc number:=0;

countd number:=0;

begin

open cplayer;

loop

fetch cplayer into pheight;

exit when cplayer%notfound;

if pheight>=2.00 and pheight<=2.50 then counta = counta+1

elsif pheight >=1.90 and pheight<=1.99 then countb = countb+1

elsif pheight >=1.80 and pheight<=1.89 then countc = countc+1

else countd = countd+1

end if;

end loop;

close cplayer;

dbms.output.put_line('A类人数'||counta)

dbms.output.put_line('B类人数'||countb)

dbms.output.put_line('C类人数'||countc)

dbms.output.put_line('D类人数'||countd)

end;

/

select 'a',count(height)

from player

where height between 2.00 and 2.50

union all

select 'b',count(height)

from player

where height between 1.90 and 1.99

union all

select 'c',count(height)

from player

where height between 1.80 and 1.89

union all

select 'd',count(height)

from player

where height between 1.60 and 1.79

select sum(decode(height, between 2.00 and 2.50,1,0) A,

sum(decode(height, between 1.90 and 1.99,1,0) B,

sum(decode(height, between 1.80 and 1.89,1,0) C,

sum(decode(height, between 1.60 and 1.79,1,0) D

from player

select 

(select count(*) from player where height between 2.00 and 2.50) A,

(select count(*) from player where height between 1.90 and 1.99) B,

(select count(*) from player where height between 1.80 and 1.89) C,

(select count(*) from player where height between 1.60 and 1.79) D

from dual;

自连接存在的问题

select e.ename 员工姓名,b.ename 老板姓名

from emp e,emp b

where e.mgr=b.empno;

获得笛卡尔全集

select count(*) from emp e,emp b;

不适合操作大表

层次查询（单表查询）

自连接

示例：查询员工姓名和员工的老板姓名

·核心：通过别名，将同一张表视为多张表

还能get 一个sql 不错嘛

然后@执行此处脚本

o 相同的部门号就显示一次， 不同的部门号跳过两行，这是sqlplus独有的吧

rollup的意义就是递归嵌套？

这么简单的嵌套吗 直接在最外层加一个括号函数，看来还是表达的简单吧

嵌套不就是所谓的2阶吗

外连接

示例：按部门统计员工人数，要求显示：部门号、部门名称、人数

select d.deptno 部门号, d.dname 部门名称,count(e.empno) 人数

from emp e,dept d

where e.deptno=d.deptno

group by d.deptno,d.dname;

通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中

左外连接：当连接条件不成立的时候，等号左边的表仍然被包含

右外连接：当连接条件不成立的时候，等号有边的表仍然被包含

a命令的先天缺陷需要后天去改

sql plus还真是 骚操作挺多 ed编辑 /执行历史 

1、2、3直接表列，当然这是sql通用的