課件3關(guān)系數(shù)據(jù)庫語言sql

1、第三章關(guān)系數(shù)據(jù)庫標準語言SQL2022/8/26數(shù)據(jù)庫系統(tǒng)原理2 本章主要介紹標準SQL的數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新等語句的基本用法、視圖創(chuàng)建和使用、嵌入式SQL的語句形式，以及游標的定義、打開、推進、關(guān)閉。本章主要內(nèi)容2022/8/26數(shù)據(jù)庫系統(tǒng)原理3結(jié)構(gòu)化查詢語言SQL（structured query language）是一種介于關(guān)系代數(shù)和關(guān)系演算之間的語言，其功能包括數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制四個方面，是關(guān)系數(shù)據(jù)庫系統(tǒng)的國際標準。SQL86：第一個SQL標準，ANSI（American National Standards Institute，美國國家標準協(xié)會）。SQL8

2、9：1989年作了少許改進SQL92：也被稱作SQL2，ANSI和ISO（International Organization for Standardization，國際標準化組織）作了較大改動和完善，這是目前絕大多數(shù)商用RDBMS支持的版本。SQL99：也被稱作SQL3，是在SQL92的基礎(chǔ)上擴展而成的。SQL2003：也稱為SQL4，擴展了對XML的支持。此后又依次推出了SQL2006和SQL2008。2022/8/26數(shù)據(jù)庫系統(tǒng)原理4目錄3.1 銀行數(shù)據(jù)庫 3.2 數(shù)據(jù)定義 3.3 數(shù)據(jù)查詢 3.4 數(shù)據(jù)操縱 3.5 視圖 3.6 嵌入式SQL 2022/8/26數(shù)據(jù)庫系統(tǒng)原理53.1

3、 銀行數(shù)據(jù)庫 支行：Branch（BName，BCity，Assets）客戶：Customer（CId，CName，CStreet，CCity）存款賬戶：Account（ANo， BName，balance）貸款賬戶：Loan（LNo，BName，amount）儲蓄：Deposits（CId，ANo）借貸：Borrows（CId，LNo）2022/8/26數(shù)據(jù)庫系統(tǒng)原理63.2 數(shù)據(jù)定義 3.2.1 數(shù)據(jù)庫的創(chuàng)建 3.2.2 表的創(chuàng)建 3.2.3 索引的創(chuàng)建 2022/8/26數(shù)據(jù)庫系統(tǒng)原理73.2.1 數(shù)據(jù)庫的創(chuàng)建 數(shù)據(jù)庫是一個存儲空間，用于存放數(shù)據(jù)庫中的數(shù)據(jù)庫對象，包括：表、視圖、索引、存

4、儲過程、觸發(fā)器、安全控制信息以及其他對象等。 SQL Server 2000將數(shù)據(jù)庫映射為一組操作系統(tǒng)文件，數(shù)據(jù)和日志信息分別存儲在不同的文件中。 SQL Server數(shù)據(jù)庫中有三種物理文件：主數(shù)據(jù)文件、輔助數(shù)據(jù)文件和日志文件。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理81、主數(shù)據(jù)文件 主數(shù)據(jù)文件也稱為基本數(shù)據(jù)文件，是數(shù)據(jù)庫的起點，包含了數(shù)據(jù)庫的初始信息，并記錄數(shù)據(jù)庫還擁有哪些文件。每個數(shù)據(jù)庫有且只能有一個主數(shù)據(jù)文件。主數(shù)據(jù)文件是數(shù)據(jù)庫必須的文件。其文件擴展名是*.mdf。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理92、輔助數(shù)據(jù)文件 輔助數(shù)據(jù)庫文件又稱從屬文件，用于保存所有主數(shù)據(jù)文件中容納不下的數(shù)據(jù)，不是數(shù)據(jù)

5、庫必須的文件。一個數(shù)據(jù)庫中可以沒有、也可以有一個或多個輔助數(shù)據(jù)文件。其文件擴展名是*.ndf。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理103、日志文件 是數(shù)據(jù)庫故障恢復的重要手段和方法，用于記錄對數(shù)據(jù)庫的各種操作及所涉及的相關(guān)數(shù)據(jù)。每個數(shù)據(jù)庫至少擁有一個日志文件，日志文件的大小最少是1MB。其文件擴展名是*.ldf。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理11一、文件組為了有助于數(shù)據(jù)布局和管理任務，可將多個數(shù)據(jù)文件集合起來形成一個整體，這個整體就是文件組，每個組有一個組名。一個數(shù)據(jù)文件只能存在于一個文件組中，日志文件不屬于任何文件組。SQL Server 2000的文件組有三種類型：主文件組、用戶定義文件

6、組、默認文件組 2022/8/26數(shù)據(jù)庫系統(tǒng)原理121、主文件組 主文件組中包含著主數(shù)據(jù)文件以及相關(guān)內(nèi)容。在創(chuàng)建數(shù)據(jù)庫時，系統(tǒng)會自動創(chuàng)建主文件組，并將主數(shù)據(jù)文件及系統(tǒng)表的所有頁都分配到主要文件組中。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理132、用戶定義文件組 由用戶通過SQL Server企業(yè)管理器創(chuàng)建的文件組稱為用戶定義文件組（User-Defined Filegroup）。該組中包含邏輯上一體的數(shù)據(jù)文件和相關(guān)信息，大多數(shù)數(shù)據(jù)庫只需要一個文件組和一個日志文件就可很好的運行。但如果庫中的文件很多，就要創(chuàng)建用戶定義文件組，以便管理。使用時，可以通過企業(yè)管理器或Transact-SQL語句中的file

