第4章关系数据库标准语言SQL

1、第四章第四章 关系数据库标准语言关系数据库标准语言SQLSQL 4.1 SQL语言的基本概念及特点4.2 数据定义4.3 查询4.4 数据更新4.5 视图4.6 SQL的数据控制功能第4章 关系数据库标准语言SQLSQL是英文（StructuredQueryLanguage）的缩写，意思为结构化查询语言，它包括了数据定义、查询、操纵和控制四种功能。 本章介绍了SQL语言的一些基本操作命令，包括数据定义语句（DDL）、数据操纵语句（DCL）及权限的操作，重点和难点是数据操纵语句中的查询操作。4.1SQL语言的基本概念及特点 4.1.1 SQL语言的基本概念 SQL语言支持关系数据库三级模式结构。

2、其中外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。在SQL中，关系模式称为基本表，存储模式称为存储文件，子模式称为视图，元组称行，属性称列。 以下是SQL的相关概念： (1)一个SQL数据库是表的汇集。 (2)一个SQL表由行集构成，行是列的序列，每列对应一个数据项。 (3)表可以是基本表，也可以是视图。基本表是实际存储在数据库中的表，视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上于基本表等同，用户可以在视图上再定义视图。 (4) 一个基

3、本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。另外，一个表可以带若干索引，索引也存放在存储文件中，存储文件与物理文件对应。 (5) 用户可以用SQL语句对表进行操作，包括视图和基本表。基本表和视图一样，都是关系。 (6) SQL的用户可以是应用程序，也可以是终端用户。 4.1.2 SQL语言的特点 SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，主要特点包括：1综合统一 SQL语言集数据定义语言、数据操纵语言、数据控制语言的功能于一体，语言风格同一，可以独立完成数据库生命周期中的全部活动，为数据库应用系统提供了良好的环境。用户在数据库系统投入运行后，还可以根

4、据需要随时地逐步地修改模式，且并不影响数据库的运行，从而使系统具有良好的可扩展性。2高度非过程化 SQL语言高度非过程化，只要提出“做什么”，而无须指明“怎么做”，减轻了用户的负担，也有利于提高数据独立性。3面向集合的操作方式 SQL语言除了操作对象、查找结果是元组的集合，一次插入、删除、更新操作的对象也可以是元组的集合。4以同一种语法结构提供两种使用方式 SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1

5、）程序中，供程序员设计程序时使用。5语言简捷，易学易用 完成核心功能只用9个动词，如下：数据查询(Select)、数据定义(Create，Drop，Alter)、数据操纵(Insert，Update，Delete)、数据控制(Grant，Revoke)。4.1.3 SQL的分类（1）数据定义语言（DDL：Data Definition Language） 创建、修改或删除数据库中各种对象，包括表、视图、索引等。（2）查询语言（QL：Query Language） 按照指定的组合、条件表达式或排序检索已存在的数据库中的数据，但并不改变数据库中数据。（3）数据操纵语言（DML：Data Manip

6、ulation Language ） 对已经存在的数据库进行记录的插入、删除、修改等操作。（4）数据控制语言（DCL：Data Control Language） 用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视。4.2数据定义 关系数据库系统支持三级模式结构，其模式、外模式和内模式的基本对象有表、视图和索引。因此SQL的数据定义功能包括定义表、定义视图定义索引和定义数据库。 4.2.1 定义数据库1.数据库的创建 SQL Server中数据库的创建可用CREATE语句来实现， 其基本命令格式为： CREATE DATABASE ; 例如建立图书销售管理数

7、据库的命令是： CREATE DATABASE tsxsk2.数据库的删除 当一个数据库及其所属的基本表、视图等都不需要时，可以用DROP语句删除这个数据库，其基本命令格式为： DROP DATABASE 如要删除上面创建的数据库的命令是：DROP DATABASE tsxsk 4.2.2 定义基本表 一个基本表由两部分组成，一部分是由各列名构成的表的结构，即一个关系模式，也称为表结构；另一部分是具体存放的数据，称为数据记录，创建基本表时，只需要定义表的结构，包括表名，列名，列的数据类型和列约束等。SQL语言使用CREATE TABLE语句定义基本表，其基本命令格式为： CREATE TABL

