约束:就是指对插入数据的各种限制,约束既可在建表的时候直接声明,也可以为已经建好的表添加约束。
视图:就是一个封装了各种复杂查询的语句,就称之为视图。
索引:是一种用于提升查询效率的数据库对象
。通过快速定位数据的方法,来减少磁盘的 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 操作)。