7、group子句指定需要的用戶定義文件組。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理143、默認文件組 在每個數(shù)據(jù)庫中，同一時間只能有一個文件組是默認文件組（Default Filegroup）。當進行數(shù)據(jù)庫操作時，如果不指定文件組，則系統(tǒng)自動選擇默認文件組?？墒褂肨ransact-SQL語句中的alter database語句指定數(shù)據(jù)庫的默認文件組。在不特別指定的情況下，系統(tǒng)將主要文件組認定為默認文件組。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理15二、數(shù)據(jù)庫的物理存儲結(jié)構(gòu) 數(shù)據(jù)文件的結(jié)構(gòu)按照層次可以劃分為頁面（page）和擴展盤區(qū)（extent）。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理161、頁面 是數(shù)據(jù)存儲的

8、基本單位。頁的尺寸是8KB。數(shù)據(jù)庫中的每個頁面中只能存儲一種數(shù)據(jù)庫對象的數(shù)據(jù)。一個頁面可以存放多條記錄，但一條記錄不能跨頁存放，即SQL Server 2000中一條記錄不能超過8060B。數(shù)據(jù)文件中的頁有8種類型：數(shù)據(jù)頁、索引頁、文本/圖像頁、全局分配映射表頁、頁空閑空間、索引分配映射表頁、大容量更改映射表頁和差異更改映射表頁。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理172、擴展盤區(qū) 由8個連續(xù)頁面組成的數(shù)據(jù)結(jié)構(gòu)稱為一個盤區(qū)。是SQL Server分配給表和索引的基本單位。擴展盤區(qū)可分為統(tǒng)一擴展盤區(qū)和混合擴展盤區(qū)。統(tǒng)一擴展盤區(qū)由一個數(shù)據(jù)庫對象所有?；旌蠑U展盤區(qū)可以為多個數(shù)據(jù)庫所有，即其中最多可以放

9、8種數(shù)據(jù)庫對象。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理18注意 一個數(shù)據(jù)庫是由文件組成的，文件是由盤區(qū)組成的，而盤區(qū)是由頁面組成的。 所有的SQL Server數(shù)據(jù)文件都會擁有兩個文件名：邏輯文件名和物理文件名。邏輯文件名是在Transact-SQL語句中引用物理文件時所使用的名稱，邏輯文件名必須是唯一的。物理文件名是包括路徑在內(nèi)的物理文件名。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理19三、創(chuàng)建數(shù)據(jù)庫 CREATE DATABASE database_nameON ，nLOG ON，n語法說明：database_name：新創(chuàng)建的數(shù)據(jù)庫的名稱。ON：用于定義存放“數(shù)據(jù)”的空間。LOG ON：用于定義存儲

10、“日志”的空間。在filespec中的內(nèi)容包括：文件邏輯名（NAME）；操作系統(tǒng)下的物理文件名（FILENAME）；文件的初始大?。⊿IZE）；文件大小的最大值（MAXSIZE）；文件大小的遞增量等（FILEGROWTH）。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理20例 創(chuàng)建銀行Bank數(shù)據(jù)庫CREATE DATABASE BankON ( NAME=Bank_dat, FILENAME=c:mssqldatabankdat.mdf, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5MB )LOG ON ( NAME=Bank_log, FILENAME=c:mssqlda

11、tabanklog.ldf, SIZE=5MB, MAXSIZE=25MB, FILEGROWTH=5MB )2022/8/26數(shù)據(jù)庫系統(tǒng)原理21四、刪除數(shù)據(jù)庫 DROP DATABASE database_name語法說明：master、tempdb、model、msdb系統(tǒng)數(shù)據(jù)庫不能刪除。正在使用的數(shù)據(jù)庫不能刪除。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理223.2.2 表的創(chuàng)建 數(shù)據(jù)庫中包含一個或多個表。表是數(shù)據(jù)的集合，是用來存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)。要對表中數(shù)據(jù)進行操縱（查詢、插入、刪除或修改表中的數(shù)據(jù)），應建立表的結(jié)構(gòu)，建表的同時還應定義好相關(guān)的完整性約束。 2022/8/26數(shù)據(jù)庫系統(tǒng)

12、原理23SQL Server2000數(shù)據(jù)類型 整數(shù)數(shù)據(jù)類型 浮點數(shù)據(jù)類型 二進制數(shù)據(jù)類型 字符數(shù)據(jù)類型 邏輯型日期/時間數(shù)據(jù)類型 貨幣數(shù)據(jù)類型 文本和圖像型 其他數(shù)據(jù)類型 用戶自定義數(shù)據(jù)類型 2022/8/26數(shù)據(jù)庫系統(tǒng)原理24一、整數(shù)數(shù)據(jù)類型 Tinyint：可以存儲從0到255之間的所有正整數(shù)。所占存儲空間大小為1個字節(jié)。 Smallint：可存儲從215到2151之間的所有正負整數(shù)。所占存儲空間大小為2個字節(jié)，其中1位表示整數(shù)值的正負號，其它15位表示整數(shù)值的長度和大小。Int：可存儲從231到2311之間的所有正負整數(shù)。所占存儲空間大小為4個字節(jié)，其中1位表示整數(shù)值的正負號，其它31位

