1.1查询表中所有的行与列(略)
1.2从表中检索部分行
select * from emp where job='SALESMAN';
1.3查找空值
select * from emp where comm is null;
NULL不支持加减乘除,大小比较,相等比较,否则只能为空。
1.4将空值转为实际值
select coalesce(comm,0) from emp;
1.5查找满足多个条件的行
select * from emp where (deptno=10 or comm is not null or (sal<2000 and deptno=20))
1.6从表中检索部分列(略)
1.7为列取有意义的名称(略)
1.8在where子句中引用取别名的列
select * from (select sal as 工资,comm as 提成 from emp ) x
1.9拼接列
select ename||'的工作是'||job AS msg from emp where deptno = 10;
1.10在select语句中使用条件逻辑
select ename,sal,case when sal<=2000 then '过低' when sal>=4000 then '过高' else 'OK' end as status from emp where deptno = 10;
1.11限制返回的行数
select * from emp where rownum <=2
但是直接用rownum=2是不会有任何查询结果的。因为rownum是依次对数据做标识的。正确取第二行数据的查询应该像下面这样:
select * from (select rownum as sn,emp.* from emp where rownum <=2) where sn = 2
1.12从表中随机返回n条记录
select empno,ename from (select empno,ename from emp order by dbms_random.value()) where rownum <=3
1.13模糊查询(略)
原文链接:https://www.f2er.com/oracle/206818.html