Y第2章关系数据库及其语言.ppt

2 1关系模型 2 2关系代数 2 3关系演算 2 4数据库标准语言SQL 第2章关系数据库 2 1关系模型 1 关系模型的特点及组成特点 结构简单 表达力强语言的一体化非过程化的操作坚实的数学基础操作效率较低组成 关系数据结构关系数据操作关系完整性约束 关系DB系统是支持关系模型的DB系统 2 1关系模型 2 关系数据结构1 域 是一组具有相同数据类型的值的集合 2 笛卡尔积给定一组域D1 Dn 可有相同的域 其笛卡尔积为 DlXD2X XDn d1 d2 dn di Di i 1 2 n 例 姓名集D1 a b c 性别集D2 0 1 D1XD2 a0b0c0a1b1c1 2 1关系模型 3 关系笛卡尔积的有限子集称作对应域上的关系 关系 是元组的集合 R D1 D2 Dn R是n元 目 关系4 术语 候选键主键 主属性非主属性外键 外来关键字 外键提供了一种表示两个关系联系的方法 S Sno Cardno Sname Sage SC Sno Cno Grade 主键 两个主属性 非主属性 2 1关系模型 5 关系的性质 1 每列的值为同一类型 2 每列具有不同的属性名 3 任意两元组不能完全相同 4 行的次序可以互换 5 列的次序可以互换 6 分量值是原子的 5 学号姓名年龄 网虫 不允许 元组 分量值 属性名 关系的类型 基本关系查询表视图表 2 1关系模型 6 关系模式与关系数据库 关系模式 是关系结构的描述和定义 即二维表的表结构定义 关系实质上是一张二维表 因此 关系模式必须指出表的结构 即它由哪些属性构成 这些属性来自哪些域 以及属性与域之间的对应关系 关系模式简记为关系的属性名表 R U R A1 A2 A3 An 例 学生 学号 姓名 总成绩 关系数据库 对应关系模型的一个应用领域的全部关系的集合 联系 2 1关系模型 3 关系模型的数据操作关系模型中常用的关系数据操作有四种 1 数据查询 基本操作有 关系属性的指定 关系元组的选择 两个关系的合并 2 数据插入 在关系内插入一些新元组 3 数据删除 在关系内删除一些元组 4 数据修改 修改关系元组的内容 可分解为 先删除要改的元组 再插入新元组 关系数据操作是一种集合式操作 复杂的关系数据操作可通过基本的关系数据运算获得 此外 还需要有关系的操作规则及具体的关系数据语言来实现这些操作 关系数据语言可分为研究用的抽象语言和可使用的实现语言 关系数据语言大体分成三类 如表2 3 2 1关系模型 4 关系的完整性三类完整性约束 实体完整性参照完整性用户定义的完整性 说明 1 该规则是对基本关系的约束和限定 2 实体有唯一性标识 主键 3 主键上的属性不能取空值 1 实体完整性实体完整性规则 若属性A是基本关系R的主码属性 则属性A不能取空值 由关系系统自动支持 是应用领域需要遵循的约束条件 2 1关系模型 2 参照完整性引用关系 关系中的某属性的值需要参照另一关系的属性来取值 例1 学生 学号 姓名 性别 专业号 年龄 专业 专业号 专业名 例2 学生 学号 姓名 性别 专业号 年龄 合作者号 引用 引用 2 1关系模型 设 基本关系R S 可为同一关系 若F是R的一个 组 属性 但不是R的键 如果F与S的主键K相对应 则称F是R的外键 例 学生 学号 姓名 性别 专业号 年龄 专业 专业号 专业名 目标关系 参照关系 外键 说明 S的主键K和R的外键F必须定义在同一个 组 域上R为参照关系 S为目标关系 2 1关系模型 参照完整性规则若属性 组 F是R的外键它与S的主键K相对应 则对于R中每个元组在F上的值必为下列之一 1 取空值 F的每个属性值均为空 2 等于S中某个元组的主键值 例 学生 学号 专业号 姓名 关系中每个元组的专业号取值 1 空值 未知值 2 非空值 3 用户定义的完整性反映具体应用所涉及的数据应满足的语义要求 约束条件 例 学生关系中的年龄在15 45之间 选修关系中的成绩在0 100之间 定义了外键与主键之间的引用规则 指外键不能引用不存在的主键值 2 2关系代数 关系数据语言分类 1 关系代数2 关系演算元组关系演算域关系演算3 具有双重特点的语言 SQL 共同特点是 具完备的表达能力是非过程化的集合操作语言功能强可嵌入 又可独立使用 2 2关系代数 关系代数是一种抽象的查询语言 它以关系为运算对象 通过对关系进行 组合 或 分割 得到所需的数据集合 关系 分类 集合运算 并 交 差 广义笛卡尔积 关系运算及其扩充 一 集合运算设 t为元组变量 R S为同类 相同元 相应属性同域 关系 下列运算结果为同类关系 1 并运算 RUS t t R t S 2 差运算 R S t t R t S 3 交运算 R S t t R t S R S R R S 2 2关系代数 集合运算示例 2 2关系代数 4 广义笛卡尔积 设 R S为不同类关系 则结果为不同类关系 R S trts tr R ts S 连接为m n元关系 n元关系 m元关系 2 2关系代数 记号 设t为R的元组变量设 R A1 A2 An R U t Ai Ai为属性 t A A为属性集 例 t 学号 R中学号上的值t 学号 姓名 学号姓名年龄 t 2 2关系代数 二 关系运算1 选择 是关系行上的选择 产生同类关系 F R t t R F t true 含义 由R中满足F条件的元组组成 其中 F由属性名 值 比较符 逻辑运算符组成 例 Sage 19 Student 例 A2 5 A3 f R 2 2关系代数 2 投影运算 是关系列的选择 产生不同类关系 A R t A t R 含义 R中取属性名表A中指定的列 消除重复元组 例 Sno Cno SC 用关系代数表示查询 例 查选2号课程的学生记录 例 成绩在90分以上的学生号 解 Cno 2 SC 解 Sno Grade 90 SC 2 2关系代数 3 连接运算 连接也称为 连接 它从两个关系的笛卡尔积中选取属性间满足一定条件的元组 R S trts tr R ts S tr A ts B A B 比较运算符 大于连接 为 的连接 等值连接 为 的连接 R S R A S B R S A B 例 R A S B R S 含义 从R S中选取R关系在A属性组上的值与S关系在B属性组上值满足 关系的元组 2 2关系代数 3 连接运算 自然联接 设R S有同名属性Bi i 1 2 k R S 属性名表 R Bi S Bi R S 例 2 2关系代数 例 查询至少选修了一门其直接先行课为005号课程的学生名 学生 课程数据库表见教材 S sno sname sex age dept C cno cname credit pcno SC sno cno grade sname pcno 005 C SC S A F R 例 查选修002号课程的学生姓名与年龄 sname age S cno 002 SC 2 2关系代数 4 除 Division 关系代数定义了除运算 但实际应用中 当关系R真包含了关系S时 R S才有意义 R能被S除尽的充分必要条件是 R中的属性包含S中的所有属性 R中有一些属性不出现在S中 设R为r元 S为s元关系 r s 0 当关系R真包含了关系S时 R S可用下式计算 R S 1 2 r s R 1 2 r s 1 2 r s R S R 例2 8 设R S P W1 P W2 P W3 P 则R W1可表示为 S R S S R W1 R 同理可列出另外两式 R S 2 2关系代数 除法示例 例2 8 例R Wi的运算结果可理解为 R中包含Wi属性值的那些元组在R与Wi的属性名之差 即S 上的投影 2 2关系代数 例 查所选课程包含学生210101所选全部课程的学生号和姓名 sno sname S sno cno SC cno sno 210101 SC 例 查询选修了全部课程的学生学号与姓名 sno sname S sno cno SC cno C 设学生 课程数据库 S SC C 2 2关系代数 关系代数五种基本运算 投影 选择 并 差 笛卡尔积5种基本运算的作用 1 关系的属性指定 A1 A2 An R 2 关系的元组选择 F R 3 两个关系的归并R1 R24 关系中元组的插入R1 R25 关系中元组的删除R1 R2 2 3关系代数 5 扩充的关系运算 1 广义投影 E1 E2 En R 2 赋值 R S 3 外连接 R和S自然连接时 保留原该舍弃的元组 同时在这些元组新增加的属性上填空值 NULL 4 半连接 R和S的自然连接只在关系R 或关系S 的属性集上的投影 R和S的半连接记为R S 5 聚集 G聚集函数名 属性 关系表达式 见教材例 5 扩充的关系运算 6 外部并 由R和S中的所有属性 无重复 组成 其元组由属于R或属于S的元组组成 并在增加的属性填上空值 说明 R S可不同类 5 扩充的关系运算 7 重命名 x E 返回表达式E的结果 并把名字x赋给E x A1 A2 An E 其含义为返回表达式E的结果 并把名字x赋给E 同时将各属性更名为A1 A2 An 课程 数学 S R R 成绩 S 成绩 S 姓名 课程 数学 姓名 王红 R 例 设R 姓名 课程 成绩 求数学成绩比王红高的学生名 2 3关系演算 1 元组关系演算中的原子公式 R t 表示 t是关系R的一个元组 关系演算 以谓词演算为基础表示的关系运算 关系演算分类元组关系演算域关系演算1 元组关系演算用 t t 表示关系 命题公式 元组变量 表示所有使得 为真的元组组成的集合 关系R可表示为 t R t t i u j 表示 元组t的第i个分量与元组u的第j个分量之间满足 关系 t i C或C t i 常量 表示 元组t的第i个分量与常量C之间满足 关系 2 3关系演算 2 公式的递归定义 每个原子公式是公式 若 1和 2是公式 则 1 2 1 2 1也是公式 若 是公式 则 t t 也是公式 有限次使用上述3条规则得到的公式都是元组关系演算表达式 公式中各种运算符的优先级 算术运算符 量词 逻辑运算符 高 低 1 元组关系演算 2 3关系演算 域关系演算 以域变量作为谓词变元的基本对象 用 t1 t2 tk t1 t2 tk 表示关系 t1 t2 tk是元组变量t的各个域变量 1 域演算中的原子公式 R t1 t2 tk 表示 由分量t1 t2 tk组成的元组属于R ti uj表示 元组变量t的第i个分量与t的第j个分量间满足 关系 ti C或C ti表示 元组变量t的第i个分量与常量C间满足 关系 2 公式的递归定义与元组演算定义类似 2 域关系演算 2 3关系演算 例 在S表中查学生年龄小于20岁的学生姓名 1 元组演算 R1 t 1 u S u t 1 u 2 u 4 20 2 域演算 R2 t2 t1 t3 t4 t5 S t1t2t3t4t5 t4 20 设学生 S sno sname sex age dept 选课 SC sno cno grade 2 4数据库标准语言SQL 一 SQL的特征SQL是通用的 功能极强的关系DB语言 1 SQL的主要标准SQL 86SQL 89SQL 92 SQL2 SQL 99 SQL3 SQL 20032 SQL的功能特点功能 数据定义 特点 综合统一高度非过程化灵活的使用方式 自含 嵌入式 模块式 简洁 通用 功能强 数据操纵 数据控制 一 SQL的特征 术语对照 一般关系模型SQL外模式 视图 VIEW 模式 基本表 TABLE 内模式 存储文件 索引元组 行 ROW 属性 列 COLUMN 3 SQL的基本组成 1 SQL数据库层次结构 2 SQL的语句类型SQL模式语句SQL数据语句SQL事务与控制语句SQL会话及诊断语句 一 SQL的特征 4 SQL的数据类型预定义数据类型构造数据类型用户定义数据类型 UDT 5 SQL环境 1 SQL模式与目录SQL模式 基本表的集合 好处 允许在不同的SQL模式中出现同名的基表名或视图名 目录 SQL环境中所有模式的集合 定位基表的方式 2 SQL环境设置默认的目录和模式设置用户身份 二 SQL的数据定义 1 表的建立和删除 1 表的建立命令格式 CREATETABLE 模式名 例 CREATETABLEStudent snoCHAR 5 NOTNULLUNIQUE snameCHAR 20 UNIQUE sexCHAR 1 ageINT deptCHAR 15 二 SQL的数据定义 完整性约束条件涉及到该表的多个属性列 则必须定义在表级上 否则既可定义在列级 也可以定义在表级 注意 例 定义表结构 并指定相应的数据完整性约束条件 分析 外码 sno cno 主码 sno 姓名 非空 性别 男 女两值 Student表 Course表 主码 cno 课程名 非空 外码 pcno SC表 主码 sno cno 成绩 0 100 二 SQL的数据定义 CREATETABLEStudent snoCHAR 5 snameCHAR 8 NOTNULL sexCHAR 2 ageSMALLINT deptCHAR 20 PRIMARYKEY sno CHECKsexIN 男 女 列级完整性约束 实体完整性约束 用户自定义完整性约束 二 SQL的数据定义 CREATETABLECourse cnoCHAR 4 cnameCHAR 10 NOTNULL pcnoCHAR 4 creditSMALLINT PRIMARYKEY cno FOREIGNKEY pcno REFERENCESCourse cno 参照完整性约束条件 二 SQL的数据定义 CREATETABLESC snoCHAR 5 cnoCHAR 4 gradeSMALLINT PRIMARYKEY sno cno FOREIGNKEY sno REFERENCESStudent sno FOREIGNKEY cno REFERENCESCourse cno CHECK gradeISNULL OR gradeBETWEEN0AND100 二 SQL的数据定义 5 索引的建立与删除目的 提供多种存取路径 加快查找速度 建索引的一般格式 CREATE UNIQUE CLUSTER INDEXON 说明 次序 ASC 缺省 或DESC 降序 一个索引值只对应唯一的数据记录 聚簇索引 索引项逻辑顺序与表中记录的物理顺序一致注意 在一个基本表上最多只能建立一个聚簇索引 经常更新的列不宜建立聚簇索引 二 SQL的数据定义 例 为Student表按学号升序建唯一聚簇索引 SC表按学号升序和课程号降序建唯一索引 CREATEUNIQUECLUSTERINDEXStnoONStudent sno CREATEUNIQUEINDEXScnoONSC sno cnoDESC 删除索引一般格式为 DROPINDEX 例 DROPINDEXStno 删除索引时 系统会同时从数据字典中删去有关该索引的描述 三 SQL的数据查询 一般形式 SELECTFROMWHEREGROUPBYHAVINGORDERBY DESC 基本语句含义 从表 或视图 R中找出满足条件F的元组 再从中选出目标属性A的值形成结果表 查询目标 为属性名表或表达式或 数据来源 选择元组的条件 将结果按的值进行分组 满足条件的组才予输出 按排序查询结果 三 SQL的数据查询 学生 课程数据库 Student sno sname sex age dept Course cno cname pcno credit SC sno cno grade 1 单表查询 1 选择表中的列例 查询所有学生的姓名 学号 所在院系 SELECTsname sno deptFROMStudentWHERE T snamesnodept 王萧虎200101信息院 目标A来源R条件F 三 SQL的数据查询 例 查全体学生的姓名及其出生年份 SELECTSname 2011 SageFROMStudent Sname2011 Sage 王萧虎1987 ASBirthday 别名 Birthday 2 选择表中的行a 比较例 查考试成绩不及格的学号 SELECTSnoFROMSCWHEREGrade 60 DISTINCT 去掉重复元组 表达式 三 SQL的数据查询 b 确定范围 BETWEEN AND 例 查询选002号课程且成绩在80 90的学生号 SELECTsnoFROMSCWHEREsno 002 ANDgradeBETWEEN80AND90 c 确定集合 IN 例 查询信息院 数学系和计算机学院学生姓名和性别SELECTsname sexFROMStudentWHEREdeptIN 信息院 数学系 计算机学院 查询不是这三个系的学生的姓名和性别 三 SQL的数据查询 d 字符匹配 近似查询格式 NOT LIKE 含义 查指定列值与相匹配的元组 其中可含 代表任意长度 可为0 的字符串 代表任意单个字符 例 查所有姓刘或姓王的学生姓名 学号和性别 SELECTsname sno sexFROMStudentWHEREsnameLIKE 刘 ORsnameLIKE 王 查询所有非姓刘和非姓王的学生姓名 学号和性别 三 SQL的数据查询 3 对查询结果排序例 查询全体男学生的学号 姓名 结果按所在的系升序排列 同一系中的学生按年龄降序排列 Student表 SELECTsno snameFROMStudentWHEREsex 男 ORDERBYdept ageDESC 三 SQL的数据查询 2 表达式与函数的使用 1 使用集函数COUNT DISTINCT ALL 统计元组个数COUNT DISTINCT ALL 统计列值个数SUM 计算一数值型列值的总和AVG MAX MIN 例 查选1号课并及格学生的总人数及最高分 最低分 SELECTCOUNT MAX Grade MIN Grade FROMSCWHERECno 1 ANDGrade 60 三 SQL的数据查询 2 对查询结果分组 GROUPBY子句将查询结果表按某一 多 列值分组 值相等的为一组 目的 细化集函数的作用对象 例 求每个学号的平均成绩 SELECTsno AVG grade FROMSCGROUPBYsno HAVING短语 在各组中选择满足条件的小组 WHERE子句 在表中选择满足条件的元组 WHERE 并将其超过88分的输出 HAVINGAVG grade 88 三 SQL的数据查询 例 找出选课学生超过30人课程的平均成绩及选课人数 SELECTcno AVG grade COUNT ASst numberFROMSCGROUPBYsnoHAVINGst number 30 例 求学生关系中女生每一年龄组 超过20人 有多少 查询结果按人数升序排列 人数相同时按年龄降序排SELECTage COUNT sno ASnumberFROMStudentWHEREsex 女 GROUPBYageHAVINGnumber 20ORDERBYnumber ageDESC 练习 查询至少选修4门课程的学生学号 三 SQL的数据查询 3 多表查询 1 子查询 嵌套查询 查询块 SELECTFROMWHERE子查询 一个查询块嵌套在另一查询块中作为条件的查询 子查询一般跟在IN SOME ANY ALL和EXIST等谓词后面 例 查平均成绩最高的学生号SELECTsnoFROMSCGROUPBYsnoHAVINGAVG grade ALL SELECTAVG grade FROMSCGROUPBYsno 三 SQL的数据查询 例 找出籍贯为湖北或河北 选课成绩为90分以上的学生的姓名 课号和成绩 SELECTsname cno gradeFROMStudent SCWHEREStudent sno SC snoANDplaceIN 湖北 河北 ANDgrade 90 三 SQL的数据查询 SELECTStudent sno sname sex age dept cno gradeFROMStudent SCWHEREStudent sno SC sno 2 条件连接查询连接条件的一般格式为 当连接运算符为 时 称为等值连接 例 查询每个学生的情况及其选课成绩 三 SQL的数据查询 3 带存在谓词的子查询EXISTS作用 若子查询结果非空 则父查询的WHERE子句返回真 否则返回假产生逻辑值 例 查询所有选修了1号课程的学生姓名 SELECTsnameFROMStudentWHEREEXISTS SELECT FROMSCWHEREsno Student snoANDcno 1 执行过程 相关子查询 子查询的条件依赖于外层父查询的某个属性值 三 SQL的数据查询 3 自身连接查询例 找出年龄比 王迎 同学大的同学的姓名及年龄 SELECTs1 sname s1 ageFROMSASs1 SASs2WHEREs1 age s2 ageANDs2 sname 王迎 练习 查每一门课的间接先修课 即先修课的先修课 SELECTFIRST cno SECOND pcnoFROMCourseFIRST CourseSECONDWHEREFIRST pcno SECOND cno 三 SQL的数据查询 4 自然连接与外连接1 自然连接 NATURALJOIN 例 查籍贯为湖北 课程成绩在90分以上的学生档案及其成绩情况 SELECT FROMStudentNATURALJOINSCWHEREplace 湖北 ANDgrade 90 2 外连接左外连接 LEFT OUTER JOIN 保留左关系所有元组右外连接 RIGHT OUTER JOIN 保留右关系所有元组全外连接 FULL OUTER JOIN 保留两关系所有元组 三 SQL的数据查询 例 查每个学生及其选课情况 SELECT FROMStudent SCFROMStudentNATURALRIGHTOUTERJOINSC 未选课 用右外连接产生未选课的空行 教师表 Teacher 教师号 姓名 所属大学 职称 任职表 Post 编号 姓名 职务 例 SELECT FROMTeacherFULLOUTERJOINPost 例 SELECT FROMTecherLEFTOUTERJOINPost 三 SQL的数据查询 5 SQL中的空值处理1 空值的含义值暂未知 值不适用 值需隐瞒 2 空值的若干规则 1 空值与数值型数据进行算术运算 结果为空值 2 空值和任何值 包括空值 进行比较运算 结果为UNKNOWN 空值的测试 ISNULL和ISNOTNULL 例 查询缺少选课成绩的学生号和相应的课程号SELECTSno CnoFROMSCWHEREgradeISNULL 三 SQL的数据查询 6 递归合并查询递归合并语句格式 WITHRECURSIVEASUNION例 对表2 12航班表Flight airline from to depart arrive 求出 能从一个城市飞到另一个城市的城市对集合 含直接到达和间接中转到达 三 SQL的数据查询 直达 Reach f t Flight a f t d r 间接到达 Reach f t Flight a f t1 d r ANDReach t1 t SQL语句 WITHRECURSIVEReach from to AS SELECTfrom to 选出直接到达城市对FROMFlight UNION SELECTFlight from Reach to 选出间接到达城市对FROMFlight Reach 递归WHEREFlight to Reach from SELECT FROMReach 四 SQL的数据更新 插 删数据注意 插入时 表定义中说明了NOTNULL的列不能取空值 向参照表中插入元组 关系系统自动支持 实体完整性参照完整性删除时 需维护一致性 修改基本表注意 新增加的列一律为空值 修改列定义可能会破坏已有数据 删除基本表注意 基本表定义一旦删除 表中的数据 此表上建立的索引和视图都将自动被删掉 四 SQL的数据更新 1 插入数据1 插入单个元组格式 INSERTINTO VALUES 说明 1 若插入全部属性 则属性列可省略 2 表定义中说明为NOTNULL的列不能取空值 3 属性值与相对应的属性列的数据类型要匹配 4 向参照表中插入元组 关系系统自动支持 实体完整性参照完整性 四 SQL的数据更新 2 插入子查询结果格式 INSERTINTO 2 修改数据格式 UPDATESET WHERE 3 删除数据格式 DELETEFROM WHERE 五 视图机制 1 视图的概念及作用视图 从实 基本 表或视图中导出的虚表 1 视图与表的关系 在表上建立的表 是用户用来看数据的窗口 对视图的操作最终将转为对实表的操作 对视图的更新有一定的