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

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

邵家玉@中国QQ:171995639MSN：bistone_shao@hotmailPOPO:bistone参考书：1．王能斌。数据库系统。电子工业出版社。1995年。2．王能斌编。数据库系统原理。电子工业出版社。2000年。3．王珊

陈红。数据库系统原理教程。4．[美]J·D·厄尔曼。数据库系统原理。课程考核第一章

Introduction1.1

Concepts

Data、DataBase、DataBaseSystem、DataBaseManagementSystem1.

Data(李明，男，1972，江苏，计算机系，1990)数据、信息、知识三者之间的关系：数据的语义即为信息，信息在计算机中的存储〔表示形式〕即为数据。从信息中提升、推理、推导出的新的信息即为知识。例如：40〔数据〕—40℃〔信息〕—发烧〔知识〕2.

Database——DB3.

DatabaseManagementSystem——DBMS4.

databasesystem——DBS数据库管理员(databaseadministrator，简称DBA)。5.

DataModel数据模型是用来描述数据的一组概念和定义。一般来说，数据的描述包括两个方面：(1)数据的静态特性它包括数据的根本结构、数据间的联系和数据中的约束。(2)数据的动态特性它指定义在数据上的操作。如文件系统。数据模型要面向现实世界，面向用户。数据模型要面向实现，面向计算机。1)

conceptualdatamodel如ER模型、面向对象数据模型等。2)

logicaldatamodel如关系数据模型、层次模型、网状模型等。3)

physicaldatamodel概念数据模型只用于数据库的设计，逻辑数据模型和物理数据模型用于DBMS的实现。6.

DataSchematype:型是该数据所属数据类型的说明。value:值是型的一个实例(instance或occurrence)。对某一类数据的结构、联系和约束的描述是型的描述，型的描述称为数据模式〔DataSchema〕。在同一数据模式下，可以有很多的值，即实例。例如，学生记录可以定义为图1-3(a)的形式，这是数据模式。而图1-3(b)是其一个实例。数据模型是描述数据的手段．而数据模式是用给定数据模型对具体数据的描述。美国国家标准协会(ANSI)的ANSI／X3／SPARC报告把数据模式分为三级(见图1-4)。1)

conceptualschema/logicalschema2)

externalschema3)

internalschema7.

DatabaseInstance数据模式是相对稳定的，而实例是相对变动的。数据模式反映一个单位的各种事物的结构、属性、联系和约束，实质上是用数据模型对一个单位的模拟。而实例反映数据库的某一时刻的状态，也就是这一单位在此时的状态。

数据库技术的产生与开展1.

人工管理阶段人工管理数据具有如下特点：1)

数据不保存。2)

数据需要由应用程序自己管理，没有相应的软件系统负责数据的管理工作。3)

数据不共享。4)

数据不具有独立性。

人工管理阶段应用程序与数据之间的对应关系可用图l-3表示。2.

文件系统阶段用文件系统管理数据具有如下特点：1)

数据可以长期保存。2)

由专门的软件即文件系统进行数据管理。3)

数据共享性差。4)

数据独立性低。文件系统阶段应用程序与数据之间的关系如图1-4所示。3.

数据库系统阶段用数据库系统来管理数据具有如下特点：1)

数据结构化学生人事记录学号姓名性别系别年龄政治面貌家庭出身籍贯家庭成员奖惩情况图1-52)

数据的共享性好，冗余度低3)

数据独立性高4)

数据由DBMS统一管理和控制l

数据的平安性(security)l

数据的完整性(integrity)l

并发(concurrency)控制l

数据库恢复(recovery)量大持久共享

数据库技术的研究领域1.

数据库管理系统软件的研制2.

数据库设计3.

数据库理论1.2

数据库工程与应用

数据库设计的目标与特点图1-10

数据库设计方法新奥尔良方法：需求分析(分析用户要求)、概念设计(信息分析和定义)、逻辑设计(设计实现)和物理设计(物理数据库设计)。S．B．Yao：需求分析、模式构成、模式汇总、模式重构、模式分析和物理数据库设计。I．R．Palmer那么主张把数据库设计当成一步接一步的过程，并采用一些辅助手段实现每一过程。此外，基于E—R模型的数据库设计方法，基于3NF(第三范式)的设计方法，基于抽象语法标准的设计方法等。标准设计法在具体使用中又可以分为两类：手工设计和计算机辅助数据库设计。ORACLEDesigner2000

数据库设计步骤1.

需求分析2.

概念结构设计3.

逻辑结构设计图1-114.

数据库物理设计5.

数据库实施6.

数据库运行和维护在数据库设计过程中必须注意以下问题。1.

数据库设计过程中要注意充分调动用户的积极性。2.

应用环境的改变、新技术的出现等都会导致应用需求的变化，因此设计人员在设计数据库时必须充分考虑到系统的可扩充性，使设计易于变动。3.

