数据库技术-第3章+结构化查询语言SQL(数据库技术)-课件.ppt

1、第3章 结构化查询语言-SQL 3.1 SQL概述u 3.2 SQL语言u 3.3 视图 3.4 SQL中的复杂完整性约束u 3.5 小结 结构化查询语言SQL(Structured Query Language)是一种面向集合的数据库查询语言。SQL语言是集数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definition）和数据控制（Data Control）于一体的一种非过程化语言。数据DBMSClient请求请求回答回答SQL 语言语言Oracle 8i/9i/10g/11gSQL Server 2000、2005、2008、20

2、12第3章 结构化查询语言-SQL 3.1 SQL概述u 3.2 SQL语言u 3.3 视图 3.4 SQL中的复杂完整性约束u 3.5 小结 SQL最早的版本是IBM开发的。是20世纪70年代早期作为Systems R项目的一部分实现的。现在有许多产品支持SQL语言，它已经很明确地确立了自己作为标准关系数据库语言的地位。1986年，美国国家标准化组织（ANSI）和国家标准化组织（ISO）发布了SQL标准：SQL-86；1989年，发布了SQL-89；1992年，发布了SQL-92；1999年，发布了SQL-99；2003年，发布了SQL-2003;2006年，发布了SQL-2006;SQL语

3、言有以下几部分：数据定义语言；数据操作语言；完整性；视图定义：嵌入式SQL和动态SQL:授权SQL视图视图1视图视图2基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2内模式外模式模 式SQL数据库体系结构（三级模式）数据库体系结构（三级模式）外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。特征如下：l 一个SQL模式(Schema)是表和约束的集合。l 一个表(Table)是行(Row)的集合，每行是列(Column)的序列，每列对应一个数据项。l 个表可以是一个基本表，也可以是一个视图，基本表是实际存储在数据库中的表，视图是一

4、个虚表。l 一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表 。SQL语言的特点：1.集DDL、DML、DCL于一体 SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。2.高度非过程化 SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，用户无需了解存取路径。3.面向集合的操作方式 4.一种语法提供两种操作方式 交互式操作和嵌入式操作。5.功能强大、语言简洁 设计巧妙，语言十分简洁，核心功能只用了9个动词：CREATE、ALTER、DROP、SELECT、INSER

5、T、UPDATE、DELETE、GRANT、REVOKE。数据定义(DDL)Create table Drop table Create View Drop View Create Index Drop Index 数据操纵(DML)Select Update Insert Delete数据控制(DCL)Grant Revokel数据类型 1.字符型（varchar,char)2.文本型(Text)3.数值型 整数：(Int,Smallint,Tinyint)小数：Numeric 钱数：Money 4.逻辑性(Bit)5.日期型(Datetime,SmallDatetime)第3章 结构化查询

6、语言-SQL 3.1 SQL概述u 3.2 SQL语言u 3.3 视图 3.4 SQL中的复杂完整性约束u 3.5 小结设有一个学生-课程数据库，包括学生关系Student，课程关系Course和选修关系SC。其关系模式分别为：Student(Sno,Sname,Ssex,Sage,Sdept);Course(Cno,Cname,Cpno,Ccredit)SC(Sno,Cno,Grade)一、数据定义命令1.模式的定义 Create Schema 模式名 Authorization；例1：定义一个学生-课程模式2.删除模式Drop Schema ；例2：删除学生-课程模式3.表结构的建立、删除

