《查询与视图》PPT课件.ppt

1、第4章 查询与视图,4.1 查询的创建和使用 4.2 视图的创建和使用 4.3 SELECT-SQL语句 4.4 建立表之间的临时关系,4.1.1 查询概述 查询: 从数据源（一张或多张表、视图）中检索或统计出符合条件的记录数据。（结果是只读的） 数据源: 是一张或多张相关表(自由表或库表)或视图,4.1 查询的创建和使用,查询条件： 要那些字段？那些记录？按什么要求收集？ 查询实质： 执行一条SELECT-SQL命令语句 保存: .QPR文件，保存的是对应SELECT-SQL语句(非查询结果)。 查询和视图设计器在本质上都是SELECT-SQL命令的可视化（界面）设计方法,4.1.2 使用查

2、询设计器建立查询 创建查询的方法： 使用查询设计器 CREATE QUERY 查询文件名|？ “项目管理器”数据”查询”新建” “文件”新建”或“常用工具栏” “新建” 打开“查询设计器”建立查询 编写SELECT-SQL语句(难,建查询的步骤 打开查询设计器 添加表或视图 （即该查询所涉及的数据源） 设置输出字段 （“字段”选项卡） 选定需要包含到查询结果中的字段及需计算的表达式。 设置联接 （“联接”选项卡）确定匹配多表的联接表达式 设置筛选条件 （“筛选”选项卡） 确定查询条件,排序 （“排序依据”选项卡） 决定查询输出结果时，记录或行的顺序 分组 （“分组依据”选项卡） 分组的作用可将

3、一组类似的记录压缩成一个记录，主要用于计算。 其他 （“杂项”选项卡） 重复记录处理、结果记录范围等 设置输出的方式 浏览、临时表、表、图形、屏幕、报表、标签等,查询设计器举例（单表查询） 例1：查询教师表中基本工资在800元（含800元）以上的教师的工号、姓名和基本工资情况，并按系名升序排序，同一系的教师按基本工资的降序排序。 1. 打开查询设计器(项目管理器|CREATE QUERY) 添加表或视图:添加表JS: FROM子句 2. 定义输出内容(“字段”选项卡): SELECT 子句 字段 表达式（AS 别名,3. 设置筛选条件P113（WHERE子句）js.jbgz=800,4. 设置

4、排序依据（ORDER BY 子句） 升序/降序 优先权 查看SQL 5. 设置分组依据（GROUP BY 子句） 分组: 将数据源中一组相关的记录运算成一个查询记录输出，在查询运行中完成基于数据源的一组记录的统计计算,按系名分组: 把所有系名相同的记录压缩成一个记录 几个合计函数 计数COUNT(*) 求和SUM() 求平均函数AVG() 查看SQL(如何理解ORDER BY 4) 分组结果的筛选条件 GROUP BY 子句的同时使用HAVING 平均工资800 查看SQL,6. 其他设置 是否允许重复记录 结果的记录范围（需排序配合） 7. 运行查询 “运行”按钮 “查询”菜单中的“运行查询

5、” DO 命令（DO xijsgz. qpr,8. 选择输出类型（INTO 子句,例： 查询成绩表中各个学生的课程总分及平均成绩，要求查询输出学号、课程代号、总分及平均成绩，查询结果按总分由高到底的排列。 数据源：CJ.DBF ; 联接： 选定字段：xh，kcdh，sum (CJ.cj）, AVE(CJ.cj） 排序依据：总分 分组依据：xh,4.1.3 创建多表查询,多表查询与单表查询的不同： 查询的数据源为两个以上的表或视图 多张表之间需要建立联接条件 1. 联接的概念 联接: 查询或视图的一个数据操作,通过公共字段联接相关表或视图 联接结果： 形成一个新的单表（只读、暂时、用以建立查询)

6、 条件联接: 无条件联接：多表交叉组合,形成的新纪录数是多张表记录数的乘积. 条件联接:即按联接条件将多表纪录生成一个新单表纪录,联接类型P117 内联接,左联接,右联接,完全联接,2. 创建多表查询 打开查询设计器 向查询中添加多张表 - “添加表或视图”对话框 “查询设计器”工具栏添加表 查询菜单添加表 快捷菜单添加表 设置表之间的联接条件 存在永久性关系，则系统将以该永久性关系作为默认的联接条件,没有建立永久性关系，将两表同名字段作为默认的联接条件。 没有同名字段，可以创建其他的联接 如果按“取消”按钮，将不建立任何联接 自动默认联接类型为：内部连接,建立联接条件的方法 对于已存在于查询

7、中的表或视图，可以拖动一张表 中的字段到另一张表中的相应字段上建立联接条件 “查询设计器”工具栏/添加联接 “查询设计器”/“联接”选项卡/插入,编辑联接条件的方法 双击连接线 单击“联接”选项卡中的条件行前的按钮 “查询设计器”/联接选项卡 删除联接条件的方法 则先单击连接线，按【Del】键即可将其删除 联接选项卡/移去 联接不必基于完全匹配的字段 (LIKE,=,) 联接与筛选的联系与区别（p118,3. 多表查询举例(演示） 例2（book402） 查询信息管理系中所有成绩总分在480分以上，并且各门课程的成绩不得低于70分的学生。要求在查询结果中包含学生的学号、姓名、总分、平均分和最低

8、分，并按照总分从高到低排列。 选取表(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,SELECT Xs.xh, Xs.xm, SUM(cj.cj) AS 总分,; AVG(cj.cj) AS 平均成绩,; MIN(cj.cj) AS 最低分; FROM jxsj!xs INNER JOIN jxs

9、j!cj ON Xs.xh = cj.xh; WHERE Xs.ximing = 信息管理系; GROUP BY Xs.xh; HAVING 总分=480 AND 最低分=70; ORDER BY 3 DESC,4.1.4 使用向导建立交叉表查询,1.交叉表 以电子表格形式建立的查询。 2.例(book118,4.2 视图的创建和使用,4.2.1 概述 定义 视图：数据库的一个组成部分，是基于源数据的可更新的动态数据集合，是一个虚表（兼有表和查询的特点） 基表（base table)：视图中的源数据表。 分类 本地视图：使用VFPSQL语法从视图或表中选择信息 远程视图：使用远程SQL语法从远

10、程ODBC数据源表（例：ACCESS，EXCEL）中选择信息（P126,比较 查询： 只读，生成新文件，不是数据库的组成部分，有多种输出方式，不可以作为新的数据源 视图： 读写，不生成新文件，是数据库的组成部分，只有一种输出方式，可更新源数据表，也可以作为新的数据源,4.2.2 创建本地视图,使用视图设计器 “项目管理器”-选中数据库- -选择“本地视图”-选“新建” 视图设计器与查询设计器的不同：增加了一个“更新条件”选项卡 使用CREATE SQL VIEW 命令 条件：对应数据库打开 格式：CREATE SQL VIEW 视图名；AS SELECT-SQL 语句 例1： CREATE S

11、QL VIEW js_view AS SELECT * FROM sjk!JS 例2: CSQL=“SELECT * FROM sjk!JS”（字符型变量） CREATE SQL VIEW JS_VIEW AS SUM(Js.jbgz),AVG(Js.jbgz); 最高分 FROM sjk!cj; GROUP BY kcdh 查询每门课程的课程代号和名称以及最高分，并把查询结果保存到kc_maxcj.dbf表文件中 SELECT cj.kcdh,kc.kcm,max(cj.cj); as最高分 FROM sjk!cj,sjk!kc; WHERE cj.kcdh=kc.kcdh; GROUP B

12、Y cj.kcdh; INTO TABLE kc_maxcj,select 成绩.kcdh,kc1.kcm,; max(成绩.cj) as “最高; from jxgl1!成绩,jxgl1!kc1; where 成绩.kcdh=kc1.kcdh; group by 成绩.kcdh 4.子查询示例 显示JS表中已担任课程教师的姓名和系名。 SELECT js.xdh,js.xm; FROM sjk!js; WHERE js.gh in (SELECT rk.gh; FROM sjk!rk,5.基于视图的查询示例 已知在sjk库中包含一个视图viewKcXsCj，该视图包含三个字段：kc.kcm,

13、 cj.xh, xs.xm和cj.cj。现在从该视图中查询每门课程不及格学生的名单，查询结果包含视图中的所有字段。 以下命令建立基于viewKcXsCj视图的查询： SELECT * FROM sjk!viewKcXsCj ; WHERE viewKcXsCj.cj60 以下命令建立viewKcXsCj视图 CREATE SQL VIEW viewkcxscj AS; SELECT Kc.kcm, Cj.xh, Xs.xm, Cj.cj; FROM sjk!xs INNER JOIN sjk!cj; INNER JOIN sjk!kc ; ON Cj.kcdh = Kc.kcdh ON Xs.

14、xh=Cj.xh; ORDER BY Kc.kcm,6.组合查询示例 查询全校师生名单。要求如果是教师必须注明“教师”，结果中包含四个列：系名、类别、姓名和性别，并按系名排序。 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 Xim.ximing AS 系名, S

15、PACE(8) as 姓名, SUM(Gz.jbgz) as 工资 FROM sjk!xim INNER JOIN sjk!js; INNER JOIN sjk!gz ; ON Js.gh = Gz.gh ; ON Xim.xdh = Js.xdh; GROUP BY Xim.ximing; UNION; SELECT Xim.ximing AS 系名, Js.xm AS 姓名, Gz.jbgz AS 工资; FROM sjk!xim INNER JOIN sjk!js; INNER JOIN sjk!gz ; ON Js.gh = Gz.gh ; ON Xim.xdh = Js.xdh; U

16、NION; SELECT 总额+ SPACE(14) as 系名, SPACE(8) as 姓名,; SUM(Gz.jbgz) as 工资; FROM sjk!xim INNER JOIN sjk!js; INNER JOIN sjk!gz ; ON Js.gh = Gz.gh ; ON Xim.xdh = Js.xdh; ORDER BY 1,4.4 建立表之间的临时关系,4.4.1 临时关系 临时关系建立在具有一对多一对一的表(视图)之间 临时关系会使一张表(子表)的指针随另一张表(父表) 指针的移动而移动.（方便相关表数据的访问） 临时关系可通过数据工作期窗口在打开的表之间建立临时关系，也可在打开的表之间用SET RELATION 命令建立 临时关系和永久关系之间的联系区别 联系： 客观性：建立关系的两表在客观上确实有关系 默认性：永久关系常被作为默认的临时关系,4.4.2 建立临时关系,区别： 临时关系用于控制表之间的记录访问；永久关系用来存储表之间的参照完整性。 临时关系在表打开之后使用SET RELATION创建，随表关闭而解除；永久关系永久保存在数据库中。 临时关系可在任何表间建立；永久关系只存在于库表间 临时关系中一张表不能有两张主表，永久关系则不然。 建立临时关系的条件 两表客观上必