数据库技术及应用第4章关系数据库标准语言sql课件.ppt

1、第第4章章 关系数据库标准语言关系数据库标准语言SQL 4.1 SQL概述及特点概述及特点 4.2 SQL的数据定义功能的数据定义功能 4.3 SQL的数据查询功能的数据查询功能 4.4 SQL的数据更新功能的数据更新功能 4.5 嵌入式嵌入式SQL 4.6 SQL的数据控制功能的数据控制功能 习题习题4 4.1 SQL概述及特点概述及特点 SQL是一种介于关系代数与关系演算之间的结构化查询语言，其功能并不仅仅是查询。SQL语言是一个通用的、功能极强的关系数据库语言。1.SQL的主要功能SQL的功能可以分为三类：(1)数据定义功能。(2)数据操纵功能。(3)数据控制功能。2.SQL的特点 SQ

2、L语言集数据查询、数据操纵、数据定义和数据控制功能于一体，它除了具有一般关系数据库语言的特点外，还具有以下三个特点：(1)SQL具有自主式和嵌入式两种形式。(2)SQL具有语言简洁、易学易用的特点。(3)SQL支持三级数据模式结构。表4-1 SQL的命令动词 图4-1 SQL对关系数据库模式的支持SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式4.2 SQL的数据定义功能的数据定义功能 SQL的数据定义包括定义基本表、索引、视图和数据库，其基本语句如表4-2所示。表4-2 SQL的数据定义语句 1.语句格式的约定符号 在语句格式中：尖括号“”实际语义。中括

3、号“”中的内容任选项。大括号“”或用分隔符“|”中的内容必选项，即必选其中之一项。，n表示前面的项可重复多次。2.一般语法规定 SQL中的数据项(包括列项、表和视图)分隔符为“，”，其字符串常数的定界符用单引号“”表示。3.SQL的特殊语法规定 SQL的关键词一般使用大写字母表示。SQL语句的结束符为“；”。SQL一般应采用格式化书写方式。4.2.1 基本表的定义和维护功能 SQL的基本表定义和维护功能使用基本表的定义、修改和删除三种语句实现。1.定义基本表 SQL语言使用CREATE TABLE语句定义基本表，定义基本表语句的一般格式为 CREATE TABLE 库名表名(列名数据类型列级完

4、整性约束条件 ，列名数据类型列级完整性约束条件，n ，表级完整性约束条件，n)；(1)SQL支持的数据类型。不同的数据库系统支持的数据类型不完全相同。IBM DB2 SQL支持的数据类型由表4-3列出，其中，使用最多的是字符型数据和数值型数据。表4-3 IBM DB2 SQL支持的主要数据类型 (2)列级完整性的约束条件。列级完整性约束是针对属性值设置的限制条件。SQL的列级完整性条件有以下几种：NOT NULL或NULL约束。UNIQUE约束。DEFAULT约束。CHECK约束。(3)表级完整性约束条件。表级完整性约束条件是指涉及到关系中多个列的限制条件。在上述的CHECK约束中，如果约束条

5、件表达式中涉及到多列数据，它便为表级约束。【例4-1】用SQL建立学生_课程库中的基本表，其表结构为 学生(学号，姓名，年龄，性别，所在系)课程(课程号，课程名，先行课)选课(学号，课程号，成绩)2.修改基本表 当已建立好的基本表随着应用环境和应用需求的变化而需要修改时，需要利用SQL的修改基本表语句修改表结构。SQL语言用ALTER TABLE语句来修改基本表，其一般格式为 ALTER TABLE 表名 ADD(新列名 数据类型 完整性约束，n)DROP 完整性约束名 MODIFY(列名 数据类型，n)；(1)使用ADD子句增加新列。当需要向表中增加新列和新的完整性约束时，使用ADD字句对表

6、结构进行修改操作。【例4-2】向课程表中增加“学时”字段。ALTER TABLE 课程 ADD 学时 SMALLINT；(2)使用MODIFY子句修改列的原定义。(3)使用DROP子句删除指定的完整性约束条件。【例4-3】删除学生表中对“年龄”的默认值的定义。3.删除基本表 当不再需要某个基本表时，使用DROP TABLE语句删除它，其一般格式为 DROP TABLE 表名；基本表一旦被删除，表中的数据及在此表基础上建立的索引、视图将自动地全部被删除掉。因此，执行删除基本表的操作时一定要格外小心。4.2.2 索引的定义和维护功能 1.索引的作用 使用索引有以下三方面的作用：(1)可以明显地加快