13、表示整數(shù)值的長度和大小。Bigint：可存儲從263到2631之間的所有正負整數(shù)。所占存儲空間大小為8個字節(jié)。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理25二、浮點數(shù)據(jù)類型 real：可精確到第7位小數(shù)，其范圍為從3.40E+38到3.40E+38，占用4個字節(jié)的存儲空間。Float：可精確到第15位小數(shù)，其范圍為從1.79E+308到1.79E+308，占用8個字節(jié)的存儲空間。Decimal：可以提供小數(shù)所需要的實際存儲空間，可以用2到17個字節(jié)來存儲從10381到10381之間的數(shù)值。Numeric：與decimal數(shù)據(jù)類型完全相同。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理26三、二進制數(shù)據(jù)類型 Bin

14、ary(n)：固定長度為n個字節(jié)二進制數(shù)據(jù)，最大長度可達8KB。n取值為1到8000。Varbinary(n)：n個字節(jié)可變長二進制數(shù)據(jù)，不能超過8KB。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理27四、字符數(shù)據(jù)類型 Char(n)：使用固定長度來存儲字符，每個字符和符號占一個字節(jié)的存儲空間。n表示所有字符所占的存儲空間，n的取值為1到8000，默認值為1。Varchar(n)：與char類型相似，n的取值為1到8000。Varchar數(shù)據(jù)類型具有變動長度的特性，存儲長度為實際數(shù)值長度。Nchar(n)：與char 類型相似，n的取值為1到4000。NCHAR類型采用UNICODE標準字符集（Char

15、acterSet）。UNICODE標準規(guī)定每個字符占用兩個字節(jié)的存儲空間，存儲空間是：字符數(shù)2（字節(jié)）。Nvarchar(n)：與varchar類型相似，n的取值為1到4000。nvarchar數(shù)據(jù)類型采用UNICODE標準字符集。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理28五、邏輯型 bit：bit數(shù)據(jù)類型占用1個字節(jié)的存儲空間，其值為0或1。如果輸入0或1以外的值，將被視為1。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理29六、日期/時間數(shù)據(jù)類型 Datetime：存儲從1753年1月1日零時起到9999年12月31日23時59分59秒之間的所有日期和時間，其精確度可達三百分之一秒，即3.33毫秒。所占存

16、儲空間為8個字節(jié)。Smalldatetime：存儲從1900年1月1日到2079年6月6日，但精度較低，只能精確到分鐘。所占存儲空間為4個字節(jié)。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理30七、貨幣數(shù)據(jù)類型 Money：有4位小數(shù)的DECIMAL值，其取值從263到2631，數(shù)據(jù)精度為萬分之一貨幣單位。所占存儲空間為8個字節(jié)。Smallmoney：其取值從214,748.3648到+214,748.3647，所占存儲空間為4個字節(jié)。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理31八、文本和圖像型 Text：專門用于存儲數(shù)量龐大的變長字符數(shù)據(jù)。最大長度可達2311個字符。Ntext：用于存儲可變長度的unicod

17、e數(shù)據(jù)，最多可存儲2301個unicode數(shù)據(jù)。Image：可用于存儲字節(jié)數(shù)超過8KB的數(shù)據(jù)，如Microsoft Word文檔、Microsoft Excel圖表以及圖像數(shù)據(jù)等，其最大長度為2311個字節(jié)。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理32九、其他數(shù)據(jù)類型 Cursor：是游標型數(shù)據(jù)類型，用于創(chuàng)建游標變量或定義存儲過程的輸出參數(shù)。Sql_variant：可以存儲除文本、圖形數(shù)據(jù)（text、ntext、image）和timestamp類型數(shù)據(jù)外的其它任何合法的SQL Server數(shù)據(jù)。Table：用于存儲對表或視圖處理后的結(jié)果集。Timestamp：相當于binary(8)或varbina

18、ry(8)，當定義的列在更新或插入數(shù)據(jù)行時，此列的值會被自動更新，每個數(shù)據(jù)庫表中只能有一個TIMESTAMP數(shù)據(jù)列。Uniqueidentifier：存儲一個16位的二進制數(shù)字，由NEWID函數(shù)產(chǎn)生的惟一的編碼。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理33十、用戶自定義數(shù)據(jù)類型 用戶定義數(shù)據(jù)類型基于SQL Server 2000中的系統(tǒng)數(shù)據(jù)類型。當多個表的列中要存儲同樣類型的數(shù)據(jù)，且想確保這些列具有完全相同的數(shù)據(jù)類型、長度和為空性時，可使用用戶定義數(shù)據(jù)類型。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理343.2.2 表的創(chuàng)建 數(shù)據(jù)庫中包含一個或多個表。表是數(shù)據(jù)的集合，是用來存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)。要對表

