数据库原理与设计方法东南大学自动控制系邵家玉课件下载：25

1、数据库原理与设计方法东南大学自动控制系邵家玉课件下载：联系方式EMAIL： 邵家玉中国QQ: 171995639MSN： bistone_shaohotmail POPO: bistone参考书： 1王能斌。数据库系统。电子工业出版社。1995年。 2王能斌编。数据库系统原理。电子工业出版社。2000年。 3王珊 陈红。数据库系统原理教程。 4美JD厄尔曼。数据库系统原理。课程考核第一章 Introduction1.1 Concepts Data、DataBase、DataBase System、DataBase Management System 1. Data(李明，男，1972，江苏，计

2、算机系，1990)数据、信息、知识三者之间的关系：数据的语义即为信息，信息在计算机中的存储表示形式即为数据。从信息中提升、推理、推导出的新的信息即为知识。例如：40数据40信息发烧知识 2. DatabaseDB 3. Database Management SystemDBMS 4. database systemDBS数据库管理员(database administrator，简称DBA)。 5. Data Model数据模型是用来描述数据的一组概念和定义。一般来说，数据的描述包括两个方面：(1)数据的静态特性它包括数据的根本结构、数据间的联系和数据中的约束。(2)数据的动态特性它指定义在

3、数据上的操作。如文件系统。数据模型要面向现实世界，面向用户。数据模型要面向实现，面向计算机。 1) conceptual data model如ER模型、面向对象数据模型等。 2) logical data model如关系数据模型、层次模型、网状模型等。 3) physical data model概念数据模型只用于数据库的设计，逻辑数据模型和物理数据模型用于DBMS的实现。 6. Data Schematype: 型是该数据所属数据类型的说明。value: 值是型的一个实例(instance或occurrence)。对某一类数据的结构、联系和约束的描述是型的描述，型的描述称为数据模式Dat

4、a Schema。在同一数据模式下，可以有很多的值，即实例。例如，学生记录可以定义为图1-3(a)的形式，这是数据模式。而图1-3(b)是其一个实例。数据模型是描述数据的手段而数据模式是用给定数据模型对具体数据的描述。美国国家标准协会(ANSI)的ANSIX3SPARC报告把数据模式分为三级(见图1-4)。 1) conceptual schema/logical schema 2) external schema 3) internal schema 7. Database Instance数据模式是相对稳定的，而实例是相对变动的。数据模式反映一个单位的各种事物的结构、属性、联系和约束，实质

5、上是用数据模型对一个单位的模拟。而实例反映数据库的某一时刻的状态，也就是这一单位在此时的状态。 数据库技术的产生与开展 1. 人工管理阶段 人工管理数据具有如下特点： 1) 数据不保存。 2) 数据需要由应用程序自己管理，没有相应的软件系统负责数据的管理工作。 3) 数据不共享。 4) 数据不具有独立性。人工管理阶段应用程序与数据之间的对应关系可用图l-3表示。 2. 文件系统阶段 用文件系统管理数据具有如下特点： 1) 数据可以长期保存。 2) 由专门的软件即文件系统进行数据管理。 3) 数据共享性差。 4) 数据独立性低。文件系统阶段应用程序与数据之间的关系如图1-4所示。 3. 数据库系

6、统阶段用数据库系统来管理数据具有如下特点： 1) 数据结构化学生人事记录学号姓名性别系别年龄政治面貌家庭出身籍贯家庭成员奖惩情况图1-5 2) 数据的共享性好，冗余度低 3) 数据独立性高 4) 数据由DBMS统一管理和控制 l 数据的平安性(security) l 数据的完整性(integrity) l 并发(concurrency)控制 l 数据库恢复(recovery) 量大 持久 共享 数据库技术的研究领域 1. 数据库管理系统软件的研制 2. 数据库设计 3. 数据库理论1.2 数据库工程与应用 数据库设计的目标与特点图 1-10 数据库设计方法新奥尔良方法：需求分析(分析用户要求)

