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

第九章結(jié)構(gòu)化查詢語句(SQL)本章要點9.1SQL語言的基本概述9.2數(shù)據(jù)定義9.3數(shù)據(jù)操縱9.4數(shù)據(jù)管理9.5SQL中的數(shù)據(jù)查詢語句1．綜合統(tǒng)一

SQL語言集數(shù)據(jù)定義（DDL）、數(shù)據(jù)操縱（DML）、數(shù)據(jù)管理（DCL）的功能于一體，語言風(fēng)格統(tǒng)一，可以獨立完成數(shù)據(jù)庫的全部操作，包括定義關(guān)系模式、錄入數(shù)據(jù)及建立數(shù)據(jù)庫、查詢、更新、維護數(shù)據(jù)、數(shù)據(jù)庫的重新構(gòu)造、數(shù)據(jù)庫安全性等一系列操作的要求，為數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)者提供了良好的環(huán)境。2．高度非過程化

用戶只需提出“做什么”，不必指明“怎么做”3．面向集合的操作方式4．以同一種語法結(jié)構(gòu)提供兩種使用方式:交互式或程序中5．語言簡潔，易學(xué)易用9.1.1SQL語言的特點9.1.2SQL語言的基本概念

SQL語言支持關(guān)系型數(shù)據(jù)庫的三級模式結(jié)構(gòu)。其中外模式對應(yīng)于視圖（View）和部分基本表（BaseTable），模式對應(yīng)于基本表，內(nèi)模式對應(yīng)于存儲文件?；颈硎潜旧愍毩⒋嬖诘谋?，在SQL語言中一個關(guān)系對應(yīng)一個表。一些基本表對應(yīng)一個存儲文件，一個表可以帶若干索引，索引存放在存儲文件中。存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系型數(shù)據(jù)庫的內(nèi)模式。而存儲文件的物理文件結(jié)構(gòu)是任意的。視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨立存儲在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫只存放在視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個虛表。9.2數(shù)據(jù)定義數(shù)據(jù)定義語言DDL用于執(zhí)行數(shù)據(jù)定義的操作，如創(chuàng)建或刪除表、索引和視圖之類的對象。由CREATE、DROP、ALTER命令組成，完成數(shù)據(jù)庫對象的建立（CREATE）、刪除（DROP）和修改（ALTER）。9.2.1定義（創(chuàng)建）表【格式】CREATETABLE[FREE]<表名>（<字段名1><數(shù)據(jù)類型>[（<寬度>[，<小數(shù)位數(shù)>]）][域完整性約束][NULL|NOTNULL][，<字段名2>…]）【功能】定義（也稱創(chuàng)建）一個表。示例【例9-1】創(chuàng)建一個自由表表STUD（學(xué)生信息表），它由以下字段組成：學(xué)號（C，10），姓名（C，8），性別（C，2)CREATETABLESTUDFREE（學(xué)號C（10），姓名C（10），性別C（2）LISTSTRUCTURE【例9-2】創(chuàng)建一個數(shù)據(jù)庫表SC（課程成績表），它由以下字段組成：學(xué)號（C，10）；課程號（C，2）。Opendatabase課程成績CREATETABLESC（學(xué)號C（10），課程號C（2）NULL）LISTSTRUCTURE9.2.2修改表【格式1】ALTERTABLE<表名>ADD<新字段名><數(shù)據(jù)類型>[（<寬度>[，<小數(shù)位數(shù)>]）]【格式2】ALTERTABLE<表名>ALTER<字段名><數(shù)據(jù)類型>[（<寬度>[，<小數(shù)位數(shù)>]）][有效性規(guī)則]【格式3】ALTERTABLE<表名>DROP<字段名>【功能】修改表結(jié)構(gòu)?！纠?-3】在課程成績表SC中，增加一個成績字段變量（N，3）。ALTERTABLESCADD成績N(3)ALTERTABLESCALTER成績N(4)LISTSTRUCTURE【例9-4】在課程成績表SC中，增加一個成績字段變量（N，3）。OPENDATABASE課程成績ALTERTABLESCALTER成績N(4)CHECK；成績>520ERROR“成績大于520分”LISTSTRUCTUREALTERTABLESCALTER成績DROPCHECKALTERTABLESCALTER成績SETDEFAULT521ALTERTABLESCDROPCOLUMN成績【格式】DROPDATABASE<數(shù)據(jù)庫名>【功能】刪除指定數(shù)據(jù)庫的結(jié)構(gòu)和數(shù)據(jù)?！菊f明】謹(jǐn)慎使用。9.2.4刪除表【格式】DROPTABLE<表名>【功能】刪除指定表的結(jié)構(gòu)和內(nèi)容（包括在此表上建立的索引）?！菊f明】如果只是想刪除一個表中的所有記錄，則應(yīng)使用DELETE語句。9.2.3刪除數(shù)據(jù)庫數(shù)據(jù)操縱語言是完成數(shù)據(jù)操作的命令，一般分為兩種類型的數(shù)據(jù)操縱，它們統(tǒng)稱為DML：●數(shù)據(jù)檢索（常稱為查詢）：尋找所需的具體數(shù)據(jù)?！駭?shù)據(jù)修改：添加、刪除和改變數(shù)據(jù)。數(shù)據(jù)操縱語言一般由INSERT（插入）、DELETE（刪除）、UPDATE（更新），SELECT（檢索，又稱查詢）等組成，由于SELECT比較特殊，所以一般又將它以查詢（檢索）語言單獨出現(xiàn)。9.3數(shù)據(jù)操縱【格式1】INSERTINTO<表名>[(<字段名表>)]VALUES（<表達(dá)式表>）【格式2】INSERTINTO<表名>FROMARRAY<數(shù)組名>|FROMMEMVAR【功能】在指定的表文件末尾追加一條記錄。格式1用表達(dá)式表中的各表達(dá)式值賦值給<字段名表>中的相應(yīng)的各字段。格式2用數(shù)組或內(nèi)存變量的值賦值給表文件中各字段?！菊f明】如果某些字段名在INTO子句中沒有出現(xiàn)，則新記錄在這些字段名上將取空值（或默認(rèn)值）。但必須注意的是，在表定義說明了NOTNULL的字段名不能取空值。9.3.1插入記錄<字段名表>：指定表文件中的字段，缺省時，按表文件字段的順序依次賦值。<表達(dá)式表>：指定要追加的記錄各個字段的值?！纠?-5】在表文件STUD的末尾追加三條記錄。***用表達(dá)式方式追加第一條記錄***INSERTINTOSTUD(學(xué)號，姓名)VALUES(“011110”，“李建國”)DIMENSIONDATA(8)DATA（1）="011103"DATA（2）="李寧"DATA（3）="女"DATA（4）="電0134"DATA（5）="02"DATA（6）="江西九江"DATA（7）=｛^1985/06/15｝DATA（8）=.F.INSERTINTOSTUDFROMARRAYDATA***用數(shù)組方式追加第二條記錄***學(xué)號="011202"姓名="趙娜"性別="女"班級名="英0112"系別代號="03"地址="廣西南寧"出生日期={^1984/02/21}是否團員=.F.INSERTINTOSTUDFROMMEMVARLIST***用內(nèi)存變量方式追加第三條記錄***【格式】UPDATE<表文件名>SET<字段名1>=<表達(dá)式>[，<字段名2>=<表達(dá)式>…][WHERE<條件>]【功能】更新指定表文件中滿足WHERE條件子句的數(shù)據(jù)。其中SET子句用于指定列和修改的值，WHERE用于指定更新的行，如果省略WHERE子句，則表示表中所有行。【說明】更新操作又稱為修改操作。【例9-6】將成績表（SC）中，所有課程號為02的成績各加5分。UPDATESCSET成績=成績+5;WHERE課程號="02"9.3.2更新記錄命令9.3.3刪除記錄【格式】DELETEFROM<表名>WHERE<表達(dá)式>【功能】從指定的表中刪除滿足WHERE子句條件的所有記錄。如果在DELETE語句中沒有WHERE子句，則該表中的所有記錄都將被刪除?！菊f明】這里的刪除是邏輯刪除，即在刪除的記錄前加上一個刪除標(biāo)記“*”。【例9-7】刪除STUD表中所有性別為男的記錄。DELETEFROMSTUDWHERE性別=“男”數(shù)據(jù)管理（也稱數(shù)據(jù)控制）語言是用來管理（或控制）用戶的訪問權(quán)限的。由GRANT（授權(quán)）、REVOTE（回收）命令組成。而VisualFoxPro6沒有這種權(quán)限管理。9.4數(shù)據(jù)管理9.5SQL中的數(shù)據(jù)查詢語句數(shù)據(jù)庫中的數(shù)據(jù)很多時侯是為了查詢的，因此，數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。而在SQL語言中，查詢語言中有一條查詢命令，即SELECT語句。9.5.1基本查詢語句【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>【功能】無條件查詢?！菊f明】ALL：表示顯示全部查詢記錄，包括重復(fù)記錄。DISTINCT：表示顯示無重復(fù)結(jié)果的記錄。9.5.2帶條件（WHERE）的查詢語句【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>[WHERE<條件表達(dá)式>]【功能】從一個表中查詢滿足條件的數(shù)據(jù)?！纠?】查詢學(xué)生的基本信息。

