基于ASPNET的WEB应用开发

1、基于基于ASP.NETASP.NET的的WEBWEB应用开发应用开发SQL ServerSQL Server数据库、表查询与优化数据库、表查询与优化 SQL ServerSQL Server数据库、表查询与优化数据库、表查询与优化第一节第一节 检索数据检索数据第二节第二节 数据分组与汇总数据分组与汇总第三节第三节 多表联接多表联接第四节第四节 子查询子查询第五节第五节 存储过程与查询优化存储过程与查询优化第一节 检索数据教学目标：教学目标：l熟练掌握使用熟练掌握使用 SELECT SELECT 语句语句检索数据检索数据l掌握结合参数进行数据的过滤掌握结合参数进行数据的过滤l掌握设置结果集格式分

2、方法掌握设置结果集格式分方法l了解需要考虑的性能问题了解需要考虑的性能问题检索数据检索数据l使用使用 SELECT SELECT 语句语句检索数据检索数据l过滤过滤数据数据l设置结果集格式设置结果集格式l需要需要考虑的性能问题考虑的性能问题l推荐操作推荐操作l强化训练强化训练使用使用 SELECT SELECT 语句语句检索数据检索数据l使用使用 SELECT SELECT 语句语句l指定指定列列l使用使用 WHERE WHERE 子子句指定行句指定行SELECT ALL | DISTINCTTOP n FROM ,n WHERE GROUP BY ,n HAVING ORDER BY ,n基

3、本基本语法语法使用使用 SELECT SELECT 语句语句l用用 括起来的是可选项，括起来的是可选项，SELECT SELECT 是必需的是必需的l选择选择列表指定了要返回的列列表指定了要返回的列lWHERE WHERE 子子句指定限制查询的条件句指定限制查询的条件 在在搜索条件中，可以使用比较操作符、字符串、搜索条件中，可以使用比较操作符、字符串、逻辑操作符来限制返回的行数逻辑操作符来限制返回的行数使用使用 SELECT SELECT 语句（续）语句（续）lFROM FROM 子句知道了返回的行和列所属的表子句知道了返回的行和列所属的表lDISTINCT DISTINCT 选选项从结果集中

4、消除了重复的行，项从结果集中消除了重复的行，TOP TOP n n 选项限定了要返回的行数选项限定了要返回的行数lGROUP BY GROUP BY 子句是对结果集进行分组子句是对结果集进行分组lHAVING HAVING 子句是在分组的时候，对字段或表子句是在分组的时候，对字段或表达式指定搜索条件达式指定搜索条件lORDER BY ORDER BY 子句对结果集按某种条件进行排子句对结果集按某种条件进行排序序 指定指定列（续）列（续）1DavolioNancySales Representative2FullerAndrewVice President, Sales3LeverlingJan

5、etSales Representative4PeacockMargaretSales Representative5BuchananStevenSales Manager6SuyamaMichaelSales Representative7KingRobertSales Representative8CallahanLauraInside Sales Coordinator9DodsworthAnneSales RepresentativeUSE northwindSELECT employeeid, lastname, firstname, titleFROM employeesGO示例示

6、例使用使用 WHERE WHERE 子句指定行子句指定行l使用使用 WHERE WHERE 子句，得到基于搜索条件的行子句，得到基于搜索条件的行 := NOT | ( ) AND | ORNOT | ( ) ,.n n列出了可以在列出了可以在 WHERE 子句中包含的表达式子句中包含的表达式列表列表n使用使用 WHERE 子句指定行时，请注意子句指定行时，请注意l在所有数据类型为 char、nchar、varchar、nvarchar、text、datetime和 smalldatetime 的数据周围，必须使用单引号（）l在使用 SELECT 语句时，尽量使用 WHERE 语句限制将要返回的

7、行使用使用 WHERE WHERE 子句指定行（续）子句指定行（续）5BuchananStevenSales ManagerUSE northwindSELECT employeeid, lastname, firstname, titleFROM employeesWHERE employeeid = 5GO示例示例 检索数据检索数据l使用使用 SELECT SELECT 语句语句检索数据检索数据l过滤过滤数据数据l设置结果集格式设置结果集格式l查询处理查询处理l需要需要考虑的性能问题考虑的性能问题l推荐操作推荐操作l强化训练强化训练过滤过滤数据数据l使用使用比较运算符比较运算符l使用使用字

