数据库原理第8讲

数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL.两个概念Schema(架构，P83)Createschema...Index(索引，P89).Schema(架构)Aschemaisessentiallyanamespace:itcontainsnamedobjects(tables,datatypes,functions,andoperators)whosenamesmayduplicatethoseofotherobjectsexistinginotherschemas.Namedobjectsareaccessedeitherby"qualifying"

theirnameswiththeschemanameasaprefix,orbysettingasearchpaththatincludesthedesiredschema(s).

.Index（索引）索引以某个（或某些）属性值为输入并找出对应属性值符合要求的纪录。具体来说，索引使我们只需查看所有可能记录中的一小部分就能找到所需记录。建立索引的属性（组合）称为查找键（searchkey），简称键。索引存放记录的块值匹配的记录.稠密索引(DenseIndexes)和

稀疏索引(SparseIndexes)稠密索引：数据文件中每个记录在索引文件中都设有一个索引项。.102010203040506070809010011012030405060708090100索引文件数据文件图3-2顺序文件（右）上的稠密索引（左）.1020103050709011013015017019021023030405060708090100图3-2顺序文件上的稀疏索引.多级索引（MultipleLevelsofIndex）10901702501090170250一级稀疏索引二级稀疏索引.辅助索引（SecondaryIndexes）2040101020202030405050601020503010506020.B-树(B-Trees)...3.1SQL概述SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言.概述SQL的发展1974年，由Boyce和Chamberlin提出1975~1979，IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)、SQL-99(SQL3)、SQL2003.3.1SQL概述SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用.3.1.2SQL的特点集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。1.综合统一

.AnIntroductiontoDatabaseSystem2.高度非过程化非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径SQL只要提出“做什么”，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。.3.面向集合的操作方式非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合.4.以同一种语法结构提供多种使用方式SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用.AnIntroductiontoDatabaseSystem5.语言简洁，易学易用SQL功能极强，完成核心功能只用了9个动词。5.语言简洁，易学易用.3.2数据定义.定义模式[例1]定义一个学生-课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户WANG定义了一个模式S-T[例2]CREATESCHEMAAUTHORIZATIONWANG；<模式名>隐含为用户名WANG如果没有指定<模式名>，那么<模式名>隐含为<用户名>.定义模式（续）定义模式实际上定义了一个命名空间在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。在CREATESCHEMA中可以接受CREATETABLE，CREATEVIEW和GRANT子句。 CREATESCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>].定义模式（续）[例3]CREATESCHEMATESTAUTHORIZATIONZHANG CREATETABLETAB1(COL1SMALLINT， COL2INT， COL3CHAR(20)， COL4NUMERIC(10，3)， COL5DECIMAL(5，2) )；.删除模式DROPSCHEMA<模式名><CASCADE|RESTRICT> CASCADE(级联)

删除模式的同时把该模式中所有的数据库对象全部删除 RESTRICT(限制)

如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。

当该模式中没有任何下属的对象时才能执行。[例4]DROPSCHEMAZHANGCASCADE；.定义基本表CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。.例题[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；.定义基本表（续）常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与

UNIQUE的区别？.例题（续）[例2]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno));.模式与表每一个基本表都属于某一个模式一个模式包含多个基本表定义基本表所属模式方法一：在表名中明显地给出模式名 Createtable“S-T”.Student（......）;/*模式名为S-T*/ Createtable“S-T”.Cource（......）; Createtable“S-T”.SC（......）;方法二：在创建模式语句中同时创建表方法三：设置所属的模式.二、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；.例题[例2]向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。

.语句格式（续）删除属性列

直接/间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名直接删除属性列:(新)例：ALTERTABLEStudentDropScome；.

[例3]将年龄的数据类型改为半字长整数。

ALTERTABLEStudentMODIFYSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据.例题[例4]删除学生姓名必须取唯一值的约束。

ALTERTABLEStudentDROPUNIQUE(Sname)；.三、删除基本表 DROPTABLE<表名>;

基本表删除 表的定义、数据、表上的索引都删除 表上的视图往往仍然保留，但 无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述

（标准中没有，认为表建立后就永久存在）.例题[例5]删除Student表

DROPTABLE

