数据库应用技术教程第四章数据库对象.ppt

第四章 数据库对象 S 4.1 表 Table 表的定义与维护 创建 CREATE TABLE 表名 （字段定义， 行级约束）； 字段定义：字段名 数据类型DEFAULT值 字段约束 4.1 表 Table 修改 ALTER TABLE 表名 ADD 字段定义 DROP COLUMN字段 MODIFY 字段定义 MODIFY:变长，不影响数据值，定义；变短，检 查数据。 ADD:字段默认值为NULL,可联合MODIFY使用。 4.1 表 Table 删除 DROP TABLE 表名； CASCADE CONSTRAINTS 清空 TRUNCATE TABLE 表名 相当于DELETE FROM 表名，区别： 1.DDL无需COMMIT，DML 需COMMIT。 2.DML需要ROLLBACK空间 4.2 约束 CONSTRAINTS 根据实际的业务要求，要求数据必须满足的一 系列条件称为商业规则。也称为数据的一致性 。实现商业规则有如下几种方式： 应用程序代码：在客户端和程序内部检查，不能保 证全面的一致性。 触发器：在DBMS内部，对所有数据的变化都通过 一段程序代码进行检查，可以保证一致性，但执行 代价较大。 约束（Constraints）：对于一些常见、通用的要求 ，使用设置的方式建立规则。设置简单，执行代价 较小，一致性强 数据类型是最基本的一种约束 4.2 约束的分类 非空 NOT NULL 字段级 唯一 UNIQUE 主键 PRIMARY KEY 外键 FOREIGN KEY 检查 CHECK 4.2 约束的使用 唯一：可为空，一般不用于表间关系。可以是 多个字段的联合 主键：唯一，不可为空，用于表联接。可以是 多个字段的联合 外键：表中某些字段一定会出现在其它表中， 应保持一致。可以是空值。 FOREIGN KEY (字段列表) REFERENCES 其它表名（ 字段名） 检查：只能针对单行记录。 4.2 外键 外键是描述表之间关系的桥梁和纽带。 用来保证参照完整性 当被其他数据引用的数据删除（或修改）时， 那些引用的数据如何处理？ 不允许删除（Restrict） 置为空或缺省值 级联删除（Cascade），可能会导致雪崩。 在数据导入导出时，往往需要暂时禁止外键 外键的循环引用 4.2 约束的语法 上述约束，均可写在CREATE TABLE，ALTER TABLE 中 也可以直接作为CONSTRAINTS 对象创建 作为表维护语言的一部分 NOT NULL（含NULL）只能写在行级 其他四种约束，即可写在对应的行上，也可以 写在语句的最后 如涉及到多个字段的联合，只能写在最后 约束创建举例 CREATE TABLE SALES (SID CHAR(3) NOT NULL, LID CHAR(3) NULL, SALARY NUMBER(4,0) PRIMARY KEY(AID), FOREIGN KEY(LID) REFERNCE LOCATIONS(LID), CHECK(SALARY0 AND UPPER(AID)=AID) ); 约束的状态 ENABLE NOVALIDATE ENABLE VALIDATE Existing dataNew data DISABLE NOVALIDATE DISABLE VALIDATE = = 约束检查的时机 DML statement Check nondeferred constraints COMMIT Check deferred constraints Primary and Unique Key 与索引 Is an index available for use? Yes No No Yes Yes No Create nonunique index Create unique index Do not use index Use existing index Key enabled? Constraint deferrable? No YesConstraint Deferrable? Is the index non-unique? No 4.3 视图 View 视图是一个虚表 不存储数据，在使用时对原有数据的重新 组织。 它是一个逻辑结构，不占据存储空间。 基于表、在大部分SQL中，特别是DQL和 DML，语法地位等价于表 使用视图的意义 限制存取（字段级权限和行级权限）。 不同用户不同视角和权限。 简化查询：别名、减少代码长度和复杂性 如，在求和值上做TOPN查询 提供不同的查询角度： 可以将常用的连接查询构造成视图。 兼容数据库设计的修改： 新表拼出旧表，视图兼容旧表的程序。 视图的创建与删除 l创建视图 CREATE OR REPLACE VIEW 名字 ( 字段列表 ) AS SELECT 子查询 WITH CHECK OPTION OR REPLACE:对已有同名视图进行替换，有违反定 义的修改，不允许。无则返错。 如省略字段列表则用子查询返回的字段名定义 删除视图 DROP VIEW 视图名 视图上的DML 简单视图可以直接执行 复杂视图 表的数目多于1个 有字段表达式作为视图的列 有聚组函数作为视图的列 复杂视图的DML不一定能够正确执行 带有CHECK OPTION的视图的DML，不能违 反条件 4.4 唯一性编号 自增长字段 在MSSQL和MYSQL中，使用自增长字段作 为解决方案 自增长字段是表格定义时，依附于某个字 段（通常是主键）的属性 插入时无须为此字段指定值，即自增长 不同表之间不能复用 必须有一种方式，获得上次insert的值 序列 Sequences 序列是一个独立的编号发生器 在一个事务中，通过访问序列的NEXTVAL和 CURRVAL可以获得序列的下一个值和当前 值 保证多个事务之间不会出现并发冲突 不与某个表绑定，可以在多个表之间复用 序列的创建与维护 创建 CREATE SEQUENCE 序列名 INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE|NOCYCLE CACHE N| NOCACHE 删除 DROP SEQUENCE 序列名 使用序列的例子 对象定义对象定义 CREATE TABLE INV_MASTER (INV_NO NUMBER, ) CREATE TABLE INV_DETAIL (INV_NO NUMBER, LINE_NO NUMBER, ) CREATE SEQUENCE SEQ_INV 插入数据插入数据 INSERT INTO INV_MASTER (INV_NO, ) VALUES (SEQ_INV.NEXTVAL, ) INSERT INTO INV_DETAIL (INV_NO, LINE_NO, ) VALUES (SEQ_INV.CURRVAL, 1, ) 4.5 索引 索引是这样一种数据库对象，它占据一定的存 储空间，能够在查询时被自动使用，从而提高 查询性能。 基于一个表中某个字段或字段的联合 索引的使用 不影响语句的正确性和结果 避免全表扫描 由查询优化器自动使用，并非所有查询都能获益 通过某些额外选项可以提示查询优化器利用索引 可能会加快DQL速度，但对DML速度的影响是双重 的 建立索引的考虑 建立索引 1.字段取值范围较大 2.字段中NULL值比例较大 3.字段经常做为查询或连接条件 4.多数查询期望返回行数较少 不建立索引 1.表较小 2.表经常更新 3.字段不常做为条件或出现在表达式内 4.期望返回行数较多 根据系统运行情况，对性能瓶颈进行优化 索引的分类 逻辑分类 唯一索引 vs 非唯一索引 自动索引 vs 手工创建的索引 UK, PK, FK 基于函数的索引 物理分类 Partitioned or non-partitioned B-tree Normal or reverse key Bitmap B-Tree Index的作用 Index entry header Key column length Key column value ROWID Root Branch Leaf Index entry Bitmap Index的作用 key start ROWID end ROWID bitmap Table Index Block 10 Block 11 Block 12 File 3 比较B-Tree和Bitmap索引 B-tree Suitable for high-cardinality columns Updates on keys relatively inexpensive Inefficient for queries using OR predicates Useful for OLTP Bitmap Suitable for low-cardinality columns Updates to key columns very expensive Efficient for queries using OR predicates Useful for data warehousing 索引的创建与维护 创建 CREATE UNIQUE BITMAP INDEX 索引名 ON 表名 (字段列表) REVERSE 重建 Rebuild 修改索引存储位置和翻转等参数 通过重建，删除不用的入口项，提高效率 ALTER INDEX 索引名 REBUILD ONLINE 整理索引 ALTER INDEX 索引名 COALESCE; 删除索引 DROP INDEX 索引名 4.6 其他数据库对象 4.6.1 聚簇 Cluster 4.6.2 分区 Partition 4.7 数据库设计初步 数据库设计的过程 概念设计 逻辑设计 物理设计 范式 1NF：不存在多值的字段。（不能违背） 2NF：多个主键时，不存在只由一个主键决定的信 息 如在orders里面加入顾客姓名 3NF：不存在由非主键决定的其他信息 如在SALES表中既有LID，又有CITY 主键的选择 业务主键和逻辑主键 业务主键 就是将业务中有唯一属性的字段挑出来做主键 逻辑主键 无实在意义的唯一性编号 选择原则 一、对于业务数据，最好采用逻辑主键; 二、对于业务复合主键有多个字段，需要采用逻辑主键; 三、对于基础数据，基于多方面考虑，是可以采用业务 主键的。这类表初始化以后数据不会经常发生改变。 四、取消业务主键后，在查询经常会用到的相关的业务 字段建立INDEX，可以提高查询效率; 五、使用逻辑主键,表的业务数据唯一性由程序来检查控 制，使业务数据重复这类脏数据控制在业务允许的范围; 六、业务数据的重复这类脏数据也可以通过分析结果数 据得到; 七、业务数据的逻辑主键使用numeric自增长型，在迁 移数据时，取消目标表的自增长，数据迁移完成后，再 重建逻辑主键。 违反范式的危害 违反范式可能产生两个问题： 1.数据冗余 数据冗余必然会带来数据不一致 2.更新异常 更新异常包括插入、修改和删除带来的异常 也就是对一个表的修改，可以会破坏其他表的 信息，或造成信息不一致，或者信息丢失。 一个数据库设计的演化 背景：一个网上论坛，用户可以注册、发 帖、回复帖子等。 设计一、只有一个表 用户名、email、主页、电话、联系地址发帖标 题、发帖内容、回复标题、回复内容 符合1NF，没有主键 设计二、增加“发帖ID”、“回复ID”字段 符合1NF，主键（用户名,发帖ID,回复ID） 不符合2NF 一个良好的设计 设计三 用户信息：用户名，email，主页，电话，联系 地址 帖子信息：发帖ID，标题，内容 回复信息：回复ID，标题，内容 发贴：用户名，发帖ID 回复：发帖ID，回复ID 满足1NF、2NF、3NF 进一步设计 设计四 用户信息：用户名，email，主页，电话，联系 地址 帖子信息：用户名，发帖ID，标题，内容 回复信息：发帖ID，回复ID，标题，内容 帖子信息表不满足2NF、回复信息表不满足 2NF 是上面设计中1-N关系的一种融合 在一定程度上，是可以接受的 不良设计举例 使用汉语拼音等作为表名、字段名 使用错误的数据类型，如使用字符串表示 日期、使用数值表示外部编号 违反1NF，使用特殊符号作为分隔符 直接使用（简单）实体的名字作为实体标 识，造成潜在的不一致 将关系作为属性，比如成绩表把期中、期