Oracle 数据库中的 索引、约束、视图

article/2025/9/26 14:15:05

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

  1. 为Person 表添加名为PERSON_PID_PK ，主键为ID 的约束
ALTER TABLE PERSON ADD CONSTRAINT PERSON_PID_PK PRIMARY KEY(ID);

--添加约束完美写法, 添加前先判断该约束是否存在
DECLARE number202203031610 number;
beginSELECT COUNT(1) INTO number202203031610 FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('A39') AND CONSTRAINT_NAME = UPPER('PKNAME_A00');if number202203031610 = 0 thenEXECUTE IMMEDIATE'ALTER TABLE A39 ADD CONSTRAINT PKNAME_A00 PRIMARY KEY (A00)';end if;
end;
/
  1. 为Person 表的NAME 字段添加名为PERSON_NAME_UK 的唯一约束
ALTER TABLE PERSON ADD CONSTRAINT PERSON_NAME_UK UNIQUE(NAME);
  1. 为Person 表的Age 字段添加名为PERSON_AGE_CK 的检查约束
ALTER TABLE PERSON ADD CONSTRAINT PERSON_AGE_CK CHECK(AGE BETWEEN 0 AND 150);
  1. 为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_TYPEUSE
CCheck on a table Column（检查约束）
ORead Only on a view Object（只读约束）
PPrimary Key Object（主键约束）
RReferential AKA Foreign Key Column（外键约束）
UUnique Key Column（唯一约束）
VCheck 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 EMPLOYEEWHERE 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 EMPLOYEEWHERE 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 操作

三、索引

  1. 什么是索引 (Index)

  索引是一种用于提升查询效率的数据库对象。通过快速定位数据的方法，来减少磁盘的 I/O 操作。索引信息与表独立存放，并且Oracle 数据库自动使用和维护索引。

  1. 索引分类

  索引大体上分为两类，唯一性索引 与 非唯一索引。

  1. 创建索引的方式

  ① 自动创建：在定义主键或唯一键约束时，系统会自动在相应的字段上创建唯一性索引。
  ② 手动创建：用户可以在其他列上创建非唯一索引，用来提高查询效率。

1）索引优缺点

建立索引的优点：

  1. 大大加快了数据的检索速度。
  2. 创建唯一性索引，保证数据库表中每一行数据的唯一性。
  3. 加速表与表之间的连接。
  4. 在使用分组和排序字句进行数据检索时，可以显著地减少查询中分组和排序的时间。

索引的缺点：

  1. 索引需要占用物理空间。
  2. 当对表中的数据进行增删改查，同时也需要动态的维护索引，降低了数据的维护速度。

2）创建与使用索引的原则

创建索引： 创建索引一般有两个目的，维护被索引列的唯一性和提供快速访问表中数据的策略。小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。

  1. 在SELECT操作占大部分的表上创建索引。
  2. 在WHERE字句中出现最频繁的列上创建索引。
  3. 在选择查询高的列上创建索引（补充索引选择性，最高是1，eg：primary key）。
  4. 复合索引的主列应该是最有选择性的和WHERE限定条件最常用的列，并以此类推第二列。

使用索引： 查询结果是所有数据行的 5%以下时，使用index查询效果最好。WHERE条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引，因为当sql 语句所查询的列，全都出现在复合索引时，此时由于 oracle 只需要查询索引块即可获取所有数据，当然比使用多个单列索引要快得多。索引利于SELECT，但对经常 增改删 的表会降低效率。

  1. 使用与不使用索引的区别
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高。

  1. 复合索引的注意点，以及索引无效的隐式写法

  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）管理索引

  1. 先插入数据后创建索引，向表中插入大量数据之前最好不要先创建索引，因为如果先创建索引。那么在插入每行数据的时候都要更改索引，这样会大大降低插入数据的速度。
  2. 设置合理的索引列顺序。
  3. 限制每个表索引的数量。
  4. 删除不必要的索引。
  5. 为每个索引指定表空间。
  6. 经常做 insert、delete 尤其是 update 的表最好定期 exp、imp 表数据，定期整理数据从而降低碎片（要停应用，以保持数据一致性），有索引的最好定期 rebuild 索引，以降低索引碎片，提高效率（rebuild 索引期间只允许做 select 操作）。

