关系数据库SQL复习琦在江湖飘

SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言1.2SQL的特点

1.综合统一

集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。数据操作符统一2.高度非过程化

非关系数据模型的数据操纵语言面向过程，必须制定存取路径SQL只要提出做什么，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。3.面向集合的操作方式

非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式4.以同一种语法结构提供两种使用方法

SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言（例如C，C++，Java）5.语言简洁，易学易用

1.3SQL的基本概念

基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图

数据定义

模式定义、表定义、视图和索引的定义

2.1模式的定义与删除

定义模式实际上定义了一个命名空间。

在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

在CREATESCHEMA中可以接受CREATETABLE，CREATEVIEW和GRANT子句。

eg:CREATESCHEMAS-TAUTHORIZATIONWANG;为用户WANG定义了一个模式S-T

CREATESCHEMAAUTHORIZATIONWANG；<模式名>隐含为用户名WANG如果没有指定<模式名>，那么<模式名>隐含为<用户名>DROPSCHEMA<模式名>CASCADE(级联):删除模式的同时把该模式中所有的数据库对象全部删除RESTRICT(限制):如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。eg:删除模式ZHANGDROPSCHEMAZHANGCASCADE；2.2基本表的定义、删除与修改

CREATETABLE<表名>（

<列名><数据类型>[<列级完整性约束条件>]...

[，<表级完整性约束条件>]

）；

<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

/*建立学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。学号是主码，姓名取值唯一。*/CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，/*列级完整性约束条件*/SnameCHAR(20)UNIQUE，/*Sname取唯一值*/SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20))；//建立一个课程表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，CnameCHAR(40)，CpnoCHAR(4)，CcreditSMALLINT，FOREIGNKEY(Cpno)REFERENCESCourse(Cno)/*Cpno是外码,被参照表是Course,被参照列是Cno*/);//建立一个学生选课表SCCREATETABLESC(SnoCHAR(9)，CnoCHAR(4)，GradeSMALLINT，PRIMARYKEY(Sno，Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*表级完整性约束条件，Sno是外码，被参照表是Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);

模式与表的关系

每一个基本表都属于某一个模式一个模式包含多个基本表

定义基本表所属模式

在表名中明显地给出模式名CreatetableS-T.SC（......）;

在创建模式语句中同时创建表

设置所属的模式

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式显示当前的搜索路径：SHOWsearch_path;搜索路径的当前默认值是：$user，PUBLIC

DBA用户可以设置搜索路径，然后定义基本表

SETsearch_pathTOS-T，PUBLIC；

CreatetableStudent（......）;

结果建立了S-T.Student基本表。修改基本表

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

eg：

ALTERTABLEStudentADDS_entranceDATE；

将年龄的数据类型由字符型改为整数。

ALTERTABLEStudentALTERCOLUMNSageINT；

增加课程名称必须取唯一值的约束条件。

ALTERTABLECourseADDUNIQUE(Cname);

若表上建有视图，选择RESTRICT时基本表不能删除。如果选择CASCADE时可以删除表，视图也自动被删除

2.3索引的建立与删除

建立索引目的：加快查询速度

建立索引权限

DBA或表的属主（即建立表的人）可以建立索引。

DBMS一般会自动建立以下列上的索引PRIMARYKEYUNIQUE。

维护索引DBMS自动完成。

使用索引DBMS自动选择是否使用索引以及使用哪些索引。

索引是关系数据库的内部实现技术，属于内模式的索引是关系数据库的内部实现技术，属于内模式的范畴。

RDBMS中索引一般采用B+树、HASH索引来实现。采用B+树，还是HASH索引则由具体的RDBMS来决定

B+树索引具有动态平衡的优点

HASH索引具有查找速度快的特点

CREATEINDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引

CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；

eg：在Student表的Sname（姓名）列上建立一个聚簇索引

