第4章查询与视图2

第4章查询与视图4.1查询的创建和使用4.2视图的创建和使用4.3SELECT-SQL语句4.4建立表之间的临时关系4.1.1查询概述查询:从数据源（一张或多张表、视图）中检索或统计出符合条件的记录数据。（结果是只读的）数据源:是一张或多张相关表(自由表或库表)或视图。4.1查询的创建和使用查询条件：要那些字段？那些记录？按什么要求收集？查询实质：执行一条SELECT-SQL命令语句保存:.QPR文件，保存的是对应SELECT-SQL语句(非查询结果)。查询和视图设计器在本质上都是SELECT-SQL命令的可视化（界面）设计方法。4.1.2使用查询设计器建立查询创建查询的方法：使用查询设计器CREATEQUERY[查询文件名|？]“项目管理器”—”数据”—”查询”—”新建”“文件”—”新建”或“常用工具栏”—“新建”打开“查询设计器”建立查询编写SELECT-SQL语句(难)建查询的步骤打开查询设计器添加表或视图（即该查询所涉及的数据源）设置输出字段（“字段”选项卡）—选定需要包含到查询结果中的字段及需计算的表达式。设置联接（“联接”选项卡）—确定匹配多表的联接表达式设置筛选条件（“筛选”选项卡）——确定查询条件。排序（“排序依据”选项卡）——决定查询输出结果时，记录或行的顺序分组（“分组依据”选项卡）——分组的作用可将一组类似的记录压缩成一个记录，主要用于计算。其他（“杂项”选项卡）—重复记录处理、结果记录范围等设置输出的方式浏览、临时表、表、图形、屏幕、报表、标签等查询设计器举例（单表查询）例1：查询教师表中基本工资在800元（含800元）以上的教师的工号、姓名和基本工资情况，并按系名升序排序，同一系的教师按基本工资的降序排序。1.打开查询设计器(项目管理器|CREATEQUERY)添加表或视图:添加表JS:FROM子句2.定义输出内容(“字段”选项卡):SELECT子句字段表达式（AS别名）3.设置筛选条件P113（WHERE子句）js.jbgz>=800条件类型说明=指定字段值等于右边的实例Like指定字段与实例文本相匹配。如，“Js.ghlikeA”与来自A0001和A0002……等的记录相匹配==指定字段与实例文本必须逐字符完全匹配>(>=)指定字段大于（大于或等于）实例文本的值<(<=)指定字段小于（小于或等于）实例文本的值IsNull指定字段包含null值Between指定字段大于等于示例文本中的低值并小于等于示例文本中的高值。实例文本中的这两个值用逗号隔开。如：“Between(JS.CSRQ,{01/01/1950},{01/01/1960})”与出生日期在1950年1月1日至1960年1月1日的教师记录相匹配IN指定字段必须与实例文本中逗号分隔的几个样本中的一个相匹配4.设置排序依据（ORDERBY子句）升序/降序优先权查看SQL5.设置分组依据（GROUPBY子句）分组:将数据源中一组相关的记录运算成一个查询记录输出，在查询运行中完成基于数据源的一组记录的统计计算按系名分组:把所有系名相同的记录压缩成一个记录几个合计函数计数COUNT(*)求和SUM()求平均函数AVG()查看SQL(如何理解ORDERBY4)分组结果的筛选条件GROUPBY子句的同时使用HAVING平均工资>800查看SQL6.其他设置是否允许重复记录结果的记录范围（需排序配合）7.运行查询“运行”按钮“查询”菜单中的“运行查询”DO命令（DOxijsgz.qpr）8.选择输出类型（INTO