8、E （列级完整性约束，列级完整性约束，）； 其中是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。 注意：定义表时每一个定义语句之间用逗号分隔，最后一条语句不用逗号。每个SQL语句以分号结束。【例4.1】 建立图书销售系统中的图书库存信息表。 CREATE TABLE BookRecord （BookNo char(30) not null UNIQUE， BookName char(200)， Publisher Char(100)， Author Char(30)， SalesPrice Money, Quantity Int， Discount Decimal(2,2)， PR

9、IMARY KEY(BookNo) )； 其中其中NOT NULL指的是该列的值不能为空值。 SQL Server 2000中,有下列常用的数据类型：（1）数值型 Int或integer：整数，占用4个字节 Decimal或numeric：数字数据类型，格式Decimal（数据长度，小数位数） float 和 real：浮点数，float更灵活一些 money 专用在与货币有关的浮点数，精确度为4位小数，占8个字节。（2）日期时间型 datetime：代表日期和一天内的时间的日期和时间数据类型。从1753年1月1日到9999年12月31日的日期和时间数据，如：2005-01-01 23:59:

10、59.993（3）字符型 char：分为两种： 固定长度字符数据类型：格式：char(n) 最大长度为n的变长字符串，格式：varchar(n), n 必须是 一个介于 1 和 8,000 之间的数值，当使用字符型数据时需要用或”括起来。 4.2.3 修改基本表 其基本命令格式为： ALTER TABLE ADD 完整性约束DROP MODIFY ； 其中指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。 【例4.2】 向BookRecord表增加一个“出版日期”的PublishiDate列。 AL

11、TER TABLE BookRecord ADD PublishiDate datetime NULL; 不论基本表中原来是否已有数据，新增加的列一律为空值。 【例4.3】 将折扣的数据类型改为整数 ALTER TABLE BookRecord MODIFY Discount int; 修改原有的列定义有可能会破坏已有数据。 【例4.4】 删除字段“出版日期” PublishiDate列 ALTER TABLE BookRecord DROP COLUMN PublishiDate【例4.5】 删除关于书号必须取唯一值的约束ALTERTABLEBookRecordDROPUNIQUE(Book

12、No); 4.2.4 删除基本表 其基本命令格式为： DROP TABLE 【例4.6】 删除BookRecord表 DROP TABLE BookRecord 4.2.5 建立索引 基本命令格式为： CREATE UNIQUE INDEX 索引名 ON 基本表名(列名次序,列名次序); 【说明】 索引可以建立在一列和多列之上，索引顺序可以是ASC（升序）或DESC（降序），缺省值是升序。UNIQUE表示每一个索引值对应唯一的数据记录。 【例4.7】 在BookRecord的单价列上建立一个升序索引s_priceindex CREATE INDEX s_priceindex ON BookRe

13、cord (SalesPrice) 4.2.6 删除索引 索引建立后，系统会自动对其进行选择和维护，无需用户干预，如果数据频繁地增加、修改、删除，系统会花大量的时间来维护索引。因此，根据要实际需要删除一些不必要的索引，基本命令格式为： DROP INDEX .; 【注意】 该命令不能删除由CREATE TABLE或者ALTER TABLE命令创建的主键和唯一性约束索引，也不能删除系统表中的索引。 【例4.8】 删除上例创建的索引“s_priceindex” 命令如下： DROP INDEX BookRecord. s_priceindex4.3查询 建立数据库的主要目的是查询数据。SQL语言提