7、、概念设计(信息分析和定义)、逻辑设计(设计实现)和物理设计(物理数据库设计)。SBYao：需求分析、模式构成、模式汇总、模式重构、模式分析和物理数据库设计。IRPalmer那么主张把数据库设计当成一步接一步的过程，并采用一些辅助手段实现每一过程。此外，基于ER模型的数据库设计方法，基于3NF(第三范式)的设计方法，基于抽象语法标准的设计方法等。标准设计法在具体使用中又可以分为两类：手工设计和计算机辅助数据库设计。ORACLE Designer 2000 数据库设计步骤1. 需求分析2. 概念结构设计3. 逻辑结构设计图1-114. 数据库物理设计5. 数据库实施6. 数据库运行和维护在数据库

8、设计过程中必须注意以下问题。1. 数据库设计过程中要注意充分调动用户的积极性。2. 应用环境的改变、新技术的出现等都会导致应用需求的变化，因此设计人员在设计数据库时必须充分考虑到系统的可扩充性，使设计易于变动。3. 系统的可扩充性最终都是有一定限度的。Database Application各种用户的数据视图DBA主要职责包括：1. 设计与定义数据库系统2. 帮助最终用户使用数据库系统3. 监督与控制数据库系统的使用和运行4. 改进和重组数据库系统，调优数据库系统的性能5. 转储与恢复数据库6. 重构数据库 第二章 Data Model数据模型应满足三方面要求：一是能比较真实地模拟现实世界；二

9、是容易为人所理解；三是便于在计算机上实现。两类：概念模型也称信息模型，数据模型包括网状模型、层次模型、关系模型。2.1 数据模型的要素 数据结构 数据操作 数据的约束条件2.2 概念模型E-R Data Model ConceptsE-R数据模型Entity-Relationship Data ModelEER数据模型Extended Entity-Relationship Data Model1实体(entity)、实体集entity setentity set与entity是型type与值value的关系类似于前述data schema与database instance2属性attrib

10、ute值集value set 实体键entity key实体主键entity primary key3联系relationship基数比约束cardinality ratio constraint参与约束participation constraint：局部参与、全参与 结构约束structural constraint 两个实体之间的联系可以分为三类：l 一对一联系(1:1)l 一对多联系(1:m)l 多对多联系(m:n)所有ownership关系弱实体weak entity E-R diagram用E-R数据模型对某一单位进行模拟，可以得到ER数据模式，ER数据模式可以ER图来直观地表示。

11、entity:weak entity:relationship:attribute:例如： 教职工研究生班级职工编号姓名出生年月职称是否博导是否硕导学号姓名出生年月学位类型是否在职课程课程号名称开课学期学时上课地点学分班级号信箱教职工班级研究生课程班主任C_G导师任课可担任选课MN1NNNMMNMMN止起时间止起时间类型性质成绩类型类型专业方向说明：1学位类型：硕士/博士2导师类型：主要指导老师、协助指导3研究生可能换导师，换专业、方向4选课性质：学位课/非学位课5任课类型：主讲/辅讲6可担任描述有哪些老师可以上哪些课7任课是指目前该课程的任课老师8开课学期：春/秋季9上课地点：目前该课程的上

12、课教室问题：1课性质属性为什么不属于课程实体，而属于选课联系？2专业、方向可不可以属于研究生？ EER data model1特殊化specialization和普遍化generalization全特殊化total specialization/局部特殊化partial specialization不相交特殊化disjoint specialization/重叠特殊化overlapping specialization2聚集aggregation3范畴category2.3 Hierarchy Data Model 层次数据模型的数据结构 1层次模型的根本结构图 TS数据模式图 TS数据模式的

