Oracle查询优化改写 ——抄书学习01

select * from emp where job =‘salesman’
select * from emp where comm =null ;error
select * from emp where comm is null
2.将空值转换为实际值
select coalesce（comm,0） from emp

3.查询部门10的所有员工，所有得到提成的员工，和部门20中不超过2000的
（部门10中的员工 or 所有有提成的 or （工资《=2000and 部门 =20））
select * from emp
where(
deptno =10
or comm is not null
or (sal<=2000 and deptno =20) );
4.在where子句终于能用取别名的列
select * from (select sal as 工资， comm as 提成 from emp) x
where 工资<1000;
5.拼接列
如果你喜欢返回的数据是xxx的工作是xxx
我们便可以用||把各列拼在一起。
select ename ||‘的工作是’||job as msg from emp where deptno =10;
6.在select 语句中使用条件逻辑
条件:员工小于等于2000美元返回消息过低，大于或等于4000美元显示过高，在两者之间显示ok。
select ename ,sal
case
when sal<=2000 then ‘过低’
when sal >=4000 then ‘过高’
else ‘OK’
end as status from emp
where deptno =‘10’;
7.限制返回行数
条件: 查询时我们并不需要返回所有的数据，如果只需要返回两条数据
rownum
select * from emp where rownum <=2;
不能用rownum =2;
2.1以制定次序返回查询结果
select emp_no e_name hiredate from emp where dept_no =10 order by hiredate asc
z其中order by hiredate asc 还可以写为order by 3 asc（按照第三列排序）
2.3按照子串排序
select last_name as 名称
phone_number as 号码
salary as 工资
substr（phone _number, -4） as 尾号/截取后四位手机尾号作为（）
from hr.employees
where rownum <5 /x显示5条数据
order by 4;/用第四列排序
2.4translate
语法格式（tanslate(expr,from_string,to_string)
tanslate(字符串,翻译前的的内容,翻译后的的内容（以子夫为单位，对应字符，一一替换）
select translate(‘ab你好bcdefg’,‘abcdefg’,‘1234567’) as new_str from dual
结果为
12你好234567
如果对应位置没有字符，则删除的from——string列出的字符会被消掉
select translate(‘ab你好bcdefg’,‘1abcdefg’,‘1’) as new_str from dual
结果为
你好
2.6处理排序空值
Oracle默认排序空值放在后面，现在默认显示前面，怎么办
用关键字 nulls first 和nulls last
空值在前
select enmae,sal,comm from emp order by 3 nulls first;
空值在后
select enmae,sal,comm from emp order by 3 nulls last;
2.7 根据条件去不同的值来排序
有时候领导对工资在于1000到2000美元的员工感兴趣，要求范围内员工排在前面
select emp_no as 编码
ename as 姓名
case when sal>1000 and sal <2000
then 1 else 2
end as 级别。
sal as 工资
from emp
where dept_no =30
order by 3,4;
3.1 union all 与空字符串
union all 常用于合并多个数据集
select emp_no as 编码，
ename as 名称，
nvl（mgr，dept_no） as 上级编码
from emp where emp_no =7788
union all
select deptno as 编码，
dname as 名称，
null as 上级编码
from dept
where dept_no =10；
在Oracle等各种数据库中’ '空字符串是varchar2类型，而null则是任何类型都不同，所以他们不等价
union 与 or （条件里有or 经常被改写为 union）
要注意区分union all 和union，后者会去重
3.3组合相关的行
多个表中返回数据
select e.emp_no e.ename,d.dname,d.loc from emp e inner ioin dept d on (e.deptno = d.deptno)
wherer e.deptno =10;
3.4 in,exists 和inner join
3.5 inner join ,left join ,right join ,full join(返回左右表的所有数据，只有相匹配的数据显示在同一行，非匹配的行只显示一个表的数据)
3.6自关联（可以了极为是在两个不同的数据集中取数据）
3.8外链接的条件不要乱放
3.9检测两个表中的数据及对应数据的条数是否相同
第一步.建立视图
create or replace view v as
select * from emp wherer deptno !=10
union all
select * from emp where ename = ‘scott’
第二步，查询出视图v与表emp中不同的数据
查询v 里的 数据2条
select rownum ,emp_no ,e_name from v where e_name =‘scott’;
查询emp里的数据一条
select rownum，emp_no，ename from emp where ename=‘scott’
比较两个数据集的不同时，tonGV航类似下面的full join 语句
select v.empno,v.ename,b.empno,b.name from v
full join emp b on (b.empno =v.empno)
where (v.empno is null or b.empno is null) 这个是错误的
对数据进行和处理,加一列显示相同数据的条数，在进行比较
select v.empno ,v.ename,v.cnt,emp.empno, emp.ename,emp.cnt /（数量）
from (select empno, ename,count() as cnt from v group by empno ,ename)
full join（select empno ,ename, count() as cnt from emp group by empno, ename）emp
on (emp.empno =v.empno and emp.cnt = v.cnt)
where (v.empno is null or b.empno is null);
3.10 聚集和内连接