SELECT*FROM學(xué)生【例2】查詢學(xué)生的年齡信息。

SELECT學(xué)號，姓名，year(date())-year(出生日期）

AS年齡FROM學(xué)生【例3】顯示學(xué)生表中所有男生記錄的學(xué)號，姓名和性別字段值。SELECT學(xué)號，姓名，性別FROM學(xué)生WHERE性別="男"【例4】顯示學(xué)生表中出生日期在85年到86年之間的學(xué)生的學(xué)號，姓名，出生日期。SELECT學(xué)號，姓名，出生日期;FROM學(xué)生;WHERE出生日期

BETWEEN{^1985-01-01}AND{^1986-12-31}【例5】顯示STUD表中姓李的學(xué)生的學(xué)號，姓名，出生日期。SELECT學(xué)號，姓名，出生日期;FROM學(xué)生;WHERE姓名

LIKE“李%”9.5.3SQL的復(fù)雜查詢在一個數(shù)據(jù)庫中的多個表之間一般都存在著某些聯(lián)系，在一個查詢語句中同時涉及到兩個或兩個以上的表時，這種查詢稱之為連接查詢（也稱為多表查詢）。在多表之間查詢必須處理表與表之間的連接關(guān)系。

SELECT[ALL|DISTINCT]<字段列表>FROM<表1>[，表2…..]WHERE<條件表達(dá)式>1、簡單連接查詢【例1】查詢男學(xué)生的成績信息，包括學(xué)號，姓名，成績。SELECTa.學(xué)號，a.姓名，b.成績;FROM學(xué)生a，成績b;WHEREa.學(xué)號=b.學(xué)號AND

a.性別=“男”【例2】查詢并顯示各個學(xué)生所學(xué)課程的情況。SELECTa.學(xué)號，a.姓名，c.課程名;FROM學(xué)生a，成績b，課程c;WHEREa.學(xué)號=c.學(xué)號.AND.c.課程號=b.課程號2．嵌套查詢在SQL語句中，一個SELECT－FROM－WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。【例1】顯示“王波”所在班級的學(xué)生名單。SELECT學(xué)號，姓名，班級;FROM學(xué)生;WHERE班級=（SELECT班級FROMSTUDWHERE姓名=“王波”）【例2】查詢女生成績信息，包括學(xué)號，課程號，成績。Sele*from成績where學(xué)號in(sele學(xué)號;From學(xué)生where性別=“女”3．超聯(lián)接查詢SELE…….FROMTABLEINNER[LEFT][RIGHT][FULL]JOINTABLEON條件WHERE……...（1）內(nèi)部聯(lián)接：普通聯(lián)接，只有滿足條件的記錄出現(xiàn)在結(jié)果。（2）左聯(lián)接：滿足條件的記錄出現(xiàn)在結(jié)果，同時第一個表不滿足條件的記錄也出現(xiàn)。（3）右聯(lián)接：滿足條件的記錄出現(xiàn)在結(jié)果，同時第二個表不滿足條件的記錄也出現(xiàn)。（4）完全聯(lián)接：不管是否滿足聯(lián)接條件的記錄全出現(xiàn)在結(jié)果SELECTa.學(xué)號，b.課程名，b.成績;FROM成績a[INNER][LEFT][RIGHT][FULL]JOIN

；

課程bONa.課程號=b.課程號4、簡單計算查詢主要函數(shù)：計數(shù)COUNT（）；求和SUM（）；平均值A(chǔ)VG（）；最大值MAX（）；最小值MIN（）。[例如]查詢最高分的學(xué)生信息。4．排序與分組查詢【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>[WHERE<條件>[GROUPBY<分類字段列表>…][HAVING<過濾條件>][ORDERBY<排序項>[ASC|DESC]【功能】包括有排序、函數(shù)運算和謂詞演算【例1】按出生日期降序顯示STUD表中的學(xué)號，姓名，出生日期。SELECT學(xué)號，姓名，出生日期;FROMSTUD;ORDERBY

出生日期

DESC【例2】按成績升序顯示SC表中的學(xué)號，課程號，成績。SELECT學(xué)號，課程號，成績;FROMSC;ORDERBY成績【例3】按班級分類顯示學(xué)生的姓名，課程名，成績，同一班級按分?jǐn)?shù)排序。SELECTa.姓名，a.班級名，c.課程名，b.成績;FROMSTUDa，SCb，COURSEc;WHEREa.學(xué)號=b.學(xué)號ANDb.課程號=c.課程號;ORDERBYa.班級名，b.成績;【例4】顯示各班總?cè)藬?shù)。SELECT班級名，COUNT（*）AS總?cè)藬?shù);FROMSTUD;GROUPBY班級名5、集合并運算【例】顯示計算機系的學(xué)生及所有男學(xué)生。SELECT學(xué)號，姓名，系別代號，性別;FROMSTUD;WHERE系別代號="01"UNION;SELECT學(xué)號，姓名，系別代號，性別;FROMSTUD;WHERE性別="男"【例1】顯示計算機系男生的名單。SELECT學(xué)號，姓名，系別代號，性別;FROMSTUD;WHERE系別代號=“01”AND性別=“男”【例2】顯示既選修了01課程又選修了02課程學(xué)生的名單。SELECT學(xué)號;FROMSC;WHERE課程號=“01”AND學(xué)號IN;（SELECT學(xué)號FROMSCWHERE課程號=“02”）【例3】顯示計算機系非男生的名單。SELECT學(xué)號，姓名，系別代號，性別;FROMSTUD;WHERE系別代號=“01”AND性別=“女”【例4】顯示選修了01課程而沒有選修02課程學(xué)生的名單。SELECT學(xué)號;FROMSC;WHERE課程號=“01”AND學(xué)號NOTIN;（SELECT學(xué)號FROMSCWHERE課程號=“02”）7．查詢?nèi)ハ蚰J(rèn)情況下，查詢輸出到一個瀏覽窗口，用戶在“SELECT”語句中可使用[INTO<目標(biāo)>|TOFILE<文件名>|TOSCREEN|TOPRINTER]子句選擇查詢?nèi)ハ颍篒NTOARRAY數(shù)組名：將查詢結(jié)果保存到一個數(shù)組中。CURSOR<臨時表名>：將查詢結(jié)果保存到一個臨時表中。DBF|TABLE<表名>：將查詢結(jié)果保存到一個永久表中。TOFILE<文件名>[ADDITIVE]：將查詢結(jié)果保存到文本文件中。如果帶“ADDITIVE”關(guān)鍵字，查詢結(jié)果以追加方式添加到<文件名>指定的文件，否則，以新建或覆蓋方式添加到<文件名>指定的文件。TOSCREEN：將查詢結(jié)果保在屏幕上顯示。TOPRINTER：將查詢結(jié)果送打