7、与修改（1）表结构的建立Create Table(列级完整性约束,列级完整性约束,列级完整性约束,表级完整性约束条件)；例3：建立一个学生表Student例4：建立一个课程表Course例5：建立一个选课表SCCreate Table Course (Cno Char(4)Primary Key,Cname Char(20),Cpno Char(4),Ccredit Smallint,Foreign Key Cpno References Course(Cno);Create Table SC (Sno Char(9),Cno Char(4),Grade Smallint,Primary Ke

8、y(Sno,Cno),Foreign Key Sno References Student(Sno)Foreign Key Cno References Course(Cno);（2）表结构的删除 Drop Table Restrict|Cascade；例6：删除Student表（3）表结构的修改Alter TableAdd 完整性约束Drop Alter Column；例7：向Student表增加“入学时间”列，其数据类型为日期型。例8：将年龄的数据类型由字符型改为整数例9：增加课程名称必须取唯一值的约束条件4.索引的建立与删除（1）建立索引 Create Unique/Cluster In

9、dex On (Asc/Desc ，Asc/Desc)；例10：为Student、Course和SC 3个表建立索引。Student按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC按学号升序和课程号降序建唯一索引。（2）索引删除Drop Index;例11:删除Student表的Stusname索引二、数据操纵命令二、数据操纵命令 1.数据查询数据查询 Select Distinct|All From Where Group By Having Order By Asc|Desc；（1）单表查询）单表查询 查询仅涉及一个表：l选择表中的若干列l选择表中的若干元组lORDER B

10、Y子句l聚集函数lGROUP BY子句l查询指定列例1 查询全体学生的学号与姓名。例2 查询全体学生的姓名、学号、所在系。选择表中的若干列l选出所有属性列：n在SELECT关键字后面列出所有列名 n将指定为*例3 查询全体学生的详细记录。l查询经过计算的值 SELECT子句的可以为：算术表达式 字符串常量 函数 列别名 例4 查全体学生的姓名及其出生年份。例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名选择表中的若干元组l 消除取值重复的行 如果没有指定DISTINCT关键词，则缺省为ALL 指定DISTINCT关键词，去掉表中重复的行 例6 查询选修了课程的学生学号。l

11、查询满足条件的元组查 询 条 件谓 词比 较=，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件（逻辑运算）AND，OR，NOT常用的查询条件比较大小例7 查询计算机科学系全体学生的姓名例8 查询所有年龄在20岁以下的学生姓名及其年龄 例9 查询考试成绩有不及格的学生的学号。确定范围谓词谓词:BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄例

12、11 查询年龄不在2023岁之间的学生姓名、系别和年龄确定集合谓词：谓词：IN,NOT IN 例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。字符匹配谓词：谓词：NOT LIKE ESCAPE 例14 查询学号为200215121的学生的详细情况例15 查询所有姓刘学生的姓名、学号和性别例16 查询姓欧阳且全名为三个汉字的学生的姓名 例17 查询名字中第2个字为“阳”字的学生的姓名和学号例18 查询所有不姓刘的学生姓名。例19 查询DB_Design课程的课程号和学分例20 查询以“DB_”开

13、头，且倒数第3个字符为i的课程的详细情况 涉及空值的查询n谓词：谓词：IS NULL 或 IS NOT NULLn“IS”不能用“=”代替例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号例22 查询所有有成绩的学生学号和课程号 多重条件查询l 逻辑运算符：AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级例23 查询计算机系年龄在20岁以下的学生姓名ORDER BY子句 ORDER BY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后

14、显示nDESC：排序列为空值的元组最先显示 例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列例25 查询全体学生情况，查询结果按所在系的系名升序排列，同一系中的学生按年龄降序排列 聚集函数聚集函数 计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）最大最小值 MAX（DISTINCT|ALL）MIN（DISTINCT|ALL）例26 查询学生总人数例27 查询选修了课程的学生人数例28 计算1号课程的学生平均成绩例29 查询选修1号课程的学生最高分数 例3

15、0查询学生200215012选修课程的总学分数 GROUP BY子句 例31 求各个课程号及相应的选课人数例32 查询选修了3门以上课程的学生学号HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组（2）连接查询 连接查询：同时涉及多个表的查询连接条件或连接谓词：用来连接两个表的条件 一般格式：.BETWEEN.AND.连接字段：连接谓词中的列名称 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的等值与非等值连接查询等值与非等值连接查询 l 等值连接：等值连接：连接运算符为连接运算符为

16、=例33 查询每个学生及其选修课程的情况l 自然连接：自然连接：例34 对例33用自然连接完成。自身连接自身连接 一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀例35查询每一门课的间接先修课（即先修课的先修课）FIRST表（Course表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信

17、息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4 SECOND表（Course表）复合条件连接复合条件连接复合条件连接：WHERE子句中含多个连接条件例36查询选修2号课程且成绩在90分以上的所有学生的学号和姓名例37查询每个学生的学号、姓名、选修的课程名及成绩（3）嵌套查询一个SELECT-FROM-WHERE语句称为一个查查询块询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套嵌套查询查询 SELECT Sname /*外层查询/父查询*/FROM Student WHER

18、E Sno IN （SELECT Sno /*内层查询/子查询*/FROM SC WHERE Cno=2）；子查询的限制不能使用ORDER BY子句层层嵌套方式反映了 SQL语言的结构化有些嵌套查询可以用连接运算替代带有IN谓词的子查询例38 查询与“刘晨”在同一个系学习的学生 此查询要求可以分步来完成 确定“刘晨”所在系名（结果为CS）查找所有在CS系学习的学生。例39查询选修了课程名为“信息系统”的学生学号和姓名 带有ANY（SOME）或ALL谓词的子查询 谓词语义ANY：任意一个值ALL：所有值需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 ANY

19、小于子查询结果中的某个值 =ANY大于等于子查询结果中的某个值 =ALL大于等于子查询结果中的所有值=ANY小于等于子查询结果中的某个值 =ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值 =ALL等于子查询结果中的所有值（通常没有实际意义）!=（或）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值例40 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄例41 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。集合查询集合操作的种类 并操作UNION 交操作INTERSECT 差操作EXCEPT参加集合操作的各查询结果的列数必须相

20、同；对应项的数据类型也必须相同 例42 查询计算机科学系的学生及年龄不大于19岁的学生。例43 查询选修了课程1或者选修了课程2的学生。例44 查询计算机科学系的学生与年龄不大于19岁的学生的交集例45 查询既选修了课程1又选修了课程2的学生例46 查询计算机科学系的学生与年龄不大于19岁的学生的差集。2.数据输入 Insert Into(属性列表)Values();例1将一个新学生元祖（学号200215128：姓名：陈冬；性别：男；所在系：IS 年龄：18）插入到Student表中。例2插入一条选课记录（200215128，1）3.数据更新 Update Set=,=,Where;例1将学生

21、200215128的年龄改为22例2将所有学生的年龄增加1岁4.数据删除 Delete From Where；例1 删除学号为 200215128的学生记录例2 删除所有学生的选课记录四、数据控制命令四、数据控制命令授权授权GRANT ON TO WITH GRANT OPTION；允许接受权限允许接受权限者将权限传递者将权限传递给其它用户给其它用户 Principle and Application of DataBase 例1 把查询Student表权限授给用户U1例2 把对Student表和Course表的全部权限授予用户U2和U3例3 把对表SC的查询权限授予所有用户例4 把查询Stu

22、dent表和修改学生学号的权限授给用户U4例5 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户角色角色 角色是对权限的集中管理。每个角色都有一个给定的名称，它是一组系统权限和对象权限的集合，当把某角色授予某个用户，该用户就会自动获得该角色包括的所有权限。使用角色将使得授予和撤销权限都比较方便，通过对一个角色添加或删除权限，从而可以改变被授予该角色的用户组的权限。当需要修改用户的权限时，只需对角色进行修改，不必对单个用户进行修改。创建和删除角色CREATE ROLE；DROP ROLE；例6创建角色role1和role2。例7将Student表的查询、插入权限授予角色r

23、ole1。例8删除角色role2。授权权限的撤销REVOKE ON FROM CASCADE|RESTRICT;例8撤销U1用户对表Student的查询权限。例9收回所有用户对表SC的查询的权限。例10把用户U5对SC表的Insert权限收回。第3章 结构化查询语言-SQL 3.1 SQL概述u 3.2 SQL语言u 3.3 视图 3.4 SQL中的复杂完整性约束u 3.5 小结视图的特点:虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不存放视图对应的数据基表中的数据发生变化，从视图中查询出的数据也随之改变视图的作用1.视图对重构数据库提供了一定程度的逻辑独立性 2.视图能够简

24、化用户的操作3.视图能够对机密数据提供安全保护基于视图的操作查询删除受限更新定义基于该视图的新视图创建视图CREATE VIEW (，)AS WITH CHECK OPTION；u组成视图的属性列名：全部省略或全部指定u子查询不允许含有ORDER BY子句和DISTINCT短语uRDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。u在对视图查询时，按视图的定义从基本表中将数据查出。例1 建立信息系学生的视图。例2建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。例3 建立信息系选修了1号课程的学生视图。例4 建立信

25、息系选修了1号课程且成绩在90分以上的学生的视图。例5 定义一个反映学生出生年份的视图。例6 将学生的学号及他的平均成绩定义为一个视图。删除视图DROP VIEW ；u该语句从数据字典中删除指定的视图定义u如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 u删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 例8删除视图BT_S：DROP VIEW BT_S；查询视图u用户角度：查询视图与查询基本表相同u RDBMS实现视图查询的方法:视图消解法 进行有效性检查 转换成等价的对基本表的查询 执行修正后的查询例9 在

26、信息系学生的视图中找出年龄小于20岁的学生。例10 查询选修了1号课程的信息系学生。例11在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩 更新视图例12 将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。例13 向信息系学生视图IS_S中插入一个新的学生记录：200215129，赵新，20岁。例14删除信息系学生视图IS_Student中学号为200215129的记录u更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。u允许对行列子集视图进行更新u对其他类型视图的更新不同系统有不同限制第3章 结构

27、化查询语言-SQL 3.1 SQL概述u 3.2 SQL语言u 3.3 视图 3.4 SQL中的复杂完整性约束u 3.5 小结 概念 数据的完整性是指数据库中数据的正确性、有效性和一致性，这是数据库理论中的重要概念。正确性是指数据的合法性；有效性是指数据是否在定义的有效范围；一致性是指表示同一个事实的两个数据应相同。数据完整性包括实体完整性、参照完整性和用户自定义的完整性。SQL完整性完整性主码(PRIMARY KEY)约束外码(FOREIGN KEY)约束非空值(NOT NULL)约束键值唯一(UNIQUE)约束检查(CHECK)约束触发器(TRIGGER)约束主码(PRIMARY KEY)

28、约束 主码(也称主键)约束是指关系表具有一个特定的最小字段集合，通过主码可以唯一确定每条记录。能唯一确定每条记录的关系的字段集合称为关系的主码(PRIMARY KEY)外码(FOREIGN KEY)约束 涉及两个关系的最普通的完整性约束是外码约束。在SQL中外码约束是通过定义关系表或者修改表时实现的。非空值(NOT NULL)约束 非空值约束用来限制关系表中某列的值不能为空值。在SQL中非空值约束是在定义表语句中用NOT NULL关键字实现的。键值唯一(UNIQUE)约束 键值唯一约束规定一列中的每个值或者列的组合必须是唯一的，即在一个关系表中的任何两行在指定的属性列或属性列的组合没有相同的值

29、。检查(CHECK)约束 检查约束是根据逻辑表达式来限定值域的，SQL中对检查约束的实现，是在定义表语句中用CHECK短语完成的。CHECK(条件表达式)；触发器 触发器是一系列在表中的数据修改时要执行的SQL语句的集合。是过程化SQL代码。当某个数据操作事件发生时，DBMS自动调用触发器。触发器中可以包含复杂的Transact-SQL语言，触发整体被看作一个事务，可以回滚。SQL Server 2000中有两种类型的触发器：DML触发器和DDL触发器。创建触发器 CREATE TRIGGER trigger_nameON table|view WITH ENCRYPTION FOR|AFTE

30、R|INSTEAD OF INSERT ,UPDATE ,DELETE WITH APPEND NOT FOR REPLICATION AS IF UPDATE(column)AND|OR UPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask)comparison_operator column_bitmask .n sql_statement .n 例1定义一个触发器，如果在学生表中插入记录，则提示“欢迎新同学”。CREATE TRIGGER welcome_studentON studentAFTER I

31、NSERTAS PRINT 欢迎新同学！例2：创建一个名为 grade_warn 的触发器，当成绩大于100 时，就提示成绩不能大于100分。CREATE TRIGGER grade_warnON SCFOR INSERT,UPDATE AS declare gg intSELECT gg=grade from insertedif gg100 BEGIN PRINT 成绩不能超过100分 ROLLBACKEND触发器的执行，是由触发事件激活的，并由数据库服务器自动执行一个数据表上可能定义了多个触发器，同一个表上的多个触发器通过存储过程sp_settriggerorder设置触发次序。激活触发器删除触发器 DROP TRIGGER ON;Drop Trigger grade_warn on SC;第3章 结构化查询语言-SQL 3.1 SQL概述u 3.2 SQL语言u 3.3 视图 3.4 SQL中的复杂完整性约束u 3.5 小结数据库原理与应用数据库原理与应用Principle and Application of DataBase 小结小结 结构化查询语言SQL具有一体化、高度非过程化、语言简洁、易于应用的特点，是目前广泛使用的数据库语言。数据定义(DDL)数据操纵(DML)数据控制(DCL)视图 SQL中的完整性约束