教师资格证备考之段函数的使用

5．1．3　SELECT－SQL应用举例

SELECT查询命令的使用非常灵活，用它可以构造各种各样的查询，SELECT列表项、FROM子句、WHERE子句构成最常用的、最基本的SQL查询语句，下面举例说明其应用方法。

一、单表查询

1．查询全部信息

例5－1：查询学生基本情况表的全部信息。

SELECT　＊　FROM　xs

符号“＊”表示选定表的全部字段。

2．不包括重复信息

例5－2：查询学生中所有的专业代号，对于重名的专业代号只显示1次。

SELECT　DISTINCT　zydh　FROM　xs

使用DISTINCT可消除重复的值，这里表示去掉查询结果中专业代号重复的记录。

3．输出字段表达式

例5－3：查询学生的学号、姓名和年龄。

SELECT xsxh as学号，xsxm as姓名，YEAR（DATE（））－YEAR（csrq）as年龄；

FROM　xs

SELECT列表项中列表项格式为“表达式as列名”形式，表达式可以是常量、变量、函数及它们的组合，列名用来指定查询结果中列表项的标题。若未用AS指定列名，对于任意表达式列表项、存在同名的字段列表项，系统自动为其定义列名。

4．记录的筛选处理

例5－4：查询工龄在10～15年之间（包括10年和15年）的教师的工号、姓名和工龄。

SELECT　jsgh，jsxm，gl　FROM　js；

WHERE　gl＞＝10 and　gl＜＝15

WHERE子句用来指定筛选记录的条件，有多个条件时，可用AND或OR连接。

SELECT－SQL的查询方式很丰富，它还可以使用量词和谓词来实现查询，主要量词有ANY、ALL、SOME，主要谓词有BETWEEN、IN、LIKE和EXIXT，本例也可以用下列SELECT语句实现：

SELECT　jsgh，jsxm，gl　FROM　js；

WHERE　gl　BETWEEN　10 AND　15；

ORDER　BY　gl　DESC

在查询中，若要求某列的数值在某个区间内，可用BETWEEN…AND…表示；若要求某列的数值不在某个区间内，可用NOT　BETWEEN…AND…。

例5－5：查询学生名字中包含“林”字的学生学号、姓名。

SELECT xsxh，xsxm FROM xs；

WHERE xsxm LIKE"%林%"

在查找中，有时需要对字符串比较。LIKE提供两种字符串匹配方式，一种是使用下划线符“＿”，匹配任意一个西文字符或中文字符，另一种是用百分号"%"，匹配0个或多个字符的字符串。同样可以使用NOT　LIKE表示与LIKE相反的含义。

5．查询结果的排序

例5－6：查询工龄在10～15年之间（包括10年和15年）的教师的工号、姓名和工龄，并按工龄由高到低列出。

SELECT jsgh，jsxm，gl FROM js；

WHERE gl IN（10，11，12，13，14，15）；

ORDER BY gl DESC

ORDER BY子句用来指明查询结果的顺序，可以用列数、字段或列名表示，选项DESC表示由大到小输出。缺省情况下，以升序排序输出查询结果。

在查询中，经常会遇到要求表的列值是某几个值中的一个，这时可用IN表示。同样可以使用NOT　IN来表示与IN完全相反的含义。

二、多表查询

在日常事务处理中，往往要涉及多个表之间的关联查询。SQL语言提供了连接多个表的操作，可以在两个表之间按指定列的值将一个表中的行与另一表中的行连接起来，从而大大增强了其查询能力。

例5－7：查询学生的学号、姓名、课程、成绩，并按学号排升序。

使用WHERE子句的筛选条件实现如下：

SELECT Xs．xsxh，Xs．xsxm，Kc．kcmc，Cj．cj；

　　FROM xs，cj，kc；

　　WHERE Xs．xsxh＝cj．xsxh AND Cj．kcdh＝kc．kcdh；

　　ORDER BY Xs．xsxh

使用FROM子句的联接条件实现如下：

　SELECT Xs．xsxh，Xs．xsxm，Kc．kcmc，Cj．cj；

　　　FROM xs INNER JOIN cj INNER JOIN kc；

　　　　　ON Cj．kcdh＝Kc．kcdh ON Xs．xsxh＝Cj．xsxh；

　ORDER　BY　Xs．xsxh

