數(shù)據(jù)庫整理第3章sql

1、第3章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,2,3.1 SQL語言的基本概念與特點(diǎn) 3.2 了解SQL Server 2008 3.3 創(chuàng)建與使用數(shù)據(jù)庫 3.4 創(chuàng)建與使用數(shù)據(jù)表 3.5 創(chuàng)建與使用索引 3.6 數(shù)據(jù)查詢 3.7 數(shù)據(jù)更新 3.8 視圖 3.9 數(shù)據(jù)控制,3,結(jié)構(gòu)化查詢語言 Structured Query Language,數(shù)據(jù)查詢 數(shù)據(jù)定義 數(shù)據(jù)操縱 數(shù)據(jù)控制,SQL簡潔、方便實(shí)用、功能齊全，已成為目前應(yīng)用最廣泛的關(guān)系數(shù)據(jù)庫語言。,4,3.1 SQL語言的基本概念與特點(diǎn),3.1.1 SQL語言的發(fā)展及標(biāo)準(zhǔn)化 SQL語言的發(fā)展,Chamberlin,SEQUEL,SQL,大型數(shù)據(jù)庫 S

2、ybase INFORMIX SQL Server Oracle DB2 INGRES - 小型數(shù)據(jù)庫 FoxPro Access,5,3.1.2 SQL語言的基本概念,基本表（Base Table） 一個關(guān)系對應(yīng)一個基本表 一個或多個基本表對應(yīng)一個存儲文件 視圖（View） 視圖是從一個或幾個基本表導(dǎo)出的表，是一個虛擬的表 S(SNo，SN，Sex，Age，Dept) S_Male(SNo，SN，Age，Dept),無數(shù)據(jù)，只有定義,Sex=男,在數(shù)據(jù)庫中只存有S_Male的定義，數(shù)據(jù)仍在S表中,6,SQL語言支持的關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu),7,3.1.3 SQL語言的主要特點(diǎn),SQL語言是

3、類似于英語的自然語言，簡潔易用 SQL語言是一種非過程語言 SQL語言是一種面向集合的語言 SQL語言既是自含式語言，又是嵌入式語言 SQL語言具有數(shù)據(jù)查詢、數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制四種功能,8,3.2 了解SQL Server 2008,企業(yè)版 SQL Server 2008 是滿足企業(yè)聯(lián)機(jī)事務(wù)處理和數(shù)據(jù)倉庫應(yīng)用程序高標(biāo)準(zhǔn)要求的綜合數(shù)據(jù)平臺。 標(biāo)準(zhǔn)版 SQL Server 2008 Standard 是一個完整的數(shù)據(jù)管理和商業(yè)智能平臺，為正在運(yùn)行的部門應(yīng)用程序提供一流的易用性和易管理性。 Specialized 版本 工作組版 在此可靠的數(shù)據(jù)管理和報(bào)表平臺上運(yùn)行分機(jī)位置，提供安全性的遠(yuǎn)程

4、同步和管理功能。 網(wǎng)絡(luò)版 借助于面向 Web 服務(wù)環(huán)境的高度可用的 Internet，為您的客戶提供低成本、大規(guī)模、高度可用的 Web 應(yīng)用程序或主機(jī)解決方案。 移動版 可以免費(fèi)下載，為所有 Microsoft Windows 平臺上的移動設(shè)備、桌面和 Web 客戶端構(gòu)建單機(jī)應(yīng)用程序和偶爾連接的應(yīng)用程序。 免費(fèi)版 可以免費(fèi)下載，Express 對于學(xué)習(xí)和構(gòu)建桌面和小型服務(wù)器應(yīng)用程序以及對于通過 ISV 重新分發(fā)非常理想。,9,SQL Server 發(fā)展,10,3.2.1 SQL Server 2008的組件與體系結(jié)構(gòu),SQL Server 2008系統(tǒng)由4個主要部分組成，這4個部分被稱為4個服

5、務(wù)，分別是上面的數(shù)據(jù)引擎、分析服務(wù)、報(bào)表服務(wù)和集成服務(wù)。這些服務(wù)之間相互依存。,數(shù)據(jù)庫引擎是（SQL Server Database Engine，SSDE）是SQL Server 2008系統(tǒng)的核心服務(wù)，負(fù)責(zé)完成業(yè)務(wù)數(shù)據(jù)的存儲、處理、查詢和安全管理等操作。例如：創(chuàng)建數(shù)據(jù)庫、創(chuàng)建表、執(zhí)行各種數(shù)據(jù)查詢、訪問數(shù)據(jù)庫等操作都是由數(shù)據(jù)庫引擎完成的。 分析服務(wù)（SQL Server Analysis Server ，SSAS）提供了多維分析和數(shù)據(jù)挖掘功能，可以支持用戶建立數(shù)據(jù)庫和進(jìn)行商業(yè)智能分析。 報(bào)表服務(wù)（SQL Server Reporting Services ，SSRS）為用戶提供了支持Web的