http://chatgpt.dhexx.cn/article/jX44CgEy.shtml

相关文章

数据库原理与应用（SQL）——2、关系数据库(E-R图、关系模式、关系运算、关系代数)

数据库原理与应用（SQL）——2、关系数据库(E-R图、关系模式、关系运算、关系代数)

目录 关系 关系运算 元组、域关系演算表达式 题目 关系代数表达式——例 元组演算表达式——例 域演算表达式——例 关系数据库是以二维表形式组织数据，应用数学方法处理数据库组织的方法。目前关系数据库系统在数据管理中已占据了主导地位。 关系 关系&…
阅读更多...
Oracle约束 SQL语句分类 集合运算 索引 相关知识点讲解

Oracle约束 SQL语句分类 集合运算 索引 相关知识点讲解

ORACLE 约束： 数据库用来确保数据满足业务的手段，强制数据符合某些特定条件或者规范才可以插入 主键约束： primary key PK唯一约束：unique非空约束：not null外键约束：foreign key FK检查约束：…
阅读更多...
SQL对数据进行统计、常用集合函数

SQL对数据进行统计、常用集合函数

学生表： 1、统计记录个数 1） count（【all】*）【as 列别名】 返回表中的记录数 执行：SELECT COUNT (* ) FROM 学生表 结果为： 执行：SELECT COUNT (* ) as 学生总人数 FROM 学生表…
阅读更多...
关系数据库SQL数据查询

关系数据库SQL数据查询

关系数据库SQL数据查询 数据查询 一、单表查询 1.查询仅涉及一个表，选择表中的若干列 [例1] 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student; [例2] 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student;查询全部列 选出所有属…
阅读更多...
样本轮廓系数（原理、sklearn.metrics.silhouette_score、silhouette_samples参数介绍）

样本轮廓系数（原理、sklearn.metrics.silhouette_score、silhouette_samples参数介绍）

一、轮廓系数含义： 轮廓系数（Silhouette Coefficient），是聚类效果好坏的一种评价方式。 最佳值为1，最差值为-1。接近0的值表示重叠的群集。负值通常表示样本已分配给错误的聚类，因为不同的聚类更为相…
阅读更多...
java上位机开发（开篇）

java上位机开发（开篇）

【 声明：版权所有，欢迎转载，请勿用于商业用途。 联系信箱：feixiaoxing 163.com】 目前对于java语言，很多人都认为太简单。其实这是不对的，因为某种意义上来说所，java语言就像学习日语一样&#…
阅读更多...
Qt之上位机开发实战（一）

Qt之上位机开发实战（一）

一、Qt三大金刚 1、qt下的串口编程 2、qt下的网络编程 3、qt下的GPIO 本章节先介绍一下qt下的串口编程，首先我们新建一个Widget项目 其中接受框使用Plain Text Edit（设置只读），串口号等按钮使用Combo Box，发送框使…
阅读更多...
C# | 上位机开发新手指南（二）上位机通信

C# | 上位机开发新手指南（二）上位机通信

上位机开发新手指南（二）上位机通信 文章目录 上位机开发新手指南（二）上位机通信前言串口通信应用场景与控制器通信与传感器通信与仪器仪表通信与智能家居设备通信 常见协议ModbusCAN（Controller Area Network&#xff…
阅读更多...
C# 西门子PLC上位机开发环境搭建

C# 西门子PLC上位机开发环境搭建

一、安装软件 1. TIA_Portal_STEP7 15 博途，西门子的自动化开发软件 2. SIMATIC_S7PLCSIM_V15_1 西门子的仿真软件 3. NetToPLCsim C#程序是不能和仿真通信的，只有利用此软件，C#程序才能连接到仿真 二、配置软件 1. 博途 点击 创建新…
阅读更多...
java上位机开发（网络编程）

java上位机开发（网络编程）

【 声明：版权所有，欢迎转载，请勿用于商业用途。 联系信箱：feixiaoxing 163.com】 所谓的网络编程，一般就是指UDP、TCP编程。传统的方法就是用api或者sdk直接编程，这种方法对于简单的协议是可以的。但是对于…
阅读更多...
上位机开发（详细设计）

上位机开发（详细设计）

