第5章查詢和視圖

第五章查詢和視圖本章知識點(diǎn)：查詢，視圖，select-sql語句查詢：從表中檢索或統(tǒng)計(jì)出所需的數(shù)據(jù)視圖：是一張?zhí)摫恚粌H具有查詢的功能，而且可以改變視圖中記錄的值，并把結(jié)果送回到源表中5.1查詢的創(chuàng)建和使用5.2視圖的創(chuàng)建和使用5.3select-sql語句查詢的創(chuàng)建和使用一、查詢的有關(guān)概念二、利用查詢設(shè)計(jì)器建立基于單表的查詢?nèi)?、?chuàng)建多表查詢四、交叉表查詢查詢的有關(guān)概念定義：是指向一個(gè)數(shù)據(jù)庫發(fā)出檢索信息的請求，使用一些條件提取特定的記錄。查詢的結(jié)果：基于表和視圖的動態(tài)的數(shù)據(jù)集合。查詢的數(shù)據(jù)源：自由表，庫表，視圖（可以是單張表，也可以是多張相關(guān)的表）查詢的本質(zhì)：生成一條select-sql語句，一個(gè)查詢保存為一個(gè)擴(kuò)展名為.qpr的文件。利用查詢設(shè)計(jì)器建立基于單表的查詢1、打開查詢設(shè)計(jì)器2、定義查詢輸出的內(nèi)容3、設(shè)置查詢的篩選條件4、設(shè)置查詢結(jié)果的排序依據(jù)5、設(shè)置查詢結(jié)果的分組依據(jù)6、對查詢結(jié)果的其它設(shè)置7、運(yùn)行查詢8、選擇查詢結(jié)果的輸出類型例5.1

查詢教師工資表中基本工資在500元以上（含500元）的教師的工號、基本工資情況，并按基本工資的降序排序。打開查詢設(shè)計(jì)器打開查詢設(shè)計(jì)器的方式：項(xiàng)目管理器/查詢/新建文件菜單/新建，常用工具欄/新建命令createquery查詢設(shè)計(jì)器界面上半部分：數(shù)據(jù)表顯示區(qū)，首先應(yīng)添加數(shù)據(jù)源下半部分：6個(gè)選項(xiàng)卡查詢設(shè)計(jì)器界面查詢設(shè)計(jì)器查詢所基于的表列表六個(gè)選項(xiàng)卡輸出字段定義查詢輸出的內(nèi)容“字段Fields”選項(xiàng)卡，添加、全部添加1)若輸出的列不是直接來源于表字段，可以定義關(guān)于表字段的函數(shù)或表達(dá)式2)選定的順序決定了結(jié)果中信息列的順序3)若給字段添加別名，可在“函數(shù)和表達(dá)式”文本框中輸入，如js.ghas工號4)工具按鈕：查詢設(shè)計(jì)器工具欄/SQL按鈕，可以看到系統(tǒng)自動生成的select-sql語句輸出字段設(shè)置查詢的篩選條件“篩選Filter”選項(xiàng)卡：利用條件表達(dá)式，定義記錄子集1)一行構(gòu)成一個(gè)邏輯關(guān)系

行之間的關(guān)系可用邏輯運(yùn)算符指定2)在“實(shí)例”文本框中輸入比較條件時(shí)應(yīng)注意p117設(shè)置查詢結(jié)果的排序依據(jù)“排序依據(jù)OrderBy”選項(xiàng)卡“排序條件OrderingCriteria”列表框中的順序決定了排序的優(yōu)先權(quán)設(shè)置查詢結(jié)果的分組依據(jù)“分組依據(jù)GroupBy”選項(xiàng)卡分組：將一組類似的記錄壓縮成一個(gè)結(jié)果記錄作用：可完成基于一組記錄的計(jì)算相關(guān)函數(shù)：COUNT(),SUM(),AVG(),MAX(),MIN()說明：1.用于分組的字段不一定是已選定輸出字段，但應(yīng)該是表中的原始字段，不能是一個(gè)計(jì)算字段2.若在分組的基礎(chǔ)上，還需對結(jié)果進(jìn)行篩選，單擊“滿足條件Having”按鈕對查詢結(jié)果的其它設(shè)置“雜項(xiàng)Miscellaneous”選項(xiàng)卡1.排除結(jié)果中所有重復(fù)的行(無重復(fù)記錄Noduplicates)2.設(shè)置結(jié)果的記錄范圍全部All前n個(gè)記錄Topn前n%個(gè)記錄nPercent運(yùn)行查詢“運(yùn)行”按鈕：!菜單命令：查詢/運(yùn)行查詢命令：do*.qpr選擇查詢結(jié)果的輸出類型若為選擇輸出類型，結(jié)果將輸出在瀏覽窗口中菜單命令：查詢/查詢?nèi)ハ?/p>

