系列文章目录
【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
文章目录
前言
本篇文章介绍一些SQL开发基础的内容，讲解的主要内容是：NULL空值的运算、coalesce比nvl的优势、拼接列实现批量脚本、如何通过rownum限制返回的行数以及如何正确的使用rownum从表中随机返回 n 条记录。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、查询表中所有的行与列
进行查询操作之前， 我们先看一下表结构，我这里查询的是oracle官方案例用户scott下的表。
SQL>desc scott.emp;
Name Null? Type----------------------------------------- -------- ----------------------------
EMPNO NOTNULL NUMBER(4) 编码
ENAME VARCHAR2(10) 名称
JOB VARCHAR2(9) 工作
MGR NUMBER(4) 主管
HIREDATE DATE 聘用日期
SAL NUMBER(7,2) 工资
COMM NUMBER(7,2) 提成
DEPTNO NUMBER(2) 部门编码
如果领导要看员工所有信息。这个操作很简单，大家应该都会。只要用select*就可以返回目标表中所有的列，查询语句及执行结果如下：
SQL>select*from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH CLERK 79021980-12-17800.00207499 ALLEN SALESMAN 76981981-2-201600.00300.00307521 WARD SALESMAN 76981981-2-221250.00500.00307566 JONES MANAGER 78391981-4-22975.00207654 MARTIN SALESMAN 76981981-9-281250.001400.00307698 BLAKE MANAGER 78391981-5-12850.00307782 CLARK MANAGER 78391981-6-92450.00107788 SCOTT ANALYST 75661987-4-193000.00207839 KING PRESIDENT 1981-11-175000.00107844 TURNER SALESMAN 76981981-9-81500.000.00307876 ADAMS CLERK 77881987-5-231100.00207900 JAMES CLERK 76981981-12-3950.00307902 FORD ANALYST 75661981-12-33000.00207934 MILLER CLERK 77821982-1-231300.00101001 test 2021-10-9115rows selected
如果想要修改日期的显示格式，也可以通过
to_char*
等函数来实现，这些后面会有单独一章讲解，其次，如果你发现你通过sqlplus无法展示的如我这样一行显示完全，可以通过设置
set linesize
、
set pagesize
、
col ename for a20
等等设置页面格式的命令自定义，或则通过plsql的命令行访问，博主就是用的plsql命令行展示的！
二、从表中检索部分行
如果想看公司有多少销售人员，那么在查询数据时只需加一个过滤条件就可以。职位列是job,销售人员条件就是
WHERE job='SALESMAN':
SQL>SELECT*FROM emp WHERE job ='SALESMAN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7499 ALLEN SALESMAN 76981981-2-201600.00300.00307521 WARD SALESMAN 76981981-2-221250.00500.00307654 MARTIN SALESMAN 76981981-9-281250.001400.00307844 TURNER SALESMAN 76981981-9-81500.000.0030
三、查找空值
如果要查询某一列为空的数据怎么办呢？比如，返回提成(comm)为空的数据。
SQL>SELECT*FROM emp WHERE comm=NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
从步骤1中明显能看到comm有为null的数据，为什么这里查不到呢？
原因是NULL是不能用"="运算符的，要用
IS NULL
判断，正确的写法如下。
SQL>select*from emp where comm isnull;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH CLERK 79021980-12-17800.00207566 JONES MANAGER 78391981-4-22975.00207698 BLAKE MANAGER 78391981-5-12850.00307782 CLARK MANAGER 78391981-6-92450.00107788 SCOTT ANALYST 75661987-4-193000.00207839 KING PRESIDENT 1981-11-175000.00107876 ADAMS CLERK 77881987-5-231100.00207900 JAMES CLERK 76981981-12-3950.00307902 FORD ANALYST 75661981-12-33000.00207934 MILLER CLERK 77821982-1-231300.00101001 test 2021-10-9111rows selected
NULL不支持加、减、乘、除、大小比较、相等比较，否则只能为空。
SQL>select*from dept where1>=null;
DEPTNO DNAME LOC
------ -------------- -------------SQL>select*from dept where1<=null;
DEPTNO DNAME LOC
------ -------------- -------------SQL>select*from dept where1+null>=0;
DEPTNO DNAME LOC
------ -------------- -------------SQL>select*from dept where1+null<=0;
DEPTNO DNAME LOC
------ -------------- -------------SQL>select*from dept where1*null>=0;
DEPTNO DNAME LOC
------ -------------- -------------SQL>select*from dept where1*null<=0;
DEPTNO DNAME LOC
------ -------------- -------------SQL>
当然了，在使用其他函数的时候最好测试一下有NULL时会返回什么结果。
SQL>selectreplace('abcde','b',null)from dual;REPLACE('ABCDE','B',NULL)--------------------------------
acde
SQL>select greatest(1,null)from dual;
GREATEST(1,NULL)----------------SQL>
可以看到，不同的函数对NULL的支持也不一样，所以大家遇到NULL时最好测试一下，而不是仅凭经验或猜测！
四、拿”返回个值中第一个不为空的值”证明coalesce比nvl好用
对于下面sql：
SELECTcoalesce(comm,0)FROM emp;
可能有人会问为什么用
coalesce
而不用
nvl
？当然是
coalesce
更好用了！拿”返回个值中第一个不为空的值”这个需求来说：
SQL>with t as(2selectnullas a,nullas b,'z'as c,nullas d,'f'as e,nullas f from dual
3unionall4selectnullas a,nullas b,nullas c,'y'as d,nullas e,'a'as f from dual
5unionall6selectnullas a,nullas b,nullas c,nullas d,'d'as e,'s'as f from dual
7)8selectcoalesce(a,b,c,d,e,f)from t
9;COALESCE(A,B,C,D,E,F)---------------------
z
y
d
可以看到，相对nvl来说，
coalesce
支持多个参数，能很方便地返回第一个不为空的值。如果上面的语句改用nvl,就要嵌套很多层。
SELECT nvl(nvl(nvl(nvl(nvl(a,b),c),d),e),f)FROM t;
五、查找满足多个条件的行
简单的查询，写起来容易，那复杂一点的呢？
比如，我们要查询部门10中的所有员工、所有得到提成的员工以及部门20中工资不超过2000美元的员工。
这是三个条件的组合，符合上述任一条件即可。
我们把这三个条件整理成逻辑表达式的形式：（部门10中的员工OR所有得到提成的员工OR(工资<=2000and部门号=20))
那我们这么写这个需求：
SELECT*FROM EMP
WHERE(DEPTNO =10OR COMM ISNOTNULLOR(SAL <=2000and DEPTNO =20));
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH CLERK 79021980-12-17800.00207499 ALLEN SALESMAN 76981981-2-201600.00300.00307521 WARD SALESMAN 76981981-2-221250.00500.00307654 MARTIN SALESMAN 76981981-9-281250.001400.00307782 CLARK MANAGER 78391981-6-92450.00107839 KING PRESIDENT 1981-11-175000.00107844 TURNER SALESMAN 76981981-9-81500.000.00307876 ADAMS CLERK 77881987-5-231100.00207934 MILLER CLERK 77821982-1-231300.00109rows selected
对于多个条件的组合，要使用括号，这样在更改维护语句时可以不必再考虑优先级问题，而且可以很容易地借助各种工具找到各组合条件的起止位置。
六、从表中检索部分列
前面我们都是取表中所有的列，但实际的场景中，常常只需要返回部分列的数据就可以。如只需员工编码、员工名称、雇佣日期、工资。所以一般要明确指定查询哪些列，而不是用"*"号来代替。另外，明确要返回的列也会使语句的维护更简单，而不必每次看到语句时都需要查看表结构才知道会返回什么数据。
SQL>SELECT empno, ename, hiredate, sal FROM emp WHERE deptno =10;
EMPNO ENAME HIREDATE SAL
----- ---------- ----------- ---------7782 CLARK 1981-6-92450.007839 KING 1981-11-175000.007934 MILLER 1982-1-231300.00
七、为列取别名
不是每个人都能看懂那些简写的字母字段什么意思，所以有时候需要给列取个别名。可以如下面所示在as后面跟别名，也可以不要as,直接在列名后跟别名即可。
SQL>SELECT ename AS 姓名, deptno AS 部门编号,sal AS 工资,comm AS 提成 FROM emp;
姓名 部门编号 工资 提成
---------- ---- --------- ---------
SMITH 20800.00
ALLEN 301600.00300.00
WARD 301250.00500.00
JONES 202975.00
MARTIN 301250.001400.00
BLAKE 302850.00
CLARK 102450.00
SCOTT 203000.00
KING 105000.00
TURNER 301500.000.00
ADAMS 201100.00
JAMES 30950.00
FORD 203000.00
MILLER 101300.00
test
15rows selected
八、在 WHERE 子句中引用取别名的列
写报表时，经常会加上各种条件，而直接在条件中使用别名比列名（如：d001,n002)要清晰得多，引用别名时千万别忘了嵌套一层，因为这个别名是在SELECT之后才有效的．
SQL>SQL>SELECT*2FROM(SELECT sal AS 工资, comm AS 提成 from emp) X
3WHERE 工资 <1000;
工资 提成
--------- ---------800.00950.00
如果你不嵌套一层，会报错的！如下：
SQL>SELECT sal AS 工资, comm AS 提成 from emp
2WHERE 工资 <1000;SELECT sal AS 工资, comm AS 提成 from emp
WHERE 工资 <1000
ORA-00904: "工资": 标识符无效
九、拼接列实现批量脚本
若有人不喜欢看表格式的数据，希望返回的数据都像"CLARK的工作是MANAGER"这样的显示。我们可以用字符串连接符"||"来把各列拼在一起。
SQL>SELECT ename ||'的工作是'|| job AS msg FROM emp WHERE deptno =10;
MSG
---------------------------------------------------
CLARK的工作是MANAGER
KING的工作是PRESIDENT
MILLER的工作是CLERK
当然，拼接列还有更多意义，比如我平时对某个用户下所有表收集统计信息，我就可以这么写脚本来生成批量脚本：
select'begin'||chr(13)||'dbms_stats.lock_table_stats(ownname =>'''||a.OWNER||''',tabname =>'''||a.TABLE_NAME||''');'||chr(13)||'END;'||chr(13)||'/'||chr(13)||'prompt '||rownum
from dba_tables a where a.OWNER in('ZYD');SQL>select'begin'||chr(13)||2'dbms_stats.lock_table_stats(ownname =>'''||a.OWNER||''',tabname =>'''||a.TABLE_NAME||''');'||chr(13)||'END;'||chr(13)||'/'||chr(13)||'prompt '||rownum
3from dba_tables a where a.OWNER in('ZYD');'BEGIN'||CHR(13)||'DBMS_STATS.LOCK_TABLE_STATS(OWNNAME=>'''||A.OWNER||''',TABNAM
--------------------------------------------------------------------------------
begin
dbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_ORACLE_HIVE');
END;
/
prompt 1
begin
dbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_HIVE_ORACLE');END;/
prompt 2begin
十、在 SELECT 语句中使用case when条件逻辑
有时为了更清楚地区分返回的信息，需要做如下处理。
如：当职员工资小于或等于2000美元时，就返回消息“过低“，大于或等于4000美元时，就返回消息“过高”，如果在这两者之间，就返回"OK"。
类似这种需求也许会经常遇见，处理这样的需求可以用
CASE WHEN
来判断转化
select ename,
sal,CASEWHEN sal <=2000THEN'过 低'WHEN sal >=4000THEN'过高 'ELSE' OK 'ENDASstatusFROM emp
WHERE deptno =10;
ENAME SAL STATUS---------- --------- ------
CLARK 2450.00 OK
KING 5000.00 过高
MILLER 1300.00 过 低
这种方式还常用在报表中，比如要按工资分档次统计人数：
SELECT 档次,COUNT(*)AS 人数
from(SELECT(CASEWHEN sal <=1000THEN'0000-1000'WHEN sal <=2000THEN'1000-2000'WHEN sal <=3000THEN'2000-3000'WHEN sal <=4000THEN'3000-4000'WHEN sal <=5000THEN'4000-5000'ELSE'好高'END)AS 档次,
ename,
sal
FROM emp)GROUPBY 档次
ORDERBY1;
档次 人数
--------- ----------0000-100021000-200062000-300054000-50001
好高 1
十一、限制返回的行数
在查询时，并不要求每次都要返回所有的数据，比如，进行抽查的时候会要求只返回两条数据。
我们可以用伪列rownum来过滤，rownum依次对返回的每一条数据做一个标识。
SQL>SQL>SELECT*FROM emp WHERE rownum<=2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH CLERK 79021980-12-17800.00207499 ALLEN SALESMAN 76981981-2-201600.00300.0030
如果直接用
rownum=2
来查询会出现会什么情况？
SQL>SELECT*FROM emp WHERE rownum=2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------SQL>
因为rownum是依次对数据做标识的，就像上学时依据考分排名一样，需要有第一名，后面才会有第二名。所以，要先把所有的数据取出来，才能确认第二名。
正确地取第二行数据的查询应该像下面这样，先生成序号：
SQL>SELECT*2FROM(SELECT rownum AS sn, emp.*FROM emp WHERE rownum <=2)3WHERE sn =2;
SN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------27499 ALLEN SALESMAN 76981981-2-201600.00300.0030SQL>
十二、你真的会从表中随机返回 n 条记录吗
我们这里的目标是随机返回N条记录，大多开发者在这里都会误认为自己平时随机取数据的方式对了！接下来我们一步步分析。
我们可以先用
dbms_random
来对数据进行随机排序，然后取其中三行。
SELECT empno,ename
FROM(select empno,ename FROM emp ORDERBY dbms_random.value())WHERE rownum <=3;
有人会问：为什么要嵌套一层呢？直接这样用多好。
SELECT empno , ename FROM emp WHERE rownum <=3ORDERBY dbms_random.value();
你可以运行一下看， 为了方便观察， 我们对得到的结果进行排序 ， 运行下面的语句就可以。
SELECT*FROM(SELECT empno, ename
FROM emp
WHERE rownum <=3ORDERBY dbms_random.value())ORDERBY1;
多运行几次，会发现是不是每次得到的数据都一样，而不是随机？为了便于解释，我们先对上面的语句进行等价改写：
SELECT empno, ename, dbms_random.value() ran
FROM emp
WHERE rownum <=3ORDERBY ran;
查询语句中这几处的执行顺序为：
- SELECT
- ROWNUM
- ORDER BY
也就是说，要先取出数据，然后生成序号，最后才是排序。我们可以通过子查询把排序前后的序号分别取出来对比。
SELECT rownum AS 排序后, 排序前, empno AS 编码, ename 姓名, ran AS 随机数
FROM(SELECT rownum AS 排序前, empno, ename, dbms_random.value() ran
FROM emp
WHERE rownum <=3ORDERBY ran);
同样，你可以运行几次，看是不是与刚才描述的一致。因此，正确的写法是：先随机排序，再取数据。
SELECT empno, ename
FROM(SELECT empno, ename FROM emp ORDERBY dbms_random.value())WHERE rownum <=3;
错误的写法是： 先取数据， 再随机排序 。
SELECT empno , ename FROM emp WHERE rownum <=3ORDERBY dbms_random.value();
十三、模糊查询，使用escape转译字符
有如下临时表：
with t as(SELECT'ABCEDF'AS vname FROM dual
UNIONALLSELECT'_BCEFG'AS vname FROM dual
UNIONALLSELECT'_BCBPF'AS vname FROM dual
UNIONALLSELECT'_\BCEDF'AS vname FROM dual
UNIONALLSELECT'XYCEG'AS vname FROM dual )select*from t
要求一： 查出 vname 中包含字符串 " CED " 的。
with t as(SELECT'ABCEDF'AS vname FROM dual
UNIONALLSELECT'_BCEFG'AS vname FROM dual
UNIONALLSELECT'_BCBPF'AS vname FROM dual
UNIONALLSELECT'_\BCEDF'AS vname FROM dual
UNIONALLSELECT'XYCEG'AS vname FROM dual )select*from t where vname like'%CED%';
VNAME
-------
ABCEDF
_\BCEDF
要求二：查出vname中包含字符串"BCE"的。这里我们需要注意，””在模糊查询是会被当做通配符的，“%”代表替代一个或多个字符“”替代一个字符。所以这个需求我们需要用转译字符来转译“”字符,我们可以使用escape关键字把’\’标识为转译字符，那么查询如下：
with t as(SELECT'ABCEDF'AS vname FROM dual
UNIONALLSELECT'_BCEFG'AS vname FROM dual
UNIONALLSELECT'_BCBPF'AS vname FROM dual
UNIONALLSELECT'_\BCEDF'AS vname FROM dual
UNIONALLSELECT'XYCEG'AS vname FROM dual )select*from t where vname like'\_BCE%'escape '\';
VNAME
-------
_BCEFG
要求三：查出vname中包含字符串"_\BCE"的。根据上面解释，我们可以这么写！
with t as(SELECT'ABCEDF'AS vname FROM dual
UNIONALLSELECT'_BCEFG'AS vname FROM dual
UNIONALLSELECT'_BCBPF'AS vname FROM dual
UNIONALLSELECT'_\BCEDF'AS vname FROM dual
UNIONALLSELECT'XYCEG'AS vname FROM dual )select*from t where vname like'\_\\BCE%'escape '\';
VNAME
-------
_\BCEDF
对于类似“%”的转译我们也可以参照这么写。
总结
以上就是本章内容，文章写起来虽然麻烦，但是写完了回头一看还是很有成就感~
版权归原作者 赵延东的一亩三分地 所有， 如有侵权，请联系我们删除。