數(shù)據(jù)庫SQL ServerSQLite教程課件：關(guān)系數(shù)據(jù)庫語言 SQL（上下）

關(guān)系數(shù)據(jù)庫語言SQL(上)4.1

SQL概述4.2

SQL的數(shù)據(jù)類型4.3數(shù)據(jù)庫定義4.4數(shù)據(jù)表定義

DevEcoStudio的功能如圖4-1所示，具體如下：

(1)多設(shè)備統(tǒng)一開發(fā)環(huán)境：支持多種HarmonyOS設(shè)備的應(yīng)用開發(fā)，包括手機(jī)、平板、車機(jī)、智慧屏、智能穿戴，輕量級智能穿戴和智慧視覺設(shè)備。

(2)支持多語言的代碼開發(fā)和調(diào)試：包括Java、XML(ExtensibleMarkupLanguage)、C/C++、JS(JavaScript)、CSS(CascadingStyleSheets)和HML(HarmonyOSMarkup

Language)。

(3)支持FA(FeatureAbility)和PA(ParticleAbility)快速開發(fā)：通過工程向?qū)Э焖賱?chuàng)建FA/PA工程模板，一鍵式打包成HAP(HarmonyOSAbilityPackage)。

(4)支持分布式多端應(yīng)用開發(fā)：一個工程和一份代碼可跨設(shè)備運(yùn)行，支持不同設(shè)備界面的實時預(yù)覽和差異化開發(fā)，實現(xiàn)代碼的最大化重用。

圖4-1DevEcoStudio功能x

4.1SQL概述

結(jié)構(gòu)化查詢語言(StructuredQueryLanguage，SQL)是1974年由Boyce和Chamberlin提出的。1975—1979年，最早是IBM的圣約瑟研究實驗室為其關(guān)系數(shù)據(jù)庫管理系統(tǒng)SYSTEMR開發(fā)的一種查詢語言，它的前身是SQUARE語言。經(jīng)過各公司的不斷修改、擴(kuò)充和完善，1986年美國頒布了SQL的美國標(biāo)準(zhǔn)，1987年國際標(biāo)準(zhǔn)化組織將SQL采納為國際標(biāo)準(zhǔn)，SQL最終成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。

SQL結(jié)構(gòu)簡潔，功能強(qiáng)大，簡單易學(xué)，自從IBM公司1981年推出以來，SQL得到了廣泛的應(yīng)用。SQLServer、Oracle、Sybase、Informix等大型的數(shù)據(jù)庫管理系統(tǒng)，Visual

Foxpro、PowerBuilder等微機(jī)上常用的數(shù)據(jù)庫開發(fā)系統(tǒng)，都支持SQL作為查詢語言。

SQL集數(shù)據(jù)定義(DataDefinition)、數(shù)據(jù)操縱(DataManipulation)和數(shù)據(jù)控制(DataControl)等功能于一體，充分體現(xiàn)了關(guān)系數(shù)據(jù)庫語言的特點和優(yōu)點。

SQL主要由以下幾部分組成：

(1)數(shù)據(jù)定義語言(DataDefinitionLanguage，DDL)。

(2)數(shù)據(jù)操縱語言(DataManipulationLanguage，DML)。

(3)數(shù)據(jù)查詢語言(DataQueryLanguage，DQL)。

(4)數(shù)據(jù)控制語言(DataControlLanguage，DCL)。

4.2SQL的數(shù)據(jù)類型

在計算機(jī)中數(shù)據(jù)有兩種特征：類型和長度，所謂數(shù)據(jù)類型就是以數(shù)據(jù)的表現(xiàn)方式和存儲方式來劃分?jǐn)?shù)據(jù)的種類。在SQLServer中，每個列、局部變量、表達(dá)式和參數(shù)都具有一個相關(guān)的數(shù)據(jù)類型。數(shù)據(jù)類型是一種屬性，用來設(shè)定某一個具體列保存數(shù)據(jù)的類型。數(shù)據(jù)類型可分為整數(shù)型、精確浮點型、近似浮點型、日期時間型等10種類型，下面依次介紹。

1.整數(shù)型

整數(shù)型的數(shù)據(jù)范圍及所占字節(jié)如表4-1所示。

2.精確浮點型

精確浮點型的數(shù)據(jù)范圍及所占字節(jié)如表4-2所示。

3.近似浮點型

近似浮點型的數(shù)據(jù)范圍及所占字節(jié)如表4-3所示。

4.日期時間型

日期時間型的格式、數(shù)據(jù)范圍及所占字節(jié)如表4-4所示。

5.字符型

字符型的數(shù)據(jù)范圍及所占字節(jié)如表4-5所示。

6.Unicode字符型

Unicode字符型的數(shù)據(jù)范圍及所占字節(jié)如表4-6所示。

7.二進(jìn)制字符型

二進(jìn)制字符型的數(shù)據(jù)范圍及所占字節(jié)如表4-7所示。

8.貨幣型

貨幣型的數(shù)據(jù)范圍及所占字節(jié)如表4-8所示。

9.特殊數(shù)據(jù)類型

特殊數(shù)據(jù)類型如表4-9所示。

10.用戶自定義數(shù)據(jù)類型

在該數(shù)據(jù)庫下創(chuàng)建“圖書”表時，就有了自定義的數(shù)據(jù)類型“pricedecimal”，其總位數(shù)為6，小數(shù)位2位。

4.3數(shù)據(jù)庫定義

4.3.1創(chuàng)建數(shù)據(jù)庫1.命令方式創(chuàng)建數(shù)據(jù)庫1)創(chuàng)建數(shù)據(jù)庫的最簡語法創(chuàng)建數(shù)據(jù)庫的最簡語法代碼如下：createdatabasedatabase_name在這種情況下，數(shù)據(jù)庫的參數(shù)設(shè)置都使用系統(tǒng)默認(rèn)值。

例4-1使用SQL語句創(chuàng)建學(xué)生成績數(shù)據(jù)庫studscore_db1，所有參數(shù)均取默認(rèn)值。

操作步驟如下：

(1)在SQLServerManagementStudio中，單擊“新建查詢”，新建SQLQuery1.sql文件。接下來，打開SQLQuery1.sql文件，如圖4-2所示。圖4-2“新建數(shù)據(jù)庫”快捷菜單

(2)在工作界面，在英文狀態(tài)下輸入“createdatabasestudscore_db1”，選定“createdatabasestudscore_db1”，單擊“”按鈕，經(jīng)“分析”提示“命令已成功”，表示沒有錯誤，否則提示錯誤信息，找出錯誤并修改。

(3)單擊“”按鈕，提示“命令已成功完成”。右擊“對象資源管理器”下面的“數(shù)據(jù)庫”，彈出快捷菜單，單擊“刷新”，可見數(shù)據(jù)庫studscore_db1，創(chuàng)建成功。

(4)單擊“保存”按鈕，保存SQLQuery1.sql文件(該腳本文件以后操作時還會用到，打開這個腳本文件，可以看到以前的操作)。

2)創(chuàng)建數(shù)據(jù)庫的完整語法

(1)on：用來存儲數(shù)據(jù)庫數(shù)據(jù)部分的磁盤文件(數(shù)據(jù)文件)。

(2)n：占位符，表示可重復(fù)前面的定義部分，即還可以有多個。

(3)logon：用來存儲數(shù)據(jù)庫日志的磁盤文件(日志文件)。

(4)primary：指定關(guān)聯(lián)的<filespec>列表定義主文件。

(5)name：為由<filespec>定義的文件指定邏輯名稱。有以下幾種形式：

例4-2創(chuàng)建數(shù)據(jù)庫studscore_ds1,數(shù)據(jù)文件和日志文件存放在D:\sq，主文件邏輯名稱studscore_ds1_data1,物理文件名studscore_ds1_data1.mdf,初始大小為5MB，最大為無

