SQL技巧（1）检索记录

（Molinaro-SQL cookbook笔记）

1.检索所有列和行（*匹配所有列）

select *
from emp

Better style：

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

两者性能相同，但列出所有列的名称提高了可读性。

2.检索满足条件的行

多数厂商都支持的where中的运算符

=   <   >   <=   >=   !   <>

逻辑运算符优先级：

not > and > or

所以

where deptno = 10 or comm is not null or sal <= 2000 and deptno=20

等价于

where deptno = 10 or comm is not null or （sal <= 2000 and deptno=20）

3.select语句和where语句中的别名

select sal as salary, comm as commission
from emp

但是select-from-where的执行顺序为

from->where->select， 在where运行是别名并不存在，可以使用内联视图（inline view）解决：

select *
from (
         select sal as salary, comm as commission
         from emp
    ) X
where salary < 5000

其中内联视图的别名为X，并非所有数据库都需要显示命名，但所有数据库都接受这种方式。

4.连接列值

Oracle，PostgreSQL

select ename || 'work as' || job as msg

MySQL

select concat (ename, 'work as' , job) as msg

SQL server

select ename + 'work as' + job as msg

5.select语句中的条件逻辑

ENAME	SAL	STATUS
SMITH	800	UNDERPAID
ALLEN	4200	OVERPAID
WARD	2450	OK

select ename, sal,
       case when sal <= 2000 then 'UNDERPAID'
            when sal >= 4000 then 'OVERPAID'
            else 'OK'
       end as status
from emp

未匹配的行会返回NULL

6.限制返回行数

DB2

select *
from emp fetch first 5 rows only

MySQL， PostgreSQL

select * 
from emp limit 5

Oracle

select * 
from emp
where rownum <= 5

注：无法用rownum = 5来返回第五行，获取的每一行由于不满足 rownum = 5永远都重新计算为第1行

SQL Server

select top 5 *
from emp

7.随即返回n条记录

DB2

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

MySQL

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

PostgreSQL

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

Oracle

select *
from (
       select ename, job
       from emp
       order by dbms_random.value()
     )
where rownum <= 5

SQL Server

select top 5 ename, job
from emp
order by newid()

注：在ORDER BY中使用数字常量，根据select列表中相应位置的列排序，在ORDER BY中使用函数时，按函数在每一行计算结果。

8.查找空值

select *
from emp
where comm is null

NULL不能用等于和不等于跟任何值比较（包括自身），必须使用IS NULL/ IS NOT NULL

9.将空值转换为实际值

select coalesce(comm, 0)
from emp

若comm非空，返回comm值，否则返回0

10.按模式搜索

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

在部门10和部门20， 返回名字中有‘I’或者职务中以‘er'结尾的员工。

IN 操作符: 在 WHERE 子句中规定多个值， IN (value1,value2,...)

通配符： % 匹配任何字符  _匹配单个字符