Microsoft PowerPoint - 第四章 SQL语言06

Transcription

1 第四章 SQL 语言 -- 本章内容 SQL 语言的基本特征 SQL 语言的基本特征 SQL 数据定义功能索引的建立和删除 SQL 的查询功能 SQL 数据操纵功能嵌入式 SQL 动态 SQL 访问数据库 一体化的特点 两种使用方式, 统一的语法格式 高度非过程化 语言简洁 易学易用 SQL 语言也支持关系数据库三级模式体系结构 外模式 : 视图 + 一些基本表 模式 : 基本表 内模式 : 存储文件 ( 逻辑结构 ) 第四章 SQL 语言 组成部分 数据定义语言 DDL(Data Definition Language) 定义关系模式 删除 / 修改关系模式 交互式数据操纵语言 DML(Data Manipulation Language) 查询 插入 删除 修改 索引与视图定义功能一体化 嵌入式 SQL 的特点 事务控制功能 -- 第 8 章 权限管理 第 9 章 完整性 第 10 章 数据库三级模式 应用 A 应用 B 应用 C 应用 D 应用 E 外模式 1 外模式 2 外模式 n 视图 模式 / 外模式映射 模式 表 内模式 / 模式映射 内模式 索引 数据库 样板模式 Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Boats Bid bname color 101 Interlake blue 样板数据库 102 Interlake red 103 Clipper green Sailors 104 Marine red Sid sname rating age Reserves sid bid day 22 Dustin /10/98 29 Brutus /10/98 31 Lubber /8/98 32 Andy /7/98 58 Rusty /10/98 64 Horatio /6/98 71 Zorba /12/98 74 Horatio /5/98 85 Art /8/98 95 Bob /8/98

2 SQL 数据定义功能 创建表 Sailors 列级约束 Create table Sailors Create table Sailors ( sid char(11), ( sid char(11) primary key, sname char(20), sname char(20), rating int, rating int, age int, age int) primary key ( sid ) ) Sailors 表级约束 sid sname rating age SQL 数据定义功能 创建表 Boats Create table Boats ( bid char(10), bname char(20), color char(10), primary key ( bid ) ) 表级约束 Boats bid bname color SQL 数据定义功能 创建表 Reserves Reserves Create table Reserves sid bid day ( sid char(11), bid char(10), 表级约束 day datetime, primary key ( sid, bid ), foreign key(sid) references Sailors, foreign key(bid) references Boats ) 数据类型 ( 教材 p89) 基本表的修改与删除 修改 Alter table Sailors add sex char(3) 删除 Drop table Sailors 把一个基本表的定义连同表上的所有记录 索引以及由此基本表导出的所有视图都删除, 并释放相应的存储空间 应用应用应用应用 索引 外模式 外模式 外模式 视图 模式内模式 表索引 数据库 当关系很大时, 通过扫描所有关系中的所有元组来找出符合条件的元组 代价太高 例如 : 选择 IS 系年龄大于 22 岁的学生 Student 关系中可能存在 5000 个元组, 只有 200 个元组是大于 22 岁的 代价大的方法 : 逐个评价 5000 个元组 改进的方法 : 直接评价 200 个元组 索引 首先 :Students 记录按 Sage 来排序 简单的改进方法 : 二分法查找文件中的记录 问题 : 如果文件很大, 二分法查找仍会带来巨大的执行代价 如何改善? 建立一个索引文件

3 索引 索引 索引文件 : 索引页的格式 一级索引结构 age P0 K1 P1 K2 P2 Kn Pn P0 16 P1 22 P2 25 Pn 索引文件 一级索引结构 P0 K1 P1 K2 P2 Kn Pn 索引文件 Page 1 Page 2 Page 3 Page n 数据文件 Page 1 Page 2 Page 3 Page n 数据文件 因为索引文件比数据文件小得多 在索引文件上进行二分法搜索比在数据文件上搜索要快得多 索引的优点 关系属性 A 上的索引可以提高查找在 A 上具有某个特性值的元组的效率 索引 --index 索引可以加快查找 索引的代价 ( 时间 空间 ) 索引的维护和使用 ( 系统 ) SQL 支持用户建立索引 索引建立策略 如果一个 ( 组 ) 属性经常在查询条件中出现 如果一个属性经常作为 min 或 max 等聚集函数的参数 在连接属性上 索引 创建索引 唯一索引 :unique 每个索引值只对应一个唯一的数据记录 聚簇索引 :cluster 索引项的顺序和表中数据记录的物理顺序一致 学号 年龄 学生 王 18 CS 李 17 IS 张 17 MA 赵 18 CS 学生 张 17 MA 李 17 IS 王 18 CS 赵 18 CS 给关系 Students 的 Sage 属性创建一个索引 CREATE INDEX ageindex ON Students(Sage); DBMS 允许对多个属性创建一个索引 CREATE INDEX keyindex ON SC(sno, cno);