8、符串比较符字符串比较符l使用逻辑运算符使用逻辑运算符l检索一定范围内的值检索一定范围内的值l使用值列表作为搜索条件使用值列表作为搜索条件l检索未知值检索未知值使用使用比较运算符比较运算符操作符描述=等于等于大于大于=大于或等于大于或等于=小于或等于小于或等于不等于不等于l使用比较操作符来比较表中的值与指定使用比较操作符来比较表中的值与指定的值或表达式的值的值或表达式的值使用使用比较运算符（续）比较运算符（续）USE northwindSELECT lastname, cityFROM employeesWHERE country = USAGODavolioSeattleFullerTacom

9、aLeverlingKirklandPeacockRedmondCallahanSeattle示例示例使用使用字符串比较符字符串比较符n在比较字符串时，可以使用在比较字符串时，可以使用 LIKE 和通配符来查找需要的行和通配符来查找需要的行n使用使用 LIKE 时，请注意时，请注意l查询条件中的字符串内，所有的字符都有效，包括开始和结尾的空格lLIKE 只可用于下列数据类型：char、nchar、varchar、nvarchar 和datetimel通配符的种类通配符的种类通配符描述%0或多个字符串或多个字符串_任何单个的字符任何单个的字符在指定区域或集合内的任何单个字符在指定区域或集合内的任

10、何单个字符不在指定区域或集合内的任何单个字符不在指定区域或集合内的任何单个字符使用使用字符串比较符（续）字符串比较符（续）USE northwindSELECT companynameFROM customersWHERE companyname LIKE %Restaurant%GOGROSELLA-RestauranteLonesome Pine RestaurantTortuga Restaurante示例示例使用逻辑运算符使用逻辑运算符n用逻辑操作符用逻辑操作符 AND、OR 和和 NOT 来连接一系列的表达式，来连接一系列的表达式，及优化查询过程。使用逻辑运算符时，遵循下列原则及优化

11、查询过程。使用逻辑运算符时，遵循下列原则l使用 AND 返回满足所有条件的行l使用 OR 返回满足任一条件的行l使用 NOT 返回不满足条件的行n使用圆括号使用圆括号l为表达式分组l改变表达式求值顺序l增加表达式的可读性使用逻辑运算符（续）使用逻辑运算符（续）n搜索条件的顺序搜索条件的顺序lMicrosoft SQL Server 2000 首先求 NOT 表达式的值，然后是 AND，最后是 ORl当表达式中所有的操作符优先级相同时，求值顺序由左到右使用逻辑运算符（续）使用逻辑运算符（续）USE northwindSELECT productid, productname, supplieri

12、d, unitprice FROM products WHERE (productname LIKE T% OR productid = 46) AND (unitprice 16.00) GO 14Tofu623.2529Thringer Rostbratwurst12123.7962Tarte au sucre2949.3示例示例检索一定范围内的值检索一定范围内的值l使用使用 BETWEEN BETWEEN 来查询在一定范围内的值，来查询在一定范围内的值，使用使用 BETWEEN BETWEEN 时，注意时，注意lSQL Server SQL Server 返回的结果集中，包含范围内的返回

13、的结果集中，包含范围内的边缘值边缘值l尽量使用尽量使用 BETWEENBETWEEN，而不用而不用 AND AND 和比较操作和比较操作符表示的表达式（符表示的表达式（=x x AND = AND x x AND AND =30GO23534511511102110222531153230示例示例数据分组与汇总l使用 TOP n 列出前 n 个记录l使用聚合函数lGROUP BY 的基础知识l在结果集中生成汇总值l使用 COMPUTE 和 COMPUTE BY 子句l推荐操作在结果集中生成汇总值l使用带有 ROLLUP 运算符的 GROUP BY 子句l使用带有 CUBE 运算符的 GROUP

14、 BY 子句l使用 GROUPING 函数使用带有 ROLLUP 运算符的 GROUP BY 子句n联合使用联合使用 GROUP BY 子句和子句和 ROLLUP 操作符，计算组操作符，计算组中的汇总值中的汇总值n使用使用 GROUP BY 子句和子句和 ROLLUP 操作符时，应注意操作符时，应注意lSQL Server 处理 GROUP BY 中字段列表的顺序是从右到左，然后对每个组使用聚合函数lSQL Server 将在结果集中增加一行，这行将显示总和或平均值之类的汇总值，新增的行以 NULL 标识l不能同时使用关键字 ALL 和操作符 ROLLUPl使用 ROLLUP 时，确保出现在

