第5章結(jié)構(gòu)化查詢語言SQL

5.1SQL語言概述 5.2SQL的數(shù)據(jù)定義功能5.3SQL的數(shù)據(jù)操縱功能5.4數(shù)據(jù)查詢5.5小型案例實(shí)訓(xùn) 第5章結(jié)構(gòu)化查詢語言SQL學(xué)習(xí)目的與要求●掌握SQL的定義數(shù)據(jù)的基本命令及格式；●掌握SQL對(duì)數(shù)據(jù)的操縱；●掌握SQL的綜合數(shù)據(jù)查詢功能。5.1SQL語言概述1．綜合統(tǒng)一2．高度非過程化3．面向集合的操作方式4．以同一種語法結(jié)構(gòu)提供兩種使用方式5．語言簡(jiǎn)潔、易學(xué)易用SQL語言的特點(diǎn):5.2SQL的數(shù)據(jù)定義功能格式：CREATETABLE<表名>（<字段名1><數(shù)據(jù)類型>[（<寬度>[，<小數(shù)位數(shù)>]）][，<字段名2>…]）功能：定義（也稱創(chuàng)建）一個(gè)表。說明：(1)<表名>是要?jiǎng)?chuàng)建的表文件名。(2)TABLE也可以用DBF代替。5.2.2建立表結(jié)構(gòu)5【例】創(chuàng)建一個(gè)表STUD（學(xué)生信息表），它由以下字段組成：學(xué)號(hào)（C，10）；姓名（C，8）；性別（C，2）；班級(jí)名（C，10）；系別代號(hào)（C，2）；地址（C，50）；出生日期（D）；是否團(tuán)員（L）；備注（M）。CREATETABLESTUD(學(xué)號(hào)C(10），姓名C（10），性別C（2），班級(jí)名C（10）,系別代號(hào)C（2）,地址C（50）,出生日期D,是否團(tuán)員L,備注M,照片G）LISTSTRUCTURE【例】創(chuàng)建一個(gè)表SC（課程成績(jī)表），它由以下字段組成：學(xué)號(hào)（C，10）；課程號(hào)（C，2）。CREATETABLESC（學(xué)號(hào)C（10），課程號(hào)C（2））LISTSTRUCTURE5.2.2建立表結(jié)構(gòu)5.2SQL的數(shù)據(jù)定義功能格式：ALTERTABLE<表名>[ADD<新列定義><數(shù)據(jù)類型>][ALTER<列名><數(shù)據(jù)類型>][DROP<字段名>]功能：修改表的結(jié)構(gòu)?！纠吭趯W(xué)生成績(jī)表中增加一個(gè)總成績(jī)字段，總成績(jī)N（5,1）ALTERTABLE學(xué)生成績(jī)ADD總成績(jī)N(5,1)LISTSTRUCTURE【例】將學(xué)生情況表的”姓名“字段的寬度改為8ALTERTABLE學(xué)生情況ALTER姓名C(8)LISTSTRUCTURE5.2.3修改數(shù)據(jù)表7【例】刪除學(xué)生情況表中的”簡(jiǎn)歷“字段。Altertable學(xué)生情況drop簡(jiǎn)歷Modifystructure

注：關(guān)鍵字alter是修改字段；add關(guān)鍵字是增加字段；drop關(guān)鍵字是刪除字段。

5.2SQL的數(shù)據(jù)定義功能5.2.3修改數(shù)據(jù)表8

數(shù)據(jù)操縱語言是完成數(shù)據(jù)操作的命令，一般分為兩種類型的數(shù)據(jù)操縱，它們統(tǒng)稱為DML：●數(shù)據(jù)檢索（常稱為查詢）：尋找所需的具體數(shù)據(jù)?！駭?shù)據(jù)修改：添加、刪除和改變數(shù)據(jù)。數(shù)據(jù)操縱語言一般由INSERT（插入）、DELETE（刪除）、UPDATE（更新），SELETE（檢索，又稱查詢）等組成，由于SELETE比較特殊，所以一般又將它以查詢（檢索）語言單獨(dú)出現(xiàn)。5.3SQL的數(shù)據(jù)操縱功能9【格式】INSERTINTO<表名>[(<字段名表>)]VALUES（<表達(dá)式表>）【功能】在指定的表文件末尾追加一條記錄。用表達(dá)式表中的各表達(dá)式值賦值給<字段名表>中的相應(yīng)的各字段?！菊f明】1、插入的字段名一定要與VALUES后面對(duì)應(yīng)表達(dá)式的數(shù)據(jù)類型一致。2、如果插入的是表中依次完整的一條記錄，則<字段名表>可以省略掉，否則必須指定。5.3SQL的數(shù)據(jù)操縱功能

5.3.1插入記錄103、<表達(dá)式表>：指定要追加的記錄各個(gè)字段的值，可以是具體值也可以是表達(dá)式。【例】在學(xué)生情況.DBF文件的末尾追加一條記錄。INSERTINTO學(xué)生情況(學(xué)號(hào)，姓名，性別，出生日期，畢業(yè)否，專業(yè)，助學(xué)金)VALUES(“41110”，“李建國”，“男”，{^1984-10-14}，.f.，’計(jì)算機(jī)’，40）因?yàn)樽芳拥氖且粭l完整的記錄，所以字段名的指定可以省略INSERTINTO學(xué)生情況VALUES(“41110”，“李建國”，“男”，{^1984-10-14}，.f.，’計(jì)算機(jī)’，40）5.3SQL的數(shù)據(jù)操縱功能

5.3.1插入記錄11【例】在學(xué)生情況.dbf末尾追加一條新記錄

Insertinto學(xué)生情況(學(xué)號(hào),姓名,性別,畢業(yè)否,專業(yè),助學(xué)金)values(‘50008’,’王勇’,’男’,.T.,’數(shù)學(xué)’,50)說明：因?yàn)樽芳拥牟皇且粭l完整的記錄，因此字段名的指定不可省略。5.3SQL的數(shù)據(jù)操縱功能

5.3.1插入記錄5.3SQL的數(shù)據(jù)操縱功能格式：DELETEFROM<表名>[WHERE<條件表達(dá)式>]功能：從指定表中，根據(jù)指定的條件邏輯刪除記錄。說明：如果要物理刪除記錄，在該命令后還必須用PACK命令。使用RECALL命令可以恢復(fù)邏輯刪除的記錄。【實(shí)例】將學(xué)生情況.dbf中所有男生的記錄邏輯刪除。DELETEFROM學(xué)生情況WHERE性別="男"

提示： ●DELETE只對(duì)表中的記錄做邏輯刪除，即添加刪除標(biāo)記，物理上并沒有刪除。

●如果想徹底刪除（物理刪除）必須與PACK命令配合使用。5.3.2刪除記錄5.3SQL的數(shù)據(jù)操縱功能格式：UPDATE<數(shù)據(jù)表>SET<字段名l>=<表達(dá)式l>[，<字段名2>=<表達(dá)式2>…][WHERE<邏輯表達(dá)式>]【實(shí)例】將學(xué)生情況.dbf中，所有專業(yè)為工業(yè)會(huì)計(jì)的學(xué)生助學(xué)金增加50。

UPDATE學(xué)生情況SET助學(xué)金=助學(xué)金+50;WHERE專業(yè)=“工業(yè)會(huì)計(jì)"

提示： ●一次可以更新一個(gè)字段，也可以更新多個(gè)字段。

●作為更新條件的字段，不可以更新。5.3.3更新記錄145.4數(shù)據(jù)查詢

SQL數(shù)據(jù)查詢語句各子句的功能SELECT子句：指定查詢中要輸出的字段。如果要輸出全部字段用*。FROM子句：指定查詢的數(shù)據(jù)源，多個(gè)數(shù)據(jù)源之間用逗號(hào)隔開。WHERE子句：指定查詢要滿足的條件，條件中數(shù)據(jù)類型一定要一致。GROUPBY子句：指定查詢需要的分組字段。ORDERBY子句：指定查詢需要的排序字段，升序用ASC，系統(tǒng)默認(rèn)是升序，所以可以省去ASC，但是如果是降序必須指定DESC。INTO子句：指定查詢的去向15【格式】SELECT[*|DISTINCT]<字段列表>|<函數(shù)>FROM<表>【功能】無條件查詢。【說明】*：表示顯示全部查詢記錄，包括重復(fù)記錄。DISTINCT：表示顯示時(shí)去除重復(fù)值，顯示無重復(fù)結(jié)果的記錄。

5.4.1簡(jiǎn)單查詢16【例12-1】顯示學(xué)生情況表中學(xué)號(hào)，姓名和專業(yè)字段的內(nèi)容SELECT學(xué)號(hào),姓名,專業(yè)FROM學(xué)生情況【例12-2】顯示學(xué)生情況.DBF中的所有記錄。SELECT*FROM學(xué)生情況命令中的*表示輸出顯示所有的字段，數(shù)據(jù)來源是學(xué)生情況表，表中的內(nèi)容以瀏覽方式顯示。【例12-3】查詢學(xué)生情況表中專業(yè)數(shù)目。SELECTCOUNT（DISTINCT專業(yè)）AS專業(yè)個(gè)數(shù)FROM學(xué)生情況5.4.1簡(jiǎn)單查詢17【例12-4】顯示學(xué)生成績(jī)表中的所有記錄，并將成績(jī)乘以0.7。SELECT學(xué)號(hào)，姓名，外語*0.7，計(jì)算機(jī)*0.7FROM學(xué)生成績(jī)SELECT學(xué)號(hào)，課程號(hào)，外語*0.7AS外語成績(jī)，計(jì)算機(jī)*0.7AS計(jì)算機(jī)成績(jī)FROM學(xué)生成績(jī)5.4.1簡(jiǎn)單查詢18【格式】SELECT[*|DISTINCT]<字段列表>FROM<表>[WHERE<條件表達(dá)式>]【功能】從一個(gè)表中查詢滿足條件的數(shù)據(jù)。【說明】<條件表達(dá)式>的格式可以是教材表5-3中的運(yùn)算符5.4.2條件（WHERE）查詢19【例12-5】顯示學(xué)生情況表中所有男生記錄的學(xué)號(hào)，姓名和性別字段值。SELECT學(xué)號(hào)，姓名，性別;FROM學(xué)生情況WHERE性別="男"【例12-6】顯示學(xué)生成績(jī)表中計(jì)算機(jī)成績(jī)?cè)?0—90分之間的學(xué)生的學(xué)號(hào)，姓名，計(jì)算機(jī)成績(jī)SELECT學(xué)號(hào)，姓名，計(jì)算機(jī);FROM學(xué)生成績(jī);WHERE計(jì)算機(jī)BETWEEN80AND905.4.2條件（WHERE）查詢20注意：BETWEENAND表示在……之間【例12-7】顯示學(xué)生情況表中姓王的學(xué)生的學(xué)號(hào)，姓名，出生日期。SELECT學(xué)號(hào)，姓名，出生日期;FROM學(xué)生情況;WHERE姓名LIKE“王%”注意：在SQL語句中通配符%代表任意個(gè)任意字符?；蛘撸簊elect學(xué)號(hào),姓名,出生日期;From學(xué)生情況whereleft(姓名,2)=‘王’5.4.2條件（WHERE）查詢215.4.3SQL的復(fù)雜查詢1．連接查詢

