第 1 章 检索记录

第 1 章 检索记录

本章主要介绍基本的 SELECT 语句。充分理解这些基础知识十分重要,因为本章中的许多内容不仅会出现在后面更复杂的实例里,同时也是日常 SQL 操作的一部分。

1.1 检索所有行和列

  1. 问题

    你有一张表,并且想查看表中的所有数据。

  2. 解决方案

    用特殊符号“*”对该表执行 SELECT 查询。

    1 select *
    2   from emp
    
  3. 讨论

    在 SQL 中,符号“*”有着特殊含义。该符号使得查询语句返回指定表的所有列。由于没有指定 WHERE 子句,因此所有行都会被提取出来。你也可以使用另一种方法,列出表中的每一列。

    select empno,ename,job,sal,mgr,hiredate,comm,deptno
      from emp
    
    

    在交互式即席查询中,使用 SELECT * 会更加容易。然而,在编写程序代码时,最好具体指明每一列。虽然执行结果相同,但指明每一列让你能清楚地知道查询语句会返回哪些列。类似地,对于其他人而言,这样的查询语句也会更易于理解,因为他们可能不知道所要查询的表里包含哪些列。

1.2 筛选行

  1. 问题

    你有一张表,并且只想查看满足指定条件的行。

  2. 解决方案

    使用 WHERE 子句指明保留哪些行。例如,下面的语句将查找部门编号为 10 的所有员工。

    1 select *
    2   from emp
    3  where deptno = 10
    
  3. 讨论

    可以使用 WHERE 子句来筛选出我们感兴趣的行。如果 WHERE 子句的表达式针对某一行的判定结果为真,那么就会返回该行的数据。

    大多数数据库都支持常用的运算符,例如 =<><=>=!<>。除此之外,你可能需要指定多个条件来筛选数据,这时就需要使用 ANDOR 和圆括号。下一个实例将讨论这一点。

1.3 查找满足多个查询条件的行

  1. 问题

    你想返回满足多个查询条件的行。

  2. 解决方案

    使用带有 ORAND 条件的 WHERE 子句。例如,如果你想找出部门编号为 10 的所有员工、有业务提成的所有员工以及部门编号是 20 且工资低于 2000 美元的所有员工。

    1 select *
    2   from emp
    3  where deptno = 10
    4     or comm is not null
    5     or sal <= 2000 and deptno=20
    
  3. 讨论

    你可以组合使用 ANDOR 和圆括号来筛选满足多个查询条件的行。在这个实例中,WHERE 子句找出了如下的数据。

    • DEPTNO 等于 10,或
    • COMM 不是 Null,或
    • DEPTNO 等于 20 且工资不高于 2000 美元的员工。

    圆括号里的查询条件被一起评估。例如,试想一下如果采用下面的做法,检索结果会发生什么样的变化。

    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 17-DEC-1980   800                20
     7876 ADAMS  CLERK   7788 12-JAN-1983  1100                20
    

1.4 筛选列

  1. 问题

    你有一张表,并且只想查看特定列的值。

  2. 解决方案

    指定你感兴趣的列。例如,只查看员工的名字、部门编号和工资。

    1 select ename,deptno,sal
    2   from emp
    
  3. 讨论

    SELECT 语句里指定具体的列名,可以确保查询语句不会返回无关的数据。当在整个网络范围内检索数据时,这样做尤为重要,因为它避免了把时间浪费在检索不需要的数据上。

1.5 创建有意义的列名

  1. 问题

    你可能想要修改检索结果的列名,使其更具可读性且更易于理解。考虑下面这个查询,它返回的是每个员工的工资和业务提成。

    1 select sal,comm
    2   from emp
    
    

    sal 指的是什么?是 sale 的缩写吗?是人名吗? comm 又是什么?是 communication 的缩写吗?显然,检索结果应该让人容易理解。

  2. 解决方案

    使用 AS 关键字,并以 original_name AS new_name 的形式来修改检索结果的列名。对于一些数据库而言,AS 不是必需的,但所有的数据库都支持这个关键字。

    1 select sal as salary, comm as commission
    2   from emp
     
    SALARY  COMMISSION
    ------- ----------
        800
       1600        300
       1250        500
       2975
       1250       1300
       2850
       2450
       3000
       5000
       1500          0
       1100
        950
       3000
       1300
    
  3. 讨论

    使用 AS 关键字重新命名查询所返回的列,即是创建别名。新的列名被称作别名。创建好的别名对于查询语句大有裨益,它能让查询结果更易于理解。

