模块1-4 商务网站后台数据库的查询与优化

模块1-4 商务网站后台数据库的查询与优化 模块4 商务网站后台数据库的查询与优化 【学习目标】 最终目标:能够根据需要灵活、快速地查询商务网站后台数据库的数据。 促成目标: 1(熟悉各种查询方法，为数据库应用系统的开发奠定基础。 2(能进行投影查询、连接查询、选择查询的操作 3(能进行分组统计查询、限定查询、排序查询、保存查询的操作。 4(培养积极、自信和严谨的工作作风，锻炼开动脑筋解决问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 的能力。 【学习导航】 【学习任务】 任务1 完成对数据库中某一个表的信息的基本查询操作 -1】网站销售部管理人员或采购部管理人员需要了解所有商品的详细 【任务1 信息。 【知识解析】 数据库查询是数据库系统中最基本的也是最重要的操作。 1(Select语句的语法 Select语句主要用于查询数据，也可以用来向局部变量赋值。常用的Select语句的用法为: SELECT的语法格式 SELECT 语句的基本语法格式如下: SELECT <字段列表> [INTO 新表名] FROM <表名/视图名列表> [WHERE 条件表达式] [GROUP BY 列名列表] [HAVING 条件表达式] [ORDER BY 列名1[ASC|DESC]，列名2[ASC|DESC]， (((，列名n[ASC|DESC]] [COMPUTE 行聚合函数名(统计表达式)[ ,,n] [BY 分类表达式[,,n]]] 其中各子名说明如下: (1)字段列表用于指出要查询的字段，也就是查询结果中包含的字段的名称。 (2)INTO子句用于创建一个新表，并将查询结果保存到这个新表中。 (3)FROM子句用于指出所要进行查询的数据来源，即来源于哪些表或视图的名称。 (4)WHERE子句用于指出查询数据时要满足的检索条件。 (5)GROUP BY子句用于对查询结果分组。 (6)ORDER BY子句用于对查询结果排序。 2(SELECT语句的功能 从FROM列出的数据源表中，找出满足WHERE检索条件的记录，按SELECT子句的字段列表输出查询结果表，在查询结果表中可进行分组与排序。 说明:在SELECT语句中SELECT子句与FROM子句是不可少的，其余的是可选的。 3(基本的SELECT语句 SELECT语句的基本形式如下: 【任务1-2】网站管理人员在了解商品信息时只需要了解所有商品的商品号、 商品名称和商品单价。 SELECT <字段列表> FROM <表名列表> [WHERE 查询条件] 【技术小提示】 子句中的【<目标列表达式>】中各个列的先后顺序可以与表中的顺序 不一致。 用户在查询时可以根据需要改变列的显示顺序，但不改变表中列的原始顺序。 【知识解析】 1(查询表中全部列(*) 将表中所有属性都选出来，可以有两种方法。一种方法是在SELECT命令后面列出所有列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<字段列表>简写为“*”。 2(设置字段别名 T-SQL提供了在SELECT语句中操作别名的方法。用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时标题。其语法格式为: 列表达式 [as] 别名 或:别名=列表达式 3(查询经过计算的值 SELECT子句的<字段列表>不仅可以是表中的属性列，也可以是表达式，包括字符串常量、函数等。其语法格式为: 计算字段名=表达式 4(返回全部记录(ALL) 要返回所有记录可在SELECT后使用ALL，ALL是默认设置，因此也可以省略。 5(过滤重复记录(DISTINCT) 如果让重复行只显示一次，需在SELECT子句中用DISTINCT指定在结果集中只能显示唯一行。 6(仅返回前面若干条记录 其语法格式如下: TOP n PERCENT] 列名l [，(((n] SELECT [TOP n , FROM 表名 其中: TOP n表示返回最前面的n行，n表示返回的行数。 TOP n PERCENT表示返回的最前面的n,行。 7(使用INTO子句 使用INTO子句允许用户定义一个新表，并且把SELECT子句的数据插入到新表中，其语法格式如下。 SELECT <字段列表> INTO 新表名 FROM <表名列表> WHERE 查询条件 使用INTO子句插入数据时，应注意以下几点: (1)新表不能存在，否则会产生错误信息。 (2)新表中的列和行是基于查询结果集的。 (REATE TABLE权限。 3)使用该子句必须在目的数据库中具有C (4)如果新表名称的开头为“#”，则生成的是临时表。 【知识小提示】 使用INTO子句，通过在WHERE子句中FALSE条件，可以创建一个和源表结构 相同的空表。 【任务1-3】在Goods表中存储有商品数量和商品单价，现在需要了解所有商品的商品号、商品名称和商品总额。 【技术小提示】 该语句中【<目标列表达式>】中第3项不是通常的列名，而是一个计算表达式， 是商品单价与商品数量的乘积，所得的积是商品的总价值。 计算列不仅可以是算术表达式，还可以是字符串常量、函数等。 【任务1-4】要求了解所有商品的商品号、商品名称和总价值，但希望以汉字标题商品号、商品名称和总价值表示g_ID、g_Name和g_Price*g_Number。 【技术小提示】 用户可以通过指定别名来改变查询结果的列标题，这在含有算术表达式、常量、函数名的列分隔目标列表达式时非常有用。 有三种方法指定别名: 通过“列名 列标题”形式; 通过“列名 AS 列标题”形式; 通过“列标题=列名”形式。 【课堂实践1】 操作要求: (1)查询E-SHOP数据库中会员信息表Customers中的所有内容。 (2)查询E-SHOP数据库中会员信息表Customers中会员的编号(c_ID)、用户名(c_Name)、真实姓名(c_TrueName)和密码(c_Password)。 (3)查询E-SHOP数据库中会员信息表Customers中会员的编号(c_ID)、用户名(c_Name)、真实姓名(c_TrueName)、年龄(c_Age)和密码(c_Password)。 (4)查询E-SHOP数据库中会员信息表Customers中会员的编号(c_ID)、用户名(c_Name)、真实姓名(c_TrueName)、年龄(c_Age)和密码(c_Password)并以汉字标题显示列名。 【任务1-5】需要了解所有商品中的“热点”商品的所有信息。 【知识解析】 1(使用WHERE子句 其语法格式如下: SELECT 列名1[,,列名n] FROM 表名 WHERE 条件表达式 使用WHERE子句可以限制查询的记录范围。在使用时，WHERE子句必须紧跟在FROM子句后面。WHERE子句中的条件是—个逻辑表达式，其中可以包含的运算符见表6-1。 (1)比较表达式作查询条件 比较表达式是逻辑表达式的一种，使用比较表达式作为查询条件的一般表达形式是: 表达式 比较运算符 表达式 其中: 表达式为:常量、变量和列表达式的任意有效组合。 比较运算符包括:=(等于)、<(小于)、>(大于)、<>(不等于)、!>(不大于)、!<(不小于)、>=(大于等于)、<=(小于等于)、!=(不等于)。 (2)逻辑表达式作查询条件 使用逻辑表达式作为查询条件的一般表达形式是: 表达式1 AND|OR表达式2， 或NOT 表达式 (3)使用(NOT)BETWEEN,AND关键字 其语法格式为: 表达式 [NOT] BETWEEN 表达式l AND 表达式2 谓词可以用来查找属性值在(或不在)指定范围“%”通配符能匹配0个或多个任意长度的字符串。 需要注意的是，以上所有通配符都只有在LIKE子句中才有意义，否则通配符 会被当作普通字符处理。 通配符和字符串必须括在单引号中。要查找通配符本身时，需将它们用方括号 括起来。例如:LIKE „ [[]?表示要匹配“[”。 (6)涉及空值的查询 对于空值(NULL)要用IS 进行连接，不能用“=”代替。 【任务1-6】需要了解商品类别为“01”，商品单价在2500元以上的商品信息，要求以 -7】需要了解“湖南”省的所有男性的会员或者是年龄在30岁以下的会 【任务1 员的会员号、会员名称、性别、籍贯和年龄。 【任务1-8】需要了解所有年龄在20,25岁之间的会员的名称、籍贯和年龄(用Nl表示，不是基本表中的字段，是计算出来的列)。 【任务1-9】需要了解所有年龄不在20,25岁之间的会员的名称、籍贯和Nl(同【任务1-8】)。 【任务1-10】需要了解来自“湖南株洲”和“湖南长沙”两地会员的详细信息。 【任务1-11】需要了解家庭地址不是“湖南株洲”和“湖南长沙”的商品的详细信息。 【任务1-12】需要了解所有商品中“三星”的商品的详细信息。 【任务1-13】需要了解姓“黄”且名字中只有两个汉字的会员的会员名、真实姓 名、电话和电子邮箱。 【技术小提示】 如果在“黄”后用两个“_”，则姓“黄”的名字为两个汉字和三个汉字的商品 将都被查询出来。 【任务1-14】知道一个商品的商品名称中包含有“520”字样，要求查询该商品的商品号、商品名称、商品单价和商品折扣。 【技术小提示】 如果用户要查询的匹配字符串本身就含有“%”或“_”，比如要查询名字为“三 星Cdmaix_008”的商品的信息，这时就要使用“ESCAPE”关键字对通配符进行转义。 \”短语表示“\”为换码字符，这样匹配串中紧跟在“\”后面的字 符“_”不再具有通配符的含义，而是取其本身含义，即普通的“_”字符。 【任务1-15】查询暂时没有商品图片的商品信息。 【技术小提示】 这里的“IS”不能用等号(“=”)代替。IS NULL表示空，IS NOT NULL表示非 空。 这里的NULL值是抽象的空值，不是0，也不是空字符串，如果用户将已有的 商品的图片信息删除，则为空字符串，而非NULL值。 任务2 使用ORDER BY 子句查询数据 【任务2-1】需要了解商品类别号为“01”的商品的商品号、商品名称和商品单价，并要求根据商品的价格进行降序(价格由高到低)排列。 【技术小提示】 子句中的【<目标列表达式>】中各个列的先后顺序可以与表中的顺序 不一致。 用户在查询时可以根据需要改变列的显示顺序，但不改变表中列的原始顺序。 【知识解析】 1(使用ORDER BY子句 对查询的结果进行排序，通过使用ORDER BY子句实现。 语法格式如下: ORDER BY表达式1 [ASC,DESC][,,n]] 其中，表达式给出排序依据，即按照表达式的值升序(ASC)或降序(DESC)排列查询结果。在默认的情况下，ORDER BY按升序进行排列，即默认使用的是ASC关键字。 不能按ntext、text或image类型的列排序，因此ntext、text或image类型的列 不允许出现在ORDER BY子句中。 2(SELECT语句的统计功能 为了进一步方便用户，增强检索功能，SELECT语句中的统计功能可以对查询结果集进 行求和、求平均值、求最大最小值等操作。统计的方法是通过集合函数和GROUP BY子句、COMPUTE子句进行组合来实现。 (1)使用集合函数 汇总查询是把存储在数据库中的数据作为一个整体，对查询结果得到的数据集合进行汇总或求平均值等各种运算。SQL Server提供了一系列统计函数，用于实现汇总查询。常用的统计函数见表6-2。 表6-2常用的聚合函数 【任务2-2】需要了解价格在2500元以上的商品的商品号、商品名称、商品类别号和商品单价信息，并要求按类别号升序排列;如果是同一类别的商品，则按价格降序排列。 【任务2-3】需要了解每一类别的商品总数。 【任务2-4】需要了解每一个订单的总金额，并根据订单总额进行升序排列。 【任务2-5】需要了解订单总额大于5000的订单信息，并按升序排列。 【技术小提示】 在使用分组时要显示的列要么包含在聚合函数中，要么包含在GROUP BY子句中，否则不能被显示。 WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作 用于基本表或视图，从中选择满足条件的记录。HAVING短语作用于组，从中选择满足条件的组。 COMPUTE子句需要提供下列信息: (1)可选BY关键字，它基于每一列计算指定的行聚合。 (2)行聚合函数名称，包括SUM、AVG、MIN、MAX或COUNT。 (3)要对其执行聚合函数的列。 【任务2-6】需要了解OrderDetails表中的单价和数量的总和。 【技术小提示】 提供COMPUTE和COMPUTE BY是为了向后兼容。SQL Server 2005中可以使用ROLLUP运算符。 在COMPUTE或COMPUTE BY子句中，不能包含ntext、text或image数据类型。 GROUP BY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数 COMPUTE 生成多个结果集。一种结果集包含每个组的明细行，其中包含选择列表中的表达式。另一种结果集包含组的子聚合，或SELECT语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其他表达式。聚合函数在COMPUTE子句中指定，而不是在选择列表中指定。 【任务2-7】既需要了解每一类送货方式产生的总金额，也需要了解每一个员工处理的订单的总金额。 【技术小提示】 第1行:送货方式为“送货上门”，订单处理员工为“E0001”的总金额。 第2行:送货方式为“送货上门”，订单处理员工为“E0003”的总金额。 第3行:送货方式为“送货上门”，所有处理员工处理的订单的总金额。e_ID为NULL，表示所有员工;o_SendMode为NULL，表示所有送货方式。 “E0004”的总金额。 第4行:送货方式为“邮寄”，订单处理员工为 第5行:送货方式为“邮寄”，所有处理员工处理的订单的总金额。 第6行:所有送货方式、所有处理员工处理的订单总金额。e_ID为NULL，表示所有员工。 第7行:所有送货方式、订单处理员工为“E0001”的总金额。o_SendMode为NULL ，表 示所有送货方式。 第8行:所有送货方式、订单处理员工为“E0003”的总金额。o_SendMode为NULL，表示所有送货方式。 第9行:所有送货方式、订单处理员工为“E0004”的总金额。o_SendMode为NULL，表示所有送货方式。 【任务2-8】在订单表中按订单总额(o_Sum)进行排序，需要显示其中指定的第3,5条记录，通过这种方法实现分页。 【技术小提示】 返回结果集分区内行的序列号，每个分区的第一行从 1 开始。 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。 【任务2-9】在订单表中按订单总额进行排序，需要显示其中指定的第3,5条记录的排名情况。 【知识解析】 1(使用GROUP BY子句 其语法格式如下。 GROUP BY 列名 [HAVING 条件表达式] HAVING 条件表达式选项是对生成的组进行筛选。 【知识小提示】 子句是对表中的记录进行筛选，而HAVING子句是对组内的记录进行筛 选，在HAVING 子句中可以使用集合函数，并且其统计运算的集合是组内的所 有列值，而WHERE子句中不能使用集合函数。 2(使用COMPUTE子句 使用COMPUTE BY子句，它对BY后面给出的列进行分组显示，并计算该列的分组小计。其语法格式如下: [BY 分类表达式] COMPUTE 集合函数 注意: (1)COMPUTE或COMPUTE BY子句中的表达式，必须出现在选择列表中，并且必须将其指定为与选择列表中的某个表达式完全一样，不能使用在选择列表中指定的列的别名。 (2)在COMPUTE或COMPUTE BY子句中，不能指定为ntext、text和image数据类型。 (3)如果使用COMPUTE BY，则必须也使用ORDER BY子句。表达式必须与在ORDER BY后列出的子句相同或是其子集，并且必须按相同的序列。例如，如果ORDER BY子句是:ORDER BY a,b,c 则COMPUTE子句可以是下面的任意一个(或全部)，即: COMPUTE BY a,b,c COMPUTE BY a,b COMPUTE BY a (4)在SELECT INTO语句中不能使用COMPUTE。因此，任何由COMPUTE生成的计算结果不出现在用SELECT INTO语句创建的新表内。 【课堂实践3】 操作要求: (1)对会员信息表Customers按年龄进行降序排列。 (2)对会员信息表Customers按会员类型(c_Type)进行升序排列，类型相同的按年龄进行降序排列。 (3)统计Customers表中男、女会员的总人数。 (4)统计Orders表中每一会员的订单总额，并显示大于平均总额的会员编号和订单总额。 (5)统计Orders表中每一天的订单总额，并根据订单总额进行降序排列。 (6)使用COMPUTE语句统计产生订单的天数和订单总额。 任务3 对数据库中的多表进行查询 【任务3-1】需要了解每个商品的商品号、商品名称和商品类别名称。 【技术小提示】 由于g_ID、g_Name和 t_Name属性列在Goods与Types表中是唯一的， 因此 使用时可以去掉表名前缀。 在两个表都出现了，因此引用时必须加上表名前缀。该查询的执行结果 不再出现Types.t_ID列。 【知识解析】 1(SELECT语句中的多表连接 在实际工作中，我们所查询的内容往往是涉及多张表中的内容。连接查询的目的是通过加载连接字段条件将多个表连接起来，以便从多个表中检索用户所需要的数据。在SQL Server中连接查询类型分为交叉连接、内连接、外连接、自连接。连接查询就是关系运算的连接运算，它是从多个数据源间(FORM)查询满足—定条件的记录。 2(交叉连接 交叉连接也叫非限制连接，它是将两个表不加任何约束地组合起来。也就是将第一个表的所有行分别与第二个表的每行形成一条新的记录，连接后该结果集的行数等于两个表的行数积，列数等于两个表的列数和。在数学上，就是两个表的笛卡尔积，在实际应用中一般是没有意义的，但在数据库的数学模式上有重要的作用。 其语法结构如下: SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 或者 SELECT 列名列表 FROM 表名1, 表名2 3(ON 表名1.列名=表名2.列名 或 SELECT 列名列表 FROM 表名1, 表名2 WHERE 表名1.列名=表名2.列名 4(外连接 在自然连接中，只有在两个表中匹配的才能在结果集中出现。而在外连接中可以只限制一个表，而对另外一个表不加限制(即另外一个表中的所有行都出现在结果集中)。 外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制;右外连接是对右边的表不加限制;全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。 (1)左外连接 SELECT 列名列表 FROM 表名1 AS A LEFT [OUTER] JOIN 表名2 AS B ON A. 列名=B. 列名 (2)右外连接 其语法格式如下: SELECT 列名列表 FROM 表名1 AS A RIGHT [OUTER] JOIN 表名2 AS B ON A. 列名=B. 列名 (3)全外连接 其语法格式如下: SELECT 列名列表 FROM 表名1 AS A FULL [OUTER] JOIN 表名2 AS B ON A. 列名=B. 列名 5(自连接 连接操作不仅可以在不同的表上进行，也可以在同一张表内进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。表名在FROM 子句中出现两次，必须对表指定不同的别名，在SELECT 子句中引用的列名也要使用表的别名进行限定，使之在逻辑上成为两张表。 (1)启动SQL Server Management Studio，在“对象资源管理”管理中依次展开【数据库】、【E-SHOP】数据库节点。 (2)右键单击【数据库关系图】，选择【新建数据库关系图】，如图5-32所示。 (3)选择要创建关系的表(这里选择所有的表)，如图5-33所示。 (4)调整好各个表的位置，单击【保存】按钮，在弹出的对话框中输入“mainRelation”,就创建好了关系。通过该关系图，可以方便地了解表之间的关系，如图5-34所示。 【任务3-2】需要了解所有订单中订购的商品信息(商品名称、购买价格和购买数量)和订单日期。 【技术小提示】 如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性 列，但保留了所有不重复的属性列，则称之为自然连接。 自行比较使用JOIN连接和使用WHERE连接的异同。 【知识解析】 (二)非等值连接 <、>=、<=等运算符作为连接条件的连接称为非等值连接。 使用>、 (三)自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。 【任务3-3】需要了解不低于“三星SGH-C178”价格的商品号、商品名称和商品单价，查询后的结果要求按商品单价升序排列。 【技术小提示】 因为是对同一个表进行连接操作，所以用别名G1和G2代表同一个表 如果将上述语句中SELECT子句中的G2换成G1，将会出现如下图Goods。 所示的错误 查询结果。 【任务3-4】需要了解所有商品类别及其对应商品信息，如果该商品类别没有对应商品也需要显示其类别信息。 【任务3-5】需要了解所有订单所订购的商品信息和订单日期。 【任务3-6】需要了解所有商品的信息(即使是不存在对应的商品类别信息，实际上这种情况是不存在的)。 【任务3-7】需要了解所有商品的基本信息和类别信息。 【任务3-8】对商品信息表和成绩表进行交叉连接。 【课堂实践4】 操作要求: (1)查询每笔订单的基本信息(订单号、订单日期、总金额)以及付款方式(名称)，和处理该订单的员工名称。 (2)使用WHERE语句来实现【任务3-3】中的查询操作。 (3)实现订单表(Orders)和订单详情表(OrderDetails)的左外连接。 (4)实现订单表(Orders)和订单详情表(OrderDetails)的右外连接。 (5)实现订单表(Orders)和订单详情表(OrderDetails)的完整外部连接。 任务4 使用子查询实现嵌套查询数据 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语中的查询称为嵌套查询或子查询。 -1】需要了解和“摩托罗拉 W380”为同类商品的商品号、商品名称和 【任务4 类别号。 1、确定“摩托罗拉W380”所属类别名。 2、查找类别号为‘01’的商品信息。 【技术小提示】 实现同一个查询可以采用多种方法，当然不同的方法其执行效率可能会有差别， 甚至会差别很大，数据库用户可以根据自己的需要进行合理的选择。 在查询语句中的常量必须准确，如上例中的“摩托罗拉W380”中间的空格也 不能省略，否则会出现查找不到的情况。 【知识解析】 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。子查询总是写在圆括号中，可以用在使用表达式的任何地方。上层的查询块称为外层查询或父查询，下层查询块称为内查询或子查询。SQL语言允许多层嵌套查询。即一个子查询中还可以嵌套其他子查询。 注意:子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。 1(嵌套子查询 嵌套子查询的执行不依赖于外部嵌套。其一般的求解方法是由里向外处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 (1)比较测试中的子查询 比较测试中的子查询是指父查询与子查询之间用比较运算符进行连接。但是用户必须确切地知道子查询返回的是一个单值，否则数据库服务器将报错。 返回的单个值被外部查询的 比较操作(如:=、!=、<、<=、>、>=)使用，该值可以是子查询中使用集合函数得到的值。 (2)集合成员测试中的子查询 集合成员测试中的子查询是指父查询与子查询之间用IN或NOT IN进行连接，判断某个属性列值是否在子查询的结果中，通常子查询的结果是一个集合。IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等;NOT IN表示不属于。 (3)批量比较测试中的子查询 使用ANY关键字的比较测试 通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为TRUE，则ANY测试返回TRUE。 使用ALL关键字的比较测试 通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为FALSE， 则ALL 测试返回FALSE。 ANY和ALL都用于一个值与一组值的比较，以“>”为例，ANY表示大于一组值中的任意一个，ALL表示大于一组值中的每一个。比如，>ANY(1,2,3)表示大于1;而>ALL(1,2,3)表示大于3。 【任务4-2】需要了解购买了“红双喜牌兵乓球拍”的订单号、订单时间和订单总金额。 【技术小提示】 连接总是可以表示为子查询，子查询经常(但不总是)可以表示为连接。 在一些必须检查存在性的情况中，使用连接会产生更好的性能。 【任务4-3】需要了解购买了商品号为“060001”的会员e_ID(会员号)、e_Name(会员名称)和e_Address(籍贯)。 【任务4-4】使用“=”完成“【任务4-2】”。 【任务4-5】需要了解比籍贯为“湖南长沙”任一会员年龄小的会员信息，查询结果按降序排列。 【技术小提示】 请读者自行 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 “出生年月”和“年龄”的关系以及比较的方法。 事实上，用聚合函数实现子查询通常比直接用ANY或ALL查询效率要高。 如果将上例中的ALL改为ANY，即只需要比最大的小即可。反过来，如果是大于ANY，则只需要大于最小的即可。 【知识解析】 1(子查询规则 子查询也受下面的条件限制 (1)通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称。 (2)如果外部查询的WHERE子句包括某个列名，则该子句必须与子查询选择列表中的该列兼容。 (3)子查询的选择列表中不允许出现ntext、text和image数据类型。 (4)无修改的比较运算符引入的子查询不能包括GROUP BY和HAVING子句。 (5)包括GROUP BY的子查询不能使用DISTINCT关键字。 (6)不能指定COMPUTE和INTO子句。 (7)只有同时指定了TOP，才可以指定ORDER BY。 (8)由子查询创建的视图不能更新。 XISTS引入的子查询的选择列表由星号(*)组成，而不使用单个 (9)通过E 列名。 (10)当=、～=、<、<=、>、或>=用在主查询中，ORDER BY子句和GROUP BY子句不能用在内层查询中，因为内层查询返回的一个以上的值不可被外层查询处理。 【任务4-6】针对Employees表中的每一名员工，在Orders表中查找处理过订单并且送货模式为“邮寄”的所有订单信息。 【技术小提示】 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真 值，否则返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用”*”，因为带EXISTS的子 查询只返回真值或假值，给出列名也没有实际意义。 这类查询与前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于 外层父查询的某个属性值(在本例中是依赖于Goods表的g_ID值)，我们称这 类查询为相关子查询。 -7】需要将Goods表中的信息进行处理，但为了防止破坏Goods表， 【任务4 可以建立一个临时表Temp，将Goods表中的数据全部复制到Temp表中，然后查询Temp中所有记录。 【技术小提示】 该语句不需要先建立表，会自动生成一个新表。 自动创建的新表必须有具体的列名，即不能包括聚合函数。 【任务4-8】求每一类商品的平均价格，并将结果保存到数据库中。 【技术小提示】 目标子句中的列必须与被插入表中所指定的被插入列一一对应，名称可以不同， 但类型必须一致。 必须首先建立好目标表。 【任务4-8】将商品中类别名称为 “家用电器”的商品折扣修改为0.8。 【技术小提示】 为避免诸如机器突然出现故障等意外导致无法继续执行第二条UPDATE语句而 造成数据库中的数据将永远处于不一致状态，借助于“事务”进行处理，事务的详细介绍请参阅第10章。 为了保证数据的参照完整性，借助于主键和外键建立表间的关系。 为了保证上述对多个表的操作能够自动完成，借助于“触发器”来完成，触发 器的详细介绍请参阅参考书。 【任务4-9】删除类别名称为“家用电器”的商品的基本信息。 【知识解析】 相关子查询 所谓相关子查询，是指在子查询中，子查询的查询条件中引用了外层查询表中的字段值。相关子查询的结果集取决于外部查询当前的数据行，这一点是与嵌套子查询不同。 相关子查询的执行过程如下。 (1)子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询。 (2)如果子查询的任何行与其匹配，则外部查询就返回结果行。 (3)再回到第一步(1)，直到处理完外部表的每一行。 【课堂实践5】 操作要求: (1)应用子查询了解“张小路”处理的订单的信息(订单号、订单时间、会员号、订单总额)。 (2)应用子查询了解“张小路”处理的订单的详细信息(订单号、商品号、购买价格、购买数量)。 (3)使用SELECT,INTO语句将OrderDetails表中的记录备份到od_Temp中。 (4)统计订单详情表中的商品的平均售价和商品总数量并存放在sale表中。 (5)将“张小路”所处理的订单的订单状态(o_Status)全部修改为true。 (6)删除汇款方式为“邮局汇款”的所有订单信息。 任务5 使用联合查询和分布式查询 【技术小提示】 参加UNION操作的各数据项(字段名、算术表达式、聚合函数)数目必须相同。 对应项的数据类型必须相同，或者可以进行显式或隐式转换。 各语句中对应的结果集列出现的顺序必须相同。 【知识解析】 1(合并结果集 合并查询也称联合查询是将两个或两个以上的查询结果合并，形成一个具有综合信息的查询结果。使用UNION语句可以把两个或两个以上的查询结果集合并 为一个结果集。 其语法格式如下: 查询语句 UNION [ALL] 查询语句 注意: (1)联合查询是将两个表(结果集)顺序连接。 2)UNION中的每一个查询所涉及到的列必须具有相同的列数、相同位置的 ( 列的数据 类型要相同。若长度不同，以最长的字段作为输出字段的长度。 (3)最后结果集中的列名来自第一个SELECT语句。 (4)最后一个SELECT查询可以带ORDER BY子句，对整个UNION操作 结果集起作用。且只能用第一个SELECT查询中的字段作排序列。 (5)系统自动删除结果集中重复的记录，除非使用ALL关键字。 2(分布式查询 分布式查询从多个异类数据源中访问数据，这些数据源可以存储于一台或多台计算机。通过使用 OLE DB，Microsoft SQL Server 2005可支持分布式查询。SQL Server 用户可以使用分布式查询访问以下内容: Server实例中的分布式数据。 (1)存储在多个SQL (2)存储在各种可以使用OLE DB访问接口访问的关系和非关系数据源中的异类数据。 任务6 在SSMS中实现查询 (1)启动SQL Server Management Studio，在“对象资源管理器”中依次展开【数据库】节点、【E-SHOP】数据库节点。 (2)右键单击要执行查询的表，选择【找开表】，在显示表的记录区域单击鼠标右键，选择【窗格】、【条件】，如图5-52所示。 (3)在查询条件选择界面中，选择要输出的列、指定排序方式和查询条件。然后单击鼠标右键，选择【执行SQL】，如图5-53所示。 【技术小提示】 选择【关系图】查看查询所影响的关系图。 选择【添加分组依据】指定分组条件。 【课堂实践6】 操作要求: (1)使用联合查询了解E-SHOP数据库的Goods表中折扣为0.8和价格在1500 元以下的商品信息。 (2)在SSMS中完成要求(1)的查询操作。 【学习小结】 本模块学习了如下内容: 单表查询:选择列，选择行 子查询，包括基本子查询语句、子查询类型、记录操作语句中的子查询和 子查询规 则; 联合查询; 分布式查询; 中实现查询。