【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）

https://developer.aliyun.com/article/1456267 

出自【进步*于辰的博客】

1、其他知识点链接

1. 细节、经验 ；

1. 索引 ；

2、伪列

2.1 rownum

参考笔记一，P18.3/4、P19.5。

2.1.1 概述

rownum 是虚拟字段，不真实存储，在返回结果集时生成，“宏观”作用类似序号。

在每次查询时，从 1 开始给结果集编号。常与 < 、 <= 连用。而与 > 、 >= 连用时，由于 rownum 是在查询记录时逐个生成，并迭代。因此，若 rownum 的判断在第1行记录时就不满足，则无法返回记录，即未迭代， rownum 始终为 1 ，最终结果集中无任何记录，与 between...and... 连用时同理。

注意 ：

无论任何SQL语句，执行顺序都是： from → where → group by → having → select → order by 。

由于 rownum 是伴随 select 生成，故与 order by 连用时，会导致 rownum 混乱。故通常是多层嵌套，先进行排序，再使用 rownum 进行筛选。（如下）

扩展 ：在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 名。

补充说明： 

两种写法在第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 序列

这两个伪列基于序列，我暂未对序列的相关理论进行整理，大家可以查阅这篇博文《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 是序列的当前值。

使用位置：

1. select 子句中，不包括子查询的 select 子句；
2. insert 语句的`select`子句或 values 子句中；
3. update 语句的 set 子句中。

不能使用位置：

1. 包含 distinct 、 group by 、 having 或 order by 的视图SQL语句的 select 子句中；
2. select、update、delete的子查询中；
3. 包含 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 约束名;

注：

1. 添加外键： alter table 表名 add constraint 约束名 约束类型(字段1) reference 主表名(字段2) ；
2. 添加 not null 约束： alter table 表名 modify(字段 constraint 约束名 not null) 。因为 not null 约束仅能定义于字段，即列约束，故不能使用 ...add constraint... 命令添加；

4.2 数据字典

--当前用户旗下所有数据表的所有约束--
user_constraints
--当前用户旗下所有数据表的所有约束所属的字段名--
user_cons_columns

4.3 外键约束

外键会将两个数据表进行关联，进而对子表相应字段的数据进行限制。这里就有个问题：若主表记录被删除，那么子表中相应数据该何去何从？

从上述可知创建外键的命令格式，若在其后增加一条子句，可以决定这些数据的去向，有以下4种子句：

1. on delete cascade ：表示子表中相应数据将连同删除；

2. on delete set null ：表示子表中相应数据将被置空（ null ）；

3. Restrict ：表示若子表中存在相应数据，则提示主表记录不能删除；

4. 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 是精确小数位数。

规则：

1. 若 b > 0 ，表示精确 b 位小数，并四舍五入；
2. 若 b < 0 ，表示精确到小数点左 b 位，并四舍五入，故只能存储整数。如： number(5, 3) ，可存储五位数整数，个位和十位都为 0 ；
3. 若 b = 0 ，则只能存储整数；
4. 若 a < b ，则只能存储 -1 ~ 0 或 0 ~ 1 的小数，且小数点右 b - a 位及其后都必须是 0 （前后四舍五入都要满足），即精确 b 位小数。

6、内置函数

6.1 单行函数

参考笔记一，P15.3、P39.1。

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。

6.3 日期函数

参考笔记一，P15.5。

7、视图（`view`）

推荐一篇博文《Oracle视图详解》（转发）。

参考笔记一，P22.24、P23.15~17。

7.1 概述

view 可认为是一种绑定了SQL语句的原表“副本”，故当查询视图时，都会重新执行一次SQL语句查询原表，且修改视图记录等同于修改原表记录。

不过，若视图记录是由原表记录经处理后生成，即SQL语句中包含“数据处理”子句或条件时，不允许变动视图记录。“数据处理”子句或条件有：

1. group函数；
2. group by；
3. distinct；
4. 使用表达式定义的字段；
5. 伪列 rownum ；
6. 原表在视图中未选择（未`select`）的字段的所有数据为非空且无默认值。

当然，并非只要SQL语句中包含了这6个子句或条件，就无法对视图进行全部DML。规则如下：

1. 删除视图记录。要求SQL语句中不能包含前3个子句或条件。
2. 修改视图记录。要求SQL语句中不能包含前5个子句或条件。
3. 新增视图记录。要求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 约束名 子句，设置一个约束限制这种修改。

最后

暂无言。

本文持续更新中。。。