《oracle查询改写》,ORACLE 优化查询改写（一）

--1.1查询表中的所有行与列

desc emp;

select * from emp;

--1.2从表中检索部分行

select * from emp where job='SALESMAN';

--1.3查找空值(NULL不支持加，减，乘，除，大小比较，相等比较)

SELECT * FROM emp WHERE comm IS NULL;

SELECT REPLACE('abcde','a',NULL) AS str FROM dual;--bcde

--GREATEST(expr_1, expr_2, ...expr_n)函数,从表达式中返回最大的值。数据类型以expr_1为准。

SELECT greatest(1,NULL) from dual;--NULL

--1.4将空值转换为实际值

--coalesce(e1,e2,...)函数，如果comm IS NULL则返回0，否则返回comm

--nvl(ex1,ex2)

SELECT coalesce(comm,0) FROM emp;

CREATE OR REPLACE VIEW v AS

SELECT NULL AS C1,NULL AS C2,1 AS C3,NULL AS C4,2 AS C5,

NULL AS C6 FROM DUAL UNION ALL

SELECT NULL AS C1,NULL AS C2,NULL AS C3,3 AS C4,NULL AS C5,2 AS C6 FROM DUAL;

SELECT * FROM v;

SELECT COALESCE(C1,C2,C3,C4,C5,C6) FROM v;

--1.5查询满足多个条件的行

SELECT * FROM EMP WHERE (DEPTNO=10 OR COMM IS NULL OR (SAL <= 2000 AND DEPTNO=20));

--1.6从表中检索部分列

SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP WHERE DEPTNO = 10;

--1.7为列取有意义的名称

SELECT ENAME AS 姓名,DEPTNO AS 部分编号, SAL AS 工资,COMM AS 提成 FROM EMP;

--1.8在where子句中引用取别名的列

SELECT * FROM (SELECT SAL AS 工资,COMM AS 提成 FROM EMP ) x WHERE 工资<1000;

--1.9拼接列

SELECT ename || '的工作是' || job AS msg FROM emp WHERE deptno = 10;

--1.10在SELECT中使用条件逻辑

SELECT ename,sal,

CASE

WHEN sal<=2000 THEN '过低'

WHEN sal>=4000 THEN '过高'

ELSE 'OK'

END AS status

FROM emp WHERE deptno = 10;

--按工资分档次查询人数

SELECT 档次,COUNT(*) AS 人数 FROM (SELECT (

CASE

WHEN sal <= 1000 THEN '0000-1000'

WHEN sal <=2000 THEN '1000-2000'

WHEN sal <=3000 THEN '2000-3000'

WHEN sal <=4000 THEN '3000-4000'

WHEN sal <=5000 THEN '4000-5000'

ELSE '好高'

END

) AS 档次,ename,sal FROM emp)

GROUP BY 档次 ORDER BY 1;

--1.11限制返回的行数

--取前两行数据

SELECT * FROM emp WHERE rownum <=2;

--取第二行数据

SELECT * FROM (SELECT rownum as sn,emp.* from emp WHERE rownum<=2) where sn = 2;

--1.12从表中随机返回n条记录

SELECT empno,ename FROM (SELECT empno,ename FROM emp ORDER BY dbms_random.value()) WHERE rownum<=3;

--1.13模糊查询

CREATE OR REPLACE VIEW v AS

SELECT 'ABCEDF' as vname FROM dual

UNION ALL

SELECT '_BCEFG' as vname FROM dual

UNION ALL

SELECT '_BCEDF' as vname FROM dual

UNION ALL

SELECT '_\BCEDF' as vname FROM dual

UNION ALL

SELECT 'XYCEG' as vname FROM dual

SELECT * FROM v WHERE vname LIKE '%CED%';

SELECT * FROM v WHERE vname LIKE '_BCE%';

SELECT * FROM v WHERE vname LIKE '\_BCE%' ESCAPE '\';

SELECT * FROM v WHERE vname LIKE '\_\\BCE%' ESCAPE '\';