一、检索记录

1.1从表中检索所有行和列

    select * from emp;

1.2从表中检索部分行

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

1.3查找满足多个条件的行

select * from emp where deptno=10

select * from emp where deptno=10 or comm is not null or sal<= 2000 and deptno = 20

select * from emp where (deptno=10 or comm is not null or sal<= 2000) and deptno = 20

1.4从表中检索部分列

select ename,deptno,salfrom emp

1.5为列取有意义的名称

select sal as salary,comm as commission from emp

1.6在WHERE子句中引用取别名的列 将查询作为内联视图就可以引用其中区别的列了：

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

1.7连接列值

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

SQLServer：使用 "+"运算符进行连接操作

select ename + 'WORKS AS A ' + job as msg from emp where deptno = 10

1.8在SELECT语句中使用条件逻辑 使用CASE表达式直接在SELECT语句中执行条件逻辑

select ename,sal,

    case when sal <= 2000 then 'UNDERPAID'

            when sal >= 4000 then 'OVERPAID'   else 'OK'   end as status from emp

1.9限制返回的行数--分页查询 使用数据库提供的内置函数来控制返回的行数

DB2

    select * from emp fetch first 5 rows only

MySQL和PostgreSQL

select * from limit 5

Oracle

select * from emp where rownum <=5

SQLServer

select top 5 * from emp

1.10 从表中随机返回n条记录 使用DBMS支持的内置函数来生成随机数值。在ORDER BY 子句中使用该函数，对行进行随机排序，然后使用前面问题 介绍的技巧，来限制所返回的行(顺序随机)的数目

DB2

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

MySQL

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

PostgreSQL

select ename,job order by random() limit 5

Oracle

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

1.11查找空值 要确定值是否为空，必须使用 IS NULL

select * from emp where comm is null

1.12将空值转换为实际值

select coalesce(comm,0) from emp

1.13按模式搜索 使用LIKE运算符和SQL通配符"%"

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