CREATECLUSTERINDEXStusnameONStudent(Sname)；在最经常查询的列上建立聚簇索引以提高查询效率，一个基本表上最多只能建立一个聚簇索引，经常更新的列不宜建立聚簇索引

/*为学生-课程数据库中的Student，Course，SC三个表建立索引。Student表按学号升序建唯一索引Course表按课程号升序建唯一索引SC表按学号升序和课程号降序建唯一索引*/CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；删除索引

DROPINDEX<索引名>；

eg:删除Student表的Stusname索引

DROPINDEXStusname；

数据查询

3.1单表查询

3.1.1选择表中的若干列

查询指定列查询经过计算的值SELECT子句的<目标列表达式>可以为算术表达式字符串常量函数列别名

/*查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名*/SELECTSname，‘YearofBirth:'，2004-Sage，ISLOWER(Sdept)FROMStudent；//使用列别名改变查询结果的列标题:SELECTSnameNAME，'YearofBirth:’BIRTH，2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；3.1.2选择表中的若干元组

(1)比较大小

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

SELECTSnameFROMStudentWHERESdept=‘CS’；

（2）确定范围

谓词:BETWEEN…AND…;NOTBETWEEN…AND…

(3)确定集合

谓词：IN<值表>,NOTIN<值表>

eg:查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');

(4)字符匹配

谓词：[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]

//匹配串为固定字符串SELECT*FROMStudentWHERESnoLIKE‘200215121'；//等价于SELECT*FROMStudentWHERESno='200215121'；//匹配串为含通配符的字符串//查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；//查询姓"欧阳"且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；//查询名字中第2个字为"阳"字的学生的姓名和学号SELECTSname，SnoFROMStudentWHERESnameLIKE‘__阳%’；//查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；//使用换码字符将通配符转义为普通字符//查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\’；//查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。//ESCAPE'＼'表示＼为换码字符SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\’；(5)涉及空值的查询

谓词：ISNULL或ISNOTNULLIS不能用=代替

(6)多重条件查询逻辑运算符：AND和OR来联结多个查询条件ND的优先级高于OR可以用括号改变优先级

3.1.3ORDERBY子句

ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示

//查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；//查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；3.1.4聚集函数

计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>）计算平均值AVG（[DISTINCT|ALL]<列名>）最大最小值MAX（[DISTINCT|ALL]<列名>）MIN（[DISTINCT|ALL]<列名>）

eg：查询学生200215012选修课程的总学分数。

SELECTSUM(Ccredit)FROMSC，CourseWHERSno='200215012'ANDSC.Cno=Course.Cno;

3.1.5GROUPBY子句

GROUPBY子句分组：

未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组按指定的一列或多列值分组，值相等的为一组

//求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；//查询选修了3门以上课程的学生学号SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；HAVING短语与WHERE子句的区别：

WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。

3.2连接查询

3.2.1连接查询：同时涉及多个表的查询一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的

连接操作的执行过程嵌套循环法(NESTED-LOOP)1）首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。

2）表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组

3）重复上述操作，直到表1中的全部元组都处理完毕

排序合并法(SORT-MERGE)1）首先按连接属性对表1和表2排序2）对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续3）找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续4）重复上述操作，直到表1或表2中的全部元组都处理完毕为止索引连接(INDEX-JOIN)1）对表2按连接字段建立索引2）对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

3.2.2等值与非等值连接查询

//等值连接：连接运算符为=//查询每个学生及其选修课程的情况SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；//自然连接：//查询每个学生及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno；3.2.3自身连接

自身连接：一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀

//查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；3.2.4外连接

外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

//查询每个学生及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；左外连接：列出左边关系（如本例Student）中所有的元组右外连接：列出右边关系中所有的元组

3.2.5复合条件连接

复合条件连接：WHERE子句中含多个连接条件

//查询选修2号课程且成绩在90分以上的所有学生SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno=‘2’ANDSC.Grade>90；/*其他限定条件*/3.3嵌套查询

3.3.1嵌套查询子查询

一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

