查询和视图课件

查詢和視圖

利用查詢和視圖，可以對多張表中的數據同時進行操作只讀方式查詢:從表中檢索或統計出符合條件的數據視圖:從表中提取一組記錄，改變這些記錄的值，並把更新結果送回源表中可以更新查詢和視圖查詢的本質查詢的創建和使用（1）查詢:向資料庫發出檢索資訊的請求,它使用一些條件提取特定的記錄。其運行結果是一個基於表或視圖的動態的數據集合。（2）數據源是一張或多張相關

表或視圖（3）查詢可以用SELECT-SQL語句完成。（4）查詢檔的擴展名：.qpr。保存的是實現查詢的SELECT-SQL語句，而非查詢的結果“查詢嚮導”“查詢設計器”建立查詢查詢設計器中各選項的含義查詢的創建和使用查詢設計器窗口查詢名稱聯接選項卡字段選項卡雜項篩選選項卡排序依據分組依據常用工具欄Page115查詢的創建和使用例5.1：查詢工資表中基本工資在500元（含500元）以上的教師的工號、姓名、系名和基本工資情況，並使教師按系名昇冪排序，同一系的教師按基本工資的降序排序。建立單表查詢Page115查詢的創建和使用示例分析Page115單表查詢字段工號、姓名、系名和基本工資篩選JBGZ>=500排序系名昇冪，同一系的教師按基本工資的降序排序。示例步驟打開查詢設計器添加表gzb.DBF定義查詢輸出內容設置篩選條件設置排序依據gzb.xim

AS系名gzb.jbgz>=500ORDERBYPage115單表查詢字段：gh、xm、xim、jbgz運算式（AS別名）儘量用運算式生成器保存“常用”工具欄＋“保存”設置篩選條件的注意事項字段名不能使用“備註”或“通用”字段作為選定條件邏輯值必須用.T..F.(不能TF)

日期常量必須用{^2002-04-17}查詢的創建和使用Page117右擊/查看SQL設置分組依據（GROUPBY子句）查詢的創建和使用Page119將一組具有重複值的記錄壓縮成一個結果記錄，這樣就可以完成基於一組記錄的計算或統計。例5.2：查詢工資表中各系教師的人數、工資總額、平均工資，並按平均工資降序排列。查詢輸出中必須包含系名、系人數、工資總額和平均工資四個列。常用合計函數計數COUNT()

求和SUM()求平均值AVG()求最大值MAX()求最小值MIN()查詢的創建和使用按系名分組：把所有系名相同的記錄壓縮成一個記錄Page119示例分析示例5.2具體步驟

打開查詢設計器添加表gzb定義查詢輸出內容設置排序依據設置分組依據count(*)AS人數avg(gzb.jbgz)降序gzb.xim查詢的創建和使用Page1196.對查詢結果的其他設置（SQL命令中）是否允許重複記錄設置結果的記錄範圍7.運行查詢“運行”按鈕“查詢”菜單中的“運行查詢”DO命令（例：DOxijsgz.qpr）右擊8.選擇查詢結果的輸出類型（INTO

子句）P121查詢的創建和使用Page121兩個以上的表或視圖查詢數據源表與表之間的聯接比較指定字段中的值聯接多張表中的記錄多表查詢查詢的創建和使用Page122多表查詢與單表查詢不同多表查詢Page122多表查詢中表與表之間的聯接類型表已經建立永久性關係，以該關係為聯接條件兩表有同名字段，以同名字段為聯接條件例5.3：查詢系代號為“02”的系中所有成績總分在400分以上，並且各門課程的成績不得低於60分的學生。要求在查詢結果中包含學生的學號、姓名、總分、平均分和最低分，並按照總分從高到低排列。

建立多表查詢多表查詢Page124多表查詢Page124選取表(XS,CJ)設置聯接(XS.xh=CJ.xh內部聯接)選擇字段(XS.xh，XS.xm，SUM(CJ.cj)AS總分，AVG(CJ.cj)AS平均分，MIN(cj.cj)AS最低分)篩選條件：XS.xdh=“02”排序依據：SUM(CJ.cj)AS總分,降序分組依據：XS.xh

滿足條件:總分>=400AND最低分>=60保存查詢xs_cj.qpr運行查詢Doxs_cj.qpr查詢的創建和使用Page124以電子錶格形式顯示數據的查詢利用“交叉表嚮導”建立字段選取（只能在單獨的表和視圖中選取）佈局（拖進，拖出）Xh

