第4章查詢與視圖2

第4章查詢與視圖4.1查詢的創(chuàng)建和使用4.2視圖的創(chuàng)建和使用4.3SELECT-SQL語句4.4建立表之間的臨時關(guān)系4.1.1查詢概述查詢:從數(shù)據(jù)源（一張或多張表、視圖）中檢索或統(tǒng)計出符合條件的記錄數(shù)據(jù)。（結(jié)果是只讀的）數(shù)據(jù)源:是一張或多張相關(guān)表(自由表或庫表)或視圖。4.1查詢的創(chuàng)建和使用查詢條件：要那些字段？那些記錄？按什么要求收集？查詢實質(zhì)：執(zhí)行一條SELECT-SQL命令語句保存:.QPR文件，保存的是對應(yīng)SELECT-SQL語句(非查詢結(jié)果)。查詢和視圖設(shè)計器在本質(zhì)上都是SELECT-SQL命令的可視化（界面）設(shè)計方法。4.1.2使用查詢設(shè)計器建立查詢創(chuàng)建查詢的方法：使用查詢設(shè)計器CREATEQUERY[查詢文件名|？]“項目管理器”—”數(shù)據(jù)”—”查詢”—”新建”“文件”—”新建”或“常用工具欄”—“新建”打開“查詢設(shè)計器”建立查詢編寫SELECT-SQL語句(難)建查詢的步驟打開查詢設(shè)計器添加表或視圖（即該查詢所涉及的數(shù)據(jù)源）設(shè)置輸出字段（“字段”選項卡）—選定需要包含到查詢結(jié)果中的字段及需計算的表達(dá)式。設(shè)置聯(lián)接（“聯(lián)接”選項卡）—確定匹配多表的聯(lián)接表達(dá)式設(shè)置篩選條件（“篩選”選項卡）——確定查詢條件。排序（“排序依據(jù)”選項卡）——決定查詢輸出結(jié)果時，記錄或行的順序分組（“分組依據(jù)”選項卡）——分組的作用可將一組類似的記錄壓縮成一個記錄，主要用于計算。其他（“雜項”選項卡）—重復(fù)記錄處理、結(jié)果記錄范圍等設(shè)置輸出的方式瀏覽、臨時表、表、圖形、屏幕、報表、標(biāo)簽等查詢設(shè)計器舉例（單表查詢）例1：查詢教師表中基本工資在800元（含800元）以上的教師的工號、姓名和基本工資情況，并按系名升序排序，同一系的教師按基本工資的降序排序。1.打開查詢設(shè)計器(項目管理器|CREATEQUERY)添加表或視圖:添加表JS:FROM子句2.定義輸出內(nèi)容(“字段”選項卡):SELECT子句字段表達(dá)式（AS別名）3.設(shè)置篩選條件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.設(shè)置排序依據(jù)（ORDERBY子句）升序/降序優(yōu)先權(quán)查看SQL5.設(shè)置分組依據(jù)（GROUPBY子句）分組:將數(shù)據(jù)源中一組相關(guān)的記錄運算成一個查詢記錄輸出，在查詢運行中完成基于數(shù)據(jù)源的一組記錄的統(tǒng)計計算按系名分組:把所有系名相同的記錄壓縮成一個記錄幾個合計函數(shù)計數(shù)COUNT(*)求和SUM()求平均函數(shù)AVG()查看SQL(如何理解ORDERBY4)分組結(jié)果的篩選條件GROUPBY子句的同時使用HAVING平均工資>800查看SQL6.其他設(shè)置是否允許重復(fù)記錄結(jié)果的記錄范圍（需排序配合）7.運行查詢“運行”按鈕“查詢”菜單中的“運行查詢”DO命令（DOxijsgz.qpr）8.選擇輸出類型（INTO

