項目數(shù)據(jù)庫基本管理及數(shù)據(jù)操作和查詢

任務(wù)2-1：系統(tǒng)數(shù)據(jù)庫管理

任務(wù)2-2：用戶數(shù)據(jù)庫的創(chuàng)建、修改和刪除

任務(wù)2-3：數(shù)據(jù)表的創(chuàng)建、修改和刪除

任務(wù)2-4：數(shù)據(jù)表記錄的添加、修改和刪除

任務(wù)2-5：約束管理

任務(wù)2-6：單表查詢（T-SQL）

任務(wù)2-7：多表查詢（T-SQL）

任務(wù)2-8：嵌套查詢（T-SQL）

SQLServer2005將數(shù)據(jù)庫映射為一組操作系統(tǒng)文件，數(shù)據(jù)和日志信息分別存儲在不同的文件中。數(shù)據(jù)文件數(shù)據(jù)庫文件事務(wù)日志文件用于存儲數(shù)據(jù)庫中的所有對象，如表、視圖、存儲過程等用以記錄所有事務(wù)及每個事務(wù)對數(shù)據(jù)庫所做的修改數(shù)據(jù)文件：數(shù)據(jù)文件用于存儲數(shù)據(jù)庫中的所有對象，如表、視圖、存儲過程等。主要數(shù)據(jù)文件次要數(shù)據(jù)文件包含數(shù)據(jù)庫的啟動信息和數(shù)據(jù)庫中其他文件的指針。每個數(shù)據(jù)庫有且僅有一個主要數(shù)據(jù)文件，主要數(shù)據(jù)文件的建議文件擴展名為.mdf。次要數(shù)據(jù)文件是可選的，由用戶定義并存儲主要數(shù)據(jù)文件未存儲的其他數(shù)據(jù)和對象，建議文件擴展名為.ndf?！咀⒁狻看我獢?shù)據(jù)文件不是必須的，如果主要數(shù)據(jù)文件足夠大，能夠容納數(shù)據(jù)庫中的所有數(shù)據(jù)，則該數(shù)據(jù)庫不需要次要數(shù)據(jù)文件；但有些數(shù)據(jù)庫可能非常大，超過了單個Windows文件的最大值(4G)，可以使用多個次要數(shù)據(jù)文件，這樣數(shù)據(jù)庫就能繼續(xù)增長?！菊f明】SQLServer2005不強制使用.mdf、.ndf和.ldf文件擴展名，但使用它們有助于標識文件的各種類型和用戶。SQLServer2005的每個數(shù)據(jù)庫文件都有一個邏輯文件名和一個物理文件名。邏輯文件名只在Transact-SQL語句中使用，是實際磁盤文件名的代號。物理文件名是操作系統(tǒng)文件的實際名字，包括文件所在的路徑。事務(wù)日志文件：用以記錄所有事務(wù)及每個事務(wù)對數(shù)據(jù)庫所做的修改。每個SQLServer2005數(shù)據(jù)庫至少擁有一個事務(wù)日志文件，也可以擁有多個事務(wù)日志文件。事務(wù)日志文件的大小最少是1MB。事務(wù)日志是數(shù)據(jù)庫的重要組件，當系統(tǒng)出現(xiàn)故障或數(shù)據(jù)庫遭到破壞時，就需要使用事務(wù)日志恢復(fù)數(shù)據(jù)庫內(nèi)容。

日志文件的建議文件擴展名為.ldf。2、數(shù)據(jù)庫文件組

多個數(shù)據(jù)文件集合起來形成的一個整體就是文件組。對文件進行

分組的目的是便于進行管理和進行數(shù)據(jù)的分配。

每個文件組有一個組名。一個數(shù)據(jù)文件不能存在于兩個或兩個以上的文件組里，日志文件不屬于任何文件組。主文件組：包含了所有的系統(tǒng)表。當建立數(shù)據(jù)庫時，主文件組包括主要數(shù)據(jù)文件和所有沒有被包含在其他文件組里的次要數(shù)據(jù)文件。用戶自定義文件組：用戶自定義文件組包含所有在使用CREATEDATABASE或ALTERDATABASE命令時使用FILEGROUP關(guān)鍵字來進行指定文件組的文件。默認文件組：默認文件組包含所有在創(chuàng)建時沒有指定文件組的表、索引等數(shù)據(jù)庫對象。在每個數(shù)據(jù)庫中，每次只能有一個文件組是默認文件組?？梢栽谟脩糇远x文件組中指定一個默認文件組；如果沒有指定默認文件組，則主文件組為默認文件組。3、數(shù)據(jù)庫的物理存儲結(jié)構(gòu)頁面和盤區(qū)SQLServer2005中數(shù)據(jù)存儲的基本單位是頁。為數(shù)據(jù)庫中的數(shù)據(jù)文件（.mdf或.ndf）分配的磁盤空間可以從邏輯上劃分成頁。在SQLServer2005中，頁的大小是8KB，SQLServer2005數(shù)據(jù)庫每兆字節(jié)有128頁。由8個連續(xù)頁面（8×8KB=64KB）組成的數(shù)據(jù)結(jié)構(gòu)稱為一個盤區(qū)，SQLServer2005數(shù)據(jù)庫每兆字節(jié)有16個盤區(qū)。

簡單地說，一個數(shù)據(jù)庫是由文件組成的，文件是由盤區(qū)組成，而盤區(qū)是由頁面組成的?！咀⒁狻繑?shù)據(jù)行存放在數(shù)據(jù)頁中，但數(shù)據(jù)頁只能包含除text、ntext和image數(shù)據(jù)外的所有數(shù)據(jù)，text、ntext和image數(shù)據(jù)存儲在單獨的頁中。行不能跨頁存儲，而每數(shù)據(jù)頁是8KB，嚴格地說是8060B，因此頁中每一行最多包含的數(shù)據(jù)量是8060B。日志文件是由一系列日志記錄組成，而不是頁面。4、SQLServer2005數(shù)據(jù)庫的分類1

系統(tǒng)數(shù)據(jù)庫

2示例數(shù)據(jù)庫

3用戶數(shù)據(jù)庫3）系統(tǒng)數(shù)據(jù)庫

系統(tǒng)數(shù)據(jù)庫是在SQLServer2005的每個實例中都存在的標準數(shù)據(jù)庫，用于存儲有關(guān)SQLServer的信息，SQLServer使用系統(tǒng)數(shù)據(jù)庫來管理系統(tǒng)。1）示例數(shù)據(jù)庫

AdventureWorks和AdventureWorksDW是SQLServer2005中的示例數(shù)據(jù)庫，是系統(tǒng)為了讓用戶學(xué)習(xí)和理解SQLServer2005而設(shè)計的。2）用戶數(shù)據(jù)庫

