VF課件(第4章)1分析

第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL

SQL結(jié)構(gòu)化查詢語(yǔ)言

使用查詢向?qū)Щ蛟O(shè)計(jì)器創(chuàng)建查詢文件標(biāo)準(zhǔn)函數(shù)視圖的創(chuàng)建與使用

結(jié)構(gòu)化查詢語(yǔ)言SQLSQL(StructuredQueryLanguage,結(jié)構(gòu)化查詢語(yǔ)言)SQL標(biāo)準(zhǔn)是于1986年10月美國(guó)國(guó)家標(biāo)準(zhǔn)局(ANSI)的數(shù)據(jù)庫(kù)委員會(huì)X3H2批準(zhǔn)作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)，隨之，國(guó)際化標(biāo)準(zhǔn)組織（ISO）也作出了同樣的決定.數(shù)據(jù)定義語(yǔ)言DDL、數(shù)據(jù)查詢語(yǔ)言DQL、數(shù)據(jù)操縱語(yǔ)言DML、數(shù)據(jù)控制語(yǔ)言DCL第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL一體化語(yǔ)言高度非過(guò)程化簡(jiǎn)潔實(shí)用兩種使用方法4.1SQL結(jié)構(gòu)化查詢語(yǔ)言

4.1.1SQL語(yǔ)言概述SQL包括數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制4個(gè)部分，是一種功能齊全的數(shù)據(jù)庫(kù)語(yǔ)言。特點(diǎn)是：

SQL功能

命令動(dòng)詞

——————————————————————

數(shù)據(jù)查詢

SELECT

數(shù)據(jù)定義

CREATE、DROP、ALTER

數(shù)據(jù)操作

INSERT、UPDATE、DELETE

數(shù)據(jù)控制

GRANT、REVOKE創(chuàng)建表結(jié)構(gòu)4.1.2

數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義的功能是定義數(shù)據(jù)庫(kù)的結(jié)構(gòu)，簡(jiǎn)稱DDL

CREATETABLE|DBF<表名>[FREE](<字段名1><類型>[(<寬度>[,小數(shù)位])][NULL|NOTNULL][CHECK<條件表達(dá)式1>[ERROR<提示信息1>]][DEFAULT<表達(dá)式1>][PRIMARYKEY|UNIQUE][REFERENCES表名2[TAG索引名1]][,<字段名2>…][,PRIMARYKEY表達(dá)式2TAG索引名2|,UNIQUE表達(dá)式3TAG索引名3][,FOREIGNKEY表達(dá)式4TAG索引名4REFERENCES表名3[TAG索引名5]][,CHECK<條件表達(dá)式2>[ERROR<提示信息2>]]第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1.2

數(shù)據(jù)定義語(yǔ)言

TABLE和DBF是等價(jià)的，前者是標(biāo)準(zhǔn)的SQL語(yǔ)言的關(guān)鍵詞，后者是VisualFoxPro的關(guān)鍵詞?？蛇x項(xiàng)FREE用于設(shè)定創(chuàng)建自由表?？蛇x項(xiàng)NULL或NOTNULL用于設(shè)定該字段是否允許保存為空值?？蛇x項(xiàng)CHECK用于設(shè)定字段的有效性規(guī)則，ERROR設(shè)定當(dāng)違反有效性規(guī)則時(shí)，顯示錯(cuò)誤信息的內(nèi)容?？蛇x項(xiàng)DEFAULT用于設(shè)定字段的默認(rèn)值?？蛇x項(xiàng)PRIMARYKEY或UNIQUE用于設(shè)定該字段是否是主關(guān)鍵字或唯一關(guān)鍵字，即建立主索引或候選索引?？蛇x項(xiàng)FOREIGNKEY和REFERENCES用于建立表的普通索引，并通過(guò)該索引建立與REFERENCES后面表（作為主表）的關(guān)系。建立一個(gè)名為“學(xué)生數(shù)據(jù)庫(kù)1”的數(shù)據(jù)庫(kù)，在其中建立一個(gè)“學(xué)生檔案表1”，表結(jié)構(gòu)和學(xué)生檔案表類似。CreateDatabase學(xué)生數(shù)據(jù)庫(kù)1Createtable學(xué)生檔案表1(學(xué)號(hào)C(6)

PrimaryKey,姓名C(6)

Unique,;性別C(2)

check性別＝“男”or性別＝”女”Error“性別是男或女”，;入學(xué)成績(jī)N(5,1)check入學(xué)成績(jī)>400default550,所在系C(10),;出生日期D，團(tuán)員否L)打開(kāi)”學(xué)生數(shù)據(jù)庫(kù)1“，創(chuàng)建“學(xué)生成績(jī)表1”，并通過(guò)“學(xué)號(hào)”字段與“學(xué)生檔案表1”建立關(guān)系。OpenDatabase學(xué)生數(shù)據(jù)庫(kù)1Createtable學(xué)生成績(jī)表1(學(xué)號(hào)C(6)

,課程編號(hào)C(6),平時(shí)成績(jī)N(5,1),;期末成績(jī)N(5,1),foreignkey學(xué)號(hào)Tag學(xué)號(hào)references學(xué)生檔案表1)修改表的結(jié)構(gòu)

【格式1】ALTERTABLE<表名1>ADD|ALTER[COLUMN]<字段名><字段類型>[(<寬度>[,小數(shù)位數(shù)])][NULL|NOTNULL][CHECK<條件表達(dá)式>][ERROR<提示信息>]][DEFAULT<表達(dá)式>][PRIMARYKEY|UNIQUE][REFERENCES表名2[TAG索引名]]功能：修改數(shù)據(jù)表的結(jié)構(gòu)，為其添加新字段或修改已有字段的類型、寬度、有效性規(guī)則、錯(cuò)誤信息、默認(rèn)值，定義主關(guān)鍵字和聯(lián)系等。說(shuō)明：該格式的命令不能修改字段名，不能刪除字段，也不能刪除已定義的規(guī)則等