子句）輸出去向類型說明瀏覽(無INTO)在“瀏覽”窗口中顯示查詢結(jié)果(默認(rèn))臨時表(INTOCURSOR)將查詢結(jié)果存貯在一張命名的臨時只讀表中，臨時表只存在于內(nèi)存中，當(dāng)臨時表被關(guān)閉時，表將從內(nèi)存中刪除表(INTOTABLE)使查詢結(jié)果保存為一個表文件，表文件將永久地保存在磁盤上圖形使查詢結(jié)果可用于MicrosoftGraph應(yīng)用程序屏幕(TOSCREEN)在VFP主窗口或當(dāng)前活動輸出窗口中顯示查詢結(jié)果報表(REPORTFORM)將輸出送到一個報表文件（.FRX）標(biāo)簽(LABELFORM)將輸出送到一個標(biāo)簽文件（.LBX）例：查詢成績表中各個學(xué)生的課程總分及平均成績，要求查詢輸出學(xué)號、課程代號、總分及平均成績，查詢結(jié)果按總分由高到底的排列。數(shù)據(jù)源：CJ.DBF;聯(lián)接：<無>選定字段：xh，kcdh，sum(CJ.cj）,AVE(CJ.cj）排序依據(jù)：總分分組依據(jù)：xh4.1.3創(chuàng)建多表查詢多表查詢與單表查詢的不同：查詢的數(shù)據(jù)源為兩個以上的表或視圖多張表之間需要建立聯(lián)接條件1.聯(lián)接的概念聯(lián)接:查詢或視圖的一個數(shù)據(jù)操作,通過公共字段聯(lián)接相關(guān)表或視圖聯(lián)接結(jié)果：形成一個新的單表（只讀、暫時、用以建立查詢)條件聯(lián)接:◎無條件聯(lián)接：多表交叉組合,形成的新紀(jì)錄數(shù)是多張表記錄數(shù)的乘積.◎條件聯(lián)接:即按聯(lián)接條件將多表紀(jì)錄生成一個新單表紀(jì)錄聯(lián)接類型P117內(nèi)聯(lián)接,左聯(lián)接,右聯(lián)接,完全聯(lián)接2.創(chuàng)建多表查詢打開查詢設(shè)計器向查詢中添加多張表--“添加表或視圖”對話框“查詢設(shè)計器”工具欄——添加表查詢菜單——添加表快捷菜單——添加表設(shè)置表之間的聯(lián)接條件存在永久性關(guān)系，則系統(tǒng)將以該永久性關(guān)系作為默認(rèn)的聯(lián)接條件沒有建立永久性關(guān)系，將兩表同名字段作為默認(rèn)的聯(lián)接條件。沒有同名字段，可以創(chuàng)建其他的聯(lián)接如果按“取消”按鈕，將不建立任何聯(lián)接自動默認(rèn)聯(lián)接類型為：內(nèi)部連接建立聯(lián)接條件的方法對于已存在于查詢中的表或視圖，可以拖動一張表中的字段到另一張表中的相應(yīng)字段上建立聯(lián)接條件“查詢設(shè)計器”工具欄/添加聯(lián)接“查詢設(shè)計器”/“聯(lián)接”選項卡/插入編輯聯(lián)接條件的方法雙擊連接線單擊“聯(lián)接”選項卡中的條件行前的按鈕“查詢設(shè)計器”/聯(lián)接選項卡刪除聯(lián)接條件的方法則先單擊連接線，按【Del】鍵即可將其刪除聯(lián)接選項卡/移去聯(lián)接不必基于完全匹配的字段(LIKE,==,>,<)聯(lián)接與篩選的聯(lián)系與區(qū)別（p118）3.多表查詢舉例(演示）例2（book402）查詢信息管理系中所有成績總分在480分以上，并且各門課程的成績不得低于70分的學(xué)生。要求在查詢結(jié)果中包含學(xué)生的學(xué)號、姓名、總分、平均分和最低分，并按照總分從高到低排列。選取表(XS,CJ)設(shè)置聯(lián)接(XS.xh=CJ.xh內(nèi)部聯(lián)接)選擇字段(XS.xh，XS.xm，SUM(CJ.cj)，AVE(CJ.cj)，MIN(cj.cj))篩選條件：XS.ximing=“信息管理系”排序依據(jù)：SUM(CJ.cj),降序分組依據(jù)：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使用向?qū)Ы⒔徊姹聿樵?.交叉表以電子表格形式建立的查詢。2.例(book118)學(xué)號課程1課程2課程3課程4課程51295010177885689904.2視圖的創(chuàng)建和使用4.2.1概述定義視圖：數(shù)據(jù)庫的一個組成部分，是基于源數(shù)據(jù)的可更新的動態(tài)數(shù)據(jù)集合，是一個虛表（兼有表和查詢的特點）基表（basetable)：視圖中的源數(shù)據(jù)表。分類本地視圖：使用VFPSQL語法從視圖或表中選擇信息遠(yuǎn)程視圖：使用遠(yuǎn)程SQL語法從遠(yuǎn)程ODBC數(shù)據(jù)源表（例：ACCESS，EXCEL）中選擇信息（P126）比較查詢：只讀，生成新文件，不是數(shù)據(jù)庫的組成部分，有多種輸出方式，不可以作為新的數(shù)據(jù)源視圖：讀寫，不生成新文件，是數(shù)據(jù)庫的組成部分，只有一種輸出方式，可更新源數(shù)據(jù)表，也可以作為新的數(shù)據(jù)源4.2.2創(chuàng)建本地視圖使用視圖設(shè)計器“項目管理器”-選中數(shù)據(jù)庫--選擇“本地視圖”-選“新建”視圖設(shè)計器與查詢設(shè)計器的不同：增加了一個“更新條件”選項卡使用CREATESQLVIEW命令條件：對應(yīng)數(shù)據(jù)庫打開格式：CREATESQLVIEW視圖名；ASSELECT-SQL語句例1：CREATESQLVIEWjs_viewASSELECT*FROMsjk!JS例2:CSQL=“SELECT*FROMsjk!JS”（字符型變量）CREATESQLVIEWJS_VIEWAS&CSQL(宏替換）1.使表可更新：“更新條件”選項卡在“表”下拉列表框中指定受設(shè)置影響的表在“字段名”列表中選定表中的字段，至少應(yīng)設(shè)置一個字段作為“主關(guān)鍵字”及“可更新字段”“發(fā)送SQL更新”復(fù)選框中可以設(shè)置是否允許對基表的更新，它是更新的主開關(guān)=DBSETPROP(“視圖名”,“View”,”SendUpdates”,.T.)2.設(shè)置關(guān)鍵字段VFP用關(guān)鍵字段來唯一標(biāo)識那些已在視圖中修改過的源表的更新記錄。設(shè)置“關(guān)鍵字段”,可用來檢驗更新沖突4.2.3使用視圖更新源表數(shù)據(jù)（1）指定可更新字段如果要設(shè)置給定表中部分或全部字段允許更新，必須在該表的所有字段中設(shè)置一個關(guān)鍵字段“關(guān)鍵”列：鑰匙形，將字段設(shè)置為關(guān)鍵字段命令設(shè)置：=DBSETPROP(“視圖名.字段名”,“Feild”,“KeyField”，.T.)“更新”列：筆形，設(shè)置一個字段為可更新的“全部更新”：在一張已有關(guān)鍵字段表中的除該關(guān)鍵字段外的所有字段均可設(shè)置為可更新命令設(shè)置：=DBSETPROP(“視圖名.字段名”,“Feild”,“Updatename”,“基表.字段名”)指定用Updatable屬性進行更新的字段=DBSETPROP(“視圖名.字段名”,“Feild”,“Updatable”,”T”)（2）控制如何檢查更新沖突若不同用戶同時更新基表數(shù)據(jù)，當(dāng)發(fā)生時如何處理?使用“SQLWHERE”單選按鈕組設(shè)置更新檢查(p128)參數(shù)化視圖用來限定視圖的作用范圍，避免每取一部分記錄就需要單獨創(chuàng)建一個視圖的情況。實質(zhì)是在視圖的SELECTSQL語句中加一條WHERE子句，僅下載符合WHERE子句條件的記錄，其子句是根據(jù)所提供的視圖參數(shù)值建立的，參數(shù)值可以在運行時傳遞，也可以編程方式傳遞。視圖參數(shù)：可以是一個變量前面加問號或是個表達(dá)式。執(zhí)行視圖命令時，DBMS無法計算值時，會通過對話讓用戶輸入。（？年齡/？請輸入學(xué)號）若要創(chuàng)建參數(shù)化視圖，可以使用“視圖設(shè)計器”，或使用CREATESQLVIEW命令。4.2.4創(chuàng)建參數(shù)化視圖例(book129)基于CJ表創(chuàng)建一個通用視圖，根據(jù)提供的課程代號下載該課程的成績記錄。1.訪問視圖（處理視圖類似于處理表）(1)視圖的打開與瀏覽OPENDATABASEsjkUSEJS_VIEWBROWSE(2)打開視圖的多個實例(3)顯示視圖結(jié)構(gòu)(4)關(guān)閉視圖的基表2.視圖的修改、重命名與刪除MODIFY/DELETE/RENAMEVIEW<視圖>DBSETPROP（）3.創(chuàng)建視圖索引（非永久索引，隨視圖關(guān)閉而消失）INDEXON命令4.創(chuàng)建視圖的臨時關(guān)系SETRELATION(視圖作為主表較好)數(shù)據(jù)環(huán)境中，視圖不能被作為子表5.用數(shù)據(jù)字典定制視圖“字段”選項卡中的“屬性”按鈕6.集成視圖（多級視圖、頂層視圖）SQL(StructuredQueryLanguage)結(jié)構(gòu)化查詢語言，關(guān)系數(shù)據(jù)庫語言的標(biāo)準(zhǔn)；使用Rushmore技術(shù)來優(yōu)化性能,并且一個SQL命令可代替多個VFP命令。VFP支持的SQL命令SELECT-SQLDELETE-SQLALTERTABLE-SQLINSERT-SQLCREATECURSOR-SQLUPDATE-SQLCREATETABLE-SQL4.3SELECT-SQL語句SQL語句創(chuàng)建查詢使用的是Select語句Select[All|Distinct]<字段名1>|<函數(shù)>[,<字段名2>……]From<表或查詢>[[Left][Right]Join<表或查詢>On<條件表達(dá)式>][Where<條件表達(dá)式>][OrderBy<排序選項>[Asc][Desc]]GroupBy<分組字段名>[Having<條件表達(dá)式>]]SELECT-SQL命令舉例SELECTJs.ximing,COUNT(Js.gh),;SUM(Js.jbgz),AVG(Js.jbgz);&&“字段”選項卡FROMjxsj!js;&&數(shù)據(jù)源WHEREjs.ximing<>"外語系";ANDjs.ximing<>“中文系”;&&“篩選”選項卡GROUPBYjs.ximing;&&“分組”選項卡ORDERBY4DESC&&“排序依據(jù)”選項卡ALL/Distinct——指定查詢結(jié)果中有無重復(fù)的行TOPn[Percent]——限定查詢結(jié)果顯示的記錄數(shù)INTO及TO子句——指定在何處輸出查詢結(jié)果（瀏覽窗口、臨時表、數(shù)據(jù)表、屏幕、打印機等)設(shè)置篩選條件（WHERE子句）條件的類型條件類型說明=指定字段值等于右邊的實例Like指定字段與實例文本相匹配。如，“Js.ghlikeA”與來自A0001和A0002……等的記錄相匹配==指定字段與實例文本必須逐字符完全匹配>(>=)指定字段大于（大于或等于）實例文本的值<(<=)指定字段小于（小于或等于）實例文本的值IsNull指定字段包含null值Between指定字段大于等于示例文本中的低值并小于等于示例文本中的高值。實例文本中的這兩個值用逗號隔開。如，“JS.CSRQBetween{01/01/1950},{01/01/1960}”與出生日期在1950年1月1日至1960年1月1日的教師記錄相匹配INlist指定字段必須與實例文本中逗號分隔的幾個樣本中的一個相匹配計數(shù)：COUNT統(tǒng)計個數(shù)COUNT([DISTINCT|ALL]*)