SELECTSname/*外层查询/父查询*/FROMStudentWHERESnoIN（SELECTSno/*内层查询/子查询*/FROMSCWHERECno='2'）；子查询的限制：不能使用ORDERBY子句

层层嵌套方式反映了SQL语言的结构化

有些嵌套查询可以用连接运算替代

2）然后再取外层表的下一个元组

3）重复这一过程，直至外层表全部检查完为止

3.3.2带有IN谓词的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。

子查询一定要跟在比较符之后

谓词语义ANY：任意一个值ALL：所有值

//查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESage‘CS';/*父查询块中的条件*/执行过程:

1)RDBMS执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20，19)

2)处理父查询，找所有不是CS系且年龄小于20或19的学生

3.3.5带有EXISTS谓词的子查询

1.EXISTS谓词

1)存在量词2)带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值true或逻辑假值false若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值3)由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOTEXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值

3.4集合查询

并操作UNION交操作INTERSECT差操作EXCEPT

4.1插入数据

插入元组

INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]VALUES(<常量1>[，<常量2>]…)INTO子句属性列的顺序可与表定义中的顺序不一致VALUES子句提供的值必须与INTO子句匹配

插入子查询结果

INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]子查询；子查询:SELECT子句目标列必须与INTO子句匹配

//对每一个系，求学生的平均年龄，并把结果存入数据库//建表CREATETABLEDept_age(SdeptCHAR(15)/*系名*/Avg_ageSMALLINT)；/*学生平均年龄*///插入数据INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

4.2修改数据

UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；

//学生200215121的年龄改为22岁UPDATEStudentSETSage=22WHERESno='200215121'；//将所有学生的年龄增加1岁UPDATEStudentSETSage=Sage+1；//将计算机科学系全体学生的成绩置零UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性主码不允许修改用户定义的完整性NOTNULL约束UNIQUE约束值域约束

4.3删除数据

DELETEFROM<表名>[WHERE<条件>]；

//删除计算机科学系所有学生的选课记录DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；

5.1视图概念及作用

视图的特点

虚表，是从一个或几个基本表（或视图）导出的表

只存放视图的定义，不存放视图对应的数据

基表中的数据发生变化，从视图中查询出的数据也随之改变

视图的作用

视图能够简化用户的操作

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

视图对重构数据库提供了一定程度的逻辑独立性

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

适当的利用视图可以更清晰的表达查询

5.2定义视图

CREATEVIEW<视图名>[(<列名>[，<列名>]…)]AS<子查询>[WITHCHECKOPTION]；

子查询不允许含有ORDERBY子句和DISTINCT短语

RDBMS执行CREATEVIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

在对视图查询时，按视图的定义从基本表中将数据查出。

//建立信息系学生的视图CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=‘IS’；//建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；带WITHCHECKOPTION选项时对IS_Student视图的更新操作：修改操作：自动加上Sdept='IS'的条件删除操作：自动加上Sdept='IS'的条件插入操作：自动检查Sdept属性值是否为'IS

//基于多个基表的视图CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；//基于视图的视图//建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；//分组视图//将学生的学号及他的平均成绩定义为一个视图。CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；删除视图

DROPVIEW<视图名>；如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除

5.3查询视图

用户角度：查询视图与查询基本表相同

RDBMS实现视图查询的方法视图消解法

进行有效性检查转换成等价的对基本表的查询执行修正后的查询

//S_G视图的子查询定义CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；//查询转换//错误：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；//正确：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；5.4更新视图

//将信息系学生视图IS_Student中学号200215122的学生姓名改为刘辰UPDATEIS_StudentSETSname='刘辰'WHERESno=‘200215122’；//转换后的语句：UPDATEStudentSETSname='刘辰'WHERESno='200215122'ANDSdept='IS'；更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

//视图S_G为不可更新视图UPDATES_GSETGavg=90WHERESno=‘200215121’；//这个对视图的更新无法转换成对基本表SC的更新