15、GROUP BY 后的各字段，在数据库环境中具有确定的、有意义的关系描述描述使用带有 ROLLUP 运算符的 GROUP BY 子句USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH ROLLUP ORDER BY productid, orderidGONULLNULL951NULL1511512102NULL35211022253NULL4531153230示例示例总数只只对对 productid 1中中

16、的行汇总的行汇总productid 1、 orderid 1 的的具体值具体值productid 1、 orderid 2 的的具体值具体值只只对对 productid 2 中中的行汇总的行汇总productid 2、 orderid 1 的的具体值具体值productid 2、 orderid 2 的具体值的具体值只只对对 productid 3 中的行汇总中的行汇总productid 3、orderid 1 的的具体值具体值productid 3、orderid 2 的的具体值具体值使用带有 CUBE 运算符的 GROUP BY 子句n联合使用联合使用 GROUP BY 子句和子句和 CU

17、BE 操作符，能生成操作符，能生成基于基于 GROUP BY 子句指定的所有字段的可能组合子句指定的所有字段的可能组合n使用使用 GROUP BY子句和子句和 CUBE 操作符时，应注意操作符时，应注意l如果在 GROUP BY 子句中有 n 个字段或表达式，SQL Server 将在结果集中返回 2n 种可能的组合l结果集中含有 NULL 的记录代表该记录由 CUBE 操作符生成l不能同时使用关键字 ALL 和操作符 CUBEl使用 CUBE 时，确保出现在 GROUP BY 后的各字段，在数据库环境中具有确定的、有意义的关系使用带有 CUBE 运算符的 GROUP BY 子句（续）CUBE

18、 操作符比ROLLUP 操作符 多产生两个汇总值USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderidGO描述描述总总数数对对 orderid 1 中中的所有行汇总的所有行汇总对对 orderid 2 中中的所有行汇总的所有行汇总只只对对 productid 1 中中的行汇总的行汇总productid 1、 orderid 1 的的具体值具体值pr

19、oductid 1、 orderid 2 的的具体值具体值只只对对 productid 2 中中的行汇总的行汇总productid 2、 orderid 1 的的具体值具体值productid 2、 orderid 2 的的具体值具体值只只对对 productid 3 中中的行汇总的行汇总productid 3、 orderid 1 的的具体值具体值productid 3、 orderid 2 的的具体值具体值NULLNULL95NULL130NULL2651NULL1511512102NULL35211022253NULL4531153230示例示例使用 GROUPING 函数n区分结果集中

20、的细节数据和汇总数据区分结果集中的细节数据和汇总数据 使用 GROUPING 函数能够帮助识别结果集中的空值是表中原有的空值，还是由 ROLLUP 或 CUBE 生成的含有汇总值的新记录n使用使用函数函数 GROUPING 时，应注意时，应注意lSQL Server 将为 GROUPING 函数指定的字段生成一个新的字段l如果 SQL Server 返回 1，代表结果集中的这一记录是由 ROLLUP 或 CUBE 生成的lSQL Server 返回 0，代表该记录原本就在数据库的表中l出现在 GROUPING 函数中的字段也必须出现在 GROUP BY 子句中lGROUPING 函数能够用编程

21、方式引用结果集 1 代表代表前一字段的汇总值前一字段的汇总值0 代表代表前一字段的具体值前一字段的具体值95306515510351025451530使用 GROUPING 函数（续）SELECT productid, GROUPING (productid) ,orderid, GROUPING (orderid) ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderidGONULLNULLNULL1112223331110

22、00000000NULL12NULL12NULL12NULL12100100100100示例示例数据分组与汇总l使用 TOP n 列出前 n 个记录l使用聚合函数lGROUP BY 的基础知识l在结果集中生成汇总值l使用 COMPUTE 和 COMPUTE BY 子句l推荐操作使用 COMPUTE 和 COMPUTE BY 子句n生成某一列的明细值和汇总值的报表生成某一列的明细值和汇总值的报表n为组中的子集生成明细值和汇总值的报表为组中的子集生成明细值和汇总值的报表使用 COMPUTE 和 COMPUTE BY 子句（续）COMPUTE BYCOMPUTEUSE northwindSELECT

23、 productid, orderid, quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) BY productid COMPUTE SUM(quantity)GOUSE northwindSELECT productid, orderid ,quantity FROM orderhistORDER BY productid, orderidCOMPUTE SUM(quantity)GO11512102110222531153230sum951151210sum1521102225sum35311

