計(jì)算機(jī)數(shù)據(jù)庫(kù)(經(jīng)濟(jì)會(huì)計(jì)類)七講關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL隨堂講義

第七章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQLSQL結(jié)構(gòu)化查詢語(yǔ)言(StructuredQueryLanguage)的功能十分強(qiáng)大，所有表的建立、查詢、索引等功能都可以用幾條語(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中由用戶輸入SQL語(yǔ)句，能夠?qū)崿F(xiàn)各種的查詢的功能，甚至有些功能只能通過SQL查詢完成。

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

1.高度綜合

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

7.1SQL概述

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

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

SQL語(yǔ)言的功能：

SQL語(yǔ)言提供數(shù)據(jù)定義、數(shù)據(jù)查詢與數(shù)據(jù)控制三大類功能。下表為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ù)類型：

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

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

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

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

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

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

CREATETABLE<表名>

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

[,<列名2><數(shù)據(jù)類型2>[(長(zhǎng)度)][<列級(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(6),姓名CHAR(6)NOTNULL,

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

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

(系編號(hào)CHAR(6)NOTNULLPRIMARYKEY,

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

學(xué)院編號(hào)CHAR(1));

或CREATETABLE系

(系編號(hào)CHAR(6)NOTNULL,

系名稱CHAR(40),

系主任CHAR(8),

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

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

(系編號(hào)CHAR(6)NOTNULLPRIMARYKEY,

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

或CREATETABLE系

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

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

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

例如，CREATETABLE系

(系編號(hào) CHAR(6)NOTNULL,

系名稱CHAR(40)NOTNULLUNIQUE,

系主任CHAR(8),

學(xué)院編號(hào)CHAR(1),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無(wú)效，參數(shù)COLUMN可省略。

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

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

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

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

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

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

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

格式：DROPTABLE<表名>

功能：刪除表。

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

例7.3

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

DROPTABLE班級(jí);

DROPTABLE學(xué)生;

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

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));

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

格式：DROPINDEX索引名

ON<表名>

功能：刪除索引。

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

DROPINDEX系表的系編號(hào)

ON系;

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

ON系;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是指顯示所有滿足條件的記錄(默認(rèn))，DISTINCT則指只顯示不完全相同結(jié)果的記錄，可用于過濾重復(fù)。

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

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

SELECT學(xué)院編號(hào),學(xué)院名稱,院長(zhǎng)姓名,

電話,地址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)系方式。

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

FROM學(xué)院WHERE學(xué)院名稱='信息學(xué)院';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.15查詢姓名中包含“麗”字的學(xué)生的姓名、學(xué)號(hào)、出生年月、籍貫信息。

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

FROM學(xué)生

WHERE姓名

LIKE'*麗*';

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

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

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

SELECT課程編號(hào),AVG(成績(jī))AS平均分

FROM選課

GROUPBY課程編號(hào);

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

FROM學(xué)生

GROUPBY性別;

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

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

SELECT

課程編號(hào),MAX(成績(jī))AS最高分

FROM選課

GROUPBY課程編號(hào);

SELECTAVG(成績(jī))AS平均分

FROM選課

WHERE課程編號(hào)='03001';

SELECT*

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

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

SELECT*

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

DESC;

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

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

8.空值問題

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

SELECT課程編號(hào),AVG(成績(jī))AS平均分

FROM選課

GROUPBY課程編號(hào)

ORDERBY平均分DESC;

SELECT

課程編號(hào)

FROM選課

WHERE

成績(jī)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語(yǔ)言中，一個(gè)SELECT…FROM…WHERE…語(yǔ)句產(chǎn)生一個(gè)新的數(shù)據(jù)集，一個(gè)查詢語(yǔ)句完全嵌套到另一個(gè)查詢語(yǔ)句中的WHERE或HAVING的“條件”短語(yǔ)中，這種查詢稱為嵌套查詢。

例如：

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ī)系、地理系和外語(yǔ)系的所有學(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ī)系','地理系','外語(yǔ)系'));

2.集合查詢

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

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

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