限大，增長速度10%，日志文件邏輯名稱tudscore_ds1_log1,物理文件名studscore_ds1_log1.ldf，初始大小3MB，最大8MB，增長速度1MB。

操作步驟如下：

第一步：在D盤創(chuàng)建文件夾sq，然后在SQLQuery1.sql文件中，輸入如下代碼：

第二步：選定上述代碼，單擊“”按鈕，提示“命令已成功完成”，再單擊“”按鈕。

第三步：鼠標(biāo)右擊“對象資源管理器”下的“數(shù)據(jù)庫”，彈出快捷菜單，再單擊“刷新”按鈕，可見創(chuàng)建的數(shù)據(jù)庫studscore_ds1。

例4-3創(chuàng)建數(shù)據(jù)庫studscore_ds2，包括3個數(shù)據(jù)文件、1個文件組和1個日志文件，自主設(shè)置參數(shù)值。

操作步驟同上，代碼如下：

⒉菜單方式創(chuàng)建數(shù)據(jù)庫

操作步驟如下：

(1)在“對象資源管理器”中找到“數(shù)據(jù)庫”節(jié)點，右擊該節(jié)點，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令，彈出如圖4-3所示的對話框。圖4-3“新建數(shù)據(jù)庫”對話框(1)

(2)在“新建數(shù)據(jù)庫”對話框的“常規(guī)”選項卡中，可輸入數(shù)據(jù)庫名稱、數(shù)據(jù)庫文件和事務(wù)日志文件的邏輯名稱，設(shè)置其初始大小、自動增長、路徑等參數(shù)，如圖4-4所示。

(3)單擊“確定”按鈕，數(shù)據(jù)庫文件創(chuàng)建成功。圖4-4“新建數(shù)據(jù)庫”對話框(2)

4.3.2刪除數(shù)據(jù)庫

1.命令方式刪除數(shù)據(jù)庫

在SSMS中可以用dropdatabase命令一次刪除一個或多個數(shù)據(jù)庫。只有數(shù)據(jù)庫所有者和數(shù)據(jù)庫管理員才有權(quán)執(zhí)行此命令。刪除數(shù)據(jù)庫語法如下：

dropdatabasedatabase_name[,...n]

例4-4刪除例4-1中創(chuàng)建的學(xué)生成績數(shù)據(jù)庫(studscore_db1)。

操作步驟如下：

(1)在SQLQuery1.sql中輸入命令：

dropdatabasestudscore_db1

(2)選中輸入的命令代碼，單擊“”按鈕，無誤則再單擊“”按鈕，顯示“命令已成功”，表示數(shù)據(jù)庫studscore_db1被刪除。

2.菜單方式刪除數(shù)據(jù)庫

在SSMS中可以用菜單方式刪除數(shù)據(jù)庫。例如，刪除在例4-1中創(chuàng)建的學(xué)生成績數(shù)據(jù)庫(studscore_db1)。操作步驟如下：

(1)在“對象資源管理器”下單擊“數(shù)據(jù)庫”前的“+”，展開數(shù)據(jù)庫文件夾。在數(shù)據(jù)庫“studscore_db1”上右擊鼠標(biāo)彈出快捷菜單，單擊“刪除”命令。

(2)進(jìn)入“刪除對象”對話框，勾選“關(guān)閉現(xiàn)有連接(C)”，再單擊“確定”按鈕，如圖4-5所示。圖4-5“刪除數(shù)據(jù)庫”對話框

4.3.3修改數(shù)據(jù)庫

1.命令方式修改數(shù)據(jù)庫

在SSMS中可以用alterdatabase命令來增加或刪除數(shù)據(jù)庫中的文件，修改文件的屬性。

1)語法格式

alterdatabase命令的語法格式如下：

2)實際操作

(1)向數(shù)據(jù)庫添加文件，包括數(shù)據(jù)文件、日志文件、文件組。

例4-5修改學(xué)生成績數(shù)據(jù)庫studscore_ds1，添加一個5MB的次數(shù)據(jù)文件studscore_ds1_data2。

代碼如下：

(2)刪除數(shù)據(jù)庫中的文件、文件組。

例4-6修改學(xué)生成績數(shù)據(jù)庫studscore_ds1，刪除次數(shù)據(jù)文件studscore_ds1_data2。

代碼如下：

alterdatabasestudscore_ds1

removefilestudscore_ds1_data2

2.菜單方式修改數(shù)據(jù)庫

在“對象資源管理器”下單擊“數(shù)據(jù)庫”前面的“+”，展開數(shù)據(jù)庫文件夾，在需要修改的數(shù)據(jù)庫上右擊鼠標(biāo)，彈出快捷菜單，單擊“屬性”選項，進(jìn)入“數(shù)據(jù)庫屬性”頁，

在“文件”“文件組”選項卡可以進(jìn)行數(shù)據(jù)庫文件、文件組的“添加”“刪除”等操作。

4.4數(shù)據(jù)表定義

數(shù)據(jù)定義語言(DDL)的主要功能是定義數(shù)據(jù)庫的模式，包括概念模式、外模式和內(nèi)模式。在SQL中對于不同的模式分別定義了一系列的語句。通過這些語句，數(shù)據(jù)庫管理員(DBA)可以創(chuàng)建和維護(hù)數(shù)據(jù)庫模式結(jié)構(gòu)。數(shù)據(jù)庫的三級模式結(jié)構(gòu)的核心是概念模式，它在SQL數(shù)據(jù)庫中表現(xiàn)為基本表的集合。

4.4.1數(shù)據(jù)表的構(gòu)成

一個數(shù)據(jù)表由表名、列和完整性約束構(gòu)成。具體如下：

1.表名

2.列

3.完整性約束

數(shù)據(jù)的完整性就是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)的準(zhǔn)確性和一致性，通過實體完整性、參照完整性、域完整性和用戶自定義完整性等完整性約束來實現(xiàn)。具體如下：

1)實體完整性

實體完整性也稱行完整性，要求表中不能有重復(fù)的行存在。

2)參照完整性

參照完整性也稱引用完整性，要求相關(guān)數(shù)據(jù)表中的數(shù)據(jù)保持一致性，即主鍵(被參照表)和外鍵之間的關(guān)系能夠得到維護(hù)。設(shè)置外鍵約束(ForeignKey)、存儲過程及觸發(fā)器等

方法加以實現(xiàn)。

如果被參考表(父表)中的一行被一個外鍵(books表—categorycode類別代碼)所參照，那么這一行數(shù)據(jù)便不能被直接刪除，用戶也不能直接修改主鍵(categories表—

categorycode)值，如圖4-6所示。圖4-6參照完整性

3)域完整性

域完整性也稱列完整性，指定列的輸入有效性。通過限制列的類型、格式、可能值的范圍等方法加以實現(xiàn)，如設(shè)置檢查約束(check)或規(guī)則。

4)用戶自定義完整性

所有完整性類別都支持用戶定義完整性，包括createtable中所有列級約束和表級約束、存儲過程、觸發(fā)器。例如，訂單表orders中，shipdate(發(fā)貨日期)不能早于rderdate(訂

貨日期)。

4.4.2創(chuàng)建表

1.命令方式創(chuàng)建表

1)創(chuàng)建表的命令和語法格式

創(chuàng)建數(shù)據(jù)庫后，需要使用SQL語句createtable創(chuàng)建數(shù)據(jù)表。其語法格式如下：

2)定義列和約束

首先，創(chuàng)建表時主要是對列的定義以及添加約束。

其次，表的約束按應(yīng)用范圍分為列級和表級約束。

例4-7創(chuàng)建學(xué)生情況表student，包括s#、sname、age、sex等列，帶主鍵、不允許空值、默認(rèn)值等約束。

代碼如下：

例4-8以創(chuàng)建學(xué)生情況表student為例，在創(chuàng)建表時分別使用列約束和表約束。此時，需要先刪除例4-7創(chuàng)建的表student。代碼如下：