1.6 在WHERE子句中引用别名列

  1. 问题

    你已经为检索结果集创建了有意义的列名,并且想利用 WHERE 子句过滤掉部分行数据。但是,如果你尝试在 WHERE 子句中引用别名列,查询无法顺利执行。

    select sal as salary, comm as commission
      from emp
     where salary < 5000
    
  2. 解决方案

    把查询包装为一个内嵌视图,这样就可以引用别名列了。

    1 select *
    2   from (
    3 select sal as salary, comm as commission
    4   from emp
    5        ) x
    6  where salary < 5000
    
  3. 讨论

    在这个简单的实例中,你可以不使用内嵌视图。在 WHERE 子句里直接引用 COMM 列和 SAL 列,也可以达到同样的效果。当你想在 WHERE 子句中引用下列内容时,这个解决方案告诉你该如何做。

    • 聚合函数
    • 标量子查询
    • 窗口函数
    • 别名

    将含有别名列的查询放入内嵌视图,就可以在外层查询中引用别名列。为什么要这么做呢? WHERE 子句会比 SELECT 子句先执行,就最初那个失败的查询例子而言,当 WHERE 子句被执行时,SALARYCOMMISSION 尚不存在。直到 WHERE 子句执行完毕,那些别名列才会生效。然而,FROM 子句会先于 WHERE 子句执行。如果把最初的那个查询放入一个 FROM 子句,其查询结果会在最外层的 WHERE 子句开始之前产生,这样一来,最外层的 WHERE 子句就能“看见”别名列了。当表里的某些列没有被恰当命名的时候,这个技巧尤其有用。

     在本例中,内嵌视图的别名为 X。并非所有数据库都需要给内嵌视图取别名,但对于某些数据库而言,确实必须如此。不过,所有的数据库都支持这一点。

1.7 串联多列的值

  1. 问题

    你想将多列的值合并为一列。例如,你想查询 EMP 表,并获得如下结果集。

    CLARK WORKS AS A MANAGER
    KING WORKS AS A PRESIDENT
    MILLER WORKS AS A CLERK
    
    

    然而,你需要的数据来自 EMP 表的 ENAME 列和 JOB 列。

    select ename, job
      from emp
     where deptno = 10
     
    ENAME      JOB
    ---------- ---------
    CLARK      MANAGER
    KING       PRESIDENT
    MILLER     CLERK
    
  2. 解决方案

    使用数据库中的内置函数来串联多列的值。

    DB2、Oracle 和 PostgreSQL

    这些数据库把双竖线作为串联运算符。

    1 select ename||' WORKS AS A '||job as msg
    2   from emp
    3  where deptno=10
    
    

    MySQL

    该数据库使用 CONCAT 函数。

    1 select concat(ename, ' WORKS AS A ',job) as msg
    2   from emp
    3  where deptno=10
    
    

    SQL Server

    该数据库使用“+”作为串联运算符。

    1 select ename + ' WORKS AS A ' + job as msg
    2   from emp
    3  where deptno=10
    
  3. 讨论

    使用 CONCAT 函数可以串联多列的值。在 DB2、Oracle 和 PostgreSQL 中,“||”是 CONCAT 函数的快捷方式,在 SQL Server 中则为“+”。

1.8 在SELECT语句里使用条件逻辑

  1. 问题

    你想在 SELECT 语句中针对查询结果值执行 IF-ELSE 操作。例如,你想生成类似这样的结果:如果员工的工资少于 2000 美元,就返回 UNDERPAID;如果超过 4000 美元就返回 OVERPAID;若介于两者之间则返回 OK。查询结果如下所示。

    ENAME             SAL STATUS
    ---------- ---------- ---------
    SMITH             800 UNDERPAID
    ALLEN            1600 UNDERPAID
    WARD             1250 UNDERPAID
    JONES            2975 OK
    MARTIN           1250 UNDERPAID
    BLAKE            2850 OK
    CLARK            2450 OK
    SCOTT            3000 OK
    KING             5000 OVERPAID
    TURNER           1500 UNDERPAID
    ADAMS            1100 UNDERPAID
    JAMES             950 UNDERPAID
    FORD             3000 OK
    MILLER           1300 UNDERPAID
    
  2. 解决方案

    SELECT 语句里直接使用 CASE 表达式来执行条件逻辑。

    1 select ename,sal,
    2        case when sal <= 2000 then 'UNDERPAID'
    3             when sal >= 4000 then 'OVERPAID'
    4             else 'OK'
    5        end as status
    6   from emp
    
  3. 讨论

    CASE 表达式能对查询结果执行条件逻辑判断。你可以为 CASE 表达式的执行结果取一个别名,使结果集更有可读性。就本例而言,STATUS 就是 CASE 表达式执行结果的别名。ELSE 子句是可选的,若没有它,对于不满足测试条件的行,CASE 表达式会返回 Null

