WZM数据库技术

1、数据库技术,数据库系统概述 关系数据库及数据模型 关系数据库标准语言SQL,1. 数据库系统概述,数据(Data): 能够输入计算机并能被计算机程序识别和处理的信息集合。数据的种类: 文字、图形、图象、声音,数据库 数据库是在数据库管理系统管理和控制之下，存放在存储介质上的数据集合（DataBase）。 数据库管理系统（DBMS） （Database Management System，简称DBMS）是位于用户与操作系统之间的一层数据库操作管理软件。,(1) 数据库,(2) 数据库管理系统（DBMS）,(3) 数据库应用( Database Application ),(4) DBA ( Da

2、ta Base Administrator-DBA ),(5) 计算机系统平台, 包括：硬件、软件、网络系统等。,数据库系统及构成,硬件、软件、网络,DB、DBMS、DBS之间的关系,数据库应用,DBMS的用途:科学地组织和存储数据、高效地获取和维护数据,DBMS的主要功能,(1) 数据定义功能 提供数据定义语言(DDL) 定义数据库中的数据对象,(2) 数据操纵功能: 提供数据操纵语言(DML) 操纵数据实现对数据库的基本操作 (查询、插入、删除和修改) (3) 数据库的运行管理 提供数据库运行控制语言(DCL) 保证数据的安全性、完整性、 多用户对数据的并发使用 发生故障后的系统恢复,(4

3、) 数据库的建立和维护功能(实用程序) 数据库数据批量装载 数据库转储 介质故障恢复 数据库的重组织 性能监视等,数据库的特征: 数据按一定的数据模型组织、描述和储存 可为各种用户共享 冗余度较小 数据独立性较高 易扩展,目前主流的数据库: ORACEL,SQL SERVER,DB2,SyBase,2. 文件管理阶段,优点：（）数据可长期保存,二、数据管理及其发展,缺点： (）数据冗余度（redundancy）大，,（）数据与程序缺乏高度独立性。,1. 人工管理阶段,数据一致性（consistency）、 完整性（integrity、空指针）难以维持。,（）能存储大量数据,3.数据库系统阶段,

4、（）统一的数据控制。,（）具有较高的数据与程序之间的独立性。,（）数据冗余度比较小，易扩充。,（）数据组织结构化。,三、数据库技术的发展,1. 1969年IBM公司研制了商品化软件IMS (Information Management System)，IMS的数据模型是层次结构的。,2. DBTG 于60年代末至70年代初提出了若干报告，称为DBTG报告，确立了数据库系统的许多概念、方法和技术。DBTG所提议的方法是基于网状结构的。,3. 1970年IBM 公司San Jone研究实验室的研究员 E.F.Codd 发表了题为“大型共享数据库数据的关系模型”论文，提出了数据库的关系模型，开创了数

5、据库关系方法和关系数据理论的研究，为数据库技术奠定了理论基础。1978年，美国ANSI/X3/SPARC 的SPARC报告，标志数据库技术进入成熟阶段。,查询处理程序,DBMS 输入部分,元数据（metadata）,更新,查询,DBMS的构成及工作流程,模式更新,-关于数据的数据,页命令,应用程序,查询/更新,查询计划,索引、文件、记录请求,读写页,数据/元数据/索引,DBA,元数据/统计数据,元数据,模式创建/更新,查询处理器(Query Processor),存储管理器(Storage Manager),事务管理器(Transaction Manager),1. 事务：一个事务是访问并可能

6、更新数据库数据的一组数据库操作。,2. 事务管理器的任务：维护日志(logging) 、并发控制（加锁Locking等技术)、故障恢复。,控制数据的存储和数据在磁盘与主存之间移动。,把对数据库的操作（查询、更新等）转换成对存储器处理的请求。,（查询编译器；执行引擎）,视图层（局部逻辑层）,外模式/模式映像,模式/内模式映像,数据库,模式,全局逻辑层,内模式,物理层,数据库系统的模式（schema）结构,数据抽象！,数据库的模式结构 :,2. 模式（schema）,3. 外模式（external schema）,三层模式结构：外模式、模式和内模式。,内模式也称存贮模式或物理层。它描述数据的实际存