統(tǒng)計元組個數(shù)COUNT([DISTINCT|ALL]列名)[for條件][while條件][to內(nèi)存變量]

統(tǒng)計一列中值的個數(shù)注意:Count(*)與Count(列名)的差別總和：SUM計算一列值(數(shù)值型)的總和SUM([DISTINCT|ALL]列名)平均值：AVG計算一列值(數(shù)值型)的平均值A(chǔ)VG([DISTINCT|ALL]列名)

最大值：MAX求一列值中的最大值MAX([DISTINCT|ALL]列名)最小值：MIN求一列值中的最小值MIN([DISTINCT|ALL]列名)total命令計算當(dāng)前選定表中數(shù)值字段的總和。對按關(guān)鍵字排序或索引過的當(dāng)前庫文件進行分部匯總后

生成一個新的結(jié)構(gòu)相同的庫文件。

TOTALTOTableNameONFieldName

[FIELDSFieldNameList]

[Scope]

[FORlExpression1]

[WHILElExpression2]

[NOOPTIMIZE]

查看一個查詢的SELECT-SQL語句1）“查詢設(shè)計器”窗口—查看SQL（只讀）2）命令窗口—TYPE命令—主窗口顯示如：TYPE統(tǒng)計.QPR3）命令窗口—MODIFYCOMMAND或者MODIFYFILE命令編輯窗口顯示（SQL內(nèi)容可編輯更改）如：MODIFYCOMMAND統(tǒng)計.QPR如：MODIFYFILE統(tǒng)計.QPR直接編寫SELECT-SQL語句創(chuàng)建查詢在命令窗口或程序編輯窗口輸入SELECT-SQL語句1.基于單個表的查詢示例顯示JS表中所有教師的工號和姓名（從一個表中選取兩個字段）SELECTjs.gh,js.xmFROMsjk!js顯示XS表中學(xué)號以“99”開頭的學(xué)生情況。SELECT*FROMsjk!xsWHERElike("99*",xh)SELECT*FROMsjk!xsWHEREXS.XHLIKE"99%"從高到低顯示CJ表中代號為“01”課程的學(xué)生的學(xué)號和成績SELECTcj.xh,cj.cjFROMsjk!cjWHEREcj.kcdh="01“ORDERBYcj.cjDESC2.基于多個表的查詢示例顯示XS和ZY兩個表中三個字段xh,xm,zydh的內(nèi)容，并根據(jù)zydh字段聯(lián)接兩個表。其中用到了兩個表的本地別名。SELECTxs.xh,xs.xm,zy.zymcFROMsjk!xs,sjk!zyWHERExs.zydh=zy.zydh顯示CJ表中有不及格成績的學(xué)生的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查詢每門課程的課程代號和名稱以及最高分，并把查詢結(jié)果保存到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表中已擔(dān)任課程教師的姓名和系名。SELECTjs.xdh,js.xm;FROMsjk!js;WHEREjs.ghin(SELECTrk.gh;FROMsjk!rk)5.基于視圖的查詢示例已知在sjk庫中包含一個視圖viewKcXsCj，該視圖包含三個字段：kc.kcm,cj.xh,xs.xm和cj.cj。現(xiàn)在從該視圖中查詢每門課程不及格學(xué)生的名單，查詢結(jié)果包含視圖中的所有字段。以下命令建立基于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.組合查詢示例查詢?nèi)熒麊?。要求如果是教師必須注明“教師”，結(jié)果中包含四個列：系名、類別、姓名和性別，并按系名排序。SELECTjs.ximing,"教師"AS類別,js.xm,js.xbFROMjxsj!js;UNION;SELECTxs.ximing,"學(xué)生"AS類別,xs.xm,xs.xbFROMjxsj!xs;ORDERBY1查詢各系科教師工資總額、各系科每個教師的工資以及全校所有教師工資總額。要求結(jié)果中包含三個列：系名、姓名和工資，并按系名排序。SELECTXim.ximingAS系名,SPACE(8)as姓名,SUM(Gz.jbgz)as工資FROMsjk!ximINNERJOINsjk!js;INNERJOINsjk!gz;ONJs.gh=Gz.gh;ONXim.xdh=Js.xdh;GROUPBYXim.ximing;