行Kcdh

列Cj

數據加入總和資訊完成（cj_cross.qpr)交叉表查詢查詢視圖

讀寫，不生成新檔，是資料庫的組成部分，可以作為數據環境的數據源只讀，生成新檔，不是資料庫的組成部分，不可以作為數據環境的數據源查詢與視圖比較視圖的創建和使用Page126

視圖

基表

本地視圖

遠程視圖

視圖中的源數據表資料庫的組成部分，基於表的可更新的數據集合，是一個虛表使用VFPSQL語法從視圖或表中選擇資訊使用遠程SQL語法從遠程ODBC數據源中選取信息視圖的創建和使用Page126視圖概念

視圖設計器窗口視圖名稱常用工具欄聯接選項卡字段選項卡更新條件篩選選項卡排序依據分組依據創建本地視圖

Page127創建本地視圖Page127例如：在jxsj庫中創建一個視圖viewxskccj,要求該視圖包含四個字段：學號、姓名、課程名稱和成績。選取表(XS,CJ,KC)設置內聯接(XS.xh=CJ.xh,CJ.kcdh=KC.kcdh)選擇字段(XS.xh，XS.xm,KC.kcm，

CJ.cj)保存視圖viewxskccjPage127創建本地視圖CREATESQLVIEW<視圖名>AS<SELECT-SQL語句>使用CREATESQLVIEW

命令例1：CREATESQLVIEWjs_viewAS;SELECT*FROMjxsj!js

恢復源表初始狀態主關鍵字段(必設)可更新字段視圖的創建和使用

Page127用視圖更新源表數據選擇“表”除關鍵字段外的所有字段均設置為可更新更新主開關例：使用視圖js_view

更新js表中的數據參數化視圖限定作用範圍避免每取部分記錄就單獨創建一個視圖參數值選擇符合條件的記錄視圖的創建和使用Page129

視圖的創建和使用參數視圖1“篩選”選項卡

創建參數化視圖Page129

創建參數化視圖視圖的創建和使用點擊滑鼠設定參數名和參數類型

Page129ALTERTABLE-SQLCREATETABLE-SQLUPDATE-SQLINSERT-SQLSELETE-SQL語句SELECT-SQLDELETE-SQLCREATECURSOR-SQLPage133SQL——

結構化查詢語言，關係資料庫語言的標準1、創建學生表結構createtable

xs(xhc(6),xmc(8),xbc(2))2、學生表添加字段班級名稱(bjmc)altertable

xs

addcolumn

bjmcC(12)3、學生表字段班級名稱(bjmc)改為bjaltertable

xs

renamecolumn

bjmc

TO

bj4、學生表刪除字段班級名稱(bj)

altertable

xs

dropcolumn

bj表結構相關命令1、成績表設置字段有效性規則Creattablecj1(xhc(6),kcdhc(6),cjn(3,0)check

cj>=80)altertable

cj1altercolumn

cj

setcheck

cj>=602、成績表設置字段默認值creattablecj2(xhc(6)default'980000',kcdhc(6),cjn(3))altertablecj2altercolumnxhsetdefault'980000'3、教師表設置記錄有效性規則和有效性資訊altertable

jssetcheckyear(gzrq)-year(csrq)>17;error“年齡要大於17歲"資料庫表相關命令1、學生表中插入一條記錄

Insertintoxs(xh，xm)values(“950106”,”高山”)2、把教師表中所有工齡不滿20的教師的工齡加5(1)update

js

set

gl=gl+5

where

gl<20(2)replace

gl

with

gl+5

for

gl<203、將學生表中年齡(age)大於25歲的記錄刪除(1)use

xsdeletefor

age>25(傳統VFP命令語句)(2)deletefrom

xs

where

age>25（SQL語句）表記錄的處理相關命令SELETE-SQL語句Page133SELECT-SQL命令的語法格式（見P135）SELECT指定輸出字段、函數和運算式FROM指定數據源表JOIN…ON確定表之間的聯接WHERE篩選源表記錄GROUPBY定義記錄的分組HAVING篩選結果記錄ORDERBY指定結果的順序INTO指定輸出類型顯示JS表中所有教師的工號和姓名（從一個表中選取兩個字段）

