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

7.1SQL概述SQL是一個(gè)功能強(qiáng)大且十分龐大的語言，其標(biāo)準(zhǔn)文檔就有600多頁。在Access中由用戶輸入SQL語句，能夠?qū)崿F(xiàn)各種的查詢的功能，甚至有些功能只能通過SQL查詢完成。

SQL語言的特點(diǎn)：

1.高度綜合

SQL語言可用于表示用戶對數(shù)據(jù)庫的所有操作，而所有對數(shù)據(jù)庫的操作是由幾個(gè)命令來完成的。

7.1SQL概述

2.非過程化用戶只需要描述要做什么，而不必告訴系統(tǒng)如何去做。3.采用面向集合的操作方式該語言的操作對象和操作結(jié)果都是元組的集合，也就是表。

現(xiàn)在幾乎所有數(shù)據(jù)庫應(yīng)用開發(fā)工具都將SQL直接融入自身中，Access也不例外。

SQL語言的功能：

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

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

SQL語言的數(shù)據(jù)類型：

注意：不同數(shù)據(jù)庫管理系統(tǒng)所提供的SQL數(shù)據(jù)類型不完全相同。(教材表7.2中有些類型在Access中不能使用。

掌握以下數(shù)據(jù)類型：數(shù)據(jù)類型Access中數(shù)據(jù)類型說明TEXT/VARCHAR文本常用,掌握CHAR定長文本常用,掌握DATETIME/DATE日期/時(shí)間常用,掌握SMALLINT短整型常用,掌握REAL單精度型常用,掌握INTEGER/INT長整型常用,掌握FLOAT雙精度型常用,了解BIT/LOGICAL

邏輯型常用,了解MEMO備注常用,了解MONEY貨幣常用,了解

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

SQL語言的數(shù)據(jù)定義主要包括表和索引的創(chuàng)建、修改和刪除操作。(針對表結(jié)構(gòu)的操作)

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

CREATETABLE<表名>

(<列名1><數(shù)據(jù)類型1>[<長度>][<列級完整性約束1>]

[,<列名2><數(shù)據(jù)類型2>[(長度)][<列級完整性約束2>]][,…]

[,<表級完整性約束1>]][,…])；

功能：創(chuàng)建一個(gè)表，指定列屬性定義的表結(jié)構(gòu)。

說明：

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

例如，CREATETABLE職工

(工號CHAR(6),姓名CHAR(6)NOTNULL,

工作時(shí)間DATE,年齡SMALLINT,工資REAL);

②PRIMARYKEY表或列級約束。如果單個(gè)字段做主健，可在“<列名><數(shù)據(jù)類型名><長度>”的后面寫“PRIMARYKEY。如果多個(gè)字段做主鍵，則需在所有字段定義完成后寫“PRIMARYKEY(<字段名1>,<字段名2>,…)”。其作用與Access表的設(shè)計(jì)視圖中“主鍵”相同。例如，CREATETABLE系

(系編號CHAR(6)NOTNULLPRIMARYKEY,

系名稱CHAR(40),系主任CHAR(8),

學(xué)院編號CHAR(1));

或CREATETABLE系

(系編號CHAR(6)NOTNULL,

系名稱CHAR(40),

系主任CHAR(8),

學(xué)院編號CHAR(1),PRIMARYKEY(系編號));

③FOREIGNKEY外鍵約束。該約束可在列級或表級定義。如果外鍵只有一列，可定義為列級約束。如果外鍵包含一到多個(gè)列，可定義為表級約束。例如，CREATETABLE系

(系編號CHAR(6)NOTNULLPRIMARYKEY,

系名稱CHAR(40),系主任CHAR(8),學(xué)院編號CHAR(1)REFERENCES學(xué)院(學(xué)院編號));

或CREATETABLE系

(系編號CHAR(6)NOTNULL,系名稱CHAR(40),

系主任CHAR(8),學(xué)院編號CHAR(1),PRIMARYKEY(系編號),FOREIGNKEY(學(xué)院編號)REFERENCES學(xué)院(學(xué)院編號));

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

例如，CREATETABLE系

(系編號 CHAR(6)NOTNULL,

系名稱CHAR(40)NOTNULLUNIQUE,

系主任CHAR(8),

學(xué)院編號CHAR(1),PRIMARYKEY(系編號),FOREIGNKEY(學(xué)院編號)REFERENCES學(xué)院(學(xué)院編號));

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

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

ALTERTABLE<表名>

[ADD<列名>

<數(shù)據(jù)類型>[<完整性約束>][,…]]

[DROP[[[CONSTRAINT]<約束名>]|[COLUMN<列名>]][,…]]

[ALTER<列名><數(shù)據(jù)類型>[,…]];

功能：修改表的結(jié)構(gòu)。

說明：

(1)<表名>是要修改結(jié)構(gòu)的表的名字

(2)ADD用于增加新列和新的完整性約束條件

(3)DROP用于刪除指定列或完整性約束條件

(4)ALTER用于修改表的列寬度、數(shù)據(jù)類型等。注:參數(shù)CONSTRAINT無效，參數(shù)COLUMN可省略。

