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

第七章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQLSQL語(yǔ)言，即結(jié)構(gòu)化查詢語(yǔ)言(StructuredQueryLanguage)。該語(yǔ)言的功能十分強(qiáng)大，所有表的建立、查詢、索引等功能都可以用幾條SQL語(yǔ)句完成。本章簡(jiǎn)要介紹SQL語(yǔ)言的常用語(yǔ)句。只要求掌握這些語(yǔ)句的基本語(yǔ)法及使用，重點(diǎn)掌握SELECT查詢語(yǔ)句的一般使用。

7.1SQL概述SQL是一個(gè)功能強(qiáng)大且十分龐大的語(yǔ)言。其標(biāo)準(zhǔn)文檔就有600多頁(yè)。我們只需要掌握能夠在Access環(huán)境中使用簡(jiǎn)單的SQL語(yǔ)句完成想要執(zhí)行的操作。SQL語(yǔ)言是一個(gè)非過(guò)程化語(yǔ)言，其大多數(shù)語(yǔ)句都可獨(dú)立執(zhí)行并完成一個(gè)特定操作、與上下文無(wú)關(guān)。在Access中使用SQL查詢，由用戶輸入SQL語(yǔ)句，能夠?qū)崿F(xiàn)各種的查詢的功能，甚至有些功能只能通過(guò)SQL查詢完成。

SQL語(yǔ)言的特點(diǎn)：1.高度綜合SQL語(yǔ)言可用于表示用戶對(duì)數(shù)據(jù)庫(kù)的所有操作，而所有對(duì)數(shù)據(jù)庫(kù)的操作是由幾個(gè)命令來(lái)完成的。2.非過(guò)程化用戶只需要描述要做什么，而不必告訴系統(tǒng)如何去做。3.采用面向集合的操作方式該語(yǔ)言的操作對(duì)象和操作結(jié)果都是元組的集合，即表。現(xiàn)在很多數(shù)據(jù)庫(kù)應(yīng)用開(kāi)發(fā)工具都將SQL直接融入自身中，Access也不例外。

SQL語(yǔ)言的功能：SQL語(yǔ)言提供數(shù)據(jù)定義、數(shù)據(jù)查詢與數(shù)據(jù)控制三大類(lèi)功能。下表為Access所支持的功能。功能支持的SQL語(yǔ)句詳細(xì)功能數(shù)據(jù)定義CREATETABLE/INDEXALTERTABLE/INDEXDROPTABLE/INDEX定義、修改與刪除基本表和索引。數(shù)據(jù)更新INSERT、UPDATE、DELETE插入數(shù)據(jù)；修改數(shù)據(jù)；刪除數(shù)據(jù)數(shù)據(jù)查詢SELECT數(shù)據(jù)庫(kù)各種查詢

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

SQL語(yǔ)言的數(shù)據(jù)類(lèi)型：

注意：不同數(shù)據(jù)庫(kù)管理系統(tǒng)所提供的SQL數(shù)據(jù)類(lèi)型不完全相同。(教材表7.2中TINYINT、DECIMAL、UNIQUEIDENTIFIER、CHARACTER在Access中不能使用。另外，表中TEXT與CHAR等同)掌握以下數(shù)據(jù)類(lèi)型：數(shù)據(jù)類(lèi)型Access中數(shù)據(jù)類(lèi)型說(shuō)明CHAR/TEXT字符熟悉掌握DATETIME/DATE日期/時(shí)間熟悉掌握SMALLINT短整型熟悉掌握REAL單精度型熟悉掌握INTEGER/INT長(zhǎng)整型熟悉掌握FLOAT雙精度型BIT/LOGICAL

邏輯型MEMO備注MONEY貨幣IMAGEOLE對(duì)象

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

SQL語(yǔ)言的數(shù)據(jù)定義主要包括表和索引的創(chuàng)建、修改和刪除操作。

1.建立數(shù)據(jù)表

CREATETABLE<表名>(<列名1><數(shù)據(jù)類(lèi)型1>[<長(zhǎng)度>][<列級(jí)完整性約束1>][,<列名2><數(shù)據(jù)類(lèi)型2>[(長(zhǎng)度)][<列級(jí)完整性約束2>]][,…][,<表級(jí)完整性約束1>]][,…])；