4 索引的建立 在表 Sailors 的属性 sid 上建立一个唯一索引 create unique index in_u_sid on Sailors( sid ) 在表 Boats 的属性 bid 上建立一个聚簇索引 create cluster index in_c_bid on Boats( bid ) 创建索引 在表 Sailors 的属性 sname 上建立一个索引 create index xsname on Sailors( sname ) 在表 Boats 的属性 bname 上建立一个索引 create index xbname on Boats( bname ) 在表 Reverse 的属性 (bid,sid) 上建立一个索引 ( 组合索引 ) create index xbidsid on Reverse( bid asc, sid desc ) asc 为升序,desc 为降序, 缺省为升序 应用应用应用应用 索引的删除 索引的删除 : Drop index 索引名 Drop index xsname 索引可以加快查找, 但不是索引越多越好 对某个属性使用索引能极大地提高对该属性上值的检索效率, 使用到该属性时, 可以加快连接操作 对某个属性使用索引会使得对关系的插入 删除 修改变得复杂和费时 访问和更新索引结构本身也需要磁盘操作 SQL 的查询功能 外模式 外模式 外模式 视图 模式内模式 表索引 SQL 查询语句的基本结构数据库 SELECT [DISTINCT] 目标列 FROM 基本表 ( 视图 ) 范围变量名,. [WHERE 条件表达式 ] [Group by 列名 1 [having 分组表达式 ] ] [Order By 列名 2 asc desc ]; 基本结构 样板模式 三个子句 Select: 对应关系代数的投影运算, 列出查询结果中的属性 From: 对应关系代数的笛卡尔集运算, 列出表达式求值中需扫描的关系 Where: 对应关系代数的选择谓词, 包括一个作用在 from 子句中关系属性上的谓词 属性 Select A1, A2,, An π A1,A2,..,An (σ P (r1 r2 rm)) From r1, r2,, rm [Where P] 关系谓词 用 SQL 创建以上三个表

5 查询内容仅涉及一个表 例子 : 查询全体学生的学号和姓名 select Sno, Sname 顺序无关性 ; 例子 : 查询全体学生的基本情况 select * ; 例子 : 查询全体学生的姓名及出生年份 select Sname, 2009-Sage ; 例子 : 查询全体学生的姓名 出生年份和所在系, 要求用小写字母表示所有系名 select Sname, Year of birth:, 2009-Sage, islower(sdept) 示意答案 ; 例子 : 查询全体学生的姓名 出生年份 Sname Year of birth: 2009-Sage islower(sdept) 李勇 Year of birth: 1976 cs 刘辰 Year of birth: 1977 is 王敏 Year of birth: 1978 ma 张立 Year of birth: 1978 is 例子 : 查询全体学生的姓名 出生年份 用户可以通过指定别名来改变查询结果的列标题 select Sname NAME, Year of birth BIRTH, 2009-Sage BIRTHDAY, islower(sdept) DEPARTMENT ; NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of birth: 1976 cs 刘辰 Year of birth: 1977 is 王敏 Year of birth: 1978 ma 张立 Year of birth: 1978 is

6 消除取值重复的行 查询选修了课程的学生学号 select Sno ; 消除重复行 select distinct Sno ; Sno Sno 查询满足条件的元组 常用查询条件 查询条件 谓词 比较 =, >, <, >=, <=,!=, <>,!>,!< not + 上述比较运算符 ( 例如 not=) 确定范围 between and, not between and 确定集合 in, not in 字符匹配 like, not like 空值 is null, is not null 多重条件 and, or 一般查询 查询年龄在 20 岁以下的学生姓名及其年龄 select Sname, Sage where Sage < 20; 查询年龄在 20~23 岁之间的学生姓名 所在系, 年龄 select Sname, Sdept, Sage where Sage between 20 and 23; 查询信息系 数学系和计算机系学生的姓名和性别 select Sname, Ssex where Sdept in ( IS, MA, CS ); 查询所有姓刘的学生的姓名 学号和性别 select Sname, Sno, Ssex where Sname like 刘 % ; 查询姓欧阳且全名为三个汉字的学生姓名 select Sname where Sname like 欧阳 ; 查询 DB_Design 课程号和学分 select Cno, Ccredit from Courses where Cname like DB\_Design ; 查询以 C 开始和结束, 并且有至少 3 个字母组成的系的学生的学号 select Sno where Sdept like C_%C ; 查询缺考学生的学号和课号 select Sno, Cno where Grade is null;

7 查询计算机系年龄在 20 岁以下的学生的姓名 select Sname where Sdept= CS and Sage<20; 查询信息系 数学系和计算机系学生的姓名和性别 select Sname, Ssex where Sdept= CS or Sdept= IS or Sdept= MA 对查询结果排序 例子 : 查询选修了 3 号课程的学生学号和成绩, 要求查询结果按成绩降序排列 select Sno, Grade where Cno= 3 order by Grade desc; 例子 : 查询全体学生的情况, 查询结果按系号升序排列, 同一系的学生按年龄降序排列 select * order by Sdept asc, Sage desc; 使用集函数 例子 : 查询学生总数 select count(*) ; 例子 : 查询选修了课程的学生人数 select count (distinct Sno) ; 求选修 1 号课程的学生的平均成绩 select avg(grade) where Cno= 1 ; SQL 提供的主要集函数 ( 聚合函数 ) count( [distinct all] *) count( [distinct all] < 列名 >) sum( [distinct all] < 列名 >) avg( [distinct all] < 列名 >) max ( [distinct all] < 列名 >) min ( [distinct all] < 列名 >) 对查询结果分组 : 将查询结果按一列或多列值分组, 目的是将集函数作用到组上 ( 例如 : 小计 ) 例子 : 求每门课的课号及其选课人数 select Cno, count(sno) group by Cno; 例子 : 求选课人数超过 10 人的课程号及其人数 select Cno, count(sno) group by Cno 组内条件 having count(sno)>10;

8 多个关系上的查询 集合操作 多个 SQL 的查询结果可以进行集合操作 多关系的连接操作 多个关系上的查询 集合操作主要有 : union intersect 和 except 例子 : 查询计算机系的学生以及年龄不大于 19 岁的学生 select * where Sdept= CS union select * where Sage <= 19; 不用 union, 如何实现该查询? 多个关系上的查询 例子 : 查询计算机系的学生与年龄不大于 19 岁的学生的交集 select * where Sdept= CS intersect select * where Sage<=19; 不用 intersect, 如何实现该查询? 多个关系上的查询 例子 : 查询计算机系的学生与年龄不大于 19 岁的学生的差集 select * where Sdept= CS except select * where Sage<=19; 不用 except, 如何实现该查询? 多个关系上的查询 关于消除重复元组的讨论 在 select 中 缺省情况下 : 保留重复元组 使用 distinct 消除重复元组 在 union, intersect, except 中 缺省情况下 : 消除重复元组 在 union, intersect, 或 except 后加上 all, 保留重复元组 多表连接查询 等值与非等值连接查询 查询每个学生及其选修课的情况 select Students.*, SC.*, SC where Students.Sno=SC.Sno; Students.Sno Sname SC.Sno Cno 李勇