13、一个值2多对多联系在层次模型中的表示 层次数据模型的操纵与完整性约束 层次数据模型的存储结构 层次数据模型的优缺点层次数据模型的优点主要有：l层次数据模型本身比较简单，只需很少几条命令就能操纵数据库，比较容易使用。l对于实体间联系是固定的，且预先定义好的应用系统，采用层次模型来实现，其性能优于关系模型，不次于网状模型。l层次数据模型提供了良好的完整性支持。层次数据模型的缺点主要有：l现实世界中很多联系是非层次性的，如多对多联系、一个结点具有多个双亲等，层次模型表示这类联系的方法很笨拙，只能通过引入冗余数据(易产生不一致性)或创立非自然的数据组织(引入虚拟结点)来解决。l对插入和删除操作的限制比

14、较多。l查询子女结点必须通过双亲结点。l由于结构严密，层次命令趋于程序化。2.4 网状数据模型 网状数据模型的数据结构 网状数据模型的操纵与完整性约束 网状数据模型的存储结构 网状数据模型的优缺点网状数据模型的优点主要有：l能够更为直接地描述现实世界，如一个结点可以有多个双亲、允许结点之间为多对多的联系等。l具有良好的性能，存取效率较高。网状数据模型的缺点主要有：l其DDL语言极其复杂。l数据独立性较差。由于实体问的联系本质上是通过存取路径指示的，因此应用程序在访问数据时要指定存取路径。2.5 Relation Data Model Concepts 1. Attribute and Doma

15、inDomain: 第一范式1NF(first nomal form) atomic data非第一范式Non-First Nomal FormNF2空值：NULL 2. relation and tuple设有一命名为R的关系，它有属性A1、A2、An，其对应的城分别为Dl、D2、Dn那么关系R可表示为：R(D1/Al，D2/A2，Dn/An)或 R(A1，A2，An)或 R(A1A2An)R.A1表示关系R的属性A1。degree(arity):nR的值：r r(R)r=t1,t2,tmt=， viDi,1in笛卡尔乘积 A B ABDEFGDEFG125612563478127890=1

