Oralce子查询，伪列，函数

1.子查询

select语句的结果作为外部语句条件的一部分，也可以作为一个临时表

子语句的类型:1.单行子查询：不向外部返回结果，或者只返回一行结果  >,<,=
	        2.多行子查询：向外部返回0行，1行或多行 in,<any,<all
	        3.非关联子查询： 子查询可以脱离主查询独立执行.
	        
	        4.关联子查询：子查询与主查询之间有条件关联,  不能独自执行
	           select a.deptno, a.* 
	           from emp a
	           where a.sal = (select max(b.sal) from emp b where b.deptno =  a.deptno)
对于关联子查询来讲,是自外到内的, 先执行外查询,会执行多次子查询
例如上面的例子,对于主表a来间,对于每1个deptno,都要执行一次子查询. 
关联子查询还有1个限制,就是子查询不能返回多于1行的数据.

查询开发部门下面员工的姓名，工作，工资
–方法1 子查询

select ename,job,salary
from emp
where depno=(select depno 
             from dep 
             where dname='开发部');

–方法2 连接查询

select e.ename,e.job,e.salary
from emp e,dep d
where e.depno=d.depno and d.dname='开发部';

查询emp中比任何一个’工程师’工资低的员工的姓名，工作，工资
–方法1

select ename,job,salary
from emp
where salary<(select min(salary)--单行子查询
              from emp
              where job='工程师');

–方法2
关键字all所有

select ename,job,salary
from emp
where salary<all(select salary--多行子查询
              	 from emp
              	 where job='工程师');

关键字any其中任意一个

2.伪列

在我们使用过程中，实际表还有附加的列，叫伪列，像表中的列一样，但是表中不存储，只用于查询
不能增删改

–rowid，在表中的每一行数据都有一个物理地址，rowid返回该行的物理地址
作用：快速定位某一行数据，唯一的，不重复

select rowid,ename,job from emp;

–rownum,查询的结果集，rownum为每一行标识一个行号，第一行返回1，第二行返回2
–作用：限制查询行数

--查询员工表里工资最高的前五名员工的姓名，工作，工资
--子查询
select rownum,t.* 
from (select ename,job,salary 
      from emp 
      order by salary desc) t
where rownum<=5;

--查询员工表里第3条到第6条员工的姓名，工作，工资
select ename,job,salary 
from (select rownum r,ename,job,salary 
      from emp)
where r >=3 and r<=6;
--为啥用r而不是rownum
--r不再是rownum，而是变成了一个列名，所以可以用大于

3.函数

1). 字符函数

--转成ascii码
select ascii('a') from dual;
select ascii('A') from dual;

--连接字符串
select concat(ename,job) from emp;
select concat('hello','world') from dual;

--查找字符串的位置
select instr('my name is jason','jason') from dual;

--查字符串长度
select length(job),job from emp;

--转大写
select upper(ename),ename from emp;
select upper('jason') from dual;

--转小写
select lower('JASON') from dual;

--ltrim去除匹配的左边内容
select ltrim('===lucy==','=') from dual;

--rtrim去除匹配的左边内容
select ltrim('  lucy ') from dual;

--trim去除空格
select trim('  lucy ') from dual;

--trim去除两边字符
select trim('i' from 'iailuicyii') from dual;--结果ailuicy

--替换
select replace('mm nn pp qq','mm','zzz') from dual;--结果zzz nn pp qq
select replace(job,'工程师','喝酒') from emp;--不修改数据库，只是在查询的记录中修改

--截取字符串
select substr('my name is jason',4,7) from emp;--从第4位开始往后7个字符
select substr('my name is jason',4) from emp;--从第4位开始到最后

2). 数字函数

--绝对值
select abs(-1) from dual;

```sql
--余弦
select cos(1.047) from dual;

--反余弦
select acos(0.5) from dual;

--返回大于等于n的最小整数
select ceil(n) from dual;

```sql
--返回小于等于n的最大整数
select floor(n) from dual;

--对数
select log(10,100) from dual;

--取余
select mod(20,3) from dual;

--幂次
select power(2,3) from dual;

--四舍五入
--round(m,n) 对十进制数字m，根据n进行四舍五入计算
select round(126.56,0) from dual;  --127
select round(126.56,-1) from dual; --130

--平方根
select sqrt(4) from dual;

--小数点保留几位
select trunc(3.1415926,2) from dual;

--查询系统时间
select sysdate,systimestamp from dual;

--加月份add_months(时间，添加月)
select sysdate,add_months(sysdate,2) from dual;

--返回月份的最后一天
select last_day(sysdate) from dual;

--提取日期extract
select sysdate,extract(year from sysdate) 年,
extract(month from sysdate) 月,
extract(day from sysdate) 日 from dual;

select systimestamp,extract(hour from systimestamp) 时,--得到的小时值会减去时区差值
extract(minute from systimestamp) 分,
extract(second from systimestamp) 秒 from dual;

3). 转换函数

--to_char
select to_char(sysdate,'YYYY"-"MM"-"DD HH24:MI:SS') "日期" from dual;

--to_date
select to_date('2018-10-02 10:09:02','yyyy"-"mm"-"dd hh24:mi:ss') from dual;

select to_char(1234,'9.99EEEE') from dual;

--nvl(m,value) 若m为空返回value值，若不为空返回m值
--nvl2(m,value1,value2) 若m非空返回value1，否则返回value2

--对于员工表的奖金（comn）,若没有，则奖金500，若有奖金，则加两百
update emp set comn=nvl2(comn,comn+200,500);
--若有奖金，输出奖金，若没有，奖金为800
select nvl(comn,800) from emp;

控制语句
case when … then … end