概述
目标表：
SQL> desc emp; Name Type Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO NUMBER(4) ENAME VARCHAR2(10) Y JOB VARCHAR2(9) Y MGR NUMBER(4) Y HIREDATE DATE Y SAL NUMBER(7,2) Y COMM NUMBER(7,2) Y DEPTNO NUMBER(2) Y SQL>
1.1 查询表中所有的行与列
select * from emp;
1.2 从表中检索部分行
只需要加过滤条件即可
select * from emp a where a.ename='ALLEN';
1.3 查找空值
null值 “=”判断，需用is null判断
select * from emp where comm is null;
null值也不支持 加减乘除 大小比较 相等比较，否则只能为空。
对于其他函数，在使用时最好测试一下null值时会返回什么结果。
1.4 将空值转换为实际值
select coalesce(comm,0) from emp;
有人会问，为什么不是nvl 而是 coalesce呢？ 因为coalesce更好用。
看下面这个例子，返回多个值中第一个不为空的值。
CREATE OR REPLACE VIEW xgj 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;
SQL> select * from xgj; C1 C2 C3 C4 C5 C6 -- -- ---------- ---------- ---------- ---------- 1 2 3 2 SQL>
SQL> select coalesce(c1,c2,c3,c4,c5,c6) from xgj; COALESCE(C1,C2,C3,C4,C5,C6) --------------------------- 1 3 SQL>
可以看到对于nvl来说，coalesce支持多个参数，能很方便的返回第一个不为空的值，如果上面的语句改用nvl.则需要套用很多层
SQL> select nvl(nvl(nvl(nvl(nvl(c1, c2), c3), c4), c5), c6) from xgj; NVL(NVL(NVL(NVL(NVL(C1,C2),C3) ---------------------------------------- 1 3 SQL>
1.5 查找满足多个条件的行
对于简单的查询，操作起来比较简单，那么复杂一点儿的呢？ 比如 ，查询部门号10中的所有员工、所有得到提成的员工、以及部门20中工资不超过2000的员工。
这是三个条件的组合，符合上述任一一条即可。
注意：对于多个条件的组合，要使用括号，这样在更改维护语句时可以不必再考虑优先级问题，而且可以很容易的借助龚总工具找到组合条件的起始位置
select * from emp e where (e.deptno = 10 or e.comm is not null or (e.sal > 2000 and e.deptno = 20));
1.6 从表中检索部分列
select empno ,ename from emp a ;
1.7 为列取有意义的名称
不是每个人都能看懂那些简写字母是什么意思，所有在必要的时候应该给列取个别名。
在as 后面跟别名，也可以不要as 直接在列名后跟别名。
SQL> select empno as 工号 ,ename 姓名 from emp a ; 工号 姓名 ----- ---------- 7369 SMITH ...........
1.8 在 WHERE 子句中引用取别名的列
写报表时，经常会加上各种条件， 引用别名时，千万别忘了嵌套一层，因为这个别名是在select之后才有效的.
比如：
SQL> select * 2 from (select ename 姓名, sal as 工资 from emp a) 3 where 工资 < 1000; 姓名 工资 ---------- --------- SMITH 800.00 JAMES 950.00 SQL>
如果不嵌套，提示 标识符无效
SQL> select ename 姓名, sal as 工资 from emp a where 工资 < 1000; select ename 姓名, sal as 工资 from emp a where 工资 < 1000 ORA-00904: "工资": invalid identifier SQL>
1.9 拼接列 “||”
我们可以使用字符串连接符“||”将各个列拼在一起。
SQL> select ename || '的工资是' || sal as msg from emp ; MSG -------------------------------------------------------------- SMITH的工资是800 ALLEN的工资是1600 ......
当然了对于拼接列，我们还可以如下使用
使用字符串连接符 用SQL生成SQL
select 'truncate table ' || owner || '.' || table_name || '; ' from all_tables ;
1.10 在 SELECT 语句中使用条件逻辑 case when
SQL> select ename, job, case when job = 'ANALYST' then '分析员' when job = 'CLERK' then '服务员' when job = 'MANAGER' then '经理' when job = 'PRESIDENT' then '主席' else '其他' end as 职位 from emp; ENAME JOB 职位 ---------- --------- --------- SMITH CLERK 服务员 ALLEN SALESMAN 其他 WARD SALESMAN 其他 JONES MANAGER 经理 MARTIN SALESMAN 其他 BLAKE MANAGER 经理 CLARK MANAGER 经理 SCOTT ANALYST 分析员 KING PRESIDENT 主席 TURNER SALESMAN 其他 ADAMS CLERK 服务员 JAMES CLERK 服务员 FORD ANALYST 分析员 MILLER CLERK 服务员 14 rows selected SQL>
1.11 限制返回的行数
在查询时，并不要求返回所有的数据，比如进行抽查的时候会要求只返回两条数据。
我们可以使用伪列rownum来过滤，rownum依次对返回的每一条数据做一个标识 。
SQL> select * from emp where rownum <=2 ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 SQL>
如果只想取第二条呢？
SQL> select * from emp where rownum =2 ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ SQL>
显然是不行的。 因为rownum是依次对数据做标识的，所以要先把所有的数据取出来，才能确认第二条记录。
我们可以这样
SQL> select * from (select rownum as rn,a.* from emp a where rownum <=2) where rn=2; RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 2 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 SQL>
1.12 从表中随机返回 n 条记录
我们可以先用dbms_random来对数据先进行数据排序，然后取其中三列。
select * from (select ename, job, sal, comm from emp order by dbms_random.value()) where rownum <= 3;
1.13 模糊查询
数据：
CREATE OR REPLACE VIEW xgj 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;
SQL> select * from xgj; VNAME ------- ABCEDF _BCEFG _BCEDF _\BCEDF XYCEG SQL>
查出vname中包含ＣＥＤ的
SQL> select * from xgj where vname like '%CED%'; VNAME ------- ABCEDF _BCEDF _\BCEDF SQL>
查出vname中包含“_BCE”的
SQL> select * from xgj where vname like '_BCE%'; VNAME ------- ABCEDF _BCEFG _BCEDF SQL>
发现突然多了一个 ABCDEF ， 因为在like子句中有个两个通配符
- % 代替一个或者多个字符
- _ 代替一个字符
在这里“_”被当成通配符了，那怎么办呢？ 我们可以使用转义字符。
SQL> select * from xgj where vname like '\_BCE%' escape '\'; VNAME ------- _BCEFG _BCEDF SQL>
ESCAPE 把‘\’标识作为转义字符， 而 ‘\’把‘_’转义为字符，而非其愿义（通配符）。
我们注意到我们的数据中 有 一列的值为 _\BCEDF ，那么加了ESCAPE ‘\’ 后怎么返回呢？
SQL> select * from xgj where vname like '_\BCE%' escape '\'; select * from xgj where vname like '_\BCE%' escape '\' ORA-01424: missing or illegal character following the escape character
双写转义字符即可
SQL> select * from xgj where vname like '_\\BCE%' escape '\'; VNAME ------- _\BCEDF SQL>
对于字符串中包含’%’ 的也一样的方式处理