16、290345634783490关系模式：SUDENT(姓名，学号，性别，出生年份籍贯，系别，入学年份)投影：RX tX STUDENT姓名，性别3. key定义：如果关系的某一属性或属性组的值唯一地决定其他所有属性的值，也就是唯一地决定一个元组，而其任何真子集无此性质，那么这个属性或属性组称为该关系的候选键(candidate key)，或简称为键。superkey primary key alternate key all key (SUPPLY(供给商，零件名，工程名)prime attribute non-prime attributeforeign keyCOURSE(课程名，课程号，

17、学分，开课时间，先修课程号)GRADE(学号，课程号，成绩) ConstraintR(D1/Al，D2/A2，Dn/An)1. Domain integrity constraint2. Entity integrity constraint3. Referential integrity constraint4. General integrity constraint Operationrelational algebra operations1. Select operation()2. Project operation()性别，籍贯、出生年份(STUDENT)假设包含那么：(R)=(

18、R)姓名(性别=女(STUDENT)3. Set operationABA-(A-B)union compatibility课程号(COURSE)-先修课程号(COURSE)系别=计算机系(STUDENT)系别=电子系(STUDENT)RS=|tR AND gS4 Join operationR S=(RS)连接条件：ANDANDAND 连接：AiBj等连接equijoin 自然连接natural join例：GRADE GRADE.课程号=COURSE.课程号课程名，课程号，学分COURSE关系代数操作集，-，是完备的操作集。，-， relationally complete5. Outer

19、 join operation6. Outer union operation Relational Calculus1. Tuple Relational Calculus2. Domain Relational Calculus 第三章Database Language SQL结构化查询语言(structured query language，简称SQL) 3.1 IntroductionSQL语言是1974年由Boyce和Chamberlin提出的。1975年至1979年IBM System R实现了这种语言。1986年10月 美国国家标准局(简称ANSI) SQL-861987年国际标

20、准化组织简称ISO也通过了这一标准。ANSI 1989年第二次公布SQL标准(SQL-89)1992年 SQL-92标准目前ANSI正在酝酿新的SQL标准：SQL3。现在SQL已被重新解释成为：Standard Query LanguageSQL按其功能可分为四大局部：1.数据定义语言(Data Definition Language，简称DDL)2.查询语言(Query Language，简称QL) 3.数据操纵语言(Data Manipulation Language，简称DML)4.数据控制语言(Data Control Language，简称DCL)SQL的特点1.综合统一2.高度非过

21、程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方式5.语言简洁，易学易用表3-1 SQL语言的动词 SQL语言的根本概念 3.2 数据定义表3-2 SQL的数据定义语句 定义、删除与修改基表 1. 定义基表CREATE TABLE 表名 (列名数据类型列级完整件约束条件,列名数据类型列级完整性约束条件 表级完整性约束条件；列级完整性约束条件格式：NOT NULL UNIQUE DEFAULT 字值|USER|NULL表级完整性约束条件有三个任选项。用于定义主键的PRIMARY KEY子句，用于定义外键的FOREIGN KEY子句和用于定义列值限制条件的CHECK子句。格式：，PRI

22、MARY KEY ，FOREIGN KEY 外键名 REFERENCES ON DELETE RESTRICT |CASCADE|SET NULL，CHECK 条件IBM DB2 SQL主要支持以下数据类型：SMALLINT 半字长二进制整数。INTEGER或INT 全字长二进制整数。DECIMAL(p,q)或DEC(p,q) 压缩十进制数，共p位，其中小数点后有q位。0qp15,q0时可以省略。FLOAT 双字长浮点数。CHARTER(n)或CHAR(n) 长度为n的定长字符串。VARCHAR(n) 最大长度为n的变长字符串。GRAPHIC(n) 长度为n的定长图形字符串。VARGRAPHI

23、C(n) 最大长度为n的变长图形字符串。DATE 日期型，格式为YYYYMMDD。TIME 时间型，格式为。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，

24、Ccredit)其中Cno为主键。3“学生选课表SC由学号(Sno)、课程号(Cno)、成绩(Grade)3个属性组成，其中(Sno，Cno)为主键。CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname VARCHAR(20) NOT NULL, Ssex CHAR(1)， Sage INT, Sdept CHAR(15),PRIMARY KEY(Sno)；CREATE TABLE Course (Cno CHAR(1) NOT NULL,Cname VARCHAR(20),Cpno CHAR(1)Ccredit DEC(2,1),P

25、RIMARY KEY(Cno),FOREIGN KEY (Cpno) REFERENCES Course ON DELETE RESTRICT)；CREATE TABLE SC (Sno CHAR(5) NOT NULL, Cno CHAR(1) NOT NULL, Grade DEC(4,1) DEFAULT NULL,PRIMARY KEY(Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student ON DELETE CASCADE,FOREIGN KEY (Cno) REFERENCES Course ON DELETE RESTRICT); 2. 修改

26、基表 ALTER TABLE表名 ADD新列名数据类型完整性约束 DROP完整性约束名 MODIFY列名数据类型；例2 向student表增加“入学时间列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATE；例3 将年龄的数据类型改为半字长整数。ALTER TABLE Student MODIFY Sage SMALLINT;例4 删除撤消Student表主键定义。ALTER TABLE Student DROP PRIMARY KEY；3. 删除基表DROP TABLE 表名；例5删除Student表。DROP TABLE Student； 建立与删除

27、索引 1. 建立索引CREATE UNIQUE CLUSTER INDEX ON ( 次序 ， 次序)；排列次序，包括ASC(升序)和DESC(降序)两种，缺省值为ASC。CREATE CLUSTER INDEX Stusname ON Student(Sname);例6 为学生课程数据库中的Student，Course，SC 3个表建立索引。其中Student表按学号升序建立唯一索引，course表按课程号升序建立唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Co

28、ucno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC); 2. 删除索引DROP INDEX索引名；例7 删除Student表的Stusname索引。DROP INDEX Stusname; 3.3 查询SELECT ALL| DISTINCT 目标列表达式，目标列表达式FROM 表名或视图名，表名或视图名WHERE条件表达式GROUP BY列名1HAVING条件表达式ORDER BY列名2ASC | DESC； 单表查询1. 选择表中的假设干列1) 查询指定列例1 查询全体学生的学号与姓名。SELECT Sno

29、，SnameFROM Student;例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM Student；2) 查询全部列例3 查询全体学生的详细记录SELECT *FROM Student；3) 查询经过计算的值例4 查询全体学生的姓名及其出生年份。SELECT Sname，2004-SageFROM Student；例5 查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名SELECT Sname，Year of Birth：，2004-Sage，ISLOWER(Sdept)FROM Student;SELECT Sname NAME，