系统的可扩充性最终都是有一定限度的。

DatabaseApplication各种用户的数据视图DBA主要职责包括：1.

设计与定义数据库系统2.

帮助最终用户使用数据库系统3.

监督与控制数据库系统的使用和运行4.

改进和重组数据库系统，调优数据库系统的性能5.

转储与恢复数据库6.

重构数据库第二章

DataModel数据模型应满足三方面要求：一是能比较真实地模拟现实世界；二是容易为人所理解；三是便于在计算机上实现。两类：概念模型也称信息模型，数据模型包括网状模型、层次模型、关系模型。2.1

数据模型的要素

数据结构

数据操作

数据的约束条件2.2

概念模型——E-RDataModel

ConceptsE-R数据模型〔Entity-RelationshipDataModel〕EER数据模型〔ExtendedEntity-RelationshipDataModel〕1．实体(entity)、实体集〔entityset〕entityset与entity是型〔type〕与值〔value〕的关系〔类似于前述dataschema与databaseinstance〕2．属性〔attribute〕值集〔valueset〕实体键〔entitykey〕实体主键〔entityprimarykey〕3．

联系〔relationship〕基数比约束〔cardinalityratioconstraint〕参与约束〔participationconstraint〕：局部参与、全参与结构约束〔structuralconstraint〕两个实体之间的联系可以分为三类：l

一对一联系(1:1)l

一对多联系(1:m)l

多对多联系(m:n)所有〔ownership〕关系——弱实体〔weakentity〕

E-Rdiagram用E-R数据模型对某一单位进行模拟，可以得到ER数据模式，ER数据模式可以ER图来直观地表示。entity:

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

EERdatamodel1．特殊化〔specialization〕和普遍化〔generalization〕全特殊化〔totalspecialization〕/局部特殊化〔partialspecialization〕不相交特殊化〔disjointspecialization〕/重叠特殊化〔overlappingspecialization〕2．

聚集〔aggregation〕3．

范畴〔category〕2.3

HierarchyDataModel

层次数据模型的数据结构1．层次模型的根本结构图TS数据模式图TS数据模式的一个值2．多对多联系在层次模型中的表示

层次数据模型的操纵与完整性约束

层次数据模型的存储结构

层次数据模型的优缺点层次数据模型的优点主要有：l

层次数据模型本身比较简单，只需很少几条命令就能操纵数据库，比较容易使用。l

对于实体间联系是固定的，且预先定义好的应用系统，采用层次模型来实现，其性能优于关系模型，不次于网状模型。l

层次数据模型提供了良好的完整性支持。层次数据模型的缺点主要有：l

现实世界中很多联系是非层次性的，如多对多联系、一个结点具有多个双亲等，层次模型表示这类联系的方法很笨拙，只能通过引入冗余数据(易产生不一致性)或创立非自然的数据组织(引入虚拟结点)来解决。l

对插入和删除操作的限制比较多。l

查询子女结点必须通过双亲结点。l

由于结构严密，层次命令趋于程序化。2.4

网状数据模型

网状数据模型的数据结构

网状数据模型的操纵与完整性约束

网状数据模型的存储结构

网状数据模型的优缺点网状数据模型的优点主要有：l

能够更为直接地描述现实世界，如一个结点可以有多个双亲、允许结点之间为多对多的联系等。l

具有良好的性能，存取效率较高。网状数据模型的缺点主要有：l

其DDL语言极其复杂。l

数据独立性较差。由于实体问的联系本质上是通过存取路径指示的，因此应用程序在访问数据时要指定存取路径。2.5

RelationDataModel

Concepts1.AttributeandDomainDomain:第一范式1NF(firstnomalform)atomicdata非第一范式〔Non-FirstNomalForm〕NF2空值：NULL2.

relationandtuple设有一命名为R的关系，它有属性A1、A2、…、An，其对应的城分别为Dl、D2、…、Dn那么关系R可表示为：R＝(D1/Al，D2/A2，…，Dn/An)或R＝(A1，A2，…，An)或R＝(A1A2…An)R.A1表示关系R的属性A1。degree(arity):nR的值：rr(R)r={t1,t2,…,tm}t=<v1,v2,…,vn>，vi∈Di,1≤i≤n笛卡尔乘积×ABA×BDE

FG

DEFG1256125634781278

×90=1290

345634783490关系模式：SUDENT(姓名，学号，性别，出生年份．籍贯，系别，入学年份)<李彤，9093135，女，1975，江苏，计算机系，1993>投影：R[X]t[X]STUDENT[姓名，性别]3.

key定义：如果关系的某一属性或属性组的值唯一地决定其他所有属性的值，也就是唯一地决定一个元组，而其任何真子集无此性质，那么这个属性或属性组称为该关系的候选键(candidatekey)，或简称为键。superkeyprimarykeyalternatekeyallkey(SUPPLY(供给商，零件名，工程名))primeattributenon-primeattributeforeignkeyCOURSE(课程名，课程号，学分，开课时间，先修课程号)GRADE(学号，课程号，成绩)

