第08章查询和视图

1、第8章 查询和视图第第8章章查询和视图查询和视图 视图（view) 把分散在相关表中的数据通过联接条件收集到一起，构成一张“虚表” 。 视图并不等同于查询，查询不能修改表中的数据，而视图可以修改表中的数据 。 可以象打开表一样打开视图，对视图进行定位、移动、编辑；可作为查询的数据源；给字段设置标题、设置字段的有效性规则、添加注释等。 第8章 查询和视图8.1 查询的创建和使用查询的创建和使用 创建查询可以有三种方法：创建查询可以有三种方法：使用查询向导使用查询设计器直接编写select-sql语句 三种查询向导的功能三种查询向导的功能 不管是用查询向导还是用查询设计器创建查询，其结果都是生成一

2、条select-sql语句，在本质上是select-sql命令的可视化设计方法 。 第8章 查询和视图8.1.1查询设计器查询设计器 打开查询设计器有下列方法：打开查询设计器有下列方法： (1) “项目管理器”选“数据” 选项卡选“查询”。(2) “文件”菜单“新建”选“查询”。或者选“常用” 工具栏“新建”按钮。(3) 使用create query命令。 查询设计器查询设计器 打开查询设计器后打开查询设计器后系统还自动增加查系统还自动增加查询主菜单询主菜单 第8章 查询和视图8.1.1查询设计器查询设计器查询设计器对话框的上半部分为数据环境显示区，可看到所选择查询设计器对话框的上半部分为数据

3、环境显示区，可看到所选择的两个表，两个表之间有一条表示关系的连线。下半部分包括的两个表，两个表之间有一条表示关系的连线。下半部分包括6个选项卡，各个选项卡的功能见表。个选项卡，各个选项卡的功能见表。 查询设计器工具栏的按钮说明如表查询设计器工具栏的按钮说明如表 第8章 查询和视图8.1.1查询设计器查询设计器1查询设计器介绍查询设计器介绍 1.数据环境数据环境 查询设计器对话框的上半部分为数据环境显示区，用来显示所选择的表或视图，可以用“添加表（ ）”或“移去表（ ）”功能向数据环境添加或移去表。2.字段字段 “可用字段”框中列出了查询数据环境中选择的数据表的所有字段；在“选定字段”框中设置在

4、查询结果中要输出的字段或表达式；“函数和表达式”框用来建立查询结果中输出的表达式。“选定字段”框中行的顺序就是查询结果中列的顺序。 3.联接联接 添加多张表时，如已经有建立永久性关系，则该作为默认的联接条件。否则，系统会打开“联接条件”对话框，并以两张表的同名字段作为默认的联接条件。 第8章 查询和视图8.1.1查询设计器查询设计器联接条件对话框联接条件对话框 联接类型联接类型 第8章 查询和视图8.1.1查询设计器查询设计器两表之间的联接条件也可以通过查询设计器的“联接”选项卡来设置和修改。查询设计器的查询设计器的“联接联接”选项卡选项卡 第8章 查询和视图8.1.1查询设计器查询设计器4.

5、 筛选筛选 查询既可以查询所有记录，也可以查询满足某个条件的记录。指定 选取记录的条件可以使用查询设计器的“筛选”选项卡。 条件类型和意义条件类型和意义 第8章 查询和视图8.1.1查询设计器查询设计器5. 5. 排序依据排序依据 使用查询设计器，可以对查询结果中输出的记录排序。 查询设计器的查询设计器的“排序依据排序依据”选项卡选项卡 第8章 查询和视图8.1.1查询设计器查询设计器6. 6. 分组依据分组依据 分组就是将一组类似的记录压缩成一个结果记录，以便完成对这一组 记录的计算。 【例例ex_querygroupex_querygroup】查询男女职工人数、工资总额和平均工 资，并按平