1.為“學(xué)生成績(jī)表1”增加一個(gè)”總評(píng)成績(jī)“字段，并為該字段設(shè)置有效性規(guī)則2.修改“學(xué)生成績(jī)表1”的平時(shí)成績(jī)字段的寬度Altertable學(xué)生成績(jī)表1Alter平時(shí)成績(jī)N(4，1)check平時(shí)成績(jī)<＝100;Error“平時(shí)成績(jī)不能超過(guò)100分”Default80Altertable學(xué)生成績(jī)表1add總評(píng)成績(jī)N(4，1)check總評(píng)成績(jī)<＝100;Error“總評(píng)成績(jī)不能超過(guò)100分”Default90修改表的結(jié)構(gòu)

【格式2

】ALTERTABLE<表名>ALTER[COLUMN]<字段名>[NULL|NOTNULL][SETDEFAULT<表達(dá)式>][SETCHECK<條件表達(dá)式>[ERROR<提示信息>]][DROPDEFAULT][DROPCHECK]功能：主要用于定義、修改和刪除有效性規(guī)則和默認(rèn)值定義

說(shuō)明：該格式的命令不能修改字段名和刪除字段1.刪除“學(xué)生成績(jī)表1”的”總評(píng)成績(jī)”的有效性規(guī)則2.修改（定義）“學(xué)生成績(jī)表1”的”平時(shí)成績(jī)”的有效性規(guī)則3.修改“學(xué)生成績(jī)表1”的”總評(píng)成績(jī)”的默認(rèn)值4.刪除“學(xué)生成績(jī)表1”的”總評(píng)成績(jī)”的默認(rèn)值A(chǔ)ltertable學(xué)生成績(jī)表1alter總評(píng)成績(jī)DropcheckAltertable學(xué)生成績(jī)表1alter平時(shí)成績(jī)Setdefault70Setcheck‘平時(shí)成績(jī)<90’error’平時(shí)成績(jī)不能大于90’Altertable學(xué)生成績(jī)表1alter總評(píng)成績(jī)Setdefault100Altertable學(xué)生成績(jī)表1alter總評(píng)成績(jī)dropdefault修改表的結(jié)構(gòu)

【格式3

】ALTERTABLE<表名>[DROP[COLUMN]<字段名>][RENAMECOLUMN<原字段名>TO<新字段名>][SETCHECK<條件表達(dá)式>[ERROR<提示信息>]][DROPCHECK][ADDPRIMARYKEY<表達(dá)式>TAG<索引標(biāo)識(shí)>[FOR<邏輯表達(dá)式>]][DROPPRIMARYKEY][ADDUNIQUE<表達(dá)式>[TAG<索引標(biāo)識(shí)>[FOR<邏輯表達(dá)式>]]][DROPUNIQUETAG<索引標(biāo)識(shí)>][ADDFOREIGNKEY<表達(dá)式>TAG<索引標(biāo)識(shí)>[FOR<邏輯表達(dá)式>]REFERENCES<表名2>[TAG<索引標(biāo)識(shí)>]][DROPFOREIGNKEYTAG<索引標(biāo)識(shí)>]功能：該格式可以刪除字段、可以修改字段名、可以定義、修改和刪除表一級(jí)的有效性規(guī)則等1.把“學(xué)生檔案表1”中的“所在系”的名字改成“系別”2.為“學(xué)生檔案表1”設(shè)置“學(xué)號(hào)和姓名不能同時(shí)為空”的規(guī)則3.刪除“學(xué)生檔案表1”中的“團(tuán)員否”的字段4.將“學(xué)生檔案表1”中“學(xué)號(hào)＋課程編號(hào)“定義成主索引Altertable學(xué)生成績(jī)表1renamecolumn‘所在系’to‘系別‘Altertable學(xué)生成績(jī)表1setcheck學(xué)號(hào)<>nulland姓名<>nullAltertable學(xué)生成績(jī)表1drop團(tuán)員否Altertable學(xué)生成績(jī)表1addprimarykey‘學(xué)號(hào)+課程編號(hào)’tagxk第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL刪除數(shù)據(jù)表【格式】DROPTABLE<表名>功能：刪除指定的表插入記錄

