系列文章目录

【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开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

• 系列文章目录
• 前言
• 一、查询表中所有的行与列
• 二、从表中检索部分行
• 三、查找空值
• 四、拿”返回个值中第一个不为空的值”证明coalesce比nvl好用
• 五、查找满足多个条件的行
• 六、从表中检索部分列
• 七、为列取别名
• 八、在 WHERE 子句中引用取别名的列
• 九、拼接列实现批量脚本
• 十、在 SELECT 语句中使用case when条件逻辑
• 十一、限制返回的行数
• 十二、你真的会从表中随机返回 n 条记录吗
• 十三、模糊查询，使用escape转译字符
• 总结

前言

本篇文章介绍一些SQL开发基础的内容，讲解的主要内容是：NULL空值的运算、coalesce比nvl的优势、拼接列实现批量脚本、如何通过rownum限制返回的行数以及如何正确的使用rownum从表中随机返回 n 条记录。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、查询表中所有的行与列

进行查询操作之前， 我们先看一下表结构，我这里查询的是oracle官方案例用户scott下的表。

SQL> desc scott.emp; Name   Null?    Type ----------------------------------------- -------- ---------------------------- EMPNO   NOT NULL 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      7902 1980-12-17     800.00               20 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 7839 KING       PRESIDENT       1981-11-17    5000.00               10 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 1001 test                       2021-10-9 1                     15 rows 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   7698 1981-2-20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

三、查找空值

如果要查询某一列为空的数据怎么办呢？比如，返回提成(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 is null;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980-12-17     800.00               20 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 7839 KING       PRESIDENT       1981-11-17    5000.00               10 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 1001 test                       2021-10-9 1                     11 rows selected

NULL不支持加、减、乘、除、大小比较、相等比较，否则只能为空。

SQL> select * from dept where 1>=null;DEPTNO DNAME          LOC------ -------------- -------------SQL> select * from dept where 1<=null;DEPTNO DNAME          LOC------ -------------- -------------SQL> select * from dept where 1+null>=0;DEPTNO DNAME          LOC------ -------------- -------------SQL> select * from dept where 1+null<=0;DEPTNO DNAME          LOC------ -------------- -------------SQL> select * from dept where 1*null>=0;DEPTNO DNAME          LOC------ -------------- -------------SQL> select * from dept where 1*null<=0;DEPTNO DNAME          LOC------ -------------- -------------SQL> 

当然了，在使用其他函数的时候最好测试一下有NULL时会返回什么结果。

SQL> select replace('abcde','b',null) from dual;REPLACE('ABCDE','B',NULL)--------------------------------acdeSQL> select greatest(1,null) from dual;GREATEST(1,NULL)----------------SQL> 

可以看到，不同的函数对NULL的支持也不一样，所以大家遇到NULL时最好测试一下，而不是仅凭经验或猜测！

四、拿”返回个值中第一个不为空的值”证明coalesce比nvl好用

对于下面sql：

SELECT coalesce(comm,0) FROM emp;

可能有人会问为什么用coalesce而不用nvl？当然是coalesce更好用了！拿”返回个值中第一个不为空的值”这个需求来说：

SQL> with t as (  2  select null as a,null as b,'z' as c,null as d,'f' as e,null as f from dual  3  union all  4  select null as a,null as b,null as c,'y' as d,null as e,'a' as f from dual  5  union all  6  select null as a,null as b,null as c,null as d,'d' as e,'s' as f from dual  7  )  8  select coalesce(a,b,c,d,e,f) from t  9  ;COALESCE(A,B,C,D,E,F)---------------------zyd

可以看到，相对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 = 10 OR COMM IS NOT NULL OR (SAL <= 2000 and DEPTNO = 20));EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980-12-17     800.00               20 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 7839 KING       PRESIDENT       1981-11-17    5000.00               10 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 7934 MILLER     CLERK      7782 1982-1-23     1300.00               109 rows selected

