用SELECT语句查询记录-《SQLServer2005程序设计语言》ppt课件

1、SQL Server 2005 数据库程序设计言语数据库程序设计言语第第8章章 数据操作数据操作8.1 用用SELECT语句查询记录语句查询记录 1、检索数据、检索数据 2、数据分组与汇总、数据分组与汇总 3、其它操作、其它操作 1、检索数据、检索数据1.1 运用运用 SELECT 语句检索数据语句检索数据1.2 过滤数据过滤数据1.3 设置结果集格式设置结果集格式1.4 需求思索的性能问题需求思索的性能问题1.5 引荐操作引荐操作 1.1 运用运用 SELECT 语句检索数据语句检索数据1.1.1 运用运用 SELECT 语句语句1.1.2 指定列指定列1.1.3 运用运用 WHERE 子句

2、指定行子句指定行SELECT ALL | DISTINCTTOP n FROM ,n WHERE GROUP BY ,n HAVING ORDER BY ,n根本语法根本语法运用运用 SELECT 语句语句用用 括起来的是可选项，括起来的是可选项，SELECT 是必需的是必需的选择列表指定了要前往的列选择列表指定了要前往的列WHERE 子句指定限制查询的条件子句指定限制查询的条件 在搜索条件中，可以运用比较操作符、在搜索条件中，可以运用比较操作符、字符串、逻辑操作符来限制前往的行数字符串、逻辑操作符来限制前往的行数1.1.1 运用运用 SELECT 语句语句运用运用 SELECT 语句续语句续

3、FROM 子句知道了前往的行和列所属的表子句知道了前往的行和列所属的表DISTINCT 选项从结果集中消除了反复的行，选项从结果集中消除了反复的行，TOP n 选项限定了要前往的行数选项限定了要前往的行数GROUP BY 子句是对结果集进展分组子句是对结果集进展分组HAVING 子句是在分组的时候，对字段或表子句是在分组的时候，对字段或表达式指定搜索条件达式指定搜索条件ORDER BY 子句对结果集按某种条件进展排子句对结果集按某种条件进展排序序 1.1.1 运用运用 SELECT 语句语句指定列指定列1.1.2 指定列指定列在选择列表中指定要从表中前往的列在选择列表中指定要从表中前往的列在选

4、择列表中可以包含字段名、表达式、所选关键在选择列表中可以包含字段名、表达式、所选关键字或要赋值的部分变量。其语法为字或要赋值的部分变量。其语法为 := *| 表名表名 | 视图视图名名 | 表的表的别别名名.* |字段名字段名 | 表达式表达式 |$IDENTITY | $ROWGUID AS 字段字段别别名名 | 字段字段别别名名 = 表达式表达式 ,.n l指定要检索的字段时，请留意指定要检索的字段时，请留意l选择列表将按照指定顺序查找并显示所查询的字选择列表将按照指定顺序查找并显示所查询的字段段l不同的字段名用不同的字段名用“，分开，分开l在选择列表中，防止或尽量少运用符号在选择列表中，

5、防止或尽量少运用符号“ * 。该。该符号代表选择表中一切的字段符号代表选择表中一切的字段运用运用 WHERE 子句指定行子句指定行1.1.3 运用运用 WHERE 子句指定行子句指定行经过运用经过运用 WHERE 子句，可以得到基于搜索条件的行子句，可以得到基于搜索条件的行 := NOT | ( ) AND | ORNOT | ( ) ,.n l列出了可以在列出了可以在 WHERE 子句中包含的表达式子句中包含的表达式列表列表l运用运用 WHERE 子句指定行时，请留意子句指定行时，请留意l在一切数据类型为在一切数据类型为 char、nchar、varchar、nvarchar、text、da

6、tetime和和 smalldatetime 的数据周围，必需的数据周围，必需运用单引号运用单引号l在运用在运用 SELECT 语句时，尽量运用语句时，尽量运用 WHERE 语句限语句限制将要前往的行制将要前往的行运用运用 WHERE 子句指定行续子句指定行续5BuchananStevenSales ManagerUSE northwindSELECT employeeid, lastname, firstname, titleFROM employeesWHERE employeeid = 5GO例如例如 1.1.3 运用运用 WHERE 子句指定行子句指定行 1、检索数据、检索数据1.1

7、运用运用 SELECT 语句检索数据语句检索数据1.2 过滤数据过滤数据1.3 设置结果集格式设置结果集格式1.4 需求思索的性能问题需求思索的性能问题1.5 引荐操作引荐操作 过滤数据过滤数据1.2 过滤数据过滤数据过滤的类型过滤的类型搜索条件搜索条件运用比较运算符运用比较运算符、=、=、运用字符串比较符运用字符串比较符LIKE和和NOT LIKE运用逻辑运算符运用逻辑运算符AND、OR检索一定范围内的值检索一定范围内的值BETWEEN和和NOT BETWEEN运用值列表作为搜索条运用值列表作为搜索条件件IN和和NOT IN检索未知值检索未知值IS NULL和和IS NOT NULL运用比较