3)創(chuàng)建帶約束的表

(1)primarykey約束。

在SQLServer中，主鍵(primarykey)保證實體完整性，可以是單列，也可以是多列組合。其特點為：一個表只能定義一個主鍵約束；主鍵約束所在列(或組合值)不允許輸

入重復(fù)值；所在列不允許取空值；主鍵約束自動在指定的列上創(chuàng)建了一個唯一性索引，默認(rèn)為聚集索引。

例4-9創(chuàng)建圖書分類表categories，用于存放圖書的類別信息，categorycode設(shè)為主鍵。

代碼如下：

說明：本例中，只有列級約束，而且該主鍵約束沒有指定約束名，由系統(tǒng)自動添加約束名。

例4-10創(chuàng)建選課成績表sc，用于存放學(xué)生的成績，其中s#和c#組成復(fù)合主鍵。代碼如下：

例4-11創(chuàng)建表orderitems，用于存放訂單項目信息，主鍵約束設(shè)置在orderid和bookid列上。代碼如下：

例4-12創(chuàng)建圖書信息表books，用于存放圖書信息，其中bookid設(shè)為主鍵，約束名pk_books_bookid，且bookid設(shè)為標(biāo)識列，由101開始每次自動增長1。代碼如下：

例4-13創(chuàng)建學(xué)生信息表studinfo，代碼如下：

例4-14創(chuàng)建班級信息表classinfo，代碼如下：

4-15此題在完成例4-16之后進(jìn)行，在customers表中，為customerid列添加主鍵約束，約束名為pk_customers_customerid。代碼如下：

(2)default約束。

默認(rèn)值(default)約束的語法格式如下：

default<值>

default約束的特點為：每個列只能定義一個默認(rèn)值；默認(rèn)值不能引用其他列或其他表、視圖或存儲過程；不能放在identity列或timestamp列。

例4-16創(chuàng)建用戶信息表customers，列rating設(shè)為默認(rèn)值約束，默認(rèn)值為5(常量)。

代碼如下：

例4-17使用insert命令向student表中添加一條記錄，輸入下列代碼，并執(zhí)行。

insertintostudent(s#,sname,age)values('2018010101','張真',20)

表student中列sex的值以默認(rèn)值自動填充為“男”，其結(jié)果如表4-10所示。

例4-18創(chuàng)建訂單表orders(orderid、orderdate、shipdate、customerid)，然后為orderdate列添加一個默認(rèn)值約束，默認(rèn)值為當(dāng)前系統(tǒng)時間，當(dāng)前系統(tǒng)時間由getdate()產(chǎn)生。代碼如下：

altertableorders

addconstraintdf_orders_orderdatedefaultgetdate()fororderdate

(3)unique約束。

唯一性(unique)約束是指表中的某一列或多列不能有相同的兩行或多行數(shù)據(jù)存在。其特點為：不能是主鍵約束所在列；每個表可以定義多個唯一性約束；約束所在列不允許輸

入重復(fù)值(或組合值不重復(fù))；所在列允許有空值；在指定列自動創(chuàng)建一個唯一性索引，默認(rèn)非聚集索引。

主鍵約束和唯一性約束既有相同點，也有不同點，具體如下：

相同點：關(guān)鍵字值不允許重復(fù)；創(chuàng)建唯一性索引來保證實體完整性。

不同點：是否取空值；可以定義一個還是多個約束。

unique約束的語法格式如下：

constraintconstraintnameunique[clustered|nonclustered][(column[,...])]

例4-21創(chuàng)建部門信息表department。注意，組合值具有唯一性約束。代碼如下：

nique約束對空值的處理：若唯一性約束列中有一列不為空，就實施約束；若唯一性約束列都為空，則不實施約束，如圖4-7所示。圖4-7唯一性約束

(4)foreignkey約束。

在SQLServer中，使用外鍵(foreignkey)保證參照完整性。外鍵約束用于建立和加強(qiáng)兩個表之間的連接的一列或多列，也就是表中某列值引用其他表的主鍵列或unique列。

外鍵表的被約束列的取值，必須是主鍵表的被約束列的值。

其特點為：每個表可以定義多個外鍵約束；外鍵表中被約束的列必須和主鍵表中被約束的列寬度一致、數(shù)據(jù)類型一致；外鍵約束不能自動創(chuàng)建索引；當(dāng)向設(shè)有外鍵約束的表(子表)中插入記錄或更新記錄時，該記錄被約束列的值必須在參照的主鍵表(父表)中存在。

foreignkey約束的語法格式如下：

參數(shù)說明：

·references：指定該外鍵參考哪個父表中的哪個主鍵列。

·ondelete：說明如果已創(chuàng)建表中的行具有參照關(guān)系，并且被引用行已從父表中刪除，則對這些行采取的操作。

·默認(rèn)noaction：表示數(shù)據(jù)庫引擎將引發(fā)錯誤，并回滾對父表中相應(yīng)行的刪除操作。

·cascade：表示級聯(lián)刪除，如果父表中刪除一行，則將從參照表中刪除相應(yīng)行。

·setnull：表示如果父表中對應(yīng)的行被刪除，則子表中組成外鍵的所有值將設(shè)置為null。若要執(zhí)行此約束，外鍵列必須可為空值。

·setdefault：表示如果父表中對應(yīng)的行被刪除，則子表中組成外鍵的所有值都將設(shè)置為默認(rèn)值。若要執(zhí)行此約束，所有外鍵列都必須有默認(rèn)值定義。如果某個列可為空值，

并且未設(shè)置顯式的默認(rèn)值，則會使用null作為該列的隱式默認(rèn)值。

·onupdate：用于說明如果表中發(fā)生更新的行有參照關(guān)系，并且被引用行在父表中已更新，則這些行將發(fā)生什么操作。(同上)

例4-26在noaction情況下，已知student和sc表的參照關(guān)系如圖4-8所示。圖4-8參照完整性

換句話說：

①在子表sc中插入記錄時，若主表中對應(yīng)的列值不存在，則插入出錯。

②刪除主表student中的記錄時，若有子表中的相應(yīng)記錄存在，也出錯。當(dāng)然，若設(shè)置了級聯(lián)刪除(ondeletecascade)，則可以執(zhí)行刪除操作。

(5)check約束。

檢查(check)約束用于驗證輸入數(shù)據(jù)的有效性，從而保證域完整性。其特點為：每個表可以定義多個檢查約束；可以參考本表中的其他列；檢查約束不能放在identity列或

timestamp列(它們自動插入數(shù)據(jù))；插入或更新記錄時，滿足條件才能錄入。

(6)identity列。

標(biāo)識(identity)列是由系統(tǒng)生成的標(biāo)識符列，序號值以唯一方式標(biāo)識表中的每一行。identity列的語法格式如下：

identity[(seed,increment)]

參數(shù)說明：seed為初值，increment為增量值；一個表只能有一個標(biāo)識列(int/decimal/numeric等類型的列)，不能是空值null,也不能包含default定義或?qū)ο蟆?/p>

例4-29創(chuàng)建表studbdinfo，seq_id設(shè)為標(biāo)識列，初始值為1001，增量為1。代碼如下

說明：seq_id的數(shù)據(jù)類型是int，可設(shè)置為標(biāo)識(identity)列；如果seq_id的數(shù)據(jù)類型是char，則不能設(shè)為標(biāo)識列。

2.菜單方式創(chuàng)建表

1)創(chuàng)建表

例4-30創(chuàng)建學(xué)生信息表student，表的結(jié)構(gòu)同前所述。

操作步驟如下：

(1)在“對象資源管理器”中單擊數(shù)據(jù)庫文件studscore_ds1前面的“+”，展開數(shù)據(jù)庫節(jié)點，右擊“表”的名稱或圖標(biāo)，在彈出的快捷菜單中選擇“新建表”命令，如圖4-9所示。圖4-9“新建表”快捷菜單