14、供了SELECT语句进行数据库的查询，其基本命令格式为： SELECT ALL|DISTINCT, FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC; 说明： （1）整个命令含义 从FROM子句指定基本表或视图中，根据WHERE子句的条件表达式查找出满足该条件的记录，按照SELECT子句指定的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP BY子句，则将结果按“列名1”的值进行分组，该属性列值相等的元组为一个组；如果GROUP BY子句带有短语HAVING，则只有满足短语指定条件的分组才会输出。如果有ORDER BY子句，则结果表要按照

15、的值进行升序和降序排列。 SELECT ALL|DISTINCT实现的是对表的投影操作，WHERE 中实现的是选择操作。 （2）目标列表达式 列表达式可以是“列名1,列名2”的形式；如果FROM子句指定了多个表，则列名应是“表名.列名”的形式。 列表达式可以使用SQL提供的库函数形成表达式，常用的函数如下： COUNT(*)：统计记录条数。 COUNT(列名)：统计一列值得个数。 SUM(列名):计算某一数值型列的值的总和。 AVG(列名):计算某一数值型列的值的平均值 MAX(列名):计算某一数值型列的值的最大值 MIN(列名):计算某一数值型列的值的最小值 DISTINCT参数：表示在结果

16、集中，查询出的内容相同的记录只留下一条。 4.3.1 单表查询 单表查询是指仅设计一个表的查询, ,一般只用到SELECT子句、FROM子句和WHERE子句,分别说明所查询列、查询的表或视图、以及搜索条件等，也称为简单查询。 【例4.9】 查询BookRecord表中书名为C+程序设计的BookName字段和Publisher字段 SELECT BookName, Publisher FROM BookRecord WHERE BookName =C+程序设计1SELECT子句选择列表 选择列表(select_list)指出所查询的列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全

17、局变量)等构成。（1）选择所有列【例4.10】 查询BookRecord表中所有列的数据 SELECT * FROM BookRecord 该SELECT语句实际上是无条件地把BookRecord表的全部信息都查询出来，也称为全表查询。（2）选择部分列并指定它们的显示次序 一般情况下，查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 【例4.11】 查询BookRecord表中书号、书名、出版社 SELECT BookNo ，BookName, Publisher FROM BookRecord 在 中各个列的先后顺序也可以与表中的顺序不一致。【例4.12】 SELECT P

18、ublisher ，BookNo ，BookName FROM BookRecord 这时结果表中的列的顺序与基本表中列的顺序不同，是按查询要求，先列出出版社属性，然后再列出书号属性和书名属性。（3）选择经过计算的列 SELECT子句的不仅可以是表中的属性列，也可以是表达式，即可以将查询出来的属性列经过一定的计算后列出结果。【例4.13】 查询所有书本打了折扣后的售价 SELECT BookName, SalesPrice* Discount FROM BookRecord（4）更改列标题 用户可以通过指定别名来更改查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。其定

19、义格式为： 列标题=列名 或 列名 列标题 如果指定的列标题不是标准的标识符格式时，应使用引号定界符。【例4.14】 查询表中书号、书名、出版社用汉字显示对应的列名 SELECT书号= BookNo, 书名= BookName，出版社= Publisher FROM BookRecord （5）删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 2FROM子句选择表 FROM子句指定SELECT语句查询及与查询相关的表或视图。在F

20、ROM子句中最多可指定256个表或视图，它们之间用逗号分隔，在单表查询中FROM子句后只有一个基本表名。3使用WHERE子句设置查询条件 WHERE子句设置查询条件，过滤掉不需要的数据行，只有满足条件的行才能出现在查询结果中。 如在前面查询书名为C+程序设计的内容的例中，WHERE BookName =C+程序设计子句就是将满足书号为“C+程序设计”的行显示在查询结果中。 WHERE子句中常用的查询条件如表4.1所示：(1) 比较大小 【例4.15】 在BookRecord表中查询人民邮电出版社出版的图书的书名、单价。 SELECT BookName, SalesPrice FROM Book