6、企業(yè)級的報(bào)表功能。通過使用SQL Server 2008系統(tǒng)提供的SSRS服務(wù)，用戶可以方便地定義和發(fā)展布滿足自己需求的報(bào)表。 集成服務(wù)（SQL Server Integration Sevives，SSIS）是一個數(shù)據(jù)集成平臺，可以完成有關(guān)數(shù)據(jù)的提取、轉(zhuǎn)換、加載等。,11,3.2.2 企業(yè)管理器,由Enterprise Manager產(chǎn)生的SQL腳本是一個后綴名為.sql的文件 企業(yè)管理器的管理工作,文本文件,管理數(shù)據(jù)庫,管理數(shù)據(jù)庫對象,管理備份,管理復(fù)制,管理登錄和許可,管理SQL Server Agent,管理SQL Server Mail,企業(yè)管理器界面、菜單、工具欄,12,3.2.3

7、 查詢分析器,使用查詢分析器的熟練程度是衡量一個SQL Server用戶水平的標(biāo)準(zhǔn)。,自SQL server 2005開始微將企業(yè)管理器和查詢分析器合二為一，統(tǒng)一整合到 SQL Server Management Studio中。,13,3.3 創(chuàng)建與使用數(shù)據(jù)庫,數(shù)據(jù)文件1,事務(wù)日志文件,數(shù)據(jù)庫,數(shù)據(jù)文件n,存放數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對象的文件 主要數(shù)據(jù)文件(.mdf ) +次要數(shù)據(jù)文件(.ndf ),只有一個,可有多個,記錄數(shù)據(jù)庫更新情況，擴(kuò)展名為.ldf 當(dāng)數(shù)據(jù)庫破壞時可以用事務(wù)日志還原數(shù)據(jù) 庫內(nèi)容,采用多個數(shù)據(jù)文件來存儲數(shù)據(jù)的優(yōu)點(diǎn)體現(xiàn)在： (1)數(shù)據(jù)文件可以不斷擴(kuò)充而不受操作系統(tǒng)文件大小的限

8、制。 (2)可將數(shù)據(jù)文件存儲在不同的硬盤中，同時對多個硬盤做數(shù)據(jù)存取，提高效率。,14,文件組 文件組（File Group）是將多個數(shù)據(jù)文件集合起來形成的一個整體 主要文件組+次要文件組 一個數(shù)據(jù)文件只能存在于一個文件組中，一個文件組也只能被一個數(shù)據(jù)庫使用 日志文件不分組，它不能屬于任何文件組,15,3.3.1 SQL Server的系統(tǒng)數(shù)據(jù)庫,Model,Msdb,Tempdb,系統(tǒng)默認(rèn)數(shù)據(jù)庫,系統(tǒng)信息 ： 磁盤空間 ；文件分配和使用 ；系統(tǒng)級的配置參 數(shù)；登錄賬號信息 ；SQL Server初始化信息； 系統(tǒng)中其他系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫的相關(guān)信息,Model數(shù)據(jù)庫存儲了所有用戶數(shù)據(jù)庫和T

9、empdb數(shù) 據(jù)庫的創(chuàng)建模板 通過更改Model數(shù)據(jù)庫的設(shè)置可以大大簡化數(shù)據(jù) 庫及其對象的創(chuàng)建設(shè)置工作,存儲計(jì)劃信息以及與備份和還原相關(guān)的信息,Tempdb數(shù)據(jù)庫用作系統(tǒng)的臨時存儲空間 存儲臨時表,臨時存儲過程和全局變量值 ，創(chuàng)建臨 時表 ，存儲用戶利用游標(biāo)說明所篩選出來的數(shù)據(jù),Master,16,3.3.2 SQL Server的實(shí)例數(shù)據(jù)庫,實(shí)例數(shù)據(jù)庫,pubs,Northwind,虛構(gòu)的圖書出版公司的基本情況,包含了一個公司的銷售數(shù)據(jù),SQL Server2005及SQL Server 2008中，代碼示例和示例數(shù)據(jù)庫不再隨產(chǎn)品一起提供。但可以從 下載。,17,3.3.3 創(chuàng)建用戶數(shù)據(jù)庫,

10、用SQL Server Management Studio(SSMS)創(chuàng)建數(shù)據(jù)庫 用SQL命令創(chuàng)建數(shù)據(jù)庫 CREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n COLLATE collation_name FOR LOAD | FOR ATTACH ,18,例3-1 用SQL命令創(chuàng)建一個教學(xué)數(shù)據(jù)庫Teach，數(shù)據(jù)文件的邏輯名稱為Teach_Data，數(shù)據(jù)文件物理地存放在D：盤的根目錄下，文件名為TeachData.mdf，數(shù)據(jù)文件的初始存儲空間大小為10MB，最大存儲空間為50MB，存儲空間自動增長量為5MB；日志文件的邏輯名稱為Teach

11、_Log，日志文件物理地存放在D：盤的根目錄下，文件名為TeachLog.ldf，初始存儲空間大小為10MB，最大存儲空間為25MB，存儲空間自動增長量為5MB。,CREATE DATABASE Teach ON (NAME=Teach_Data, FILENAME=D:TeachData.mdf, SIZE=10, MAXSIZE=50, FILEGROWTH=5) LOG ON (NAME=Teach_Log, FILENAME=D:TeachLog.ldf, SIZE=5, MAXSIZE=25, FILEGROWTH=5),19,CREATE DATABASE MyDB ON PRIM