(2)圖4-10是“表設(shè)計器”工作界面。第1列的列名為s#，數(shù)據(jù)類型為varchar(10)且長度改為10，取消“允許Null...”復(fù)選框中的“√”。s#列是表的主鍵，定義方法是單擊“表設(shè)計器”菜單下的“設(shè)置主鍵”命令，或鼠標(biāo)右擊s#列，在彈出的快捷菜單中選擇“設(shè)置主鍵”命令，在該列的前面出現(xiàn)一個金色的鑰匙圖標(biāo)，表示該列是表的主鍵。圖4-10“表設(shè)計器”工作界面

(3)依次設(shè)置表的其他列，如sname、age、sex等。表的列設(shè)計好以后，單擊工具欄上的“保存”按鈕，或右擊表的名稱，在彈出的“選擇名稱”對話框中輸入表名稱“student”，然后單擊“確定”按鈕，創(chuàng)建的表就被保存起來。

2)創(chuàng)建帶標(biāo)識列的表

例4-31創(chuàng)建表orders，其中訂單編號orderid是一個標(biāo)識列，而且是主鍵。orderdate列是訂貨日期，通常訂貨日期與系統(tǒng)日期相同，因此為該列定義一個默認(rèn)值，shipdate是發(fā)貨日期，customerid是訂貨的顧客的編號。shipdate可以為空值。

操作步驟如下：

(1)在“對象資源管理器”中單擊數(shù)據(jù)庫文件studscore_ds1前面的“+”，展開數(shù)據(jù)庫節(jié)點，右擊“表”的名稱或圖標(biāo)，在彈出的快捷菜單中選擇“新建表”命令。

(2)在“表設(shè)計器”界面中，第1列的列名為orderid，數(shù)據(jù)類型選擇int，取消“允許Null...”復(fù)選框中的“√”。在“列屬性”列表框中，展開“標(biāo)識規(guī)范”節(jié)點，將“(是標(biāo)識)”屬性值改為“是”，“標(biāo)識增量”和“標(biāo)識種子”的值分別設(shè)為101、1。按此前的方法設(shè)置orderid為主鍵，如圖4-11所示。圖4-11設(shè)置“標(biāo)識列”屬性

(3)第2列為orderdate，數(shù)據(jù)類型為datetime，不允許空值。接下來為該列添加一個默認(rèn)值約束。在“列屬性”框中，展開“常規(guī)”節(jié)點，在“默認(rèn)值或綁定”屬性后面輸入

getdate()，該函數(shù)的作用是獲取當(dāng)前系統(tǒng)日期和時間，如圖4-12所示。圖4-12設(shè)置“默認(rèn)值”屬性

(4)第3列為shipdate，數(shù)據(jù)類型為datetime，允許空值，設(shè)置一個檢查約束，要求發(fā)貨日期大于訂單日期。

在“表設(shè)計器”中，在shipdate處單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“CHECK約束”命令，打開“CHECK約束”對話框，如圖4-13所示。

(5)依次輸入其他列，然后單擊“保存”按鈕，輸入表名稱，單擊“確定”按鈕，帶約束的表創(chuàng)建完成。圖4-13設(shè)置CHECK約束

4.4.3修改表

1.命令方式修改表

altertable命令可以添加或刪除表的列、約束，也可以禁用或啟用已存在的約束或觸發(fā)器。語法格式如下：

1)修改列

語法格式如下：

altertable<表名>

altercolumn<列定義>

說明：<列定義>與createtable中相同，但是列名不能修改。

例4-32在表中有數(shù)據(jù)記錄的情況下，修改列的數(shù)據(jù)類型、長度、允許Null…。代碼如下：

2)增加列

語法格式如下：

altertable<表名>

add<列定義>

例4-33在表中有數(shù)據(jù)記錄的情況下，增加列并設(shè)置約束。代碼如下：

3)刪除列

語法格式如下：

altertable<表名>

dropcolumn<列名>

4)增加或刪除約束

(1)增加表約束。語法格式如下：

altertable<表名>

add<表約束>

說明：只能增加表約束，且表約束格式與創(chuàng)建表時相同。如果需要修改原來的約束，則必須先刪除原約束，然后再添加新的約束。

(2)刪除表約束。語法格式如下：

dropconstraint<約束名>

2.菜單方式修改表

創(chuàng)建的表只是表的結(jié)構(gòu)，因此修改表也是修改表的結(jié)構(gòu)，還可以建立表的外鍵關(guān)系。以表books(包括bookid、categorycode列)和表categories(包括categorycode、categoryname列)為例進(jìn)行介紹。

1)修改表結(jié)構(gòu)

(1)在“對象資源管理器”中展開數(shù)據(jù)庫節(jié)點和表節(jié)點，鼠標(biāo)右擊已經(jīng)存在的表(如categories表)，在彈出的快捷菜單中選擇“設(shè)計”命令，如圖4-14所示。

(2)在“表設(shè)計器”工作界面，可以修改表的結(jié)構(gòu)，如列名、數(shù)據(jù)類型及其長度、主鍵。然后單擊工具欄的“保存”命令，或用鼠標(biāo)右擊文件名，單擊“保存”命令。

圖4-14表“設(shè)計”菜單

2)添加外鍵約束

(1)在“對象資源管理器”中展開數(shù)據(jù)庫節(jié)點和表節(jié)點，在表books上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)計”命令，打開一個選項卡顯示表books的定義。

(2)在表books的定義中，在categorycode列單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“關(guān)系”命令，或在“表設(shè)計器”菜單中單擊“關(guān)系”命令，彈出“外鍵關(guān)系”對話框，

單擊“添加”按鈕，在“選定的關(guān)系”列表框中添加一個新的關(guān)系，在“標(biāo)識”節(jié)點修改關(guān)系名稱為FK_books_categories。

(3)選中這個新添加的關(guān)系，再右側(cè)單擊選中“表和列規(guī)范”節(jié)點并單擊節(jié)點后面的“...”按鈕，彈出“表和列”對話框，其中“外鍵表”是固定的books表，選擇外鍵所在

列categorycode；“主鍵表”選擇表categories，主鍵所在的列選擇categorycode列，如圖4-15所示。

(4)單擊工具欄的“保存”按鈕，保存對books表的修改。圖4-15“外鍵關(guān)系”對話框

4.4.4刪除表

1.命令方式刪除表

droptable語句的功能是刪除基本表(表所包含的記錄也隨之刪除)。

語法格式如下：

droptable<表名>

說明：如果表中存在foreignkey約束，則需要先刪除外鍵約束。

例如：刪除學(xué)生成績表studscoreinfo。代碼如下：

droptablestudscoreinfo

2.菜單方式刪除表

在“對象資源管理器”中展開數(shù)據(jù)庫節(jié)點和表節(jié)點，用鼠標(biāo)右鍵單擊相關(guān)的表，在彈出快捷菜單中選擇“刪除”命令，彈出“刪除對象”對話框，單擊“確定”按鈕，將刪除

選定的表。

在“刪除對象”對話框中單擊“顯示依賴關(guān)系”，如果有外鍵約束，則先刪除外鍵約束然后才能刪除該表，否則單擊“確定”按鈕后，顯示消息“刪除對...表失敗”。關(guān)系數(shù)據(jù)庫語言SQL(下)5.1數(shù)據(jù)操作語言(DML)5.2數(shù)據(jù)查詢語言(DQL)5.3數(shù)據(jù)控制語言(DCL)

5.1數(shù)據(jù)操作語言(DML)

5.1.1數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)SQL使用insert語句為數(shù)據(jù)表添加記錄。insert語句通常有兩種形式：一種是一次插入一條記錄，另一種是一次插入多條記錄，即使用子查詢批量插入。

參數(shù)說明：