ConstraintR＝(D1/Al，D2/A2，…，Dn/An)1.

Domainintegrityconstraint2.

Entityintegrityconstraint3.

Referentialintegrityconstraint4.

Generalintegrityconstraint

Operationrelationalalgebraoperations1.

Selectoperationσ<选择条件>(<关系名>)2.

ProjectoperationΠ<属性表>(<关系名>)Π性别，籍贯、出生年份(STUDENT)假设<属性表2>包含<属性表1>那么：Π<属性表1>(Π<属性表2>(R))=Π<属性表1>(R)Π姓名(σ性别=‘女’(STUDENT))3.

SetoperationA∩B≡A-(A-B)unioncompatibilityΠ课程号(COURSE)-Π先修课程号(COURSE)σ系别=‘计算机系’(STUDENT)∪σ系别=‘电子系’(STUDENT)R×S={<t,g>|t∈RANDg∈S}4

JoinoperationR<连接条件>S=σ<连接条件>(R×S)连接条件：<条件1>AND<条件2>AND……AND<条件k>θ连接：AiθBj等连接〔equijoin〕自然连接〔naturaljoin〕例：GRADEGRADE.课程号=COURSE.课程号〔Π课程名，课程号，学分〔COURSE〕〕关系代数操作集{σ，Π，∪，-，×}是完备的操作集。{σ，Π，∪，-，}relationallycomplete5.

Outerjoinoperation6.

Outerunionoperation

RelationalCalculus1.

TupleRelationalCalculus2.

DomainRelationalCalculus第三章

DatabaseLanguageSQL结构化查询语言(structuredquerylanguage，简称SQL)3.1

IntroductionSQL语言是1974年由Boyce和Chamberlin提出的。1975年至1979年IBMSystemR实现了这种语言。1986年10月美国国家标准局(简称ANSI)SQL-861987年国际标准化组织〔简称ISO〕也通过了这一标准。ANSI1989年第二次公布SQL标准(SQL-89)1992年SQL-92标准目前ANSI正在酝酿新的SQL标准：SQL3。现在SQL已被重新解释成为：StandardQueryLanguageSQL按其功能可分为四大局部：1.

数据定义语言(DataDefinitionLanguage，简称DDL)2.

查询语言(QueryLanguage，简称QL)3.

数据操纵语言(DataManipulationLanguage，简称DML)4.

数据控制语言(DataControlLanguage，简称DCL)

SQL的特点1.

综合统一2.

高度非过程化3.

面向集合的操作方式4.

以同一种语法结构提供两种使用方式5.

语言简洁，易学易用表3-1SQL语言的动词

SQL语言的根本概念3.2

数据定义表3-2SQL的数据定义语句

定义、删除与修改基表1.

定义基表CREATETABLE＜表名＞(＜列名＞＜数据类型＞[列级完整件约束条件][,＜列名＞＜数据类型＞[列级完整性约束条件]……][＜表级完整性约束条件＞]〕；列级完整性约束条件格式：[NOTNULL[UNIQUE]][DEFAULT字值|USER|NULL]表级完整性约束条件有三个任选项。用于定义主键的PRIMARYKEY子句，用于定义外键的FOREIGNKEY子句和用于定义列值限制条件的CHECK子句。格式：[，PRIMARYKEY〔<列名>…〕][，FOREIGNKEY[外键名]〔<列名>…〕REFERENCES<表名>[ONDELETERESTRICT|CASCADE|SETNULL]][，CHECK〔条件〕……]IBMDB2SQL主要支持以下数据类型：SMALLINT半字长二进制整数。INTEGER或INT全字长二进制整数。DECIMAL(p[,q])或DEC(p[,q])压缩十进制数，共p位，其中小数点后有q位。0≤q≤p≤15,q＝0时可以省略。FLOAT双字长浮点数。CHARTER(n)或CHAR(n)长度为n的定长字符串。VARCHAR(n)最大长度为n的变长字符串。GRAPHIC(n)长度为n的定长图形字符串。VARGRAPHIC(n)最大长度为n的变长图形字符串。DATE日期型，格式为YYYY—MM—DD。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，Ccredit)其中Cno为主键。3．“学生选课〞表SC由学号(Sno)、课程号(Cno)、成绩(Grade)3个属性组成，其中(Sno，Cno)为主键。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameVARCHAR(20)NOTNULL,SsexCHAR(1)，SageINT,SdeptCHAR(15),PRIMARYKEY(Sno))；CREATETABLECourse(CnoCHAR(1)NOTNULL,CnameVARCHAR(20),CpnoCHAR(1)CcreditDEC(2,1),PRIMARYKEY(Cno),FOREIGNKEY(Cpno)REFERENCESCourseONDELETERESTRICT)；CREATETABLESC(SnoCHAR(5)NOTNULL,CnoCHAR(1)NOTNULL,GradeDEC(4,1)DEFAULTNULL,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudentONDELETECASCADE,FOREIGNKEY(Cno)REFERENCESCourseONDELETERESTRICT);2.

