第3章 SQL数据操纵语言

第三章SQL数据操纵语言3.1数据查询3.2数据更新如没有特别说明本节的所有查询都在Northwind数据库中进行。3.1数据查询3.1.1SELECT语句结构SELECT<字段列表表达式>［INTO表名］FROM<表名或视图名列表>[WHERE<条件表达式>][GROUPBY<分组列表>[HAVING<筛选条件>]][ORDERBY<关键字表达式>[ASC|DESC][,<关键字表达式>[ASC|DESC]…]][TOP<数值表达式>[PERCENT]][UNION[ALL]<SELECT命令>]3.1.2简单查询

最简单的查询只由SELECT、FROM两个子句构成。1即指定字段列表表达式，每个之间用逗号分开，可以使用星号(*)检索所有列。例1查询所有产品的基本信息。 例2查询所有供应商的名称、所在城市和州。2使用DISTINCT|ALL消除重复行

例3查询哪些州有供应商。3.1.3使用WHERE子句指定行使用比较运算符>、<、=、>=、<=、!=、<>例4查询定购数量大于10的定单信息。逻辑运算符AND、OR、NOT例5查询要求到货日期在98-5-6或者是98-5-10号的定单信息。使用[NOT]BETWEEN…AND…BETWEEN…AND…是一个闭区间。例6查询单价在15到20之间的所有产品的信息。使用[NOT]IN例7查询所在州为‘TN’，‘OR’，‘MI’之一的供应商的信息。使用[NOT]LIKE通配符%，代表任意数量的任意字符，？代表一个任意字符。 例8查询产品名中含有“Tofu”的所有产品信息。判断空值的查询条件

IS［NOT］NULL ISNULL()函数例9查询传真号为空值的所有供应商的信息。1使用AS更改列名 例10查询所有订单的订单号、产品号和订购该产品的金额。2使用ORDERBY排序数据

语法：ORDERBY排序表达式1[ASCENDING|DESCENDING][，排序表达式2[ASCENDING|DESCENDING]…]

例11对上例查询的结果按订单号排序，同一订单号的按金额降序排序。3.1.4格式化结果集3使用TOP返回指定行数

使用TOPN(N为整数)返回指定的行，或使用TOPNPERCENT返回满足查询条件的总记录的百分比。 例12查询单价最高的5种商品。

3.1.5使用集函数COUNT(*|[DISTINCT|ALL]<列名>)统计元组(*)或统计一列中值(列名)的个数SUM([DISTINCT|ALL]<列名>)计算一列值的总和(该列必须为数值型)AVG([DISTINCT|ALL]<列名>)计算一列值的平均值(该列必须为数值型)MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值如果指定了DISTINCT，则表示在计算时要取消指定列中的重复值。例13查询所有定单的总交易额。例14查询产品号为’11’的产品交易发生的次数和总交易额，平均、最高、最低交易额。3.1.6对查询结果分组和筛选1分组查询结果

GROUPBY子句将查询结果进行分组，分组原则是按分组列名表的值相等的分为一组。分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，则集函数将作用于整个查询结果，即整个查询结果只有一个函数值。

例15查询每个定单的交易额。 例16查询每种产品的交易发生的次数和交易额，交易量。2对分组结果筛选

使用HAVING短语，使返回的结果只包含满足条件的组。HAVING短语只能放在GROUPBY分组子句中，和分组一起使用。 例17查询交易额超过1000元的定单。 例18查询单次交易量超过10且交易次数超过20次的产品。 什么时候该用HAVING筛选条件？其实很简单：要使用集函数的统计值的就应该用HAVING筛选，在WHERE子句不能使用集函数。3.1.7联接查询1使用WHERE条件联接

例19查询1998年每个月每个员工接收订单的总额。 例20查询销往每个国家的总金额。 例21查询每份订单的公司名称、产品名称。2使用JOIN联接

FROM<数据源表或视图>[[INNER|LEFTOUTER|RIGHTOUTER|FULL

OUTER｜CROSS]JOIN<表名>ON<联接条件>…]

内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。语法：

SELECT列

FROM表1[inner]JION表2 ON表1.列=表2.列 试将上面几例改为内联接查询。1．内连接（Innerjoin）2外联接（outerjoin)在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中可以只限制一个表，而对另外一个表不加限制（即所有的行都出现在结果集中）。外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制；右外连接是对右边的表不加限制；全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。例22查询所有客户的公司名及与其处于同一城市的供应商的公司名称。交叉连接交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。3.1.8嵌套子查询嵌套子查询的执行不依赖于外部嵌套。嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。嵌套子查询一般也分为两种：子查询返回单个值和子查询返回一个值列表。（1）返回单个值，该值被外部查询的比较操作（如，=、!=、<、<=、>、>=）使用，该值可以使子查询中使用集合函数得到的值。

例23查询单价高于平均价格的所有产品。 例24在pubs数据库查询书号为pc1035的作者的作者号、作者姓名。

（2）返回一个值列表，该列表被外部查询的IN、NOTIN、ANY或ALL比较操作使用。IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOTIN表示不属于。 例25查询销售总金额前5名的产品信息。

例26查询地区为‘SP’的客户订购的产品信息。ANY、SOME和ALL用于一个值与一组值的比较，以“>”为例，ANY表示大于一组值中的任意一个，ALL表示大于一组值中的每一个。比如，>ANY(1,2,3)相当于大于MIN（1，2，3）；而>ALL(1,2,3)相当于大于MAX（1，2，3）。SOME在SQL-92标准中与ANY含义相同。

例27查询比最近三个订单中任何一个交易额高的订单号。2．相关子查询在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的WHERE子句中引用了外部查询的表。相关子查询的执行过程与嵌套子查询完全不同，嵌套子查询中子查询只执行一次，而相关子查询中的子查询需要重复地执行。相关子查询的执行过程如下：（1）子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询。（2）如果子查询的任何行与其匹配，外部查询就返回结果行。（3）再回到第一步，直到处理完外部表的每一行。

1

Exists及NotExists查询 例28查询没有任何订购的产品的信息。 例29查询所有由‘5’号员工处理的产品信息。

2计算相关子查询 例30列出累计销售额在50000元以上的产品信息。 例31查询每种产品的编号、名称、销售数量。3.1.9合并结果集

关系的集合操作主要包括UNION(并)、INTERSECT(交)、MINUS(差)和笛卡积，其中笛卡积通过交叉联接实现。在标准SQL语言中，并没有直接实现交、差运算的方法。并集

例32查询所有客户和供应商的公司名称。 注意：使用Orderby排序只能放在查询最后。交集 例33查询曾经订购过20和60号产品的客户。差集 例34查询曾经订购过20但从没有订购过60号产品的客户。3.1.10在查询的基础上创建新表使用SELECTINTO语句可以在查询的基础上创建新表，SELECTINTO语句首先创建一个新表，然后用查询的结果填充新表。例35查询每种产品的编号、销售数量并将结果保存在sales表中。3.2数据更新3.2.1添加数据1单记录添加语法：INSERT

INTO表名[(列名列表）]VALUES(值列表）例36向SHIPPERS表中添加一条记录。2从查询中批量添加INSERT

INTO表名[(列名列表）]

SELECT查询例37新建一个表并将SUPPLIERS及CUSTOMERS中的公司名称添加到新表中。3.2.2修改数据1简单修改语句语法：UPDATETABLE表名SET列名＝值[，列名＝值…］[WHERE条件］例38将订单号为10248、产品号为11的订货数量改为20。2使用子查询修改UPDATE的子查询可以在SET和WHERE子句中使用。例39修改sales表添加一列销售总额，并修改数据。3.2.3