用戶數(shù)據(jù)庫是用戶根據(jù)事務(wù)管理需求創(chuàng)建的數(shù)據(jù)庫，例如，圖書信息管理數(shù)據(jù)庫、sales數(shù)據(jù)庫等。master數(shù)據(jù)庫：SQLServer2005中的總控數(shù)據(jù)庫，是最重要的系統(tǒng)數(shù)據(jù)庫。系統(tǒng)是根據(jù)master數(shù)據(jù)庫中的信息來管理系統(tǒng)和其他數(shù)據(jù)庫。如果master數(shù)據(jù)庫信息被破壞，整個SQLServer系統(tǒng)將受到影響，用戶數(shù)據(jù)庫將不能被使用。model數(shù)據(jù)庫：用戶建立新數(shù)據(jù)庫提供模板和原型，它包含了將復(fù)制到每個新建數(shù)據(jù)庫中的系統(tǒng)表。msdb數(shù)據(jù)庫：支持SQLServer代理。當代理程序調(diào)度作業(yè)、記錄操作時，系統(tǒng)要用到或?qū)崟r產(chǎn)生很多相關(guān)信息，這些信息一般存儲在msdb數(shù)據(jù)庫中。tempdb數(shù)據(jù)庫：臨時數(shù)據(jù)庫，保存所有的臨時表、臨時數(shù)據(jù)以及臨時創(chuàng)建的存儲過程。resource數(shù)據(jù)庫：只讀和隱藏的數(shù)據(jù)庫，包含SQLServer2005所有的系統(tǒng)對象，我們無法使用可以列出所有數(shù)據(jù)庫的一般SQL命令來看到它。5、數(shù)據(jù)庫對象的結(jié)構(gòu)架構(gòu)是一種允許我們對數(shù)據(jù)庫對象進行分組的容器對象，是形成單個命名空間的數(shù)據(jù)庫對象的集合。命名空間是一個集合，其中每個元素的名稱都是唯一的。在SQLServer2005中，一個數(shù)據(jù)庫對象通過由4個命名部分組成的結(jié)構(gòu)來引用，即：

[[[server_name.][database_name].][schema_name].]object_name對象所在的服務(wù)器名稱對象所在的數(shù)據(jù)庫名稱對象的架構(gòu)名稱對象名如果應(yīng)用程序引用了一個沒有限定架構(gòu)的數(shù)據(jù)庫對象，那么SQLServer2005將嘗試在用戶的默認架構(gòu)（通常為dbo）中找出這個對象。

例如，引用服務(wù)器“HBSI”上的數(shù)據(jù)庫“sales”中的銷售員表“Seller”時，完整的引用為“HBSI.sales.dbo.Seller”。在實際引用時，在能夠區(qū)分對象的前提下，前三個部分是可以根據(jù)情況省略的。1、創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫。使用Transact-SQL語句創(chuàng)建數(shù)據(jù)庫優(yōu)點：簡單直觀優(yōu)點：可以將創(chuàng)建數(shù)據(jù)庫的腳本保存下來，在其他計算機上運行以創(chuàng)建相同的數(shù)據(jù)庫

1）使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫CREATEDATABASEdatabase_name

[ON[

<filespec>[,...n]]

[,<filegroup>[,...n]]]

[LOGON<filespec>[,...n]]<filespec>::=[PRIMARY]

({NAME

=logical_file_name}

{,FILENAME

=‘os_file_name’}

[,SIZE

=size]

[,MAXSIZE

={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment]

)[,...n]

<filegroup>::=FILEGROUPfilegroup_name<filespec>[,...n]2）使用Transact-SQL語句創(chuàng)建數(shù)據(jù)庫指明主要數(shù)據(jù)文件、次要數(shù)據(jù)文件和文件組的明確定義

事務(wù)日志文件的明確定義

數(shù)據(jù)庫的名稱附錄：Transact-SQL語法約定參考說明

大寫

Transact-SQL關(guān)鍵字。粗體

數(shù)據(jù)庫名、表名、列名、索引名、存儲過程、實用工具、數(shù)據(jù)類型名以及按所顯示的原樣必須鍵入的文本。[]（方括號）

可選語法項。不要鍵入方括號。<>（<標簽>）

說明。[,...n]

此項可以重復(fù)n次，各項之間以逗號分隔。[....n]

指示前面的項可以重復(fù)n次。每一項由空格分隔。<label>::=

語法塊的名稱。此約定用于對可在語句中的多個位置使用的過長語法段或語法單元進行分組和標記。可使用語法塊的每個位置由括在尖括號內(nèi)的標簽指示：<標簽>。{}（大括號）

必選語法項。不要鍵入大括號。|（豎線）

大括號中的語法項。只能使用其中一項。;

Transact-SQL語句終止符。雖然在此版本的SQLServer中大部分語句不需要分號，但將來的版本需要分號。斜體

用戶提供的Transact-SQL語法的參數(shù)。下劃線

指示當語句中省略了包含帶下劃線的值的子句時應(yīng)用的默認值。

CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME='d:\data\sample_data.mdf',SIZE=5,MAXSIZE=50,FILEGROWTH=10)該語句需在“查詢

編輯器”中輸入并執(zhí)行例創(chuàng)建一個數(shù)據(jù)庫sample。主文件為sample_dat?！菊f明】由于沒有為主要數(shù)據(jù)文件指定容量單位，系統(tǒng)默認為MB。由于在創(chuàng)建時沒有指定日志文件，系統(tǒng)將自動創(chuàng)建一個初始容量為2MB的日志文件并且沒有最大容量限制。如果在查詢語句編輯區(qū)域選定了語句，則只執(zhí)行選定的語句，否則執(zhí)行所有語句。其他示例:教材P35~P36。2、查看數(shù)據(jù)庫查看數(shù)據(jù)庫的屬性使用SQLServerManagementStudio用Transact-SQL語句對于已有的數(shù)據(jù)庫1）使用SQLServerManagementStudio查看數(shù)據(jù)庫屬性

教材P36~P37。2）使用Transact-SQL語句查看數(shù)據(jù)庫屬性

系統(tǒng)存儲過程sp_helpdb查看數(shù)據(jù)庫的屬性。

例：查看數(shù)據(jù)庫sales的屬性。語句格式：sp_helpdbsales

例：查看所有數(shù)據(jù)庫的屬性。語句格式：sp_helpdb

