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

《数据库应用技术教程第四章数据库对象.ppt》由会员分享，可在线阅读，更多相关《数据库应用技术教程第四章数据库对象.ppt（40页珍藏版）》请在三一办公上搜索。

1、第四章 数据库对象,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 表名，区别：DDL无需CO

2、MMIT，DML 需COMMIT。DML需要ROLLBACK空间,4.2 约束 CONSTRAINTS,根据实际的业务要求，要求数据必须满足的一系列条件称为商业规则。也称为数据的一致性。实现商业规则有如下几种方式：应用程序代码：在客户端和程序内部检查，不能保证全面的一致性。触发器：在DBMS内部，对所有数据的变化都通过一段程序代码进行检查，可以保证一致性，但执行代价较大。约束（Constraints）：对于一些常见、通用的要求，使用设置的方式建立规则。设置简单，执行代价较小，一致性强数据类型是最基本的一种约束,4.2 约束的分类,非空 NOT NULL 字段级唯一 UNIQUE主键 PRIMA

3、RY KEY外键 FOREIGN KEY检查 CHECK,4.2 约束的使用,唯一：可为空，一般不用于表间关系。可以是多个字段的联合主键：唯一，不可为空，用于表联接。可以是多个字段的联合外键：表中某些字段一定会出现在其它表中，应保持一致。可以是空值。FOREIGN KEY(字段列表)REFERENCES 其它表名（字段名）检查：只能针对单行记录。,4.2 外键,外键是描述表之间关系的桥梁和纽带。用来保证参照完整性当被其他数据引用的数据删除（或修改）时，那些引用的数据如何处理？不允许删除（Restrict）置为空或缺省值级联删除（Cascade），可能会导致雪崩。在数据导入导出时，往往需要暂时禁

4、止外键外键的循环引用,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(

5、SALARY0 AND UPPER(AID)=AID);,约束的状态,ENABLENOVALIDATE,ENABLEVALIDATE,Existing data,New data,DISABLENOVALIDATE,DISABLEVALIDATE,=,=,约束检查的时机,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

6、nonunique index,Create unique index,Do not use index,Use existing index,Key enabled?,Constraintdeferrable?,No,Yes,ConstraintDeferrable?,Is the indexnon-unique?,No,4.3 视图 View,视图是一个虚表不存储数据，在使用时对原有数据的重新组织。它是一个逻辑结构，不占据存储空间。基于表、在大部分SQL中，特别是DQL和DML，语法地位等价于表,使用视图的意义,限制存取（字段级权限和行级权限）。不同用户不同视角和权限。简化查询：别名、减少

7、代码长度和复杂性如，在求和值上做TOPN查询提供不同的查询角度：可以将常用的连接查询构造成视图。兼容数据库设计的修改：新表拼出旧表，视图兼容旧表的程序。,视图的创建与删除,创建视图CREATE OR REPLACE VIEW 名字(字段列表)ASSELECT 子查询 WITH CHECK OPTION OR REPLACE:对已有同名视图进行替换，有违反定义的修改，不允许。无则返错。如省略字段列表则用子查询返回的字段名定义 删除视图DROP VIEW 视图名,视图上的DML,简单视图可以直接执行复杂视图表的数目多于1个有字段表达式作为视图的列有聚组函数作为视图的列复杂视图的DML不一定能够正确

8、执行带有CHECK OPTION的视图的DML，不能违反条件,4.4 唯一性编号,目的自动产生唯一编号，可看作编号发生器。数字要求递增（或按一定规律变化），不重复不断号用作主键避免并发冲突减少应用程序代码提高性能一个错误的例子,自增长字段,在MSSQL和MYSQL中，使用自增长字段作为解决方案自增长字段是表格定义时，依附于某个字段（通常是主键）的属性插入时无须为此字段指定值，即自增长不同表之间不能复用必须有一种方式，获得上次insert的值,序列 Sequences,序列是一个独立的编号发生器在一个事务中，通过访问序列的NEXTVAL和CURRVAL可以获得序列的下一个值和当前值保证多个事务之

9、间不会出现并发冲突不与某个表绑定，可以在多个表之间复用,序列的创建与维护,创建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 SEQUENC

10、E 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速度的影响是双重的,建立索引的

11、考虑,建立索引字段取值范围较大字段中NULL值比例较大字段经常做为查询或连接条件多数查询期望返回行数较少 不建立索引表较小表经常更新字段不常做为条件或出现在表达式内期望返回行数较多根据系统运行情况，对性能瓶颈进行优化,索引的分类,逻辑分类唯一索引 vs 非唯一索引自动索引 vs 手工创建的索引UK,PK,FK基于函数的索引物理分类Partitioned or non-partitioned B-treeNormal or reverse keyBitmap,B-Tree Index的作用,Index entry header,Key column length,Key column value