24、53230sum45sum95示例示例 2示例示例 1生成某一列的明细值和汇总值的报表n使用使用COMPUTE 子句的注意事项和原则子句的注意事项和原则l在一条语句中，不能同时使用多个 COMPUTE 和 COMPUTE BY 子句lSQL Server 中要求选择列表中的字段和 COMPUTE 子句中的字段相同l不能同时使用 SELECT INTO 和 COMPUTE，因为 COMPUTE 不会生成关系型的输出结果为组中的子集生成明细值和汇总值的报表n使用使用COMPUTE BY子句的注意事项和原则子句的注意事项和原则l应当同时使用 ORDER BY 子句和 COMPUTE BY 子句，这样

25、记录就会被分组显示l应在 COMPUTE BY 子句后指定字段名，使 SQL Server 能够决定要生成的汇总值是什么l出现在 COMPUTE BY 子句后的字段必须出现在 ORDER BY 子句后，且顺序相同，始于同一表达式，不能略过任一表达式数据分组与汇总l使用 TOP n 列出前 n 个记录l使用聚合函数lGROUP BY 的基础知识l在结果集中生成汇总值l使用 COMPUTE 和 COMPUTE BY 子句l推荐操作避免避免使用使用 COMPUTE 或或 COMPUTE BY 子子句句索引索引通常会对字段进行汇总操作通常会对字段进行汇总操作避免避免在含有空值的字段上使用聚合函数在含有

26、空值的字段上使用聚合函数使用使用 ORDER BY 子子句为结果集中的记录排序句为结果集中的记录排序尽量尽量使用使用 ROLLUP 操作操作符，符，而而不是不是 CUBE 操作操作符符推荐操作实验 数据分组和汇总练习练习1 使用使用 TOP n 关键字关键字练习练习2 使用使用 GROUP BY 子句和子句和 HAVING 子句子句练习练习3 使用使用 ROLLUP 子句和子句和 CUBE 运算符运算符练习练习4 使用使用 COMPUTE 和和 COMPUTE BY 子句子句 多表联接l使用表的别名l命名列l组合多个表中的数据l合并多个结果集l推荐操作使用表的别名l增加语句的可读性，有利于写复

27、杂的表的联接操作，同时简化 Transact-SQL 脚本维护l有时复杂的 JOIN 语句和子查询必须使用表的别名 例如，联接一个表和它自身时，必须使用别名例如，联接一个表和它自身时，必须使用别名l给字段起名 使用多表联接时，必须慎重地指定字段名，任何数据库使用多表联接时，必须慎重地指定字段名，任何数据库对象名称包含四个标识：服务器名、数据库名、主人名对象名称包含四个标识：服务器名、数据库名、主人名称、对象名称、对象名SELECT * FROM server.database.schema.table AS table_alias多表联接l使用表的别名l命名列l组合多个表中的数据l合并多个结果

28、集l推荐操作命名列l在查询中使用多表联接时，必须注意列的命名l任何数据库对象的全名包括四个标识符：服务器名、数据库名、主人名和对象名l 在多表联接中，如果表拥有相同的字段名，则在指定字段时，必须包含表名多表联接l使用表的别名l命名列l组合多个表中的数据l合并多个结果集l推荐操作组合多个表中的数据l联接概述l使用内联接l使用外联接l使用交叉联接l联接两个以上的表l自联接 （Self-Join）联接概述l从多个表中选择指定的字段l关键字 JOIN 指定要联接的表，以及这些表联接的方式l关键字 ON 指定联接条件l查询两个或多个表并生成单个结果集l基于表的主键和外键，指定联接的条件l如果表中有组合主

29、键，联接表时，必须在 ON 子句中引用该键l所有联接的表必须共同拥有某些字段，这些字段必须有相同的或兼容的数据类型联接概述（续）l查询两个或多个表并生成单个结果集（续）l如果联接的表有相同字段，则引用这些字段时必须指定表名l尽量在联接中限制表的个数l在单个 SELECT 语句中，可以多表联接lANSI JOIN 操作符 INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、GROSS JOIN 使用内联接n内联接通过比较被联接的表所共同拥有的字段，内联接通过比较被联接的表所共同拥有的字段，把多个表联接起来把多个表联接起来n为什么

30、要使用内联接为什么要使用内联接l内联接是 SQL Server 缺省的联接方式l在选择列表中指定结果集要显示的字段名l使用 WHERE 子句来限制结果集要返回的记录l在联接条件中不要使用空值，因为空值与其他值不会相等使用内联接（续）USE joindbSELECT buyer_name, sales.buyer_id, qtyFROM buyers INNER JOIN salesON buyers.buyer_id = sales.buyer_idGOsales114323151553711421003buyersAdam BarrSean ChaiEva CoretsErin OMelia