修改基表ALTERTABLE＜表名＞[ADD＜新列名＞＜数据类型＞[完整性约束]][DROP＜完整性约束名＞][MODIFY＜列名＞＜数据类型＞]；例2向student表增加“入学时间〞列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；例3将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT;例4删除〔撤消〕Student表主键定义。ALTERTABLEStudentDROPPRIMARYKEY；3.

删除基表DROPTABLE＜表名＞；例5删除Student表。DROPTABLEStudent；

建立与删除索引1.

建立索引CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][，<列名>[次序]]…)；排列次序，包括ASC(升序)和DESC(降序)两种，缺省值为ASC。CREATECLUSTERINDEXStusnameONStudent(Sname);例6为学生—课程数据库中的Student，Course，SC3个表建立索引。其中Student表按学号升序建立唯一索引，course表按课程号升序建立唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC);2.

删除索引DROPINDEX＜索引名＞；例7删除Student表的Stusname索引。DROPINDEXStusname;3.3

查询SELECT[ALL|DISTINCT]＜目标列表达式＞[，＜目标列表达式＞]…FROM＜表名或视图名＞[，＜表名或视图名＞]…[WHERE＜条件表达式＞][GROUPBY＜列名1＞[HAVING＜条件表达式＞]][ORDERBY＜列名2＞[ASC|DESC]]；

单表查询1.

选择表中的假设干列1)

查询指定列例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent;例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；2)

查询全部列例3查询全体学生的详细记录SELECT*FROMStudent；3)

查询经过计算的值例4查询全体学生的姓名及其出生年份。SELECTSname，2004-SageFROMStudent；例5查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名SELECTSname，‘YearofBirth：’，2004-Sage，ISLOWER(Sdept)FROMStudent;

SELECTSnameNAME，‘YearofBirth：’BIRTH，2004-SagcBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；结果为：NAMEBIRTHBIRTHDAYDEPARTMENT李勇YearofBirth：1976cs刘晨YearofBirth：1977if王名YearofBirth：1978ma张立YearofBirth：1978if2.

选择表中的假设干元组1)

消除取值重复的行例6查询所有选修过课的学生的学号。SELECTSnoFROMSC；假设SC表中有以下数据：SnoCnoGrade9500119295001285950013889500229095002380执行上面的SELECT语句后，结果为：Sno9500195001950019500295002SELECTDISTINCTSnoFROMSC；执行结果为：Sno9500195002

SELECTSnoFROMSC；与SELECTALLSnoFROMSC；完全等价。2)

查询满足条件的元组表3-5常用的查询条件①比较大小＝等于＞大于＜小于＞＝大于等于＜＝小于等于！＝或＜＞不等于有些产品中还包括：！＞不大于！＜不小于逻辑运算符NOT可与比较运算符同用，对条件求非。例7查计算机系全体学生的名单。SELECTSnameFROMStudentWHERESdept＝‘CS’；例8查所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMstudentWHERESage＜20；或SELECTSname，SageFROMstudentWHERENOTSage>=20；例9查考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade＜60；②确定范围谓词BETWEEN．．．AND．．．和NOTBETWEEN．．．AND．．．可以用来查找属性值在(或不在)指定范围内的元组，其中BETWEEN后是范围的下限(即低值)，AND后是范围的上限(即高值)。例10查询年龄在20至23岁之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；与BETWEEN．．．AND．．．相对的谓词是NOTBETWEEN．．．AND．．．。例11查询年龄不在20至23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；③确定集合谓词IN可以用来查找属性值属于指定集合的元组。例12查信息系(IS)、数学系(MA)和计算机科学系(CS)的学生的姓名和性别。SELECTSname．SsexFROMStudentWHERESdeptIN(‘IS’，‘MA’，‘CS’)；与IN相对的谓词是NOTIN，用于查找属性值不属于指定集合的元组。例13查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname．SsexFROMStudentWHERESdeptNOTIN(‘IS’，‘MA’，‘CS’)；④字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：[NOT]LIKE‘＜匹配串＞’其含义是查找指定的属性列值与＜匹配串＞相匹配的元组，＜匹配串＞可以是一个完整的字符串，也可以含有通配符％和_。其中：％(百分号)代表任意长度(长度可以为0)的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串，acb,adefb,ab等都满足该匹配串。_(下划线)代表任意单个宁符。例如a_b表示以a开头,以b结尾，长度为3的字符串，acb,adb等都满足该匹配串。例14查询学号为95001的学生的详细情况SELECT*FROMStudentWHERESnoLIKE‘9500l’;该语句实际上与下面的语句完全等价：SELECT*FROMStudentWHERESno=‘9500l’;例15查所有姓刘的学生的姓名、学号和性别。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE‘刘%’；例16查姓“欧阳〞且全名为3个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE‘欧阳__’；例17查名字中第二字为“阳〞字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE’__阳％’；例18查所有不姓刘的学生姓名。SELECTSnamc，Sno，SsexFROMStudentWHERESnameNOTLIKE‘刘%’；⑤涉及空值的查询谓词ISNULL和ISNOTNULL可用来查询空值和非空值。例19某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；例20查所有有成绩的记录的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；⑥多重条件查询例21查CS系年龄在20岁以下的学生姓名SELECTSnameFROMStudentWHERESdept=‘CS’ANDSage<20;例12中的IN谓词实际上是多个OR运算符的缩写，因此，例l2中的查询也可以用OR运算符写成如下等价形式：SELECTSname．SsexFROMStudentWHERESdept=‘IS’ORSdept=‘MA’ORSdept=‘CS’；3.

