第5章 查询和视图

1、第五章 查询和视图本章知识点：查询，视图，本章知识点：查询，视图，select-sql语句语句查询查询：从表中检索或统计出所需的数据从表中检索或统计出所需的数据视图视图：是一张虚表，不仅具有查询的功能，而且可以改是一张虚表，不仅具有查询的功能，而且可以改变视图中记录的值，并把结果送回到源表中变视图中记录的值，并把结果送回到源表中5.1 查询的创建和使用查询的创建和使用5.2 视图的创建和使用视图的创建和使用5.3 select-sql语句语句查询的创建和使用查询的创建和使用一一、查询的有关概念、查询的有关概念二二、利用查询设计器建立基于单表的查询、利用查询设计器建立基于单表的查询三三、创建多表

2、查询、创建多表查询四四、交叉表查询、交叉表查询查询的有关概念查询的有关概念 定义：是指向一个数据库发出检索信息的请求，使用一些条件提取特定的记录。 查询的结果：基于表和视图的动态的数据集合。 查询的数据源：自由表，库表，视图 （可以是单张表，也可以是多张相关的表） 查询的本质：生成一条select-sql语句，一个查询保存为一个扩展名为.qpr的文件。利用查询设计器建立基于单表的查询利用查询设计器建立基于单表的查询1、打开查询设计器、打开查询设计器2、定义查询输出的内容、定义查询输出的内容3、设置查询的筛选条件、设置查询的筛选条件4、设置查询结果的排序依据、设置查询结果的排序依据5、设置查询结

3、果的分组依据、设置查询结果的分组依据6、对查询结果的其它设置、对查询结果的其它设置7、运行查询、运行查询8、选择查询结果的输出类型、选择查询结果的输出类型例例5.1 查询教师工资表中基本工资在查询教师工资表中基本工资在500元以上（含元以上（含500元）的教师的工号、基本工资情况，并按基本工资的元）的教师的工号、基本工资情况，并按基本工资的降序排序。降序排序。打开查询设计器打开查询设计器的方式：打开查询设计器的方式： 项目管理器项目管理器/查询查询/新建新建 文件菜单文件菜单/新建，常用工具栏新建，常用工具栏/新建新建 命令命令create query查询设计器查询设计器界面界面 上半部分：数

4、据表显示区，首先应上半部分：数据表显示区，首先应添加数据源添加数据源 下半部分：下半部分：6个选项卡个选项卡查询设计器界面查查询询设设计计器器查询所基于的表列表查询所基于的表列表六个选项卡六个选项卡输出字段输出字段定义查询输出的内容“字段字段 Fields ”选项卡，添加、全部添加选项卡，添加、全部添加1) 若输出的列不是直接来源于表字段，可以定义若输出的列不是直接来源于表字段，可以定义关于关于表字段表字段的的函数函数或或表达式表达式2) 选定的选定的顺序顺序决定了结果中信息列的顺序决定了结果中信息列的顺序3) 若给字段添加若给字段添加别名别名，可在，可在“函数和表达式函数和表达式”文文本框中

5、输入，如本框中输入，如js.gh as 工号工号4) 工具按钮：查询设计器工具栏工具按钮：查询设计器工具栏/SQL按钮按钮，可，可以看到系统自动生成的以看到系统自动生成的select-sql语句语句输出字段输出字段设置查询的筛选条件“筛选筛选 Filter ”选项卡：利用条件表达式，定义记选项卡：利用条件表达式，定义记录子集录子集1) 一行构成一个逻辑关系一行构成一个逻辑关系 行之间行之间的关系可用的关系可用逻辑运算符逻辑运算符指定指定2) 在在“实例实例”文本框中输入比较条件时文本框中输入比较条件时 应注意应注意p117设置查询结果的排序依据“排序依据排序依据Order By”选项卡选项卡“

6、排序条件排序条件Ordering Criteria ”列表框中的顺序列表框中的顺序决定了排序的决定了排序的优先权优先权设置查询结果的分组依据“分组依据分组依据Group By”选项卡选项卡分组分组：将一组类似的记录压缩成一个结果记录：将一组类似的记录压缩成一个结果记录作用作用：可完成基于一组记录的计算：可完成基于一组记录的计算相关函数相关函数：COUNT( ),SUM( ),AVG( ) ,MAX( ) ,MIN( )说明说明：1. 用于分组的字段不一定是已选定输出字段，但应该是用于分组的字段不一定是已选定输出字段，但应该是表中的原始字段，不能是一个计算字段表中的原始字段，不能是一个计算字段2