对于上述查询，学号、姓名在学生表中可以查到，课程在课程表中可以查到，而成绩需到成绩表中才能查到，所以需按照学号相等联接学生表和成绩表，按照课程号相等联接成绩表和课程表。由于某些字段在多个表中都出现，所以为了防止二义性，在其字段名前必须加上表的别名作为前缀，以示区别。如果字段名是惟一的，可以不必加前缀。相关联的表中联接字段通常为同名字段，也可以为不同名字段，甚至可以为相应表达式，联接运算符通常为相等运算符，也可以为其他关系运算符。

三、统计处理

SELECT语句不仅可以通过WHERE子句筛选满足条件的数据，还可以通过字段函数对满足条件的数据进行统计处理。下列5种字段函数可以应用于统计处理：

MIN　　　求（字符、日期、数值）列的最小值

MAX　　　求（字符、日期、数值）列的最大值

COUNT　　对一列中的值计算个数

SUM　　　计算数值列的总和

AVG　　　计算数值列的平均值

例5－8：查询教师人数、最高工龄、最低工龄和平均工龄。

　SELECT COUNT（＊）AS人数，MAX（gl）AS最高工龄，；

　　　MIN（gl）AS最低工龄，AVG（gl）AS平均工龄；

　FROM js

COUNT（＊）表示统计记录条数，在教师表中记录条数可以表示教师人数。

例5－9：查询每个部门教师的人数、最高工龄、最低工龄和平均工龄，并按部门排升序，部门由工号的第1位数据表示。

SELECT substr（gh，1，1）as部门，count（＊）as人数，max（gl）as最高工龄，；

　　　　min（gl）as最低工龄，avg（gl）as平均工龄；

　　FROM js；

　　GROUP BY　1；

　　ORDER BY　1

字段函数是从基表的一组值中计算出一个汇总结果，基表中的一组记录在查询结果中生成一条统计记录，GROUP　BY子句用来定义组，列表项的值相同的分在一组。通常情况下，统计计算列表项不能作为分组依据，一般将非统计计算列表项作为分组依据（若未作分组依据，则此列表项在统计中无意义，只起一些修饰作用），若在分组依据的列表项中有多个相关字段，则选择关键字段作为分组依据，以提高系统处理效率。本例中共有5列数据，2～5列为统计计算列表项，因此第1列为分组依据。

例5－10：统计各班各门课程的考试情况，输出字段包括：班级编号、班级名称、课程名称、考试人数、优秀率、不及格率，查询结果按班级编号升序排序。（注：优秀率＝成绩90分以上（包括90分）的人数／总人数，不及格率＝不及格（成绩小于60分）人数／总人数）

SELECT bj．bjdh，bjmc，kcmc，COUNT（＊）AS总人数，；

　　　　SUM（IIF（cj＞＝90，1，0））／COUNT（＊）AS优秀率，；

　　　　SUM（IIF（cj＜60，1，0））／COUNT（＊）AS不及格率；

FROM bj INNER JOIN xs INNER JOIN cj INNER JOIN　kc；

　　　　　ON　cj．kcdh＝Kc．kcdh ON xs．xsxh＝cj．xsxh ON bj．bjdh＝xs．bjdh；

　　GROUP BY bj．bjdh，kcmc；

　　ORDER BY bj．bjdh

在统计计算中一般求和处理使用SUM（计算字段），计数处理使用COUNT（字段）或COUNT（＊），而对于按条件求和处理，则需要构造表达式SUM（IIF（条件表达式，计算字段，0）），按条件计数处理，需要构造表达式SUM（IIF（条件表达式，1，0））来实现。本例中共有6列数据，4～6列为统计计算列表项，1～3列为非统计计算列表项，所以可设bjdh、bjmc、kcmc为分组依据，但bjdh可决定bjmc，因此分组依据设为bj．bjdh和kcmc，能够提高处理效率。

例5－11：在教师表中查询出各部门中有3人以上具有相同职称的部门和职称名称。

SELECT SUBSTR（jsgh，1，1）as部门，zc as职称名称；

　　FROM js；

　　GROUP BY 1，zc；

　　HAVING COUNT（＊）＞＝3

要特别注意HAVING子句和WHERE子句的区别。WHERE子句用来指定表中记录所应满足的条件，而HAVING子句用来指定表中每一分组所应满足的条件，只有满足HAVING子句的条件的那些组才能在查询结果中被显示。即HAVING用于去掉不符合条件的若干组，如同WHERE用于去掉不符合条件的若干行一样。