9 多表连接查询 自然连接 select Students.*, Cno, Grade, SC where Students.Sno=SC.Sno; 自身连接 查询每门课的课号及其间接先修课的课号 C1: Courses ( Cno, Cname, Cpno, Credit) C2: Courses ( Cno, Cname, Cpno, Credit) select C1.Cno, C2.Cpno from Courses C1, Courses C2 where C1.Cpno=C2.Cno; 多表连接查询 自身连接 : 查询每门课的课号及其间接先修课的课号 C1 多表连接查询 自身连接 查询每门课的课号及其间接先修课的课号 ( 间接距离为 1) C1: Courses ( Cno, Cname, Cpno, Credit) C2: Courses ( Cno, Cname, Cpno, Credit) 连接结果 select C1.Cno, C2.Cpno from Courses C1, Courses C2 where C1.Cpno=C2.Cno; C2 C1.Cpno=C2.Cno 多表连接查询 外连接 : 在一般连接中, 只有满足条件的元组才可以作为结果输出, 外连接则不同 ( 左 右外连接 ) 查询学校内学生及雇员的情况 ( 内 ) 连接 既是学生, 又是雇员 左外连接 是学生, 可以不是雇员 右外连接 可以不是学生, 但, 是雇员 全外连接 可以不是学生, 可以不是雇员 Students(name, dept) Employee(name, salary) 多表连接查询 Students(name, dept) Employee(name, salary) 多表连接查询 Students(name, dept) Employee(name, salary) name 李勇 王敏 dept IS CS name 李勇 王一 salary 左外连接 : 是学生, 可以不是雇员 From 子句的写法 Students LEFT OUTER JOIN Employee name 李勇 王敏 dept IS CS salary 200 null name 李勇 王敏 dept IS CS name 李勇 王一 salary 全外连接 : 可以不是学生, 可以不是雇员 From 子句的写法 Students FULL OUTER JOIN Employee name 李勇 王敏 王一 dept IS CS null salary 200 null 500

10 多表连接查询 多表连接查询 左外连接 思考 :Student 和 SC 之间可以做哪种外连接? 多表连接查询 复合条件连接 查询选修 2 号课程且成绩在 90 分以上的所有学生 select Students.*, SC where Students.Sno=SC.Sno and Cno= 2 and Grade>90; 多表连接查询 复合条件连接 查询每个学生的学号 姓名 选修的课程名和成绩 select Students.Sno, Sname, Cname, Grade, Courses, SC where Students.Sno=SC.Sno and SC.Cno=Courses.Cno; 嵌套查询 查询块 (SELECT-FROM-WHERE) 一个查询块可以放在另一个查询块的 where 子句或 having 短语的条件中 父查询和子查询 子查询中不可以使用 order by 子句,order by 只能对最终结果排序 多个简单查询构造一个复杂的查询 相关子查询和不相关子查询 不相关子查询 带有 in 的子查询 : 子查询结果为多值 查询与刘晨在一个系学习的学生 要求 : 不使用连接查询 select * where Sdept in (select Sdept where Sname= 刘晨 ); 不相关子查询求解方法由里向外

11 不相关子查询 带有 in 的子查询 : 子查询结果为多值 查询与刘晨在一个系学习的学生 要求 : 使用连接查询 不相关子查询 带有 in 的子查询 : 子查询结果为多值 查询与刘晨在一个系学习的学生 要求 : 使用连接查询 select S2.* S1, Students S2, where S1.Sname = 刘晨 and S1.Sdept = S2.Sdept; 有些嵌套查询可以用连接查询代替 不相关子查询 查询选修了课程名为信息系统的学生学号和姓名 select Sno, Sname where Sno in (select Sno where Cno in (select Cno from Courses where Cname= 信息系统 )); 不相关子查询 带有比较运算符的子查询 : 子查询结果为单值的情况 例子 : 查询与刘晨在一个系学习的学生 select * where Sdept = (select Sdept where Sname= 刘晨 ); 注意 : 不能用 = 表示值和表间的等值判断 不相关子查询 带有 any 或 all 的子查询 : 必须同比较运算符同用 θ any θ all 查询比信息系某一学生年龄小的其他系学生姓名和年龄 select Sname, Sage where Sage <any (select Sage where Sdept= IS ) and Sdept!= IS ; 不相关子查询 用集函数实现上述查询 ( 效率比 any 和 all 高 ) select Sname, Sage where Sage < (select max(sage) where Sdept= IS ) and Sdept!= IS ; 查询比信息系所有学生年龄都小的其他系的学生姓名和年龄 ( 思考题 ) Any 和 all 和集函数的对应关系参见教材