19、中數(shù)據(jù)進行操縱（查詢、插入、刪除或修改表中的數(shù)據(jù)），應建立表的結(jié)構(gòu)，建表的同時還應定義好相關(guān)的完整性約束。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理35一、創(chuàng)建表 CREATE TABLE database_name. owner .| owner. table_name（column_definition|table_constraint，n ）語法說明：table_name：新建的表的名稱。在column_definition中的主要內(nèi)容包括：列名、列類型、是否允許空值、初值和列級約束等。table_constraint指的是設定表級約束，包括主碼約束、唯一約束和外碼約束、檢查等。 2022/8/

20、26數(shù)據(jù)庫系統(tǒng)原理36例 創(chuàng)建銀行數(shù)據(jù)庫中的六個表結(jié)構(gòu)CREATE TABLE Bank.dbo.Branch（BName CHAR（15）PRIMARY KEY,BCity VARCHAR（20）NOT NULL,Assets DECIMAL（20,2）NOT NULL）CREATE TABLE Bank.dbo.Customer（CId CHAR（7）PRIMARY KEY NONCLUSTERED,CName CHAR（8）NOT NULL,CStreet VARCHAR（20）NULL,CCity VARCHAR（20）NULL）CREATE TABLE Bank.dbo.Accoun

21、t（ANo CHAR（4）PRIMARY KEY,BName CHAR（15）NOT NULL FOREIGN KEY REFERENCES Branch,balance DECIMAL（10,2）NOT NULL）2022/8/26數(shù)據(jù)庫系統(tǒng)原理37例 創(chuàng)建銀行數(shù)據(jù)庫中的六個表結(jié)構(gòu)CREATE TABLE Bank.dbo.Loan（LNo CHAR（4）PRIMARY KEY,BName CHAR（15）NOT NULL FOREIGN KEY REFERENCES Branch,amount DECIMAL（10,2）NOT NULL）CREATE TABLE Bank.dbo.Depo

22、sits（CId CHAR（7）NOT NULL FOREIGN KEY REFERENCES Customer,ANo CHAR（4）NOT NULL FOREIGN KEY REFERENCES AccountPRIMARY KEY（CId,ANo）CREATE TABLE Bank.dbo.Borrows（CId CHAR（7）NOT NULL,LNo CHAR（4）NOT NULLPRIMARY KEY（CId,LNo）2022/8/26數(shù)據(jù)庫系統(tǒng)原理38二、修改表 ALTER TABLE table_name ALTER COLUMN column_name new_data_typ

23、e ( precision , scale ) NULL | NOT NULL | ADD | ADD ,n | DROP CONSTRAINT constraint_name | COLUMN column ,n 2022/8/26數(shù)據(jù)庫系統(tǒng)原理39修改表（續(xù)）語法說明：ALTER COLUMN用于修改表中原有列，可修改內(nèi)容包括數(shù)據(jù)類型、大小和可空性。ADD 用于增加新的列。ADD 用于增加新的表級約束。DROP CONSTRAINT constraint_name | COLUMN column 用于刪除約束或原有的列。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理40例子例 修改客戶Customer

24、表的結(jié)構(gòu)，改變說明列的數(shù)據(jù)類型。ALTER TABLE Bank.dbo.Customer ALTER COLUMN CName CHAR(10)ALTER TABLE Bank.dbo.Customer ALTER COLUMN CCity CHAR(20)例 修改借貸Borrows表結(jié)構(gòu)，新增外碼約束。ALTER TABLE Bank.dbo.Borrows ADD FOREIGN KEY (CId) REFERENCES CustomerALTER TABLE Bank.dbo.Borrows ADD FOREIGN KEY (LNo) REFERENCES Loan例 修改客戶Cust

25、omer表結(jié)構(gòu)，增加一新列phone。ALTER TABLE Bank.dbo.Customer ADD phone CHAR(10)例 修改客戶Customer表結(jié)構(gòu)，刪除上面的新增列。ALTER TABLE Bank.dbo.Customer DROP COLUMN phone2022/8/26數(shù)據(jù)庫系統(tǒng)原理41三、刪除表 DROP TABLE table_name 語法說明：刪除表操作會將表中所有數(shù)據(jù)刪除，而且直接或間接地建立在該表上的視圖及相關(guān)授權(quán)等與此表有關(guān)的內(nèi)容也會被自動撤銷。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理423.2.3 索引的創(chuàng)建 索引（index）是關(guān)于數(shù)據(jù)位置信息的關(guān)鍵字

26、表，由表中的一列或若干列字段值以及相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針構(gòu)成。利用索引，系統(tǒng)可較快地在磁盤上定位所需數(shù)據(jù)，從而加快了數(shù)據(jù)查詢的速度；通過在表中創(chuàng)建索引還可以保證數(shù)據(jù)記錄的唯一性；此外，利用索引還可以加速表與表之間的連接操作。2022/8/26數(shù)據(jù)庫系統(tǒng)原理43一、索引的類型聚簇（clustered）索引非聚簇（nonclustered）索引復合索引唯一（unique）索引2022/8/26數(shù)據(jù)庫系統(tǒng)原理441、聚簇索引 磁盤上表的數(shù)據(jù)存儲在相鄰物理空間，并且表中行的物理順序與索引的順序一致。每張表最多只能建一個聚簇索引。合理地創(chuàng)建聚簇索引，可以十分顯著地提高系統(tǒng)性能。當