3、修改數(shù)據(jù)庫1）使用SQLServerManagementStudio修改數(shù)據(jù)庫屬性 教材P38。2）使用Transact-SQL語句修改數(shù)據(jù)庫屬性ALTERDATABASEdatabase_name{ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name

|ADDFILEGROUPfilegroup_name

|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}|SETAUTO_SHRINKon/off}為數(shù)據(jù)庫sample添加數(shù)據(jù)文件sample_dat2和日志文件sample_log2。ALTERDATABASEsampleADDFILE(NAME=sample_dat2,FILENAME='d:\data\sample_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)ALTERDATABASEsampleADDLOGFILE(NAME=sample_log2,FILENAME='d:\data\sample_log2.ldf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)例其他示例： 教材P40。4、重命名數(shù)據(jù)庫使用系統(tǒng)存儲過程sp_renamedb更改某個數(shù)據(jù)庫的名字，其語法格式為：sp_renamedb‘old_name’,’new_name’例如，sp_renamedb'business','company‘【注意】后續(xù)版本的SQLServer將刪除該功能，最好使用ALTERDATABASEMODIFYNAME進行重命名數(shù)據(jù)庫。正在使用的數(shù)據(jù)庫是不能進行重命名的。5、數(shù)據(jù)庫的收縮 教材P41~P42。6、刪除數(shù)據(jù)庫當一個數(shù)據(jù)庫不再需要時可以將其刪除，以釋放該數(shù)據(jù)庫所占有的磁盤空間。但是應(yīng)該注意的是，如果某個數(shù)據(jù)庫正在被使用時，則無法對其進行刪除操作。1）使用SQLServerManagementStudio刪除數(shù)據(jù)庫教材P43。2）使用Transact-SQL語句刪除數(shù)據(jù)庫語法格式：DROPDATABASE

database_name例：刪除數(shù)據(jù)庫student

DROPDATABASEstudent例：刪除sample和student數(shù)據(jù)庫。

DROPDATABASEsample,student【注意】4個系統(tǒng)數(shù)據(jù)庫master、tempdb、model、msdb不能刪除。正在使用的數(shù)據(jù)庫不能刪除。數(shù)據(jù)庫被刪除之后，文件及其數(shù)據(jù)都從服務(wù)器上的磁盤中被刪除。一旦刪除數(shù)據(jù)庫，它即被永久刪除，所以刪除數(shù)據(jù)庫時一定要謹慎。課堂練習(xí)教材P44： 【2.7實驗】中的實驗內(nèi)容（1、2、3、4、6）。1、表的概念

數(shù)據(jù)庫中包含一個或多個表。表是數(shù)據(jù)的集合，是用來存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)。數(shù)據(jù)在表中是按照行和列的格式來組織排列的，每一行代表一條唯一的記錄，每一列代表記錄的一個屬性。例如，一個包含銷售員基本信息的數(shù)據(jù)表，表中每一行代表一名銷售員，每列分別代表該銷售員的信息，如編號、姓名、性別等。2、系統(tǒng)數(shù)據(jù)類型二進制數(shù)據(jù)類型（binary、varbinary、image）整數(shù)數(shù)據(jù)類型（bit、int、bigint、smallint、tinyint）浮點數(shù)據(jù)類型（float、real）精確小數(shù)數(shù)據(jù)類型（decimal、numeric）貨幣數(shù)據(jù)類型（money、smallmoney)日期/時間數(shù)據(jù)類型（datetime、smalldatetime）字符數(shù)據(jù)類型（char、varchar、text）unicode數(shù)據(jù)類型（nchar、nvarchar、ntext）特殊數(shù)據(jù)類型3、表的創(chuàng)建1）使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)表

教材P50~P51。2）使用Transact-SQL語句創(chuàng)建數(shù)據(jù)表CREATETABLE[database_name.[schema_name].|schema_name.]table_name({column_name1data_type}[DEFAULTconstant_expression][IDENTITY(SEED,INCREMENT)][NULL|NOTNULL][,…n])[ON{filegroup|DEFAULT}]說明：P51。例：為sales數(shù)據(jù)庫創(chuàng)建一個銷售人員表Seller，它包含銷售員編號（SaleID）、姓名（SaleName）、性別（Sex）、出生日期（Birthady）、雇用日期（HireDate）、地址（Address）、電話（Telephone）和備注（Note）字段，其中SaleID、SaleName這兩列不允許為空。CREATETABLESeller(SaleIDchar(3)NOTNULL,SaleNamechar(8)NOTNULL,Sexchar(2),Birthdaydatetime,HireDatedatetime,Addresschar(60),Telephonechar(13),Notechar(200))例：為sales數(shù)據(jù)庫創(chuàng)建訂單表Orders，包括OrderID、CustomerID、SaleID和OrderDate字段，其中OrderID為標識列，起始值為10248，增量為1；CustomerID和SaleID字段不允許為空值；OrderDate字段的默認值為當前日期。CREATETABLEOrders(OrderIDintIDENTITY(10248,1),CustomerIDchar(3)NOTNULL,SaleIDchar(3)NOTNULL,OrderDatedatetimeDEFAULTgetdate())4、修改表結(jié)構(gòu)1）使用SQLServerManagementStudio修改表結(jié)構(gòu) 教材P52~P53。2）使用Transact-SQL語句修改表結(jié)構(gòu)ALTERTABLEtable_name{ADDcolumn_namedate_type[DEFAULTcontant_expression][IDENTITY(SEED,INCREMENT)][NULL|NOTNULL][,…n]|DROPCOLUMNcolumn_name[,…n]|ALTERCOLUMNcolumn_namenew_datetype[NULL|NOTNULL]}例：sales數(shù)據(jù)庫中的Customer表包含CustomerID、ConpanyName和ConnectName三個字段，現(xiàn)為該表添加地址（Address）、郵政編碼（ZipCode）和電話號碼（Telephone）字段。

ALTERTABLECustomer

ADDAddresschar(40),ZipCodechar(6),Telephonechar(20)例：將表Seller中的Sex列刪除

ALTERTABLESellerDROPCOLUMNSex例：將Seller表中的Address字段的長度改為30，且不能為空

ALTERTABLESeller

ALTERCOLUMNAddressvarchar(30)NOTNULL5、刪除數(shù)據(jù)表1）使用SQLServerManagementStudio刪除數(shù)據(jù)表

教材P53~P54。2）使用Transact-SQL語句刪除數(shù)據(jù)表使用DROPTABLE命令刪除表格，其語法格式為：

DROPTABLEtable_name1[,…n]例：將Customer表從sales數(shù)據(jù)庫中刪除。

DROPTABLECustomer課堂練習(xí)

教材P68： 【3.9實驗】中的實驗內(nèi)容（1、2）。

注：建表時約束暫不創(chuàng)建。1、使用Transact-SQL語句向表中插入數(shù)據(jù)INSERT[INTO]table_name[(column_name[,…n]

)]

VALUES

(expression|NULL|DEFAULT

[,…n]

)

其中：table_name：要插入數(shù)據(jù)的表名。column_name：要插入數(shù)據(jù)的列名。expression：與column_name相對應(yīng)的字段的值，字符型和日期型值插入時要加單引號。例：向Category表中添加三行數(shù)據(jù)。

INSERTINTOCategory(CategoryID,CategoryName,Description)

VALUES(1,'飲料','軟飲料、咖啡、茶、啤酒和淡啤酒')

INSERTINTOCategory(CategoryID,CategoryName,Description)

VALUES(2,'調(diào)味品','香甜可口的果醬、調(diào)料、醬汁和調(diào)味品')

INSERTINTOCategory(CategoryID,CategoryName,Description)

VALUES(3,'點心','甜點、糖和面包')例：向Seller表中插入一行數(shù)據(jù)，其中Sex字段使用默認值為‘男’，HireDate等字段均取空值。