7、贮方式。,模式也称逻辑模式或逻辑层。它描述数据库所有数据及其联系。,外模式也称子模式、用户模式或视图层。,1. 内模式（internal schema）,-DBA使用！,-面向用户！,4. 二级映像(map)：,(2) 模式 / 内模式映像,(1) 外模式 / 模式映像,定义了数据逻辑结构与存贮结构之间的对应关系。,定义了各个外模式与模式之间的对应关系。,数据库数据的逻辑独立性和物理独立性,在某一层次上修改该层模式定义而不影响位于上一层模式的能力叫做数据独立性。,1外模式/模式映象,2. 模式/内模式映象,解决：数据的物理独立性（逻辑结构独立于物理结构）。,解决： 数据的逻辑独立性（局部逻辑结

8、构独立于全局逻辑结构）。,模式改变,内模式改变, 映象改变, 外模式不变, 映象改变, 模式不变,C/S模型,管理软件应用架构及发展,管理软件应用架构及发展,B/S模型,多数据源结构,ODBC统一接口,数据集成！,管理软件应用架构及发展,2.1. 数据库的类型:,以树状结构描述数据。最常用的层次型数据库是IBM的IMS（Information Management System）。它把每个记录中的数据元素组成一个个记录块叫做节点。对于用户来说，每个记录就象一个组织图，最上层的节点叫根节点，上下层的节点以父-子逻辑关系关联。父节点可以有多个子节点，而子节点只能有一个父节点。,(1).层次型数据库

9、,2. 关系数据库及数据模型,是层次型数据库的变种。实际上，数据库为了优化数据处理，既可以从层次变为网状，也可以从网状变为层次。层次结构描述的是一对多的关系，而网络结构描述的是多对多的关系。,(2). 网络型数据库,将数据库中所有数据以二维表（称为关系）的形式来表示。表（Table）看起来类似于一个文件，多个文件中的信息很容易地提取和合并。,(3). 关系型数据库,下图列出的是供应商表、零件表、订货表。在每一个表中，每行表示一个记录，每列表示一个字段。用户经常需要用多个相关联的表中的信息来生成报表，而这一点恰好是关系型数据库的威力所在，即它可以将任何一个表与另一个表关联起来，前提是两个表要有共

10、同的数据项。,关系数据库模型：,按用户的观点对数据和信息建模。常常首先将现实世界的客观对象抽象为某一种不依赖于计算机系统和某一个DBMS的信息结构即概念模型，然后再把概念模型转换为计算机上某一DBMS支持的数据模型。如ODL,E-R模型。,2.2. 数据模型:,概念模型（信息模型） 数据模型,数据模型是数据库系统设计中用于提供信息表示和操作手段的形式构架，分为以下两个层次：,概念模型,按用户观点对数据和信息的建模。,实体：现实世界中存在的对象或事物。 属性：实体具有的某种特性。 联系：实体内部或实体之间的联系。,概念模型描述 E-R方法,E-R方法 实体-联系方法（Entity-Relatio

11、n Approach）。用来描述某一组织的概念（信息）模型。 表示方法 实体：用矩形表示 属性：用椭圆表示 关系：用菱形表示 联系：用无向边表示,数据模型,数据模型是对客观事物及其联系的数据化描述。在数据库系统中,对现实世界中数据的抽象、描述以及处理等都是通过数据模型来实现的。数据模型在数据库系统设计中是用来提供信息表示和操作手段的形式构架,是数据库系统实现的基础。 目前，主要的数据模型: 对象模型: ODL 关系模型: Relational Model。,关系模型,每一个关系为一张二维表，相当于一个文件。实体间的联系均通过关系进行描述。,关系模型：应用关系代数和关系演算等数学理论来处理数据库