8、运算符运用比较运算符1.2.1 运用比较运算符运用比较运算符运用比较操作符来比较表中的值与指定的值或表运用比较操作符来比较表中的值与指定的值或表达式的值达式的值USE northwindSELECT lastname, cityFROM employeesWHERE country = USAGO运用字符串比较符运用字符串比较符1.2.2 运用字符串比较符运用字符串比较符l在比较字符串时，可以运用在比较字符串时，可以运用 LIKE 和通配符来查找和通配符来查找需求的行需求的行通配符的种类通配符的种类通配符通配符描画描画%0或多个字符串或多个字符串_任何任何单单个的字符个的字符在指定区域或集合内

9、的任何在指定区域或集合内的任何单单个字符个字符不在指定区域或集合内的任何不在指定区域或集合内的任何单单个字符个字符运用字符串比较符续运用字符串比较符续1.2.2 运用字符串比较符运用字符串比较符表达式表达式LIKE %EENLIKE _EENLIKE CKLIKE S-VLIKE 990-90-9LIKE A-Z0-9FMLIKE MCLIKE 5_% ESCAPE LIKE ABA-Z ESCAPE 运用逻辑运算符运用逻辑运算符1.2.3 运用逻辑运算符运用逻辑运算符l用逻辑操作符用逻辑操作符 AND、OR 和和 NOT 来衔接一系列的表来衔接一系列的表达式，及优化查询过程。运用逻辑运算符时

10、，遵达式，及优化查询过程。运用逻辑运算符时，遵照以下原那么照以下原那么l运用运用 AND 前往满足一切条件的行前往满足一切条件的行l运用运用 OR 前往满足任一条件的行前往满足任一条件的行l运用运用 NOT 前往不满足条件的行前往不满足条件的行l有多个表达式时，运用圆括号有多个表达式时，运用圆括号l为表达式分组为表达式分组l改动表达式求值顺序改动表达式求值顺序l添加表达式的可读性添加表达式的可读性运用逻辑运算符续运用逻辑运算符续l搜索条件的顺序搜索条件的顺序lMicrosoft SQL Server 首先求首先求 NOT 表达式的值，然后表达式的值，然后是是 AND，最后是，最后是 ORl当表

11、达式中一切的操作符优先级一样时，求值顺序当表达式中一切的操作符优先级一样时，求值顺序由左到右由左到右1.2.3 运用逻辑运算符运用逻辑运算符(productname LIKE T% OR productid = 46) AND (unitprice 16.00) (productname LIKE T% )OR (productid = 46 AND unitprice 16.00) 检索一定范围内的值检索一定范围内的值运用运用 BETWEEN 来查询在一定范围内的值，来查询在一定范围内的值，运用运用 BETWEEN 时，留意时，留意SQL Server 前往的结果集中，包含范围内的前往的结果

12、集中，包含范围内的边缘值边缘值尽量运用尽量运用 BETWEEN，而不用，而不用 AND 和比较操和比较操作符表示的表达式作符表示的表达式=x AND x AND y要前往不在指定区域的行时，运用要前往不在指定区域的行时，运用 NOT BETWEEN1.2.4 检索一定范围内的值检索一定范围内的值unitprice BETWEEN 10 AND 20运用值列表作为搜索条件运用值列表作为搜索条件1.2.5 运用值列表作为搜索条件运用值列表作为搜索条件l运用运用 IN 语句指定一个值的列表作为查询条件。运语句指定一个值的列表作为查询条件。运用用 IN 时，留意时，留意l运用运用 IN 搜索条件和运用

13、由搜索条件和运用由 OR 操作符连起来的一系操作符连起来的一系列比较操作符，其效果是等价的列比较操作符，其效果是等价的l在搜索条件中不能包含在搜索条件中不能包含 NULL 值，这将前往不可预值，这将前往不可预测的结果集测的结果集l运用运用 NOT IN 搜索条件来前往值不在指定列表中的搜索条件来前往值不在指定列表中的行。运用行。运用 NOT 会降低数据检索速度会降低数据检索速度country IN (Japan, Italy)country= Japan or country= Italy检索未知值检索未知值1.2.6 检索未知值检索未知值l输入数据时，没有指定一个字段的值，且此字段输入数据时

14、，没有指定一个字段的值，且此字段没有定义默许值的值，称此字段含有空值没有定义默许值的值，称此字段含有空值NULLl运用运用 IS NULL 搜索条件来查询某指定列没任何信息搜索条件来查询某指定列没任何信息的行。需求前往含有空值的行时，留意的行。需求前往含有空值的行时，留意l把空值和任何表达式比较都会失败，由于空值不把空值和任何表达式比较都会失败，由于空值不等于任何表达式等于任何表达式l在在 CREATE TABLE 语句里，定义一个字段能否允许语句里，定义一个字段能否允许空值的存在空值的存在l运用运用 IS NOT NULL 来查询指定列中非空的行来查询指定列中非空的行Score IS NUL