SELECTjs.gh,js.xmFROMjxsj!jsSELETE-SQL語句應用舉例Page1371.基於單個表的查詢示例顯示XS表中學號以“99”開頭的學生情況SELECT*FROMjxsj!xsWHERElike("99*",xh)SELETE-SQL語句應用舉例Page1381.基於單個表的查詢示例like()函數－like(cExpression1,cExpression2)確定一個字元運算式是否與另一個字元運算式相匹配。cExpression1中可以包含通配符*和?。問號(?)可與cExpression2中的任何單個字元相匹配，星號(*)可與任意數目的字元相匹配。從高到低顯示CJ表中代號為“01”課程的學生的學號和成績selectcj.xh,cj.cj;fromjxsj!cj;wherecj.kcdh='01';orderbycj.cj

descSELETE-SQL語句應用舉例Page1381.基於單個表的查詢示例顯示XS和ZY兩個表中三個字段xh,xm,zymc的內容，並根據zydh字段聯接兩個表。selectxs.xh,xs.xm,zy.zymc;fromjxsj!xs,jxsj!zy;wherexs.zydh=zy.zydhSELETE-SQL語句應用舉例Page1382.基於多個表的查詢示例selectxs.xh,xs.xm,zy.zymc;fromjxsj!xsinnerjoinjxsj!zyonxs.zydh=zy.zydh顯示CJ表中有不及格成績的學生學號和姓名,多門不及格者顯示一次，且顯示開始的6條記錄，按成績降序排列。selectdistincttop5cj.xh,xs.xm;fromjxsj!xsinnerjoinjxsj!cj;onxs.xh=cj.xh;wherecj<60;orderbycj.cj

descSELETE-SQL語句應用舉例Page1382.基於多個表的查詢示例Distinct——排除查詢結果中重複的行TOPn[Percent]——限定查詢顯示的記錄數分組顯示CJ表中的課程代號及其最高分selectcj.kcdh,max(cj.cj)as最高分;fromjxsj!cj;groupbycj.kcdhSELETE-SQL語句應用舉例Page1383.含有計算字段的查詢示例查詢每門課程的課程代號和名稱及最高分，把查詢結果保存到kc_maxcj.dbf中（把兩個表中的字段內容保存到第三個表中）。selectcj.kcdh,kc.kcm,max(cj.cj)as最高分;fromjxsj!cjinnerjoinjxsj!kc;oncj.kcdh=kc.kcdh;groupbycj.kcdh;intotablekc_maxcj.dbfSELETE-SQL語句應用舉例Page1383.含有計算字段的查詢示例已知在jxsj庫中包含一個視圖viewXsKcCj，該視圖包含字段：cj.xh,xs.xm,kc.kcm和cj.cj。現在從該視圖中查詢每門課程中的不及格學生的名單，查詢結果包含視圖中的所有字段。select*fromjxsj!viewxskccj;whereviewxskccj.cj<60SELETE-SQL語句應用舉例Page1384.基於視圖的查詢示例顯示JS表中已擔任課程的教師的姓名和系代號。selectjs.xm,js.xdh;fromjxsj!js;Wherejs.gh

in(select

rk.ghfromjxsj!rk)SELETE-SQL語句應用舉例Page1385.子查詢示例selectjs.xm,js.xdh;fromjxsj!js;WhereEXIST(select

rk.ghfromjxsj!rkwhereJS.GH=RK.GH)查詢全校師生名單，要求如果是教師必須注明“教師”，結果包括四列：系名、類別、姓名和性別。selectxim.ximing,"教師"as類別,js.xm,js.xb;fromjxsj!xim,jxsj!js;wherexim.xdh=js.xdh;Union;selectxim.ximing,"學生"as類別,xs.xm,xs.xb;fromjxsj!ximinnerjoinjxsj!xson;

xim.xdh=xs.xdhSELETE-SQL語句應用舉例Page1386.組合查詢示例選定的輸出字段必須具有相同的數據類型和寬度，否則不相容。基於成績表，查詢各個分數段的學生得分人數，要求結果中包含兩個列：分數段類型和數量，並按分數段類型降序排列。sele"90~100"as分數段類型,count(*)as數量;fromcj;wherecj>=90;union;sele"80~89"as分數段類型,count(*)as數量;fromcj;wherecj>=80andcj<90;union;…Orderby1descSELETE-SQL語句應用舉例Page1386.組合查詢示例SELECT-SQL命令的語法格式（見P135）SELECT指定輸出字段、函數和運算式FROM指定數據源表JOIN…ON確定表之間的聯接WHERE篩選源表記錄GROUPBY定義記錄的分組HAVING篩選結果記錄ORDERBY指定結果的順序INTO指定輸出類型