12、系统中的数据关系。,主要术语 关系：一个关系对应一张二维表。 元组：二维表中的一行称为一个元组。 属性：二维表中的一列称为一个属性。 主码（Primary Key）：唯一标识一个元组。 域：属性的取值范围。 分量：元组中的一个属性值。 关系模式：用关系名（属性1，属性2，）表示。,下表用m行n列的二维表表示了具有n元组(n-Tuple)的付款关系。每一行即一个n元组,相当于一个记录,用来描述一个实体。,ER模型到关系模型的转换实例,零件（零件号，零件名，规格） 组成（零件号，子零件号，数量）,仓库（仓库号，仓库名，地址） 商店（商店号，商店名） 商品（商品号，商品名） 进货（商店号，商品名，仓

13、库号，日期，数量）,关系的规范化,目的： 降低数据冗余度，消除插入、修改与删除 异常的发生。 方法：关系拆分（分解）,关系必须是规范化的关系，满足一定的约束条件。称为范式（Normal Form）。,一范式（1NF）:元组中的每一个分量都是不可分 割的数据项，即属性值唯一。,其中：主属性有“教师代码”、“研究课题号”,二范式（2NF）：不仅满足第一范式，而且所有非 主属性完全(非部分)依赖于其主 码。,Key = “教师代码”+“研究课题号”,1NF转化2NF：关系分解,数组,第三范式（3NF）:不仅满足第二范式，而且它的 任何一个非主属性都不传递依 赖于任何主关键字。,2NF转化3NF：关系

14、分解,关系模式规范化分析,设车间考核职工完成生产定额关系W： W（日期，工号，姓名，工种，定额，超额，车间，车间主任）,1NF 消除非主属性对码的部分函数依赖 消除决定属性 2NF 集非码的非平 消除非主属性对码的传递函数依赖 凡函数依赖 3NF 消除主属性对码的部分和传递函数依赖 BCNF 消除非平凡且非函数依赖的多值依赖 4NF,关系模式规范化的基本步骤,数据库操作主要有基本表的建立与删除、数据查询及更改等。一般使用关系数据库标准语言结构化查询语言SQL (Structured Query Language)来完成上述操作。 如：SQL的核心语句是数据库查询语句，一般格式为： SELECT

15、 FROM WHERE GROUP BY ORDER BY ASC/DESC 其功能是根据WHERE子句中的条件表达式，从指定表中找出满足条件的元组 。,3. 关系数据库标准语言SQL,3.1 SQL的特征,3.2 SQL的数据定义,3.3 SQL的数据查询,3.4 SQL的数据更新,关系数据库标准语言SQL,3.5 SQL的数据控制,其它: 嵌入式 SQL ,动态 SQL, ODBC与JDBC,SQL-86。SQL的第一个标准是1986年10月由美国国家标准化组织(ANSI)公布的。 SQL-89。ANSI以后通过对SQL-86的不断修改和完善，于1989年第二次公布了SQL标准，即SQL-

16、89，该标准增强了完整性的语言特征。 SQL-92(SQL2)。1992年又公布了SQL-92标准，该标准增加了支持对远程数据库的访问，扩充了数据类型、操作类型、动态SQL等许多新的特征。 SQL-99(SQL3)。完成于1999年的SQL-99修订本具有更高级的特征。引入了支持对象-关系DBMS模型的SQL，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。,3.1 SQL的特征,特点： 综合统一 非关系模型的数据语言不同模式有不同的数据定义语言及数据操纵语言。SQL语言则集数据定义、操纵和控制功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义

17、关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。,高度非过程化 非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。,灵活的使用方式 SQL语言既是自含式语言，

18、又是嵌入式语言。 作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。,简洁、通用、功能强 SQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词，如表所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。,SQL的基