Query/Query

Destination創(chuàng)建多表查詢1）與單表查詢區(qū)別#表的數(shù)目是兩張或兩張以上#多張表之間需建立聯(lián)接條件2）聯(lián)接的概念：聯(lián)接是根據(jù)給定的聯(lián)接條件將兩個(gè)關(guān)系模式拼成一個(gè)新的關(guān)系，聯(lián)接是指查詢或視圖的一個(gè)數(shù)據(jù)庫操作，兩張表的聯(lián)接結(jié)果是一張新表。3）聯(lián)接的類型例5.2

查詢森環(huán)院中每位學(xué)生的成績情況，包括學(xué)號，姓名，總分，平均分，最低分，并按總分降序排序。(xim,xs,cj三張表)聯(lián)接的類型內(nèi)聯(lián)接(innerjoin)兩張表中僅滿足條件的記錄。左聯(lián)接(leftouterjoin)表中在聯(lián)接條件左邊的所有記錄和表中聯(lián)接條件右邊的且滿足聯(lián)接條件的記錄右聯(lián)接(rightouterjoin)表中在聯(lián)接條件右邊的所有記錄和表中聯(lián)接條件左邊的且滿足聯(lián)接條件的記錄完全聯(lián)接(fulljoin)表中不論是否滿足條件的所有記錄（笛卡爾積）例：R:S:則：RХ

S=abcd

aabbccddaabbfffhg

Avdcfffhg

Avdcccdd

ab

cd

aabbccddAvdcfffhg結(jié)論：如果R中有M個(gè)子段，X條記錄如果S中有N個(gè)子段，Y條記錄則：R和S的笛卡爾積記錄個(gè)數(shù)為X*Y條字段個(gè)數(shù)為M+N個(gè)笛卡爾積交叉表查詢交叉表查詢：以電子表格形式顯示數(shù)據(jù)的查詢，可以使用交叉表向?qū)Ы⒔徊姹聿樵?，只能基于單張表建立交叉表查詢步驟：字段選取-〉布局-〉加入總和信息-〉完成視圖的創(chuàng)建和使用一、基本概念二、創(chuàng)建本地視圖三、用視圖更新源表數(shù)據(jù)四、創(chuàng)建參數(shù)化視圖五、視圖的使用基本概念查詢：得到的只是一組只讀型的檢索結(jié)果視圖：不僅可以從表中提取一組記錄，還可以改變這些記錄的值，并把更新的結(jié)果送回源表，是數(shù)據(jù)庫的一個(gè)組成部分，是基于表的并且可更新的數(shù)據(jù)集合。視圖的特點(diǎn)：兼有表和查詢的特點(diǎn)視圖的類型：本地視圖-使用VFP-SQL語法從表或視圖中選擇信息；遠(yuǎn)程視圖-使用遠(yuǎn)程SQL語法從遠(yuǎn)程ODBC數(shù)據(jù)源表中選擇信息。ODBC：一種用于數(shù)據(jù)庫服務(wù)器的標(biāo)準(zhǔn)協(xié)議，通過ODBC可訪問多種數(shù)據(jù)庫中的數(shù)據(jù)，可安裝多種數(shù)據(jù)庫的ODBC驅(qū)動程序，使VFP可與該數(shù)據(jù)庫相連，訪問數(shù)據(jù)庫中的數(shù)據(jù)。創(chuàng)建本地視圖1)視圖設(shè)計(jì)器項(xiàng)目管理器/本地視圖/新建數(shù)據(jù)庫打開時(shí)，文件/新建，常用/新建數(shù)據(jù)庫打開時(shí)，Createview命令2)createsqlview命令格式：createsqlviewViewNameasselect-sql語句例，createsqlviewaaasselect*fromxs用視圖更新源表數(shù)據(jù)檢驗(yàn)視圖對表的更新：Updatettsetcj=0wherett.xh='950101'SelectttBrowseSelectcjbrowse更新方法：1)“視圖設(shè)計(jì)器”界面2)函數(shù)dbsetprop()更新步驟：1)使表可更新2)設(shè)置關(guān)鍵字段3)設(shè)置可更新字段4)設(shè)置‘發(fā)送SQL更新’創(chuàng)建參數(shù)化視圖目的：限定視圖的作用范圍，可避免每取一部分記錄就要單獨(dú)創(chuàng)建一個(gè)視圖的情況參數(shù)化視圖的創(chuàng)建方法：1)視圖設(shè)計(jì)器：篩選選項(xiàng)卡，cj.kcdh=?課程代號&&課程代號為參數(shù)名2)createsqlview命令中加一條where子句Createsqlviewaaasselectcj.xh,cj.kcdh,cj.cjfromcj;Where