6、均工资的降序排序。查询输出必须包含性别、人数、 工资总额和平均工资。 本例按照性别进行分组，也就是说，把所有性别相同的记录 压缩成一个记录，利用count()、sum()和avg()函数可以对每一组记录进行计数、求和及求平均计算。另外还有max()是求最大值函数，min()是求最小值函数。 第8章 查询和视图8.1.1查询设计器查询设计器新建一个查询，选择“基本情况”表，在“查询设计器”对话框的“字段”选项卡中，将下列字段和表达式设置到“选定字段”列表框中：基本情况.性别count(*) as 人数sum(基本情况.工资) avg(基本情况.工资)查询设计器查询设计器 “字段字段”选项卡选项卡

7、 第8章 查询和视图8.1.1查询设计器查询设计器在“分组依据”选项卡中，把“可用字段”中的“基本情况. 性别”字段添加到“分组字段”中。查询设计器查询设计器 “分组字段分组字段”选项卡选项卡 第8章 查询和视图8.1.1查询设计器查询设计器这样就完成了本例的查询要求。查询结果如图所示。在分组的基础上，还要对查询结果记录进行筛选，可以单击在“分组依据”选项卡中的“满足条件”按钮，打开“满足条件”对话框设置条件。 查询结果查询结果 “满足条件满足条件”对话框对话框 第8章 查询和视图8.1.1查询设计器查询设计器7. 杂项杂项 在“查询设计器”的“杂项”选项卡中，可以设置一些特殊的查询 条件。

8、查询设计器查询设计器 “杂项杂项”选项卡选项卡 第8章 查询和视图8.1.1查询设计器查询设计器各选项的意义各选项的意义: 如果选择复选框“无重复记录”，则查询结果中将排除所有相同 的记录；否则，将允许重复记录的存在。 如果选择复选框“交叉数据表”，将把查询结果以交叉表格式传 送给microsoft graph、报表或表。只有当“选定字段”刚好为3项 时，才可以选择“交叉数据表”复选框，选定的3项代表x轴、y 轴和图形的单元值。 如果选择复选框“全部”，则满足查询条件的所有记录都包括在 查询结果中。 第8章 查询和视图8.1.1查询设计器查询设计器8. 选择查询结果的输出去向 查询结果可以输出

9、到不同的目的地。在“查询去向”对话框中， 根据需要可以把查询结果输出到不同的目的地。 第8章 查询和视图8.1.1查询设计器查询设计器2生成生成sql语句语句 选择“查询”菜单（或者快捷菜单）中的“查看sql”项或单击查询 设计器的工具栏上的“sql”按钮（ ）即可看到所生成的 select-sql语句。 例如：例如：select 基本情况.编号,基本情况.姓名,基本情况.文化程度,基本情况.职称,基本情况.工资;from ry!基本情况;where 基本情况.性别=”女”;order by 基本情况.工资 desc 第8章 查询和视图8.1.1查询设计器查询设计器3生成查询文件生成查询文件

10、查询创建完成以后，单击常用工具栏上的“保存“按钮”或文件“菜单”中的“保存”命令，输入文件名，该文件中保存的就是sql语句。4运行查询运行查询 在项目管理器打开的情况下，选择要运行的查询文件，单击项 目管理器上的“运行”按钮，即可运行查询。 在查询文件打开的情况下，单击“常用”工具栏上的“运行”按钮 （ ）或“查询”菜单（或者快捷菜单）中的“运行查询”项即可运 行查询。 在命令窗口或应用程序中用do 命令运行查询。 第8章 查询和视图8.1.1查询设计器查询设计器【例【例ex_fsql】查询出每个职工的编号、所在部门名称、姓名、职 称、实发工资，并先按部门名称的降序排序，再按编号的升序排序。

11、创建查询步骤如下：创建查询步骤如下： 打开查询设计器，将“基本情况”表、“工资情况”表及“部门工资”表添加 到数据环境中，用联接选项卡设置“基本情况”表和“工资情况”表的联接 条件。 第8章 查询和视图8.1.1查询设计器查询设计器 通过用筛选选项卡设置如下筛选条件联接“基本情况”表和“部门工资”表：部门工资.部门编号=left（基本情况.编号,2）。 用字段选项卡设置要输出的字段，最后，选排序依据选项卡设置查询结果的输出顺序。 第8章 查询和视图8.1.1查询设计器查询设计器将创建的查询用ex_fsql文件名保存起来，则生成查询文件 ex_fsql.qpr。运行此查询，输出结果如下。 第8章

