VFP數(shù)據(jù)庫系統(tǒng)Visual FoxPro SQL關系數(shù)據(jù)庫查詢語言

12023/4/7菜單與工具欄設計第七章SQL關系數(shù)據(jù)庫查詢語言7.1SQL概述7.2SQL數(shù)據(jù)定義語言7.3SQL數(shù)據(jù)查詢語言7.4SQL數(shù)據(jù)操縱語言22023/4/7菜單與工具欄設計7.1SQL概述2.特點SQL是一體化地語言SQL語言是一種高度非過程化地語言SQL語言非常簡潔應用方式靈活:SQL語言可以直接以命令方式交互使用,也可以嵌入到程序設計語言中以程序方式使用。1.概念:結構化查詢語言SQL（StructuredQueryLanguage）用于對關系型數(shù)據(jù)庫中地數(shù)據(jù)進行儲存,查詢,更新等操作,為用戶提供了以下功能:數(shù)據(jù)定義,數(shù)據(jù)查詢,數(shù)據(jù)操縱與數(shù)據(jù)控制。32023/4/7菜單與工具欄設計3.SQL語言組成數(shù)據(jù)庫定義語言DDL（DataDefinitionLanguage）數(shù)據(jù)操縱語言DML（DataManipulationLanguage）:數(shù)據(jù)操縱分成數(shù)據(jù)查詢與數(shù)據(jù)更新兩類。數(shù)據(jù)庫控制語言DCL（DataControlLanguage）:數(shù)據(jù)庫控制語言用來授予或收回訪問數(shù)據(jù)庫地某些權限。嵌入式SQL語言:SQL語言有兩種使用方式:一種是在終端交互方式下使用,稱為交互式SQL。另一種是嵌入在高級語言地程序中使用,稱為嵌入式SQL。42023/4/7菜單與工具欄設計7.2SQL數(shù)據(jù)定義語言SQL地數(shù)據(jù)定義語言包括數(shù)據(jù)庫模式地定義,基本表地定義,視圖地定義與索引地定義四部分,它由CREATE,ALTER與DROP命令組成。在本節(jié)將主要介紹一下VisualFoxPro支持地基本表定義功能。52023/4/7菜單與工具欄設計7.2.1創(chuàng)建基本表命令格式CREATETABLE|DBF<基本表名>[FREE](<字段名1><數(shù)據(jù)類型>[(字段寬度[,小數(shù)位數(shù)])][NULL|NOTNULL][CHECK字段地有效性規(guī)則[ERROR錯誤信息]][DEFAULT字段地默認值][PRIMARYKEY|UNIQUE][REFERENCES父表名[TAGTagName1]][,字段名2…]說明PRIMARYKEY將此字段作為主索引UNIQUE將此字段作為候選索引62023/4/7菜單與工具欄設計續(xù)命令格式[,PRIMARYKEY字段或字段組合TAG主索引標識|,UNIQUE字段或字段組合TAG候選索引標識][,FOREIGNKEY外部索引關鍵字TAG外部索引標識REFERENCES父表名[TAGTagName5]][,CHECK表地有效性規(guī)則[ERROR錯誤信息]])說明FOREIGNKEY外碼,可建立普通索引,并與父表建立一對多地關系。72023/4/7菜單與工具欄設計例7.1用命令建立教師工資管理數(shù)據(jù)庫與數(shù)據(jù)庫中地基本表。*ex7.1.prgCREATEDATABASEmydata\gzglCREATETABLEmydata\jsgz(bhC(5)PRIMARYKEY,jbgzN(6,1);CHECK(jbgz>800ANDjbgz<4000);ERROR"基本工資地范圍在800～4000！",;gwgzN(6,1),kkN(6,1),sfgzN(7,1))CREATETABLEmydata\jsqk;(bhC(5)PRIMARYKEYREFERENCESjsgz,;xmC(8)UNIQUE,;xbC(2),csrqD,zcC(10),dyfL,;szxyC(12),zpG,bzM)82023/4/7菜單與工具欄設計運行程序ex7.1.prg后,在數(shù)據(jù)庫設計器中打開新建地數(shù)據(jù)庫:92023/4/7菜單與工具欄設計7.2.2修改基本表修改表結構地命令是ALTERTABLE,該命令有3種格式。1.添加（ADD）或修改（ALTER）指定表地字段

ALTERTABLE<基本表名>;

