三、子查询、伪劣和常用函数

三、子查询、伪劣和常用函数

Select 语句可以当做返回的集合做为值

一、单行子查询

 1、子查询做数据源：select ename from (select * from emp); 

 2、子查询做条件: select ename from emp where sal=(select sal from emp where sal=1000);

 3、多行子查询： select ename from emp where sal in (select sal from emp where sal>1000);

 4、any(任意的意思):select * from emp where sal<any(1000,2000,3000)

    翻译：只要比括号中任意一个值小就可以输出，any和all是将sal与子查询返回的多值一条一条的比较所以不会报错

 5、all(所有的意思):select * from emp where sal<all(select sal from emp where job=’SALESMAN’)

翻译：比子查询中的所有的值都小就输出

 

二、oracle中的伪列

 1、rowid:返回该行的物理地址:select rowid from dual;

 

    每次查询都会有rowid只是它自动隐藏了

 2、rownum:返回改行的行号：select rownum from dual;

 

每一行返回一个行号自动隐藏

 3、rouwnum 一般用于子查询分页查询

   select * from (Select rownum r,ename,job,sal from (select ename,job,sal from emp order by sal desc ))  where r>=1 and r<=3; 

   首先将以工资降序作为一个结果集数据源然后给他添加一个rownum最后输出这rownum等于1到3的结果集

    易错区： 

      Select rownum r,ename,sal from emp where r>=1 and r<=3; 

           Select * from (select rownum r,ename,job,sal from emp order by sal desc) t where r>=1 and r<=3;   

   第一种：执行顺序就出错了

   第二种：将工资order by的时候就已经打乱rownum的顺序 使其变得无意义

 4、外部查询用于分组,注意：当子查询作为列名时只能返回一条数据

查询 dept表数据 并得到每个dept部门的员工

select dept.dname,dept.deptno,(select count(1) from emp where emp.deptno=dept.deptno)t from dept;

 

个人理解：首先运行子查询中的内容，当运行到dept时则扩大范围到外层寻找dept，找到时连带外层会执行一次并得到dept一行值，然后回到子查询中运算由于只能返回一个值所以只能返回count记录行数而不能是字符