12、 查询和视图8.1.2 select-sql查询语句查询语句 1.1.sqlsql语言概述语言概述sql(structure query language，结构化查询语言)是美国国家标准局ansi确认的关系数据库语言的标准，用于对关系型数据库中的数据进行存储、查询、更新等操作。sql为用户提供了以下功能：数据定义、数据检索、数据操纵和数据控制。 sql语言是一种交互式的计算机操作语言，也是一种数据库编程语言，它不仅能够在单机的环境下提供对数据库的各种访问操作，而且还作为一种分布式数据库语言用于客户/服务器(client/server)模式数据库应用的开发。 第8章 查询和视图8.1.2 sel

13、ect-sql查询语句查询语句vfp支持如下支持如下sql命令：命令： (1) select-sql 指定查询条件并执行查询命令，vfp解释该查询，并且从表中检索出指 定的数据。 (2) alter table-sql 用于修改自由表的结构，可以对表中每一个字段的字段名、字段类型、 精度、比例、是否允许空值和引用完整性规则进行修改。 (3) create cursor-sql 用于创建一个临时表。 (4) create table-sql用于创建一张表。 (5) delete-sql用于将表中的记录打上删除标记。(6) insert-sql用于在已经存在的表的尾部追加一条新的记录。 (7) u

14、pdate-sql用于更新表中的记录，可以基于select-sql第8章 查询和视图8.1.2 select-sql查询语句查询语句2. select-sql命令命令 select all | distinct top n percent 列名 ,列名.from 表名 ,表名. | inner | left | right | full join 表名 on 联接条件into array 数组名 | cursor 临时表名 | dbf 表名 |table 表名 | to file 文件名 additive | to print prompt | to screen where 条件表达式 gr

15、oup by 列名 ,列名.having 条件 order by 表达式 asc/desc.第8章 查询和视图8.1.2 select-sql查询语句查询语句select-sql命令与查询设计器各选项卡的对比命令与查询设计器各选项卡的对比 第8章 查询和视图8.1.2 select-sql查询语句查询语句sql 查询语句的各子句的意义查询语句的各子句的意义:select子句指明查询输出的项目(称为列)也可以是表达式。利用表达式可以查询表中未存储但可以计算出的结果。构造表达式，sql 提供了加减乘除)四种运算符和一些函数。若以*代替列名，则表示查询表的所有列。 from子句指明被查询的表或视图名

16、。 into子句指明查询结果保存在何处。可以是数组、临时表或表。to 子句也是指明查询结果输出到何处。 where 子句说明查询的条件。 roup by子句将表按列的值分组，列的值相同的分在一组，having后的条件是选择组的条件，符合条件的组才能输出。 order by子句可对查询结果按子句中指定的列的值排序，asc 表示升序，desc表示降序。 第8章 查询和视图8.1.2 select-sql查询语句查询语句3. select-sql应用举例应用举例 (1) 简单查询简单查询 查询全部信息 select *;(* 表示全部的列)from 基本情况 不包重复信息 select distin

