南京邮电大学软件开发技术基础02345_DB第2章.ppt

1、1,第七章 关系数据库基本原理,二维表格（table） 实体集及实体集之间的联系 例如：,2,:,:,:,学生,课程,选修,m,n,E-R图,关系数据结构,3,第七章 关系数据库基本原理,集合操作 查询：选择、投影、连接、 并、差、交 更新：增、删、改 SQL(Standard Query Language),对关系的约束条件 三类：实体完整性 参照完整性 用户定义的完整性,4,关系数据结构,一、从用户的角度看 二、从理论上 三、键,5, 选课(学号，课程号，成绩),选课(学号，课程号，成绩) ,选课,3,2,关系数据结构,6,例如：,选课（学号，课程号，成绩） SC(SNO，CNO，GRAD

2、E),984101，0001，85,3,2,关系模式：,元组1：,目:,基数:,关系数据结构,7,二、从理论上看,关系模式 R(A1，A2，A3， An),关系： R D1D2Dn,属性域 (D1，D2，D3，Dn),R的值 r= t1，t2，t3， tm ，元组的集合,关系的目: n 基数：m,t=, vi Di , 1i n,tiDi D2 D3 Dn , 1i n,R的值为属性域的笛卡尔积的子集 r D1D2Dn,或 R(D1/A1，D2/A2，D3/A3，Dn/An),关系数据结构,8,数据库中关系的性质：, 列是同质的，即每一列中的分量是同一类型的数据， 来自同一个域。 不同的列可出

3、自同一个域，不同的属性要给予不同 的属性名。 列的顺序无所谓，即列的次序可以任意交换。 行的顺序无所谓，即行的次序可以任意交换。 任意两个元组不能完全相同。 分量必须取原子值（不可分的数据项）。,关系数据结构,9,三、键(KEY), 数据间关系的描述(表内、表间),(1) 键,如果关系的某一属性或属性组的值能唯一地决定 其它所有属性的值，而其任何真子集无此性质， 则称该属性或属性集为关系的候选键或键,例1：,STUDENT(学号，姓名，性别，出生日期，籍贯),学号,？学号，姓名,？思考: COURSE(课程名，课程号，学分，开课时间),关系数据结构,10,例2：,COURSE(课程名，课程号，

4、学分，开课时间，先修课号),课程号,课程名，课程号 ?,N,关系数据结构,11,(2)主键 (Primary Key),当一个关系能有多个候选键时， 可选定一个作为主键( PK ),(3)候补键(Alternate Key),主键之外的候选键,例： 设 在STUDENT关系中，学生姓名唯一 则学号、姓名都为STUDENT的候选健 若定义学号为主键，则姓名就为候补健,(4)主属性,包含在任何一个候选键中的属性,非主属性,不包含在任何一个候选键中的属性,关系数据结构,12,(5)外键(Foreign Key),不是本关系的键，却引用了其 它关系或本关系的键的属性或 属性组，记做( FK ),例如：

5、 关系STUDENT(学号，姓名，性别，出生日期，籍贯) 关系COURSE(课程名，课程号，学分，开课时间，先修课号) 关系SC (学号，课程号，成绩) PK,学号 PK,课程号 PK,* 关系数据模型中实体间（表间）的联系是用外键隐含地表示的,关系数据结构,13,思考？,在关系模式 COURSE(课程名，课程号，学分，开课时间，先修课号) 中先修课号是什么键，与课程号的关系如何,关系数据结构,14,关系完整性约束,15,一、 实体完整性约束(Entity Integrity Constraint),关系内的约束, 每个关系都应有一个主键, 每个元组的主键的值应当唯一, 主键值不能为空值（NU

6、LL）,NULL, 一种标记，用来说明在数据库中某些属性 值可能是未知的或者是无意义的,关系完整性约束,16,二、 参照完整性约束(Referential Integrity Constraint),不同关系间或同一关系的不同元组间的约束,外键要么引用实际存在的主键值，要么是NULL,若关系R的外键FK，引用关系R的主键PK(R可以是R 或不是R),则对于R中的每个元组在属性组FK上的值必须为： tFK= tPK (t为R中的某一元组) NULL,关系完整性约束,17,三、 用户定义的完整性约束,数据库中完整性约束检查，由DBMS实现,和数据的具体内容有关的约束,各种DBMS产品对完整性约束的

7、支持程度不同,关系完整性约束,18,一、基本概念,(1) 关系数据库的数据操作有两大类：, 查询对数据的检索 更新数据的插入(I)、删除(D)和修改(U),更新以查询为基础,(2) 关系数据模型提供一组完备的关系操作， 以支持对数据库的查询等操作,7.3 关系操作,19,(4) 关系操作分为两大类：,关系代数,关系演算,查询操作以集合操作为基础，又分为： 关系专用操作：选择、投影、连接、除 传统集合操作：并、交、差、笛卡儿积,查询操作以谓词演算为基础，又按谓词 变量分为：元组关系演算 域关系演算,(3) 关系操作以一个或多个关系为运算对象，运算后形 成新的关系，提供用户所需数据,7.3 关系操

