的特征SQL的数据定义

会计学1的特征SQL的数据定义第三章关系数据库语言SQL3.1SQL的特征1.SQL的主要标准

SQL-86SQL-89SQL-92(SQL2)SQL-99(SQL3)第1页/共119页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，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。第2页/共119页2.SQL的功能特点功能：数据定义数据查询数据操纵数据控制第3页/共119页第三章关系数据库语言SQL

特点：综合统一

非关系模型的数据语言不同模式有不同的数据定义语言及数据操纵语言。SQL语言则集数据定义、操纵和控制功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。

第4页/共119页第三章关系数据库语言SQL高度非过程化

非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。第5页/共119页第三章关系数据库语言SQL灵活的使用方式SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。

第6页/共119页第三章关系数据库语言SQL

简洁、通用、功能强

SQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词，如表3-1所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。SQL功能核心动词数据查询SELECT数据定义CREATE,DROP,ALTER数据控制INSERT,UPDATE,DELETE数据操纵GRANT,REVOKE第7页/共119页3.1SQL的特征3.SQL的基本组成

（1）SQL数据库层次结构

存储文件1存储文件2SQL视图1视图2基本表1基本表2基本表3术语对照：一般关系模型SQL外模式-----视图（VIEW)模式-----基本表(TABLE)内模式-----存储文件（索引）元组--------行(ROW)属性--------列(COLUMN)第8页/共119页3.1SQL的特征基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。

第9页/共119页

3.1SQL的特征（2）SQL语言的组成

数据定义语言（DDL）数据操纵语言（DML）数据控制语言（DCL）

嵌入与会话规则

（3）SQL的语句类型

SQL模式语句

SQL数据语句

SQL事务与控制语句

SQL连接、会话及诊断语句

4.SQL的数据类型

预定义数据类型构造数据类型用户定义数据类型（UDT，UserDefinedType）

第10页/共119页3.1SQL的特征5.SQL环境（1）SQL模式与目录SQL模式：基本表的集合。好处：允许在不同的SQL模式中出现同名的基表名或视图名。目录：SQL环境中所有模式的集合。定位基表的方式：

<目录名>．<模式名>．<表名>（2）SQL环境设置默认的目录和模式设置用户身份

第11页/共119页3.1SQL的特征（3）存储过程

存储过程是存储在SQL服务器上的预编译好的一组为了完成特定功能的SQL语句集。通过指定存储过程的名字并给出参数来执行它。可分为两类：

◆系统存储过程：由系统自动创建，完成的功能主要是从系统表中获取信息。

◆用户定义的存储过程：由用户为完成某一特定功能而编写的存储过程。使用存储过程的好处：

◆可减少网络流量。

◆增强代码的重用性和共享性。

◆加快系统运行速度。

◆保证数据安全性。第12页/共119页3.2SQL的数据定义1.SQL模式的定义与撤销（1）SQL模式的定义

CREATESCHEMA＜模式名＞AUTHRIZATION＜用户名＞[＜CREATEDOMAIN子句＞|＜CREATETABLE子句＞|＜CREATEVIEW＞|……]例；CREATESCHEMATeaching_dbAUTHRIZATIONHang；（2）数据库模式的删除

DROPSCHEMA＜模式名＞{CASCADE|RESTRICT}

CASCADE(级联式)：

RESTRICT（约束式)：

第13页/共119页3.2SQL的数据定义

2.表的建立和删除

（1）表的建立命令格式：

CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件][，<列名><数据类型>[列级完整性约束条件]...)[,<表级完整性约束条件>];

例：CREATETABLEStudent

(snoCHAR(5)NOTNULL

UNIQUE，

snameCHAR(20)NOTNULL

，

sexCHAR(1)，

ageINT，

deptCHAR(15)

)；主码第14页/共119页3.2SQL的数据定义完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。注意：

例：定义P.76的三个表结构，并指定相应的数据完整性约束条件。分析外码：sno,cno

主码：sno姓名：非空性别：男、女两值Student表：Course表：主码：cno课程名：非空外码：pcnoSC表：主码：(sno,cno)成绩：0—100第15页/共119页3.2SQL的数据定义CREATETABLEStudent