·insertinto是插入語句的命令關(guān)鍵詞，其中into可以省略。tablename指定要向其中插入數(shù)據(jù)的表的名稱。columnlist是列列表，用來指定要向其中插入數(shù)據(jù)的列，列和列之間用逗號分開。

·values用于引出要插入的數(shù)據(jù)，columnvalue是數(shù)據(jù)表達(dá)式列表，數(shù)據(jù)項之間需要用逗號分開。

向表中插入數(shù)據(jù)應(yīng)注意以下幾點：

(1)數(shù)據(jù)表達(dá)式列表columnvalue中的數(shù)據(jù)值應(yīng)該與列列表columnlist中的列一一對應(yīng)，數(shù)據(jù)類型也應(yīng)該兼容。

(2)必須為表中所有定義notnull的列提供值，對于定義為null的列既可以提供值也可以不提供值。

(3)如果表中存在標(biāo)識列，則不能向標(biāo)識列中插入數(shù)據(jù)。如果表中有計算列，則不能向計算列中插入值。

(4)因為主鍵所在列不允許有空值也不允許有重復(fù)值，所以插入數(shù)據(jù)時必須保證主鍵所在列中有值而且不能與該列中已經(jīng)存在的值重復(fù)。

(5)如果表中存在外鍵約束，則向表中插入數(shù)據(jù)時要注意避免違反參照完整性約束。

在接下來的例子中將向表books中插入數(shù)據(jù)。先分析一下books表的特點，表中包括6個列，即bookid、title、isbn、author、unitprice和categorycode。其中：bookid是主鍵、

int類型；title、isbn和author被定義成notnull、字符型；categorycode是一個外鍵，父表是categories，存放圖書的類別，categories表中已經(jīng)存在數(shù)據(jù)，如圖5-1所示。

圖5-1categories表

⒉菜單方式插入數(shù)據(jù)

在SSMS中，除了用insert語句插入記錄，還可以使用菜單方式插入記錄。

例5-4向student表中插入記錄。其中有一個主鍵約束PK_s#、一個檢查約束CK_

student_email(默認(rèn)格式：emaillike'%_@%_._%')。

操作步驟如下：

(1)在“對象資源管理器”中，展開“數(shù)據(jù)庫”節(jié)點和“表”節(jié)點，用鼠標(biāo)右鍵單擊student表，在彈出的快捷菜單中選擇“編輯前200行”，進(jìn)入編輯界面，如圖5-2所示。

(2)依次輸入各行字段的值，單擊“保存”按鈕。圖5-2“編輯前200行”工作界面

5.1.2數(shù)據(jù)更新

SQL使用update語句更新或修改滿足規(guī)定條件的記錄。

說明：將符合where條件的記錄的一個或多個列修改為新值。若省略where，則全表更新。operator即運(yùn)算符。

例5-5在數(shù)據(jù)庫studscore_ds1中，將表student中s#是2018010103的學(xué)生的age加1。代碼如下：

updatestudent

setage=age+1wheres#='2018010103'

說明：如果省略where條件，則所有記錄加1歲。

例5-6將學(xué)生John的性別改為“男”，年齡改為“23”。代碼如下：

updatestudent

setsex='男',age=23wheresname='John'

5.1.3數(shù)據(jù)刪除

SQL使用delete語句刪除數(shù)據(jù)庫表格中的行或記錄。

1.delete語句

語句格式如下：

deletefrom<表名>where<條件>

說明：將符合<條件>的記錄從表中刪除。

例5-7：在數(shù)據(jù)庫studscore_ds1中，將表student中學(xué)號s#為2018010101的學(xué)生刪除。

代碼如下：

deletefromstudentwheres#='2018010101'

說明：如果省略where條件，則刪除表中所有的記錄。

2.truncatetable命令

如果要刪除表中的所有數(shù)據(jù)記錄，則使用truncatetable命令比用delete命令快得多，這是因為delete命令除了刪除數(shù)據(jù)外，還會對刪除數(shù)據(jù)在事務(wù)處理日志中作出記錄，以便

刪除失敗時可以使用事務(wù)處理日志來恢復(fù)數(shù)據(jù)。而truncatetable命令的功能相當(dāng)于使用不帶where子句的delete命令。

語句格式如下：

truncatetabletable_name

例5-8刪除學(xué)生成績表studscoreinfo中的所有記錄。代碼如下：

truncatetablestudscoreinfo

需要指出的是，truncatetable命令不能用于有依賴關(guān)系的表，也不能激發(fā)觸發(fā)器。

5.1.4merge語句

1.功能

merge關(guān)鍵字是在SQLServer2008引入的DML關(guān)鍵字，它能將insert、update、delete簡單地并為一句。MSDN對merge的解釋是：根據(jù)與源表連接的結(jié)果，對目標(biāo)表執(zhí)行插入、更新或刪除操作。

merge的功能是：檢查原數(shù)據(jù)表記錄和目標(biāo)表記錄，如果記錄在原數(shù)據(jù)表和目標(biāo)表中均存在，則目標(biāo)表中的記錄將被原數(shù)據(jù)表中的記錄更新(執(zhí)行update操作)；如果目標(biāo)表

中不存在的某些記錄在原數(shù)據(jù)表中存在，則原數(shù)據(jù)表的這些記錄將被插入到目標(biāo)表中(執(zhí)行insert操作)。

2.語法格式及示例

參數(shù)說明：

第一行merge子句：命名目標(biāo)表并給出別名。

第二行using子句：提供merge操作的數(shù)據(jù)源，并給出別名。

第三行on子句：指定合并的條件。

第四行whenmatchedthen子句：判斷條件符合則對目標(biāo)表更新或刪除。

第八行whennotmatchedthen子句：判斷條件不符合則執(zhí)行插入的操作。

5.2數(shù)據(jù)查詢語言(DQL)

5.2.1SQL簡單查詢1.查詢結(jié)構(gòu)1)查詢的語法格式

上述語法中共有7個子句，其中select和from子句是必不可少的。各子句的功能如下：

(1)select_list子句用于指定希望查看的列，中間用逗號分隔。

(2)intonew_table_name子句用于將檢索出來的結(jié)果集創(chuàng)建一個新的數(shù)據(jù)表。

(3)fromtable_list子句用于指定檢索數(shù)據(jù)的數(shù)據(jù)表的列表。

(4)where<條件>子句用于對數(shù)據(jù)行進(jìn)行篩選，指定查詢的條件，是一個條件表達(dá)式，只有滿足條件的數(shù)據(jù)行才作為查詢的對象。

(5)groupby<分組列名表>子句用于指定要分組的列。

(6)having<條件>子句用于指定分組的條件。從結(jié)果集對記錄進(jìn)行篩選，只有滿足條件表達(dá)式的組才作為查詢的對象。

(7)orderby<排序列名表>子句用于對查詢的結(jié)果排序。asc表示升序排序，desc表示降序排序。asc是默認(rèn)選項。

2)select查詢的執(zhí)行過程

雖然select查詢的各個子句書寫的順序是select→from→where→groupby→having→orderby，但是在計算機(jī)中各個子句實際的執(zhí)行順序是from→where→group

by→having→select→orderby。也就是說首先確定從哪個或哪些表(或視圖)中查詢數(shù)據(jù)，如有必要就篩選，如有必要就分組，還有必要再對分組進(jìn)行篩選。接下來確定查詢結(jié)果，如有排序要求就對查詢結(jié)果進(jìn)行排序。其過程如下：

(1)讀取from子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。例如，從兩張表中取數(shù)，對比記錄數(shù)、兩張表記錄數(shù)的乘積數(shù)，理解笛卡爾積。

(2)選取滿足where子句中給出的條件表達(dá)式的元組。

(3)按照groupby子句中指定列的值進(jìn)行分組，同時提取滿足having子句中組條件表達(dá)式的那些組。

(4)按照select子句中給出的列名或列表達(dá)式求值輸出。