30、Year of Birth： BIRTH，2004-Sagc BIRTHDAY， ISLOWER(Sdept) DEPARTMENTFROM Student；结果为：NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of Birth： 1976 cs 刘晨 Year of Birth： 1977 if 王名 Year of Birth： 1978 ma 张立 Year of Birth： 1978 if 2. 选择表中的假设干元组 1) 消除取值重复的行例6 查询所有选修过课的学生的学号。SELECT SnoFROM SC；假设SC表中有以下数据：Sno Cno G

31、rade95001 1 9295001 2 8595001 3 8895002 2 9095002 3 80执行上面的SELECT语句后，结果为：Sno9500195001950019500295002SELECT DISTINCT SnoFROM SC；执行结果为：Sno9500195002SELECT SnoFROM SC；与SELECT ALL SnoFROM SC；完全等价。2) 查询满足条件的元组表3-5 常用的查询条件比较大小 等于 大于 小于 大于等于 小于等于！或 不等于有些产品中还包括：！ 不大于！ 不小于逻辑运算符NOT可与比较运算符同用，对条件求非。例7 查计算机系全体学

32、生的名单。SELECT SnameFROM StudentWHERE SdeptCS；例8 查所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname，SageFROM studentWHERE Sage20；或SELECT Sname，SageFROM studentWHERE NOT Sage=20；例9 查考试成绩有不及格的学生的学号。SELECT DISTINCT SnoFROM SCWHERE Grade60；确定范围谓词BETWEENAND和NOT BETWEENAND可以用来查找属性值在(或不在)指定范围内的元组，其中BETWEEN后是范围的下限(即低值)，AND后是范围

33、的上限(即高值)。例10 查询年龄在20至23岁之间的学生的姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23；与BETWEENAND相对的谓词是NOT BETWEENAND。例11 查询年龄不在20至23岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23；确定集合谓词IN可以用来查找属性值属于指定集合的元组。例12 查信息系(IS)、数学系(MA)和计算机科学系(CS)的学生的姓名和性别

34、。SELECT SnameSsexFROM StudentWHERE Sdept IN(IS，MA，CS)；与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。例13 查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECT SnameSsexFROM StudentWHERE Sdept NOT IN(IS，MA，CS)；字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：NOT LIKE 匹配串其含义是查找指定的属性列值与匹配串相匹配的元组，匹配串可以是一个完整的字符串，也可以含有通配符和_。其中：(百分号) 代表任意长度(长度可以为0)的字

35、符串。例如a%b表示以a开头，以b结尾的任意长度的字符串，acb,adefb,ab等都满足该匹配串。_(下划线) 代表任意单个宁符。例如a_b表示以a开头,以b结尾，长度为3的字符串，acb,adb等都满足该匹配串。例14 查询学号为95001的学生的详细情况SELECT *FROM StudentWHERE Sno LIKE 9500l;该语句实际上与下面的语句完全等价：SELECT *FROM StudentWHERE Sno=9500l;例15 查所有姓刘的学生的姓名、学号和性别。SELECT Sname,Sno,SsexFROM StudentWHERE Sname LIKE 刘%；例

36、16 查姓“欧阳且全名为3个汉字的学生的姓名。SELECT SnameFROM StudentWHERE Sname LIKE 欧阳_；例17 查名字中第二字为“阳字的学生的姓名和学号。SELECT Sname，SnoFROM StudentWHERE Sname LIKE _阳；例18查所有不姓刘的学生姓名。SELECT Snamc，Sno，SsexFROM StudentWHERE Sname NOT LIKE 刘%；涉及空值的查询谓词IS NULL和IS NOT NULL可用来查询空值和非空值。例19 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成

