Oracle 中索引与完整性（SQL）

索引

在数据库中建立索引主要有以下作用：

（1）快速存取数据；

（2）既可以改善数据库性能，又可以保证列值的唯一性；

（3）实现表与表之间的参照完整性；

（4）在使用'ORDER BY' 、'GROUP BY' 子句进行数据检索时，利用索引可以减少排序和分组的时间。

索引分类

索引的分类可以根据索引的结构和用途进行。

1.按用途和特性分类：

1. 单列索引（Single Column Index）：只包含单个表列的索引。
2. 复合索引（Composite Index）：包含多个表列的组合索引，用于加速包含多个条件的查询。
3. 唯一索引（Unique Index）：索引列的值必须唯一，用于确保表中的数据不重复。
4. 聚集索引（Clustered Index）：按照索引的顺序存储表中的行数据，通常与主键或唯一约束相关联。
5. 非聚集索引（Non-clustered Index）：索引中存储了索引列的值及指向实际数据行的指针，通常用于普通查询。

实现方式：

另一方面，索引也可以根据其在内部数据结构上的不同实现方式进行分类，这就包括了 B*树索引和位图索引等，这种分类更多地关注于索引的内部实现和算法，而不是索引的用途和特性。

2.按实现方式分类：

1. B*树索引：平衡多路搜索树实现的索引结构，常见于关系型数据库中。
2. 哈希索引：使用哈希表实现的索引结构，用于快速等值查询，不适用于范围查询。
3. 位图索引：基于位图的索引结构，适用于低基数列的查询。
4. 全文索引：用于全文搜索的索引类型，支持文本分词和高效的文本匹配操作。
5. 空间索引：专用于地理空间数据的索引类型，用于空间查询和距离计算等操作。

3.按支持的数据类型分类：

1. 普通索引：适用于基本数据类型的索引。
2. 函数索引：基于函数的索引，用于对表达式或函数的结果进行索引。
3. 空间索引：用于地理空间数据类型（如点、线、面等）的索引。
4. 全文索引：用于文本数据类型的索引，支持全文搜索和文本匹配操作。

使用索引的原则

1. 选择合适的列：选择最频繁用于检索的列来创建索引。
2. 避免过度索引：不要在所有列上都创建索引，因为它会增加数据更新和插入的成本。
3. 注意索引选择性：索引选择性越高，性能越好。选择性是指索引列中不同值的比例。
4. 考虑查询性能：根据数据库的实际查询需求来选择创建索引的列。
5. 定期维护索引：随着数据库的变化，定期检查并更新索引是很重要的。

创建索引

CREATE INDEX idx_name ON table_name (column_name);

 

维护索引

索引的维护包括更新、重建和重新组织索引。数据库管理系统通常会自动维护索引，但是在数据量较大或者对性能要求较高的情况下，可能需要手动干预来优化索引的性能。

删除索引

DROP INDEX idx_name;

数据库完整性

数据完整性的分类

数据的完整性就是指数据库中的数据在逻辑上的一致性和准确性。

一般情况下，可以把数据完整性分成三类：域完整性、实体完整性和参照完整性。

域完整性

Oracle 可以通过CHECK 约束实现域完整性。CHECK 约束实际上是字段输入内容的验证规则，表示这个字段的输入内容必须满足CHECK 约束的条件；若不满足，则数据无法正常输入。

 

域完整性又称为列完整性，指定一个数据集对某一个列是否有效和确定是否允许空值。

域完整性通常是经过使用有效性检查来实现的，还可以通过限制数据类型、格式或者可能的取值范围来实现。

        例如：对于数据库XSCJ的KCB表，课程的学分应该在0~10之间，所以我们需要对学分这一数据项输入的数据范围进行限制，可以在定义KCB表的同时定义学分列的约束条件以达到这一目的。

实体完整性

实体完整性也可以称为行完整性，要求表中的每一行有一个唯一的标识符，这个标识符救世主关键字(PRIMARY KEY)。

        例如：居民身份证号是唯一的，这样才能唯一地确定某一个人。

通过索引、UNIQUE 约束。PRIMARY KEY 约束可实现数据的实体完整性。

        例如：对于XSCJ数据库的XSB表，“学号”作为主关键字，可以唯一地标识每一位学生对应的行记录信息，那么在输入数据时，就不能有相同学号的行记录，通过对“学号”这一字段建立主键约束可实现XSB表的实体完整性。

参照完整性

一旦定义了两个表之间的参照完整性，则有如下-要求-。
(1)从表不能引用不存在的键值。

        例如，对于 CJB 表中行记录出现的学号必须是 XSB 表中已存在的学号。
(2)如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用都要进行一致的更改。

        例如，如果对 XSB 表中的某一学号进行了修改，那么对 CJB 表中所有对应学号也要进行相应的修改.
