【文档说明】数据库技术-第3章+结构化查询语言SQL(数据库技术)-课件.ppt，共(93)页，924.512 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92444.html

以下为本文档部分文字说明：

第3章结构化查询语言-SQL◆3.1SQL概述3.2SQL语言3.3视图◆3.4SQL中的复杂完整性约束3.5小结结构化查询语言SQL(StructuredQueryLanguage)是一种面向集合的数据库查询语言。SQL语言是集数据查询（DataQuery）、数据操纵（Data

Manipulation）、数据定义（DataDefinition）和数据控制（DataControl）于一体的一种非过程化语言。数据DBMSClient请求回答SQL语言Oracle8i/9i/10g/11gSQLS

erver2000、2005、2008、2012第3章结构化查询语言-SQL◆3.1SQL概述3.2SQL语言3.3视图◆3.4SQL中的复杂完整性约束3.5小结SQL最早的版本是IBM开发的。是20世纪70年代早期作为SystemsR项目的一部分实现的。现在有许多产品支持SQL语言，它

已经很明确地确立了自己作为标准关系数据库语言的地位。1986年，美国国家标准化组织（ANSI）和国家标准化组织（ISO）发布了SQL标准：SQL-86；1989年，发布了SQL-89；1992年，发布了SQL-92；

1999年，发布了SQL-99；2003年，发布了SQL-2003;2006年，发布了SQL-2006;SQL语言有以下几部分：数据定义语言；数据操作语言；完整性；视图定义：嵌入式SQL和动态SQL:授权SQL视图1视图2基本

表1基本表2基本表3基本表4存储文件1存储文件2内模式外模式模式SQL数据库体系结构（三级模式）外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。特征如下：一个SQL模式(Schema)是表和

约束的集合。一个表(Table)是行(Row)的集合，每行是列(Column)的序列，每列对应一个数据项。—个表可以是一个基本表，也可以是一个视图，基本表是实际存储在数据库中的表，视图是一个虚表。一个基本表可以跨一个或多个存储文件，一个存储文件也可存放

一个或多个基本表。SQL语言的特点：1.集DDL、DML、DCL于一体SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。2.高度非过程化SQL语言进行数据操作，用户只需提出“做什么”，而

不必指明“怎么做”，用户无需了解存取路径。3.面向集合的操作方式4.一种语法提供两种操作方式交互式操作和嵌入式操作。5.功能强大、语言简洁设计巧妙，语言十分简洁，核心功能只用了9个动词：CREATE、ALTER、DROP、SELECT、

INSERT、UPDATE、DELETE、GRANT、REVOKE。数据定义(DDL)CreatetableDroptableCreateViewDropViewCreateIndexDropIndex数据操纵(DML)

SelectUpdateInsertDelete数据控制(DCL)GrantRevoke数据类型1.字符型（varchar,char)2.文本型(Text)3.数值型整数：(Int,Smallint,Tinyint)小数：Numeric钱数：Money4.

逻辑性(Bit)5.日期型(Datetime,SmallDatetime)第3章结构化查询语言-SQL◆3.1SQL概述3.2SQL语言3.3视图◆3.4SQL中的复杂完整性约束3.5小结设有一

个学生-课程数据库，包括学生关系Student，课程关系Course和选修关系SC。其关系模式分别为：Student(Sno,Sname,Ssex,Sage,Sdept);Course(Cno,Cname,Cpno,Ccredit)SC(Sno,

Cno,Grade)一、数据定义命令1.模式的定义CreateSchema[模式名]Authorization<用户名>；例1：定义一个学生-课程模式2.删除模式DropSchema<模式><Cascade|Restrict

>；例2：删除学生-课程模式3.表结构的建立、删除与修改（1）表结构的建立CreateTable<表名>(<列名><数据类型>[列级完整性约束],<列名><数据类型>[列级完整性约束],…<列名><数据类型>[列级完整性约束],[,表级完整

性约束条件])；例3：建立一个学生表Student例4：建立一个课程表Course例5：建立一个选课表SCCreateTableCourse(CnoChar(4)PrimaryKey,CnameChar(20)

,CpnoChar(4),CcreditSmallint,ForeignKeyCpnoReferencesCourse(Cno));CreateTableSC(SnoChar(9),CnoChar(4),GradeSmallint,PrimaryKey(Sno,Cno),ForeignKey

SnoReferencesStudent(Sno)ForeignKeyCnoReferencesCourse(Cno));（2）表结构的删除DropTable<表名>[Restrict|Cascade]；例