(5)orderby子句對輸出的目標(biāo)表進(jìn)行排序，按asc(升序)排列，或按desc(降序)排列。

3)使用select查詢應(yīng)注意的問題

(1)在數(shù)據(jù)庫系統(tǒng)中，可能存在對象名稱重復(fù)的現(xiàn)象。例如，兩個用戶同時定義了studinfo的表，在引用用戶ID為stud的用戶定義的studinfo表時，需要使用用戶ID限定

數(shù)據(jù)表的名稱。語法代碼如下：

select*fromstud.studinfo

(2)在使用select語句進(jìn)行查詢時，需要引用的對象所在的數(shù)據(jù)庫不一定總是當(dāng)前的數(shù)據(jù)庫，在引用數(shù)據(jù)表時需要使用數(shù)據(jù)庫來限定數(shù)據(jù)表的名稱。語法代碼如下：

select*fromstudscore_ds1.dbo.studinfo

select*fromstudscore_ds1..studinfo

(3)在from子句中指定的數(shù)據(jù)表和視圖可能包含有相同的字段名稱，外鍵字段名稱很可能與相應(yīng)的主鍵字段名稱相同。因此，為避免字段引用時的歧義，必須使用數(shù)據(jù)表或視

圖名稱來限定字段名稱。語法代碼如下：

selectstudinfo.studno,studname,classinfo.classid,classname

fromstudinfo,classinfo

wherestudinfo.classid=classinfo.classid

2.查詢操作

1)select子句

select子句指定需要通過查詢返回的表的列。

參數(shù)說明：

(1)all：指明查詢結(jié)果中可以顯示值相同的列，all是系統(tǒng)默認(rèn)的選項。

(2)select_list：指所要查詢的表的列的集合，多個列之間用逗號分開。

(3)*：通配符，返回所有對象的所有列。

(4)table_name|view_name|table_alias.*：限制通配符*的作用范圍，凡是帶*的項均返回其中所有的列。

(5)column_name：指定返回的列名。

(6)expression：表達(dá)式可能為列名常量、函數(shù)或它們的組合。此時應(yīng)給表達(dá)式指定一個別名，通常有3種方式：原列名as別名、原列名別名、別名=原列名。在一個查詢語

句中，也可以混合使用以上3種方式來定義別名。

(7)identitycol：返回identity列。如果from子句中有多個表含有identity列，則在identitycol選項前必須加上表名，如table.identitycol。

(8)rowguidcol：返回表的rowguidcol列，同identitycol選項。當(dāng)要指定多個rowguidcol列時，選項前要加上表名。

(9)column_alias：在返回的查詢結(jié)果中用此別名替代列的原名。column_alias可用于orderby子句，但不能用于where、groupby、having子句。

例5-13查詢學(xué)生信息表studinfo中不重復(fù)的性別記錄。查詢結(jié)果如圖5-3所示。代碼如下：

selectdistinctstudsexfromstudinfo

圖5-3distinct關(guān)鍵字

2)into子句

intonew_table_name子句用于將查詢的結(jié)果集創(chuàng)建一個新的表。新表的列由select子句中指定的列構(gòu)成，且查詢結(jié)果各列必須具有唯一的名稱。新表中的數(shù)據(jù)是由where子句

指定的，但如果select子句中指定了計算列，在新表中對應(yīng)的列則不是計算列，而是一個實際存儲在新表中的列，其中的數(shù)據(jù)由執(zhí)行select…into語句時計算得出。

例5-19查詢表studscoreinfo中courseid為a0101的記錄，并插入到新表stscore_1。代碼如下：

select*intostscore_1fromstudscoreinfowherecourseid='a0101'

3)from子句

from子句主要用來指定檢索數(shù)據(jù)的來源，指定數(shù)據(jù)來源的數(shù)據(jù)表和視圖的列表，該列表中的表名和視圖名之間用逗號分開。from子句不可省略。

語法格式如下：

from{<table_sourse>}[,...n]

例5-20使用表別名查詢表studinfo中的記錄。代碼如下：

selects.studno學(xué)號,s.studname姓名fromstudinfos

4)where子句

where子句用于對表中的數(shù)據(jù)記錄進(jìn)行篩選，其中構(gòu)造篩選的條件表達(dá)式是重點。需要強(qiáng)調(diào)的是，在where子句中不能使用聚合函數(shù)及別名。

語法格式如下：

where<search_condition>

功能：限制結(jié)果集內(nèi)返回的行。

查詢的限制條件可以是比較運(yùn)算符(=、<>、<、>、>=等)、范圍說明(betweenand和notbetweenand)、可選值列表(in、notin)、模式匹配(like、notlike)、是否為空值(is

null和isnotnull)、上述條件的邏輯組合(and、or、not)。分別介紹如下：

(1)使用比較查詢條件。比較查詢條件由表達(dá)式的雙方和比較運(yùn)算符組成，系統(tǒng)根據(jù)查詢條件的真假來決定某一條記錄是否滿足查詢條件。只有滿足查詢條件的記錄才會出現(xiàn)

在最終結(jié)果集中。

例5-21查詢成績大于70的學(xué)生成績信息。代碼如下：

select*fromstudscoreinfowherestudscore>70

例5-22查詢1981年1月1日及以后出生的學(xué)生信息。代碼如下：

select*fromstudscoreinfowherestudbirthday>='1981/01/01'

(2)使用邏輯運(yùn)算符。and連接兩個布爾表達(dá)式并當(dāng)兩個表達(dá)式都為true時返回“true”。or將兩個條件結(jié)合起來。not用于反轉(zhuǎn)查詢條件的結(jié)果。其優(yōu)先級順序是：括號最優(yōu)先，其次not＞and＞or。

例5-23查詢學(xué)生成績在60到70之間的所有記錄。代碼如下：

select*fromstudscoreinfowherestudscore＞=60andstudscore＜=70

例5-24查詢學(xué)生成績小于等于70或者大于等于90的所有記錄。代碼如下：

select*fromstudscoreinfowherestudscore＜=70orstudscore＞=90

(3)使用范圍查詢條件。內(nèi)含范圍條件(between…and)要求返回記錄某個字段的值在兩個指定值范圍內(nèi)，同時包括這兩個指定的值。排除范圍條件(notbetween…and)則相反。

例5-25查詢學(xué)生成績在70到80之間的記錄。代碼如下：

select*fromstudscoreinfowherestudscorebetween70and80

(4)使用列表查詢條件。in關(guān)鍵字的格式為：in(列表值1，列表值2，…)。其功能是將返回所有與列表中的任意一個值匹配的記錄。

例5-26查詢課程代碼courseid為a0101、a0102的學(xué)生成績信息。代碼如下：

select*fromstudscoreinfowherecourseidin('a0101','a0102')

(5)使用模式查詢條件(like或notlike)。模式查詢條件常用來返回符合某種格式的所有記錄。模式匹配通配符是like，另外還需要使用模式通配符，如表5-1所示。

(6)使用空值判斷查詢?？罩挡樵兂Ｓ糜诓樵兡骋蛔侄螢榭罩档挠涗?，可以使用“isnull”(是空值)或“isnotnull”(不是空值)關(guān)鍵字來指定查詢條件。

在表的某些列可能存在空值“null”。“null”不是一種值，表示一種未知或不確定的狀態(tài)，它并不表示零、零長度的字符串或空白(字符值)。

例5-29在班級信息表classinfo中，查詢班級描述為空的班級情況。代碼如下：

select*fromclassinfowhereclassdescisnull

5)groupby子句

有時需要對表中的數(shù)據(jù)進(jìn)行分組，然后對每個組單獨進(jìn)行統(tǒng)計計算，此時需要使用groupby子句。在按照指定的條件進(jìn)行分類計算時，可以使用聚合函數(shù)計算各組的數(shù)據(jù)。

語法格式如下：

groupby[all]group_by_expression[,...n]

其中：group_by_expression是對表執(zhí)行分組的表達(dá)式，也稱分組列。