功能：創(chuàng)建一個(gè)表，指定列屬性定義的表結(jié)構(gòu)。說(shuō)明：(1)[]可選項(xiàng)；<>名詞不可拆分項(xiàng)；[,…]前面的項(xiàng)可重復(fù)多次。(2)表的完整性約束一般分列級(jí)和表級(jí)兩種，定義格式如下：①NOTNULL列級(jí)約束。輸入數(shù)據(jù)時(shí)不允許字段值為空。系統(tǒng)默認(rèn)值為NULL。其作用與Access表的設(shè)計(jì)視圖中“必填字段”相同。

例如，CREATETABLE系(系編號(hào) CHAR(5)NOTNULL);

②PRIMARYKEY表或列級(jí)約束。該約束可在列級(jí)或表級(jí)定義。如果單個(gè)字段做主健，可在“<列名><數(shù)據(jù)類(lèi)型名><長(zhǎng)度>”的后面寫(xiě)“PRIMARYKEY。如果多個(gè)字段做主鍵，則需在所有字段定義完成后寫(xiě)“PRIMARYKEY(<字段名1>,<字段名2>,…)”。其作用與Access表的設(shè)計(jì)視圖中“主鍵”相同。例如，CREATETABLE系(系編號(hào)CHAR(5)NOTNULLPRIMARYKEY,系名稱(chēng)CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5));或CREATETABLE系(系編號(hào)CHAR(5)NOTNULL,系名稱(chēng)CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5),PRIMARYKEY(系編號(hào)));③FOREIGNKEY外鍵約束。該約束可在列級(jí)或表級(jí)定義。如果外鍵只有一列，可定義為列級(jí)約束。如果外鍵包含一到多個(gè)列，可定義為表級(jí)約束。例如，CREATETABLE系(系編號(hào)CHAR(5)NOTNULLPRIMARYKEY,系名稱(chēng)CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5)REFERENCES學(xué)院(學(xué)院編號(hào)));或CREATETABLE系(系編號(hào)CHAR(5)NOTNULL,系名稱(chēng)CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5),PRIMARYKEY(系編號(hào))，F(xiàn)OREIGNKEY(學(xué)院編號(hào))REFERENCES學(xué)院(學(xué)院編號(hào)));

說(shuō)明：系編號(hào)是主鍵，NOTNULL可省略。注意在參照表（表名由REFERENCES后面的<表名>定義）中，這些對(duì)應(yīng)字段必須是已建立了唯一性索引的，否則該建表語(yǔ)句無(wú)法執(zhí)行。④CHECK是域完整性約束，用于輸入列值時(shí)對(duì)輸入數(shù)據(jù)進(jìn)行有效性檢查。其作用與Access表的設(shè)計(jì)視圖中“有效性規(guī)則”相同。⑤UNIQUE唯一性約束，要求不同記錄在此字段上取值不能相等。

例如，CREATETABLE系(系編號(hào) CHAR(5)NOTNULL,系名稱(chēng)CHAR(20)NOTNULLUNIQUE,系主任CHAR(20),學(xué)院編號(hào)CHAR(5),PRIMARYKEY(系編號(hào)),FOREIGNKEY(學(xué)院編號(hào))REFERENCES學(xué)院(學(xué)院編號(hào)));

說(shuō)明：UNIQUE唯一性約束則要求姓名不能有重名。另外，完整性約束主要掌握前2種。

2.修改數(shù)據(jù)表

ALTERTABLE<表名>[ADD<列名><數(shù)據(jù)類(lèi)型>[<完整性約束>][,…]][DROP[[[CONSTRAINT]<約束名>]|[COLUMN<列名>]][,…]][ALTER<列名><數(shù)據(jù)類(lèi)型>[,…]];

功能：修改表的結(jié)構(gòu)。說(shuō)明：(1)<表名>是要修改結(jié)構(gòu)的表的名字(2)ADD用于增加新列和新的完整性約束條件(3)DROP用于刪除指定列或完整性約束條件(4)ALTER用于修改表的列寬度、數(shù)據(jù)類(lèi)型等。注:參數(shù)CONSTRAINT無(wú)效，參數(shù)COLUMN可省略。例7.2在學(xué)生表上，給出完成下列操作的SQL語(yǔ)句。(1)增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類(lèi)型為日期類(lèi)型(2)將學(xué)號(hào)字段由字符類(lèi)型改為整數(shù)類(lèi)型，(3)增加學(xué)號(hào)字段不能為空的完整性約束條件(4)刪除“班級(jí)編號(hào)”字段。完成上述操作的SQL語(yǔ)句依次為：ALTERTABLE學(xué)生ADD入學(xué)時(shí)間DATE;ALTERTABLE學(xué)生ALTERCOLUMN學(xué)號(hào)INT;