21、Record WHERE Publisher =人民邮电出版社 【注意】 只有两个内容和长度完全相同的字符串才能相等。 【例4.16】 在InRecord表中查询在2004年以后入库的图书的书名、出版社。 SELECT BookName, Publisher FROM InRecord WHERE InDate=2004-12-31 【注意】 两个日期时间型数据相比，越靠后的日期时间越大，如2005年1 月1日比2004年12月31日大。 另外，查询条件InDate=2004-12-31中2004-12-31虽然是一个字符串，但是，SQL Server可以自动将其转换对应的日期类型数据，如果想

22、强制进行这一类型转换，可以使用CAST函数将该条件写成InDate=CAST（2004-12-31AS DateTime）。 【例4.17】 查询所有书价小于25元的图书的书号。 SELECT DISTINCT BookNo FROM BookRecord WHERE SalesPrice 25; (2) 确定范围 Between And 表示数据在某一范围的条件，其使用格式为: Between And 【例4.18】 查询单价在20至50元之间的图书的书名、作者、和出版社。 SELECT BookName, Author, Publisher FROM BookRecord WHERE Sa

23、lesPrice BETWEEN 20 AND 50; 这 里 S a l e s P r i c e B E T W E E N 2 0 A N D 5 0 相 当 于SalesPrice=20 AND SalesPrice=50。 与BETWEEN.AND.相对的谓词是NOT BETWEEN.AND。 【例4.19】 查询单价不在20至50元之间的图书的书名、作者、和出版社。 SELECT BookName, Author, Publisher FROM BookRecord WHERE SalesPrice NOT BETWEEN 20 AND 50; (3) 确定集合【例4.20】 查

24、询清华大学出版社、人民邮电出版社出版的图书的书名、出版社。 SELECT BookName, Publisher FROM BookRecord WHERE Publisher IN (清华大学出版社, 人民邮电出版社) 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。 【例4.21】 查询不是清华大学出版社、也不是人民邮电出版社出版的图书的书名、出版社。 SELECT BookName, Publisher FROM BookRecord WHERE Publisher NOT IN (清华大学出版社, 人民邮电出版社) (4) 字符匹配 谓词LIKE可以用来进行字符串的

25、匹配，常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用 于char、varchar、text、ntext、datetime和smalldatetime等类型查询。 其一般语法格式如下： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符%和_。 百分号%： 可匹配任意类型和长度（长度可以为0）的字符串，如果是中文，请使用两个百分号即%。 下划线_： 匹配单个任意字符，它常用来限制表达式的字符长度。 方括号： 指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。 ： 其取值也 相同，但它要求所匹配对象为指定字符以

26、外的任一个字符。 例如： 限制以“出版社”结尾，使用LIKE %出版社 限制以A开头：LIKE A% 限制以A开头外：LIKE A%【例4.22】 查询姓“谭”的作者所编著的图书的书名、单价，出版社。 SELECT BookName, SalesPrice ，Publisher FROM BookRecord WHERE Author LIKE 谭%； 【例4.23】 查询姓“欧阳”且全名为三个汉字的作者编著的图书的书名、单价，出版社。 SELECT BookName, SalesPrice ，Publisher FROM BookRecord WHERE Author LIKE 欧阳_ _;

27、 【注意】 由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个_。 (5) 涉及空值的查询 在基本表中，如果那一列中没有输入数据，则它的值就为空，空值用一个特殊的数据NULL来表示，如果要判断某一列是否为空，不能用“=NULL”或“ NULL”来比较，只能用IS NULL或IS NOT NULL来运算。 【例4.24】 查询没有打过折扣图书的书名、作者、单价、出版社。 SELECT BookName, Author ，SalesPrice ，Publisher FROM BookRecord WHERE Discount IS NULL; 【注意】 这里的IS不能用等号（=） 代替。 【

