串联多列的值

        你想将多列的值合并为一列

        DB2、Oracle 和 PostgreSQL 这些数据库把双竖线作为串联运算符。

                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

                where deptno=10

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

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

                 from emp 3 where deptno=10 

        使用 CONCAT 函数可以串联多列的值。

        在 DB2、Oracle 和 PostgreSQL 中，“||”是 CONCAT 函 数的快捷方式，

        在 SQL Server 中则为“+”。

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

               你想在 SELECT 语句中针对查询结果值执行 IF-ELSE 操作。

                例如，你想生成类似这样的 结果：如果员工的工资少于 2000 美元，

                 就返回 UNDERPAID；如果超过 4000 美元就返回 OVERPAID；

                 若介于两者之间则返回 OK。

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

             select ename,sal,

                case when sal = 4000 then 'OVERPAID' 

                        when sal >= 4000 then 'OVERPAID'

                        else 'OK'

                  end as status

                from emp

        CASE 表达式能对查询结果执行条件逻辑判断。

        你可以为 CASE 表达式的执行结果取一个别 名，使结果集更有可读性。

        就本例而言，STATUS 就是 CASE 表达式执行结果的别名。

        ELSE 子句是可选的，若没有它，对于不满足测试条件的行，CASE 表达式会返回 Null。

随机返回若干行记录

1. 问题
你希望从表中获取特定数量的随机记录。修改下面的语句，以便连续执行查询并使结果集
含有 5 行不同的数据。
        select ename, job
         from emp
2. 解决方案
使用数据库的内置函数来随机生成查询结果。在 ORDER BY 子句里使用该内置函数可以实现
查询结果的随机排序。

 
DB2
把内置函数 RAND 和 ORDER BY、FETCH 结合使用。
1 select ename,job
2 from emp
3 order by rand() fetch first 5 rows only
 
MySQL
把内置函数 RAND 和 LIMIT、ORDER BY 结合使用。
1 select ename,job
2 from emp
3 order by rand() limit 5
 
PostgreSQL
把内置函数 RANDOM 和 LIMIT、ORDER BY 结合使用。
1 select ename,job
2 from emp
3 order by random() limit 5
检索记录 ｜ 9
 
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
同时使用内置函数 NEWID 和 TOP、ORDER BY 来返回一个随机结果集。
1 select top 5 ename,job
2 from emp
3 order by newid()

ORDER BY 子句能够接受一个函数的返回值，并利用该值改变当前结果集的顺序。

在本例中， 所有查询都是在 ORDER BY 子句执行结束后才限定返回值的行数。

看过 Oracle 的解决方案 后，非 Oracle 用户可能会受到启发，因为 Oracle 的解决方案展示了（在理论上）其他数据 库内部是如何实现该查询的。

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

把Null值转换为实际值

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

2. 解决方案 使用 COALESCE 函数将 Null 值替代为实际值。

1 select coalesce(comm,0)
2 from emp

需要为 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. 问题 你想按照一个字符串的特定部分排列查询结果。

例如，你希望从 EMP 表检索员工的名字和 职位，并且按照职位字段的最后两个字符对检索结果进行排序。结果集应该像下面这样。

ENAME JOB ---------- ---------

KING PRESIDENT

SMITH CLERK

ADAMS CLERK

AMES CLERK

MILLER CLERK

JONES MANAGER

CLARK MANAGER

BLAKE MANAGER

ALLEN SALESMAN

MARTIN SALESMAN

WARD SALESMAN

TURNER SALESMAN

SCOTT ANALYST

FORD ANALYST

2. 解决方案

DB2、MySQL、Oracle 和 PostgreSQL
 
在 ORDER BY 子句里使用 SUBSTR 函数。
select ename,job
 from emp
 order by substr(job,length(job)-2)
 
 
SQL Server
在 ORDER BY 子句里使用 SUBSTRING 函数。
 
select ename,job
 from emp
 order by substring(job,len(job)-2,2)