INSERTINTOSeller(SaleID,SaleName,Sex,Birthday,HireDate,Address,Telephone,Notes)VALUES('s11','趙宇飛',DEFAULT,'1974-07-5',NULL,NULL,NULL,NULL)或

INSERTINTOSeller(SaleID,SaleName,Birthday)

VALUES('s11','趙宇飛','1974-07-25')例：對表中所有列插入數(shù)據(jù)，則可省略列名。

INSERTINTOCategory

VALUES(1,'飲料','軟飲料、咖啡、茶、啤酒和淡啤酒')

INSERTINTOCategory

VALUES('調(diào)味品',2,'香甜可口的果醬、調(diào)料、醬汁和調(diào)味品')

INSERTINTOCategory

VALUES(2,'點心')【注意】在插入數(shù)據(jù)時，對允許為空的列可使用NULL插入空值；對具有默認值的列可使用DEFAULT插入默認值。當向表中所有列都插入新數(shù)據(jù)時，可以省略列名表，但必須保證VALUES后的各數(shù)據(jù)項位置同表定義時的順序一致，否則系統(tǒng)會報錯。錯誤錯誤例：創(chuàng)建數(shù)據(jù)表Orders，然后再插入一行數(shù)據(jù)。CREATETABLEOrders(OrderIDintIDENTITY(10248,1),CustomerIDchar(3)NOTNULL,SaleIDchar(3)NOTNULL,OrderDatedatetimeDEFAULTgetdate())INSERTINTOOrders(CustomerID,SaleID)Values('c01','s11')【注意】具有IDENTITY屬性的列，其值由系統(tǒng)給出，用戶不必向表中插入數(shù)據(jù)。2、使用Transact-SQL語句修改表中數(shù)據(jù)UPDATEtable_name

SETcolumn_name=expression[,…n]

[WHEREsearch_conditions]

其中：table_name：要更新數(shù)據(jù)的表名。column_name：要更新數(shù)據(jù)的列名。expression：更新后的數(shù)據(jù)值。search_conditions：更新條件，只對表中滿足該條件的記錄進行更新。例：將Product表中‘啤酒’的價格改為4元。

UPDATEProduct

SETPrice=4

WHEREProductName='啤酒'例：將Seller表中SaleID為s11的地址改為‘東直門外大街108號’，電話改為‘（010）60486658’。

UPDATESeller

SETAddress='東直門外大街108號',Telephone='(010)60486658'

WHERESaleID='s11'例：將Product表中CategoryID為2的所有產(chǎn)品的價格下調(diào)10%。

UPDATEProduct

SETPrice=Price*(1-0.1)

WHERECategoryID=23、使用Transact-SQL語句刪除表中數(shù)據(jù)DELETE

[FROM]table_name

[

WHEREsearch_conditions]

說明：刪除表中符合search_conditions的數(shù)據(jù)；缺省WHERE子句時，表示刪除該表中的所有數(shù)據(jù)。例：將SaleID為s11的數(shù)據(jù)從Seller表中刪除。

DELETEFROMSellerWHERESaleID='s11'4、使用SQLServerManagementStudio插入、修改、刪除表中

數(shù)據(jù)教材P57~P58。課堂練習(xí)1、根據(jù)以下數(shù)據(jù)表中的內(nèi)容，使用T-SQL語句分別創(chuàng)建出表結(jié)構(gòu)，并完成數(shù)據(jù)的插入。（1）表名：Employee（2）表名：Product2、修改“ProductID”為“Y19”的產(chǎn)品，使其“Stocks”為“1000”、“Price”為“200”。3、刪除“EmpNo”為“J0015”的職員記錄。

任務(wù)2-5：約束管理約束可以在兩個層次上實施：列級：用戶定義的約束只對表中的一列起作用。表級：用戶定義的約束對表中的多列起作用。約束定義了必須遵循的用于維護數(shù)據(jù)一致性和正確性的規(guī)則，是強制實現(xiàn)數(shù)據(jù)完整性的主要途徑。約束有5種類型，包括：主鍵約束、唯一性約束、檢查約束、默認約束、外鍵約束（參照約束）。1、主鍵（PRIMARYKEY）約束主鍵用于唯一地標識表中每一條記錄。我們可以定義表中的一列或多列為主鍵，則主鍵列上沒有任何兩行具有相同值（即重復(fù)值），該列也不能為空值。為了有效實現(xiàn)數(shù)據(jù)的管理，每張表都應(yīng)該有自己的主鍵，且只能有一個主鍵。

CREATETABLEStudent(sidintPRIMARY

KEY,snamechar(20)NOTNULL,sageint,scitychar(10))在SQLServerManagementStudio中創(chuàng)建主鍵約束教材P60。2、唯一性（UNIQUE）約束 用來限制表中任意兩行在指定列上都不允許有相同的值。一個表上可以放置多個UNIQUE約束。

唯一性約束和主鍵約束的區(qū)別：唯一性約束允許在該列上存在NULL值，而主鍵約束限制更為嚴格，不但不允許有重復(fù)，而且也不允許有空值。CREATETABLEDepartment (dep_idintPRIMARYKEY, dep_namechar(20)NOTNULLUNIQUE, dep_headchar(5))在SQLServerManagementStudio中創(chuàng)建唯一性約束教材P61。3、檢查（CHECK）約束

CREATETABLEStudent (sidintPRIMARYKEY, snamechar(20)NOTNULL, sageintCHECK(sageBETWEEN18AND30), scitychar(10))用來指定某列的可取值的范圍。它通過限制輸入到列中的值來強制域的完整性。我們可以在單列上定義多個CHECK約束，以它們定義的順序來求值。在SQLServerManagementStudio中創(chuàng)建檢查約束教材P62~P63。4、默認（DEFAULT）約束

CREATETABLEStudent (sidintPRIMARYKEY, snamechar(20)NOTNULL, sageintdefault20, scitychar(10)) 用于給表中指定列賦予一個常量值（默認值），當向該表插入數(shù)據(jù)時，如果用戶沒有明確給出該列的值，SQLServer會自動為該列輸入默認值。每列只能有一個DEFAULT約束。在SQLServerManagementStudio中創(chuàng)建默認約束教材P63。5、外鍵（FOREIGNKEY）約束 用于與其他表（稱為參照表）中的列（稱為參照列）建立連接。通過將參照表中的主鍵所在列或具有唯一性約束的列包含在另一個表中，這些列就構(gòu)成了另一個表的外鍵。當參照表中的參照列更新后，外鍵列也會自動更新，從而保證兩個表之間的一致性關(guān)系。在SQLServerManagementStudio中創(chuàng)建外鍵約束 教材P65~P66。CREATETABLEScore(snochar(10)REFERENCESStudent(sno),Cnochar(10)REFERENCESCourse(cno),gradeint,PRIMARY

KEY(sno,cno))6、實現(xiàn)數(shù)據(jù)完整性 數(shù)據(jù)完整性是指數(shù)據(jù)的正確性、一致性和安全性，它是衡量數(shù)據(jù)庫中數(shù)據(jù)質(zhì)量好壞的重要標準。當用戶用INSERT、DELETE或UPDATE語句修改數(shù)據(jù)庫內(nèi)容時，數(shù)據(jù)的完整性就可能會遭到破壞。實體完整性（EntityIntegrity）域完整性（DomainIntegrity）參照完整性（ReferentialIntegrity）用戶定義完整性1）實體完整性 實體完整性指的是表中的每一行都能由稱為主鍵的屬性列來唯一標識，且不存在重復(fù)的數(shù)據(jù)行。作為唯一標識符的主鍵可能是一列，也可能是幾列的組合，并且主鍵不可為空。

