方案
- 概念:方案是指由特定用户拥有的数据库对象的集合。方案与拥有该方案的用户同名。方案对象是指直接引用数据库数据的逻辑结构。方案对象包含表、视图和索引等多种结构。
- 重要的方案
- sys方案:包含数据字典
- system方案:包含存储管理信息的附加表和视图
- 命名数据库对象
- 名称长度必须为 1 至 30 个字节()
- 不带引号的名称不能采用 Oracle 保留字
- 不带引号的名称必须以数据库字符集中的字母字符开头
- 不建议使用带引号的名称
表
- 表中的数据类型
- CHAR(size [BYTE|CHAR]):size 个字节或字符的固定长度字符数据
- VARCHAR2(size [BYTE|CHAR]):最大长度为size 个字节或字符的可变长度字符串
- DATE:在公元前 4712 年 1 月 1 日至公元 9999 年12 月 31 日之间的有效日期
- NUMBER(p,s):精度为 p 且小数位数为 s 的数字(精度可介于1 与 38 之间。小数位数可介于–84 与 127 之间)
- 创建和修改表:em
- 删除表
- 语法:DROP TABLE [schema.] table [CASCADE CONSTRAINTS] [PURGE]
- 内容:DROP TABLE命令可删除数据、表结构和关联的对象权限。
- 可选项
- purge:无法闪回,表数据仍存在,空间仍占用
- cascade constraints:删除表的同时删除约束
- 截断表
- 语法:TRUNCATE TABLE [schema.] table [{DROP | REUSE} STORAGE] (DDL命令)
- 内容:
- 截断表会使表的行数据不可用,从而可以选择释放占用空间。
- 相应的索引也会被截断。
- 不会截断外键引用的表。
- 使用此命令时不会触发删除触发器。
约束条件
- 数据完整性
- NOT NULL(非空):默认情况下,表中的所有列均允许空值。Null 指没有值。NOT NULL 约束条件要求表列必须包含非空值
- UNIQUE Key(唯一键):UNIQUE键完整性约束条件要求一列或一组列(键)中的每个值必须唯一,即在指定的列或一组列中,表任意两行的值均不重复。
- PRIMARY KEY(主键):数据库中的每个表最多可包含一个PRIMARY KEY 约束条件。在这个约束条件的限制下,单个列或一组列中的值就构成了行的唯一标识符。(NOT NULL +PRIMARY KEY 自动创建唯一索引)
- 引用完整性约束条件:对于每个表行,引用完整性约束条件要求外键中的值必须与父键中的值匹配。
- 检查约束条件:单个列或一组列的CHECK完整性约束条件要求为每个表行指定的条件必须为真或未知。
- 定义约束条件:em
- 违反约束条件
- 唯一性:尝试在设置有唯一约束条件的列中使用重复值
- 引用完整性:违反了具有父行的每个子行的规则。
- 检查:尝试在不遵循列定义规则的列中存储一个值
- 约束条件状态(
- 启用 (ENABLE) 或禁用 (DISABLE) 完整性约束条件。如果启用约束条件,在数据库
- 中输入或更新数据时就会检查数据)
- DISABLE NOVALIDATE:不检查新数据和现有数据,因此这些数据可能不符合约束条件。
- DISABLE VALIDATE:如果约束条件处于此状态,则不允许对受约束的列进行任何修改,因为在验证现有数据后又允许将未经检查的数据输入表中,会出现不一致的情况。
- ENABLE NOVALIDATE:新数据符合约束条件,但现有数据处于未知状态。
- ENABLE VALIDATE:新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。
- 约束条件检查
- 非延迟约束条件:又称即时约束条件,是在每个DML 语句结束时强制执行的。违反约束条件会导致回退语句。
- 延迟约束条件:提交事务处理时才检查的约束条件。如果在提交时检测到任何违反约束条件的情况,则会回退整个事务处理。
- 流程
- 非延迟约束条件已检查
- commit发出
- 延迟约束条件已检查
- commit完成
- 使用 SQL 创建约束条件
- ALTER TABLE countriesADD (UNIQUE(country_name) ENABLE NOVALIDATE);
-
- 执行此语句后,需要对 COUNTRIES 表执行插入或更新操作,才能生成唯一的COUNTRY_NAME 值。但是,有可能在发出此语句后,表中已存在非唯一的COUNTRY_NAME 值。NOVALIDATE 关键字指示应忽略这些值。仅新行受到约束条件的约束。
- ALTER TABLE employees ADD CONSTRAINT pk PRIMARY KEY (employee_id);
-
- 使用此语句可向雇员表添加主键。约束条件名称为 PK,主键为 EMPLOYEE_ID 列。
- CREATE TABLE t1 (pk NUMBER PRIMARY KEY, fk NUMBER, c1 NUMBER, c2 NUMBER,CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1, CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0));
-
- RI 约束条件强制使 FK 列中的值必须存在于 T1 表的主键列中。CK1 约束条件强制使PK 和 C1 列大于零。
- 查看表中的列和内容:em
- 对表执行的操作
- Create Like(类似创建):使用此操作可创建与选定表具有相同结构的表。
- Create Index(创建索引):使用此选项可创建表的索引。
- Generate DDL(生成 DDL):生成表示已存在的表的DDL。
- Grant Privileges(授予权限):默认情况下,创建表后,只有所有者才能对其执行任何操作。其它用户若要对表执行DML 甚至 DDL,所有者必须向这些用户授予权限。
- Show Dependencies(显示相关性):显示此表依赖的对象,或依赖于此表的对象。
- View Data(查看数据)
索引
- 定义:索引是与表关联的可选结构。创建索引可提高更新和检索数据的性能
- 类型
- B树索引
- 结构:索引的顶层为根,它包含指向索引中下一层次的条目。下一层次为分支块,它又指向位于索引中下一层次的块。位于最低层次的是叶节点,它包含指向表行的索引条目。叶块在两个方向都是相互关联的,这便于按键值升序或降序扫描索引。
- 索引叶条目的格式
- 条目头,用于存储列数和锁定信息。
- 键列长度-值对,用于定义键中的列大小,后面跟着列值
- 包含键值的行的 ROWID。
- 索引叶条目的特性
- 当多个行具有相同的键值时,如果不压缩索引,键值会出现重复。
- 当某行包含的所有键列为 NULL 时,该行没有对应的索引条目。因此,当WHERE子句指定了 NULL 时,始终会导致全表扫描。
- 因为所有行属于同一个段,所以要使用受限的 ROWID指向表行。
- 对索引执行 DML 操作的效果
- 执行插入操作会导致在相应块中插入索引条目。
- 删除一行只会导致对索引条目进行逻辑删除。在删除块中的所有条目之前,已删除行占用的空间不可用于新条目。
- 更新键列会导致对索引进行逻辑删除和插入。PCTFREE 设置对索引没有影响,但创建时除外。即使索引块的空间少于 PCTFREE指定的空间,也可以向索引块添加新条目。
- 位图索引
- 优势:
- 表具有数百万行且键列的基数较低时,也就是列的独特值极少时。
- 经常使用涉及 OR 运算符的多个 WHERE 条件组合进行查询时。
- 键列上存在只读活动或较少更新活动时。
- 位图索引的叶节点
- 条目头,其中包含列数和锁定信息
- 由每个键列的长度 - 值对组成的键值
- 开始 ROWID
- 结束 ROWID
- 由位字符串组成的位图段。
- 索引选项:
- 唯一索引可确保每个索引值是唯一的。
- 索引可按升序或降序存储其键值。
- 反向键索引以反向顺序存储其键值字节。
- 组合索引是基于多列的索引。
- 基于函数的索引是以函数返回值为基础的索引。
- 在压缩的索引中重复的键值已被删除。
- 创建索引:CREATE INDEX my_index ON employees(last_name, first_name);
视图
- 定义:视图是一个或多个表中或其它视图中的数据的自定义表示方式。视图并不包含数据,它们从所基于的表中获取数据。这些表称为视图的基表。
- 创建视图:
- CREATE VIEW v AS SELECT location_id, country_name
- FROM locations l, countries c
- WHERE l.country_id = c.country_id AND c.country_id
- in ('AU','BR');
序列
- 定义(序列是按照某种模式自动生成整数的一种机制)
- 序列具有名称,表明了请求下一个值时引用序列的方式。
- 序列不与任何特定的表或列相关联。
- 增长方式可为升序,也可为降序。
- 编号之间的间隔可为任意大小。
- 达到限额时,序列可循环使用。(循环序列)
- 创建序列
- Name
- Schema
- Maximum Value:指定序列可以生成的最大值。默认为unlimited(最大值升序序列的最大值为10的27次方,降序序列的最大值为–1)
- Minimum Value:指定序列的最小值。默认为unlimited(升序序列的最小值为1,降序序列的最小值为 –10的26次方)
- Interval(间隔):指定序列号之间的间隔。这个整数值可以是任意正整数或负整数,但不能为零。它不能超过 28 位数。默认值为1。
- Initial(初始值):指定要生成的第一个序列号。
- Cycle Values(循环值):升序序列达到序列最大值后,会生成序列最小值。降序序列达到序列最小值后,会生成序列最大值。
- Order Values(顺序值):此选项可确保按请求顺序生成序列号。
- Cache Options(缓存选项)
- 使用序列
- CURRVAL:返回序列的当前值
- NEXTVAL:递增序列,返回下一个值
临时表
- 定义
- 会话或事务处理结束时会自动清除存储数据
- 为每个会话提供专用数据存储
- 可供所有会话使用,而不影响各个会话的专用数据
- 注意事项
- 创建
- CREATE GLOBAL TEMPORARY TABLE employees_temp
- ON COMMIT PRESERVE ROWS
- AS SELECT * FROM employees;
- 使用 TRUNCATE TABLE 命令删除表内容。
- 在临时表中可创建下列项:索引,视图,触发器
数据字典
- 概览:
- Oracle 的数据字典是对数据库的描述,其中包含了数据库中所有对象的名称和属性。
- sys用户所有
- 不得使用 SQL 直接进行修改
- 使用示例
- SELECT table_name, tablespace_name FROM user_tables;
-
- 方案中已创建的表的名称(以及表所在表空间的名称)是什么?
- SELECT USERNAME, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS = 'OPEN';
-
- 在此数据库中当前哪些用户可以登录?