ALTERTABLE學(xué)生ADDCONSTRAINTNOTNULL(學(xué)號(hào));ALTERTABLE學(xué)生DROP班級(jí)編號(hào);

說(shuō)明，該語(yǔ)句主要掌握增加一個(gè)字段、刪除一個(gè)字段的簡(jiǎn)單命令。

3.刪除數(shù)據(jù)表格式：DROPTABLE<表名>功能：刪除表。說(shuō)明：表一旦被刪除，表中的數(shù)據(jù)、此表上建立的索引和查詢等結(jié)構(gòu)都將自動(dòng)被刪除，并且無(wú)法恢復(fù)，因此一定要格外小心。

例7.3刪除學(xué)生和班級(jí)兩張表。DROPTABLE班級(jí);DROPTABLE學(xué)生;

4.索引操作語(yǔ)句(1)建立索引CREATE[UNIQUE][CLUSTERED]INDEX<索引名>ON<表名>(<列名1>[ASC∣DESC][,<列名2>[ASC∣DESC]][,……])功能：為表創(chuàng)建索引。

例7.4(1)為系表建立系編號(hào)字段上的唯一索引(2)為系表建立學(xué)院編號(hào)字段上的普通索引SQL語(yǔ)句如下：CREATEUNIQUEINDEX系表的系編號(hào)ON系(系編號(hào));CREATEINDEX系表的學(xué)院編號(hào)ON系(學(xué)院編號(hào));(2)刪除索引格式：DROPINDEX索引名功能：刪除索引。

例7.5給出刪除例7.4建立的兩個(gè)索引的SQL語(yǔ)句。DROPINDEX系表的系編號(hào);DROPINDEX系表的學(xué)院編號(hào);7.3SQL的數(shù)據(jù)查詢

SELECT語(yǔ)句：(該命令掌握程度以教材例題為準(zhǔn))SELECT[ALL|DISTINCT]<列名>|<目標(biāo)列表達(dá)式>|<函數(shù)>[,……]FROM<表名或視圖名>[,……][WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC][DESC]]功能：創(chuàng)建一個(gè)由指定范圍內(nèi)、滿足條件、按某字段分組、按某字段排序的指定字段組成的新記錄集。(1)WHERE<條件表達(dá)式>：符合條件的記錄集。(2)<函數(shù)>：查詢計(jì)算函數(shù)。(3)GROUPBY<分組字段名>：查詢結(jié)果按指定字段分組。(4)HAVING<條件表達(dá)式>：只對(duì)滿足條件的查詢結(jié)果分組。(5)ORDERBY<排序字段>[ASC][DESC]：按指定字段排序(6)ALL|DISTINCT的含義：ALL是指顯示所有滿足條件的記錄，DISTINCT則指只顯示不完全相同結(jié)果的記錄。簡(jiǎn)單查詢語(yǔ)句：是指數(shù)據(jù)來(lái)源是一個(gè)表或一個(gè)視圖的查詢操作，它是最簡(jiǎn)單的查詢操作。1.檢索表中所有的行和列

例7.6假設(shè)學(xué)院表包含的數(shù)據(jù)如表所示，編寫(xiě)查詢語(yǔ)句查詢所有學(xué)院的各項(xiàng)信息。學(xué)院編號(hào)學(xué)院名稱(chēng)院長(zhǎng)姓名電話地址a信息學(xué)院劉萬(wàn)85285555嵩山b地理學(xué)院寬帶里65433213東山…

SELECT學(xué)院編號(hào),學(xué)院名稱(chēng),院長(zhǎng)姓名,電話,地址FROM學(xué)院；SELECT*FROM學(xué)院；

注：如果查詢包括所有列，可用*號(hào)代替。

2.檢索表中指定的列

例7.7查詢所有學(xué)院的名稱(chēng)和聯(lián)系方式。

SELECT學(xué)院名稱(chēng),電話,地址FROM學(xué)院；3.檢索表中指定的列和產(chǎn)生新列