对查询结果排序例22查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECTSno，GradeFROMSCWHERECno＝‘3’ORDERBYGradeDESC；例23查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC;4.

使用集函数COUNT([DISTINCT|ALL]*)统计元组个数COUNT([DISTINCT|ALL]＜列名＞)统计一列中值的个数SUM([DISTINCT|ALL]＜列名＞)计算一列值的总和(此列必须是数值型)AVG([DISTINCT|ALL]＜列名＞)计算一列值的平均值(此列必须是数值型)MAX([DISTINCT|ALL]＜列名＞)求一列值中的最大值MIN([DISTINCT|ALL]＜列名＞)求一列值中的最小值例24查询学生总人数。SELECTCOUNT〔*〕FROMStudent;例25查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC;例26计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno＝‘l’；例27查询学习l号课程的学生最高分数。SELECTMAX〔Grade〕FROMSCWHERECno＝‘1’；5.

对查询结果分组例28查询各个课程号与相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；例29查询信息系选修了3门以上课程的学生的学号，为简单起见，假设SC表中有一列Dept，它记录了学生所在系。SELECTSnoFROMSCWHEREDept＝‘IS’GROUPBYSnoHAVINGCOUNT(*)＞3；

连接查询1.

等值与非等值连接查询[＜表名1＞．]＜列名1＞＜比较运算符＞[＜表名2＞．]＜列名2＞其中比较运算符主要有：＝、＞、＜、＞＝、＜＝、！＝。此外，连接谓词还可以使用下面形式：[＜表名1＞．]＜列名1＞BETWEEN[＜表名2．]<列名2＞AND[＜表名2＞．]<列名3>当连接运算符为＝时，称为等值连接。使用其它运算符称为非等值连接。例30查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno＝SC.Sno;例31Student表和SC表的笛卡尔积。SELECTStudent.*，SC.*FROMStudent，SC

例32自然连接Student表和SC表。SELECTStudent.Sno，Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent，SCWHEREStudent.Sno＝SC.Sno;或SELECTStudent.*，Cno,GradeFROMStudent，SCWHEREStudent.Sno＝SC.Sno;2.

自身连接例33查询每一门课的间接先修课(即先修课的先修课)。SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno＝SECOND.Cno；

CnoCpno1735563.

外连接例34SELECTStudent.Sno，Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent，SCWHEREStudent.Sno＝SC.Sno(*);Student.Sno，Sname,Ssex,Sage,Sdept,Cno,Grade9500l李勇

男20CS1929500l李勇

男20CS2859500l李勇

男20CS38895002刘晨

女19IS29095002刘晨

女19IS38095003王名

女18MA95004张立

男18IS4.

复合条件连接例35查询选修2号课程且成绩在90分以上的所有学生。SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno＝SC.SnoANDSC.Cno＝’2’ANDSC.Grade＞90;结果表为；Student.SnoSname95002刘晨例36查询每个学生选修的课程名及其成绩。SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno＝SC．SnoandSC．Cno＝COURSE．Cno；

嵌套查询SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno＝‘2’)；1.