19、本组成 （1）SQL数据库层次结构,术语对照： 一般关系模型 SQL 外模式-视图（VIEW) 模式-基本表(TABLE) 内模式-存储文件（索引） 元组-行(ROW) 属性-列(COLUMN),基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQ

20、L语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,（2）SQL语言的组成 数据定义语言（DDL） 数据操纵语言（DML） 数据控制语言（DCL） 嵌入与会话规则 （3）SQL的语句类型 SQL模式语句 SQL数据语句 SQL事务与控制语句 SQL连接、会话及诊断语句,(4) SQL的数据类型 预定义数据类型 构造数据类型 用户定义数据类型（UDT，User Defined Type）,SQL环境 （1）SQL模式与目录 SQL模式：基本表的集合。 好处：允许在不同的SQL模式中出现同名的基表名或视图名。 目录：SQL环境中所有模式的集合。 定位基表的方

21、式： （2）SQL环境 设置默认的目录和模式 设置用户身份,（3）存储过程 存储过程是存储在SQL服务器上的预编译好的一组为了完成特定功能的SQL语句集。 通过指定存储过程的名字并给出参数来执行它。可分为两类： 系统存储过程：由系统自动创建，完成的功能主要是从系统表中获取信息。 用户定义的存储过程：由用户为完成某一特定功能而编写的存储过程。 使用存储过程的好处： 可减少网络流量。 增强代码的重用性和共享性。 加快系统运行速度。 保证数据安全性。,3.2 SQL的数据定义,一. SQL模式的定义与撤销 （1）SQL模式的定义 CREATE SCHEMA 模式名 AUTHRIZATION 用户名

22、CREATE DOMAIN子句|CREATE TABLE子句|CREATE VIEW | 例；CREATE SCHEMA Teaching_db AUTHRIZATION Hang； （2）数据库模式的删除 DROP SCHEMA 模式名 CASCADE | RESTRICT CASCADE(级联式) ： RESTRICT（约束式)：,二. 表的建立和删除 （1）表的建立 命令格式： CREATE TABLE ( 列级完整性约束条件 ， 列级完整性约束条件.) ,;例：CREATE TABLE Student ( sno CHAR(5) NOT NULL UNIQUE， sname CHAR(

23、20) NOT NULL ， sex CHAR(1)， age INT ， dept CHAR(15) )；,主码,完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。,注意：,例：定义P.76的三个表结构，并指定相应的数据完整性约束条件。,分析,外码：sno,cno,主码：sno,姓名：非空,性别：男、女两值,Student表：,Course表：,主码：cno,课程名：非空,外码：pcno,SC表：,主码：(sno,cno),成绩：0100,CREATE TABLE Student ( sno CHAR (5), sname CHAR (8) NO

24、T NULL, sex CHAR (2), age SMALLINT, dept CHAR (20), PRIMARY KEY(sno), CHECK sex IN (男,女) );,列级完整性约束条件,实体完整性约束条件,用户自定义完整性约束条件,CREATE TABLE Course ( cno CHAR (4), cname CHAR (10) NOT NULL, pcno CHAR (4), credit SMALLINT, PRIMARY KEY (cno), FOREIGN KEY (pcno) REFERENCES Course(cno) );,参照完整性约束条件,CREATE