7、. 若在分组的基础上，还需对结果进行筛选，单击若在分组的基础上，还需对结果进行筛选，单击“满满足条件足条件Having”按钮按钮对查询结果的其它设置“杂项杂项 Miscellaneous ”选项卡选项卡1. 排除结果中所有重复的行排除结果中所有重复的行 (无重复记录无重复记录No duplicates)2. 设置结果的记录范围设置结果的记录范围 全部全部 All 前前n个记录个记录 Top n 前前n%个记录个记录 n Percent运行查询“运行”按钮： !菜单命令：查询/运行查询命令：do *.qpr选择查询结果的输出类型 若为选择输出类型，结果将输出在浏览窗口中 菜单命令：查询/查询去向

8、 Query/Query Destination创建多表查询创建多表查询1）与单表查询）与单表查询区别区别#表的数目是两张或两张以上表的数目是两张或两张以上#多张表之间需建立联接条件多张表之间需建立联接条件2）联接的）联接的概念概念：联接是根据给定的联接条件将两个关系：联接是根据给定的联接条件将两个关系模式拼成一个新的关系，联接是指查询或视图的一个模式拼成一个新的关系，联接是指查询或视图的一个数据库操作，两张表的联接结果是一张数据库操作，两张表的联接结果是一张新表新表。3）联接的类型联接的类型例例5.2 查询森环院中每位学生的成绩情况，包括学号，姓查询森环院中每位学生的成绩情况，包括学号，姓名

9、，总分，平均分，最低分，并按总分降序排序。名，总分，平均分，最低分，并按总分降序排序。(xim,xs,cj三张表三张表)联接的类型联接的类型 内联接内联接(inner join) 两张表中仅满足条件的记录。两张表中仅满足条件的记录。 左联接左联接(left outer join) 表中在联接条件左边的所有记录表中在联接条件左边的所有记录 和表中联接条件右边的且满足联接条件的记录和表中联接条件右边的且满足联接条件的记录 右联接右联接(right outer join) 表中在联接条件右边的所有记录表中在联接条件右边的所有记录 和表中联接条件左边的且满足联接条件的记录和表中联接条件左边的且满足联接

10、条件的记录 完全联接完全联接(full join) 表中不论是否满足条件的所有记录（表中不论是否满足条件的所有记录（笛卡尔积笛卡尔积）交叉表查询交叉表查询交叉表查询交叉表查询：以：以电子表格电子表格形式显示数据的形式显示数据的查询，可以使用查询，可以使用交叉表向导交叉表向导建立交叉表建立交叉表查询，只能基于查询，只能基于单张表单张表建立交叉表查询建立交叉表查询步骤步骤：字段选取字段选取-布局布局-加入总和信息加入总和信息-完成完成视图的创建和使用视图的创建和使用一一、基本概念、基本概念二二、创建本地视图、创建本地视图三三、用视图更新源表数据、用视图更新源表数据四四、创建参数化视图、创建参数化视

11、图五五、视图的使用、视图的使用基本概念基本概念查询查询：得到的只是一组：得到的只是一组只读型只读型的检索结果的检索结果视图视图：不仅可以从表中：不仅可以从表中提取提取一组记录，还可以一组记录，还可以改变改变这些这些记录的值，并把记录的值，并把更新更新的结果送回源表，是数据库的一的结果送回源表，是数据库的一个组成部分，是基于表的并且可更新的数据集合。个组成部分，是基于表的并且可更新的数据集合。视图的视图的特点特点：兼有：兼有表表和和查询查询的特点的特点视图的视图的类型类型：本地视图本地视图-使用使用VFP-SQL语法从表或视图中语法从表或视图中选择信息；选择信息；远程视图远程视图-使用远程使用远

12、程SQL语法从远程语法从远程ODBC数据源表中选择信息。数据源表中选择信息。ODBC：一种用于数据库服务器的标准协议，通过：一种用于数据库服务器的标准协议，通过ODBC可访问多可访问多种数据库中的数据，可安装多种数据库的种数据库中的数据，可安装多种数据库的ODBC驱动程序，使驱动程序，使VFP可与该数据库相连，访问数据库中的数据。可与该数据库相连，访问数据库中的数据。创建本地视图创建本地视图1) 视图设计器视图设计器 项目管理器项目管理器/本地视图本地视图/新建新建 数据库打开时，文件数据库打开时，文件/新建，常用新建，常用/新建新建 数据库打开时，数据库打开时， Create view命令命