12、ARY (NAME = MyDB_file1, FILENAME = NC:DataMyDB_1.mdf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), ( NAME = MyDB_file2, FILENAME = NC:DataMyDB_2.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), FILEGROUP NewFileGroup1 ( NAME = MyDB_file3, FILENAME = NC:DataMyDB_3.ndf, SIZE = 10MB, MAXSIZE = 5

13、0MB, FILEGROWTH = 10%), ( NAME = MyDB_file4, FILENAME = NC:DataMyDB_4.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), FILEGROUP NewFileGroup2 ( NAME = MyDB_file5, FILENAME = NC:DataMyDB_5.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%) LOG ON ( NAME = MyDB_Logfile1, FILENAME = NC:DataMyDB_L

14、ogfile1.ldf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), ( NAME = MyDB_Logfile2, FILENAME = NC:DataMyDB_Logfile2.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB) GO,數(shù)據(jù)庫 MyDB,主文件組 10MB C:DataMyDB_1.mdf 10MB C:DataMyDB_2.ndf,NewFileGroup1文件組 10MB C:DataMyDB_3.ndf 10MB C:DataMyDB_4.ndf,NewFileGro

15、up2文件組 10MB C:DataMyDB_5.ndf,日志文件組 10MB C:DataMyDB_Logfile1.ldf 10MB C:DataMyDB_Logfile2.ldf,21,3.3.4 修改用戶數(shù)據(jù)庫,用SSMS修改數(shù)據(jù)庫 用SQL命令修改數(shù)據(jù)庫 ALTER DATABASE database_name ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name WITH DELETE | ADD FILEGROUP filegroup_name | R

16、EMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE ,22,例3-2 修改Teaching數(shù)據(jù)庫中的邏輯文件名Teaching_Data的文件增容方式為一次增加2MB。,ALTER DATABASE Teaching MODIFY FILE (NAME = Teaching_Data, FILE

17、GROWTH = 2mb ),23,3.3.5 刪除用戶數(shù)據(jù)庫,用SSMS刪除數(shù)據(jù)庫 用SQL命令刪除數(shù)據(jù)庫 DROP DATABASE database_name ,.n 例3-3 刪除數(shù)據(jù)庫Teach。 DROP DATABASE Teach,24,3.3.6 查看數(shù)據(jù)庫信息,用SSMS查看數(shù)據(jù)庫信息(屬性) 用系統(tǒng)存儲過程顯示數(shù)據(jù)庫信息 用系統(tǒng)存儲過程顯示數(shù)據(jù)庫結(jié)構(gòu) 用系統(tǒng)存儲過程顯示文件信息 用系統(tǒng)存儲過程顯示文件組信息,Sp_helpdb dbname= name,Sp_helpfile filename = name,Sp_helpfilegroup filegroupname =

18、 name,25,EXEC Sp_helpdb Northwind EXEC Sp_helpfile Northwind EXEC Sp_helpfilegroup,26,3.4 創(chuàng)建與使用數(shù)據(jù)表,3.4.1 數(shù)據(jù)類型,整數(shù)數(shù)據(jù),精確數(shù)值,近似浮點(diǎn)數(shù)值,日期時間數(shù)據(jù),bigint，int，smallint，tinyint,numeric和decimal,float和real,datetime與smalldatetime,27,字符串?dāng)?shù)據(jù),Unicode字符串?dāng)?shù)據(jù),二進(jìn)制數(shù)據(jù),貨幣數(shù)據(jù),char、varchar、text,nchar、nvarchar與ntext,binary、varbinary