31、1234结果结果Adam BarrAdam BarrErin OMeliaEva Corets11431553711Erin OMelia41003示例示例使用外联接n左（右）外联接可以从两个表中返回符合联接条左（右）外联接可以从两个表中返回符合联接条件的记录，同时也将返回左（右）边不符合联接件的记录，同时也将返回左（右）边不符合联接条件的记录条件的记录n使用左（右）外联接时，请注意使用左（右）外联接时，请注意l不满足联接条件的记录将显示空值l左联接可以显示第一个表中所有记录l右联接可以显示第二个表中所有记录l可以把 LEFT OUTER JOIN 或 RIGHT OUTER JOIN 简写为

32、 LEFT JOIN 或 RIGHT JOIN使用外联接（续）USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGOsales114323151553711421003buyersAdam BarrSean ChaiEva CoretsErin OMelia1234结果结果Adam BarrAdam BarrErin OMeliaEva Corets11431553711Erin OMelia41003Sean

33、 ChaiNULLNULL示例示例使用交叉联接n交叉交叉联接将从被联接的表中返回所有可能的记录联接将从被联接的表中返回所有可能的记录组合组合n为什么使用交叉联接为什么使用交叉联接 l在规范化的数据库中，很少使用交叉联接l可以为数据库生成测试数据l为清单及企业模板生成所有可能的组合数据使用交叉联接（续）USE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO结果结果Adam BarrAdam BarrAdam BarrAdam Barr1553711 Adam Barr1003 Sean Chai15 Sean Chai5 S

34、ean Chai37 Sean Chai11 Sean Chai1003 Eva Corets15 .sales114323151553711421003buyers1234Adam BarrSean ChaiEva CoretsErin OMelia示例示例联接两个以上的表n联接任意数目的表都有可能，通过使用共同拥有联接任意数目的表都有可能，通过使用共同拥有的字段，任何一个表都可以和其他表联接的字段，任何一个表都可以和其他表联接n为什么要联接两个以上的表为什么要联接两个以上的表 使用多重联接可以从多个表中得到彼此相关的信息 l至少有一个表具有外键，把要联接的表按一定关系联系起来l组合键中的每

35、一字段必须出现在 ON 子句后l可以使用 WHERE 子句限制结果集所返回的记录联接两个以上的表（续）SELECT buyer_name, prod_name, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id INNER JOIN produce ON d_id = d_idGOproduce1234ApplesPearsOrangesBananas5Peachesbuyers1234Adam BarrSean ChaiEva CoretsErin OMeliasa

36、les114323154215537111003结果结果Erin OMeliaAdam BarrErin OMeliaAdam BarrEva CoretsApplesPearsPearsOrangesPeaches37151003511示例示例自联接 （Self-Join）n使用自联接，可以查询一个表中各记录之间的使用自联接，可以查询一个表中各记录之间的关系关系n使用自联接时，应注意使用自联接时，应注意 l引用表的两份副本时，必须使用表的别名l生成自联接时，表中每一行都和自身比较一下，并生成重复的记录，使用 WHERE 子句来消除这些重复记录自联接 （Self-Join）（续）USE joi

37、ndbSELECT a.buyer_id AS buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idWHERE a.buyer_id b.buyer_idGOsales b114323151553711421003sales a114323151553711421003结果结果421示例示例多表联接l使用表的别名l命名列l组合多个表中的数据l合并多个结果集l推荐操作合并多个结果集n使用使用 UNION 操作操作符符可以可以从多个查询里产生单个结从多个查询里

38、产生单个结果集果集n使用操作符使用操作符 UNION 时，注意时，注意l每一个查询必须有类似的数据、相同的字段数目，并且在选择列表中字段顺序相同lSQL Server 将自动删除结果集中重复的记录l必须在第一个 SELECT 语句中指定字段名l在 UNION 子句中使用 ORDER BY 指定排序方式l用 UNION 分解复杂的查询会提高查询速度合并多个结果集（续）nUNION 和和 JOIN 的区别的区别lJOIN 是合并多个表并生存一个单独的结果集，该结果集将包含多个表中的字段lUNION 是把多个 SELECT 语句返回的结果集合并到一个结果集中l使用操作符 UNION，要求所引用的表必