習題五：P-141

思考題：

1.查詢檔的擴展名是什麼？查詢檔中保存的是查詢的命令還是查詢的結果？

2.簡述查詢和視圖之間的異同點。作業五設有兩個數據庫表，父表和子表之間是一對多的聯繫，為控制子表和父表的關聯，可以設置“參照完整性規則”，為此要求這兩個表______。

A）在父表連接字段上建立普通索引，在子表連接字段上建立主索引

B）在父表連接字段上建立主索引，在子表連接字段上建立普通索引

C）在父表連接字段上不需要建立任何索引，在子表連接字段上建立普通索引

D）在父表和子表的連接字段上都要建立主索引

2005年4月全國電腦等級考試二級VisualFoxPro筆試試卷

1、BVisualFoxPro的“參照完整性”中“插入規則”包括的選擇是______。

A）級聯和忽略B）級聯和刪除

C）級聯和限制D）限制和忽略

在VisualFoxPro中，如果在表之間的聯繫中設置了參照完整性規則，並在刪除規則中選擇了“限制”，則當刪除父表中的記錄時，系統反應是______。

A）不做參照完整性檢查

B）不准刪除父表中的記錄

C）自動刪除子表中所有相關的記錄

D）若子表中有相關記錄，則禁止刪除父表中記錄1、D2、D2005年4月全國電腦等級考試二級VisualFoxPro筆試試卷

在VisualFoxPro中，關於查詢和視圖的正確描述是______。

A）查詢是一個預先定義好的SQLSELECT語句檔

B）視圖是一個預先定義好的SQLSELECT語句檔

C）查詢和視圖是同一種檔，只是名稱不同

D）查詢和視圖都是一個存儲數據的表

在VisualFoxPro中，以下關於視圖描述中錯誤的是______。

A）通過視圖可以對表進行查詢

B）通過視圖可以對表進行更新

C）視圖是一個虛表

D）視圖就是一種查詢

2005年4月全國電腦等級考試二級VisualFoxPro筆試試卷

1、A2、D在VisualFoxPro中，以下有關SQL的SELECT語句的敘述中，錯誤的是______。

A）SELECT子句中可以包含表中的列和運算式

B）SELECT子句中可以使用別名

C）SELECT子句規定了結果集中的列順序

D）SELECT子句中列的順序應該與表中列的順序一致

下列關於SQL中HAVING子句的描述，錯誤的是______。

A）HAVING子句必須與GROUPBY子句同時使用

B）HAVING子句與GROUPBY子句無關

C）使用WHERE子句的同時可以使用HAVING子句

D）使用HAVING子句的作用是限定分組的條件

2005年4月全國電腦等級考試二級VisualFoxPro筆試試卷

1、D2、B使用如下3個數據庫表：

學生表：S(學號，姓名，性別，出生日期，院系)

課程表：C(課程號，課程名，學時)

選課成績表：SC(學號，課程號，成績)2005年4月全國電腦等級考試二級VisualFoxPro筆試試卷

用SQL語言檢索選修課程在5門以上（含5門）的學生的學號、姓名和平均成績，並按平均成績降序排序，正確的命令是______。

A）SELECTS.學號,姓名,平均成績FROMS,SC;

WHERES.學號=SC.學號;

GROUPBYS.學號HAVINGCOUNT(*)>=5ORDERBY平均成績DESC

B）SELECT學號,姓名,AVG(成績)FROMS,SC;

WHERES.學號=SC.學號ANDCOUNT(*)>=5;

GROUPBY學號ORDERBY3DESC

C）SELECTS.學號,姓名,AVG(成績)平均成績FROMS,SC;

WHERES.學號=SC.學號ANDCOUNT(*)>=5;

GROUPBYS.學號ORDERBY平均成績DESC

D）SELECTS.學號,姓名,AVG(成績)平均成績FROMS,SC;

WHERES.學號=SC.學號;

GROUPBYS.學號HAVINGCOUNT(*)>=5ORDERBY3DESCD2005年9月全國電腦等級考試二級VisualFoxPro筆試試卷

以下關於視圖的描述正確的是

A）視圖保存在專案檔中B）視圖保存在資料庫檔中

C）視圖保存在表檔中D）視圖保存在視圖檔中

在VisualFoxPro中，要運行查詢檔query1.qpr，可以使用命令

A）DOquery1B）DOquery1.qpr