利用数据库中的子串函数，你可以很方便地按照一个字符串的任意部分来排序。

要想按照 一个字符串的最后两个字符排序，需要先找到该字符串的结尾处（即字符串的长度），然 查询结果排序

这样，起始位置就是该字符串的倒数第 2 个字符。

然后，你就可以截取从指定 起始位置开始直到字符串结束的所有字符。

SQL Server 的 SUBSTRING 函数略有不同，

它要 求提供第 3 个参数来指定需要截取几个字符。对于本实例而言，

第 3 个参数既可以是 2， 也可以是任何大于 2 的数字。

查找只存在于一个表中的数据

1. 问题 你希望从一个表（可以称之为源表）里找出那些在某个目标表里不存在的值。

        例如，你想 找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号（如果有的话）。

        在示例数据中， DEPT 表里 DEPTNO 为 40 的数据并不存在于 EMP 表里，

        因此结果集应该如下所示。

        DEPTNO ----------

                40

2. 解决方案 计算差集的函数对解决本问题尤其有用。

DB2、PostgreSQL 和 Oracle 支持差集运算。

如果 你所使用的数据库管理系统没有提供差集函数，

那么就要采用 MySQL 和 SQL Server 解决 方案介绍的子查询技巧。

DB2 和 PostgreSQL
使用集合运算 EXCEPT。
1 select deptno from dept
2 except
3 select deptno from emp
 
 
Oracle
使用集合运算 MINUS。
1 select deptno from dept
2 minus
3 select deptno from emp
 
 
MySQL 和 SQL Server
使用子查询得到 EMP 表中所有的 DEPTNO，并将该结果传入外层查询，然后外层查询会检索
DEPT 表，找出没有出现在子查询结果里的 DEPTNO 值。
1 select deptno
2 from dept
3 where deptno not in (select deptno from emp)

DB2 和 PostgreSQL DB2 和 PostgreSQL 提供的内置函数使得该操作非常简单。

EXCEPT 运算符获取第一个结果 集的数据，然后从中删除第二个结果集的数据。

这种运算非常像减法。 包括 EXCEPT 在内的集合运算符在使用上都有一些限制条件。

参与运算的两个 SELECT 列表 要有相同的数据类型和值个数。

而且，EXCEPT 不返回重复项；并且 Null 值不会产生问题， 这与 NOT IN 子查询不同（参考对 MySQL 和 SQL Server 的讨论）。

EXCEPT 运算符会返回只 存在于第一个查询（EXCEPT 前面的查询）结果里而不存在于第二个查询（EXCEPT 后面的查 询）结果里的行。

Oracle Oracle 解决方案除了集合运算符叫作 MINUS 而不是 EXCEPT，其他方面与 DB2 和 PostgreSQL 的解决方案相同。

另外，上述解释也适用于 Oracle。 M

ySQL 和 SQL Server 这个子查询会获取 EMP 表中所有的 DEPTNO。外层查询会返回 DEPT 表中“不存在于”或“未 被包含在”子查询结果集里的所有的 DEPTNO 值。

当你使用 MySQL 和 SQL Server 的解决方案时，需要考虑排除重复项。其他数据库基于 EXCEPT 或者 MINUS 的解决方案已经从结果集中排除了重复的行，确保每个 DEPTNO 只出现 一次。

当然，之所以能这样做，是因为示例数据中的 DEPTNO 是表的主键。如果 DEPTNO 不是主键，你可以使用 DISTINCT 来确保每个在 EMP 表里缺少的 DEPTNO 值只出现一次，

如下所示。

select distinct deptno from dept

where deptno not in (select deptno from emp)

在使用 NOT IN 时，要注意 Null 值。考虑如下的表 NEW_DEPT。

create table new_dept(deptno integer)

insert into new_dept values (10)

insert into new_dept values (50)

insert into new_dept values (null)

如果你试着使用 NOT IN 子查询检索存在于 DEPT 表却不存在于 NEW_DEPT 表的 DEPTNO，会发 现查不到任何值。