25、TABLE SC ( sno CHAR (5), cno CHAR (4) , grade SMALLINT, PRIMARY KEY (sno,cno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (cno)REFERENCES Course(cno), CHECK (grade IS NULL) OR (grade BETWEEN 0 AND 100) );,（2）表的删除 格式：DROP TABLE CASCADE | RESTRICT 基本表定义一旦删除，表中的数据、在此表上建立的视图、索引、触发器、断言都将自动被删除掉

26、。RESTRICT确保只有不具有相关对象的表才能被撤销。 例：DROP TABLE Student CASCADE；,三.基本表的扩充和修改 一般格式为： ALTER TABLE ADD 完整性约束DROP MODIFY ; 其中指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。 （1）在现存表中增加新列 格式：ALTER TABLE ADD ( ， ),（2）删除已存在的某个列 格式：ALTER TABLE DROP CASCADE | RESTRICT 例：ALTER TABLE Student

27、 DROP addr； （3）修改原有列的类型 格式：ALTER TABLE MODIFY ； 例：ALTER TABLE Student MODIFY place CHAR(8)；,（4）补充定义主码 格式：ALTER TABLE ADD PRIMARY KEY ( ),（5）删除主码 格式：ALTER TABLE DROP PRIMARY KEY,修改基本表实例 例 向Student表增加“入学时间”列，其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 例 将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Sag

28、e SMALLINT; 例 删除关于学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno);,四.索引的建立与删除 建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。 命令格式： CREATE UNIQUE CLUSTER INDEX ON ( ，)； 次序： 升序（ASC，缺省） 降序 （DESC） UNIQUE: 每一个索引值只对应惟一的数据记录。 CLUSTER: 建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。,注意：在一个基本表上最多只能建立一个聚簇索引。 经常更新的列不宜建立聚簇索引。 所建索引放何处？ 例

29、: 为Student表按学号升序建惟一聚簇索引。 为SC表按学号升序和课程号降序建惟一索引。 CREATE UNIQUE CLUSTER INDEX Stno ON Student(Sno)； CREATE UNIQUE INDEX Scno ON SC(Sno ，Cno DESC)； 删除索引一般格式为： DROP INDEX； 例： DROP INDEX Stno ； 删除索引时，系统会同时从数据字典中 删去有关该索引的描述。,一般形式： SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC;,基本语句含义: 根据WHERE中的F，从基表或

30、视图R中找出满足条件的元组，再从中选出目标属性值形成结果表。,查询目标,数据来源,选择元组的条件,将结果按的值进行分组,满足条件的组才予输出,按排序查询结果,3.3 SQL的数据查询,如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常在成组后再使用集函数。 如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 如果有ORDER子句，则结果表还要按的值的升序或降序排序。,其中：表示其中的成分为任选项。 ：表示其中的成分由用户具体给定。 |： 表示其中并列的成分只能择一。 查询目标： ALL：表示保留满足条件的所有元组（

31、缺省）。 DISTINCT：表示去掉重复元组。 目标列：可以为属性名、表达式、通配符*（表示所有属性列）。,学生-课程数据库: Student(sno，sname，sex，age，dept) Course(cno，cname，pcno，credit) SC(sno，cno，grade) 3.3.1 单表查询 一、选择表中的列 例 ：查询所有学生的姓名、学号、所在系。,SELECT sname，sno，dept FROM Student （次序不同 ）;,目标A 来源R 条件F,sname sno dept 王萧虎 200101 信息院 ,查询经过计算的值 SELECT子句的不仅可以是表中的属性

32、列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 例4 查全体学生的姓名及其出生年份 SELECT Sname, 2006-Sage FROM Student; 例5 查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, Year of Birth:, 1996-Sage, ISLOWER(Sdept) FROM Student;,例：查全体学生的姓名及其出生年份。 SELECT sname，2007-age AS FROM Student ；,sname 2005-age 王萧虎 1987 ,Birthday,别名,Birthd

33、ay,二、选择表中的行 1 消除取值重复的行 例：查考试成绩不及格的学号。,SELECT sno FROM SC WHERE grade60 ；,DISTINCT,2. 查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如下所示。 常用的查询条件：查询条件 谓 词 比较 比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件 AND, OR,(1) 比较（=, ） 例 查计算机系全体学生的姓名

34、SELECT Sname FROM Student WHERE Sdept = CS; 例 查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage = 20;,(2)确定范围 (BETWEEN AND ) 例: 查询选002号课程且成绩在80-90的学生号。 SELECT sno FROM SC WHERE sno=002 AND grade BETWEEN 80 AND 90；,其他表示？,关系运算？,(3)确定集合(IN) 例：查询信息院、数学系和计算机学院学生的姓名和性别。 SELECT sname，sex FROM