15、L 1、检索数据、检索数据1.1 运用运用 SELECT 语句检索数据语句检索数据1.2 过滤数据过滤数据1.3 设置结果集格式设置结果集格式1.4 需求思索的性能问题需求思索的性能问题1.5 引荐操作引荐操作 设置结果集格式设置结果集格式对数据进展排序对数据进展排序消除反复行消除反复行改动列名改动列名运用字面值运用字面值FOR子句子句UNION ALL1.3 设置结果集格式设置结果集格式对数据进展排序对数据进展排序1.3.1 对数据进展排序对数据进展排序l运用运用 ORDER BY 子句为结果集中的行排序时，有升子句为结果集中的行排序时，有升序和降序两种。运用序和降序两种。运用ORDER B

16、Y 时，留意时，留意l除非运用除非运用 ORDER BY 指定顺序，指定顺序，SQL Server 并不自并不自动为结果集中的行排序动为结果集中的行排序lSQL Server 缺省地按升序陈列缺省地按升序陈列lORDER BY 子句中指定的列并不一定要出如今选择子句中指定的列并不一定要出如今选择列表中列表中l可以按照字段名、计算出的值或表达式进展排序可以按照字段名、计算出的值或表达式进展排序l可以在可以在 ORDER BY 子句中援用某字段在选择列表中子句中援用某字段在选择列表中的位置而进展排序的位置而进展排序lORDER BY 子句不能用于数据类型子句不能用于数据类型 text 、ntext

17、或或 image 字段字段对数据进展排序续对数据进展排序续USE northwindSELECT productid, productname, categoryid, unitprice FROM products ORDER BY categoryid, unitprice DESCGO38Cote de Blaye1263.500043Ipoh Coffee146.00002Chang119.000063Vegie-spread243.90008Northwoods Cranberry Sauce240.000061Sirop drable228.5000例如例如1.3.1 对数据进展排

18、序对数据进展排序消除反复行消除反复行1.3.2 消除反复行消除反复行l用用 DISTINCT 可以消除反复行，使结果集列出独一可以消除反复行，使结果集列出独一的值。运用的值。运用 DISTINCT 时，留意时，留意l除非指定除非指定 DISTINCT 子句，否那么子句，否那么 SELECT 语句将前语句将前往一切满足搜索条件的行往一切满足搜索条件的行l选择列表中一切列的组合值将决议其独一性选择列表中一切列的组合值将决议其独一性l包含任何独一组合值的行都将被检索到并被前往包含任何独一组合值的行都将被检索到并被前往到结果集中到结果集中l除非运用了除非运用了 ORDER BY 子句，否那么子句，否那

19、么 DISTINCT 子句子句将按随机的顺序显示结果集中的行将按随机的顺序显示结果集中的行消除反复行续消除反复行续USE northwindSELECT DISTINCT country FROM suppliers ORDER BY countryGOAustraliaBrazilCanadaDenmarkFinlandFranceGermanyItalyJapanNetherlandsNorwaySingaporeSpainSwedenUKUSA例如例如1.3.2 消除反复行消除反复行改动列名改动列名1.3.3 改动列名改动列名l为了提高可读性，可以为选择列表中的字段名为了提高可读性，可以

20、为选择列表中的字段名起别名起别名l改动字段名时，应留意改动字段名时，应留意l缺省情况下，结果集所显示的字段名，就是在缺省情况下，结果集所显示的字段名，就是在 CREATE TABLE 语句中指定的字段名语句中指定的字段名l假设字段名中有空格，或字段名不符合假设字段名中有空格，或字段名不符合 SQL Server 命名规那么，用单引号来标识这个字段名命名规那么，用单引号来标识这个字段名l可以为经过计算的包含函数、字符符号的字段可以为经过计算的包含函数、字符符号的字段起别名起别名l字段名中至多可以包含字段名中至多可以包含128个字符个字符改动列名续改动列名续USE northwindSELECT

21、firstname AS First, lastname Last ,Employee ID: =employeeid FROM employeesNancyDavolio1AndrewFuller2JanetLeverling3MargaretPeacock4StevenBuchanan5MichaelSuyama6RobertKing7LauraCallahan8AnneDodsworth91.3.3 改动列名改动列名例如例如运用字面值运用字面值1.3.4 运用字面值运用字面值l运用字面值可以添加结果集的可读性，符号能够运用字面值可以添加结果集的可读性，符号能够是字母、数字或标识，它们被用

