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

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

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

SQL語言的特點(diǎn)：1.高度綜合SQL語言可用于表示用戶對(duì)數(shù)據(jù)庫的所有操作，而所有對(duì)數(shù)據(jù)庫的操作是由幾個(gè)命令來完成的。2.非過程化用戶只需要描述要做什么，而不必告訴系統(tǒng)如何去做。3.采用面向集合的操作方式該語言的操作對(duì)象和操作結(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中TINYINT、DECIMAL、UNIQUEIDENTIFIER、CHARACTER在Access中不能使用。另外，表中TEXT與CHAR等同)掌握以下數(shù)據(jù)類型：數(shù)據(jù)類型Access中數(shù)據(jù)類型說明CHAR/TEXT字符熟悉掌握DATETIME/DATE日期/時(shí)間熟悉掌握SMALLINT短整型熟悉掌握REAL單精度型熟悉掌握INTEGER/INT長整型熟悉掌握FLOAT雙精度型BIT/LOGICAL

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

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

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

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

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

功能：創(chuàng)建一個(gè)表，指定列屬性定義的表結(jié)構(gòu)。說明：(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ù)類型名><長度>”的后面寫“PRIMARYKEY。如果多個(gè)字段做主鍵，則需在所有字段定義完成后寫“PRIMARYKEY(<字段名1>,<字段名2>,…)”。其作用與Access表的設(shè)計(jì)視圖中“主鍵”相同。例如，CREATETABLE系(系編號(hào)CHAR(5)NOTNULLPRIMARYKEY,系名稱CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5));或CREATETABLE系(系編號(hào)CHAR(5)NOTNULL,系名稱CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5),PRIMARYKEY(系編號(hào)));③FOREIGNKEY外鍵約束。該約束可在列級(jí)或表級(jí)定義。如果外鍵只有一列，可定義為列級(jí)約束。如果外鍵包含一到多個(gè)列，可定義為表級(jí)約束。例如，CREATETABLE系(系編號(hào)CHAR(5)NOTNULLPRIMARYKEY,系名稱CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5)REFERENCES學(xué)院(學(xué)院編號(hào)));或CREATETABLE系(系編號(hào)CHAR(5)NOTNULL,系名稱CHAR(20),系主任CHAR(20),學(xué)院編號(hào)CHAR(5),PRIMARYKEY(系編號(hào))，F(xiàn)OREIGNKEY(學(xué)院編號(hào))REFERENCES學(xué)院(學(xué)院編號(hào)));

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

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

說明：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é)號(hào)字段由字符類型改為整數(shù)類型，(3)增加學(xué)號(hào)字段不能為空的完整性約束條件(4)刪除“班級(jí)編號(hào)”字段。完成上述操作的SQL語句依次為：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);

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

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

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

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

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

例7.5給出刪除例7.4建立的兩個(gè)索引的SQL語句。DROPINDEX系表的系編號(hào);DROPINDEX系表的學(xué)院編號(hào);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á)式>：只對(duì)滿足條件的查詢結(jié)果分組。(5)ORDERBY<排序字段>[ASC][DESC]：按指定字段排序(6)ALL|DISTINCT的含義：ALL是指顯示所有滿足條件的記錄，DISTINCT則指只顯示不完全相同結(jié)果的記錄。簡單查詢語句：是指數(shù)據(jù)來源是一個(gè)表或一個(gè)視圖的查詢操作，它是最簡單的查詢操作。1.檢索表中所有的行和列

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

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

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

2.檢索表中指定的列

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

SELECT學(xué)院名稱,電話,地址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.檢索表中指定的列和指定的行查詢滿足條件的記錄可以通過WHERE子句來實(shí)現(xiàn)。例7.10查詢信息學(xué)院的名稱和聯(lián)系方式。SELECT學(xué)院名稱,院長姓名,電話,地址FROM學(xué)院WHERE學(xué)院名稱='信息學(xué)院';例7.11查詢有不及格成績的學(xué)生的學(xué)號(hào)、課程編號(hào)和成績。SELECT學(xué)號(hào),課程編號(hào),成績FROM選課WHERE成績<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ì)每門課程的平均分。SELECT課程編號(hào),AVG(成績)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ù)可完成簡單的數(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的課程的平均成績。SELECTAVG(成績)FROM選課WHERE課程編號(hào)='03001';

例7.18統(tǒng)計(jì)選課表中各門課程的最高分。SELECT課程編號(hào),MAX(成績)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(成績)AS平均分FROM選課GROUPBY課程編號(hào)ORDERBY平均分DESC;

8.空值問題

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

1.兩表連接

例7.22查詢每個(gè)學(xué)生的姓名、所在班級(jí)名稱和專業(yè)。本例中，連接條件為“學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào)”。SELECT學(xué)號(hào),姓名,班級(jí)名稱,專業(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í)的名稱。SELECT學(xué)院.學(xué)院名稱,系.系名稱,班級(jí).班級(jí)名稱,學(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é)院名稱可直接寫成學(xué)院名稱，因?yàn)橹挥幸粋€(gè)表有此字段，其它項(xiàng)也如此。其它查詢：

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

例如：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è)集合，在此稱該集合為S。外層循環(huán)的含義是查找學(xué)號(hào)在S中的學(xué)生的詳細(xì)信息。因此,此嵌套查詢的含義是查詢選修了“03001”號(hào)課程的學(xué)生的詳細(xì)信息。例7.25查詢計(jì)算機(jī)系、地理系和外語系的所有學(xué)生的姓名和專業(yè)。SELECT姓名,專業(yè)FROM學(xué)生,班級(jí)WHERE學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào)AND班級(jí).系編號(hào)IN(SELECT系編號(hào)FROM系WHERE系名稱IN('計(jì)算機(jī)系','地理系','外語系'));

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