(

snoCHAR(5),snameCHAR(8)NOTNULL,sexCHAR(2),ageSMALLINT,deptCHAR(20),PRIMARYKEY(sno),CHECKsexIN(‘男’,‘女’)

);列级完整性约束条件实体完整性约束条件用户自定义完整性约束条件第16页/共119页3.2SQL的数据定义CREATETABLECourse

(

cnoCHAR(4),cnameCHAR(10)NOTNULL,pcnoCHAR(4),creditSMALLINT,PRIMARYKEY(cno),FOREIGNKEY(pcno)REFERENCESCourse(cno)

);参照完整性约束条件第17页/共119页3.2SQL的数据定义CREATETABLESC

(

snoCHAR(5),cnoCHAR(4),gradeSMALLINT,PRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESStudent(sno),FOREIGNKEY(cno)REFERENCESCourse(cno),CHECK((gradeISNULL)OR(gradeBETWEEN0AND100))

);第18页/共119页3.2SQL的数据定义（2）表的删除

格式：DROPTABLE<表名>[CASCADE|RESTRICT]

基本表定义一旦删除，表中的数据、在此表上建立的视图、索引、触发器、断言都将自动被删除掉。RESTRICT确保只有不具有相关对象的表才能被撤销。例：DROPTABLEStudentCASCADE；第19页/共119页3.2SQL的数据定义3.基本表的扩充和修改

一般格式为：

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名><完整性约束名>][MODIFY<列名><数据类型><数据类型>];

其中<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。（1）在现存表中增加新列

格式：ALTERTABLE<表名>ADD(<列名><数据类型>，…)第20页/共119页3.2SQL的数据定义（2）删除已存在的某个列

格式：ALTERTABLE<表名>DROP<列名>[CASCADE|RESTRICT]

例：ALTERTABLEStudentDROPaddr；（3）修改原有列的类型

格式：ALTERTABLE<表名>MODIFY<列名><类型>；例：ALTERTABLEStudentMODIFYplaceCHAR(8)；第21页/共119页3.2SQL的数据定义（4）补充定义主码

格式：ALTERTABLE<表名>ADDPRIMARYKEY(<列名表>)（5）删除主码

格式：ALTERTABLE<表名>DROPPRIMARYKEY第22页/共119页3.2SQL的数据定义修改基本表实例例向Student表增加“入学时间”列，其数据类型为日期型ALTERTABLEStudentADDScomeDATE;

例将年龄的数据类型改为半字长整数ALTERTABLEStudentMODIFYSageSMALLINT;例删除关于学号必须取唯一值的约束ALTERTABLEStudentDROPUNIQUE(Sno);

第23页/共119页3.2SQL的数据定义4.索引的建立与删除

建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。

命令格式：

CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名><次序>[[，<列名><次序>]]…)；次序：升序（ASC，缺省）降序（DESC）

UNIQUE:每一个索引值只对应惟一的数据记录。

CLUSTER:建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。

第24页/共119页3.2SQL的数据定义注意：在一个基本表上最多只能建立一个聚簇索引。经常更新的列不宜建立聚簇索引。所建索引放何处？例:①

为Student表按学号升序建惟一聚簇索引。

②为SC表按学号升序和课程号降序建惟一索引。

①

CREATEUNIQUECLUSTERINDEXStno

ONStudent(Sno)；

②

CREATEUNIQUEINDEXScno

ONSC(Sno，CnoDESC)；删除索引一般格式为：

DROPINDEX<索引名>；例：DROPINDEXStno

；删除索引时，系统会同时从数据字典中

删去有关该索引的描述。第25页/共119页3.3SQL的数据查询一般形式：SELECT<A>FROM<R>WHERE<F>GROUPBY<列名1>HAVING<条件表达式>ORDERBY<列名2>[ASC|DESC];基本语句含义:

根据WHERE中的F，从基表或视图R中找出满足条件的元组，再从中选出目标属性值形成结果表。查询目标数据来源选择元组的条件将结果按<列名1>的值进行分组满足条件的组才予输出按<列名2>排序查询结果第26页/共119页3.3SQL的数据查询如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常在成组后再使用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。第27页/共119页3.3SQL的数据查询其中：[…]：表示其中的成分为任选项。