子句）输出去向类型说明浏览(无INTO)在“浏览”窗口中显示查询结果(默认)临时表(INTOCURSOR)将查询结果存贮在一张命名的临时只读表中，临时表只存在于内存中，当临时表被关闭时，表将从内存中删除表(INTOTABLE)使查询结果保存为一个表文件，表文件将永久地保存在磁盘上图形使查询结果可用于MicrosoftGraph应用程序屏幕(TOSCREEN)在VFP主窗口或当前活动输出窗口中显示查询结果报表(REPORTFORM)将输出送到一个报表文件（.FRX）标签(LABELFORM)将输出送到一个标签文件（.LBX）例：查询成绩表中各个学生的课程总分及平均成绩，要求查询输出学号、课程代号、总分及平均成绩，查询结果按总分由高到底的排列。数据源：CJ.DBF;联接：<无>选定字段：xh，kcdh，sum(CJ.cj）,AVE(CJ.cj）排序依据：总分分组依据：xh4.1.3创建多表查询多表查询与单表查询的不同：查询的数据源为两个以上的表或视图多张表之间需要建立联接条件1.联接的概念联接:查询或视图的一个数据操作,通过公共字段联接相关表或视图联接结果：形成一个新的单表（只读、暂时、用以建立查询)条件联接:◎无条件联接：多表交叉组合,形成的新纪录数是多张表记录数的乘积.◎条件联接:即按联接条件将多表纪录生成一个新单表纪录联接类型P117内联接,左联接,右联接,完全联接2.创建多表查询打开查询设计器向查询中添加多张表--“添加表或视图”对话框“查询设计器”工具栏——添加表查询菜单——添加表快捷菜单——添加表设置表之间的联接条件存在永久性关系，则系统将以该永久性关系作为默认的联接条件没有建立永久性关系，将两表同名字段作为默认的联接条件。没有同名字段，可以创建其他的联接如果按“取消”按钮，将不建立任何联接自动默认联接类型为：内部连接建立联接条件的方法对于已存在于查询中的表或视图，可以拖动一张表中的字段到另一张表中的相应字段上建立联接条件“查询设计器”工具栏/添加联接“查询设计器”/“联接”选项卡/插入编辑联接条件的方法双击连接线单击“联接”选项卡中的条件行前的按钮“查询设计器”/联接选项卡删除联接条件的方法则先单击连接线，按【Del】键即可将其删除联接选项卡/移去联接不必基于完全匹配的字段(LIKE,==,>,<)联接与筛选的联系与区别（p118）3.多表查询举例(演示）例2（book402）查询信息管理系中所有成绩总分在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；满足条件:总分>=480AND最低分>=70保存查询xs_cj.qprSELECTXs.xh,Xs.xm,SUM(cj.cj)AS总分,;AVG(cj.cj)AS平均成绩,;MIN(cj.cj)AS最低分;FROMjxsj!xsINNERJOINjxsj!cjONXs.xh=cj.xh;WHEREXs.ximing="信息管理系";GROUPBYXs.xh;HAVING总分=>480AND最低分=>70;ORDERBY3DESC4.1.4使用向导建立交叉表查询1.交叉表以电子表格形式建立的查询。2.例(book118)学号课程1课程2课程3课程4课程51295010177885689904.2视图的创建和使用4.2.1概述定义视图：数据库的一个组成部分，是基于源数据的可更新的动态数据集合，是一个虚表（兼有表和查询的特点）基表（basetable)：视图中的源数据表。分类本地视图：使用VFPSQL语法从视图或表中选择信息远程视图：使用远程SQL语法从远程ODBC数据源表（例：ACCESS，EXCEL）中选择信息（P126）比较查询：只读，生成新文件，不是数据库的组成部分，有多种输出方式，不可以作为新的数据源视图：读写，不生成新文件，是数据库的组成部分，只有一种输出方式，可更新源数据表，也可以作为新的数据源4.2.2创建本地视图使用视图设计器“项目管理器”-选中数据库--选择“本地视图”-选“新建”视图设计器与查询设计器的不同：增加了一个“更新条件”选项卡使用CREATESQLVIEW命令条件：对应数据库打开格式：CREATESQLVIEW视图名；ASSELECT-SQL语句例1：CREATESQLVIEWjs_viewASSELECT*FROMsjk!JS例2:CSQL=“SELECT*FROMsjk!JS”（字符型变量）CREATESQLVIEWJS_VIEWAS&CSQL(宏替换）1.使表可更新：“更新条件”选项卡在“表”下拉列表框中指定受设置影响的表在“字段名”列表中选定表中的字段，至少应设置一个字段作为“主关键字”及“可更新字段”“发送SQL更新”复选框中可以设置是否允许对基表的更新，它是更新的主开关=DBSETPROP(“视图名”,“View”,”SendUpdates”,.T.)2.设置关键字段VFP用关键字段来唯一标识那些已在视图中修改过的源表的更新记录。设置“关键字段”,可用来检验更新冲突4.2.3使用视图更新源表数据（1）指定可更新字段如果要设置给定表中部分或全部字段允许更新，必须在该表的所有字段中设置一个关键字段“关键”列：钥匙形，将字段设置为关键字段命令设置：=DBSETPROP(“视图名.字段名”,“Feild”,“KeyField”，.T.)“更新”列：笔形，设置一个字段为可更新的“全部更新”：在一张已有关键字段表中的除该关键字段外的所有字段均可设置为可更新命令设置：=DBSETPROP(“视图名.字段名”,“Feild”,“Updatename”,“基表.字段名”)指定用Updatable属性进行更新的字段=DBSETPROP(“视图名.字段名”,“Feild”,“Updatable”,”T”)（2）控制如何检查更新冲突若不同用户同时更新基表数据，当发生时如何处理?使用“SQLWHERE”单选按钮组设置更新检查(p128)参数化视图用来限定视图的作用范围，避免每取一部分记录就需要单独创建一个视图的情况。实质是在视图的SELECTSQL语句中加一条WHERE子句，仅下载符合WHERE子句条件的记录，其子句是根据所提供的视图参数值建立的，参数值可以在运行时传递，也可以编程方式传递。视图参数：可以是一个变量前面加问号或是个表达式。执行视图命令时，DBMS无法计算值时，会通过对话让用户输入。（？年龄/？请输入学号）若要创建参数化视图，可以使用“视图设计器”，或使用CREATESQLVIEW命令。4.2.4创建参数化视图例(book129)基于CJ表创建一个通用视图，根据提供的课程代号下载该课程的成绩记录。1.访问视图（处理视图类似于处理表）(1)视图的打开与浏览OPENDATABASEsjkUSEJS_VIEWBROWSE(2)打开视图的多个实例(3)显示视图结构(4)关闭视图的基表2.视图的修改、重命名与删除MODIFY/DELETE/RENAMEVIEW<视图>DBSETPROP（）3.创建视图索引（非永久索引，随视图关闭而消失）INDEXON命令4.创建视图的临时关系SETRELATION(视图作为主表较好)数据环境中，视图不能被作为子表5.用数据字典定制视图“字段”选项卡中的“属性”按钮6.集成视图（多级视图、顶层视图）SQL(StructuredQueryLanguage)结构化查询语言，关系数据库语言的标准；使用Rushmore技术来优化性能,并且一个SQL命令可代替多个VFP命令。VFP支持的SQL命令SELECT-SQLDELETE-SQLALTERTABLE-SQLINSERT-SQLCREATECURSOR-SQLUPDATE-SQLCREATETABLE-SQL4.3SELECT-SQL语句SQL语句创建查询使用的是Select语句Select[All|Distinct]<字段名1>|<函数>[,<字段名2>……]From<表或查询>[[Left][Right]Join<表或查询>On<条件表达式>][Where<条件表达式>][OrderBy<排序选项>[Asc][Desc]]GroupBy<分组字段名>[Having<条件表达式>]]SELECT-SQL命令举例SELECTJs.ximing,COUNT(Js.gh),;SUM(Js.jbgz),AVG(Js.jbgz);&&“字段”选项卡FROMjxsj!js;&&数据源WHEREjs.ximing<>"外语系";ANDjs.ximing<>“中文系”;&&“筛选”选项卡GROUPBYjs.ximing;&&“分组”选项卡ORDERBY4DESC&&“排序依据”选项卡ALL/Distinct——指定查询结果中有无重复的行TOPn[Percent]——限定查询结果显示的记录数INTO及TO子句——指定在何处输出查询结果（浏览窗口、临时表、数据表、屏幕、打印机等)设置筛选条件（WHERE子句）条件的类型条件类型说明=指定字段值等于右边的实例Like指定字段与实例文本相匹配。如，“Js.ghlikeA”与来自A0001和A0002……等的记录相匹配==指定字段与实例文本必须逐字符完全匹配>(>=)指定字段大于（大于或等于）实例文本的值<(<=)指定字段小于（小于或等于）实例文本的值IsNull指定字段包含null值Between指定字段大于等于示例文本中的低值并小于等于示例文本中的高值。实例文本中的这两个值用逗号隔开。如，“JS.CSRQBetween{01/01/1950},{01/01/1960}”与出生日期在1950年1月1日至1960年1月1日的教师记录相匹配INlist指定字段必须与实例文本中逗号分隔的几个样本中的一个相匹配计数：COUNT统计个数COUNT([DISTINCT|ALL]*)

