数据库课件：关系数据库标准语言SQL

关系数据库标准语言

——SQL15.1SQL概述5.2数据定义5.3数据查询5.4数据更新5.5视图管理5.6数据控制5.7嵌入式SQL5.8SQLServer简介25.1SQL概述1970年美国IBM研究中心的E.F.Codd提出关系模型。1972年IBM公司研制实验型关系数据库管理系统SYSTEMR1974年Boyce和Chamberlin把SQUARE修改为SEQUEL（StructuredEnglishQueryLanguage）语言。1979年关系式软件公司（RelationalSoftware，Inc即如今的Oracle公司）发展了第一种以SQL实现的商业产品。1986年10月美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月ISO提出了具有完整性特征的SQL89标准。1992年11月ISO公布了SQL92标准3数据定义语言（DDL）数据操纵语言（DML）数据控制语言（DCL）嵌入式SQL语言（E－SQL）

SQL的核心主要包括4个部分：SQL的一般特性1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方式5.语言简洁，易学易用6.支持关系数据库的3级模式结构56一、综合统一集DDL、DML、DCL的功能于一体语言风格统一可独立完成数据库生命周期中的全部活动定义关系模式，插入数据建立数据库对数据库中的数据进行查询和更新数据库重构和维护数据库安全性、完整性控制实体和实体之间的联系均用关系表示数据结构的单一性使每种操作只需一种操作符7二、高度非过程化只需提出“做什么”，无须指明“怎么做”，不需要了解存取路径存取路径的选择以及SQL语句的操作过程由系统自动完成减轻用户负担，提高数据独立性8三、面向集合的操作方式采用集合操作方式操作对象。查询结果是元组的集合一次插入、删除、更新操作的对象也可是元组的集合9四、以同一种语法结构提供多种使用方式独立的语言独立用于联机交互用户可直接键入SQL命令进行操作嵌入式语言嵌入到高级语言程序中两种方式的语法结构基本一致提供了极大的灵活性和方便性10五、语言简捷，易学易用六、支持关系数据库的3级模式结构模式SQL用户基本表1视图视图2基本表2基本表3基本表4存储文件1存储文件1存储文件1存储文件1外模式内模式SQL语言支持的关系数据库的三级模式结构1112SQL语言的基本概念1、用户可以用SQL语言对视图(View)和基本表(BaseTable)进行查询等操作，在用户观点里，视图和表一样，都是关系。2、视图是从一个或多个基本表中导出的表，在数据库中本身不存储对应的数据，只存放其定义，可以将其理解为一个虚表。可在视图上再定义视图。3、基本表是本身独立存在的表，每个（或多个）基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。SQL用户BaseTableB1ViewV1ViewV2BaseTableB2BaseTableB3BaseTableB4StoredFileS1StoredFileS1StoredFileS1StoredFileS1外模式模式内模式13学生－课程数据库学生－课程模式S-T中定义三个基本表：学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade)14学生表StudentSnoSnameSsexSageSdet字符型长度为9不能为空值字符型长度为2字符型长度为20字符型长度为20整数15课程表CourseCnoCnameCpnoCcredit字符型长度为4不能为空值字符型长度为4字符型长度为40整数16学生选课表SCSnoCnoGrade字符型长度为9字符型长度为4整数SQL语言功能SQL功能命令动词数据定义CREATE、DROP、ALTER数据控制GRANT、REVOKE数据查询SELECT数据操纵INSERT、UPDATE、DELETE175.2数据定义操作对象操作方式创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW

索引CREATEINDEXDROPINDEX

SQL的数据定义功能提供了包括定义表、定义视图和定义索引三个功能

185.2.1SQL的基本数据类型关系数据库支持非常丰富的数据类型，不同的数据库管理系统支持的数据类型基本是一样的，主要有数值型、字符串型、位串型和时间型等SQLServer的主要数据类型见表5-3195.2.2基本表的创建、修改和撤销1.表的概念表（table）是用来存储数据的二维数组，它有行（rows）和列（columns）。列也称为表属性或字段，表中的每一列拥有惟一的名字，每一列包含具体的数据类型202.建立数据表1）定义数据结构