例如，在Seller表中可能由兩個或多個銷售員都叫“張芳”，因此SaleName字段不能設(shè)為主鍵。我們給每一個銷售員賦予唯一編碼SaleID來標識他們，SaleID字段為主鍵。2）域完整性指的是限制向表中輸入的值的范圍，保證給定列的輸入有效性。它可以通過限制數(shù)據(jù)類型、值域或數(shù)據(jù)格式來實現(xiàn)。

例如，銷售員的性別只能是“男”或“女”，年齡必須在18～60歲之間，產(chǎn)品的價格不可能為負數(shù)等。3）參照完整性 參照完整性也叫引用完整性。指的是當一個表引用了另一個表中的某些數(shù)據(jù)時，要防止非法的數(shù)據(jù)更新，以保持表格間數(shù)據(jù)的一致性。課堂練習(xí)

教材P68：【3.9實驗】中的實驗內(nèi)容（1，3，4）。1、SELECT語句1）SELECT語句的作用2）SELECT語句的基本語法格式【說明】SELECT：關(guān)鍵字，用于從數(shù)據(jù)庫中檢索數(shù)據(jù)。

select_list：描述進入結(jié)果集的列，它指定了結(jié)果集中要包含的列的名稱，是一個逗號分隔的表達式列表。

table_name：用于指定產(chǎn)生查詢結(jié)果集的源表的表名。

SELECT[ALL|DISTINCT]

[TOPn[PERCENT]select_list

[INTOnew_table]

FROMtable_name

[WHEREsearch_condition]

[GROUPBYgroup_by_expression]

[HAVINGsearch_condition]

[ORDERBYorder_expression[ASC|DESC]]

讓數(shù)據(jù)庫服務(wù)器根據(jù)客戶的要求從數(shù)據(jù)庫中搜索出所需要的信息資料，并且可以按規(guī)定的格式進行分類、統(tǒng)計、排序，再返回給客戶。另外，利用SELECT語句還可以設(shè)置和顯示系統(tǒng)信息、給局部變量賦值等。2、基本查詢1)選擇列2)選擇行基本查詢3)排序4)使用關(guān)鍵字TOP和DISTINCT1）選擇列格式：

SELECT

{

search_condition[,…..n]|

[*]

}FROMtable_name【列名】例4.1從sales數(shù)據(jù)庫的產(chǎn)品表Product中查詢出產(chǎn)品ID（ProductID）、產(chǎn)品名稱（ProductName）和單價（Price）的數(shù)據(jù)信息。SELECTProductID,ProductName,Price

FROMProduct【注意】在數(shù)據(jù)查詢時，列的顯示順序由SELECT語句的SELECT子句指定，該順序可以和列定義時順序不同，這并不影響數(shù)據(jù)在表中的存儲順序。【*】例4.2顯示Orders表中的所有信息。SELECT*FROMOrders【作用】無條件地把Orders表中的全部信息都查詢出來。該語句也稱全表查詢，這是最簡單的一種查詢?！居嬎懔小坷?.3從Product表中檢索出產(chǎn)品ID（ProductID）、產(chǎn)品名稱（ProductName）、產(chǎn)品單價（Price）、產(chǎn)品庫存量（Stocks）及產(chǎn)品的總價值。SELECTProductID,ProductName,Price,Stocks,Price*Stocks

FROMProductSELECTProductID,ProductName,Price,Stocks,‘總價值’=Price*StocksFROMProduct例4.4為例4.3中的計算列指定一個列標題“總價值”。【說明】在SELECT子句中可以使用算術(shù)運算符對數(shù)值型數(shù)據(jù)列進行加（+）、減（-）、乘（*）、除（/）和取模（%）運算，構(gòu)造計算列。產(chǎn)品的總價值是一個計算表達式，是產(chǎn)品單價和產(chǎn)品庫存量的乘積。需注意，對表中列的計算只是影響查詢結(jié)果，并不改變表中的數(shù)據(jù)。關(guān)鍵字AS可以省略例4.5顯示銷售員信息，格式如下：姓名性別出生日期地址SELECTSaleNameAS'姓名',SexAS'性別',BirthdayAS'出生日期',AddressAS'地址‘FROMSeller2）選擇行在實際工作中，大部分查詢并不是針對表中所有數(shù)據(jù)記錄的查詢，而是要找出滿足某些條件的數(shù)據(jù)記錄。此時我們可以在SELECT語句中使用WHERE子句。格式：

SELECT

{

search_condition[,…..n]|

[*]

}FROMtable_name

WHEREsearch_condition【使用關(guān)系運算符】例4.6查詢Product表中價格小于5元的產(chǎn)品記錄。SELECTProductID,ProductName,PriceFROMproductWHEREPrice<5.0例4.7查詢Seller表中男銷售人員的信息。SELECTSaleID,SaleName,Address,TelephoneFROMSellerWHERESex='男'【使用邏輯運算符】邏輯運算符包括：邏輯與AND、邏輯或OR、邏輯非NOTSELECTProductID,ProductName,PriceFROMProductWHEREPrice>=5.0ANDPrice<=10.0例4.8查詢Product表中價格在5－10元之間的產(chǎn)品記錄?！臼褂米址：ヅ洹扛袷剑?/p>

expression[NOT]LIKE‘string’[ESCAPE‘換碼字符’]

‘string’：是匹配字符串。其含義是查找指定的屬性列值與匹配字符串相匹配的記錄。匹配字符串可以是一個完整的字符串，也可以使用四種匹配符。匹配符描述%包含零個或多個字符的任意字符串_代表一個任意字符[]表示指定范圍內(nèi)的任意單個字符[^]表示不在指定范圍內(nèi)的任意單個字符表達式

描述

