第4章 查询与统计.ppt

《第4章 查询与统计.ppt》由会员分享，可在线阅读，更多相关《第4章 查询与统计.ppt（89页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第4章章查询与视图查询与视图查询查询是指从指定的数据库、表或视图中搜索搜索那些满足一定条件的记录，并可以根据需要对这些记录进行排序和分组，还可以将查询结果输出到浏览器、表、报表、标签及图形中。让我们想到搜索引擎百度、Google。查询可以利用表的查询与统计查询可以利用表的查询与统计命令实现命令实现，也可，也可以通过以通过查询设计器查询设计器或者或者SQL语句语句实现。实现。v4.1查询与统计查询与统计v4.2结构化查询语言结构化查询语言SQLv4.3查询查询v4.4视图视图v思考题思考题4.1查询与统计查询与统计本节将介绍关于表的记录查询和统本节将介绍关于表的记录查询和统计的命令，包括记录的

2、定位、数据筛计的命令，包括记录的定位、数据筛选、记录查询和统计命令。选、记录查询和统计命令。v4.1.1表的记录定位表的记录定位v4.1.2表的数据筛选表的数据筛选v4.1.3表的查询命令表的查询命令v4.1.4表的统计命令表的统计命令4.1.1表的记录定位表的记录定位在在数数据据表表应应用用中中，有有时时需需要要将将记记录录指指针针定定位位到到某某条条记记录录上上，然然后后进进行行记记录录处处理理。一一共共有有6种种定定位方式。位方式。v第一个：定位到第一条记录。第一个：定位到第一条记录。v最后一个：定位到最后一条记录。最后一个：定位到最后一条记录。v下一个：定位到当前记录的下一条。下一个：

3、定位到当前记录的下一条。v上一个：定位到当前记录的上一条。上一个：定位到当前记录的上一条。v记录号：定位到指定记录号的位置记录号：定位到指定记录号的位置v条件定位：指针将移至满足条件的首条纪录。条件定位：指针将移至满足条件的首条纪录。操作命令：操作命令：”表表”转到记录转到记录”例例4.1在在“学学生生表表”的的头头5条条记记录录中中，定定位位指指针针到到奖学金高于奖学金高于100的男生。的男生。4.1.2表的数据筛选表的数据筛选在在显显示示表表中中的的记记录录时时，可可以以只只显显示示某某些些记记录录(选选择择)，称称为为记记录录筛筛选选；也也可可以以只只显显示示某某些些字字段段(投投影影)

4、，称为，称为字段筛选字段筛选。筛选命令见下节。筛选命令见下节4.1.3.2数据筛选的设置可用如下两种方法：数据筛选的设置可用如下两种方法：(1)”表表“”属性属性“(2)”窗口窗口“”数据工作期数据工作期“”属性属性“在在打打开开的的”工工作作区区属属性性“窗窗口口中中：在在”数数据据过过滤滤器器“中中设设置置记记录录筛筛选选；在在”字字段段筛筛选选“中中设设置置字字段筛选。段筛选。例例4.2对对“学学生生表表”设设置置筛筛选选条条件件，要要求求显显示示女女生生的名单。的名单。例例4.3设设置置上上例例的的输输出出字字段段为为“xh”，“xm”，“xb”。4.1.3表的查询命令表的查询命令下面

5、介绍几个有关查询的命令，包括记下面介绍几个有关查询的命令，包括记录定位和数据筛选录定位和数据筛选。v1.记录定位记录定位n1).绝对定位绝对定位n2).相对定位相对定位n3).顺序查询定位顺序查询定位n4).索引查询定位索引查询定位v2.数据筛选数据筛选n1).记录筛选命令：记录筛选命令：SET FILTERn2).字段筛选命令：字段筛选命令：SET FIELDS1).绝对定位绝对定位格式：格式：GO TO TOPGO TO BOTTOMGO RECORD 功能：功能：定位到首记录定位到首记录 定位到末记录定位到末记录 定位到第定位到第N条记录。条记录。2).相对定位相对定位格式格式:SKIP

6、+|-功能功能:定位到当前记录的上一条定位到当前记录的上一条|下一条下一条|往上往上或往下移动或往下移动N条。条。例例4.4打开打开“学生表学生表”，定位到最后一，定位到最后一条记录，并向前移动三条记录，然后再向条记录，并向前移动三条记录，然后再向后移动一条记录。后移动一条记录。3).顺序查询定位顺序查询定位格式：格式：LOCATEFOR|WHILE功能：功能：在在指指定定的的范范围围内内，查查找找当当前前表表中中满满足足条条件件的的记记录录，并并且且定定位位到到第第一一条条满满足足条条件件的的记记录录。如如果果要要查查找找后后面面满满足足条条件件的的记记录录，可可以以反反复复使使用用CONT