<…>：表示其中的成分由用户具体给定。

|：表示其中并列的成分只能择一。查询目标：

ALL：表示保留满足条件的所有元组（缺省）。

DISTINCT：表示去掉重复元组。目标列：可以为属性名、表达式、通配符‘*’（表示所有属性列）。第28页/共119页3.3SQL的数据查询－单表查询学生-课程数据库:Student(sno，sname，sex，age，dept)Course(cno，cname，pcno，credit)SC(sno，cno，grade)3.3.1单表查询

一、选择表中的列例：查询所有学生的姓名、学号、所在系。

SELECTsname，sno，deptFROMStudent

（次序不同）;目标A来源R条件Fsnamesnodept———————————王萧虎200101信息院……第29页/共119页3.3SQL的数据查询－单表查询查询经过计算的值SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。例4查全体学生的姓名及其出生年份

SELECTSname,2006-SageFROMStudent;例5查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名

SELECTSname,'YearofBirth:',1996-Sage,ISLOWER(Sdept)FROMStudent;第30页/共119页3.3SQL的数据查询－单表查询例：查全体学生的姓名及其出生年份。

SELECTsname，2007-ageASFROMStudent；来源R条件F目标Asname

2005-age———————————王萧虎

1987……Birthday别名Birthday二、选择表中的行1消除取值重复的行例：查考试成绩不及格的学号。

SELECTsnoFROMSCWHEREgrade<60；DISTINCT第31页/共119页3.3SQL的数据查询－单表查询2.查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如下所示。

常用的查询条件：

查询条件谓词比较比较运算符确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件AND,OR第32页/共119页3.3SQL的数据查询－单表查询(1)比较（=,<,>…

）

例查计算机系全体学生的姓名

SELECTSname

FROMStudentWHERESdept='CS';

例查所有年龄在20岁以下的学生姓名及其年龄

SELECTSname,Sage

FROMStudent

WHERESage<20;

或WHERENOTSage>=20;第33页/共119页

3.3SQL的数据查询－单表查询

(2)确定范围(BETWEEN…AND…)例:

查询选002号课程且成绩在80--90的学生号。

SELECTsnoFROMSCWHEREsno=‘002’ANDgradeBETWEEN80AND90；

其他表示？关系运算？

(3)确定集合(IN)例：查询信息院、数学系和计算机学院学生的姓名和性别。

SELECTsname，sexFROMStudentWHEREdeptIN('信息院'，'数学系'，'计算机学院')?查询不是这三个系的学生的姓名和性别。

NOT

第34页/共119页3.3SQL的数据查询－单表查询

(4)字符匹配---近似查询，模糊查询格式：[NOT]LIKE’<匹配串>’

含义：是查找指定的属性列值与<匹配串>相匹配的元组。其中匹配串可含：％：代表任意长度(可为0)的字符串。

_：代表任意单个字符。例：查所有姓刘或姓王的学生姓名、学号和性别。

SELECTsname，sno，sexFROMStudentWHEREsnameLIKE‘刘％’ORsnameLIKE‘王％’；?查询所有非姓刘或非姓王的学生姓名、学号和性别。NOTNOT第35页/共119页3.3SQL的数据查询－单表查询例查姓“欧阳”且全名为三个汉字的学生的姓名SELECTSname

FROMStudent

WHERESnameLIKE‘欧阳__';

注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟２个_。例查名字中第二字为“阳”字的学生的姓名和学号SELECTSname,Sno

FROMStudent

WHERESnameLIKE'__阳%';注意：一个汉字要占两个字符的位置第36页/共119页3.3SQL的数据查询－单表查询

格式2：LIKE’<匹配串>’ESCAPE’<换码字符>’若要查的串本身就含有％或_，则用ESCAPE‘<换码字符>’对通配符进行转义。ESCAPE’\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。例：查“DB_”开头且倒数第2个字符为i的课程情况。

SELECT*FROMCourseWHEREcnameLIKE‘DB\

_

％i_’ESCAPE’\’；第37页/共119页3.3SQL的数据查询－单表查询查以”DB_”开头，且倒数第三个字符为i的课程的详细情况

SELECT*FROMCourseWHERECnameLIKE’DB_%i__’ESCAPE’&’;