常用的聚合函數(shù)及其含義如表5-2所示。

6)having子句

having子句用于指定分組搜索條件，是對分組之后的結(jié)果再次篩選。having子句必須和groupby子句一起使用，有having子句就必須有g(shù)roupby子句，但有g(shù)roupby子句可以沒有having子句。

having和where類似，其區(qū)別在于where子句在進(jìn)行分組操作之前對查詢結(jié)果進(jìn)行篩選，而having子句是對分組操作之后的結(jié)果再次篩選。作用的對象也不同，where子句作用于表和視圖，having子句作用于組。

7)orderby子句

orderby子句指定查詢結(jié)果的排序方式。其語法格式如下：

orderby{order_by_expression[asc|desc]}[,...n]

order_by_expression可以是表或視圖的列的名稱或別名。asc表示升序(默認(rèn))；desc表示降序。

5.2.2SQL高級查詢

1.關(guān)聯(lián)表查詢

SQL簡單查詢是基于單個數(shù)據(jù)表來實現(xiàn)的。在數(shù)據(jù)庫中，各個表存放著不同的數(shù)據(jù)，表和表之間存在著各種聯(lián)系，往往需要用多個表中的數(shù)據(jù)來組合查詢，補(bǔ)充所需要的信息。

所謂多表查詢是相對于單表查詢而言的，是指從多個關(guān)聯(lián)表中查詢數(shù)據(jù)，通常采用等值多表查詢的方式，即在where子句中設(shè)置等值的條件來查詢多個數(shù)據(jù)表中關(guān)聯(lián)的數(shù)據(jù)。這種

查詢要求關(guān)聯(lián)的多個數(shù)據(jù)表的某些字段具有相同的屬性，即具有相同的數(shù)據(jù)類型和寬度。

1)雙表關(guān)聯(lián)查詢

在where子句中，可以將具有相等的字段值的兩張表連接起來，數(shù)據(jù)來源于兩張表。

例5-38查詢某班級學(xué)生的基本信息和成績信息，數(shù)據(jù)來源于表student和表sc，代碼如下：

select*fromstudent,scwherestudent.s#=sc.s#

查詢結(jié)果如圖5-4所示。圖5-4等值查詢

例5-39查詢某班級學(xué)生的基本信息和成績信息，包括s#、sname、age、c#、score等字段，數(shù)據(jù)來源于student表和sc表。代碼如下：

selectstudent.s#,sname,age,c#,scorefromstudent,scwherestudent.s#=sc.s#

例5-40使用別名、邏輯運(yùn)算符查詢滿足復(fù)雜條件的記錄，結(jié)果如圖5-5所示。代碼如下：

selects.s#學(xué)號,sname姓名,c#課程代碼,score成績fromstudents,scwheres.s#=sc.s#andc#='001'圖5-5雙表別名查詢

2)多表關(guān)聯(lián)查詢

有時需要將多個表進(jìn)行關(guān)聯(lián)查詢，才能比較完整地反映有關(guān)信息。超過兩個表的關(guān)聯(lián)查詢稱為多表查詢，返回多個表中與連接條件相互匹配的記錄，不返回不相匹配的記錄。

例5-41根據(jù)圖5-6所示的student、sc和course表，查詢學(xué)生的基本信息，包括個人基本情況、課程信息和成績。代碼如下：

selects.s#學(xué)號,sname姓名,sc.c#課程代碼,ame課程名稱,c.credit學(xué)分,score成績from

students,coursec,sc

wheres.s#=sc.s#andsc.c#=c.c#andsc.c#='001'圖5-6sc表(s#和c#是雙屬性主鍵，c#是外鍵)

查詢結(jié)果如圖5-7所示。圖5-7多表關(guān)聯(lián)查詢結(jié)果

3)關(guān)聯(lián)表使用聚合函數(shù)

在單表查詢中，可以使用聚合函數(shù)進(jìn)行統(tǒng)計，但統(tǒng)計結(jié)果的信息不夠全面，需要使用多表查詢補(bǔ)齊相關(guān)信息。在多表關(guān)聯(lián)查詢中，仍可以使用聚合函數(shù)進(jìn)行統(tǒng)計。

例5-42在student、sc等表中，查詢學(xué)生的學(xué)號、姓名、平均分等字段信息。代碼如下：

selects.s#,s.sname,avg(score)asavgscorefromstudents,scwheres.s#=sc.s#groupbys.s#,sname

例5-42中，兩表通過學(xué)號關(guān)聯(lián)，因為兩表均有學(xué)號字段，所以為student表指定別名，以別名對學(xué)號字段進(jìn)行限制。使用了groupby子句，只有g(shù)roupby后面的字段和聚合函數(shù)才能放在select子句后面，因此，除學(xué)號之外，姓名字段也必須放在groupby子句后面。

2.使用union連接

使用union運(yùn)算符可以將兩個或多個select子句的結(jié)果組合成一個結(jié)果集。使用union組合的結(jié)果集都必須滿足三個條件：具有相同的結(jié)構(gòu)，字段數(shù)目相同，結(jié)果集中相應(yīng)字段的數(shù)據(jù)類型必須兼容。同時還要注意以下幾點：

(1)union中每一個查詢所涉及的列必須具有相同的列數(shù)、相同的數(shù)據(jù)類型，并以相同的順序出現(xiàn)。

(2)最后結(jié)果集里的列名來自第一個select語句。

(3)若union中包含orderby子句，則將對最后的結(jié)果集排序。

(4)在合并結(jié)果集時，默認(rèn)從最后的結(jié)果集中刪除重復(fù)的行，除非使用all關(guān)鍵字。

union運(yùn)算符的語法格式如下：

select子句

union[all]

select子句

例5-43查詢成績在60～70分?jǐn)?shù)段和90及以上區(qū)域的學(xué)生信息。代碼如下：

select*fromscwherescore>=60andscore<=70

unionall

select*fromscwherescore>=90

3.子查詢

1)子查詢的基本概念

在SQL中，當(dāng)一個查詢語句嵌套在另一個查詢的查詢條件之中時，該查詢稱為嵌套查詢，又稱為子查詢。在一個外層查詢中包含有另一個內(nèi)層查詢，其中外層查詢稱為主查

詢，內(nèi)層查詢稱為子查詢。

使用子查詢時應(yīng)注意以下幾點：

(1)子查詢的基本結(jié)構(gòu)和基本查詢一樣，其中select子句和from子句是必需的，而where子句、groupby子句和having子句是可選的。

(2)子查詢的select語句通常使用圓括號括起來。

(3)子查詢的select語句中通常只有一個列，不能使用compute子句。

(4)除非在子查詢中使用了top選項，否則子查詢中不能使用orderby子句。

(5)如果某個數(shù)據(jù)表只出現(xiàn)在子查詢中，而不出現(xiàn)在主查詢中，那么在數(shù)據(jù)列表中不能包含該數(shù)據(jù)表中的字段。

2)子查詢的使用

(1)使用in關(guān)鍵字。當(dāng)子查詢的結(jié)果不唯一時，可以在子查詢前使用運(yùn)算符in。in關(guān)鍵字在大多數(shù)情況下應(yīng)用于嵌套查詢中，首先使用select語句選定一個范圍，然后將選定的范圍作為in關(guān)鍵字的符合條件的列表，從而得到最終的結(jié)果。

語法格式如下：

test_expression[not]in(subquery|expression[,...n])

參數(shù)說明：

①test_expression是任何有效的SQLServer表達(dá)式。

②subquery是包含某列結(jié)果集的子查詢。expression是一個表達(dá)式列表，用來測試是否匹配。

(2)使用比較運(yùn)算符的子查詢。使用比較運(yùn)算符的子查詢的結(jié)果必須是單值，即子查詢的結(jié)果為單行單列的值。