1.9 限定返回行数

  1. 问题

    你想限定查询结果的行数。你不关心排序,任意 n 行都可以。

  2. 解决方案

    使用数据库的内置功能来控制返回的行数。

    DB2

    使用 FETCH FIRST 子句。

    1 select *
    2   from emp fetch first 5 rows only
    
    

    MySQL 和 PostgreSQL

    使用 LIMIT 子句。

    1 select *
    2   from emp limit 5
    
    

    Oracle

    对于 Oracle 而言,通过在 WHERE 子句中限制 ROWNUM 的值来获得指定行数的结果集。

    1 select *
    2   from emp
    3  where rownum <= 5
    
    

    SQL Server

    使用 TOP 关键字限定返回行数。

    1 select top 5 *
    2   from emp
    
  3. 讨论

    许多数据库提供了类似 FETCH FIRSTLIMIT 这样的子句来指定查询结果的行数。Oracle 与此不同,你必须使用 ROWNUM 的函数,该函数会为结果集里的每一行指定一个行号(从 1 开始,逐渐增大)。

    当你使用 ROWNUM<=5 限定只返回最初的 5 行数据时,会发生如下的事情。

    (1) Oracle 执行查询。

    (2) Oracle 取得第一行数据,并把它的行号定为 1。

    (3) 已经超过第 5 行了吗?如果没有,Oracle 会返回当前行,因为当前的行号满足小于或等于 5 这一条件。如果已经超过,那么 Oracle 就不返回当前行。

    (4) Oracle 取得下一行数据,并且将行号加 1(得到 2,然后得到 3,再然后得到 4,以此类推)。

    (5) 返回第 3 步。

    如上述处理过程所示,Oracle 会在取得某一行数据之后再为其编号,这是关键之处。很多 Oracle 开发人员试图只获取一行数据,比如指定 ROWNUM=5,希望只返回第 5 行。但是,同时使用 ROWNUM 和等式条件是不对的。以下是使用 ROWNUM=5 后实际发生的事情。

    (1) Oracle 执行查询。

    (2) Oracle 取得第一行数据,并把它的行号定为 1。

    (3) 已经到第 5 行了吗?如果没有,那么 Oracle 会舍弃这一行,因为它不符合条件。如果是,那么 Oracle 会返回当前行。但是,行号永远不可能到 5 !

    (4) Oracle 取得下一行数据,并把它的行号定为 1。这是因为查询结果的第 1 行的行号必须是 1。

    (5) 返回第 3 步。

    深入理解这一过程,你会明白为什么通过指定等式条件 ROWNUM=5 来获取第 5 行会失败。如果你不先获取第 1 行到第 4 行,第 5 行从何而来?

    你可能会注意到,ROWNUM=1 确实能得到第 1 行,这似乎与上述解释相矛盾。ROWNUM=1 运行正常的原因在于,Oracle 必须至少尝试一次读取,才能确定表里是否有记录。仔细阅读以上处理过程,用 1 替换 5,你就会理解为什么指定 ROWNUM=1 作为条件(为了返回一行)会成功。

1.10 随机返回若干行记录

  1. 问题

    你希望从表中获取特定数量的随机记录。修改下面的语句,以便连续执行查询并使结果集含有 5 行不同的数据。

    select ename, job
      from emp
    
  2. 解决方案

    使用数据库的内置函数来随机生成查询结果。在 ORDER BY 子句里使用该内置函数可以实现查询结果的随机排序。最后要结合 1.9 节中的技巧从随机排序结果里获取限定数目的行。

    DB2

    把内置函数 RANDORDER BYFETCH 结合使用。

    1 select ename,job
    2   from emp
    3  order by rand() fetch first 5 rows only
    
    

    MySQL

    把内置函数 RANDLIMITORDER BY 结合使用。

    1 select ename,job
    2   from emp
    3  order by rand() limit 5
    
    

    PostgreSQL

    把内置函数 RANDOMLIMITORDER BY 结合使用。

    1 select ename,job
    2   from emp
    3  order by random() limit 5
    
    

    Oracle

    在内置包 DBMS_RANDOM 里可以找到 VALUE 函数,把该内置函数和 ORDER BY、内置函数 ROWNUM 结合使用。

    1 select *
    2   from (
    3  select ename, job
    4    from emp
    6   order by dbms_random.value()
    7        )
    8   where rownum <= 5
    
    

    SQL Server

    同时使用内置函数 NEWIDTOPORDER BY 来返回一个随机结果集。

    1 select top 5 ename,job
    2   from emp
    3  order by newid()
    
  3. 讨论

    ORDER BY 子句能够接受一个函数的返回值,并利用该值改变当前结果集的顺序。在本例中,所有查询都是在 ORDER BY 子句执行结束后才限定返回值的行数。看过 Oracle 的解决方案后,非 Oracle 用户可能会受到启发,因为 Oracle 的解决方案展示了(在理论上)其他数据库内部是如何实现该查询的。

    不要误认为 ORDER BY 子句中的函数是数值常量,这一点很重要。如果 ORDER BY 子句使用数值常量,那么就需要按照 SELECT 列表里的顺序来排序。如果 ORDER BY 子句使用了函数,那么就需要按照该函数的返回值来排序,而函数返回的值是根据结果集里的每一行计算而来的。