对于多个条件的组合，要使用括号，这样在更改维护语句时可以不必再考虑优先级问题，而且可以很容易地借助各种工具找到各组合条件的起止位置。

六、从表中检索部分列

前面我们都是取表中所有的列，但实际的场景中，常常只需要返回部分列的数据就可以。如只需员工编码、员工名称、雇佣日期、工资。所以一般要明确指定查询哪些列，而不是用”*”号来代替。另外，明确要返回的列也会使语句的维护更简单，而不必每次看到语句时都需要查看表结构才知道会返回什么数据。

SQL> SELECT empno, ename, hiredate, sal FROM emp WHERE deptno = 10;EMPNO ENAME      HIREDATE          SAL----- ---------- ----------- --------- 7782 CLARK      1981-6-9      2450.00 7839 KING       1981-11-17    5000.00 7934 MILLER     1982-1-23     1300.00

七、为列取别名

不是每个人都能看懂那些简写的字母字段什么意思，所以有时候需要给列取个别名。可以如下面所示在as后面跟别名，也可以不要as,直接在列名后跟别名即可。

SQL> SELECT ename AS 姓名, deptno AS 部门编号,sal AS 工资,comm AS 提成 FROM emp;姓名       部门编号        工资        提成---------- ---- --------- ---------SMITH        20    800.00 ALLEN        30   1600.00    300.00WARD         30   1250.00    500.00JONES        20   2975.00 MARTIN       30   1250.00   1400.00BLAKE        30   2850.00 CLARK        10   2450.00 SCOTT        20   3000.00 KING         10   5000.00 TURNER       30   1500.00      0.00ADAMS        20   1100.00 JAMES        30    950.00 FORD         20   3000.00 MILLER       10   1300.00 test                      15 rows selected

八、在 WHERE 子句中引用取别名的列

写报表时，经常会加上各种条件，而直接在条件中使用别名比列名（如：d001,n002)要清晰得多，引用别名时千万别忘了嵌套一层，因为这个别名是在SELECT之后才有效的．

SQL> SQL> SELECT *  2    FROM (SELECT sal AS 工资, comm  AS 提成 from emp) X  3   WHERE 工资 <1000;       工资        提成--------- ---------   800.00    950.00 

如果你不嵌套一层，会报错的！如下：

SQL> SELECT sal AS 工资, comm  AS 提成 from emp  2   WHERE 工资 <1000;SELECT sal AS 工资, comm  AS 提成 from emp WHERE 工资 <1000ORA-00904: "工资": 标识符无效

九、拼接列实现批量脚本

若有人不喜欢看表格式的数据，希望返回的数据都像”CLARK的工作是MANAGER”这样的显示。我们可以用字符串连接符”||”来把各列拼在一起。

SQL> SELECT ename || '的工作是'|| job AS msg FROM emp WHERE deptno = 10 ;MSG---------------------------------------------------CLARK的工作是MANAGERKING的工作是PRESIDENTMILLER的工作是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 '||rownumfrom 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  3  from dba_tables a where a.OWNER in('ZYD');'BEGIN'||CHR(13)||'DBMS_STATS.LOCK_TABLE_STATS(OWNNAME=>'''||A.OWNER||''',TABNAM--------------------------------------------------------------------------------begindbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_ORACLE_HIVE');END;/prompt 1begindbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_HIVE_ORACLE');END;/prompt 2begin

十、在 SELECT 语句中使用case when条件逻辑

有时为了更清楚地区分返回的信息，需要做如下处理。
如：当职员工资小于或等于2000美元时，就返回消息“过低“，大于或等于4000美元时，就返回消息“过高”，如果在这两者之间，就返回”OK”。
类似这种需求也许会经常遇见，处理这样的需求可以用CASE WHEN来判断转化