28、例4.25】 查询所有打过折扣图书的书名、作者、单价、出版社。 SELECT BookName, Author ，SalesPrice ，Publisher FROM BookRecord WHERE Discount IS NOT NULL; (6) 多重条件查询 逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。 【例4.26】 查询清华出版社出版并且单价在30元以下的书名、作者、单价、出版社。 SELECT BookName, Author ，SalesPrice ，Publisher

29、 FROM BookRecord WHERE Publisher =清华大学出版社 AND SalesPrice 30; 在【例4.20】中的IN谓词实际上是多个OR运算符的缩写，因此【例4.20】中的查询也可以用OR运算符写成如下等价形式： SELECT BookName, Publisher FROM BookRecord WHERE Publisher=清华大学出版社OR Publisher=人民邮电出版社 4对查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序

30、（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。 使用ORDER BY子句对查询返回的结果按一列或多列排序。其语法格式为： ORDER BY column_name ASC|DESC ,n 其中ASC表示升序，为默认值，DESC为降序。ORDER BY不能按ntext、text和image数据类型进行排序。 【例4.27】 查询“人民邮电出版社”出版的图书的书名、单价，并将查询结果按照单价的升序排列。 SELECT BookName, SalesPrice FROM BookRecord WHERE Publisher =清华大学出版社 ORDER BY SalesPri

31、ce ASC; 用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。 【例4.28】 查询图书情况，查询结果按出版社降序排列，对同一出版社出版的图书按单价升序排列。 SELECT * FROM BookRecord ORDERBYPublisherDESC,SalesPrice;5使用集函数 为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括： COUNT(DISTINCT|ALL *) 统计元组个数 COUNT(DISTINCT|ALL ) 统计一列中值的个数 SUM(DISTINCT|ALL ) 计算

32、一列值的总和（此列必须是数值型） AVG(DISTINCT|ALL ) 计算一列值的平均值（此列必须是数值型） MAX(DISTINCT|ALL ) 求一列值中的最大值 MIN(DISTINCT|ALL ) 求一列值中的最小值 如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。 【例4.29】 查询图书的种类。 SELECT COUNT(*) FROM BookRecord; 【例4.30】 计算“清华出版社”出版的图书的平均价格。 SELECT AVG(SalesPrice ) FROM

33、BookRecord WHERE Publisher =清华大学出版社; 【例4.31】 查询“清华出版社”出版的单价最高的图书的价格。 SELECT MAX(SalesPrice) FROM BookRecord WHEREPublisher=清华大学出版社;6对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。 对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 【例4.32】 查询各个出版社出版的图书种类。 SELE

34、CT Publisher, COUNT(BookNo) FROM BookRecord GROUP BY Publisher ; 该SELECT语句对表BookRecord按Publisher的取值进行分组，所有具有相同Publisher值的元组为一组，然后对每一组作用集函数COUNT以求得该出版社出版的图书种类。假设查询结果为： Publisher COUNT(BookNo) 清华大学出版社 22 人民邮电出版社 34 中国水利出版社 44 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。 【例4.33】 查询出版的图书种类超

35、过20种的出版社。 SELECT Publisher, COUNT(BookNo) FROM BookRecord GROUP BY Publisher HAVING COUNT（BookNo）20; WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。 4.3.2 连接查询 一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。1等值与非等值连接查询 连接谓词：用来连接两个表的条件称为连接条件或连接谓

36、词，其一般格式为： . . 其中比较运算符主要有：=、=、=、!= 此外连接谓词还可以使用下面形式： . BETWEEN . AND . 当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比,但不必是相同。例如，可以都是字符型，或都是日期型；也可以一个是整型，另一个是实型，整型和实型都是数值型。 执行连接操作的过程是，首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部扫描完毕后，再到表1中