4.1.3數(shù)據(jù)操縱命令

【格式1】INSERTINTO<表名>[(<字段名1>[,<字段名2>,...])]VALUES(<表達(dá)式1>[,<表達(dá)式2>,...])功能：向指定數(shù)據(jù)表的末端插入一條新記錄說(shuō)明：當(dāng)需要插入表中的所有字段的數(shù)據(jù)時(shí)，表名后面的字段名可以缺省，但插入數(shù)據(jù)的格式及順序必須與表的結(jié)構(gòu)完全吻合；若只需要插入表中某些字段的數(shù)據(jù)，就需要列出插入數(shù)據(jù)的字段名，相應(yīng)的表達(dá)式的數(shù)據(jù)位置應(yīng)與之相對(duì)應(yīng)。insertinto學(xué)生檔案表111;

(學(xué)號(hào),姓名,性別,入學(xué)成績(jī));values('00101','amy','女',560)【格式2】INSERTINTO<表名>FROMARRAY數(shù)組名|FROMMEMVAR功能：向指定數(shù)據(jù)表的末端插入一條新記錄，其值來(lái)自于數(shù)組或?qū)?yīng)的同名內(nèi)存變量說(shuō)明：數(shù)組中各元素與表中各字段順序?qū)?yīng)。如果數(shù)組中元素的數(shù)據(jù)類型與其對(duì)應(yīng)的字段類型不一致，則新記錄對(duì)應(yīng)的字段為空值；如果表中字段個(gè)數(shù)大于數(shù)組元素的個(gè)數(shù)，則多出的字段為空值

declarr(4)arr(1)='00102'arr(2)='tom'arr(3)='男'arr(4)=578insertinto學(xué)生檔案表111fromarrayarr更新記錄

4.1.3數(shù)據(jù)操縱命令

【格式】UPDATE<表名>SET<字段名1>=<表達(dá)式1>[,<字段名2>=<表達(dá)式2>……][WHERE<條件表達(dá)式>]功能：對(duì)表中的記錄進(jìn)行修改，實(shí)現(xiàn)記錄數(shù)據(jù)更新說(shuō)明：一般使用WHERE子句指定條件，以更新滿足條件的一些記錄的字段值，并且一次可以更新多個(gè)字段；如果不使用WHERE子句，則更新全部記錄1.將”學(xué)生檔案表”中”劉輝“同學(xué)的所在系改成”計(jì)算機(jī)系“2.將”學(xué)生成績(jī)表”中計(jì)算機(jī)系學(xué)生的”總評(píng)成績(jī)“等于”平時(shí)成績(jī)“的30％加上”期末成績(jī)“的70％。Update學(xué)生檔案表set系別=‘計(jì)算機(jī)系’where姓名=‘劉輝’Altertable學(xué)生成績(jī)表set總評(píng)成績(jī)=平時(shí)成績(jī)*0.3+期末成績(jī)*0.7where系別=‘計(jì)算機(jī)系’刪除記錄

4.1.3數(shù)據(jù)操縱命令

【格式】DELETEFROM<表名>[WHERE<條件表達(dá)式>]功能：從指定的表中，根據(jù)指定的條件邏輯刪除記錄說(shuō)明：使用FROM指定從哪個(gè)表中刪除，WHERE指定被刪除的記錄所滿足的條件，如果不使用WHERE子句，則刪除該表中的全部記錄。

該命令是加邏輯刪除標(biāo)記，并沒(méi)有從物理上刪除。只有執(zhí)行了PACK命令，邏輯刪除的記錄才真正地從物理上刪除。4.1.4SELECT數(shù)據(jù)查詢命令

查詢命令SELECT是SQL語(yǔ)言的核心，SELECT命令的基本結(jié)構(gòu)是SELECT…FROM…WHERE，它包含輸出字段、數(shù)據(jù)來(lái)源、條件查詢等基本子句。在這種固定格式中，可以不要WHERE，但是SELECT和FROM是必備的。例4-1查詢?nèi)w學(xué)生的學(xué)號(hào)、姓名和年齡。

程序清單如下：

