第10章 關(guān)系數(shù)據(jù)庫標準語言SQL

第10章關(guān)系數(shù)據(jù)庫標準語言SQL教學目標掌握SQL修改表結(jié)構(gòu)的規(guī)則與方法

；掌握SQL修改數(shù)據(jù)的規(guī)則與方法

；掌握SQL數(shù)據(jù)查詢功能的語句規(guī)則和特點。重點與難點：表定義中數(shù)據(jù)完整性規(guī)則的設(shè)定、多表連接查詢的方法與使用規(guī)則、在分組查詢中使用函數(shù)的規(guī)則和方法、使用嵌套子查詢的方法和規(guī)則、綜合應(yīng)用SQL數(shù)據(jù)查詢功能完成實際問題的能力。

10.1

SQL概述

SQL是StructuredQueryLanguage的縮寫，即結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫的標準語言。主要特點：⑴SQL是一種功能齊全的一體化語言。⑵SQL是一種高度非過程化的語言。⑶SQL語言簡潔，易學易用。⑷語言共享。SQL語言可以分成以下幾組：數(shù)據(jù)定義語言DDL(DataDefinitionLanguage)：包括定義、修改與刪除基本表及建立與刪除索引等。數(shù)據(jù)操縱語言DML(DataManipulationLanguage)：包括插入、修改與刪除數(shù)據(jù)等。數(shù)據(jù)查詢語言DQL(DataQueryLanguage)：包括單表查詢、連接查詢、嵌套查詢等各種查詢功能。數(shù)據(jù)控制語言DCL(DataControlLanguage)：包括數(shù)據(jù)的安全性控制、數(shù)據(jù)的完整性控制、數(shù)據(jù)庫的恢復(fù)及并發(fā)控制等功能。Access支持數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)操作功能。

SQL功能命令動詞數(shù)據(jù)定義

CREAT、ALTER、DROP數(shù)據(jù)操作

INSERT、DELETE、UPDATE數(shù)據(jù)查詢

SELECT

10.2數(shù)據(jù)定義一.創(chuàng)建表格式：

CREATETABLE<表名>(<字段名1><類型名>[(長度)][PRIMARYKEY][NOTNULL][，<字段名2><類型名>[(長度)][NOTNULL]]…)功能：創(chuàng)建一個數(shù)據(jù)表的結(jié)構(gòu)。說明：

<表名>：要創(chuàng)建的數(shù)據(jù)表的名字。

<字段名><類型名>：要創(chuàng)建的數(shù)據(jù)表的字段名和字段類型。字段長度僅限于文本及二進制字段。

PRIMARYKEY：表示將該字段定義為主鍵。

NOTNULL：不允許字段值為空，而NULL允許字段值為空。例10.1

在職工管理數(shù)據(jù)庫中建立一個數(shù)據(jù)表“職工”，表結(jié)構(gòu)由職工號、姓名、性別、職稱、部門、出生日期、婚否等字段組成。并設(shè)置“職工號”為主鍵。

操作步驟如下：①創(chuàng)建“職工管理”數(shù)據(jù)庫。②在“職工管理”數(shù)據(jù)庫窗口中選擇“查詢”對象。③雙擊“在設(shè)計視圖中創(chuàng)建查詢”，關(guān)閉彈出的“顯示表”對話框，打開查詢設(shè)計視圖窗口。④選擇“查詢”菜單中“SQL特定查詢”下的“數(shù)據(jù)定義”命令項，打開“數(shù)據(jù)定義查詢”窗口。⑤在“數(shù)據(jù)定義查詢”窗口中輸入SQL語句，每個數(shù)據(jù)定義查詢只能包含一條數(shù)據(jù)定義語句。

CREATEDATABASE

職工管理

CREATETABLE

