《第3章 SQL数据操纵语言.ppt》由会员分享，可在线阅读，更多相关《第3章 SQL数据操纵语言.ppt（27页珍藏版）》请在金锄头文库上搜索。
1、第三章 SQL数据操纵语言,3.1 数据查询 3.2 数据更新 如没有特别说明本节的所有查询都在Northwind数据库中进行。,3.1 数据查询,3.1.1 SELECT语句结构 SELECT INTO 表名 FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC, ASC|DESC TOP PERCENT UNION ALL,3.1.2 简单查询,最简单的查询只由SELECT、FROM两个子句构成。 1 即指定字段列表表达式，每个之间用逗号分开，可以使用星号(*)检索所有列。 例1 查询所有产品的基本信息。 例2 查询所有供应商的名称、所在城市和州。 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”的所有产品信息。
3、 判断空值的查询条件 IS NOT NULL ISNULL()函数 例9 查询传真号为空值的所有供应商的信息。,1 使用AS更改列名 例10 查询所有订单的订单号、产品号和订购该产品的金额。 2使用ORDER BY排序数据 语法：ORDER BY 排序表达式1 ASCENDING | DESCENDING，排序表达式2 ASCENDING | DESCENDING 例11 对上例查询的结果按订单号排序，同一订单号的按金额降序排序。,3.1.4 格式化结果集,3 使用TOP返回指定行数 使用TOP N(N为整数)返回指定的行，或使用TOP N PERCENT返回满足查询条件的总记录的百分比。 例
4、12 查询单价最高的5种商品。,3.1.5 使用集函数,COUNT(* | DISTINCT | ALL)统计元组(*)或统计一列中值(列名)的个数 SUM(DISTINCT | ALL )计算一列值的总和(该列必须为数值型) AVG(DISTINCT | ALL )计算一列值的平均值(该列必须为数值型) MAX(DISTINCT | ALL )求一列值中的最大值 MIN(DISTINCT | ALL )求一列值中的最小值 如果指定了DISTINCT，则表示在计算时要取消指定列中的重复值。,例13 查询所有定单的总交易额。 例14 查询产品号为11的产品交易发生的次数和总交易额，平均、最高、最
5、低交易额。,3.1.6 对查询结果分组和筛选,1 分组查询结果 GROUP BY子句将查询结果进行分组，分组原则是按分组列名表的值相等的分为一组。分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，则集函数将作用于整个查询结果，即整个查询结果只有一个函数值 。 例15查询每个定单的交易额。 例16查询每种产品的交易发生的次数和交易额，交易量。,2 对分组结果筛选 使用HAVING短语，使返回的结果只包含满足条件的组。HAVING短语只能放在GROUP BY分组子句中，和分组一起使用。 例17查询交易额超过1000元的定单。 例18查询单次交易量超过10且交易次数超过20次的产品。 什么
6、时候该用HAVING筛选条件？其实很简单：要使用集函数的统计值的就应该用HAVING筛选，在WHERE子句不能使用集函数。,3.1.7联接查询,1 使用WHERE条件联接 例19查询1998年每个月每个员工接收订单的总额。 例20查询销往每个国家的总金额。 例21查询每份订单的公司名称、产品名称。 2 使用JOIN联接 FROM INNER | LEFT OUTER | RIGHT OUTER | FULLOUTERCROSS JOIN ON ,内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。语法： SELECT 列
7、FROM 表1 inner JION 表2 ON 表1.列=表2.列 试将上面几例改为内联接查询。,1内连接（Inner join）,2外联接（outer join),在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中可以只限制一个表，而对另外一个表不加限制（即所有的行都出现在结果集中）。 外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制；右外连接是对右边的表不加限制；全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。,例22查询所有客户的公司名及与其处于同一城市的供应商的公司名称。 交叉连接 交叉连接也叫非限制连接，它将两个表不加
8、任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。,3.1.8嵌套子查询,嵌套子查询的执行不依赖于外部嵌套。 嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。 嵌套子查询一般也分为两种：子查询返回单个值和子查询返回一个值列表。 （1）返回单个值，该值被外部查询的比较操作（如，= 、!=、=）使用，该值可以使子查询中使用集合函数得到的值。,例23查询单价高于平均价格的所有产品。 例24在pubs数据库查询书号为pc103
9、5的作者的作者号、作者姓名。 （2）返回一个值列表，该列表被外部查询的IN、NOT IN、ANY或ALL比较操作使用。 IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOT IN表示不属于。 例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含义相同。
10、 例27查询比最近三个订单中任何一个交易额高的订单号。,2相关子查询,在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的WHERE子句中引用了外部查询的表。 相关子查询的执行过程与嵌套子查询完全不同，嵌套子查询中子查询只执行一次，而相关子查询中的子查询需要重复地执行。相关子查询的执行过程如下： （1）子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询。 （2）如果子查询的任何行与其匹配，外部查询就返回结果行。,（3）再回到第一步，直到处理完外部表的每一行。 1Exists及Not Exists查询 例28查询没有任何订购的产品的信息。 例29查询所有由5号员
11、工处理的产品信息。 2计算相关子查询 例30列出累计销售额在50000元以上的产品信息。 例31查询每种产品的编号、名称、销售数量。,3.1.9合并结果集,关系的集合操作主要包括UNION(并)、INTERSECT(交)、MINUS(差) 和笛卡积，其中笛卡积通过交叉联接实现。在标准SQL语言中，并没有直接实现交、差运算的方法。 并集 例32查询所有客户和供应商的公司名称。 注意：使用Order by排序只能放在查询最后。 交集 例33查询曾经订购过20和60号产品的客户。,差集 例34查询曾经订购过20但从没有订购过60号产品的客户。,3.1.10在查询的基础上创建新表,使用SELECT I
12、NTO语句可以在查询的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。 例35查询每种产品的编号、销售数量并将结果保存在sales表中。,3.2 数据更新,3.2.1添加数据 1单记录添加 语法： INSERTINTO表名(列名列表） VALUES(值列表） 例36向SHIPPERS表中添加一条记录。 2从查询中批量添加 INSERTINTO表名(列名列表）SELECT查询 例37 新建一个表并将SUPPLIERS 及CUSTOMERS中的公司名称添加到新表中。,3.2.2修改数据,1简单修改语句 语法： UPDATE TABLE 表名 SET 列名值，列
13、名值WHERE条件 例38 将订单号为10248、产品号为11的订货数量改为20。 2使用子查询修改 UPDATE的子查询可以在SET和WHERE子句中使用。 例39 修改sales表添加一列销售总额，并修改数据。,3.2.3删除数据,1简单删除 语法： DELETEFROM表名WHERE条件 例40 从ORDER DETAILS表中删除10248的订单。 2使用子查询删除 DELETE的子查询只能使用在WHERE子句中。 例41 从ORDER DETAILS表中删除产品名称为Chang的产品的订单。,3 TRUNCATE TABLE 表名 删除表中所有行，但保留表结构，比DELETE执行速度快。,
