SQL--检索记录

1.检索所有行和列
select * from emp

2.筛选行(例子：查找部门编号为10的所有员工)
select * from emp where deptno = 10

3.查找满足多个查询条件的行（例：查出部门编号为10的所有员工、有业务提成的所有员工以及部门编号是20且工资低于2000美元的所有员工）
select * from emp where deptno = 10
or comm is not null
or sal <= 2000 and deptno = 20

4.筛选列（例：查看员工的名字、部门编号和工资）
selet ename,deptno,sal from emp

5.创建有意义的列明（例：将列ename为a，列sal为b）
select ename as a,sal as b from emp

6.在where句中引用别名列
select * from(select ename as a,sal as b from emp) x where a < 500

7.串联多列的值
select ename + ‘Works as A’ + job as msg
from emp
where deptno = 10

8.在Select语句里使用条件逻辑
select ename,sal
case when sal < = 2000 then ‘UNDERPAID’
when sal >= 4000 then ‘OVERPAID’
else ‘OK’
end as status
from emp

9.限定返回行数
select top 5* from emp

10.随机返回若干行记录
select top 5 ename, job
from emp
order by newid()

11.查找null值(不能使用=或！=)
select * from emp
where comm is null

12.把null转化为实际值(使用coalesce函数)
select coalesce(comm,0)
from emp

13.查找匹配项（结合使用LIKE运算符和SQL通配符%）
select ename,job
from emp
where deptno in (10,20)
and(ename like ‘%I%’ or job like ‘%ER’)