(3)如果主表中没有关联的记录，则不能将记录添加到从表。
(4)如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。

 

完整性约束

特点：

- 完整性约束是通过限制列数据和表之间数据来保证数据完整性的有效方法。

- 约束是保证数据完整性的标准方法

- 每一种数据完整性类型都可以由不同的约束类型来保障。

- 约束确保有效的数据输入到列中并维护表与表之间的关系。

不同类型的完整性约束

约束类型	描述
NOT NULL	指定表中某个列不允许空值，必须为该列提供值
UNIQUE	使某个列或某些列的组合唯一，防止出现冗余值
PRIMARY KEY	使某个列或某些列的组合唯一，也是表的主关键字
FOREIGN KEY	使某个列或某些列位外关键字，其值与本表或者另表的主关键字匹配，实现引用完整性
CHECK	指定表中的每一行数据必须满足的条件

两种不同的完整性声明及其特点

数据库触发器（Database Triggers）：

数据库触发器是一种特殊类型的存储过程，它会在特定的数据库事件发生时自动执行。这些事件可以是对表的数据进行插入、更新、删除等操作。触发器通常用于实现数据完整性、约束、审计跟踪等功能。

触发器的特点包括：

• 自动执行：触发器会在定义的事件发生时自动执行，无需手动调用。
• 触发条件：触发器可以根据定义的条件在特定的操作前或后触发。
• 作用范围：触发器可以应用于整个表，对表中的所有数据操作进行响应。

存储过程（Stored Procedures）

存储过程是一组预编译的 SQL 语句集合，存储在数据库中以供重复使用。存储过程可以接受参数输入，执行一系列的数据库操作，并返回结果。存储过程通常用于实现复杂的业务逻辑、数据处理和数据分析等功能。

存储过程的特点包括：

• 手动调用：存储过程需要显式地被调用才会执行。
• 参数输入：存储过程可以接受参数输入，根据参数执行不同的逻辑。
• 功能多样：存储过程可以执行各种数据库操作，包括查询、插入、更新、删除等。

示例：

CREATE PROCEDURE calculate_salary(emp_id INT, hours_worked INT)
BEGIN-- 声明变量来存储小时工资和总工资DECLARE hourly_rate DECIMAL(10, 2);DECLARE total_salary DECIMAL(10, 2);-- 获取员工的小时工资SELECT hourly_rate INTO hourly_rate FROM employees WHERE employee_id = emp_id;-- 根据员工的小时工资和工作时长计算总工资SET total_salary = hourly_rate * hours_worked;-- 将计算得到的总工资存储到数据库中的工资历史记录表中INSERT INTO salary_history (employee_id, salary) VALUES (emp_id, total_salary);
END;

也可以将代码写进sql文件中，然后执行：

SQL> select * from salary_history;1        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -24

如此，我们可以

声明完整性约束的区别：

数据库触发器和存储过程都可以用于实现数据的完整性约束，但它们的方式和作用有所不同。

• 数据库触发器：触发器可以在数据插入、更新或删除之前或之后执行自定义的逻辑，从而实现数据的完整性约束。例如，可以使用触发器在插入新数据之前检查其完整性，并在必要时阻止操作的执行。

• 存储过程：存储过程通常用于执行复杂的数据操作，包括对数据的验证、转换、处理等。虽然存储过程也可以用于实现完整性约束，但通常更适合执行一系列的操作，而不是简单的约束检查。

为什么前者比使用后者声明要好：

数据库触发器通常比存储过程更适合用于声明完整性约束的原因包括：

1. 自动触发：触发器会在特定的数据库事件发生时自动执行，无需手动调用，这样可以确保在任何情况下都能够执行约束检查。
2. 作用范围：触发器可以应用于整个表，对表中的所有数据操作进行响应，而存储过程通常需要显式调用。
3. 数据库级别约束：触发器可以实现更复杂和灵活的约束逻辑，包括跨行、跨表的约束，而存储过程通常局限于单个过程的逻辑。
4. 数据库一致性：触发器可以确保在数据库的任何地方都执行完整性检查，从而提高了数据库的一致性和完整性。

域完整性的实现

CHECK 约束 

创建表时创建约束

CREATE TABLE books
book_id     number(10),
book_name   varchar2(50)    NOT NULL,
book_desc   varchar2(50)    DEFAULT 'New book',
max_lvl     number(3,2)     NOT NULL,
trade_price number(4,1)     NOT NULL,
CONSTRAINT ch_cost CHECK(max_lvl<=250)    /*定义为表的约束*/
);

修改表时创建约束

