前言

本篇文章介绍一些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)
--------------------------------
acde

SQL> 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)
---------------------
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 = 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               10

9 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.00
WARD         30   1250.00    500.00
JONES        20   2975.00 
MARTIN       30   1250.00   1400.00
BLAKE        30   2850.00 
CLARK        10   2450.00 
SCOTT        20   3000.00 
KING         10   5000.00 
TURNER       30   1500.00      0.00
ADAMS        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 工资 <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
  3  from 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 2
begin

十、在 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  OK
KING         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          2
1000-2000          6
2000-3000          5
4000-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     30

SQL>

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

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

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();

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

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 dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t

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

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

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

总结

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

【SQL开发实战技巧】系列（二）：简单单表查询

前言

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

二、从表中检索部分行

三、查找空值

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

五、查找满足多个条件的行

六、从表中检索部分列

七、为列取别名

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

九、拼接列实现批量脚本

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

十一、限制返回的行数

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

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

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【SQL开发实战技巧】系列（二）：简单单表查询

前言

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

二、从表中检索部分行

三、查找空值

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

五、查找满足多个条件的行

六、从表中检索部分列

七、为列取别名

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

九、拼接列实现批量脚本

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

十一、限制返回的行数

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

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

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景