Oracle学习日记——单表查询 oracle简单查询

Oracle学习同样是从单表查询开始，本篇内容主要是Oracle单表查询，主要包括全表查询、模糊查询、返回固定条数、以及返回随机数据。

1.查询表中所有的行和列

select * from 表名 * 表示所有的列

2.从表中检索部分行

select * from 表名 where 条件

eg：查询工资高于3000的员工信息

select * from emp where salary > 3000

3.查找空值

错误示例：查询工资为空的员工信息 select * from emp where salary = null;

错误原因：null 不能用 = 运算符，要用is null

正确写法：select * from emp where salary is null

同理，判断工资不为空： select * from emp where salary is not null;

注：null不支持加、减、乘、除、大小比较、相等比较，否则只能为空；

4.把空值转换为实际值

4.1:coalesce()查询第一个不是空值的值,括号里参数可以有多个

测试用例：

(1)在数据库中创建视图v，创建视图语句如下：

create or replace view v as

select null as c1,null as c2,1 as c3,null as c4,2 as c5,null as c6 from dual

union all

select null as c1,null as c2,null as c3,3 as c4,2 as c5,null as c6 from dual ;

(2)查询视图v

select * from v;

(3)用coalesce()函数查询

select coalesce(c1,c2,c3,c4,c5,c6) as c from v;

比较2和3的查询结果理解coalesce;

4.2:nvl(要转换的列,要转换的值)

nvl()用法举例：把工资是空值的值在查询结果中用0代替

select nvl(salary,0) from emp;

5.查询满足多个条件的行

and 和 or 连接

eg: (1)查询部门编码为10且工资高于3000的员工信息

select * from emp where salary > 3000 and deptno = 10

(2)查询部门编码为10或工资高于3000的员工信息

select * from emp where salary > 3000 or deptno = 10

注意：对于多个条件的组合，要使用括号，这样在更改维护语句是可以不必再考虑优先级问题，而且可以很容易的借助各种

工具找到各组合条件的起止位置。

6.从表中检索部分列

select 列名1,列名2,列名3 from 表名

eg:select name,borndate,salary from emp;

7.为列取有意义的名称

select 列名1 别名1,列名2 别名2,列名3 别名3 from 表名

eg:select name 员工姓名,sex 性别,borndate 生日 from emp;

8.在where子句中引用取别名的列

注意：在where条件中引用取别名的列需要嵌套一层，因为这个别名是在select以后才生效的；

select * from

(select salary 工资,name 姓名 from emp

) x

where 工资 > 3000;

9.拼接列

(1)希望返回的数据是特定格式，比如“张三的工资是3000元”

select name || '的工资是' ||salary || '元' from emp;

|| 把查询的内容拼接起来， 自己手动加的内容是字符串，要用单引号''框起来。

(2)进行批处理的时候可以生成批处理的语句 //不同语句的拼接。

10.在select语句中使用条件逻辑

case when 语句的用法

select name,salary,

case

when salary <=2000 then '过低'

when salary >=4000 then '过高'

else 'OK'

END AS STATUS

from emp;

eg:查询报表统计各个工资段的总人数

select 档次,count(*) as 人数

from (

select (

case

when salary <= 1000 then '0000-1000'

when salary <= 2000 then '1000-2000'

else '工资好高'

end

) as 档次,

name,salary from emp

) group by 档次

order by 1;

11.限制返回的行数

在查询的时候，并不是每次都需要查询所有的数据，比如，在抽查的时候只要求返回两条数据

可以用伪列rownum来过滤，rownum会对返回的每一条数据做一个标识

eg：select * from emp where rownum <= 2;

但是，如果直接用select * from emp where rownum = 2 查询是没有结果返回的

因为，rownum是对返回的数据做标记的，所以，需要把需要的数据返回来，才能进行取值，

select * from

(select rownum as sn,emp.* from emp) where sn = 2;

12.从表中随机返回n条数据

为了防止作假，需要对数据进行随机排序

使用dbms_random来进行随机排序

eg：select name

from (

select empno,name from emp order by dbms_random.value()

) where rownum <=3;

这里为什么要使用嵌套查询？

因为执行顺序的原因，在这个语句里面对数据库有三个操作：查询，生成序号，排序

因为是随机取数，所以我们应该先生成序号，再进行排序，最后取出数据；

如果不进行嵌套，会先取出数据，再进行嵌套，每次取出的结果都一样，达不到随机的效果。

验证方法(该写法错误)：

select rownum 排序后,排序前,name 姓名 ran 随机数 from

(

select rownum 排序前,name,dbms_random.value ran

from emp

where rownum <=3

order by ran

);

13.模糊查询

测试用例：

建立视图v

CREATE OR REPLACE VIEW V AS

select 'ABCDEF' as vname FROM DUAL

UNION ALL

SELECT '_BDEFG' as vname FROM DUAL

UNION ALL

SELECT '_BCEDF' as vname FROM DUAL

UNION ALL

SELECT '_\BCEDF' as vname FROM DUAL;

查询包含BCD的信息

select * from v where vname like '%BCD%'

%被称为通配符(替代一个或多个字符)

数据库查询中有两个通配符，% 和 _

二者意思完全相同，

考虑一种情况，查询字符串中包含'_dfgh'的字符串

select * from v where vname like '_dfgh%'

如果正好有字符串为'asdfgh',也会做为查询结果被查询出来，因为系统把'_'作为通配符进行处理

解决方法：使用转义字符

select * from v where vname like '_\dfgh%' escape '\';

使用\对_ 进行了转义，使其不是本来的意思;

对于模糊查询条件中有“%”的处理方法和上面相同；

MySQL学习系列之五——通配符

MySQL学习系列之四——数据过滤和过滤数据