27、在一個表格中建立了聚簇索引后，在執(zhí)行插入、修改、刪除等操作時，系統(tǒng)要維護聚簇結(jié)構(gòu)，開銷比較大。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理452、非聚簇索引 與表中數(shù)據(jù)行的實際存儲結(jié)構(gòu)無關(guān)，在創(chuàng)建與使用時不會改變數(shù)據(jù)表中記錄的實際存儲順序。非聚簇索引保存的是行指針，而不是數(shù)據(jù)頁，因此檢索速度不如聚簇索引快。在SQL Server 2000中每個表最多能創(chuàng)建249個非聚簇索引。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理463、復合索引 對多個字段創(chuàng)建的索引。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理474、唯一索引 該索引要求被索引的列不能有相同值出現(xiàn)，可用來限制表中某些字段值的唯一性。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理

28、48建索引時應遵循的原則 經(jīng)常做為條件查詢的字段加上索引，如果幾個字段同時做為查詢時就建復合索引。在表的主鍵、外鍵上創(chuàng)建索引。在經(jīng)常用于表間連接的字段上建立索引。2022/8/26數(shù)據(jù)庫系統(tǒng)原理49建立聚簇索引若滿足下列情況之一，可考慮建立聚簇索引，否則應建立非聚簇索引：（1）檢索數(shù)據(jù)時，常以某個屬性（組）作為排序、分組條件。（2）檢索數(shù)據(jù)時，常以某個屬性（組）作為檢索限制條件，并返回大量數(shù)據(jù)。（3）表格中某個屬性（組）的值重復性較大。2022/8/26數(shù)據(jù)庫系統(tǒng)原理50二、創(chuàng)建索引 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name

29、 ON table_name ( column ASC | DESC ,n ) 語法說明：索引的建立和刪除，必須要有DBA權(quán)限或是表的擁有者。UNIQUE表示建立唯一性的索引。CLUSTERED | NONCLUSTERED 表示聚簇索引或非聚簇索引。ON table表示為哪個表創(chuàng)建索引。column ASC | DESC ,n 用于指定索引基于的列和索引排序方式。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理51例子CREATE CLUSTERED INDEX CName ON Customer(CName)CREATE INDEX Customer_address ON Customer(CStree

30、t, CCity)CREATE INDEX Account_BName ON Account(BName)CREATE INDEX Account_balance ON Account(balance)2022/8/26數(shù)據(jù)庫系統(tǒng)原理52三、刪除索引 DROP INDEX index_name語法說明：只有索引的擁有者和具有DBA權(quán)限的用戶，可以刪除索引。索引刪除后，有關(guān)索引的定義將從系統(tǒng)的數(shù)據(jù)字典（DD）中刪除，并且包含在索引中的全部索引項將被清除。索引的刪除，不會影響其他表和索引的正常使用，只會在某種程度上影響系統(tǒng)的性能。在數(shù)據(jù)定義語言中，沒有索引的修改功能。要修改索引，可先刪除要修改的索

31、引，然后創(chuàng)建一個同名的索引。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理533.3 數(shù)據(jù)查詢 3.3.1 基本語法格式3.3.2 單表查詢3.3.3 聚集函數(shù)3.3.4 多表查詢 2022/8/26數(shù)據(jù)庫系統(tǒng)原理543.3.1 基本語法格式 SELECT select_listFROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 2022/8/26數(shù)據(jù)庫系統(tǒng)原理55語法說明：select_list：

32、描述進入結(jié)果集的列，是由逗號分隔的表達式的列表。table_source：用于指定產(chǎn)生查詢結(jié)果的數(shù)據(jù)源。search_condition：用于從指定數(shù)據(jù)源中篩選元組。group_by_expression：分組的依據(jù)。search_condition：用于篩選分組。order_expression：排序的依據(jù)。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理56一、從單表中讀取指定列 例 查詢?nèi)w客戶表中的客戶標識和客戶姓名。SELECT CId,CName FROM Customer 例 查詢?nèi)w客戶的全部信息。SELECT * FROM Customer 例 查詢有貸款記錄的支行名稱。SELECT DI

33、STINCT BName FROM Loan 2022/8/26數(shù)據(jù)庫系統(tǒng)原理57一、從單表中讀取指定列（續(xù)）例 以萬元為單位顯示支行的資產(chǎn)額。格式一：SELECT BName,BCity,Assets/10 000 FROM Branch格式二：SELECT BName,BCity,Assets/10 000 AS Assets_ tenthousand FROM Branch格式三：SELECT BName name, BCity city, Assets/10 000 Assets_tenthousand FROM Branch2022/8/26數(shù)據(jù)庫系統(tǒng)原理58二、從單表中讀取指定行

34、例 查詢客戶姓名為“劉偉”的客戶信息。SELECT * FROM Customer WHERE CName=劉偉 2022/8/26數(shù)據(jù)庫系統(tǒng)原理59常用的條件表達式運算符 運算類型運 算 符比較=、=、=、!=范圍BETWEEN AND，NOT BETWEEN AND集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL邏輯AND，OR，NOT2022/8/26數(shù)據(jù)庫系統(tǒng)原理60例子例 查詢貸款金額在10 000元以上的貸款信息。SELECT * FROM Loan WHERE amount10000例 查詢存款金額在5000元至7000元之間(含

35、5 000元和7 000元)的賬號。格式一：SELECT * FROM Account WHERE balance BETWEEN 5000 AND 7000格式二：SELECT * FROM Account WHERE balance=5000 AND balance=7000 2022/8/26數(shù)據(jù)庫系統(tǒng)原理61例子例 查詢存款金額不在5 000元至7 000元之間的賬號。格式一：SELECT * FROM Account WHERE balance NOT BETWEEN 5000 AND 7000格式二：SELECT * FROM Account WHERE balance7000 例

