Oracle查询优化--单表查询

--查询所有

select * from emp;
select * from emp where comm is null;

--错误表达

--select * from emp where comm = null;

--其他有关null的返回结果

select replace('abcde','a',null) as str from dual;
select greatest(1,null) from dual;

/*结论：遇到null最好先测试一下，不能臆想猜测*/

--coalesce函数

SELECT coalesce(comm,0) FROM emp;

--上述例子中coalesce与nvl函数起同样作用，但coalesce函数更好用，支持更多参数，能很方便地返回第一个不为空的值

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) AS c FROM v;

--key:别名

--desc:可以为结果集的列指定别名，用AS或空格紧跟。
SELECT 姓名 FROM (SELECT ename 姓名,comm AS 提成 FROM emp) WHERE 提成 IS NULL;
--summary:使用别名当筛选条件需要在外面嵌套一层
--key:拼接
--desc:concat和||都可以拼接字段

SELECT ename || '的工作是' ||job AS msg FROM emp WHERE deptno=20;
SELECT concat('姓名：',ename) AS msg FROM emp WHERE deptno=20;

--summary:concat可以拼接两个字段，使用||可以拼接多重字段

--key:生成sql
--desc:用sql生成sql

DROP TABLE test_concat PURGE;
CREATE TABLE test_concat AS 
SELECT table_name,
'N_' || table_name AS new_tbl_name,
column_name,
'new_' || column_name AS new_col_name
FROM all_tab_cols
WHERE owner = 'SCOTT';

/*博客链接*/

DECLARE v_sql CLOB;
BEGIN
FOR cur IN (SELECT 'CREATE OR REPLACE VIEW ' || new_tbl_name || ' as ' || chr(10) ||
'select ' || chr(10) ||
wmsys.wm_concat(column_name || ' as ' || new_col_name || chr(10)) ||
'from scott.' || table_name AS create_view
FROM test_concat
GROUP BY table_name,new_tbl_name)
LOOP
v_sql := cur.create_view;
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;

--key:条件逻辑

--desc:case when

SELECT (CASE 
WHEN sal<1000 THEN '0000-1000' 
WHEN sal<=2000 THEN '1000-2000'
ELSE '好高' END) AS 提成 
FROM emp;

--key:rownum

--desc:限制返回行数

SELECT * FROM emp WHERE ROWNUM<=3 ORDER BY sal DESC;

--upgrade

SELECT * FROM (SELECT ROWNUM rn ,emp.* FROM emp WHERE ROWNUM<=3) WHERE rn=2;

--summary:并不能通过order by和rownum的配合得到排名

--key:随机
--desc:随机抽查3行数据

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

/*summary:必须嵌套使用，否则直接使用并不能得到随机效果：

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

因为查询语句中执行的顺序是：！.select 2.rownum 3.order by 

所以，在order by执行之前，前三行已经定了，随后对已选定的三行进行随机显示而已*/
--key:模糊查询
--desc:模糊查询及转义字符的使用
--prep:

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;

--查询包含CED的行

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

--查询包含_BCE的行

SELECT * FROM v WHERE vname LIKE '%_BCE%';--ABCEDF和_BCEDF并不是想要的结果，因为_被当做通配符了
SELECT * FROM v WHERE vname LIKE '%\_BCE%' ESCAPE '';--escape是用来定义转义字符的，只有定义了才有效果，也可以定义成其他，如*、/等