例7.2在學(xué)生表上，給出完成下列操作的SQL語句。

(1)增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期類型

(2)將“入學(xué)時(shí)間”字段改為整數(shù)類型，

(3)刪除“入學(xué)時(shí)間”字段。完成上述操作的SQL語句依次為：

ALTERTABLE學(xué)生ADD入學(xué)時(shí)間DATE;ALTERTABLE學(xué)生ALTER入學(xué)時(shí)間INT;ALTERTABLE學(xué)生DROP入學(xué)時(shí)間;

說明，該語句主要掌握增加一個(gè)字段、刪除一個(gè)字段的簡單命令。(注：不能對主鍵操作)

3.刪除數(shù)據(jù)表

格式：DROPTABLE<表名>

功能：刪除表。

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

例7.3

刪除學(xué)生和班級兩張表。

DROPTABLE班級;

DROPTABLE學(xué)生;

注：如果學(xué)生表與班級表有關(guān)聯(lián)，需選刪除學(xué)生表，再刪除班級表。

4.索引操作語句

(1)建立索引

CREATE

[UNIQUE][CLUSTERED]INDEX

<索引名>ON<表名>(<列名1>[ASC∣DESC]

[,<列名2>[ASC∣DESC]][,……])

功能：為表創(chuàng)建索引。

例7.4(1)為系表建立系編號字段上的唯一索引

(2)為系表建立學(xué)院編號字段上的一般索引

SQL語句如下：

CREATEUNIQUEINDEX系表的系編號ON

系(系編號);

CREATEINDEX系表的學(xué)院編號ON系(學(xué)院編號);

注:按設(shè)計(jì)視圖打開表,點(diǎn)擊視圖/索引,可看到已創(chuàng)建的索引。(2)刪除索引

格式：DROPINDEX索引名

ON<表名>

功能：刪除索引。

例7.5給出刪除例7.4建立的兩個(gè)索引的SQL語句。

DROPINDEX系表的系編號

ON系;

DROPINDEX系表的學(xué)院編號

ON系;7.3SQL的數(shù)據(jù)查詢

SELECT語句：(該命令掌握程度以教材例題為準(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á)式>：只對滿足條件的查詢結(jié)果分組。

(5)ORDERBY<排序字段>[ASC][DESC]：按指定字段排序

(6)ALL|DISTINCT的含義：ALL是指顯示所有滿足條件的記錄(默認(rèn))，DISTINCT則指只顯示不完全相同結(jié)果的記錄，可用于過濾重復(fù)。

簡單查詢語句：是指數(shù)據(jù)來源是一個(gè)表或一個(gè)視圖的查詢操作，它是最簡單的查詢操作。1.檢索表中所有的行和列

例7.6查詢所有學(xué)院的各項(xiàng)信息。

SELECT學(xué)院編號,學(xué)院名稱,院長姓名,

電話,地址FROM學(xué)院;

或SELECT*FROM學(xué)院;

注：如果查詢包括所有列，可用*號代替。

2.檢索表中指定的列

例7.7查詢所有學(xué)院的名稱和聯(lián)系方式。SELECT學(xué)院名稱,電話,地址FROM學(xué)院;3.檢索表中指定的列和產(chǎn)生新列

例7.8查詢員工工資表，輸出員工編號、姓名、時(shí)間、扣款、稅款和實(shí)發(fā)工資。假設(shè)員工工資表結(jié)構(gòu)為：工資(員工編號CHAR(10),姓名CHAR(10),時(shí)間DATE,應(yīng)發(fā)工資REAL,扣款REAL,稅款REAL)。SELECT員工編號,姓名,時(shí)間,扣款,稅款,

應(yīng)發(fā)工資-扣款-稅款A(yù)S實(shí)發(fā)工資FROM工資;

例7.9在學(xué)生表中，顯示學(xué)號、姓名和年齡三個(gè)字段，且將學(xué)號字段的標(biāo)題顯示為“學(xué)生號碼”。SELECT學(xué)號AS學(xué)生號碼,姓名,year(now())-year(出生年月)AS年齡FROM學(xué)生;4.檢索表中指定的列和指定的行查詢滿足條件的記錄可以通過WHERE子句來實(shí)現(xiàn)。例7.10查詢信息學(xué)院的名稱和聯(lián)系方式。

例7.11查詢有不及格成績的學(xué)生的學(xué)號、課程編號和成績。SELECT學(xué)院名稱,電話,地址

FROM學(xué)院WHERE學(xué)院名稱='信息學(xué)院';SELECT學(xué)號,課程編號,成績FROM選課

WHERE成績<60;

例7.12查詢年齡在20-23歲的學(xué)生的學(xué)號、姓名和出生年月。

SELECT學(xué)號,姓名,出生年月FROM學(xué)生

WHEREyear(now())-year(出生年月)BETWEEN20AND23;

或

SELECT學(xué)號,姓名,出生年月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é)號、出生年月、籍貫信息。SELECT姓名,學(xué)號,出生年月,籍貫FROM學(xué)生