13、令2) create sql view命令命令格式：格式：create sql view ViewName as select-sql语句语句例，例，create sql view aa as select * from xs用视图更新源表数据用视图更新源表数据更新方法更新方法：1) “视图设计器视图设计器”界面界面2) 函数函数dbsetprop()更新步骤更新步骤：1) 使表可更新使表可更新2) 设置关键字段设置关键字段3) 设置可更新字段设置可更新字段4) 设置设置发送发送SQL更新更新创建参数化视图创建参数化视图目的目的：限定视图的作用范围，限定视图的作用范围， 可避免每取一部分记录就

14、要单独创建一个视图的情况可避免每取一部分记录就要单独创建一个视图的情况参数化视图的参数化视图的创建方法创建方法：1) 视图设计器：筛选选项卡，视图设计器：筛选选项卡，cj.kcdh=?课程代号课程代号 &课程代号课程代号为为参数名参数名2) create sql view命令中加一条命令中加一条where子句子句Create sql view aa as select cj.xh, cj.kcdh,cj.cj from cj;Where cj.kcdh=?课程代号课程代号 order by cj.xh本质本质：在视图的在视图的select-sql语句中加一条语句中加一条where子句子句 子句

15、是根据所提供的参数值建立的子句是根据所提供的参数值建立的 仅下载那些符合仅下载那些符合where子句条件的记录子句条件的记录参数值的传递参数值的传递：1) 可以在运行视图时传递可以在运行视图时传递 2) 也可以以编程方式传递，如：也可以以编程方式传递，如： 课程代号课程代号=02 use jxsj!aa browse视图的使用视图的使用1、视图的处理类似于表的处理、视图的处理类似于表的处理：用用use命令打开、关闭视图命令打开、关闭视图在浏览窗口中显示视图记录在浏览窗口中显示视图记录在数据工作期窗口中显示打开的视图在数据工作期窗口中显示打开的视图在文本框、表单或报表中使用视图作为数据源在文本框

16、、表单或报表中使用视图作为数据源既可通过项目管理器，又可借助既可通过项目管理器，又可借助VFP语言来使用视图语言来使用视图2、视图的打开与浏览、视图的打开与浏览3、创建视图索引、创建视图索引4、创建临时关系、创建临时关系5、用数据字典定制视图、用数据字典定制视图6、集成视图、集成视图视图的打开与浏览视图的打开与浏览界面界面：项目管理器窗口，数据工作期窗口：项目管理器窗口，数据工作期窗口命令命令：Use、browse命令命令注注：1) 打开视图时，若视图基于打开视图时，若视图基于本地表本地表，则在其它工作区中同时打，则在其它工作区中同时打开基表，数据工作期窗口显示视图及其基表（自动打开的本开基表

17、，数据工作期窗口显示视图及其基表（自动打开的本地基表并不在关闭视图时自动关闭）；若视图基于地基表并不在关闭视图时自动关闭）；若视图基于远程表远程表，则基表将不在工作区中打开，只在数据工作期窗口显示远程则基表将不在工作区中打开，只在数据工作期窗口显示远程视图的名称视图的名称2) 可在多个工作区中多次打开一张视图的多个实例，默认情况可在多个工作区中多次打开一张视图的多个实例，默认情况下，每次使用视图时，都要去取一个新的数据集合，但可在下，每次使用视图时，都要去取一个新的数据集合，但可在use命令中使用命令中使用norequery或或again子句，不必重新查询数据子句，不必重新查询数据源而打开视图

18、的另一个实例（视图为远程视图是特别有用，源而打开视图的另一个实例（视图为远程视图是特别有用，无需等待从远程数据源中下载数据）无需等待从远程数据源中下载数据）创建视图索引创建视图索引方法：方法：index on 命令命令index on eExpression tag TagName for lExpression asending|desending unique |candidate说明：说明： 与与表表的索引的索引不同不同，在视图上创建的本地，在视图上创建的本地索引索引非非永久保存永久保存，随视图的，随视图的关闭关闭而而消失消失创建临时关系创建临时关系作用作用：子表记录指针跟随主表记录指针