22、作特定的值。其是字母、数字或标识，它们被用作特定的值。其语法为语法为SELECT 字段名字段名 |字符串符号字符串符号 ，字段名，字段名 |字符串符号字符串符号 FROM 表名表名USE northwindSELECT firstname, lastname ,Identification number:, employeeidFROM employeesGOFORFOR子句子句FOR子句用来指定子句用来指定BROWSE或或XML选项，主要在运用选项，主要在运用程序的开发中经常运用。程序的开发中经常运用。FOR子句生成子句生成XML的详细的详细运用引见请参考第运用引见请参考第24章。本节经过一

23、个运用章。本节经过一个运用FOR子子句的例如，简单阐明其普通的功能。句的例如，简单阐明其普通的功能。1.3.5 FOR子句子句USE northwindSELECT country FROM suppliers ORDER BY country for xml autoGO例如例如UNION ALLUNION ALL将两个或更多查询的结果合并为单个结果集，该结将两个或更多查询的结果合并为单个结果集，该结果集包含结合查询中的一切查询的全部行。果集包含结合查询中的一切查询的全部行。UNION 运算不同于运用联接合并两个表中的列的运算。运算不同于运用联接合并两个表中的列的运算。 1.3.6 UNIO

24、N ALLUSE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel UNION SELECT ProductModelID, Name FROM dbo.ProductModel2 ORDER BY Name ;运用运用 UNION 合并两个查询结果集的根本规那么：合并两个查询结果集的根本规那么： 一切查询中的列数和列的顺序必需一样。一切查询中的列数和列的顺序必需一样。数据类型必需兼容。数据类型必需兼容。需求思索的性能问题需求思索的性能问题非逻辑操作会降低查询速度非逻辑操作会降低查询速度 比如：比如：

25、NOT BETWEEN 、NOT IN 和和 IS NOT NULL LIKE 搜索条件会降低查询速度搜索条件会降低查询速度运用准确查询或在一定范围的查询能够会提运用准确查询或在一定范围的查询能够会提高数据查询速度高数据查询速度ORDER BY 子句能够降低数据查询速度子句能够降低数据查询速度 由于由于 SQL Server 在前往第一行前，必需对在前往第一行前，必需对结果集中一切的行进展计算并排序结果集中一切的行进展计算并排序1.4 需求思索的性能问题需求思索的性能问题引荐操作引荐操作运用运用 DISTINCT 子句消除结果集中反复的行子句消除结果集中反复的行经过改动字段名和运用字面值来提高

26、结果集的可读性经过改动字段名和运用字面值来提高结果集的可读性提出问题提出问题我们遇到了下面的问题：我们拥有桌子和椅子两种我们遇到了下面的问题：我们拥有桌子和椅子两种产品，每种产品都有红色和蓝色两种颜色。产品，每种产品都有红色和蓝色两种颜色。每种颜色的桌子有多少库存，每种颜色的椅子有多每种颜色的桌子有多少库存，每种颜色的椅子有多少库存。少库存。椅子有多少库存，不论它是什么颜色；桌子有多少椅子有多少库存，不论它是什么颜色；桌子有多少库存，不论它是什么颜色。库存，不论它是什么颜色。红色的商品一共有多少库存，蓝色的商品一共有多红色的商品一共有多少库存，蓝色的商品一共有多少库存。少库存。这个时候大家发现

27、，我们需求的是以不同商品种类这个时候大家发现，我们需求的是以不同商品种类和颜色进展陈列组合的小计报表。和颜色进展陈列组合的小计报表。8.2、 数据分组与汇总数据分组与汇总运用运用 TOP n 列出前列出前 n 个记录个记录运用运用 TABLESAMPLE 限制结果集限制结果集运用聚合函数运用聚合函数GROUP BY 的根底知识的根底知识在结果集中生成汇总值在结果集中生成汇总值运用运用 COMPUTE 和和 COMPUTE BY 子句子句引荐操作引荐操作运用运用 TOP n 列出前列出前 n 个记录个记录8.2.1 运用运用 TOP n 列出前列出前 n 个记录个记录l关键字关键字 TOP n

28、只列出结果集中前只列出结果集中前 n 个或个或 前前 n% 的记的记录。运用录。运用 TOP n 或或 TOP n PERCENT 时，应留意时，应留意l在在 ORDER BY 子句中指定值的范围子句中指定值的范围l关键字关键字 TOP 后运用无符号的整数后运用无符号的整数l假设假设 TOP n PERCENT 生成小数，那么生成小数，那么 SQL Server 将将把这个数取整把这个数取整l可以在结果集中用可以在结果集中用 WITH TIES 子句包含那些值相等子句包含那些值相等的记录，这时结果集中可以包含恣意数目的行的记录，这时结果集中可以包含恣意数目的行部分语法：部分语法：SELECT