39、须具有相似的数据类型、相同的字段数，每个查询中的选择列表必须具有相同的顺序l使用操作符 JOIN，只要求联接的表共同拥有某些字段l用 UNION 分解复杂的查询会提高查询速度，而联接表越多，查询速度越慢多表联接l使用表的别名l命名列l组合多个表中的数据l合并多个结果集l推荐操作推荐操作在在主键和外键的字段上联接表主键和外键的字段上联接表当当表联接操作发生在多个字段组成的主键或外键上时，表联接操作发生在多个字段组成的主键或外键上时，ON 子句中一定要引用所有组成键的字段子句中一定要引用所有组成键的字段尽量尽量限制联接的表的数目限制联接的表的数目实验 查询多个表练习练习1 联接表联接表练习练习2

40、使用使用 UNION 运算符合并结果集运算符合并结果集 子查询l子查询介绍l把子查询用作派生表l把子查询用作表达式l使用子查询关联数据l使用 EXISTS 和 NOT EXISTS 子句l推荐操作子查询介绍l子查询是嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或另一个子查询中的 SELECT 语句l使用子查询的原因l把复杂的查询分解成一系列的逻辑步骤l作为 WHERE 子句中条件的组成部分l使用联接而不使用子查询的原因lSQL Server 执行联接的速度比子查询要快子查询介绍（续）l子查询的使用方法l子查询要用括号括起来l只要需要返回一个值或一系列值，就可以使用子查询

41、l不能使用子查询检索数据类型为 text 或 image 的字段l子查询可以再包含子查询，至多可嵌套32层子查询l子查询介绍l把子查询用作派生表l把子查询用作表达式l使用子查询关联数据l使用 EXISTS 和 NOT EXISTS 子句l推荐操作把子查询用作派生表l可以用子查询产生一个派生的表，用于代替 FROM 子句中的表l用子查询产生派生表时，子查询可以认为是l是查询语句中的一个结果集，被用作一个表l代替了 FROM 子句中的表l将与查询的其他部分一起优化USE northwindSELECT T.orderid, T.customerid FROM ( SELECT orderid, c

42、ustomerid FROM orders ) AS TGO子查询l子查询介绍l把子查询用作派生表l把子查询用作表达式l使用子查询关联数据l使用 EXISTS 和 NOT EXISTS 子句l推荐操作把子查询用作表达式l在 Transact-SQL 中，所有使用表达式的地方，都可以使用子查询代替l当子查询被用作表达式时，子查询可以认为是l被当作一个表达式处理并计算。查询优化器总是将表达式等同于联接一个只有一行记录的表l在整个语句中，只计算一次USE pubsSELECT title, price ,( SELECT AVG(price) FROM titles) AS average ,pri

43、ce-(SELECT AVG(price) FROM titles) AS difference FROM titles WHERE type=popular_compGO子查询l子查询介绍l把子查询用作派生表l把子查询用作表达式l使用子查询关联数据l使用 EXISTS 和 NOT EXISTS 子句l推荐操作使用子查询关联数据l计算一个相关子查询l模拟 JOIN 子句l模拟 HAVING 子句计算一个相关子查询l使用相关子查询时，内层子查询被反复执行。外层查询有多少记录，内层查询就被执行多少次lSQL Server 在外层查询的每一条记录执行一次内层查询lSQL Server 将比较内层查询

