第4章查询和视图.

第四章查询和视图4.1查询和视图概述4.2查询的创建和使用4.3SELECT–SQL语句4.4视图的创建和使用综合例题结束放映?YES(是)NO(否)“查询”(Query)，是指向一个数据库发出的检索信息的请求，它使用一些条件提取特定的记录。查询的运行结果是一个基于表和视图的动态的数据集合。创建查询必须基于确定的数据源。数据源可以是一个或多个相关的自由表、数据库表或视图。多个表一般是指多个相关的表。事实上，一个查询可以用一条SELECT-SQL语句来完成。一个查询可保存为一个扩展名为.QPR的文件即查询文件。查询文件中保存的是实现查询的SELECT-SQL命令，而非查询的结果。查询和视图设计器在本质上都是SELECT-SQL命令的可视化设计方法。查询的本质视图有两种类型：本地视图和远程视图。远程视图使用远程SQL语法从远程ODBC数据源表中选择信息，本地视图使用VFPSQL语法从视图或表中选择信息。可以将一个或多个远程视图添加到本地视图中，以便能在同一个视图中同时访问VFP数据和远程ODBC（开放式数据互连）数据源中的数据。ODBC是一种用于数据库服务器的标准协议，通过ODBC可以访问多种数据库中的数据，可以安装多种数据库的ODBC驱动程序，从而使VFP可以与该数据库相连，访问数据库中的数据。视图的本质一、查询和视图的共同之处不论是查询还是视图，其本质都是靠一条SELECT–SQL命令从指定的数据源中获取所需信息。二、查询和视图的不同之处1、使用查询，得到的是一组只读型的检索结果；而使用视图，可以从表中提取一组记录，改变这些记录的值，并把更新结果送回到源表中。2、视图可以作为查询和视图的数据源；但查询不可以作为数据源。3、查询以命令文件形式独立存储；而视图必须保存在数据库中。4、用MODIFYCOMMAND命令创建查询文件；用CREATESQLVIEW命令创建视图。5、视图是数据库的一部分，是一个虚拟的表。用DO命令运行查询文件；用USE命令查看视图。查询与视图的比较4.2查询的创建和使用创建查询的方法创建查询的步骤用查询设计器创建查询用命令方式创建和运行查询交叉表查询视图的本质用视图设计器创建本地视图用命令方式创建本地视图视图的使用4.4视图的创建和使用4.3SELECT-SQL语句SQL语言概述SELECT-SQL命令SELECT-SQL应用举例1.打开包含数据源的数据库2.用查询向导或查询设计器开始建立查询3.选择包含要搜索信息的表或视图4.定义查询结果的显示顺序及应满足的条件5.设置用于组织查询结果的排序或分组依据7.选择查询结果的输出去向，如表、报表等8.保存查询，运行查询创建查询的步骤创建查询的方法1.使用界面方法1）查询向导2）查询设计器打开查询设计器的方法：用createquery命令打开查询设计器窗口。选择“文件”菜单中的“新建”或常用工具栏中的“新建”按钮。在“项目管理器”窗口中的“数据”选项卡中选择“查询”，并选择“新建”按钮。2.命令方法用select-sql语句。用查询设计器创建查询创建基于一个表的查询创建多表查询【例1】查询教师表中基本工资在500元以上（含500元）的教师的工号、姓名和基本工资情况，按系名升序排序，同一个系的教师按基本工资的降序排序。1、打开查询设计器从“项目管理器”、“文件”菜单、工具栏中或用CREATEQUERY命令均可打开查询设计器。创建基于一个表的查询(1)2、选择数据源表打开查询设计器时，系统会出现“添加表或视图”对话框，提示从当前数据库或自由表中选择表或视图添加到查询设计器中。现当前数据库为Jxsj，从“添加表或视图”对话框中的“数据库中的表”下面的列表框中选择“js”表，单击“关闭”，便进入了查询设计器。创建基于一个表的查询(2)3、定义查询的输出内容进入“字段”选项卡，从左边的可用字段中选取js.ximing、js.gh、js.xm、js.jbgz，并逐一将其添加到右边的选定字段框中。“字段”选项卡中4个按钮的使用“添加”、“全部添加”、“移去”、“全部移去”在“函数和表达式”框中给输出字段加标题查看SELECT-SQL命令通过“查询”菜单中的“查看SQL”命令或工具栏上的“SQL”按钮，可以查看到SELECT-SQL命令。创建基于一个表的查询(3)4、设置查询的筛选条件进入“筛选”选项卡，定义筛选条件:js.jbgz=500查看设置“筛选条件”后的SELECT-SQL命令创建基于一个表的查询(4)5、设置查询结果的排序依据升序降序问题、排序的优先权问题查看设置“排序依据”后的SELECT-SQL命令创建基于一个表的查询(5)6、保存查询文件查询文件的扩展名为.qpr7、修改查询打开查询设计器查看、修改查询文件在命令方式下查看、修改查询文件MODIFYCOMMAND例1.QPR查询文件的内容与查询结果不一样8、运行查询文件在查询设计器或项目管理器中运行查询文件在命令窗口运行查询文件：DOcx01.qpr查询结果在浏览窗口显示创建基于一个表的查询(6)【例2】查询教师表中各系科教师的人数、工资总额和平均工资，并按平均工资降序排列。查询输出中必须包含系名、系人数、工资总额和平均工资四个列。通过本例介绍查询结果的分组，通过分组可以根据记录进行计算，用“分组依据”选项卡可以控制如何将记录分组，分组时还可使用COUNT()、SUM()、AVG()、MAX()、MIN()等函数。1、利用查询设计器新建一个查询，选择JS表作为数据源。创建基于一个表的查询(7)2、设置输出字段在“字段”选项卡中的“函数和表达式”文本框中，把下面的四个表达式分别输入并添加到“选定字段”列表框中：JS.ximingAS系科名称COUNT(*)AS人数SUM(js.jbgz)AS工资总额AVG(js.jbgz)AS平均工资查看SELECT-SQL命令运行结果创建基于一个表的查询(8)3、设置分组依据在“分组依据”选项卡中选择js.ximing作为分组字段，分组字段不一定要是已选定输出的字段，但分组字段不能是一个计算字段。查看设置“分组依据”后的SELECT-SQL命令创建基于一个表的查询(9)4、对分组结果作进一步的筛选在“分组依据”选项卡中按“满足条件”按钮，打开下面的对话框，将筛选条件“平均工资=300元”填入该对话框中：查看设置“分组筛选”后的SELECT-SQL命令创建基于一个表的查询(10)5、对查询结果的其他设置进入查询设计器的“杂项”选项卡可以做如下设置：无重复记录（DISTINCT）全部（ALL）前n个记录（TOPn）前n%个记录（nPERCENT）6、选择查询结果的输出去向可以将查询结果送到不同的地方，如果没有改变输出设置，则查询结果将显示在浏览窗口中。右击查询设计器空白处，在快捷菜单中选择“输出设置”命令后出现“查询去向”对话框。创建基于一个表的查询(11)7.“查询去向”对话框中各选择按钮的含义浏览：在浏览窗口显示查询结果临时表：在临时只读表中存储查询结果INTOCURSORTEMP01表：将查询结果作为表保存INTOTABLETAB01.DBF图形：使查询结果用于MicrosoftGraph屏幕：在VFP主窗口或当前窗口中显示查询结果TOSCREEN/TOPRINTER/TOFILEF01.TXT报表：将结果输出到报表文件(.FRX)标签：将结果输出至标签文件(.LBX)创建基于一个表的查询(12)“添加表或视图”对话框查看SELECT-SQL命令(1)筛选条件排序依据查询的“浏览窗口”“选定字段”列表框查看SELECT-SQL命令(2)分组依据从上面的运行结果看出，该查询实际上是把所有的记录当成一组。要想获得按系科分组的效果还必须设置分组依据。未设置“分组依据”前的运行结果可见在SELECT-SQL命令中，是使用HAVING子句实现对分组结果的进一步的筛选的。所以HAVING应该同GROUPBY一起使用。查看SELECT-SQL命令(3)分组筛选查询设计器的“杂项”选项卡“查询去向”对话框当需要获取存储在两个或更多表或视图中的信息时，只要把所有有关的表或视图添加到查询中，并用公共字段联接它们。多个表可以是库表、自由表或视图的任意组合。用查询设计器创建多表查询的方法与单个表的方法基本一样。不同之处在于：（1）表的个数是两个或两个以上；（2）多个表之间需建立联接条件。创建基于多个表的查询(1)【例3】基于XS表和CJ表查询各学生各门课程的成绩，要求输出字段为：XH、XM、KCDH、CJ。1、新建一个查询，并将XS表和CJ表作为数据源加入。2、按“内部联接”的方式联接公共字段xs.xh与cj.xh3、设置输出字段在“字段”选项卡中选取xs.xh、xs.xm、cj.kcdh和cj.cj4、查看SELECT-SQL命令注意INNERJOIN….ON….子句的用法创建基于多个表的查询(2)5、表与表之间的四种联接类型内部联接（INNERJOIN）一个只包含XS表与CJ表中完全匹的记录的集合左联接（LEFTJOIN）一个包含XS表所有记录以及CJ表中与之匹配记录的集合右联接（RIGHTJOIN）一个包含CJ表所有记录以及XS表中与之匹配记录的集合完全联接（FULLJOIN）一个包含XS表和CJ表中所有记录的集合创建基于多个表的查询(3)【例4】查询信息管理系中所有成绩总分在480分以上，并且各门课程的成绩不得低于70分的学生。要求在查询结果中包含学生的学号、姓名、总分、平均分和最低分，并按照总分从高到低排列。1.新建查询，并把“XS”表和“CJ”表添加到查询设计器中；2.设置联接条件为：Xs.xh=Cj.xh联接类型为：内部联接创建基于多个表的查询(4)3.在“字段”选项卡中选择以下输出字段：Xs.xhXs.xmSUM(Cj.cj)AS总分AVG(Cj.cj)AS平均成绩MIN(Cj.cj)AS最低分4.设置筛选条件：Xs.ximing=“信息管理系”5.设置结果按“SUM(Cj.cj)AS总分”降序排序6.设置“Xs.xh”作为分组字段，并设置结果满足的条件为：“总分=480AND最低分=70”7.查看SELECT-SQL命令创建基于多个表的查询(5)【例5】按系科统计各教师的任课课时数，并按系科和工号排序，要求输出系科名、教师工号、教师姓名及教师任课课时数。分析：该查询的结果涉及2个表的内容，即JS表中的ximing、gh、xm字段和KC表中的kss字段，但由于JS表与KC表之间没有关系，所以必须靠纽带表RK表起桥梁作用，即该查询是涉及三个表的查询。1、新建一个查询，将JS、RK、KC三个表作为数据源依次加入查询设计器，联接条件为js.gh=rk.gh和rk.kcdh=kc.kcdh，联接方式均为内部联接。创建基于多个表的查询(6)注意：涉及三个表或视图的查询，添加数据源的次序应为一、多、一，即必须将纽带表放在中间。2、选择输出字段从可选字段中选择js.ximing、js.gh、js.xm，生成“sum(kc.kss)AS任课时数”并依次添加到选定字段框中。3、设置分组依据选择js.ximing和js.gh作为分组依据。4、对分组结果做进一步的筛选设分组结果满足条件：js.xing=“信息管理系”5、运行查询并查看SELECT-SQL命令创建基于多个表的查询(7)“联接条件”对话框查看SELECT-SQL命令(4)例3例4查看SELECT-SQL命令(5)例5一、用命令方式创建查询文件查询文件中的内容实质上是一条SELECT-SQL命令，所以除了可以用查询设计器和查询向导创建查询，还可以用命令方式创建一个以SELECT-SQL命令为内容的文本文件作为查询文件，其扩展名为.qpr。具体方法如下：1、在命令窗口输入命令：MODIFYCOMMANDCX05.QPR注意：查询文件的扩展名.qpr不可以省略，否则默认文件的扩展名为.prg（程序文件）。用命令方式创建和