37、绩的学生的学号和相应的课程号。SELECT Sno，CnoFROM SCWHERE Grade IS NULL；例20 查所有有成绩的记录的学生学号和课程号。SELECT Sno，CnoFROM SCWHERE Grade IS NOT NULL；多重条件查询例21 查CS系年龄在20岁以下的学生姓名SELECT SnameFROM StudentWHERE Sdept=CSAND Sage20;例12中的IN谓词实际上是多个OR运算符的缩写，因此，例l2中的查询也可以用OR运算符写成如下等价形式：SELECT SnameSsexFROM StudentWHERE Sdept=ISOR Sde

38、pt=MAOR Sdept=CS； 3.对查询结果排序例22 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECT Sno，GradeFROM SCWHERE Cno3ORDER BY Grade DESC；例23 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。SELECT * FROM StudentORDER BY Sdept, Sage DESC; 4.使用集函数COUNT(DISTINCT | ALL *) 统计元组个数COUNT(DISTINCT | ALL列名) 统计一列中值的个数SUM(DISTINCT | ALL 列名)

39、计算一列值的总和(此列必须是数值型)AVG(DISTINCT | ALL 列名) 计算一列值的平均值(此列必须是数值型)MAX(DISTINCT | ALL 列名) 求一列值中的最大值MIN(DISTINCT | ALL 列名) 求一列值中的最小值例24 查询学生总人数。SELECT COUNT*FROM Student;例25 查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno)FROM SC;例26 计算1号课程的学生平均成绩。SELECT AVG(Grade)FROM SCWHERE Cnol；例27 查询学习l号课程的学生最高分数。SELECT MAXGrad

40、eFROM SCWHERE Cno1； 5. 对查询结果分组例28 查询各个课程号与相应的选课人数。SELECT Cno，COUNT(Sno)FROM SCGROUP BY Cno；例29 查询信息系选修了3门以上课程的学生的学号，为简单起见，假设SC表中有一列Dept，它记录了学生所在系。SELECT SnoFROM SCWHERE DeptISGROUP BY SnoHAVING COUNT(*)3； 连接查询1. 等值与非等值连接查询 表名1列名1比较运算符表名2列名2其中比较运算符主要有：、！。此外，连接谓词还可以使用下面形式：表名1列名1BETWEEN表名2列名2AND表名2当连接运

41、算符为时，称为等值连接。使用其它运算符称为非等值连接。例30 查询每个学生及其选修课程的情况。SELECT Student.*，SC.*FROM Student，SCWHERE Student.SnoSC.Sno;例31 Student表和SC表的笛卡尔积。SELECT Student.*，SC.*FROM Student，SC例32 自然连接Student表和SC表。SELECT Student.Sno，Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student，SCWHERE Student.SnoSC.Sno;或SELECT Student.*，Cn

42、o, GradeFROM Student，SCWHERE Student.SnoSC.Sno; 2. 自身连接例33 查询每一门课的间接先修课(即先修课的先修课)。SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.CpnoSECOND.Cno；Cno Cpno 1 7 3 5 5 63. 外连接例34SELECT Student.Sno，Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student，SCWHERE Student.SnoSC.Sno(*);Studen

43、t.Sno，Sname, Ssex, Sage, Sdept, Cno, Grade 9500l 李勇 男 20 CS 1 92 9500l 李勇 男 20 CS 2 85 9500l 李勇 男 20 CS 3 88 95002 刘晨 女 19 IS 2 90 95002 刘晨 女 19 IS 3 80 95003 王名 女 18 MA 95004 张立 男 18 IS 4.复合条件连接 例35 查询选修2号课程且成绩在90分以上的所有学生。 SELECT Student.Sno,Sname FROM Student, SC WHERE Student .SnoSC.Sno AND SC.Cn