36、 查詢“解放支行”和“鐵路支行”的賬戶信息。格式一：SELECT * FROM Account WHERE BName IN (解放支行, 鐵路支行)格式二：SELECT * FROM Account WHERE BName=解放支行 OR BName=鐵路支行 2022/8/26數(shù)據(jù)庫系統(tǒng)原理62例子例 查詢不是“解放支行”和“鐵路支行”的賬戶信息。格式一：SELECT * FROM Account WHERE BName NOT IN (解放支行, 鐵路支行)格式二：SELECT * FROM Account WHERE BName解放支行AND BName!= 鐵路支行 例 查詢沒有登記

37、所在街道的客戶信息。SELECT * FROM Customer WHERE CStreet IS NULL 2022/8/26數(shù)據(jù)庫系統(tǒng)原理63例子例 查詢所有登記了所在街道的客戶信息。SELECT * FROM Customer WHERE CStreet IS NOT NULL 例 查詢所有姓“劉”的客戶信息。SELECT * FROM Customer WHERE CName LIKE 劉% 例 查詢所有不姓“劉”的客戶信息。SELECT * FROM Customer WHERE CName NOT LIKE 劉% 例 查詢姓名中第二個字為“大”的顧客信息。SELECT * FROM

38、 Customer WHERE CName LIKE _ _大% 例 查詢支行名稱以“青島_”打頭的支行信息。SELECT * FROM Branch WHERE BName LIKE 青島_% ESCAPE 2022/8/26數(shù)據(jù)庫系統(tǒng)原理64三、從單表中讀取指定行和指定列 例 查詢記錄所在街道的客戶姓名。SELECT CName FROM Customer WHERE CStreet IS NOT NULL 例 查詢“市中支行”的所有賬號。SELECT ANo FROM Account WHERE BName=市中支行 2022/8/26數(shù)據(jù)庫系統(tǒng)原理65四、對查詢結(jié)果進行排序 例 按資產(chǎn)

39、額從小到大的順序顯示支行信息。SELECT * FROM Branch ORDER BY assets 例 查詢?nèi)抠~戶信息，查詢結(jié)果按開戶支行升序排列，相同開戶支行情況下按賬戶余額降序排列。SELECT * FROM Account ORDER BY BName ASC,balance DESC 2022/8/26數(shù)據(jù)庫系統(tǒng)原理663.3.3 聚集函數(shù) COUNT(ALL|DISTINCT expression|*)統(tǒng)計記錄個數(shù)SUM (ALL|DISTINCT expression) 計算指定表達式的總和AVG(ALL|DISTINCT expression) 計算指定表達式的平均值MAX

40、(ALL|DISTINCT expression) 計算指定表達式中的最大值MIN(ALL|DISTINCT expression) 計算指定表達式中的最小值 注意：如果在聚集函數(shù)中指定DISTINCT關(guān)鍵詞，則表示在計算時要取消指定表達式中的重復值，如果不指定DISTINCT關(guān)鍵詞或ALL關(guān)鍵詞(ALL為缺省值)，則表示不取消重表達式值。COUNT（*）返回所有記錄的行數(shù)，其中包括NULL值的行，而COUNT（ALL|DISTINCT expression）返回的是表達式值不為NULL值的行數(shù)。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理67一、使用聚集函數(shù)例 查詢?nèi)靠蛻粲涗泜€數(shù)。SELECT CO

41、UNT(*) FROM Customer例 查詢登記了所在街道的客戶人數(shù)。SELECT COUNT(CStreet) FROM Customer例 查詢支行所在城市的個數(shù)。SELECT COUNT(DISTINCT BCity) FROM Branch例 查詢開戶行是“市中支行”的賬戶余額總額。SELECT SUM(balance) FROM Account WHERE BName=市中支行2022/8/26數(shù)據(jù)庫系統(tǒng)原理68例子例 查詢所有支行的平均資產(chǎn)額。SELECT AVG(assets) FROM Branch例 查詢所有支行中資產(chǎn)額最大的支行。SELECT MAX(assets) F

42、ROM Branch例 查詢所有支行中資產(chǎn)額最小的支行。SELECT MIN(assets) FROM Branch 2022/8/26數(shù)據(jù)庫系統(tǒng)原理69二、查詢結(jié)果分組 例 查詢不同所在城市中支行的個數(shù)。SELECT BCity,COUNT(*) FROM Branch GROUP BY BCity 例 查詢不同所在城市支行的資產(chǎn)總額。SELECT BCity,SUM(assets) FROM Branch GROUP BY BCity例 查詢所在城市中有兩個以上支行的城市。SELECT BCity FROM Branch GROUP BY BCity HAVING count(*)=2 2

