第九章結(jié)構(gòu)化查詢(xún)語(yǔ)句(SQL)

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

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

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

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

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

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

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

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

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

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

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

；

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

出生日期

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