2）命名约定3）CREATETABLE语句创建表的基本语法如下：CREATETABLE<表名>(<列名><数据类型>[列完整性的约束条件][，<列名><数据类型>[列完整性的约束条件]]……[，<表级完整性的约束条件>]);<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件21约束条件类型NOTNULLCHECKPRIMARYKEYFOREIGNKEYUNIQUE22【例5-1】建立一个“职工”表。所用语句如下：CREATETABLEemployee（Emp_idCHAR(5)NOTNULLUNIQUE,Emp_nameCHAR(20)NOTNULL,Emp_sexCHAR(2),Emp_ageINT,Emp_deptCHAR(15),PRIMARYKEY(Emp_id),CHECK(Emp_ageBETWEEN18AND60),);2324[例]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY，

SnameCHAR(20)UNIQUE，

SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20))；25[例6]建立一个“课程”表Course。

CREATETABLECourse(CnoCHAR(4)PRIMARYKEY，

CnameCHAR(40)，

CpnoCHAR(4)，

CcreditSMALLINT，

FOREIGNKEY(Cpno)REFERENCESCourse(Cno))；26[例7]建立一个“学生选课”表SC。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno), FOREIGNKEY(Cno)REFERENCESCourse(Cno))3.修改数据表一般格式如下：ALTERTABLE<表名>ADD<列名><数据类型>[<列级完整性约束>]|DROPCONSTRAINT<完整性约束名>|DROPCOLUMN<列名>|ALTERCOLUMN<列名><数据类型>[<列级完整性约束>]27【例5-2】向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomeDATE；注意：不论基本表中原来是否已有数据，新增加的列一律为空值。284.撤销数据表一般格式如下：DROPTABLE<表名>基本表定义一旦被删除，表中的数据、表上的索引都自动删除。表上的视图往往仍然保留，但无法引用。删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述。

295.2.3索引的创建和撤销建立索引是加快查询速度的有效手段

用户对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。有关“索引”的比喻从某种程度上，可以把数据库看作一本书，把索引看作书的目录，通过目录查找书中的信息，显然较没有目录的书方便、快捷。5.2.3索引的创建和撤销1.创建索引

CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][，<列名>[<次序>]]...);用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引3132例题[例12]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

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

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

DROPINDEXStusname；5.3数据查询SQL查询语句的一般格式为:SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...FROM<表名或视图名>[,<表名或视图名>]...[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];3738语句格式SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件

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

查询方式运算符比较=，>=，<，<=，!=，<>，!>，!<确定范围BETWEENAND、NOTBETWEENAND确定集合IN、NOTIN字符匹配LIKE、NOTLIKE控制ISNULL、ISNOTNULL否定NOT多重条件AND、OR394041查询指定列[例]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

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

输出结果：

Sname----------------------

李勇1990

刘晨1991

王名1992

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

LOWER(Sdept)FROMStudent;

输出结果：

SnameYearofBirth:----------------------------------------------

李勇YearofBirth:1990cs

刘晨YearofBirth:1991is

王名YearofBirth:1992ma

张立YearofBirth:1990is46[例]使用列别名改变查询结果的列标题SELECTSnameNAME,'YearofBirth:’

BIRTH,

2011-SageBIRTHDAY,LOWER(Sdept)DEPARTMENTFROM

Student；输出结果：

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

李勇YearofBirth:1990cs

刘晨YearofBirth:1991is

王名YearofBirth:1992ma

张立YearofBirth:1990is5.3.2单表查询(续)2.选择表中的若干元组1）取消重复元组【例5-14】查询选修了课程的学生学号。SELECTDISTINCTSnoFROMSC;【例5-15】查询选修课程的各种成绩SELECTDISTINCTCno,GradeFROMSC;

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