35、 Student WHERE dept IN (信息院，数学系，计算机学院) ? 查询不是这三个系的学生的姓名和性别。,NOT,(4)字符匹配-近似查询，模糊查询 格式：NOT LIKE 含义：是查找指定的属性列值与相匹配的元组。 其中匹配串可含： ：代表任意长度(可为0)的字符串。 _：代表任意单个字符。 例： 查所有姓刘或姓王的学生姓名、学号和性别。 SELECT sname，sno，sex FROM Student WHERE sname LIKE 刘 OR sname LIKE 王 ； ? 查询所有非姓刘或非姓王的学生姓名、学号和性别。,NOT,NOT,例 查姓“欧阳”且全名为三个汉字

36、的学生的姓名 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个_。 例 查名字中第二字为“阳”字的学生的姓名和学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE _阳%;,注意：一个汉字要占两个字符的位置,格式2： LIKE ESCAPE 若要查的串本身就含有或_ ，则用ESCAPE 对通配符进行转义。ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。

37、例：查“DB_”开头且倒数第2个字符为 i 的课程情况。 SELECT * FROM Course WHERE cname LIKE DB _ i_ ESCAPE ；,查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE 注意这里的匹配字符串DB_%i_。第一个_前面有换码字符，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符，所以它们仍作为通配符。 执行结果为： Cno Cname Ccredit 8 DB_Design 4 10 DB_Programming 2

38、 13 DB_DBMS Design 4, 注意这里的IS不能用等号（=） 代替。 例 查所有有成绩的记录的学生学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;,三、对查询结果排序 例: 查询全体男学生的学号、系、年龄 结果按所在的系升序排列， 同一系中的学生按年龄降序排列。,Student表,排序二,排序一,SELECT sno, dept, age FROM Student WHERE sex=男 ORDER BY dept，age DESC；,Orderby的排序键应该是 查询清单中的列名,四、使用集函数 COUNT(DIST

39、INCT | ALL * ) 统计元组个数 COUNT(DISTINCT | ALL) 统计一列中值的个数 SUM(DISTINCT | ALL ) 计算一数值型列值的总和 AVG(DISTINCT | ALL ) 计算一数值型列值的平均值 MAX(DISTINCT | ALL ) 求一列值的最大值 MIN(DISTINCT | ALL ) 求一列值的最小值,缺省值,无重复值,例: 查询女学生的总人数和平均年龄。 SELECT COUNT(sno),AVG(age) FROM Student WHERE sex=女 例: 查询选修001号课程并及格的学生的最高分数、最低分及总分。 SELECT

40、 MAX(grade),MIN(grade),SUM(grade) FROM SC WHERE cno=001 and grade=60,五、对查询结果分组： GROUP BY 子句 将查询结果表按某一(多)列值分组，值相等的为一组。 目的：细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 例 查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;,该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元

41、组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。 查询结果为： Cno COUNT(Sno) - - 1 22 2 34 3 44 4 33 5 48,如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。 例：求每个学生（号）的平均成绩，并将其超过88分的输出。 SELECT sno，AVG( grade) FROM SC GROUP BY sno；,HAVING短语：在各组中选择满足条件的小组。 WHERE子句 ：在表中选择满足条件的元组。,WHERE ?,HAVING AVG( grade) 88；,集函数在成组

42、之前不计算，因此 不能用于WHERE子句, 一般将简单条件写入WHERE。,HAVING子句的条件运算数之一 是一个集函数。,若HAVING子句无前导GROUPBY， 选择清单中不能含有非集函数列。,练习：查询至少选修4门课程的学生学号。 例：找出选课学生超过30人的课程的平均成绩及选课人数。 SELECT cno,AVG(grade),COUNT(*) AS st_number FROM SC GROUP BY sno HAVING st_number=30,例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。 SELECT age,C

