SQLCookBook第一章学习日记2

74 阅读 0 评论 49 点赞

我是靠谱客的博主傻傻向日葵，最近开发中收集的这篇文章主要介绍SQLCookBook第一章学习日记2，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.8在select语句使用条件逻辑

问题：要在select语句中，对数值执行if-else操作。例如，要产生一个结果集，如果一个员工工资小于等于2000美金，就返回消息”underpaid”：如果大于等于4000美金：就返回消息”overpaid”,如果实在这两者之间，就返回”OK”。

解决方案：

select enmae,sal,
    case when sal <=2000 then 'underpaid'
         when sal >=4000 then 'overpaid'
         else 'ok'
    end as status
from emp

讨论:
case 表达式可以针对查询的返回值执行条件逻辑。可以给case表达式取别名，使结果集更易读。在这个解决方案中可以看到，给case表达式的别名是status。else子句是可选的，如果没有使用else

1.9限制返回的行数

问题：限制查询中返回的行数。这里不关心顺序，返回任何n行都行。
解决：用数据库内置函数来控制返回的函数。

DB2
在db2中，使用fetch first子句：

select *
from emp fetch first 5 rows only

MySQL 和 PostgreSQL
在MySQL和PostgreSQL中，使用limit：

select * 
    from emp limit 5

Oracle
在Oracle中，在where子句中通过使用rownum来限制行数：

select * from emp
    where rownum <=5

SQL server
使用top关键字，来限制返回的行数：

select top 5 * from emp

讨论：
许多数据提供一些子句，比如fetch first 和limit ，让用户指定从查询中返回的行数。oracle的做法不同，必须使用rownum函数来得带每行的行号（从1开始递增数值）。

在使用rownum <=5来返回前五行时，会发生下面的操作：

1.oracle 执行查询
2.oracle 获取第一个符合条件的行，将它叫做第一行
3.有五行了吗？ 如果没有，那么，oracle 就再返回行。因为它要满足行号小于等于5的条件，如果到了五行，那么，oracle就不再返回行。
4.oracle获取下一行，并递增行号
5.返回到第三步。

可以看到 oracle的rownum数值是在获取每行之后才赋予的。这非常重要，是一个关键点。许多人想要通过rownum= 5来返回第五行，这是错误的做法。下面说明使用rownum=5时会发生什么：

1.oracle执行查询
2.oracle 获取第一个符合条件的行，将它叫做第一行
3.有五行了吗？如果没有，那么oracle就丢弃这些行，一位它不满足条件。如果到了五行，那么，oracle就返回该行。但是，答案是，永远也不会有"到了5行"的情况发生.
4.oracle获取下一行，这是第一行。原因是，从查询中返回的必须是编号为1的行。
5.转向第三步。

仔细看看就可以知道，使用 = 5 来返回第五行失败的原因。如果不返回第一行到第四行的话，就不会有第五行。

rownum = 1 确实是返回第一行，这似乎与前面的说明矛盾了。原因是，rownum = 1 返回第一行，不管表中是否有行，oracle 都会尝试至少取一行。

1.10从表中随机返回n条记录

问题：从表中随机返回n条记录。可以修改下面的语句，要求下次执行时产生不同的结果集。

select ename,job from emp

解决方案：使用dbms支持的内置函数来生成随机函数。在order by子句中使用该函数，对行进行随机排序，然后，使用前面问题介绍的技巧，来限制所返回的行（顺序随机）的数目。

DB2
同时使用内置函数rand与order by和fetch

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

MySQL
同时使用内置函数的rand函数 limit 和order by：

select enmae,job from emp
order by rand() limit 5

PostgreSQL
同时使用内置函数random函数，limit和orderby：

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

Oracle
同时使用dbms_random包中的内置函数value order by 和内置函数rownum：

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

讨论：
order by 子句可以接受函数的返回值，并使用它来改变结果集的次序。这个解决方案中，在order by 子句中执行函数之后，再查询返回的行数。非oracle用户会发现，看看oracle解决方案会很有用，可以理解解决方案的原理。

重要的是，不要把在order by 子句中的使用函数与使用字数常量混淆起来。在order by 子句中指定数字常量时，是要求根据select列表中相应位置的列来排序，在order by子句中使用函数时，则按函数在每一行计算排序结果。

1.11查找空值

问题：要查找某列值为空的所有行。
解决方案：要确定值是否为空，必须使用 is null

select * from emp 
    where comm is null

讨论：
NULL不能用等于或不等于跟任何值比较，包括它自身。所以不鞥使用 = 或！ = 来测试一列是否为NULL 为了确定一行是否有空值，必须使用is null 也可以使用 is not null 来查找给定列的值不为空的行。

1.12将控制转换为实际值

问题：在一些行中包含空值，需要使用非空值来替代这些控制。
解决方案：使用coalesce函数用实际的值来替换空置，语句如下：

select coalesce(comm,0) from emp

讨论：
coalesce 函数有一个或多个参数。该函数返回列表中的第一个非控制。在这个解决方案中，只要comm非控，就返回comm的值，否则返回0。

在使用空值的时候，最好是利用dbms提供的内置功能。许多情况下有几个函数都可以完成这项任务。coalesce可以用于所有的dbms。另外，对于所有的dbms，也都可以使用case，如下所示：

select case 
    when comm is null then 0
    ekse comm
    end
from emp

尽管可以使用case将控制转换为非空数值，但是可以看到，使用coalesc更为容易简洁。

1.13按模式搜索

问题:需要返回匹配特定子串或模式识别的行。考虑下面的查询和结果集：

select  ename,job from emp
    where deptno in(10,20)

在部门10和部门20，需要返回名字中有一个”I” 或者职务中带有‘ER’的员工

解决方案：

select ename,job from emp
    where deptop in (10,20)
        and (ename like '%I%' or job like '%ER')

讨论：
在like模式匹配操作中，百分号运算符可以匹配任何字符序列。多数SQL实现中也提供了”_”运算符，来匹配单个字符。使用“%”运算符将搜索模式” I ” 括起来，就会返回任何包含”I” 的字符串，不管 “I”在什么位置。如果不用 “%” 运算符将搜索模式”I”括起来，那么，这个运算符的位置就会影响查询结果。例如，要查找以”ER” 结尾的职务，可以在”ER”的前面加上前缀”%”运算符；如果需要查找以”ER”开头的职务，则将百分号放在”ER”后面