12 多表连接查询 自身连接 查询每门课的课号及其间接先修课的课号 C1: Courses ( Cno, Cname, Cpno, Credit) C2: Courses ( Cno, Cname, Cpno, Credit) select C1.Cno, C2.Cpno from Courses C1, Courses C2 where C1.Cpno=C2.Cno; 查询每门课的课号及其直接先修课的课号 Select Cno, Cpno from Courses 嵌套查询 查询块 一个查询块可以放在另一个查询块的 where 子句或 having 短语的条件中 父查询和子查询 子查询中不可以使用 order by 子句,order by 只能对最终结果排序 多个简单查询构造一个复杂的查询 不相关子查询 相关子查询 相关子查询 带有 exists 的子查询 : 子查询结果为 true 或 false 查询所有选修了 1 号课程的学生姓名 select Sname where exists (select * where Sno=Students.Sno and Cno= 1 ); 相关子查询求解方法由外向里 ( 样板数据库 ) 思考 : 查询没有选修 1 号课程的学生姓名 相关子查询 带有 exists 的子查询 : 子查询结果为 true 或 false 查询没有选修了 1 号课程的学生姓名 select Sname where not exists (select * where Sno=Students.Sno and Cno= 1 ); 相关子查询求解方法由外向里 ( 样板数据库 ) 相关子查询 ( 思考题 ) 带有 exists 的子查询 : 子查询结果为 true 或 false 查询没有选修了 1 号课程的学生姓名 select Sname where exists (select * where Sno=Students.Sno and Cno!= 1 ); 上述查询是否正确? 相关子查询 分析 : 求学生 x, 对于任意一门课 y, P: 学生 x 选修了 y, 则 : y(p) ( y( P)) SQL 中没有全称量词, 但是 x(p) ( x ( P) ) 例子 : 查询选修了全部课程的学生姓名 select Sname where not exists (select * from Course where not exists (select * where Sno=Students.Sno and Cno=Course.Cno)); Sno 95001(x) Sname 王一李二 Sno 当 x=95001 时, 找不到一个课程, 使 P 不成立 Cno C1 C2 C2 grade Cno C1(y) C2 x: Students.Sno y: Courses.Cno Cname DBMS O.S.

13 相关子查询 SQL 中没有逻辑蕴含运算, 但是 (p q) p q 例子 : 查询至少选修了学生 选修的全部课程的学生学号分析 : 查询学号 u, 对于任意一门课 v, 只要 选修, 则 u 必然选修, 如果 : p: 选修 v q: u 选修 v 则 : v(p q) ( v( (p q))) ( v( ( p q))) ( v( ( p q))) v(p q) 相关子查询 例子 : 查询至少选修了学生 选修的全部课程的学生学号 p: 选修 v, q: u 选修 v v(p q) v(p q) select Sno X where not exists (select * Y X Y Z Sno 95001(u) Cno C1 C2 (v) C2 grade where Sno= and not exists (select * Z where Sno=X.Sno and Cno=Y.Cno)); u: X.Sno v: Y.Cno 习题 查询租用过 103 号船的船员姓名 习题 查找租用过船只的船员编号 问题 1: 是否需要在 Select 子句中添加 DISTINCT? 问题 2: 如果将 Select 子句中的 S.sid 换成 S.sname, 是否需要添加 DISTINCT? 问题 1: 若不加 DISTINCT, 则结果中存在重复元组, 结果中的原则个数等于 Reserves 的元组个数问题 2: 与问题 1 的答案相同习题 ( 嵌套查询 ) Sailors Reserves Sailors.Sid θ Reserves.Sid Sailors.Sid sname rating age Reserves.Sid bid day 22 Dustin /10/98 22 Dustin /10/98 64 Horatio /5/98 查找租用过 103 号船只的水手的名字 不相关子查询 Sailors Reserves Sid sname rating age Sid bid day 22 Dustin /10/98 29 Brutus /10/98 58 Rusty /5/98 64 Horatio 7 35 问题 3: 不使用嵌套查询该怎麽做?

14 嵌套查询 相关子查询 嵌套查询 查找租用过 103 号船只的水手的名字 查找比 Harotio 级别高的水手 习题 查找 rating>7 且年龄 >25 的水手编号 复合条件 Select S.sid from Sailors S where S.rating>7 and S.age>25 使用 Intersect 来代替 and Select S.sid from Sailors S where s.rating>7 intersect select S2.sid from Sailors S2 where S2.age>25 使用 In 查询来改写 Intersect 查询 查找 rating>7 且年龄 >25 的水手编号 不相关子查询 Select S.sid from Sailors S where S.rating > 7 And S.sid in ( select S2.sid from Sailors S2 where S2.age > 25 ) 查找租用过红船或绿船的水手编号 可以使用 Union 来代替 or 运算 习题 查找租用过红船和绿船的水手编号 错误的写法 Select S.sname from Sailors S, Reverses R, Boats B where S.sid=R.sid and R.bid=B.bid and B.color= red and B.color= green 正确的写法 Select S.sname from Sailors S, Reverses R1, Boats B1, Reverses R2, Boats B2 where S.sid=R1.sid and R1.bid=B1.bid and S.sid=R2.sid and R2.bid=B2.bid and B1.color= red and B2.color= green

15 使用 In 查询来改写 Intersect 查询 查找租用过红船和绿船的水手编号 Intersect 查询 Select S.sid from Sailors S, Reverses, R Boats B where S.sid=R.bid and R.bid=B.bid and B.color= red intersect select S2.sid from Sailors S2, Reverses R2, Boats B2 where S2.sid=R2.bid and R2.bid=B2.bid 可以相同 and B2.color= green 思考 : 第二部分的 from 子句和第一部分的 from 子句是否可以相同? 使用 In 查询来改写 Intersect 查询 查找租用过红船和绿船的水手编号 In 查询 ( 不相关子查询 ) 思考 : 父子两块查询中的 from 子句是否可以相同? 查找最年长的水手的年龄和名字 在 18 岁以上水手中, 对于每个 rating 级别中最少有两个水手以上的组中最年轻水手的年龄 在 18 岁以上水手中, 对于每个 rating 级别中最少有两个水手以上的组中最年轻水手的年龄 在 18 岁以上水手中, 对于每个 rating 级别中最少有两个水手以上的组中最年轻水手的年龄 ( 子查询 ) Having 子句中也可以包含子查询 Having 子句可以为 Count(*) > 1

