数据库基础教程(SQLServer平台)[顾韵华]第3章.ppt

第三章 关系数据库语言SQL,3.1 SQL概述3.2 SQL语言的数据类型 3.3 数据定义3.4 数据查询3.5 数据更新,数据库应用基础,3.1 SQL概述,3.1.1 SQL的特点 3.1.2 SQL基本概念 3.1.3 SQL语言的组成 3.1.4 SQL语句分类,3.1.1 SQL的特点,SQL的特点综合统一高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方法语言简洁，易学易用,3.1.2 SQL基本概念,SQL语言支持数据库的三级模式结构,基本表（Base Table）,基本表是独立存在于数据库中的表，是“实表”。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。,视图（View）,视图是从一个或几个基本表（或视图）导出的表，是“虚表”。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。,存储文件,数据库的所有信息都保存在存储文件中。数据库是逻辑的，存储文件是物理的。用户操作的数据库，实际上最终都是操作存储文件。一个基本表可以用一个或多个存储文件存储，存储文件的物理结构对用户是透明的。,表中的记录通常按其输入的时间顺序存放，这种顺序称为记录的物理顺序。为了实现对表记录的快速查询，可以对表文件中的记录按某个和某些属性进行排序，这种顺序称为逻辑顺序。索引即是根据索引表达式的值进行逻辑排序的一组指针，它可以实现对数据的快速访问。索引是关系数据库的内部实现技术，属于内模式，被存放在存储文件中。,索引,3.1.3 SQL语言的组成,（1）数据定义语言DDL（Data Definition Language）。定义数据库结构，包括定义表、视图和索引等。（2）数据操纵语言DML（Data Manipulation Language）。主要包括查询、插入、删除和修改数据库中数据的操作。（3）数据控制语言DCL（Data Control Language）。包括对数据库的安全性控制、完整性控制以及对事务的定义、并发控制和恢复等。,（1）数据定义。其功能是创建、更新和撤销模式及其对象。包含的语句动词主要有：CREATE、DROP、ALERT。（2）数据查询。其功能是进行数据库的数据查询。包含的语句动词主要有：SELECT。（3）数据操纵。其功能是完成数据库的数据更新。包含的语句动词主要有：INSERT、UPDATE、DELETE。（4）数据控制。其功能是进行数据库的授权、事务管理和控制。包含的语句动词主要有：GRANT、REVOKE、COMMIT、ROLLBACK等。,3.1.4 SQL语句分类,3.2 SQL语言的数据类型,SQL语言在定义表中各属性时，要求指明其数据类型和长度。SQL语言提供了一些基本数据类型，而不同RDBMS所支持的数据类型不完全相同，在使用时要注意具体的RDBMS规定。,T-SQL数据类型,T-SQL常用数据类型包括：数值型 字符型 Unicode字符型 文本型 日期时间类型 二进制型 货币类型,数值型包括整型（bigint、int、smallint、tinyint、bit）、定点实数（numericdecimal）、浮点数（float、real），各数值类型的取值范围、存储字节数都有差异（详见教材列表）。要注意，bit类型数据相当于其他语言中的逻辑型数据，它只存储0和1。当为bit类型数据赋值0时，其值为0；而赋非0（如100）值时，其值为1。,数值型,字符型,字符型数据用于存储字符串。字符串中可包括字母、数字和其他特殊符号（如#、&等），也可包含汉字。字符串型包括两类：定长字符串char和变长字符串varchar。,Unicode字符型,Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。SQL Server的Unicode字符型可以存储Unicode标准字符集定义的各种字符。Unicode字符型包括nchar(n)和nvarchar(n)两类。nchar是固定长度 Unicode 数据的数据类型，nvarchar 是可变长度 Unicode 数据的数据类型，二者均使用 UNICODE UCS-2 字符集。nchar、nvarchar与char、varchar使用非常相似，只是字符集不同（前者使用Unicode字符集，后者使用ASCII字符集）。,当需要存储大量的字符数据，如较长的备注、日志信息时，字符型数据最长8000个字符的限制可能使它们不能满足这种应用需求，此时可使用文本型数据。文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。,文本型,日期时间类型数据用于存储日期和时间信息，包括datetime和smalldatetime两类。datetime类型可表示从1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据。smalldatetime类型可表示从1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据。用户以字符串形式输入日期时间类型数据，系统也以字符串形式输出日期时间类型数据。用户给出日期时间类型数据值时，日期部分和时间部分分别给出。日期部分常用的几种格式如下：Oct 10 2009/*英文数字格式*/2009-10-10/*数字加分隔符*/20091010/*纯数字格式*/,日期时间类型,二进制型,二进制数据类型表示位数据流。binary(n)。固定长度的n字节二进制数据。n取值范围为 18000，默认为1。binary(n)数据的存储长度为 n+4 字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。varbinary(n)。n字节变长二进制数据。n取值范围为 18000，默认为1。varbinary(n)数据的存储长度为实际输入数据长度+4字节。image。用于存储大容量的、可变长度的二进制数据，介于02311(2147483647)字节之间。,货币类型,money和smallmoney是两个专用于货币的数据类型，它们用十进制数表示货币值。money的数据范围与bigint相同，不同的只是money型有4位小数。实际上，money型数据就是按照整数进行运算的，只是将小数点固定在末4位。smallmoney与int的关系就如同money与bigint的关系一样。当向表中插入money或smallmoney类型值时，必须在数据前面加上货币符号（$），并且数据中间不能有逗号（,）；若货币值为负数，需要在符号$的后面加上负号（）。例如，$15000.32，$680，$20000.9088都是正确的货币数据表示形式。,3.3 数据定义,3.3.1 模式定义 3.3.2 基本表定义 3.3.3 索引定义 3.3.4 视图定义,模式定义即定义一个存储空间。一个SQL模式由模式名、用户名或账号来确定。在这个空间中可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等。SQL3标准的模式定义语句是CREATE SCHEMA。但由于“模式”这个名称较抽象，多数RDBMS不采用该名词，而采用“数据库”这一名称。这个数据库概念将数据库视为许多对象的容器。在SQL标准中没有CREATE DATABASE语句，但多数SQL产品都支持CREATE DATABASE创建数据库的语句。,3.3.1 模式定义,(1)定义数据库,T-SQL定义数据库的基本格式为：CREATE DATABASE 说明：T-SQL语句通常还包含各种子句，如CREATE DATABASE语句包含ON子句、LOG ON子句等。SQL Server的大多数数据库操作都有两种方式：一是命令方式，二是界面方式。例如，定义数据库，既可采用这里介绍的CREATE DATABASE语句，也可通过SQL Server Management Studio界面操作实现。,(2)使用数据库,语句格式为：USE 使用USE语句将选择为当前操作的数据库。一旦选定，若不对操作的数据库对象加以限定，则其后命令均是针对当前数据库中的表或视图进行的。,(3)修改数据库,基本语句格式为：ALTER DATABASE 该语句可以对指定的数据库的数据文件和日志文件等进行修改。,(4)删除数据库,基本语句格式为：DROP DATABASE 删除数据库后，该数据库的所有对象均被删除，将不能再对该数据库做任何操作。,3.3.2 基本表定义,定义基本表的实质就是定义表结构及约束等。在T-SQL语句定义表之前，先要设计表结构，即确定表的名字、所包含的列名、列的数据类型、长度、是否可为空值、默认值情况、是否要使用以及何时使用约束、默认设置或规则以及所需索引的类型、哪里需要索引、哪些列是主码、哪些列是外码等。,“商品订购数据库”结构,数据库名：SPDG包括三个基本表：客户信息表（表名：KHB）商品信息表（表名：SPB）商品订购表（表名：SPDGB）,客户信息表（表名：KHB）,商品信息表（表名：SPB）,商品订购表（表名：SPDGB）,(1)定义基本表,定义基本表的的基本格式为：CREATE TABLE(，)在定义基本表的同时还可定义该表有关的完整性约束。其中列级完整性约束的作用范围仅限于该列，而表级完整性约束的作用范围是整个表。,列级完整性约束,NOT NULL 限制列取值不能为空。DEFAULT 指定列的默认值。UNIQUE 限制列的取值不能重复。CHECK 限制列的取值范围。PRIMARY KEY 指定本列为主码。FOREIGN KEY 指定本列为引用其他表的外码。格式为：FOREIGN KEY()REFERENCE(),【例】定义SPDGB数据库的三个基本表。,创建KHB的SQL语句：CREATE TABLE KHB(客户编号 char(5)PRIMARY KEY,客户名称 char(20)NOT NULL,出生日期 datetime,性别 char(2),所在省市 varchar(50),联系电话 varchar(12),备注 text),创建SPB的SQL语句：,CREATE TABLE SPB(商品编号 char(8)PRIMARY KEY,商品类别 char(20)NOT NULL,商品名称 varchar(50)NOT NULL,单价 float,生产商 varchar(50),保质期 datetime DEFAULT 2000-1-1,库存量 int,备注 text),创建SPDGB的SQL语句：,CREATE TABLE SPDGB(客户编号 char(5)NOT NULL,商品编号 char(8)NOT NULL,订购时间 datetime NOT NULL,数量 int,需要日期 datetime,付款方式 varchar(40),送货方式 varchar(50),PRIMARY KEY(客户编号，商品编号，订购时间),FOREIGN KEY(客户编号)REFERENCES KHB(客户编号),FOREIGN KEY(商品编号)REFERENCES SPB(商品编号),(2)修改基本表,ALERT TABLE语句用于更改基本表结构，包括增加列、删除列、修改已有列的定义等。该语句的基本格式为：ALTER TABLE ALTER COLUMN NULL|NOT NULL-修改已有列定义|ADD 约束-增加新列|DROP COLUMN-删除列|ADD CONSTRAINT-添加约束|DROP CONSTRAINT-删除约束,示例,【例】在表SPB中增加1个新列商品图片。ALTER TABLE SPBADD 商品图片 image【例】将表SPB中“保质期”列的数据类型改为smalldatetime。ALTER TABLE SPBALTER COLUMN保质期 smalldatetime【例】删除表SPB中“商品图片”列。ALTER TABLE SPBDROP COLUMN商品图片,(3)删除基本表,DROP TABLE语句用于删除基本表，其语法格式：DROP TABLE【例】删除表SPB的SQL语句为：DROP TABLE SPB删除一个表时，表的定义、表中的所有数据以及表的索引、触发器、约束等均被删除。注意：不能删除系统表和有外码约束所参照的表。,3.3.3 索引定义,在数据库中建立索引是为了提高数据查询速度。查询是数据库使用最频繁的操作，如何能更快地找到所需数据，是数据库的一项重要任务。,(1)索引的概念,(2)索引分类,聚簇索引（Clustered Index）对表的物理数据页中的数据按索引关键字进行排序，然后重新存储到磁盘上，即聚簇索引与数据是一体的。非聚簇索引（Nonclustered Index）具有完全独立于数据的索引结构，它不将物理数据页中的数据按索引关键字排序。,(3)建立索引,建立索引使用语句CREATE INDEX，其基本格式为：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX ON(ASC|DESC,ASC|DESC)其中，是要建立索引的基本表的名称。当索引建立在多个列上时，该索引称为复合索引。复合索引各列之间要用逗号（，）分隔，每个列后面还可以用ASC或DESC表示按索引值按升序或降序排列。默认排序方式为ASC（升序）。UNIQUE表示创建的是唯一索引。CLUSTERED用于指定创建聚簇索引，NONCLUSTERED用于指定创建非聚簇索引。默认创建的是非聚簇索引。,示例,【例】在KHB表的“客户编号”列上建立一个非聚簇索引KHBH_ind。CREATE INDEX KHBH_ind ON KHB(客户编号)【例】在SPB表的“商品编号”列上建立一个唯一的聚簇索引SPBH_ind。CREATE UNIQUE CLUSTERED INDEX SPBH_ind ON SPB(商品编号)【例】在SPDGB表上按“客户编号”升序、“商品编号”升序、“订购时间”降序建立一个唯一的非聚簇索引SPDG_ind。CREATE UNIQUE INDEX SPBH_ind ON SPDGB(客户编号 ASC,商品编号 ASC,订购时间 DESC),(4)删除索引,索引一经建立，就由DBMS自动使用和维护，无须用户干预。当不需要某个索引时，可使用DROP INDEX语句将其删除。DROP INDEX语句的格式为：DROP INDEX.【例】删除KHB表“客户编号”列的索引KHBH_ind。DROP INDEX KHB.KHBH_ind,3.3.4 视图定义,视图是从一个或多个基本表（或视图）导出的表。视图是一个虚表，数据库中只存储视图的定义，而不存放视图对应的数据，这些数据仍然存放在原来的基本表中。视图是数据库系统提供给用户以多种角度观察数据库中数据的重要机制。,(1)视图的概念,视图的优点,为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。屏蔽数据库的复杂性。用户不必了解复杂的数据库表结构，并且数据库表的更改也不影响用户对数据库的使用。简化用户权限管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，增加了安全性。便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，同样的数据只需存储一次。可以重新组织数据以便输出到其他应用程序中。,使用视图的注意事项,只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。不能在视图上建立索引。,(2)定义视图,CREATE VIEW语句用于创建视图，其基本格式为：CREATE VIEW(，)AS 其中，SELECT是SQL查询语句，表示从表中选择指定列构成视图的各个列。当列名省略时，表示SELECT取所有列。,示例,【例】创建视图KH_NJview，其内容为“江苏南京”的客户信息。CREATE VIEW KH_NJviewASSELECT*FROM KHB WHERE 所在省市=江苏南京【例】创建视图DG_NJview，其内容为“江苏南京”的“客户编号”及其订购的“商品编号”。CREATE VIEW DG_NJviewASSELECT a.*，商品编号FROM KHB a，SPDGB bWHERE a.所在省市=江苏南京 AND a.客户编号=b.客户编号,示例,【例】创建“江苏南京”订购了编号为“10010001”商品的所有客户的客户编号、客户姓名视图DG_NJview_2。CREATE VIEW DG_NJview_2(客户编号，客户姓名)ASSELECT 客户编号，客户姓名FROM DG_NJviewWHERE 商品编号=10010001,使用ALTER VIEW语句可修改视图的定义，该语句基本格式为：ALTER VIEW(，)AS,(3)修改视图,示例,【例】修改视图DG_NJview_2，使其内容是选购了编号为“30010001”的所有“江苏南京”客户的客户编号、客户姓名。ALTER VIEW DG_NJview_2 AS SELECT 客户编号，客户姓名 FROM DG_NJview WHERE 商品编号=30010001,删除视图的语句是DROP VIEW，其基本格式为：DROP VIEW 删除视图不会影响基本表的数据。但如果被删视图还导出了其他视图，则对由其导出的视图执行操作将会发生错误。【例】删除视图DG_NJview。DROP VIEW DG_NJview当视图DG_NJview被删除后，对由其导出的视图DG_NJview_2进行操作将会发生错误。,(4)删除视图,3.4 数据查询,3.4.1 SELECT语句结构 3.4.2 单表查询 3.4.3 连接查询 3.4.4 嵌套查询 3.4.5 集合查询 3.4.6 视图查询,3.4.1 SELECT语句结构,SELECT语句的基本格式如下：SELECT ALL|DISTINCT，FROM，WHERE-WHERE子句，指定查询条件 GROUP BY-GROUP BY子句，指定分组表达式 HAVING-HAVING子句，指定分组过滤条件 ORDER BY ASC|DESC-ORDER子句，指定排序表达式和顺序,*-选择当前表或视图的所有列|.*|.*|.*-选择指定的表或视图的所有列|列名 AS-选择指定的列|-选择表达式例如，以下是对KHB表的查询语句：SELECT 客户编号，客户姓名，联系电话 FROM KHB WHERE 所在省市=江苏南京,的定义,3.4.2 单表查询,1.选择列 2.选择行 3.对查询结果排序4.聚合函数5.对查询结果分组6.使用HAVING子句进行筛选,1.选择列,选择表中的部分或全部列形成结果表相当于：关系代数的投影运算,【例】查询SPB中的商品编号、商品名称和库存量。SELECT 商品编号，商品名称，库存量 FROM SPB,(1)选择表中指定的列,选择表中全部列，可在SELECT语句中指出各列的名称，更简便的方法是在指定列的位置上使用“*”。【例】查询SPB中的所有列。SELECT 商品编号，商品类别，商品名称，生产商，单价，保质期，库存量，备注 FROM SPB或者SELECT*FROM SPB,(2)选择表中全部列,(3)查询经过计算的值,使用SELECT对列进行查询时，不仅可以直接以列的原始值作为结果，而且还可以将对列值进行计算后所得的值作为查询结果，即SELECT子句可使用表达式作为结果。【例】将SPB中各商品的编号及其打8折后的单价输出。SELECT 商品编号，单价*0.8 FROM SPB,(4)更改结果列标题,当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列标题名。【例】查询KHB表中的客户编号、客户姓名和联系电话，结果中各列的标题分别指定为CNO、CNAME和TEL。SELECT 客户编号 AS CNO，客户姓名 AS CNAME，联系电话 AS TEL FROM KHB,(5)替换查询结果中的数据,在对表进行查询时，有时对所查询的某些列希望得到一种概念而不是具体数据。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式如下：CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 ELSE 表达式END,示例,SELECT 商品编号，商品名称，价格等级=CASE WHEN 单价 IS NULL THEN 尚未定价 WHEN 单价=20 AND 单价50 AND 单价=100 THEN 较高 ELSE 高 END FROM SPB,(6)去除重复行,一个表中本来并不完全相同的元组，当投影到指定的某些列上时，就可能变成相同的行了。可以用DISTINCT语句取消它们。【例】在SPDGB表中查询订购了商品的客户编号。SELECT 客户编号 FROM SPDGB,2.选择行,选择表中的部分或全部元组形成结果表相当于：关系代数的投影运算,查询满足条件的行通过WHERE子句实现。WHERE子句必须紧跟FROM子句之后。构成WHERE子句的条件表达式的运算符也称谓词。谓词包括：比较运算、指定范围、确定集合、字符匹配、空值比较和逻辑运算等几类。可以将多个判定运算的结果通过逻辑运算符再组成更为复杂的查询条件。,查询满足条件的元组,常用查询条件,SELECT语句的ORDER BY子句可用于对查询结果按照一个或多个列、表达式或序号进行升序（ASC）或降序（DESC）排列，默认值为升序（ASC）。ORDER BY子句的格式如下：ORDER BY ASC|DESC，ASC|DESC 当按多个列排序时，前面列的优先级高于后面的列。,3.对查询结果排序,【例】将KHB表中的所有客户按所在省市的汉语拼音顺序排序。SELECT*FROM KHB ORDER BY 所在省市【例】将KHB表中的所有客户按姓名的汉语拼音升序、再按年龄由小到大排序。SELECT*FROM KHB ORDER BY 客户姓名 ASC，出生日期 DESC,示例,SELECT子句表达式可以包含聚合函数（Aggregate Function，也称统计、组、集合或列函数），用来增强查询功能。聚合函数是指对集合操作但只返回单个值的函数。使用聚合函数须遵循以下规则：带有一个聚合函数的SELECT语句仅产生一行作为结果。不允许嵌套使用聚合函数。几种表达式形式可用作聚合函数的参数，但不能作为聚合函数本身。如果SELECT子句包含一个或多个聚合函数，则SELECT子句中的列规范仅发生在聚合函数内。,4.聚合函数,常用聚合函数,SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式如下：SUM|AVG(ALL|DISTINCT)【例】查询SPB表中所有商品的平均单价。SELECT AVG(单价)AS 平均单价 FROM SPB,SUM和AVG,MAX和MIN分别用于求表达式中所有项的最大值与最小值，语法格式如下：MAX|MIN(ALL|DISTINCT)【例】查询SPB表中最高和最低单价。SELECT MAX(单价)AS 最高单价,MIN(单价)AS 最低单价 FROM SPB,MAX和MIN,COUNT用于统计组中满足条件的行数或总行数，格式如下：COUNT(ALL|DISTINCT|*)【例】查询客户总数。SELECT COUNT(*)AS 客户总数 FROM KHB【例】查询订购了编号为“10010001”的商品的客户数。SELECT COUNT(*)AS 客户数 FROM SPDGBWHERE 商品编号=10010001,COUNT,SELECT语句的GROUP BY子句用于将查询结果表按某一列或多列值进行分组，值相等的为一组。对查询结果分组的主要目的是为了细化聚合函数的作用对象。GROUP BY子句的基本格式如下：GROUP BY 注意：使用GROUP BY子句后，SELECT子句列表中只能包含GROUP BY中指出的列或在聚合函数中指定的列。,5.对查询结果分组,【例】查询各种商品的订购客户数。SELECT 商品编号，COUNT(*)AS 订购客户数 FROM SPDGB GROUP BY 商品编号,示例,如果查询结果集在使用GROUP BY子句分组后，还需要按条件进一步对这些组进行筛选，最终只输出满足指定条件的组，那么可以使用HAVING子句来指定筛选条件。HAVING子句须与GROUP BY子句结合使用。HAVING子句的格式如下：HAVING,6.使用HAVING子句进行筛选,【例】查找订购客户数超过1的商品。SELECT 商品编号，COUNT(*)AS 订购客户数 FROM SPDGB GROUP BY 商品编号HAVING 订购客户数1【例】查找同一省市且在1975年以后出生、客户数不少于2的省市。SELECT 所在省市FROM KHB WHERE 出生日期1975-1-1GROUP BY 所在省市HAVING COUNT(*)=2,示例,3.4.3 连接查询,若一个查询同时涉及两个或两个以上的表，则称为连接查询。连接是二元运算，类似于关系代数中的连接操作。可以对两个或多个表进行查询，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。连接查询有两种形式：1.连接谓词 2.以JOIN关键字指定的连接,连接谓词又称连接条件，其一般格式如下：BETWEEN AND 其中，谓词主要有、=、!=、!。当谓词为“=”时，就是等值连接。若在目标列中去除相同的字段名，则为自然连接。可用逻辑运算符AND和OR来连接多个连接谓词，实现复杂条件的连接查询。,1.连接谓词,【例】查找SPDG数据库每个订购了商品的客户及其订单情况。SELECT KHB.*，SPDGB.*FROM KHB，SPDGB WHERE KHB.客户编号=SPDGB.客户编号【例】查找SPDG数据库每个订购了商品的客户及其订单情况，去除重复的列。SELECT a.*，b.商品编号，b.订购时间，b.数量，b.需要日期，b.付款方式，b.送货方式 FROM KHB a，SPDGB b WHERE a.客户编号=b.客户编号,示例,【例】查找SPDG数据库订购了编号为“10010001”商品的客户编号、姓名、所在省市及其联系电话。SELECT DISTINCT a.客户编号，客户姓名，所在省市，联系电话FROM KHB a，SPDGB bWHERE a.客户编号=b.客户编号 AND 商品编号=10010001【例】查找订购了“体育用品”类别商品的客户的客户编号、客户姓名、联系电话和所订购商品的需要日期，并按需要日期排序。SELECT DISTINCT a.客户编号，客户姓名，联系电话，需要日期FROM KHB a，SPB b，SPDGB cWHERE a.客户编号=c.客户编号 AND b.商品编号=c.商品编号 AND 商品类别=体育用品ORDER BY 需要日期,示例,【例】在KHB表中查询具有相同姓名的客户信息。SELECT KH1.*FROM KHB KH1，KHB KH2WHERE KH1.客户姓名=KH2.客户姓名 AND KH1.客户编号KH2.客户编号,示例,在FROM子句的扩展定义中：-INNER JOIN表示内连接-OUTER JOIN表示外连接,2.以JOIN关键字指定的连接,内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。其语法格式如下：FROM JOIN ON=【例】查找SPDG数据库每个订购了商品的客户及其订单情况。SELECT*FROM KHB INNER JOIN SPDGB ON KHB.客户编号=SPDGB.客户编号,(1)内连接,【例】用FROM的JOIN关键字表达下列查询：查询订购了商品编号为“10010001”的客户姓名及联系电话。SELECT DISTINCT 客户姓名，联系电话FROM KHB INNER JOIN SPDGB ON KHB.客户编号=SPDGB.客户编号WHERE 商品编号=10010001【例】用FROM的JOIN关键字表达下列查询：在SPDG数据库中查询订购了类别为“体育用品”的客户的客户编号、客户姓名、联系电话以及商品的需要日期。SELECT DISTINCT KHB.客户编号，客户姓名，联系电话，需要日期FROM KHB JOIN SPB JOIN SPDGB ON SPB.商品编号=SPDGB.商品编号ON KHB.客户编号=SPDGB.客户编号WHERE 商品类别=体育用品,示例,外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括三种：左外连接（LEFT OUTER JOIN），结果表中除了包括满足连接条件的行外，还包括左表的所有行；右外连接（RIGHT OUTER JOIN），结果表中除了包括满足连接条件的行外，还包括右表的所有行；完全外连接（FULL OUTER JOIN），结果表中除了包括满足连接条件的行外，还包括两个表的所有行。其中OUTER关键字均可省略。,(2)外连接,【例】查找所有客户情况，及他们订购商品的编号。若客户没有任何订购商品记录，也要包括其基本信息。SELECT KHB.*，商品编号FROM KHB LEFT JOIN SPDGB ON KHB.客户编号=SPDGB.客户编号【例】用右外连接实现上例的查询。SELECT KHB.*,商品编号FROM SPDGB RIGHT JOIN KHB ON KHB.客户编号=SPDGB.客户编号,示例,3.4.4 嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。在WHERE子句或HAVING子句所表示的条件中，可以使用另一个查询的结果（即一个查询块）作为条件的一部分。这种将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件查询称为嵌套查询。,在嵌套查询中，子查询的结果往往是一个集合，所以IN是嵌套查询中最常使用的谓词。IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式如下：NOT IN(子查询)当与的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。注意：IN和NOT IN子查询只能返回一列数据。,1.带IN谓词的子查询,【例】查找与“张小林”在同一个省市的客户情况。SELECT*FROM KHB WHERE 所在省市 IN(SELECT 所在省市 FROM KHB WHERE 客户姓名=张小林)执行：先执行子查询，产生一个结果表，再执行父查询。,示例,【例】查找未订购“食品”类商品的客户情况。SELECT*FROM KHBWHERE 客户编号 NOT IN(SELECT 客户编号 FROM SPDGB WHERE 商品编号 IN(SELECT 商品编号 FROM SPB WHERE 商品类别=食品),示例,比较子查询是指父查询与子查询之间用比较运算符进行关联。如果能够确切地知道子查询返回的是单个值，就可以使用比较子查询。这种子查询可认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，基本格式如下：|=|!=|!（子查询）,2.带比较运算符的子查询,【例】在SPDGB表中查找订购了商品编号为“10010001”的商品、且订购数量超过全表中该商品平均订购数的记录。SELECT*FROM SPDGBWHERE 商品编号=10010001 AND 数量(SELECT AVG(数量)FROM SPDGB WHERE商品编号=10010001),示例,【例】找出每个客户超过他订购商品平均数量的商品编号。SELECT 客户编号，商品编号 FROM SPDGB a WHERE 数量(SELECT AVG(数量)FROM SPDGB b WHERE b.客户编号=a.客户编号)说明：这是一个相关子查询，内层查询的条件：a.客户编号=b.客户编号，与外层查询有关。内层查询是求一个客户订购商品数量的平均值，至于要求的是哪个客户的平均值，是由外层查询当前正处理的元组来决定的。,示例,当子查询返回多个值时，若父查询需与子查询的返回结果进行比较，则须在比较运算符之后加上ALL（SOME）或ANY进行限制。格式如下：|=|!=|!ALL|SOME|ANY(子查询)ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，才返回TRUE，否则返回FALSE。ANY与SOME的限制含义相同，通常采用ANY，表示表达式只要与子查询结果集中的某个值满足比较关系时，就返回TRUE，否则返回FALSE。,3.带ALL(SOME)或ANY谓词的子查询,【例】查找比所有食品类的商品单价都低的商品信息。SELECT*FROM SPBWHERE 商品类别食品 AND 单价 ALL(SELECT 单价 FROM SPB WHERE 商品类别=食品),示例,【例】查找比某个食品类的商品单价低的商品信息。SELECT*FROM SPBWHERE 商品类别食品 AND 单价 ANY(SELECT 单价 FROM SPB WHERE 商品类别=食品),示例,EXISTS谓词用于测试子查询的结果是否为空表。若子查询的结果集不空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式如下：NOT EXISTS(子查询),4.带EXISTS谓词的子查询,【例】查找订购了编号为“10010001”商品的客户姓名。SELECT 客户姓名 FROM KHB a WHERE EXISTS(SELECT*FROM SPDGB b WHERE b.客户编号=a.客户编号 AND b.商品编号=10010001),示例,【例】查询至少订购了编号为“100006”的客户所订购的全部商品的客户编号。SELECT DISTINCT 客户编号 FROM SPDGB a WHERE NOT EXISTS(SELECT*FROM SPDGB b WHERE b.客户编号=100006 AND NOT EXISTS(SELECT*FROM SPDGB c WHERE c.客户编号=a.客户编号AND c.商品编号=b.商品编号),示例,3.4.5 集合查询,SELECT语句执行的结果是元组的集合，因此多个SELECT语句的结果集可以进行集合操作。集合操作主要包括：并（UNION）交（INTERSECT）差（EXCEPT）注意：这里的集合操作要求各SELECT的查询结果集列数必须相同，并且对应列的数据类型必须相同。,【例】查询订购了编号为“10010001”或“10020001”商品的客户的编号。SELECT 客户编号 FROM SPDGB WHERE 商品编号=10010001UNION SELECT 客户编号 FROM SPDGB WHERE 商品编号=10020001,示例,【例】查询单价小于50的商品与库存量大于20的商品的交集。SELECT 商品编号，商品类别，商品名称，单价，生产商，保质期，库存量 FROM SPB WHERE 单价20,示例,【例】查询单价小于50的商品与库存量大于20的商品的差集。SELECT 商品编号，商品类别，商品名称，单价，生产商，保质期，库存量 FROM SPB WHERE 单价20,示例,3.4.6 视图查询,对视图查询时，首先进行有效性检查，检查查询的表、视图是否存在。如果存在，那么从系统表中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行转换以后的查询。,视图KH_JS定义,视图KH_JS：所在省为“江苏”的客户信息。CREATE VIEW KH_JSASSELECT*FROM KHB WHERE 所在省市 LIKE 江苏%,视图LEFT_NUM定义