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

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第四章 关系数据库标准语言,SQL,4.1,SQL,语言的基本概念及特点,4.2 数据定义,4.3 查询,4.4 数据更新,4.5 视图,4.6,SQL,的数据控制功能,第4章 关系数据库标准语言,SQL,SQL,是英文（,Structured Query Language,）,的缩写，意思为结构化查询语言，它包括了数据定义、查询、操纵和控制四种功能。,本章介绍了,SQL,语言的一些基本操作命令，包括数据定义语句（,DDL）、,数据操纵语句（,DCL）,及权限的操作，重点和难点是数据操纵语句中的查询操作。,4.1,SQL,语言的基本概念及特点,4.1.1,SQL,语言的基本概念,SQL,语言支持关系数据库三级模式结构。其中外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。在,SQL,中，关系模式称为基本表，存储模式称为存储文件，子模式称为视图，元组称行，属性称列。,以下是,SQL,的相关概念：,(1)一个,SQL,数据库是表的汇集。,(2)一个,SQL,表由行集构成，行是列的序列，每列对应一个数据项。,(3)表可以是基本表，也可以是视图。基本表是实际存储在数据库中的表，视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上于基本表等同，用户可以在视图上再定义视图。,(4) 一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。另外，一个表可以带若干索引，索引也存放在存储文件中，存储文件与物理文件对应。,(5) 用户可以用,SQL,语句对表进行操作，包括视图和基本表。基本表和视图一样，都是关系。,(6),SQL,的用户可以是应用程序，也可以是终端用户。,4.1.2,SQL,语言的特点,SQL,语言集数据查询、数据操纵、数据定义和数据控制功能于一体，主要特点包括：,1综合统一,SQL,语言集数据定义语言、数据操纵语言、数据控制语言的功能于一体，语言风格同一，可以独立完成数据库生命周期中的全部活动，为数据库应用系统提供了良好的环境。用户在数据库系统投入运行后，还可以根据需要随时地逐步地修改模式，且并不影响数据库的运行，从而使系统具有良好的可扩展性。,2高度非过程化,SQL,语言高度非过程化，只要提出“做什么”，而无须指明“怎么做”，减轻了用户的负担，也有利于提高数据独立性。,3面向集合的操作方式,SQL,语言除了操作对象、查找结果是元组的集合，一次插入、删除、更新操作的对象也可以是元组的集合。,4以同一种语法结构提供两种使用方式,SQL,语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入,SQL,命令对数据库进行操作。作为嵌入式语言，,SQL,语句能够嵌入到高级语言（例如,C、COBOL、FORTRAN、PL/1）,程序中，供程序员设计程序时使用。,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 Manipulation Language ）,对已经存在的数据库进行记录的插入、删除、修改等操作。,（4）数据控制语言（,DCL：Data Control Language）,用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视。,4.2 数据定义,关系数据库系统支持三级模式结构，其模式、外模式和内模式的基本对象有表、视图和索引。因此,SQL,的数据定义功能包括定义表、定义视图定义索引和定义数据库。,4.2.1 定义数据库,1.数据库的创建,SQL Server,中数据库的创建可用,CREATE,语句来实现， 其基本命令格式为：,CREATE DATABASE ;,例如建立图书销售管理数据库的命令是：,CREATE DATABASE,tsxsk,2.,数据库的删除,当一个数据库及其所属的基本表、视图等都不需要时，可以用,DROP,语句删除这个数据库，其基本命令格式为：,DROP DATABASE ,如要删除上面创建的数据库的命令是：,DROP DATABASE,tsxsk,4.2.2 定义基本表,一个基本表由两部分组成，一部分是由各列名构成的表的结构，即一个关系模式，也称为表结构；另一部分是具体存放的数据，称为数据记录，创建基本表时，只需要定义表的结构，包括表名，列名，列的数据类型和列约束等。,SQL,语言使用,CREATE TABLE,语句定义基本表，其基本命令格式为：,CREATE TABLE （列级完整性约束，列级完整性约束，）；,其中是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。 注意：定义表时每一个定义语句之间用逗号分隔，最后一条语句不用逗号。每个,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)，,PRIMARY 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: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,列。,ALTER 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】 删除关于书号必须取唯一值的约束,ALTER TABLE,BookRecord,DROP UNIQUE(,BookNo,);,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,BookRecord,(,SalesPrice,),4.2.6 删除索引,索引建立后，系统会自动对其进行选择和维护，无需用户干预，如果数据频繁地增加、修改、删除，系统会花大量的时间来维护索引。因此，根据要实际需要删除一些不必要的索引，基本命令格式为：,DROP INDEX .;,【注意】 该命令不能删除由,CREATE TABLE,或者,ALTER TABLE,命令创建的主键和唯一性约束索引，也不能删除系统表中的索引。,【例4.8】 删除上例创建的索引“,s_,priceindex,”,命令如下：,DROP INDEX,BookRecord,. s_,priceindex,4.3 查询,建立数据库的主要目的是查询数据。,SQL,语言提供了,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,子句，则结果表要按照的值进行升序和降序排列。,SELECT ALL|DISTINCT实现的是对表的投影操作，,WHERE 中实现的是选择操作。,（2）目标列表达式, 列表达式可以是“列名1,列名2”的形式；如果,FROM,子句指定了多个表，则列名应是“表名.列名”的形式。, 列表达式可以使用,SQL,提供的库函数形成表达式，常用的函数如下：,COUNT(*)：,统计记录条数。,COUNT(,列名)：统计一列值得个数。,SUM(,列名):计算某一数值型列的值的总和。,AVG(,列名):计算某一数值型列的值的平均值,MAX(,列名):计算某一数值型列的值的最大值,MIN(,列名):计算某一数值型列的值的最小值,DISTINCT,参数：表示在结果集中，查询出的内容相同的记录只留下一条。,4,.3.1 单表查询,单表查询是指仅设计一个表的查询,一般只用到,SELECT,子句、,FROM,子句和,WHERE,子句,分别说明所查询列、查询的表或视图、以及搜索条件等，也称为简单查询。,【例4.9】 查询,BookRecord,表中书名为,C+,程序设计的,BookName,字段和,Publisher,字段,SELECT,BookName, Publisher,FROM,BookRecord,WHERE,BookName,=C+,程序设计,1,SELECT,子句选择列表,选择列表(,select_list),指出所查询的列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。,（1）选择所有列,【例4.10】 查询,BookRecord,表中所有列的数据,SELECT *,FROM,BookRecord,该,SELECT,语句实际上是无条件地把,BookRecord,表的全部信息都查询出来，也称为全表查询。,（2）选择部分列并指定它们的显示次序,一般情况下，查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。,【例4.11】 查询,BookRecord,表中书号、书名、出版社,SELECT,BookNo,，,BookName, Publisher,FROM,BookRecord,在 中各个列的先后顺序也可以与表中的顺序不一致。,【例4.12】,SELECT Publisher ，,BookNo,，,BookName,FROM,BookRecord,这时结果表,中的列的顺序与,基本表中,列的顺序,不同，是按查询要求，先列出出版社属性，然后再,列,出书号属性和书名属性。,（3）选择经过计算的列,SELECT,子句的不仅可以是表中,的属性列,，也可以是表达式，即可以将查询出来的,属性列经过一定,的计算后列出结果。,【例4.13】 查询所有书本打了折扣后的售价,SELECT,BookName,SalesPrice,* Discount,FROM,BookRecord,（4）更改列标题,用户可以通过指定别名来更改查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。其定义格式为：,列标题=列名 或 列名 列标题,如果指定的列标题不是标准的标识符格式时，应使用引号定界符。,【例4.14】 查询表中书号、书名、出版社用汉字显示对应的列名,SELECT,书号=,BookNo,书名=,BookName,，,出版社=,Publisher,FROM,BookRecord,（5）,删除重复行,SELECT,语句中使用,ALL,或,DISTINCT,选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为,ALL。,使用,DISTINCT,选项时，对于所有重复的数据行在,SELECT,返回的结果集合中只保留一行。,2,FROM,子句选择表,FROM,子句指定,SELECT,语句查询及与查询相关的表或视图。在,FROM,子句中最多可指定256个表或视图，它们之间用逗号分隔，在单表查询中,FROM,子句后只有一个基本表名。,3使用,WHERE,子句设置查询条件,WHERE,子句设置查询条件，过滤掉不需要的数据行，只有满足条件的行才能出现在查询结果中。,如在前面查询书名为,C+,程序设计的内容的例中，,WHERE,BookName,=C+,程序设计子句就是将满足书号为“,C+,程序设计”的行显示在查询结果中。,WHERE,子句中常用的查询条件如表4.1所示：,(1) 比较大小,【例4.15】 在,BookRecord,表中查询人民邮电出版社出版的图书的书名、单价。,SELECT,BookName,SalesPrice,FROM,BookRecord,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,可以自动将其转换对应的日期类型数据，如果想强制进行这一类型转换，可以使用,CAST,函数将该条件写成,InDate,=CAST（2004-12-31AS,DateTime,）。,【例4.17】 查询所有书价小于25元的图书的书号。,SELECT DISTINCT,BookNo,FROM,BookRecord,WHERE,SalesPrice,25;,(2),确定范围,BetweenAnd,表示数据在某一范围的条件，其使用格式为:,Between,And ,【例4.18】 查询单价在20至50元之间的图书的书名、作者、和出版社。,SELECT,BookName, Author, Publisher,FROM,BookRecord,WHERE,SalesPrice,BETWEEN 20 AND 50;,这里,SalesPrice,BETWEEN 20 AND 50,相当于,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】 查询清华大学出版社、人民邮电出版社出版的图书的书名、出版社。,SELECT,BookName, Publisher,FROM,BookRecord,WHERE Publisher IN (,清华大学出版社, 人民邮电出版社),与,IN,相对的谓词是,NOT IN，,用于查找属性值不属于指定集合的元组。,【例4.21】 查询不是清华大学出版社、也不是人民邮电出版社出版的图书的书名、出版社。,SELECT,BookName, Publisher,FROM,BookRecord,WHERE Publisher NOT IN (,清华大学出版社, 人民邮电出版社),(4) 字符匹配,谓词,LIKE,可以用来进行字符串的匹配，常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用 于,char、,varchar,、text、,ntext,、,datetime,和,smalldatetime,等类型查询。,其一般语法格式如下：,NOT LIKE ,ESCAPE ,其含义是查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符%和_。,百分号%： 可匹配任意类型和长度（长度可以为0）的字符串，如果是中文，请使用两个百分号即%。,下划线_： 匹配单个任意字符，它常用来限制表达式的字符长度。,方括号： 指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。,： 其取值也 相同，但它要求所匹配对象为指定字符以外的任一个字符。,例如：,限制以“出版社”结尾，使用,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 ,欧阳_ _;,【注意】 由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个_。,(5) 涉及空值的查询,在基本表中，如果那一列中没有输入数据，则它的值就为空，空值用一个特殊的数据,NULL,来表示，如果要判断某一列是否为空，不能用“=,NULL”,或“ ,NULL”,来比较，只能用,IS NULL,或,IS NOT NULL,来运算。,【例4.24】 查询没有打过折扣图书的书名、作者、单价、出版社。,SELECT,BookName, Author ，,SalesPrice,，Publisher,FROM,BookRecord,WHERE Discount IS NULL;,【,注意】 这里的,IS,不能用等号（=） 代替。,【例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,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,子句指定按照一个或多个属性列的升序（,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,SalesPrice,ASC;,用,ORDER BY,子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。,【例4.28】 查询图书情况，查询结果按出版社降序排列，对同一出版社出版的图书按单价升序排列。,SELECT *,FROM,BookRecord,ORDER BY Publisher DESC,SalesPrice,;,5使用集函数,为了进一步方便用户，增强检索功能，,SQL,提供了许多集函数，主要包括：,COUNT(DISTINCT|ALL *),统计元组个数,COUNT(DISTINCT|ALL ) 统计一列中值的个数,SUM(DISTINCT|ALL ) 计算一列值的总和（此列必须是数值型）,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,BookRecord,WHERE Publisher =,清华大学出版社;,【例4.31】 查询“清华出版社”出版的单价最高的图书的价格。,SELECT MAX(,SalesPrice,),FROM,BookRecord,WHERE Publisher =,清华大学出版社,;,6对查询结果分组,GROUP BY,子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。,对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。,【例4.32】 查询各个出版社出版的图书种类。,SELECT Publisher, COUNT(,BookNo,),FROM,BookRecord,GROUP BY Publisher ;,该,SELECT,语句对表,BookRecord,按,Publisher,的取值进行分组，所有具有相同,Publisher,值的元组为一组，然后对每一组作用集函数,COUNT,以求得该出版社出版的图书种类。假设查询结果为：,Publisher COUNT(,BookNo,),清华大学出版社 22,人民邮电出版社 34,中国水利出版社 44,如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用,HAVING,短语指定筛选条件。,【例4.33】 查询出版的图书种类超过20种的出版社。,SELECT Publisher, COUNT(,BookNo,),FROM,BookRecord,GROUP BY Publisher,HAVING COUNT（,BookNo,）20;,WHERE,子句与,HAVING,短语的根本区别在于作用对象不同。,WHERE,子句作用于基本表或视图，从中选择满足条件的元组。,HAVING,短语作用于组，从中选择满足条件的组。,4.3.2 连接查询,一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。,1等值与非等值连接查询,连接谓词：用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：,. .,其中比较运算符主要有：=、=、=、!=,此外连接谓词还可以使用下面形式：,.,BETWEEN .,AND .,当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。,连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比,但不必是相同。例如，可以都是字符型，或都是日期型；也可以一个是整型，另一个是实型，整型和实型都是数值型。,执行连接操作的过程是，首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部扫描完毕后，再到表1中找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1全部元组都处理完毕为止。,【例4.34】 查询每个操作员销售图书的情况。,每个操作员的姓名等基本情况放在操作员信息表(,Operator),中、图书销售的情况存放在图书销售记录信息表（,SalesRecord,）,中，所以本查询实际上同时涉及,operator,与,SalesRecord,两个表中的数据。这两个表之间的联系是通过两个表都具有的属性操作员账户,Account,实现的。要查询每个操作员销售图书的情况，就必须将这两个表中相同操作员账户的元组连接起来，这是一个等值连接。在操作时，在相同属性名的前面必须加上各自的表名，如,Operator.Account,和,SalesRecord,.Account，,完成本查询的,SQL,语句为：,SELECT Operator.*,SalesRecord,.*,FROM Operator,SalesRecord,WHERE Operator.Account =,SalesRecord,.Account;,连接运算中有两种特殊情况，一种称为笛卡儿积连接，另一种称为自然连接。,笛卡儿积：是不带连接谓词的连接。两个表的笛卡儿积即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼接，因此结果表往往很大，并且查询结果会出现许多无意义的行，所以这种运算很少使用。,自然连接：如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。,【例4.35】 自然连接,Operator,和,SalesRecord,表。,SELECT Operator. Account, Name， Password ,Isdeleted,Sn,，,SaleDate,SumMonet,FROM Operator,SalesRecord,WHERE Operator.Account =,SalesRecord,.Account;,自身连接（,Self join）,连接操作可以在同一张表内可进行自身连接，即将同一个表的不同行连接起来。自身连接可以看作一张表的两个副本之间的连接。在自身连接中，必须为表指定两个别名，使之在逻辑上成为两张表。,【例4.36】 在,BookRecord,表中查询同名的作者及其所著的图书书名。,SELECT a1. Author, a1.,BookName,FROM,BookRecord,a1 join,BookRecord,a2 on a1. Author =a2. Author,WHERE a1.,BookNO, a2.,BookNO,查询结果为：,Author,BookName,_,谭浩强,C+,程序设计,谭浩强,C,程序设计,3外连接,在通常的连接操作中，只有满足符合查询条件(,WHERE,搜索条件或,HAVING,条件)和连接条件的元组才能作为结果输出，这样的连接称作内连接。如果在实际应用中，也想同时输出那些不满足连接条件的元组，这时就需要使用外连接。,外连接常用的有两种：左外连接和右外连接，左外连接是在查询结果中包含连接表达式左边表中的不满足连接条件的元组，使用运算符“*=”；右外连接是在查询结果中包含连接表达式右边表中的不满足连接条件的元组，使用运算符“=*”。,这样，我们就可以如下改写【例4.35】,SELECT Operator. Account, Name， Password ,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,WHERE Operator.Account =,SalesRecord,.Account;AND,SalesRecord,.,SumMonet,3000;,连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。,【例4.38】 查询每个操作员及其销售的的图书其及金额。,SELECT Operator.Name，,SalesDetail,.,BookName,SalesRecord,.,SumMonet,FROM Operator,salesDetail,，,SalesRecord,WHERE Operator.Account =,SalesRecord,.Account AND,SalesRecord,.,Sn,=,salesDetail,.,Sn,4.3.3 嵌套查询,在,SQL,语言中，一个,SELECT-FROM-WHERE,语句称为一个查询块。将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语的条件中的查询称为嵌套查询, 又称子查询。其中处于内层的查询称为子查询，嵌套查询命令在执行时，每个子查询在上一级查询处理之前求解，即由里向外查，子查询的结果用于建立其父查询的查找条件。子查询是,SQL,语句的扩展，其语句形式如下：,SELECT ，.,FROM ,WHERE ,表达式 （,SELECT ,.,FROM ,表或视图名2）,GROUP BY ,HAVING 比较运算符 （,SELECT ,.,FROM ）,【例4.39】 查询“清华大学出版社”出版的图书已销售的书名、数量。,SELECT,BookName,，Quantity,FROM,SalesDetail,WHERE,BookNo,IN,SELECT,BookNo,FROM,BookRecord,WHERE Publisher =,清华大学出版社;,嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了,SQL,的查询能力。以层层嵌套的方式来构造程序正是,SQL(,Structurred,Query Language),中,“,结构化,”,的含义所在。,1带有,IN,谓词的子查询,带有,IN,谓词的子查询是指父查询与子查询之间用,IN,进行连接，判断父查询的某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词,IN,是嵌套查询中最经常使用的谓词。,【例4.40】 查询作者为“谭浩强”的图书的销售数量。,查询作者为“谭浩强”的图书的销售数量，可以首先查询所有“谭浩强”所编写的图书，然后在所有已销售的图书中查找作者是“谭浩强”的图书销售数量。可以分步来完成此查询：, 先查询所有“谭浩强”所编写的图书,SELECT,BookNo,FROM,BookRecord,WHERE Author =“,谭浩强”;,结果为：,BookNo,9787302778211,9787302778252, 在所有已销售的图书中查找作者是“谭浩强”的图书销售数量。,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 (SELECT,BookNo,FROM,BookRecord,WHERE Author =“,谭浩强”),可见，实现同一个查询可以多种方法，当然不同的方法其执行效率可能会有差别，甚至会差别很大，以上嵌套查询还可以用下面的连接查询来实现：,SELECT,BookName,，Quantity,FROM,SalesDetail,，,BookRecord,WHERE,SalesDetail,.,BookNo,=,BookRecord,.,BookNo,AND,BookRecord,.Author =“,谭浩强”,2带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、 =、等比较运算符。,【例4.41】 查询“人民邮电出版社”出版的并且单价小于该所有图书平均价格的图书。,由于所有图书平均价格的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其,SQL,语句如下：,SELECT,BookName,FROM,BookRecord,WHERE Publisher =,人民邮电出版社,AND,SalesPrice,ANY,大于子查询结果中的某个值 即表示大于查询结果中最小值,=,ANY,大于等于子查询结果中的某个值即表示大于等于结果集中最小值, ALL,大于子查询结果中的所有值 即表示大于查询结果中最大值,=,ALL,大于等于子查询结果中的所有值即表示大于等于结果集中最大值,=,ALL,小于等于子查询结果中的所有值即表示小于等于结果集中最小值,=,ALL,等于子查询结果中的所有值（通常没有实际意义）,!=,ALL,或,ALL,不等于子查询结果中的任何一个值即相当于,NOT IN,【,例4.42】 查询其他出版社中比“人民邮电出版社”任一图书单价都低的图书的书名、单价。,SELECT,BookName,，,SalesPrice,FROM,BookRecord,WHERE Publisher ,人民邮电出版社,AND,SalesPrice,ANY(SELECT,SalesPrice,FROM,BookRecord,WHERE Publisher =,人民邮电出版社,SELECT,BookName,，,SalesPrice,FROM,BookRecord,WHERE Publisher ,人民邮电出版社,AND,SalesPrice, (SELECT MAX(,SalesPrice,),FROM,BookRecord,WHERE Publisher =,人民邮电出版社,【例4.43】 查询其他出版社中比“人民邮电出版社”所有图书单价都低的图书的书名、单价。,SELECT,BookName,，,SalesPrice,FROM,BookRecord,WHERE Publisher ,人民邮电出版社,AND,SalesPrice,ALL(SELECT,SalesPric,FROM,BookRecord,WHERE Publisher =,人民邮电出版社,以上查询实际上也可以用集函数实现。先利用求最小值函数找出“人民邮电出版社”出版的图书中最低单价,然后在父查询中查找出所有单价比前面的最低单价都低并且不是“人民邮电出版社”出版的图书显示其书名，单价。,SELECT,BookName,，,SalesPrice,FROM,BookRecord,WHERE Publisher ,人民邮电出版社,AND,SalesPrice,100，,则取此,BookRecord,.,BookName,，,BookRecord,. Quantity,送入查询结果中。写成,SQL,语句就是：,SELECT,BookName,，Publisher,FROM,BookRecord,WHERE EXISTS ( SELECT *,FROM,SalesDetail,WHERE,SalesDetail,.,BookNo,=,BookRecord,.,BookNo,AND Quantity100);,使用存在量词,EXISTS,后，若内层查询结果非空，则外层的,WHERE,子句返回真值，否则返回假值。,由,EXISTS,引出的子查询，其目标列表达式通常都用*，因为带,EXISTS,的子查询只返回真值或假值，给出列名亦无实际意义。,这类查询与我们前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值（在本例中是依赖于,BookRecord,表的,BookNo,值），我们称这类查询为相关子查询（,Correlated,Subquery,）。,相关子查询的内层查询由于与外层查询有关，因此必须反复求值。从概念上讲，相关子查询的一般处理过程是：,首先取外层查询中,BookRecord,表的的第一个元组，根据它与内层查询相关的属性值（即,BookNo,值）处理内层查询， 若,WHERE,子句返回值为真（即内层查询结果非空），则取此元组放入结果表； 然后再检查,Student,表的下一个元组；重复这一过程，直至,BookRecord,表全部检查完毕为止。,本例中的查询也可以用连接运算来实现，可以参照有关的例子，自己给出相应的,SQL,语句。,与,EXISTS,谓词相对应的是,NOT EXISTS,谓词。使用存在量词,NOT EXISTS,后，若内层查询结果为空，则外层的,WHERE,子句返回真值，否则返回假值。,【例4.45】 查询当前没有销售图书的书名、出版社。,SELECT,BookName,，Publisher,FROM,BookRecord,WHERE NOT EXISTS ( SELECT *,FROM,SalesDetail,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,BookRecord,WHERE,BookRecord,.,BookNo,=,SalesDetai,.,BookNo,AND Author =“,谭浩强”),由于带,EXISTS,量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于不相关子查询，甚至有时是最高效的方法。,4.3.4 集合查询,把多个,SELECT,语句的结构完全相同的结果合并为一个结果，用集合操作来完成，这种查询称为集合查询，标准,SQL,集合操作只要并操作,UNION。,使用,UNION,将多个查询结果合并起来，形成一个完整的查询结果时，系统可以去掉重复的元组。需要注意的是，参加,UNION,操作的各结果表的列数必须相同；对应项的数据类型也必须相同。,UNION,的语法格式为：,(,Select,查询语句1,UNION ALL,（select,查询语句2）,ALL,选项表示将所有行合并到结果集合中。不指定该项时，被联合