16 问题 4: 查询每条船被租用的次数 问题 5: 如果 Reserves 引用 Sailors, 则去掉 Sailors 和与 S.sid 相关的条件不会影响查询结果 查找每条红色船只被租用的次数 关于 group by 的练习 求选修课程记录中, 成绩最好和最差的学生的姓名, 性别和成绩 在三个关系连接之上的一个分组操作 问题 4: 如果去掉颜色选择条件, 查询结果如何? 问题 5: 如果去掉 Sailors 和与 S.sid 相关的条件, 情况会怎样? 用嵌套查询可以解决此问题 关于 group by 的练习 求选修课程记录中, 成绩最好和最差的学生的姓名, 性别和成绩 关于 group by 的练习 求选修课程记录中, 男生里成绩最好和最差的学生的性别和成绩, 以及女生里成绩最好和最差的学生的性别和成绩 嵌套查询 关于 group by 的练习 求选修课程记录中, 男生里成绩最好和最差的学生的性别和成绩, 以及女生里成绩最好和最差的学生的性别和成绩 关于 group by 的练习 求选修课程记录中, 男生里成绩最好和最差的学生的性别和成绩, 以及女生里成绩最好和最差的学生的性别和成绩

17 关于 group by 的练习 求选修课程记录中, 男生里成绩最好和最差的学生的姓名, 性别和成绩, 以及女生里成绩最好和最差的学生的姓名, 性别和成绩 SQL 数据操纵功能 ( 更新 ) 数据插入 数据删除 数据修改 数据插入 单记录插入 insert into Students values( 95020, 陈东, 男, 18, IS ); insert into SC(Sno, Cno) values( 95020', 1'); 多记录插入 ( 插入子查询结果 ) 假定数据库中有表 : deptage(sdept char(15), Avgage smallint) 则可以将 Students 中的部分数据导入到 deptage insert into deptage ( sdept, avgage ) select Sdept, avg( Sage ) group by Sdept; 数据删除 delete where Sno = '95019 ; delete 带有子查询的删除 delete where 'CS' = ( select Sdept delete where 王立 = (select Sname where Students.Sno=SC.Sno) where Students.Sno = SC.Sno ); 思考 : 王立同学因病休学, 请将王立的选课记录删去 数据修改 update Students set Sage = 22 where Sno= ; update Students set Sage = Sage + 1; 带有子查询的修改 update SC set Grade = 0 where 'MA' = ( select Sdept where Sno=SC.Sno ); 更新操作与数据库的一致性 更新操作只对一个表操作, 但实际中可能 : 例如 : 请删除学号为 的学生, 隐含将其所有选课记录删去 delete from students where Sno= ; delete where Sno= ; 如果建表时, 说明 SC 参照 Students 存在, 则此删除失败

18 应用应用应用应用 更新操作与数据库的一致性 数据库提供事务概念处理这类问题 如果建表时, 说明 SC 参照 Students 存在, 且说明 on delete cascade, 则只要 delete from students where Sno= ; 便可将 的选课记录全部删去 视图 视图是用户可以看见的 ( 虚 ) 关系, 它不是逻辑模型的一部分 视图是从一个或几个基本表 ( 视图 ) 导出的表 视图是虚表 : 数据库中只存放视图的定义 ( 存放于数据字典中 ), 不存放视图对应的数据 视图也称动态窗口 : 视图可以和基本表一样被查询, 被删除 视图的更新是有一定限制的 可以基于视图, 定义新的视图 外模式 外模式 外模式 视图 模式内模式 表索引 数据库 视图定义 基于关系建立视图例子 : 建立信息系学生的视图 create view IS_S as select *? 视图的属性名 where Sdept= IS ; create view IS_Students(No, Name, Sex) as select Sno, Sname, Ssex where Sdept= IS ; 行列子集视图 : 从单个基本表导出, 去掉了一些行和列, 但保留了码 视图定义 视图的属性或者全部指定, 或者全部不指定 换名 某个目标列是集函数或列表达式 多表连接时有几个同名列需要区分 例子 : 将学生的学号及平均成绩定义为一个视图 create view avggrade(sno, avgg) as 虚拟列 select Sno, avg(grade) group by Sno; 视图定义 基于视图建立视图例子 : 建立信息系选修了 1 号课程且成绩在 90 分以上的学生的视图 Create view IS_Top_Students(Sno, Sname, Grade) as select IS_Students.Sno, Sname, Grade from IS_Students, SC where IS_Students.Sno=SC.Sno and Cno= 1 and Grade>90; 视图定义 with check option 子句对视图进行 UPDATE INSERT DELETE 时要保证更新 插入 删除的行满足视图定义的谓词条件 create view IS_S as select * where Sdept= IS with check option;

19 删除视图 语句格式 :DROP VIEW < 视图名 >; 将视图定义从数据字典中删除, 且由该视图导出的视图均失效, 要从数据字典中将失效的视图都删除, 以免用户使用错误 例如 : DROP VIEW IS_Student; 思考 : 若删除了基本表, 由基本表导出的视图又如何? 查询视图 视图查询执行过程 : 把定义中的子查询和用户查询结合起来, 转化成等价的对基本表的查询 例子 : 在信息系的学生中找到年龄小于 20 岁的学生 select * from IS_S where Sage<20; 转换后的查询为 ( 系统完成 ): select * where Sdept= IS and Sage<20; 查询视图 查询信息系选修了 1 号课程的学生姓名和成绩 select Sname, Grade from IS_Students, SC where IS_Students.Sno=SC.Sno and Cno= 1 ; 查询视图 对视图的查询有些是不能直接进行转换的 例子 : 在 avggrade 视图中, 查询平均成绩在 90 分以上的学生学号和平均成绩 select * from avggrade where avgg>=90; 错误转换 : select Sno, avg(grade) where avg(grade)>=90 group by Sno; create view avggrade(sno, avgg) as select Sno, avg(grade) group by Sno 查询视图 对视图的查询有些是不能直接进行转换的 例子 : 在 avggrade 视图中, 查询平均成绩在 90 分以上的学生学号和平均成绩 select * from avggrade where avgg>=90; 正确转换 : select Sno, avg(grade) group by Sno having avg(grade)>=90; 视图的作用 简化用户的操作 视图使用户能以多种角度看待同一数据 视图可以对机密数据提供一定的安全保护 create view avggrade(sno, avgg) as select Sno, avg(grade) group by Sno