注意这里的匹配字符串’DB\_%i__’。第一个_前面有换码字符\，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符\，所以它们仍作为通配符。执行结果为：CnoCnameCcredit8DB_Design410DB_Programming213

DB_DBMSDesign4&第38页/共119页3.3SQL的数据查询－单表查询(5)涉及空值的查询例某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号

SELECTSno,Cno

FROMSC

WHEREGradeISNULL;

注意这里的‘IS’不能用等号（‘=’）代替。

例查所有有成绩的记录的学生学号和课程号

SELECTSno,Cno

FROMSC

WHEREGradeISNOTNULL;第39页/共119页3.3SQL的数据查询－单表查询三、对查询结果排序例:

查询全体男学生的学号、系、年龄结果按所在的系升序排列，同一系中的学生按年龄降序排列。

来源R

条件F目标AStudent表排序二排序一

SELECTsno,dept,ageFROMStudentWHEREsex=‘男’

ORDERBYdept，ageDESC；

Orderby的排序键应该是查询清单中的列名第40页/共119页3.3SQL的数据查询－单表查询四、使用集函数

COUNT([DISTINCT|ALL]*)统计元组个数

COUNT([DISTINCT|ALL]<列名>)统计一列中值的个数

SUM([DISTINCT|ALL]<列名>)计算一数值型列值的总和

AVG([DISTINCT|ALL]<列名>)计算一数值型列值的平均值

MAX([DISTINCT|ALL]<列名>)求一列值的最大值

MIN([DISTINCT|ALL]<列名>)求一列值的最小值缺省值无重复值第41页/共119页3.3SQL的数据查询－单表查询例:

查询女学生的总人数和平均年龄。

SELECTCOUNT(sno),AVG(age)FROMStudentWHEREsex=‘女’例:

查询选修001号课程并及格的学生的最高分数、最低分及总分。

SELECTMAX(grade),MIN(grade),SUM(grade)FROMSCWHEREcno=‘001’andgrade>=60

第42页/共119页3.3SQL的数据查询－单表查询五、对查询结果分组：GROUPBY子句将查询结果表按某一(多)列值分组，值相等的为一组。目的：细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。

例查询各个课程号与相应的选课人数

SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;第43页/共119页3.3SQL的数据查询－单表查询该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为：

Cno

COUNT(Sno)

----------------

1

22

2

34

3

44

4

33

5

48

第44页/共119页3.3SQL的数据查询－单表查询如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。

例：求每个学生（号）的平均成绩，并将其超过88分的输出。

SELECTsno，AVG(grade)FROMSCGROUPBYsno；HAVING短语：在各组中选择满足条件的小组。WHERE子句：在表中选择满足条件的元组。WHERE?HAVINGAVG(grade)>88；第45页/共119页3.3SQL的数据查询－单表查询集函数在成组之前不计算，因此不能用于WHERE子句,一般将简单条件写入WHERE。HAVING子句的条件运算数之一是一个集函数。若HAVING子句无前导GROUPBY，选择清单中不能含有非集函数列。第46页/共119页3.3SQL的数据查询－单表查询练习：查询至少选修4门课程的学生学号。例：找出选课学生超过30人的课程的平均成绩及选课人数。

SELECTcno,AVG(grade),COUNT(*)ASst_numberFROMSCGROUPBYsnoHAVINGst_number>=30第47页/共119页3.3SQL的数据查询－单表查询例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。

SELECTage,COUNT(sno)ASnumberFROMStudentWHEREsex=‘女’

GROUPBYageHAVINGnumber>20ORDERBYnumber,ageDESC第48页/共119页3.3SQL的数据查询3.3.3嵌套查询

1.子查询（嵌套查询）查询块：SELECT<A>FROM<R>WHERE<F>子查询（嵌套查询）：一个查询块嵌套在另一查询块的条件之中。上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。第49页/共119页3.3SQL的数据查询子查询一般跟在IN、SOME（ANY）、ALL和EXIST等谓词后面。SQL语言允许多层嵌套查询。嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是SQL(StructurredQueryLanguage)中“结构化”的含义所在。第50页/共119页3.3SQL的数据查询(1)带有IN谓词的子查询带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。例：查询与“刘晨”在同一个系学习的学生的学号、姓名、系①确定“刘晨”所在系名

