本文详细介绍了Oracle数据库中的约束、视图和索引。约束包括非空、主键、唯一、外键及级联删除等，用于数据限制。视图是封装复杂查询的语句，可用于数据保护。索引则用于提升查询效率，分为唯一性和非唯一性，文章讨论了其优缺点、创建原则和管理方法。
约束：就是指对插入数据的各种限制，约束既可在建表的时候直接声明，也可以为已经建好的表添加约束。
视图：就是一个封装了各种复杂查询的语句，就称之为视图。
索引：是一种用于提升查询效率的数据库对象。通过快速定位数据的方法，来减少磁盘的 I/O 操作。索引信息与表独立存放，并且Oracle 数据库自动使用和维护索引。
一、约束
1）非空约束
CREATE TABLE TEST
(
ID VARCHAR2(36) NOT NULL, // 该字段不能为空
NAME VARCHAR2(200)
)
/
INSERT INTO TEST(NAME) VALUES ('jane'); // [23000][1400] ORA-01400: 无法将 NULL 插入 ("SNOW_ZY"."TEST"."ID")
2）主键约束
CREATE TABLE TEST
(
ID VARCHAR2(36) PRIMARY KEY, // 不能重复、不能为空
NAME VARCHAR2(200)
)
/
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane');
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane'); // [23000][1] ORA-00001: 违反唯一约束条件 (SNOW_ZY.SYS_C00124287)
3）唯一约束
CREATE TABLE TEST
(
ID VARCHAR2(36) UNIQUE, // 值不能重复 (空值除外)
NAME VARCHAR2(200)
)
/
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane');
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane'); // [23000][1] ORA-00001: 违反唯一约束条件 (SNOW_ZY.SYS_C00124287)
4）条件约束
CREATE TABLE TEST
(
ID VARCHAR2(36),
NAME VARCHAR2(200),
AGE NUMBER CHECK ( AGE BETWEEN 0 AND 150) // 插入的数据必须是满足某些条件
)
/
INSERT INTO TEST(ID, NAME, AGE) VALUES ('123', 'jane', 100);
INSERT INTO TEST(ID, NAME, AGE) VALUES ('123', 'jane', 200); // [23000][2290] ORA-02290: 违反检查约束条件 (SNOW_ZY.SYS_C00124289)
5）外键 (references : 参考、证明)
CREATE TABLE PERSON
(
ID VARCHAR2(36) PRIMARY KEY ,
NAME VARCHAR2(200)
)
/
INSERT INTO PERSON(ID, NAME) VALUES ('123', 'jane');
INSERT INTO PERSON(ID, NAME) VALUES ('456', 'kobe');
CREATE TABLE BOOK
(
BID VARCHAR2(36),
BNAME VARCHAR2(200),
PID VARCHAR2(36) REFERENCES PERSON(ID) --书只属于一个人, 插入该值时回去父表中查询有没有对应的记录
--建立约束: book_pid_fk, 与person 中的pid 形成主+外键关系
--CONSTRAINT b00k_pid_fk FOREIGN KEY(PID) REFERENCES PERSON(ID)
)
/
INSERT INTO BOOK(BID, BNAME, PID) VALUES ('000', '追风筝的人', '123');
INSERT INTO BOOK(BID, BNAME, PID) VALUES ('001', '解忧杂货铺', '111'); // [23000][2291] ORA-02291: 违反完整约束条件 (SNOW_ZY.SYS_C00124291) - 未找到父项关键字
6）级联删除
接上，此时已经有了Person 表与Book 表，且Book 表中的PID 字段与Person 表的ID 建立了外键关系，假设现在Person表中一条记录消失了，那此时该PID 在Book 表中的那行记录也应该消失。
DELETE FROM PERSON WHERE ID = '123'; // [23000][2292] ORA-02292: 违反完整约束条件 (SNOW_ZY.SYS_C00124293) - 已找到子记录
删除Person 表的某行记录，会提示已找到子记录，因为在book 表中该人的id 已经被使用了， 所以想要删除该人时，应该首先在book 中删除该人的书本信息。、
如果想完成删除person 表中数据的时候，同时将book 表中用到该id 的记录一起删除，则需要使用 级联删除（ON DELETE CASCADE）。
CREATE TABLE BOOK
(
BID VARCHAR2(36),
BNAME VARCHAR2(200),
PID VARCHAR2(36),
--建立约束: book_pid_fk, 与person 中的pid 形成主+外键关系
CONSTRAINT b00k_pid_fk FOREIGN KEY(PID) REFERENCES PERSON(ID) ON DELETE CASCADE //级联删除, 删除主键的同时, 用到该主键当外键的记录也会同步删除
)
/
INSERT INTO BOOK(BID, BNAME, PID) VALUES ('000', '追风筝的人', '123');
DELETE FROM PERSON WHERE ID = '123'; //Person 表中 ID=123 的记录被删除, book 表中 PID=123 的记录被删除
7）语句添加约束
- 为Person 表添加名为
PERSON_PID_PK，主键为ID的约束
ALTER TABLE PERSON ADD CONSTRAINT PERSON_PID_PK PRIMARY KEY(ID);
--添加约束完美写法, 添加前先判断该约束是否存在
DECLARE number202203031610 number;
begin
SELECT COUNT(1) INTO number202203031610 FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('A39') AND CONSTRAINT_NAME = UPPER('PKNAME_A00');
if number202203031610 = 0 then
EXECUTE IMMEDIATE
'ALTER TABLE A39 ADD CONSTRAINT PKNAME_A00 PRIMARY KEY (A00)';
end if;
end;
/
- 为Person 表的
NAME字段添加名为PERSON_NAME_UK的唯一约束
ALTER TABLE PERSON ADD CONSTRAINT PERSON_NAME_UK UNIQUE(NAME);
- 为Person 表的
Age字段添加名为PERSON_AGE_CK的检查约束
ALTER TABLE PERSON ADD CONSTRAINT PERSON_AGE_CK CHECK(AGE BETWEEN 0 AND 150);
- 为Book 表中的
PID字段添加与Person 表的主-外键约束，名为PERSON_BOOK_FK，要求带级联删除
ALTER TABLE BOOK ADD CONSTRAINT PERSON_BOOK_FK FOREIGN KEY (PID) REFERENCES PERSON(PID) ON DELETE CASCADE;
8）删除约束
ALTER TABLE BOOK DROP CONSTRAINT B00K_PID_FK; -- 约束直接被删除
9）禁 / 启用约束
ALTER TABLE BOOK DISABLE CONSTRAINT B00K_PID_FK; -- 约束会被禁用, 但是约束还存在
ALTER TABLE BOOK ENABLE CONSTRAINT B00K_PID_FK; -- 启用约束, 前提是表中的数据全都满足约束的条件
10）查看表中的约束
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('BOOK');
| CONSTRAINT_TYPE | USE |
|---|---|
| C | Check on a table Column（检查约束） |
| O | Read Only on a view Object（只读约束） |
| P | Primary Key Object（主键约束） |
| R | Referential AKA Foreign Key Column（外键约束） |
| U | Unique Key Column（唯一约束） |
| V | Check Option on a view Object（视图约束） |
二、视图
视图：就是一个封装了各种复杂查询的语句，就称之为视图。
1）创建视图
CREATE OR REPLACE VIEW EMP(EMP00, EMP01) AS (SELECT EMP00, EMP01 FROM EMPLOYEE);
2）查看视图
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = 'EMP';
3）修改视图
默认情况下创建的视图，如果更新了，则会自动将此数据从视图中删除，之后会更新原本的数据。
UPDATE EMP SET EMP.EMP01='周康2' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E'; --EMP 视图与EMPLOYEE 表中数据都修改了
虽然可以修改视图达到修改数据库数据的目的，但是还是存在隐患问题。视图最好还是不要更新。
4）视图的保护机制
在建立视图的时候，可以指定两个参数，保护视图的创建规则：WITH CHECK OPTION；视图只读：WITH READ ONLY
--1. 保护视图的创建规则
CREATE OR REPLACE VIEW EMP(EMP00, EMP01) AS SELECT EMP00, EMP01 FROM EMPLOYEE
WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E' WITH CHECK OPTION CONSTRAINT EMP_CK;
此时视图为：
此时如果去更新EMP00，会报错，因为EMP00 是创建视图的WHERE 字段，被保护了，所以不能修改，但是可以修改EMP01；
UPDATE EMP SET EMP.EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4A' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E';
--[44000][1402] ORA-01402: 视图 WITH CHECK OPTION where 子句违规
UPDATE EMP SET EMP.EMP01='周康3' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E'; --正常执行
--2. 只读视图
CREATE OR REPLACE VIEW EMP(EMP00, EMP01) AS SELECT EMP00, EMP01 FROM EMPLOYEE
WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E' WITH READ ONLY; --视图的所有字段都不能修改
UPDATE EMP SET EMP.EMP01='周康3' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E';
--[99999][42399] ORA-42399: 无法对只读视图执行 DML 操作
三、索引
- 什么是索引 (Index)
索引是一种用于提升查询效率的数据库对象。通过快速定位数据的方法，来减少磁盘的 I/O 操作。索引信息与表独立存放，并且Oracle 数据库自动使用和维护索引。
- 索引分类
索引大体上分为两类，唯一性索引 与 非唯一索引。
- 创建索引的方式
① 自动创建：在定义主键或唯一键约束时，系统会自动在相应的字段上创建唯一性索引。
② 手动创建：用户可以在其他列上创建非唯一索引，用来提高查询效率。
1）索引优缺点
建立索引的优点：
- 大大加快了数据的检索速度。
- 创建唯一性索引，保证数据库表中每一行数据的唯一性。
- 加速表与表之间的连接。
- 在使用分组和排序字句进行数据检索时，可以显著地减少查询中分组和排序的时间。
索引的缺点：
- 索引需要占用物理空间。
- 当对表中的数据进行增删改查，同时也需要动态的维护索引，降低了数据的维护速度。
2）创建与使用索引的原则
创建索引： 创建索引一般有两个目的，维护被索引列的唯一性和提供快速访问表中数据的策略。小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。
- 在
SELECT操作占大部分的表上创建索引。 - 在
WHERE字句中出现最频繁的列上创建索引。 - 在选择查询高的列上创建索引（补充索引选择性，最高是1，eg：primary key）。
- 复合索引的主列应该是最有选择性的和
WHERE限定条件最常用的列，并以此类推第二列。
使用索引： 查询结果是所有数据行的 5%以下时，使用index查询效果最好。WHERE条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引，因为当sql 语句所查询的列，全都出现在复合索引时，此时由于 oracle 只需要查询索引块即可获取所有数据，当然比使用多个单列索引要快得多。索引利于SELECT，但对经常 增改删 的表会降低效率。
- 使用与不使用索引的区别
SELECT EMP00, EMP01 FROM EMPLOYEE WHERE EMP00 NOT IN (SELECT EMP00 FROM EMP) --语句A
SELECT EMP00, EMP01 FROM EMPLOYEE WHERE NOT EXIST (SELECT EMP00 FROM EMP WHERE EMPLOYEE.EMP00 = EMP.EMP00) --语句B
这两条sql语句实现的结果是相同的，但是当数据量非常大的时候，两条语句执行效率会差很多。在执行A的时候，Oracle会对整个EMP表进行扫描，不会使用建立在EMP上的EMP00索引，执行B的时候，由于在子查询中使用了联合查询，Oracle只是对EMP表进行部分的数据扫描，并利用了EMP00列的索引，所以B的效率要比A高。
- 复合索引的注意点，以及索引无效的隐式写法
WHERE字句中的字段,必须是复合索引的第一个字段。一个索引是按照f1, f2, f3的次序建立的，如果WHERE的字句是f2 = val2，则会因为f2 不是索引的第一个字段从而导致无法使用索引。
WHERE字句中的字段，不应该参加任何形式的计算，任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等，查询时要尽量将操作符移至等号右边。一下操作符会显示地阻止oracle使用索引：IS NULL、IS NOT NULL、NOT IN、!=、LIKE、NUMERIC_COL+0、DATE_COL+0、CHAR_COL || ''、TO_CHAR、TO_DATE、TO_NUMBER 等。
SELECT EMP01 FROM EMPLOYEE WHERE EMP02 = '0' AND TO_DATE(UPDATETIME) > to_DATE('2022-01-01', 'YYYY-MM-DD');
--UPDATETIME列的索引会失效
3）创建 \ 删除索引
CREATE INDEX ABC ON EMPLOYEE(EMP00, EMP01);
CREATE INDEX CBA ON EMPLOYEE(EMP01, EMP00);
索引ABC 与 CBA 是不一样的，索引ABC 对于WHERE EMP00 = '1'这样的查询语句更有效，而CBA 索引对于WHERE EMP01 = '周康'这样的查询语句更有效。因此建立索引的时候，字段的组合顺序是非常重要的，一般情况下需要经常访问的字段放在组合索引字段的前面。
DROP INDEX ABC; --删除索引
4）查看索引
SELECT * FROM USER_INDEXES; --查询现有的索引
SELECT * FROM USER_IND_COLUMNS; --可得到索引建立在那些字段上
要注意的是索引和表都是独立存在的，在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生 IO 冲突。使oracle 能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。
5）索引类型
B树索引（B-Tree Index）： 创建索引的默认类型，结构是一棵树，采用的是平衡 B 树算法。
位图索引（BitMap Index）： 如果表中的某些字段取值范围比较小，比如职员性别、分数列 ABC 级等等，只有几种取值，这样的字段如果建 B树索引没有意义，不能提高检索效率。此时就要考虑使用位图索引。
CREATE BITMAP INDEX EMP05Index ON EMPLOYEE(EMP05); --EMP05Index 索引名, EMPLOYEE(EMP05) 表名(字段名)
6）管理索引
- 先插入数据后创建索引，向表中插入大量数据之前最好不要先创建索引，因为如果先创建索引。那么在插入每行数据的时候都要更改索引，这样会大大降低插入数据的速度。
- 设置合理的索引列顺序。
- 限制每个表索引的数量。
- 删除不必要的索引。
- 为每个索引指定表空间。
- 经常做 insert、delete 尤其是 update 的表最好定期 exp、imp 表数据，定期整理数据从而降低碎片（要停应用，以保持数据一致性），有索引的最好定期 rebuild 索引，以降低索引碎片，提高效率（rebuild 索引期间只允许做 select 操作）。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
