ACCESS查询和视图的重点

ACCESS查询和视图的重点

9.1 SELECT-SQL语句
SQL —— 结构化查询语言,关系数据库语言的标准
VFP的SQL命令使用 Rushmore技术来优化性能,并且一个SQL命令可代替多个VFP命令。
VFP支持的SQL命令
CREATE CURSOR-SQL
CREATE TABLE-SQL
ALTER TABLE-SQL
DELETE-SQL
INSERT-SQL
UPDATE-SQL
SELECT-SQL
SELECT-SQL命令的语法格式（见P175）
SELECT-SQL命令
SELECT Js.ximing, COUNT(Js.gh), SUM(Js.jbgz), ；
AVG(Js.jbgz); && “字段”选项卡
FROM jxgl!js; && 数据源
WHERE js.ximing <> "外语系";
AND a.ximing <> “中文系”; && “筛选”选项卡
GROUP BY js.ximing; && “分组”选项卡
ORDER BY 4 DESC && “排序依据”选项卡
SELECT-SQL应用-(示例1)
1.基于单个表的查询示例
显示JS表中所有教师的工号和姓名（从一个表中选取两个字段） SELECT js.gh, js.xm ; FROM jxgl!js
显示XS表中学号以“95”开头的学生情况。
SELECT * FROM jxsj!xs WHERE like("95*",xh)
Sele * from xs where substr(xh,1,2)=‘95’
SELECT语句—示例(2)
从高到低显示CJ表中代号为“01”课程的学生的学号和成绩 SELECT cj.xh,cj,cj
FROM jxsj!cj ; WHERE cj.kcdh=“01” ; ORDER BY cj.cj DESC
基于多个表的查询示例
显示XS和ZY两个表中三个字段xh,xm, zydh的内容，并根据zydh字段联接两个表。 SELECT xs.xh, xs.xm, zy.zymc ; FROM jxsj!xs, jxsj!zy; WHERE xs.zydh = zy.zydh
SELECT语句——示例(4)
显示CJ表中有不及格成绩的学生的xh和xm,多门不及格者显示一次，且显示开始的5条记录。 SELECT DISTINCT cj.xh,xs.xm ;
FROM jxsj!cj,jxsj!xs; WHERE cj.cj < 60 AND cj.xh = xs.xh
SELECT语句——示例(5)
含有计算字段的查询
分组显示CJ表中的课程代号及最高分
SELECT cj.kcdh, MAX(cj.cj) as “最高分”
FROM jxsj!cj ;
GROUP BY kcdh
SELECT语句——示例(6)
查询每门课程的课程代号和名称以及最高分，并把查询结果保存到kc_maxcj.dbf表文件中（把两个表中的字段内容保存到第三个表中）。 sele 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_maxcj
SELECT语句——示例(7)
基于视图的查询示例
已知在JXSJ库中包含一个视图viewKcXsCj，该视图包含三个字段：kc.kcm, cj.xh, xs.xm和cj.cj。现在从该视图中查询每门课程不及格学生的名单，查询结果包含视图中的所有字段。
以下命令建立基于viewKcXsCj视图的查询：
SELECT * FROM jxsj!viewKcXsCj ;
WHERE viewKcXsCj.cj<60
SELECT语句——示例(8)
子查询示例
显示JS表中已担任课程教师的姓名和系名。 SELECT js.ximing,js.xm;
FROM jxgl!js;
WHERE js.gh in (SELECT rk.gh;
FROM jxgl!rk)
SELECT语句——示例(8)
组

