目录【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权)
https://www.cnblogs.com/cnb-yuchen/p/18338280
出自【进步*于辰的博客】
1、rownum
参考笔记一,P18.3/4、P19.5。
1.1 介绍
rownum
是虚拟字段,不真实存储,在返回结果集时生成,宏观作用类似序号。在每次查询时,从1
开始给结果集编号。常与<
、<=
连用。
在与>
、>=
连用时,由于rownum
是在查询记录时逐个生成,并迭代。因此,若rownum
的判断在第1行记录时就不满足,则无法返回记录,即未迭代,rownum
始终为1
,最终结果集中无任何记录,与between...and...
连用时同理。
$\color{red}{注意}$:所有SQL语句的执行顺序都是:from → where → group by → having → select → order by
。
由于rownum
是伴随select
生成,故与order by
连用时,会导致rownum
混乱。因此,通常是多层嵌套,先进行排序,再使用rownum
进行筛选。(见示例)
$\color{green}{扩展}$:在Oracle中,常言的top-n
查询其实是在rownum
编号后,使用rownum
进行判断,从而获取前n
条记录的查询方法。(见示例)
1.2 示例
数据表:emp(no, ..., sal)
。
需求:查询工资6 ~ 10
名的员工的所有信息。
写法一:
select e2.*
from (select e1.*, rownum rn
from (select * from emp order by sal desc) e1) e2
where rn between 6 and 10
前2层仅对结果集进行一次排序,不做筛选,在第3层时,才进行筛选。rn
是rownum
的别名,由于rn
属于第2层的结果集,非rownum
,故已固定,因此可以直接使用rn between 6 and 10
筛选出第6 ~ 10
名。
写法二。
select e2.*
from (select e1.*, rownum rn
from (select * from emp order by sal desc) e1
where rownum <= 10) e2
where rn > 5
在第2层时就进行结果集筛选,rownum
初始为1
,满足rownum <= 10
,则返回记录,同时rownum
迭代,如此反复直到条件不满足,这样就查询出前10条记录(工资最高的前10名员工)。此时rownum
固定,别名是rn
,第3层可以直接使用rn
筛选出第6 ~ 10
名。
$\color{red}{补充说明:}$
两种写法在第1层时,都得到根据工资降序排序后的所有员工信息。
写法二较写法一,效率高很多。
因为写法一是在第3层才进行结果集筛选,由于条件是rn between 6 and 10
,使用的是rn
,rn
属于结果集,已固定。因此在筛选时,会遍历根据工资降序排序后的所有员工信息。
而写法二,在第2层时,是通过rownum <= 10
进行筛选,由于rownum
的生成机制,第2层仅遍历前10条记录(工资最高的前10名员工)。因此,第3层仅遍历10条员工信息。
2、nextval、currval
参考笔记一,P23.18。
2.1 序列
这两个伪列皆基于$\color{green}{序列}$,我暂未对序列的相关理论进行整理,大家可以查阅这篇博文《Oracle数据库序列》(转发)。
“序列”是一种按照一定规则自动增加或减少数字的数据库对象,主要用于主键(新增时填充主键)。创建示例:
create sequence swq_emp_empId
increment by 1
start with 1000
nocycle
cache 20
seq_emp
是序列名;increment
是递增值,默认值为1
;start
是初始值;nocycle
表示不循环;cache 20
表示进行缓存,缓存大小为20
。
大家也可以使用图形化界面操作:
2.2 介绍
nextval
是序列的下一个值,currval
是序列的当前值。
使用位置:
select
子句中,不包括子查询的select
子句;- insert 语句的
select
子句或values
子句中; - update 语句的
set
子句中。
不能使用位置:
- 包含
distinct
、group by
、having
或order by
的视图SQL语句的select
子句中; - select、update、delete的子查询中;
- 包含
default
的create table
、alter table
语句中。
操作,
--修改序列--
alter sequence 序列名 ...;// 后面格式与创建语句相同
// 注:后面语句中没有start with,并且修改的值不能少于当前值
2.3 示例
insert into emps values(swq_emp_empId.nextval, '张三', 7500.00, 10);
update emps
set sal = 10000.00
where emp_id = swq_emp_empId.currval;
必须先获取nextval
,才能使用currval
。
最后
掌握伪列,我建议:理解、自测。
本文持续更新中。。。
标签:10,面试官,nextval,currval,emp,序列,rownum,rn,select From: https://www.cnblogs.com/cnb-yuchen/p/18338280