数据库基础讲解,详细,入门,初学.xls

关系数据数据模型 使用最多 也是本书重点 IBM工程师E Fcodd与1970年提出 用二维表的形式来表示实体集属性间的关系及实体之间的联系的形式化模型 建立在集合代数基础上 关系模型中数据的逻辑结构是二维表 二维表指含有有限个不重复行的表格 F1F2F3F4表头 A1A2A3A4 数据B1B2B3B4 C1C2C3C4 一 属性和域 1 属性 Attribute 指关系中的列 列名为属性的名称 2 域 Domain 值的集 每个属性的取值范围 3 元组 Tuple 给定一组域D1 D2 Dn 它们的笛卡尔乘积为D1 D2 Dn d1 d2 dn di Di i 1 2 n 则 d1 d2 dn 称为元组 例 Studentclass 夏青操作系统 吴凡软件工程 章强数据库系统 例中有两个域 D1 Student 夏青 吴凡 章强 D2 Class 操作系统 软件工程 数据库系统 笛卡尔乘积元组为 D1 D2 夏青 操作系统 夏青 软件工程 夏青 数据库系统 吴凡 操作系统 吴凡 软件工程 吴凡 数据库系统 章强 操作系统 章强 软件工程 章强 数据库系统 笛卡尔乘积的基数为3 3 9 D1 D2共有9个元组 这9个元组构成一张表格 Studentclass 夏青操作系统 夏青软件工程 夏青数据库系统 吴凡操作系统 吴凡软件工程 吴凡数据库系统 章强操作系统 章强软件工程 章强数据库系统 4 关系 域D1 D2 Dn的子集称为D1 D2 Dn上的关系 使用R D1 D2 Dn 表示 R表示关系的名称 n为关系的目或者度 通常记为 R D1 A1 D2 A2 D3 A3 Dn An 关系是笛卡尔乘积的有限子集 所以关系也是一个二维表 表的每行对应一个元组 表的每列对应一个域 由于域可以相同 为了加以区分 必需给每列起一个名字 称为属性 N目关系必有n个属性 若关系中的某一个属性的值能够唯一的标志一个元组 则称该属性组为候选码Candidata key 若一个关系有多个候选码 则选定其中一个为主码Primay key 候选码的诸属性称为主属性 不包含在任何候选码中的属性称为非主属性或非码属性 最简单情况下 候选码只包含一个属性 最极端情况下 关系模式的所有属性都是这个关系模式的候选码 称为全码 一般来说D1 D2 Dn的笛卡尔成绩是没有实际语义的 只有它的某个子集才有实际含义 教材事例 结论 关系是一个二维表 表的每行对应一个元组 表的列对应属性 上述概念中关系写作 Study Sname Sclass 再比如 描述大学生的关系可以写作 Student 姓名 学号 性别 出生年月 籍贯 性别 入学年份 二 关系和元组的特点 1 列是同质的 即每一列中的分量是同一类型的数据 来自同一个域 2 不同的列可以出自同一个域 称其中的每一列为一个属性 不同的属性要给予不同的属性名 3 列的顺序是无关紧要的 可以任意交换 R A B 和R B A 相同 4 任意两个候选码不能相同 即表中的行应该互不相同 5 行的顺序无所谓 可以任意交换 6 关系的表是简单表 不允许出现组合属性 或分量必须取原子值 即每一个分量都必须是不可分割的数据项 三 关系模式 关系模式 Relation Schema 是型 关系是值 关系模式是对关系的描述 关系实质是一张二维表 表的每一行为一个元组 每一列为一个属性 一个元组就是该关系所涉及的属性集的笛卡尔乘积的一个元素 一个关系是由它的元组语义来确定的 元组语义实质上是一个n目谓词 现实中 关系模式的所有关系必须满足完整性约束条件 结论 关系是个表格 但加以适当限制 属性称为列 Column 元组称为行 Row 投影的概念 取一个关系或者元组的部分属性的值 称为关系或者元组在属性上的投影 例 Student 姓名 学号 性别 出生年月 籍贯 系别 入学年份 记着R X 或者T X 例如Student 姓名 性别 键 关系数据模型要求 每一个元组不同 例 Student 姓名 学号 性别 出生年月 籍贯 系别 入学年份 结论 每个学生学号应该不同 区分每个元组 T 学号 姓名 T 姓名 性别 出生年月 候选键 关系中的属性或者属性组唯一决定一个元组 即其他属性值 属性组 学号 姓名 性别 出生年月 决定唯一性 但它为超键 因为其中属性子集 学号 也决定唯一性 一个关系可以有多个候选键 但只能选择一个为主键 全键 在最极端的情况下 关系模式的所有属性都是这个关系模式的候选码 则称为全键 关系模式中常常在主属性下加下划线 标出主键 其他为非主键 例 Student 姓名 学号 性别 出生年月 籍贯 系别 入学年份 如果关系的属性来自其他关系的主键 称为外键 例 课程 课程号 课程名 学分 开课时间 先修课程 学生 学号 姓名 性别 出生年月 成绩 学号 课程号 成绩 注 SQL语句定义方法 使用外键解决学生 课程之间的N M的关系 优点 形式简单一致 缺点 不能显式表示这种语义 键的理解 候选键 主键 超键 全键 外键 约束 关系模式R D1 A1 D2 A2 D3 A3 Dn An 关系有A1 An共N个属性 每一个属性的域分别是D1 Dn 约束 1 指语义上的限制 例如 年龄 工龄 库存量等均有语义限制 年龄 工龄 库存量不可能为负数 2 制约属性上关系 例如主键的值不能为空 或者重复 结论 元组应该是满足约束条件的元组的子集 当然约束条件将随应用场合不同而不同 比如 库存量对于有些货物只能整数 而另一些货物可以小数 再比如性别只能去男或女 一 语义检查可以有用户或数据库完成 注意 在定义数据库时如果没有定义这种语义检查 就必须有应用程序员在编程中完成检查 如定义则由数据库系统自身检查 二 完整性约束一般在数据更新时检查 四类约束完整性 1 实体完整性约束 每个关系相当于一个实体 主键应该是唯一的且不能为空 是实体内的约束 2 参照完整性约束 a 不同关系之间的约束 设关系R1有一个外键FK 则其元组t的外键值为t FK t FK 引用了R2的主键PK 参照完整性约束要求 t FK t PK 或者为空 学生 学号 姓名 性别 专业号 出生年月 专业 专业号 专业名 学生表中的专业号引用自专业关系表的专业号 学生关系中的专业号或者为空值或者为专业关系中真实存在的专业号 学生 学号 姓名 性别 出生年月 课程 课程号 课程名 学分 成绩 学号 课程号 成绩 成绩关系中的学号必须赋值且为学生关系中存在的 成绩关系中的课程号必须赋值且为课程关系中存在的 b 同一关系之间不同元组的约束 课号课程学时学分先修课程 C01数据结构804 C02操作系统643 C01 C03人工智能643 C02 C04计算机网络643 C08 3 用户定义完整性约束 允许用户对数据库做自定义的语义约束 关系操作 关系操作即关系代数操作 1 选择操作 给定条件选择符合条件的元组 选取适当的行 Student 姓名 学号 性别 出生年月 籍贯 系别 入学年份 选择条件是布尔表达式 条件为真或者为假 或者条件的逻辑运算and or not select from student where 姓名 夏青 select from student where 籍贯 江苏 and 出生年月 1980 复合选择操作 例如选择成绩在80分以上的男同学 可以先选择男同学 再选择80分以上的 也可以先选择所有80分以上的同学 再选择男同学 select from student where 性别 男 and 成绩 80 select from student where 成绩 80 and 性别 男 2 投影操作 Ps 例 课程 课程名 课程号 学分 开课时间 先修课程 例 Student 姓名 学号 性别 出生年月 籍贯 系别 入学年份 U 例 R1R2 ABCD abcb dafd ebd R1 R2 R1 U R2 abca ebdd e b 注意 两个可以做并的集合 两个属性的名字不一定要求相同 但是其属性的目以及每个属性的域必须相同 笛卡尔乘积运算 参与的关系不必兼容 设有关系 R和S 它们的元组数量为 R 和 S 则乘积为 上式中是t和g的拼接 即把t g的元组拼接成一个元组 笛卡尔乘积元组为 广义笛卡尔乘积 两个分别为n目和m目的关系R和S的笛卡尔乘积是一个n m列的元组的集合 D1 D2 夏青 操作系统 夏青 软件工程 夏青 数据库系统 吴凡 操作系统 吴凡 软件工程 吴凡 数据库系统 章强 操作系统 章强 软件工程 章强 数据库系统 R StudentStudent笛卡尔乘积元组为 夏青D1 D2 夏青 操作系统 夏青 软件工程 夏青 数据库系统 吴凡 吴凡 操作系统 吴凡 软件工程 吴凡 数据库系统 章流StudentStudentClassClass 章强 操作系统 章强 软件工程 章强 数据库系统 夏青操作系统 S夏青软件工程 C SRC ClassClass夏青数据库 操作系统吴凡操作系统 软件工程吴凡软件工程 数据库吴凡数据库 章流操作系统 章流软件工程 章流数据库 连接 join 连接也称为 连接 连接从两个关系的笛卡儿积中选取属性间满足一定条件的元组 记为 其中A和B分别为R和S上度数相等且可比的属性组 为 的连接 称为等值连接 记为 如果两个关系中进行比较的分量必须是相同的属性组 并且在结果中把重复的属性列去掉 则称为自然连接 记为 4 4 除除法法 设两个关系R和S的元数分别为r和s 设r s 0 那么R S是一个 r s 元的元组的集合 R S 是满足下列条件的最大关系 其中每个元组t与S中每个元组u组成的新元组必在关系R中 其具体计算公式如下 5 5 外外连连接接 两个关系R和S进行自然连接时 选择两个关系R和S公共属性上相等的元组 去掉重复的属性列构成新关系 这样 关系R中的某些元组有可能在关系S中不存在公共属性值上相等的元组 造成关系R中这些元组的值在运算时舍弃了 同样关系S中的某些元组也可能舍弃 为此 扩充了关系运算左外连接 右外连接和完全外连接 1 左外连接 R和S进行自然连接时 只把R中舍弃的元组放到新关系中 2 右外连接 R和S进行自然连接时 只把S中舍弃的元组放到新关系中 3 完全外连接 R和S进行自然连接时 只把R和S中舍弃的元组都放到新关系中 6 6 关关系系运运算算实实例例 RS A AB BC CD D 1 12 23 31 1 4 45 56 62 2 2 2 R S笛卡尔乘积 A AB BC CD D 1 12 23 31 1 1 12 26 62 2 4 45 53 31 1 4 45 56 62 2 一般连接 step1ABCD 1231 1262 4531 4562 step2ABCD 1231 1262 4562 等值连接 ABCD 1231 1262 4531 4562 ABCD 1262 自然连接先完成笛卡尔乘积 SR CB 电子系系别s 连接从两个关系的笛卡儿积中选取属性间满足一定条件的元组 记为 其中A和B分别为R和S上度数相等且可比的属性组 为 的连接 称为等值连接 记为 如果两个关系中进行比较的分量必须是相同的属性组 并且在结果中把重复的属性列去掉 则称为自然连接 记为 设两个关系R和S的元数分别为r和s 设r s 0 那么R S是一个 r s 元的元组的集合 R S 是满足下列条件的最大关系 其中每个元组t与S中每个元组u组成的新元组必在关系R中 其具体计算公式如下 两个关系R和S进行自然连接时 选择两个关系R和S公共属性上相等的元组 去掉重复的属性列构成新关系 这样 关系R中的某些元组有可能在关系S中不存在公共属性值上相等的元组 造成关系R中这些元组的值在运算时舍弃了 同样关系S中的某些元组也可能舍弃 为此 扩充了关系运算左外连接 右外连接和完全外连接 1 左外连接 R和S进行自然连接时 只把R中舍弃的元组放到新关系中 2 右外连接 R和S进行自然连接时 只把S中舍弃的元组放到新关系中 3 完全外连接 R和S进行自然连接时 只把R和S中舍弃的元组都放到新关系中 CD c2d1 c1d1 c3d2 v在关系R中 A可以取四个值 a1 a2 a3 a4 a1的象集为 b1 c2 b2 c3 b2 c1 a2的象集为 b3 c7 b2 c3 a3的象集为 b4 c6 a4的象集为 b6 c6 vS在 B C 上的投影为 b1 c2 b2 c1 b2 c3 设两个关系R和S的元数分别为r和s 设r s 0 那么R S是一个 r s 元的元组的集合 R S 是满足下列条件的最大关系 其中每个元组t与S中每个元组u组成的新元组必在关系R中 其具体计算公式如下 两个关系R和S进行自然连接时 选择两个关系R和S公共属性上相等的元组 去掉重复的属性列构成新关系 这样 关系R中的某些元组有可能在关系S中不存在公共属性值上相等的元组 造成关系R中这些元组的值在运算时舍弃了 同样关系S中的某些元组也可能舍弃 为此 扩充了关系运算左外连接 右外连接和完全外连接 查询只有一个人选修的课程号 学号课号成绩学号课号成绩 9901 C01859901 C0185 9902 C01909902 C0190 9902 C02859902 C0285 相关子查询求解 select 课号 from 选课 scx where 课号 not in select 课号 from 选课 where 学号 scx 学号 分组查询求解 select 课号 count from 选课 group by 课号 having count 1 select 课号 from 选课 group by 课号 having count 1 分析此语句的执行过程 from从句 制订查询使用 选课 表的精确副表 学号课号成绩 9901 C0185 9902 C0190 9902 C0285 group by从句组合副表中的行 学号课号成绩 9901 9902 C01 85 90 9902 C02 90 having从句 count 1 意味着由多行组成的所有 组合后 的行必须满足条件 学号课号成绩 9902 C02 90 select从句指定哪些列出现在最终结果中 课号 C02 查询年龄大于其所在职称的教师平均年龄的教师信息 编号姓名性别年龄职称 T01张伟男34 副教授 T02王强男27 讲师 T03叶力女54 教授 T04李霞女50 教授 相关子查询求解 select 姓名 编号 职称 from 教师 t1 where 年龄 select Avg 年龄 from 教师 t2 where t1 职称 t2 职称 返回队列子查询 查询与王强老师年龄与职称相同的教师 select from 教师 where 年龄 select 年龄 from 教师 where 姓名 王伟 and 职称 select 职称 from 教师 where 姓名 王伟 如果支持返回多列子查询时使用下语句 select from 教师 where 年龄 职称 select 年龄 职称 from 教师 where 姓名 王伟 多重子查询 查询大于王强老师年龄或与他职称相同的教师 select from 教师 where 职称 select 职称 from 教师 where 姓名 王强 or 年龄 select 年龄 from 教师 where 姓名 王强 查询选修数据库课程的学生姓名 性别和年龄 学号姓名性别年龄学号课号成绩 9901 陈平男209901 C0185 9902 王波男199902 C0390 9903 张莉女199902 C0285 课号课程学时学分 C01数据结构804 C02操作系统643 C03数据库643 C04计算机网络643 select 姓名 性别 年龄 from 学生 where 学号 in select 学号 from 选课 where 课号 select 课号 from 课程 where 课程名 数据库 查询总结9902 1 基本查询 a 无条件查询 选全部列或者指定列 列的显示次序 防止重复行distinct 别名 b 条件查询 比较运算 等 逻辑运算符or not and 特殊运算符in like between 空值is null或者is not null c 其他 order by Top N 聚集函数sum count max min avg group by 2 连接查询操作 a 连接的概念 多表操作 相等连接 非相等连接 b 自身连接 把表看成两个副本 c 非匹配连接 left join rightjoin 3 集合查询 a 并运算 把查询的行并起来 union b 交运算 intersection c 差运算 minus 4 子查询操作 a 返回一个值的子查询 b 返回一组值的子查询 all any in exists c 相关子查询 重复执行的子查询执行 d 返回多列的子查询 括号显示包含 e 多重子查询 子查询可以进行逻辑连接 多重嵌套查询 SQL操纵语言 DML 修改数据库中数据 insert delete update 数据插入Insert a 单行插入 命令格式 insert into values 列名表 指定列 未被选中的为空 默认全部 值 为具体数据值 与列要对应 例 insert into 教师 values T001 林强 男 30 助教 insert into 教师 编号 姓名 性别 values T001 林强 男 b 多行插入 insert into select语句 假设创建了 教师1 表 与教师格式相同 批量插入男教师信息 insert into 教师1 select from 教师 where 性别 男 删除数据delete语句 delete from where delete from 成绩表 where 成绩 80 修改数据update 1 修改数据一般格式 update set where 将教师表中王强的职称改为副教授 update 教师 set 职称 副教授 where 姓名 王强 将所有教师年龄增加一岁 update 教师 set 年龄 年龄 1 把学生表格中身高单位改成厘米 update 学生 set 身高 身高 100 2 引入子查询的修改 可以利用其他表格的数据修改当前表数据 例 利用课程的课程名 更新成绩表中的课程名 将计算机系学生全体成绩置零 update 选课 set 成绩 0 where 学号 in select 学生 学号 from 学生 选 课 where 学生 学号 选课 学号 and 学生 系别 计算机 update 选课 set 成绩 0 where 计算机 select 系别 from 学生 where 学生 学号 选课 学号 SQL中的视图 虚表 是从一个或几个基本表 或视图 导出的表 数据库中只存放视图的定义 不存放视图对应的数据 视图中的数据仍存放在原来的基本表中 因此 基表中的数据发生变化 从视图中查询出的数据也随之改变 视图相当于一个窗口或镜子 透过它可以看到数据库中自己感兴趣的数据以及其变化 视图一经定义 就可以和基本表一样被查询 删除 也可以在一个视图之上再定义视图 但对视图的更新 增 删 改 操作有一定的限制 视图可以进行插入 注意 非空以及键的要求 v语句格式 CREATE VIEW AS WITH CHECK OPTION v组成视图的属性列名 全部省略或全部指定 以下三种情况需要指定视图所有列名 1 某个目标列不是单纯的属性名 而是聚集函数或列表达式 2 多表连接时选出几个同名列作为视图字段 3 需要在视图中为某个列启用新的更合适的名字 v子查询不允许含有ORDER BY子句和DISTINCT短语 vRDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典 并不执行其中的SELECT语句 v在对视图查询时 按视图的定义从基本表中将数据查出 例 建立计算机系学生的视图 CREATE VIEW CS Student AS SELECT 学号 姓名 年龄 FROM 学生 WHERE 系别 计算机系 RDBMS执行Create view语句的结果只是把视图的定义存入数据字典 并不执行其中的select语句 只是执行视图查询时 才按照视图的定义从基本表中将视图查出 例 建立计算机系学生的视图 并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生 CREATE VIEW CS Student AS SELECT 学号 姓名 年龄 FROM 学生 WHERE 系别 计算机系 WITH CHECK OPTION 备课 例 把成绩和课程通过课程号相等连接成一个视图 CREATE VIEW V3 AS SELECT 学号 课程名 成绩 from 课程 成绩 where 课程 课号 成绩 课号 例 在学生 成绩和课程通过课程号和学号相等连接成一个视图 create vrew V4 as select 姓名 课程名 成绩 from 课程 C 选课 G 学生 S where C 课号 G 课号 and G 学号 S 学号 例 定义一个反映学生出生年份的视图 CREATE VIEW BT S Sno Sname Sbirth AS SELECT Sno Sname 2009 Sage FROM Student T SQL中的变量 局部变量 用户自定义的变量 仅限于在定义它的程序内部使用 特点 一般出现在批处理 游标 存储过程 触发器和事件定义中 必须先定义后使用 默认值为null 在程序中使用局部变量之前应该先赋值 局部变量定义方法 declare 局部变量名1 数据类型名1 局部变量名2 数据类型名2 例 定义一个int类型变量X declare x int 例 定义一个float型变量y declare y float 局部变量赋值方法 1 select 局部变量名 功能 一次可以给多个变量赋值 例 select x 100 y 5 5 2 set 局部变量名 功能 一次可以只能给一个变量赋值 例 set x 100 set y 5 5 局部变量值输出 1 select 局部变量名 功能 一次可以输出多个变量赋值或表达式值 例 select x y 100 2 print 局部变量名 功能 一次只能输出一个局部变量或一个表达式值 例 print x 5 print y 实例 局部变量应用 declare number varchar 10 name varchar 20 sex char 2 declare birthday datetime declare height float declare telphone varchar 20 select number 090207101 name 路人甲 sex 男 birthday 1989 2 5 set height 1 74 set telphoneselect number name sex birthday height telphone select number 学号 name 姓名 sex 性别 birthday 出生日期 height 身高 telphone 电话 如使用print输出 print 学号 number char 10 姓名 name char 10 性别 sex print 出生日期 print birthday print 身高 print height print 电话 telphone 全局变量 属于系统变量 是在T SQL内部使用的变量 任何程序均可使用 特点 1 全局变量反映的是SQL服务器当前活动状态的信息 2 全局变量不需要定义 可直接在程序中使用 3 全局变量不可由用户赋值 其值的大小和用户程序的运行状态有关 4 全局变量一律以 开头 常用全局变量 ERROR 返回执行上一条T SQL语句所返回的错误号 返回0 表示操作成功 ROWCOUNT 返回执行上一条T SQL语句所影响的行数 FETCH STATUS 返回执行上一次使用游标FETCH 读取 操作返回的状态 返回0表示操作成功 返回 1表示操作失败或者已经超出游标所能操作的数据行范围 返回 2表示返回的值已经丢失 VERSION返回SQL SERVER当前安装的日期 版本和处理器类型 SERVERNAME返回运行SQL SERVER的本地服务器名称 实例 全局变量使用 declare a int b int count int select from 成绩表where 成绩 90 set a rowcount select from 成绩表where 成绩 70 set b rowcount update 成绩表set 成绩 成绩 5 set count rowcount 原行数赋值给局部变量 insert into 成绩表values 06508009 PHY45211L 89 插入一行 if error 0 插入成功 select a 90分以上行数 b 70分以下行数 count 原行数 count