44、o2AND SC.Grade90;结果表为；Student.Sno Sname 95002 刘晨例36 查询每个学生选修的课程名及其成绩。SELECT Student.Sno，Sname，Cname， GradeFROM Student，SC，CourseWHERE Student.SnoSCSno and SCCnoCOURSECno； 嵌套查询SELECT SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno2)； 1.带有IN谓词的子查询例37 查询与“刘晨在同一个系学习的学生。查询与“刘晨在同一个系学习的学生，可以首先确

45、定“刘晨所在系名，然后再查找所有在该系学习的学生。所以可以分步来完成此查询：确定“刘晨所在系名SELECT SdeptFROM StudentWHERE Sname=刘晨；结果为：IS查找所有在IS系学习的学生。SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept=IS；分步写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。SQL语句如下：SELECT Sno，Sname，SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WH

46、ERE Sname刘晨)；本例中的查询也可以用前面学过的表的自身连接查询来完成：SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨；本例中父查询和子查询均引用了Student表也可以像表的自身连接查询那样用别名将父查询中的Student表与子查询中的Student表区分开：SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE S1.Sdept IN (SELECT S2.Sdept FROM Studen

47、t S2 WHERE S2.Sname刘晨)；例38 查询选修了课程名为信息系统的学生学号和姓名。完成此查询的根本思路是：首先在Course关系中找出信息系统课程的课程号Cno。然后在SC关系中找出Cno等于第一步给出的Cno集合中某个元素的Sno。最后在Student关系中选出Sno等于第二步中求出Sno集合中某个元素的元组。取出Sno和Sname送入结果表列。将上述想法写成SQL语句就是：SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WH

48、ERE Cname信息系统)；DBMS按照由内向外的原那么求解此SQL语句，首先处理最内层查询块，即课程名信息系统的课程号：SELECT CnoFROM CourseWHERE Cname信息系统查询结果为3。从而可以把上面的SQL语句简化为：SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN 3)；对此SQL语句再处理内层查询，SELECT SnoFROM SCWHERE Cno IN 3结果为95001和95002。从而可以把上面的SQL语句进一步简化为：SELECT Sno,SnameFR

49、OM StudentWHERE Sno IN95001，95002；这样就可以求得最终结果。本查询同样可以用连接查询实现：SELECT Student.Sno,SnameFROM Student，SC，CourseWHERE Student.SnoSC.Sno AND SC.CnoCourse.Cno AND Course.Cname信息系统； 2. 带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、!或等比较运算符。例如，在例37中，由于一个学生只可能在一个系学习，也就是说内查询刘晨所在系的结果是一个唯一值

50、，因此该查询也可以用比较运算符来实现，其SQL语句如下；SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE S1.Sdept = (SELECT S2.Sdept FROM Student S2 WHERE S2.Sname刘晨)；需要注意的是，子查询一定要跟在比较符之后。以下写法是错误的：SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE (SELECT S2.Sdept FROM Student S2 WHERE S2.Sname刘晨)=S1.Sdept；例38中信息系统的课程号是唯一的

51、，但选修该课程的学生并不止一个，所以例38也可以用=运算符和IN谓词共同完成：SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= (SELECT Cno FROM Course WHERE Cname信息系统)；3.带有ANY或ALL谓词的子查询ANY 大于子查询结果中的某个值ANY 小于子查询结果中的某个值ANY 大于等于子查询结果中的某个值ANY 小于等于子查询结果中的某个值ANY 等于子查询结果中的某个值!ANY或ANY 不等于子查询结果中的某个值ALL 大于子查询结果中的所有值ALL 小于子

52、查询结果中的所有值ALL 大于等于子查询结果中的所有值ALL 小于等于子查询结果中的所有值ALL 等于子查询结果中的所有值(通常没有实际意义)!ALL或ALL 不等于子查询结果中的任何一个值例39 查询其他系中比IS系某一学生年龄小的学生名单。SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.SageANY (SELECT S2.Sage FROM Student S2 WHERE S2.SdeptIS) AND S1.SdeptISORDER BY S1.Sage DESC；注意，S1.SdeptIS条件是父查询块中的条件，不是子查询块中的条件。用