上例中按部门和职称进行分组统计，然后在每个分组中检测其记录个数是否大于等于3，如果满足条件，则该组的部门和职称才被输出。

实质上，WHERE子句是对基表记录的筛选处理，HAVING子句是对中间的查询结果进一步作筛选处理。因此，HAVING子句可以使用列表项中的列名，或使用字段函数来表示条件表达式，而WHERE子句不可以使用列名。此外，在无GROUP　BY子句的情况下，HAVING子句也可独立使用，此时可代替WHERE子句的功能，对记录进行筛选处理。

四、嵌套查询

如果一个SELECT命令无法完成查询任务，而需要一个子SELECT的结果作为条件语句的条件，即在一个SELECT命令的WHERE子句中出现另一个SELECT命令，则称为嵌套查询或称为子查询，必须将子查询部分用圆括号括起来。

例5－12：查询成绩表中孤立的记录（即成绩表中的学号在学生表中不存在的记录）。

通常情况下，可用下列SELECT－SQL语句实现：

SELECT＊FROM　cj；

　　WHERE cj．xsxh NOT IN（SELECT xs．xsxh　FROM xs）

也可以利用相关子句查询实现：

SELECT＊FROM　cj；

　　WHERE NOT EXISTS （SELECT xs．xsxh　FROM xs　WHERE　xs．xsxh＝cj．xsxh）

还可以利用相应联接和空值查询加以实现：

SELECT cj．＊FROM cj LEFT JOIN xs ON cj．xsxh＝xs．xsxh；

　　HAVING xs．xsxh IS NULL

在SQL语言标准中，嵌套查询可以在FROM子句、WHERE子句和HAVING子句实现，允许多层嵌套。但在VFP中，子查询只能限定在WHERE子句中，子SELECT语句的结果必须有确定的内容，在WHERE子句中，最多出现两个并列的子SELECT语句，并且子查询只能嵌套一次。

五、联合查询

在SQL语言中可以将两个或多个查询结果进行并操作（UNION）。需要注意的是两个查询结果进行并操作时，它们必须具有相同的列数，并且对应的列有着相同的数据类型和长度（对应的列名可以不同）。UNION运算自动去掉重复记录。

例5－13：查询具有“教授”职称和“副教授”职称的教师的编号、姓名和职称。

SELECT Js．jsgh，Js．jsxm，Js．zc；

　　FROM sjk！js；

　　WHERE Js．zc＝"教授"；

UNION；

SELECT Js．jsgh，Js．jsxm，Js．zc；

　　FROM sjk！js；

　　WHERE Js．zc＝"副教授"

合并后，自动按第1列排序，也可以最后使用ORDER　BY子句指定排序列。

六、别名与自联接查询

＊例5－14：在成绩表中查询成绩超出每门课程成绩平均分10分以上的学生，输出学号、课程代号、成绩，根据课程代号排升序，并将查询结果输出到cjtemp自由表中。

SELECT a．xsxh，a．kcdh，a．cj　FROM cj a；

　　WHERE a．cj－10＞＝（SELECT　avg（b．cj）　FROM cj b　WHERE b．kcdh＝a．kcdh）；

　　ORDER BY kcdh；

　　INTO TABLE cjtemp

＊例5－15：在学生表中查询所有同年同月同日生的男女学生对。

SELECT a．xsxm　as男方姓名，b．xsxm　as女方姓名，b．csrq as出生日期；

　　FROM xs a，xs　b；

　　WHERE a．xsxm＜＞b．xsxm and a．csrq＝b．csrq；

　　　　　and a．xb＝"男" and　b．xb＝"女"

或者：SELECT　a．xsxm　as男方姓名，b．xsxm　as女方姓名，b．csrq　as出生日期；

　　FROM　xs　a　INNER　JOIN　xs　b　ON　a．csrq＝b．csrq；

　　WHERE　a．xsxm＜＞b．xsxm　and　a．xb＝"男" and　b．xb＝"女"

SQL不仅可以对多个表实行联接操作，也可以将同一关系与自身进行联接，这种联接称为自联接，在自联接操作中，别名是必不可少的。在本例中，学生表一个实例使用别名为a，另一个实例使用别名为b。