19、、image,money與smallmoney,標(biāo)記數(shù)據(jù),timestamp和uniqueidentifier,28,3.4.2 創(chuàng)建數(shù)據(jù)表,用SSMS創(chuàng)建數(shù)據(jù)表 相關(guān)屬性定義 “字段名” “數(shù)據(jù)類型” 字段的“長度”、“精度”和“小數(shù)位數(shù)” “允許空” “默認(rèn)值”,同一表中不許有重名字段,系統(tǒng)默認(rèn)為NULL,29,用SQL命令創(chuàng)建數(shù)據(jù)表 CREATE TABLE (，|) 例3-4 用SQL命令建立一個學(xué)生表S。 CREATE TABLE S (SNo CHAR(6), SN VARCHAR(8), Sex CHAR(2) DEFAULT 男, Age INT, Dept VARCHAR(2

20、0), DEFAULT ,缺省值為“男”,30,3.4.3 定義數(shù)據(jù)表的約束,正確性,有效性,相容性,數(shù)據(jù)的完整性,約束（Constraint） 默認(rèn)（Default） 規(guī)則（Rule） 觸發(fā)器（Trigger） 存儲過程（Stored Procedure）,SQL Server的數(shù)據(jù)完整性機(jī)制,第3章,第5章,第7章,31,完整性約束的基本語法格式 CONSTRAINT ,NULL/NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,約束是SQL Server自動強(qiáng)制數(shù)據(jù)庫完整性的方式，約束定義了列中允許的取值。 列約束 CONSTRAINT 表約束

21、, , CONSTRAINT (,),列約束,列約束/表約束,列約束/表約束,列約束/表約束,列約束/表約束,32,NULL/NOT NULL約束 NULL表示“不知道”、“不確定”或“沒有數(shù)據(jù)”的意思 主鍵列不允許出現(xiàn)空值 CONSTRAINT NULL|NOT NULL 例3-5 建立一個S表，對SNo字段進(jìn)行NOT NULL約束。 CREATE TABLE S (SNo CHAR(6) CONSTRAINT S_Cons NOT NULL, SN VARCHAR(8), Sex CHAR(2), Age INT, Dept VARCHAR(20),可省略約束名稱 : SNo CHAR(6

22、) NOT NULL,33,UNIQUE約束（惟一約束） 指明基本表在某一列或多個列的組合上的取值必須惟一 在建立UNIQUE約束時，需要考慮以下幾個因素： 使用UNIQUE約束的字段允許為NULL值。 一個表中可以允許有多個UNIQUE約束。 可以把UNIQUE約束定義在多個字段上。 UNIQUE約束用于強(qiáng)制在指定字段上創(chuàng)建一個UNIQUE索引，缺省為非聚集索引。 UNIQUE用于定義列約束 CONSTRAINT UNIQUE UNIQUE用于定義表約束 CONSTRAINT UNIQUE（,）,34,例3-6 建立一個S表，定義SN為惟一鍵。 CREATE TABLE S (SNo CHA

23、R(6), SN CHAR(8) CONSTRAINT SN_Uniq UNIQUE, Sex CHAR(2), Age INT, Dept VARCHAR(20) 例3-7 建立一個S表，定義SN+SEX為惟一鍵，此約束為表約束。 CREATE TABLE S (SNo CHAR(6), SN CHAR(8) UNIQUE, Sex CHAR(2), Age INT, Dept VARCHAR(20), CONSTRAINT S_UNIQ UNIQUE(SN, Sex),SN_Uniq可以省略 SN CHAR(8) UNIQUE,35,PRIMARY KEY約束（主鍵約束） 用于定義基本表的

24、主鍵，起惟一標(biāo)識作用 PRIMARY KEY與UNIQUE 的區(qū)別： 一個基本表中只能有一個PRIMARY KEY，但可多個UNIQUE 對于指定為PRIMARY KEY的一個列或多個列的組合，其中任何一個列都不能出現(xiàn)NULL值，而對于UNIQUE所約束的惟一鍵，則允許為NULL 對于指定為PRIMARY KEY的一個列或多個列的組合，其中任何一個列都不能出現(xiàn)NULL值，而對于UNIQUE所約束的惟一鍵，則允許為NULL,不能為NULL,不能重復(fù),36,PRIMARY KEY用于定義列約束 CONSTRAINT PRIMARY KEY PRIMARY KEY用于定義表約束 CONSTRAINT

25、 PRIMARY KEY (,) 例3-8 建立一個S表，定義SNo為S的主鍵，建立另外一個數(shù)據(jù)表C，定義CNo為C的主鍵。 CREATE TABLE S ( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN CHAR(8), Sex CHAR(2), Age INT, Dept VARCHAR(20) CREATE TABLE C ( CNo CHAR(5) CONSTRAINT C_Prim PRIMARY KEY, CN CHAR(20), CT INT),37,例3-9 建立一個SC表，定義SNo+CNo為SC的主鍵。 CREATE TABLE

26、 SC (SNo CHAR(5) NOT NULL, CNo CHAR(5) NOT NULL, Score NUMERIC(4,1), CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo),38,FOREIGN KEY約束（外鍵約束） CONSTRAINT FOREIGN KEY REFERENCES (,),外部鍵,從表,主鍵,主表,引用,39,例3-10 建立一個SC表，定義SNo,CNo為SC的外部鍵。 CREATE TABLE SC (SNo CHAR(5) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES

27、S(SNo), CNo CHAR(5) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo), Score NUMERIC(4,1), CONSTRAINT S_C_Prim PRIMARY KEY (SNo,CNo);,40,CHECK約束 CHECK約束用來檢查字段值所允許的范圍 在建立CHECK約束時，需要考慮以下幾個因素： 一個表中可以定義多個CHECK約束。 每個字段只能定義一個CHECK約束。 在多個字段上定義的CHECK約束必須為表約束。 當(dāng)執(zhí)行INSERT、UNDATE語句時CHECK約束將驗(yàn)證數(shù)據(jù)。 CONSTRAI

28、NT CHECK (),41,例3-11 建立一個SC表，定義Score的取值范圍為0100之間。 CREATE TABLE SC (SNo CHAR(5), CNo CHAR(5), Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score=0 AND Score =100) 例3-12 建立包含完整性定義的學(xué)生表。 CREATE TABLE S (SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN CHAR(8) CONSTRAINT SN_Cons NOT NULL, Sex CHAR(2) DEFAU

29、LT 男, Age INT CONSTRAINT Age_Cons NOT NULL CONSTRAINT Age_Chk CHECK (Age BETWEEN 15 AND 50), Dept CHAR(10) CONSTRAINT Dept_Cons NOT NULL),42,3.4.4 修改數(shù)據(jù)表,用SSMS修改數(shù)據(jù)表的結(jié)構(gòu) 用SQL命令修改數(shù)據(jù)表,ALTER TABLE ADD | ,ALTER TABLE ALTER COLUMN NULL|NOT NULL,ALTER TABLE DROP CONSTRAINT ,43,例3-13 在S表中增加一個班號列和住址列。 ALTER TA

30、BLE S ADD Class_No CHAR(6), Address CHAR(40) 使用此方式增加的新列自動填充NULL值，所以不能為增加的新列指定NOT NULL約束。 例3-14 在SC表中增加完整性約束定義，使Score在0100之間。 ALTER TABLE SC ADD CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100),44,例3-15 把S表中的SN列加寬到10個字符。 ALTER TABLE S ALTER COLUMN SN CHAR(10) 不能改變列名； exec sp_rename 表名.原列名,新列名,colu

31、mn; 不能將含有空值的列的定義修改為NOT NULL約束； 若列中已有數(shù)據(jù)，則不能減少該列的寬度，也不能改變其數(shù)據(jù)類型；存在沖突，則不能做此更改 只能修改NULL/NOT NULL約束，其他類型的約束在修改之前必須先將約束刪除，然后再重新添加修改過的約束定義。 例3-16 刪除S表中的主鍵。 ALTER TABLE S DROP CONSTRAINT S_Prim,Drop方式只用于刪除完整性約束定義,45,3.4.5 刪除基本表,用SSMS刪除數(shù)據(jù)表 用SQL命令刪除數(shù)據(jù)表 DROP TABLE 只能刪除自己建立的表，不能刪除其他用戶所建的表,46,3.4.6 查看數(shù)據(jù)表,查看數(shù)據(jù)表的屬性

32、 屬性包括：數(shù)據(jù)表的名稱，所有者，創(chuàng)建日期，文件組，記錄的行數(shù)，數(shù)據(jù)表中的字段名稱、結(jié)構(gòu)和類型等。 查看數(shù)據(jù)表中的數(shù)據(jù) 在SSMS中，用右鍵單擊要查看數(shù)據(jù)的表，從快捷菜單中選擇“選擇前1000行”或選擇“編輯前200行”，或在查詢窗口，使用SELECT語句查看 。,47,3.5 創(chuàng)建與使用索引,3.5.1 索引的作用 3.5.2 索引的分類,加快查詢速度 保證行的惟一性,聚集索引與非聚集索引,唯一索引,復(fù)合索引,聚集索引：查詢速度快 非聚集索引：更新速度快,排列的結(jié)果存儲在表中 只有一個,排列的結(jié)果不存儲在表中 可以有多個,有UNIQUE，自動建立非聚集的惟一索引 有PRIMARY KEY，自

33、動建立聚集索引,將兩個或多個字段組合起來建立的索引， 單獨(dú)的字段允許有重復(fù)的值,48,3.5.3 創(chuàng)建索引,用SSMS創(chuàng)建索引 用索引創(chuàng)建向?qū)?chuàng)建索引 直接創(chuàng)建索引 用SQL命令創(chuàng)建索引 CREATE UNIQUE CLUSTERED INDEX ON ( 次序 , 次序),建立惟一索引,建立聚集索引,ASC或DESC，默認(rèn)為ASC,49,例3-18 為表SC在SNo和CNo上建立惟一索引。 CREATE UNIQUE INDEX SCI ON SC(SNo,CNo) 例3-19 為教師表T在TN上建立聚集索引。 CREATE CLUSTERED INDEX TI ON T(TN) 注意： （

34、1）改變表中的數(shù)據(jù)（如增加或刪除記錄）時，索引將自動更新。 （2）索引建立后，在查詢使用該列時，系統(tǒng)將自動使用索引進(jìn)行查詢。 （3）索引數(shù)目無限制，但索引越多，更新數(shù)據(jù)的速度越慢。對于僅用于查詢的表可多建索引，對于數(shù)據(jù)更新頻繁的表則應(yīng)少建索引。,50,3.5.4 查看與修改索引,用SSMS查看和修改索引 用Sp_helpindex存儲過程查看索引 Sp_helpindex objname = name 例3-20 查看表SC的索引。 EXEC Sp_helpindex SC,表的名稱,51,用Sp_rename存儲過程更改索引名稱 Sp_rename 數(shù)據(jù)表名.原索引名, 原索引名 例3-21

35、 更改T表中的索引TI名稱為T_Index。 EXEC Sp_rename T.TI, T_Index, index,52,3.5.5 刪除索引,用SSMS刪除索引 用DROP INDEX命令刪除索引 DROP INDEX數(shù)據(jù)表名.索引名 例3-22 刪除表SC的索引SCI。 DROP INDEX SC.SCI,不能刪除由CREATE TABLE 或ALTER TABLE命令創(chuàng)建的PRIMARY KEY或UNIQUE約束索引，也不能刪除系統(tǒng)表中的索引,53,3.6 數(shù)據(jù)查詢,3.6.1 SELECT命令的格式與基本使用 SELECT ALL|DISTINCTTOP N PERCENTWITH

36、TIES 列名AS 別名1 ，列名 AS 別名2 INTO 新表名 FROM表名1或視圖名1AS 表1別名 ，表名2或視圖名2AS 表2別名 WHERE檢索條件 GROUP BY HAVING ORDER BY ASC|DESC,投影,選取,54,查詢語句SELECT 這是最常用的SQL語句?；咀饔檬歉鶕?jù)其子句where指定的條件在特定的表內(nèi)進(jìn)行查詢，返回一個記錄集。簡潔的語法形式如下： Select All | Distinct fields_list From table_name Where Group by Order by,55,SELECT語句的各參數(shù)和從句說明 All：選擇符合

37、條件的全部記錄； Distinct：省略選擇字段中包含重復(fù)數(shù)據(jù)的記錄； Fields_list：字段名稱列表，可以來自多個表，字段名稱之間用“,” 隔開； Table_name：從其中獲取記錄的表的名稱，記錄可以來自多個表，表之間用“,”隔開； From：創(chuàng)建 一個從句，指明Fields_list中的字段來自哪些表； Where：創(chuàng)建一個從句，指定查詢查詢返回的結(jié)果應(yīng)該滿足的條件； Group By：按照選定的字段對查詢結(jié)果分組； Order By：在從句中指定按哪些字段排序，升序(asc)，降序(desc)。,56,例3-23 查詢?nèi)w學(xué)生的學(xué)號、姓名和年齡。 SELECT SNo, SN,

