--查询所有
1 select * from emp; 2 select * from emp where comm is null;
--错误表达
1 --select * from emp where comm = null;
--其他有关null的返回结果
1 select replace('abcde','a',null) as str from dual; 2 select greatest(1,null) from dual;
/*结论:遇到null最好先测试一下,不能臆想猜测*/
--coalesce函数
1 SELECT coalesce(comm,0) FROM emp;
--上述例子中coalesce与nvl函数起同样作用,但coalesce函数更好用,支持更多参数,能很方便地返回第一个不为空的值
1 CREATE OR REPLACE VIEW v AS 2 SELECT NULL AS c1,NULL AS c2,'1' AS c3,NULL AS c4,'2' AS c5,NULL AS c6 FROM dual UNION ALL 3 SELECT NULL AS c1,NULL AS c2,NULL AS c3,'3' AS c4,NULL AS c5,'2' AS c6 FROM dual; 4 SELECT * FROM v; 5 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和||都可以拼接字段
1 SELECT ename || '的工作是' ||job AS msg FROM emp WHERE deptno=20; 2 SELECT concat('姓名:',ename) AS msg FROM emp WHERE deptno=20;
--summary:concat可以拼接两个字段,使用||可以拼接多重字段
--key:生成sql
--desc:用sql生成sql
1 DROP TABLE test_concat PURGE; 2 CREATE TABLE test_concat AS 3 SELECT table_name, 4 'N_' || table_name AS new_tbl_name, 5 column_name, 6 'new_' || column_name AS new_col_name 7 FROM all_tab_cols 8 WHERE owner = 'SCOTT';
1 DECLARE v_sql CLOB; 2 BEGIN 3 FOR cur IN (SELECT 'CREATE OR REPLACE VIEW ' || new_tbl_name || ' as ' || chr(10) || 4 'select ' || chr(10) || 5 wmsys.wm_concat(column_name || ' as ' || new_col_name || chr(10)) || 6 'from scott.' || table_name AS create_view 7 FROM test_concat 8 GROUP BY table_name,new_tbl_name) 9 LOOP 10 v_sql := cur.create_view; 11 EXECUTE IMMEDIATE v_sql; 12 END LOOP; 13 END;
--key:条件逻辑
--desc:case when
1 SELECT (CASE 2 WHEN sal<1000 THEN '0000-1000' 3 WHEN sal<=2000 THEN '1000-2000' 4 ELSE '好高' END) AS 提成 5 FROM emp;
--key:rownum
--desc:限制返回行数
1 SELECT * FROM emp WHERE ROWNUM<=3 ORDER BY sal DESC;
--upgrade
1 SELECT * FROM (SELECT ROWNUM rn ,emp.* FROM emp WHERE ROWNUM<=3) WHERE rn=2;
--summary:并不能通过order by和rownum的配合得到排名
--key:随机
--desc:随机抽查3行数据
1 SELECT empno,ename FROM (SELECT dbms_random.value(),empno,ename FROM emp ORDER BY dbms_random.value()) WHERE ROWNUM <= 3;
/*summary:必须嵌套使用,否则直接使用并不能得到随机效果:
1 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:
1 CREATE OR REPLACE VIEW v AS SELECT 'ABCEDF' AS vname FROM dual 2 UNION ALL 3 SELECT '_BCEFG' AS vname FROM dual 4 UNION ALL 5 SELECT '_BCEDF' AS vname FROM dual 6 UNION ALL 7 SELECT '_\BCEDF' AS vname FROM dual 8 UNION ALL 9 SELECT 'XYCEG' AS vname FROM dual 10 SELECT * FROM v;
--查询包含CED的行
1 SELECT * FROM v WHERE vname LIKE '%CED%';
--查询包含_BCE的行
1 SELECT * FROM v WHERE vname LIKE '%_BCE%';--ABCEDF和_\BCEDF并不是想要的结果,因为_被当做通配符了 2 SELECT * FROM v WHERE vname LIKE '%\_BCE%' ESCAPE '\';--escape是用来定义转义字符的,只有定义了才有效果,也可以定义成其他,如*、/等