SELECTSdept

FROMStudent

WHERESname=‘刘晨’;

②查找所有在IS系学习的学生。

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept='IS';

子查询实现：将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。WHERESdeptIN(SELECT…)

第51页/共119页3.3SQL的数据查询例:查询选修了数据库课程的学生号、成绩。

SELECTsno，gradeFROMSCWHEREcnoIN

(SELECTcnoFROMCourseWHEREcname=‘数据库’)；

（2）带有比较运算符的子查询例:找出年龄超过平均年龄的学生姓名。SELECTsnameFROMStudentWHEREage＞

(SELECTAVG(age)FROMStudent)；第52页/共119页3.3SQL的数据查询（3）带SOME(ANY)、ALL谓词的子查询>SOME

大于子查询结果中的某个值<ALL

小于子查询结果中的所有值注意：SOME、ALL必须与关系比较符同时使用。例：查询有一门课程成绩在90分以上的学生的姓名。

SELECTsnameFROMStudentWHEREsno=SOME(SELECTDISTINCTsnoFROMSCWHEREgrade>90);第53页/共119页3.3SQL的数据查询例：找出(平均)成绩最高的学生号。

SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(grade)>=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)；SELECTsnoFROMSCWHEREAVG(grade)>=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)???第54页/共119页3.3SQL的数据查询例：查询其他系中比IS系任一学生年龄大的学生名单SELECTSname,Sage

FROMStudent

WHERESage>

(SELECTMAX(Sage)

FROMStudent

WHERESdept='IS')

ANDSdept<>'IS'第55页/共119页3.3SQL的数据查询

SELECTStudent.sno，sname，sex，age，

dept，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.sno；

2.条件连接查询

连接条件的一般格式为：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>当连接运算符为=时，称为等值连接。例:查询每个学生的情况及其选课成绩。第56页/共119页3.3SQL的数据查询例：找出系为信息系，选课成绩为90分以上的学生的姓名、课号和成绩。

SELECTsname，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.snoANDdept=‘信息系’

ANDgrade＞＝90；第57页/共119页3.3SQL的数据查询例:查询选修了数据库的学生号、成绩

SELECTsno，gradeFROMSCWHEREcnoIN

(SELECTcnoFROMCourseWHEREcname=‘数据库’)；学分？SELECTsno，gradeFROMSC，CourseWHERESC.cno=CoANDcname=‘数据库’；，Ccredit=子查询连接查询第58页/共119页3.3SQL的数据查询例：按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括008号考查课。

SELECTStudent.sno，sname，AVG(grade)ASavg_gFROMStudent，SCWHEREStudent.sno=SC.snoANDcno<>’008’GROUPBYsnoHAVINGMIN(grade)>=60ORDERBYavg_gDESC；

第59页/共119页3.3SQL的数据查询3.自身连接查询例：找出年龄比“王迎”同学大的同学的姓名及年龄。SELECTs1.sname，s1.ageFROMSASs1，SASs2WHEREs1.age>s2.ageANDs2.sname=‘王迎’；

其他方法？练习：查询每一门课的间接先修课（即先修课的先修课）。SELECTFIRST.cno,SECOND.pcnoFROMCourseFIRST,CourseSECONDWHEREFIRST.pcno=SECOND.cno;第60页/共119页3.3SQL的数据查询4.FROM子句中的子查询在FROM子句中可以使用子查询，其查询的结果表称为导出关系（DerivedRelation）。例：查出选课成绩在80分以上的女学生的姓名、课程名和成绩。SELECTsname，cname，gradeFROM(SELECTsname，cname，gradeFROMStudent，SC，CourseWHEREStudent.sno=SC.snoANDSC.cno=CoANDsex＝'女')

ASTemp(sname，cname，grade)WHEREgrade>=80；

导出关系第61页/共119页3.3SQL的数据查询5.相关子查询当一个子查询的判断条件涉及到一个来自外部查询的列时，称为相关子查询。

带存在谓词的子查询：只产生逻辑值存在谓词EXISTS作用:若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。

第62页/共119页3.3SQL的数据查询例：查询所有选修了1号课程的学生姓名。

SELECTsnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.Sno

ANDcno=’1’)；分析：查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，在Student关系中依次取每个元组的Sno值，用此Student.Sno值去检查SC关系，若SC中存在这样的元组：其SC.Sno值等于用来检查的Student.Sno值，并且其SC.Cno=‘1’，则取此Student.Sname送入结果关系。第63页/共119页3.3SQL的数据查询相关子查询的一般处理过程是：

首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（即Sno值）处理内层查询，若WHERE子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。

其它方法？优点？第64页/共119页3.3SQL的数据查询SQL语言中没有全称量词∨（Forall）。因此对于求所有的操作，必须利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词。SQL语言中也没有蕴涵（Implication）逻辑运算。因此也必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词。第65页/共119页3.3SQL的数据查询例:查询选修了全部课程的学生姓名。转换成：查询这样的学生，没有一门课程是他不选修的。

(x)P≡(x(P))

SELECTsnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHEREsno=Student.snoANDcno=Co))；每门课都没选某学生选某课第66页/共119页3.3SQL的数据查询练习：查询至少选修了学号为200101的学生选修的全部课程的学生学号。分析：查询这样的学生，凡是200101选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为200101的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生200101选修了y，而学生x没有选。第67页/共119页3.3SQL的数据查询SELECTsnoFROMStudentXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.sno=‘200101’ANDNOTEXISTS(SELECT*FROMSCZWHEREX.sno=Z.snoANDY.cno=Z.cno))第68页/共119页3.3SQL的数据查询6.集合运算（1）UNION

（并）（2）INTERSECT

（交）（3）EXCEPT

（差）例：求选修了001或002号而没有选003号课程的学生号。(SELECTsnoFROMSCWHEREcno=’001’ORcno=’002’)EXCEPT(SELECTsnoFROMSCWHEREcno=‘003’)；第69页/共119页3.3SQL的数据查询3.3.4自然连接与外连接

1.自然连接(NATURALJOIN)例：查系别为信息、课程成绩在90分以上的学生档案及其成绩情况。

SELECT*FROMStudentNATURALJOINSCWHEREdept=‘信息’ANDgrade>=90；2.

外连接左外连接。LEFT[OUTER]JOIN，保留左关系的所有元组。右外连接。RIGHT[OUTER]JOIN，保留右关系的所有元组。全外连接。FULL[OUTER]JOIN，保留左右两关系的所有元组。

FROMSC,Student?自然连接的定义？第70页/共119页3.3SQL的数据查询

教师表：Teacher(教师号，姓名，所属大学，职称)

任职表：Post(编号，姓名，职务)

例：SELECT*FROMTeacherFULLOUTERJOINPost；例：SELECT*FROMTecher

LEFTOUTERJOINPost；例：SELECT*FROMTecherRIGHTOUTERJOINPost；第71页/共119页3.3SQL的数据查询3.3.5SQL中的空值处理1.空值的含义值暂未知。值不适用。值需隐瞒。2.空值的若干规则（1）空值与数值型数据进行算术运算，结果为空值。（2）空值和任何值(包括空值)进行比较运算，结果为UNKNOWN。

第72页/共119页3.3SQL的数据查询（3）空值的布尔运算UANDT=U；UANDF=F；UANDU=U；UORT=T；UORF=U；UORU=U；

NOTU=U。空值的测试：ISNULL和ISNOTNULL。例：查询缺少选课成绩的学生号和相应的课程号。

SELECTSno，CnoFROMSCWHEREgradeISNULL；第73页/共119页3.3SQL的数据查询3.3.6递归合并查询

递归合并语句格式：WITHRECURSIVE<临时表R>AS<R的定义>UNION<涉及到R的查询><递归结果查询>例：对表3.6所示的航班表Flight(airline，from，to，depart，arrive)，求出：能从一个城市飞到另一个城市的城市对集合(含直接到达和间接中转到达)。第74页/共119页3.3SQL的数据查询

直接到达：

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；

第75页/共119页3.3SQL的数据查询例：有下列所示的一棵二叉树：abecfd162453id

pidname1NULLa21b31c42d53e63f求出:该子树中除子树根结点k以外的所有子孙结点的集合。