C）DOQUERYquery1D）RUNquery1在SQL的SELECT查詢中使用【】子句消除查詢結果中的重複記錄。1、B2、B3、DISTINCT1、INTOCURSER2、CHECK3、HAVING

在VisualFoxPro中，使用SQL的SELECT語句將查詢結果存儲在一個臨時表中，應該使用【】子句。在VisualFoxPro中，使用SQL的CREATETABLE語句建立資料庫表時，使用【】子句說明有效性規則（域完整性規則或字段取值範圍）。在SQL的SELECT語句進行分組計算查詢時，可以使用【】子句來去掉不滿足條件的分組。

2005年9月全國電腦等級考試二級VisualFoxPro筆試試卷

使用如下三個表：

職員.DBF：職員號C（3），姓名C（6），性別C（2），組號N（1），職務C（10）

客戶.DBF：客戶號C（4），客戶名C（36），地址C（36），所在城市C（36）

訂單.DBF：訂單號C（4），客戶號C（4），職員號C（3），簽訂日期D，金額N（6.2）查詢金額最大的那10%訂單的資訊。正確的SQL語句是

A）SELECT*TOP10PERCENTFROM訂單

B）SELECTTOP10%*FROM訂單ORDERBY金額

C）SELECT*TOP10PERCENTFROM訂單ORDERBY金額

D）SELECTTOP10PERCENT*FROM訂單ORDERBY金額DESC

D

2005年9月全國電腦等級考試二級VisualFoxPro筆試試卷

使用如下三個表：

職員.DBF：職員號C（3），姓名C（6），性別C（2），組號N（1），職務C（10）

客戶.DBF：客戶號C（4），客戶名C（36），地址C（36），所在城市C（36）

訂單.DBF：訂單號C（4），客戶號C（4），職員號C（3），簽訂日期D，金額N（6.2）查詢訂單數在3個以上、訂單的平均金額在200元以上的職員號。正確的SQL語句是

A）SELECT職員號FROM訂單GROUPBY職員號HAVINGCOUNT(*)>3ANDAVG_金額>200

B）SELECT職員號FROM訂單GROUPBY職員號HAVINGCOUNT(*)>3ANDAVG(金額)>200

C）SELECT職員號FROM訂單GROUPBY職員號HAVINGCOUNT(*)>3WHEREAVG(金額)>200

D）SELECT職員號FROM訂單GROUPBY職員號WHERECOUNT(*)>3ANDAVG_金額>200

B2005年9月全國電腦等級考試二級VisualFoxPro筆試試卷

使用如下三個表：

職員.DBF：職員號C（3），姓名C（6），性別C（2），組號N（1），職務C（10）

客戶.DBF：客戶號C（4），客戶名C（36），地址C（36），所在城市C（36）

訂單.DBF：訂單號C（4），客戶號C（4），職員號C（3），簽訂日期D，金額N（6.2）顯示2005年1月1日後簽訂的訂單，顯示訂單的訂單號、客戶名以及簽訂日期。正確的SQL語句是

A）SELECT訂單號,客戶名,簽訂日期FROM訂單JOIN

客戶

ON訂單.客戶號=客戶.客戶號WHERE簽訂日期>{^2005-1-1}

B）SELECT訂單號,客戶名,簽訂日期FROM訂單JOIN客戶

WHERE訂單.客戶號=客戶.客戶號AND簽訂日期>{^2005-1-1}

C）SELECT訂單號,客戶名,簽訂日期FROM訂單,客戶

WHERE訂單.客戶號=客戶.客戶號AND簽訂日期<{^2005-1-1}

D）SELECT訂單號,客戶名,簽訂日期FROM訂單,客戶

ON訂單.客戶號=客戶.客戶號AND簽訂日期<{^2005-1-1}

A

2005年9月全國電腦等級考試二級VisualFoxPro筆試試卷

在VisualFoxPro中以下敘述錯誤的是

A)關係也被稱作表B)資料庫檔不存儲用戶數據

C)表檔的擴展名是.dbfD)多個表存儲在一個物理檔中在VisualFoxPro中以下敘述正確的是

A)利用視圖可以修改數據B)利用查詢可以修改數據

C)查詢和視圖具有相同的作用D)視圖可以定義輸出去向

以下關於”查詢”的描述正確的是

A)查詢保存在專案檔中B)查詢保存在資料庫檔中

C)查詢保存在表檔中D)查詢保存在查詢檔中

SQL的數據操作語句不包括

A)INSERTB)UPDATEC)DEL