43、022/8/26數(shù)據(jù)庫系統(tǒng)原理703.3.4 多表查詢 多表查詢方法包括：嵌套子查詢、相關(guān)子查詢、集合查詢、連接查詢等。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理71一、嵌套子查詢 嵌套子查詢概念：一個查詢語句的查詢結(jié)果作為另一個查詢語句的條件，則前者為“子查詢”，亦稱“嵌套子查詢”（nested sub-query），而后者為“主查詢”，或稱“外查詢”。2022/8/26數(shù)據(jù)庫系統(tǒng)原理72例子例 查詢有儲蓄賬戶的客戶姓名。SELECT CName FROM Customer WHERE CId IN (SELECT CId FROM Deposits) 例 查詢比所在城市是“濟南”的所有支行資產(chǎn)額都

44、大的支行名稱。格式一：SELECT BName FROM Branch WHERE assetsALL (SELECT assets FROM Branch WHERE BCity=濟南)格式二：SELECT BName FROM Branch WHERE assets(SELECT MAX(assets) FROM Branch WHERE BCity=濟南) 2022/8/26數(shù)據(jù)庫系統(tǒng)原理73比較運算符 ANY大于子查詢結(jié)果中的某個值A(chǔ)LL大于子查詢結(jié)果中的所有值A(chǔ)NY小于子查詢結(jié)果中的某個值=ANY大于等于子查詢結(jié)果中的某個值=ALL大于等于子查詢結(jié)果中的所有值=ANY小于等于子查詢結(jié)

45、果中的某個值=ALL小于等于子查詢結(jié)果中的所有值=ANY等于子查詢結(jié)果中的某個值=ALL等于子查詢結(jié)果中的所有值(通常沒有實際意義)!=(或) ANY不等于子查詢結(jié)果中的某個值!=(或) ALL不等于子查詢結(jié)果中的所有值2022/8/26數(shù)據(jù)庫系統(tǒng)原理74帶有ANY或ALL的子查詢可用集函數(shù)來實現(xiàn) =!=或=ANYINMAXMIN=MINALLNOT INMINMAX=MAX2022/8/26數(shù)據(jù)庫系統(tǒng)原理75例子例 查詢比所在城市是“濟南”的某個支行的資產(chǎn)額小的支行名稱。格式一：SELECT BName FROM Branch WHERE assetsANY (SELECT assets F

46、ROM Branch WHERE BCity=濟南) AND BCity濟南格式二：SELECT BName FROM Branch WHERE assets(SELECT MAX(assets) FROM Branch WHERE BCity=濟南) and BCity濟南 2022/8/26數(shù)據(jù)庫系統(tǒng)原理76二、相關(guān)子查詢 相關(guān)子查詢概念：是一種特殊的嵌套子查詢，即子查詢與來自主查詢的列有關(guān)。首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個元組；重復這一過程，直至外層表全部檢查完為止。2022

47、/8/26數(shù)據(jù)庫系統(tǒng)原理77例子例 查詢有儲蓄賬戶的客戶姓名。SELECT CName FROM Customer WHERE EXISTS (SELECT * FROM Deposits WHERE Deposits.CId=Customer.CId) 例 查詢比所在城市是“濟南”的所有支行資產(chǎn)額都大的支行名稱。SELECT BName FROM Branch AS A WHERE NOT EXISTS (SELECT * FROM Branch AS B WHERE B.BCity=濟南 and B.assetsA.assets) 2022/8/26數(shù)據(jù)庫系統(tǒng)原理78三、集合查詢 SELE

48、CT語句的查詢結(jié)果是記錄的集合，所以多個SELECT語句的結(jié)果可進行集合操作。不過在SQL Server中只支持UNION集合的并操作（有些產(chǎn)品實現(xiàn)的SQL中支持交INTERSECT和差EXCEPT運算）。交和差操作可以使用變通（使用EXISTS）的方法進行處理 2022/8/26數(shù)據(jù)庫系統(tǒng)原理79例子例 查詢有儲蓄或者有貸款的客戶標識。SELECT CId FROM Deposits UNION SELECT CId FROM Borrows 例 查詢既有儲蓄又有貸款的客戶標識（Deposits和Borrows的交）。格式一：SELECT CId FROM Deposits WHERE EX

49、ISTS (SELECT * FROM Borrows WHERE Borrows.CId = Deposits.CId)格式二：SELECT CId FROM Deposits WHERE CId IN (SELECT CId FROM Borrows)2022/8/26數(shù)據(jù)庫系統(tǒng)原理80例子例 查詢只有儲蓄沒有貸款的客戶標識（Deposits和Borrows的差）。格式一：SELECT CId FROM Deposits WHERE NOT EXISTS (SELECT * FROM Borrows WHERE Borrows.CId = Deposits.CId)格式二：SELECT C

50、Id FROM Deposits WHERE CId NOT IN (SELECT CId FROM Borrows)2022/8/26數(shù)據(jù)庫系統(tǒng)原理81四、連接查詢 From子句同時涉及多個表的查詢稱為連接查詢。用來連接兩個表的條件稱為連接條件或連接謂詞，一般格式：. . 比較運算符：=、=、=、!=. BETWEEN . AND .2022/8/26數(shù)據(jù)庫系統(tǒng)原理82連接查詢 （續(xù)）連接字段連接條件中的列名稱為連接字段連接條件中的各連接字段類型必須是可比的，但不必是相同的2022/8/26數(shù)據(jù)庫系統(tǒng)原理83連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個元組，然

