【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权)
https://blog.csdn.net/m0_69908381/article/details/131040299
出自【进步*于辰的博客】
1、其他知识点链接
- 细节、经验;
- 索引;
2、伪列
2.1 rownum
参考笔记一,P18.3/4、P19.5。
2.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
条记录的查询方法。(如下)
2.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.2 nextval、currval
参考笔记一,P23.18。
2.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.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.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
。
4、约束
参考笔记一,P22.18~22。
“约束”是一种对数据表字段存储数据的限制,分为主键(primary key
)、唯一键(unique
)、外键(foreign key
)、check
约束、not null
约束。
4.1 操作命令
--添加约束--
alter table 表名 add constraint 约束名 约束类型(字段);
--删除约束--
alter table 表名 drop constraint 约束名;
--启用/禁用约束--
alter table 表名 enable/disable constraint 约束名;
注:
- 添加外键:
alter table 表名 add constraint 约束名 约束类型(字段1) reference 主表名(字段2)
; - 添加
not null
约束:alter table 表名 modify(字段 constraint 约束名 not null)
。因为not null
约束仅能定义于字段,即列约束,故不能使用...add constraint...
命令添加;
4.2 数据字典
--当前用户旗下所有数据表的所有约束--
user_constraints
--当前用户旗下所有数据表的所有约束所属的字段名--
user_cons_columns
4.3 外键约束
外键会将两个数据表进行关联,进而对子表相应字段的数据进行限制。这里就有个问题:若主表记录被删除,那么子表中相应数据该何去何从?
从上述可知创建外键的命令格式,若在其后增加一条子句,可以决定这些数据的去向,有以下4种子句:
-
on delete cascade
:表示子表中相应数据将连同删除; -
on delete set null
:表示子表中相应数据将被置空(null
); -
Restrict
:表示若子表中存在相应数据,则提示主表记录不能删除; -
on delete no action
:表示子表不受影响。
4.4 check 约束
此约束用于限制数据范围(多用于数值字段),其内不允许使用伪列,如:rownum
。
可以调用sysdate
、uid()
、user()
、userenv()
。
5、数据类型
5.1 char族
Oracle中char族数据类型与MySQL中相同,大家可查阅博文《MySQL知识点锦集》的第3项,在此不作赘述。
5.2 number(a, b)
参考笔记一,P20.13。
此数据类型为数字,可存储小数。其中,a 是数字位数(包括小数),b 是精确小数位数。
规则:
- 若
b > 0
,表示精确b
位小数,并四舍五入; - 若
b < 0
,表示精确到小数点左b
位,并四舍五入,故只能存储整数。如:number(5, 3)
,可存储五位数整数,个位和十位都为0
; - 若
b = 0
,则只能存储整数; - 若
a < b
,则只能存储-1 ~ 0
或0 ~ 1
的小数,且小数点右b - a
位及其后都必须是0
(前后四舍五入都要满足),即精确b
位小数。
6、内置函数
6.1 单行函数
参考笔记一,P15.3、P39.1。
摘要 | 参数说明 | 返回值类型/返回值 | 说明 |
---|---|---|---|
substr(s, a, b) |
a-开始索引,可为负值;b-截取长度 | 截取。截取方向始终向右 | |
round(a, b) |
b-精确位数 | 四舍五入。若b < 0 ,则向左精确,故round(a) 等价于round(a, 0)
|
|
upper(s) |
转大写 | ||
lower(s) |
转小写 | ||
initcap(s) |
首字母大写 | ||
length(s) |
返回长度 | ||
concat(a, b) |
拼接,类似||
|
||
trunc(s) |
截取,类似round(a)
|
||
sysdate |
获取系统时间 | ||
instr(s1, s2[, a][, b]) |
s2-查找字符,a-开始索引,b-第几个 | 查找。instr(s1, s2) 等价于instr(s1, s2, 1, 1)
|
|
lpad/rpad(s1, n, s2) |
左 / 右填充。表示将s1 用s2 向左 / 向右填充成长度为n 的字符串 |
||
replace(s1, s2, s3) |
替换。表示将s1 中的s2 用s3 替换 |
||
trim(s) |
去除前后空格 | ||
trim(leading/trailing/both/无 a from b) |
去除 b 中开头 / 结尾 / 开头和结尾 / 开头以及结尾的 a | ||
mod(a, b) |
等同于a%b ,余数符号跟 a |
6.1.1 lpad/rpad(s1, n, s2)
示例。
select lpad(rpad('csdn', 7, '#'), 10, '*') result from dual
结果:
若n < s1.length
,则无论lpad/rpad()
,结果都只显示s1
的前n
个字符(从左往右)。
6.1.2 instr(s1, s2[, a][, b])
示例。
select instr(s1, s2, -2, 3) from dual;
表示在s1
中,从倒数第2
个开始,向左查找第3
个s2
的位置。注意:无论a
的正负,返回的都是绝对位置。
6.2 非空判断函数
参考笔记一,P18.1。
摘要 | 参数说明 | 返回值类型/返回值 | 说明 |
---|---|---|---|
nvl(a, b) |
若a 为null ,返回 b,否则返回 a |
||
nvl2(a, b, c) |
若a 不为null ,返回 b,否则返回 c |
||
nullif(a, b) |
比较 a、b,若a = b ,返回null ,否则返回 a |
||
case xx when w1 then c1 when w2 then c2 else c3 end |
若xx = w1 或w1 为true ,返回 c1;若xx = w2 或w2 为true ,返回 c2;否则返回 c3 |
||
decode(xx, w1, c1, w2, c2, c3) |
作用同case()
|
6.3 日期函数
参考笔记一,P15.5。
摘要 | 参数说明 | 返回值类型/返回值 | 说明 |
---|---|---|---|
months_between(d1, d2) |
返回d1 与d2 相差的自然月数 |
||
add_months(d, n) |
增加月数 | ||
next_day(d, '星期一') |
返回 d 后的第1个星期一 | ||
last_day(d) |
返回 d 当月的最后1天 | ||
round(d, 'dd') |
以day 四舍五入。'dd' 是格式码,其他格式码:'CC' → 世纪,'YY' → 年,'mm' → 月,'hh24' → 小时,'mi' → 分钟,'ss' → 秒。其中,round(d, 'dd') 等价于round(d)
|
||
trunc(d, 'dd') |
同round() 。trunc(d, 'dd') 等价于trunc(d)
|
||
extract(day from d) |
获取 d 的天数。day 是标识符,表示“天”。其他标识符:'year' → 年,'month' → 月, |
7、视图(view)
推荐一篇博文《Oracle视图详解》(转发)。
参考笔记一,P22.24、P23.15~17。
7.1 概述
view
可认为是一种绑定了SQL语句的原表“副本”,故当查询视图时,都会重新执行一次SQL语句查询原表,且修改视图记录等同于修改原表记录。
不过,若视图记录是由原表记录经处理后生成,即SQL语句中包含“数据处理”子句或条件时,不允许变动视图记录。“数据处理”子句或条件有:
-
group
函数; - group by;
- distinct;
- 使用表达式定义的字段;
- 伪列
rownum
; - 原表在视图中未选择(未
select
)的字段的所有数据为非空且无默认值。
当然,并非只要SQL语句中包含了这6个子句或条件,就无法对视图进行全部DML。规则如下:
- 删除视图记录。要求SQL语句中不能包含前3个子句或条件。
- 修改视图记录。要求SQL语句中不能包含前5个子句或条件。
- 新增视图记录。要求SQL语句中不能包含所有子句或条件。
我们也可以干脆在SQL语句末添加with read only
子句禁用视图DML。
7.2 数据字典
--当前用户旗下所有视图--
user_views
--显示视图中哪些字段允许DML--
user_updatable_columns
7.3 一个问题
假若SQL语句是:
create view v_emp_1(id, name, sal)
as
select emp_id, emp_name, sal
from emps
where dept_no = 10;
这里dept_no
是外键,该视图记录为员工表中隶属部门10
的所有员工信息。
现在,我把10
改成20
。前言道,查询视图是对原表的再次查询,这样修改后,该视图记录就变为部门20
的所有员工信息。可是,view
是原表的“副本”,既然创建视图时部门编号为10
,说明当时我设定该视图的作用是用于快速查询部门10
的所有员工信息,那么又怎能允许后续随意修改查询条件(dept_no = 10
)。
因此,可以在SQL语句末添加with check option constraint 约束名
子句,设置一个约束限制这种修改。
最后
暂不言。文章来源:https://www.toymoban.com/news/detail-501695.html
本文持续更新中。。。文章来源地址https://www.toymoban.com/news/detail-501695.html
到了这里,关于oracle初级锦集的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!