職工(職工號TEXT(5)

PRIMARYKEY,姓名

TEXT(4),性別TEXT(1),職稱TEXT(4),部門TEXT(8),出生日期DATE,婚否LOGICAL)⑥保存查詢?yōu)椤皵?shù)據(jù)表定義查詢（職工）”，查詢建立完畢。⑦運行查詢。在設(shè)計視圖中，單擊工具欄上的“運行”按鈕，執(zhí)行SQL語句，完成創(chuàng)建表的操作。⑧在數(shù)據(jù)庫窗口中單擊“表”對象，可以看到在“表”列表框中多了一個“職工”表，這就是用SQL的定義查詢創(chuàng)建的表。例10.2在職工管理數(shù)據(jù)庫中建立一個數(shù)據(jù)表“工資”，并通過“職工號”字段建立與“職工”表的關(guān)系。

CREATETABLE

工資(職工號TEXT(5)PRIMARYKEY

REFERENCES

職工,

工資Single,津貼Single,所得稅Single,公積金Single,

水電費Single，應(yīng)扣Single,實發(fā)Single)

其中的“REFERENCES

職工”表示與“職工”表建立關(guān)系。格式：DROPTABLE

<表名>功能：刪除指定的數(shù)據(jù)表文件說明：一定要慎用DROPTABLE語句，一旦使用以后就無法恢復(fù)表或其中的數(shù)據(jù)，此表上建立的索引也將自動刪除，并且無法恢復(fù)。二.刪除表例10.3

刪除例10.2建立的工資表操作步驟如下：①打開“數(shù)據(jù)定義查詢”窗口。②輸入刪除表的SQL語句：

DROPTABLE

工資③單擊工具欄上的“運行”按鈕，完成刪除表的操作，“工資”表將從“職工管理”數(shù)據(jù)庫窗口消失。三、修改表的結(jié)構(gòu)格式一：