38、 Age FROM S 例3-24 查詢學(xué)生的全部信息。 SELECT * FROM S 例3-25 查詢選修了課程的學(xué)生號。 SELECT DISTINCT SNo FROM SC 例3-26 查詢?nèi)w學(xué)生的姓名、學(xué)號和年齡。 SELECT SN Name, SNo, Age FROM S,SELECT SN AS Name, SNo, Age,57,3.6.2 條件查詢,常用的比較運(yùn)算符：,58,比較大小 例3-27 查詢選修課程號為C1的學(xué)生的學(xué)號和成績 SELECT SNo,Score FROM SC WHERE CNo= C1 例3-28 查詢成績高于85分的學(xué)生的學(xué)號、課程號和成績

39、。 SELECT SNo,CNo,Score FROM SC WHERE Score85,59,多重條件查詢 NOT、AND、OR 用戶可以使用括號改變優(yōu)先級 例3-29 查詢選修C1或C2且分?jǐn)?shù)大于等于85分學(xué)生的學(xué)號、課程號和成績。 SELECT SNo, CNo, Score FROM SC WHERE (CNo = C1 OR CNo = C2) AND (Score = 85),高,低,60,確定范圍 例3-30 查詢工資在1000至1500元之間的教師的教師號、姓名及職稱。 SELECT TNo,TN,Prof FROM T WHERE Sal BETWEEN 1000 AND 1