37、找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1全部元组都处理完毕为止。 【例4.34】 查询每个操作员销售图书的情况。 每个操作员的姓名等基本情况放在操作员信息表(Operator)中、图书销售的情况存放在图书销售记录信息表（SalesRecord）中，所以本查询实际上同时涉及operator与SalesRecord两个表中的数据。这两个表之间的联系是通过两个表都具有的属性操作员账户Account实现的。要查询每个操作员销售图书的情况，就必须将这两个表中相同操作

38、员账户的元组连接起来，这是一个等值连接。在操作时，在相同属性名的前面必须加上各自的表名，如Operator.Account和 SalesRecord.Account，完成本查询的SQL语句为： SELECT Operator.*,SalesRecord.* FROM Operator, SalesRecord WHERE Operator.Account = SalesRecord.Account; 连接运算中有两种特殊情况，一种称为笛卡儿积连接，另一种称为自然连接。 笛卡儿积：是不带连接谓词的连接。两个表的笛卡儿积即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼

39、接，因此结果表往往很大，并且查询结果会出现许多无意义的行，所以这种运算很少使用。 自然连接：如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。 【例4.35】 自然连接Operator和SalesRecord表。 SELECT Operator. Account, Name， Password , Isdeleted , Sn ，SaleDate , SumMonet FROM Operator, SalesRecord WHERE Operator.Account = SalesRecord.Account;自身连接（Sel

40、fjoin） 连接操作可以在同一张表内可进行自身连接，即将同一个表的不同行连接起来。自身连接可以看作一张表的两个副本之间的连接。在自身连接中，必须为表指定两个别名，使之在逻辑上成为两张表。 【例4.36】 在BookRecord表中查询同名的作者及其所著的图书书名。 SELECT a1. Author, a1. BookName FROM BookRecord a1 join BookRecord a2 on a1. Author =a2. Author WHERE a1. BookNO a2. BookNO 查询结果为： A u t h o r B o o k N a m e _ 谭浩强 C

41、+程序设计 谭浩强 C程序设计3外连接 在通常的连接操作中，只有满足符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的元组才能作为结果输出，这样的连接称作内连接。如果在实际应用中，也想同时输出那些不满足连接条件的元组，这时就需要使用外连接。 外连接常用的有两种：左外连接和右外连接，左外连接是在查询结果中包含连接表达式左边表中的不满足连接条件的元组，使用运算符“*=”；右外连接是在查询结果中包含连接表达式右边表中的不满足连接条件的元组，使用运算符“=*”。 这样，我们就可以如下改写【例4.35】 SELECT Operator. Account, Name， Passwor

42、d , Isdeleted , Sn ，SaleDate , SumMonet FROM Operator, SalesRecord WHERE Operator.Account =* SalesRecord.Account；4复合条件连接 上面各个连接查询中，WHERE子句中只有一个条件，即一个连接谓词。WHERE子句中有多个条件的连接操作，称为复合条件连接。 【例4.37】 查询图书销售总金额大于3000元操作员的操作员帐号、姓名。 SELECT Operator. Account, Name FROM Operator, SalesRecord W H E R E O p e r a t

43、 o r . A c c o u n t = SalesRecord.Account;AND SalesRecord. SumMonet 3000; 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。 【例4.38】 查询每个操作员及其销售的的图书其及金额。 SELECT Operator.Name，SalesDetail . BookName , SalesRecord .SumMonet FROM Operator, salesDetail， SalesRecord WHERE Operator.Account = SalesRecord

44、.Account AND SalesRecord.Sn= salesDetail.Sn 4.3.3 嵌套查询 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询, 又称子查询。其中处于内层的查询称为子查询，嵌套查询命令在执行时，每个子查询在上一级查询处理之前求解，即由里向外查，子查询的结果用于建立其父查询的查找条件。子查询是SQL 语句的扩展，其语句形式如下： SELECT ，. FROM WHERE 表达式 （SELECT ,. FROM 表或视图名2） GROUP BY HAV