统计元组个数COUNT([DISTINCT|ALL]列名)[for条件][while条件][to内存变量]

统计一列中值的个数注意:Count(*)与Count(列名)的差别总和：SUM计算一列值(数值型)的总和SUM([DISTINCT|ALL]列名)平均值：AVG计算一列值(数值型)的平均值AVG([DISTINCT|ALL]列名)

最大值：MAX求一列值中的最大值MAX([DISTINCT|ALL]列名)最小值：MIN求一列值中的最小值MIN([DISTINCT|ALL]列名)total命令计算当前选定表中数值字段的总和。对按关键字排序或索引过的当前库文件进行分部汇总后

生成一个新的结构相同的库文件。

TOTALTOTableNameONFieldName

[FIELDSFieldNameList]

[Scope]

[FORlExpression1]

[WHILElExpression2]

[NOOPTIMIZE]

查看一个查询的SELECT-SQL语句1）“查询设计器”窗口—查看SQL（只读）2）命令窗口—TYPE命令—主窗口显示如：TYPE统计.QPR3）命令窗口—MODIFYCOMMAND或者MODIFYFILE命令编辑窗口显示（SQL内容可编辑更改）如：MODIFYCOMMAND统计.QPR如：MODIFYFILE统计.QPR直接编写SELECT-SQL语句创建查询在命令窗口或程序编辑窗口输入SELECT-SQL语句1.基于单个表的查询示例显示JS表中所有教师的工号和姓名（从一个表中选取两个字段）SELECTjs.gh,js.xmFROMsjk!js显示XS表中学号以“99”开头的学生情况。SELECT*FROMsjk!xsWHERElike("99*",xh)SELECT*FROMsjk!xsWHEREXS.XHLIKE"99%"从高到低显示CJ表中代号为“01”课程的学生的学号和成绩SELECTcj.xh,cj.cjFROMsjk!cjWHEREcj.kcdh="01“ORDERBYcj.cjDESC2.基于多个表的查询示例显示XS和ZY两个表中三个字段xh,xm,zydh的内容，并根据zydh字段联接两个表。其中用到了两个表的本地别名。SELECTxs.xh,xs.xm,zy.zymcFROMsjk!xs,sjk!zyWHERExs.zydh=zy.zydh显示CJ表中有不及格成绩的学生的xh和xm,多门不及格者显示一次。SELECTDISTINCTcj.xh,xs.xmFROMsjk!cj,sjk!xsWHEREcj.cj<60ANDcj.xh=xs.xh查询仅显示开始的6条记录SELECTDISTINCTTop6cj.xh,xs.xmFROMsjk!cj,sjk!xsWHEREcj.cj<60ANDcj.xh=xs.xhORDERBY13.含有计算字段的查询显示CJ表中的课程代号及最高分SELECTcj.kcdh,MAX(cj.cj)as;"最高分"FROMsjk!cj;GROUPBYkcdh查询每门课程的课程代号和名称以及最高分，并把查询结果保存到kc_maxcj.dbf表文件中SELECTcj.kcdh,kc.kcm,max(cj.cj);as"最高分"FROMsjk!cj,sjk!kc;WHEREcj.kcdh=kc.kcdh;GROUPBYcj.kcdh;INTOTABLEkc_maxcjselect成绩.kcdh,kc1.kcm,;max(成绩.cj)as“最高";fromjxgl1!成绩,jxgl1!kc1;where成绩.kcdh=kc1.kcdh;groupby成绩.kcdh4.子查询示例显示JS表中已担任课程教师的姓名和系名。SELECTjs.xdh,js.xm;FROMsjk!js;WHEREjs.ghin(SELECTrk.gh;FROMsjk!rk)5.基于视图的查询示例已知在sjk库中包含一个视图viewKcXsCj，该视图包含三个字段：kc.kcm,cj.xh,xs.xm和cj.cj。现在从该视图中查询每门课程不及格学生的名单，查询结果包含视图中的所有字段。以下命令建立基于viewKcXsCj视图的查询：SELECT*FROMsjk!viewKcXsCj;WHEREviewKcXsCj.cj<60以下命令建立viewKcXsCj视图CREATESQLVIEWviewkcxscjAS;SELECTKc.kcm,Cj.xh,Xs.xm,Cj.cj;FROMsjk!xsINNERJOINsjk!cj;INNERJOINsjk!kc;ONCj.kcdh=Kc.kcdhONXs.xh=Cj.xh;ORDERBYKc.kcm6.组合查询示例查询全校师生名单。要求如果是教师必须注明“教师”，结果中包含四个列：系名、类别、姓名和性别，并按系名排序。SELECTjs.ximing,"教师"AS类别,js.xm,js.xbFROMjxsj!js;UNION;SELECTxs.ximing,"学生"AS类别,xs.xm,xs.xbFROMjxsj!xs;ORDERBY1查询各系科教师工资总额、各系科每个教师的工资以及全校所有教师工资总额。要求结果中包含三个列：系名、姓名和工资，并按系名排序。SELECTXim.ximingAS系名,SPACE(8)as姓名,SUM(Gz.jbgz)as工资FROMsjk!ximINNERJOINsjk!js;INNERJOINsjk!gz;ONJs.gh=Gz.gh;ONXim.xdh=Js.xdh;GROUPBYXim.ximing;