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

,第二级,第三级,第四级,第五级,第,4,章,关系数据库标准语言,SQL,第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的特点,SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，它除了具有一般关系数据库语言的特点外，还具有以下三个特点：,(1) SQL具有自主式和嵌入式两种形式。,(2) SQL具有语言简洁、易学易用的特点。,(3) SQL支持三级数据模式结构。,表4-1 SQL的命令动词,图4-1 SQL对关系数据库模式的支持,4.2 SQL的数据定义功能,SQL的数据定义包括定义基本表、索引、视图和数据库，其基本语句如表4-2所示。,表4-2 SQL的数据定义语句,1. 语句格式的约定符号,在语句格式中：,尖括号“”实际语义。,中括号“ ”中的内容任选项。,大括号“”或用分隔符“|”中的内容必选项，即必选其中之一项。,，n表示前面的项可重复多次。,2. 一般语法规定,SQL中的数据项(包括列项、表和视图)分隔符为“，”，其字符串常数的定界符用单引号“ ”表示。,3. SQL的特殊语法规定,SQL的关键词一般使用大写字母表示。SQL语句的结束符为“；”。SQL一般应采用格式化书写方式。,4.2.1 基本表的定义和维护功能,SQL的基本表定义和维护功能使用基本表的定义、修改和删除三种语句实现。,1. 定义基本表,SQL语言使用CREATE TABLE语句定义基本表，定义基本表语句的一般格式为,CREATE TABLE 库名表名(,列名数据类型列级完整性约束条件,，列名数据类型列级完整性约束条件，n,，表级完整性约束条件，n )；,(1) SQL支持的数据类型。,不同的数据库系统支持的数据类型不完全相同。IBM DB2 SQL支持的数据类型由表4-3列出，其中，使用最多的是字符型数据和数值型数据。,表4-3 IBM DB2 SQL支持的主要数据类型,(2) 列级完整性的约束条件。,列级完整性约束是针对属性值设置的限制条件。SQL的列级完整性条件有以下几种：, NOT NULL或NULL约束。, UNIQUE约束。, DEFAULT约束。, CHECK约束。,(3) 表级完整性约束条件。,表级完整性约束条件是指涉及到关系中多个列的限制条件。在上述的CHECK约束中，如果约束条件表达式中涉及到多列数据，它便为表级约束。,【例4-1】用SQL建立学生_课程库中的基本表，其表结构为,学生(学号，姓名，年龄，性别，所在系),课程(课程号，课程名，先行课),选课(学号，课程号，成绩),2. 修改基本表,当已建立好的基本表随着应用环境和应用需求的变化而需要修改时，需要利用SQL的修改基本表语句修改表结构。SQL语言用ALTER TABLE语句来修改基本表，其一般格式为,ALTER TABLE 表名,ADD(新列名 数据类型 完整性约束 ，n),DROP 完整性约束名,MODIFY(列名 数据类型，n)；,(1) 使用ADD子句增加新列。,当需要向表中增加新列和新的完整性约束时，使用ADD字句对表结构进行修改操作。,【例4-2】向课程表中增加“学时”字段。,ALTER TABLE 课程 ADD 学时 SMALLINT；,(2) 使用MODIFY子句修改列的原定义。,(3) 使用DROP子句删除指定的完整性约束条件。,【例4-3】删除学生表中对“年龄”的默认值的定义。,3. 删除基本表,当不再需要某个基本表时，使用DROP TABLE语句删除它，其一般格式为,DROP TABLE 表名；,基本表一旦被删除，表中的数据及在此表基础上建立的索引、视图将自动地全部被删除掉。因此，执行删除基本表的操作时一定要格外小心。,4.2.2 索引的定义和维护功能,1. 索引的作用,使用索引有以下三方面的作用：,(1) 可以明显地加快数据查询的速度。,(2) 可保证数据的惟一性。,(3) 可以加快连接速度。,2. 建立索引的原则,建立索引是加快数据查询的有效手段，在建立索引时，用户应当依照以下原则：,(1) 索引的建立和维护由DBA和DBMS完成。,(2) 大表应当建索引，小表则不必建索引。,(3) 对于一个基本表，不要建立过多的索引。,(4) 根据查询要求建索引。,3. 建立索引的格式,在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为,CREATE UNIQUE CLUSTER INDEX 索引名,ON 表名(列名次序，列名次序)；,【例4-4】为学生课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按“学号”升序建立索引；课程表按“课程号”升序建立惟一索引；选课表按“学号”升序和“课程号”降序建立惟一索引。,CREATE UNIQUE INDEX 学号ON 学生(学号)；,CREATE UNIQUE INDEX 课程号 ON 课程(课程号)；,CREATE UNIQUE INDEX 选课号 ON 选课(学号 ASC，课程号 DESC)；,4. 删除索引,索引一经建立，就由系统来选择和维护，无须用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统就会花费大量的时间来维护索引，这样就得不偿失了，因此，有时需要删除一些不必要的索引。,SQL语言使用DROP INDEX语句删除索引，其一般格式为,DROP INDEX 索引名；,【例4-5】删除学生表的学生姓名索引。,DROP INDEX 学生姓名；,删除索引时，系统会同时从数据字典中删去有关对该索引的描述。,4.2.3 视图的定义和维护功能,视图是根据子模式设计的关系，它是由一个或几个基本表(或已定义的视图)导出的虚表。,1. 视图的优点,合理地使用视图能够对系统的设计和用户的使用带来很多方便。,(1) 视图能够简化用户的操作。,(2) 视图机制可以使用户以不同的方式看待同一数据。,(3) 视图对数据库的重构提供了一定程度的逻辑独立性。,(4) 视图可以对机密的数据提供安全保护。,2. 视图定义的格式,SQL语言用CREATE VIEW 来定义视图，其一般格式为,CREATE VIEW 视图名 (列名组) ,AS 子查询, WITH CHECK OPTION ；,【例4-6】建立计算机系学生的视图。,CREATE VIEW 计算机系学生,AS SELECT 学号，姓名，年龄,FROM 学生,WHERE 所在系=计算机系；,例4-6中的视图是由一个基本表构造出的。,【例4-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩。,【例4-8】将学生的学号、总成绩、平均成绩定义成一个视图。,3. 视图的删除,视图删除语句的一般格式为,DROP VIEW 视图名；,视图删除后，视图的定义将从数据字典中删除，而由该视图导出的其他视图的定义却仍存在于数据字典中，但这些视图已失效。,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 排序列1 排序要求1，n；,(1) SELECT子句。,(2) FROM子句。,(3) WHERE子句。,(4) GROUP BY子句。,(5) ORDER BY子句。,2. SELECT语句的操作符,(1) 算术操作符。,算术操作符在SQL语句中表达数学运算操作。SQL的数学运算操作符只有四种，它们是：+(加号)、(减号)、*(乘号)和 /(除号)。,(2) 比较操作符。,比较操作符用于测试两个数据是否相等、不等、小于或大于某个值。SQL中的比较操作符包括：=(等于)、(大于)、=(大于等于)、!=或(不等于)、!(不大于)和 !、=、= 和!=。,当连接运算符为“=”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算称为非等值连接。,当等值连接中的连接字段相同，并且在SELECT子句中去除了重复字段时，则该连接操作为自然连接。,【例4-15】查询每个学生的情况以及他(她)所选修的课程。,【例4-16】求学生的学号、姓名、选修的课程名及成绩。,【例4-17】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。,(2) 自连接操作。,连接操作不只是在两个表之间进行，一个表内还可以进行自身连接操作。表自身的连接操作称为自连接。,【例4-18】查询每一门课的间接先行课(即先行课的先行课)。,表4-5 课程表中的数据,表4-6 课程表自连接操作,(3) 外部连接操作。,在前面连接示例的结果集中只保留了符合连接条件的元组，而排除了两个表中没有对应的或匹配的元组情况，这种连接称为内连接。如果要求查询结果集中保留非匹配的元组，就要执行外部连接操作。,表4-7 职工和部门表数据,【例4-19】用SQL表达职工和部门的内连接、左外部连接和右外部连接的语句分别如下：,内连接：,SELECT 职工.*，部门名称，电话,FROM 职工，部门,WHERE 职工.所在部门= 部门.部门号；,左外部连接：,SELECT 职工.*，部门名称，电话,FROM 职工，部门,WHERE 职工.所在部门*= 部门.部门号；,右外部连接：,SELECT 职工.*，部门名称，电话,FROM 职工，部门,WHERE 职工.所在部门 =* 部门.部门号；,表4-8 职工和部门表各种连接的结果集对照表,3. 嵌套查询,在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。,(1) 使用IN操作符的嵌套查询。,当IN操作符后的数据集需要通过查询得到时，就需要使用IN嵌套查询。,【例4-20】求选修了高等数学的学生学号和姓名。,(2) 使用比较符的嵌套查询。,IN操作符用于一个值与多值比较，而比较符则用于一个值与另一个值之间的比较。当比较符后面的值需要通过查询才能得到时，就需要使用比较符嵌套查询。,【例4-21】求C1课程的成绩高于张三的学生学号和成绩。,(3) 使用ANY或ALL操作符的嵌套查询。,表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(并操作)、INTERSECT(交操作)和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】求选修课超过三门课的学生学号。,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. 在表中插入子查询的结果集,如果插入的数据需要查询才能得到，就需要使用插入子查询结果集的INSERT语句。SQL允许将查询语句嵌到数据插入语句中，以便将查询得到的结果集作为批量数据输入到表中。含有子查询的INSERT语句的格式为,INSERT,INTO 表名(属性列1，属性列2),子查询；,【例4-37】求每个系学生的平均年龄，并把结果存入数据库中。,4.4.2 SQL的数据修改功能,SQL修改数据操作语句的一般格式为,UPDATE 表名,SET 列名=表达式，列名=表达式，n,WHERE 条件；,【例4-38】将学生表中全部学生的年龄加上2岁。,【例4-39】将选课表中的数据库课程的成绩乘以1.2。,4.4.3 SQL的数据删除功能,数据删除语句的一般格式为,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语句的结束标志则随主语言的不同而不同。,2. 使数据库的工作单元与程序工作单元之间能够通信,在含有嵌入式SQL的应用程序中，SQL语句负责管理数据库，主语言语句负责控制程序流程和其他功能。数据库的工作单元和程序工作单元之间通信的主要方式有以下两种：,(1) 主语言通过主变量向SQL语句提供参数。,(2) SQL语句的当前工作状态和运行环境数据要反馈给应用程序。,3. 使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾,SQL语言与主语言具有不同的数据处理方式。,4.5.2 不用游标的SQL语句,1. 几种不需要使用游标的SQL语句,下面四种SQL语句不需要使用游标：,(1) 用于说明主变量的说明性语句。,(2) 数据定义和数据控制语句。,(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】将计算机系所有学生的年龄都加上主变量Raise。,【例4-44】将计算机系学生的年龄置空。,(3) 不用游标的数据插入语句。,INSERT语句的VALUES子句可以使用主变量和指示变量，当需要插入空值时，可以把指示变量置为负值。,【例4-45】将学号由主变量Sno、课程号由Cno决定的选课记录插入到数据库中。,4.5.3 使用游标的SQL,游标机制用于解决SQL查询结果为集合而主语言处理方式为记录方式的矛盾。在处理中，必须使用游标的SQL语句有两种：一种是查询结果为多条记录的SELECT语句；另一种是使用游标的DELETE语句和UPDATE语句。,1. 定义游标命令,游标通过DECLARE语句定义，其语句格式为,EXEC SQL DECLARE 游标名 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后的主变量组中。,【例4-48】将打开的指向系的游标向前推进。,4. 关闭游标命令,由于许多系统允许打开的游标数有一定的限制，所以当数据处理完后应及时把不使用的游标关闭，以释放结果集占用的缓冲区及其他资源。,关闭游标使用CLOSE命令，CLOSE命令的具体格式为,EXEC SQL CLOSE 游标名；,4.6 SQL的数据控制功能,4.6.1 数据控制的方法,数据库系统通过以下三步来实现数据控制。,1. 授权定义,具有授权资格的用户，如数据库管理员DBA或建表户DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。,2. 存权处理,数据库管理系统DBMS把授权的结果编译后存入数据字典中。,3. 查权操作,当用户提出操作请求时，系统首先要在数据字典中查找该用户的数据操作权限，当用户拥有该操作权时才能执行其操作，否则系统将拒绝其操作。,4.6.2 SQL Server的数据库操作权限,1. 隐含特权,隐含特权是系统内置权限，是用户不需要进行授权就可拥有的数据操作权。,2. 系统特权,系统特权又称为语句特权，它相当于数据定义语句DLL的语句权限。,不同的数据库系统规定的系统权限不同，SQL Server中的系统特权如表4-11所示。,表4-11 SQL Server中的系统特权,3. 对象特权,对象特权类似于数据库操作语言DML的语句权限，它指用户对数据库中的表、视图、存储过程等对象的操作权限。SQL Server中的对象特权如表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语句的一般格式为,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、DELETE、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 在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建_。,A. 另一个表 B. 游标 C. 视图 D. 索引,4.8 设职工_社团数据库有三个基本表：,职工(职工号，姓名，年龄，性别),社会团体(编号，名称，负责人，活动地点),参加(职工号，编号，参加日期),其中：, 职工表的主码为“职工号”。, 社会团体表的主码为“编号”；外码为“负责人”，被参照表为职工表，对应属性为“职工号”。, 参加表的“职工号”和“编号”为主码；“职工号”为外码，其被参照表为职工表，对应属性为“职工号”；“编号”为外码，其被参照表为社会团体表，对应属性为“编号”。,试用SQL语句表达下列操作：,(1) 定义职工表、社会团体表和参加表，并说明其主码和参照关系。,(2) 建立下列两个视图：,社团负责人(编号，名称，负责人职工号，负责人姓名，负责人性别),参加人情况(职工号，姓名，社团编号，社团名称，参加日期),(3) 查找参加唱歌队或篮球队的职工号和姓名。,(4) 查找没有参加任何社会团体的职工情况。,(5) 查找参加了全部社会团体的职工情况。,(6) 查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。,(7) 求每个社会团体的参加人数。,(8) 求参加人数最多的社会团体的名称和参加人数。,(9) 求参加人数超过100人的社会团体的名称和负责人。,(10) 把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。,4.9 设工程_零件数据库中有四个基本表：,供应商(供应商代码，姓名，所在城市，联系电话),工程(工程代码，工程名，负责人，预算),零件(零件代码，零件名，规格，产地，颜色),供应零件(供应商代码，工程代码，零件代码，数量),试用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。,