20 视图的作用 视图对数据库重构提供了一定程度的逻辑独立性 例如 : Students(Sno, Sname, Ssex, Sage, Sdept) 分为 : S1(Sno, Sname, Sage) S2(Sno, Ssex, Sdept) 但在 Students 上已有开发了多个应用, 为了达到不改变应用程序的目的, 可以建视图 Students: create view Students(Sno, Sname, Ssex, Sage, Sdept) as select S1.Sno, S1.Sname, S2.Ssex, S1.Sage, S2.Sdept from S1, S2 where S1.Sno=S2.Sno; 视图更新 对视图更新最终转换成对基本表的更新 将信息系学生学号为 的学生姓名改为 刘辰 update IS_Students set Sname= 刘辰 where Sno= ; 通过视图 IS_Students 插入一信息系的学生记录 insert into IS_Students values( 95029, 赵新, 女 ); insert into IS_S values( 95029, 赵新, 女,20, MA ) with check option 子句 视图更新 视图更新 删除信息系学号为 的记录 delete from IS_Students where Sno= ; 行列子集视图是可以更新的, DB2 例子参见教材 由两个以上基本表导出的视图不允许更新 ; 来自于字段表达式或常数的视图字段不允许进行 insert 和 update 操作, 但允许 delete 操作 ; 不允许更新集函数视图字段 ; 不允许更新含有 group by 或 distinct 视图 ; 嵌套查询内层的表也是导出该视图的基本表时, 不允许更新 ; 不允许更新的视图上定义的视图也不允许更新 嵌套查询内层的表也是导出该视图的基本表 嵌入式 SQL 例如 :CREATE VIEW GOOD_SC AS SELECT Sno,Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); DBMS 程序 DBMS

21 嵌入式 SQL 实现复杂应用 : 高级语言计算完备性 +SQL 语言的高效处理 主语言 ( 宿主语言 ), 嵌入式 SQL 几点需要注意的问题 在主语言中,SQL 语句必须被清楚地标识 如何在两种语句间传递数据类型 SQL 语句的查询结果是一个集合, 而高级语言通常不是为集合而定义的 嵌入式 SQL 嵌入式 SQL 的处理过程 : 数据库厂商提供 预编译器 修改和扩充主语言编译器 关系 DBMS 一般提供一批用宿主语言编写的 SQL 函数, 组成 SQL 函数库, 供应用程序调用 DBMS 的各种功能 宿主语言 + 嵌套 SQL 预编译器宿主语言 + SQL 函数调用宿主语言编译器目标代码程序链接 SQL 库可执行代码 嵌入式 SQL 嵌入式 SQL 在主语言中,SQL 语句必须被清楚地标识 嵌入式 SQL 的一般格式 所有 SQL 语句必须加前缀 EXEC SQL 和语句结束标志 (C 语言中一般是 ; 号 ) EXEC SQL select Sno where Cno= 1 ; 嵌入式 SQL 语句可分为 : 可执行语句和说明性语句 SQL 函数库实际上是 DBMS 向应用程序提供的一种接口, 称为调用级接口 (CLI) 预编译器将嵌入 SQL 语句编译成宿主语言对 SQL 函数的调用 95 年 ISO 公布了一个 CLI 标准, 作为 SQL-92 的一个附件, 称为 SQL-92/CLI 或 CLI95, 新的 RDBMS 都支持该标准, 标准中只规定了函数的定义 嵌入式 SQL 如何在两种语句间传递数据类型, 实现嵌入式 SQL 语句与主语言语句之间的通信 公共数据结构 -- SQL 通信区 (SQLCA) SQL 通信区 (SQLCA): 每个 SQL 语句执行后, 系统要反馈给应用程序若干信息, 这些信息存于 SQLCA 中 应用程序从 SQLCA 中取出这些状态信息, 并据此决定应用程序的进一步执行 SQLCA 是一个数据结构, 由系统定义, 程序员只需在程序中加入 EXEC SQL INCLUDE SQLCA; 便可以引用其中的分量 嵌入式 SQL 如何在两种语句间传递数据类型, 实现嵌入式 SQL 语句与主语言语句之间的通信 公共数据结构 -- 分量 SQLCODE 分量 SQLCODE 是一整型变量, 系统将每个 SQL 语句执行成功与否的结果赋给该变量 0: 成功 正数 : 已执行但有异常 ( 其中 100 表示没取到数 ) 负数 : 表示 SQL 语句因某些错误而未被执行, 负数的值表示错误的类别