cj.kcdh=?課程代號orderbycj.xh本質(zhì)：在視圖的select-sql語句中加一條where子句子句是根據(jù)所提供的參數(shù)值建立的僅下載那些符合where子句條件的記錄參數(shù)值的傳遞：1)可以在運(yùn)行視圖時(shí)傳遞2)也可以以編程方式傳遞，如：

課程代號=‘02’

usejxsj!aa

browse視圖的使用1、視圖的處理類似于表的處理：用use命令打開、關(guān)閉視圖在瀏覽窗口中顯示視圖記錄在數(shù)據(jù)工作期窗口中顯示打開的視圖在文本框、表單或報(bào)表中使用視圖作為數(shù)據(jù)源既可通過項(xiàng)目管理器，又可借助VFP語言來使用視圖2、視圖的打開與瀏覽3、創(chuàng)建視圖索引4、創(chuàng)建臨時(shí)關(guān)系5、用數(shù)據(jù)字典定制視圖6、集成視圖視圖的打開與瀏覽界面：項(xiàng)目管理器窗口，數(shù)據(jù)工作期窗口命令：Use、browse命令注：1)打開視圖時(shí)，若視圖基于本地表，則在其它工作區(qū)中同時(shí)打開基表，數(shù)據(jù)工作期窗口顯示視圖及其基表（自動打開的本地基表并不在關(guān)閉視圖時(shí)自動關(guān)閉）；若視圖基于遠(yuǎn)程表，則基表將不在工作區(qū)中打開，只在數(shù)據(jù)工作期窗口顯示遠(yuǎn)程視圖的名稱2)可在多個(gè)工作區(qū)中多次打開一張視圖的多個(gè)實(shí)例，默認(rèn)情況下，每次使用視圖時(shí)，都要去取一個(gè)新的數(shù)據(jù)集合，但可在use命令中使用norequery或again子句，不必重新查詢數(shù)據(jù)源而打開視圖的另一個(gè)實(shí)例（視圖為遠(yuǎn)程視圖是特別有用，無需等待從遠(yuǎn)程數(shù)據(jù)源中下載數(shù)據(jù)）創(chuàng)建視圖索引方法：indexon命令indexoneExpressiontagTagName[forlExpression][asending|desending][unique|candidate]說明：與表的索引不同，在視圖上創(chuàng)建的本地索引非永久保存，隨視圖的關(guān)閉而消失創(chuàng)建臨時(shí)關(guān)系作用：子表記錄指針跟隨主表記錄指針移動而移動創(chuàng)建臨時(shí)關(guān)系的命令：setrelationtoSelect0Usecjordertagxh&&設(shè)置子表的主控索引Select0UsexsSetrelationtoxhintocj解除臨時(shí)關(guān)系：