7、INUE命命令令，直直至至到到达达范范围围边边界界或或表表尾尾为为止。止。例例4.5在在“学学生生表表”的的头头7条条记记录录中中，定定位位指指针到奖学金高于针到奖学金高于100的男生。的男生。4).索引查询定位索引查询定位(1)格式：格式：SEEK 功能：功能：在在指指定定的的范范围围内内，查查找找当当前前表表中中满满足足条条件件的的记记录录。如如果果找找到到，则则记记录录指指针针定定位位在在满满足足条条件件的的第第一一条条记记录录，且且FOUND()为为真真；查查找找失失败败时时，FOUND()的的返返回回值值为为假，记录指针定位在文件尾。假，记录指针定位在文件尾。4).索引查询定位索引查

8、询定位(2)说明：说明：(1)在在使使用用SEEK命命令令时时应应先先打打开开索索引引文文件件。SEEK命令中的表达式须和索引表达式的类型相同。命令中的表达式须和索引表达式的类型相同。(2)SEEK命命令令不不仅仅可可以以查查找找字字符符串串和和常常数数，它它还还可可以以查查找找字字符符型型、数数值值型型、日日期期型型或或逻逻辑辑型型表表达达式式的的值值。用用SEEK命命令令查查找找字字符符串串常常量量时时，字字符符串串常量必须放在定界符中。常量必须放在定界符中。(3)SEEK命命令令定定位位到到第第一一条条满满足足条条件件的的记记录录，反复使用反复使用SKIP命令可查找下一条满足条件的记录。

9、命令可查找下一条满足条件的记录。例例4.6索索引引查查询询的的例例子子：查查找找“学学生生表表”中中“奖奖学金学金”为为100的学生名单的学生名单1).记录筛选命令记录筛选命令格式：格式：SETFILTERTO功功能能：设设置置当当前前表表中中可可以以被被访访问问的的记记录录必须满足的条件。必须满足的条件。说明：说明：若若对对同同一一个个数数据据表表使使用用了了多多条条记记录录筛筛选选命令，仅最后一条起作用。命令，仅最后一条起作用。记记录录筛筛选选命命令令一一直直有有效效，直直至至表表关关闭闭为为止，或使用止，或使用SETFILTERTO命令。命令。例例4.7对对“学生表学生表”设置筛选条件，

10、要设置筛选条件，要求显示女生的名单。求显示女生的名单。2).字段筛选命令字段筛选命令格式：格式：SETFIELDSTO功能：设置当前表中可以被访问的字段。功能：设置当前表中可以被访问的字段。说明：说明：(1)若有多条字段筛选命令，它们是联合起作用若有多条字段筛选命令，它们是联合起作用的，即合并所有被指定的字段成为当前可被访问的，即合并所有被指定的字段成为当前可被访问的字段。的字段。(2)不带参数的不带参数的SETFIELDS命令，即命令，即SETFIELDSTO，表示当前表中无一字段可被访问。，表示当前表中无一字段可被访问。(3)要访问所有字段用：要访问所有字段用：SETFIELDSTOALL

11、。例例4.8对对“学生表学生表”设置输出字段为学号设置输出字段为学号“xh”，姓名，姓名“xm”，性别，性别“xb”。4.1.4表的统计命令表的统计命令下面介绍几个有关统计的命令。下面介绍几个有关统计的命令。v1.计数命令计数命令v2.求和命令求和命令v3.求平均值命令求平均值命令v4.计算命令计算命令v5.分类汇总命令分类汇总命令1.计数命令计数命令(1)格式：格式：COUNTFOR|WHILETO功能：计算当前表中在指定范围内满足指定功能：计算当前表中在指定范围内满足指定条件的记录的个数。条件的记录的个数。说明：说明：缺省范围与条件时，将对表中所有的记录进缺省范围与条件时，将对表中所有的记

12、录进行计数。行计数。计数的结果显示在主窗口的状态条中。若使计数的结果显示在主窗口的状态条中。若使用用TO子句，则可将计数结果保存到指定的内存子句，则可将计数结果保存到指定的内存变量中。变量中。1.计数命令计数命令(2)TO：将每个总计值存：将每个总计值存入相应的内存变量。如果指定的变量不存入相应的内存变量。如果指定的变量不存在，在，VFP将自动创建它。将自动创建它。如果如果SETDELETEON，则已做删除标，则已做删除标记的记录将不被记的记录将不被COUNT命令统计，但仍命令统计，但仍被被RECCOUNT()函数统计。函数统计。例例4.9统计统计“学生表学生表”中男生的数目中男生的数目并把结