LIKE‘RA%’將搜索以字母“RA”開頭的所有字符串LIKE‘%ion’將搜索以字母“ion”結(jié)尾的所有字符串LIKE‘%ir%’將搜索任意位置中包含字母“ir”的所有字符串LIKE‘_mt’將搜索以字母“mt”結(jié)尾的所有三個字母組成的字符串LIKE‘[BC]%’將搜索以字母“B”或“C”開頭的所有字符串LIKE‘[B-K]air’將搜索以字母“B”到“K”中任意一個字母開頭，以“air”結(jié)尾的字符串LIKE‘B[^a]%’將搜索以字母“B”開頭，第二個字母不是“a”的所有字符串例4.11從Seller表中檢索出所有姓張的銷售人員的資料。SELECT*FROMSellerWHERESaleNameLIKE'張%‘例4.12從Seller表中檢索出名字的第二個字不是“芳”和“偉”的銷售人員的資料。SELECT*FROMSellerWHERESaleNameLIKE'_[^芳偉]%'

格式：column_name[NOT]BETWEENexpression1ANDexpression2SELECT*

FROMProduct

WHEREPriceBETWEEN5AND10NOTBETWEEN…AND…

表示不在某一范圍內(nèi)【使用查詢范圍】

在WHERE子句中使用BETWEEN關(guān)鍵字可以對表中某一范圍內(nèi)的數(shù)據(jù)進行查詢，系統(tǒng)將逐行檢查表中的數(shù)據(jù)是否在BETWEEN關(guān)鍵字設(shè)定的范圍內(nèi)。如果在其設(shè)定的范圍內(nèi)，則取出該行，否則不取該行。例4.13從Product表中查詢出價格在5-10元之間的產(chǎn)品信息。等價于：NOTIN用于查詢屬性值

不屬于指定集合的記錄?！臼褂貌樵兞斜怼咳绻兄档娜≈捣秶皇且粋€連續(xù)的區(qū)間，而是一些離散的值，此時就應(yīng)使用SQLServer提供的另一個關(guān)鍵字IN。

格式：column_name[NOT]IN

(value1,value2,…)例4.14查詢Seller表中SaleID為s01，s05，s07的銷售人員信息。SELECTSaleID,SaleName,Sex,Birthday,HireDate,AddressFROMSellerWHERESaleIDIN('S01','S05','S07')SELECTSaleID,SaleName,Sex,Birthday,HireDate,AddressFROMSellerWHERESaleID='S01'ORSaleID='S05'ORSaleID='S07'在SQLServer中，用NULL表示空值，它僅僅是一個符號，不等于空格，也不等于0，空值判定的語法格式如下：

SELECT*FROMSellerWHEREHireDateISNULL【空值的判斷】column_nameIS[NOT]NULL例4.16檢索Salers表中雇用日期為空的銷售人員的資料。3）排序在通常情況下，SQLServer數(shù)據(jù)庫中的數(shù)據(jù)記錄行在顯示時是無序的，它按照數(shù)據(jù)記錄插入數(shù)據(jù)庫時的順序排列，因此用SELECT語句查詢的結(jié)果也是無序的。通過ORDERBY子句，可以將查詢結(jié)果進行排序顯示。其語法格式為：SELECT[ALL|DISTINCT]

[TOPn[PERCENT]select_list

FROMtable_nameWHEREsearch_conditionORDERBY

order_expression[ASC|DESC]]

升序，可省略降序排列SELECTSaleID,SaleName,Sex,Birthday,AddressFROMSellerORDERBYBirthdayDESCSELECT*FROMSellerORDERBYSaleNameASC例4.17從Seller表中按姓名順序檢索出所有銷售員的信息。例4.18按出生日期列的降序排列Seller表。例4.19按出生日期的升序排列Seller表。SELECTSaleID,SaleName,Birthday,AddressFROMSellerORDERBY

3使用列所處的位置來指定排序列【注意】在默認情況下，ORDERBY子句按升序進行排序，即默認使用的是ASC關(guān)鍵字，如果特別要求按降序進行排列，必須使用DESC關(guān)鍵字。當ORDERBY子句指定了多個排序列時，系統(tǒng)先按照ORDERBY子句中第一列的順序排列，當該列出現(xiàn)相同值時，再按照第二列的順序排列，依次類推。例4.20查詢Orders表中的數(shù)據(jù)，先按CustomerID的升序排列，當CustomerID相同時再按照SaleID的降序排列。SELECT*FROMOrdersORDERBYCustomerID,SaleIDDESC4）使用TOP和DISTINCT關(guān)鍵字【TOP關(guān)鍵字】在SELECT子句中利用TOP關(guān)鍵字限制返回到結(jié)果集中的行數(shù)。其語法格式為：

SELECT

[TOPinteger|TOPintegerPERCENT

]column_nameFROMtable_nameTOPinteger：表示返回表中最前面的幾行，用integer表示返回的行數(shù)。TOPintegerPERCENT：用百分比表示返回的行數(shù)。例4.21分別從Customer表中檢索出前5個及表中前20％的顧客信息。SELECTTOP

5*FROMCustomerSELECTTOP

20

PERCENT*FROMCustomer例4.22查詢Product表中，價格最高的6種商品。SELECTTOP

6*FROMProductORDERBYPriceDESC

SELECT

[ALL|DISTINCT]column_name1[,column_name2,…]

FROMtable_name

WHEREsearch_condition允許重復(fù)數(shù)據(jù)行的出現(xiàn)，是默認的關(guān)鍵字從結(jié)果集中剔除重復(fù)的行【DISTINCT關(guān)鍵字】使用DISTINCT關(guān)鍵字可以從返回的結(jié)果集中刪除重復(fù)的行，使結(jié)果更簡潔。其語法格式為：例4.23查詢OrderDetail表，顯示訂購的產(chǎn)品編號，如果多張訂單訂購了同一產(chǎn)品，只需顯示一次產(chǎn)品編號。SELECTProductIDFROMOrderDetailSELECT

DISTINCTProductIDFROMOrderDetail【注意】DISTINCT關(guān)鍵字的作用范圍是整個查詢的結(jié)果集，而不是單獨的一列。如果同時對兩列數(shù)據(jù)進行查詢時，使用了DISTINCT關(guān)鍵字，將返回這兩列數(shù)據(jù)的唯一組合。課堂練習(xí)1、顯示Customer表中的所有信息。2、顯示Customer表中的CompanyName（公司名稱）、ConnectName（聯(lián)系人）、Telephone（電話）。3、從Product表中查詢所有產(chǎn)品的信息，包括產(chǎn)品的總價值。并以中文名顯示標題列。4、查詢價格不在10~20元之間的產(chǎn)品信息。5、查詢Seller表中女銷售人員的信息。6、查詢Seller表中在1970年之前出生的銷售人員信息。7、在Seller表中查詢姓“劉”的銷售員信息。8、在Seller表中查詢第2個字為“雪”的銷售員信息。9、在Seller表中查詢姓“張”并且名字為2個字的銷售員信息。10、按產(chǎn)品價格降序排列Product表。11、先按性別升序、再按出生日期降序排列Seller表。12、查詢Product表中庫存最低的6種商品。1.多表查詢

2.分組和匯總

高級查詢3.嵌套查詢

4.合并數(shù)據(jù)集

5.在查詢的基礎(chǔ)上創(chuàng)建新表