合查询示例
查询全校师生名单。要求如果是教师必须注明“教师”，结果中包含四个列：系名、类别、姓名和性别，并按系名排序。
SELECT js.ximing, ’教师’ AS 类别, js.xm,js.xb FROM jxsj!js ;
UNION;
SELECT xs.ximing, ’学生’ AS 类别, xs.xm,xs.xb FROM jxsj!xs ;
ORDER BY 1
SELECT语句——示例(9)
组合查询示例
查询各系科教师工资总额、各系科每个教师的工资以及全校
所有教师工资总额。要求结果中包含三个列：系名、姓名和
工资，并按系名排序。
SELECT js.ximing AS 系名, space(8) AS 姓名, sum(js.jbgz) AS 工资;
FROM jxsj!js;
GROUP BY js.ximing;
UNION;
SELECT js.ximing AS 系名, js.xm AS 姓名, js.jbgz AS 工资;
FROM jxsj!js;
UNION;
SELECT ‘总额’+space(14) AS 系名, space(8) AS 姓名,；
sum(js.jbgz) AS 工资;
FROM jxsj!js;
ORDER BY 1
利用查询和视图，可以对多张表中的数据同时进行操作。
查询：
从表中检索或统计出符合条件的的数据。
视图：
从表中提取一组记录，改变这些记录的值，并把更新结果送回源表中。
查询的本质
查询：向数据库发出检索信息的请求,它使用一些条件提取特定的记录。其运行结果是一个基于表和视图的动态的数据集合
数据源是一张或多张相关表或视图
查询可以SELECT-SQL语句来完成
.QPR，存放的是SELECT-SQL语句(非查询结果)
查询和视图设计器在本质上都是SELECT-SQL命令的
可视化设计方法
使用查询设计器建立查询
“查询向导”(慢)
“查询设计器”建立查询
编写SELECT-SQL语句
建查询的步骤
添加表或视图—— 确定该查询所涉及的数据源
设置输出字段(“字段”）—— 选定需要包含到查询结果中的字段及需计算的表达式。
设置联接（“联接” ）——确定匹配多表的联接表达式
设置筛选条件（“筛选” ）—— 确定查询条件
排序（“排序依据” ）—— 决定查询输出结果时，记录或行的顺序
分组（“分组依据” ）—— 分组的作用可将一组类似的记录压缩成一个记录，主要用于统计
其他（“杂项” ）—— 重复记录处理、结果记录范围等
设置输出的方式
查询设计器举例
单表查询
例：查询教师表中基本工资在500元（含500元）以上的教师的工号、姓名和基本工资情况，并使教师按系名升序排序，同一系的教师按基本工资的降序排序。
1. 打开查询设计器
(项目管理器|CREATE QUERY)
添加表或视图（FROM子句）:添加表JS
2. 定义输出内容(“字段”选项卡)
字段
表达式（AS 别名）
尽量用表达式生成器(尤其函数名)
3. 设置筛选条件（WHERE子句）js.jbgz>=500
条件的类型(参见P183)

条件的注意事项
仅当字符串与源表中的字段名相同时才加引号
如输

入源表字段名，将识别为一个字段
日期常量也不必用{}
.T. ，.F.(不能T F)
字段名不能使用“备注”或“通用”字段作为选定条件
右击/查看SQL

4. 设置排序依据（ORDER BY 子句）
升序/降序
优先权
5. 设置分组依据（GROUP BY 子句）自己分析
分组: 将一组类似的记录压缩成一个结果记录，这样就可以完成基于一组记录的计算
例：查询教师表中各系科教师的人数、工资总额和平均工资，并按平均工资降序排列。查询输出中必须包含系名、系人数、工资总额和平均工资四个列
按系名分组:把所有系名相同的记录压缩成一个记录
几个合计函数
计数：COUNT()
求和：SUM()
求平均：AVG()
查看SQL(如何理解ORDER BY 4)
分组结果的筛选条件
GROUP BY 子句的同时使用HAVING
平均工资<400
用于分组的可用字段不一定要是已选定输出的字段
但分组字段不能是一个计算字段，如输出列中的平均工资查看SQL

6. 其他设置
是否允许重复记录
结果的记录范围（需排序配合）
7. 运行查询
“运行”按钮
“查询”菜单中的“运行查询”
DO 命令（DO xijsgz. qpr）
右击
运行工具按钮
8. 选择输出类型（INTO 子句）参见P186
练习一：查询成绩表中各年级学生各门课程的及格学生的平均成绩。查询输出，一门课一条记录，输出年级、课程代号、平均成绩，记录按成绩由高到底的排列。
数据源：CJ.DBF ;
联接：<无>
选定字段：LEFT(CJ.xh,2)，KCDH，AVE(CJ.cj）
筛选条件：平均成绩>=60
排序依据：平均成绩
分组依据：年级、课程代号
练习二：查询成绩表中95年级学生各门课程的及格学生的平均成绩。查询输出，一门课一条记录，输出年级、课程代号、平均成绩，记录按成绩由高到底的排列。
创建多表查询
多表查询与单表查询的不同：
查询的数据源为两个以上的表或视图
表之间需要建立联接条件
1. 联接的概念
联接和联接条件
查询或视图的一个数据库操作
形成新表
笛卡尔乘积
限定条件
联接类型
内联接,左联接,右联接,完全联接
举例:
演示表XS和CJ
演示4个查询
2. 创建多表查询
向查询中添加多张表
设置表之间的联接条件
如果新添加的表与已存在的表之间在数据库中已经建立永久性关系，则系统将以该永久性关系作为默认的联接条件
否则系统会打开“联接条件”对话框，并以两个表的同名字段作为默认的联接条件
如果所有的表中都没有同名字段，则对话框中的联接条件为空白。此时可以创建其他的联接
如果按“取消”按钮，将不建立任何联接
(1)向查询中添加多张表
“查询设计器”工具栏、查询菜单、快捷菜单
(2)设置表之间的联接条件
如果表之间

在数据库中已经建立永久性关系，则系统将以该永久性关系作为默认的联接条件
如果两表之间在数据库中没有建立永久性关系，但两表有同名字段，则系统将以两表的同名字段作为默认的联接条件
建立联接条件的方法
对于已存在于查询中的表或视图,可以拖动一张
表中的字段到另一张表中的字段上
“查询设计器”工具栏/添加联接
“联接”选项卡/插入

对于已存在的联接，在查询设计器的表显示区中将看到表之间的联接线；在“联接”选项卡中将看到一行对应的条件
编辑联接条件的方法
双击连接线
单击“联接”选项卡中的条件行前的按钮
删除联接条件的方法
则先单击连接线，按【Del】键即可将其删除
联接选项卡/移去
联接不必基于完全匹配的字段(LIKE,==,>,<)
3. 多表查询举例
例1：查询信息管理系中所有成绩总分在480分以上，并且各门课程的成绩不得低于70分的学生。要求在查询结果中包含学生的学号、姓名、总分、平均分和最低分，并按照总分从高到低排列。
选取表(XS,CJ)
设置联接(XS.xh=CJ.xh内部联接)
选择字段(XS.xh，XS.xm，SUM(CJ.cj)，AVE(CJ.cj)，MIN(cj.cj) )
筛选条件：XS.ximing=“信息管理系”
排序依据： SUM(CJ.cj),降序
分组依据：XS.xh ；
满足条件:总分>=480 AND 最低分>=70
保存查询xs_cj.qpr
查询统计实验
统计各系科学生的总分和平均分，按系科和平均分名次排序。
按课程统计各系科的总分和平均分，按课程排序
按系科统计男学生的总分和平均分，按系科和得分名次排序
查询成绩不及格学生的ximing, xh, xm, kcm, cj,按ximing, xh排序。
按系科统计各教师的任课课时数，按系科工号排序
统计“信息管理系”各教师的任课课时数，按系科工号排序
交叉表查询
交叉表查询就是以电子表格形式显示数据的查询
交叉表查询可用“交叉表向导”建立
字段选取（只能在单独的表和视图中选取）
布局（拖进，拖出）
Xh---? 行
Kcdh ---?列
Cj ---?数据
加入总和信息
完成（cj_cross.qpr)
9.2 视图的创建和使用
比较
查询：只读，生成新文件，不是数据库的组成部分，
不可以作为数据环境的数据源
视图：读写，不生成新文件，是数据库的组成部分，
可以作为数据环境的数据源
定义
视图：数据库的一个组成部分，是基于表的可更新的数据集合，是一个虚表
基表：视图中的源数据表
分类
本地视图：使用VFP SQL语法从视图或表中选择信息
远程视图：使用远程SQL语法从远程ODBC数据源表
（例：ACCESS，EXCEL）中选择信息
创建本地视图
使用视图设计器
选中数据库，选择“本地视图”，选“新建”

视图设计器较查询设计器增加了一个“更新条件”选项卡
使用CREATE SQL VIEW 命令
格式：CREATE SQL VIEW <视图名> AS
例1：CREATE SQL VIEW js_view AS
SELECT * FROM JXSJ!JS
例2：CSQL=“SELECT * FROM JXSJ!JS”（字符型变量）
CREATE SQL VIEW JS_VIEW AS &CSQL(宏替换）
使用视图更新基表数据
注意：视图可以更新基表，而查询不行
1. 使表可更新
在“表”下拉列表框中指定受设置影响的表
在“字段名”列表中 选定表中的字段，至少应设置一个字段作为“主关键字”和“可更新字段”
“发送SQL更新”复选框是更新的主开关，等价于函数：
=DBSETPROP (“视图名”,“View”,”SendUpdates”,.T. )
2. 设置关键字段
VFP用关键字段来唯一标识那些已在视图中修改过的源表的更新记录，可用来检验更新冲突
=DBSETPROP (“<视图名>.<字段名>”，“Feild” , “KeyField” ，.T. )
(1) 指定可更新字段
如果要设置给定表中的字段允许更新，必须在该表的所有字段中设置一个关键字段
“关键”列：钥匙形，将字段设置为关键字段
“更新”列：笔形，设置一个字段为可更新的
“全部更新”：
在一张已有关键字段表中的除该关键字段外的所有字段均可设置为可更新
(2) 控制如何检查更新冲突
若不同用户同时更新基表数据，当发生冲突时如何处理？
使用“SQL WHERE”单选按钮组设置更新方法
关键字段：DB_KEY
关键字和可更新字段：DB_KEYANDUPDATABLE
关键字和已修改字段：DB_KEYANDMODIFIED(默认)
关键字段和时间戳：DB_KEYANDTIMESTAMP
DBSETPROP ( ) 函数
发生更新冲突时，可“强行更新”或“放弃更新”
TABLEUPDATE( )和TABLEREVERT( )函数
创建参数化视图
参数化视图用来限定视图的作用范围
参数化视图在视图的 SQL SELECT 语句中加一条 WHERE 子句
其中的子句是根据所提供的参数值建立的
参数值可以在运行时传递，也可以编程方式传递。
【例】基于CJ表创建视图,根据提供的课程代号下载该课程的成绩。
视图参数：变量或表达式
使用“视图设计器”的“筛选”选项
选择字段为“cj.kcdh”，在“实例”框中输入 “? 课程代号”。
使用 CREATE SQL VIEW 命令
注意：“？”后加单引号括起来的字符串，只是 输入提示，而非变量
视图的操作
处理视图类似于处理表
1. 视图的打开与浏览
OPEN DATABASE JXSJ
USE JS_VIEW
BROWSE
2. 关闭视图的基表
注意：基表并不随着视图的关闭而关闭

3. 创建视图索引(非永久索引，随视图关闭而消失)
INDEX ON 命令
4. 创建视图的临时关系
SET RELATION (视图作为主表较好)
数据环境中，视图不能被作为子表
5. 视图的修改、重命

名与删除
MODIFY / DELETE / RENAME VIEW <视图>
DBSETDROP（）