例7.8查詢員工工資表，輸出員工編號(hào)、姓名、時(shí)間、扣款、稅款和實(shí)發(fā)工資。假設(shè)員工工資表結(jié)構(gòu)為：工資(員工編號(hào)CHAR(10),姓名CHAR(10),時(shí)間DATE,應(yīng)發(fā)工資REAL,扣款REAL,稅款REAL)。SELECT員工編號(hào),姓名,時(shí)間,扣款,稅款,應(yīng)發(fā)工資-扣款-稅款A(yù)S實(shí)發(fā)工資FROM工資;例7.9在學(xué)生表中，顯示學(xué)號(hào)、姓名和年齡三個(gè)字段，且將學(xué)號(hào)字段的標(biāo)題顯示為“學(xué)生號(hào)碼”。SELECT學(xué)號(hào)AS學(xué)生號(hào)碼,姓名，year(now())-year(出生年月)AS年齡FROM學(xué)生;4.檢索表中指定的列和指定的行查詢滿足條件的記錄可以通過(guò)WHERE子句來(lái)實(shí)現(xiàn)。例7.10查詢信息學(xué)院的名稱(chēng)和聯(lián)系方式。SELECT學(xué)院名稱(chēng),院長(zhǎng)姓名,電話,地址FROM學(xué)院WHERE學(xué)院名稱(chēng)='信息學(xué)院';例7.11查詢有不及格成績(jī)的學(xué)生的學(xué)號(hào)、課程編號(hào)和成績(jī)。SELECT學(xué)號(hào),課程編號(hào),成績(jī)FROM選課WHERE成績(jī)<60;例7.12查詢年齡在20-23歲的學(xué)生的學(xué)號(hào)、姓名和出生年月。SELECT學(xué)號(hào),姓名,出生年月FROM學(xué)生WHEREyear(now())-year(出生年月)BETWEEN20AND23;或SELECT學(xué)號(hào),姓名,出生年月FROM學(xué)生WHERE(year(now())-year(出生年月)>=20)AND(year(now())-year(出生年月)<=23);

5.字符串匹配關(guān)鍵詞“LIKE”用以進(jìn)行字符串的匹配，其格式如下：[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]表示查找指定屬性列的值與<匹配串>相匹配的元組。<匹配串>通常是含有通配符的字符串。Access中通配符目前只支持“*”和“?”。例7.13查詢叫張玲玲的學(xué)生的詳細(xì)信息。SELECT*FROM學(xué)生WHERE姓名='張玲玲';例7.14查詢姓張且姓名只有兩個(gè)字的同學(xué)的姓名、學(xué)號(hào)、出生年月、籍貫信息。SELECT姓名,學(xué)號(hào),出生年月,籍貫FROM學(xué)生WHERE姓名LIKE'張?';

例7.16統(tǒng)計(jì)每門(mén)課程的平均分。SELECT課程編號(hào),AVG(成績(jī))AS平均分FROM選課GROUPBY課程編號(hào);

例7.15查詢姓名中包含“麗”字的學(xué)生的姓名、學(xué)號(hào)、出生年月、籍貫信息。SELECT姓名,學(xué)號(hào),出生年月,籍貫FROM學(xué)生WHERE姓名LIKE'*麗*';6.檢索表中分組統(tǒng)計(jì)結(jié)果SQL中常用的合計(jì)函數(shù)與Access中使用的函數(shù)相同，利用這些函數(shù)可完成簡(jiǎn)單的數(shù)據(jù)匯總工作。

例7.17按性別統(tǒng)計(jì)學(xué)生人數(shù)。SELECT性別,COUNT(學(xué)號(hào))AS人數(shù)FROM學(xué)生GROUPBY性別;

例7.19計(jì)算課程編號(hào)為03001的課程的平均成績(jī)。SELECTAVG(成績(jī))FROM選課WHERE課程編號(hào)='03001';

例7.18統(tǒng)計(jì)選課表中各門(mén)課程的最高分。SELECT課程編號(hào),MAX(成績(jī))FROM選課GROUPBY課程編號(hào);

例7.20將學(xué)生按出生年月由小到大進(jìn)行排序。SELECT*FROM學(xué)生ORDERBY出生年月;7.檢索表中排序結(jié)果利用ORDERBY子句可以對(duì)查詢的結(jié)果進(jìn)行排序。

如果按由大到小進(jìn)行排序，則：SELECT*FROM學(xué)生ORDERBY出生年月DESC;