1、多表查詢SQLServer提供了實現(xiàn)多表查詢的方法――連接查詢。所謂連接查詢是將多個表以某個或某些列為條件進行連接，從中檢索出關(guān)聯(lián)數(shù)據(jù)。語法格式：教材P82。

所謂內(nèi)連接是多個表通過連接條件中共享列的值進行的比較連接。當未指明連接類型時，默認為內(nèi)連接。內(nèi)連接值顯示兩個表中所有匹配數(shù)據(jù)的行。1）內(nèi)連接（INNERJOIN）例4.24顯示OrderID為‘10249’的產(chǎn)品ID、產(chǎn)品名稱、產(chǎn)品數(shù)量及價格。

【分析】由于OrderID、ProductID以及Quantity這三列來自于OrderDetail表，而ProductName、Price來自于Product表，因此該查詢涉及到多表查詢。其中連接條件為兩個表中的ProductID列值相等，以此查詢出符合條件的數(shù)據(jù)信息。SELECTOrderID,OrderDetail.ProductID,ProductName,Price,QuantityFROMOrderDetailJOINProductON

OrderDetail.ProductID=Product.ProductIDWHEREOrderID='10249'【注意】缺省了連接類型，默認為內(nèi)連接。當單個查詢引用多個表時，所有列都必須明確。在查詢所引用的兩個或多個表之間，任何重復(fù)的列名都必須用表名限定，如OrderDetail.ProductID，表示引用了OrderDetail表中的ProductID列。如果某個列名在查詢用到的兩個或多個表中不重復(fù)，如ProductName，則對該列的引用不必用表名限定。為了增加可讀性，可以使用表的別名。表的別名的命令語法格式為：FROMtable_nametable_alias例4.25修改例4.24，使用表的別名，結(jié)果和例4.24一樣。SELECTOrderID,O.ProductID,ProductName,Price,QuantityFROMOrderDetailO

INNERJOINProductPON

O.ProductID=P.ProductIDWHEREOrderID='10249'例4.26查詢OrderID為‘10248’的顧客ID、顧客姓名、銷售員ID、銷售員姓名。SELECTOrderID,O.CustomerID,ConnectName,O.SaleID,SaleNameFROMOrdersO

INNERJOINCustomerC

ON

O.CustomerID=C.CustomerIDINNERJOINSellerSON

O.SaleID=S.SaleIDWHEREOrderID='10248'【注意】一旦使用了別名代替某個表，則在連接時必須用表的別名，不能再用表的原名。2）外連接（OUTERJOIN）外連接顯示包含來自一個表中所有行和來自另一個表中匹配行的結(jié)果集。外連接又分為左外連接、右外連接和完全外連接。

【左外連接（LEFTOUERJOIN）】左外連接返回LEFTOUTERJOIN關(guān)鍵字左側(cè)指定的表（左表）的所有行和右側(cè)指定的表（右表）的匹配的行。對于來自左表中的行，在右表中沒有發(fā)現(xiàn)匹配的行，那么在來自右表中獲得數(shù)據(jù)的列中將顯示NULL值。例4.27顯示所有產(chǎn)品的ProductID、ProductName、Price以及被顧客定購的OrderID、Quantity。SELECTP.ProductID,ProductName,Price,OrderID,QuantityFROMProductPLEFTOUTERJOINOrderDetailOONP.ProductID=O.ProductID例4.28顯示所有顧客的信息以及他們訂購產(chǎn)品的訂單ID、銷售員ID和訂購日期。SELECTC.CustomerID,CompanyName,ConnectName,OrderID,SaleID,OrderDateFROMCustomerCLEFTOUTERJOINOrdersOONC.CustomerID=O.CustomerID【結(jié)果】左表Customer中的所有行都顯示出來，而不管Orders表中是否訂購了這種產(chǎn)品。

【右外連接（RIGHTOUERJOIN）】右外連接即在連接兩表時，不管左表中是否有匹配數(shù)據(jù)，結(jié)果將保留右表中的所有行。例4.29修改例4.28使用右外連接。

SELECTOrderID,Quantity,P.ProductID,ProductName,Price

FROMOrderDetailORIGHTOUTERJOINProductPONO.ProductID=P.ProductID【結(jié)果】包含了右表中的所有數(shù)據(jù)行，而不管左表中是否有匹配數(shù)據(jù)?！就耆膺B接（FULLOUERJOIN）】完全外連接是左外連接和右外連接的組合。這個連接返回來自兩個表的所有匹配和非匹配行。其中，匹配記錄僅被顯示一次。在非匹配行的情況下，對于數(shù)據(jù)不可用的列將顯示NULL值。

例4.30需要了解所有產(chǎn)品的基本信息和類別信息。

SELECTProductID,ProductName,Price,Stocks,C.CategoryID,CategoryNameFROMProductPFULLOUTERJOINCategoryCON

P.CategoryID=C.CategoryID2、分組與匯總函數(shù)名描述AVG([ALL|DISTINCT][expression])返回表達式的平均值MAX(expression)返回表達式中的最大值MIN(expression)返回表達式中的最小值SUM([ALL|DISTINCT][expression])返回表達式中所有值的和COUNT([ALL|DISTINCT][expression])返回表中指定列的數(shù)據(jù)記錄行數(shù)。使用DISTINCT關(guān)鍵字刪除重復(fù)值COUNT(*)返回表中所有數(shù)據(jù)記錄的行數(shù)例4.32求Product表中，所有產(chǎn)品的平均價格、最高價、最低價以及總庫存。

SELECT

AVG(Price)AS'平均價格'FROMProduct

SELECT

MAX(Price)AS'最高價格'FROMProduct

SELECT

MIN(Price)AS'最低價格'FROMProduct

SELECT

SUM(Stocks)AS'總庫存'FROMProduct例4.33統(tǒng)計Product表中，庫存量>200的產(chǎn)品數(shù)量。

SELECTCOUNT(ProductID)FROMProductWHEREStocks>200例4.34統(tǒng)計Product表中的產(chǎn)品種數(shù)。

SELECTCOUNT(*)FROMProduct

【1）使用分組匯總子句】顯示分組的匯總數(shù)據(jù)，必須使用GROUPBY子句。該子句的功能是根據(jù)指定的列將表中數(shù)據(jù)分成多個組后進行匯總。其語法格式為：SELECTcolumn_name1[,…n]FROMtable_nameWHEREsearch_conditionGROUPBY[ALL]colum_name1[,…n][HAVINGsearch_condition]例4.35將Product表中的數(shù)據(jù)按CategoryID進行分組，然后分別統(tǒng)計每一組產(chǎn)品的平均價格及總庫存。SELECTCategoryID,AVG(Price)AS'平均價格',SUM(Stocks)AS'總庫存'FROMProduct