13、果存到内存变量并把结果存到内存变量“男生数目男生数目”中。中。2.求和命令求和命令格式：格式：SUM数数值值字段名表字段名表FOR|WHILETO|TOARRAY功能：对当前表的指定数值字段或全部功能：对当前表的指定数值字段或全部数值字段进行求和。数值字段进行求和。说明：说明：数值字段名表缺省表示对所有数值型字数值字段名表缺省表示对所有数值型字段求和，范围缺省表示段求和，范围缺省表示ALL。例例4.10统计统计“学生表学生表”男生获得的奖男生获得的奖学金的总数，存到数组学金的总数，存到数组“男生奖学金男生奖学金”中。中。3.求平均值命令求平均值命令格式：格式：AVERAGE数值字数值字段名表段

14、名表FOR|WHILETO|TOARRAY功能：对当前表的指定数值字段或全部功能：对当前表的指定数值字段或全部数值字段求平均值。数值字段求平均值。说明：参数说明与说明：参数说明与SUM命令相同，不再命令相同，不再赘述。赘述。例例4.11计算计算“学生表学生表”中学生平均获中学生平均获得的奖学金数额，并输出结果。得的奖学金数额，并输出结果。4.计算命令计算命令格式：格式：CALCULATEFOR/WHILETO|TOARRAY功能：对当前表的指定字段按指定函数进行功能：对当前表的指定字段按指定函数进行计算。计算。说明：说明：参数说明与参数说明与SUM命令相同的，不再赘述。命令相同的，不再赘述。常

15、用的有五个计算函数常用的有五个计算函数。例例4.12计算计算“学生表学生表”中学生获得的最低中学生获得的最低的奖学金数额，并输出结果。的奖学金数额，并输出结果。CALCULATE常用的五个计算函数常用的五个计算函数AVG()：计算数值型表达式的：计算数值型表达式的算术平均值。算术平均值。CNT()：返回表中记录的数目。：返回表中记录的数目。MAX()：返回：返回的最大的最大值。值。可指定任何字符型、日期型、日可指定任何字符型、日期型、日期时间型、数值型等字段，或包含这些类型字期时间型、数值型等字段，或包含这些类型字段的表达式。段的表达式。MIN()：返回：返回的最小值。的最小值。SUM()：对

16、数值表达式求和。：对数值表达式求和。另有标准差另有标准差STD(),均方差均方差VAR(),净现值净现值NPV()共共8个。个。5.分类汇总命令分类汇总命令(1)格式：格式：TOTALTOONFIELDSFOR|WHILE功能：功能：分别计算当前表中满足条件的各类别数值字段的和。说明：说明：指定存放计算结果的表的名称。：指定总计时作为分组依据的字段。要求表必须以该字段排序要求表必须以该字段排序。FIELDS：指定要总计的字段。字段名间用逗号分隔。如果省略该子句，则默认合计所有的数值型字段。5.分类汇总命令分类汇总命令(2)例例4.13分别统计分别统计“学生表学生表”中男女生获得的奖学金总额，并

17、中男女生获得的奖学金总额，并将结果汇总到表将结果汇总到表“奖学金汇总表奖学金汇总表.DBF”。4.2结构化查询语言结构化查询语言SQLp118SQL(structurequerylanguage)是关系数据库系统的国际标准查询语言，当前主流的数据库都提供对该语言的支持，它包括数据定义数据定义、数据控制数据控制、数据操作数据操作以及数据查询数据查询。其中查询是数据库最重要的操作，SQL语言的SELECT语句为查询提供了灵活及全面的支持。(增删改名)v4.2.1数据定义数据定义表和字段数据定义v4.2.2数据操作数据操作记录数据操作v4.2.3数据查询数据查询查询数据查询4.2.1数据定义数据定义

18、一个表分为两个层次：第一个层次是表一个表分为两个层次：第一个层次是表的结构，第二个层次是表的数据。数据定的结构，第二个层次是表的数据。数据定义就是一系列对表结构操纵的义就是一系列对表结构操纵的SQL命令，命令，包括包括CREATETABLE-SQL，CREATECURSOR-SQL，ALTERTABLE-SQL，DROPTABLE-SQL命令。命令。v1.表的定义表的定义v2.表的修改表的修改v3.表的删除表的删除1.表的定义表的定义(1)(1)CREATETABLE：创建表文件创建表文件格式：格式：CREATETABLE(,)NULLNOTNULLDEFAULTPRIMARYKEYUNIQU