ADD|ALTER[COLUMN]<字段名><數(shù)據(jù)類型>[(字段寬度[,小數(shù)位數(shù)])][NULL|NOTNULL];[CHECK字段地有效性規(guī)則[ERROR錯誤信息]][DEFAULT字段地默認值][PRIMARYKEY|UNIQUE][REFERENCES父表名[TAGTagName1]]說明說明:它地句法基本可以與CREATETABLE地句法相對應。102023/4/7菜單與工具欄設計例7.2為教師工資表增加一個xm字段。ALTERTABLEmydata\jsgzADDCOLUMN;xmC(6)NULL例7.3將教師工資表中地xm字段寬度由6改為8,并將該字段改為候選關鍵字。ALTERTABLEmydata\jsgzALTER;xmC(8)NULLUNIQUE112023/4/7菜單與工具欄設計2.定義,修改與刪除字段地有效性規(guī)則與默認值定義

ALTERTABLE<基本表名>;

ALTER[COLUMN]<字段名>[SETDEFAULT字段地默認值][SETCHECK字段地有效性規(guī)則[ERROR錯誤信息]][DROPDEFAULT][DROPCHECK]說明:DROPDEFAULT刪除已有字段地默認值。

DROPCHECK刪除已有字段地有效性規(guī)則。122023/4/7菜單與工具欄設計例7.4修改或定義教師工資表中gwgz字段地有效性規(guī)則。ALTERTABLEmydata\jsgzALTERgwgz;SETCHECKgwgz>800;ERROR"崗位工資應該大于800"例7.5刪除教師工資表中jbgz字段地有效性規(guī)則,并設置該字段地缺省值為1000。ALTERTABLEmydata\jsgzALTERjbgz;ALTERjbgzSETDEFAULT1000DROPCHECK132023/4/7菜單與工具欄設計3.刪除字段,修改字段名,定義,修改與刪除表一級地有效性規(guī)則等

ALTERTABLE<基本表名>;

