SQL经典实例 第1章

                                                检索记录

1.1 检索所有的行和列

1. 问题：
你有一张表，并且想查看表中的所有数据。
2. 解决方案：
用特殊符号" * "对该表执行SELECT查询。
SELECT
    *
FROM
    emp;

结果如下：

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

SELECT
    empno,
    ename,
    job,
    sal,
    mgr,
    hiredate,
    comm,
    deptno
FROM
    emp;

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

1.2 筛选行

1.问题：

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

2.解决方案：

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

SELECT
    *
FROM
    emp
WHERE
    DEPTNO = 10;

结果如下：

3. 讨论

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

大多数数据库都支持常用的运算符，例如 = 、< 、> 、<= 、>= 、! 、和<>。除此之外，你可能需要指定多个条件来筛选数据，这时就需要使用AND、OR、和圆括号。

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

1.问题

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

2.解决方案

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

SELECT
    *
FROM
    emp
WHERE
    DEPTNO = 10
OR COMM IS NOT NULL
OR SAL <= 2000
AND DEPTNO = 20;
 结果如下：

3. 讨论

你可以组合使用AND、OR和圆括号来筛选满足多个查询条件的行。在这个实例中，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;

结果如下：

1.4 筛选列

1.问题

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

2.解决方案

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

SELECT
    ename,
    deptno,
    sal
FROM
    emp;

结果如下：

3. 讨论

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

1.5 创建有意义的列名

1.问题

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

SELECT sal,comm FROM emp;

结果如下：

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

2.解决方案

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

SELECT
    sal AS salary,
    comm AS commission
FROM
    emp;

结果如下：

3.讨论

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

1.6 在WHERE子句中引用别名列

1.问题

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

SELECT
    sal AS salary,
    comm AS commission
FROM
    emp
WHERE
    salary < 5000;

结果如下：

2.解决方案

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

SELECT
    *
FROM
    (
        SELECT
            sal AS salary,
            comm AS commission
        FROM
            emp
    ) x
WHERE
    salary < 5000;

结果如下：

3.讨论

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

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

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

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

1.7 串联多列的值

1.问题

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

CLERK 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;

结果如下：

2.解决方案

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

DB2、Oracle和PostgreSQL

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

select ename || ' WORKS AS A '|| job as msg 

from emp

where deptno = 10;

MySQL

该数据库使用CONCAT函数作为串联运算符。

select concat(ename,' WORKS AS A  ',job) as msg

from emp

where deptno=10;

SQL Server

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

select ename + ' WORKS AS A '+ job as msg

from emp

where eptno=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表达式来执行条件逻辑。

SELECT
    ename,
    sal,
    CASE
WHEN sal <= 2000 THEN
    'UNDERPAID'
WHEN SAL >= 4000 THEN
    'OVERPAID'
ELSE
    'OK'
END AS STATUS
FROM
    emp;

结果如下:

3.讨论

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

1.9 限定返回行数

1.问题

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

2.解决方案

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

DB2

使用FECTH FIRST子句。

select * 

    from emp fetch first 5 rows only

MySQL和PostgreSQL

使用LIMIT子句。

SELECT
    *
FROM
    emp
LIMIT 5

Oracle

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

select *

    from emp

    where rownum <= 5

SQL Server

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

3.讨论

许多数据库提供了类似FETCH FIRST 和 LIMIT这样的子句来指定查询结果的行数。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.

（5）返回第3步。

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

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

1.10 随机返回若干行记录

1.问题

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

SELECT
    ename,
    JOB
FROM
    emp

2.解决方案

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

DB2

把内置函数RAND和ORDER BY、FETCH结合使用。

select ename,job

from emp

order by rand() fetch first 5 rows only

MySQL

把内置函数RAND和LIMIT、ORDER BY结合使用。

select ename,job

from emp

order by rand() limit 5

PostgresSQL

把内置函数RANDOM和LIMIT、ORDER BY结合使用。

select ename,job

from emp

order by random() limit 5

Oracle

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

select * 

from(

select ename,job

from emp

order by dbms_random.value()

)

where rownum<=5

SQL Server

同时使用内置函数NEWID和TOP、ORDER BY来返回一个随机结果集。

select top 5 ename,job

from emp

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。

SELECT
    *
FROM
    emp
WHERE
    COMM IS NULL

3.讨论

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

1.12 把Null值转换为实际值

1.问题

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

2.解决方案

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

SELECT
    COALESCE (COMM, 0)
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)

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

2.解决方案

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

SELECT
    ename,
    job
FROM
    emp
WHERE
    deptno IN (10, 20)
AND (
    ename LIKE '%I%'
    OR job LIKE '%ER'
)

3.讨论

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