SELECTDISTINCTCno，GradeFROMSC;

2）查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。（1）比较大小。在WHERE子句的<条件表达式>中使用比较运算符从而对大小进行比较。【例5-16】查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname,SageFROMStudentWHERESage<20;或SELECTSname,SageFROMStudentWHERENOTSage>=20;5.3.2单表查询(续)49【例5-17】查询计算机系全体学生的名单。SELECTSnameFROMStudentWHERESdept=‘CS’【例5-18】查询考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade<6050（2）确定范围。在WHERE子句的<条件表达式>中使用BETWEEN…AND…NOTBETWEEN…AND…来确定范围。【例5-19】查询年龄在18至20岁之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN18AND20;5.3.2单表查询(续)51（3）确定集合。我们可以使用谓词IN用来查找属性值属于指定集合的元组。【例5-21】查询数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname,Ssex,SdeptFROMStudentWHERESdeptIN('MA','CS');5.3.2单表查询(续)52确定集合（续）[例5-21]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');53（4）字符（串）匹配。谓词LIKE用来进行全部或部分字符串匹配。在进行部分字符串匹配时要用通配符“%”和“_”。其中，“%”匹配零个或多个字符，“_”匹配单个字符。此外，当LIKE之后的匹配串为固定匹配串（即不含通配符）时，可以用“=”运算符代替LIKE谓词，用“<>”或“!=”代替NOTLIKE谓词。

【例5-24】查询所有姓江学生的姓名、学号和性别。

SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'江%';5.3.2单表查询(续)5455例题1)匹配模板为固定字符串[例5-23]查询学号为200215121的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE‘200215121'；等价于：

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

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

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

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

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

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

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

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

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；（5）涉及空值的查询。当查询涉及到空值时，就要使用谓词ISNULL或ISNOTNULL，注意“ISNULL”不能用“=NULL”来代替。【例5-30】查询选修了课程但没有成绩的学生学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL;5.3.2单表查询(续)62（6）多重条件查询。当查询的条件不止一个时，可以使用逻辑运算符AND和OR来联结多个查询条件。AND的优先级高于OR，但用户可以使用括弧改变优先级。【例5-32】查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20;5.3.2单表查询(续)633.对查询结果排序如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在数据表中的先后顺序）输出查询结果。当然，用户也可以用ORDERBY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。【例5-33】查询计算机系（CS）所有学生的名单并按学号升序显示。SELECTSname,SnoFROMStudentWHERESdept='CS'ORDERBYSnoASC;5.3.2单表查询(续)64【例5-34】查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC;654.使用聚集函数COUNT（*）：计算元组的个数。COUNT（列名）：对一列中的值计算个数。SUM（列名）：求某一列值的总和（此列的值必须是数值型）。AVG（列名）：求某一列值的平均值（此列的值必须是数值型）。MAX（列名）：求某一列值的最大值。MIN（列名）：求某一列值的最小值。5.3.2单表查询(续)66【例5-35】查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC;【例5-37】查询学生S1所修读课程的平均成绩。SELECTAVG(Grade)FROMSCWHERESno=‘S1’;67【例5-38】查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERECno=‘1’;685.对查询结果分组GROUPBY子句可以将查询结果表的各行按一列或多列值分组，值相等的为一组。同时，我们还可以使用HAVING短语设置逻辑条件【例5-39】查询每个学生的平均成绩。SELECTSno,AVG(Grade)FROMSCGROUPBYSno;注意：使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数。5.3.2单表查询(续)69【例5-40】查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3;70WHERE子句与HAVING短语的区别WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组【例5-41】查询所有课程（不包括课程C1）都及格的所有学生的平均成绩，结果按平均值降序排列。SELECTSno,AVG(Grade)FROMSCWHERECno<>'C1'GROUPBYSnoHAVINGMIN(Grade)>=60ORDERBYAVG(Grade)DESC;715.3.3连接查询若一个查询同时涉及两个以上的表，则称之为连接查询。其一般格式为： [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2]