SELECT學(xué)號(hào),姓名,年齡

FROMS例4-2查詢學(xué)生的全部信息。

程序清單如下：

SELECT*FROMS注意：用‘*’表示表的全部列名，而不必逐一列出。4.1.4SELECT數(shù)據(jù)查詢命令

基本查詢

【格式】SELECT[ALL|DISTINCT][別名.]<選項(xiàng)>

[[AS]<列名>]...]FROM[<數(shù)據(jù)庫(kù)名>!]<表名>DISTINCT和ALL選項(xiàng)的用法在SQL語(yǔ)句中，ALL表示輸出所有記錄，包括重復(fù)記錄；DISTINCT表示輸出無(wú)重復(fù)結(jié)果的記錄。系統(tǒng)默認(rèn)ALL選項(xiàng)。AS選項(xiàng)的用法在默認(rèn)情況下，在數(shù)據(jù)查詢結(jié)果中所顯示出來(lái)的列標(biāo)題就是在創(chuàng)建表時(shí)使用的字段名。但有時(shí)用AS選項(xiàng)給列或字段指定一個(gè)新的顯示別名。新列名應(yīng)遵循字段名的規(guī)定，中間不能有空格。使用時(shí)也可省略ASStudentSnumberSnameSsex

SdeptSdateSgrade001小紅女信息系19569002小剛男稅務(wù)系20589003小明男金融系185681。查詢學(xué)生數(shù)據(jù)庫(kù)中的”學(xué)生檔案表“的所有學(xué)生的”姓名“”學(xué)號(hào)“2。根據(jù)學(xué)生檔案表，列出所有系的系名3。輸出學(xué)生數(shù)據(jù)庫(kù)中的”學(xué)生檔案表“的所有學(xué)生的”姓名“”學(xué)號(hào)“‘年齡’，其中沒(méi)有年齡字段名，但有”出生日期“selectSnumber,SnamefromstudentselectdistinctSdeptfromstudent3。輸出學(xué)生數(shù)據(jù)庫(kù)中的”學(xué)生檔案表“的所有學(xué)生的”姓名“”學(xué)號(hào)“‘年齡’，其中沒(méi)有年齡字段名，但有”出生日期“selectSnumberas學(xué)號(hào),Snameas姓名,;year(date())-year(Sdate)as年齡fromstudent第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1.4SELECT數(shù)據(jù)查詢命令

條件查詢【格式】WHERE<條件表達(dá)式1>[AND|OR<條件表達(dá)式2>…]使用邏輯運(yùn)算符用邏輯操作符AND、OR和NOT來(lái)連接一系列的表達(dá)式，及優(yōu)化查詢過(guò)程。使用邏輯運(yùn)算符時(shí)，遵循下列原則使用AND返回滿足所有條件的行使用OR返回滿足任一條件的行使用NOT返回不滿足條件的行使用圓括號(hào)為表達(dá)式分組改變表達(dá)式求值順序增加表達(dá)式的可讀性使用邏輯運(yùn)算符（續(xù)）搜索條件的順序MicrosoftSQLServer2000首先求NOT表達(dá)式的值，然后是AND，最后是OR當(dāng)表達(dá)式中所有的操作符優(yōu)先級(jí)相同時(shí)，求值順序由左到右使用邏輯運(yùn)算符（續(xù)）SELECT學(xué)號(hào),姓名,年齡,

系別FROM學(xué)生信息表

WHERE(姓名LIKE‘張%’OR系別

=信息系)

AND(年齡>16)學(xué)號(hào)姓名年齡系別014張霞21外語(yǔ)029梁咪22信息062張曉天21金融示例

BETWEEN用法

【格式】<字段>BETWEEN<范圍始值>AND<范圍終值>功能：字段內(nèi)容在指定的范圍內(nèi)。IN用法