22 嵌入式 SQL 主变量 ( 宿主变量 ) 嵌入式 SQL 使用主语言的变量输入 / 输出数据, 主变量的说明 ( 可以和属性同名 ) 例子 : EXEC SQL BEGIN DECLARE SECTION; char Sno[7], char givensno[7], char Cno char[2], float Grade, short GradeIn; EXEC SQL END DECLARE SECTION; 嵌入式 SQL 主变量的使用 在 SQL 中使用主变量时, 主变量前一定加 : 例子 : EXEC SQL select Sno, Cno, Grade into :Sno, :Cno, :Grade where Sno=:givenSno; 嵌入式 SQL 指示变量 ( 主变量的一种 ) 一个主变量可以附带一个指示变量, 指示变量也必须说明, 使用时放在主变量之后 例子 : EXEC SQL select Sno, Cno, Grade into :Sno, :Cno, :Grade :GradeIn where Sno=:givenSno; 指示主变量 Grade 的取值情况 ( 是否为空值 ), 当 GradeIn<0 时, 表明成绩为空值 指示变量不允许用在 where 子句中 嵌入式 SQL SQL 语句的查询结果是一个集合, 而高级语言通常不是为集合而定义的 游标 SQL 与主语言具有不同的处理方式, 用游标协调 游标区是系统为用户开设的一个数据缓冲区, 存放 SQL 语句的执行结果, 每个游标区有一个名字 游标的说明和使用 嵌入式 SQL EXEC SQL DECLARE cursor1 CURSOR FOR select Sno, Cno, Grade into :Sno, :Cno, :Grade ; EXEC SQL OPEN cursor1; while(1) { EXEC SQL FETCH cursor1; if(sqlca.sqlcode)<>0 // 没有取到结果 break; // 跳出循环 printf( Sno:%s, Cno:%s, Grade:%d, Sno, Cno, Grade); } EXEC SQL CLOSE cursor1; 不使用游标的 SQL 语句 不使用游标的 sql 语句 Insert 语句 update( 不带 current 子句 ) 语句特点 delete( 不带 current 子句 ) 语句 查询结果为单记录的语句 例子 : 例子 1: 查询某个学生选修某门课程的成绩, 要求输出学号, 课号和成绩 exec sql include sqlca; exec sql begin declare section; char Sno[7], char givensno[7],

23 不使用游标的 SQL 语句 char Cno char[3], givencno char[3], float Grade, short GradeIn, exec sql end declare section; main() { printf( \n 请输入学号 : ) scanf( %s, Sno); printf( \n 请输入课号 : ) scanf( %s, Cno); 不使用游标的 SQL 语句 exec sql select Sno, Cno, Grade into :Sno, :Cno, :Grade: GradeIn where Sno=:givenSno and Cno=:givenCno; if(sqlca.sqlcode==0) // 执行正确 if (GradeIn<0) // 成绩为空 printf( \n%s, %s, %d, Sno, Cno); else printf( \n%s, %s, %d, Sno, Cno, Grade); else // 执行不正确 printf( \n 没有查到所需数据 \n ); } 不使用游标的 SQL 语句 例子 2: 将学生的年龄增加 1 岁 exec sql update Students set Sage=Sage+1; 例子 3: 将学生的 1 号课成绩增加指定的分数, 要求从键盘上输入增加的值 exec sql update SC set Grade=Grade+:Raise where Cno= 1 ; 例子 4: 将学生的 1 号课成绩置空 exec sql update SC set Grade = Grade + :Raise :GradeIn where Cno= 1 ; 不使用游标的 SQL 语句 例子 5: 某学生退学了, 将其所有选课记录删除, 要求输入学生的学号 exec sql delete where Sno=:givenSno; 例子 6: 某个学生选修了某门课程, 将有关记录插入 SC 表中, 要求从键盘输入学生的学号, 成绩为空分析 : 说明主变量, 将指示变量赋初值 -1 exec sql insert into SC values(:sno, :Cno, :Grade :GradeIn) 使用游标的 SQL 语句 使用游标的 SQL 语句查询结果为多条记录的 select 语句带有 current 的 update 语句带有 current 的 delete 语句 例子 1: 查询某个系全体学生的信息, 要求系名由键盘输入 exec sql include sqlca; exec sql begin declare section; char Sno[7], Sname[11], Ssex[3]; char givensdept[20]; short Sage; exec sql end declare section; 使用游标的 SQL 语句 main() { EXEC SQL DECLARE cursor2 CURSOR FOR select Sno, Sname, Ssex, Sage into :Sno, :Sname, :Ssex, :Sage where Sdept=:givenSdept; printf( \n 请输入要查询的系 : ); scanf( %s, givensdept); EXEC SQL OPEN cursor2; while(1) { EXEC SQL FETCH cursor2; if(sqlca.sqlcode)<>0 break; printf( \n%s, %s, %s, %d, Sno, Sname, Ssex, Sage); } EXEC SQL CLOSE cursor2; }

24 使用游标的 SQL 语句 例子 2: 查询某个系全体学生的信息, 要求系名由键盘输入, 并根据用户需要修改某些记录的年龄字段 exec sql include sqlca; exec sql begin declare section; char Sno[7], Sname[11], Ssex[3]; char givensdept[20]; short Sage; exec sql end declare section; 使用游标的 SQL 语句 main() { EXEC SQL DECLARE cursor3 CURSOR FOR select Sno, Sname, Ssex, Sage into :Sno, :Sname, :Ssex, :Sage where Sdept=:givenSdept for update of Sage; printf( \n 请输入要查询的系 : ); scanf( %s, gevensdept); EXEC SQL OPEN cursor3; while(1) { } 使用游标的 SQL 语句 EXEC SQL FETCH cursor3; if(sqlca.sqlcode)<>0 break; printf( \n%s, %s, %s, %d, Sno, Sname, Ssex, Sage); printf( \n 需要修改吗? ); scanf( %c &yn); if(yn= y or yn= Y ){ printf( \n 输入要修改的值 : ); Scanf( %d, givensage); exec sql update Students set Sage=:givenSage where current of cursor3; } } EXEC SQL CLOSE cursor3; 使用游标的 SQL 语句 带有 current 的删除语句处理同上 当游标中的 select 带有 union 或 order by 子句时, 不能使用带有 current 的 update 和 delete 当游标中的 select 定义了一个不可更新的视图时, 不能使用带有 current 的 update 和 delete 动态 SQL 静态 SQL: 编写程序时, SQL 语句是已知的, 只有一些主变量的值需要输入的 动态 SQL: 如果想查询的列不固定, 查询的条件不固定, 这样通用的查询程序必须通过动态 SQL 实现 动态 SQL 允许在程序运行过程中, 临时组装 SQL 语句, 主要有三种形式 : 语句可变 条件可变 数据库对象和查询条件都可变 例子 : 例子 1: 删除表 Students 中的某些学生记录 动态 SQL exec sql begin declare section; char sqlstring[200]; exec sql end declare section; char conditons[150]; main() { strcpy(sqlstring, delete where ); printf( enter search condition: ); scanf( %s, conditions); 以分号结尾 strcat(sqlstring, conditons); exec sql execute immediate :sqlstring; } 思考题 : 删除表中的一些记录

25 动态 SQL 例子 2: 删除某年以前出生的某些学生记录, 要求由键盘输入年份 ( 仅给出程序段 ) exec sql begin declare section; SQL 中带有 char sqlstring[200]; 参数 int birthyear; exec sql end declare section;. strcpy(sqlstring, delete where Sage<=:y; ); 不在任何地方说明, 仅占位 printf( Enter birthyear for deleting: ); Scanf( %d, &birthyear); exec sql prepare q1 from :sqlstring; 动态 SQL exec sql execute q1 using :birthyear;. 例子 3: 求选修某门课程号的学生和成绩, 按什么排序由查询者指定 ( 仅给出程序段 ). // 主变量说明 char orderby[150]; 查询需要返回结果, 但是否是多个元组, 在编程时不能确定 strcpy(sqlstring, select Sno, Grade where Cno=:C ); printf( 输入 orderby 子句 : ); scanf( %s, orderby); strcat(sqlstring, orderby); printf( 输入课程号 : ); 动态 SQL scanf( %s, givencno); exec sql prepare query1 from :sqlstring; exec sql declare cursor4 cursor for query; exec sql open cursor4 using :givencno; while(1) { exec sql fetch cursor4 into :Sno, :Grade :GradeIn; if(sqlca.sqlcode==100) break; if(sqlca.sqlcode<0) break; } 访问数据库 DBMS 程序 DBMS 程序 API DBMS ODBC 和 JDBC ODBC:Open DataBase Connectivity JDBC:Java DataBase Connectivity 特点 : 程序不需要重新编译, 就可以在多个不同的 DBMS 上进行操作 可以同时访问多个 DBMS 在源代码级和代码执行级独立于 DBMS 程序 ODBC / JDBC ODBC 和 JDBC 体系结构 : 四个主要组件 应用 驱动器管理 多个数据源指定的驱动器 数据源 程序 ODBC / JDBC

26 ODBC 和 JDBC ODBC 和 JDBC 应用 初始化 终止与数据源的连接 通过定义好的 ODBC/JDBC API, 提交 SQL 语句 获得结果 驱动器管理 加载 ODBC/JDBC 驱动器 从应用层传递 ODBC/JDBC 函数到正确的驱动器 处理从应用层提交的 ODBC/JDBC 初始化和信息调用, 并纪录所用的功能调用 执行初步的错误检测 驱动器 建立与数据源的连接 提交请求 返回请求结果 转换数据源与 ODBC/JDBC 间的数据格式 错误格式和错误代码 数据源 处理从驱动器发来的命令 返回结果 ODBC 和 JDBC 不同的体系结构 JDBC 体系结构 : 通常有 4 种类型 桥 将 JDBC 函数调用直接翻译到另一种 API, 但不是到 DBMS 的本地 API 例如 :ODBC-JDBC 桥 应用只加载一个驱动器 直接翻译到本地的 ( 最初的 )API 将 JDBC 函数调用直接翻译到某一个指定的数据源之上的 API 的方法调用 驱动器动态链接到指定的数据源上 ODBC 和 JDBC 不同的体系结构 JDBC 体系结构 : 通常有 4 种类型 网络桥 驱动器与网络中的中间件通讯, 将 JDBC 函数调用翻译为 DBMS 指定的方法调用 注意, 客户方的驱动器 ( 在网络桥场地的驱动器 ) 通常不是面向 DBMS 的 直接翻译到 socket( 套接字 ) 不直接调用 DBMS API, 驱动器通过 JAVA socket 与 DBMS 通讯 注意, 客户方的驱动器是面向 DBMS 的 使用 JDBC 的实例 JDBC 是一组 JAVA 和接口的集合, 用户可以用 JAVA 编程语言来直接访问数据库 java.sql 包中包括类和接口 使用 JDBC 的实例 数据源驱动器由 Drivermanager 类管理, 维护当前加载的驱动器列表 动态加入和删除驱动器 registerdriver, deregisterdriver, getdrivers 例子 : 动态加载一个数据源并建立连结 Class.forName( oracle/jdbc.driver.oracledriver ); Connection connection=drivermanager.getconnection(url, uid, pid);

27 使用 JDBC 的实例 Sailors Sid sname rating age 使用 JDBC 的实例 Sailors Sid sname rating age 例子 : 向 Sailors 关系中插入一条元组 connection.setautocommit(false); PreparedStatement pstmt = connection.preparestatement( INSERT INTO Sailors VALUES?,?,?,? ); pstmt.setstring(1, j_id); pstmt.setstring(2, j_name); pstmt.setint(3, j_rate); pstmt.setint(4, j_age); pstmt.execute(); pstmt.close(); connection.commit(); 例子 : 执行查询请求 Statement stmt = connection.createstatement(); ResultSet res = stmt.executequery( SELECT S.sname, S.age FROM Sailors S ); While (result.next()) { String name = res.getstring(1); int age = res.getint(2); // process result row } stmt.close(); connection.commit();