40、500 例3-31 查詢工資不在1000至1500之間的教師的教師號、姓名及職稱。 SELECT TNo,TN,Prof FROM T WHERE Sal NOT BETWEEN 1000 AND 1500,WHERE Sal=1000 AND Sal=1500,61,確定集合 利用“IN”操作可以查詢屬性值屬于指定集合的元組。 例3-32 查詢選修C1或C2的學(xué)生的學(xué)號、課程號和成績。 SELECT SNo, CNo, Score FROM SC WHERE CNo IN(C1，C2) 利用“NOT IN”可以查詢指定集合外的元組。 例3-33 查詢沒有選修C1，也沒有選修C2的學(xué)生的學(xué)號、

41、課程號和成績。 SELECT SNo, CNo, Score FROM SC WHERE CNo NOT IN(C1，C2),62,部分匹配查詢 當(dāng)不知道完全精確的值時，用戶可以使用LIKE或NOT LIKE進(jìn)行部分匹配查詢（也稱模糊查詢） LIKE 例3-34 查詢所有姓張的教師的教師號和姓名。 SELECT TNo, TN FROM T WHERE TN LIKE 張% 例3-35 查詢姓名中第二個漢字是“力”的教師號和姓名。 SELECT TNo, TN FROM T WHERE TN LIKE_力%,63,SQL模糊查詢，使用like比較關(guān)鍵字，加上SQL里的通配符，請參考以下： 1、

42、LIKE Mc% 將搜索以字母 Mc 開頭的所有字符串（如 McBadden） 2、LIKE %inger 將搜索以字母 inger 結(jié)尾的所有字符串（如 Ringer、Stringer） 3、LIKE %en% 將搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden） 4、LIKE _heryl 將搜索以字母 heryl 結(jié)尾的所有六個字母的名稱（如 Cheryl、Sheryl） 5、LIKE CKarseon 將搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson） 6、LIKE M-Zinger 將搜索以字符串

43、 inger 結(jié)尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer） 7、LIKE Mc% 將搜索以字母 M 開頭，并且第二個字母不是 c 的所有名稱（如MacFeather）,64,空值查詢 某個字段沒有值稱之為具有空值（NULL） 空值不同于零和空格，它不占任何存儲空間 例3-36 查詢沒有考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號。 SELECT SNo, CNo FROM SC WHERE Score IS NULL,65,3.6.3 常用庫函數(shù)及統(tǒng)計(jì)匯總查詢,66,例3-37 求學(xué)號為S1學(xué)生的總分和平均分。 SELECT SUM(Score) AS TotalScore,

44、 AVG(Score) AS AveScore FROM SC WHERE (SNo = S1) 例3-38 求選修C1號課程的最高分、最低分及之間相差的分?jǐn)?shù)。 SELECT MAX(Score) AS MaxScore, MIN(Score) AS MinScore, MAX(Score) MIN(Score) AS Diff FROM SC WHERE (CNo = C1) 例3-40 求學(xué)校中共有多少個系。 SELECT COUNT(DISTINCT Dept) AS DeptNum FROM S,DISTINCT消去重復(fù)行,67,例3-41 統(tǒng)計(jì)有成績同學(xué)的人數(shù)。 SELECT COU

