排序
select * from emp order by sal desc（降序）,hiredate asc（升序，默认）
模糊查询
select * from emp where ename not like '_A%';第二个字母不是A的 ‘A%’(以A开头) ‘%A%’（中间有A的）
_ 单个匹配 % 多个匹配
substr 截取字符串
select ename,substr(ename,3)(从第3个开始一直到结束截取字符串)from emp
select ename,substr(ename,0,3)=（ename，1,3）(从第1个开始截取3个)from emp 在oracle中从第0个和从第一个一样
replace
select ename,replace(ename,'A','_') from emp 替换A成_
select initcap(ename) from emp 首字母大写其余小写 upper（全部大写） lower（全部小写）
数字函数
select round(514.5462) from dual; round(514.5462,2) （正数表示从小数开始截，不写代表取整，负数表示从整数开始截）保留2位小数
round(514.5462,-2)整数位2位4舍5入549--500 550-600
trunc() 取整 小数部分全部省去
mod(10,3) 取模后结果为1
日期函数
select sysdate from dual;
select sysdate,sysdate+3,sysdate-3,sysdate-hiredate from dual;
日期只有加数字，减数字，减日期（大减小） 无日期+日期
select last_day(sysdate) from dual;本月最后一天
select next_day(sysdate,'星期一') from dual;求出下个星期1的日期
select add_months(sysdate,3) from dual;3个月之后的今天
select ename,months_between(sysdate,hiredate) from emp;获得到目前的月份，最好trunc(months_between(sysdate,hiredate))截取掉小数
转换函数(number,date,varchar)
日期转字符串：
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;以2011-11-22开始，如果是1月1日，结果为2011-01-01，保留0（消除0加fm）即‘fmyyyy-mm-dd’
默认是12小时制，换成24时应为hh24:mi:ss 注yyyy，mm，dd，hh，mi，ss是固定格式
数字转字符串
select to_char(3797676869679,'L999,999,999,999,999')from dual;结果￥3,797,676,869,679 L表示local，即本语言环境下的货币，‘999此时不表示数字，而是格式
字符串转日期
select to_date('1990-11-12','yyyy-mm-dd')from dual;
通用函数
nvl处理null
select ename,(sal+comm)*14 from emp;会发现comm有的字段是空，所以算出来有的年薪也为空
解决办法就是select ename,(sal+nvl(comm,0))*14 from emp;
decode(数值|列,判断1，显示1，判断2，显示2，···)处理多值判断，必须成对出现，类似于if else
select ename,sal,empno,decode(job,'CLERK','办事者','SALESMAN','销售者','MANAGER','管理者','ANALYST','分析员','PRESIDENT','总裁') from emp;
注意CLERK等一定要大写
左右连接
（+）=
select e.ename,e.sal,m.ename from emp e,emp m where e.mgr=m.empno(+);
分组函数
count()一共记录数
avg()平均值
sum()求和
max()最大值
min()最小值
group by 分组函数
select count(*),avg(sal),sum(sal),max(sal),min(sal) from emp;
注意 1，分组函数可以在没有分组的时候单独使用，但不能出现其他字段 select count(*),sal from emp 错误；不是单独的分组函数
不能出现sal
2，如果进行分组，则select后只能出现分组的字段和统计函数
select job,count(*),max(sal) from emp group by job
select d.dname,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname;
where和having区别：
where： 执行group by之前的过滤，表示从全部数据中选出部分数据，where中不能使用统计函数，即 where avg（sal）
having： 执行group by之后的再次过滤，可以使用统计函数 having avg（sal）>2000
如果想在分组之后再次过滤 就用having
select d.dname,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname having avg(e.sal)>2000;
显示非销售人员的工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资
合计大于5000，输出结果按月工资的合计升序排列
select e.job,sum(sal) from emp e where e.job<>'salesman' group by e.job having sum(sal)>5000 order by sum(sal);
子查询
in any all
in 指定是不是在此范围
select * from emp where sal not in(select sal from emp where ename='BLAKE');
any 与每一个内容相匹配，有3种形式：=any，>any,<any
select * from emp where sal=any(select sal from emp where ename='BLAKE');与in相同
select * from emp where sal>any(select sal from emp where ename='BLAKE');比最小的还要大
select * from emp where sal<any(select sal from emp where ename='BLAKE');比最大的还要小
all与每一个相比较 两种形式 >all <all
select * from emp where sal>all(select sal from emp where ename='BLAKE');比最大的还要大
select * from emp where sal<all(select sal from emp where ename='BLAKE');比最小的还要小
与多表查询区别：
例题：查询出每个部门的编号，名称，部门人数，平均工资
多表查询：select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from
emp e,dept d where d.deptno=e.deptno group by d.deptno,d.dname,d.loc;
产生14*4=56条笛卡尔积
子查询： select d.deptno,d.dname,d.loc,temp.count,temp.avg
from dept d,(select avg(sal) avg,deptno depno,count(ename) count from emp group by deptno) temp
where temp.depno=d.deptno;
子查询查emp表14条，查出3条，dept表4条 笛卡尔积一共12条，加上子查询的14条一共26条 性能比较好
子查询适用于如果最终的查询结果之中需要select子句，但是又不能直接使用统计函数，就在子查询中统计信息，在外部负责将统计信息与表数据想关联
数据伪列
ROWNUM
实现分页 取第6到10条数据不能用rownum between 6 and 10 因为rownum不是真实的列，只能用子查询
即子查询中查出rownum小于10的，外查询选出大于5的
select * from (select rownum rm,empno,ename,job,hiredate,sal from emp where rownum<=5) temp
where temp.rm>0; 第一页
select * from (select rownum rm,empno,ename,job,hiredate,sal from emp where rownum<=10) temp
where temp.rm>5; 第二页
select * from (select rownum rm,empno,ename,job,hiredate,sal from emp where rownum<=15) temp
where temp.rm>10; 第三页
ROWID
实现删除重复名字的行
delete from dept where rowid not in (select min(rowid) from dept group by dname,loc);
以dname分组，如果dname重复，则选出来这个重复名字的最小的rowid，不是这个的都删除
select e.empno,e.ename,tem.e2,tem.dn from emp e,
(select en.deptno e1,en.ename e3,nvl(te.ename,null) e2,d.dname dn from emp en,emp te ,dept d
where te.empno(+)=en.mgr and d.deptno=en.deptno) tem
where tem.e3=e.ename and e.sal>all(
select sal from emp where ename in('SMITH','ALLEN')
)
;
select e.ename,e.ename,d.dname,m.ename
from emp e,emp m,dept d
where e.sal>all(select sal from emp where ename in('SMITH','ALLEN'))
and e.mgr=m.empno(+) and e.deptno=d.deptno;
列出所有员工的编号，姓名，领导的编号，姓名，显示结果按领导的年工资的降序排列
select e.empno,e.ename,m.empno,m.ename,(m.sal+nvl(m.comm,0))*12 income from emp e,emp m
where e.mgr=m.empno(+) order by income desc;
列出受雇日期早于直接上级的所有员工的编号，姓名，部门位置，部门名称，部门人数
select e.empno,e.ename,e.hiredate,d.dname,d.loc,temp.count from emp e,emp m,dept d
,(select deptno dno,count(deptno) count from emp group by deptno) temp
where e.mgr=m.empno and e.deptno=d.deptno and e.hiredate<m.hiredate and e.deptno=dno;
列出部门名称和这些部门员工的信息（数量，平均工资），同时列出没有员工的部门
select d.dname,avg(e.sal),count(e.deptno) from emp e,dept d where e.deptno=d.deptno(+) group by d.dname;
select d.dname,e.avg,e.count
from dept d,(select deptno dno,avg(sal) avg,count(sal) count from emp group by deptno) e
where d.deptno=e.dno(+);
6列出所有‘clerk’（办事员）的姓名及其部门名称，部门人数，工资等级
false:select e.ename,d.dname,s.grade,count(e.empno)from emp e,dept d,salgrade s
where e.deptno=d.deptno(+) and e.job='CLERK' and e.sal between s.losal and s.hisal
group by e.ename,d.dname,s.grade;
true:select e.ename,d.dname,s.grade,temp.count from emp e,dept d,salgrade s
,(select deptno dno,count(empno)count from emp group by deptno) temp
where e.deptno=temp.dno and e.sal between s.losal and s.hisal and e.job='CLERK' and e.deptno=d.deptno;
7.列出最低薪金大于1500的各种工作的全部雇员人数及所在的部门名称，位置，平均工资
false:select temp.job,d.loc,d.dname,avg from dept d,(select job,deptno,min(sal) min,avg(sal)avg from emp group by job,deptno having min(sal) >1500)temp
where temp.deptno=d.deptno(+) ;
true:select temp.job,temp.count,d.dname,e.ename,res.avg from dept d,emp e,
(select e.job job,count(e.empno) count from emp e group by job having min(e.sal)>1500)temp,
(select deptno,avg(sal) avg from emp group by deptno)res
where e.deptno=d.deptno and e.job=temp.job and res.deptno=e.deptno;
6,7两题错误的原因是不懂group by的真正意思，例如第7题的temp表和res表不能合并，加入合并
代码为 select job,deptno ,count(empno) count from emp group by job,deptno having min(sal)>1500
这时count后的值基本上全为1，除了analyst的sal都是3000，为什么出现这情况呢？group by以···分组，后面两个字段则以两个字段分组
所以第七条正确答案拼了两个表，分别group by job，和group by deptno，而不是group by job，deptno
之后由于group by job后没法和外面关联，所以新建一个emp表，用其job字段和temp表关联