53、集函数实现：SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.Sage (SELECT MAX(S2.Sage) FROM Student WHERE S2.SdeptIS) AND S1.SdeptISORDER BY Sage DESC；例40 查询其他系中比IS系所有学生年龄都小的学生名单。SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.SageALL (SELECT S2.Sage FROM Student S2 WHERE S2.SdeptIS AND S1.SdeptIS“ORDER BY S

54、1.Sage DESC；本查询同样也可以用集函数实现。即首先用子查询找出IS系的最小年龄(18)，然后在父查询中查所有非IS系且年龄小于18岁的学生姓名及其年龄。SQL语句如下： SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.Sage (SELECT MIN(S2.Sage) FROM Student S2 WHERE S2.SdeptIS) AND S1.SdeptISORDER BY S1.Sage DESC；事实上，用集函数实现子查询通常比直接用ANY或ALL查询效率要高。 4. 带有EXISTS谓词的子查询EXISTS代表存在量词。带有E

55、XISTS谓词的子查询不退回任何实际数据，它只产生逻辑真值“TRUE或逻辑假值“FALSE。例41 查询所有选修了1号课程的学生姓名。SELECT SnameFROM StudentWHERE EXISTS (SELECT * FROM SC WHERE SC.SnoStudent. Sno AND Cno1)；例41 查询所有未修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM SC WHERE SC.SnoStudent. Sno AND Cno1)；带有IN谓词的例37可以用如下带EXISTS谓词的子查询替

56、换：查询与“刘晨在同一个系学习的学生。SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE EXISTS (SELECT * FROM Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname刘晨)；例42 查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.SnoStudent.Sno AND SC.CnoCours

57、e.Cno)；例43 查询至少选修了学生95002选修的全部课程的学生号码。此题的查询要求可以做如下解释，查询这样的学生，但凡95002选修的课，他都选修了。换句话说，假设有一个学号为x的学生，对所有的课程y，只要学号为95002的学生选修了课程y，那么x也选修了y；那么就将他的学号选出来。即不存在这样的课程y，学生95002选修了y，而学生x没有选。用SQL语言可表示如下：SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCYWHERE SCY.Sno95002 AND NOT EXISTS (SELECT *

58、 FROM SC SCZ WHERE SCZ.SnoSCX.Sno AND SCZ.CnoSCY.Cno)； 集合查询集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。例44 查询计算机科学系的学生及年龄不大于19岁的学生。SELECT *FROM StudentWHERE SdeptCSUNIONSELECT *FROM StudentWHERE Sage19；本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。例45 查询选修了课程1或者选修了课程2的学生。本例实际上是查选修课程1的学生集合与选修课程2的学生集合的并集。SELECT SnoFR

59、OM SCWHERE Cno=1UNIONSELECT SnoFROM SCWHERE Cno=2；注：标准SQL只有并，没有交和差，但实际上，交或差都可以用其它方法实现，具体实现根据不同的查询而不同用语义替换。例46 查询计算机科学系的学生与年龄不大于19岁的学生的交集。本查询换种说法就是，查询计算机科学系中年龄不大于19岁的学生。例47 查询选修课程l的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生。例48 查询计算机科学系的学生与年龄不大于19岁的学生的差集。本查询换种说法就是，查询计算机科学系中年龄大于19岁的学生。例49 查询选修课程1的学生集

60、合与选修课程2的学生集合的差集本例实际上是查询选修了课程1但没有选修课程2的学生。 小结问题：有关系模式part(Item_no,Name,P_no)表示一个产品零部件情况及产品的组成P_no表示上一级的零件，如何用SQL实现查询：查询某个产品给定Item_no的所有零部件？。 3.4 数据更新 插入数据 1. 插入单个元组INSERT INTO 表名(属性列1，属性列2)VALUES (常量1，常量2)例1 将一个新学生记录(学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁)插入Student表中。INSERT INTO StudentVALUES(95020，陈冬，男，I