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

---

前言

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

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

---

总结

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