CREATE TABLE books
book id     number(10),
book name   varchar2(50)    NOT NULL,
book_desc   varchar2(50)    DEFAULT 'New book',
max_lvl     number(3,2)     NOT NULL,
trade_price number(4,1)     NOT NULL,
CONSTRAINT ch_cost CHECK(max_lvl<=250)    /*定义为表的约束*/
);

实体完整性的实现

创建约束

ON DELETE CASCADE 是用于在删除主表中的记录时自动删除相关联的从表记录的约束修饰语。这通常用于外键约束，以确保在主表中删除记录时，相关联的从表中的记录也会被自动删除，从而保持数据的一致性。

创建表的同时创建约束：

CREATE TABLE students (学号 CHAR(6) NOT NULL,姓名 VARCHAR2(16) NOT NULL,身高cm NUMBER(5,2) NOT NULL,体重kg NUMBER(4,1) NOT NULL,爱好 VARCHAR(30) NULL,性格 CHAR(10) NOT NULL,CONSTRAINT PK_XH PRIMARY KEY (学号), --定义主键约束CONSTRAINT FK_point FOREIGN KEY (学号) REFERENCES XSB (学号) ON DELETE CASCADE --定义外键约束
/*不能添加姓名，因为并没有将姓名 列设定为主键或唯一约束，外键约束必须引用被引用表的主键或者是唯一约束*/
);

通过修改表来创建约束：

SQL> select count(*) from salary_history;0SQL> ALTER TABLE salary_history2  ADD CONSTRAINT SH_ID PRIMARY KEY(EMPLOYEE_ID);表已更改。

注意事项：如果定义表可以存在重复的数据，就不可以添加主键了。

        验证如下 ：

我们先往salary_history表中添加重复的数据->

SQL> select *from salary_history;EMPLOYEE_ID     SALARY SALARY_DATE
----------- ---------- --------------1        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -24已选择 10 行。SQL> select count(*)from salary_history;COUNT(*)
----------10

然后我们执行脚本文件，持续添加

SQL> @C:\Users\24034\Desktop\编程\InsertToSH.sql;已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。SQL> @C:\Users\24034\Desktop\编程\InsertToSH.sql;已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。SQL> @C:\Users\24034\Desktop\编程\InsertToSH.sql;已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。已创建 1 行。SQL> select * from salary_history2  ;EMPLOYEE_ID     SALARY SALARY_DATE
----------- ---------- --------------1        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -241        500 01-5月 -24EMPLOYEE_ID     SALARY SALARY_DATE
----------- ---------- --------------2        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -241        500 01-5月 -242        600 01-5月 -24EMPLOYEE_ID     SALARY SALARY_DATE
----------- ---------- --------------3        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -241        500 01-5月 -242        600 01-5月 -243        800 01-5月 -24EMPLOYEE_ID     SALARY SALARY_DATE
----------- ---------- --------------4        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -24已选择 40 行。

当然这样看着不舒服，我们可以使用以下代码，更改终端设置：

SET PAGESIZE 0
SET LINESIZE 100

- 当你将页面大小（PAGESIZE）设置为 0 时，SQL*Plus 将不会分页显示查询结果，这意味着查询结果将不再按照页面大小分割成多个部分显示，而是一次性全部输出到屏幕上。

- 当你将行大小（LINESIZE）设置为一个足够大的值（如 100）时，SQLPlus 可以容纳更多的字符，但仍会在达到行尾时自动换行显示。

现在执行查询语句：

SQL> SELECT * FROM salary_history;1        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -241        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -241        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -241        500 01-5月 -242        600 01-5月 -243        800 01-5月 -244        900 01-5月 -245        550 01-5月 -246        650 01-5月 -247        530 01-5月 -248        720 01-5月 -249        920 01-5月 -2410       1000 01-5月 -24

现在我们对此表添加主键：

SQL> ALTER TABLE salary_history2  ADD CONSTRAINT SH_ID PRIMARY KEY(EMPLOYEE_ID);
ADD CONSTRAINT SH_ID PRIMARY KEY(EMPLOYEE_ID)*
第 2 行出现错误:
ORA-02437: 无法验证 (SYSTEM.SH_ID) - 违反主键

由此可见这是不被允许的，因为表中的EMPLOYEE_ID已经重复，不可以被设置为主键

删除约束 

DROP CONSTRAINT FK_point;

修改约束

在某些数据库管理系统中，可能允许直接修改外键约束的定义，但在许多主流的数据库系统（包括Oracle）中，通常不允许直接修改外键约束的定义。

SQL> ALTER TABLE students2  DROP CONSTRAINT FK_point;表已更改。SQL> ALTER TABLE students2  ADD CONSTRAINT FK_point FOREIGN KEY (学号) REFERENCES XSB (学号) ON DELETE CASCADE;表已更改。

以上是本期Oracle数据库的SQL语句分享，感谢陪伴🌹