對(duì)例7.16中的查詢結(jié)果，按平均分由大到小進(jìn)行排序。SELECT課程編號(hào),AVG(成績(jī))AS平均分FROM選課GROUPBY課程編號(hào)ORDERBY平均分DESC;

8.空值問(wèn)題

例7.21查詢課程中還沒(méi)有給出所有學(xué)生成績(jī)的課程編號(hào)。SELECT課程編號(hào)FROM選課WHERE成績(jī)ISNULL;連接查詢：把多個(gè)表的信息集中在一起輸出，就要用到“連接”操作，可通過(guò)在FROM子句中使用多個(gè)表名來(lái)實(shí)現(xiàn)。多個(gè)表名在FROM子句中通過(guò)逗號(hào)隔開(kāi)。另外還可以用WHERE子句給定表的連接條件。

1.兩表連接

例7.22查詢每個(gè)學(xué)生的姓名、所在班級(jí)名稱(chēng)和專(zhuān)業(yè)。本例中，連接條件為“學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào)”。SELECT學(xué)號(hào),姓名,班級(jí)名稱(chēng),專(zhuān)業(yè)FROM學(xué)生,班級(jí)WHERE學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào);

2.多表連接連接操作可以是兩個(gè)以上表之間進(jìn)行的，此時(shí)連接條件要求必須是兩兩之間給出。例7.24查詢學(xué)生的學(xué)號(hào)和姓名，及所在學(xué)院、系和班級(jí)的名稱(chēng)。SELECT學(xué)院.學(xué)院名稱(chēng),系.系名稱(chēng),班級(jí).班級(jí)名稱(chēng),學(xué)生.學(xué)號(hào),學(xué)生.姓名FROM學(xué)院,系,班級(jí),學(xué)生WHERE(學(xué)院.學(xué)院編號(hào)=系.學(xué)院編號(hào))AND(系.系編號(hào)=班級(jí).系編號(hào))AND(班級(jí).班級(jí)編號(hào)=學(xué)生.班級(jí)編號(hào));注：學(xué)院.學(xué)院名稱(chēng)可直接寫(xiě)成學(xué)院名稱(chēng)，因?yàn)橹挥幸粋€(gè)表有此字段，其它項(xiàng)也如此。其它查詢：

1.嵌套查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECT…FROM…WHERE…語(yǔ)句產(chǎn)生一個(gè)新的數(shù)據(jù)集，一個(gè)查詢語(yǔ)句完全嵌套到另一個(gè)查詢語(yǔ)句中的WHERE或HAVING的“條件”短語(yǔ)中，這種查詢稱(chēng)為嵌套查詢。

例如：SELECT*FROM學(xué)生WHERE學(xué)號(hào)IN(SELECT學(xué)號(hào)FROM選課WHERE課程編號(hào)='03001')該例中內(nèi)層查詢的含義是查找所有選修了“03001”號(hào)課程的學(xué)生的學(xué)號(hào)，這些學(xué)號(hào)形成一個(gè)集合，在此稱(chēng)該集合為S。外層循環(huán)的含義是查找學(xué)號(hào)在S中的學(xué)生的詳細(xì)信息。因此,此嵌套查詢的含義是查詢選修了“03001”號(hào)課程的學(xué)生的詳細(xì)信息。例7.25查詢計(jì)算機(jī)系、地理系和外語(yǔ)系的所有學(xué)生的姓名和專(zhuān)業(yè)。SELECT姓名,專(zhuān)業(yè)FROM學(xué)生,班級(jí)WHERE學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào)AND班級(jí).系編號(hào)IN(SELECT系編號(hào)FROM系WHERE系名稱(chēng)IN('計(jì)算機(jī)系','地理系','外語(yǔ)系'));

2.集合查詢可以把多個(gè)SELECT產(chǎn)生的數(shù)據(jù)集進(jìn)行合并。SQL提供的集合操作主要包括并、交和差三類(lèi)，分別用UNION，INTERSECT和EXCEPT表示，其含義與集合操作相同。注：Access目前不支持INTERSECT和EXCEPT操作。例7.26輸出所有教師和學(xué)生的姓名和性別字段。SELECT姓名,性別FROM教師UNIONSELECT姓名,性別FROM學(xué)生;7.4SQL的數(shù)據(jù)更新SQL數(shù)據(jù)更新操作有三類(lèi)，向表中插入、修改或刪