29、ALL | DISTINCT SELECT ALL | DISTINCT TOP n PERCENT WITH TIES TOP n PERCENT WITH TIES 要求：要求：1、检索出、检索出SC表中的一切成果表中的一切成果记录记录2、检索出、检索出SC表中的前表中的前5条成果条成果记录记录3、检索出、检索出SC表中的前表中的前50%的成的成果记录果记录4、检索出、检索出SC表中的前表中的前8%的成的成果记录。留意察看有几条记录果记录。留意察看有几条记录?5、检索出、检索出SC表中成果前表中成果前5名的名的学生学号学生学号1 1、select select * * from sc fr

30、om sc运用运用 TOP n 列出前列出前 n 个记录个记录8.2.1 运用运用 TOP n 列出前列出前 n 个记录个记录2 2、select top 5 select top 5 * * from sc from sc3 3、select top 50 percent select top 50 percent * * from sc from sc4 4、select top 8 percent select top 8 percent * * from sc from scselect top 5 with tiesselect top 5 with ties* * from sc

31、from scorder by score descorder by score descselect top 5 select top 5 * * from sc from scorder by score descorder by score desc运用运用 TOP n 列出前列出前 n 个记录个记录8.2.1 运用运用 TOP n 列出前列出前 n 个记录个记录限制结果集大小的另一种方法是在执行限制结果集大小的另一种方法是在执行一个语句之前执行一个语句之前执行 SET ROWCOUNT n 语语句。句。SET ROWCOUNT 与与 TOP 的不同之的不同之处在于：处在于： TOP 子

32、句适用于指定了该子句的单个子句适用于指定了该子句的单个 SELECT 语句。在执行另一个语句。在执行另一个 SET ROWCOUNT 语句之前，语句之前，SET ROWCOUNT 会不断有效，例如执行会不断有效，例如执行 SET ROWCOUNT 0 将会封锁此选项。将会封锁此选项。SET ROWCOUNT n运用运用 TOP n 列出前列出前 n 个记录个记录运用运用 TABLESAMPLE 限制结果集限制结果集运用聚合函数运用聚合函数GROUP BY 的根底知识的根底知识在结果集中生成汇总值在结果集中生成汇总值运用运用 COMPUTE 和和 COMPUTE BY 子句子句引荐操作引荐操作8

33、.2、 数据分组与汇总数据分组与汇总运用运用 TABLESAMPLE 限制结果集限制结果集 8.2.2运用运用 TABLESAMPLE 限制结果集限制结果集 lTABLESAMPLE 子句将从子句将从 FROM 子句中的表前往的行子句中的表前往的行数限制到样本数或行数的某一百分比。数限制到样本数或行数的某一百分比。l不能对视图运用不能对视图运用 TABLESAMPLE。部分语法：部分语法：TABLESAMPLE SYSTEM (sample_number PERCENT | TABLESAMPLE SYSTEM (sample_number PERCENT | ROWS ) ROWS ) -执

34、行几次代码，所得到的结果是不同的执行几次代码，所得到的结果是不同的 USE AdventureWorks ;USE AdventureWorks ; GOGO SELECT count(SELECT count(* *) ) FROM Sales.Customer TABLESAMPLE SYSTEM (10 FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;PERCENT) ;运用运用 TABLESAMPLE TABLESAMPLE 限制结果集限制结果集 运用运用 TOP n 列出前列出前 n 个记录个记录运用运用 TABLESAMPL

35、E 限制结果集限制结果集运用聚合函数运用聚合函数GROUP BY 的根底知识的根底知识在结果集中生成汇总值在结果集中生成汇总值运用运用 COMPUTE 和和 COMPUTE BY 子句子句引荐操作引荐操作8.2、 数据分组与汇总数据分组与汇总运用聚合函数续运用聚合函数续8.2.3 运用聚合函数运用聚合函数l计算诸如平均值和总和的函数称为聚合函数计算诸如平均值和总和的函数称为聚合函数lSQL Server 对整个表或表里某个组中的字段进展汇对整个表或表里某个组中的字段进展汇总、计算，然后生成单个的值总、计算，然后生成单个的值l可以在可以在 SELECT 语句中单独运用聚合函数，也可以语句中单独运

36、用聚合函数，也可以与语句与语句 GROUP BY 结合运用结合运用l除了除了 COUNT函数，假设记录集中没有满足函数，假设记录集中没有满足 WHERE 子句的记录，那么一切函数前往空值，子句的记录，那么一切函数前往空值， COUNT前前往往 0l字段的数据类型决议了可以用在该字段上的聚合字段的数据类型决议了可以用在该字段上的聚合函数类型函数类型l如：如：SUM 和和 AVG 只能用在数据类型代表数字的字只能用在数据类型代表数字的字段上段上SUM和和AVG。 SUM和和AVG分别用于求表达式中一切分别用于求表达式中一切值项的总和与平均值。语法格式：值项的总和与平均值。语法格式： SUM | A