其中：比较运算符主要有：=、>、<、>=、<=、!=。此外连接谓词还可以使用下面形式：[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名3>.]<列名3>7273连接查询（续）连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的74连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕1.等值与非等值连接在连接查询中，当连接运算符为“=”时，称为等值连接。否则称为非等值连接。【例5-42】查询每个学生及其选修课程的情况。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;或SELECTStudent.*,SC.*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno;75等值与非等值连接查询（续）结果假设Student表、SC表分别有下列数据：

Student表SC表SnoSnameSsexSageSdept95001李勇男

20

CS95002刘晨女

19

IS95003王敏女

18

MA95004张立男

19

ISSnoCnoGrade9500119295001285950013889500229095002380等值与非等值连接查询（续）结果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002刘晨女19 IS 9500229095002刘晨女19 IS 95002380

SnoSnameSsexSageSdept95001李勇男

20

CS95002刘晨女

19

IS95003王敏女

18

MA95004张立男

19

ISSnoCnoGrade9500119295001285950013889500229095002380等值与非等值连接查询（续）自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。

<表名1>.<列名1>=<表名2>.<列名2>SELECT语句不能直接实现自然连接对[例5-42]用自然连接完成。

SELECTStudent.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；2.自身连接一个表与其自己进行连接，称为表的自身连接。为了实现自连接需要将一个关系看作两个逻辑关系，为此需要给关系指定别名。由于所有属性名都是同名属性，因此必须使用别名前缀。【例5-44】查询至少修读学号为95001的学生所修读的一门课的学生学号。SELECTSC1.SnoFROMSCSC1,SCSC2WHERESC1.Cno=SC2.CnoANDSC2.Sno='95001';7980SnoCnoGradeSnoCnoGrade950011929500119295001285950012859500128595002290950013889500138895001388950023809500229095001285950022909500229095002380950013889500238095002380SnoCnoGrade9500119295001285950013889500229095002380SnoCnoGrade9500119295001285950013889500229095002380SC1SC281SnoCnoGradeSnoCnoGrade95001192950011929500128595001285950013889500138895002290950012859500238095001388Sno95001950019500195002950023.外连接

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接。【例5-45】查询每个学生及其选修课程的情况（即使没有选课也列出该学生的基本情况）。SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent.Sno=SC.Sno(*);在SQLServer系统里应该写为:SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTJOINSCONStudent.Sno=SC.Sno;

82外连接（续）结果：

Student.SnoSnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002刘晨女19IS29095002刘晨女19IS38095003王敏女18MA95004张立男19IS84外连接（续）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出4.复合条件连接WHERE子句中有多个条件的连接操作，称为复合条件连接。【例5-46】查询选修1号课程且成绩在90分以上的所有学生的学号、姓名。SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDCno='1'ANDGrade>90;85865.3.4嵌套查询嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

87嵌套查询(续)SELECTSname 外层查询/父查询

FROMStudentWHERESnoIN

（SELECTSno内层查询/子查询

FROMSCWHERECno='2'）；求选修了2号课程的学生姓名88嵌套查询(续)子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代89引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询5.3.4嵌套查询1.带有IN谓词的子查询带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。【例5-48】查询与“刘振”在同一个系学习的学生信息。

SELECT*FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='刘振');902.带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。【例5-48】查询年龄比“刘振”大的学生的学号和姓名。SELECTSno,SnameFROMStudentWHERESage>(SELECTSageFROMStudentWHERESname='刘振');5.3.4嵌套查询(续)913.带有ANY或ALL谓词的子查询使用ANY或ALL谓词时则必须同时使用比较运算符。ANY表示任意一个值，ALL表示全部值。【例5-49】查询非CS系的学生名单，并且这些学生必须满足这个条件：在CS系中有学生的年龄比这些学生大。SELECTSname,SageFROMStudentWHERESdept<>'CS'ANDSage<ANY(SELECTSageFROMStudentWHERESdept='CS');5.3.4嵌套查询(续)9293带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY 不等于子查询结果中的某个值!=（或<>）ALL 不等于子查询结果中的任何一个值94带有ANY或ALL谓词的子查询（续）ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系