7、数据查询的速度。(2)可保证数据的惟一性。(3)可以加快连接速度。2.建立索引的原则 建立索引是加快数据查询的有效手段，在建立索引时，用户应当依照以下原则：(1)索引的建立和维护由DBA和DBMS完成。(2)大表应当建索引，小表则不必建索引。(3)对于一个基本表，不要建立过多的索引。(4)根据查询要求建索引。3.建立索引的格式 在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为 CREATE UNIQUE CLUSTER INDEX 索引名 ON 表名(列名次序，列名次序)；【例4-4】为学生课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按“学号”升序建立索引；

8、课程表按“课程号”升序建立惟一索引；选课表按“学号”升序和“课程号”降序建立惟一索引。CREATE UNIQUE INDEX 学号ON 学生(学号)；CREATE UNIQUE INDEX 课程号 ON 课程(课程号)；CREATE UNIQUE INDEX 选课号 ON 选课(学号 ASC，课程号 DESC)；4.删除索引 索引一经建立，就由系统来选择和维护，无须用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统就会花费大量的时间来维护索引，这样就得不偿失了，因此，有时需要删除一些不必要的索引。SQL语言使用DROP INDEX语句删除索引，其一般格式为 DROP I

9、NDEX 索引名；【例4-5】删除学生表的学生姓名索引。DROP INDEX 学生姓名；删除索引时，系统会同时从数据字典中删去有关对该索引的描述。4.2.3 视图的定义和维护功能 视图是根据子模式设计的关系，它是由一个或几个基本表(或已定义的视图)导出的虚表。1.视图的优点 合理地使用视图能够对系统的设计和用户的使用带来很多方便。(1)视图能够简化用户的操作。(2)视图机制可以使用户以不同的方式看待同一数据。(3)视图对数据库的重构提供了一定程度的逻辑独立性。(4)视图可以对机密的数据提供安全保护。2.视图定义的格式SQL语言用CREATE VIEW 来定义视图，其一般格式为 CREATE V

10、IEW 视图名(列名组)AS 子查询 WITH CHECK OPTION；【例4-6】建立计算机系学生的视图。CREATE VIEW 计算机系学生 AS SELECT 学号，姓名，年龄 FROM 学生 WHERE 所在系=计算机系；例4-6中的视图是由一个基本表构造出的。【例4-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩。【例4-8】将学生的学号、总成绩、平均成绩定义成一个视图。3.视图的删除 视图删除语句的一般格式为 DROP VIEW 视图名；视图删除后，视图的定义将从数据字典中删除，而由该视图导出的其他视图的定义却仍存在于数据字典中，

11、但这些视图已失效。4.视图的查询和维护 视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。4.3 SQL的数据查询功能的数据查询功能 4.3.1 SELECT语句介绍 SQL的数据查询语句中包括SELECT、FROM、WHERE、GROUP BY和ORDER BY子句。SELECT语句具有数据查询、统计、分组和排序的功能，其语句表达能力非常强大。1.SELECT语句的语法 SELECT语句的语法格式为 SELECT 目标列组 FROM 数据源 WHERE 元组选择条件 GROUP BY 分列组 HAVING组选择条件 ORDER BY 排序

12、列1 排序要求1，n；(1)SELECT子句。(2)FROM子句。(3)WHERE子句。(4)GROUP BY子句。(5)ORDER BY子句。2.SELECT语句的操作符 (1)算术操作符。算术操作符在SQL语句中表达数学运算操作。SQL的数学运算操作符只有四种，它们是：+(加号)、(减号)、*(乘号)和/(除号)。(2)比较操作符。比较操作符用于测试两个数据是否相等、不等、小于或大于某个值。SQL中的比较操作符包括：=(等于)、(大于)、(小于)、=(大于等于)、!=或(不等于)、!(不大于)和!、=、=和!=。当连接运算符为“=”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算

13、称为非等值连接。当等值连接中的连接字段相同，并且在SELECT子句中去除了重复字段时，则该连接操作为自然连接。【例4-15】查询每个学生的情况以及他(她)所选修的课程。【例4-16】求学生的学号、姓名、选修的课程名及成绩。【例4-17】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。(2)自连接操作。连接操作不只是在两个表之间进行，一个表内还可以进行自身连接操作。表自身的连接操作称为自连接。【例4-18】查询每一门课的间接先行课(即先行课的先行课)。表4-5 课程表中的数据 表4-6 课程表自连接操作 (3)外部连接操作。在前面连接示例的结果集中只保留了符合连接条件的元组，而排除了两个

