第 5 章 创建查询和视图

第五章创建查询和视图

5.1创建查询

1.查询的概念

◆查询：就是向一个数据库发出检索信息的请求，从中提取符合特定

条件的记录。

◆查询文件：一个查询可以用一条SELECT-SQL语句来完成，查询保存

为一个扩展名为. qpr的文件；文件中保存的是实现查询的

SELECT-SQL命令，而非查询的结果。查询被运行后，系统还会生成

一个编译后的查询文件，扩展名为. qpx。

◆查询结果：通过运行查询文件得到的一个基于表和视图的动态的数

据集合。查询结果可以用不同的形式来保存。查询中的数据是只读

的。

◆查询的数据来源：数据源的类型可以是一张或多张相关的自由表、

数据库表、视图或它们的组合。

2.用查询设计器创建查询

举例：查询教师表中基本工资在800元以上（含800元）的教师的工号，姓名和基本工资情况，按系代号升序排序，同一个系的教师按基本工资降序排序。

（1）打开查询设计器

方法1：从文件菜单或工具栏上单击新建→查询→新建文件→

进入查询设计器

方法2：当所用的数据表已在项目中时，从项目管理器窗口中单击

数据→查询→新建→新建查询→进入查询设计器方法3：从命令窗口中输入命令：

create query查询文件名&&创建新查询

modify query查询文件名&&修改已存在的查询（2）定义查询的输出内容(字段)

单击字段选项卡→从可用字段列表框中单击所需字段（当输出的

按钮，打开表达式生成器，构造出所需的表达式）→单击添加按钮→所需字段自动出现在选定字段框中。

注意：别名的添加：as 别名

（3）设置查询的筛选条件

筛选条件决定将哪些记录显示出来。

在筛选框中构造筛选条件表达式时，要注意在实例框中输入不同数据类型时的格式：

字符串可以不带引号（当与源表中的字段名相同时才用引号）；

日期型数值要用{ }括起来；

逻辑型数据两侧要带.号，如 .T. .F.

举例：查询工号开头字母为A的教师。

1）gh like A%

2）substr(gh,1,1) = A

3）between(substr(gh,1,1) ,A,A)

注意大小写的区别（补充函数UPPER（），LOWER（））

（4）设置查询结果的排序依据

排序决定查询输出结果中记录显示的顺序。

单击排序依据→从选定字段框选中字段→选择升序或降序→单击添加。

（5）设置查询结果的分组依据

所谓分组，是指对表中记录取重复值的记录进行压缩，形成一个结果记录，目的是为了完成基于该组记录的计算或统计，对每一组记录进行求平均值、求和、统计个数、最大值、最小值等。

几个常用的统计函数

举例：查询教师表gzc中各职称教师的人数，基本工资总额和平均工资，并按平均工资降序排序，查询结果输出中必须包含职称，人数，基本工资总额和平均工资四个列。

注意：用于分组的字段不一定是选定输出的字段，但分组字段不能是一个计算字段。

可以用满足条件…来对分组结果进行进一步筛选。

（6）对查询结果的其他设置

可以排除查询结果中所有重复的行，并设置结果的记录范围。

All:全部

前N个记录：TOP N

前N%个记录：N PERCENT

（7）选择查询结果的输出类型

默认情况下，查询结果将输出在浏览窗口中，且其中的数据是只读的。

设置其他输出类型的方法：打开查询菜单→选择查询去向→在查询去向对话框中选择一种。一般多选择表或报表。

在SELECT-SQL语句中使用INTO /TO来定向查询结果

（8）运行查询

在查询设计器打开的状态下，单击常用工具栏上的查询菜单中选择运行查询。其他情况下，可从项目管理器中选中查询

文件并单击运行按钮，或从程序菜单中选择执行命令，或从命令窗

口中输入：DO 查询文件名。

3.创建多表查询

?多表查询：当需要获取存储在两张或更多张表或视图中的信息时，需要将这些表或视图添加到查询中来，多张表可以是数据库表，自

由表或视图的任意组合。

?联接与联接条件：

（1）联接：指查询或视图的一个数据库操作，通过比较指定字段中的值联接两张或多张表或视图中的记录。

（2）联接类型：

内联接：两张表中仅满足条件的记录。

左联接：表中在联接条件左边的所有记录，和表中联接条件右边的且满足联接条件的记录

右联接：表中在联接条件右边的所有记录，和表中联接条件左边的且满足联接条件的记录

完全联接：表中不论是否满足条件的所有记录。

?创建多表查询：