6：删除Student表（3）表结构的修改AlterTable<表名>[Add<新列名><数据类型>[完整性约束]][Drop<完整性约束>][AlterColumn<列名><数据类型>]；例7：向Student表增加“入学时间”列，其数据类型为日期型。例8：将年龄的数据类型

由字符型改为整数例9：增加课程名称必须取唯一值的约束条件4.索引的建立与删除（1）建立索引Create[Unique/Cluster]Index<索引名>On<表名>(<列名1>[Asc/Desc][，

<列名2>[Asc/Desc]…)；例10：为Student、Course和SC3个表建立索引。Student按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC按学号升序和课程号降序建唯一索引。（2）索引删除DropIndex<索引名>;例11:删除Student表

的Stusname索引二、数据操纵命令1.数据查询Select[Distinct|All]<*|目标列表达式>From<表名>[Where<条件表达式>][GroupBy<属性列>[Having<条件表达式>]][

OrderBy<属性列>[Asc|Desc]]；（1）单表查询查询仅涉及一个表：选择表中的若干列选择表中的若干元组ORDERBY子句聚集函数GROUPBY子句查询指定列[例1]查询全体学生的学号与姓名。[例2]查询全体学生的姓名、学号、所在系。选择表中的若干列选出所有属

性列：在SELECT关键字后面列出所有列名将<目标列表达式>指定为*[例3]查询全体学生的详细记录。查询经过计算的值SELECT子句的<目标列表达式>可以为：–算术表达式–字符串常量–函数–列别名[例4]查全体学生的姓名及其出生年份。[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字

母表示所有系名选择表中的若干元组消除取值重复的行如果没有指定DISTINCT关键词，则缺省为ALL指定DISTINCT关键词，去掉表中重复的行[例6]查询选修了课程的学生学号。查询满足条件的元组查询条件谓词比较=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符确定范围BET

WEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件（逻辑运算）AND，OR，NOT常用的查询条件比较大小[例7]查询计算机科学系全体学生的姓名[例8]查询所有年龄在

20岁以下的学生姓名及其年龄[例9]查询考试成绩有不及格的学生的学号。确定范围谓词:BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄[例11]查询年龄不在20~23岁之间的学生姓

名、系别和年龄确定集合谓词：IN<值表>,NOTIN<值表>[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名

和性别。字符匹配谓词：[NOT]LIKE„<匹配串>‟[ESCAPE„<换码字符>‟][例14]查询学号为200215121的学生的详细情况[例15]查询所有姓刘学生的姓名、学号和性别[例16]查询姓"欧阳"且全名

为三个汉字的学生的姓名[例17]查询名字中第2个字为“阳”字的学生的姓名和学号[例18]查询所有不姓刘的学生姓名。[例19]查询DB_Design课程的课程号和学分[例20]查询以“DB_”开头，且倒数第3

个字符为i的课程的详细情况涉及空值的查询谓词：ISNULL或ISNOTNULL“IS”不能用“=”代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号[例22]查询所有有成绩的学生学号和课

程号多重条件查询逻辑运算符：AND和OR来联结多个查询条件•AND的优先级高于OR•可以用括号改变优先级[例23]查询计算机系年龄在20岁以下的学生姓名ORDERBY子句ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空

值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列[例25］查询全体学生情况，查询结果按所在系的系名升序排列，同一系中的学生按年龄降序排列聚集函数–计数COUNT（[DISTINCT|A

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

TINCT|ALL]<列名>）[例26]查询学生总人数[例27]查询选修了课程的学生人数[例28]计算1号课程的学生平均成绩[例29]查询选修1号课程的学生最高分数[例30]查询学生200215012选修课程的总学分数GROUPBY子句[例31]求各个课程号及

相应的选课人数[例32]查询选修了3门以上课程的学生学号HAVING短语与WHERE子句的区别：–作用对象不同–WHERE子句作用于基表或视图，从中选择满足条件的元组–HAVING短语作用于组，从中选择满足条件的组（2）连接查

询连接查询：同时涉及多个表的查询连接条件或连接谓词：用来连接两个表的条件一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名

2>.]<列名3>连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的等值与非等值连接查询等值连接：连接运算符为=[例33]查询每个学生及其选修课程的情况自然连接：[例

34]对[例33]用自然连接完成。自身连接一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀[例35]查询每一门课的间接先修课（即先修课的先修课）FIRST表（Course表）CnoCnameCpnoCcred

it1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构74

6数据处理27PASCAL语言64SECOND表（Course表）复合条件连接复合条件连接：WHERE子句中含多个连接条件[例36]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名[例37]查询每个学生的学号、姓名、选修的课程名及成绩

（3）嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询SELECTSname/*外层查询/父查询*/FROMStudentWHERESnoIN（SE

LECTSno/*内层查询/子查询*/FROMSCWHERECno='2'）；子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代带有IN谓词

的子查询[例38]查询与“刘晨”在同一个系学习的学生此查询要求可以分步来完成①确定“刘晨”所在系名（结果为CS）②查找所有在CS系学习的学生。[例39]查询选修了课程名为“信息系统”的学生学号和姓名带有ANY（SOME）或ALL谓词的子查询谓词语义–AN

Y：任意一个值–ALL：所有值需要配合使用比较运算符>ANY大于子查询结果中的某个值>ALL大于子查询结果中的所有值<ANY小于子查询结果中的某个值<ALL小于子查询结果中的所有值>=ANY大于等于子查询结果中的某个值>=ALL大于等于子查询结果中的所有值<=ANY小于等于子查询结

果中的某个值<=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY不等于子查询结果中的某个值!=（或<>）ALL不等于子查询结果中的任何一个值[例40]查询

其他系中比计算机科学某一学生年龄小的学生姓名和年龄[例41]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。集合查询集合操作的种类–并操作UNION–交操作INTERSECT–差操作EXCEPT参加集合操作的各查询结果的列数必须相同；对应项的数据类型也

必须相同[例42]查询计算机科学系的学生及年龄不大于19岁的学生。[例43]查询选修了课程1或者选修了课程2的学生。[例44]查询计算机科学系的学生与年龄不大于19岁的学生的交集[例45]查询既选修了课程1又选修了课程2的学生[例

46]查询计算机科学系的学生与年龄不大于19岁的学生的差集。2.数据输入InsertInto<表名>[(属性列表)]Values(<常量表>);[例1]将一个新学生元祖（学号200215128：姓名：陈冬；性

别：男；所在系：IS年龄：18）插入到Student表中。[例2]插入一条选课记录（‘200215128‟，‘1‟）3.数据更新Update<表名>Set<属性列>=<表达式>[,<属性列>=<表达式>],…][Where<条件表达式>];

[例1]将学生200215128的年龄改为22[例2]将所有学生的年龄增加1岁4.数据删除DeleteFrom<表名>[Where<条件>]；[例1]删除学号为200215128的学生记录[例2]删除所有学生的选课记录四、数据控制命令授权GRANT<权限列表>ON<表名或视图名>

TO<用户列表|角色列表|PUBLIC>[WITHGRANTOPTION]；允许接受权限者将权限传递给其它用户PrincipleandApplicationofDataBase[例1]把查询Student表权限授给用户U1[

例2]把对Student表和Course表的全部权限授予用户U2和U3[例3]把对表SC的查询权限授予所有用户[例4]把查询Student表和修改学生学号的权限授给用户U4[例5]把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

角色角色是对权限的集中管理。每个角色都有一个给定的名称，它是一组系统权限和对象权限的集合，当把某角色授予某个用户，该用户就会自动获得该角色包括的所有权限。使用角色将使得授予和撤销权限都比较方便，通过对一个角色添加或删除权限，

从而可以改变被授予该角色的用户组的权限。当需要修改用户的权限时，只需对角色进行修改，不必对单个用户进行修改。创建和删除角色CREATEROLE<角色名>；DROPROLE<角色名>；[例6]创建角色role1和role2。[例

7]将Student表的查询、插入权限授予角色role1。[例8]删除角色role2。授权权限的撤销REVOKE<权限列表|角色>ON<表名或视图名>FROM<用户名列表|角色|PUBLIC>[CASCADE|RESTRICT];[例8]撤销U1用户对表Student的查询权限

。[例9]收回所有用户对表SC的查询的权限。[例10]把用户U5对SC表的Insert权限收回。第3章结构化查询语言-SQL◆3.1SQL概述3.2SQL语言3.3视图◆3.4SQL中的复杂完整性约束3.5小结视图的特点:虚表，是从一个或几个基本表（或视图

）导出的表只存放视图的定义，不存放视图对应的数据基表中的数据发生变化，从视图中查询出的数据也随之改变视图的作用1.视图对重构数据库提供了一定程度的逻辑独立性2.视图能够简化用户的操作3.视图能够对机密数据提供安全保护基于视图的操作查询删除受

限更新定义基于该视图的新视图创建视图CREATEVIEW<视图名>[(<列名>[，<列名>]…)]AS<子查询>[WITHCHECKOPTION]；组成视图的属性列名：全部省略或全部指定子查询不允许含有ORDERBY子句和DISTINCT短语RDBMS执行CREATEVIEW语

句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。[例1]建立信息系学生的视图。[例2]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的

学生。[例3]建立信息系选修了1号课程的学生视图。[例4]建立信息系选修了1号课程且成绩在90分以上的学生的视图。[例5]定义一个反映学生出生年份的视图。[例6]将学生的学号及他的平均成绩定义为一个视图。删除视图DROPVIEW<视图名>；该语句

从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除[例8]删除视图BT_S：DROPV

IEWBT_S；查询视图用户角度：查询视图与查询基本表相同RDBMS实现视图查询的方法:视图消解法进行有效性检查转换成等价的对基本表的查询执行修正后的查询[例9]在信息系学生的视图中找出年龄小于20岁的学生。[例10]查询选修了1号课程的信息系学生。[例11]在S_G视图中查询平均成绩在9

0分以上的学生学号和平均成绩更新视图[例12]将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。[例13]向信息系学生视图IS_S中插入一个新的学生记录：200215

129，赵新，20岁。[例14]删除信息系学生视图IS_Student中学号为200215129的记录更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。允许对行列子集视图进行更新对其他类型视图的更新不同系统有不同限

制第3章结构化查询语言-SQL◆3.1SQL概述3.2SQL语言3.3视图◆3.4SQL中的复杂完整性约束3.5小结概念数据的完整性是指数据库中数据的正确性、有效性和一致性，这是数据库理论中的重要概念。正确性是指数据的合法性；有效性是指数据是否在定义的有效范围；一致性是指表示同一个事

实的两个数据应相同。数据完整性包括实体完整性、参照完整性和用户自定义的完整性。SQL完整性主码(PRIMARYKEY)约束外码(FOREIGNKEY)约束非空值(NOTNULL)约束键值唯一(UNIQUE)约束检查(CHEC

K)约束触发器(TRIGGER)约束主码(PRIMARYKEY)约束主码(也称主键)约束是指关系表具有一个特定的最小字段集合，通过主码可以唯一确定每条记录。能唯一确定每条记录的关系的字段集合称为关系的主码(PRIMARYKEY)外码(FOREIGNKEY)约束涉

及两个关系的最普通的完整性约束是外码约束。在SQL中外码约束是通过定义关系表或者修改表时实现的。非空值(NOTNULL)约束非空值约束用来限制关系表中某列的值不能为空值。在SQL中非空值约束是在定义表语句中用NOT

NULL关键字实现的。键值唯一(UNIQUE)约束键值唯一约束规定一列中的每个值或者列的组合必须是唯一的，即在一个关系表中的任何两行在指定的属性列或属性列的组合没有相同的值。检查(CHECK)约束检查约束是根据逻辑表达式来限定值域的

，SQL中对检查约束的实现，是在定义表语句中用CHECK短语完成的。CHECK(条件表达式)；触发器触发器是一系列在表中的数据修改时要执行的SQL语句的集合。是过程化SQL代码。当某个数据操作事件发生时

，DBMS自动调用触发器。触发器中可以包含复杂的Transact-SQL语言，触发整体被看作一个事务，可以回滚。SQLServer2000中有两种类型的触发器：DML触发器和DDL触发器。创建触发器CREATETRIGGERtrigger_name

ON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE],[DELETE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(colum

n)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_oper

ator}column_bitmask[...n]}]sql_statement[...n]}}[例1]定义一个触发器，如果在学生表中插入记录，则提示“欢迎新同学”。CREATETRIGGERwelcome_studentONstudentAFTERINSERTASPRINT'欢迎新同学

！'例2：创建一个名为grade_warn的触发器，当成绩大于100时，就提示‘成绩不能大于100分’。CREATETRIGGERgrade_warnONSCFORINSERT,UPDATEASdeclare@ggintSEL

ECT@gg=gradefrominsertedif@gg>100BEGINPRINT'成绩不能超过100分‘ROLLBACKEND触发器的执行，是由触发事件激活的，并由数据库服务器自动执行一个数据表上可能定义了多个触发器，同一个表上的多个触发器通过存储过程sp

_settriggerorder设置触发次序。激活触发器删除触发器DROPTRIGGER<触发器名>ON<表名>;DropTriggergrade_warnonSC;第3章结构化查询语言-SQL◆3.1SQL概述3.2SQL语言3.3视图◆3.4SQL中的复杂完整性约束3

.5小结数据库原理与应用PrincipleandApplicationofDataBase小结结构化查询语言SQL具有一体化、高度非过程化、语言简洁、易于应用的特点，是目前广泛使用的数据库语言。数据定义(DDL)数据操纵(DML)数据控制(DCL)视图SQL中的完整性约束