51、后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。重復上述操作，直到表1中的全部元組都處理完畢 2022/8/26數(shù)據(jù)庫系統(tǒng)原理84排序合并法(SORT-MERGE)常用于=連接首先按連接屬性對表1和表2排序?qū)Ρ?的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。當遇到表2中第一條大于表1連接字

52、段值的元組時，對表2的查詢不再繼續(xù)2022/8/26數(shù)據(jù)庫系統(tǒng)原理85排序合并法找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù)重復上述操作，直到表1或表2中的全部元組都處理完畢為止 2022/8/26數(shù)據(jù)庫系統(tǒng)原理86索引連接(INDEX-JOIN)對表2按連接字段建立索引對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組 2022/

53、8/26數(shù)據(jù)庫系統(tǒng)原理87例子例 查詢儲蓄賬號及對應的客戶姓名。格式一：SELECT ANo,CName FROM Deposits, Customer WHERE Deposits.CId = Customer.CId格式二：SELECT ANo,CName FROM Deposits A,Customer B WHERE A.CId = B.CId格式三：SELECT ANo,CName FROM Deposits AS A INNER JOIN Customer AS B ON A.CId = B.CId 2022/8/26數(shù)據(jù)庫系統(tǒng)原理88例子例 查詢儲蓄賬號、對應的客戶姓名以及儲蓄金

54、額。SELECT A.ANo,CName,balance FROM Deposits A,Customer B,Account C WHERE A.CId = B.CId AND A.ANo = C.ANo 例 查詢?nèi)靠蛻粜畔?，同時顯示貸款號(如果有的話)。格式一：SELECT customer.*,Borrows.LNo FROM Customer LEFT OUTER JOIN Borrows ON Customer.Cid = Borrows.CId格式二：SELECT customer.*,Borrows.LNo FROM Borrows RIGHT OUTER JOIN Custo

55、mer ON Customer.Cid = Borrows.CId 2022/8/26數(shù)據(jù)庫系統(tǒng)原理893.4 數(shù)據(jù)操縱 3.4.1 數(shù)據(jù)插入 3.4.2 數(shù)據(jù)修改 3.4.3 數(shù)據(jù)刪除 2022/8/26數(shù)據(jù)庫系統(tǒng)原理903.4.1 數(shù)據(jù)插入 格式一：INSERT INTOtable_name|viewname (column_list) VALUES( data_values)格式二：INSERT INTO table_name|viewname (column_list) SELECT statement 2022/8/26數(shù)據(jù)庫系統(tǒng)原理91語法說明 如果有列名表“column_list

56、”，則要求其后的數(shù)據(jù)在順序、個數(shù)和類型上，應與其一一對應；如果沒有列名表，則要求插入的數(shù)據(jù)在順序、個數(shù)和類型上，應與表或視圖定義中的列一一對應。插入數(shù)據(jù)時，對于允許為空的列，可使用NULL插入空值。格式一為單行數(shù)據(jù)插入方式，一次只能插入一行記錄。格式二為集合數(shù)據(jù)插入方式，一次可插入一行或多行記錄。2022/8/26數(shù)據(jù)庫系統(tǒng)原理92例子例 在銀行數(shù)據(jù)庫表中插入數(shù)據(jù)。INSERT INTO Branch(BName,BCity,assets) VALUES(棧橋支行, 青島,7100000)INSERT INTO Branch VALUES(市中支行, 青島,9000000)INSERT INT

57、O Customer VALUES(0503001, 李勇, 迎春路, 威海)INSERT INTO Account VALUES(A101, 市中支行,5000) 2022/8/26數(shù)據(jù)庫系統(tǒng)原理93例子例 將客戶姓名、存款賬號和存款余額信息插入到一個臨時表中。CREATE TABLE Customer_Deposits(CName CHAR(8),ANo CHAR(4),balance DECIMAL(10,2)INSERT INTO Customer_Deposits SELECT ame, B.ANo, B.balance FROM Customer A,Account B,Depos

58、its C WHERE CId=C.CId AND B.ANo=C.ANo 2022/8/26數(shù)據(jù)庫系統(tǒng)原理943.4.2 數(shù)據(jù)修改 UPDATE table_name|viewname SET columnname =expression | NULL, n WHERE 語法說明：UPDATE后的表名和視圖名，是指要進行修改操作的表或視圖。如果沒有WHERE子句，則將修改表中的所有數(shù)據(jù)，否則只修改滿足search_condition查詢條件的記錄。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理95例子例 修改Customer表中的數(shù)據(jù)。UPDATE Customer SET CStreet=和平路 WH

59、ERE CId =0503001UPDATE Customer SET CStreet=新華路, customer_city=濟南WHERE CName=錢小平 2022/8/26數(shù)據(jù)庫系統(tǒng)原理963.4.3 數(shù)據(jù)刪除 DELETE FROM table_name|viewname WHERE 語法說明：DELETE后的表名和視圖名，是指要進行刪除操作的表或視圖。如果沒有WHERE子句，則將刪除表中的所有數(shù)據(jù)，否則只刪除滿足search_condition查詢條件的記錄。 2022/8/26數(shù)據(jù)庫系統(tǒng)原理97例子例 刪除Borrows借貸表中的所有數(shù)據(jù)。DELETE FROM Borrows例 刪除Customer客戶表中的指定數(shù)據(jù)。DELETE FROM Customer WHERE CId=0