带有IN谓词的子查询例37查询与“刘晨〞在同一个系学习的学生。查询与“刘晨〞在同一个系学习的学生，可以首先确定“刘晨〞所在系名，然后再查找所有在该系学习的学生。所以可以分步来完成此查询：①确定“刘晨〞所在系名SELECTSdeptFROMStudentWHERESname=‘刘晨’；结果为：IS②查找所有在IS系学习的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdept=‘IS’；分步写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。SQL语句如下：SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname＝‘刘晨’)；本例中的查询也可以用前面学过的表的自身连接查询来完成：SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=‘刘晨’；本例中父查询和子查询均引用了Student表．也可以像表的自身连接查询那样用别名将父查询中的Student表与子查询中的Student表区分开：SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1WHERES1.SdeptIN(SELECTS2.SdeptFROMStudentS2WHERES2.Sname＝’刘晨’)；例38查询选修了课程名为信息系统的学生学号和姓名。完成此查询的根本思路是：①首先在Course关系中找出‘信息系统’课程的课程号Cno。②然后在SC关系中找出Cno等于第一步给出的Cno集合中某个元素的Sno。③最后在Student关系中选出Sno等于第二步中求出Sno集合中某个元素的元组。取出Sno和Sname送入结果表列。将上述想法写成SQL语句就是：SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname＝’信息系统’))；DBMS按照由内向外的原那么求解此SQL语句，首先处理最内层查询块，即课程名‘信息系统’的课程号：SELECTCnoFROMCourseWHERECname＝‘信息系统’查询结果为3。从而可以把上面的SQL语句简化为：SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN〔‘3’〕)；对此SQL语句再处理内层查询，SELECTSnoFROMSCWHERECnoIN〔‘3’〕结果为95001和95002。从而可以把上面的SQL语句进一步简化为：SELECTSno,SnameFROMStudentWHERESnoIN〔‘95001’，‘95002’〕；这样就可以求得最终结果。本查询同样可以用连接查询实现：SELECTStudent.Sno,SnameFROMStudent，SC，CourseWHEREStudent.Sno＝SC.SnoANDSC.Cno＝Course.CnoANDCourse.Cname＝’信息系统’；2.

带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用＞、＜、＝、＞＝、＜＝、!＝或＜＞等比较运算符。例如，在例37中，由于一个学生只可能在一个系学习，也就是说内查询刘晨所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其SQL语句如下；SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1WHERES1.Sdept=(SELECTS2.SdeptFROMStudentS2WHERES2.Sname＝’刘晨’)；需要注意的是，子查询一定要跟在比较符之后。以下写法是错误的：SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1WHERE(SELECTS2.SdeptFROMStudentS2WHERES2.Sname＝’刘晨’)=S1.Sdept；例38中信息系统的课程号是唯一的，但选修该课程的学生并不止一个，所以例38也可以用=运算符和IN谓词共同完成：SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=(SELECTCnoFROMCourseWHERECname＝’信息系统’))；3.

带有ANY或ALL谓词的子查询＞ANY大于子查询结果中的某个值＜ANY小于子查询结果中的某个值＞＝ANY大于等于子查询结果中的某个值＜＝ANY小于等于子查询结果中的某个值＝ANY等于子查询结果中的某个值!＝ANY或＜＞ANY不等于子查询结果中的某个值＞ALL大于子查询结果中的所有值＜ALL小于子查询结果中的所有值＞＝ALL大于等于子查询结果中的所有值＜＝ALL小于等于子查询结果中的所有值＝ALL等于子查询结果中的所有值(通常没有实际意义)!＝ALL或＜＞ALL不等于子查询结果中的任何一个值例39查询其他系中比IS系某一学生年龄小的学生名单。SELECTS1.Sname，S1.SageFROMStudentS1WHERES1.Sage＜ANY(SELECTS2.SageFROMStudentS2WHERES2.Sdept＝’IS’)ANDS1.Sdept＜＞’IS‘ORDERBYS1.SageDESC；注意，S1.Sdept＜＞‘IS’条件是父查询块中的条件，不是子查询块中的条件。用集函数实现：SELECTS1.Sname，S1.SageFROMStudentS1WHERES1.Sage＜(SELECTMAX(S2.Sage)FROMStudentWHERES2.Sdept＝’IS’)ANDS1.Sdept＜＞‘IS’ORDERBYSageDESC；例40查询其他系中比IS系所有学生年龄都小的学生名单。SELECTS1.Sname，S1.SageFROMStudentS1WHERES1.Sage＜ALL(SELECTS2.SageFROMStudentS2WHERES2.Sdept＝’IS’〕ANDS1.Sdept＜＞’IS“ORDERBYS1.SageDESC；本查询同样也可以用集函数实现。即首先用子查询找出‘IS’系的最小年龄(18)，然后在父查询中查所有非‘IS’系且年龄小于18岁的学生姓名及其年龄。SQL语句如下：SELECTS1.Sname，S1.SageFROMStudentS1WHERES1.Sage＜(SELECTMIN(S2.Sage)FROMStudentS2WHERES2.Sdept＝’IS’)ANDS1.Sdept＜＞’IS’ORDERBYS1.SageDESC；事实上，用集函数实现子查询通常比直接用ANY或ALL查询效率要高。4.

带有EXISTS谓词的子查询EXISTS代表存在量词。带有EXISTS谓词的子查询不退回任何实际数据，它只产生逻辑真值“TRUE〞或逻辑假值“FALSE〞。例41查询所有选修了1号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESC.Sno＝Student.SnoANDCno＝‘1’)；例41查询所有未修1号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESC.Sno＝Student.SnoANDCno＝‘1’)；带有IN谓词的例37可以用如下带EXISTS谓词的子查询替换：查询与“刘晨〞在同一个系学习的学生。SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname＝’刘晨’)；例42查询选修了全部课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESC.Sno＝Student.SnoANDSC.Cno＝Course.Cno))；例43查询至少选修了学生95002选修的全部课程的学生号码。此题的查询要求可以做如下解释，查询这样的学生，但凡95002选修的课，他都选修了。换句话说，假设有一个学号为x的学生，对所有的课程y，只要学号为95002的学生选修了课程y，那么x也选修了y；那么就将他的学号选出来。即不存在这样的课程y，学生95002选修了y，而学生x没有选。用SQL语言可表示如下：SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno＝’95002’ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno＝SCX.SnoANDSCZ.Cno＝SCY.Cno))；