14、表中没有对应的或匹配的元组情况，这种连接称为内连接。如果要求查询结果集中保留非匹配的元组，就要执行外部连接操作。表4-7 职工和部门表数据 【例4-19】用SQL表达职工和部门的内连接、左外部连接和右外部连接的语句分别如下：内连接：SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门=部门.部门号；左外部连接：SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门*=部门.部门号；右外部连接：SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门=*部门.部门号；表4-8 职工和部门表各种连

15、接的结果集对照表 3.嵌套查询 在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。(1)使用IN操作符的嵌套查询。当IN操作符后的数据集需要通过查询得到时，就需要使用IN嵌套查询。【例4-20】求选修了高等数学的学生学号和姓名。(2)使用比较符的嵌套查询。IN操作符用于一个值与多值比较，而比较符则用于一个值与另一个值之间的比较。当比较符后面的值需要通过查询才能得到时，就需要使用比较符嵌套查询。【例4-21】求C1课程的成绩高于张三的学生学号和成绩。(3)使用ANY或ALL操作符的嵌套

16、查询。表4-9 ANY和ALL与比较符结合的操作符及其语意表 【例4-22】求其他系中比计算机系某一学生年龄小的学生(即求年龄小于计算机系年龄最大者的学生)。【例4-23】求其他系中比计算机系学生年龄都小的学生。(4)使用EXISTS操作符的嵌套查询。【例4-24】求选修了C2课程的学生姓名。【例4-25】求没有选修C2课程的学生姓名。【例4-26】查询选修了全部课程的学生的姓名。【例4-27】求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。4.组合查询 将SELECT语句的查询结果集再进行集合运算就构成了SQL的组合查询。SQL的组合查询操作符有UNION(并操作)、INT

17、ERSECT(交操作)和MINUS(差操作)三种。【例4-28】求选修了C1课程或选修了C2课程的学生学号。【例4-29】求选修C1课程，并且也选修C2课程的学生学号。【例4-30】求选修了C1课程但没有选修C2课程的学生学号。表4-10 基本的SQL函数 5.使用分组和SQL函数查询 SQL函数是综合信息的统计函数，包括计数、求最大值、求最小值、求平均值、求和值等。SQL函数可作为列标识符出现在SELECT子句的目标列或HAVING子句的条件中。【例4-31】求学生的总人数。【例4-32】求选修了课程的学生人数。【例4-33】求课程和选修该课程的人数。【例4-34】求选修课超过三门课的学生学

18、号。4.4 SQL的数据更新功能的数据更新功能 4.4.1 SQL的数据插入功能 SQL的数据插入语句有两种使用形式：一种是使用常量，一次插入一个元组；另一种是插入子查询的结果，一次插入多个元组。1.使用常量插入单个元组 使用常量插入单个元组的INSERT语句的格式为 INSERT INTO 表名(属性列1，属性列2)VALUES(常量1，常量2)；【例4-35】将一个新学生记录(学号：03010，姓名：张三，年龄：20，所在系：计算机系)插入到学生表中。【例4-36】插入一条选课记录(学号：03011，课程号：C10，成绩不详)。2.在表中插入子查询的结果集 如果插入的数据需要查询才能得到，

19、就需要使用插入子查询结果集的INSERT语句。SQL允许将查询语句嵌到数据插入语句中，以便将查询得到的结果集作为批量数据输入到表中。含有子查询的INSERT语句的格式为 INSERT INTO 表名(属性列1，属性列2)子查询；【例4-37】求每个系学生的平均年龄，并把结果存入数据库中。4.4.2 SQL的数据修改功能 SQL修改数据操作语句的一般格式为 UPDATE 表名 S E T 列 名 =表 达 式 ，列 名 =表 达式，n WHERE 条件；【例4-38】将学生表中全部学生的年龄加上2岁。【例4-39】将选课表中的数据库课程的成绩乘以1.2。4.4.3 SQL的数据删除功能 数据删除

20、语句的一般格式为 DELETE FROM 表名 WHERE 条件；DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。【例4-40】删除管理系的学生记录及选课记录。4.5 嵌嵌 入入 式式 SQL 4.5.1 嵌入式SQL的特点 SQL的功能只包括数据定义功能DDL、数据操纵功能DML和数据控制功能DCL，而缺少程序设计必要的程序流程控制和交互式功能，也缺少一些专业应用的功能，例如SQL没有分支、循环、赋值等语句等。1.区别SQL和主语言 在嵌入式SQL中，为了能够区分SQL语句与主语言语句，必须在所有的SQL语句前面加上前缀EXEC SQL。SQL语句的结束标志则随主语言