例5-47查詢ISBN為“978-7-1254-2487-1”的圖書的訂單號和訂貨數(shù)量。代碼如下：

(3)使用some/any關(guān)鍵字。some/any關(guān)鍵字完全等價。通過比較運(yùn)算符將一個表達(dá)式的值或列值與子查詢返回的一列值中的每一個進(jìn)行比較，如果哪行的比較結(jié)果為真，則滿

足條件立即返回該行。

語法格式如下：

scalar_expression{=|<>|!=|>|>=|!>|<=|!<}

{some|any}(subquery)

參數(shù)說明：

①scalar_expression：任何有效的SQLServer表達(dá)式。

②{=|<>|!=|>|>=|!>|<=|!<}：任何有效的比較運(yùn)算符。

③{some|any}：指定應(yīng)進(jìn)行比較。當(dāng)子查詢的結(jié)果為多值時，使用some|any表示匹配子查詢結(jié)果中的任意一個值即可。

④subquery：包含某列結(jié)果集的子查詢。所返回列的數(shù)據(jù)類型必須是與scalar_expression相同的數(shù)據(jù)類型。

(4)使用all關(guān)鍵字。all的子查詢是把列值與子查詢結(jié)果進(jìn)行比較，但是它要求所有列的查詢結(jié)果都為真，否則不返回行。使用all表示匹配子查詢的所有值才可以。

語法格式如下：

scalar_expression{=|<>|!=|>|>=|!>|<=|!<}all(subquery)

其中參數(shù)subquery返回單列結(jié)果集的子查詢，是受限的select子句(不允許使用orderby子句、compute子句和into子句)。

例5-49查詢表books的圖書單價高于或等于表orderitems中orderid為2的最高單價的圖書信息。代碼如下：

select*frombookswhereunitprice＞=all(selectpricefromorderitemswhereorderid=2)

或者使用單值比較，執(zhí)行結(jié)果與all一樣。代碼如下：

select*frombookswhereunitprice＞=(selectmax(price)fromorderitemswhereorderid=2)

(5)使用exists關(guān)鍵字。使用exists關(guān)鍵字指定一個子查詢，檢測行的存在。exists搜索條件并不真正地使用子查詢的結(jié)果，它僅僅檢查子查詢是否返回了任何結(jié)果，因此

exists子查詢中的select子句可用任意列名或用*號。

關(guān)鍵字exists用來檢驗子查詢的結(jié)果是否為空。在使用exists的子查詢中，外層查詢要依次判斷exists運(yùn)算是否為“true”。如果非空，則exists運(yùn)算返回“true”；如果為空，

則exists運(yùn)算返回“false”。

(6)在select子句中使用子查詢。

例5-52在表orderitems和表books中，查詢所有圖書的編號、書名、單價及訂單總量。代碼如下：

selectbookid,title,unitprice,(selectsum(quantity)fromorderitemsiwherei.bookid=b.bookid)as訂單總量frombooksb

(7)在insert語句中使用子查詢。使用insertinto…values語句一次向表中插入的記錄是有限的，可以將values子句替換為一個select語句，將select語句檢索到的數(shù)據(jù)(可能

若干條)插入到指定的表中。x

4.連接查詢

1)基本概念及分類

在關(guān)系數(shù)據(jù)庫管理系統(tǒng)中，表建立時各數(shù)據(jù)之間的關(guān)系不必確定，常把一個實體的所有信息存放在一個表中。當(dāng)檢索數(shù)據(jù)時，通過連接操作查詢出存放在多個表中的不同實體

的信息。連接操作給用戶帶來很大的靈活性，可以在任何時候增加新的數(shù)據(jù)類型，為不同實體創(chuàng)建新的表，然后通過連接進(jìn)行查詢。

連接查詢主要包括內(nèi)連接查詢、外連接查詢和交叉連接查詢等。具體如下：

(1)內(nèi)連接是SQLServer缺省的連接方式，又分為等值連接、自然連接和不等連接三種。

(2)外連接的連接查詢結(jié)果集中既包含那些滿足條件的行，還包含其中某個表的全部行，有三種形式的外連接：左外連接、右外連接和全外連接。

(3)交叉連接即笛卡兒積，是指兩個關(guān)系中所有元組的所有組合。一般情況下，交叉連接查詢是沒有實際意義的。

2)連接查詢的應(yīng)用

(1)內(nèi)連接查詢。內(nèi)連接查詢(innerjoin…on…)使用比較運(yùn)算符進(jìn)行表間某(些)列數(shù)據(jù)的比較操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行。在內(nèi)連接查詢中，只有滿

足連接條件的元組才能出現(xiàn)在結(jié)果關(guān)系中。內(nèi)連接的3種連接方式如下：

①等值連接。在連接條件中使用等號(=)運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復(fù)列。

②非等值連接。在連接條件中使用除等號以外的其他比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括>、>=、<=、<、!>、!<和<>。

③自然連接。在連接條件中使用等于(=)運(yùn)算符比較被連接列的列值，查詢所涉及的兩個關(guān)系模式有公共屬性，且公共屬性值相等，相同的公共屬性只在結(jié)果關(guān)系中出現(xiàn)一次。

內(nèi)連接查詢的語法格式如下：

selectselect_listfrom{<table_source><join_type><table_source>[,...n]on<search_condition>}

參數(shù)說明：

·<table_source>：參與連接操作的表名，可以是一張表，也可以是多張表。

·<join_type>=inner[outer]join

·on<search_condition>：連接操作中的on子句指出連接條件，它由被連接表中的列和比較運(yùn)算符、邏輯運(yùn)算符等構(gòu)成。

注意：無論哪種連接，都不能對text、ntext和image數(shù)據(jù)類型列進(jìn)行直接連接。圖5-8內(nèi)連接查詢結(jié)果

(2)外連接查詢。外連接分為左連接、右連接和全連接三種。與內(nèi)連接不同的是，外連接不僅列出與連接條件相匹配的行，而還會列出左表(左外連接時)、右表或兩個表中

所有符合搜索條件的數(shù)據(jù)行。注意，此時以on給出搜索條件。

外連接查詢的語法格式如下：

selectselect_listfrom{<table_source><join_type><table_source>[,...n]on<search_condition>}

參數(shù)說明：<join_type>=left|right|full[outer]join

①左外連接。左外連接(leftouterjoin或leftjoin)的結(jié)果集包括leftjoin或leftouterjoin子句中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表

中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。

例5-56在表student、sc中，查詢表student的所有學(xué)生的基本信息和成績信息。代碼如下：

selectstudent.s#,student.sname,student.classid,sc.c#,sc.score

fromstudentleftouterjoinsconstudent.s#=sc.s#

查詢結(jié)果如圖5-9所示。圖5-9左外連接查詢結(jié)果

②右外連接。右外連接(rightouterjoin…on…或rightjoin…on…)使用rightjoin或rightouterjoin子句，是左向外連接的反向連接，將返回右表的所有行。如果右表的某行

在左表中沒有匹配行，則將為左表返回空值。

例5-57在表student、sc中，查詢所有學(xué)生的基本信息和成績信息。代碼如下：

selectstudent.s#,student.sname,student.classid,sc.c#,sc.score

fromstudentrightouterjoinsconstudent.s#=sc.s#

從查詢結(jié)果看，只包括圖5-9的前12條記錄，也就是有成績的數(shù)據(jù)行。

③全連接。全連接(fullouterjoin…on…或fulljoin…on…)使用fulljoin或fullouterjoin子句返回左表和右表中的所有行。當(dāng)某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結(jié)果集行包含基表的數(shù)據(jù)值。

例5-58在表student、sc中，查詢出現(xiàn)在兩個表中的所有學(xué)生的基本信息和成績信息。代碼如下：

selectstudent.s#,student.sname,student.classid,sc.c#,sc.score

fromstudentfullouterjoinsconstudent.s#=sc.s#

(3)交叉連接查詢。交叉連接(cross