集合查询集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。例44查询计算机科学系的学生及年龄不大于19岁的学生。SELECT*FROMStudentWHERESdept＝’CS’UNIONSELECT*FROMStudentWHERESage＜＝19；本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。例45查询选修了课程1或者选修了课程2的学生。本例实际上是查选修课程1的学生集合与选修课程2的学生集合的并集。SELECTSnoFROMSCWHERECno=’1’UNIONSELECTSnoFROMSCWHERECno=’2’；注：标准SQL只有并，没有交和差，但实际上，交或差都可以用其它方法实现，具体实现根据不同的查询而不同〔用语义替换〕。例46查询计算机科学系的学生与年龄不大于19岁的学生的交集。本查询换种说法就是，查询计算机科学系中年龄不大于19岁的学生。例47查询选修课程l的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生。例48查询计算机科学系的学生与年龄不大于19岁的学生的差集。本查询换种说法就是，查询计算机科学系中年龄大于19岁的学生。例49查询选修课程1的学生集合与选修课程2的学生集合的差集本例实际上是查询选修了课程1但没有选修课程2的学生。

小结问题：有关系模式part(Item_no,Name,P_no)表示一个产品零部件情况及产品的组成〔P_no表示上一级的零件〕，如何用SQL实现查询：查询某个产品〔给定Item_no〕的所有零部件？。3.4

数据更新

插入数据1.