1.11 查找Null

  1. 问题

    你想查找特定列的值为 Null 的所有行。

  2. 解决方案

    要判断一个值是否为 Null,必须使用 IS Null

    1 select *
    2   from emp
    3  where comm is null
    
  3. 讨论

    Null 值不会等于或者不等于任何值,甚至不能与其自身作比较。因此,不能使用 = 或 != 来测试某一列的值是否为 Null。判断一行是否含有 Null,必须使用 IS Null。你也可以使用 IS NOT Null 来找到给定列的值不是 Null 的所有行。

1.12 把Null值转换为实际值

  1. 问题

    有一些行包含 Null 值,但是你想在返回结果里将其替换为非 Null 值。

  2. 解决方案

    使用 COALESCE 函数将 Null 值替代为实际值。

    1 select coalesce(comm,0)
    2   from emp
    
  3. 讨论

    需要为 COALESCE 函数指定一个或多个参数。该函数会返回参数列表里的第一个非 Null 值。在本例中,若 COMM 不为 Null,会返回 COMM 值,否则返回 0。

    处理 Null 值时,最好利用数据库的内置功能。在许多情况下,你会发现有不止一个函数能解决本实例中的问题。COALESCE 函数只是恰好适用于所有的数据库。除此之外,CASE 也适用于所有数据库。

    select case
           when comm is not null then comm
           else 0
           end
      from emp
    
    

    尽管 CASE 也能把 Null 值转换成实际值,但 COALESCE 函数更方便、更简洁。

1.13 查找匹配项

  1. 问题

    你想返回匹配某个特定字符串或模式的行。考虑下面的查询及其结果集。

    select ename, job
      from emp
     where deptno in (10,20)
     
    ENAME      JOB
    ---------- ---------
    SMITH      CLERK
    JONES      MANAGER
    CLARK      MANAGER
    SCOTT      ANALYST
    KING       PRESIDENT
    ADAMS      CLERK
    FORD       ANALYST
    MILLER     CLERK
    

    你想从编号为 10 和 20 的两个部门中找到名字中含有字母 I 或职位以 ER 结尾的人。

    ENAME      JOB
    ---------- ---------
    SMITH      CLERK
    JONES      MANAGER
    CLARK      MANAGER
    KING       PRESIDENT
    MILLER     CLERK
    
  2. 解决方案

    结合使用 LIKE 运算符和 SQL 通配符 %

    1 select ename, job
    2   from emp
    3  where deptno in (10,20)
    4    and (ename like '%I%' or job like '%ER')
    
  3. 讨论

    被用于 LIKE 模式匹配操作时,运算符 % 可以匹配任意长度的连续字符。大多数 SQL 实现也提供了下划线(_)运算符,用于匹配单个字符。通过在字母 I 前后都加上 %,任何(在任意位置)出现 I 的字符串都会被检索出来。如果没有使用 % 把检索模式围起来,那么 % 的位置会影响查询结果。例如,为了找到以 ER 结尾的职位,就需要在 ER 的前面加上 %;如果是要找以 ER 开头的职位,那就应该在 ER 的后面加上 %

目录

  • 版权声明
  • O'Reilly Media, Inc. 介绍
  • 前言
  • 第 1 章 检索记录
  • 第 2 章 查询结果排序
  • 第 3 章 多表查询
  • 第 4 章 插入、更新和删除
  • 第 5 章 元数据查询
  • 第 6 章 字符串处理
  • 第 7 章 数值处理
  • 第 8 章 日期运算
  • 第 9 章 日期处理
  • 第 10 章 区间查询
  • 第 11 章 高级查询
  • 第 12 章 报表和数据仓库
  • 第 13 章 层次查询
  • 第 14 章 杂项
  • 附录 A 窗口函数简介
  • 附录 B 重温 Rozenshtein
  • 作者简介
  • 封面介绍