【說明】在一個(gè)數(shù)據(jù)庫中的多個(gè)表之間一般都存在著某些聯(lián)系，在一個(gè)查詢語句中同時(shí)涉及到兩個(gè)或兩個(gè)以上的表時(shí)，這種查詢稱之為連接查詢（也稱為多表查詢）。在多表之間查詢必須處理表與表之間的連接關(guān)系。

SELECT[*|DISTINCT]<字段列表>；

FROM<表1>[，表2…..]；

WHERE<條件表達(dá)式>22【例12-8】查詢并顯示各個(gè)學(xué)生的姓名，專業(yè)，各科成績(jī)。

SELECT學(xué)生情況.姓名,專業(yè),外語,計(jì)算機(jī);FROM學(xué)生情況,學(xué)生成績(jī);WHERE學(xué)生情況.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)或者SELECT學(xué)生情況.姓名,專業(yè),外語,計(jì)算機(jī);FROM學(xué)生情況JOIN學(xué)生成績(jī)ON學(xué)生情況.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)注：當(dāng)數(shù)據(jù)源中多個(gè)表具有同一字段時(shí)，要指定該字段必須加以限制！1．連接查詢23【例12-9】查詢并顯示計(jì)算機(jī)專業(yè)學(xué)生外語成績(jī)的情況。SELECT學(xué)生情況.學(xué)號(hào),學(xué)生情況.姓名,外語;FROM學(xué)生情況,學(xué)生成績(jī);WHERE學(xué)生情況.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)and專業(yè)=‘計(jì)算機(jī)’注意：在FROM子句中可以為表指定一個(gè)別名。格式：FROM<表名><別名>【例12-10】SELECTb.姓名，計(jì)算機(jī)，專業(yè);FROM學(xué)生情況aJOIN學(xué)生成績(jī)bONa.學(xué)號(hào)=b.學(xué)號(hào)