12、,ROWID,Root,Branch,Leaf,Index entry,Bitmap Index的作用,Table,Index,Block 10,Block 11,Block 12,File 3,比较B-Tree和Bitmap索引,B-treeSuitable for high-cardinality columnsUpdates on keys relativelyinexpensiveInefficient for queries using OR predicatesUseful for OLTP,BitmapSuitable for low-cardinality columns Up

13、dates to key columns veryexpensiveEfficient for queries using OR predicatesUseful for data warehousing,索引的创建与维护,创建CREATE UNIQUE BITMAP INDEX 索引名ON 表名(字段列表)REVERSE重建 Rebuild修改索引存储位置和翻转等参数通过重建，删除不用的入口项，提高效率ALTER INDEX 索引名 REBUILD ONLINE整理索引ALTER INDEX 索引名 COALESCE;删除索引DROP INDEX 索引名,4.6 其他数据库对象,4.6.1

14、聚簇 Cluster4.6.2 分区 Partition,4.7 数据库设计初步,数据库设计的过程概念设计逻辑设计物理设计范式1NF：不存在多值的字段。（不能违背）2NF：多个主键时，不存在只由一个主键决定的信息如在orders里面加入顾客姓名3NF：不存在由非主键决定的其他信息如在SALES表中既有LID，又有CITY,主键的选择,业务主键和逻辑主键业务主键就是将业务中有唯一属性的字段挑出来做主键逻辑主键无实在意义的唯一性编号,选择原则,一、对于业务数据，最好采用逻辑主键;二、对于业务复合主键有多个字段，需要采用逻辑主键;三、对于基础数据，基于多方面考虑，是可以采用业务主键的。这类表初始化以

15、后数据不会经常发生改变。四、取消业务主键后，在查询经常会用到的相关的业务字段建立INDEX，可以提高查询效率;五、使用逻辑主键,表的业务数据唯一性由程序来检查控制，使业务数据重复这类脏数据控制在业务允许的范围;六、业务数据的重复这类脏数据也可以通过分析结果数据得到;七、业务数据的逻辑主键使用numeric自增长型，在迁移数据时，取消目标表的自增长，数据迁移完成后，再重建逻辑主键。,违反范式的危害,违反范式可能产生两个问题：1.数据冗余 数据冗余必然会带来数据不一致2.更新异常更新异常包括插入、修改和删除带来的异常也就是对一个表的修改，可以会破坏其他表的信息，或造成信息不一致，或者信息丢失。,一

16、个数据库设计的演化,背景：一个网上论坛，用户可以注册、发帖、回复帖子等。设计一、只有一个表用户名、email、主页、电话、联系地址发帖标题、发帖内容、回复标题、回复内容 符合1NF，没有主键设计二、增加“发帖ID”、“回复ID”字段符合1NF，主键（用户名,发帖ID,回复ID）不符合2NF,一个良好的设计,设计三用户信息：用户名，email，主页，电话，联系地址 帖子信息：发帖ID，标题，内容 回复信息：回复ID，标题，内容 发贴：用户名，发帖ID 回复：发帖ID，回复ID 满足1NF、2NF、3NF,进一步设计,设计四用户信息：用户名，email，主页，电话，联系地址 帖子信息：用户名，发帖

17、ID，标题，内容 回复信息：发帖ID，回复ID，标题，内容 帖子信息表不满足2NF、回复信息表不满足2NF是上面设计中1-N关系的一种融合在一定程度上，是可以接受的,不良设计举例,使用汉语拼音等作为表名、字段名使用错误的数据类型，如使用字符串表示日期、使用数值表示外部编号违反1NF，使用特殊符号作为分隔符直接使用（简单）实体的名字作为实体标识，造成潜在的不一致将关系作为属性，比如成绩表把期中、期末作为属性违反2NF、3NF，实体之间缺少关系,实体设计注意的几个问题,实体某些属性实际上是多值的如姓名某些属性的值域是一个小的离散集合如省份，对应取值不应该直接存储，而应该设计一个对应的代码表，然后存储代码 推广：一般数据库系统中都有一个类似的代码总表 实体一般要有一个状态标志意味着实体的生存周期，也就是创建、生存和作废在生产数据库中一般不删除实体信息。还要有时间字段标志实体的产生时间。还很可能有一个录入员的信息。某些属性需要仔细考虑其划分的层次粒度如姓名、地址,一般系统都有的表,用户表权限表系统、用户参数表数据字段,