44、的结果和外层查询的结果USE northwindSELECT orderid,customerid FROM orders AS or1 WHERE 20 (SELECT quantity FROM order details AS od WHERE or1.orderid = od.orderid AND ductid = 23 GO模拟 JOIN 子句l相关子查询可以产生跟联接子句一样的结果集l联接可以使查询优化器以效率最高的方式查询数据USE pubsSELECT DISTINCT t1.type FROM titles AS t1 WHERE t1.type IN (SEL

45、ECT t2.type FROM titles AS t2 WHERE t1.pub_id t2.pub_id)GO模拟 HAVING 子句l相关子查询产生的结果集可以模拟 HAVING 子句产生的结果集USE pubsSELECT t1.type, t1.title, t1.price FROM titles AS t1 WHERE t1.price ( SELECT AVG(t2.price) FROM titles AS t2 WHERE t1.type = t2.type )GOUSE pubsSELECT t1.type, t1.title, t1.price FROM titles

46、 AS t1 INNER JOIN titles AS t2 ON t1.type = t2.type GROUP BY t1.type, t1.title, t1.price HAVING t1.price AVG(t2.price)GO示例示例 1示例示例 2子查询l子查询介绍l把子查询用作派生表l把子查询用作表达式l使用子查询关联数据l使用 EXISTS 和 NOT EXISTS 子句l推荐操作使用 EXISTS 和 NOT EXISTS 子句l和相关子查询一起使用 可以可以限制外层查询，使其结果集符合子查限制外层查询，使其结果集符合子查询的条件询的条件l判断某个值是否存在于一系列的值中

47、 SQL Server 将将测试数据是否匹配子查询测试数据是否匹配子查询结果集的某一条记录结果集的某一条记录lSQL Server 处理过程l外层子查询测试子查询返回的记录是否存在 l基于查询所指定的条件，子查询返回 TRUE 或 FALSEl子查询不产生任何记录USE northwindSELECT lastname, employeeid FROM employees AS e WHERE EXISTS (SELECT * FROM orders AS o WHERE e.employeeid = o.employeeid AND o.orderdate = 10/4/00)GO示例示例子

48、查询l子查询介绍l把子查询用作派生表l把子查询用作表达式l使用子查询关联数据l使用 EXISTS 和 NOT EXISTS 子句l推荐操作推荐操作使用使用子查询分解复杂的查询子查询分解复杂的查询在在相关子查询中使用表的别名相关子查询中使用表的别名使用使用 INSERTSELECT 语句，从语句，从别的地方别的地方向向一个表增加记录一个表增加记录尽量尽量使用操作符使用操作符 EXISTS ，而不是而不是 IN 操作操作符符实验 使用子查询练习练习1 把子查询用作派生表把子查询用作派生表练习练习2 把子查询用作表达式把子查询用作表达式练习练习3 使用子查询关联数据使用子查询关联数据 l存储过程介绍

49、l创建、执行、修改和删除存储过程l在存储过程中使用参数l性能考虑l推荐操作 实现存储过程l定义存储过程l存储过程的初始处理l存储过程的后续处理l存储过程的优点存储过程介绍定义存储过程l存储过程l是存储在服务器上的 Transact-SQL 语句的命名集合l是封装重复性任务的方法l支持用户声明变量、条件执行以及其他强有力的编程特性lSQL Server 中的存储过程与其他编程语言中的过程类似，它可以l包含执行数据库操作（包括调用其他过程）的编程语句l接受输入参数l向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）l以输出参数的形式将多个值返回至调用过程或批处理定义存储过程（续）lSQ

50、L Server 支持五种类型的存储过程l系统存储过程（sp_）：存储在 master 数据库内，以“sp_”前缀标识l本地存储过程：本地存储过程在单独的用户数据库内创建l临时存储过程：临时存储过程可能是局部的，名称以“#”开头；也可能是全局的，名称以“#”开头l远程存储过程：远程存储过程是 SQL Server 早期版本的特性，分布式查询支持这项功能l扩展存储过程（xp_）：扩展存储过程以动态链接库（DLL）的形式实现，在 SQL Server 环境外执行存储过程的初始处理l存储过程的处理l包括创建，以及初次执行时将执行计划放入过程缓存l过程缓存是一个包含所有当前正在执行的 Transact

51、-SQL 语句的执行计划的内存池，其大小动态变化l过程缓存在内存池内。内存池是 SQL Server 内存的主要单元，它包含了 SQL Server 中大部分使用内存的数据结构存储过程的初始处理（续）l存储过程的创建l创建存储过程时，先分析该过程中的语句以检查语法的准确性。然后 SQL Server 将存储过程的名字存入当前数据库的 sysobjects 系统表中，存储过程的文本存入当前数据库的 syscomments 系统表中l延迟名称解析：存储过程引用的对象不需要在创建该存储过程时就存在，而只需在执行该存储过程时存在l存储过程的执行（初次或重新编译时）l存储过程初次执行或者重新编译后，查询

52、处理器读入存储过程的处理过程称为解析l数据库的某些变化会使得执行计划低效或失效，SQL Server 检测这些变化并自动重新编译执行计划存储过程的初始处理（续）l优化l如果过程执行顺利通过解析阶段，则查询优化器将分析该存储过程中的 Transact-SQL 语句，并创建一个执行计划，描述执行存储过程的最快方法l编译l编译指的是分析存储过程，创建执行计划并将之放入过程缓存的过程l过程缓存包含了最有价值的存储过程执行计划。增加执行计划价值的因素包括：重新编译需要的时间（高的编译代价）和频繁被使用存储过程的初始处理（续）项存入项存入 sysobjects和和 syscomments 表表 编译过的执

53、行计划编译过的执行计划放入过程缓存放入过程缓存编译编译优化优化解析解析存储过程的后续处理l若符合下列条件，则 SQL Server 使用在内存中的计划来执行随后的查询l当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境l存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析lSQL Server 的执行计划有两个主要部分l查询计划：执行计划的主体是一个重入的只读数据结构，可由任意数量的用户使用，这称为查询计划。查询计划中不存储用户环境l执行环境：每个正在执行查询的用户都有一个包含其执行专用数据（如参数值）的数据结构，称为执行环境存储过程的后续

54、处理（续）l在缓存中，对于每个存储过程和环境的组合最多只有一个编译过的计划。对于一个存储过程的多个不同环境，可以有多个计划l形成不同环境并影响编译选择的因素l并行和串行编译计划l隐含的对象拥有l不同的 SET 选项l一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，SQL Server 将老的、没用的计划移出缓存存储过程的后续处理（续）检索到的执行计划检索到的执行计划未用过的计划过时被清除未用过的计划过时被清除执行计划执行计划执行上下文执行上下文SELECT *FROM dbo.memberWHEREmember_no = ?连接连接18082连接连接2连接连接3241003存储过程的优点

55、l存储过程具有许多优点l与其他应用程序共享应用逻辑，确保一致的数据访问和修改。存储过程封装了商务逻辑。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用l屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象l提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限存储过程的优点（续）l存储过程具有许多优点（续）l改善性能。预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分l减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/

56、回答包l自动执行存储过程：在 SQL Server 启动时自动运行特定存储过程语法：sp_procoption 存储过程名, startup, true|falsel存储过程介绍l创建、执行、修改和删除存储过程l在存储过程中使用参数l性能考虑l推荐操作实现存储过程创建、执行、修改和删除存储过程l创建存储过程l创建存储过程的指导原则 l执行存储过程l修改和删除存储过程创建存储过程l创建存储过程l只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中l存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表l若存储过程创建了局部临时表，则当存储过程执

57、行结束后临时表消失USE NorthwindGOCREATE PROC dbo.OverdueOrdersAS SELECT * FROM dbo.Orders WHERE RequiredDate GETDATE() AND ShippedDate IS NullGO创建存储过程（续）l创建存储过程（续）l在单个批处理中，CREATE PROCEDURE 语句不能与其他 Transact-SQL 语句组合使用lCREATE PROCEDURE 定义可以包括任何数目和类型的Transact-SQL语句，但不包括下列对象创建语句：CREATE DEFAULT、CREATE PROCEDURE、C

58、REATE RULE、CREATE TRIGGER 和 CREATE VIEWl执行 CREATE PROCEDURE 语句的用户必须是 sysadmin、db_owner 或 db_ddladmin角色的成员，或必须拥有 CREATE PROCEDURE 权限l依赖于可用内存，存储过程的最大大小为128 MB创建存储过程（续）l嵌套存储过程：一个存储过程调用另一个l存储过程可以嵌套32层。若试图超过32层嵌套，则整个存储过程调用链失败l当前的嵌套层数存储在系统函数 nestlevel 中l若一个存储过程调用了第二个存储过程，那么第二个存储过程可以访问第一个存储过程创建的所有对象，包括临时表l

59、嵌套的存储过程可以递归调用。例如，存储过程X调用了存储过程Y，当存储过程Y运行的时候它可以调用存储过程X创建存储过程（续）l查看存储过程的信息l查看所有类型存储过程的额外信息l系统存储过程 sp_help、sp_helptext、sp_dependsl显示数据库中的存储过程以及拥有者名字的列表l系统存储过程 sp_stored_proceduresl得到存储过程的信息l查询系统表 sysobjects、syscomments、sysdepends创建存储过程的指导原则 l避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象l存储过程的创建者应该拥有存储过程

60、中所引用的所有对象的适当权限l若创建用户定义系统存储过程，必须以 sysadmin 角色成员的身份登录，并使用 master 数据库l每个存储过程完成单个任务l在服务器上创建、测试和排错存储过程，然后从客户端测试l命名本地存储过程的时候，避免使用“sp_”前缀创建存储过程的指导原则（续）l所有存储过程应使用相同的连接设置l尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表l建议使用 sp_executesql 而不要使用 EXECUTE 语句执行字符串l支持参数替换不仅使 sp_executesql 比 EXECUTE 更通用，而且还使 sp_executesql 更有效，因为它