1．連接查詢24在SQL語句中，一個(gè)SELECT－FROM－WHERE語句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。一般情況是要查詢輸出的字段來自于一個(gè)表，而查詢滿足的條件則來自于另一個(gè)表，這時(shí)就要用嵌套查詢。有時(shí)也可以用連接查詢做。【例12-11】顯示計(jì)算機(jī)成績(jī)大于80分的學(xué)生的學(xué)號(hào)，姓名和專業(yè)。

SELECT學(xué)號(hào),姓名,專業(yè);FROM學(xué)生情況;WHERE學(xué)號(hào)IN(SELECT學(xué)號(hào)FROM學(xué)生成績(jī)WHERE計(jì)算機(jī)>80)2．嵌套查詢255.4.4．對(duì)查詢結(jié)果排序

如果沒有指定查詢結(jié)果的顯示順序，將按其最方便的順序（通常是元組在表中的先后順序）輸出查詢結(jié)果。用戶也可以用ORDERBY子句指定按照一個(gè)或多個(gè)屬性列的升序（ASC）或降序(DESC)重新排列查詢結(jié)果，其中升序ASC為缺省值?！纠?2-12】查詢所有學(xué)生信息，并按出生日期排序。

SELECT*FROM學(xué)生情況ORDERBY出生日期

