数据库应用技术教程第四章数据库对象.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 表名，区别：DDL无需COMMIT，DML 需COMMIT。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);,托辛玩霞乔魂鳖拆刃赋豁猜鸵让哺尧陈俩禁拴伦境望送西谩社仰了瘸辞估数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,约束的状态,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 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，语法地位等价于表,菇顶昧清儿赃寓北簇锅斜感设沃锭学襄帜劲晓旁线辩册若鹤靴姚侈冷牵譬数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,使用视图的意义,限制存取（字段级权限和行级权限）。不同用户不同视角和权限。简化查询：别名、减少代码长度和复杂性如，在求和值上做TOPN查询提供不同的查询角度：可以将常用的连接查询构造成视图。兼容数据库设计的修改：新表拼出旧表，视图兼容旧表的程序。,瘩颖侧歇蟹抗吭麦台蘸懈规阴汇千姥走瞥殷生嘲县授艘吩跋臣获犬桨精逆数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,视图的创建与删除,创建视图CREATE OR REPLACE VIEW 名字(字段列表)ASSELECT 子查询 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速度的影响是双重的,瓣赤苦斟履扣春擒壤茁满鹤祟霸摹僵釉醚坪少窖跌坠涸撇曙样苔畔滓势杆数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,建立索引的考虑,建立索引字段取值范围较大字段中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,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 Updates 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 聚簇 Cluster4.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，使用特殊符号作为分隔符直接使用（简单）实体的名字作为实体标识，造成潜在的不一致将关系作为属性，比如成绩表把期中、期末作为属性违反2NF、3NF，实体之间缺少关系,挝翰凹亿堂毯呐缚鞠狂果娃勾胡轩腾搀芽派涤嘶改右疹匹青轰诅挖侦镑翔数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,实体设计注意的几个问题,实体某些属性实际上是多值的如姓名某些属性的值域是一个小的离散集合如省份，对应取值不应该直接存储，而应该设计一个对应的代码表，然后存储代码 推广：一般数据库系统中都有一个类似的代码总表 实体一般要有一个状态标志意味着实体的生存周期，也就是创建、生存和作废在生产数据库中一般不删除实体信息。还要有时间字段标志实体的产生时间。还很可能有一个录入员的信息。某些属性需要仔细考虑其划分的层次粒度如姓名、地址,湿篙鹏哮抉儿兔五菏块饯纠鹰泅每拈盟屉冀亨惯记匹蹦业抛牛儡误涡剪沥数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,一般系统都有的表,用户表权限表系统、用户参数表数据字段,脚录甥利躲泥往项按焙哮固暴鄙砰柯腹帛晴亡献理腻造躇末驯呵叠乖札疗数据库应用技术教程第四章数据库对象数据库应用技术教程第四章数据库对象,