37、VG (ALL | DISTINCT expression) SUM | AVG忽略空值。忽略空值。运用聚合函数续运用聚合函数续8.2.3 运用聚合函数运用聚合函数运用聚合函数续运用聚合函数续MAX和和MIN。 MAX和和MIN分别用于求表达式中一切值分别用于求表达式中一切值项的最大值和最小值。语法格式：项的最大值和最小值。语法格式： MAX | MIN (ALL | DISTINCT expression) 只能用在数字、字符、和时间日期数据类型。只能用在数字、字符、和时间日期数据类型。MAX | MIN忽略空值。忽略空值。8.2.3 运用聚合函数运用聚合函数COUNT与与COUNT(*)。

38、用于统计组中满足条件的行数或。用于统计组中满足条件的行数或总行数。语法格式：总行数。语法格式： COUNT ( ALL | DISTINCT expression | *) 选择选择*时将统计总行数。时将统计总行数。COUNT忽略空值，忽略空值， COUNT(*)计算空值。计算空值。运用聚合函数续运用聚合函数续8.2.3 运用聚合函数运用聚合函数运用含有空值的聚合函数运用含有空值的聚合函数空值能使聚合函数产生意想不到的结果空值能使聚合函数产生意想不到的结果对包含空值的字段运用聚合函数时，应留意对包含空值的字段运用聚合函数时，应留意SQL Server 的聚合函数的聚合函数COUNT(*) 除外

39、将忽略字段除外将忽略字段中的空值中的空值COUNT*将计算一切的行，即使每个字段都含有将计算一切的行，即使每个字段都含有空值空值 运用含有空值的聚合函数运用含有空值的聚合函数create table table1(sno char(5),age tinyint)insert into table1 values(95001,null)insert into table1 values(95002,null)insert into table1 values(null,null)insert into table1 values(95003,null)-以下前往的结果分别是什么以下

40、前往的结果分别是什么select count(*) from table1select count(sno)from table1select count(age)from table1select avg(age)from table1ISNULL 假设第一个表达式为非假设第一个表达式为非 NULL，前往第，前往第一个表达式，否那么前往第二个表达式一个表达式，否那么前往第二个表达式ISNULL( )的运用的运用 ISNULL(表达式表达式1，表达式，表达式2)ISNULL( )的运用的运用 select avg(salary) select avg(salary) from avg_test