21、的不同而不同。2.使数据库的工作单元与程序工作单元之间能够通信 在含有嵌入式SQL的应用程序中，SQL语句负责管理数据库，主语言语句负责控制程序流程和其他功能。数据库的工作单元和程序工作单元之间通信的主要方式有以下两种：(1)主语言通过主变量向SQL语句提供参数。(2)SQL语句的当前工作状态和运行环境数据要反馈给应用程序。3.使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾 SQL语言与主语言具有不同的数据处理方式。4.5.2 不用游标的SQL语句 1.几种不需要使用游标的SQL语句 下面四种SQL语句不需要使用游标：(1)用于说明主变量的说明性语句。(2)数据定义和数据控制语

22、句。(3)查询结果为单记录的查询语句。(4)数据的插入语句和某些数据删除、修改语句。2.不用游标的查询语句 不用游标的查询语句的一般格式为 EXEC SQL SELECT ALL|DISTINCT 目标列表达式，n INTO 主变量 指示变量，n FROM 表名或视图名，n WHERE 条件表达式；【例4-41】查询学号为主变量givesno的值、课号为主变量givecno的值的学生选课记录。3.不用游标的数据维护语句 (1)不用游标的数据删除语句。在删除语句中，WHERE子句的条件中可以使用主变量。【例4-42】删除学号由主变量Sno决定的学生记录。【例4-43】将计算机系所有学生的年龄都加

23、上主变量Raise。【例4-44】将计算机系学生的年龄置空。(3)不用游标的数据插入语句。INSERT语句的VALUES子句可以使用主变量和指示变量，当需要插入空值时，可以把指示变量置为负值。【例4-45】将学号由主变量Sno、课程号由Cno决定的选课记录插入到数据库中。4.5.3 使用游标的SQL 游标机制用于解决SQL查询结果为集合而主语言处理方式为记录方式的矛盾。在处理中，必须使用游标的SQL语句有两种：一种是查询结果为多条记录的SELECT语句；另一种是使用游标的DELETE语句和UPDATE语句。1.定义游标命令 游标通过DECLARE语句定义，其语句格式为 EXEC SQL DEC

24、LARE 游标名 CURSOR FOR 子查询 FOR UPDATE OF 字段名1，n；【例4-46】定义按主变量DEPT查询系里学生的游标。2.打开游标命令 游标通过OPEN命令打开，打开游标语句的格式为 EXEC SQL OPEN 游标名；OPEN语句的作用是执行游标对应的查询语句，并将游标指向结果集的第一条记录前。【例4-47】打开SX游标。3.推进游标命令 游标通过FETCH命令向前(或称向下)推进一条记录。推进游标的语句格式为 EXEC SQL FETCH 游标名 INTO 主变量组；推进游标的作用是将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。

25、【例4-48】将打开的指向系的游标向前推进。4.关闭游标命令 由于许多系统允许打开的游标数有一定的限制，所以当数据处理完后应及时把不使用的游标关闭，以释放结果集占用的缓冲区及其他资源。关闭游标使用CLOSE命令，CLOSE命令的具体格式为 EXEC SQL CLOSE 游标名；4.6 SQL的数据控制功能的数据控制功能 4.6.1 数据控制的方法 数据库系统通过以下三步来实现数据控制。1.授权定义 具有授权资格的用户，如数据库管理员DBA或建表户DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。2.存权处理 数据库管理系统DBMS把授权的结果编译后存入数据字典中。3.查权操作 当用

26、户提出操作请求时，系统首先要在数据字典中查找该用户的数据操作权限，当用户拥有该操作权时才能执行其操作，否则系统将拒绝其操作。4.6.2 SQL Server的数据库操作权限 1.隐含特权 隐含特权是系统内置权限，是用户不需要进行授权就可拥有的数据操作权。2.系统特权 系统特权又称为语句特权，它相当于数据定义语句DLL的语句权限。不同的数据库系统规定的系统权限不同，SQL Server中的系统特权如表4-11所示。表4-11 SQL Server中的系统特权 3.对象特权 对象特权类似于数据库操作语言DML的语句权限，它指用户对数据库中的表、视图、存储过程等对象的操作权限。SQL Server中

27、的对象特权如表4-12所示。表4-12 SQL Server中的对象特权 4.6.3 数据操作权限的设置 数据操作权限的设置语句包括授权语句、收权语句和拒绝访问语句三种。1.授权语句 系统授权的授权语句格式为 GRANT 系统特权组 To 用户组|PUBLIC WITH GRANT OPTION；【例4-49】把修改学生学号和查询学生表的权力授予用户王平。【例4-50】把建立数据库和备份数据库的权力赋给用户王平。2.收权语句 数据库管理员DBA、数据库拥有者(建库户)DBO或数据库对象拥有者DBOO(数据库对象主要是基本表)可以通过REVOKE语句将其他用户的数据操作权收回。REVOKE语句的

28、一般格式为 REVOKE 权限组|ALL PRIVILIGES ON 对象名 FROM 用户名组|PUBLIC；【例4-51】将用户王平的可以在学生表中修改学生学号的权利收回。3.拒绝访问语句 拒绝访问语句的一般格式为 DENY ALL PRIVILIGES|权限组 ON 对象名 TO 用户组|PUBLIC；其中，ON子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。习习 题题 4 4.1 试述SQL语言的特点。4.2 在嵌入式SQL中，如何区分SQL语句和主语言语句？4.3 SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句INSERT、DE

29、LETE、UPDATE实现_功能。A.数据查询 B.数据操纵 C.数据定义 D.数据控制 4.4 下面列出的关于视图(VIEW)的条目中，不正确的是_。A.视图是外模式 B.视图是虚表 C.使用视图可以加快查询语句的执行速度 D.使用视图可以简化查询语句的编写 4.5 在SQL语言的SELECT语句中，实现投影操作的是_子句。A.SELECT B.FROM C.WHERE D.GROUP BY 4.6 SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句ALTER TABLE实现_功能。A.数据查询 B.数据操纵 C.数据定义 D.数据控制 4.7 在关系数据库系统中，为了简化用

30、户的查询操作，而又不增加数据的存储空间，常用的方法是创建_。A.另一个表 B.游标 C.视图 D.索引4.8 设职工_社团数据库有三个基本表：职工(职工号，姓名，年龄，性别)社会团体(编号，名称，负责人，活动地点)参加(职工号，编号，参加日期)其中：职工表的主码为“职工号”。社会团体表的主码为“编号”；外码为“负责人”，被参照表为职工表，对应属性为“职工号”。参加表的“职工号”和“编号”为主码；“职工号”为外码，其被参照表为职工表，对应属性为“职工号”；“编号”为外码，其被参照表为社会团体表，对应属性为“编号”。试用SQL语句表达下列操作：(1)定义职工表、社会团体表和参加表，并说明其主码和参

31、照关系。(2)建立下列两个视图：社团负责人(编号，名称，负责人职工号，负责人姓名，负责人性别)参加人情况(职工号，姓名，社团编号，社团名称，参加日期)(3)查找参加唱歌队或篮球队的职工号和姓名。(4)查找没有参加任何社会团体的职工情况。(5)查找参加了全部社会团体的职工情况。(6)查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。(7)求每个社会团体的参加人数。(8)求参加人数最多的社会团体的名称和参加人数。(9)求参加人数超过100人的社会团体的名称和负责人。(10)把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。4.9

32、设工程_零件数据库中有四个基本表：供应商(供应商代码，姓名，所在城市，联系电话)工程(工程代码，工程名，负责人，预算)零件(零件代码，零件名，规格，产地，颜色)供应零件(供应商代码，工程代码，零件代码，数量)试用SQL语句完成下列操作：(1)找出天津市供应商的姓名和电话。(2)查找预算在50 000100 000元之间的工程的信息，并将结果按预算降序排列。(3)找出使用供应商S1所供零件的工程号码。(4)找出工程项目J2使用的各种零件名称及其数量。(5)找出上海厂商供应的所有零件号码。(6)找出使用上海产的零件的工程名称。(7)找出没有使用天津产零件的工程号码。(8)把全部红色零件的颜色改成蓝色。(9)将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并作其他必要的修改。(10)从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。4.10 关系数据模型如下：学生 S(SNO，SN，SEX，AGE)课程 C(CNO，CN，PCNO)PCNO为直接先行课号 选课 SC(SNO，CNO，GR)GR为课程考试成绩 试用SQL写出查询程序：(1)将选修课程“DB”的学生学号SNO、姓名SN建立视图SDB。(2)查询选修课程“DB”的学生姓名SN。