【格式IN(<數(shù)據(jù)項(xiàng)列表>)功能：字段內(nèi)容是結(jié)果集合或子查詢中的內(nèi)容1.根據(jù)學(xué)生檔案表，輸出計(jì)算機(jī)系入學(xué)成績(jī)不低于90分的學(xué)生的姓名，學(xué)號(hào)2.根據(jù)學(xué)生檔案表，輸出計(jì)算機(jī)系入學(xué)成績(jī)介于80分到90分之間的學(xué)生的姓名，學(xué)號(hào)3.根據(jù)學(xué)生檔案表，輸出計(jì)算機(jī)系和金融系學(xué)生的姓名，學(xué)號(hào)（兩種表示方法）selectSnumberas學(xué)號(hào),Snameas姓名fromstudentWhereScore>=90andsdept=‘計(jì)算機(jī)系’selectSnumberas學(xué)號(hào),Snameas姓名fromstudentWhere(Scorebetween80and90)andsdept=‘計(jì)算機(jī)系’selectSnumber,SnamefromstudentWheresdept=‘計(jì)算機(jī)系’orsdept=‘金融系’selectSnumber,SnamefromstudentWheresdeptin（‘計(jì)算機(jī)系’，‘金融系’）檢索一定范圍內(nèi)的值使用BETWEEN來(lái)查詢?cè)谝欢ǚ秶鷥?nèi)的值，使用BETWEEN時(shí)，注意SQLServer返回的結(jié)果集中，包含范圍內(nèi)的邊緣值盡量使用BETWEEN，而不用AND和比較操作符表示的表達(dá)式（>=xAND<=y）。要使結(jié)果集不包含邊緣值，則使用（>xAND<y）要返回不在指定區(qū)域的行時(shí)，使用NOTBETWEEN使用值列表作為搜索條件使用IN語(yǔ)句指定一個(gè)值的列表作為查詢條件。使用IN時(shí)，注意使用IN搜索條件和使用由OR操作符連起來(lái)的一系列比較操作符，其效果是等價(jià)的在搜索條件中不能包含NULL值，這將返回不可預(yù)測(cè)的結(jié)果集使用NOTIN搜索條件來(lái)返回值不在指定列表中的行。使用NOT會(huì)降低數(shù)據(jù)檢索速度4.1.4SELECT數(shù)據(jù)查詢命令

條件查詢LIKE及通配符

【格式】<字段>LIKE<字符表達(dá)式>功能：對(duì)字符型數(shù)據(jù)進(jìn)行字符串比較，提供兩種通配符，即下劃線“_”和百分號(hào)“%”，下劃線表示1個(gè)字符，百分號(hào)表示0個(gè)或多個(gè)字符

NULL用法

【格式】IS[NOT]NULL

功能：測(cè)試字段值是否為空值說(shuō)明：在查詢時(shí)用“字段名IS[NOT]NULL”的形式，而不能寫(xiě)成“字段名=NULL”或“字段名!=NULL”1。輸出學(xué)生數(shù)據(jù)庫(kù)中的“學(xué)生檔案表”的所有“李”姓學(xué)生的“姓名”“學(xué)號(hào)”。2。輸出學(xué)生數(shù)據(jù)庫(kù)中的”學(xué)生檔案表”的所有“李”姓，且名字只有一個(gè)字的學(xué)生的“姓名”“學(xué)號(hào)”。3。輸出”學(xué)生檔案表“的所有添加了簡(jiǎn)歷的學(xué)生的”姓名“”學(xué)號(hào)”。selectSnumber,SnamefromstudentWheresnamelike’李%’selectSnumber,SnamefromstudentWheresnamelike’李_’selectSnumber,SnamefromstudentWhereSresumeisnotnull使用比較運(yùn)算符操作符描述=等于>大于<小于>=大于或等于<=小于或等于<>不等于使用比較操作符來(lái)比較表中的值與指定的值或表達(dá)式的值使用比較運(yùn)算符（續(xù)）SELECTlastname,cityFROMstudentWHEREcountry='USA'lastnamecityDavolioSeattleFullerTacomaLeverlingKirklandPeacockRedmondCallahanSeattle示例簡(jiǎn)單的計(jì)算查詢

4.1.4SELECT數(shù)據(jù)查詢命令

用于計(jì)算查詢的函數(shù)很多，常用的函數(shù)及其功能如下：

聚合函數(shù)描述AVG計(jì)算表達(dá)式中平均值COUNT表達(dá)式中值的數(shù)目COUNT(*)所選擇的行的數(shù)目MAX表達(dá)式中的最大值MIN表達(dá)式中最小值SUM計(jì)算表達(dá)式中所有值的和STDEV樣本標(biāo)準(zhǔn)偏差STDEVP填充標(biāo)準(zhǔn)偏差VAR樣本方差VARP總體方差函數(shù)功能AVG(<字段名>)求一列數(shù)據(jù)的平均值SUM(<字段名>)給出一列數(shù)據(jù)的和COUNT(<*>)輸出查詢的行數(shù)MIN(<字段名>)給出列中的最小值MAX(<字段名>)給出列中的最大值1。輸出“學(xué)生檔案表”中學(xué)生入學(xué)成績(jī)的最高分，最低分，平均分，總分。2。統(tǒng)計(jì)“學(xué)生檔案表”中計(jì)算機(jī)系的女生的人數(shù)。selectmax(Score),min(Score),avg(Score),sum(Score)fromstudentselectcount(*)fromstudentwheresex=‘女’【1】在student表中,找出系的數(shù)目。

SELECTCOUNT(DISTINCTsdept)FROMstudent

說(shuō)明:

除非對(duì)關(guān)系中的元組個(gè)數(shù)進(jìn)行計(jì)數(shù)，一般應(yīng)用COUNT函數(shù)應(yīng)該使用DISTINCT。例如：

SELECTCOUNT(*)FROMstudent

將給出學(xué)生的記錄數(shù)是6個(gè)。

【2】在職工表中,求支付的工資總數(shù)。

SELECTSUM(工資)FROM職工結(jié)果是：6160。這個(gè)結(jié)果是職工關(guān)系中的工資值的總和，它并不管是否有重復(fù)值。這時(shí)若使用命令：

SELECTSUM(DISTINCT工資)FROM職工

將得出錯(cuò)誤的結(jié)果4910。原因是DISTINCT命令去掉重復(fù)值1250。

【5】在student中,求在計(jì)算機(jī)系的學(xué)生的最高分SELECTMAX(score)FROMstudentWHEREsdept=“計(jì)算機(jī)系"

結(jié)果是：1250

與MAX函數(shù)相對(duì)應(yīng)的是MIN函數(shù)(求最小值)。比如，求最低分可以有如下命令：

SELECTMIN(score)FROMstudentWHEREsdept=“計(jì)算機(jī)系"

多表查詢

4.1.4SELECT數(shù)據(jù)查詢命令

在多表之間進(jìn)行查詢就比較復(fù)雜，必須處理表和表間的等值連接關(guān)系。等值連接是按對(duì)應(yīng)字段的共同值將一個(gè)表中的記錄與另一個(gè)表中的記錄相連接。學(xué)號(hào)姓名性別出生日期所在系團(tuán)員否入學(xué)成績(jī)簡(jiǎn)歷照片030102王蕭麗女1981-12-23信息系F521.5MemoGen030221張宇輝男1982-05-16稅務(wù)系F543MemoGen030101李俊明男1981-09-09會(huì)計(jì)系T499MemoGen030105劉少波男1982-11-15會(huì)計(jì)系F520MemoGen030104白蘭蘭女1980-05-22稅務(wù)系F531MemoGen030219張曦女1981-09-03稅務(wù)系T545MemoGen030108陳志寒男1982-07-12信息系T526MemoGen030216錢宇龍男1982-05-08金融系F522MemoGenstudent課程編號(hào)課程名稱學(xué)時(shí)學(xué)分020469計(jì)算機(jī)原理543020489數(shù)據(jù)庫(kù)理論543010350VB543020499C語(yǔ)言543020301計(jì)算機(jī)導(dǎo)論543020490數(shù)據(jù)結(jié)構(gòu)724010304平面圖形設(shè)計(jì)362sc學(xué)號(hào)課程編號(hào)平時(shí)成績(jī)期末成績(jī)總評(píng)0301010203812685860301010203012687900301020204892575780301030204692788890301040103502467710301040204992994950301050203013081.587sg學(xué)號(hào)姓名性別出生日期所在系團(tuán)員否入學(xué)成績(jī)簡(jiǎn)歷照片030102王蕭麗女1981-12-23信息系F521.5MemoGen030221張宇輝男1982-05-16稅務(wù)系F543MemoGen030101李俊明男1981-09-09會(huì)計(jì)系T499MemoGen030105劉少波男1982-11-15會(huì)計(jì)系F520MemoGen030104白蘭蘭女1980-05-22稅務(wù)系F531MemoGen030219張曦女1981-09-03稅務(wù)系T545MemoGen030108陳志寒男1982-07-12信息系T526MemoGen030216錢宇龍男1982-05-08金融系F522MemoGenstudent課程編號(hào)課程名稱學(xué)時(shí)學(xué)分020469計(jì)算機(jī)原理543020489數(shù)據(jù)庫(kù)理論543010350VB543020499C語(yǔ)言543020301計(jì)算機(jī)導(dǎo)論543020490數(shù)據(jù)結(jié)構(gòu)724010304平面圖形設(shè)計(jì)362學(xué)號(hào)課程編號(hào)平時(shí)成績(jī)期末成績(jī)總評(píng)0301010203812685860301010203012687900301020204892575780301030204692788890301040103502467710301040204992994950301050203013081.5871.輸出所有學(xué)生的成績(jī)單，要求給出學(xué)號(hào)，姓名，課程編號(hào)，課程名稱，期末成績(jī)

selectStudent.學(xué)號(hào),姓名,課程編號(hào),課程名稱,總評(píng)fromstudent,sc,sgwhereStudent.學(xué)號(hào)=sg.學(xué)號(hào)andsg.課程編號(hào)=sc.課程編號(hào)selecta.學(xué)號(hào),a.姓名,b.課程編號(hào),b.期末成績(jī),c.課程名稱from學(xué)生檔案表a，成績(jī)表b，課程表cwherea.學(xué)號(hào)=b.學(xué)號(hào)andb.課程編號(hào)=c.課程編號(hào)第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL連接查詢