41、from avg_testselect select avg(isnull(salary,0) avg(isnull(salary,0) from avg_testfrom avg_testl常见的聚合函数及其描画常见的聚合函数及其描画聚合函数聚合函数描画描画AVGAVG前往组中值的平均值。空值将被忽略。前往组中值的平均值。空值将被忽略。COUNTCOUNT表达式中值的数目表达式中值的数目COUNT (COUNT (* *) ) 所选择的行的数目所选择的行的数目MAXMAX前往表达式的最大值。前往表达式的最大值。MAX MAX 忽略任何空值。对于字符列，忽略任何空值。对于字符列，MAX MAX

42、 查找排序序列查找排序序列的最大值。的最大值。MINMIN前往表达式的最小值。前往表达式的最小值。MIN MIN 忽略任何空值。对于字符列，忽略任何空值。对于字符列，MIN MIN 查找排序序列查找排序序列的最低值。的最低值。SUMSUM前往表达式中一切值的和，或只前往前往表达式中一切值的和，或只前往 DISTINCT DISTINCT 值。值。SUM SUM 只能用于数字列。只能用于数字列。空值将被忽略。空值将被忽略。GROUPINGGROUPING是一个聚合函数是一个聚合函数, ,它产生一个附加的列，当用它产生一个附加的列，当用 CUBE CUBE 或或 ROLLUP ROLLUP 运算符

43、添加行运算符添加行时，附加的列输出值为时，附加的列输出值为1 1，当所添加的行不是由，当所添加的行不是由 CUBE CUBE 或或 ROLLUP ROLLUP 产生时，产生时，附加列值为附加列值为0 0。仅在与包含。仅在与包含 CUBE CUBE 或或 ROLLUP ROLLUP 运算符的运算符的 GROUP BY GROUP BY 子句相联子句相联络的选择列表中才允许分组。络的选择列表中才允许分组。分组用于区分由分组用于区分由 CUBE CUBE 和和 ROLLUP ROLLUP 前往的空值和规范的空值。作为前往的空值和规范的空值。作为CUBE CUBE 或或 ROLLUP ROLLUP 操

44、作结果前往的操作结果前往的 NULL NULL 是是 NULL NULL 的特殊运用。它在结果集内作为列的特殊运用。它在结果集内作为列的占位符，意思是的占位符，意思是 全体全体 。运用运用 TOP n 列出前列出前 n 个记录个记录运用运用 TABLESAMPLE 限制结果集限制结果集运用聚合函数运用聚合函数GROUP BY 的根底知识的根底知识在结果集中生成汇总值在结果集中生成汇总值运用运用 COMPUTE 和和 COMPUTE BY 子句子句引荐操作引荐操作8.2、 数据分组与汇总数据分组与汇总GROUP BY 的根底知识的根底知识运用运用 GROUP BY 子句子句结合运用结合运用 GR

45、OUP BY 子句和子句和 HAVING 子句子句8.2.4 GROUP BY 的根底知识的根底知识n结合运用聚合函数和结合运用聚合函数和 GROUP BY 子句，可以把表中子句，可以把表中的数据按字段分组，并对组中数据进展汇总。的数据按字段分组，并对组中数据进展汇总。n语法：语法：n GROUP BY ALLgroup_by_expression,nn WITH CUBE | ROLLUP n group_by_expression 用于分组的表达式，用于分组的表达式，WITH指定指定CUBE或或ROLLUP操作符。操作符。GROUP BY 的根底知识的根底知识8.2.4 GROUP BY

46、的根底知识的根底知识USE northwindSELECT productid, orderid ,quantity FROM orderhistGO11523534511512102110222531153230235只对满足只对满足 WHERE 子句的行分组子句的行分组USE northwindSELECT productid ,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productidGO例如例如 2USE northwindSELECT productid ,SUM(quan

47、tity) AS total_quantity FROM orderhist GROUP BY productidGO例如例如 1GROUP BY 的根底知识的根底知识8.2.4 GROUP BY 的根底知识的根底知识运用运用 GROUP BY 子句时，应留意子句时，应留意SQL Server 将为每一组计算一个汇总值，并把汇总值将为每一组计算一个汇总值，并把汇总值保管在一个字段中保管在一个字段中对于指定的一组，对于指定的一组，SQL Server 只生成一条记录，不前只生成一条记录，不前往详细信息往详细信息SQL Server 只对满足只对满足 WHERE 子句的记录进展分组和子句的记录进展

48、分组和汇总汇总不要对可包含空值的字段运用不要对可包含空值的字段运用 GROUP BY 子句，由子句，由于空值也将被当作一组于空值也将被当作一组结合运用关键字结合运用关键字 ALL 和和 GROUP BY 子句时，组合字子句时，组合字段中含有空值的行都将被列出，而不论记录能否满段中含有空值的行都将被列出，而不论记录能否满足足 WHERE 子句的条件子句的条件运用运用 GROUP BY 子句子句 运用运用 GROUP BY 子句子句-下面的例子给出了针对一切下面的例子给出了针对一切type和和pub_id的独一组合进展的独一组合进展分类并统计计算的情况。分类并统计计算的情况。use

49、pubsgoselect type,pub_id,avg(price) avg_price,sum(price) sum_pricefrom titleswhere type in (business,trad_cook)group by all type,pub_idorder by typecreate table p1(sno char(3),sex char(8),age int )insert into p1 values (001,m,20)insert into p1 values (null,null,null)insert into p1 values (null,null,

50、null)select count(sno) from p1 group by snol不要对可包含空值的字段运用不要对可包含空值的字段运用 GROUP BY 子句，由于空值也将被当子句，由于空值也将被当作一组作一组运用运用 GROUP BY 子句续子句续SELECT productid, orderid ,quantity FROM orderhistGOSELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productidGO11523534511511102110222531153230235

51、只对满足只对满足 WHERE 子句的行分组子句的行分组SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productidGO例如例如 1例如例如 运用运用 GROUP BY 子句子句结合运用结合运用 GROUP BY 子句和子句和 HAVING 子句子句 结合运用结合运用 GROUP BY 子句和子句和 HAVING 子句子句l在分组的同时，对字段或表达式指定搜索条件在分组的同时，对字段或表达式指定搜索条件l运用运用 HAV

52、ING 子句时，应留意子句时，应留意l只在运用只在运用 GROUP BY 子句的同时，运用子句的同时，运用 HAVING 子子句来限制分组句来限制分组l可以援用任何出如今选择列表中的字段可以援用任何出如今选择列表中的字段l不要结合运用关键字不要结合运用关键字 ALL 和和 HAVING 子句。子句。 由于由于 HAVING 子句会忽略子句会忽略 ALL 关键字，并前往只符合关键字，并前往只符合 HAVING 条件的组条件的组结合运用结合运用 GROUP BY 子句和子句和 HAVING 子句续子句续SELECT productid, orderid ,quantity FROM orderhi

53、stGOSELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)=30GO23534511512102110222531153230 结合运用结合运用 GROUP BY 子句和子句和 HAVING 子句子句WHERE 子句与子句与HAVING子句的区别：子句的区别： WHERE子句作用于根本表或视图，从中选择满足子句作用于根本表或视图，从中选择满足条件的记录；条件的记录； HAVING子句作用于组，从中选择满足条件的组子句作用于组

54、，从中选择满足条件的组结合运用结合运用 GROUP BY 子句和子句和 HAVING 子句续子句续 结合运用结合运用 GROUP BY 子句和子句和 HAVING 子句子句运用运用 TOP n 列出前列出前 n 个记录个记录运用运用 TABLESAMPLE 限制结果集限制结果集运用聚合函数运用聚合函数GROUP BY 的根底知识的根底知识在结果集中生成汇总值在结果集中生成汇总值运用运用 COMPUTE 和和 COMPUTE BY 子句子句引荐操作引荐操作8.2、 数据分组与汇总数据分组与汇总在结果集中生成汇总值在结果集中生成汇总值运用带有运用带有 CUBE 运算符的运算符的 GR

55、OUP BY 子句子句运用带有运用带有 ROLLUP 运算符的运算符的 GROUP BY 子句子句运用运用 GROUPING 函数函数8.2.5 在结果集中生成汇总值在结果集中生成汇总值运用带有运用带有 CUBE 运算符的运算符的 GROUP BY 子句子句 运用带有运用带有 CUBE 运算符的运算符的 GROUP BY 子句子句l运用带有运用带有CUBE运算符的运算符的GROUP BY子句创建并汇总子句创建并汇总一切能够的基于一切能够的基于GROUP BY子句分组的组合。子句分组的组合。l运用带有运用带有CUBE运算符的运算符的GROUP BY子句用于提供规子句用于提供规范的关

56、系型格式的数据。范的关系型格式的数据。select item,color,sum(quantity)from cpgroup by item,colorwith cubechair、blue的详细值的详细值chair、red的详细值的详细值汇总汇总chair的一切记录的一切记录table、blue的详细值的详细值table、red的详细值的详细值汇总汇总table的一切记录的一切记录总计总计汇总汇总bule的一切记录的一切记录汇总汇总red的一切记录的一切记录每种产品的每种颜色的总数量，每种产品的一切颜色的总数量，每种产品的每种颜色的总数量，每种产品的一切颜色的总数量，每种颜色的一切产品的总数

57、量，一切产品一切颜色的总数量。每种颜色的一切产品的总数量，一切产品一切颜色的总数量。运用带有运用带有 CUBE 运算符的运算符的 GROUP BY 子句子句 运用带有运用带有 CUBE 运算符的运算符的 GROUP BY 子句子句l运用运用 GROUP BY子句和子句和 CUBE 操作符时，应留意操作符时，应留意l假设在假设在 GROUP BY 子句中有子句中有 n 个字段或表达式，个字段或表达式，SQL Server 将在结果集中前往将在结果集中前往 2n 种能够的组合种能够的组合l结果集中含有结果集中含有 NULL 的记录代表该记录由的记录代表该记录由 CUBE 操作操作符生

58、成留意此符生成留意此NULL不是根本表中的实践空值不是根本表中的实践空值l不能同时运用关键字不能同时运用关键字 ALL 和操作符和操作符 CUBE运用带有运用带有 ROLLUP 运算符的运算符的 GROUP BY 子句子句 运用带有运用带有 ROLLUP 运算符的运算符的 GROUP BY 子句子句lROLLUP运算符的功能类似于运算符的功能类似于CUBE运算符。运算符。l运用带有运用带有ROLLUP运算符的运算符的GROUP BY子句汇总分组值子句汇总分组值l运用带有运用带有ROLLUP运算符的运算符的GROUP BY子句用于提供规子句用于提供规范的关系型格式的数据。范的关系型

59、格式的数据。select item,color,sum(quantity)from cpgroup by item,colorwith rollupchair、blue的详细值的详细值chair、red的详细值的详细值汇总汇总chair的一切记录的一切记录table、blue的详细值的详细值table、red的详细值的详细值汇总汇总table的一切记录的一切记录总计总计每种产品的每种颜色的总数量，每种产品的一切颜色的总数量，每种产品的每种颜色的总数量，每种产品的一切颜色的总数量，一切产品一切颜色的总数量。一切产品一切颜色的总数量。运用带有运用带有 ROLLUP 运算符的运算符的 GROUP B

60、Y 子句子句 运用带有运用带有 ROLLUP 运算符的运算符的 GROUP BY 子句子句l运用运用 GROUP BY 子句和子句和 ROLLUP 操作符时，应留意操作符时，应留意lSQL Server 将在结果集中添加一行，这行将显示总和将在结果集中添加一行，这行将显示总和或平均值之类的汇总值，新增的行以或平均值之类的汇总值，新增的行以 NULL 标识标识l不能同时运用关键字不能同时运用关键字 ALL 和操作符和操作符 ROLLUP运用运用 GROUPING 函数函数 运用运用 GROUPING 函数函数l如何区分如何区分 CUBE 操作或操作或ROLLUP运算符