GROUPBYCategoryID【注意】使用GROUPBY子句為每一個組產(chǎn)生一個匯總結(jié)果，每個組只返回一行，不返回詳細信息。SELECT子句中指定的列必須是GROUPBY子句中指定的列，或者是和聚合函數(shù)一起使用。如果包含WHERE子句，則只對滿足WHERE條件的行進行分組匯總。如果GROUPBY子句使用關(guān)鍵字ALL，則WHERE子句將不起作用。HAVING子句可進一步排除不滿足條件的組。當同時存在GROUPBY子句、HAVING子句和WHERE子句時，其執(zhí)行順序為：先WHERE子句，后GROUPBY子句，再HAVING子句。即先用WHERE子句過濾不符合條件的數(shù)據(jù)記錄，接著用GROUPBY子句對余下的數(shù)據(jù)記錄按指定列分組、匯總，最后再用HAVING子句排除不符合條件的組。例4.36在上例基礎(chǔ)上只顯示平均價格低于10元的分組匯總信息。

SELECTCategoryID,AVG(Price)AS'平均價格',SUM(Stocks)AS'總庫存'

FROMProduct

GROUP

BYCategoryID

HAVING

AVG(Price)<10例4.37對所有價格大于5元且組平均價格大于15元的價格表目，列出所有的產(chǎn)品的類型、平均價格及總庫存。SELECTCategoryID,AVG(Price)AS'平均價格',SUM(Stocks)AS'總庫存'

FROMProduct

WHEREPrice>5GROUPBYCategoryID

HAVING

AVG(Price)>15注意：如果GROUPBY子句中指定了多個列，則表示要基于這些列的唯一組合來進行分組。在分組過程中，首先按第一列進行分組并按升序排列，然后再按第二列進行分組并按升序排列，依次類推，最后在分好的組中進行匯總。

【2）使用明細匯總子句】使用GROUPBY對查詢出來的數(shù)據(jù)作分類匯總后，只能顯示統(tǒng)計結(jié)果，看不到詳細的數(shù)據(jù)。使用COMPUTE和COMPUTEBY子句既能瀏覽詳細數(shù)據(jù)又可看到統(tǒng)計的結(jié)果。其語法格式為：COMPUTEaffregate_function(column_name)[,…n][BYcolumn_name[,…n]]其中：affregate_function表示聚合函數(shù)。類似于總計類似于小計例4.38使用COMPUTE子句對所有訂單訂購產(chǎn)品數(shù)量進行明細匯總。

SELECTOrderID,ProductID,QuantityFROMOrderDetail

COMPUTE

SUM(Quantity)例4.39用COMPUTEBY子句按訂單號匯總出OrderDetail表中每個訂單訂購產(chǎn)品的數(shù)量。

SELECTOrderID,ProductID,QuantityFROMOrderDetail

ORDERBYOrderID

COMPUTESUM(Quantity)BYOrderID【注意】COMPUTE[BY]子句不能與SELECTINTO子句一起使用。COMPUTE子句中的列必須出現(xiàn)在SELECT子句的列表中。COMPUTEBY表示按指定的列進行明細匯總，使用BY關(guān)鍵字時必須同時使用ORDERBY子句，并且COMPUTEBY后出現(xiàn)的列必須具有與ORDERBY后出現(xiàn)的列相同的順序，且不能跳過其中的列。例如：如果ORDERBY子句按照如下順序指定排序列：

ORDERBYCategoryID,Price,Stocks則COMPUTEBY后的列表只能是下面任一種形式：

BYCategoryID,Price,StocksBYCategoryID,PriceBYCategoryID例4.40用COMPUTEBY子句按類別ID匯總出Product表中每類產(chǎn)品的平均價格和總庫存量。

SELECTCategoryID,ProductID,Price,StocksFROMProduct

ORDERBYCategoryID,PriceCOMPUTE

AVG(Price),SUM(Stocks)BYCategoryID課堂練習(xí)1、查詢產(chǎn)品類別為“飲料”的所有產(chǎn)品信息。2、顯示所有客戶的信息，以及其訂購產(chǎn)品的OrderID、OrderDate。（使用左外連接）3、顯示所有銷售員的信息，以及其銷售產(chǎn)品的OrderID、OrderDate。（使用右外連接）4、統(tǒng)計產(chǎn)品類別為“飲料”的產(chǎn)品的種類數(shù)量、以及總庫存。5、統(tǒng)計產(chǎn)品類別為“調(diào)味品”的產(chǎn)品的平均價格、最高價、以及最低價。6、統(tǒng)計產(chǎn)品編號為“p01005”所銷售總量。7、按照“性別”進行分組，分別統(tǒng)計Seller表中男、女的人數(shù)。8、按照“類別編號”進行分組，列出所有庫存量大于200且組平均價格大于50元的產(chǎn)品類型編號、平均價格、以及庫存總量。9、查詢所有產(chǎn)品信息，并使用COMPUTE子句對Product表中的產(chǎn)品種類、以及庫存總量進行明細匯總。10、查詢產(chǎn)品信息，并使用COMPUTEBY子句按CategoryID匯總出Product表中每種類別產(chǎn)品的平均價格、以及庫存總量。1、嵌套查詢所謂嵌套查詢指的是在一個SELECT查詢內(nèi)再嵌入一個SELECT查詢。外層的SELECT語句叫外部查詢；內(nèi)層的SELECT語句叫子查詢。使用子查詢時需注意：子查詢可以嵌套多層。子查詢需用圓括號（）括起來。子查詢中不能使用COMPUTE[BY]和INTO子句。子查詢的SELECT語句中不能使用image、text或ntext數(shù)據(jù)類型。【1）子查詢返回值的類型為單值單列】例4.41查詢訂單ID為‘10249’的顧客信息。

SELECTCustomerID,CompanyName,ConnectName,Address,ZipCode,TelephoneFROMCustomerWHERECustomerID=

(SELECTCustomerIDFROMOrdersWHEREOrderID='10249')該子查詢的執(zhí)行過程：首先對子查詢求值（僅一次），求出OrderID為‘10249’的CustomerID為‘c02’，然后作外部查詢，外部查詢依賴于子查詢的結(jié)果。例4.42顯示所有價格高于平均價格的產(chǎn)品。

SELECT*FROMProductWHEREPrice>(SELECTavg(Price)FROMProduct)【注意】在例4.41和例4.42中，子查詢的結(jié)果是用于比較的單值單列數(shù)據(jù)；如果子查詢中返回的是單列多值，則必須在子查詢前使用關(guān)鍵字ALL或ANY，否則系統(tǒng)會提示錯誤信息。關(guān)鍵字含義示例ALL比較子查詢的所有值>ALL大于子查詢結(jié)果中的所有值（大于最大的）<ALL小于子查詢結(jié)果中的所有值（小于最小的）>=ALL大于等于子查詢結(jié)果中的所有值<=ALL小于等于子查詢結(jié)果中的所有值=ALL等于子查詢結(jié)果中的所有值（通常沒有實際意義）<>ALL不等于子查詢結(jié)果中的任何一個值

ANY比較子查詢的任一值>ANY大于子查詢結(jié)果中的某個值（大于最小）<ANY小于子查詢結(jié)果中的某個值（小于最大）>=ANY大于等于子查