=

<>或!=

<<=>>=ANY

IN

--

<MAX<=MAX>MIN>=MINALL--

NOTIN

<MIN<=MIN>MAX>=MAX

SELECTSname,SageFROMStudentWHERESdept<>‘CS’ANDSage<(SELECTMAX(Sage)FROMStudentWHERESdept=‘CS‘)9596带有ANY或ALL谓词的子查询（续）[例]查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS’;97带有ANY或ALL谓词的子查询（续）

方法二：用集函数

SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;4.带有EXISTS谓词的子查询EXISTS代表存在量词“

”。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true”或逻辑假值“false”。5.3.4嵌套查询(续)985.3.4嵌套查询(续)【例5-51】查询所有选修了1号课程的学生姓名。思路分析：本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno='1'，则取此Student.Sname送入结果关系。99【例5-51】查询所有选修了1号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');由EXISTS引出的子查询，其目标列表达式通常都用*，这是因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。100101带有EXISTS谓词的子查询(续）例：查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

（SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘

刘晨’）；102带有EXISTS谓词的子查询(续）量词：定义：设P(x)是包含变元x的句子，并且设D是一个集合。对于D中的每一个x，P(x)是一个命题，称P是（相对于D的）一个命题函数或谓词。D称为P的论域。全称量词

的意思是“对每个”。句子可写成：

xP(x)存在量词语句

的意思是“存在”。句子可写成：

xP(x)103带有EXISTS谓词的子查询(续）用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词

（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(

x)P≡

(

x(

P))104带有EXISTS谓词的子查询(续）[例]查询选修了全部课程的学生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）；5.3.5集合查询集合操作:并操作(UNION)交操作(INTERSECT)差操作(MINUS)【例5-55】查询计算机科学系（CS）的学生及年龄小于20岁的学生。SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<20;1055.4数据更新SQL中数据更新包括插入、修改和删除。执行插入、修改和删除操作时可能会受到关系完整性的约束，这种约束是为了保证数据库中的数据的正确性和一致性。1065.4.1插入数据1.插入单个元组一般格式为：INSERTINTO<表名>[(<属性列1>[,<属性列2>…)]VALUES(<常量1>[,<常量2>]…)【例5-58】将一个新学生记录（学号：841064；姓名：刘振；性别：男；所在系：CS；年龄：18岁）插入Student表中。INSERTINTOStudentVALUES('841064','刘振','男','CS',18);1072.插入子查询结果一般格式为：INSERTINTO<表名>[(<属性列1>[，<属性列2>...)]子查询;说明：该语句的功能将子查询结果插入指定表中，这是一种批量插入形式。INTO用法与前面所述的相同。在子查询中，SELECT子句目标列必须与INTO子句匹配，包括值的个数与值的类型。108【例5-60】求各个院系学生的平均年龄并存放于一张新表DeptAge(Sdept,Avgage)中，其中，Sdept存放系名，Avgage存放相应系的学生平均年龄。（1）建表。CREATETABLEDeptage(SdeptCHAR(15),AvgageSMALLINT);（2）插入。INSERTINTODeptAge(Sdept,Avgage)

SELECTSdept,AVG(Sage)

FROMStudentGROUPBYSdept;1095.4.2修改数据其一般格式为UPDATE<表名>SET<列名>=<表达式>[,<列名>=<表达式>]...[WHERE<条件>];【例5-61】将学号为841053的学生的年龄改为22岁。UPDATEStudentSETSage=22WHERESno='841053';1105.4.3删除数据一般格式为：DELETEFROM<表名>[WHERE<条件>];【例5-64】删除学号为841053的学生记录。DELETEFROMStudentWHERESno='841053';1115.5视图管理视图（View）是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表，只在数据目录中保留其逻辑定义，而不作为一个表实际存储在数据库中。当基本表中的数据发生变化，从视图中查询出的数据也随之改变。1125.5.1视图的创建与删除1.创建视图SQL创建视图语句的一般格式为：CREATEVIEW<视图名>[(<列名>[,<列名>]...)]AS<子查询>[WITHCHECKOPTION];【例5-67】建立计算机系学生的视图。CREATEVIEWCS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='CS'WITHCHECKOPTION;1132.删除视图SQL删除视图语句的一般格式为DROPVIEW<视图名>;【例5-72】删除前面建立的视图CS_S1。DROPVIEWCS_S1;1145.5.2视图操作1.视图查询视图定义后，用户就可以像对基本表进行查询一样对视图进行查询了。【例5-73】在计算机系学生的视图中找出年龄小于20岁的学生。SELECTSno,SageFROMCS_StudentWHERESage<20;对基本表的查询（即视图消解后的查询）为：SELECTSno,SageFROMStudentWHERESdept='CS'ANDSage<20;1152.视图更新更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。对视图的更新，最终要转换为对基本表的更新。【例5-75】将计算机系学生视图CS_Student中学号为841064的学生姓名改为“刘振”。UPDATECS_StudentSETSname='刘振'WHERESno='841064';转换为对基本表的更新：UPDATEStudentSETSname='刘振'WHERESno='841064'ANDSdept='CS';116不可更新视图（1）若视图是由两个以上基本表导出的，则此视图不允许更新。（2）若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。（3）若视图的字段来自聚集函数，则此视图不允许更新。（4）若视图定义中含有GROUPBY子句，则此视图不允许更新。（5）若视图定义中含有DISTINCT短语，则此视图不允许更新。（6）若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。1175.5.3视图的优点1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护1185.6数据控制1.用户权限2.授权与回收1191用户权限定义存取权限存取权限存取权限由两个要素组成数据对象操作类型关系系统中的存取权限类型

数据对象 操作类型模式 模式 建立、修改、删除、检索 外模式建立、修改、删除、检索内模式 建立、删除、检索

数据 表 查找、插入、修改、删除 属性列 查找、插入、修改、删除关系系统中的存取权限(续)定义方法GRANT/REVOKE关系系统中的存取权限(续)例:一张授权表用户名数据对象名允许的操作类型王平关系StudentSELECT

张明霞关系StudentUPDATE

张明霞关系CourseALL

张明霞SC.GradeUPDATE

张明霞SC.SnoSELECT

张明霞SC.CnoSELECT检查存取权限对于获得上机权后又进一步发出存取数据库操作的用户DBMS查找数据字典，根据其存取权限对操作的合法性进行检查若用户的操作请求超出了定义的权限，系统将拒绝执行此操作授权粒度授权粒度是指可以定义的数据对象的范围它是衡量授权机制是否灵活的一个重要指标。授权定义中数据对象的粒度越细，即可以定义的数据对象的范围越小，授权子系统就越灵活。关系数据库中授权的数据对象粒度数据库表属性列行2、SQL的授权功能GRANT语句的一般格式：

GRANT<权限>[,<权限>]...[ON<对象类型><对象名>]TO<用户>[,<用户>]...[WITHGRANTOPTION];功能：将对指定操作对象的指定操作权限授予指定的用户。【例5-78】把查询Student表权限授给用户User1，User2。 GRANTSELECTONTABLEStudentTOUser1,User2;127[例]把对表SC的查询权限授予所用用户。

GRANTSELECT ONTABLEStudent TOPUBLIC;[例]把查询Student表和修改学号的的权限授予用户U4。

GRANTUPDATE(Sno),SELECT ONTABLEStudent TOU4;128[例]把对表SC的INSERT权限授予用户U5，并允许将该权限再授予其他用户。

GRANTINSERT ONTABLESC TOU5 WITHGRANTOTION;1293收回权限SQL中可以用REVOKE语句收回权限。REVOKE语句的一般格式为REVOKE<权限>[,<权限>]...[ON<对象类型><对象名>]FROM<用户>[,<用户>]...;级联收回。系统只收回直接或间接从某用户获得的权限。130【例5-83】把用户User3修改学生学号的权限收回。REVOKEUPDATE(Sno)ONTABLEStudentFROMUser3;131[例]收回所有用户对表SC的查询权限。

REVOKESELECT ONTABLESC FROMPUBLIC[例]收回用户U5对SC表的INSERT权限。

REVOKEINSERT ONTABLESC FROMU5CASCADE132MSSQLServer2000中相关命令sp_addloginsp_addusersp_addrolesp_addrolemembergrantrevoke133数据库角色角色是SQLServer7.0版本引进的新概念，它代替了以前版本中组的概念。利用角色，SQLServer管理者可以将某些用户设置为某一角色，这样只对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了管理员的工作量。SQLServer提供了用户通常管理工作的预定义服务器角色和数据库角色。数据库角色是被命名的一组与数据库操作有关的权限，角色是权限的集合。可以为一组具有相同权限的用户创建一个角色。使用角色来管理数据库权限可以简化授权过程。数据库角色1、角色的创建例CreateroleR1;数据库角色2、给角色授权例GRANTselectONtableSCTOR1;数据库角色3、将一个角色授予其他角色或用户例GRANTR1TOU1;数据库角色4、角色权限收回例revokeselectOntableSCfromR1;5.7嵌入式SQLSQL语言提供了两种不同的使用方式：交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充139什么是宿主语言宿主语言嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言。140嵌入式SQL的处理过程Hostlanguage+EmbeddedSQLDBMSPreprocessorsHostlanguage+FunctioncallsHostlanguagecompilerObjectcodeSQLLibrary1415.7.1嵌入式SQL的说明部分为了区分SQL语句与主语言语句，所有SQL语句必须加前缀EXECSQL，以(;)结束:EXECSQL<SQL语句>;142嵌入式SQL语句与主语言之间的通信数据库工作单元与源程序工作单元之间的通信：1.SQL通信区向主语言传递SQL语句的执行状态信息使主语言能够据此控制程序流程格式为：

EXECSQLINCLUDESQLCA2.宿主变量主语言向SQL语句提供参数将SQL语句查询数据库的结果交主语言进一步处理格式为：

EXECSQLBEGINDECLARESECTION;…….EXECSQLENDDECLARESECTION;3.游标解决集合性操作语言与过程性操作语言的不匹配1435.7.2嵌入式SQL的可执行语句把程序作为一个用户，和数据库建立连接EXECSQLCONNECT:uidIDENTIFIENDBY:pwd;嵌入式SQL的DDL和DML语句除了前面加EXECSQL外，与ISQL基本相同

【例5-87】一个插入语句的例子。

EXECSQLINSERTINTOSC(Sno,Cno,Grade)VALUES(：Sno,：Cno,：Grade);144查询结果为单记录的SELECT语句这类语句不需要使用游标，只需要用INTO子句指定存放查询结果的主变量[例]根据学生号码查询学生信息。假设已经把要查询的学生的学号赋给了主变量givensno。EXECSQLSELECTSno，Sname，Ssex，Sage，SdeptINTO:Hsno，:Hname,:Hsex,:Hage,:HdeptFROMStudentWHERESno=:givensno；145查询结果为多条记录的SELECT语句说明游标语句

ExecsqlDeclare<游标名>CursorFor<查询块>;

打开游标语句

ExecsqlOpen<游标名>; //执行查询移动指针/获取数据ExecsqlFetch<游标名>Into<共享变量名><指示变量名>,...判断是否到底sqlca.sqlcode（＝＝100）关闭游标ExecsqlClose<游标名>;1465.7.3动态SQL简介静态SQLSql语句在写应用程序时指明应用程序中的SQL命令在编译时已经确定.动态