Oracle索引&约束
Oracle索引&约束
1索引的原理
索引是一种允许直接访问数据表某一数据行的树形结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中
索引记录中存有索引关键字和指向表中数据的指针（地址）
对索引进行的I/O操作比对表进行操作要少很多
索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引
索引是一种提高查询效率的机制
Oracle B-tree索引的结构
ROWID：伪劣，唯一标识一条数据记录，可理解为行地址
1.1创建索引
单列索引:
CREATE [UNIQUE] INDEX index_name
ON table(column[,column]);
-- index_name 表示索引名称
-- table 表示表名
-- column 表示列名，可以建立单列索引或复合索引
-- UNIQUE 表示唯一索引
-- 在EMP表的ENAME列上建立索引
CREATE INDEX idx_emp_ename ON emp(ename);
复合索引:
复合索引也叫多列索引，是基于多个列的索引
如果经常在ORDER BY 子句中使用job和sal作为排序依据，可以建立复合索引：
CREATE INDEX idx_emp_job_sal ON emp(job,sal);
-- 当做下面的查询时，会自动应用索引idx_emp_job_sal
SELECT empno,ename,sal,job FROM emp ORDER BY job,sal;
基于函数的索引:
如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引:
CREATE INDEX emp_ename_upper_idx ON emp(UPPER(ename));
当做下面的查询时，会自动应用刚刚建立的索引:
SELECT * FROM emp WHERE UPPER(ename) = 'KING';
修改和删除索引:
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率
ALTER INDEX index_name REBUILD;
-- 重建索引idx_emp_ename
ALTER INDEX idx_emp_ename REBUILD;
当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：
DROP INDEX index_name;
-- 删除索引idx_emp_ename
DROP INDEX idx_emp_ename;
合理使用索引提升查询效率:
-
为经常出现在WHERE子句中的列创建索引
-
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致。
-
为经常作为表的连接条件的列上创建索引
-
不要在经常做DML操作的表上建立索引
-
不要在小表上建立索引
-
限制表上的索引数目，索引并不是越多越好
-
删除很少被使用的、不合理的索引
2约束
约束的作用
-
约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件
-
约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行
-
约束条件可以保证表中数据的完整性，保证数据间的商业逻辑
约束条件：
-
非空约束（NOT NULL），简称NN
-
唯一性约束（UNIQUE），简称UK
-
主键约束（PRIMARY Key），简称PK
-
外键约束（Foreign Key），简称FK
-
检查约束（Check），简称CK
2.1非空约束
2.1.1建表时添加非空约束
-
非空约束用于确保字段值不为空
-
默认情况下，任何列都允许有空值，但业务逻辑可能会要求某些列不能取空值
-
当某个字段被设置了非空约束条件，这个字段中必须存在有效值，即：
-
当执行INSERT操作时，必须提供这个列的数据
-
当执行UPDATE操作时，不能给这个列的值设置为NULL
-
CREATE TABLE employees(
eid NUMBER(6),
name VARCHAR(30) NOT NULL,
salary NUMBER(7,2),
hiredate DATE
CONSTRAINT employees_hiredate_nn NOT NULL
);
2.1.2修改表时添加非空约束
可以在建表之后，通过修改表的定义，添加非空约束
ALTER TABLE employees MODIFY (eid NUMBER(6) NOT NULL);
2.1.3取消非空约束
如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：
ALTER TABLE employees MODIFY (eid NUMBER(6) null);
2.2唯性一约束
-
唯一性（Unique）约束条件用于保证字段或者字段的组合不出现重复值
-
当给表的某个列定义了唯一性约束条件，该列的值不允许重复，但允许是NULL值
-
唯一性约束条件可以在建表同时建立，也可以在建表以后再建立
2.2.1添加唯一性约束
在建表employees的同时，在eid、email列上创建唯一性约束条件，并在建表后在name列上建立一个名为employees_name_uk的唯一性约束条件
DROP TABLE employees;-- 将原有的表删除重建
CREATE TABLE employees(
eid NUMBER(6) UNIQUE,
name VARCHAR(30) NOT NULL,
salary NUMBER(7,2),
email VARCHAR2(50),
hiredate DATE,
CONSTRAINT employees_email_uk UNIQUE(email)
);
2.2.2建表后添加唯一约束
ALTER TABLE employees
ADD CONSTRAINT employees_name_uk UNIQUE(name);
2.3主键
主键（Primary Key）约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合
主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值
主键可以用来在表中唯一的确定一行数据
一个表上只允许建立一个主键，而其他约束条件则没有明确的个数限制
2.3.1主键选取的原则
-
主键应是对系统无意义的数据
-
永远也不要更新主键，让主键除了唯一标识一行之外，再无其他用途
-
主键不应包含动态变化的数据，如时间戳
-
主键应自动生成，不要认为干预，以免使它带有除了唯一标识一行以外的意义
-
主键尽量建立在单列上
2.3.2添加主键约束
- 建表时添加主键约束
CREATE TABLE emp2(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR(30) NOT NULL,
salary NUMBER(7,2),
email VARCHAR2(50),
hiredate DATE
);
- 建表后创建主键约束
CREATE TABLE emp2(
eid NUMBER(6),
name VARCHAR(30) NOT NULL,
salary NUMBER(7,2),
email VARCHAR2(50),
hiredate DATE
);
ALTER TABLE emp2 ADD CONSTRAINT emp2_eid_pk PRIMARY KEY (eid);
2.4外键
外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字短的关系
2.4.1添加外键约束
CREATE TABLE emp3(
eid NUMBER(6),
name VARCHAR(30) NOT NULL,
salary NUMBER(7,2),
email VARCHAR2(50),
deptno NUMBER(4),
hiredate DATE
);
ALTER TABLE emp3
ADD CONSTRAINT emp3_deptno_fk
FOREIGN KEY (deptno) REFERENCES dept(deptno);
外键约束对一致性的维护：
-
外键约束条件包括两个方面的数据约束
-
从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL
-
当主表参照列的值被从表参照时，主表的该行记录不允许被删除
-
2.5检查
检查（Check）约束条件用来强制在字段上的每个值都要满足Check中定义的条件
当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件
2.5.1添加检查约束
- 员工薪水必须大于2000元
ALTER TABLE emp3
ADD CONSTRAINT emp3_salary_check
CHECK(salary > 2000);
-- 正常插入数据
INSERT INTO emp3(eid,name,salary,deptno)
VALUES(123,'pp',2500,40);
-- 试图修改薪水为1500，更新失败
UPDATE emp3 SET salary = 1500 WHERE eid = 123;
posted on 2023-08-09 16:53 JavaCoderPan 阅读(138) 评论(0) 编辑 收藏 举报