45、ING 比较运算符 （SELECT ,. FROM ） 【例4.39】 查询“清华大学出版社”出版的图书已销售的书名、数量。 SELECT BookName，Quantity FROM SalesDetail WHERE BookNo IN SELECT BookNo FROM BookRecord WHERE Publisher =清华大学出版社; 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是SQL(StructurredQueryLanguage)中“结构化”的含义所在。1带有IN谓词的子查询 带有IN谓词的子查询是指父查

46、询与子查询之间用IN进行连接，判断父查询的某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。 【例4.40】 查询作者为“谭浩强”的图书的销售数量。 查询作者为“谭浩强”的图书的销售数量，可以首先查询所有“谭浩强”所编写的图书，然后在所有已销售的图书中查找作者是“谭浩强”的图书销售数量。可以分步来完成此查询： 先查询所有“谭浩强”所编写的图书 SELECT BookNo FROM BookRecord WHERE Author =“谭浩强”; 结果为： BookNo 9787302778211 9787302778252

47、在所有已销售的图书中查找作者是“谭浩强”的图书销售数量。 SELECT BookName，Quantity FROM SalesDetail WHERE BookNo=“9787302778211” SELECT BookName，Quantity FROM SalesDetail WHERE BookNo=“9787302778252” 分步书写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。SQL语句如下： SELECT BookName，Quantity FROM SalesDetail WHERE BookNo IN (S

48、ELECT BookNo FROM BookRecord WHERE Author =“谭浩强”) 可见，实现同一个查询可以多种方法，当然不同的方法其执行效率可能会有差别，甚至会差别很大，以上嵌套查询还可以用下面的连接查询来实现： SELECT BookName，Quantity FROM SalesDetail ，BookRecord WHERE SalesDetail .BookNo =BookRecord.BookNo AND BookRecord.Author =“谭浩强”2带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查

49、询返回的是单值时，可以用、 =、=、!=或等比较运算符。 【例4.41】 查询“人民邮电出版社”出版的并且单价小于该所有图书平均价格的图书。 由于所有图书平均价格的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其SQL语句如下： SELECT BookName FROM BookRecord WHERE Publisher =人民邮电出版社 AND SalesPrice ANY 大于子查询结果中的某个值 即表示大于查询结果中最小值 = ANY 大于等于子查询结果中的某个值即表示大于等于结果集中最小值 = ANY 小于等于子查询结果中的某个值即表示小于等于结果集中最大值 = ANY 等于

50、子查询结果中的某个值 != ANY或 ANY 不等于子查询结果中的某个值 即相当于IN ALL 大于子查询结果中的所有值 即表示大于查询结果中最大值 = ALL 大于等于子查询结果中的所有值即表示大于等于结果集中最大值 = ALL 小于等于子查询结果中的所有值即表示小于等于结果集中最小值 = ALL 等于子查询结果中的所有值（通常没有实际意义） != ALL或 ALL 不等于子查询结果中的任何一个值即相当于 NOT IN 【例4.42】 查询其他出版社中比“人民邮电出版社”任一图书单价都低的图书的书名、单价。 SELECT BookName， SalesPrice FROM BookRecor