select * from dept

where deptno not in (select deptno from new_dept)

DEPTNO 为 20、30 和 40 的数据虽然不在 NEW_DEPT 表中，却没被上述查询检索到。

原因就在 于 NEW_DEPT 表里有 Null 值。子查询会返回 3 行 DEPTNO，分别为 10、50 和 Null 值。IN 和 NOT IN 本质上是 OR 运算，由于 Null 值参与 OR 逻辑运算的方式不同，IN 和 NOT IN 将会产 生不同的结果。考虑以下分别使用 IN 和 OR 的例子。

select deptno from dept where deptno in ( 10,50,null )

DEPTNO ------- 10

select deptno from dept where (deptno=10 or deptno=50 or deptno=null)

DEPTNO ------- 10

再来看看使用 NOT IN 和 NOT OR 的例子。

select deptno from dept where deptno not in ( 10,50,null ) ( no rows )

select deptno from dept where not (deptno=10 or deptno=50 or deptno=null)

( no rows ) 如你所见，

条件 DEPTNO NOT IN (10, 50, NULL) 等

价于： not (deptno=10 or deptno=50 or deptno=null) 对于 DEPTNO 是 50 的情况，下面是这个表达式的展开过程。 not (deptno=10 or deptno=50 or deptno=null) (false or false or null) (false or null) null

在 SQL 中，TRUE or NULL 的运算结果是 TRUE，但 FALSE or NULL 的运算结果却是 Null ！

一 旦混入了 Null，结果就会一直保持为 Null。必须谨记，当使用 IN 谓词以及当执行 OR 逻辑运算的时候，你要想到是否 会涉及 Null 值。

为了避免 NOT IN 和 Null 值带来的问题，需要结合使用 NOT EXISTS 和关联子查询。

关联子 查询指的是外层查询执行后获得的结果集会被内层子查询引用。

下面的例子给出了一个免 受 Null 值影响的替代方案（回到“问题”部分给出的那个原始查询语句）。

select d.deptno from dept d

where not exists ( select null from emp e where d.deptno = e.deptno )

DEPTNO ---------- 40

上述查询语句遍历并评估 DEPT 表的每一行。

针对每一行，会有如下操作。

(1) 执行子查询并检查当前的部门编号是否存在于 EMP 表。要注意关联条件 D.DEPTNO = E.DEPTNO，它通过部门编号把两个表连接起来。

(2) 如果子查询有结果返回给外层查询，那么 EXISTS (...) 的评估结果是 TRUE，这样 NOT EXISTS (...) 就是 FALSE，如此一来，外层查询就会舍弃当前行。

(3) 如果子查询没有返回任何结果，那么 NOT EXISTS (...) 的评估结果是 TRUE，由此外层查 询就会返回当前行（因为它是一个不存在于 EMP 表中的部门编号）

多表查询

EXISTS/NOT EXISTS 和关联子查询一起使用时，

SELECT 列表里的项目并不重要，

因此我 在这个例子中用了 SELECT NULL，这是为了让你把注意力放到子查询的连接操作上，而非 SELECT 列表的项目上。

在运算和比较中使用Null

1. 问题

Null 不会等于或不等于任何值，甚至不能与其自身进行比较，但是你希望对从 Null 列返 回的数据进行评估，就像评估具体的值一样。例如，你想找出 EMP 表里业务提成（COMM 列）比员工 WARD 低的所有员工。检索结果应该包含业务提成为 Null 的员工。

2. 解决方案

使用如 COALESCE 这样的函数把 Null 转换为一个具体的、可以用于标准评估的值。

3. 讨论

COALESCE 函数会返回参数列表里的第一个非 Null 值。就本实例而言，COMM 列中的 Null 会 被替换为 0，这样才能与 WARD 的业务提成相比较。把 COALESCE 函数添加到 SELECT 列 表，就能查看其执行结果。