ALTERTABLE<表名>[ADD<新字段名1><類型名>[(長度)][，<新字段名2><類型名>[(長度)]…]][DROP<字段名1>[，<字段名2>…][ALTER<字段名1><類型名>[(長度)][，<字段名2><類型名>[(長度)]…]]

功能：修改指定的數(shù)據(jù)表的結(jié)構(gòu)。說明：

<表名>：要修改的數(shù)據(jù)表的名字。

ADD子句用于增加新的字段。

DROP子句用于刪除指定的字段。

ALTER子句用于修改原有字段的定義，包括字段名、數(shù)據(jù)類型和字段的長度。應(yīng)注意ADD子句、DROP子句和ALTER子句不能同時使用。例10.4

為職工表增加一個電話號碼字段操作步驟如下：①在“職工管理”數(shù)據(jù)庫窗口中選擇“查詢”對象。②雙擊“在設(shè)計視圖中創(chuàng)建查詢”，關(guān)閉彈出的“顯示表”對話框，打開查詢設(shè)計視圖窗口。③選擇“查詢”菜單中“SQL特定查詢”下的“數(shù)據(jù)定義”命令項，打開“數(shù)據(jù)定義查詢”窗口。④在“數(shù)據(jù)定義查詢”窗口中，輸入修改表結(jié)構(gòu)的SQL語句：

ALTERTABLE

職工ADD

電話號碼Text(8)⑤單擊工具欄上的“運行”按鈕，完成修改表結(jié)構(gòu)的操作。例10.5

將職工表的姓名字段的寬度由原來的6改為8，SQL語句如下：

ALTERTABLE

職工ALTER

姓名Text(8)例10.6

刪除職工表“電話號碼”字段，SQL語句如下：

ALTERTABLE

職工DROP

電話號碼

10.3數(shù)據(jù)操作一.插入記錄格式：

INSERTINTO

<表名>[(<字段名清單>)]VALUES

(<表達式清單>）功能：在指定的數(shù)據(jù)表的尾部添加一條新記錄。說明：

<表名>：要插入數(shù)據(jù)的表的名字。

<字段名清單>為數(shù)據(jù)表要插入新值的字段。

VALUES（表達式清單）為數(shù)據(jù)表要插入新值的各字段的數(shù)據(jù)值。

<字段名清單>和VALUES子句中（表達式清單）的個數(shù)和數(shù)據(jù)類型要完全一致。若省略<字段名清單>，則數(shù)據(jù)表中的所有字段必須在VALUES子句中都有相應(yīng)的值。例10.7

在職工表尾部添加一條新記錄。操作步驟如下：①在“職工管理”數(shù)據(jù)庫窗口中，打開“數(shù)據(jù)定義查詢”窗口。②輸入SQL語句：INSERTINTO

職工(職工號,姓名,性別,職稱,部門,出生日期,婚否)VALUES("01001","陳周","男","教授","計算機",#1958-03-05#,yes)③單擊工具欄上的“運行”按鈕，完成插入數(shù)據(jù)的操作。例10.8

在職工表尾部插入第二條記錄，SQL語句如下：INSERTINTO

職工VALUES("03021","劉楊","女","副教授","管理",#1962-06-18#,no)格式：UPDATE

<表名>

SET

<字段名1>=表達式1

[,<字段名2>=表達式2…][WHERE<條件>]

功能：根據(jù)WHERE子句指定的條件，對指定記錄的字段值進行更新說明：

<表名>：要更新數(shù)據(jù)的表的名字。

<字段名>=<表達式>是指用<表達式>的值替代<字段名>的值，一次可更新多個字段的值。若省略WHERE子句，則更新全部記錄。一次只能在單一的表中更新記錄。二.更新數(shù)據(jù)例10.9

計算工資表中的應(yīng)扣和實發(fā)數(shù)。①在“職工管理”數(shù)據(jù)庫窗口中，打開“數(shù)據(jù)定義查詢”窗口②輸入SQL語句：

UPDATE

工資SET

應(yīng)扣=所得稅+公積金+水電費,實發(fā)=工資+補貼-應(yīng)扣③單擊工具欄上的“運行”按鈕，完成更新數(shù)據(jù)的操作。格式：

DELETEFROM

<表名>[WHERE<條件>]

功能：根據(jù)WHERE子句指定的條件，刪除表中指定的記錄。說明：<表名>：要刪除數(shù)據(jù)的表的名字。若省略WHERE子句，則刪除表中全部記錄。DELETE語句刪除的只是表中的數(shù)據(jù)，而不是表的結(jié)構(gòu)。三.刪除數(shù)據(jù)例10.10

將職工表中職工號為“03021”的記錄刪除。①在“職工管理”數(shù)據(jù)庫窗口中，打開“數(shù)據(jù)定義查詢”窗口。②輸入SQL語句：

DELETEFROM

職工

WHERE

職工號="03021"③單擊工具欄上的“運行”按鈕，完成刪除數(shù)據(jù)的操作。

10.4數(shù)據(jù)查詢一.SELECT查詢命令格式：SELECT[ALL|DISTINCT|TOPn[PERCENT]]<字段名>|<字段表達式>|<函數(shù)>[,…]FROM<數(shù)據(jù)源表或查詢>[WHERE<篩選條件>][GROUPBY<分組字段表>[HAVING<過濾條件>][ORDERBY<排序關(guān)鍵字1>[ASC|DESC][,<排序關(guān)鍵字2>[ASC|DESC]…]]

功能：

從FROM子句列出的表或查詢中，選擇滿足WHERE子句中給出的條件的記錄，然后按GROUPBY子句（分組子句）中指定字段的值分組，再提取滿足HAVING

子句中過濾條件的那些組，按SELECT子句給出的字段名或字段表達式求值輸出。ORDERBY子句（排序子句）是對輸出的目標表進行重新排序，并可附加說明ASC（升序）或DESC（降序）排列。

說明：ALL：查詢的結(jié)果中包含數(shù)據(jù)源中的所有記錄。DISTINCT：查詢的結(jié)果中不包含數(shù)據(jù)源中重復(fù)行的記錄。<字段名表>：指定查詢結(jié)果輸出的字段，如果要包含數(shù)據(jù)源中的所有字段，可以使用通配符“*”。AS<列名稱>：如果在輸出時不希望使用原來的字段名，可以用列名稱重新設(shè)置。FROM<數(shù)據(jù)源表或查詢>：指出查詢的數(shù)據(jù)來源。WHERE<篩選條件>：說明查詢條件，即選擇記錄的條件。二.簡單查詢格式：SELECT[ALL｜DISTINCT]<字段名1>[AS<列名稱>][,<字段名2>[AS<列名稱>]…]

FROM<數(shù)據(jù)源表或查詢>[WHERE<篩選條件>]1.基本查詢例10.11

查詢學生表的全部字段。操作步驟如下：①在“學生成績管理”數(shù)據(jù)庫窗口中選擇“查詢”對象。②雙擊“在設(shè)計視圖中創(chuàng)建查詢”，關(guān)閉彈出的“顯示表”對話框，打開查詢設(shè)計視圖窗口。③選擇“視圖”菜單的“SQL視圖”命令項，打開“選擇查詢”窗口。④輸入SQL語句：

SELECT*FROM學生⑤保存查詢，查詢建立完畢。⑥運行查詢。例10.12查詢學生表中所有學生的姓名和截至統(tǒng)計時的年齡，去掉重名。SQL語句如下：

SELECTDISTINCT

姓名,YEAR(DATE())-YEAR(出生日期)AS

年齡FROM

學生例10.13查詢學生表中所有已通過四級的男生記錄。SQL語句如下：

SELECT*FROM

學生WHERE

性別="男"AND

四級通過=yes2.帶特殊運算符的條件查詢SELECT語句可以使用的特殊運算符有：（1）BETWEEN…AND運算符格式：<字段名>[NOT]BETWEEN<初值>AND<終值>BETWEEN運算符用于檢測字段的值是否介于指定的范圍內(nèi)。（2）IN運算符格式：<字段名>[NOT]IN(<表達式1>[,<表達式2>…])IN運算符用于檢測字段的值是否屬于表達式集合或子查詢。（3）LIKE運算符格式：<字段名>LIKE<字符表達式>LIKE運算符用于檢測字段的值是否與樣式字符串匹配。例10.14:查詢學生表中入學成績在550～570之間的學號、姓名、入學成績。SQL語句如下：

SELECT

學號,姓名,入學成績

FROM

學生

WHERE

入學成績BETWEEN550AND570上述語句的功能相當于：

SELECT學號,姓名,入學成績

FROM

學生

WHERE

入學成績>=550AND

入學成績<=570例10.15：查詢學生表中學號為070102和070401的記錄。SQL語句如下：SELECT*FROM

學生WHERE

學號IN("070102","070401")上述語句的功能相當于：SELECT*FROM

學生WHERE

學號="070102"OR

學號="070401"例10.16：查詢學生表中姓“王”的學生的記錄。SQL語句如下：

SELECT*FROM學生

WHERE

姓名LIKE"王*"通配符“*”表示零個或多個字符“？”表示一個字符

3.計算查詢函數(shù)

功能COUNT(<字段名>)對指定字段的值計算個數(shù)COUNT(*)計算記錄個數(shù)SUM(<字段名>)計算指定的數(shù)值列的和AVG(<字段名>)計算指定的數(shù)值列的平均值MAX(<字段名>)計算指定的字符、日期或數(shù)值列中的最大值MIN(<字段名>)計算指定的字符、日期或數(shù)值列中的最小值說明：（字段名）可以是字段名，也可以是SQL表達式。上述聚合函數(shù)可以用在SELECT短語中對查詢結(jié)果進行計算，也可以在HAVING子句中構(gòu)造分組篩選條件。例10.17：在學生表中統(tǒng)計學生人數(shù)。SQL語句如下：SELECTCOUNT(*)AS

學生人數(shù)

FROM

學生例10.18：查詢學生表中男生入學成績字段的平均值、最大值和最小值。SQL語句如下：SELECT“男”AS

性別，AVG(入學成績)AS

入學平均分

MAX(入學成績)AS

入學最高分,MIN(入學成績)AS

入學最低分

FROM

學生

WHERE

性別="男"4.分組與計算查詢格式：GROUPBY<分組關(guān)鍵字1>[,<分組關(guān)鍵字2>…][HAVING<篩選條件>]說明：①分組關(guān)鍵字是分組的依據(jù),可以是字段名，也可以是SQL函數(shù)表達式，還可以是字段序號（從1開始）。②HAVING是對分組進行篩選的條件。HAVING只能與GROUPBY一起出現(xiàn)，不能單獨使用。例10.19：分別統(tǒng)計男、女學生人數(shù)和入學成績的最高分和平均分。SELECT

性別,COUNT(性別)AS

人數(shù)，MAX(入學成績)AS

入學最高分,AVG(入學成績)AS

入學平均分

FROM

學生GROUPBY

性別例10.20：在成績表中統(tǒng)計有6個以上學生選修的課程。SQL語句如下：SELECT

課程號,COUNT(*)AS

選課人數(shù)

FROM

成績

GROUPBY

課程號HAVINGCOUNT(*)>=6例10.21：對1992年以后出生的學生分別按專業(yè)統(tǒng)計入學成績，并輸出入學平均成績在560分以上的組。SELECT

專業(yè),AVG(入學成績)AS

入學平均分

FROM

學生

WHERE

出生日期>=#1988-01-01#GROUPBY

專業(yè)HAVINGAVG(入學成績)>=560說明：

HAVING與WHERE的區(qū)別在于：WHERE是對表中所有記錄進行篩選，HAVING是對分組結(jié)果進行篩選。在分組查詢中如果既選用了WHERE，又選用了HAVING，執(zhí)行的順序是先用WHERE限定記錄，然后對篩選后的記錄按GROUPBY指定的分組關(guān)鍵字分組，最后用HAVING子句限定分組。5.排序格式：ORDERBY<排序關(guān)鍵字1>[ASC|DESC][,<排序關(guān)鍵字2>[ASC|DESC]…][TOP<數(shù)值表達式>[PERCENT]]說明：①ASC表示對查詢結(jié)果按指定字段升序排序。②DESC表示對查詢結(jié)果按指定字段降序排序；ASC|DESC缺省時默認值是升序。③TOP必須與ORDERBY短語同時使用，表示從第一條記錄開始，顯示滿足條件的前N個記錄。選擇PERCENT短語時，數(shù)值表達式表示百分比。

例10.22：在學生表中查詢?nèi)雽W成績在前3名的學生信息。SQL語句如下：

SELECTTOP3*

FROM學生

ORDERBY

入學成績DESC例10.23：顯示年齡最小的30%的學生的信息。SQL語句如下：

SELECTTOP20PERCENT*

FROM

學生

ORDERBY

出生日期DESC

SELECT命令支持多表之間的連接查詢，并提供了專門的JOIN子句。格式：SELECT<字段名表>

FROM<表名1>[INNER

JOIN<表名2>ON

<連接接條件>][WHERE<篩選條件>]其中：INNERJOIN用來連接左右兩個<表名>指定的表，ON用來指定連接條件。三.連接查詢例10.24：在職工管理數(shù)據(jù)庫中查詢高級職稱（教授或副教授）教師的姓名、基本工資、津貼和所得稅。SQL語句如下：

SELECT

姓名,工資,津貼,所得稅

FROM

職工INNERJOIN

工資ON職工.職工號=工資.職工號

WHERE

職稱

IN（“教授”，“副教授”）或者：

SELECT

姓名,工資,津貼,所得稅

FROM

職工,工資

WHERE

職工.職工號=工資.職工號AND職稱IN（“教授”，“副教授”）“職工號”是職工表和工資表的公共字段，“職工.職工號=工資.職工號”是連接條件。INNERJOIN

子句還可以嵌套，即在一個INNERJOIN

之中，可以嵌套多個INNERON

子句。例10.25：輸出所有學生每門課程的綜合成績單，要求給出學號、姓名、課程名和綜合成績信息。SQL語句如下：

SELECT

學生.學號,姓名,平時*0.1+期中*0.2+期末*0.7AS

綜合成績

FROM

學生INNERJOIN(成績INNERJOIN

課程

ON

成績.課程號=課程.課程號)ON

學生.學號=成績.學號或者：

SELECT學生.學號,姓名,課程名,平時*0.1+期中*0.2+期末*0.7AS綜合成績

FROM

學生,成績,課程

WHERE

學生.學號=成績.學號AND

成績.課程號=課程.課程號說明：由于學號字段在兩個表中都出現(xiàn)，為了防止二義性，在其列名前加上表名作為前綴，以示區(qū)別。如果列名是唯一的，則不必加前綴。例10.26：按各門課程期末平均成績的降序輸出每位學生的學號和期末平均成績（保留小數(shù)1位）。SQL語句如下：

SELECT

成績.學號,ROUND(Avg(成績.期末),1)AS

期末平均成績

FROM

學生INNERJOIN

成績ON

學生.學號=成績.學號

GROUPBY

成績.學號

ORDERBY2DESC在SQL語言中，當一個查詢是另一個查詢的條件時，即在一個SELECT語句的WHERE子句中出現(xiàn)另一個SELECT語句，這種查詢稱為嵌套查詢。通常把內(nèi)層的查詢語句稱為子查詢，調(diào)用子查詢的查詢語句稱為父查詢。SQL語言允許多層嵌套查詢，即一個子查詢中還可以嵌套其他子查詢。子查詢的SELECT語句中不能使用ORDERBY子句，ORDERBY子句只能對最終查詢結(jié)果排序。四.嵌套查詢1．帶有比較運算符的子查詢帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符（>、<、=、>=、<=、<>）進行連接。例10.27：查詢所有參加“計算機”課程考試的學生的學號。SELECT學號FROM

成績WHERE

課程號=(SELECT

課程號FROM

課程

WHERE課程名="計算機")例10.28：檢索所有入學成績高于于海濤的學生的學號、姓名、性別和入學成績。

SELECT

姓名,性別,入學成績FROM

學生

WHERE

入學成績>(SELECT

入學成績

FROM

學生

WHERE

姓名="于海濤")

例10.29：顯示入學成績高于男生平均入學成績的女生的學號、姓名和平均成績。

SELECT

學號,姓名,入學成績

FROM

學生

WHERE

性別=“女”AND入學成績>=(SELECTAVG(入學成績)FROM

學生

WHERE

性別=“男”)2．帶有IN謂詞的子查詢格式：<字段名>[NOT]IN(<子查詢>)IN是屬于的意思，<字段名>指定的字段內(nèi)容屬于子查詢中任何一個值，運算結(jié)果都為真。

例10.30：查詢所有參加“計算機”課程考試的學生的學號、姓名和性別。

SELECT

學號,姓名,性別FROM學生

WHERE

學號IN(SELECT學號FROM

成績

WHERE

課程號=(SELECT

課程號FROM

課程

WHERE課程名="計算機")3．帶有ANY或ALL謂詞的子查詢格式：<字段名><比較運算符>[ANY|ALL]（<子查詢>）注意：使用ANY或ALL謂詞時必須同時使用比較運算符。例10.31：查詢?nèi)雽W成績高于女生最低入學成績的男生的學號、姓名和入學成績，SQL語句如下：

SELECT學號,姓名,性別,入學成績FROM學生

WHERE

性別=“男”AND

入學成績>ANY(SELECT

入學成績FROM學生

WHERE

性別=“女”）用聚合函數(shù)來實現(xiàn)：SELECT

學號,姓名,性別,入學成績FROM

學生WHERE

性別=“男”AND

入學成績>=(SELECTMIN(入學成績)FROM

學生

WHERE性別=“女”)用聚合函數(shù)實現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高。4．帶有EXISTS謂詞的子查詢格式：[NOT]EXISTS(<子查詢>)帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值（true）或邏輯假值（false），即是否存在相應(yīng)的記錄。例10.32：查詢參加了A02課程考試的學生學號和姓名。SQL語句如下：SELECT學號,姓名FROM學生WHEREEXISTS(SELECT*FROM成績

WHERE

成績.學號=學生.學號AND成績.課程號="A02");帶EXISTS的子查詢只返回真值或假值，其輸出項通常都用*例10.33：查詢參加了全部課程考試的學生學號和姓名。