举例：查询信息管理系中所有成绩总分在480分以上，并且各门课程的

成绩不得低于70分的学生，要求在查询结果中包含学生的学号，姓名，总分，平均分，并按照总分从高到低排序。

4. 创建交叉表查询

打开待查询的数据表→从文件菜单中单击新建→在新建对话框中选择查询并单击向导→选择交叉表向导→从单张表中选取字段→

设计布局→加入总和信息→选择查询结果的保存方式→给出查询文件名并选择保存位置→查询结果显示在查询窗口中。注意：不是任何表都适宜采用交叉表的形式。

5. 用SELECT-SQL命令创建查询

常用的格式：

SELECT表名1. 字段名1 [AS标题名1], 表名1. 字段名2 [AS

标题名2], …

FROM数据库名! 表名1 [inner/left/right/full] join[，数据库名!

表名2 ] [inner/left/right/full] join [, 数据库名! 表名3 ]

On 表之间的联接条件

[TO FILE文本文件名| into table | into cursor 表文件名]

[into array arrayname][to screen]

[ WHERE选定条件]

[GROUP BY分组字段名]

[HA VING分组中的满足条件]

[ORDER BY排序字段名 1 [ASC | DESC][, 排序字段名 2

[ASC | DESC] ...]]

示例：（1）显示JS表中所有教师的工号和姓名

（2）显示XS表中学号以“99”开头的学生情况。

（3）显示CJ表中有不及格课程的学生的学号，姓名，不及格

课程门数，最高分，最低分。

5.2 创建视图

1．视图的特点

视图是存在于数据库中的一个虚表，不以独立的文件形式保存；

视图中的数据是可以更改的，它不仅具有查询的功能，且可以把更新结果反映到源数据表中；

视图打开时，其基表自动打开;但视图关闭时，其基表并不随之自动关闭；

关闭一个视图所对应的基表，将不会自动关闭该视图

视图的数据源可以是自由表、数据库表或另一个视图。

2．用视图设计器创建本地视图

从项目管理器中选择一个数据库→选择本地视图→单击新建按钮→选择新视图→添加所需的数据表→在视图设计器中按照与创建查询相同的步骤（2）～（6）建立视图→设置更新条件→保存视图→给出视图名称→关闭视图设计器（可以先运行一下看看）。

创建参数化视图：

在筛选页面的实例框中输入：？参数名→从查询菜单中选择视图参数→在对话框中给出参数名并选择参数类型→确定→保存视图→给出视图名称→关闭视图设计器（可以先运行一下看看）。

例题：

1、设计查询（8分）(2005sp01)

在TEST项目中已存在查询chaxun，且在SJK中包含一个名为XSCJST的视图，该视图中包含学生的学号(xh)，课程代号(kcdh)、课程名(kcm)和成绩(cj)字段，另外在SJK的学生（xs）表含有学生的学号（xh）和所在班级编号（bjbh）字段。按如下要求修改查询：

基于xs表和XSCJST视图表查询每个班课程代号为”01”的考试情况。要求输出字段为：bjbh,kcdh,kcm,最高分，平均分，不及格人数，查询结果按bjbh 的升序排序(注：成绩低于60分为不及格)。

2、设计查询（8分）(2005sp02)

在TEST项目中已存在查询chaxun，且在SJK中学生（xs）表包含学号（xh）、

姓名（xm）、性别（xb）、班级编号（bjbh）、系代号（xdh）和专业代号（zydh）字段，专业（zy）表含有专业代号（zydh）和专业名称（zymc）字段，按如下要求修改查询：

基于xs表和zy表查询“01”年级每个专业女生所占比例。要求输出字段为：zydh,zymc,年级，女生人数和女生所占比例，查询结果按女生所占比例从高到低排序（假定bjbh字段值的前二位表示年级）。

3、设计查询（8分）(2005sp03)

TEST项目中已经存在查询chaxun，且在SJK中包含一个名为JSST的视图。该视图包括教师工号（gh）姓名（xm）、出生日期（csrq）、职称（zc）和系代号（xdh）字段；在SJK的系名（XIM）表含有系代号（xdh）和系名称（ximing）字段。按如下要求修改查询：

基于JSST视图和XIM表查询每个系职称为“教授”的人数、平均年龄，要求输出字段包括：xdh、ximing、zc、人数、平均年龄、查询结果按人数从高到低排序。

4、设计查询（8分）(2005sp04)

