数据库原理与设计方法.ppt

数据库原理与设计方法 参考书 1 王能斌 数据库系统 电子工业出版社 1995年 2 王能斌编 数据库系统原理 电子工业出版社 2000年 3 王珊陈红 数据库系统原理教程 4 美 j d 厄尔曼 数据库系统原理 课程考核 第一章introduction1 1concepts1 1 1data database databasesystem databasemanagementsystem1 data 李明 男 1972 江苏 计算机系 1990 数据 信息 知识三者之间的关系 数据的语义即为信息 信息在计算机中的存储 表示形式 即为数据 从信息中提升 推理 推导出的新的信息即为知识 例如 40 数据 40 信息 发烧 知识 2 database db3 databasemanagementsystem dbms4 databasesystem dbs数据库管理员 databaseadministrator 简称dba 5 datamodel数据模型是用来描述数据的一组概念和定义 一般来说 数据的描述包括两个方面 1 数据的静态特性它包括数据的基本结构 数据间的联系和数据中的约束 2 数据的动态特性它指定义在数据上的操作 如文件系统 数据模型要面向现实世界 面向用户 数据模型要面向实现 面向计算机 1 conceptualdatamodel如er模型 面向对象数据模型等 2 logicaldatamodel如关系数据模型 层次模型 网状模型等 3 physicaldatamodel概念数据模型只用于数据库的设计 逻辑数据模型和物理数据模型用于dbms的实现 6 dataschematype 型是该数据所属数据类型的说明 value 值是型的一个实例 instance或occurrence 对某一类数据的结构 联系和约束的描述是型的描述 型的描述称为数据模式 dataschema 在同一数据模式下 可以有很多的值 即实例 例如 学生记录可以定义为图1 3 a 的形式 这是数据模式 而图1 3 b 是其一个实例 数据模型是描述数据的手段 而数据模式是用给定数据模型对具体数据的描述 美国国家标准协会 ansi 的ansi x3 sparc报告把数据模式分为三级 见图1 4 1 conceptualschema logicalschema2 externalschema3 internalschema7 databaseinstance数据模式是相对稳定的 而实例是相对变动的 数据模式反映一个单位的各种事物的结构 属性 联系和约束 实质上是用数据模型对一个单位的模拟 而实例反映数据库的某一时刻的状态 也就是这一单位在此时的状态 1 1 2数据库技术的产生与发展1 人工管理阶段人工管理数据具有如下特点 1 数据不保存 2 数据需要由应用程序自己管理 没有相应的软件系统负责数据的管理工作 3 数据不共享 4 数据不具有独立性 人工管理阶段应用程序与数据之间的对应关系可用图l 3表示 2 文件系统阶段用文件系统管理数据具有如下特点 1 数据可以长期保存 2 由专门的软件即文件系统进行数据管理 3 数据共享性差 4 数据独立性低 文件系统阶段应用程序与数据之间的关系如图1 4所示 3 数据库系统阶段用数据库系统来管理数据具有如下特点 1 数据结构化学生人事记录 图1 5 2 数据的共享性好 冗余度低3 数据独立性高4 数据由dbms统一管理和控制l数据的安全性 security l数据的完整性 integrity l并发 concurrency 控制l数据库恢复 recovery 量大持久共享 1 1 3数据库技术的研究领域1 数据库管理系统软件的研制2 数据库设计3 数据库理论1 2数据库工程与应用1 2 1数据库设计的目标与特点 图1 10 1 2 2数据库设计方法新奥尔良方法 需求分析 分析用户要求 概念设计 信息分析和定义 逻辑设计 设计实现 和物理设计 物理数据库设计 s b yao 需求分析 模式构成 模式汇总 模式重构 模式分析和物理数据库设计 i r palmer则主张把数据库设计当成一步接一步的过程 并采用一些辅助手段实现每一过程 此外 基于e r模型的数据库设计方法 基于3nf 第三范式 的设计方法 基于抽象语法规范的设计方法等 规范设计法在具体使用中又可以分为两类 手工设计和计算机辅助数据库设计 oracledesigner20001 2 3数据库设计步骤 1 需求分析2 概念结构设计3 逻辑结构设计 图1 11 4 数据库物理设计5 数据库实施6 数据库运行和维护 在数据库设计过程中必须注意以下问题 1 数据库设计过程中要注意充分调动用户的积极性 2 应用环境的改变 新技术的出现等都会导致应用需求的变化 因此设计人员在设计数据库时必须充分考虑到系统的可扩充性 使设计易于变动 3 系统的可扩充性最终都是有一定限度的 1 2 4databaseapplication 各种用户的数据视图 dba主要职责包括 1 设计与定义数据库系统2 帮助最终用户使用数据库系统3 监督与控制数据库系统的使用和运行4 改进和重组数据库系统 调优数据库系统的性能5 转储与恢复数据库6 重构数据库 第二章datamodel数据模型应满足三方面要求 一是能比较真实地模拟现实世界 二是容易为人所理解 三是便于在计算机上实现 两类 概念模型也称信息模型 数据模型包括网状模型 层次模型 关系模型 2 1数据模型的要素2 1 1数据结构2 1 2数据操作2 1 3数据的约束条件 2 2概念模型 e rdatamodel 2 2 1conceptse r数据模型 entity relationshipdatamodel eer数据模型 extendedentity relationshipdatamodel 1 实体 entity 实体集 entityset entityset与entity是型 type 与值 value 的关系 类似于前述dataschema与databaseinstance 2 属性 attribute 值集 valueset 实体键 entitykey 实体主键 entityprimarykey 3 联系 relationship 基数比约束 cardinalityratioconstraint 参与约束 participationconstraint 部分参与 全参与结构约束 structuralconstraint 两个实体之间的联系可以分为三类 l一对一联系 1 1 l一对多联系 1 m l多对多联系 m n 所有 ownership 关系 弱实体 weakentity 2 2 2e rdiagram用e r数据模型对某一单位进行模拟 可以得到er数据模式 er数据模式可以er图来直观地表示 entity weakentity relationship attribute 示例 说明 1 学位类型 硕士 博士2 导师类型 主要指导老师 协助指导3 研究生可能换导师 换专业 方向4 选课性质 学位课 非学位课5 任课类型 主讲 辅讲6 可担任描述有哪些老师可以上哪些课7 任课是指目前该课程的任课老师8 开课学期 春 秋季9 上课地点 目前该课程的上课教室 问题 1 课性质属性为什么不属于课程实体 而属于选课联系 2 专业 方向可不可以属于研究生 2 2 3eerdatamodel1 特殊化 specialization 和普遍化 generalization 全特殊化 totalspecialization 部分特殊化 partialspecialization 不相交特殊化 disjointspecialization 重叠特殊化 overlappingspecialization 2 聚集 aggregation 3 范畴 category 2 3hierarchydatamodel2 3 1层次数据模型的数据结构1 层次模型的基本结构 图ts数据模式 图ts数据模式的一个值 2 多对多联系在层次模型中的表示2 3 2层次数据模型的操纵与完整性约束2 3 3层次数据模型的存储结构2 3 4层次数据模型的优缺点层次数据模型的优点主要有 l层次数据模型本身比较简单 只需很少几条命令就能操纵数据库 比较容易使用 l对于实体间联系是固定的 且预先定义好的应用系统 采用层次模型来实现 其性能优于关系模型 不次于网状模型 l层次数据模型提供了良好的完整性支持 层次数据模型的缺点主要有 l现实世界中很多联系是非层次性的 如多对多联系 一个结点具有多个双亲等 层次模型表示这类联系的方法很笨拙 只能通过引入冗余数据 易产生不一致性 或创建非自然的数据组织 引入虚拟结点 来解决 l对插入和删除操作的限制比较多 l查询子女结点必须通过双亲结点 l由于结构严密 层次命令趋于程序化 2 4网状数据模型2 4 1网状数据模型的数据结构 2 4 2网状数据模型的操纵与完整性约束2 4 3网状数据模型的存储结构2 4 4网状数据模型的优缺点 网状数据模型的优点主要有 l能够更为直接地描述现实世界 如一个结点可以有多个双亲 允许结点之间为多对多的联系等 l具有良好的性能 存取效率较高 网状数据模型的缺点主要有 l其ddl语言极其复杂 l数据独立性较差 由于实体问的联系本质上是通过存取路径指示的 因此应用程序在访问数据时要指定存取路径 2 5relationdatamodel2 5 1concepts1 attributeanddomaindomain 第一范式1nf firstnomalform atomicdata非第一范式 non firstnomalform nf2空值 null2 relationandtuple设有一命名为r的关系 它有属性a1 a2 an 其对应的城分别为dl d2 dn则关系r可表示为 r d1 al d2 a2 dn an 或r a1 a2 an 或r a1a2 an r a1表示关系r的属性a1 degree arity nr的值 rr r r t1 t2 tm t vi di 1 i n笛卡尔乘积 aba b 关系模式 sudent 姓名 学号 性别 出生年份 籍贯 系别 入学年份 投影 r x t x student 姓名 性别 3 key定义 如果关系的某一属性或属性组的值唯一地决定其他所有属性的值 也就是唯一地决定一个元组 而其任何真子集无此性质 则这个属性或属性组称为该关系的候选键 candidatekey 或简称为键 superkeyprimarykeyalternatekeyallkey supply 供应商 零件名 工程名 primeattributenon primeattributeforeignkeycourse 课程名 课程号 学分 开课时间 先修课程号 grade 学号 课程号 成绩 2 5 2constraintr d1 al d2 a2 dn an 1 domainintegrityconstraint2 entityintegrityconstraint3 referentialintegrityconstraint4 generalintegrityconstraint 2 5 3operationrelationalalgebraoperations1 selectoperation 2 projectoperation 性别 籍贯 出生年份 student 若包含则 r r 姓名 性别 女 student 3 setoperationa b a a b unioncompatibility 课程号 course 先修课程号 course 系别 计算机系 student 系别 电子系 student r s t randg s 4joinoperationrs r s 连接条件 andand and 连接 ai bj 等连接 equijoin 自然连接 naturaljoin 例 gradegrade 课程号 course 课程号 课程名 课程号 学分 course 关系代数操作集 是完备的操作集 relationallycomplete5 outerjoinoperation6 outerunionoperation 2 5 4relationalcalculus1 tuplerelationalcalculus2 domainrelationalcalculus 第三章databaselanguagesql结构化查询语言 structuredquerylanguage 简称sql 3 1introductionsql语言是1974年由boyce和chamberlin提出的 1975年至1979年ibmsystemr实现了这种语言 1986年10月美国国家标准局 简称ansi sql 861987年国际标准化组织 简称iso 也通过了这一标准 ansi1989年第二次公布sql标准 sql 89 1992年sql 92标准 目前ansi正在酝酿新的sql标准 sql3 现在sql已被重新解释成为 standardquerylanguagesql按其功能可分为四大部分 1 数据定义语言 datadefinitionlanguage 简称ddl 2 查询语言 querylanguage 简称ql 3 数据操纵语言 datamanipulationlanguage 简称dml 4 数据控制语言 datacontrollanguage 简称dcl 3 1 1sql的特点1 综合统一2 高度非过程化3 面向集合的操作方式4 以同一种语法结构提供两种使用方式5 语言简洁 易学易用表3 1sql语言的动词 3 1 2sql语言的基本概念 3 2数据定义表3 2sql的数据定义语句 3 2 1定义 删除与修改基表1 定义基表 createtable 表名 列名 数据类型 列级完整件约束条件 列名 数据类型 列级完整性约束条件 表级完整性约束条件 列级完整性约束条件格式 notnull unique default字值 user null 表级完整性约束条件有三个任选项 用于定义主键的primarykey子句 用于定义外键的foreignkey子句和用于定义列值限制条件的check子句 格式 primarykey foreignkey 外键名 references ondeleterestrict cascade setnull check 条件 ibmdb2sql主要支持以下数据类型 smallint半字长二进制整数 integer或int全字长二进制整数 decimal p q 或dec p q 压缩十进制数 共p位 其中小数点后有q位 0 q p 15 q 0时可以省略 float双字长浮点数 charter n 或char n 长度为n的定长字符串 varchar n 最大长度为n的变长字符串 graphic n 长度为n的定长图形字符串 vargraphic n 最大长度为n的变长图形字符串 date日期型 格式为yyyy mm dd time时间型 格式为hh mm ss timestamp日期加时间 例1建立student 学生 course 课程 sc 选课 表 1 学生 表student由学号 sno 姓名 sname 性别 ssex 年龄 sage 所在系 sdept 5个属性组成 可记为student sno sname ssex sage sdept 其中sno为主键 2 课程 表course由课程号 cno 课程名 cname 先修课号 cpno 学分 ccredit 4个属性组成 可记为 course cno cname cpno ccredit 其中cno为主键 3 学生选课 表sc由学号 sno 课程号 cno 成绩 grade 3个属性组成 其中 sno cno 为主键 createtablestudent snochar 5 notnullunique snamevarchar 20 notnull ssexchar 1 sageint sdeptchar 15 primarykey sno createtablecourse cnochar 1 notnull cnamevarchar 20 cpnochar 1 ccreditdec 2 1 primarykey cno foreignkey cpno referencescourseondeleterestrict createtablesc snochar 5 notnull cnochar 1 notnull gradedec 4 1 defaultnull primarykey sno cno foreignkey sno referencesstudentondeletecascade foreignkey cno referencescourseondeleterestrict 2 修改基表altertable 表名 add 新列名 数据类型 完整性约束 drop 完整性约束名 modify 列名 数据类型 例2向student表增加 入学时间 列 其数据类型为日期型 altertablestudentaddscomedate 例3将年龄的数据类型改为半字长整数 altertablestudentmodifysagesmallint 例4删除 撤消 student表主键定义 altertablestudentdropprimarykey 3 删除基表droptable 表名 例5删除student表 droptablestudent 3 2 2建立与删除索引1 建立索引create unique cluster indexon 次序 次序 排列次序 包括asc 升序 和desc 降序 两种 缺省值为asc createclusterindexstusnameonstudent sname 例6为学生 课程数据库中的student course sc3个表建立索引 其中student表按学号升序建立唯一索引 course表按课程号升序建立唯一索引 sc表按学号升序和课程号降序建唯一索引 createuniqueindexstusnoonstudent sno createuniqueindexcoucnooncourse cno createuniqueindexscnoonsc snoasc cnodesc 2 删除索引dropindex 索引名 例7删除student表的stusname索引 dropindexstusname 3 3查询select all distinct 目标列表达式 目标列表达式 from 表名或视图名 表名或视图名 where 条件表达式 groupby 列名1 having 条件表达式 orderby 列名2 asc desc 3 3 1单表查询1 选择表中的若干列1 查询指定列例1查询全体学生的学号与姓名 selectsno snamefromstudent 例2查询全体学生的姓名 学号 所在系 selectsname sno sdeptfromstudent 2 查询全部列例3查询全体学生的详细记录select fromstudent 3 查询经过计算的值例4查询全体学生的姓名及其出生年份 selectsname 2004 sagefromstudent 例5查询全体学生的姓名 出生年份和所在系 要求用小写字母表示所在系名selectsname yearofbirth 2004 sage islower sdept fromstudent selectsnamename yearofbirth birth 2004 sagcbirthday islower sdept departmentfromstudent 结果为 namebirthbirthdaydepartment李勇yearofbirth 1976cs刘晨yearofbirth 1977if王名yearofbirth 1978ma张立yearofbirth 1978if2 选择表中的若干元组1 消除取值重复的行例6查询所有选修过课的学生的学号 selectsnofromsc 假设sc表中有下列数据 snocnograde9500119295001285950013889500229095002380执行上面的select语句后 结果为 sno9500195001950019500295002 selectdistinctsnofromsc 执行结果为 sno9500195002selectsnofromsc 与selectallsnofromsc 完全等价 2 查询满足条件的元组表3 5常用的查询条件 比较大小 等于 大于 小于 大于等于 小于等于 或 不等于有些产品中还包括 不大于 不小于逻辑运算符not可与比较运算符同用 对条件求非 例7查计算机系全体学生的名单 selectsnamefromstudentwheresdept cs 例8查所有年龄在20岁以下的学生姓名及其年龄 selectsname sagefromstudentwheresage 20 或selectsname sagefromstudentwherenotsage 20 例9查考试成绩有不及格的学生的学号 selectdistinctsnofromscwheregrade 60 确定范围谓词between and 和notbetween and 可以用来查找属性值在 或不在 指定范围内的元组 其中between后是范围的下限 即低值 and后是范围的上限 即高值 例10查询年龄在20至23岁之间的学生的姓名 系别和年龄 selectsname sdept sagefromstudentwheresagebetween20and23 与between and 相对的谓词是notbetween and 例11查询年龄不在20至23岁之间的学生姓名 系别和年龄 selectsname sdept sagefromstudentwheresagenotbetween20and23 确定集合谓词in可以用来查找属性值属于指定集合的元组 例12查信息系 is 数学系 ma 和计算机科学系 cs 的学生的姓名和性别 selectsname ssexfromstudentwheresdeptin is ma cs 与in相对的谓词是notin 用于查找属性值不属于指定集合的元组 例13查既不是信息系 数学系 也不是计算机科学系的学生的姓名和性别 selectsname ssexfromstudentwheresdeptnotin is ma cs 字符匹配谓词like可以用来进行字符串的匹配 其一般语法格式如下 not like 匹配串 其含义是查找指定的属性列值与 匹配串 相匹配的元组 匹配串 可以是一个完整的字符串 也可以含有通配符 和 其中 百分号 代表任意长度 长度可以为0 的字符串 例如a b表示以a开头 以b结尾的任意长度的字符串 acb adefb ab等都满足该匹配串 下划线 代表任意单个宁符 例如a b表示以a开头 以b结尾 长度为3的字符串 acb adb等都满足该匹配串 例14查询学号为95001的学生的详细情况select fromstudentwheresnolike 9500l 该语句实际上与下面的语句完全等价 select fromstudentwheresno 9500l 例15查所有姓刘的学生的姓名 学号和性别 selectsname sno ssexfromstudentwheresnamelike 刘 例16查姓 欧阳 且全名为3个汉字的学生的姓名 selectsnamefromstudentwheresnamelike 欧阳 例17查名字中第二字为 阳 字的学生的姓名和学号 selectsname snofromstudentwheresnamelike 阳 例18查所有不姓刘的学生姓名 selectsnamc sno ssexfromstudentwheresnamenotlike 刘 涉及空值的查询谓词isnull和isnotnull可用来查询空值和非空值 例19某些学生选修某门课程后没有参加考试 所以有选课记录 但没有考试成绩 下面来查一下缺少成绩的学生的学号和相应的课程号 selectsno cnofromscwheregradeisnull 例20查所有有成绩的记录的学生学号和课程号 selectsno cnofromscwheregradeisnotnull 多重条件查询例21查cs系年龄在20岁以下的学生姓名selectsnamefromstudentwheresdept cs andsage 20 例12中的in谓词实际上是多个or运算符的缩写 因此 例l2中的查询也可以用or运算符写成如下等价形式 selectsname ssexfromstudentwheresdept is orsdept ma orsdept cs 3 对查询结果排序例22查询选修了3号课程的学生的学号及其成绩 查询结果按分数的降序排列 selectsno gradefromscwherecno 3 orderbygradedesc 例23查询全体学生情况 查询结果按所在系升序排列 对同一系中的学生按年龄降序排列 select fromstudentorderbysdept sagedesc 4 使用集函数count distinct all 统计元组个数count distinct all 列名 统计一列中值的个数sum distinct all 列名 计算一列值的总和 此列必须是数值型 avg distinct all 列名 计算一列值的平均值 此列必须是数值型 max distinct all 列名 求一列值中的最大值min distinct all 列名 求一列值中的最小值 例24查询学生总人数 selectcount fromstudent 例25查询选修了课程的学生人数 selectcount distinctsno fromsc 例26计算1号课程的学生平均成绩 selectavg grade fromscwherecno l 例27查询学习l号课程的学生最高分数 selectmax grade fromscwherecno 1 5 对查询结果分组例28查询各个课程号与相应的选课人数 selectcno count sno fromscgroupbycno 例29查询信息系选修了3门以上课程的学生的学号 为简单起见 假设sc表中有一列dept 它记录了学生所在系 selectsnofromscwheredept is groupbysnohavingcount 3 3 3 2连接查询1 等值与非等值连接查询 表名1 列名1 比较运算符 表名2 列名2 其中比较运算符主要有 此外 连接谓词还可以使用下面形式 表名1 列名1 between 表名2 当连接运算符为 时 称为等值连接 使用其它运算符称为非等值连接 例30查询每个学生及其选修课程的情况 selectstudent sc fromstudent scwherestudent sno sc sno 例31student表和sc表的笛卡尔积 selectstudent sc fromstudent sc例32自然连接student表和sc表 selectstudent sno sname ssex sage sdept cno gradefromstudent scwherestudent sno sc sno 或selectstudent cno gradefromstudent scwherestudent sno sc sno 2 自身连接例33查询每一门课的间接先修课 即先修课的先修课 selectfirst cno second cpnofromcoursefirst coursesecondwherefirst cpno second cno cnocpno1735563 外连接例34selectstudent sno sname ssex sage sdept cno gradefromstudent scwherestudent sno sc sno student sno sname ssex sage sdept cno grade9500l李勇男20cs1929500l李勇男20cs2859500l李勇男20cs38895002刘晨女19is29095002刘晨女19is38095003王名女18ma95004张立男18is4 复合条件连接例35查询选修2号课程且成绩在90分以上的所有学生 selectstudent sno snamefromstudent scwherestudent sno sc snoandsc cno 2 andsc grade 90 结果表为 student snosname95002刘晨例36查询每个学生选修的课程名及其成绩 selectstudent sno sname cname gradefromstudent sc coursewherestudent sno sc snoandsc cno course cno 3 3 3嵌套查询selectsnamefromstudentwheresnoin selectsnofromscwherecno 2 1 带有in谓词的子查询例37查询与 刘晨 在同一个系学习的学生 查询与 刘晨 在同一个系学习的学生 可以首先确定 刘晨 所在系名 然后再查找所有在该系学习的学生 所以可以分步来完成此查询 确定 刘晨 所在系名selectsdeptfromstudentwheresname 刘晨 结果为 is 查找所有在is系学习的学生 selectsno sname sdeptfromstudentwheresdept is 分步写查询毕竟比较麻烦 上述查询实际上可以用子查询来实现 即将第一步查询嵌入到第二步查询中 用以构造第二步查询的条件 sql语句如下 selectsno sname sdeptfromstudentwheresdeptin selectsdeptfromstudentwheresname 刘晨 本例中的查询也可以用前面学过的表的自身连接查询来完成 selects1 sno s1 sname s1 sdeptfromstudents1 students2wheres1 sdept s2 sdeptands2 sname 刘晨 本例中父查询和子查询均引用了student表 也可以像表的自身连接查询那样用别名将父查询中的student表与子查询中的student表区分开 selects1 sno s1 sname s1 sdeptfromstudents1wheres1 sdeptin selects2 sdeptfromstudents2wheres2 sname 刘晨 例38查询选修了课程名为信息系统的学生学号和姓名 完成此查询的基本思路是 首先在course关系中找出 信息系统 课程的课程号cno 然后在sc关系中找出cno等于第一步给出的cno集合中某个元素的sno 最后在student关系中选出sno等于第二步中求出sno集合中某个元素的元组 取出sno和sname送入结果表列 将上述想法写成sql语句就是 selectsno snamefromstudentwheresnoin selectsnofromscwherecnoin selectcnofromcoursewherecname 信息系统 dbms按照由内向外的原则求解此sql语句 首先处理最内层查询块 即课程名 信息系统 的课程号 selectcnofromcoursewherecname 信息系统 查询结果为3 从而可以把上面的sql语句简化为 selectsno snamefromstudentwheresnoin selectsnofromscwherecnoin 3 对此sql语句再处理内层查询 selectsnofromscwherecnoin 3 结果为95001和95002 从而可以把上面的sql语句进一步简化为 selectsno snamefromstudentwheresnoin 95001 95002 这样就可以求得最终结果 本查询同样可以用连接查询实现 selectstudent sno snamefromstudent sc coursewherestudent sno sc snoandsc cno course cnoandcourse cname 信息系统 2 带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接 当用户能确切知道内层查询返回的是单值时 可以用 或 等比较运算符 例如 在例37中 由于一个学生只可能在一个系学习 也就是说内查询刘晨所在系的结果是一个唯一值 因此该查询也可以用比较运算符来实现 其sql语句如下 selects1 sno s1 sname s1 sdeptfromstudents1wheres1 sdept selects2 sdeptfromstudents2wheres2 sname 刘晨 需要注意的是 子查询一定要跟在比较符之后 下列写法是错误的 selects1 sno s1 sname s1 sdeptfromstudents1where selects2 sdeptfromstudents2wheres2 sname 刘晨 s1 sdept 例38中信息系统的课程号是唯一的 但选修该课程的学生并不止一个 所以例38也可以用 运算符和in谓词共同完成 selectsno snamefromstudentwheresnoin selectsnofromscwherecno selectcnofromcoursewherecname 信息系统 3 带有any或all谓词的子查询 any大于子查询结果中的某个值 any小于子查询结果中的某个值 any大于等于子查询结果中的某个值 any小于等于子查询结果中的某个值 any等于子查询结果中的某个值 any或 any不等于子查询结果中的某个值 all大于子查询结果中的所有值 all小于子查询结果中的所有值 all大于等于子查询结果中的所有值 all小于等于子查询结果中的所有值 all等于子查询结果中的所有值 通常没有实际意义 all或 all不等于子查询结果中的任何一个值例39查询其他系中比is系某一学生年龄小的学生名单 selects1 sname s1 sagefromstudents1wheres1 sage any selects2 sagefromstudents2wheres2 sdept is ands1 sdept is orderbys1 sagedesc 注意 s1 sdept is 条件是父查询块中的条件 不是子查询块中的条件 用集函数实现 selects1 sname s1 sagefromstudents1wheres1 sage selectmax s2 sage fromstudentwheres2 sdept is ands1 sdept is orderbysagedesc 例40查询其他系中比is系所有学生年龄都小的学生名单 selects1 sname s1 sagefromstudents1wheres1 sage all selects2 sagefromstudents2wheres2 sdept is ands1 sdept is orderbys1 sagedesc 本查询同样也可以用集函数实现 即首先用子查询找出 is 系的最小年龄 18 然后在父查询中查所有非 is 系且年龄小于18岁的学生姓名及其年龄 sql语句如下 selects1 sname s1 sagefromstudents1wheres1 sage selectmin s2 sage fromstudents2wheres2 sdept is ands1 sdept is orderbys1 sagedesc 事实上 用集函数实现子查询通常比直接用any或all查询效率要高 4 带有exists谓词的子查询exists代表存在量词 带有exists谓词的子查询不退回任何实际数据 它只产生逻辑真值 true 或逻辑假值 false 例41查询所有选修了1号课程的学生姓名 selectsnamefromstudentwhereexists select fromscwheresc sno student snoandcno 1 例41查询所有未修1号课程的学生姓名 selectsnamefromstudentwherenotexists select fromscwheresc sno student snoandcno 1 带有in谓词的例37可以用如下带exists谓词的子查询替换 查询与 刘晨 在同一个系学习的学生 selects1 sno s1 sname s1 sdeptfromstudents1whereexists select fromstudents2wheres1 sdept s2 sdeptands2 sname 刘晨 例42查询选修了全部课程的学生姓名 selectsnamefromstudentwherenotexists select fromcoursewherenotexists select fromscwheresc sno student snoandsc cno course cno 例43查询至少选修了学生95002选修的全部课程的学生号码 本题的查询要求可以做如下解释 查询这样的学生 凡是95002选修的课 他都选修了 换句话说 若有一个学号为x的学生 对所有的课程y 只要学号为95002的学生选修了课程y 则x也选修了y 那么就将他的学号选出来 即不存在这样的课程y 学生95002选修了y 而学生x没有选 用sql语言可表示如下 selectdistinctsnofromscscxwherenotexists select fromscscywherescy sno 95002 andnotexists select fromscsczwherescz sno scx snoandscz cno scy cno 3 3 4集合查询集合操作主要包括并操作union 交操作intersect和差操作minus 例44查询计算机科学系的学生及年龄不大于19岁的学生 select fromstudentwheresdept cs unionselect fromstudentwheresage 19 本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集 例45查询选修了课程1或者选修了课程2的学生 本例实际上是查选修课程1的学生集合与选修课程2的学生集合的并集 selectsnofromscwherecno 1 unionselectsnofromscwherecno 2 注 标准sql只有并 没有交和差 但实际上 交或差都可以用其它方法实现 具体实现根据不同的查询而不同 用语义替换 例46查询计算机科学系的学生与年龄不大于19岁的学生的交集 本查询换种说法就是 查询计算机科学系中年龄不大于19岁的学生 例47查询选修课程l的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生 例48查询计算机科学系的学生与年龄不大于19岁的学生的差集 本查询换种说法就是 查询计算机科学系中年龄大于19岁的学生 例49查询选修课程1的学生集合与选修课程2的学生集合的差集本例实际上是查询选修了课程1但没有选修课程2的学生 3 3 5小结问题 有关系模式part item no name p no 表示一个产品零部件情况及产品的组成 p no表示上一级的零件 如何用sql实现查询 查询某个产品 给定item no 的所有零部件 3 4数据更新3 4 1插入数据 1 插入单个元组insertinto 表名 属性列1 属性列2 values 常量1 常量2 例1将一个新学生记录 学号 95020 姓名 陈冬 性别 男 所在系 is 年龄 18岁 插入student表中 insertintostudentvalues 95020 陈冬 男 is 18 例2插入一条选课记录 95020 1 insertintosc sno cno values 95020 1 2 插入子查询结果insertinto 表名 属性列1 属性列2 子查询 例3对每一个系 求学生的平均年龄 并把结果存入数据库 对于这道题 首先要在数据库中建立一个有两个属性列的新表 其中一列存放系名 一列存放相应系的学生平均年龄 createtabledeptage sdeptchar 15 avgagesmallint 然后对数据库的student表按系分组求平均年龄 再把系名和平均年龄存入到新表中 insertintodeptage sdept avgage selectsdept avg sage fromstudentgroupbysdept 3 4 2修改数据update 表名 set 列名 表达式 列名 表达式 where 条件 1 修改某一个元组的值例4将学生95001的年龄改为22岁updatestudentsetsage 22wheresno 95001 2 修改多个元组的值例5将所有学生的年龄增加1岁 updatestudentsetsage sage 1 3 带子查询的修改语句例6将计算机科学系全体学生的成绩置零 updatescsetgrade 0where cs selectsdeptfromstudentwherestudent sno sc sno 或 updatescsetgrade 0wheresnoin selectsnofromstudentwheresdept cs 4 修改操作与数据库的一致性第一条update语句修改student表 updatestudentsetsno 96089 wheresno 95007 第二条update语句修改sc表 updatescsetsno 96089 wheresno 95007 问题 sc表中要修改的sno是引用student的外键 第一条update执行后 显然已破坏该参照完整性 这样的过程能正常执行吗 3 4 3删除数据deletefrom 表名 where 条件 1 删除某一个元组的值例7删除学号为95019的学生记录 deletefromstudentwheresno 95019 1 删除多个元组的值例8删除所有的学生选课记录 deletefromsc 2 带子查询的删除语句例9删除计算机科学系所有学生的选课记录 deletefromscwhere cs selectsdeptfromstu