45、NT (Score) FROM SC 成績?yōu)榱愕耐瑢W(xué)他計(jì)算在內(nèi)，沒有成績（即為空值）的不計(jì)算。 例3-42 利用特殊函數(shù)COUNT(*)求計(jì)算機(jī)系學(xué)生的總數(shù)。 SELECT COUNT(*) FROM S WHERE Dept=計(jì)算機(jī),COUNT（*）用來統(tǒng)計(jì)元組的個數(shù)，不消除重復(fù)行， 不允許使用DISTINCT關(guān)鍵字。,68,3.6.4 分組查詢,GROUP BY子句可以將查詢結(jié)果按屬性列或?qū)傩粤薪M合在行的方向上進(jìn)行分組，每組在屬性列或?qū)傩粤薪M合上具有相同的值。 例3-43 查詢各個教師的教師號及其任課的門數(shù)。 SELECT TNo,COUNT(*) AS C_Num FROM TC GRO

46、UP BY TNo,GROUP BY子句按TNo的值分組，所有具有相同TNo的元 組為一組，對每一組使用函數(shù)COUNT進(jìn)行計(jì)算，統(tǒng)計(jì)出各位教 師任課的門數(shù)。,69,若在分組后還要按照一定的條件進(jìn)行篩選，則需使用HAVING子句 例3-44 查詢選修兩門以上課程的學(xué)生的學(xué)號和選課門數(shù)。 SELECT SNo, COUNT(*) AS SC_Num FROM SC GROUP BY SNo HAVING (COUNT(*) = 2),GROUP BY子句按SNo的值分組，所有具有相同SNo的元組為一 組，對每一組使用函數(shù)COUNT進(jìn)行計(jì)算，統(tǒng)計(jì)出每位學(xué)生選課的門 數(shù)。HAVING子句去掉不滿足CO

47、UNT（*）=2的組,70,3.3.5 查詢的排序,當(dāng)需要對查詢結(jié)果排序時，應(yīng)該使用ORDER BY子句，ORDER BY子句必須出現(xiàn)在其他子句之后。排序方式可以指定，DESC為降序，ASC為升序，缺省時為升序。 例3-45 查詢選修C1 的學(xué)生學(xué)號和成績，并按成績降序排列。 SELECT SNo, Score FROM SC WHERE (CNo = C1) ORDER BY Score DESC,71,例3-46 查詢選修C2、C3、C4或C5課程的學(xué)號、課程號和成績，查詢結(jié)果按學(xué)號升序排列，學(xué)號相同再按成績降序排列。 SELECT SNo, CNo, Score FROM SC WHER

48、E (CNo IN (C2, C3, C4, C5) ORDER BY SNo, Score DESC,72,例3-47 求選課在三門及以上且各門課程均及格的學(xué)生的學(xué)號及其總成績，查詢結(jié)果按總成績降序列出。 SELECT SNo, SUM(Score) AS TotalScore FROM SC WHERE (Score = 60) GROUP BY SNo HAVING (COUNT(*) = 3) ORDER BY SUM(Score) DESC,取出整個SC,篩選Score=60的元組,將選出的元組按SNo分組,篩選選課三門以上的分組,將選取結(jié)果排序,在剩下的組中提取學(xué)號和總成績,ORD

49、ER BY 2 DESC ; “2”代表查詢結(jié)果的第二列,73,3.6.6 數(shù)據(jù)表連接及連接查詢,連接查詢：一個查詢需要對多個表進(jìn)行操作 表之間的連接：連接查詢的結(jié)果集或結(jié)果表 連接字段：數(shù)據(jù)表之間的聯(lián)系是通過表的字段值來體現(xiàn)的 連接操作的目的：從多個表中查詢數(shù)據(jù) 表的連接方法 : 方法1:表之間滿足一定條件的行進(jìn)行連接時，F(xiàn)ROM子句指明進(jìn)行連接的表名，WHERE子句指明連接的列名及其連接條件 方法2:利用關(guān)鍵字JOIN進(jìn)行連接：當(dāng)將JOIN 關(guān)鍵詞放于FROM子句中時，應(yīng)有關(guān)鍵詞ON與之對應(yīng)，以表明連接的條件,74,JION的分類,75,等值連接與非等值連接 例3-48 查詢“劉偉”老師所

50、講授的課程，要求列出教師號、教師姓名和課程號。 方法1： SELECT T.TNo,TN,CNo FROM T,TC WHERE (T.TNo = TC. TNo) AND (TN=劉偉) 方法2： SELECT T.TNo, TN, CNo FROM T INNER JOIN TC ON T.TNo = TC.TNo WHERE (TN = 劉偉),連接條件 ，當(dāng)比較運(yùn)算符為“”時，稱為等值連接。其他情況為非等值連接。,引用列名TNo時要加上表名前綴，這是因?yàn)閮蓚€表中的列名相同， 必須用表名前綴來確切說明所指列屬于哪個表，以避免二義性。,76,例3-49 查詢所有選課學(xué)生的學(xué)號、姓名、選課名

51、稱及成績。 SELECT S.SNo,SN,CN,Score FROM S,C,SC WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo 例3-50 查詢每門課程的課程名、任課教師姓名及其職務(wù)、選課人數(shù)。 select CN,COUNT(SNo) as Num,TN,Prof from SC,C ,TC,T where C.CNo=SC.CNo and C.CNo=TC.CNo and TC.TNo=T.TNo group by C.CN,T.TN,Prof,77,自身連接 例3-51 查詢所有比“劉偉”工資高的教師姓名、工資和劉偉的工資。,方法1： SELECT X.TN