setrelationto/setrelationoff說明：可在視圖索引之間、視圖索引與表索引之間創(chuàng)建臨時(shí)關(guān)系若要對一張視圖于一張表創(chuàng)建臨時(shí)關(guān)系，可在關(guān)系中設(shè)置視圖為父表，表為子表用數(shù)據(jù)字典定制視圖可為視圖創(chuàng)建標(biāo)題、注釋、默認(rèn)值、字段及有效性規(guī)則及有效性信息，視圖的數(shù)據(jù)字典在功能上與庫表中的相應(yīng)部分非常相似以語言方式（函數(shù)dbsetprop()）創(chuàng)建或在視圖設(shè)計(jì)器中創(chuàng)建集成視圖多級視圖：一張基于視圖的視圖，或基于集成了本地表、本地視圖或遠(yuǎn)程視圖的視圖，稱為多級視圖。頂層視圖：集成了其它視圖的視圖。使用多級視圖時(shí)，頂層視圖所基于的視圖和各級視圖使用的基表將出現(xiàn)在數(shù)據(jù)工作期窗口中，遠(yuǎn)程表不會出現(xiàn)在數(shù)據(jù)工作期窗口中。select-sql語句一、SQL語言概述(structuredquerylanguage)是美國國家標(biāo)準(zhǔn)局ANSI確認(rèn)的關(guān)系數(shù)據(jù)庫語言的標(biāo)準(zhǔn)，一個(gè)SQL命令可以代替多個(gè)VFP命令，VFP支持的SQL命令有：Select-sql，alter

table-sql，create

table-sql，create

cursor-sql，delete-sql，insert-sql，update-sql二、select-sql命令簡介1）語法格式P1352）select-sql命令主要組成部分三、舉例說明舉例說明1、基于單張表的查詢2、基于多張表的查詢3、含有計(jì)算字段的查詢4、子查詢5、組合查詢select-sql命令主要組成部分Select……

From……指定數(shù)據(jù)源表…join…on…確定源表之間的聯(lián)接Where……篩選記錄或確定源表之間的聯(lián)接Groupby……定義記錄的分組Having……篩選結(jié)果記錄Orderby……指定結(jié)果的順序Into|to……指定輸出類型all|distinct……指定有無重復(fù)記錄TopnExpr|[percent]

……指定結(jié)果的范圍1)顯示js表中所有教師的工號和姓名2)顯示xs表中學(xué)號以“95”開頭的學(xué)生情況3)從高到低顯示cj表中代號為“02”的課程的學(xué)生的學(xué)號和成績selectjs.gh,js.xmfrom

jsSelect

*

from

jxsj!xs

where

substr(xh,1,2)='95'Select*fromjsxj!xswhere

like("95*",xh)Selectcj.xh,cj.cjfromjxsj!cj;wherecj.kcdh="02";orderbycjdesc1）顯示xs和zy兩張表中的三個(gè)字段xh,xm,zymc的內(nèi)容，并根據(jù)zydh字段聯(lián)接兩張表。Select

xs.xh,xs.xm,zy.zymc;fromjxsj!xs,jxsj!zy;where

xs.zydh=zy.zydh2）顯示cj表中有不及格課程成績的學(xué)生的學(xué)號和姓名，有多門課程不及格的同學(xué)只顯示一次。Select

distinct

cj.xh,xs.xm;fromjxsj!xs,jxsj!cj;wherecj.cj<60andcj.xh=xs.xh1）顯示cj表中課程代號以及最高分Select

cj.kcdh,max(cj.cj)as“最高分”;fromjxsj!cj;groupby

kcdh2）查詢每門課程的課程代號和名稱以及最高分，并把查詢結(jié)果保存到kc_maxcj.dbf中。Selectcj.kcdh,kc.kcm,max(cj.cj)as

'最高分';fromjxsj!cj,jxsj!kc;wherecj.kcdh=kc.kcdh;groupbycj.kcdh;intotablekc_maxcjbrowse顯示js表中已擔(dān)任課程教師的工號和系名。Selectjs.gh,js.xm;fromjxsj!js;wherejs.gh;

in