WHERE姓名LIKE'張?';

例7.15查詢姓名中包含“麗”字的學(xué)生的姓名、學(xué)號、出生年月、籍貫信息。

SELECT姓名,學(xué)號,出生年月,籍貫

FROM學(xué)生

WHERE姓名

LIKE'*麗*';

例7.16統(tǒng)計(jì)每門課程的平均分。6.檢索表中分組統(tǒng)計(jì)結(jié)果

SQL中常用的統(tǒng)計(jì)函數(shù)與Access中使用的函數(shù)相同，利用這些函數(shù)可完成簡單的數(shù)據(jù)匯總工作。

例7.17按性別統(tǒng)計(jì)學(xué)生人數(shù)。

SELECT課程編號,AVG(成績)AS平均分

FROM選課

GROUPBY課程編號;

SELECT性別,COUNT(學(xué)號)AS人數(shù)

FROM學(xué)生

GROUPBY性別;

例7.19計(jì)算課程編號為03001的課程的平均成績。

例7.18統(tǒng)計(jì)選課表中各門課程的最高分。

SELECT

課程編號,MAX(成績)AS最高分

FROM選課

GROUPBY課程編號;

SELECTAVG(成績)AS平均分

FROM選課

WHERE課程編號='03001';

SELECT*

FROM學(xué)生ORDERBY出生年月;7.檢索表中排序結(jié)果利用ORDERBY子句可以對查詢的結(jié)果進(jìn)行排序。

如果按由大到小進(jìn)行排序，則：

SELECT*

FROM學(xué)生ORDERBY出生年月

DESC;

例7.20將學(xué)生按出生年月由小到大進(jìn)行排序。

對例7.16中的查詢結(jié)果，按平均分由大到小進(jìn)行排序。

8.空值問題

例7.21查詢課程中還沒有給出所有學(xué)生成績的課程編號。

SELECT課程編號,AVG(成績)AS平均分

FROM選課

GROUPBY課程編號

ORDERBY平均分DESC;

SELECT

課程編號

FROM選課

WHERE

成績ISNULL;

連接查詢：把多個(gè)表的信息集中在一起輸出，就要用到“連接”操作，可通過在FROM子句中使用多個(gè)表名來實(shí)現(xiàn)。多個(gè)表名在FROM子句中通過逗號隔開。另外還可以用WHERE子句給定表的連接條件。

1.兩表連接

例7.22查詢每個(gè)學(xué)生的姓名、所在班級名稱和專業(yè)。本例中，連接條件為“學(xué)生.班級編號=班級.班級編號”。SELECT學(xué)號,姓名,班級名稱,專業(yè)FROM學(xué)生,班級

WHERE學(xué)生.班級編號=班級.班級編號;

2.多表連接連接操作可以是兩個(gè)以上表之間進(jìn)行的，此時(shí)連接條件要求必須是兩兩之間給出。

例7.24查詢學(xué)生的學(xué)號和姓名，及所在學(xué)院、系和班級的名稱。SELECT學(xué)院.學(xué)院名稱,系.系名稱,班級.班級名稱,學(xué)生.學(xué)號,

學(xué)生.姓名FROM學(xué)院,系,班級,學(xué)生

WHERE(學(xué)院.學(xué)院編號=系.學(xué)院編號)AND(系.系編號=班級.系編號)AND(班級.班級編號=學(xué)生.班級編號);

注：學(xué)院.學(xué)院名稱可直接寫成學(xué)院名稱，因?yàn)橹挥幸粋€(gè)表有此字段，其它項(xiàng)也如此。

其它查詢：

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

例如：

SELECT*FROM學(xué)生

WHERE學(xué)號

IN(SELECT學(xué)號FROM選課WHERE課程編號='03001')

該例中內(nèi)層查詢的含義是查找所有選修了“03001”號課程的學(xué)生的學(xué)號，這些學(xué)號形成一個(gè)集合，在此稱該集合為S。外層循環(huán)的含義是查找學(xué)號在S中的學(xué)生的詳細(xì)信息。因此,此嵌套查詢的含義是查詢選修了“03001”號課程的學(xué)生的詳細(xì)信息。

例7.25查詢計(jì)算機(jī)系、地理系和外語系的所有學(xué)生的姓名和專業(yè)。SELECT姓名,專業(yè)FROM學(xué)生,班級

WHERE學(xué)生.班級編號=班級.班級編號AND

班級.系編號IN(SELECT系編號FROM系

WHERE系名稱IN('計(jì)算機(jī)系','地理系','外語系'));

2.集合查詢

SQL提供的集合操作主要包括并、交和差三類，分別用UNION，INTERSECT和EXCEPT表示，其含義與集合操作相同。

注：Access目前不支持INTERSECT和EXCEPT操作。

例7.26輸出所有教師和學(xué)生的姓名和性別字段。SELECT姓名,性別FROM教師UNIONSELECT姓名,性別FROM學(xué)生;7.4SQL的數(shù)據(jù)更新SQL數(shù)據(jù)更新操作