《WZM数据库技术》PPT课件

数据库技术,数据库系统概述关系数据库及数据模型关系数据库标准语言SQL,1.数据库系统概述,数据(Data):能够输入计算机并能被计算机程序识别和处理的信息集合。数据的种类:文字、图形、图象、声音,数据库数据库是在数据库管理系统管理和控制之下，存放在存储介质上的数据集合（DataBase）。数据库管理系统（DBMS）（DatabaseManagementSystem，简称DBMS）是位于用户与操作系统之间的一层数据库操作管理软件。,(1)数据库,(2)数据库管理系统（DBMS）,(3)数据库应用(DatabaseApplication),(4)DBA(DataBaseAdministrator-DBA),(5)计算机系统平台,包括：硬件、软件、网络系统等。,数据库系统及构成,硬件、软件、网络,DB、DBMS、DBS之间的关系,数据库应用,DBMS的用途:科学地组织和存储数据、高效地获取和维护数据,DBMS的主要功能,(1)数据定义功能提供数据定义语言(DDL)定义数据库中的数据对象,(2)数据操纵功能:提供数据操纵语言(DML)操纵数据实现对数据库的基本操作(查询、插入、删除和修改)(3)数据库的运行管理提供数据库运行控制语言(DCL)保证数据的安全性、完整性、多用户对数据的并发使用发生故障后的系统恢复,(4)数据库的建立和维护功能(实用程序)数据库数据批量装载数据库转储介质故障恢复数据库的重组织性能监视等,数据库的特征:数据按一定的数据模型组织、描述和储存可为各种用户共享冗余度较小数据独立性较高易扩展,目前主流的数据库:ORACEL,SQLSERVER,DB2,SyBase,2.文件管理阶段,优点：（）数据可长期保存,二、数据管理及其发展,缺点：(）数据冗余度（redundancy）大，,（）数据与程序缺乏高度独立性。,1.人工管理阶段,数据一致性（consistency）、完整性（integrity、空指针）难以维持。,（）能存储大量数据,3.数据库系统阶段,（）统一的数据控制。,（）具有较高的数据与程序之间的独立性。,（）数据冗余度比较小，易扩充。,（）数据组织结构化。,三、数据库技术的发展,1.1969年IBM公司研制了商品化软件IMS(InformationManagementSystem)，IMS的数据模型是层次结构的。,2.DBTG于60年代末至70年代初提出了若干报告，称为DBTG报告，确立了数据库系统的许多概念、方法和技术。DBTG所提议的方法是基于网状结构的。,3.1970年IBM公司SanJone研究实验室的研究员E.F.Codd发表了题为“大型共享数据库数据的关系模型”论文，提出了数据库的关系模型，开创了数据库关系方法和关系数据理论的研究，为数据库技术奠定了理论基础。1978年，美国ANSI/X3/SPARC的SPARC报告，标志数据库技术进入成熟阶段。,查询处理程序,DBMS输入部分,元数据（metadata）,更新,查询,DBMS的构成及工作流程,模式更新,-关于数据的数据,页命令,应用程序,查询/更新,查询计划,索引、文件、记录请求,读写页,数据/元数据/索引,DBA,元数据/统计数据,元数据,模式创建/更新,查询处理器(QueryProcessor),存储管理器(StorageManager),事务管理器(TransactionManager),1.事务：一个事务是访问并可能更新数据库数据的一组数据库操作。,2.事务管理器的任务：维护日志(logging)、并发控制（加锁Locking等技术)、故障恢复。,控制数据的存储和数据在磁盘与主存之间移动。,把对数据库的操作（查询、更新等）转换成对存储器处理的请求。,（查询编译器；执行引擎）,视图层（局部逻辑层）,外模式/模式映像,模式/内模式映像,数据库,模式,全局逻辑层,内模式,物理层,数据库系统的模式（schema）结构,数据抽象！,数据库的模式结构:,2.模式（schema）,3.外模式（externalschema）,三层模式结构：外模式、模式和内模式。,内模式也称存贮模式或物理层。它描述数据的实际存贮方式。,模式也称逻辑模式或逻辑层。它描述数据库所有数据及其联系。,外模式也称子模式、用户模式或视图层。,1.内模式（internalschema）,-DBA使用！,-面向用户！,4.二级映像(map)：,(2)模式/内模式映像,(1)外模式/模式映像,定义了数据逻辑结构与存贮结构之间的对应关系。,定义了各个外模式与模式之间的对应关系。,数据库数据的逻辑独立性和物理独立性,在某一层次上修改该层模式定义而不影响位于上一层模式的能力叫做数据独立性。,1外模式/模式映象,2.模式/内模式映象,解决：数据的物理独立性（逻辑结构独立于物理结构）。,解决：数据的逻辑独立性（局部逻辑结构独立于全局逻辑结构）。,模式改变,内模式改变,映象改变,外模式不变,映象改变,模式不变,C/S模型,管理软件应用架构及发展,管理软件应用架构及发展,B/S模型,多数据源结构,ODBC统一接口,数据集成！,管理软件应用架构及发展,2.1.数据库的类型:,以树状结构描述数据。最常用的层次型数据库是IBM的IMS（InformationManagementSystem）。它把每个记录中的数据元素组成一个个记录块叫做节点。对于用户来说，每个记录就象一个组织图，最上层的节点叫根节点，上下层的节点以父-子逻辑关系关联。父节点可以有多个子节点，而子节点只能有一个父节点。,(1).层次型数据库,2.关系数据库及数据模型,是层次型数据库的变种。实际上，数据库为了优化数据处理，既可以从层次变为网状，也可以从网状变为层次。层次结构描述的是一对多的关系，而网络结构描述的是多对多的关系。,(2).网络型数据库,将数据库中所有数据以二维表（称为关系）的形式来表示。表（Table）看起来类似于一个文件，多个文件中的信息很容易地提取和合并。,(3).关系型数据库,下图列出的是供应商表、零件表、订货表。在每一个表中，每行表示一个记录，每列表示一个字段。用户经常需要用多个相关联的表中的信息来生成报表，而这一点恰好是关系型数据库的威力所在，即它可以将任何一个表与另一个表关联起来，前提是两个表要有共同的数据项。,关系数据库模型：,按用户的观点对数据和信息建模。常常首先将现实世界的客观对象抽象为某一种不依赖于计算机系统和某一个DBMS的信息结构即概念模型，然后再把概念模型转换为计算机上某一DBMS支持的数据模型。如ODL,E-R模型。,2.2.数据模型:,概念模型（信息模型）数据模型,数据模型是数据库系统设计中用于提供信息表示和操作手段的形式构架，分为以下两个层次：,概念模型,按用户观点对数据和信息的建模。,实体：现实世界中存在的对象或事物。属性：实体具有的某种特性。联系：实体内部或实体之间的联系。,概念模型描述E-R方法,E-R方法实体-联系方法（Entity-RelationApproach）。用来描述某一组织的概念（信息）模型。表示方法实体：用矩形表示属性：用椭圆表示关系：用菱形表示联系：用无向边表示,数据模型,数据模型是对客观事物及其联系的数据化描述。在数据库系统中,对现实世界中数据的抽象、描述以及处理等都是通过数据模型来实现的。数据模型在数据库系统设计中是用来提供信息表示和操作手段的形式构架,是数据库系统实现的基础。目前，主要的数据模型:对象模型:ODL关系模型:RelationalModel。,关系模型,每一个关系为一张二维表，相当于一个文件。实体间的联系均通过关系进行描述。,关系模型：应用关系代数和关系演算等数学理论来处理数据库系统中的数据关系。,主要术语关系：一个关系对应一张二维表。元组：二维表中的一行称为一个元组。属性：二维表中的一列称为一个属性。主码（PrimaryKey）：唯一标识一个元组。域：属性的取值范围。分量：元组中的一个属性值。关系模式：用关系名（属性1，属性2，）表示。,下表用m行n列的二维表表示了具有n元组(n-Tuple)的付款关系。每一行即一个n元组,相当于一个记录,用来描述一个实体。,ER模型到关系模型的转换实例,零件（零件号，零件名，规格）组成（零件号，子零件号，数量）,仓库（仓库号，仓库名，地址）商店（商店号，商店名）商品（商品号，商品名）进货（商店号，商品名，仓库号，日期，数量）,关系的规范化,目的：降低数据冗余度，消除插入、修改与删除异常的发生。方法：关系拆分（分解）,关系必须是规范化的关系，满足一定的约束条件。称为范式（NormalForm）。,一范式（1NF）:元组中的每一个分量都是不可分割的数据项，即属性值唯一。,其中：主属性有“教师代码”、“研究课题号”,二范式（2NF）：不仅满足第一范式，而且所有非主属性完全(非部分)依赖于其主码。,Key=“教师代码”+“研究课题号”,1NF转化2NF：关系分解,数组,第三范式（3NF）:不仅满足第二范式，而且它的任何一个非主属性都不传递依赖于任何主关键字。,2NF转化3NF：关系分解,关系模式规范化分析,设车间考核职工完成生产定额关系W：W（日期，工号，姓名，工种，定额，超额，车间，车间主任）,1NF消除非主属性对码的部分函数依赖消除决定属性2NF集非码的非平消除非主属性对码的传递函数依赖凡函数依赖3NF消除主属性对码的部分和传递函数依赖BCNF消除非平凡且非函数依赖的多值依赖4NF,关系模式规范化的基本步骤,数据库操作主要有基本表的建立与删除、数据查询及更改等。一般使用关系数据库标准语言结构化查询语言SQL(StructuredQueryLanguage)来完成上述操作。如：SQL的核心语句是数据库查询语句，一般格式为：SELECTFROMWHEREGROUPBYORDERBYASC/DESC其功能是根据WHERE子句中的条件表达式，从指定表中找出满足条件的元组。,3.关系数据库标准语言SQL,3.1SQL的特征,3.2SQL的数据定义,3.3SQL的数据查询,3.4SQL的数据更新,关系数据库标准语言SQL,3.5SQL的数据控制,其它:嵌入式SQL,动态SQL,ODBC与JDBC,SQL-86。SQL的第一个标准是1986年10月由美国国家标准化组织(ANSI)公布的。SQL-89。ANSI以后通过对SQL-86的不断修改和完善，于1989年第二次公布了SQL标准，即SQL-89，该标准增强了完整性的语言特征。SQL-92(SQL2)。1992年又公布了SQL-92标准，该标准增加了支持对远程数据库的访问，扩充了数据类型、操作类型、动态SQL等许多新的特征。SQL-99(SQL3)。完成于1999年的SQL-99修订本具有更高级的特征。引入了支持对象-关系DBMS模型的SQL，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。,3.1SQL的特征,特点：综合统一非关系模型的数据语言不同模式有不同的数据定义语言及数据操纵语言。SQL语言则集数据定义、操纵和控制功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。,高度非过程化非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。,灵活的使用方式SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。,简洁、通用、功能强SQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词，如表所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。,SQL的基本组成（1）SQL数据库层次结构,术语对照：一般关系模型SQL外模式-视图（VIEW)模式-基本表(TABLE)内模式-存储文件（索引）元组-行(ROW)属性-列(COLUMN),基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,（2）SQL语言的组成数据定义语言（DDL）数据操纵语言（DML）数据控制语言（DCL）嵌入与会话规则（3）SQL的语句类型SQL模式语句SQL数据语句SQL事务与控制语句SQL连接、会话及诊断语句,(4)SQL的数据类型预定义数据类型构造数据类型用户定义数据类型（UDT，UserDefinedType）,SQL环境（1）SQL模式与目录SQL模式：基本表的集合。好处：允许在不同的SQL模式中出现同名的基表名或视图名。目录：SQL环境中所有模式的集合。定位基表的方式：（2）SQL环境设置默认的目录和模式设置用户身份,（3）存储过程存储过程是存储在SQL服务器上的预编译好的一组为了完成特定功能的SQL语句集。通过指定存储过程的名字并给出参数来执行它。可分为两类：系统存储过程：由系统自动创建，完成的功能主要是从系统表中获取信息。用户定义的存储过程：由用户为完成某一特定功能而编写的存储过程。使用存储过程的好处：可减少网络流量。增强代码的重用性和共享性。加快系统运行速度。保证数据安全性。,3.2SQL的数据定义,一.SQL模式的定义与撤销（1）SQL模式的定义CREATESCHEMA模式名AUTHRIZATION用户名CREATEDOMAIN子句|CREATETABLE子句|CREATEVIEW|例；CREATESCHEMATeaching_dbAUTHRIZATIONHang；（2）数据库模式的删除DROPSCHEMA模式名CASCADE|RESTRICTCASCADE(级联式)：RESTRICT（约束式)：,二.表的建立和删除（1）表的建立命令格式：CREATETABLE(列级完整性约束条件，列级完整性约束条件.),;例：CREATETABLEStudent(snoCHAR(5)NOTNULLUNIQUE，snameCHAR(20)NOTNULL，sexCHAR(1)，ageINT，deptCHAR(15)；,主码,完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。,注意：,例：定义P.76的三个表结构，并指定相应的数据完整性约束条件。,分析,外码：sno,cno,主码：sno,姓名：非空,性别：男、女两值,Student表：,Course表：,主码：cno,课程名：非空,外码：pcno,SC表：,主码：(sno,cno),成绩：0100,CREATETABLEStudent(snoCHAR(5),snameCHAR(8)NOTNULL,sexCHAR(2),ageSMALLINT,deptCHAR(20),PRIMARYKEY(sno),CHECKsexIN(男,女);,列级完整性约束条件,实体完整性约束条件,用户自定义完整性约束条件,CREATETABLECourse(cnoCHAR(4),cnameCHAR(10)NOTNULL,pcnoCHAR(4),creditSMALLINT,PRIMARYKEY(cno),FOREIGNKEY(pcno)REFERENCESCourse(cno);,参照完整性约束条件,CREATETABLESC(snoCHAR(5),cnoCHAR(4),gradeSMALLINT,PRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESStudent(sno),FOREIGNKEY(cno)REFERENCESCourse(cno),CHECK(gradeISNULL)OR(gradeBETWEEN0AND100);,（2）表的删除格式：DROPTABLECASCADE|RESTRICT基本表定义一旦删除，表中的数据、在此表上建立的视图、索引、触发器、断言都将自动被删除掉。RESTRICT确保只有不具有相关对象的表才能被撤销。例：DROPTABLEStudentCASCADE；,三.基本表的扩充和修改一般格式为：ALTERTABLEADD完整性约束DROPMODIFY;其中指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。（1）在现存表中增加新列格式：ALTERTABLEADD(，),（2）删除已存在的某个列格式：ALTERTABLEDROPCASCADE|RESTRICT例：ALTERTABLEStudentDROPaddr；（3）修改原有列的类型格式：ALTERTABLEMODIFY；例：ALTERTABLEStudentMODIFYplaceCHAR(8)；,（4）补充定义主码格式：ALTERTABLEADDPRIMARYKEY(),（5）删除主码格式：ALTERTABLEDROPPRIMARYKEY,修改基本表实例例向Student表增加“入学时间”列，其数据类型为日期型ALTERTABLEStudentADDScomeDATE;例将年龄的数据类型改为半字长整数ALTERTABLEStudentMODIFYSageSMALLINT;例删除关于学号必须取唯一值的约束ALTERTABLEStudentDROPUNIQUE(Sno);,四.索引的建立与删除建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。命令格式：CREATEUNIQUECLUSTERINDEXON(，)；次序：升序（ASC，缺省）降序（DESC）UNIQUE:每一个索引值只对应惟一的数据记录。CLUSTER:建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。,注意：在一个基本表上最多只能建立一个聚簇索引。经常更新的列不宜建立聚簇索引。所建索引放何处？例:为Student表按学号升序建惟一聚簇索引。为SC表按学号升序和课程号降序建惟一索引。CREATEUNIQUECLUSTERINDEXStnoONStudent(Sno)；CREATEUNIQUEINDEXScnoONSC(Sno，CnoDESC)；删除索引一般格式为：DROPINDEX；例：DROPINDEXStno；删除索引时，系统会同时从数据字典中删去有关该索引的描述。,一般形式：SELECTFROMWHEREGROUPBYHAVINGORDERBYASC|DESC;,基本语句含义:根据WHERE中的F，从基表或视图R中找出满足条件的元组，再从中选出目标属性值形成结果表。,查询目标,数据来源,选择元组的条件,将结果按的值进行分组,满足条件的组才予输出,按排序查询结果,3.3SQL的数据查询,如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常在成组后再使用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按的值的升序或降序排序。,其中：表示其中的成分为任选项。：表示其中的成分由用户具体给定。|：表示其中并列的成分只能择一。查询目标：ALL：表示保留满足条件的所有元组（缺省）。DISTINCT：表示去掉重复元组。目标列：可以为属性名、表达式、通配符*（表示所有属性列）。,学生-课程数据库:Student(sno，sname，sex，age，dept)Course(cno，cname，pcno，credit)SC(sno，cno，grade)3.3.1单表查询一、选择表中的列例：查询所有学生的姓名、学号、所在系。,SELECTsname，sno，deptFROMStudent（次序不同）;,目标A来源R条件F,snamesnodept王萧虎200101信息院,查询经过计算的值SELECT子句的不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。例4查全体学生的姓名及其出生年份SELECTSname,2006-SageFROMStudent;例5查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名SELECTSname,YearofBirth:,1996-Sage,ISLOWER(Sdept)FROMStudent;,例：查全体学生的姓名及其出生年份。SELECTsname，2007-ageASFROMStudent；,sname2005-age王萧虎1987,Birthday,别名,Birthday,二、选择表中的行1消除取值重复的行例：查考试成绩不及格的学号。,SELECTsnoFROMSCWHEREgrade=60,五、对查询结果分组：GROUPBY子句将查询结果表按某一(多)列值分组，值相等的为一组。目的：细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。例查询各个课程号与相应的选课人数SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;,该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为：CnoCOUNT(Sno)-122234344433548,如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。例：求每个学生（号）的平均成绩，并将其超过88分的输出。SELECTsno，AVG(grade)FROMSCGROUPBYsno；,HAVING短语：在各组中选择满足条件的小组。WHERE子句：在表中选择满足条件的元组。,WHERE?,HAVINGAVG(grade)88；,集函数在成组之前不计算，因此不能用于WHERE子句,一般将简单条件写入WHERE。,HAVING子句的条件运算数之一是一个集函数。,若HAVING子句无前导GROUPBY，选择清单中不能含有非集函数列。,练习：查询至少选修4门课程的学生学号。例：找出选课学生超过30人的课程的平均成绩及选课人数。SELECTcno,AVG(grade),COUNT(*)ASst_numberFROMSCGROUPBYsnoHAVINGst_number=30,例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。SELECTage,COUNT(sno)ASnumberFROMStudentWHEREsex=女GROUPBYageHAVINGnumber20ORDERBYnumber,ageDESC,3.3.3嵌套查询1.子查询（嵌套查询）查询块：SELECTFROMWHERE子查询（嵌套查询）：一个查询块嵌套在另一查询块的条件之中。上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。,子查询一般跟在IN、SOME（ANY）、ALL和EXIST等谓词后面。SQL语言允许多层嵌套查询。嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是SQL(StructurredQueryLanguage)中“结构化”的含义所在。,(1)带有IN谓词的子查询带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。例：查询与“刘晨”在同一个系学习的学生的学号、姓名、系确定“刘晨”所在系名SELECTSdeptFROMStudentWHERESname=刘晨;查找所有在IS系学习的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdept=IS;,子查询实现：将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。WHERESdeptIN(SELECT),例:查询选修了数据库课程的学生号、成绩。SELECTsno，gradeFROMSCWHEREcnoIN(SELECTcnoFROMCourseWHEREcname=数据库)；,（2）带有比较运算符的子查询例:找出年龄超过平均年龄的学生姓名。SELECTsnameFROMStudentWHEREage(SELECTAVG(age)FROMStudent)；,（3）带SOME(ANY)、ALL谓词的子查询SOME大于子查询结果中的某个值90);,例：找出(平均)成绩最高的学生号。SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(grade)=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)；,SELECTsnoFROMSCWHEREAVG(grade)=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)?,例：查询其他系中比IS系任一学生年龄大的学生名单SELECTSname,SageFROMStudentWHERESage(SELECTMAX(Sage)FROMStudentWHERESdept=IS)ANDSdeptIS,SELECTStudent.sno，sname，sex，age，dept，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.sno；,2.条件连接查询连接条件的一般格式为：.当连接运算符为=时，称为等值连接。例:查询每个学生的情况及其选课成绩。,例：找出系为信息系，选课成绩为90分以上的学生的姓名、课号和成绩。SELECTsname，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.snoANDdept=信息系ANDgrade90；,例:查询选修了数据库的学生号、成绩SELECTsno，gradeFROMSCWHEREcnoIN(SELECTcnoFROMCourseWHEREcname=数据库)；,学分？,SELECTsno，gradeFROMSC，CourseWHERESC.cno=CoANDcname=数据库；,，Ccredit,=,子查询,连接查询,例：按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括008号考查课。SELECTStudent.sno，sname，AVG(grade)ASavg_gFROMStudent，SCWHEREStudent.sno=SC.snoANDcno008GROUPBYsnoHAVINGMIN(grade)=60ORDERBYavg_gDESC；,3.自身连接查询例：找出年龄比“王迎”同学大的同学的姓名及年龄。SELECTs1.sname，s1.ageFROMSASs1，SASs2WHEREs1.ages2.ageANDs2.sname=王迎；,其他方法？,练习：查询每一门课的间接先修课（即先修课的先修课）。SELECTFIRST.cno,SECOND.pcnoFROMCourseFIRST,CourseSECONDWHEREFIRST.pcno=SECOND.cno;,4.FROM子句中的子查询在FROM子句中可以使用子查询，其查询的结果表称为导出关系（DerivedRelation）。例：查出选课成绩在80分以上的女学生的姓名、课程名和成绩。SELECTsname，cname，gradeFROM(SELECTsname，cname，gradeFROMStudent，SC，CourseWHEREStudent.sno=SC.snoANDSC.cno=CoANDsex女)ASTemp(sname，cname，grade)WHEREgrade=80；,导出关系,5.相关子查询当一个子查询的判断条件涉及到一个来自外部查询的列时，称为相关子查询。带存在谓词的子查询：只产生逻辑值存在谓词EXISTS作用:若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。,例：查询所有选修了1号课程的学生姓名。SELECTsnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDcno=1)；,分析：查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，在Student关系中依次取每个元组的Sno值，用此Student.Sno值去检查SC关系，若SC中存在这样的元组：其SC.Sno值等于用来检查的Student.Sno值，并且其SC.Cno=1，则取此Student.Sname送入结果关系。,相关子查询的一般处理过程是：首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（即Sno值）处理内层查询，若WHERE子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。其它方法？优点？,SQL语言中没有全称量词（Forall）。因此对于求所有的操作，必须利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词。SQL语言中也没有蕴涵（Implication）逻辑运算。因此也必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词。,例:查询选修了全部课程的学生姓名。转换成：查询这样的学生，没有一门课程是他不选修的。(x)P(x(P),SELECTsnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHEREsno=Student.snoANDcno=Co)；,每门课都没选,某学生选某课,练习：查询至少选修了学号为200101的学生选修的全部课程的学生学号。分析：查询这样的学生，凡是200101选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为200101的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生200101选修了y，而学生x没有选。,SELECTsnoFROMStudentXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.sno=200101ANDNOTEXISTS(SELECT*FROMSCZWHEREX.sno=Z.snoANDY.cno=Z.cno),6.集合运算（1）UNION（并）（2）INTERSECT（交）（3）EXCEPT（差）例：求选修了001或002号而没有选003号课程的学生号。(SELECTsnoFROMSCWHEREcno=001ORcno=002)EXCEPT(SELECTsnoFROMSCWHEREcno=003)；,3.3.4自然连接与外连接1.自然连接(NATURALJOIN)例：查系别为信息、课程成绩在90分以上的学生档案及其成绩情况。SELECT*FROMStudentNATURALJOINSCWHEREdept=信息ANDgrade=90；2.外连接左外连接。LEFTOUTERJOIN，保留左关系的所有元组。右外连接。RIGHTOUTERJOIN，保留右关系的所有元组。全外连接。FULLOUTERJOIN，保留左右两关系的所有元组。,FROMSC,Student?,自然连接的定义？,教师表：Teacher(教师号，姓名，所属大学，职称)任职表：Post(编号，姓名，职务)例：SELECT*FROMTeacherFULLOUTERJOINPost；例：SELECT*FROMTecherLEFTOUTERJOINPost；例：SELECT*FROMTecherRIGHTOUTERJOINPost；,3.3.5SQL中的空值处理1.空值的含义值暂未知。值不适用。值需隐瞒。2.空值的若干规则（1）空值与数值型数据进行算术运算，结果为空值。（2）空值和任何值(包括空值)进行比较运算，结果为UNKNOWN。,（3）空值的布尔运算UANDT=U；UANDF=F；UANDU=U；UORT=T；UORF=U；UORU=U；NOTU=U。空值的测试：ISNULL和ISNOTNULL。例：查询缺少选课成绩的学生号和相应的课程号。SELECTSno，CnoFROMSCWHEREgradeISNULL；,3.3.6递归合并查询递归合并语句格式：WITHRECURSIVEASUNION例：对表3.6所示的航班表Flight(airline，from，to，depart，arrive)，求出：能从一个城市飞到另一个城市的城市对集合(含直接到达和间接中转到达)。,直接到达：Reach(f，t)=Flight(a，f，t，d，r)f，t在表的同一个元组中。间接到达：Reach(f，t)=Flight(a，f，t1，d，r)ANDReach(t1，t)SQL语句：WITHRECURSIVEReach(from，to)AS(SELECTfrom，to/选出直接到达城市对FROMFlight)UNION(SELECTFlight.from，Reach.to/选出间接到达城市对FROMFlight，Reach/递归WHEREFlight.to=Reach.from)SELECT*FROMReach；,例：有下列所示的一棵二叉树：,求出:该子树中除子树根结点k以外的所有子孙结点的集合。,分析：定义结果集为Tsub(id，name)：结点k的所有子女：Tsub(id，name)=Tree(id，pid，name)ANDpid=k结点k的所有子女的孩子（k的子孙）：Tsub(id，name)=Tsub(id1，name1)ANDTree(id，id1，name)SQL:WITHRECURSIVETsub(id，name)AS(SELECTid，nameFROMTree/结点k的所有子女WHEREpid=k)UNION(SELECTTree.id，TFROMTsub，Tree/结点k的所有子孙WHERETsub.id=Tree.pid)SELECT*FROMTsub；,视图的概念及作用是从一个或几个基本表(或视图)导出的表，是虚表。与表一样可被查询。对视图的更新操作有一定的限制。对视图的一切操作最终将转换为对基本表的操作。视图的作用：（1）简化结构及复杂操作。（2）多角度地、更灵活地共享。（3）提高逻辑独立性。（4）提供安全保护。,3.4SQL的数据更新,一.插入数据1.插入单个元组格式：INSERTINTO（，)VALUES(,);说明：（1）若插入全部属性，则属性列可省略。（2）表定义中说明为NOTNULL的列不能取空值。（3）属性值与相对应的属性列的数据类型要匹配。（4）向参照表中插入元组，关系系统自动支持：实体完整性参照完整性,2.插入子查询结果格式：INSERTINTO(,)子查询；二.修改数据格式：UPDATESET=,=WHERE;三.删除数据格式：DELETEFROMWHERE;,四.构造数据类型1.聚合类型ARRAY例：CREATETABLEmailout(nameCHAR(8)，addressCHAR(20)ARRAY3)；INSERTINTOTABLEmailout(name，address)VALUES(Wang，ARRAY20HongsangRoad，Wuhan，430079)；,2.行类型格式：ROW(，例：CREATETABLElineage(nameCHAR(8)，statusCHAR(10)，lastoneROW(babyCHAR(8)，birthDATE)；/插入数据操作SELECTname，status，lastoneFROMlineageWHERElastone.baby=Liming；,作为一个整体,3.5SQL的视图,创建与使用视图1.创建视图一般格式：CREATEVIEW(,)ASWITHCHECKOPTION；其中：子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT短语。WITHCHECKOPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。作用：命名一个视图，AS子句定义每次查看视图时将看到的数据，在任何时刻，视图的数据由对其查询定义求值的结果行构成。,例1：建立信息院学生的视图，CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=信息院；,例2：建立信息院学生的视图，要求进行更新时仍保证该视图只有信息院的学生。CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=信息院；WITHCHECKOPTION,视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。例3建立信息系选修了1号课程的学生的视图CREATEVIEWIS_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=1;,例4建立信息系选修了1号课程且成绩在90分以上的学生的视图CREATEVIEWIS_S2ASSELECTSno,Sname,GradeFROMIS_S1WHEREGrade=90;这里的视图IS_S2就是建立在视图IS_S1之上的。,带虚拟列的视图：是指设置了一些基本表中并不存在的派生列(虚拟列)的视图。定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列，带虚拟列的视图也称为带表达式的视图。例5定义一个反映学生出生年份的视图CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,1996-SageFROMStudent;,分组视图：带有集函数和GROUPBY子句查询所定义的视图。例6将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型，否则无法求平均值。CREATVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;例7将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student(stdnum,name,sex,age,dept)ASSELECT*FROMStudentWHERESsex=女;,删除视图DROPVIEWCASCADE|RESTRICT注意：视图删除后，视图的定义将从数据字典中自动删除。但要删除该视图导出的其他视图应用CASCADE。,作用：撤销一个视图，但不消除数据，在基础表中的数据仍然保留。RESTRICT确保只有不具有相关对象的视图（不涉及任何的约束、断言、触发器）才能被撤消。,查询视图视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（ViewResolution）。,例1在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,SageFROMIS_StudentWHERESage20;视图是定义在基本上的虚表，它可以和其他基本表一起使用，实现连接查询或嵌套查询。例2查询信息系选修了1号课程的学生SELECTSno,SnameFROMIS_Student,SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno=1;本查询涉及虚表IS_Student和基本表SC，通过这两个表的连接来完成用户请求。,更新视图最终要转换为对基本表的更新。注意：并不