一、检索记录
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')