SQL查询语句(检索记录)
1.从表中检索所有行和列
//查看表中的所有行和列
select * from emp
2.从表中查看满足条件的行
select * from emp
where deptno = 10
3.从表中查看满足多个条件的行
//查找部门10中,所有得到提成的员工,以及查找部门20中,所有工资不足2000元的员工
select * from emp
where deptno = 10
or comm not null
or deptno = 20 and sal <= 2000
4.查看表中特定列的值
select enname deptno sal
from emp
5.改变返回列名的名称,使他们更具有可读性,更容易理解(取别名)
select sal as Salary, comm as commission
from emp
6.在where子句中使用列的别名
//使用内联视图就where就可以使用列的别名了
select *
from (
select sal as Salary, comm as commission
from emp
)
where Salary < 5000
7.将多列值作为1列返回
//查找和使用MBMS提供的内置函数,来连接来自不同列的值
//DB2、Oracle、PostgreSQL使用"||"作为连接运算符
select enname || 'works as a' || job as msg
from emp
where deptno = 10
//MySql使用concat函数
select concat (enname 'works as a' job) as msg
from emp
where deptno = 10
//SQLServer使用+作为连接运算符
select enname + 'works as a' + job as msg
from emp
where deptno = 10
8.在select语句中使用IF-ELSE语句。
//返回一个结果集:如果员工工资小于2000,就返回消息“underpaid”;如果大于等于4000就返回消息“overpaid”;如果结余之间就返回“OK”。
select ename, sal, case when sal <= 2000 then 'underpaid'
when sal >= 4000 them 'overpaid'
else 'Ok'
end as status
from emp
9.限制返回的行数
//DB2中使用'fetch frist 子句
select *
from emp fetch frist 5 rows only
//MySql和PostgerSql使用limit函数
select *
from emp limit 5
//Oracle在where中使用rownum来限定行数
select * from emp
where rownum <= 5
//SQLServer中使用Top关键字来限定返回的行数
select Top 5 *
from emp
10.从表中随机返回n条记录
//DB2使用order by、rand和fetch
select ename, job from emp
order by rand() fetch first 5 rows only
//MySql使用order by、rand和limit
select ename, job from emp
order by rand() limit 5
//PostgerSql使用内置的random、limit和order by
select ename, job from emp
order by random() limit 5
//oracle使用DBMS_random的内置函数value、order by和rownum
select *(select ename, job from emp
order by dbms_random.value())
where rawnum <= 5
//sqlServer使用内置函数newid、top和order by
select top 5 ename, job
from emp
order by newid()
11.查找某列值为空的所有行
select * from emp
where comm is null
12.使用特定的值代替返回的空值
select case
when comm is null then 0
else comm
end
from emp
13.按模式搜索(返回匹配特定子串或模式的行)
select ename, job
from emp
where deptno in (10,20)
and (ename like '%I%' or job like '%ER')