4.1.4SELECT數(shù)據(jù)查詢命令

連接查詢是一種基于多個(gè)表的查詢。連接分為內(nèi)部連接和外部連接。外部連接又分為左外連接、右外連接和全外連接

內(nèi)部連接（INNERJOIN）指將所有滿足連接條件的記錄都包含在查詢結(jié)果中。在VisualFoxPro中連接動(dòng)詞為INNERJOIN（等價(jià)于JOIN），連接條件用ON引出。1.輸出所有學(xué)生的成績(jī)單，要求給出學(xué)號(hào)，姓名，課程編號(hào)，課程名稱，期末成績(jī)selecta.學(xué)號(hào),a.姓名,b.課程編號(hào),b.期末成績(jī),c.課程名稱from學(xué)生檔案表a，成績(jī)表b，課程表cwherea.學(xué)號(hào)=b.學(xué)號(hào)andb.課程編號(hào)=c.課程編號(hào)selecta.學(xué)號(hào),a.姓名,b.課程編號(hào),b.期末成績(jī),c.課程名稱from學(xué)生檔案表a

innerjoin

成績(jī)表bona.學(xué)號(hào)=b.學(xué)號(hào)innerjoin

課程表conb.課程編號(hào)=c.課程編號(hào)SELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_idsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234結(jié)果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003示例連接查詢

4.1.4SELECT數(shù)據(jù)查詢命令

外部連接（OUTERJOIN）

左外連接（LeftOuterJoin）：包括了左表(FROM子句中的第一個(gè)表)中的全部記錄以及右表(FROM子句中的第二個(gè)表)中與之匹配的記錄。

右外連接（RightOuterJoin)：包括了右表中的全部記錄以及左表與之匹配的記錄。

全外部連接（FullOuterJoin）：包括了左表和右表所有的記錄。使用外聯(lián)接左（右）外聯(lián)接可以從兩個(gè)表中返回符合聯(lián)接條件的記錄，同時(shí)也將返回左（右）邊不符合聯(lián)接條件的記錄使用左（右）外聯(lián)接時(shí)，請(qǐng)注意不滿足聯(lián)接條件的記錄將顯示空值左聯(lián)接可以顯示第一個(gè)表中所有記錄右聯(lián)接可以顯示第二個(gè)表中所有記錄可以把LEFTOUTERJOIN或RIGHTOUTERJOIN簡(jiǎn)寫(xiě)為L(zhǎng)EFTJOIN或RIGHTJOIN1.從學(xué)生檔案表中輸出所有期末成績(jī)大于90分的學(xué)生的學(xué)號(hào)，姓名，課程編號(hào)，期末成績(jī)selecta.學(xué)號(hào),a.姓名,b.課程編號(hào),b.期末成績(jī)from學(xué)生檔案表a

leftouterjoin

成績(jī)表bona.學(xué)號(hào)=b.學(xué)號(hào)andb.期末成績(jī)>90SELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_idsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234結(jié)果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003SeanChaiNULLNULL示例使用全外部連接SELECTbuyer_name,qtyFROMbuyers

fullouterJOINsales結(jié)果buyer_nameAdamBarrAdamBarrAdamBarrAdamBarrqty1553711

AdamBarr1003

SeanChai15

SeanChai5

SeanChai37

SeanChai11

SeanChai1003

EvaCorets15

......salesbuyer_idprod_idqty114323151553711421003buyersbuyer_id1234buyer_nameAdamBarrSeanChaiEvaCoretsErinO’Melia示例聯(lián)接兩個(gè)以上的表聯(lián)接任意數(shù)目的表都有可能，通過(guò)使用共同擁有的字段，任何一個(gè)表都可以和其他表聯(lián)接自聯(lián)接（Self-Join）使用自聯(lián)接，可以查詢一個(gè)表中各記錄之間的關(guān)系使用自聯(lián)接時(shí)，應(yīng)注意

引用表的兩份副本時(shí)，必須使用表的別名生成自聯(lián)接時(shí)，表中每一行都和自身比較一下，并生成重復(fù)的記錄，使用WHERE子句來(lái)消除這些重復(fù)記錄1.列出經(jīng)理極其職員的清單雇員號(hào)雇員姓名經(jīng)理E3趙勇E4錢潮E3E6孫潔E3E8李路E6selecta.雇員姓名，‘領(lǐng)導(dǎo)’，b.雇員姓名from雇員表a,雇員表bWherea.雇員號(hào)=b.經(jīng)理趙勇領(lǐng)導(dǎo)錢潮趙勇領(lǐng)導(dǎo)孫潔孫潔領(lǐng)導(dǎo)李路排序查詢