19、ECHECKERROR,CHECKERROR)1.表的定义表的定义(2)说明：说明：用字母表达，如下所示用字母表达，如下所示:C字符型字符型N数值型数值型D日期型日期型L逻辑型逻辑型M备注型备注型G通用型通用型例例4.14用用SQLCREATE命命令令创创建建一一个个“学学生生表表”包包含含学学号号(XH)，姓姓名名(XM)，性性别别(XB)，奖奖学金学金(JXJ)，备注，备注(BZ)。CREATE CREATE TABLE TABLE 学学生生表表(XH(XH C(7)C(7)NOT NOT NULL NULL UNIQUE,XM UNIQUE,XM C(8)C(8)NOT NOT NULL

20、,XB NULL,XB L,CSRQ L,CSRQ D,JXJ D,JXJ N(7,2),BZ M)N(7,2),BZ M)1.表的定义表的定义(3,临时表临时表)(2)CREATECURSOR：创建临时表创建临时表格式：格式：CREATECURSOR(,)(,),)说明：临时表只是一个存在于内存中的表，只说明：临时表只是一个存在于内存中的表，只要不关闭，就可以像操作数据表那样对临时表要不关闭，就可以像操作数据表那样对临时表进行操作，但是一旦被关闭，临时表就不存在进行操作，但是一旦被关闭，临时表就不存在了。了。例例4.15创建一个临时表创建一个临时表CREATECURSOR通讯录通讯录(姓名姓

21、名C(8),电话电话C(30)2.表的修改表的修改(1)功能：修改表结构。功能：修改表结构。格式：格式：ALTER TABLE ADDCOLUMN 完整完整性约束性约束 RENAME COLUMN TO ALTER DROP COLUMN 说明：说明：ADD增加字段增加字段 RENAME字段重命名字段重命名 ALTER修改字段修改字段 DROP删除字段删除字段2.表的修改表的修改(2)例例4.16为为“学生表学生表”增加增加A字段。字段。ALTERTABLE学生表学生表ADDAN(10)例例4.17修改修改“学生表学生表”的的A字段名称为字段名称为ZY。ALTERTABLE学生表学生表RENA

22、MECOLUMNATOZY例例4.18修改修改“学生表学生表”的的ZY字段类型为字符字段类型为字符型。型。ALTERTABLE学生表学生表ALTERZYC(20)例例4.19删除删除“学生表学生表”的的ZY字段。字段。ALTERTABLE学生表学生表DROPCOLUMNZY3.表的删除表的删除功能：删除表。功能：删除表。格式：格式：DROPTABLE例例4.20删除删除“学生表学生表”DROPTABLE学生表学生表4.2.2数据操作数据操作表结构建立之后需要填写表中表结构建立之后需要填写表中的数据或者对表中的数据进行修改，的数据或者对表中的数据进行修改，对表中的数据的操作包括插入、更对表中的数

23、据的操作包括插入、更新、删除等。新、删除等。v1.插入插入InsertintoValuev2.更新更新UpdateSetv3.删除删除Deletefrom1.插入插入功能：在表的末尾插入一条新纪录。功能：在表的末尾插入一条新纪录。格式：格式：INSERTINTO(,)VALUES(,)例例4.21向向“学生表学生表”插入一条新纪录。插入一条新纪录。INSERTINTO学生表学生表(XH,XM,XB);VALUES(“0603009”,”章玉婷章玉婷”,.F.)2.更新更新功能：更新记录，一次可以更新多条记录。功能：更新记录，一次可以更新多条记录。格式：格式：UPDATESET=,=)WHERE

24、例例4.22将将“学生表学生表”中女生的奖学金增加中女生的奖学金增加100元，再减少元，再减少100元。元。UPDATE学生表学生表SETJXJ=JXJ+100WHEREXB=.F.UPDATE学生表学生表SETJXJ=JXJ-100WHEREXB=.F.3.删除删除功能：逻辑删除记录，一次可以删除多功能：逻辑删除记录，一次可以删除多条记录，要彻底删除需要用条记录，要彻底删除需要用PACK命令。命令。格式：格式：DELETEFROMWHERE例例4.23物理删除物理删除“学生表学生表”中中“章玉章玉婷婷”的记录。的记录。DELETEFROM学生表学生表WHEREXM=”章玉婷章玉婷”PACK4

25、.2.3数据查询数据查询SQL的数据查询语句SELECT-SQL是SQL语言中使用最广泛的，也是SQL中的重点。(功能非常丰富，基本要求4子句)SQL语句命令的结构如下：Select字段名列表From表名列表Where条件Orderby排序依据Groupby分组名Having分组条件UnionSelect命令To文件名Into输出目标1.1.投影投影:SELECT子句子句(1)(1)功能：指定查询输出字段。功能：指定查询输出字段。格式：格式：SELECTALL/DISTINCTTOPPERCENT.|AS,.|AS,说明说明1.1.投影投影:SELECT子句子句(2)(2)例4.24查询“学生