TEST项目中已经存在查询chaxun，且在SJK中包含一个名为JSST的视图。该视图包括教师工号（gh）、姓名（xm）、出生日期（csrq）、职称（zc）和系代号（xdh）字段，在SJK的工资（GZ）表含有教师工号（gh）、基本工资（jbgz）和综合津贴（zhjt）字段。按如下要求修改查询：

基于JSST视图和GZ表查询每个系各类职称基本工资、岗位津贴和综合津贴三项之和的最大值、最小值、平均值、要求输出字段包括：xdh、zc，最大值、最小值、平均值，查询结果按xdh从高到低排序。

5、设计查询（8分）(2005sp05)

在TEST项目中已存在查询chaxun，且在SJK中学生（xs）表包含学号（xh）、姓名（xm）、性别（xb）、班级编号（bjbh）、系代号（xdh）和专业代号（zydh）字段，专业（zy）表含有专业代号（zydh）和专业名称（）zymc）字段，按如下要求修改查询：

基于xs表和zy表查询“01”年级每个专业女生所占比例。要求输出字段为：zydh,zymc,年级，女生人数和女生所占比例，查询结果按女生所占比例从高到低排序（假定bjbh字段值的前二位表示年级）。

6关于SELECT-SQL的考察

(1)．在某教学管理数据库中，有一个成绩表（cj.dbf），表结构及其记录如下表所述。

对于下列SELECT-SQL语句，其查询结果含有3条记录。

SELECT xh, COUNT (*) AS 选课门数FROM cj GROUP BY xh (2)．在某教学管理数据库中有一个学生表（XS.DBF ），其表结构及其所含有的记录数据如表1 所示。

对于下列SELECT-SQL 语句，其查询结果含有____3___ 条记录。

SELECT * FROM xs WHERE SUBSTR(xh,5,2)=＇01＇

(3)．已知学生（XS ）表中含学号（xh ）、性别（xb ）、专业（zy ）字段。下列ＳＱＬ命令用来查询每个专业的男、女生人数。

SELECT zy, SUM(IIF(xb=＇男＇,1, ________)) AS 男生人数，；

SUM(IIF(xb=＂女＂,1, ________)) AS 女生人数；

FROM xs;

GROUP BY 1

(4)．已知成绩（CJ ）表中含学号（xh ）、课程代号（kcdh ）和成绩（cj ）

字段，成绩表中存有记录数据如表2 所示。

表２成绩表的记录数据

运行下列程序字段以后，变量m 、n 的值分别为___．Ｆ．２__ 。

【程序清单】

SELECT * FROM cj;

WHERE cj.cj<60;

INTO TABLE cj_tmp

m=EOF()

n=RECCOUNT()

(5)．教学管理数据库中含有课程表（KC ）及成绩表（CJ ）。课程表中含有以下字段：课程代号（kcdh ），课程名(kcm) ，学分(xf) ；成绩表中有以下字段：学号（xh ），课程代号(kcdh) ，成绩（cj ）。成绩表记录了全体学生所学各门课程的成绩。现根据上述两表查询总学分已超过120 的所有学生情况（要求输出：学号，平均分，总学分，并按学号的升序排列），可使用下述命令。（注：成绩小于60 分的学分设置为0 ）

SELECT xh AS 学号,A VG(cj.cj) AS 平均分,_sum(iif(cj>=60,xf,0))_ _____ AS 总学分;

FROM kc INNER JOIN cj ON kc.kcdh=cj.kcdh;

GROUP BY xh;

HA VING 总学分>=120;

ORDER BY xh

(6)．已知学生（XS）表含有学号（XH）、姓名（xm）和系代号（xdh）字段；成绩（CJ）表中含有学号（xh）、课程代号（kcdh）和成绩（cj）字段；系名（XIM）表中含有系代号（xdh）和系名（ximing）字段。以下的SELECT-SQL语句是查询数据库SJK中XIM表、XS表和CJ表中“信息管理系”每个学生的学号、姓名、系名、总成绩、平均成绩，要求输出总成绩不低于480分的所有学生学习情况，输出结果按学生学号进行排序，请完善以下的SQL语句。

SELECT xs.xh,xs.xm,xim.ximing,SUM(cj.cj) as 总成绩，A VG(cj.cj) as 平均成绩；

FROM sjk!xim INNER JOIN sjk!xs INNER JOIN sjk!cj;

ON xs.xh=cj.xh ON xim.xdh=xs.xdh;

where Xim.ximing=”信息管理系”;

ORDER BY xs.xh;

GROUP BY xs.xh;

having 总成绩>=480