插入单个元组INSERTINTO＜表名＞[(＜属性列1＞[，＜属性列2＞…)]VALUES(＜常量1＞[，＜常量2＞…])例1将一个新学生记录(学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁)插入Student表中。INSERTINTOStudentVALUES(’95020’，‘陈冬’，‘男’，‘IS’，18)；例2插入一条选课记录(’95020’，’1’)INSERTINTOSC(Sno，Cno)VALUES(“95020’，’1’)；2.

插入子查询结果INSERTINTO＜表名＞[(＜属性列1＞[，＜属性列2＞…]]子查询；

例3对每一个系，求学生的平均年龄，并把结果存入数据库。对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，一列存放相应系的学生平均年龄。CREATETABLEDeptage(SdeptCHAR(15)，AvgageSMALLINT)；然后对数据库的student表按系分组求平均年龄，再把系名和平均年龄存入到新表中。INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；

修改数据UPDATE＜表名＞SET＜列名＞＝＜表达式＞[，＜列名＞＝＜表达式＞…][WHERE＜条件＞]；1.

修改某一个元组的值例4将学生95001的年龄改为22岁UPDATEStudentSETSage＝22WHERESno＝‘95001’；2.

修改多个元组的值例5将所有学生的年龄增加1岁。UPDATEStudentSETSage＝Sage+1；3.

带子查询的修改语句例6将计算机科学系全体学生的成绩置零。UPDATESCSETGrade＝0WHERE‘CS’＝(SELECTSdeptFROMStudentWHEREStudent.Sno＝SC.Sno)；或UPDATESCSETGrade=0WHERESnoIN(SELECTSnoFROMStudentWHERESdept=‘CS’)；4.

修改操作与数据库的一致性第一条UPDATE语句修改Student表：UPDATEStudentSETSno＝’96089’WHERESno＝’95007’；第二条UPDATE语句修改SC表:UPDATESCSETSno＝‘96089’WHERESno＝’95007’；问题：SC表中要修改的Sno是引用Student的外键，第一条UPDATE执行后，显然已破坏该参照完整性。这样的过程能正常执行吗？

删除数据DELETEFROM＜表名＞[WHERE＜条件＞]；1.

删除某一个元组的值例7删除学号为95019的学生记录。DELETEFROMStudentWHERESno＝‘95019’；1.

删除多个元组的值例8删除所有的学生选课记录。DELETEFROMSC；2.

带子查询的删除语句例9删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERE‘CS’＝(SELECTSdeptFROMStudentWHEREStudent.Sno＝SC.Sno)；3.5

视图

定义视图1.

建立视图CREATEVIEW＜视图名＞[(＜列名＞[，＜列名＞]…)]AS＜子查询＞如果CREATEVIEW语句仅指定了视图名，省略了组成视图的各个属性列名，那么隐含该视图由子查询中SELECT子句目标列中的诸字段组成。但在以下三种情况下必须明确指定组成视图的所有列名：l

其中某个目标列不是单纯的属性名，而是集函数或列表达式。l

多表连接时选出了几个同名列作为视图的字段。l

需要在视图中为某个列启用新的更适宜的名字。例1建立信息系学生的视图CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept＝‘IS’；本例中省略了视图IS_Student的列名，隐含了该视图由子查询中SELECT子句中的3个目标列名组成。DBMS执行此语句就相当于建立虚表：IS_Student(Sno,Sname,Sage)2.

删除视图DROPVIEW＜视图名＞；例2删除视图IS_Student。DROPVIEWIS_Student；

查询视图对视图的查询转换为对基表的查询的过程称为视图的消解(viewresolution)。例3在信息系学生的视图中找出年龄小于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage＜20；DBMS执行此查询时，将其与IS_Student视图定义中的子查询SELECTSno,Sname,SageFROMStudentWHERESdept＝‘IS’；结合起来，转换成对基表student的查询:SELECTSno，SageFROMStudentWHERESdept＝‘IS’ANDSage＜20；

更新视图更新视图包括插入(INSERT)、删除(DELETE)和修改(UPDATE)三类操作。例如，如定义的视图S_G是由‘学号’和‘平均成绩’两个属性列组成的，其中平均成绩一项为哪一项由SC表中多个元组分组后计算平均值得来的。如果想把视图S_G中学号为95001的学生的平均成绩改成90分，SQL语句如下：UPDATES_GSETGrade＝90WHERESno＝’95001’；但这个对视图的更新是无法转换成对基表SC的更新的，因为系统无法修改各科成绩，以便平均成绩成为90。所以S_G视图是不可更新的。l由一个基表定义的视图，只有含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性，才允许更新；l

由多表连接所定义的视图不允许更新；l

定义中用到GROUPBY子句或集函数的视图不允许更新。

视图的用途1.

视图能够简化用户的操作2.

视图使用户能以多种角度看待同一数据3.

视图对重构数据库提供了一定程度的逻辑独立性例如，将学生关系Student(Sno,Sname,Ssex,Sage,Sdept)分为SX(Sno,Sname,Ssex)和SY(Sno,Sage,Sdept)两个关系，这时原表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：CREATEVIEWStudent(Sno,Sname,Ssex,Sage,Sdept)ASSELECTSX.Sno,Sname,Ssex,Sage,SdeptFROMSX，SYWHERESX.Sno＝SY.Sno；4.

视图能够对机密数据提供平安保护3.6

嵌入式SQL介绍嵌入式SQL(embeddedSQL)嵌入式SQL必须解决以下四个问题：(1)宿主语言编译器不可能识别和接受SQL语言，如何将嵌有SQL的宿主语言编译成可执行码，这是首先要解决的问题；(2)宿主语言和DBMS之间如何传递数据和信息；(3)数据库的查询结果一般是元组的集合，而宿主语言使用变量(单值)，这些元组须逐个地赋值给程序中的变量，供宿主语言处理，其间存在一个转换问题；(4)两者的数据类型有时不完全对应或等价，须解决必要的数据类型转换问题。要进行何种数据类型转换，与宿主语言和DBMS有关。如ORACLE的PRO接口：PRO*C、PRO*PASCAL等。第四章

数据库系统结构4.1

数据库系统的模式结构typevalue

数据库系统的三级模式结构1.

模式2.

外模式3.

内模式图4-1数据库系统的模式结构

数据库的二级映象功能与数据独立性两层映象：外模式/模式映象和模式/内模式映象

Conclusion4.2

数据库系统的体系结构从最终用户角度来看，数据库系统分为单用户结构、主从式结构、分布式结构和客户／效劳器结构等。

单用户数据库系统图4-2单用户数据库系统

主从式结构的数据库系统图4-3主从式数据库系统特点：数据集中、处理集中。

分布式结构的数据库系统

特点：数据分布、处理分布。图4-4分布式数据库系统

Client／Server结构的数据库系统

特点：数据集中、处理分布。特点：数据分布、处理分布。

联邦分布式数据库系统每个结点所看到的数据模式仅仅限于该结点所用到的数据。它—般由两局部组成：一是本结点的数据模式，二是供本结点共享的其他的点上有关的数据模式。结点间的数据共享由双边协商确定。特点：数据分布、处理分布。目前大型主流DBMS〔如ORACLE、SQLServer等〕商品化产品均为支持分布的Client/Server结构。4.3

DatabaseManagementSystem

DBMS的功能和组成1.

数据定义2.

数据操纵3.

数据库运行管理4.

数据组织、存储和管理5.

数据库的建立和维护6.

数据通信接口为了提供上述6方面的功能，DBMS通常由以下4局部组成