43、OUNT(sno) AS number FROM Student WHERE sex=女 GROUP BY age HAVING number20 ORDER BY number,age DESC,3.3.3 嵌套查询 1. 子查询（嵌套查询） 查询块： SELECT FROM WHERE 子查询（嵌套查询）： 一个查询块嵌套在另一查询块的条件之中。 上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。,子查询一般跟在IN、SOME（ANY）、ALL和EXIST等谓词后面。 SQL语言允许多层嵌套查询。 嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理

44、之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是 SQL(Structurred Query Language)中“结构化”的含义所在。,(1) 带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。 例：查询与“刘晨”在同一个系学习的学生的学号、姓名、系 确定“刘晨”所在系名 SELECT Sdept FROM StudentWHERE Sname=刘晨; 查找所有在IS系学习的学生。SELECT Sno, Sna

45、me, Sdept FROM Student WHERE Sdept=IS;,子查询实现：将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。 WHERE Sdept IN (SELECT),例:查询选修了数据库课程的学生号、成绩。 SELECT sno， grade FROM SC WHERE cno IN ( SELECT cno FROM Course WHERE cname=数据库)；,（2）带有比较运算符的子查询 例:找出年龄超过平均年龄的学生姓名。 SELECT sname FROM Student WHERE age (SELECT AVG(age) FROM Studen

46、t)；,（3）带SOME ( ANY)、ALL谓词的子查询 SOME 大于子查询结果中的某个值 ALL 小于子查询结果中的所有值 注意：SOME、ALL必须与关系比较符同时使用。,例：查询有一门课程成绩在90分以上的学生的姓名。 SELECT sname FROM Student WHERE sno = SOME ( SELECT DISTINCT sno FROM SC WHERE grade 90);,例：找出(平均)成绩最高的学生号。 SELECT sno FROM SC GROUP BY sno HAVING AVG(grade) = ALL (SELECT AVG(grade) FR

47、OM SC GROUP BY sno)；,SELECT sno FROM SC WHERE AVG(grade) =ALL ( SELECT AVG(grade) FROM SC GROUP BY sno) ?,例：查询其他系中比IS系任一学生年龄大的学生名单 SELECT Sname, Sage FROM Student WHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept=IS) AND Sdept IS,SELECT Student. sno，sname，sex，age， dept，cno，grade FROM Student，SC

48、WHERE Student. sno=SC. sno ；,2. 条件连接查询 连接条件的一般格式为： . . 当连接运算符为=时，称为等值连接。 例: 查询每个学生的情况及其选课成绩。,例：找出系为信息系，选课成绩为90分以上的学生的姓名、课号和成绩。 SELECT sname，cno，grade FROM Student，SC WHERE Student.sno = SC.sno AND dept =信息系 AND grade90；,例:查询选修了数据库的学生号、成绩 SELECT sno， grade FROM SC WHERE cno IN ( SELECT cno FROM Cours

49、e WHERE cname=数据库)；,学分 ？,SELECT sno， grade FROM SC，Course WHERE SC. cno = Course . cno AND cname=数据库 ；,， Ccredit,=,子查询,连接 查询,例：按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括008号考查课。 SELECT Student.sno，sname，AVG(grade) AS avg_g FROM Student，SC WHERE Student.sno=SC.sno AND cno008 GROUP BY sno HAVING MIN(g

50、rade)=60 ORDER BY avg_g DESC ；,3.自身连接查询 例：找出年龄比“王迎”同学大的同学的姓名及年龄。 SELECT s1.sname，s1.age FROM S AS s1，S AS s2 WHERE s1.age s2.age AND s2.sname =王迎；,其他方法？,练习：查询每一门课的间接先修课（即先修课的先修课）。 SELECT FIRST.cno,SECOND.pcno FROM Course FIRST,Course SECOND WHERE FIRST.pcno=SECOND.cno;,4. FROM子句中的子查询 在FROM子句中可以使用子查询