26【例12-13】按專業(yè)降序，姓名升序顯示學(xué)生情況表中的學(xué)號(hào)、姓名、專業(yè)。

SELECT學(xué)號(hào),姓名,專業(yè);FROM學(xué)生情況;ORDERBY專業(yè)DESC，姓名5.4.4．對(duì)查詢結(jié)果排序275.4.5．使用集函數(shù)

主要集函數(shù)包括：

COUNT([DISTINCT|ALL]*|列名)

統(tǒng)計(jì)元組個(gè)數(shù)。

SUM([DISTINCT|ALL]<列名>)

計(jì)算一列值的總和（此列必須是數(shù)值型）

AVG（[DISTINCT|ALL]<列名>）計(jì)算一列值的平均值（此列必須是數(shù)值型）

MAX（[DISTINCT|ALL]<列名>）計(jì)算一列值的最大值

MIN（[DISTINCT|ALL]<列名>）計(jì)算一列值的最小值28

【例12-14】查詢學(xué)生人數(shù)。

SELECTCOUNT（學(xué)號(hào)）AS人數(shù);FROM學(xué)生情況

【例12-15】查詢專業(yè)為計(jì)算機(jī)的學(xué)生計(jì)算機(jī)平均成績(jī)

SELECT專業(yè)，AVG（計(jì)算機(jī)）AS平均成績(jī);FROM學(xué)生情況，學(xué)生成績(jī);WHERE學(xué)生情況.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)AND專業(yè)=‘計(jì)算機(jī)’

5.4.5．使用集函數(shù)

29【格式】SELECT分組表達(dá)式，集函數(shù);FROM表名;WHERE<條件>;GROUPBY<分組表達(dá)式>…[HAVING<過濾條件>]

GROUPBY<分組表達(dá)式>：表示分組查詢，若查詢到的數(shù)據(jù)里有多個(gè)記錄的指定字段的值相同，只取一條記錄作為查詢結(jié)果。HAVING<過濾條件>：指定分組進(jìn)一步滿足的條件，只能跟在GROUPBY后面使用，不可獨(dú)立使用。5.4.6．分組與計(jì)算查詢30【例12-16】顯示各專業(yè)總?cè)藬?shù)。

SELECT專業(yè),COUNT(*)AS人數(shù);FROM學(xué)生情況;GROUPBY專業(yè)【例12-17】統(tǒng)計(jì)男女生人數(shù)

SELECT性別,COUNT(學(xué)號(hào))AS人數(shù);FROM學(xué)生情況;GROUPBY性別5.4.6．分組與計(jì)算查詢31【例12-18】統(tǒng)計(jì)每個(gè)專業(yè)學(xué)生計(jì)算機(jī)成績(jī)的平均分SELECT學(xué)生情況.學(xué)號(hào),學(xué)生情況.姓名,avg(計(jì)算機(jī))as平均成績(jī);FROM學(xué)生情況,學(xué)生成績(jī);WHERE學(xué)生情況.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào);GROUPB