[DROP[COLUMN]字段名][RENAMECOLUMN字段名1TO字段名2][SETCHECK表地有效性規(guī)則[ERROR錯誤信息]][DROPCHECK][ADDPRIMARYKEY關鍵字表達式TAG主索引標識][DROPPRIMARYKEY][ADDUNIQUE關鍵字表達式[TAG候選索引標識][DROPUNIQUETAG候選索引標識][ADDFOREIGNKEY關鍵字表達式TAG索引標識

REFERENCES父表名[TAG索引標識]][DROPFOREIGNKEYTAG索引標識]142023/4/7菜單與工具欄設計例7.7將教師工資表中xm字段名改為職工姓名。。ALTERTABLEmydata\jsgz;RENAMECOLUMNxmTO職工姓名例7.8刪除教師工資表中職工姓名字段。ALTERTABLEmydata\jsgz;DROPCOLUMN職工姓名例7.9刪除教師工資表地主索引,將bh字段定義外部索引關鍵字,索引標識名為bh,建立與教師基本情況表地關系。ALTERTABLEmydata\jsgzDROPPRIMARYKEYALTERTABLEmydata\jsgz;ADDFOREIGNKEYbhTAGbh;REFERENCESjsqk152023/4/7菜單與工具欄設計7.2.3刪除基本表命令格式DROPTABLE<表名>功能直接從磁盤上刪除數(shù)據(jù)表文件

162023/4/7菜單與工具欄設計7.3SQL數(shù)據(jù)查詢語言7.3.1SELECT語句SQL地核心是查詢。SQL地查詢語句也稱SELECT語句,它地基本形式由SELECT－FROM－ＷHERE查詢塊組成,多個查詢塊可以嵌套執(zhí)行。172023/4/7菜單與工具欄設計SELECT語法格式SELECT[ALL|DISTINCT][TOPnExpr[PERCENT]]

[表名.]字段名1[AS顯示名][,[表名.]字段名1[AS顯示名]...]FROM[數(shù)據(jù)庫名!]表名[[AS]表別名][[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN

數(shù)據(jù)庫名!]表名[[AS]表別名][ONJoinCondition][[INTO目地文件]|[TOFILE文件名[ADDITIVE]

|TOPRINTER[PROMPT]|TOSCREEN]]注:目地文件有三種選擇:INTOARRAY數(shù)組名,INTOCURSOR臨時表名,INTOTABLE表名182023/4/7菜單與工具欄設計續(xù)SELECT語法格式[NOCONSOLE][PLAIN][NOWAIT][WHERE連接條件[AND|OR篩選條件]][GROUPBY分組字段][HAVING分組條件][UNION[ALL]另一個查詢語句][ORDERBY排序字段[ASC|DESC]192023/4/7菜單與工具欄設計說明SELECT子句指定在查詢結果中包含地字段,常量與表達式。FROM子句指定查詢所涉及地關系。WHERE子句指定查詢地邏輯條件。GROUPBY按表達式1地值對查詢結果地行進行分組。UNION把一個查詢語句地最后查詢結果同另一個查詢語句最后查詢結果組合起來。ORDERBY按表達式2地值對查詢結果地行進行排序。202023/4/7菜單與工具欄設計續(xù)說明條件表達式運算符運算符意義運算符意義＝等于OR或>,>=,<,<=關系運算NOT非<>,!=,#不等于IN在集合中AND與NOTIN不在集合中BETWEEN…AND閉區(qū)間[,]LIKE與_與%進行單個,多個字符匹配ANY滿足一個就為真ISNULL為空ALL,SOME滿足所有地記錄才為真ISNOTNULL不為空212023/4/7菜單與工具欄設計7.3.2簡單查詢簡單查詢是SQL語言中最簡單地查詢操作,這些查詢都基于單個表,可以由SELECT與FROM短語構成無條件查詢,或由SELECT-FROM-WHERE短語構成條件查詢。1.不帶條件地列查詢例7.10查詢"jsqk.DBF"表中地所有記錄。OPENDATABASEgzgl

SELECT*FROMjsqk例7.11查詢"jsqk.DBF"表中地全體教師地姓名與新編號。SELECTxm,"1"+bhAS新編號FROMjsqk運行程序222023/4/7菜單與工具欄設計運行程序2.帶條件地列查詢例7.12查詢"jsqk.DBF"表中地全體女教師地編號,姓名與性別。SELECTbhAS編號,xmAS姓名,xbFROMjsqkWHERExb="女"232023/4/7菜單與工具欄設計例7.13查詢"jsqk.DBF"表中地信息學院全體教師地職稱SELECTzcAS職稱FROMjsqkWHEREszxy="信息學院"在結果中有重復值,如果要去掉重復值只需要指定DISTINCT:SELECTDISTINCTzcAS職稱FROMjsqk;

WHEREszxy="信息學院"242023/4/7菜單與工具欄設計例7.15查詢"jsqk.DBF"表中在1960~1971出生地教師記錄地姓名與出生日期。SELECTxmAS姓名,csrqAS出生日期FROMjsqk;

WHEREyear(csrq)BETWEEN1960AND1971252023/4/7菜單與工具欄設計例7.16查詢"jsqk.DBF"表中既不是信息學院,也不是電氣學院地教師姓名與所在學院。SELECTxmAS姓名,szxyAS所在學院FROMjsqk;

WHEREszxyNOTIN("信息學院","電氣學院")例7.17查詢"jsqk.DBF"表中姓劉地教師姓名與所在學院。SELECTxmAS姓名,szxyAS所在學院FROMjsqk;

WHERExmLIKE"劉%"例7.18查詢"jsqk.DBF"表中姓名第二個字符為"小"地教師姓名與所在學院。SELECTxmAS姓名,szxyAS所在學院FROMjsqk;

WHERExmLIKE"_小%"262023/4/7菜單與工具欄設計例7.21按崗位工資升序,再按編號降序查詢"jsgz.DBF"表中所有記錄。SELECTbhAS編號,jbgzAS基本工資,;

gwgzAS崗位工資,kkAS扣款,sfgzAS實發(fā)工資;

FROMjsgzORDERBYgwgz,bhDESC注:對于NULL值,如果是升序排列,含NULL值地記錄最后顯示;如果是降序排列,含NULL值地記錄最先顯示。272023/4/7菜單與工具欄設計例7.22查詢"jsgz.DBF"表中崗位工資最低地3條記錄。SELECTbhAS編號,jbgzAS基本工資,;

gwgzAS崗位工資,sfgzAS實發(fā)工資,;

kkAS扣款Top3FROMjsgzORDERBYgwgz282023/4/7菜單與工具欄設計例7.23查詢"jsgz.DBF"表中基本工資最高地40%條記錄。SELECT*top40PERCENTFROMjsgzORDERBYjbgzDESC例7.24查詢"jsgz.DBF"表中實發(fā)工資為空值地教師編號與實發(fā)工資。SELECTbhAS編號,sfgzAS實發(fā)工資FROMjsgzWHEREsfgzISNULL如果要查詢實發(fā)工資不為空地教師編號與實發(fā)工資,只要將ISNULL改為ISNOTNULL:SELECTbhAS編號,sfgzAS實發(fā)工資FROMjsgzWHEREsfgzISNOTNULL292023/4/7菜單與工具欄設計7.3.3連接查詢1.簡單連接查詢例7.25查詢所有教師地編號,姓名與基本工資。SELECTjsqk.bhAS編號,xmAS姓名,;

jbgzAS基本工資FROMjsqk,jsgz;

WHEREjsqk.bh=jsgz.bh注:這里"jsqk.bh=jsgz.bh"是連接條件。"jsqk.DBF"與"jsgz.DBF"之間應建立關系（否則無法構成檢索表達式）。連接查詢是基于多個表地查詢,即FROM后面有多個表。302023/4/7菜單與工具欄設計2.自身連接查詢有時在查詢中對相同地表進行連接,即一個表與其自身進行連接,稱其為自身連接。為了區(qū)別兩個相同地表,需要對一個表指定兩個別名。例7.26查詢"jsgz.DBF"表中基本工資大于等于編號為"40001"地教師基本工資地所有教師記錄。SELECTS.bhAS編號,S.jbgzAS基本工資,;S.gwgzAS崗位工資,S.kkAS扣款,;S.sfgzAS實發(fā)工資;FROMjsgzASS,jsgzASC;WHERES.jbgz>=C.jbgzANDC.bh="40001"312023/4/7菜單與工具欄設計2.外連接查詢外聯(lián)接查詢可分為左連接,右連接與完全連接查詢。在一般SQL中外連接運算符是"*="與"=*":"*="稱為左連接,"=*"稱為右連接。VisualFoxPro不支持外連接運算符"*="與"=*",它用另外地語法格式支持外連接,語法格式如下:FROMTableINNER|LEFT|RIGHT|FULLJOINTableONJoinConditionLEFT[OUTER]JOIN（左聯(lián)接）查詢結果中包含父表中地所有記錄,如果子表中無匹配地記錄,子表字段顯示NULL。RIGHT[OUTER]JOIN（右聯(lián)接）查詢結果中包含子表中地所有記錄,如果父表中無匹配地記錄,父表字段顯示NULL。FULL[OUTER]JOIN（完全聯(lián)接）查詢結果中包含左右表中地所有記錄。如果父表中地行在子表中無匹配地記錄,子表字段顯示NULL。如果子表中地行在父表中無匹配地記錄,父表字段顯示NULL。322023/4/7菜單與工具欄設計例7.27用左連接查詢"jsqk.DBF"表中地編號,姓名與"jsgz.DBF"表中地編號。SELECTjsqk.bhAS教師情況表編號,jsqk.xmAS姓名,jsgz.bhAS教師工資表編號;

FROMjsqkLEFTJOINjsgzONjsqk.bh=jsgz.bh332023/4/7菜單與工具欄設計例7.28用完全連接查詢"jsqk.DBF"表中地編號,姓名與"jsgz.DBF"表中地編號。SELECTjsqk.bhAS教師情況表編號,jsqk.xmAS姓名,jsgz.bhAS教師工資表編號;

FROMjsqkFULLJOINjsgzONjsqk.bh=jsgz.bh342023/4/7菜單與工具欄設計7.3.4嵌套查詢例7.29查詢外語學院教師地編號,基本工資。SELECTbhAS編號,jbgzAS基本工資;

FROMjsgzWHEREbhIN;(SELECTbhFROMjsqkWHEREszxy="外語學院")在SQL語言中,SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊中地WHERE子句中地查詢稱為嵌套查詢。352023/4/7菜單與工具欄設計例7.30查詢與劉詩云地崗位工資相同地教師姓名與崗位工資。SELECTxmAS姓名,gwgzAS崗位工資;

FROMjsgzS1,jsqkC1;

WHERES1.bh=C1.bhANDS1.gwgz=;(SELECTgwgzFROMjsqkS2,jsgzC2;

WHERES2.xm="劉詩云"ANDS2.bh=C2.bh)362023/4/7菜單與工具欄設計例7.31查詢比外語學院某一教師年齡小地其它學院教師地姓名,出生日期與所在學院。SELECTxmAS姓名,csrqAS出生日期,;

szxyAS所在學院FROMjsqk;

WHEREszxy!="外語學院"ANDcsrq>ANY;(SELECTcsrqFROMjsqk;

WHEREszxy="外語學院")372023/4/7菜單與工具欄設計例7.32查詢信息學院教師地基本工資。SELECTjbgzFROMjsgzWHEREEXISTS;(SELECT*FROMjsqk;

WHEREbh=jsgz.bhANDszxy="信息學院")注:其中謂詞EXISTS或NOTEXISTS是用來檢查在子查詢中是否有結果返回,帶有EXISTS謂詞地子查詢不返回任何數(shù)據(jù),只產(chǎn)生邏輯值真值"TRUE"或邏輯值假值"FALSE"。382023/4/7菜單與工具欄設計7.3.5分組與計算查詢1.計算查詢SQL不僅具有一般地檢索能力,而且還有計算方式地檢索,用于計算檢索地函數(shù)有:COUNT([DISTINCT|ALL]*)或

COUNT([DISTINCT|ALL]<列名>):計數(shù)。SUM([DISTINCT|ALL]<列名>):求與。AVG([DISTINCT|ALL]<列名>):計算平均值。MAX([DISTINCT|ALL]<列名>):求最大值。MIN([DISTINCT|ALL]<列名>):求最小值。如果指定DISTINCT短語,則表示在計算時要取消指定列中地重復值。392023/4/7菜單與工具欄設計例7.33查詢所在學院個數(shù)。SELECTCOUNT(DISTINCTszxy)FROMjsqk例7.34查詢?nèi)w教師數(shù)。SELECTCOUNT(*)FROMjsqk例7.35查詢信息學院教師地平均基本工資。SELECTAVG(jbgz)FROMjsgz,jsqk;

WHEREjsgz.bh=jsqk.bhANDszxy="信息學院"402023/4/7菜單與工具欄設計2.分組查詢例7.36查詢各學院教師地平均基本工資。SELECTszxyAS所在學院,;

AVG(jbgz)AS平均基本工資;FROMjsgz,jsqkwherejsgz.bh=jsqk.bh;GROUPBYszxy412023/4/7菜單與工具欄設計例7.37查詢至少有兩個職工地學院教師地平均基本工資。SELECTszxyAS所在學院,COUNT(*)AS人數(shù),;

AVG(jbgz)AS平均基本工資;FROMjsgz,jsqkwherejsgz.bh=jsqk.bh;GROUPBYszxyHAVINGCOUNT(*)>=2422023/4/7菜單與工具欄設計例7.37查詢至少有兩個職工地學院教師地平均基本工資。SELECTszxyAS所在學院,COUNT(*)AS人數(shù),;

AVG(jbgz)AS平均基本工資;FROMjsgz,jsqkwherejsgz.bh=jsqk.bh;GROUPBYszxyHAVINGCOUNT(*)>=2432023/4/7菜單與工具欄設計7.3.6集合地并運算SQL支持集合地并（UNION）運算,即可以將兩個SELECT語句地查詢結果通過并運算合并成一個查詢結果。為了進行并運算,要求這樣地兩個查詢結果具有相同地字段個數(shù),并且對應字段地值要出自同一個值域（相同地數(shù)據(jù)類型與取值范圍）。例7.38查詢信息學院與電氣學院教師地記錄。SELECT*FROMjsqkWHEREszxy="信息學院";UNION;SELECT*FROMjsqkWHEREszxy="電氣學院"442023/4/7菜單與工具欄設計7.3.7重定向例7.39查詢"jsqk.DBF"地所有記錄,并將顯示結果保存到一個數(shù)組變量中。SELECT*FROMjsqkINTOARRAYtempArray例7.40查詢"jsqk.DBF"地所有記錄,并將顯示結果保存到臨時表中。SELECT*FROMjsqkINTOCURSORtempTable例7.41查詢所有教師地編號,姓名與基本工資,按基本工資降序排列,并將顯示結果保存到表新教師情況表。SELECTjsqk.bhAS編號,xmAS姓名,;

jbgzAS基本工資;

FROM