8、作,20,二、关系代数(Relational Algebra),(1) 选择操作(), 是一元操作, 目的：在关系中选出满足条件的元组(某些行), 表示：() F(R)，F是布尔表达式, 结果：,7.3 关系操作,21, 性质：,(a)(R) )= （R）,(b) ( （ ( R）) = AND AND AND (R), 例如：,(1)查询王彤同学的情况 姓名王彤（STUDENT） (2)查询1975出生的江苏学生的情况 籍贯 江苏 AND 出生年份1975（STUDENT） (3)查询1975年之后出生的男生的情况 性别男 AND 出生年份=1976 (STUDENT),7.3 关系操作,2

9、2,STUDENT(学号，姓名，性别，出生日期，籍贯),选择操作的结果是其作用的关系的子集,姓名王彤（STUDENT）,7.3 关系操作,23,(2) 投影操作, 一元操作, 目的：选取关系的某些列（即感兴趣的属性）, 表示：() A(R), 结果：,A,C(R), 投影结果可能有重复元组，在结果关系中应去掉重复元组,7.3 关系操作,或: 1,3(R),24,(3) 集合操作,并、差、交、笛卡儿积,(a)并,二元操作, 前提：并兼容两关系具有相同的目，对应属性的域相同, 定义：RS=t|tRtS，属于R或者属于S的元组的集合,应用：,7.3 关系操作,25,(籍贯安徽(STUDENT) （籍

10、贯 福建(STUDENT)）,例：,STUDENT(学号，姓名，性别，出生日期，籍贯),7.3 关系操作,26,(b)差,二元操作,前提：两关系并兼容,定义：R-S=t | tR tS，属于R但不属于S的元组的集合,7.3 关系操作,27,(c)交, 二元操作, 前提：两关系并兼容, 定义：RS=t | tR tS 既属于R又属于S的元组的集合, 结果：,7.3 关系操作,28,(d)笛卡儿积, 二元操作, 定义：RS= | tRgS R的每个元组与S的每个元组拼接成的元组 两关系的同名属性在属性名前加“关系名.”来标注 COURSE.SNO，SC.SNO, 结果：是目为nr+ns，基数为rs

11、的关系,7.3 关系操作,29,“”的应用：,COURSE SC COURSE-SC(课程名，COURSE.课程号，学分， 开课时间，先修课号，学号， SC.课程号，成绩),7.3 关系操作,30,(4)连接操作, 二元操作, 目的：在笛卡儿积中选出满足条件的元组 笛卡儿积是无条件的连接, 定义：RS= (RS), 连接条件：是两关系中同域属性的比较 F andand AiBj Ai是R的属性 Bj是S的属性 , 连接类型：连接、等连接、自然连接,7.3 关系操作,31,连接,，称普遍连接, 连接类型：连接、等连接、自然连接,等连接所有条件中的都为“ ”,公共属性的语法、语义相同但不一定同名(

12、例如：主键和外键),自然连接在R和S的公共属性上的等连接， 消除冗余属性，可简记为：RS,7.3 关系操作,32,例：,生成一个学生成绩表，它具有学号、课程号、课 程名、学分和成绩等属性,写出关系代数表达式,关系COURSE(课程名，课程号，学分，开课时间),关系SC (学号，课程号，成绩), “”的应用,学号，课程号，课程名，学分，成绩(SCSC.课程号COURSE.课程号COURSE),或： 学号，课程号，课程名，学分，成绩(SCCOURSE),或： SC(课程名，课程号，学分(COURSE),7.3 关系操作,33,(5) 除操作 RS, 二元操作(两关系R和S,要求：R中的属性包含S中

13、的属性， 且有些属性不在S中出现 ), 目的：RS是满足下列条件的最大关系，它的属性由R中 那些不出现在S的属性组成, (RS)S的每个元组都在R中, RS的具体计算过程如下： 1) T=X (R) X为不包含在S中的属性 2) W=(TS)R (计算TS中不在R的元组) 3) V=X(W) 4) RS=TV RS=X(R) - X(X(R) S ) R),7.3 关系操作,34,7.3 关系操作,35,例：写出检索学习全部课程的学生学号的关系代数表达式。, “”的应用,关系S(S#,SNAME,SEX,AGE) 关系C(C#,CNAME,TEACHER) 关系SC(S#,C#,GRADE),

14、学生选课情况表示为： S#,C#(SC) 全部课程的课程号为： C#(C) 学了全部课程的学生号可用除法操作表示为： S#,C#(SC) C#(C),7.3 关系操作,36,7.3 关系操作,关系代数的五种基本运算： 选择、投影、笛卡尔积、并、差,， ， ， ， 构成了关系代数的完备集关系代完备集,（ ）,A（， ）构成了一个代数系统， 称为关系代数,37,7.4 关系数据库标准语言SQL,38,用于定义、撤销和修改数据模式,用于查询数据,用于增、删、改数据,用于数据访问权限的控制,SQL概述,39,二、特点,综合统一集DDL、DML、DCL功能于一体 语言风格统一 非过程化只需提出“做什么”

15、，不必指明“怎么做” 面向集合操作对象是元组的集合 以同一种语法结构提供两种使用方式 交互式（ISQL）：在终端输入命令 嵌入式：将SQL嵌入其它程序设计语言 语言简洁，易学易用,SQL概述,40,7.4.2 数据定义语言(DDL),其数据显式地存储在数据库中,虚表，仅有逻辑定义，无具体数据,可由其它基表或视图导出 SQL的视图与数据库的外模式相对应 基于同一基表，可为不同用户提供不同的外模式,对象：表、视图、模式 定义：创建、修改、删除 索引的建立和撤销,41,外模式,模式,内模式,SQL支持的关系数据库三级模式结构,42,7.4.2 数据定义语言(DDL),43,SQL支持的数据类型,44

16、,(3) 有关符号,a ： a为任选项，可有可无,ab ： 可在a、b选一个,： 必须取a、b之一,a： a为必选项,a ： 带下划线的a为缺省选项 例: asc desc，不写，则默认为升序, ： 其中内容可重复0次多次,7.4.2 数据定义语言(DDL),45,定义基本表CREATE TABLE,1. 基本句法：,2. 列级完整性约束条件,CREATE TABLE ( 列级完整性约束条件 , 列级完整性约束条件 , ) ；,两个任选项,46,例1：定义学生 STUDENT基表。,CREATE TABLE STUDENT,(SNO CHAR(7) NOT NULL，,SNAME VARCHA

17、R(8) NOT NULL, SEX CHAR(2) NOT NULL, BDATE DATE NOT NULL, HEIGHT DEC(5,2) DEFAULT 000.00);,定义基本表CREATE TABLE,47,3. 表级完整性约束条件(主键子句，外键子句 ，CHECK子句 ),例：CREATE TABLE STUDENT (SNO CHAR(7) NOT NULL，,，PRIMARY KEY(SNO);,约束条件涉及到表的多个属性，则必须定义在表级,定义基本表CREATE TABLE,48,作用：提供参照完整性约束的说明 每表可有0多个外键（任选项） 可以附加引用完整性约束选项

18、ON DELETE 格式：,外键(FOREIGN KEY )子句,FOREIGN KEY 外键名 () REFERENCES (列名表2) ON DELETE ,基表的列随主键的删除设为NULL 该列应无NOT NULL说明,基表的行随主键的删除而删除,被引用的主键不得删除,定义基本表CREATE TABLE,49,例如：说明分数GRADE应取NULL或0100之间的整数值 CREATE TABLE SC (SNO CHAR(7) NOT NULL， ， GRADE SMALLINT,可选的检查(CHECK)子句,CHECK(GRADE IS NULL) OR (GRADE BETWEEN 0

19、 AND 100) );,定义基本表CREATE TABLE,50,4. 举例,定义STUDENT(学生)，COURSE(课程)，SC（选课） 三个基表。,CREATE TABLE STUDENT (SNO CHAR(7) NOT NULL， SNAME VARCHAR(8) NOT NULL, SEX CHAR(2) NOT NULL, BDATE DATE NOT NULL, HEIGHT DEC(5,2) DEFAULT 000.00, PRIMARY KEY(SNO);,定义基本表CREATE TABLE,51,CREATE TABLE COURSE (CNO CHAR(6) NOT

20、NULL, LHOUR SMALLINT NOT NULL, CREDIT DEC(1,0) NOT NULL, SEMESTER CHAR(2) NOT NULL, PRIMARY KEY(CNO)；,定义基本表CREATE TABLE,52,CREATE TABLE SC (SNO CHAR(7) NOT NULL, CNO CHAR(6) NOT NULL, GRADE DEC(4,1) DEFAULT NULL, PRIMARY KEY (SNO,CNO), FOREIGN KEY (SNO) REFERENCES STUDENT ON DELETE CASCADE, FOREIGN

21、KEY (CNO) REFERENCES COURSE ON DELETE RESTRICT);,定义基本表CREATE TABLE,53,5. 说明,数据库对象(基表、视图等)都有它的拥有者(用户or模式)， 其它用户对该对象的访问受限，并且要说明表创建者： . 表名,用CREATE语句创建的基表，只是一个空框架，需装入数据 数据的装入可用 Insert命令 或数据装载程序,定义基本表CREATE TABLE,54,修改基本表ALTER TABLE,1. 增加列：,删除列：,先定义一个由新表(不含欲删除列)，将原表中 被保留列的内容复制到新表中，然后删除原表， 用重命名(ENAME)命令把新

22、表改名为原表名,55,3. 补充定义主键,4. 撤销主键定义,ALTER TABLE ADD PRIMARY KEY()； *被定义为主键的列名必须满足NOT NULL和唯一性条件,ALTER TABLE DROP PRIMARY KEY； *可用于在插入大批数据时，暂时撤销主键定义，提高系统的性能,修改基本表ALTER TABLE,56,ALTER TABLE ADD FOREIGN KEY () REFERENCES ON DELETE ;,5. 补充定义外键,6. 撤销外键定义,ALTER TABLE DROP ；,修改基本表ALTER TABLE,57,7. 定义和撤销别名,别名(al

23、ias)用简名代替全名，方便书写和输入 例如： 可以简写“表创建者.表名” 对同一数据对象，可定义不同的别名 例如：工资、薪水、薪金表示同一对象 ，,句法： 定义 CREATE SYNONYM FOR . ； 撤销 DROP SYNONYM ；,修改基本表ALTER TABLE,58,删除表：,表中的数据被自动删除 在该表上建立的索引自动被删除 建立在该表上的视图仍然保留，但已无法使用,删除基本表DROP TABLE,59,索引属性名，也称索引键名,CREATE UNIQUE INDEX 索引名 ON 基表名( ASC DESC ，列名 ASC DESC )；,创建,可在多属性上建立索引,每个

24、索引键值只能对应一个元组，例：主键或候补键,索引键按升序排列,索引键按降序排列,加快表的查询速度,数据更新频繁时，系统维护代价高，可删除不必要的索引,索引的建立和撤消,60,3. 举例,例1：在基表STUDENT的属性身高HEIGHT上建索引,例2：在基表SC的属性SNO和CNO上建索引，分别采用降序 和升序，索引键取值唯一 CREATE UNIQUE INDEX SC_INDEX ON SC(SNO DESC,CNO ASC)；,例3：撤销索引H_INDEX DROP INDEX H_INDEX；,CREATE INDEX H_INDEX ON STUDENT(HEIGHT)；,索引的建立和

25、撤消,61,用DDL创建关系数据库的三级模式结构,视图1,视图2,基表1,基表2,基表3,基表4,存储文件2,存储文件1,SQL,外模式,模式,内模式,虚表 只存放定义 可以查询,独立的表 可建索引,存储文件的 逻辑结构组成 数据库内模式 对用户透明,62,课堂小结（掌握，了解）,1. 关系模型组成 2. 关系数据结构： 键 候选键、主键、主属性、外键 3. 关系代数（能根据查询要求写出关系代数表达式） 4. 三类完整性约束 5. SQL主要功能：DDL、DML、QL、DCL 6. DDL的语法 7. 用DDL定义数据库的三级模式结构,63,作业,P135 9 (写出建立表 S, SPJ 的S

26、QL),64,第七章 关系数据库理论与SQL,关系模型概述 关系数据结构 关系代数 关系数据库标准语言 关系数据库的规范化理论,65,7.4.3 数据查询语言(QL),例：查询STUDENT表中男生的学号SNO和姓名SNAME。,SELECT SNO，SNAME FROM STUDENT WHERE SEX=男；,66,2. 语义,SELECT A1，An FROM R1 ，Rn WHERE F,*在关系R1 ，Rn中查询符合条件F的元组中的属性 A1，An的值，其结果仍是一张二维表, A1，An (F(R1 Rn),7.4.3 数据查询语言(QL),67,二、完整语法,SELECT FROM

27、 WHERE 行条件子句 GROUP BY 分组子句 HAVING 组条件子句 ORDER BY ASC DESC ; 排序子句,7.4.3 数据查询语言(QL),68,执行过程,读取FROM子句中基表、视图的数据，执行笛卡儿乘积 选取满足WHERE子句中所给条件表达式的元组 按GROUP BY子句中指定列的值将元组分组，同时提取 满足HAVING子句中组条件表达式的那些组 按SELECT子句中所给的列或列表达式求值输出 按ORDER BY子句对输出的目标表进行排序,SELECT FROM WHERE GROUP BY HAVING ORDER BY,7.4.3 数据查询语言(QL),69,7

28、.4.3 数据查询语言(QL),70,(3)目标表的列名或列表达式,* 所有列,. * 同时从多表中查询时区分同名列,例： SELECT SNAME， COURSE.CNO， GRADE FROM STUDENT，COURSE，SC WHERE , 列名、常数、运算符(/ ),例：查询所有女学生的身高（以厘米表示） SELECT SNAME , 100*HEIGHT FROM STUDENT WHERE SEX=女；,聚集函数(列名),COUNT(*)：计算元组数 SUM()：求该列的值的总和(限数值型列) AVG()：求该列的值的平均值(限数值型列) MAX()：求该列的值中的最大值 MIN

29、()：求该列的值中的最小值,列名表 部分列，顺序可与表中列的顺序不一致,例：查询所有学生的姓名和学号 SELECT SNAME , SNO FROM STUDENT；,7.4.3 数据查询语言(QL),71,查询学生的总人数 SELECT COUNT( * ) FROM STUDET;,SELECT COUNT ( DISTINCT SNO ) FROM SC;,SELECT COUNT ( SNO ) FROM SC;,例：求所有学生的平均身高 SELECT AVG(HEIGHT) FROM STUDET;,查询选修了课程的学生人数,去掉查询结果中的重复列值,7.4.3 数据查询语言(QL)

30、,72, 在计算聚集函数时，如果变量为列名，NULL不参加计算 如果列的所有值都为零，则MAX、MIN函数不返回结果 如果变量为空集，则除COUNT返回零外，其它函数返回NULL 聚集函数与GROUP BY联用时聚集函数以基本组为计算对象， SELECT子句只能包含聚集函数或GROUP BY子句所指的列 无GROUP BY子句时，SELECT子句中聚集函数不能与单独的 列并存 在SELECT子句加DISTINCT选项，可消除查询结果中的重复项 可在SELECT子句中用“旧名 AS 新名”来为查询输出的列改名,注意事项：,7.4.3 数据查询语言(QL),73,(4) FROM子句中，可以为表和

31、视图取一别名，这种别名只 在本句中有效,例：检索至少选修课程CNO为C2和C4的学生学号 SELECT X .SNO FROM SC AS X，SC AS Y WHERE X.SNO=Y.SNO AND X.CNO=C2 AND Y.CNO=C4；,7.4.3 数据查询语言(QL),74,(5) 条件表达式F简单条件，复合条件，基于多表的查询,(a) 简单条件 比较、BETWEEN、LIKE、IN、EXISTS,比较条件(3种形式):, IS NOT NULL,例：查询缺成绩的学号和课号 SELECT SNO，CNO FROM SC WHERE GRADE IS NULL；, (字符大小与编码

32、有关),例：查询1976年前出生的学生姓名 SELECT SNAME FROM STUDENT WHERE YEAR(BDATE) 1976；,=，!=， ， =，!，!,7.4.3 数据查询语言(QL),75,(5) 条件表达式F简单条件，复合条件，基于多表的查询,(a) 简单条件 比较、BETWEEN、LIKE、IN、EXISTS,比较条件(3种形式):, IS NOT NULL,例：查询缺成绩的学号和课号 SELECT SNO，CNO FROM SC WHERE GRADE IS NULL；, (字符大小与编码有关),例：查询1976年前出生的学生姓名 SELECT SNAME FROM

33、 STUDENT WHERE YEAR(BDATE) 1976；, (),F中的运算对象 可以是另一个 SELECT语句,=，!=， ， =，!，!,例：查询不学C2课程的学生姓名 SELECT SNAME FROM STUDENT WHERE SNO ALL(SELECT SNO FROM SC WHERE CNO=C2）；,7.4.3 数据查询语言(QL),76,例：查询19741976年出生的学生姓名 SELECT SNAME FROM STUDENT WHERE YEAR(BDATE) BETWEEN 1974 AND 1976；,例：列出计算机系所开课程（课程号以CS开头）的最高成绩

34、 和平均成绩 SELECT CNO，MAX(GRADE)，AVG(GRADE) FROM SC WHERE CNO LIKE CS% GROUP BY CNO；,例1：查询在CS-110和CS-210课程中至少选了一门的学生学号 SELECT SNO FROM SC WHERE CNO IN(CS-110，CS-210)；,例2：查询秋季学期选了一门以上课程的学生学号 SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM COURSE WHERE SEMESTER=秋）；,例：查询选了课的学生姓名 SELECT SNAME FROM STUDENT

35、WHERE EXISTS (SELECT * FROM SC WHERE SNO=STUDENT.SNO)；,7.4.3 数据查询语言(QL),77,例：查询19741976年出生的学生姓名 SELECT SNAME FROM STUDENT WHERE YEAR(BDATE) BETWEEN 1974 AND 1976；,例：列出计算机系所开课程（课程号以CS开头）的最高成绩 和平均成绩 SELECT CNO，MAX(GRADE)，AVG(GRADE) FROM SC WHERE CNO LIKE CS% GROUP BY CNO；,例1：查询在CS-110和CS-210课程中至少选了一门的

36、学生学号 SELECT SNO FROM SC WHERE CNO IN(CS-110，CS-210)；,例2：查询秋季学期选了一门以上课程的学生学号 SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM COURSE WHERE SEMESTER=秋）；,7.4.3 数据查询语言(QL),78,(5) 条件表达式F简单条件，复合条件，基于多表的查询,(a) 简单条件 比较、BETWEEN、LIKE、IN、EXISTS,比较条件(3种形式):, IS NOT NULL, (字符大小与编码有关), (),7.4.3 数据查询语言(QL),79,(5)

37、条件表达式F简单条件，复合条件，基于多表的查询,(a) 简单条件 比较、BETWEEN、LIKE、IN、EXISTS,7.4.3 数据查询语言(QL),80,有关子查询块的注意事项：, 子查询块中的查询项目一般为一列或一个表达式 输出为中间结果，不须存储、排序，不允许使用 ORDER BY 查询条件中仍可嵌入子查询块，层数受DBMS的限制 子查询出现在WHERE子句和HAVING短语中 子查询的求解方法：由里向外处理，即每一个子查询 在上一级查询处理之前求解,7.4.3 数据查询语言(QL),81,(b)复合条件简单条件用逻辑运算符连接而成 NOT ANDOR ,例：查询1976年出生的学生名

38、及其秋季所修课程的课程号与成绩 SELECT SNAME，COURSE.CNO，GRADE FROM STUDENT，COURSE，SC WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND YEAR(BDATE)=1976 AND SEMESTER=秋；,语义： SNAME,COURSE.CNO,GRADE(YEAR(BDATE)=1976(STUDENT) SEMESTER=秋(COURSE)SC) 只要语义上等价，连接、选择、投影的执行次序决定于DBMS的优化策略,7.4.3 数据查询语言(QL),82,SELECT STUDENT .SN

39、O，SNAME FROM STUDENT，SC WHERE STUDENT.SNO=SC.SNO AND CNO=C2)；,(c) 基于多表的查询,查询选修了C2课程的学生的姓名与学号,解法1： 连接查询,解法2： 嵌套查询,SELECT SNO，SNAME FROM STUDENT WHERE SNO IN(SELECT SNO FROM SC WHERE CNO=C2)；,解法3： 使用存在量词（EXISTS）的嵌套查询,嵌套查询先子查询，再外查询，效率比连接查询的高,7.4.3 数据查询语言(QL),83,四、GROUP BY和ORDER BY子句的应用, GROUP BY HAVING

40、 将表按列名表中列的值分组 如果GROUP BY后有多个列名，则先按第一列名分 组，再按第二列名在组中分组，直到分出所指明的 列都具有相同值的基本组 HAVING后的条件是选择基本组的条件 使SELECT子句中的聚集函数对组计算 加了GROUP BY子句后，SELECT子句的 中只能取簇集函数或GROUP BY子句所指的列,(1) GROUP BY子句,7.4.3 数据查询语言(QL),84,(2) ORDER BY子句, ORDER BY ASCDESC , ASCDESC , 对查询结果按子句中指定的列的值排序, 如果ORDER BY后有多个列名，则先按第一列名排序，再对 于具有相同第一列

41、值的各行，按第二列名排序，, 列序号是在SELECT子句中出现的序号(选的列是聚集函数 或表达式), ASC表示升序，DESC表示降序，缺省时表示升序,7.4.3 数据查询语言(QL),85,例：列出计算机系所开课程（课程号以CS开头）的最高成绩，最低成绩 和平均成绩。若某门课程的成绩不全（即GRADE中有NULL出现）， 则该课程不予统计，结果按CNO升序排列,SELECT CNO，MAX(GRADE)，MIN(GRADE)，AVG(GRADE) FROM SC WHERE CNO LIKE CS% GROUP BY CNO HAVING CNO NOT IN (SELECT CNO FRO

42、M SC WHERE GRADE IS NULL) ORDER BY CNO；,(2) 然后按CNO分组,(3)再按HAVING子句条件 删去成绩不全的组,(4)最后按SELECT子句列出所需结果,(4)按CNO升序排序,(1)先按WHERE子句的条件 删去非计算机系所开的课程,语义：,结果: ,7.4.3 数据查询语言(QL),86,回顾: SELECT * FROM SC； 查询结果中？代表NULL,查询结果:,删去非CS 然后按CNO分组 删去成绩不全组 (4) 最后按SELECT子句列出所需结果, 按CNO升序排序,7.4.3 数据查询语言(QL),87,五、包含UNION的查询,(1

43、) SQL包含的集合运算,并（UNION） 交（INTERSECTION） 差（MINUS）,把关系看成是元组的集合 参与运算的关系必须并兼容：关系同目 属性同域,(2)包含UNION的查询,例：查询1973年出生的学生和选修电机工程系所开课程(程课程号以EE 开头）的学生的学号,SELECT SNO FROM STUDENT WHERE YEAR(BDATE)=1973 UNION SELECT SNO FROM SC WHERE CNO=EE%；,7.4.3 数据查询语言(QL),88,查询结果为： 9104421， 9309203， 9209120， 9208123,注意：在做UNION

44、运算时，必须消除结果中的重复项。,7.4.3 数据查询语言(QL),89,例1： 查询缺成绩的学生名及课程号。,SELECT SNAME，CNO FROM STUDENT，SC WHERE STUDENT.SNO=SC.SNO AND GRADE IS NULL；,数据查询语言举例,90,例2：查询选修CS-110课程的学生名。,SELECT SNAME FROM STUDENT WHERE SNO IN ( SELECT SNO FROM SC WHERE CNO=CS-110)；,数据查询语言举例,91,例3：查询秋季学期课程获90分以上成绩的学生名。,SELECT SNAME FROM

45、STUDENT WHERE SNO IN ( SELECT SNO FROM SC WHERE GRADE =90.0 AND CNO IN ( SELECT CNO FROM COURSE WHERE SEMESTER=秋）；,数据查询语言举例,92,例4： 查询只有一人选修的课程号。,SELECT CNO FROM SC SCX WHERE CNO NOT IN ( SELECT CNO FROM SC WHERE SNO != SCX.SNO )；,* 外查询中为SC表取了别名SCX * 对于外查询中的SCX表的每一行，都检查是否有其他学生 选同一课程；如果没有那就表明此课程只有一人选修

46、,数据查询语言举例,93,7.4.4 数据操纵语言(DML),对数据库中的数据进行 一、增(insert)、 二、删(delete)、 三、改(update)的语句,一、INSERT语句插入元组值,94,7.4.4 数据操纵语言(DML),95,(2) 查询结果的插入,句法：INSERT INTO (列名表) ；,功能：将SELECT 语句的查询结果插入表中,例：生成一个女生成绩临时表FGRADE，表中包括SNAME， CNO和GRADE三个属性，再插入有关女生的数据,CREATE TABLE FGRADE (SNAME VARCHAR(8) NOT NULL, CNO CHAR(6) NOT

47、 NULL, GRADE DEC(4,1) DEFAULT NULL)；,定义临时表：,INSERT INTO FGRADE SELECT SNAME，CNO，GRADE FROM STUDENT，SC WHERE STUDENT.SNO=SC.SNO AND SEX=女；,插入数据：,7.4.4 数据操纵语言(DML),96,临时表：FGRADE(SNAME，CNO，GRADE ) 筛选条件：STUDENT.SNO=SC.SNO AND SEX=女,7.4.4 数据操纵语言(DML),97,从SC表中删除GRADE为NULL的元组 DELETE FROM SC WHERE GRADE IS

48、NULL;, WHERE子句表示要删除的元组应满足的条件 如果没有WHERE子句，则删除表的所有元组， 表成为空表 要删除表，必须用DROP TABLE语句。,二、DELETE语句,例如：,说明：,7.4.4 数据操纵语言(DML),98,三、UPDATE语句,说明：, WHERE子句表示要修改的元组需满足的条件 SET子句表示要修改的列及其新值,7.4.4 数据操纵语言(DML),99,7.4.5 视图,100,7.4.5 视图,101,例1：试定义一视图，作为学生春季选课一览表，其中 有SNO、SNAME、CNO、CREDIT等属性,CREATE VIEW ENROL-SPRING AS

49、SELECT STUDENT.SNO, SNAME, SC.CNO, CREDIT FROM STUDENT，COURSE，SC WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO AND SEMESTER=春；,7.4.5 视图,102,例2: 试定义一视图GRADE-AVG，表示学生的平均成绩， 其中包括SNAME和AVGGRADE（平均成绩）两个 属性。,CREATE VIEW GRADE-AVG(SNAME,AVGGRADE) AS SELECT SNAME, AVG(GRADE) FROM SC,STUDENT WHERE SC.SNO=ST

50、UDENT.SNO GROUP BY SNAME;,7.4.5 视图,103,二、视图的撤销,例：,DROP VIEW ENROL-SPRIN DROP VIEW GRADE-AVG;,查询 原则上可以象基表一样 更新 最终要落实到有关基表的更新， 有一定限制,三、对视图的操作,7.4.5 视图,104,三、视图的操作,7.4.5 视图,105,四、视图的作用,简化用户的操作 使用户能以多种角度看待同一数据 对重构数据库提供了一定程度的逻辑独立性 能够对机密数据提供安全保护,7.4.5 视图,106,课堂小结（掌握，了解）,1. 关系模型组成 2. 关系数据结构： 键 候选键、主键、主属性、外

51、键 3. 关系代数（能根据查询要求写出关系代数表达式） 4. 三类完整性约束 5. SQL主要功能：DDL、DML、QL、DCL 6. DDL的语法 7. 用DDL定义数据库的三级模式结构,107,SQL语言的特点与类型 交互式、嵌入式,SQL语言的组成 DDL、QL、DML、DCL,SQL的DDL基表、视图、索引的定义和撤销， 基表的修改 定义数据库的三级模式结构,SQL的QL SELECT语句的完整语法、限定 GROUP BY和ORDER BY子句的应用、 基于多表的查询,SQL的DML增、删、改的语法,课堂小结（掌握，了解）,108,P135 9 10-(2) (3) 11,作业,109

52、,第七章 关系数据库理论与SQL,关系模型概述 关系数据结构 关系代数 关系数据库标准语言 关系数据库的规范化理论,110,关系数据库的规范化理论,111,例如：设计一个教学情况数据库，它包含：学号S#、课程 号C#、成绩G、教师号T#、教师所在系TD 等属性。 设一门课只有一位教师上构造出关系数据模式,语义：(1)学号是一个学生的标识，课程号是一门课程的标识 (2)一位学生所修的每门课程都有一个成绩 (3)每门课程只有一位任课教师，每位教师可教多门课 (4)每位教师只属于一个系,关系数据库的规范化理论,112,解法一：SCG(S#，C#，G，T#,TD ) 存在问题： （1）冗余度大：学生每

53、选一门课，教师信息重复一次 （2）插入异常：暂无人选的课程，无法插入信息 （3）删除异常：暂时停开的课程被删除，教师信息会丢失 （4）修改异常：一门课换了教师，数据可能不一致 特点： （1）查询简单：只需对单表查询 （2）数据冗余： （3）更新异常：增加异常、删除异常、修改复杂,关系数据库的规范化理论,113,解法二：SC(S#，C#，G) C(C#，T#) T(T#，TD),解法一：SCG(S#，C#，G，T#,TD ),特点：（1）冗余度减小 （2）无插入、删除、修改异常 （3）查询要涉及多表连接(开销大),关系数据库的规范化理论,114,例如：设计一个教学情况数据库，它有：学号S#、学生

54、姓 名SN、所在系SD、年龄SA、课程号C#、课程名CN、 成绩G、教师号T#、所在系TD属性,设一门课只有一 位教师上构造出关系数据模式,包含语义： (1) 学号S#唯一地确定一个学生，课程号C#唯一确定一门课 (2) 一个系有若干学生，但一个学生只属于一个系 (3) 一个学生可以选修多门课程，每门课程有若干学生选修 (4) 一个学生选了一门课有一个成绩 (5) 一门课只有一位教师上，一个教师可以讲多门课 (6) 每个教师只属于一个系,关系数据库的规范化理论,115,解法一： SCG(S#，SN，SD，SA，C#，CN，G，T#,TD ) 存在问题： （1）数据冗余大(学生每选一门课，信息重

55、复一次) （2）修改异常(一门课换了教师) （3）插入异常(实体完整性暂无人选的课程) （4）删除异常(所有学生都不选某门课程) 查询:只需对单表进行，查询效率高,关系数据库的规范化理论,116,解法二： S(S#，SN，SD，SA) C(C#，CN，T#) SC(S#，C#，G) T(T#，TD) 特点： （1）冗余度减小 （2）无插入、删除、修改异常 （3）查询要涉及多表连接(开销大),关系数据库的规范化理论,117,分析： (1)出现冗余和各种异常的原因关系的结构 客观事物及事物的各个属性之间有一定的联系 关系模式应尽量准确地反映这种内在的语义 不应把关系不密切或具有“ 排它性”的属性集

56、中 (2)避免的办法依据语义分解关系 规范化理论 (3)属性值之间的相互关连数据依赖,函数依赖 多值依赖 连接依赖,关系模式 R(U，D，DOM，F) 简化为 R(U，F),关系数据库的规范化理论,118,函数依赖,一个或一组属性的值可以决定其它属性的值 是最基本的数据依赖,1. 函数依赖的形式化定义： 假定t1,t2是关系R上的任意两个元组， X和Y是R的属性子集 如果 t1X=t2X 必有 t1Y=t2Y 则称X函数决定Y，或Y函数依赖于X 记为：XY,119,说明： (1) 函数依赖成立的条件： 关系的任一可能值都满足(不仅是当前值) (2) 根据数据语义确定函数依赖 (3) XY，称X为决定子 (4) 若XY，且YX，记作XY，表示X与Y互相依赖，即X与Y一一对应 (5) Y不函数依赖于X,记作X Y,函数依赖,120,函数依赖,121,例如：教学情况关系R有属性：学号S#、课程号C# ，成绩G 、任课教师号TN、教师所在系名D,设一门课一教师.对关系模式 R(S#,C#,G,TN,D)，分析函数依赖关系,传递依赖；,P,S#,C#G； C#TN，TND; S#,C#TN,C#D,S#,C#D；,P,键是S#,C#,函