4.1.4SELECT數(shù)據(jù)查詢命令

ORDERBY子句

【格式】ORDERBY<排序項(xiàng)1>[ASC|DESC][,<排序項(xiàng)2>[ASC|DESC]...]

功能：缺省值為升序排列，用ASC表示。降序排列，用DESC表示。如果希望對(duì)多字段進(jìn)行排序，要在ORDERBY后面的字段名與字段名之間加上逗號(hào)SELECTproductid,productname,categoryid,Unitprice

FROMproductsORDERBYcategoryid,unitpriceDESCproductidproductnamecategoryidunitprice38CotedeBlaye1263.500043IpohCoffee146.00002Chang119.0000…………63Vegie-spread243.90008NorthwoodsCranberrySauce240.000061Siropd'érable228.5000…………示例1。輸出所有學(xué)生的入學(xué)成績(jī)單，按“所在系”列出學(xué)號(hào)，姓名，所在系，如果“所在系”相同，再按“期末成績(jī)”由高到低排序。selecta.學(xué)號(hào),a.姓名,a.所在系from學(xué)生檔案表aorderbya.所在系，入學(xué)成績(jī)desc排序查詢

4.1.4SELECT數(shù)據(jù)查詢命令

TOP用法

【格式】TOP<數(shù)值表達(dá)式>[PERCENT]

說(shuō)明：TOP短語(yǔ)要與ORDERBY短語(yǔ)同時(shí)使用才有效。

當(dāng)不使用PERCENT時(shí)，<數(shù)值表達(dá)式>是1至32767之間的整數(shù)，說(shuō)明顯示前幾個(gè)記錄；當(dāng)使用PERCENT時(shí)，<數(shù)值表達(dá)式>是0.01至99.99間的實(shí)數(shù)，說(shuō)明顯示結(jié)果中前百分之幾的記錄。

TOP短語(yǔ)不在相同值間作選擇。例如指定輸出前5項(xiàng)的記錄，但第5項(xiàng)記錄和第6項(xiàng)值相同，則查詢輸出6項(xiàng)記錄。1。輸出學(xué)生檔案表中“入學(xué)成績(jī)”最高的前3個(gè)學(xué)生的姓名，所在系，入學(xué)成績(jī)selecttop3學(xué)號(hào),姓名,所在系from學(xué)生檔案表orderby入學(xué)成績(jī)desc限制返回的行數(shù)

SELECTTOP2*

FROMstudentorderbyScore

SELECTTOP20PERCENT*

FROMstudentorderbysage分組查詢

4.1.4SELECT數(shù)據(jù)查詢命令

GROUPBY子句

【格式】GROUPBY<分組列名1>[,<分組列名2>...]

功能：GROUPBY子句可以將查詢結(jié)果按指定字段值在行的方向上進(jìn)行分組。GROUPBY子句與統(tǒng)計(jì)函數(shù)一起完成對(duì)每一個(gè)組生成一行和一個(gè)匯總值的功能，即每組產(chǎn)生一個(gè)匯總記錄。1、輸出學(xué)生檔案表中所在系，各系學(xué)生的平均入學(xué)成績(jī)和各系的人數(shù)select所在系，avg（入學(xué)成績(jī)）as‘平均成績(jī)’，count（*）as‘人數(shù)’from學(xué)生檔案表groupby所在系使用GROUPBY子句SELECTproductid,orderid

,quantityFROMorderhist

GOSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid

GOproductidtotal_quantity115235345productidorderidquantity11511102110222531153230productidtotal_quantity235只對(duì)滿足WHERE子句的行分組SELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductid

GO示例1示例2分組查詢

4.1.4SELECT數(shù)據(jù)查詢命令

HAVING用法

功能：HAVING子句用來(lái)指定每一分組內(nèi)應(yīng)滿足的條件。在使用HAVING子句時(shí)，它應(yīng)該與GROUPBY子句連用，并放在GROUPBY子句之后，不可以單獨(dú)使用。說(shuō)明：HAVING子句和WHERE子句不矛盾，在查詢中先用WHERE子句限定元組，然后進(jìn)行分組，最后再用HAVING子句限定分組。

1。輸出學(xué)生檔案表中學(xué)生人數(shù)至少有5人的系名和人數(shù)。2。輸出學(xué)生檔案表中團(tuán)員人數(shù)超過(guò)3人的系名和人數(shù)。select所在系，count（*）as‘人數(shù)’from學(xué)生檔案表groupby所在系having人數(shù)>=5select所在系，count（*）as‘人數(shù)’from學(xué)生檔案表where團(tuán)員否=.t.groupby所在系having人數(shù)>3聯(lián)合使用GROUPBY子句和HAVING子句（續(xù)）SELECTproductid,orderid,quantityFROMorderhist

SELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUP