关系数据库标准语言SQL.doc

15第三篇 关系数据库标准语言SQL3 关系数据库标准语言SQL【本章综述】SQL语言是关系数据库操作的标准语言。主要包括数据定义、数据操作和数据控制三类。【本章重点】sql语句的编写和执行。【本章难点】嵌套查询。前言：SQL（Structured Query Language）语言1974年提出，19751979年IBM公司研制了著名的关系数据库管理系统原形System R 并实现了这种语言。特点：功能丰富、语言简捷。经过不断修改，最终发展成为关系数据库的标准语言。31 SQL概述本节综述SQL 是一种介于关系代数与关系演算之间的结构化查询语言，但其功能不仅仅是查询，SQL是一个通用的、功能极强的关系数据库语言。3 1.1 SQL的特点SQL 语言集数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definiton）和数据控制（Data control）功能与一体，主要特点包括：综合统一问题的提出：非关系模型的数据语言一般都分为模式、外模式数据定义语言、与数据存储有关的描述语言及数据操纵语言，他们分别用于定义模式、外模式、内模式和进行数据的存取与处置。因此，当用户眼数据库投入运行后，如需修改模式，必须停止现有数据库的运行，转储数据，修改模式并编译后再重装数据库，十分麻烦。SQL如何解决：集数据定义语言、数据操纵语言、数据控制语言的功能与一体，语言风格统一，可以独立完成数据库生命周期中的全部活动（包括定义关系模式、插入数据建立数据库、查询、更新、维护、数据库重构、数据库安全性等一系列操作要求），为数据库应用系统的开发提供了良好的环境。另：在关系数据模型中实体和实体间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一。查找、插入、删除、修改等每一种操作都只要一种操作符，克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。高度非过程化问题：非关系数据模型的数据操纵语言是面向过程的语言（可少做解释），用其完成的某项请求，必须指定存取路径。解决：SQL语言只要提出“做什么”，而无须指明“怎么做”，无须了解存取路径，路径选择由系统自动完成。面向集合的操作方式问题：非关系数据模型采用的是面向记录的操作方式。用户要对记录逐条操作。解决：SQL采用集合操作方式。操作对象、查找结果、插入、删除、修改的对象均为元组的集合。以同一种语法结构提供两种使用方式SQL即是自含式语言（用户直接键入SQL命令操作数据库），又是嵌入式语言（嵌入高级语言程序中，供程序设计时使用）。语言简捷，易学易用3 1.2 SQL语言的基本概念【简单介绍】SQL外模式视图2视图1模 式基本表3基本表4基本表1基本表2内模式存储文件2存储文件1基本表：本身独立存在的表，在SQL中一个关系对应一个表。一个（多个）基本表对应一个存储文件。存储文件：其逻辑结构组成了关系数据库的内模式。视图：从一个或几个基本表导出的表，本身不独立存储在数据库中，数据库只存放视图的定义而不存放视图对应的数据。32 数据定义（详讲的一节）操作对象操作方式创 建删 除修 改表CREATE TABLEDROP TABLEALTER TABLE 视 图CREATE VIEWDROP VIEW索 引CREATE INDEXDROP INDEX SQL的数据定义语句321 定义、删除与修改基本表1. 定义基本表CREATE TABLE ( 列级完整性约束条件 ， 列级完整性约束条件 ，表级完整性约束条件）；重点讲述P88 例1（讲清完整性约束条件）2. 修改基本表ALTER TABLE ADD 完整性约束DROPMODIFY;重点讲述P89 例2、3、43. 删除基本表DROP TABLE 注意：基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除，因此执行此操作要格外当心。322 建立与删除索引 建立索引的意义：加快查询速度的有效手段。（可以讲查字典的例子）1. 建立索引CREATE UNIQUECLUSTERINDEX ON (,);次序：ASC（升序，缺省）、DESC（降序）UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER：表示建立聚簇索引。聚簇索引：指索引项的顺序与表中记录的物理顺序一致的索引组织。其目的用户可以在最常查询的列上建立聚簇索引提高查询效率。缺点：索引更新时，物理顺序的变更代价过大。重点讲述P91 例62. 删除索引DROP INDEX33 查 询数据库查询是数据库的核心操作。SELECT 语句的一般格式： SELECT all |distinct FROM , WHERE GROUP BYHAVING ORDER BYASC|DESC; 含义：整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式选出元组中属性值形成的结果表。 附：GROUP 子句按列名1分组，属性相等的为一个组。HAVING ：满足条件的组才能输出。ORDER：排序。331 单表查询（重点是若干例子）单表查询是指仅涉及一个表的查询。1. 选择表中的若干列（P9293 例1例5）(1) 查询指定列(2) 查询全部列（注意“*”的用法）(3) 查询经过计算的值2. 选择表中的若干元组（P9499 例6例23）(1) 消除取值重复的行（DISTINCT的用法）(2) 查询满足条件的元组（WHERE的用法）l 比较大小（=、=、=、!=或）l 确定范围（BETWEEN。AND 的用法）l 确定集合（IN、NOT IN 的用法）l 字符匹配（LIKE 的用法）NOTLIKEESCAPE匹配串可以是完整的字符，亦可是如下两种通配符：%（表任意长度） -（表单个字符）l 涉及空值的查询（NULL）l 多重条件查询（AND OR的使用）(3) 对查询结果排序（P99 例24，25）(4) 使用集函数（P100 例26例29）COUNT （DISTINCT|ALL*）统计元组个数COUNT （DISTINCT|ALL）统计一列中值的个数SUM （DISTINCT|ALL）计算一列中值的总和AVG （DISTINCT|ALL）计算一列中值总和的平均值MAX （DISTINCT|ALL）求一列中值的最大值MIN （DISTINCT|ALL）求一列中值的最小值(5) 对查询结果分组（P101 例30例31）注意：WHERE 子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组，HAVING短语作用与组，从中选择满足条件的组。332 连接查询（重点是若干例子） 连接查询：一个查询同时涉及两个以上的表。包括等值连接、自然连接、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。1. 等值与非等值连接查询（P102 例32）连接条件（谓词）的定义：用来连接两个表的条件。连接查询的格式： . 或.BETWEEN .AND.等值连接：运算符为 =连接操作的过程：找表1中的第一个元组，扫描表2找满足连接条件的元组，将两元组拼接，继续扫描表2直到完毕，接着找表1中的第二个元组，依次类推。连接操作的分类：自然连接（去掉重复的属性列）和广义笛卡儿积（无连接谓词的连接）。2. 自身连接（P103 例34）自身连接定义：一个表与其自己进行连接。3. 外连接应用的范围：当某个元组的某一属性为空值，无法满足连接条件，因而无法输出，我们要能够输出该元组的其他情况信息，因此使用外连接。外连接的表示方法：在连接谓词的某一边加“*”，就好象为“*“所在边的表增加一个“万能行”，这个行全部由空值组成，他可以和另一边的表中所有不满足连接条件的元组进行连接。外连接的分类：右外连接和左外连接。4. 复合条件连接（P105 例35，例36）WHERE子句中可以有多个连接条件，成为复合条件连接。333 嵌套查询（重点是若干例子）嵌套查询：在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING 短语的条件的查询称为嵌套查询。上层称为父查询（外层查询），下层称为子查询（内层查询）。注意：子查询中不能用ORDER BY 语句，ORDER BY 只能对最终查询结果排序。嵌套查询的方法：由内向外1. 带有IN谓词的子查询（P107 例37，P108 例38）不相关子查询：子查询的查询条件不依赖于父查询。2. 带有比较运算符的子查询指父查询与子查询之间用比较运算符进行连接，需注意的是子查询一定要跟在比较符之后。3. 带有ANY 或ALL谓词的子查询（P110 例39，40）子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时则必须同时使用比较运算符。其语意为：ANY 大于子查询结果中的某个值ALL 大于子查询结果中的所有值ANY 小于子查询结果中的某个值ALL 小于子查询结果中的所有值ANY大于等于子查询结果中的某个值ALL 大于等于子查询结果中的所有值ANY小于等于子查询结果中的某个值ALL小于等于子查询结果中的所有值ANY等于子查询结果中的某个值ALL等于子查询结果中的所有值（通常没有实际意义）！（或）ANY 不等于子查询结果中的某个值！（或ALL 不等于子查询结果中的所有值4. 带有EXISTS谓词的子查询（P111 例41例44）EXISTS 代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“TURE“或“FALSE”。若内层查询的结果非空，则外层的WHERE子句返回真值，否则假值。由EXISTS引出的子查询，其目标列表表达式通常都用*。一个名词：相关子查询：子查询的查询条件依赖于外层父查询的某个属性值。因此需反复求值。（类似于程序设计中的主子程序调用）。相关子查询处理过程：首先取外层查询中的第1个元组，根据他与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表，接着取下一个元组。334 集合查询（重点是若干例子）（P114115 例46-49）SELECT语句的查询结果是元组的集合，所以多个SELEST语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INSTERSECT和差操作MINUS。335 SELECT 语句的一般格式（简单总结）。34 数据更新341 插入数据 （INSERT）1. 插入单个元组（P117 例1，2）格式：INSERT INTO ,.VALUES(,.); 功能：将新元组插入到指定表中。 注意：表定义时说明了NOT NULL的属性列不能取空值。2. 插入子查询结果（P118 例2，3）格式：INSERT INTO ,.子查询；功能：生成要插入的批量数据。342 修改数据 格式：UPDATA 表名SET列名=表达式，列名=表达式.WHERE条件； 功能：修改指定表中满足WHERE子句条件的元组。SET给出表达式的值用于取代对应的属性列值。1. 修改某一个元组的值（P119。4）2. 修改多个元组的值（P119。5）3. 带子查询的修改语句（P119。6）343 删除数据 格式：DELETE FROM表名WHERE条件； 功能：删除满足条件的元组。1. 删除某一个元组的值（P119。7）2. 删除多个元组的值（P120。8）3. 带子查询的删除语句（P120。9）4. 更新操作与数据库的一致性问题：增删改操作只能对一个表操作，因此某些时候会破坏数据的参照完整性。解决：(1) 事务（第七章）(2) 当向参照表中插入元组时，系统将自动的检查被参照表中是否存在响应的元组，如都存在则成功否则失败。(3) 删除主表中的元组时，系统自动删除参照表的响应的元组。35 视图 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。特点：视图是虚表，由基本表导出的表，在数据库中仅含有定义而无数据，视图如“窗口”，通过它可以看到数据库中自己感兴趣的数据及其变化。351 定义视图(1) 建立视图（P122123 例1例6）CREATE VIEW(,.)ASWITH CHECK OPTION; 说明：1、通常不许含有ORDER BY 子句和DISTINCT 短语 2、WIHT CHECK OPTION 表示对视图进行增删改时要保证更新、插入或删除的行满足视图定义中的谓词条件。 3、组成视图的属性列名或者全部省略或者全部指定。 4、三种情况必须明确指定组成视图的所有列名。(2) 删除视图（P123 例8）DROP VIEW视图名 说明：视图删除后视图的定义将从字典中删除，但由该视图导出的其他视图定义仍在数据字典中，不过均已失效。352查询视图（P125 例1例3） 说明：DBMS执行对视图的查询时，首先进行有效性检查，检查查询的表、视图是否存在，如存在则从数据字典中取出视图的定义， 把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。此过程称为视图消解。注意：例3出现的问题。WHERE子句不能用集函数作为条件表达式。建议：例3的问题，最好直接对基本表操作。353更新视图（P126 例1例3） 定义：指通过视图来插入、删除和修改数据。 实质：视图是无数据的虚表，因此必须转换到基本表的操作。 注意：在关系数据库中，并不是所有的视图都是可更新的。 例：S-C视图内含两列属性：学号、平均成绩（由基本表对元组分组后计算而得到的平均值） 我们想通过视图把某一学号的平均成绩进行更改，但在基本表中无法对平均成绩这一列进行更新，因为它要由各科成绩共同决定，因此通过视图是无法更改平均成绩这一列的。 DB2对如下不能进行更新的视图作了规定：l 若视图是由两个以上基本表导出的，此视图不允许更新。l 视图的字段来自字段表达式或常数，不允许对此视图执行INSERT和UPDATA 操作，但可执行DELETE。l 若视图的字段来自集函数，此视图不允许更新。l 若视图定义中含有GROUP BY 子句，此视图不允许更新。l 若视图定义中含有DISTINCT 子句，此视图不允许更新。l 若视图定义中有嵌套查询，并且内层查询的FROM子句涉及的表也是导出该视图的基本表，此视图不允许更新。l 一个不允许更新的视图上定义的视图也不允许更新。354视图的作用l 视图能够简化用户的操作l 视图能使用户以多种角度看待同一数据l 视图对重构数据库提供了一定程度的逻辑独立性l 视图能够对机密数据提供安全保护。35 数据控制SQL中的数据控制功能包括事务管理功能和数据保护功能，即数据库的恢复、并发控制；数据库的安全性和完整性控制。我们这一章主要讨论SQL语言的安全性控制功能。要求DBMS能够作到：l 把授权的决定告知系统。（由GRANT 和REVOKE 语句完成）l 把授权的结果存入数据字典。l 当用户提出操作请求时，根据授权情况进行检查，以决定是否执行操作请求。361授权（P131 例1例6）SQL语言用GRANT 语句向用户授予操作权限。格式： GRANT,. ON TO ,. WITH GRANT OPTION不同对象类型允许的操作权限对象对象类型操作权限属性列TABLESELECT，INSERT，UPDATA，DELETE，ALL PRIVILEGES视图TABLESELECT，INSERT，UPDATA，DELETE，ALL PRIVILEGES基本表TABLESELECT，INSERT，UPDATA，DELETE，ALTER，INDEXX，ALL PRIVILEGES数据库DATABASEDREATETAB 注意：如指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。如没有则只能自己使用，不能传播。362收回权限 格式：REVOKE,. ON FROM ,.注意：收回权限的级联性。 例：U5被赋予权限，U5又将权限传播至U6，则收回U5权限的同时，U6的权限亦不存在。37 嵌入式SQL问题的提出：前面介绍的SQL语言是作为独立语言在终端交互方式下使用，是非过程的。大多数语句独立执行，与上下文无关。但许多事务处理应用都是过程性的，需要根据不同的条件来执行不同的任务，单纯用SQL无法实现。解决的方法：将SQL语言嵌入到高级语言中去。利用高级语言的过程性结构来弥补SQL语言的不足。此时的SQL称为嵌入式SQL，而嵌入SQL的高级语言称为主语言或宿主语言。371嵌入式SQL的一般形式DBMS处理宿主型SQL语言的两种方法：(1) 预编译：由DBMS的预处理程序扫描原程序，识别出SQL语句，把它们转换成主语言调用语句，使主语言编译程序能识别它，最后由主语言的编译程序将整个源程序编译成目标码。(2) 修改和扩充主语言使之能处理SQL语句。嵌入式SQL的一般形式：前加 EXEC SQL，结束标志随主语言的不同而不同：PL/C （“；”）COBOL （“END-EXEC”）372嵌入式SQL语句与主语言之间的通信问题的提出：SQL语句负责操纵数据库，高级语言语句负责控制程序流程。此时程序含有两种不同计算模型的语句，一种是面向集合的SQL语句，另一种是面向过程的高级语言语句。它们之间如何通信？解决的方法及步骤：(1) 用SQL通信区SQLCA向主语言传递SQL语句的执行状态信息，使主语言控制信息流程。(2) 主语言用主变量向SQL提供参数。(3) 用主变量和游标将SQL语句查询数据库的结果交主语言进行进一步处理。几个较为关键的术语：(1) SQL通信区SQLCA是一个数据结构。用EXEC SQL INCLUDE SQLCA 定义，SQLCA中存放每次执行SQL语句后返回代码的变量SQLCODE。每执行完一条SQL语句都测试SQLCODE的值，以了解SQL语句的执行情况并做响应处理。如SQLCODE 等于预定常量SUCCESS，则表示SQL语句成功。(2) 主变量把SQL语句中使用的主语言程序变量成为主变量。输入主变量：由应用程序对其赋值，SQL语句引用。可以指定向数据库中插入的数据，可以将数据库中的数据修改为指定值。输出主变量：由SQL语句对其赋值或设置状态信息，返回给应用程序。可以得到SQL语句的结果数据和状态。指示变量：一个整型变量，用来“指示”所指主变量的值或条件。 主变量的说明位置：BEGIN DECLARE SECTION 与END DECLARE SECTION 之间。(3) 游标作用：SQL处理多条记录，主变量只能一次存放一条记录，用主变量不能完全满足SQL语句向应用程序输出数据的要求。为此引入游标。定义：是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。 过程：每一个游标区都有一个名字，用户通过游标区获取记录，赋予主变量，交由主语言进一步处理。程序示例：（进一步重申嵌入式SQL的工作原理）373不用游标的SQL语句 (1) 说明性语句作用：专为在嵌入式SQL中说明主变量而设置的。格式：EXEC SQL BEGIN DECLARE SECTION 主变量说明； EXEC SQL END DECLARE SECTION(2) 数据定义语句(3) 数据控制语句(4) 查询结果为单记录的SELECT语句在嵌入式SQL中，查询结果为单记录的SELECT语句需要用INTO 子句指定查询结果的存放地点。注意几点：l