26、表”中所有的信息。SELECT*FROM学生表学生表例4.25显示“学生表”中奖学金的金额的种类。SELECTDISTINCTJXJAS奖学金金额奖学金金额类型类型FROM学生表学生表例4.26查询“学生表”中奖学金的金额的种类。SELECTCOUNT(DISTINCTJXJ)AS奖学金金额种类奖学金金额种类FROM学生表学生表1.1.投影投影:SELECT子句子句(3)(3)注：注意区分以下两条命令的不同SELECTCOUNT(DISTINCTJXJ)AS奖学奖学金金额种类金金额种类FROM学生表学生表SELECTCOUNT(JXJ)AS获得奖学金的人获得奖学金的人数数FROM学生表学生表第

27、一条命令是查询奖学金发放的金额类型一共有多少种，在COUNT函数中用DISTINCT参数将相同的奖学金数额屏蔽掉。而第二条命令没有使用DISTINCT参数,那么COUNT函数将计算所有获得奖学金的学生的数目。1.1.投影投影:SELECT子句子句(4)(4)例4.27查询“学生表”中学生的姓名和年龄。SELECTXMAS姓名姓名,YEAR(DATE()-YEAR(CSRQ)AS年龄年龄FROM学生表学生表例4.28查询“学生表”中最高最低的奖学金数额、奖学金的平均值以及学生数。SELECTMAX(JXJ)AS最高奖学金最高奖学金,MIN(JXJ)AS最低奖学金最低奖学金,AVG(JXJ)AS平

28、均奖学金平均奖学金,COUNT(*)AS学生数学生数FROM学生表学生表投影投影说说明明(1)(1)ALL|DISTINCT：ALL(默认值)-显示所有结果。DISTINCT-不显示重复值。TOPPERCENT：显示查询结果的前条记录，若包含PERCENT为前百分条记录。使用该参数应该在ORDERBY子句中设定排序依据。AS：指定查询结果中列标题的名称。结果表中的每个字段除了字段名之外都有一个编号，最左边的列编号为1，从左到右以此类推。投影投影说说明明(2)(2).|：指定作为查询结果的列内容，可以是字段名、常量或表达式；用标识来自不同表同名字段；在中可以使用各种函数及如表4.2中的统计函数。

29、投影投影说说明明(3)(3)表表4.2SELECT子句的统计函数子句的统计函数名名 称称功功能能MIN(DISTINCT|ALL)求一列中的最小值求一列中的最小值MAX(DISTINCT|ALL)求一列中的最大值求一列中的最大值AVG(DISTINCT|ALL)按列计算平均值按列计算平均值SUM(DISTINCT|ALL)按列计算值的总和按列计算值的总和COUNT(*)或或COUNT(DISTINCT|ALL)统计记录个数个数统计一列中值的个数统计一列中值的个数2.2.筛选筛选:WHERE子句子句(1)(1)功能：功能：指定多表之间的联接条件和查询条件，多个条件用AND或OR联结。联接条件和筛

30、选条件-p127表格式：格式：WHEREANDAND/OR,AND/OR注意注意：联接条件只有AND。例4.29查找“学生表”中84年以前,或者85年以后出生的学生的名单。SELECT*FROM学生表学生表WHEREYEAR(CSRQ)19852.2.筛选筛选:WHERE子句子句(2)(2)例例4.30查找查找“学生表学生表”中出生于中出生于85年的学生年的学生名单。名单。SELECT*FROM学生表学生表WHERECSRQ=1985-01-01ANDCSRQ=1985-12-31或者或者SELECT*FROM学生表学生表WHERECSRQBETWEEN1985-01-01AND1985-12

31、-31或者或者SELECT*FROM学生表学生表WHEREBETWEEN(CSRQ,1985-01-01,1985-12-31)2.2.筛选筛选:WHERE子句子句(3)(3)例4.31查找“学生表”中男生的纪录。SELECT*FROM学生表学生表WHEREXB=.T.SELECT*FROM学生表学生表WHEREXB例4.32查找“学生表”中获得奖学金数额为100和200的学生名单。SELECT*FROM学生表学生表WHEREJXJ=100ORJXJ=200SELECT*FROM学生表学生表WHEREJXJIN(100,200)2.2.筛选筛选:WHERE子句子句(4)(4)例4.33查找“学

32、生表”中姓“李”的学生名单。SELECT*FROM学生表学生表WHEREXM=”李李”SELECT*FROM学生表学生表WHEREXMLIKE“李李%”例4.34查找“学生表”中名字含有”军”的学生名单。SELECT*FROM学生表学生表WHEREXMLIKE“%军军%”如果修改命令如下，表示查找第二个字是“军”的学生：SELECT*FROM学生表学生表WHEREXMLIKE“_军军%”3.3.排序排序:ORDER BY子句子句(1)功能：指定对查询结果排序的依据。功能：指定对查询结果排序的依据。格式：格式：ORDERBYASC/DESC,ASC/DESC,说明：说明：指定排序所依据的列。若依

33、据多个列排序，则指定排序所依据的列。若依据多个列排序，则列名之间用列名之间用“，”分隔，排序时先按第一项排分隔，排序时先按第一项排序，对第一项值相同的记录，按第二项排序，序，对第一项值相同的记录，按第二项排序，以此类推。以此类推。ASC/DESC：默认值默认值ASC-升序排列升序排列,DESC-降序排列。降序排列。3.3.排序排序:ORDER BY子句子句(2)排序依据可以是下列形式之一：排序依据可以是下列形式之一：(1)字段名字段名(2)列序号：表示该列在查询结果中的位列序号：表示该列在查询结果中的位置置(各列序号从左到右依次为各列序号从左到右依次为1、2、3)。(3)由由AS子句命名的列标

34、题子句命名的列标题注意：注意：ORDERBY子句中不允许直接使用子句中不允许直接使用表达式表达式(包括函数包括函数)。3.3.排序排序:ORDER BY子句子句(3)不同类型数据的排序规则：不同类型数据的排序规则：1.字母按字母按ASCII码值顺序码值顺序2.汉字按汉字码值顺序汉字按汉字码值顺序3.数值按大小顺序数值按大小顺序4.日期按前后顺序日期按前后顺序5.逻辑型逻辑型“假假”在前、在前、“真真”在后在后3.3.排序排序:ORDER BY子句子句(4)例4.35查询“学生表”中的学生信息，并按奖学金升序、奖学金相同按年龄降序排列，显示姓名、奖学金和年龄字段。SELECTXM姓名姓名,JXJ

35、奖学金奖学金,YEAR(DATE()-YEAR(CSRQ)年龄年龄FROM学生表学生表ORDERBY奖学金奖学金,年龄年龄DESC或者SELECTXM姓名姓名,JXJ奖学金奖学金,YEAR(DATE()-YEAR(CSRQ)年龄年龄FROM学生表学生表ORDERBYJXJ,3DESC注：不可将本题的ORDERBY子句替换为ORDERBYYEAR(DATE()-YEAR(CSRQ)但可以用ORDERBY3替换3.3.排序排序:ORDER BY子句子句(5)例例4.36查询查询“学生表学生表”中的学生信中的学生信息，并按奖学金升序排列，显示排序息，并按奖学金升序排列，显示排序后的前后的前3条记录。

36、条记录。SELECTTOP3*FROM学生表学生表ORDERBYJXJ4.4.分组分组:GROUP BY子句子句(1)功能：分组统计。功能：分组统计。格式：格式：GROUPBY,HAVING说明：说明：:指定的分组依据。与排序依据指定的分组依据。与排序依据一样，可以有三种表示方式：一样，可以有三种表示方式：(1)字段名字段名(2)列序号列序号(3)列标题。与排序依据不同的是，列标题。与排序依据不同的是，统计函数不能出现在分组依据中。统计函数不能出现在分组依据中。HAVING子句：与子句：与GROUPBY子句联用，指子句联用，指定对分组结果进行筛选的条件。定对分组结果进行筛选的条件。4.4.分组

37、分组:GROUP BY子句子句(2)注意区分HAVING子句与WHERE子句的不同，对分组操作而言，WHERE子句指定了哪些记录能参加分组，而HAVING子句指定的是分组后哪些记录能作为查询的最终结果输出。例4.37将“学生表”按照男女生分组统计人数和奖学金数。SELECTXB性别性别,COUNT(*)人数人数,SUM(JXJ)AS奖学金统计奖学金统计FROM学生表学生表GROUPBYXB4.4.分组分组:GROUP BY子句子句(3)例4.38将“学生表”中奖学金按照男女生分组，要求统计奖学金高于100元的学生人数以及他们所获得的奖学金总数。SELECTXB性别性别,COUNT(*)人数人数

38、,SUM(JXJ)AS奖学金统计分组奖学金统计分组FROM学生表学生表WHEREJXJ100GROUPBYXB例4.39将“学生表”中奖学金按照男女生分组，要求统计奖学金高于100元的学生人数以及他们所获得的奖学金总数，并输出男生的统计信息。SELECTXB性别性别,COUNT(*)人数人数,SUM(JXJ)奖奖学金统计分组学金统计分组FROM学生表学生表WHEREJXJ100GROUPBYXBHAVINGXB=.T.5.连接查询连接查询(1)连接查询连接查询可以通过WHERE子句也可以通过FROM子句实现。WHERE子句子句(只能内部连接)利用WHERE子句实现多表查询时，必须在WHERE子

39、句的条件表达式中给出各个表之间的连接条件，同时在FROM子句中列出所需要的所有表的名字。FROM子句子句(可以内部/外部连接)利用FROM子句实现多表查询时，在FROM子句中给出表以及各表之间的连接条件。5.连接查询连接查询(2)FROM子句的语法格式为：子句的语法格式为：FROM!ASINNER/LEFTOUTER/RIGHTOUTER/FULLOUTERJOIN!ASON5.连接查询连接查询(3)表表4.5联结类型联结类型(VFP支持ANSI92JoinAyntax)联结类型联结类型名称名称联接结果联接结果INNERJOIN内部连接内部连接只包含两个表中满足条件的记录LEFTJOIN左连接

40、左连接左表的全部记录以及右表满足条件的记录RIGHTJOIN右连接右连接右表的全部记录以及左表满足条件的记录FULLJOIN完全连接完全连接除重复记录外，包含左右表的全部记录表ALeftJoin表BInnerRightJoinJoin5.连接查询连接查询(4)例4.40查询学生的专业，要求实现内部连接，查询学生的姓名和专业号。SELECTXH学号学号,XM姓名姓名,ZYBH专业号专业号,ZYMC专业专业FROM学生表学生表INNERJOIN专专业表业表ONSUBSTR(学生表学生表.XH,3,2)=专业表专业表.ZYBHSELECTXH学号学号,XM姓名姓名,ZYBH专业号专业号,ZYMC专业

41、专业FROM学生表学生表,专业表专业表WHERESUBSTR(学生表学生表.XH,3,2)=专业表专业表.ZYBH将以上连接改为“左连接”，“右连接”和“完全连接”，观察结果有什么不同？5.连接查询连接查询(5)例4.44查询学生表中与学生“李永年”同一个系(专业)的学生的学号和姓名。p134SELECT学生表学生表1.xh学号学号,学生表学生表1.XM姓名姓名FROM学生表学生表学生表学生表1innerjoin学生表学生表学生表学生表2;onsubstr(学生表1.XH,3,2)=substr(学生表2.XH,3,2)and学生表2.xm=李永年SELECT学生表学生表1.xh学号学号,学生

42、表学生表1.XM姓名姓名FROM学生表学生表学生表学生表1innerjoin学生表学生表学生表学生表2;onsubstr(学生表1.XH,3,2)=substr(学生表2.XH,3,2)where学生表2.xm=李永年5.连接查询连接查询(6)例4.45查询学生的成绩单,要求显示学生的姓名和课程的名称。p134通过WHERE子句实现连接SELECT XM SELECT XM 姓名姓名,KCM,KCM 课程名课程名,CJ,CJ 成绩成绩 FROM FROM 成成绩表绩表,学生表学生表,课程表课程表 WHERE WHERE 成绩表成绩表.XH=.XH=学生表学生表.XH.XH AND AND 成绩

43、表成绩表.KCH=.KCH=课程表课程表.KCH.KCH 通过FROM子句实现连接SELECT XM SELECT XM 姓名姓名,KCM,KCM 课程名课程名,CJ,CJ 成绩成绩 FROM FROM 学学生表生表 INNER JOIN INNER JOIN(成绩表成绩表 INNER JOIN INNER JOIN 课程表课程表 ON ON 成绩表成绩表.KCH=.KCH=课程表课程表.KCH).KCH)ON ON 学生表学生表.XH=.XH=成绩表成绩表.XH.XH括号可以去掉,由外到内顺序联接!5.连接查询连接查询(7)嵌套查询嵌套查询在在SQL中，由中，由SELECTFROMWHERE

44、组成的结构叫做查询块查询块。在这个结构的WHERE子句中再插入另一个(括号括起)查询块的查询方式叫做嵌套查询嵌套查询。其中外层查询块也叫父查询父查询，内层查询块也叫子查询子查询(Subquery)。SQL允许多层嵌套查询，但是如果增加排序控制子句，则只允许放在最外层结构中，也就是说只能对最终输出内容只能对最终输出内容排序排序。5.连接查询连接查询(8)嵌套子查询嵌套子查询的SELECT-SQL命令形式如下：SelectFromWhere谓词谓词(selectfromwhere)例4.46查询选修“计算机基础”的学生名单。SELECTXM姓名FROM学生表WHEREXHIN(SELECTXHFR

45、OM成绩表,课程表WHERE成绩表.XH=学生表.XHAND成绩表.KCH=课程表.KCHANDKCM=计算机基础)p136谓词表5.连接查询连接查询(9)例4.47查询奖学金高于某个陈姓学生的学生名单。p136SELECT*FROM学生表学生表WHEREJXJANY(SELECTJXJFROM学生表学生表WHEREXMLIKE“陈陈%”)例4.48查询奖学金高于所有陈姓学生的学生名单。SELECT*FROM学生表学生表WHEREJXJALL(SELECTJXJFROM学生表学生表HEREXMLIKE“陈陈%”)5.连接查询连接查询(10)集合查询集合查询利用集合的并操作，可以实现一个表内或两

46、个表之间的合并查询。由于查询结果会将两个表的数据组合在一起，所以要求两个表的输出字段必须类型和宽度都一样。在SQL中执行并操作后，系统会自动将合并后的重复行全部删除。例例4.51将奖学金为将奖学金为100的学生名单和奖学金的学生名单和奖学金为为150的学生名单合并的学生名单合并SELECT*FROM学生表学生表WHEREJXJ=100UNIONSELECT*FROM学生表学生表WHEREJXJ=150WHERE子句的谓词取值子句的谓词取值p135表谓词谓词含义含义IN以子表达式值作为主表达式的参数BETWEENAND以子表达式值确定主表达式的取值范围()ANY以子表达式最小值决定主表达式结果（

47、与SOME为同义词）()ALL以子表达式最大值决定主表达式结果NOTEXISTS以子表达式作为主表达式的条件LIKE用通配符%选择某类记录6.6.数据保存数据保存:INTO子句子句(1)功能：指定查询结果的输出目标。功能：指定查询结果的输出目标。格式：格式：INTOTABLE|CURSOR|ARRAY(1)INTOARRAY将查询结果存放到一个二维数组中。该数组可由查询直接创建，每行存放一条记录，每列对应于查询结果的一列。若查询结果只有一行，该数组可当做一维数组使用。6.6.数据保存数据保存:INTO子句子句(2)例4.52从“学生表”中查出所有专业名称(不为空、不重复)，并将查询结果保存在数

48、组ZY中。SELECTDISTINCTZYMCAS专专业业名名称称From学学生生表表,专专业业表表WhereSUBSTR(学学生生表表.XH,3,2)=专业表专业表.ZYBHIntoarrayzy(2)INTOCURSOR将查询结果保存在一个只读临时表中。临时表是一个暂时的表，查询结束后，该表自动作为当前表打开，可以像一般的表一样使用它，但仅是只读。该临时表一旦关闭则自动删除。6.6.数据保存数据保存:INTO子句子句(3)例4.53根据学生表,课程表和成绩表，查询学号为”0303001”的学生姓名、所学课程、课程学分及成绩，并将查询结果保存在临时表temp中。SELECTXMAS姓名姓名,

49、KCMAS课程名课程名,XFAS学分学分,CJAS成绩成绩;FROM学生表学生表,课程表课程表,成绩表成绩表WHERE学生表学生表.XH=成绩表成绩表.XHand课程表课程表.KCH=成绩表成绩表.KCHand学生表学生表.XH;=0303001INTOCURSORTEMP6.6.数据保存数据保存:INTO子句子句(4)(3)INTOTABLE将查询结果保存在一个自由表文件(.DBF)中。查询结束后该表自动作为当前表打开。例4.54对学生表按性别汇总奖学金总额，并将汇总结果保存在xbhz表中。SELECTXBAS性别性别,sum(jxj)as奖学金奖学金From学生表学生表groupbyxbI

50、ntotablexbhz.dbf7.文本输出文本输出:TO子句子句(1)功能：指定查询结果的输出目标。功能：指定查询结果的输出目标。格式：格式：TOPRINTER|FILEADDITIVE|SCREEN说明：说明：TOPRINTER:将查询结果直接输出到打印机。TOFILEADDITIVE:将查询结果保存在一个文本文件(.TXT)中。若指定文本文件已存在，可使用ADDITIVE选项使查询结果以追加方式存入指定文件，否则将覆盖指定文件。文本文件不是表，它不会像表那样打开着。TOSCREEN:活动窗口。7.文本输出文本输出:TO子句子句(2)例4.55对学生表按性别汇总奖学金总额，并将汇总结果保存