第7章表中数据排序.ppt

第第7 7章章 表中数据的排序与分组表中数据的排序与分组在前面介绍的使用在前面介绍的使用SQL语句执行查询操作时，读者可能语句执行查询操作时，读者可能发现查询出的数据结果的排序是无序的。为了更好的观察数发现查询出的数据结果的排序是无序的。为了更好的观察数据表中的查询结果，开发人员或者用户经常要对查询的数据据表中的查询结果，开发人员或者用户经常要对查询的数据进行排序操作，这就需要使用进行排序操作，这就需要使用ORDER BY子句。在数据库的子句。在数据库的实际应用中，有时需要对查询的数据进行统计和分组操作，实际应用中，有时需要对查询的数据进行统计和分组操作，这就需要了解这就需要了解SQL语句的聚合函数和语句的聚合函数和GROUP BY子句的使用。子句的使用。这一章将主要对这两部分的内容进行介绍。有些时候开发人这一章将主要对这两部分的内容进行介绍。有些时候开发人员或者用户还希望对分组后的结果做进一步的统计，在员或者用户还希望对分组后的结果做进一步的统计，在SQL语句中提供了语句中提供了ROLLUP这样一个关键字用来对数据进行统计。这样一个关键字用来对数据进行统计。在本章的最后还将介绍主要数据库中如何限制结果集的行数。在本章的最后还将介绍主要数据库中如何限制结果集的行数。第第7 7章章 表中数据的排序与分组表中数据的排序与分组指定表中的一列进行排序指定表中的一列进行排序指定表中列的位置序号进行排序指定表中列的位置序号进行排序对对SELECT语句中的非选择列进行排序语句中的非选择列进行排序指定表中的多列进行排序指定表中的多列进行排序常用的聚合函数常用的聚合函数单列分组与多列分组单列分组与多列分组使用使用HAVING子句限制分组后的查询结果子句限制分组后的查询结果对分组结果进行排序对分组结果进行排序使用使用ROLLUP关键字统计数据关键字统计数据不同数据库中限制结果集行数的方法不同数据库中限制结果集行数的方法7.1 7.1 使用使用ORDER BY ORDER BY 子句对数据记录进行排序子句对数据记录进行排序如果想如果想SELECT语句中对所查询的结果按照某种顺序进语句中对所查询的结果按照某种顺序进行排序操作，那么就需要使用行排序操作，那么就需要使用ORDER BY 子句。使用子句。使用ORDER BY子句可以对数据表中指定的某一列进行排序，也子句可以对数据表中指定的某一列进行排序，也可以对数据表中指定的多个列进行排序操作。这一节将介绍可以对数据表中指定的多个列进行排序操作。这一节将介绍使用使用ORDER BY子句实现对数据记录排序的方法。子句实现对数据记录排序的方法。7.1.1 7.1.1 指定表中的一列进行排序指定表中的一列进行排序通过通过ORDER BY 子句可以对查询结果中指定的列进行子句可以对查询结果中指定的列进行升序或者是降序操作，这取决于升序或者是降序操作，这取决于ORDER BY 子句后的关键子句后的关键字，如果字，如果ORDER BY 子句后面的关键字是子句后面的关键字是ASC，则对查询，则对查询的结果执行升序操作；如果的结果执行升序操作；如果ORDER BY 子句后面的关键字子句后面的关键字是是DESC，则对查询的结果执行降序操作。其语法规则如下，则对查询的结果执行降序操作。其语法规则如下ORDER BY 列名列名1 ASC|DESC其中列名其中列名1表示需要对该列进行排序操作。关键字表示需要对该列进行排序操作。关键字ASC和和DESC是可选的。如果是可选的。如果ORDER BY 子句后面不写子句后面不写ASC或者或者是是DESC，则默认执行的是升序操作。，则默认执行的是升序操作。7.1.2 7.1.2 指定表中列的位置序号进行排序指定表中列的位置序号进行排序在使用在使用ORDER BY子句进行排序操作时，除了可以使子句进行排序操作时，除了可以使用列名对指定列进行排序，也可以使用该列在选择列表中的用列名对指定列进行排序，也可以使用该列在选择列表中的位置的序号对其进行排序。这里还以教师信息表中教师工资位置的序号对其进行排序。这里还以教师信息表中教师工资排序为例，看一下如何使用列的位置序号对教师工资进行排排序为例，看一下如何使用列的位置序号对教师工资进行排序。序。7.1.3 7.1.3 对对SELECTSELECT语句中的非选择列进行排序语句中的非选择列进行排序在上面的这些例子中，使用在上面的这些例子中，使用ORDER BY子句排序的列子句排序的列都出现的都出现的SELECT语句的查询列表中。但是语句的查询列表中。但是ORDER BY子句子句中也可以对没有在中也可以对没有在SELECT语句中出现的选择列进行排序操语句中出现的选择列进行排序操作。作。7.1.4 7.1.4 指定表中的多列进行排序指定表中的多列进行排序ORDER BY子句除了可以指定单列进行排序操作，也子句除了可以指定单列进行排序操作，也可以指定数据表中的多个列进行排序操作。如果要指定数据可以指定数据表中的多个列进行排序操作。如果要指定数据表中的多个列进行排序操作，则指定排序的列与列之间需要表中的多个列进行排序操作，则指定排序的列与列之间需要用逗号隔开。其语法规则如下：用逗号隔开。其语法规则如下：ORDER BY 列名列名1ASC|DESC,列名列名2 ASC|DESC其中列名其中列名1和列名和列名2表示需要对指定的数据列进行排序操表示需要对指定的数据列进行排序操作。列名作。列名1和列名和列名2之间用逗号进行分割。关键字之间用逗号进行分割。关键字ASC和和DESC是可选的。如果是可选的。如果ORDER BY 子句后面不写子句后面不写ASC或者或者DESC，则默认执行的是升序操作。然后，再根据，则默认执行的是升序操作。然后，再根据ORDER BY子句中指定的第二列的升序或者降序方式进行排序。子句中指定的第二列的升序或者降序方式进行排序。7.2 7.2 常用的聚合函数常用的聚合函数聚合函数也被称为分组函数或者统计函数，主要用于对聚合函数也被称为分组函数或者统计函数，主要用于对得到的一组数据进行统计计算，例如求和、求平均值等，常得到的一组数据进行统计计算，例如求和、求平均值等，常用的聚合函数包括用的聚合函数包括COUNT、MAX、MIN、SUM和和AVG五五个。个。COUNT、SUM和和AVG函数中可以使用函数中可以使用DISTINCT关键关键字去除指定列中的重复项。使用字去除指定列中的重复项。使用DISTINCT关键字后只是对关键字后只是对不同行的值进行统计。不同行的值进行统计。MAX和和MIN函数中的列或者表达式可以是数字型、字函数中的列或者表达式可以是数字型、字符型或者是日期类型的值。如果符型或者是日期类型的值。如果MAX和和MIN函数中的列或函数中的列或者表达式是字符型的，则按照首字母从者表达式是字符型的，则按照首字母从A到到Z的顺序排序，的顺序排序，如果首字母相同，则比较字符串中第二个字母的大小，以此如果首字母相同，则比较字符串中第二个字母的大小，以此类推。汉字则是按照其汉语拼音的全拼来排序。类推。汉字则是按照其汉语拼音的全拼来排序。7.27.2 常用的聚合函数常用的聚合函数SUM和和AVG函数中的表达式只能是数字类型的值。函数中的表达式只能是数字类型的值。除了除了COUNT(*)之外，其他的几个函数在计算时都忽略之外，其他的几个函数在计算时都忽略表达式中的空值（表达式中的空值（NULL行）。行）。COUNT函数是用来计算数据表中的总行数，函数是用来计算数据表中的总行数，SUM函数函数是用来计算数据表中某一列的属性值的总和。是用来计算数据表中某一列的属性值的总和。7.27.2 常用的聚合函数常用的聚合函数 聚聚合合函函数数只只能能出出现现在在SELECT语语句句、GROUP BY子子句句以以及及HAVING子子句句中中，WHERE子子句句中中不不能能出出现现聚合函数聚合函数。7.3 7.3 使用使用GROUP GROUP 子句对表中数据进行分组子句对表中数据进行分组GROUP 子句可以根据给定数据列的多个数据查询子句可以根据给定数据列的多个数据查询出来的结果进行分组，它既可以对单列数据进行分组，也可出来的结果进行分组，它既可以对单列数据进行分组，也可以对多列数据进行分组。在以对多列数据进行分组。在GROUP 子句后还可以使用子句后还可以使用HAVING子句对分组后的结果做进一步的筛选。这一节就来子句对分组后的结果做进一步的筛选。这一节就来介绍使用介绍使用GROUP 子句对表中数据进行分组的方法。子句对表中数据进行分组的方法。7.3.1 7.3.1 单列分组单列分组使用使用GROUP 子句对数据表中的某一列进行分组时，子句对数据表中的某一列进行分组时，会对指定分组的列中不同的值都计算出一个统计结果。其语会对指定分组的列中不同的值都计算出一个统计结果。其语法格式如下：法格式如下：GROUP BY列名列名1其中列名其中列名1表示需要对该列进行分组操作。表示需要对该列进行分组操作。7.3.2 7.3.2 多列分组多列分组使用使用GROUP 子句对数据表中的多个列进行分组时，子句对数据表中的多个列进行分组时，会对指定分组的多个列中不同的值都计算出一个统计结果。会对指定分组的多个列中不同的值都计算出一个统计结果。其语法格式如下：其语法格式如下：GROUP BY列名列名1,列名列名2其中列名其中列名1和列名和列名2表示需要对指定列进行分组操作。列表示需要对指定列进行分组操作。列名名1和列名和列名2之间用逗号进行分割。之间用逗号进行分割。7.3.3 7.3.3 使用使用HAVINGHAVING子句子限制分组后的查询结果子句子限制分组后的查询结果如果想要对分组后的结果限制查询条件，就需要使用如果想要对分组后的结果限制查询条件，就需要使用HAVING子句。由于子句。由于HAVING子句是用来限制分组后的查询子句是用来限制分组后的查询结果，所以该子句需要放到结果，所以该子句需要放到GROUP BY子句的后面使用。其子句的后面使用。其语法格式如下：语法格式如下：GROUP BY列名列名1 HAVING 条件表达式条件表达式其中列名其中列名1表示需要对该列进行分组操作。表示需要对该列进行分组操作。HAVING子子句后的条件表达式是用来筛选分组后的结果。在句后的条件表达式是用来筛选分组后的结果。在HAVING子子句中经常使用聚合函数对分组后的结果进行筛选。句中经常使用聚合函数对分组后的结果进行筛选。7.3.4 7.3.4 对分组结果进行排序对分组结果进行排序很多时候，对数据表中数据进行分组后，还希望对分组很多时候，对数据表中数据进行分组后，还希望对分组的结果进行排序操作。如果想对使用了的结果进行排序操作。如果想对使用了GROUP BY子句的分子句的分组结果进行排序的话，就需要使用组结果进行排序的话，就需要使用ORDER BY子句。子句。7.3.5 GROUP BY7.3.5 GROUP BY子句中处理子句中处理NULLNULL值值在使用在使用GROUP BY子句对对指定列进行分组时，有时子句对对指定列进行分组时，有时可能会遇到指定列中含有可能会遇到指定列中含有NULL值的情况。此时，值的情况。此时，GROUP BY子句会将该列中所有的子句会将该列中所有的NULL值归为一组。值归为一组。7.4 7.4 使用使用ROLLUPROLLUP关键字统计数据关键字统计数据前面一节中已经介绍了使用前面一节中已经介绍了使用GROUP BY子句可以对数子句可以对数据表中的一列或者多列进行分组，查询数据统计的结果。在据表中的一列或者多列进行分组，查询数据统计的结果。在实际应用中，有时不仅需要得到分组后的统计结果，还希望实际应用中，有时不仅需要得到分组后的统计结果，还希望对分组的统计结果做进一步的计算，例如通过对教师信息表对分组的统计结果做进一步的计算，例如通过对教师信息表（T_teacher）中的院校和教师职称进行分组，得到分组后教）中的院校和教师职称进行分组，得到分组后教师的最高工资，得到这个最高工资后，还希望对每一个院系师的最高工资，得到这个最高工资后，还希望对每一个院系中的教师的最高工资做一个阶段性的统计，希望得到各个院中的教师的最高工资做一个阶段性的统计，希望得到各个院校中不同职称的教师的最高工资的加和（相当于小计），还校中不同职称的教师的最高工资的加和（相当于小计），还希望得到所有院校不同职称教师最高工资的总和（相当于总希望得到所有院校不同职称教师最高工资的总和（相当于总计）。这个时候仅仅使用计）。这个时候仅仅使用GROUP BY子句是无法做到的，此子句是无法做到的，此时就需要使用时就需要使用ROLLUP关键字。关键字。7.4 7.4 使用使用ROLLUPROLLUP关键字统计数据关键字统计数据ROLLUP关键字的作用是对分组后的数据做阶段性的关键字的作用是对分组后的数据做阶段性的操作（相当于小计），并且可以对查询出来的所有记录的数操作（相当于小计），并且可以对查询出来的所有记录的数据做一个最终操作（相当于总计）。小计和总计的运算结果据做一个最终操作（相当于总计）。小计和总计的运算结果会自动添加到分组后的数据列中以及查询结果的最后一行中。会自动添加到分组后的数据列中以及查询结果的最后一行中。ROLLUP关键字使用时需要放到关键字使用时需要放到GROUP BY关键字的后面。关键字的后面。ROLLUP关键字在不同的数据库中的使用方式上稍有不同，关键字在不同的数据库中的使用方式上稍有不同，1在在MySQL5.0和和Microsoft SQL Server数据库中使用数据库中使用ROLLUP关键字统计数据。关键字统计数据。2在在Oracle数据库中使用数据库中使用ROLLUP关键字统计数。关键字统计数。7.4 7.4 使用使用ROLLUPROLLUP关键字统计数据关键字统计数据1在在MySQL5.0和和Microsoft SQL Server数据库中需数据库中需要使用要使用WITH ROLLUP。其语法格式如下：。其语法格式如下：GROUP BY 列名列名1 WITH ROLLUP其中列名其中列名1表示要对该列进行分组，表示要对该列进行分组，WITH ROLLUP关关键字表示要对分组的结果进行统计。当然也可以对多个列进键字表示要对分组的结果进行统计。当然也可以对多个列进行分组，并统计分组后的结果。其语法格式如下：行分组，并统计分组后的结果。其语法格式如下：GROUP BY 列名列名1,列名列名2 WITH ROLLUP7.4 7.4 使用使用ROLLUPROLLUP关键字统计数据关键字统计数据2在在Oracle数据库中，数据库中，ROLLUP关键字需要紧跟在关键字需要紧跟在GROUP BY关键字的后面，然后再写需要分组的字段。其关键字的后面，然后再写需要分组的字段。其语法格式如下：语法格式如下：GROUP BY ROLLUP(列名列名1,列名列名2)其中列名其中列名1与列名与列名2表示要对指定列进行分组，表示要对指定列进行分组，ROLLUP关键字表示要对分组的结果进行统计。关键字表示要对分组的结果进行统计。GROUP BY子句后面用括号括起来的的列名可以是一列，也可以是子句后面用括号括起来的的列名可以是一列，也可以是多列，如果是多个列，列名与列名之间需要用逗号分隔。多列，如果是多个列，列名与列名之间需要用逗号分隔。7.5 7.5 限制结果集行数限制结果集行数有些时候，开发人员或者用户并不希望将查询结果的数有些时候，开发人员或者用户并不希望将查询结果的数据列中的数据全部显示出来，而是只希望显示其中的几行，据列中的数据全部显示出来，而是只希望显示其中的几行，尤其是在需要分页的操作中。例如，一个数据表最后查询出尤其是在需要分页的操作中。例如，一个数据表最后查询出了了100条记录，而开发人员或者用户只关心其中前条记录，而开发人员或者用户只关心其中前10条记录条记录的值，这就需要对查询结果中的数据记录的行数进行限制。的值，这就需要对查询结果中的数据记录的行数进行限制。在不同的数据库中限制结果集行数的方法也不尽相同，这里在不同的数据库中限制结果集行数的方法也不尽相同，这里就以就以MySQL数据库、数据库、Oracle数据库和数据库和Microsoft SQL Server数据库为例，介绍如何在这数据库为例，介绍如何在这3种数据库中实现限制结果集行种数据库中实现限制结果集行数的操作。数的操作。7.5.1 7.5.1 使用使用MySQLMySQL数据库限制结果集行数数据库限制结果集行数在在MySQL数据库中限制结果集行数可以使用数据库中限制结果集行数可以使用LIMIT关关键字，它可以用来限制查询出来的数据结果的个数。通过使键字，它可以用来限制查询出来的数据结果的个数。通过使用用LIMIT关键字可以让开发人员或者用户得到其中想要的部关键字可以让开发人员或者用户得到其中想要的部分的结果。如果要使用分的结果。如果要使用LIMIT限制结果集行数，可以使用下限制结果集行数，可以使用下面的语法格式。面的语法格式。LIMIT n其中其中LIMIT是关键字，数字是关键字，数字n表示要限制结果集行数。表示要限制结果集行数。7.5.1 7.5.1 使用使用MySQLMySQL数据库限制结果集行数数据库限制结果集行数例如，查询教师信息表中的教师信息，只显示按照教师例如，查询教师信息表中的教师信息，只显示按照教师编号升序排序后的前编号升序排序后的前3条记录。条记录。SELECT teaID,teaName,dept,professionFROM T_teacherORDER BY teaIDLIMIT 37.5.2 7.5.2 使用使用OracleOracle数据库限制结果集行数数据库限制结果集行数Oracle数据库中不支持类似于数据库中不支持类似于 MySQL 中的中的 LIMIT关键关键字来限制结果集行数，但是在字来限制结果集行数，但是在Oracle数据库中可以使用数据库中可以使用ROWNUM关键字限制结果集的行数。其语法格式如下：关键字限制结果集的行数。其语法格式如下：WHERE ROWNUMn其中其中ROWNUM关键字表示对符合条件结果的序列号，关键字表示对符合条件结果的序列号，它的起始值总是从它的起始值总是从1开始的。数字开始的。数字n表示要限制的结果集的行表示要限制的结果集的行数。当然，这里的比较运算符除了可以使用（数。当然，这里的比较运算符除了可以使用（）小于以外，）小于以外，还可以使用（还可以使用（=）小于等于。）小于等于。例如，例如，SELECT teaID,teaName,dept,professionFROM T_teacherWHERE ROWNUM=3ORDER BY teaID7.5.3 7.5.3 使用使用Microsoft SQL ServerMicrosoft SQL Server数据库限制数据库限制结果集行数结果集行数在在MySQL数据库和数据库和Oracle数据库中使用数据库中使用LIMIT关键字关键字和和ROWNUM的方法限制结果集行数，在的方法限制结果集行数，在Microsoft SQL Server数据库中需要使用数据库中需要使用TOP关键字。其语法格式如下：关键字。其语法格式如下：SELECT TOP n PRECENT 列名列名1,列名列名2 FROM 表名表名其中，其中，TOP是表示限制结果集行数的关键字；数字是表示限制结果集行数的关键字；数字n表表示限制结果集行数；示限制结果集行数；PRECENT关键字表示返回查询的结果关键字表示返回查询的结果集中前集中前n%的行数，它是可选的；列名的行数，它是可选的；列名1与列名与列名2表示要查询表示要查询的数据表中列的名字，多个列名之间需要用逗号分隔。的数据表中列的名字，多个列名之间需要用逗号分隔。7.5.3 7.5.3 使用使用Microsoft SQL ServerMicrosoft SQL Server数据库限制数据库限制结果集行数结果集行数例如，例如，SELECT TOP 3 teaID,teaName,dept,professionFROM T_teacher除了可以指定行数限制查询结果中返回的结果集之外，除了可以指定行数限制查询结果中返回的结果集之外，也可以在也可以在TOP语句中使用语句中使用PRECENT关键字返回指定百分比关键字返回指定百分比数量的行数。例如下面这个例子。数量的行数。例如下面这个例子。例如，例如，返回教师信息表中前返回教师信息表中前50%的行数。的行数。SELECT TOP 50 PRECENT teaID,teaName,dept,professionFROM T_teacher