19、移动而移动子表记录指针跟随主表记录指针移动而移动创建创建临时关系的命令：临时关系的命令：set relation to Select 0Use cj order tag xh &设置子表的主控索引设置子表的主控索引Select 0 Use xs Set relation to xh into cj 解除解除临时关系：临时关系： set relation to /set relation off说明说明：可在视图索引之间、视图索引与表索引之间创建临时关系：可在视图索引之间、视图索引与表索引之间创建临时关系若要对一张视图于一张表创建临时关系，可在关系中设置视图为若要对一张视图于一张表创建临时关系，

20、可在关系中设置视图为父表，表为子表父表，表为子表用数据字典定制视图用数据字典定制视图 可为视图创建标题、注释、默认值、字可为视图创建标题、注释、默认值、字段及有效性规则及有效性信息，视图的段及有效性规则及有效性信息，视图的数据字典在功能上与库表中的相应部分数据字典在功能上与库表中的相应部分非常相似非常相似select-sql语句语句一、一、SQL语言概述语言概述(structured query language) 是美国国家标准局是美国国家标准局ANSI确认的关系数据库语言的标准，一确认的关系数据库语言的标准，一个个SQL命令可以代替多个命令可以代替多个VFP命令，命令，VFP支持的支持的S

21、QL命令有：命令有：Select-sql，alter table-sql，create table-sql，create cursor-sql，delete-sql，insert-sql，update-sql二、二、select-sql命令简介命令简介1）语法格式）语法格式2） select-sql命令主要组成部分命令主要组成部分三、三、举例说明举例说明举例说明举例说明1、基于单张、基于单张 表的查询表的查询2、基于多张表的查询、基于多张表的查询3、含有计算字段的查询、含有计算字段的查询4、子查询、子查询5、组合查询、组合查询select-sql命令主要组成部分命令主要组成部分Select F

22、rom 指定数据源表指定数据源表 join on 确定源表之间的联接确定源表之间的联接 Where 筛选记录或确定源表之间的联接筛选记录或确定源表之间的联接Group by 定义记录的分组定义记录的分组 Having 筛选结果记录筛选结果记录Order by指定结果的顺序指定结果的顺序Into|to 指定输出类型指定输出类型all|distinct 指定有无重复记录指定有无重复记录Top nExpr|percent 指定结果的范围指定结果的范围1)显示显示js表中所有教师的工号和姓名表中所有教师的工号和姓名2)显示显示xs表中学号以表中学号以“95”开头的学生情况开头的学生情况3)从高到低显示

23、从高到低显示cj表中代号为表中代号为“02”的课程的学生的课程的学生的学号和成绩的学号和成绩select js.gh,js.xm from jsSelect * from jxsj!xs where substr(xh,1,2)=95Select * from jsxj!xs where like(95*,xh)Select cj.xh,cj.cj from jxsj!cj; where cj.kcdh=02; order by cj desc1）显示）显示xs和和zy两张表中的三个字段两张表中的三个字段xh,xm,zymc的内容，的内容，并根据并根据zydh字段联接两张表。字段联接两张表。S

24、elect xs.xh,xs.xm,zy.zymc; from jxsj!xs,jxsj!zy; where xs.zydh=zy.zydh2）显示）显示cj表中有不及格课程成绩的学生的学号和姓名，表中有不及格课程成绩的学生的学号和姓名，有多门课程不及格的同学有多门课程不及格的同学只显示一次只显示一次。Select distinct cj.xh,xs.xm;from jxsj!xs , jxsj!cj;where cj.cj60 and cj.xh=xs.xh1）显示）显示cj表中课程代号以及最高分表中课程代号以及最高分Select cj.kcdh,max(cj.cj) as “最高分最高分”; from jxsj!cj ;group by kcdh2）查询每门课程的课程代号和名称以及最高分，并把）查询每门课程的课程代号和名称以及最高分，并把查询结果保存到查询结果保存到kc_maxcj.dbf中。中。Select cj.kcdh,kc.kcm,max(cj.cj) as 最高分最高分; from jxsj!cj,jxsj!kc;where cj.kcdh=kc.kcdh;group by cj.kcdh;into table kc_maxcjbrowse显示显示js表中已担任课程教师的工号和系名。表中已担任课程教师的工号和系名。Select