【 声明：版权所有，欢迎转载，请勿用于商业用途。 联系信箱：feixiaoxing @163.com】 所谓软件详细设计，它的上面对应的是架构设计，下面对应的是程序开发。架构设计有可能涉及多个软件，比如既可能涉及上位机这端，也可能涉及到mcu这端。详细设计的话，一般要落实到…
阅读更多...
android ble 上位机,BLE的Android上位机开发（下）

android ble 上位机,BLE的Android上位机开发（下）

原标题：BLE的Android上位机开发(下) ELLO! 大家好！马上就更新下篇了呢~哈哈！(上篇的地址：BLE的Android上位机开发(上))废话不多说了，先上主角吧！代码如下！ [Java] 纯文本查看复制代码 ? 01 02 …
阅读更多...
java上位机开发（java基础）

java上位机开发（java基础）

【 声明：版权所有，欢迎转载，请勿用于商业用途。 联系信箱：feixiaoxing 163.com】 java基础就是把java环境安装好，把基础程序构建起来。至少说让开发的同学来说有一点信心。从这一点来说，就算编写一个“Hell…
阅读更多...
上位机开发的意义

上位机开发的意义

上位机开发的意义 常见的上位机定义为一台可以发出特定操控命令的计算机， 通过操作预先设定好的命令，将命令传递给下位机，通过下位机来控制设备完成各项操作。此定义着重于强调控制指令的发送，实际上除了发送控制命令&#xff0c…
阅读更多...
Qt之上位机开发实战（三）

Qt之上位机开发实战（三）

UDP编程 udp相对于tcp来说相对简单&#xff0c;udp不分客户端和服务器&#xff0c;只需要使用一个QUdpSocket类&#xff0c;首先完成布局 Pro文件下加QT core gui network&#xff0c;.h文件下加#include <QUdpSocket> .h文件public下创建对象 QUdpSocket *udpSocket;…
阅读更多...
C# | 上位机开发新手指南（三）框架

C# | 上位机开发新手指南（三）框架

上位机开发新手指南&#xff08;三&#xff09;框架 文章目录 上位机开发新手指南&#xff08;三&#xff09;框架.NETWinFormsWPFUnity3D CQTMFC 其他语言LabVIEW .NET WinForms 在上位机开发中&#xff0c;Windows Forms是使用最广泛的C#框架之一。Windows Forms是.NET Fra…
阅读更多...
哈喽上位机（上位机开发指南）

哈喽上位机（上位机开发指南）

哈喽&#xff0c;上位机&#xff01; -------上位机开发指南 作者&#xff1a;Robert Zhang本博客以一个程序猿的角度解答了: - 有关上位机开发的疑惑 - 上机软件开发需要学习哪些知识 - 开发一个完整商业软件所需的技术与实现方法 - 怎样美化软件&#xff0c;那些漂亮的软件…
阅读更多...
C#上位机开发常遇问题

C#上位机开发常遇问题

C#上位机开发常遇问题 C#引用C动态库/静态库静态库动态库 unsafe和fixed关键字的应用unsafefixed Path类的使用写在结尾 最近完成了一个小项目的开发&#xff0c;在开发中遇到了一些问题&#xff0c;我感觉这些问题其他人在开发的时候也会遇到&#xff0c;所以我在这边总结一下…
阅读更多...
C# 三菱PLC上位机开发环境搭建

C# 三菱PLC上位机开发环境搭建

一、安装软件 用到两个三菱的软件&#xff1a; 1. MX Component&#xff08;下载地址 也可以直接在官网上搜索&#xff0c;注意MX后面有空格&#xff09; 用于连接PLC 2. GX Works2&#xff08;下载地址 用GX Works3也行&#xff09; 这个软件主要是电气做PLC编程&#xf…
阅读更多...
USB上位机开发

USB上位机开发

上位机开发采用图形化界面&#xff0c;使用C#语言进行开发。调用C编写的Dll动态库&#xff0c;实现与STM32进行USB通信的目的。 首先&#xff0c;编写Dll动态库。 开发环境采用VS2017&#xff0c;语言C 将lusb0_usb.h和libusb.lib文件拷贝到工程目录下。 libusb-win32-bin-…
阅读更多...
推荐文章

Copyright @ 2022~2023