52、,X.Sal AS Sal_a,Y.Sal AS Sal_b FROM T AS X ,T AS Y WHERE X.SalY.Sal AND Y.TN=劉偉,方法2： SELECT X.TN, X.Sal,Y.Sal FROM T AS X INNER JOIN T AS Y ON X.SalY.Sal AND Y.TN=劉偉,方法3： SELECT R1.TN,R1.Sal, R2.Sal FROM (SELECT TN,Sal FROM S ) AS R1 INNER JOIN (SELECT Sal FROM T WHERE TN=劉偉) AS R2 ON R1.SalR2.Sal,7

53、8,例3-52 檢索所有學(xué)生姓名，年齡和選課名稱。,方法1： SELECT SN,Age,CN FROM S,C,SC WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo,方法2： SELECT R3.SNo,R3.SN,R3.Age,R4.CN FROM (SELECT SNo,SN,Age FROM S) AS R3 INNER JOIN (SELECT R2.SNo,R1.CN FROM (SELECT CNo,CN FROM C) AS R1 INNER JOIN (SELECT SNo,CNo FROM SC) AS R2 ON R1.CNo=R2.CNo) AS

54、 R4 ON R3.SNo=R4.SNo,select SN,Age,CN from S inner join SC on S.SNo =SC.SNo inner join C on SC.CNo =C.CNo,79,外連接 而在外部連接中，參與連接的表有主從之分，以主表的每行數(shù)據(jù)去匹配從表的數(shù)據(jù)列。 符合連接條件的數(shù)據(jù)將直接返回到結(jié)果集中，對那些不符合連接條件的列，將被填上NULL值后再返回到結(jié)果集中。 例3-53 查詢所有學(xué)生的學(xué)號、姓名、選課名稱及成績（沒有選課的同學(xué)的選課信息顯示為空）。 SELECT S.SNo,SN,CN,Score FROM S LEFT OUTER JOIN S

55、C ON S.SNo=SC.SNo LEFT OUTER JOIN C ON C.CNo=SC.CNo,左外部連接右外部連接,例：select S.SNo,SC.CNo from s left join SC on S.SNo =SC.SNo,80,3.6.7 子查詢,在WHERE子句中包含一個形如SELECT-FROM-WHERE的查詢塊，此查詢塊稱為子查詢或嵌套查詢。 返回一個值的子查詢 例3-54 查詢與“劉偉”老師職稱相同的教師號、姓名 SELECT TNo,TN FROM T WHERE Prof= (SELECT Prof FROM T WHERE TN= 劉偉),使用比較運(yùn)算符

56、（=, , =, =, !=）,81,返回一組值的子查詢 使用ANY 例3-55 查詢講授課程號為C5的教師姓名。 SELECT TN FROM T WHERE (TNo = ANY (SELECT TNo FROM TC WHERE CNo = C5),使用ANY或ALL,SELECT TN FROM T,TC WHERE T.TNo=TC.TNo AND TC.CNo= C5 ,IN,82,例3-56 查詢其他系中比計(jì)算機(jī)系某一教師工資高的教師的姓名和工資。 SELECT TN, Sal FROM T WHERE (Sal ANY (SELECT Sal FROM T WHERE Dept

57、 = 計(jì)算機(jī)) AND (Dept 計(jì)算機(jī)) SELECT TN, Sal FROM T WHERE Sal (SELECT MIN(Sal) FROM T WHERE Dept = 計(jì)算機(jī)) AND Dept 計(jì)算機(jī),83,使用ALL 例3-58 查詢其他系中比計(jì)算機(jī)系所有教師工資都高的教師的姓名和工資。 SELECT TN, Sal FROM T WHERE (Sal ALL (SELECT Sal FROM T WHERE Dept = 計(jì)算機(jī)) AND (Dept 計(jì)算機(jī)) 例3-59 查詢不講授課程號為C5的教師姓名。 SELECT DISTINCT TN FROM T WHERE

58、(C5 ALL (SELECT CNo FROM TC WHERE TNo = T.TNo),Sal (SELECT MAX(Sal),NOT IN,84,子查詢分為: 普通子查詢 相關(guān)子查詢 二者執(zhí)行順序不同: 普通子查詢: 首先執(zhí)行子查詢,再將子查詢的結(jié)果作為父查詢的查詢條件的值.只執(zhí)行一次. 相關(guān)子查詢: 首先選取父查詢表中的第一行記錄,內(nèi)部的子查詢利用些行中的相關(guān)屬性值進(jìn)行查詢,然后父查詢根據(jù)子查詢返回的結(jié)果判斷此行是否滿足查詢條件.如滿足,則將該行放入父查詢的結(jié)果集中. 相關(guān)子查詢的執(zhí)行次數(shù)由父查詢表的行數(shù)決定. 上例中,子查詢的查詢條件引用父查詢中表中的屬性值T.TNO,為相關(guān)子查詢,85,使用EXISTS 帶有EXISTS的子查詢不返回任何實(shí)際數(shù)據(jù)，它只得到邏輯值“真”或“假” 。 當(dāng)子查詢的的查詢結(jié)果集合為非空時，外層的