java web项目开发案例精粹-补技术sql查询语言chap

第六章数据的排序，和分本章主要内容：上一章已经学习了从表中查询数据。有的时候这样的选择结果排列的并不是非常合理。因为，一般来说，查询的结果是按照数据被添加到表时的顺序来排列。因此，需要ORDERBYSQLORDERBY子句以使查询结果按照用户的需要进行排列，以便于用户阅读。ORDERBY的基本格式如下：SELECTselect_listFROMtable_list[WHEREconditions][ORDERBYcolumnname|alias|position[ASC|在ORDERBY子句中也可以包含多个元素，元间也是使用逗号隔开ASCDESC表示按ORDERBYSELECT子句中一个列的名称——columnname；也可SELECT子句中为列或表达式创建的别名——aliasSELECT子句中代表位置的编码——position1SELECT子句中第一个列，2表示SELECT子句中第二个列。如果没有使用ORDERBY子句，返回的数据将按照表中数据的物理顺序排列，Employee表创建索引，在这种情况下查找所有工资为3000.00元和6000.00元的工作人SELECTname,sex,id,salaryFROMEmployeeWHEREsalary=3000.00salary=————男女男男男男男男男女刚男序，可以这样使用SELECT语句：SELECTname,sex,id,salaryFROMEmployeeWHEREsalary=3000.00salary=6000.00ORDERBYid————男男男女男男女男男男刚男EmployeeidCREATEINDEXSELECTname,sex,id,salaryFROMEmployeeWHEREsalary=3000.00salary=————男男男女男男女男男男刚男对比上面使用索引和不使用索引而对dODERBY子句，那么为什么还要提供索引呢？实际上，虽然上面的显示结果是相同的，但是索引与ORDERBY子句是两个完全不同的概念：创建了索引，数据库系统就将为索引建立了一个对应的物理对象，ODERBY子句的SQLODERBY子句指定的条件到物理磁盘上，每次都必须指定ORDERBY子句以重新将数据进在排序中您可以指定按照什么顺序来排序：升序或降序。SQL中，关键字ASC表DESC表示按照降序排列。而默认的排序方式为升序，也就是DESCASC，那么显示结果将按照从低到高的顺序排列。当然，为了使用户能更好的明白显示结果的排列顺序，您也可以指定ASC。3000.006000.00id列降序排列，可以这样使用SELECT语句：SELECTname,sex,id,salaryFROMEmployeeWHEREsalary=3000.00salary=6000.00ORDERBYidDESC————刚男男男男女男男女男男男SELECTname,sex,idFROMEmployeeWHEREnameBETWEEN’王AND’张ORDERBY—男女男女男男男女女刚男男ORDERBYASCDESC，因此结果按照在SQL中字符之间是怎么排序的呢？特殊字符又是怎么排序的呢？特殊字符与普通字符间是怎么排序的呢？“A”在“”之前么？大小写是否有区别呢？字符的排序取决于数据库系统使用的字符集和字符集中字符的顺序。在SQL中，并没有限定字符集与排序顺序，而是可以由数据库系统自行决定。多数数据库系统都允许用户安装或选择自己想使用的字符集，系统中也有默认的字符集设置和排序顺序，使用时请注意查看系统文档。年1月1号之后开始的项目，并按照日期的先后进行降序排序，可以这样使用SELECTSELECTpnumber,budget,pstrdateFROMProjectWHEREpstrdate>’2002-01-ORDERBYpstrdate

2002-12-2002-04-2002-01-SELECT子句中的表达式来对查询结果进行排序。这时就不能直接在ORDERBY子句中直接使用表达式。如，按照项目利润排序：SELECTpnumber,budget,gross,gross-budgetFROMProjectORDERBYgross-在SL2SL3DS将通过对表达式的比较而不是列名的比较进行排列。不同的数据库系统实现也有所不同，有的SELECTpnumber,budgetFROMProjectORDERBY但是，SQL3的这两个特性使得编写的代码不够系统。因此，建议仍使用SQL2的ORDERBYSELECT列表中的ORDERBYORDERBYcolumnname|alias|position[ASC|ORDERBYcolumnname排序，也可以使用为表达式创建的别名排序，还可以按照其在SELECT子句中的位置position排序。如，查询Projiect表中每个项目的利润，为利润创建别名“profit”，并且按照升序的顺序进行排序，可以这样使用ORDERBY子句：SELECTpnumber,budget,gross,gross–budgetASprofitFROMProjectWHEREprofit>=30000.00profit<=70000.00ORDERBYprofit如果没有为表达式创建别名也没有关系，SQLSELECT子句列表中的位置来指定排序的列。例如，查询Projiect表中每个项目的利润，并且按照降序的顺序进行排序，在没有为利润创建别名的情况下，可以这样使用ORDERBY子句：SELECTpnumber,budget,gross,gross–budgetFROMProjectWHEREprofit>=30000.00profit<=70000.00ORDERBY4DESC gross–SELECT列表中的列，如删除一列、改变列的先后顺序等等，这时需要注意ORDERBY子句中使用的位置代表的列是否发生了变化。例如，在SELECT列表中增加pmgrid列，如果仍这样使用ORDERBY子句：SELECTpnumber,pmgrid,budget,gross,gross–budgetFROMProjectWHEREprofit>=30000.00profit<=70000.00ORDERBY4DESCgross–从查询结果可以看出结果按照gross列降序排列，而不是按照利润降序排列。可见4不再代表列“gross–budgetgross列了。如果还要按照利润对结果SELECTpname.pnumber,budget,gross,gross–FROMWHEREprofit>=30000.00profit<=70000.00ORDERBY5DESC多级排清晰。例如，公司想知道每个部门工作人员的薪水情况，将工作人员的记录按照salary列进行排序，使用SELECT语句：SELECTname,sex,id,salaryFROMEmployeeORDERBY————女男男男男男女刚男男男女女女女女男男男男男男男男男salaryidsalary列的值相同的列进行再次排序，使用SELECT语句：SELECTname,sex,id,salaryFROMEmployeeORDERBY————男男女男男女男刚男男女女女女女男男男男男男男男男男DESCASC关键字，以指定排序的列是按照DESC与ASC关键字的使用是互不干扰的。例如，按照salary列降序排序，再按照id列升序排序：SELECTname,sex,id,salaryFROMEmployeeWHEREnameBETWEEN’王AND’张ORDERBYsalaryDESC,id

————

———男 男 男 男 男 女 女 女 女 男 SELECTname,sex,id,salaryFROMEmployeeORDERBY4DESC,3————男男男男男男男男男男女女女女女男男男女男男女男刚男由于SQL支持多级排序，因此，在需要的时候您就可以使查询结果按照所需的方空值的SQLServerProjiect表中每个项目ORDERBY子句：SELECTpnumber,budget,gross,gross–budgetASprofitFROMProjectORDERBYOracleProjiect表中每个项目的利润，为利润创建别名“profitORDERBYSELECTpnumber,budget,gross,gross–budgetASprofitFROMProjectORDERBY6.2数同记录的情况，或者希望知道平均值、最大值等要求，没关系不必担心，SQL还提供了去除相SELECT子句中有两个可选的关ALLSELECT[ALL|DISTINCT]ALL为默认选项，表示列出所有记录，而不管是否出现重复。如果要去掉重复的记DISTINCTDISTINCT对一列或多列的组合进行重复的3000.00dno列进行排序，当在SELECT子句中不使用DISTINCT关键字时：SELECTdnoFROMEmployeeWHEREsalary=3000.00ORDERBYdno11223355DISTINCTSELECTDISTINCTFROMWHEREsalary=3000.00ORDERBYdno1235DISTINCT关键字时需要注意：DISTINCTselect_list之前使SELECTdno,DISTINCTFROMWHEREsalary=3000.00ORDERBYdnoSELECT子句中包含多个列时，DISTINCT是对列的组合进行去除的。例如下面的SELECT语句：SELECTDISTINCTname,dnoFROMEmployeeWHEREsalary=ORDERBY 12233 显示结果表明，dnonamedno列的组合并没有出现重复。所以没有删除dno列中的重复数据。可见，DISTINCT是对列的组合进行去除DISTINCT关键字，所有的例如，查询工资为3000.00元的工作人员的名字、所属部门号、参与的项目号，可以这样使用SELECT语句：SELECTDISTINCTname,dno,pnoFROMEmployeeWHEREsalary=3000.00ORDERBYdno1122335刚5从本例查询结果再次证明，DISTINCTSELECTSELECTDISTINCTdno,FROMWHEREsalary=3000.00ORDERBYdno 的行。可见DISTINCT中空值是相同的。SELECTSELECTDISTINCTFROMWHEREsalary=3000.00ORDERBYpno6.2.2函出Project表中所有记录的budget：SELECTpnumber,budgetFROMProject您可以使用SQL提供的函数SUM()。SELECTSUM(budget)FROMProject函数是SQL中很重要的一部分。函数不是对某个记录进行操作，而是对function([DISTINCT]expression通常是一个列名或者别名，但也可以是一个常量或者一个函数(SQL支持函数的嵌套)。表6-1列出了SQL中支持的函数。表6- SQL支持的函含COUNT(SUM(AVG(MAX(MIN(COUNT、SUM()、AVG()DISTINCT关键字，以在计算中MAX()、MIN()COUNT(*)由于不会改变其结果，因此没有必要使用DISTINCT。SUM()、AVG()COUNT()、MAX()、MIN()与COUNT(*)可以对所有数据类型使用。VARIANCE（）函数，ASALIST（）函数。使用时请注意查阅系统SELECTSUM(budget)ASTotalFROMProjectWHERE有时您可能不需要对所有的记录都进行函数操作，而仅需要对符合条件的记录进行与WHEE行WHE子句到满件录后查的录数计。SELECTSUM(budget)ASTotalFROMProjectWHEREbudget<同时需要注意的是：在WHERE子句中不能使用函数。例如，您可能想查找所SELECTFROMWHEREbudget>SELECTFROMProjectWHEREbudget>(SELECTAVG(budget)FROMProject)8以同时使用SUM()与COUNT()函数：SELECTSUM(budget)ASTotal,COUNT(budget)ASNumberFROMProjectWHEREbudget< COUNT()COUNT()函数可以用来计算查询结果中某个特定列出现的次数。SQL提供了COUNT()和COUNT(*)函数。这两个函数很相似，但使用是完全不同的。通过下面的SELECTFROMProject12gross列的值为空值，上面的计算结果仅为10，可以看出COUNT(column)函数并不把column在内。再看看COUNT(*)函数的使用：SELECTFROM可见，COUNT(*)函数计算所有的记录，不用考虑空值。当然，如果COUNT()函数在没有空值的列上进行计算得到的值与COUNT(*)函数相同。例如，在dnumber列上使用COUNT()函数：SELECTCOUNT(dnumber)FROMProject可以看见计算的结果与使 COUNT(*)函数计算的结果相同COUNT()不仅可以对数字类型的列使用，也可以对其它数据类型的列使用，如字符串类型。例如，计算公司有多少工作人员，可以对Employee表中的name列使用COUNT()函数，其中可以再比较一下COUNT()和COUNT(*)两个函数的结果：SELECTCOUNT(name),FROM DISTINCTCOUNT中唯一值的数目。例如，下面的查询计算了在Projectgross列唯一值的数目：SELECTCOUNT(DISTINCTFROMCOUNT(DISTINCT8因此使用DISTINCT关键字后COUNT()函数返回的值为8。注意：DISTINCT注意：DISTINCTCOUNT(*)COUNT(*)计算的是表中含DISTINCTCOUNT(*SELECTCOUNT(DISTINCTFROMCOUNT使用一个临时表或视图不同DISTINCTCOUNTCREATEVIEWtemp_tableSELECTDISTINCTsex,salaryFROMEmployeeSELECTFROMtemp_talbeORDERBY 这样临时视图中了唯一的sex列与salary列的组合的值。接着，对临时的视SELECTCOUNT(*)ASFROM7SUMAVG()SUM(column)columnAVG(column)函数用来计算选择的行中column列所有值的平均值。因此，SUMAVG(SUMAVGSELECTSUM(gross),AVG(gross)FROMProject SELECTCOUNT(gross),FROM 268000.00.0010SUMAVG(注意：AVG()函数中计算值的数目时，并不会将空值计算在内。如上例中，虽然Project注意：AVG()函数中计算值的数目时，并不会将空值计算在内。如上例中，虽然Project12grossDISTINCT关键字也可以在SUM()与AVG()函数中使用。在SUM()函数中使用DISTINCT，SUM()函数将只计算列中唯一值的总和。在AVG(DISTINCT，使用SUM()与AVG()函数：SELECTSUM(DISTINCTgross),AVG(DISTINCTFROMSUM(DISTINCT AVG(DISTINCT DISTINCTDISTINCT是不同的。当DISTINCT关键字包含在函数中时，它是在函数计算时起作用，并不与规则——DISTINCTselect_list之前使用——相违背。在SELECTselect_list前SELECTCOUNT(DISTINCTgross),FROMCOUNT(DISTINCT 可见，唯一值有8个。与不使用DISTINCT关键字时相比，使用DISTINCT关键字的SUM()函数没有将重复值进行多次计算，因此SUM()函数得出的值为.00，而包含DISTINCT键字的AVG()函数得到的是.00/8=.00的值，即得到MAXMINMAX(column)函数用来计算选择的行中column列包含的所有值中最大的值。而MIN(column)函数用来计算选择的行中column列包含的所有值中最小的值。因此，MAX()与MIN()函数中没有必要使用DISTINCT关键字。SELECTMAX(gross),MIN(gross)FROMProject 注意注意：当列中存在非空值时，MAX(MIN(非空值存在，MAX(MIN(MAX(MIN(日期类型数据使用，当然对于字符串与时间日期类型数据空值也是不大于或小于任何值。EmloyeeameMAX(MIN()函数：SELECTMAX(name),MIN(name)FROMEmployee SQL3ROLLUPCUBE关键字。ROLLUP意思是从所有每个所成的组的列中得出每一个组的组；CUBE意思是通过组合成组列的所有可能的组合，从所有组中得出一个组。ROLLUPCUBE关键字的使用比较复杂，也比较晦涩，由于在使用中6.2.3避免使用DISTINCT时的要查找记录中唯一的组合，您可以使用DISTINCT关键字。但是，初学者对于对其注意事项做了介绍，但为了您更好的掌握DISTINCT，这儿对注意的地方进行一下总在列和表达式中使用DISTINCT时，只能使用一次DISTINCTDISTINCT是对SELECTDISTINCT时有了很大的限制，分别对每个列或表达式指定是否包含DISTINCT。下面的例子可以更好的帮助您理解这样的限制。查找收入低于300000.00元的项目SELECTDISTINCTFROMWHEREgross<300000.00SELECTDISTINCTgross–budgetASprofitFROMProjectWHEREgross<SELECTDISTINCTgross,gross–budgetASprofitFROMProjectWHEREgross<ORDERBY 其中，gross180000.00280000.00，profit也出现了重复，四个40000.00。DISTINCTDISTINCT是互不干扰的，它是在函数计算时起作用，并不与规则——DISTINCTselect_list之前使用——相违背。例如，可以在SUM()函数与AVG()函数中分别使用：SELECTSUM(DISTINCTgross),AVG(DISTINCTFROMSUM(DISTINCT AVG(DISTINCT SELECTSUM(DISTINCTgross),FROMSUM(DISTINCT 在函数中使用DISTINCT与在SELECT列表中使用DISTINCT区别还是比较大的。错误是使用带有DISTINCT关键字的COUNT(*)函数，如：SELECTCOUNT(DISTINCTFROM其中，对特定列计算不同值的数目比较简单，您可以在COUNT()函数中使用DISTINCT关键字从而直接得出正确结果。使用一个临时表或视图不同DISTINCT不同点：对其它运算符而言，空值之间是互不相同的，而且要想查找到空值需ISNULLDISTINCT来说，将空值看作是相同的，也不需要使用ISNULL。SELECTgrossFROMWHEREgross<200000.00grossISSELECTDISTINCTFROMWHEREgross<DISTINCTISNULL关键字就可以查找出空值，而且将所有的空值看成是相同的。所以，使用DISTINCT关键字去掉了一个重复的180000.00，同时也去掉一个重复的NULL。学习了函数后，有的时候可能希望对一组数据使用函数。例如，统计每个部门工资总额，就可以使用函数SUM()函数为每个部门进行计算，如计算部门号为1的部门工资总额：SELECTSUM(salary)FROMEmployeeWHEREdno=1然而，您会发觉无法在一个查询中完成所有部门的工资总额的计算，而必须为每一个部门使用一个查询，而且您必须对部门的个数有清楚的了解。这样使用是非常不方便，SQLGROUPBYGROUPBYGROUPBY子句根据列的内容对查询结果进行分类。GROUPBY子句的基本格式SELECT[ALL|DISTINCT]select_listFROMtable_list/view_list[WHEREconditions][GROUPBYgroup_list][HAVINGconditions][ORDERBYorder_list]GROUPBY时，order_listselect_list中，即不能选择select_listdnoSELECT列表中，下面的SQL语句将无法运行：SELECTSUM(salary)FROMEmployeeGROUPBYdno注意注意GROUPBYGROUPBYGROUPBY子句来方便的完成每个部SELECTdno,SUM(salary)FROMEmployeeGROUPBY — 3456GROUPBYGROUPBY子句可以用来对数据进行分组，将数据分成组并为每一组返回一行。在不使用函数时，使用GROUPBY与DISTINCT类似。SELECTDISTINCTFROMEmployeeORDERBYdno123456GROUPBYSELECTdnoFROMEmployeeGROUPBYdnoGROUPBY6个部门，—123456可见，查询结果是相同的。GROUPBY与DISTINCT另一个相似之处是：与DISTINCT一样，GROUPBYGROUPBY与函GROUPBY子句与函数有密切的联系，可以说没有函数GROUPBY子句也就没有多大的用处。GROUPBY子句可以将查询到的数据分成组，而函数则用来GROUPBYSELECTsalary,COUNT(*)FROMEmployeeGROUPBY SELECTdno,AVG(salary)FROMEmployeeGROUPBY —123456WHERE排序中可以使用WHERE子句以对特定的记录进行排序，在分组中也可以使用WHEREWHERE子句将符合条件的记录选出，再对选出的数据进行分组。当然，WHERE子句中使用的列并不是一定要在SELECT列表中出现。3000.00SELECTdnoFROMEmployeeWHEREsalary=3000.00GROUPBYdno1235多次分ODERYOUPY以指定多个列对一组数据再次进行分组。多个列之间也是使用逗号分隔。执行顺序也与SELECTdno,sex,COUNT(*)FROMEmployeeGROUPBY—男男女男女男34女35男35女16男2再按照dno列进行分组：SELECTdno,sex,COUNT(*)FROMEmployeeGROUPBY—2女13女24女35女11男52男23男24男35男36男2SQL的读者可能并不能完全掌握。例如，要句进行查询：SELECTdno,COUNT(id),sex,COUNT(id)FROMEmployeeGROUPBY—男男女男232女243男343女353男351女162男2COUNT(dCOUNT(d)并不是对每个部门工作人员人数进行了计算。函数是对进行多次分组之后形成的小组进行计算的。因此，要实现上面的计算，必须使用两个查询，分别计算每个部门工作人员的人数及部门中男女工作人员各自的人数。SELECTdno,FROMEmployeeGROUPBYdno — 2SELECTdno,sex,COUNT(id)FROMEmployeeGROUPBYdno,SELECTdno,sex,COUNT(*)FROMEmployeeGROUPBYdno,SELECTsex,FROMEmployeeGROUPBYsex — Oracle中提供了工具来ORDERBYORDERBYGROUPBY子句一起使用以对结果进行排序，更清晰的显示结果。SQL语句书写时，ORDERBYGROUPBY子句之后书写。这两个GROUPBYORDERBY例如，列出工资为3000.00的工作人员所属的所有部门号，并按照降序排列，可以SELECTdnoFROMEmployeeWHEREsalary=3000.00GROUPBYdnoORDERBYdno5321注意：ORDERBY子句中使用的列名必须是GROUPBY子句中包含的列名 在ODERBYGROUPBY列表之外包含的列名将会产生错误。例如，计算每个部门的平均工资水平，这时应该对部门进行分组，如果将结果按照平均工资进SQL语句：SELECTdno,AVG(salary)ASavgFROMEmployeeGROUPBYdnoORDERBY — 相反地，上面的语句是不能运行的，根本不可能产生上面的查询结果。SQL引擎将当然必须是包含在GROUPBY子句中的列。例如，计算每个部门中和女性工作人员的数目，并将结果按照dno列降序排列，可以这样使用GROUPBY子句：SELECTdno,sex,COUNT(*)FROMEmployeeGROUPBYdno,sexORDERBYdnoDESC,sex—6男25女15男34女34男33女23男22女12男21男5ORDERBYGROUPBY子句中列的顺序不同。例如，上面的例子可以先按照sex列排序，再按照id列进行排序：SELECTdno,sex,COUNT(*)FROMEmployeeGROUPBYORDERBYsex,dno—5女女女女男男4男33男22男21男5当然，GROUPBY子句中有多个列时，ORDERBY子句也可以仅使用其中的部分列进行排序。例如，可以仅按照dno列进行排序：SELECTdno,sex,COUNT(*)FROMEmployeeGROUPBYdno,sexORDERBYdnoSELECTdno,sex,COUNT(*)FROMEmployeeGROUPBYdno,sexORDERBYsexHAVING子SELECTWHEREGROUPBY子句时，也可以HAVING子句及函数时，执行顺序如下：GROUPBY对GROUPBY子句形成的组运行函数计算每一组的值HAVING子句去掉不符合条件的组。例如，删除工作人员数目小于4的部门号：SELECTdno,FROMEmployeeGROUPBYdnoHAVINGCOUNT(*)> — 64HAVING使用HAVING子句时，需要注意的是：HAVING子句中的每一个元素也必须出现SELECTHAVINGSELECT列表SELECTdno,FROMEmployeeGROUPBYdnoHAVINGSUM(salary)> — 虽然HAVING子句通常与函数一起使用，但是HAVING子句中也可以不使用SELECT10000.006HAVING子SELECTdno,FROMEmployeeGROUPBYdnoHAVINGdno<>6 — HAVINGAND、ORNOT运算符来包含多个限制条件。例如，SELECTdno,AVG(salary),SUM(salary)FROMEmployeeGROUPBYHAVINGSUM(salary)>10000.00AVG(salary)<—235SELECTHAVINGHAVING子句与WHERE子句都可以用来设定限制条件以使查询结果满足一定的10000.00元的组，可以使用HAVING子句设置限制：SELECTdno,SUM(salary)FROMEmployeeGROUPBYHAVINGSUM(salary)> — SELECTdno,SUM(salary)FROMEmployeeWHEREdno<>6GROUPBYdno — 但是，WHERE子句与HAVING子句并不是相同的：HAVING子句限制的是组，而不是行；WHERE子句用于限制查找的行，而不是对形成的组进行限制。即WHERE子句产生用于成组的行，HAVINGWHERE子句中不允许使用函数，而HAVING子句中却可以使用。WHERE子句也有自己的优点，它比HAVING子句更有效。因为WHERE子句删除行，这样用于成组的行更少，函数也在更少的行上进行计算，而HAVING子句不能限制成组的行，只能对组的值进行4．综合使用各SELECTdno,AVG(salary),SUM(salary)FROMEmployeeWHEREdno>2GROUPBYdnoHAVINGSUM(salary)>10000.00AVG(salary)<4000.00ORDERBYdnoDESCdno2dno的值进行分组，并对分组结果进行限制，最后将结果按照dno列进行降序排列： —53空值的SELECTpno,FROMEmployeeGROUPBYpnoORDERBYpno WHERE子句以去除分组结果中的空值。例如，计算参与各项目pnopnoNULL的SELECTpno,FROMWHEREpnoISNOTNULLGROUPBYpnoORDERBY 当分组中包含空值时，需要注意COUNT(*)与COUNT()的区别。例如，对上例使用COUNT()进行计算：SELECTpno,COUNT(pno)FROMEmployeeGROUPBYpnoORDERBY 注意，使用COUNT()进行计算