17、ct 姓名; from 基本情况使用distinct 可消除重复的值，这里表示去掉重复的姓名。 输出字段表达式 select 编号,姓名,year(date()-year(出生日期) as 年龄;from 基本情况 第8章 查询和视图8.1.2 select-sql查询语句查询语句 排序查询结果select 编号,姓名,工资;from 基本情况;where 工资1000;order by 工资 desc 数据区间查询 select 编号,姓名,职称,工资;from 基本情况;where 工资 between 1000 and 1500 数据包含查询 select 编号,姓名;from 基本情况

18、;where 文化程度 in (本科,大专) 第8章 查询和视图8.1.2 select-sql查询语句查询语句 数据匹配查询select *;from 工资情况;where 编号 like 01_ （ 或者 where 编号 like 01%） 第8章 查询和视图8.1.2 select-sql查询语句查询语句(2) 联接查询联接查询 即可以在两个表之间按指定列的相同值将一个表中的行与另一表中的行连接起来,从而大大增强了其查询能力。 【例【例ex_sql8】查询职工的编号、姓名、职称、工资和实 发工资。select 基本情况.编号,基本情况.姓名,职称,工资,实发工资;from 基本情况,工

19、资情况;where 基本情况.编号=工资情况.编号可以在可以在from子句中给表起一个别名。子句中给表起一个别名。select rs.编号,rs.姓名,职称,工资,实发工资;from 基本情况 rs,工资情况 gz;where rs.编号=gz.编号列名是唯一的，则不必加前缀 第8章 查询和视图8.1.2 select-sql查询语句查询语句(3) 联合查询联合查询 在sql 语言中可以将两个或多个查询结果进行并操作 (union) 。 需要注意的是，两个查询结果进行并操作时，它们必须具有相同 的列数，并且对应的列有着相同的数据类型和长度（对应的列名 可以不同）。 union 运算自动去掉重复

20、记录。 【例例ex_sql9】查询具有工程师职称和政工师职称的职工的编号和姓名。select 编号,姓名;from 基本情况;where 职称=工程师;union;select 编号,姓名;from 基本情况;where 职称=政工师 第8章 查询和视图8.1.2 select-sql查询语句查询语句【例ex_sql10】查询具有工程师职称和实发工资高于1000元的 职工的编号。select 编号;from 基本情况;where 职称=工程师;union;select 编号;from 工资情况;where 实发工资1000第8章 查询和视图8.1.2 select-sql查询语句查询语句(4)

21、 嵌套查询嵌套查询 【例ex_sql11】查询实发工资高于1000的职工的编号、姓名和职称。select 编号,姓名,职称;from 基本情况;where 编号 in ( select 编号; from 工资情况; where 实发工资1000)第8章 查询和视图8.1.2 select-sql查询语句查询语句(5) 库函数库函数 下列五种函数可以在下列五种函数可以在from子句中与选定的列一起使用。子句中与选定的列一起使用。 min 求（字符、日期、数值）列的最小值max 求（字符、日期、数值）列的最大值count(*) 计算记录个数 count 对一列中的值计算个数sum 计算数值列的总和

22、avg 计算数值列的平均值这些函数一般是从一组值中计算出一个汇总信息，group by子句用来定义或者划分进行统计与求和的组。 第8章 查询和视图8.1.2 select-sql查询语句查询语句【例ex_sql12】查询男女职工人数、最高工资、最低工资、工资 总额和平均工资。select 性别, count(*),max(工资), min(工资), sum(工资), avg(工资);from 基本情况;group by 性别 【例ex_sql13】查出有三人以上具有相同职称的职称名称。select 职称;from 基本情况;group by 职称;having count(*)=3第8章 查询

23、和视图8.1.3 查询的应用查询的应用 可以将查询结果用表单中的列表框控件显示出来。可以将查询结果用表单中的列表框控件显示出来。 例如，显示职工的编号、姓名、工资信息。 第8章 查询和视图8.1.3 查询的应用查询的应用 首先，创建一个表单applysql.scx，插入一个标签控件，caption属性设置为“编号 姓名 工资”，再插入一个列表框控件，如图所示。 第8章 查询和视图8.1.3 查询的应用查询的应用 将rowsourcetype属性设置为3，rowsource属性设置为“select 编号,姓名,工资 from 基本情况”，如图所示。columncount属性设置为3。保存表单即可

24、。 第8章 查询和视图8.1.4交叉表查询交叉表查询 交叉表查询是查询的一种，这种查询能以电子表格的形式显示出交叉表查询是查询的一种，这种查询能以电子表格的形式显示出总结性的数据。总结性的数据。 字段选取 “交叉表向导”的第一个步骤是字段选取，选择“xs”表的三个字段khmc、cpmc和sl。 布局 可以用鼠标把“khmc”字段拖到“行”框中，把“cpmc”字段拖到“列”框中，把“sl”字段拖到“数据”框。 加入总和信息 为查询结果指定一个“总计”列，这个“总计”列将会被加入到查询结果的最右侧。 完成单击“预览”按钮可以查看查询的结果 ，单击“完成”按钮，结束交叉表向导的运行。 第8章 查询和

25、视图8.2 视图的创建和使用视图的创建和使用8.2.1 创建本地视图创建本地视图 1视图设计器视图设计器 创建视图的过程与创建查询的过程非常相似。 第8章 查询和视图8.2.1 创建本地视图创建本地视图视图设计好后，可在项目管理器中运行或在视图设计器中运行。视图设计好后，可在项目管理器中运行或在视图设计器中运行。 在项目管理器中运行视图：首先，打开视图所在的项目文件，进入项目管理器；然后，在项目管理器中选择要运行的视图，再单击“浏览”按钮，就可看到运行视图后的结果。 在视图设计器中运行视图有两种方法：在视图设计器中打开视图后，在“查询”菜单中选择“运行查询”命令；或在视图设计器中单击鼠标右键，

26、在弹出的快捷菜单中选择“运行查询”命令。 第8章 查询和视图8.2.1 创建本地视图创建本地视图2命令方式命令方式 可以用下面的命令直接创建视图。格式：create sql view viewname as select_sql语句 例如，例如，创建视图xss_view ，选择销售商表的全部信息，命令如下：create sql view xss_view as select * from 销售商 例如例如，上面创建视图的命令，也可以改成下列形式：v_sql = “select * from 销售商”create sql view xss_view as & v_sql 第8章 查询和视

27、图8.2.1 创建本地视图创建本地视图3. 使用视图更新基表数据使用视图更新基表数据 使用视图还可以对其基表进行更新，在视图设计器的“更新条件”选项卡中设置关键字段和设置“发送sql更新”选项。【例例ex_editview】建立职工的基本情况视图，包含下列信息： 编号、姓名、出生时间、文化程度、职称、工资及婚否字段,并 使“职称”和“工资”字段是可以更新的，其他字段为不可更新的 （这里的更新是指对基表的更新）。 第8章 查询和视图8.2.1 创建本地视图创建本地视图在视图设计器中，除更新条件选项卡是查询设计器所没有的，其余的设置与查询设计器完全一样在视图设计器中，单击“更新条件”选项卡，设置更

28、新条件。 本例中，要更新的表是“基本情况”，关键字段是编号，要更新的字段是职称和工资。选择“发送sql更新”复选框，设置表为可更新。 第8章 查询和视图8.2.1 创建本地视图创建本地视图将视图存为editview，运行视图。 第8章 查询和视图8.2.1 创建本地视图创建本地视图如果视图中的某个字段没有设置为可更新的，运行视图时，虽然也可以在浏览窗口中修改这个字段，但修改的结果不会送到它所引用的基表中。如果设置为可更新的,修改的结果会送到它所引用的基表中。更新字段也可以用下列命令来进行设置： 设置关键字段 =dbsetprop(”视图名.字段名”,”field”,”keyfield”,.t.

29、) 设置可更新字段 =dbsetprop(”视图名.字段名”,”field”,”updatename”,”基表.字段名”) 设置“发送sql更新” =dbsetprop(”视图名”,”view”,”sendupdates”,.t.) 第8章 查询和视图8.2.1 创建本地视图创建本地视图4.创建参数化视图创建参数化视图为了使视图更加灵活，可以给视图的筛选条件设置参数，以避免每取一部分记录就创建一个视图的情况。这种带有参数的视图称为“参数化视图”。 第8章 查询和视图8.2.1 创建本地视图创建本地视图【例ex_para_view】建立一个视图，可以根据提供的职称，找出给 定职称的职工的基本情况。首先，在项目管理器中，选择“本地视图”，单击“新建”按钮，选择“基本情况”表后，进入视图设计器。接着，选择“字段”选项卡，单击“全部添加”按钮，将所有字段添加到“选