select ename,       sal,       CASE         WHEN sal <= 2000 THEN          '过 低'         WHEN sal > = 4000 THEN          '过高 '         ELSE          ' OK '       END AS status  FROM emp WHERE deptno = 10;ENAME            SAL STATUS---------- --------- ------CLARK        2450.00  OKKING         5000.00 过高MILLER       1300.00 过 低

这种方式还常用在报表中，比如要按工资分档次统计人数：

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;档次              人数--------- ----------0000-1000          21000-2000          62000-3000          54000-5000          1好高               1

十一、限制返回的行数

在查询时，并不要求每次都要返回所有的数据，比如，进行抽查的时候会要求只返回两条数据。
我们可以用伪列rownum来过滤，rownum依次对返回的每一条数据做一个标识。

SQL> 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-2-20     1600.00    300.00     30

如果直接用rownum=2来查询会出现会什么情况？

SQL> SELECT * FROM emp WHERE rownum=2;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------SQL> 

因为rownum是依次对数据做标识的，就像上学时依据考分排名一样，需要有第一名，后面才会有第二名。所以，要先把所有的数据取出来，才能确认第二名。
正确地取第二行数据的查询应该像下面这样，先生成序号：

SQL> SELECT *  2    FROM (SELECT rownum AS sn, emp.* FROM emp WHERE rownum <= 2)  3   WHERE sn = 2;        SN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO---------- ----- ---------- --------- ----- ----------- --------- --------- ------         2  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30SQL> 

十二、你真的会从表中随机返回 n 条记录吗

我们这里的目标是随机返回N条记录，大多开发者在这里都会误认为自己平时随机取数据的方式对了！接下来我们一步步分析。
我们可以先用dbms_random来对数据进行随机排序，然后取其中三行。

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

有人会问：为什么要嵌套一层呢？直接这样用多好。

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

你可以运行一下看， 为了方便观察， 我们对得到的结果进行排序 ， 运行下面的语句就可以。

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

多运行几次，会发现是不是每次得到的数据都一样，而不是随机？为了便于解释，我们先对上面的语句进行等价改写：

SELECT empno, ename, dbms_random.value() ran  FROM emp WHERE rownum <= 3 ORDER BY 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 <= 3         ORDER BY ran);

同样，你可以运行几次，看是不是与刚才描述的一致。因此，正确的写法是：先随机排序，再取数据。

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

错误的写法是： 先取数据， 再随机排序 。

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

十三、模糊查询，使用escape转译字符

有如下临时表：

with t as (SELECT 'ABCEDF' AS vname FROM dualUNION ALLSELECT '_BCEFG' AS vname FROM dualUNION ALLSELECT '_BCBPF' AS vname FROM dualUNION ALLSELECT '_\BCEDF' AS vname FROM dualUNION ALLSELECT 'XYCEG' AS vname FROM dual )select * from t 

要求一： 查出 vname 中包含字符串 ” CED ” 的。

with t as (SELECT 'ABCEDF' AS vname FROM dualUNION ALLSELECT '_BCEFG' AS vname FROM dualUNION ALLSELECT '_BCBPF' AS vname FROM dualUNION ALLSELECT '_\BCEDF' AS vname FROM dualUNION ALLSELECT '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 dualUNION ALLSELECT '_BCEFG' AS vname FROM dualUNION ALLSELECT '_BCBPF' AS vname FROM dualUNION ALLSELECT '_\BCEDF' AS vname FROM dualUNION ALLSELECT '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 dualUNION ALLSELECT '_BCEFG' AS vname FROM dualUNION ALLSELECT '_BCBPF' AS vname FROM dualUNION ALLSELECT '_\BCEDF' AS vname FROM dualUNION ALLSELECT 'XYCEG' AS vname FROM dual )select * from t where  vname like '\_\\BCE%' escape '\';VNAME-------_\BCEDF

对于类似“%”的转译我们也可以参照这么写。

总结

以上就是本章内容，文章写起来虽然麻烦，但是写完了回头一看还是很有成就感~