Student;.3.2.2建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引.索引RDBMS中索引一般采用B+树、HASH索引来实现B+树索引具有动态平衡的优点HASH索引具有查找速度快的特点采用B+树，还是HASH索引则由具体的RDBMS来决定索引是关系数据库的内部实现技术，属于内模式的范畴CREATEINDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引.一、建立索引CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； .例题[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

.建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束.建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放

.建立索引（续）在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作.二、删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。

DROPINDEXStusname；.3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结.3.3.1概述语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

.语句格式SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件

GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序.

数据查询是数据库应用的核心功能基本结构

SelectA1,A2,...,An

Fromr1,r2,...,rm

WhereP

πA1,A2,...,An(p(r1×r1×...×rm))数据查询SelectWhereFrom.SQL具有包的特性Select子句的缺省情况是保留重复元组（ALL），可用

Distinct去除重复元组SelectDistinctsdeptFromStudentSelectAllsdeptFromStudent去除重复元组:费时需要临时表的支持 Select子句——重复元组.Select子句——*与属性列表星号*表示所有属性星号*：按关系模式中属性的顺序排列，并具有一定的逻辑数据独立性显式列出属性名：按用户顺序排列Select*FromStudentSelectStudent.*，cno，gradeFromStudent，SCWhereStudent.sno=SC.sno.Select子句——更名为结果集中的某个属性改名使结果集更具可读性Selectsnoasstu_no，cnoascourse_no，gradeFromSCSelectsno，sname，2001-sageasbirthdayFromStudent.Where

子句——运算符比较：<、<=、>、>=、=、<>、not+~确定范围：

BetweenAandB、NotBetweenAandB确定集合：IN、NOTIN字符匹配：LIKE，NOTLIKE空值：ISNULL、ISNOTNULL多重条件：AND、OR、NOT.Where子句——Like字符匹配：Like、NotLike通配符%——匹配任意字符串_——匹配任意一个字符大小写敏感.Where子句——Like例：列出姓张的学生的学号、姓名。

Selectsno，sname FromStudent WheresnameLIKE‘张%’.Where子句——Like例：列出张姓且单名的学生的学号、姓名。

Selectsno，sname FromStudent WheresnameLIKE‘张__’.Where子句——转义符escape

例：列出课程名称中带有‘_’的课号及课名。

Selectcno，cname FromCourse WherecnameLIKE‘%\_%’escape‘\’.From子句From子句对应与关系代数中的笛卡儿积（×）列出将被扫描的关系（表）例：列出所有学生的学号、姓名、课号、成绩。SelectSudent.sno，sname，SC.cno，gradeFromStudent，SCWhereStudent.sno=SC.sno.From子句——元组变量为From子句中的关系定义元组变量方便关系名的引用在同一关系的笛卡儿积中进行辨别例：列出与95001同岁的同学的学号,姓名,年龄Select T.sno,T.sname,T.sageFrom StudentasT,StudentasSWhere S.sno=‘95001’AND T.sage=S.sage

.示例数据库学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade).3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结.3.3.2单表查询

查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组

.查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；.查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；.3.查询经过计算的值SELECT子句的<目标列表达式>为表达式算术表达式字符串常量函数列别名等.3.查询经过计算的值[例4]查全体学生的姓名及其出生年份。SELECTSname，2000-SageFROMStudent；

输出结果：

Sname2000-Sage----------------------

李勇1976

刘晨1977

王名1978

张立1978.3.查询经过计算的值[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，'YearofBirth:'，2000-Sage，

ISLOWER(Sdept)FROMStudent；

.例题（续）输出结果：

Sname'YearofBirth:'2000-SageISLOWER(Sdept)----------------------------------------------

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977is.[例5.1]使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’

BIRTH，

2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；输出结果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977is.二、选择表中的若干元组消除取值重复的行查询满足条件的元组.1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

SnoCnoGrade---------------------9500119295001285950013889500229095002380.ALL与

DISTINCT

[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;

或(默认ALL)SELECTALLSnoFROMSC;

结果：Sno-------9500195001950019500295002.例题（续）(2)SELECTDISTINCTSnoFROMSC;

结果：

Sno-------9500195002.例题（续）注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法

SELECTDISTINCTCno，GradeFROMSC;

.2.查询满足条件的元组WHERE子句常用的查询条件.(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；

.(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

.例题（续）[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

.(3)确定集合使用谓词IN<值表>,NOTIN<值表>

<值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');.(3)确定集合[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');.(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词.通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串.ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。.例题1)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：

SELECT*FROMStudentWHERESno='95001'；.例题（续）2)匹配模板为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；.例题（续）匹配模板为含通配符的字符串（续）[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；.例题（续）匹配模板为含通配符的字符串（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__阳%'；.例题（续）匹配模板为含通配符的字符串（续）[例18]查询所有不姓刘的学生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；.例题（续）3)使用换码字符将通配符转义为普通字符

[例19]查询DB_Design课程的课程号和学分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'.例题（续）使用换码字符将通配符转义为普通字符(续)[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；.(5)涉及空值的查询

使用谓词ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，CnoFROMSCWHEREGradeISNULL；.例题(续)[例22]查所有有成绩的学生学号和课程号。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；.(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级可用来实现多种其他谓词

[NOT]IN[NOT]BETWEEN…AND….例题[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；.改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；.改写[例10][例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；.三、对查询结果排序

使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示.对查询结果排序（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；.查询结果

SnoGrade--------------9501095024950079295003829501082950097595014619500255.对查询结果排序（续）[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORD