[数据库基础]Oracle查询优化改写_03

从今天开始，我跟大家一起学习Oracle的基础知识。孔子曰：“温故而知新，可以为师矣”。学过的东西，也要经常回顾一下，才能记得更牢，用的更溜。
1. 按数字和字母混合字符串中的字母排序

create or replace view v
as
select empno || ' ' || ename as data from emp;

需求：按照视图v里字段中的ename的值排序。
那么问题来了，怎么把ename从字符串中剔除出来呢？因为ename出来以后排序是很简单的。
我们选择使用translate函数，语法还记得吗？不记得的再来温习一下。
translate（expr，from_string，to_string）

select v.data, translate(v.data, '0123456789 a', 'a') b 
from v 
order by b;

大功告成！
Tips：如果to_string为空，那返回为空。
SELECT TRANSLATE(‘12你好454658342’, ‘-1234567890’, ”) AS DATA FROM DUAL;

如果to_string对应的位置没有字符，那么from_string中的字符就会被消掉。
SELECT TRANSLATE(‘12你好454658342’, ‘-1234567890’, ‘-‘) AS DATA FROM DUAL;

---

我们来看第二个问题
2.处理排序空值
我们可能处理空值时，习惯将空值用-1代替，然后再排序。

法1：
SELECT T.ENAME, T.SAL, T.COMM, NVL(T.COMM, -1) FROM EMP T ORDER BY 4;
法2：
SELECT T.ENAME, T.SAL, T.COMM FROM EMP T ORDER BY 3 NULLS FIRST;

方法一是常用的写法，方法二是使用了函数去实现的是不是简单很多。因为第一种方法需要对列类型及其中保存的数据有所了解才行，而且保存的数据如果有变化，该语句就需要重新维护。
使用NULLS FIRST和NULLS LAST就会很方便。

---

3.根据条件取不同列中的值来排序
有时候排序的要求比较复杂，比如：领导对工资在1000到2000元之间的员工更感兴趣，于是要求工资在这个范围的员工要排在前面，以便优先查看。
针对这种需求，我们可以在查询中的新生成一列，有多列排序的方法实现。

SELECT  T.ENAME, 
    T.SAL,
    T.COMM,
    CASE 
      WHEN T.SAL >= 1000 AND T.SAL <2000 THEN 1
      ELSE 2 
    END AS ROW_ID
FROM EMP T
ORDER BY 4, 2;

4.查看sql的执行计划的方法

--先执行一条语句，之后查看更新后的执行计划。
EXPLAIN PLAN FOR 
SELECT T.ENAME, T.SAL,T.COMM,CASE WHEN T.SAL >= 1000 AND T.SAL <2000 THEN 1
ELSE 2 END AS ROW_ID
FROM EMP T
ORDER BY 4, 2;
COMMIT;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

5.合集（union） 和 并集（union all）的区别

--法一
SELECT * FROM EMP T WHERE T.EMPNO = 7788 OR T.ENAME = 'SCOTT'; 

--法二
SELECT * FROM EMP T WHERE T.EMPNO = 7788
UNION ALL
SELECT * FROM EMP T WHERE T.ENAME = 'SCOTT';

--法三
SELECT * FROM EMP T WHERE T.EMPNO = 7788
UNION
SELECT * FROM EMP T WHERE T.ENAME = 'SCOTT';

合集等价于关系运算符OR，不等于并集。
合集union的作用：
1.不仅两个数据集合间重复的数据会被去重，而且单个数据集里重复的数据也会被去重。
2.有重复数据的数据集用union后得到的数据与预期不一致。
所以在使用union时，要确保在去重前加一个可以唯一标识各行的列即可。
这个唯一标识的行可以是唯一列、主键列，还可以是rowid。
但如果数据取自视图，没有rowid时 ，就需要增加一列rownum来当做唯一列。
举个栗子：

WITH E AS
 (SELECT ROWNUM AS SN, V.DEPTNO, V.MGR, V.JOB FROM V)
SELECT DEPTNO
  FROM (SELECT * FROM E WHERE MGR = 7698
        UNION
        SELECT * FROM E WHERE JOB = 'SALESMAN')
 ORDER BY 1;

这里的WITH语句，可以理解为临时创建的在查询期间存在的的VIEW（e）。