数据库基础教程9.ppt

数据库系统基础教程AFirstCourseinDatabaseSystems 2 上海交通大学计算机系蒋建伟62932358jwjiang 3 教材 JeffreyD Ullman JenniferWidom AFirstCourseInDatabaseSystems 或中译本 数据库系统基础教程 清华大学出版社 4 参考 db stanford edu ullman fcdb htmlC J Date AnIntroductiontoDatabaseSystem Ed 7 RaghuRamakrishnan JohannesGehrke DatabaseManagementSystems Ed 2 萨师煊 王珊 数据库系统概论 第三版 5 本课程内容 数据库的建模和设计ODL及E R关系模型及关系代数范式设计数据库编程 查询和修改操作SQL StructuredQueryLanguage产品与工具 第一章数据库系统的世界TheWorldsofDatabaseSystems 数据库系统的发展数据库管理系统的结构未来的数据库系统 7 1 1数据库系统的发展c 一 术语1 数据库是长期储存在计算机内的 有组织的 可共享的数据的集合 8 2 数据库管理系统DBMS DataBaseManagementSystem是处理数据库访问的软件 提供数据库的用户接口 DBMS的目的 提供一个可以方便地 有效地存取数据库信息的环境 9 3 数据库系统是指在计算机系统中引入数据库后的系统 10 数据库系统构成 11 保存信息的两种不同方法 永久性的系统文件 数据库系统 文件方式的问题 数据的冗余和不一致数据访问困难数据孤立完整性问题原子性问题并发访问异常安全性问题 二 文件系统与数据库系统 12 数据库方法能较好地解决以上的问题数据的独立性有效地访问数据减少应用程序的开发时间数据的一致性和安全性统一的数据管理并发的数据访问 三 为什么用数据库 13 几种模型 基于树的层次模型基于图的网状模型物理相关 无高级查询语言基于表的关系模型物理无关 支持高级查询语言 基于对象的面向对象模型OOOR 四 数据库模型的发展 定长记录 14 关系数据库系统 15 关查询语言SQL语言SELECTbalanceFROMAccountsWHEREaccountNO 67890 关系数据库系统 16 DBMS的组成数据 元数据存储管理程序事务管理程序查询处理程序 1 2数据库管理系统的结构 17 数据 元数据关于数据结构的信息 关于数据的数据 索引 INDEX DBMS的组成 18 存储管理程序文件管理程序缓冲区管理查程序 DBMS的组成 19 查询处理程序查询优化磁盘访问 是查询的主要代价 索引是查询优化的利器 DBMS的组成 20 事务管理程序事务 是用户定义的一个数据库操作序列事务的四个特性原子性A一致性C隔离性I持久性D DBMS的组成 21 客户 服务器程序体系结构浏览器 服务器体系结构 DBMS的组成 22 客户 服务器程序体系结构浏览器 服务器体系结构 1 3未来的数据库系统 第二章数据库建模DatabaseModeling 24 数据库的设计步骤 需求收集和分析设计概念结构设计逻辑结构设计物理结构物理实现 25 数据库的设计步骤 需求收集和分析用户关心什么用户要什么结果设计概念结构设计逻辑结构设计物理结构物理实现 26 数据库的设计步骤 需求收集和分析设计概念结构存什么关系 联系 如何ODL或E R图 是各种数据模型的共同基础设计逻辑结构设计物理结构物理实现 27 数据库的设计步骤 需求收集和分析设计概念结构设计逻辑结构用什么数据模型数据库的模式 databaseschema 用户子模式设计物理结构物理实现 28 数据库的设计步骤 需求收集和分析设计概念结构设计逻辑结构设计物理结构数据怎么存根据DBMS产品 环境特点物理实现 29 数据库的设计步骤 需求收集和分析设计概念结构设计逻辑结构设计物理结构物理实现运行DDL装入测试数据应用程序 30 数据库的设计步骤 想法需求 ODL E R 关系 RDBMS OODBMS 31 2 1ODL 对象定义语言ObjectDefinitionLanguage以面向对象的观点 方法 说明数据库的概念结构可方便地直接转换成OODBMS的说明经过努力 可以转换成RDBMS的说明 32 面向对象的设计 对象标识 OID对象与对象的区别类具有相同特性的对象归为一类对象的归并必须有意义属于同一类的对象其特性必须相同 33 面向对象的设计 对象的三个特性属性 特性联系 引用方法 函数接口说明interface 34 属性对象某方面的特征 属性就是数据只由基本数据类型构成属性的类型 不能是类 也不能从类中构造InterfaceMovie MovieClass的ODL说明attributestringtitle attributeintegeryear attributeintegerlength attributeenumFilm color blackAndWhite filmType 35 InterfaceStar attributestringname attributeStructAddr stringstreet stringcity address 36 联系对象的引用对象的关联对象集合的引用 1 N RelationshipSetstars 单一对象集合的引用 1 1 RelationshipStarstarOf 37 反向联系ODL要求显式表示存在的反向联系InterfaceMovie MovieClass的ODL说明attributestringtitle attributeintegeryear attributeintegerlength attributeenumFilm color blackAndWhite filmType relationshipSetstarsinverseStar starredIn Star与Movie的联系 38 联系的多重性N N在联系中 每个C都和D的集合有关 而在反向联系中 每个D都和C的集合有关N 1在联系中 每个C都和唯一的D有关 而在反向联系中 每个D都和C的集合有关1 1在联系中 每个C都和唯一的D有关 而在反向联系中 每个D都和唯一的C有关 39 InterfaceMoive relationshipSetstarsinverseStar staredIn relationshipStudioownedByinverseStudio owns InterfaceStar relationshipSetstaredIninverseMoive stars InterfaceStudio relationshipSetownsinverseMoive ownedBy 40 ODL中的类型基本类型原子类型接口类型结构类型 可由以下类型组合而成集合无重复 次序无关包可重复 次序无关列表可重复 次序相关数组结构 41 2 2实体联系图 E R 用图形的方法 描述实体及实体间的联系世界由一组称作实体的基本对象及这些对象间的联系组成元素实体 Entity 客观存在并可相互区别的事件或物体对应于ODL中的对象实体集 EntitySet 同类 具有相同类型 相同性质 实体的集合对应于ODL中的类用矩形表示 42 2 2实体联系图 E R 元素属性 Attribute 实体所具有的某一特性用与实体集相连的椭圆表示联系 Relationship 实体集之间的关联可涉及多个实体集可表示双向的联系用与相应的实体集相连的菱形表示 43 Movies Stars Stars in lenght filmType title year name address 44 E R联系的多重性N与1的表示 Movies Stars Stars in Studios Presidents Runs Movies Studios Owns 45 联系的多向性E R图能方便地描述两个以上实体集间的联系 46 联系中的角色实体集在联系中的作用参与联系的实体集互异只标注联系名同一实体集在一个联系中多次出现标注联系名及角色名 47 联系中的属性联系中可以包含属性由联系而产生的属性可为由联系产生的属性建立实体集 Stars Movies Contracts Studios salary 48 将多向联系转换成二元联系新增连接实体集引入连接实体集至原实体集的多对一的联系 49 2 3设计原则 真实性设计应当忠于规范存什么避免冗余任何事物只表达一次避免引入过多的元素选择合适的元素类型属性 类 实体集 联系集 50 2 4子类 特殊化与概括子类与超类属性的继承 51 ODL中的子类子类继承其超类的所有特性属性联系InterfaceCartoon Movie relationshipsetvoices 52 ODL中的多重继承类的层次一个类可以有多个超类InterfaceMurderMystery Movie attributestringweapon InterfaceCartoon MurderMystery Cartoon MurderMystery 53 E R中的子类IsaE R中的继承 54 2 5对约束的建模 建模包含对现实世界的对象及联系的描述 也包含对它们的一些约束键码单值约束参照完整性约束域的约束一般约束 55 键码在类的范围内唯一标识一个对象 或者在实体集的范围内唯一标识一个实体 的属性或属性集一个类中的两个对象 或一个实体集中的两个实体 在构成键码的属性集上取值不能相同ODL中键码的表示interfaceMovie key title year 56 超码一个或多个属性的集合 能在一个实体集中唯一地标识一个实体一个类 或实体集 中可能有多个超码候选码其任意真子集都不为超码的超码一个类 或实体集 中可能有多个候选码主码从候选码中选取的一个 一个类 实体集 中只有一个主码E R图中只能表示主码 主码属性名加上下划线 57 单值约束要求某个角色的值是唯一的 如键码当一个属性为单值时可以要求该属性值存在 notnull 可以允许该属性值任选 null 构成键码的属性 必须有值存在 notnull 58 参照完整性约束要求由某个对象引用的值在数据库中确实存在参照与被参照 引用与被引用参照完整性约束的操作 各产品不同 禁止删除被引用的对象级联删除 修改E R图中参照完整性的表示 59 2 6弱实体集 弱实体集的属性不足以形成主码有主码的实体集称为强实体集弱实体集只有作为一对多联系的一部分 多 才有意义弱实体集与其拥有者之间的联系是标识性联系 60 2 7关于联系集 联系集的成份参加联系的实体集的主码联系集的属性联系中属性的决策 二元联系 1 1联系集的属性 放到任意一端1 N联系集的属性 放到N端N M联系集的属性 只能留在联系集中 61 联系集的取舍 二元联系 1 1联系 将一端的主码作为另一端的属性1 N联系 将一端的主码作为N端的属性N M联系 必须保留联系集联系集的键码 二元联系 1 1联系 任意一端的主码1 N联系 N端的主码N M联系 参加联系的所有实体集的主码 62 ODL E R建模关心 存什么数据 关系如何不关心 用什么数学模型 DBMS产品透过E R图 便于与用户交流 63 作业思考所有带 的练习 并上网查阅解答练习2 1 7 2 2 8 2 3 2 2 5 3 2 5 4 2 6 4 a 第三章关系数据模型TheRelationalDataModel 65 ODL E R到关系模型的转换关系模型的设计理论 66 3 1关系模型的基本概念 逻辑数据模型是用户从数据库所看到的数据模型与DBMS有关层次 网状 关系 面向对象 67 关系数据模型数据结构两维的扁平表数据操作关系代数关系演算数据的完整性实体完整性参照完整性用户定义的完整性 68 现实世界的实体以及实体间的各种联系均用关系表示关系数据库系统是建立在关系模型上的数据库系统关系数据库是表的集合 69 模型和模式数据模型是描述数据的手段数据模式是用给定的数据模型对具体数据的描述属性元组域型值联系关系的联系是通过关联属性的值连接的 70 Student sno sname ssex sage sdept 71 关系实例关系 实体集 类关系的实例 元组的集合元组 实体 对象数据库实例 给定时刻数据库中数据的一个快照 72 3 2从ODL设计到关系设计 ODL设计是概念设计的产物 UsingOO ODL描述 关系模式 实现 73 ODL属性 关系属性原子属性类 关系属性 属性非原子属性 复杂数据类型 必须转换成原子属性记录结构结构的每个item对应一个属性多值集合针对每个值建立一个元组会产生冗余 需规范化 74 ODL属性 关系属性 续 其他类型属性 包 数组 列表 针对每个元素建立一个元组增加一个记数属性 表示包的成员号定长数组扩展为多个属性 75 ODL联系 关系描述单值联系联系的类型为一个类增加一个 组 属性 存放相关类的键码属性 组 将类之间的联系 关系之间的联系 76 ODL联系 关系描述 续 多值联系联系的类型为某个类的集合类型1 N N M增加一个键码属性为集合的每个成员建立一个元组其他原始属性重复多次 与集合成员的个数相等 导致大量的冗余 需要规范化 77 键码是必需的选择合适的属性 组 作为键码学号 工号 身份证号 增加计数属性联系与反向联系在联系的双方均有联系的描述 冗余ODL 双向描述E R 相关的键码值进行连接 78 3 3从E R图到关系的设计 E R与ODL描述的差异联系作为独立的概念 联系嵌套在类定义中结构化数据 允许使用集合 聚集类型联系可以有属性 联系无属性E R 关系模式 实现 79 实体集到关系的转换非弱实体集实体集名 关系名属性 属性弱实体集为弱实体集建立关系属性 弱实体集的属性 辅助实体集的键码 80 E R联系到关系的转换用关系表示联系联系名 关系名属性 属性 相关实体集的键码属性 集 多向联系的转换注意 属性的命名 81 3 4子类结构到关系的转换 ODL中的子类一个对象完全属于一个类子类继承其超类的特性E R中的子类分层结构通过与ISA联系有关的实体集进行扩展 82 用关系表示ODL子类每个子类都有自己的关系包含该子类的所有特性 含继承特性 在一个关系中含有所有属性Movie title year length filmType studioName starName Cartoon title year length filmType studioName starName voice MurderMystery title year length filmType studioName starName weapon Cartoon MurderMystery title year length filmType studioName starName voice weapon 83 在关系模型中表示isa联系子类的信息被分散到上层的几个关系中与ISA联系有关的实体集拥有相同的键码Movie title year length filmType Cartoon title year MurderMystery title year weapon Voice title year name 84 使用NULL值合并关系将关系描述成一个 全集 属性 所有可能的属性描述 允许Null值层次越高 取Null值的属性越多Movie title year length filmType studioName starName voice weapon 只是一种方法而已 85 作业思考所有带 的练习 并上网查询解答练习3 2 3 3 3 1 3 4 1 3 5 3 86 3 5函数依赖 数据依赖函数依赖多值依赖数据依赖是针对数据模式 而不是特定的实例 87 函数依赖 FD 属性之间的联系假设给定X属性的值 就知道Y的值 那么X函数决定Y如果R的两个元组在属性A1 A2 An上一致 则它们在另一个属性B上也一致 那么A1 A2 An函数决定B 记作A1A2 An BifA1A2 An B1thenA1A2 An B2A1A2 An B1B2 Bm A1A2 An Bm 88 关系的键码如果一个或多个属性的集合 A1A2 An 满足如下条件 则该集合为关系R的键码 1 这些属性函数决定该关系的所有其他属性2 A1A2 An 的任何真子集都不能函数决定R的所有其他属性 89 超键码包含键码的属性集称为超键码 90 寻找关系的键码 来自E R 来自实体集的关系的键码就是该实体集的键码属性对于二元联系R N M 相关两个实体的键码都是R的键码属性N 1 多端实体集的键码是R的加码属性1 1 任意一端实体集的键码是R的键码对于多向联系R 如果多向联系R有一个箭头指向实体集E 则响应的关系中 除了E的键码以外 至少还存在一个键码 91 寻找关系的键码 来自ODL 来自ODL的关系的键码不一定就是该类的键码属性对于单值联系R 类C有一个指向类D的单值联系 C的关系中包含D的键码 C的键码仍是相应关系的键码对于多值联系R 被引用类的键码加入到引用类的键码组合称为键码 92 3 6函数依赖规则 分解规则A1A2 An B1B2 Bm等价于A1A2 An Bi合并规则A1A2 An Bi等价于A1A2 An B1B2 Bm 93 平凡的函数依赖如果对于函数依赖A1A2 An B B是A中的一个 则该函数依赖就是平凡的函数依赖平凡依赖是永恒的title year title非平凡如果B中至少有一个属性不在A中title year year length完全非平凡如果B中没有一个属性在A中title year length 94 计算属性的闭包闭包F的闭包是指F逻辑蕴涵的所有函数依赖的集合 记作F 闭包的意义检验给定的函数依赖是否蕴涵于某个函数依赖集S从给定的函数依赖 可以推导出蕴涵的函数依赖求闭包例 对关系R A B C D E F 给定函数依赖AB C BC AD D E CF B 求 A B 95 关系的基与最小基任何一个能从中导出关系的所有依赖的给定依赖集 称为该关系的一个基如果一个基的任何真子集都不能推导出该关系的依赖全集 则称此基为最小基求函数依赖的最小基例 对关系R A B C D 有A BC B C A B AB C AC D 求最小函数依赖集 96 3 7关系数据库模式设计 什么是好的数据库设计体现客观世界的信息无过度的冗余无插入异常无更新复杂无删除异常 97 更新复杂 删除异常 98 异常的原因数据依赖的约束解决方法数据库设计的规范化 分解 99 范式 NormalForms 规范化一个关系满足某个范式所规定的一系列条件时 它就属于该范式可以用规范化要求来设计数据库也可以用来验证设计结果的合理性 用其指导优化过程1NF 2NF 3NF BCNF 4NF 100 第一范式 1NF 当且仅当一个关系R中 每一个元组的每一个属性只含有一个值时 该关系属于第一范式 要求属性是原子的 101 第二范式 2NF 对于关系R 若R 1NF 且每一个非主属性完全函数依赖于码 则R 2NF 不能部分依赖于码sc sno sname cno grade sno cno gradesno sname 102 第三范式 3NF 对于关系R 若R 2NF 且每个非主属性都不传递依赖于码 则R 3NF 主属性可以传递依赖于码student sno sname sdept dept manager sno sname sdeptsdept dept manager传递依赖 sno dept manager 103 Boyce Codd范式 BCNF 对于关系R 若R 1NF 且所有非平凡的函数依赖 其决定因素是候选码 则R BCNF sct s c t 学生 课程 教师t c 每位教师只上一门课 s c t s t c 每门课有若干位教师 104 最高范式BCNF是基于函数依赖的最高范式但不是数据库模式设计的最高范式 105 范式优化分解 A1 A2 An B1 B2 Bn C1 C2 Cn 分解的要求无损连接B C B或B C C保持依赖范式要求 106 分解到高层范式 基于FD 找出一个不符合范式要求的FD X Y 并使其右端的属性尽可能地多 求出X 创建新的模式 包含上述FD的所有 左右 属性 X 的所有属性 创建另一模式 包含前述FD中的决定因素及剩余的所有属性 U X X 反复1 3 直至符合范式要求 107 3 8多值依赖 BCNF仍有异常 108 多值依赖 MVD 按P 110的定义 109 多值依赖 MVD 设R U 是属性集U上的一个关系 X Y Z是U的子集 且Z U X Y 关系R U 中多值依赖X Y成立 当且仅当对R U 的任一元组r 给定一对 x z 值 有一组y的值 这组值仅仅取决于x值 而与z值无关 对给定X的值 则Y的取值与Z的取值无关 110 给定一对 x z 值 有一组y的值 111 name title year 112 平凡的多值依赖如果X包含Y或X Y U 则X Y是平凡的多值依赖性质IfX Y andY ZthenX Z YIfX Y andZ U X YthenX ZIfX YthenX Y无分解 合并规则 113 第四范式 4NF 对于关系R 若R 1NF 且所有非平凡的多值依赖 其决定因素是候选码 则R 4NF 基于数据依赖的最高范式分解要求无损连接保持依赖4NF 114 name title yearname street cityAllKeyisBCNFisn t4NF R1 name title year R2 name street city AllKeyis4NF 115 范式间的联系高层范式包含低层范式分解特性 116 作业思考所有带 的练习 并上网查询解答练习3 7 5 3 8 3 第四章关系模型中的运算 OperationsInTheRelationalModel 118 概述 数据库的核心应用是数据库的查询查询语言用户用来从数据库中请求获取信息的语言关系代数是过程化的查询语言关系代数是一种抽象的查询语言 119 4 1关系代数 运算对象 关系运算结果 关系 120 关系代数 关系代数的运算普通的集合运算并 交 差删除部分关系的运算选择 投影合并两个关系元组的运算连接 积改名运算 121 关系代数 并Union R和S的并 R S 是在R或S或两者中的元素的集合一个元素在并集中只出现一次R和S必须同类型 属性集相同 次序相同 但属性名可以不同 122 关系代数 交Intersect R和S的交 R S 是在R和S中都存在的元素的集合一个元素在交集中只出现一次R和S必须同类型 属性集相同 次序相同 但属性名可以不同 123 关系代数 差Minus R和S的差 R S 是在R中而不在S中的元素的集合R和S必须同类型 属性集相同 次序相同 但属性名可以不同 124 关系代数 投影Projection 从关系R中选择若干属性组成新的关系 A1 A2 An R 表示从R中选择属性集A1 A2 An组成新的关系列的运算投影运算的结果中 也要去除可能的重复元组例 ssex sage student 125 关系代数 选择Selection 从关系R中选择符合条件的元组构成新的关系 C R 表示从R中选择满足条件 使逻辑表达式C为真 的元组行的运算例 sage 23ANDsdep CS student 例 列出CS系的学号及姓名 sno sname sdep CS student 126 关系代数 笛卡儿积 关系R S的笛卡儿积是两个关系的元组对的集合所组成的新关系R S 属性是R和S的组合 有重复 元组是R和S所有元组的可能组合是R S的无条件连接 使任意两个关系的信息能组合在一起例 student sc 注意sno的命名 127 关系代数 自然连接 从R S的结果集中 选取在某些公共属性上具有相同值的元组 组成新的关系R S的公共属性属性集的交集 名称及类型相同 公共属性在结果中只出现一次等值连接例 studentsc 注意sno的命名 128 关系代数 条件连接 从R S的结果集中 选取在指定的属性集上满足 条件的元组 组成新的关系 是一个关于属性集的逻辑表达式RCS 129 关系代数 改名 对关系R实施改名运算 可显式地改变关系名及属性名将关系R改名为S s R 将关系R B1 B2 Bn 改名S A1 A2 An S A1 A2 An R 130 关系代数 查询中的复合运算 例 找出比 95001 年龄大的学生的学号和姓名 年龄 s sno s sname s sage s student s sage stu sage stu sno 95001 stu student 131 关系代数 查询中的复合运算 例 找出比 95001 年龄大的学生的学号和姓名 年龄 与前例的结果有何差异 s sno s sname s sage stu sno 95001 ands sage stu sage s student stu student 132 关系代数 外连接 是连接运算的扩展 可以处理缺失信息保留非匹配元组外连接左外连接右外连接全外连接 133 关系代数 左外连接 取出左侧关系中所有与右侧关系的任一元组都不匹配的元组 用空值填充所有来自右侧关系的属性 再把产生的元组加到自然连接的结果上例 列出影星的当官情况 是影星 可能不是官 MovieStarMovieExec 134 关系代数 右外连接 与左外连接对称例 列出行政长官的从影情况 是长官 可能不是影星 MovieStarMovieExec 135 关系代数 全外连接 左外连接与右外连接的合成例 列出所有行政长官和所有影星的关系MovieStarMovieExec 136 4 5对关系的约束 关系数据模型数据结构关系操作关系的完整性约束实体完整性参照完整性用户定义的完整性 137 用关系代数作为约束语言 用关系代数可以表达约束R 0 R 0R S R S 0 138 参照完整性约束 如果对象 实体 A与对象 实体 B相关 那么B必须存在如果一个关系R的一个元组有个分量值为v 那么期望v将出现在另一个关系S的某个元组的特定分量中 v R v S 139 参照完整性约束 例 Movie关系中 每部电影的制片人必须出现在MovieExec关系中Movie title year length incolor studioName producerC MoiveExec name address cert networth 每个Movie元组的producerC 分量必须出现在MovieExec元组的cert 分量中 producerC Movie cert MovieExec producerC Movie cert MovieExec 0 140 参照完整性约束 例 影星只能于存在的Movie中出演角色Movie title year length incolor studioName producerC starsIn movieTitle movieYear starName movieTitle movieYear starsIn title year Movie 141 附加约束 函数依赖 对关系student 有sno sname stu1 sno stu2 snoANDstu1 snamestu2 sname stu1 stu2 0 142 附加约束 用户定义的约束 某些属性的取值有特定的要求 gender F ANDgender M MovieStar 0 143 4 6包的关系运算 集合无重复顺序无关包可重复顺序无关商业数据库系统往往是基于包的采用包可以获得比集合快的运算速度 144 包的关系运算 并 交 差投影 选择乘积 连接不必去除重复元组 145 作业 练习4 1 1 4 5 1 4 5 4 4 5 5 第五章数据库语言SQL 147 概述 SQL的发展1974年 由Boyce和Chamberlin提出1975 1979 IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言SQL 86是第一个SQL标准SQL 89 SQL 92 SQL2 SQL 99 SQL3 148 概述 现状大部分DBMS产品都支持SQL 成为操作数据库的标准语言有方言 支持程度不同 149 数据定义 DDL 定义 删除 修改关系模式 基本表 定义 删除视图 View 定义 删除索引 Index 数据操纵 DML 数据查询数据增 删 改数据控制 DCL 用户访问权限的授予 收回 概述 SQL的功能 150 交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释 概述 SQL的形式 151 嵌入式SQL能将SQL语句嵌入到高级语言 宿主语言 使应用程序充分利用SQL访问数据库的能力 宿主语言的过程处理能力一般需要预编译 将嵌入的SQL语句转化为宿主语言编译器能处理的语句 概述 SQL的形式 152 数据定义Create Drop Alter数据操纵数据查询 Select数据修改 Insert Update Delete数据控制Grant Revoke 概述 SQL的动词 153 数据查询是数据库应用的核心功能基本结构SelectA1 A2 AnFromr1 r2 rmWhereP A1 A2 An p r1 r1 rm 数据查询 154 Select语句的含义 对From子句中的各关系 作笛卡儿积 对Where子句中的逻辑表达式进行选择 运算 找出符合条件的元组根据Select子句中的属性列表 对上述结果作投影 操作 155 Select语句的含义 结果集查询操作的对象是关系 结果还是一个关系 是一个结果集 是一个动态数据集 156 Select子句 对应于关系代数的投影 运算 用以列出查询结果集中的期望属性 157 SQL具有包的特性Select子句的缺省情况是保留重复元组 ALL 可用Distinct去除重复元组SelectDistinctsdeptFromStudentSelectAllsdeptFromStudent去除重复元组 费时需要临时表的支持 Select子句 重复元组 158 Select子句 与属性列表 星号 表示所有属性星号 按关系模式中属性的顺序排列 并具有一定的逻辑数据独立性显式列出属性名 按用户顺序排列Select FromStudentSelectStudent cno gradeFromStudent SCWhereStudent sno SC sno 159 Select子句 更名 为结果集中的某个属性改名使结果集更具可读性Selectsnoasstu no cnoascourse no gradeFromSCSelectsno sname 2001 sageasbirthdayFromStudent 160 Where子句 where子句对应与关系代数中的选择 查询满足指定条件的元组可以通过Where子句来实现使where子句中的逻辑表达式返回True值的元组 是符合要求的元组 将被选择出来 161 Where子句 运算符 比较 not 确定范围 BetweenAandB NotBetweenAandB确定集合 IN NOTIN字符匹配 LIKE NOTLIKE空值 ISNULL ISNOTNULL多重条件 AND OR NOT 162 Where子句 Like 字符匹配 Like NotLike通配符 匹配任意字符串 匹配任意一个字符大小写敏感 163 Where子句 Like 例 列出姓张的学生的学号 姓名 Selectsno snameFromStudentWheresnameLIKE 张 164 Where子句 Like 例 列出张姓且单名的学生的学号 姓名 Selectsno snameFromStudentWheresnameLIKE 张 165 Where子句 转义符escape 例 列出课程名称中带有 的课号及课名 Selectcno cnameFromCourseWherecnameLIKE escape 166 From子句 From子句对应与关系代数中的笛卡儿积 列出将被扫描的关系 表 例 列出所有学生的学号 姓名 课号 成绩 SelectSudent sno sname SC cno gradeFromStudent SCWhereStudent sno SC sno 167 From子句 元组变量 为From子句中的关系定义元组变量方便关系名的引用在同一关系的笛卡儿积中进行辨别例 列出与95001同岁的同学的学号 姓名 年龄SelectT sno T sname T sageFromStudentasT StudentasSWhereS sno 95001 ANDT sage S sage 168 OrderBy子句 指定结果集中元组的排列次序耗时ASC 缺省 DESC 未选中的属性例 列出CS系中的男生的学号 姓名 年龄 并按年龄进行排列 升序 Selectsno sname sageFromStudentWheresdept CS OrderBysageASC 169 SQL的集合操作 SQL的结果集是 包 多个Select语句的结果可以进行集合操作 使结果为 集合 default SQL 92支持参加集合操作的关系 结果集 必须是相容的 170 SQL的集合操作 相容 属性个数必须一致对应的类型必须一致属性名无关最终结果集采用第一个结果的属性名缺省为自动去除重复元组除非显式说明ALLOrderBy放在整个语句的最后 171 SQL的集合操作 并 union 并 对应与关系代数的 标准SQL都支持的采用集合的观点 合成多个查询的结果select without orderby UNION ALL select without orderby UNION ALL select without orderby ORDERBYinteger ASC DESC 172 例 查询计算机系的学生或者年龄不大于19岁的学生 并按年龄倒排序 SQL的集合操作 并 173 intersect 交 对应与关系代数的 并不是所有的DBMS都支持例 查询计算机系的学生并且年龄不大于19岁的学生 并按年龄倒排序 SQL的集合操作 交 174 except 差 对应与关系代数的 并不是所有的DBMS都支持例 查询计算机系的男生 SQL的集合操作 差 175 数据定义语言 DataDefinitionLanguage Create Drop Alter定义一组关系 基本表 说明各关系的信息各关系的模式各属性的值域完整性约束索引安全性和权限 数据定义语言 DDL 176 字符型char n varchar n 数值型integer smallintnumeric p d real double float n 日期 时间型datetime DDL SQL中的域类型 177 域定义语句 SQL 92支持 需重复使用的CreateDomainstu namevarchar 20 CreateDomainzip codechar 6 DDL SQL中的域类型 178 CreateTabler A1D1 A2D2 AnDn 其中 r关系名 表名 Ai关系r的一个属性名Dn属性Ai域值的域类型主键声明 primarykey Aj1 Aj2 Ajvm DDL SQL的模式定义 179 CreateDomainstu namevarchar 20 CreateTableStudent snochar 10 primarykey sno snamestu name sagesmallint ssexchar 1 sdeptchar 2 DDL SQL的模式定义 180 CreateTableCourse cnochar 10 primarykey cno cnamevarchar 20 creditsmallint CreateTableSC snochar 10 notnull cnochar 10 notnull gradesmallint primarykey sno cno DDL SQL的模式定义 181 用SQL删除关系 表 将整个关系模式 表结构 彻底删除表中的数据也将被删除DropTablerDropTablestudent DDL 删除表结构Drop 182 删除表中的某属性去除属性及相应的数据AlterTablerDropA DDL 修改模式Alter 183 增加表中的属性向已经存在的表中添加属性allownull已有的元组中该属性的值被置为NullAlterTablerAddADAlterTablestudentphonechar 16 DDL 修改模式Alter 184 属性的默认值用户不提供某属性的值时 默认值被使用初始值TimeStampDDL中 ModifyDatechar 30 DefaultTimeStamp AlterTablestudentAddIDintegerDefaultAutoIncrement DDL DefaultValue 185 定义一个新的域 用户定义的域 需要重复使用的域必须具有相同类型的属性CreateDomainAs CreateDomaindatelogAschar 30 defaulttimestamp域的删除DropDomaindatelog各DBMS的方法是不同的Restrict Cascade使用该域的属性的处理 DDL 域定义 186 索引是一种数据结构 是对照表 指针表索引是为了加速对表中元组的检索而创建的一种分散存储结构 B树 索引是对表而建立的 由除存放表的数据页面以外的索引页面组成索引是把双刃剑 减慢更新的速度索引不是SQL标准的要求 DDL 索引Index 187 索引的种类聚簇索引 ClusteredIndex 非聚簇索引 Non ClusteredIndex DDL 索引Index 188 聚簇索引 ClusteredIndex 表中的元组按聚簇索引的顺序物理地存放根级页面 中间层页面 叶级页面 数据页面 一个表中只能有一个聚簇索引更新的复杂性 需要大量的临时空间 DDL 索引Index 189 非聚簇索引 Non ClusteredIndex 表中元组存储的物理顺序与索引的顺序无关叶级索引页面是指向数据页面的指针每个表可有多个非聚簇索引 DDL 索引Index 190 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEXindex nameOnTableName Column Column CreateIndexYearIndexOnMovie year CreateClusteredIndexSnoIndexOnstudent sno DropIndexYearIndex DDL 索引Index 191 查询与更新的评估查询多 更新多 索引的覆盖Where表达式Where表达式的顺序索引越多越好吗 了解优化器 DDL 索引的使用 192 数据添加用SQL的插入语句 向数据库表中添加数据按关系模式的属性顺序InsertIntoStudentValues 95001 张三 27 M CS 按指定的属性顺序 也可以只添加部分属性 非Null属性为必需 InsertIntoStudent sno sname sage Values 95002 李四 26 193 把一列中的值进行聚合运算 返回单值的函数五个预定义的聚合函数平均值 Avg总和 Sum最小值 Min最大值 Max计数 CountCount Count Distinct 聚合函数 194 GroupBy将查询结果集按某一列或多列的值分组 值相等的为一组 一个分组以一个元组的形式出现只有出现在GroupBy子句中的属性 才可出现在Select子句中例 统计各系学生的人数 Selectsdept count asstu countFromStudentGroupBysdept 聚合函数 195 Having针对聚合函数的结果值进行筛选 选择 它作用于分组计算结果集跟在GroupBy子句的后面 没有GroupBy则针对全表例 列出具有两门 含 以上不及格的学生的学号 不及格的课目数 Selectsno count sno FromSCWheregrade 2 聚合函数 196 Having与Where的区别Where决定哪些元组被选择参加运算 作用于关系中的元组Having决定哪些分组符合要求 作用于分组聚合函数的条件关系必须用Having Where中不应出现聚合函数 聚合函数 197 聚合函数忽略NullCount 不计Sum 不将其计入Avg 具有Null的元组不参与Max Min 不参与例 Selectcount sdept FromStudentSelectAvg sage FromStudent 聚合函数 Null 198 子查询是嵌套在另一查询中的Select From Where表达式 Where Having SQL允许多层嵌套 由内而外地进行分析 子查询的结果作为父查询的查找条件可以用多个简单查询来构成复杂查询 以增强SQL的查询能力子查询中不使用OrderBy子句 OrderBy子句只能对最终查询结果进行排序 子查询 Subquery 199 返回单值的子查询 只返回一行一列父查询与单值子查询之间用比较运算符进行连接运算符 例 找出与95001同龄的学生Select FromStudentWheresage SelectsageFromStudentWheresno 95001 子查询 单值比较 200 子查询返回多行一列运算符 In All Some Any Exists 子查询 多值 201 标量值与子查询返回集中的某一个相等 trueIN被用来测试多值中的成员例 查询选修 C01 课程的学生的学号 姓名 Selectsno snameFromStudentWheresnoIN SelectsnoFromSCWherecno C01 子查询 多值成员In 202 例 查询选修了 数据库 的学生的学号和姓名Selectsno snameFromStudentWheresnoIN SelectsnoFromSCWherecnoIN SelectcnoFromCourseWherecname 数据库 子查询 多值成员In 203 多值比较 多行一列父查询与多值子查询之间的比较需用All来连接标量值s比子查询返回集R中的每个都大时 s AllR为TrueAll表示所有 all all allall等价于notin例 找出年龄最小的学生Select FromStudentWheresage all SelectsageF