第76页/共119页3.3SQL的数据查询分析：定义结果集为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；

第77页/共119页3.3SQL的数据查询视图的概念及作用

是从一个或几个基本表(或视图)导出的表，是虚表。与表一样可被查询。对视图的更新操作有一定的限制。对视图的一切操作最终将转换为对基本表的操作。

视图的作用：（1）简化结构及复杂操作。

（2）多角度地、更灵活地共享。

（3）提高逻辑独立性。

（4）提供安全保护。

S#SNSUM虚表S#SNAVG…实表S#SUM…第78页/共119页3.4SQL的数据更新一.插入数据1.插入单个元组格式：INSERTINTO<表名>[（<属性列1>[，<属性列2>…)]

VALUES(<常量1>[,<常量2>]…);说明：（1）若插入全部属性，则属性列可省略。（2）表定义中说明为NOTNULL的列不能取空值。

（3）属性值与相对应的属性列的数据类型要匹配。（4）向参照表中插入元组，关系系统自动支持：

·

实体完整性

·

参照完整性第79页/共119页3.4SQL的数据更新2.

插入子查询结果格式：INSERTINTO<表名>[(<属性列1>[,<属性列2>…)]

子查询；二.修改数据格式：UPDATE<表名>SET<列名>=<表达式>[,<列名>=<表达式>]…[WHERE<条件>];三.删除数据格式：DELETEFROM<表名>[WHERE<条件>];第80页/共119页3.4SQL的数据更新四.构造数据类型

1.聚合类型

<数据类型>ARRAY[<无符号整数>]例：CREATETABLEmailout(nameCHAR(8)，

addressCHAR(20)ARRAY[3])；INSERTINTOTABLEmailout(name，address)VALUES('Wang'，ARRAY['20HongsangRoad'，'Wuhan'，'430079'])；第81页/共119页3.4SQL的数据更新2.行类型

格式：

ROW(<列名><数据类型>[{，<列名><数据类型>}…]例：CREATETABLElineage(nameCHAR(8)，

statusCHAR(10)，

lastoneROW(babyCHAR(8)，birthDATE))；……

//插入数据操作

SELECTname，status，lastoneFROMlineage

WHERElastone.baby='Liming'；作为一个整体第82页/共119页

3.5SQL的视图

创建与使用视图1.创建视图一般格式：

CREATEVIEW<视图名>[(<视图列名>[,<视图列名>]…)]AS<子查询>[WITHCHECKOPTION]；其中：子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT短语。

WITHCHECKOPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

作用：命名一个视图，AS子句定义每次查看视图时将看到的数据，在任何时刻，视图的数据由对其查询定义求值的结果行构成。第83页/共119页3.5SQL的视图例1：建立信息院学生的视图，

CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’；例2：建立信息院学生的视图，

要求进行更新时仍保证该视图只有信息院的学生。

CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’；

WITHCHECKOPTION第84页/共119页3.5SQL的视图视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。

例3建立信息系选修了1号课程的学生的视图CREATEVIEWIS_S1(Sno,Sname,Grade)

AS

SELECTStudent.Sno,Sname,Grade

FROMStudent,SC

WHERESdept='IS'AND

Student.Sno=SC.SnoAND

SC.Cno='1';第85页/共119页3.5SQL的视图例4建立信息系选修了1号课程且成绩在90分以上的学生的视图

CREATEVIEWIS_S2

AS

SELECTSno,Sname,Grade

FROMIS_S1

WHEREGrade>=90;

这里的视图IS_S2就是建立在视图IS_S1之上的。

第86页/共119页3.5SQL的视图带虚拟列的视图：是指设置了一些基本表中并不存在的派生列(虚拟列)的视图。定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列，带虚拟列的视图也称为带表达式的视图。

例5定义一个反映学生出生年份的视图

CREATEVIEWBT_S(Sno,Sname,Sbirth)

ASSELECTSno,Sname,1996-Sage

FROMStudent;第87页/共119页3.5SQL的视图分组视图：带有集函数和GROUPBY子句查询所定义的视图。例6将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型，否则无法求平均值。

CREATVIEWS_G(Sno,Gavg)

ASSELECTSno,AVG(Grade)

FROMSC

GROUPBYSno;例7将Student表中所有女生记录定义为一个视图

CREATEVIEWF_Student(stdnum,name,sex,age,dept)

ASSELECT*

FROMStudent

WHERESsex='女';第88页/共119页3.5SQL的视图

删除视图

DROPVIEW<视图名>[CASCADE|RESTRICT]

注意：视图删除后，视图的定义将从数据字典中自动删除。但要删除该视图导出的其他视图应用CASCADE。作用：撤销一个视图，但不消除数据，在基础表中的数据仍然保留。

RESTRICT确保只有不具有相关对象的视图（不涉及任何的约束、断言、触发器）才能被撤消。第89页/共119页3.5SQL的视图查询视图视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。

DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（ViewResolution）。

第90页/共119页3.5SQL的视图例1在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,Sage

FROMIS_Student

WHERESage<20;

视图是定义在基本上的虚表，它可以和其他基本表一起使用，实现连接查询或嵌套查询。例2查询信息系选修了1号课程的学生SELECTSno,Sname

FROMIS_Student,SC

WHEREIS_Student.Sno=SC.SnoAND

SC.Cno='1';

本查询涉及虚表IS_Student和基本表SC，通过这两个表的连接来完成用户请求。第91页/共119页3.5SQL的视图

更新视图

最终要转换为对基本表的更新。注意：并不是所有的视图都可以更新的。

P.133—P.134视图更新规定第92页/共119页3.5SQL的数据控制由DBMS提供统一的数据控制功能是数据库系统的特点之一。数据控制亦称为数据保护，包括数据的安全性控制(连接第九章数据库安全性)、完整性控制(连接第十章数据库完整性)、并发控制和恢复(连接第八章并发控制)。这里主要介绍SQL的数据控制功能。SQL语言提供了数据控制功能，能够在一定程度上保证数据库中数据的安全性、完整性，并提供了一定的并发控制及恢复能力。第93页/共119页3.5SQL的数据控制数据库的完整性是指数据库中数据的正确性与相容性。SQL语言定义完整性约束条件的功能主要体现在CREATETABLE语句中，可以在该语句中定义码、取值唯一的列、参照完整性及其他一些约束条件。

并发控制指的是当多个用户并发地对数据库进行操作时，对他们加以控制、协调，以保证并发操作正确执行，并保持数据库的一致性。恢复指的是当发生各种类型的故障，使数据库处于不一致状态时，将数据库恢复到一致状态的功能。SQL语言也提供了并发控制及恢复的功能，支持事务、提交、回滚等概念。数据库的安全性是指保护数据库，防止不合法的使用所造成的数据泄露和破坏。数据库系统中保证数据安全性的主要措施是进行存取控制，即规定不同用户对于不同数据对象所允许执行的操作，并控制各用户只能存取他有权存取的数据。不同的用户对不同的数据应具有何种操作权力，是由DBA和表的建立者（即表的属主）根据具体情况决定的，SQL语言则为DBA和表的属主定义与回收这种权力提供了手段。第94页/共119页3.5SQL的数据控制1.授权语句SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为：

GRANT<权限>[,<权限>]...

[ON<对象类型><对象名>]

TO<用户>[,<用户>]...

[WITHGRANTOPTION];

其语义为：将对指定操作对象的指定操作权限授予指定的用户。接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC即全体用户。

如果指定了WITHGRANTOPTION子句，则获得某种权限的用户还可以把这种权限再授予别的用户。如果没有指定WITHGRANTOPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。第95页/共119页3.5SQL的数据控制例1把查询Student表权限授给用户U1GRANTSELECTONTABLEStudentTOU1;

例2把对Student表和Course表的全部权限授予用户U2和U3GRANTALLPRIVILIGESONTABLEStudent,CourseTOU2,U3;

例3把对表SC的查询权限授予所有用户GRANTSELECTONTABLESCTOPUBLIC;

例4把查询Student表和修改学生学号的权限授给用户U4GRANTUPDATE(Sno),SELECTONTABLEStudentTOU4;

这里实际上要授予U4用户的是对基本表Student的SELECT权限和对属性列Sno的UPDATE权限。授予关于属性列的权限时必须明确指出相应属性列名。

第96页/共119页3.5SQL的数据控制例5把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用