51、，其查询的结果表称为导出关系（Derived Relation）。 例：查出选课成绩在80分以上的女学生的姓名、课程名和成绩。 SELECT sname，cname， grade FROM (SELECT sname， cname， grade FROM Student，SC，Course WHERE Student.sno=SC.sno AND SC.cno=Co AND sex女) AS Temp (sname， cname，grade) WHERE grade = 80 ；,导出关系,5. 相关子查询 当一个子查询的判断条件涉及到一个来自外部查询的列时，称为相关子查询。 带存在谓词的子查

52、询：只产生逻辑值 存在谓词EXISTS作用: 若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。,例：查询所有选修了1号课程的学生姓名。 SELECT sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND cno=1)；,分析： 查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，在Student关系中依次取每个元组的Sno值，

53、用此Student.Sno值去检查SC关系，若SC中存在这样的元组：其SC.Sno值等于用来检查的Student.Sno值，并且其SC.Cno=1，则取此Student.Sname送入结果关系。,相关子查询的一般处理过程是： 首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（即Sno值）处理内层查询，若WHERE子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。 其它方法？优点？,SQL语言中没有全称量词（For all）。因此对于求所有的操作，必须利用谓词演算将一个

54、带有全称量词的谓词转换为等价的带有存在量词的谓词。 SQL语言中也没有蕴涵（Implication）逻辑运算。因此也必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词。,例: 查询选修了全部课程的学生姓名。 转换成：查询这样的学生，没有一门课程是他不选修的。 (x)P (x(P),SELECT sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE sno=Student. sno AND cno=Course. cno)；,每门课都

55、没选,某学生选某课,练习：查询至少选修了学号为200101的学生选修的全部课程的学生学号。 分析：查询这样的学生，凡是200101选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为200101的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生200101选修了y，而学生x没有选。,SELECT sno FROM Student X WHERE NOT EXISTS (SELECT * FROM SC Y WHERE Y.sno=200101 AND NOT EXISTS (SELECT * FROM SC Z

56、WHERE X.sno=Z.sno AND Y.cno=Z.cno),6.集合运算 （1）UNION （并） （2）INTERSECT （交） （3）EXCEPT （差） 例：求选修了001或002号而没有选003号课程的学生号。 (SELECT sno FROM SC WHERE cno =001 OR cno =002) EXCEPT (SELECT sno FROM SC WHERE cno =003)；,3.3.4 自然连接与外连接 1.自然连接(NATURAL JOIN) 例：查系别为信息、课程成绩在90分以上的学生档案及其成绩情况。 SELECT * FROM Student NA

57、TURAL JOIN SC WHERE dept=信息 AND grade =90； 2. 外连接 左外连接。LEFT OUTER JOIN，保留左关系的所有元组。 右外连接。RIGHT OUTER JOIN，保留右关系的所有元组。 全外连接。FULL OUTER JOIN，保留左右两关系的所有元组。,FROM SC, Student?,自然连接的定义？,教师表：Teacher(教师号，姓名，所属大学，职称) 任职表：Post(编号，姓名，职务) 例：SELECT * FROM Teacher FULL OUTER JOIN Post； 例：SELECT * FROM Techer LEFT

58、OUTER JOIN Post； 例：SELECT * FROM Techer RIGHT OUTER JOIN Post；,3.3.5 SQL中的空值处理 1.空值的含义 值暂未知。 值不适用。 值需隐瞒。 2.空值的若干规则 （1）空值与数值型数据进行算术运算，结果为空值。 （2）空值和任何值(包括空值)进行比较运算，结果为UNKNOWN。,（3）空值的布尔运算 U AND T = U ； U AND F =F ； U AND U = U ； U OR T = T ； U OR F =U ； U OR U = U ； NOT U = U 。 空值的测试：IS NULL和IS NOT NULL。 例：查询缺少选课成绩的学生号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE grade IS N