51、d W H E R E P u b l i s h e r 人 民 邮 电 出 版 社 A N D SalesPriceANY(SELECT SalesPrice FROM BookRecord WHERE Publisher =人民邮电出版社 SELECT BookName， SalesPrice FROM BookRecord WHERE Publisher 人民邮电出版社 AND SalesPrice (SELECT MAX(SalesPrice) FROM BookRecord WHERE Publisher =人民邮电出版社 【例4.43】 查询其他出版社中比“人民邮电出版社”所有图

52、书单价都低的图书的书名、单价。 SELECT BookName， SalesPrice FROM BookRecord WHERE Publisher 人民邮电出版社 AND SalesPriceALL(SELECT SalesPric FROM BookRecord WHERE Publisher =人民邮电出版社 以上查询实际上也可以用集函数实现。先利用求最小值函数找出“人民邮电出版社”出版的图书中最低单价,然后在父查询中查找出所有单价比前面的最低单价都低并且不是“人民邮电出版社”出版的图书显示其书名，单价。 SELECT BookName， SalesPrice FROM BookRec

53、ord WHERE Publisher 人民邮电出版社 AND SalesPrice100，则取此 BookRecord. BookName，BookRecord. Quantity送入查询结果中。写成SQL语句就是： SELECT BookName，Publisher FROM BookRecord WHERE EXISTS ( SELECT * FROM SalesDetail WHERE SalesDetail. BookNo = BookRecord. BookNo AND Quantity100); 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则

54、返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名亦无实际意义。 这类查询与我们前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值（在本例中是依赖于BookRecord表的 BookNo值），我们称这类查询为相关子查询（Correlated Subquery）。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。从概念上讲，相关子查询的一般处理过程是： 首先取外层查询中BookRecord表的的第一个元组，根据它与内层查询相关的属性值（即BookNo值）处理内层查询， 若WHERE子句返回值

55、为真（即内层查询结果非空），则取此元组放入结果表； 然后再检查Student表的下一个元组；重复这一过程，直至BookRecord表全部检查完毕为止。 本例中的查询也可以用连接运算来实现，可以参照有关的例子，自己给出相应的SQL语句。 与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。 【例4.45】 查询当前没有销售图书的书名、出版社。 SELECT BookName，Publisher FROM BookRecord WHERE NOT EXISTS ( SELECT * FROM S

56、alesDetail WHERE SalesDetail. BookNo = BookRecord. BookNo) 【例4.45】 查询当前没有销售图书的书名、出版社。 SELECT BookName，Publisher FROM BookRecord WHERE NOT EXISTS ( SELECT * FROM SalesDetail WHERE SalesDetail. BookNo = BookRecord. BookNo) SELECT BookName，Quantity FROM SalesDetail WHERE EXISTS (SELECT * FROM BookRecor

57、d W H E R E B o o k R e c o r d . B o o k N o =SalesDetai.BookNo AND Author =“谭浩强”) 由于带EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于不相关子查询，甚至有时是最高效的方法。 4.3.4 集合查询 把多个SELECT语句的结构完全相同的结果合并为一个结果，用集合操作来完成，这种查询称为集合查询，标准SQL集合操作只要并操作UNION。 使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统可以去掉重复的元组。需要注意的是，参加UNION操作的各结果

58、表的列数必须相同；对应项的数据类型也必须相同。 UNION的语法格式为： (Select查询语句1 UNION ALL （select查询语句2） ALL选项表示将所有行合并到结果集合中。不指定该项时，被联合查询结果集合中的重复行将只保留一行。 联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。 在使用UNION 运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型。在自动转换时

59、，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。 【例4.46】 查询清华大学出版社和出版的图书的书名、单价。 (SELECT BookName, SalesPrice FROM BookRecordWHEREPublisher=清华大学出版社ANDBookName=C+程序设计) UNION (SELECT BookName, SalesPrice FROM BookRecordWHEREPublisher=人民邮电出版社ANDBookName=C+程序设计 ) 上例也可用前面讲到的逻辑运算符or来实现，其SQL语句为： SELECT BookName, SalesPrice

60、FROM BookRecord WHERE （ Publisher = 清华大学出版社 ）or（ Publisher = 人民邮电出版社 ）AND BookName= C+程序设计4.4数据更新 数据库的更新包括插入、删除、修改（也称为更新）三种操作。 4.4.1 插入数据 INSERT语句通常有两种形式。一种是插入一个元组，插入的数据以常量的形式给出；另一种是插入子查询的结果，即将查询的结果插入到表中，可以一次插入多个元组。1插入一个元组 插入一个元组的INSERT语句的格式为： INSERT INTO (,.) VALUES ( ,.) 【例4.47】 将一条图书记录（书号：9787302