SQL Server 2008实用案例教程-5

SQLServer实用案例教程(2008版)项目5数据的高级查询SQLServer实用案例教程(2008版)1234主要内容5需求分析知识点提取项目融合知识点详解自我完善6知识点详解SQLServer实用案例教程(2008版)需求分析在项目4中介绍的简单查询操作虽然能够满足用户的某些查询需求，但都仅限于单张数据表的查询操作。然而用户对“商品管理系统”的查询需求和功能需求不仅如此，更多元更复杂的查询结果是用户想要得到的。为满足用户需求，本项目将讲解多表间数据的高级查询方法。SQLServer实用案例教程(2008版)知识点提取本项目以多表为主线讲解高级查询，需要掌握的主要知识点如下：理解笛卡尔积连接查询熟练掌握条件连接查询掌握外连接查询理解自连接查询理解子查询SQLServer实用案例教程(2008版)知识点详解5.1多表联接查询SQLServer实用案例教程(2008版)5.2子查询5.1多表连接查询知识点理论上讲，设A,B为集合，用A中元素为第一元素，B中元素为第二元素构成有序对，所有这样的有序对组成的集合叫做A与B的笛卡尔积。实例上说，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。笛卡尔积结果的元素个数是两个集合中各自元素个数的乘积。SQLServer实用案例教程(2008版)5.1.1笛卡尔积连接5.1多表连接查询案例以下给出数据表A和数据表B，来帮助理解笛卡尔积。如表5-1和表5-2所示。SQLServer实用案例教程(2008版)5.1.1笛卡尔积连接5.1多表连接查询【例5.1】从表A和表B中查询出已经选课学生的选课情况（比如张三同学选了什么课）。解题根据select语句基本格式，可以得到查询代码如下：select*fromA,BSQLServer实用案例教程(2008版)5.1.1笛卡尔积连接5.1多表连接查询说明：若要知道张三同学选了什么课，那么在查询结果中至少应该有“姓名”字段表示张三，“课程名”字段表示选课信息，可见查询操作既涉及到表A也涉及到表B，因此from后面跟随了两张数据表的名字。两张表名字之间用逗号间隔。因为没要求显式哪些字段信息，所以select后面加了“*”，表示全部字段。SQLServer实用案例教程(2008版)5.1.1笛卡尔积连接5.1多表连接查询以上代码得到的查询结果如表5-3所示。SQLServer实用案例教程(2008版)5.1.1笛卡尔积连接5.1多表连接查询说明：该结果即为笛卡尔积查询结果，记录总个数是两表各自记录个数的乘积（3*2）。字段数是两表各自字段数的和。思考表5-3的查询结果及时题目想要的查询结果吗？如果不是，应该如何得到正确的查询结果？SQLServer实用案例教程(2008版)5.1.1笛卡尔积连接5.1多表连接查询显然在表5-3得到的查询结果中，只有带底纹的两条记录是正确的查询结果。即001号张三同学选了C语言课程，002号李四同学选了SQLServer课程，其余结果均为数据冗余。并且在字段上也存在两个学号字段的数据冗余显现，值相同的两个字段只保留一个即可。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询案例【例5.2】从表A和表B中查询出已经选课学生的正确选课结果。要求显示出学生的学号、姓名和选课名称。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询解题通过分析表5-3的查询结果得出正确查询结果应该满足的条件是：“学号”与“学号”相同；去掉一个重复的“学号”字段。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询根据以上分析，修改后的查询代码修改如下：select学号,姓名,课程名fromA,BwhereA.学号=B.学号SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询说明：select后面仅跟随要显示的字段名。条件“A.学号=B.学号”用来表示要查找的记录需要满足的条件是在两张表中的“学号”字段相等。其中“数据表名.字段名”表示哪张数据表的哪个字段。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询这段代码执行后将提示一个“列名’学号’不明确”的错误消息。这条提示信息是提示给select语句后的“学号”字段的。这是因为“学号”字段作为两张数据表的公共字段，如果出现在select语句后面被查询，要求必须为公共字段指明是哪张数据表的。这里使用“A.学号”或者“B.学号”都可以，通常使用主表的公共字段。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询因此，以上代码修改如下：selectA.学号,姓名,课程名fromA,BwhereA.学号=B.学号SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询此段代码为正确的查询代码，查询后的结果如表5-4所示。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询知识点条件连接的语法格式如下：select字段名列表from表1,表2,表3…where表1.公共字段名=表2.公共字段名and表2.公共字段名=表3.公共字段名……SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询“条件连接”是多表连接的常见方法，一般步骤是：select后面跟随被查询的字段名，如有公共字段，必须指明是哪张数据表的。from后面跟随被查询的多张数据表名，表名间用逗号间隔，最后一个表名后面没有逗号。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询一定有where条件，用来给出多张数据表的连接条件，表与表的公共字段即为连接媒介。通常n张数据表的连接查询，至少有n-1个连接条件支撑。表之间的多个连接条件用and（与运算）运算符连接，与其它条件的连接可以是and运算符或者是or（或运算）运算符。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询案例【例5.3】在“商品管理数据库”数据库中查询客户的购买信息。要求显示客户编号、客户姓名、商品编号和销售日期字段信息。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询解题在“管理器”中新建一个查询窗口，输入代码如下：use商品管理数据库goselect客户信息表.客户编号,客户姓名,商品编号,销售日期from客户信息表,销售信息表where客户信息表.客户编号=销售信息表.客户编号goSQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询说明：select语句后面加被查询的字段名，由于“客户编号”字段是公共字段，所以指明是“客户信息表”的。从select后的查询结果出发，判断查询需涉及“客户信息表”和“销售信息表”两张数据表。“客户信息表.客户编号=销售信息表.客户编号”为两张数据表的连接条件。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询点击“分析”按钮，无语法错误后点击“执行”按钮，得到结果如图5-1所示。SQLServer实用案例教程(2008版)5.1.2条件连接图5-1条件连接查询5.1多表连接查询案例【例5.4】在“商品管理数据库”库中查询出客户购买商品的详细信息，要求显示客户编号、客户姓名、商品编号、商品名称、销售金额和商品类型名字段信息。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询解题从要求显示的字段信息可以得到，客户编号、客户姓名字段信息在“客户信息表”中，商品编号、商品名称、字段信息在“商品信息表”中，销售金额字段信息在“销售信息表”中，商品类型名字段信息在“商品类型”表中。共查询4张数据表。分析4张数据表的连接条件，找出公共字段。“客户编号”字段是“客户信息表”和“销售信息表”的公共字段，“商品编号”字段是“销售信息表”和“商品信息表”的公共字段，“商品类型编号”字段是“商品信息表”和“商品类型表”的公共字段。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询根据以上分析，判断使用多表查询操作，代码如下：use商品管理数据库goselect客户信息表.客户编号,客户姓名,商品信息表.商品编号,商品名称,销售金额,商品类型名from客户信息表,商品信息表,销售信息表,商品类型表where客户信息表.客户编号=销售信息表.客户编号and销售信息表.商品编号=商品信息表.商品编号and商品信息表.商品类型编号=商品类型表.商品类型编号goSQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询说明：4张数据表连接需要3个连接条件，连接条件之间是同时满足的关系，因此用and（与运算符）相连。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询点击“分析”按钮，无语法错误后点击“执行”按钮，得到结果如图5-2所示。SQLServer实用案例教程(2008版)5.1.2条件连接图5-2多条件连接查询5.1多表连接查询思考在【例5.4】查询结果的基础上，只显示出客户编号为20130001的客户购买商品的信息应如何解题？SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询解题思考题即是在现有查询结果基础上做进一步的条件筛选，因此需要在where语句后面继续添加查询条件，代码如下：SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询use商品管理数据库goselect客户信息表.客户编号,客户姓名,商品信息表.商品编号,商品名称,销售金额,商品类型名from客户信息表,商品信息表,销售信息表,商品类型表where客户信息表.客户编号=销售信息表.客户编号and销售信息表.商品编号=商品信息表.商品编号and商品信息表.商品类型编号=商品类型表.商品类型编号and客户信息表.客户编号='20130001'goSQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询其中，最后一行代码即是为满足题目要求而添加。这个查询条件与前面表连接的查询条件也是与的关系，因此用and运算符相连。注意：在一段select语句的查询中，多个条件查询条件之间必须用逻辑运算符相连，而非使用多个where语句。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询点击“分析”按钮，无语法错误后点击“执行”按钮，得到结果如图5-3所示。SQLServer实用案例教程(2008版)5.1.2条件连接图5-3带查询条件的连接查询5.1多表连接查询知识点条件查询还可以使用“内连接”的方式实现。内连接查询的语法格式如下：select*|字段列表from数据表1[inner]join数据表2on连接条件表达式[inner]join数据表3on连接条件表达式……[where条件表达式]SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询说明：on关键字后面是两张表的连接条件。实现两张以上表连接时，按照数据表1连接数据表2，数据表2连接数据表3的顺序依次相连。可以使用where语句添加连接条件以外的查询条件。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询案例【例5.5】使用“内连接”方法实现【例5.4】的查询，并且只显示客户编号为20130001的客户的购买信息。SQLServer实用案例教程(2008版)5.1.2条件连接5.1多表连接查询解题在“管理器”中新建一个查询窗口，输入程序代码如下：use商品管理数据库goselect客户信息表.客户编号,客户姓名,商品信息表.商品编号,商品名称,销售金额,商品类