第五章查询和视图.

查询和视图第五章5.1查询的创建和使用5.2视图的创建和使用5.3SELECT–SQL语句综合例题结束放映?YES(是)NO(否)5.1查询的创建和使用查询的本质创建查询的方法创建查询的步骤用查询设计器创建查询用命令方式创建和运行查询交叉表查询视图的本质查询与视图的比较用视图设计器创建本地视图用命令方式创建本地视图视图的使用5.2视图的创建和使用5.3SELECT-SQL语句SQL语言概述SELECT-SQL命令SELECT-SQL应用举例“查询”(Query)，是指向一个数据库发出的检索信息的请求，它使用一些条件提取特定的记录。查询的运行结果是一个基于表和视图的动态的数据集合。创建查询必须基于确定的数据源。数据源可以是一个或多个相关的自由表、数据库表或视图。多个表一般是指多个相关的表。事实上，一个查询可以用一条SELECT-SQL语句来完成。一个查询可保存为一个扩展名为.QPR的文件即查询文件。查询文件中保存的是实现查询的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（程序文件）。用命令方式创建和运行查询文件(1)2、在打开的文本编辑窗口输入一条SELECT-SQL命令：SELECT*FROMXS3、关闭编辑窗口保存查询文件用命令方式创建的查询文件可以在查询设计器中被打开并修改。二、用命令方式运行查询文件在命令窗口输入命令：DOCX05.QPR注意：扩展名.qpr不可以省略，否则会显示错误信息“文件cx05.prg不存在”。用命令方式创建和运行查询文件(2)在学生的成绩表中，一个学生的各门课程的成绩分布在多行上，因此不方便查看每个学生各门课程的成绩。最好是一个学生的各门课程的成绩放在一行上，并在最后一列有一个总分，如下表所示。学号课程1课程2课程3课程4课程5课程6总分950201856591838777488950102767884659267462…………………………………………交叉表查询(1)可以通过“交叉表向导”建立交叉表查询。具体步骤是：1、在“新建”对话框中选择“查询”并点击“向导”按钮，打开“向导选取”对话框，选择“交叉表向导”。2、选取字段：选择JXSJ库中的CJ表，选择该表的所有字段。注意：字段只能在单独的表或视图中选取。3、布局设置：因为我们要让一个学生只占一行，每门课程成绩各占