《數(shù)據(jù)庫應(yīng)用技術(shù)》PPT課件.ppt

《《數(shù)據(jù)庫應(yīng)用技術(shù)》PPT課件.ppt》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫應(yīng)用技術(shù)》PPT課件.ppt（203頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、數(shù)據(jù)庫應(yīng)用技術(shù),總復(fù)習(xí),,Sql Server數(shù)據(jù)庫中常見的數(shù)據(jù)庫對象主要包括表、存儲過程、觸發(fā)器、視圖、規(guī)則、默認(rèn)值、索引等。,第一章SQL Server 2000高級安裝,1.1 SQL Server 2000 版本信息 1.2 使用SQL Server命名實(shí)例和多實(shí)例 1.3 確定啟動服務(wù)的帳戶 1.4 選擇安全機(jī)制 1.5 SQL Server 2000的排序規(guī)則 1.6 網(wǎng)絡(luò)庫 1.7 驗(yàn)證安裝的正確性 1.8 無人值守安裝 1.9 升級SQL Server 1.10 故障排除,1.1 SQL Server 2000 版本信息,共有6個版本：企業(yè)版、企業(yè)評估板、標(biāo)準(zhǔn)版、個人版、開發(fā)版

2、、Windows CE版 每個版本支持的操作系統(tǒng),1.2 使用SQL Server命名實(shí)例和多實(shí)例,實(shí)例是關(guān)系數(shù)據(jù)庫引擎，是SQL SERVER 2000的工作單元。每一個實(shí)例都由系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫組成，擁有獨(dú)立的管理和運(yùn)行環(huán)境。 默認(rèn)實(shí)例：一臺計算機(jī)只能存在一個默認(rèn)實(shí)例 命名實(shí)例：除默認(rèn)實(shí)例以外，其他所有的實(shí)例都稱為命名實(shí)例,1.2 使用SQL Server命名實(shí)例和多實(shí)例,多實(shí)例：一臺計算機(jī)允許安裝多個SQL Server 2000實(shí)例。一臺計算機(jī)最多可以運(yùn)行16個命名實(shí)例。 實(shí)例名稱及命名規(guī)則：必須將實(shí)例名限制為 16 個字符等等（P5）。,1.3 確定啟動服務(wù)的帳戶,帳戶類型為本地

3、系統(tǒng)帳戶或域用戶帳戶。可使用同一帳戶啟動兩個服務(wù)，也可以為每個服務(wù)分別指定帳戶。,1.4 選擇安全機(jī)制,在安裝SQL Server的過程中，用戶可以選擇Windows 身份驗(yàn)證模式或者選擇混和身份驗(yàn)證模式作為安全機(jī)制，限制和驗(yàn)證用戶對SQL Server的訪問。,1.5 SQL Server 2000的排序規(guī)則,排序規(guī)則是SQL Server 對字符數(shù)據(jù)進(jìn)行存儲和排序的方式。SQL Server具有兩種類型的排序規(guī)則：Windows排序規(guī)則和SQL 排序規(guī)則。,1.6 網(wǎng)絡(luò)庫,網(wǎng)絡(luò)庫也稱作通訊協(xié)議，用于在運(yùn)行SQL Server的客戶端和服務(wù)器之間傳遞網(wǎng)絡(luò)數(shù)據(jù)包。 SQL Server默認(rèn)實(shí)例

4、網(wǎng)絡(luò)庫為命名管道和TCP/IP協(xié)議網(wǎng)絡(luò)庫,1.7 驗(yàn)證安裝的正確性,數(shù)據(jù)庫的類型：系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫。 master 數(shù)據(jù)庫 tempdb 數(shù)據(jù)庫 model 數(shù)據(jù)庫 msdb 數(shù)據(jù)庫 northwind數(shù)據(jù)庫 pubs數(shù)據(jù)庫,第二章 Transact-SQL程序設(shè)計邏輯,系統(tǒng)提供的數(shù)據(jù)類型 2.1 數(shù)據(jù)定義語言(DDL) 2.2 使用SELECT 語句查詢數(shù)據(jù) 2.3 使用Insert向表中插入數(shù)據(jù) 2.4 使用Update修改表中的數(shù)據(jù) 2.5 使用Delete刪除表中的數(shù)據(jù) 2.6 大對象數(shù)據(jù)的訪問控制 2.7 T-SQL中數(shù)據(jù)庫對象的引用,系統(tǒng)提供的數(shù)據(jù)類型,整數(shù)數(shù)據(jù)類型 int

5、、smallint 、tinyint 、bigint 浮點(diǎn)數(shù)據(jù)類型 單精度類型（real） 、雙精度類型（float） 、精確數(shù)值類型（decimal、numeric） 字符數(shù)據(jù)類型 char 、nchar 、varchar 、nvarchar,系統(tǒng)提供的數(shù)據(jù)類型,日期和時間數(shù)據(jù)類型 datetime 、smalldatetime 二進(jìn)制數(shù)據(jù)類型 binary 、varbinary 邏輯數(shù)據(jù)類型 bit數(shù)據(jù)類型占用1個字節(jié)的存儲空間，其值為0或1。如果輸入0或1以外的值，將被視為1。bit類型不能定義為NULL值。,系統(tǒng)提供的數(shù)據(jù)類型,文本和圖形數(shù)據(jù)類型 text 、ntext

6、 、image 貨幣數(shù)據(jù)類型 money 、smallmoney,2.1 數(shù)據(jù)定義語言(DDL),數(shù)據(jù)定義語言用來定義數(shù)據(jù)的結(jié)構(gòu)，如創(chuàng)建、修改或者刪除數(shù)據(jù)庫對象，常用的數(shù)據(jù)定義語言有：CREATE、ALTER、DROP等。,創(chuàng)建一張表合同表Contract，該表中含有五個字段，分別是合同號ContractID字符型數(shù)據(jù)、單位代碼 CompanyID 字符型數(shù)據(jù)，來源于公司信息表、合同名稱 ContractName 字符型數(shù)據(jù)、合同總金額 ContractVolume 數(shù)值型數(shù)據(jù)、簽定日期 SignDate 日期型數(shù)據(jù)，SQL語句如下： CREATE TABLE Contract ( Cont

7、ractID varchar (10) NOT NULL , CompanyID varchar (10) NOT NULL , ContractName varchar (30) NULL , ContractVolume numeric(18, 2) NULL, SignDate Datetime NULL ),Create Table 語句,創(chuàng)建一個臨時表MyTempQuery，用來保存當(dāng)前用戶的合同號與合同總金額 CREATE TABLE #MyTempQuery ( ContractID Varchar(10) PRIMARY KEY, ContractVolume Numeric(

8、18, 0) ),Alter Table 語句,例程2.4：為表Contract增加一個新的字段CompanyID，數(shù)據(jù)類型為Varchar，數(shù)據(jù)長度為20個字符，允許空值。SQL語句如下： ALTER TABLE Contract ADD CompanyID VARCHAR(20) NULL 例程2.5：改變表Contract 中的字段ContractVolume的數(shù)據(jù)類型 ，使其數(shù)據(jù)類型為Money類型，SQL語句如下： ALTER TABLE Contract ALTER COLUMN ContractVolume money,Drop Table 語句,例程2.6：從當(dāng)前數(shù)據(jù)庫中刪除

9、Contract 表及其數(shù)據(jù)和索引，SQL 語句如下： DROP TABLE Contract 注：DROP TABLE 不能用于除去由 FOREIGN KEY 約束引用的表。必須先除去引用的 FOREIGN KEY 約束或引用的表。除去表時，表上的規(guī)則或默認(rèn)值將解除綁定，任何與表關(guān)聯(lián)的約束或觸發(fā)器將自動除去。,Drop Table 語句,例程2.6：從當(dāng)前數(shù)據(jù)庫中刪除 Contract 表及其數(shù)據(jù)和索引，SQL 語句如下： DROP TABLE Contract 注：DROP TABLE 不能用于除去由 FOREIGN KEY 約束引用的表。必須先除去引用的 FOREIGN KEY 約束或引

10、用的表。除去表時，表上的規(guī)則或默認(rèn)值將解除綁定，任何與表關(guān)聯(lián)的約束或觸發(fā)器將自動除去。,SELECT 子句,例程2.7：要查詢Contract表中前50條記錄，要求返回兩列數(shù)據(jù)：合同名稱和合同總金額，SQL語句如下： Select TOP 50 ContractName as 合同名稱, ContractVolume as 合同總金額 From Contract 例程2.8：查詢與多少個單位簽了合同，需要查詢Contract表中的CompanyID列，按要求不能顯示重復(fù)的列，需要使用DISTINCT 關(guān)鍵字，SQL語句如下：SELECT DISTINCT CompanyID FROM C

11、ontract,FROM 子句,例程2.9：查詢每個合同的合同明細(xì)金額之和，要求返回列合同號與合同明細(xì)金額之和，合同表與合同明細(xì)表是主子表的關(guān)系。合同明細(xì)表有三個字段分別是：ContractID、ContractDetailID、Volume。 SELECT a.ContractID ,sum(Volume) FROM Contract a LEFT JOIN ContractDetail b ON a.ContractID=b.ContractID GROUP BY a.ContractID,WHERE 子句,例程2.10：查詢合同表中所有簽訂日期在2002年1月1日以后的記錄，SQL語句如

12、下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract WHERE SignDate2002-01-01,WHERE 子句,例程2.11：查詢與“北京怡神工貿(mào)公司”簽定的合同總金額大于100萬的所有合同。此查詢需要公司信息表，該表有兩個字段：CompanyID、CompanyName，與合同表是主子表的關(guān)系，每個單位對應(yīng)多個合同。SQL語句如下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract,Company WH

13、ERE Contract.CompanyID=Company.CompanyID AND CompanyName= 北京怡神工貿(mào)公司 AND ContractVolume1000000,WHERE 子句,例程2.12：查詢總金額小于10萬的合同，或者簽定日期在2002年5月1日與2002年8月1日之間的合同。因?yàn)椴樵儣l件限定詞AND 和OR 的執(zhí)行順序是按先后順序執(zhí)行，因此需要先執(zhí)行的部分應(yīng)加括號。SQL語句如下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract WHERE ContractVolume2

14、002-05-01 AND SignDate<2002-08-01),GROUP BY 子句,例程2.13：查詢與各公司簽定的所有合同的總金額之和，在此需要聚合函數(shù)SUM，SQL語句如下： SELECT CompanyName,SUM(ContractVolume) FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,GROUP BY 子句,例程2.14：查詢各合同的合同明細(xì)金額的最小值，需要用到函數(shù)MIN，SQL語句如下： SELECT ContractName,MIN(V

15、olume) FROM Contract,ContractDetail WHERE Contract.ContractID=ContractDetail.ContractID GROUP BY ContractName,HAVING 子句,例程2.16：查詢每個單位每個合同明細(xì)金額之和，要求該合同為2002年1月1日之后簽名冊定的合同，SQL語句如下： SELECT CompanyName,ContractName,SUM(Volume) FROM Contract,ContractDetail,Company WHERE Contract.ContractID=ContractDetail.

16、ContractID AND Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,ContractName,SignDate HAVING SignDate2002-01-01,ORDER BY 子句,例程2.17：按合同總金額從小到大的順序，對于所有合同排序輸出。SQL語句如下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract ORDER BY ContractVolume ASC,ORDER BY 子句,例程2.18：查詢每個單位每個合同

17、明細(xì)金額之和，要求該合同為2002年1月1日之后簽名冊定的合同，并且按單位名稱和簽定日期升序排序，SQL語句如下： SELECT CompanyName,ContractName,SUM(Volume) FROM Contract,ContractDetail,Company WHERE Contract.ContractID=ContractDetail.ContractID AND Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,ContractName,SignDate HAVING SignDate2002-01-01

18、 ORDER BY CompanyName,SignDate,NULL關(guān)鍵字,例程2.19：檢查合同表中SignDate為NULL的記錄，SQL語句如下： SELECT * FROM Contract WHERE SignDate IS NULL,LIKE關(guān)鍵字,例程2.20：查詢所有公司名稱中含有“北京”的合同，可以使用%通配符，SQL語句下如： SELECT ContractID,ContractName,ContractVolume FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID AND CompanyNa

19、me LIKE %北京%,,例程2.21：查詢所有合同號以“01”結(jié)尾的合同，并且該合同號為四位字符長度，可以使用_通配符，_通配符代表一個字符位置，SQL語句下如： SELECT ContractID,ContractName,ContractVolume FROM Contract WHERE ContractID LIKE __01 例程2.22：查詢所有合同號以“01”結(jié)尾的合同，并且該合同號為三位字符長度，且第一位介于0-9之間，SQL語句下如： SELECT ContractID,ContractName,ContractVolume FROM Contract WHERE Con

20、tractID LIKE 0-901,CUBE 關(guān)鍵字,例程2.23：查詢與各公司簽定的所有合同的總金額之和，并且要顯示匯總行，SQL語句如下： SELECT CompanyName,SUM(ContractVolume) FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName WITH CUBE,CASE關(guān)鍵字,例程2.24：查詢所有公司的合同，如果合同總金額之和小于10000元，則顯示該公司為“小公司”，如果大于10000則顯示為“大公司”，使用CASE 關(guān)鍵字，SQL語句如

21、下： SELECT CompanyName,公司規(guī)模= CASE WHEN SUM(ContractVolume)10000 THEN 大公司 WHEN SUM(ContractVolume)<=10000 THEN 小公司 END FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,IN關(guān)鍵字,例程2.25：分別用OR 關(guān)鍵字和IN關(guān)鍵字實(shí)現(xiàn)查詢，要求查出所有合同號為“101”或“102”的合同。SQL語句如下： SELECT ContractID,ContractName

22、,ContractVolume FROM Contract WHERE ContractID IN(101 ,102) 例程2.26：用IN關(guān)鍵字實(shí)現(xiàn)查詢，要求查出所有合同號不為“101”或“102”的合同。SQL語句如下： SELECT ContractID,ContractName,ContractVolume FROM Contract WHERE ContractID NOT IN （101 ,102）,2.3 使用Insert向表中插入數(shù)據(jù),例程2.29：向公司信息表中添加一條記錄，單位代碼為“0001”，單位名稱為“北京怡神工貿(mào)公司”，SQL 語句如下： INSERT INTO C

23、ompany(CompanyID,CompanyName) VALUES (0001, 北京怡神工貿(mào)公司) 例程2.30：向合同表中添加一條記錄，合同號為“1001”，合同名稱為“2003年1001號合同”，單位代碼為“0001”，合同總金額為10萬元，簽定日期為2003年1月1日，SQL語句如下： INSERT INTO Contract (ContractID,ContractName,CompanyID,ContractVolume,SignDate) VALUES (1001, 2003年1001號合同,0001,100000,2003-01-01),使用 InsertSelect 語

24、句,例程2.31：創(chuàng)建一個臨時的合同表，把合同表中2002年1月1日以后簽定的合同記錄添加到臨時合同表中，SQL語句如下： 創(chuàng)建臨時表 CREATE TABLE #TempContract( ContractID varchar (10) NOT NULL , CompanyID varchar (10) NOT NULL , ContractName varchar (30) NULL , ContractVolume numeric(18, 2) NULL, SignDate Datetime NULL) 向臨時表中插入符合條件的記錄 INSERT INTO #TempContract (

25、ContractID,CompanyID,ContractName,ContractVolume,SignDate) SELECT ContractID,CompanyID,ContractName,ContractVolume,SignDate FROM Contract WHERE SignDate2002-01-01,使用 Select Into 語句,例程2.32：創(chuàng)建一個新的表，把合同表中合同總金額大于10萬元的合同記錄添加到新表中，新表名為NewContract，SQL語句如下： SELECT ContractID,CompanyID,ContractName,ContractVo

26、lume, SignDate INTO NewContract FROM Contract WHERE ContractVolume100000,2.4 使用Update修改表中的數(shù)據(jù),例程2.33：更新合同表中所有單位代碼為“0001”的合同記錄，使其單位代碼為“0002”，SQL語句如下： UPDATE Contract SET CompanyID=0002 WHERE CompanyID=0001,基于其他表更新數(shù)據(jù)行,例程2.34：把合同明細(xì)表中金額添加到合同表中的合同總金額中，以反應(yīng)合同表中最新的合同明細(xì)金額，SQL語句下： UPDATE Contract SET Contract

27、Volume=Contract.ContractVolume+ContractDetail.Volume FROM Contract,ContractDetail WHERE Contract.ContractID=ContractDetail.ContractID,使用子查詢更新指定的行,例程2.35：更新合同表中合同總金額字段，條件是如果在合同明細(xì)中該合同沒有記錄，則使其合同總金額為0，SQL語句如下： UPDATE Contract SET ContractVolume=0 WHERE ContractID NOT IN (SELECT ContractID FROM ContractD

28、etail),2.5 使用Delete刪除表中的數(shù)據(jù),例程2.36：用DELETE刪除表的所有行，刪除合同明細(xì)表中的所有記錄，SQL語句如下： DELETE FROM ContractDetail 例程2.37：用DELETE刪除表的指定行，刪除合同表中所有合同總金額為0并且簽定日期在2002年5月1日之前的合同記錄，SQL語句如下： DELETE FROM Contract WHERE ContractVolume=0 AND SignDate<2002-05-01,2.6 大對象數(shù)據(jù)的訪問控制,例程2.38：在合同表中增加一個字段：合同備注 ALTER TABLE

29、Contract Add ContractMemo nText NULL 現(xiàn)在查詢合同備注的前100個字符，SQL語句如下： SET TEXTSIZE 100 SELECT (ContractMemo) FROM Contract 例程2.39：讀取合同表中合同備注字段的第11到31個字符 DECLARE temp varbinary(16) SELECT temp = TEXTPTR(Contract Memo) FROM Contract READTEXT Contract.ContractMemo temp 10 30,2.7 T-SQL中數(shù)據(jù)庫對象的引用,在T-SQL中，數(shù)據(jù)對象的命名

30、規(guī)則是：Server.Database.Owner.Object，前三個限定是可以省略的。省略了某一個或多個對象的限定稱為部分限定。,完全限定,格式：Server.Database.Owner.Object SELECT * FROM RED.Selling.Dbo.Contract,部分限定,省略Server: 用戶當(dāng)前連接的Server SELECT * FROM Selling.dbo.Contract 省略Database: SELECT * FROM dbo.Contract 省略O(shè)wner: SELECT * FROM Contract,第三章 面向復(fù)雜應(yīng)用的Transact-SQ

31、L,3.1 使用系統(tǒng)內(nèi)建函數(shù) 3.2 子查詢 3.3 聯(lián)合查詢 3.4 其他復(fù)雜應(yīng)用 3.5 查詢語句的性能優(yōu)化,3.1 使用系統(tǒng)內(nèi)建函數(shù),3.1.1 日期和時間函數(shù) 3.1.2 聚合函數(shù) 3.1.3 字符串函數(shù) 3.1.4 系統(tǒng)統(tǒng)計函數(shù),日期和時間函數(shù),DATEADD：在向指定日期加上一段時間的基礎(chǔ)上，返回新的日期值； DATEDIFF：返回跨兩個指定日期的日期和時間邊界數(shù)； DATENAME：返回代表指定日期的指定日期部分的字符串； DATEPART：返回代表指定日期的指定日期部分的整數(shù)； DAY：返回代表指定日期的天的日期部分的整數(shù)； GETDATE：回當(dāng)前系統(tǒng)日期和時間； GETUTC

32、DATE：返回表示當(dāng)前 UTC 時間（世界時間坐標(biāo)或格林尼治標(biāo)準(zhǔn)時間）的值； MONTH：返回代表指定日期月份的整數(shù)； YEAR：返回表示指定日期中的年份的整數(shù)。,日期和時間函數(shù),列出所有合同的簽定日期，要求在簽定日期上加5天顯示，列出合同號，簽定日期，SQL語句如下： SELECT ContractID, DATEADD(day, 5, SignDate) FROM Contract 列出所有合同簽定日期與現(xiàn)在日期所差的天數(shù)，列出合同號、簽定日期，SQL語句如下： SELECT ContractID, DATEDIFF(day, signdate, getdate()) FROM C

33、ontract,聚合函數(shù),AVG：取平均值； MAX：取最大值； MIN：取最小值； SUM：求和函數(shù)； COUNT：返回組中項(xiàng)目的數(shù)量。,聚合函數(shù),列出所有公司的所有合同的合同總金額的平均值，要求列出公司名稱，合同平均值，SQL語句如下： SELECT CompanyName,AVG(ContractVolume) as 合同平均值 FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,聚合函數(shù),列出各合同的合同明細(xì)的數(shù)量，即一個合同有幾條合同明細(xì)記錄，顯示合同號，明細(xì)數(shù)量，

34、SQL語句如下： SELECT ContractID,COUNT(ContractID) FROM ContractDetail GROUP BY ContractID,字符串函數(shù),ASCII：返回字符表達(dá)式最左端字符的 ASCII 代碼值； CHAR：將ASCII 代碼轉(zhuǎn)換為字符的字符串函數(shù)； SPACE：返回由重復(fù)的空格組成的字符串； REPLACE：用第三個表達(dá)式替換第一個字符串表達(dá)式中出現(xiàn)的所有第二個給定字符串表達(dá)式； STR：由數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換來的字符數(shù)據(jù)； LEFT：返回從字符串左邊開始指定個數(shù)的字符； SUBSTRING：返回字符串表達(dá)式的一部分；,字符串函數(shù),LEN：返回給定字符

35、串表達(dá)式的字符（而不是字節(jié)）個數(shù)，其中不包含尾隨空格； REVERSE：返回字符表達(dá)式的反轉(zhuǎn)； LOWER：將大寫字符數(shù)據(jù)轉(zhuǎn)換為小寫字符數(shù)據(jù)后返回字符表達(dá)式； RIGHT：返回字符串中從右邊開始指定個數(shù)的字符； UPPER：返回將小寫字符數(shù)據(jù)轉(zhuǎn)換為大寫的字符表達(dá)式； LTRIM：刪除起始空格后返回字符表達(dá)式； RTRIM：截斷所有尾隨空格后返回一個字符串 。,字符串函數(shù),用“有限公司”替換公司信息表中公司名稱字段中的“公司”，SQL語句如下： UPDATE Company SET CompanyName=REPLACE(CompanyName,公司,有限公司),字符串函數(shù),返回公司信息表中

36、公司名稱的字符長度，顯示列，公司名稱、字符長度，SQL語句如下： SELECT CompanyName,LEN(CompanyName) as 字符長度 FROM Company,字符串函數(shù),把合同表中的合同號的小寫字母全部變?yōu)榇髮懽帜革@示，要求顯示列：合同號、合同名稱，SQL語句如下： SELECT UPPER(ContractID),ContractName FROM Contract,系統(tǒng)統(tǒng)計函數(shù),CONNECTIONS：返回自上次啟動SQL Server 以來連接或試圖連接的次數(shù)； CPU_BUSY：返回自上次啟動 SQL Server以來 CPU 的工作時間，單位為毫秒（基于系統(tǒng)計時

37、器的分辨率）； TIMETICKS：返回一刻度的微秒數(shù)； IDLE：返回 SQL Server 自上次啟動后閑置的時間，單位為毫秒（基于系統(tǒng)計時器的分辨率）；,系統(tǒng)統(tǒng)計函數(shù),TOTAL_ERRORS：返回SQL Server自上次啟動后，所遇到的磁盤讀/寫錯誤數(shù)； TOTAL_READ：回SQL Server 自上次啟動后讀取磁盤（不是讀取高速緩存）的次數(shù)；、 TOTAL_WRITE ：返回SQL Server自上次啟動后寫入磁盤的次數(shù)。,系統(tǒng)統(tǒng)計函數(shù),顯示自SQL SERVER啟動后到當(dāng)前日期和時間為止的總的磁盤讀寫次數(shù)（不是讀取高速緩存），SQL語句如下： SELECT TOTAL_REA

38、D AS Reads, TOTAL_WRITE AS Writes, GETDATE() AS As of,系統(tǒng)統(tǒng)計函數(shù),顯示自SQL SERVER啟動后到當(dāng)前日期和時間為止 SQL Server CPU 的工作時間，SQL語句如下： SELECT CPU_BUSY AS CPU ms, GETDATE() AS As of,3.2 子查詢,在SQL語言中，當(dāng)一個查詢語句嵌套在另一個查詢的查詢條件之中時，稱為子查詢。使用子查詢可以實(shí)現(xiàn)一些比較復(fù)雜的查詢。,帶有IN關(guān)鍵字的子查詢,查詢所有在合同明細(xì)表中，明細(xì)金額為20000元的合同，顯示列：合同號、合同名稱，SQL語句如下： SELECT C

39、ontractID,ContractName FROM Contract WHERE ContractID IN (SELECT ContractID FROM ContractDetail WHERE Volume=20000),帶有比較運(yùn)算符的子查詢,查詢出所有合同總金額不等于合同明細(xì)表中金額之和的合同，顯示合同號、合同名稱，該查詢可以列出所有合同總金額沒有實(shí)時反應(yīng)合同明細(xì)金額之和的合同，SQL語句如下： SELECT ContractID,ContractName FROM Contract WHERE ContractVolume (SELECT SUM(Volume) FROM Co

40、ntractDetail),帶有ANY或ALL關(guān)鍵字的子查詢,查詢出所有合同總金額大于合同明細(xì)表中任何一個明細(xì)金額的合同，顯示合同號、合同名稱，SQL語句如下： SELECT ContractID,ContractName FROM Contract WHERE ContractVolume any (SELECT Volume FROM ContractDetail),帶有EXISTS關(guān)鍵字的子查詢,查詢所有合同明細(xì)中，明細(xì)金額中有一條記錄是10000元的合同，列出合同號、合同名稱，SQL語句如下： SELECT ContractID,ContractName FROM Contract W

41、HERE exists (SELECT Volume FROM ContractDetail WHERE Volume=10000 AND ContractID=Contract.ContractID),子查詢的限制（P39）,查詢所有合同明細(xì)中，明細(xì)金額中有一條記錄是10000元的合同，可以用如下SQL語句實(shí)現(xiàn)： SELECT Contract.ContractID,ContractName FROM Contract,ContractDetail WHERE Contract.CompanyID=Company.CompanyID AND Volume=10000,3.3

42、聯(lián)合查詢,3.3.1 使用UNION 創(chuàng)建聯(lián)合查詢 3.3.2 聯(lián)合查詢的關(guān)鍵字ALL 3.3.3 使用INNER JOIN 3.3.4 使用LEFT JOIN 和RIGHT JOIN 建立連接 3.3.5 使用交叉連接（CROSS JOIN）,3.3.1 使用UNION 創(chuàng)建聯(lián)合查詢,查詢在2002年5月1日簽定的合同以及在合同總金額大于10000元的合同，SQL語句如下： SELECT * FROM Contract WHERE SignDate=2002-05-01 UNION SELECT * FROM Contract WHRE ContractVolume10000,3.3.1 使

43、用UNION 創(chuàng)建聯(lián)合查詢,查詢在與“北京怡神工貿(mào)公司”簽定的合同以及在合同明細(xì)金額中有一條記錄是10000元的合同，顯示公司名稱、合同號，SQL語句如下： SELECT CompanyName,ContractID FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID AND CompanyName= 北京怡神工貿(mào)公司 UNION SELECT CompanyName,Contract.ContractID FROM Contract,Company,ContractDetail WHERE Contract.Com

44、panyID=Company.CompanyID AND Contract.ContractID=ContractDetail.ContractID AND Volume=10000,3.3.2 聯(lián)合查詢的關(guān)鍵字ALL,查詢在2002年5月1日簽定的合同以及在合同總金額大于10000元的合同，如果使用了ALL關(guān)鍵字，SQL語句如下： SELECT * FROM Contract WHERE SignDate=2002-05-01 UNION ALL SELECT * FROM Contract WHRE ContractVolume10000,3.3.3 使用INNER JOIN,查詢在

45、合同表中存在的公司的基本信息，為此需要建立合同表與公司信息表的連接，SQL語句如下： SELECT Company.CompanyID,CompanyName FROM Company INNER JOIN Contract ON Company.CompanyID=Contract.CompanyID,3.3.4 使用LEFT JOIN 和RIGHT JOIN 建立連接,查詢所有公司的合同簽定總額，不管該公司有沒有簽定合同，分別使用LEFT JOIN 和ROGHT JOIN ，SQL語句如下 用LEFT JOIN SELECT CompanyName,SUM(ContractVolume)

46、FROM Company LEFT JOIN Contract ON Company.CompanyID=Contract.CompanyID GROUP BY CompanyName 用RIGHT JOIN SELECT CompanyName,SUM(ContractVolume) FROM Contract RIGHT JOIN COMPANY ON Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,3.3.5 使用交叉連接（CROSS JOIN）,現(xiàn)有部門表（Depart）與職員表(Employee)，現(xiàn)在要生成一張新的

47、表NewTable，要求在新表中包括每個部門的每個職員，部門表結(jié)構(gòu)：Depart(DepartID,DepartName)，職員表結(jié)構(gòu)：Employee(EmployeeID,EmployeeName)，SQL語句如下： SELECT DepartName,EmployeeName INTO NewTable FROM Depart CROSS JOIN Employee,3.4 其他復(fù)雜應(yīng)用,3.4.1 使用Compute（By）子句 3.4.2 交叉表查詢,3.4.1 使用Compute（By）子句,COMPUTE BY 子句使您得以用同一 SELECT 語句既查看明細(xì)行，又查看匯總行。可

48、以計算子組的匯總值，也可以計算整個結(jié)果集的匯總值。這對于需要是顯示查詢的總計與小計非常有用。COMPUTE 所生成的匯總值在查詢結(jié)果中顯示為分離的結(jié)果。,3.4.2 交叉表查詢,以合同表為例，查詢各公司在本年的付款金額，要求每公司用一行記錄顯示，每個月用一列顯示，SQL語句如下： SELECT Company.CompanyName as 公司名稱, SUM(CASE WHEN MONTH(PaymentDate)=1 THEN ContractPayment.Volume ELSE 0 END) as 1月, SUM(CASE WHEN MONTH(PaymentDate)=2 THEN C

49、ontractPayment.Volume ELSE 0 END) as 2月, SUM(CASE WHEN MONTH(PaymentDate)=3 THEN ContractPayment.Volume ELSE 0 END) as 3月, SUM(CASE WHEN MONTH(PaymentDate)=4 THEN ContractPayment.Volume ELSE 0 END) as 4月, SUM(CASE WHEN MONTH(PaymentDate)=5 THEN ContractPayment.Volume ELSE 0 END) as 5月, SUM(CASE WHEN

50、 MONTH(PaymentDate)=6 THEN ContractPayment.Volume ELSE 0 END) as 6月, SUM(CASE WHEN MONTH(PaymentDate)=7 THEN ContractPayment.Volume ELSE 0 END) as 7月, SUM(CASE WHEN MONTH(PaymentDate)=8 THEN ContractPayment.Volume ELSE 0 END) as 8月, SUM(CASE WHEN MONTH(PaymentDate)=9 THEN ContractPayment.Volume ELSE

51、 0 END) as 9月, SUM(CASE WHEN MONTH(PaymentDate)=10 THEN ContractPayment.Volume ELSE 0 END) as 10月, SUM(CASE WHEN MONTH(PaymentDate)=11 THEN ContractPayment.Volume ELSE 0 END) as 11月, SUM(CASE WHEN MONTH(PaymentDate)=12 THEN ContractPayment.Volume ELSE 0 END) as 12月 FROM Company LEFT JOIN Contract ON

52、 Company.CompanyID=Contract.CompanyID LEFT JOIN ContractDetail ON Contract.ContractID=ContractDetail.ContractID LEFT JOIN ContractPayment ON ContractDetail.ContractDetailID=ContractPayment.ContractDetailID WHERE YEAR(PaymentDate)=2002 GROUP BY CompanyName,3.5 查詢語句的性能優(yōu)化,當(dāng)數(shù)據(jù)量不是很大的時候，SQL語句的優(yōu)劣基本無法比較，但是當(dāng)

53、數(shù)據(jù)達(dá)到海量以后，劣質(zhì)的SQL語句與優(yōu)質(zhì)的SQL語句的執(zhí)行效率可能會相差幾十倍甚至幾百倍。因此提高SQL語句的執(zhí)行效率，也就是提高了應(yīng)用程序的性能。當(dāng)然，只有在實(shí)際應(yīng)用中才能分出SQL語句的優(yōu)劣，不過還是有一些優(yōu)化經(jīng)驗(yàn)，它可以使我們避免在新系統(tǒng)犯低級的錯誤。,3.5 查詢語句的性能優(yōu)化,3.5.1 通配符%的使用 3.5.2 Order by語句 3.5.3 盡量不用NOT 3.5.4 盡量不要連接列 3.5.5 表連接需要注意的地方,第四章 視圖的靈活應(yīng)用,4.1 視圖的用途和需要 4.2 創(chuàng)建和管理視圖 4.3 利用視圖簡化查詢操作 4.4 更新視圖中的數(shù)據(jù) 4.5 加密視圖 4.6 利用

54、視圖加強(qiáng)數(shù)據(jù)安全,4.1 視圖的用途和需要,視圖作為一種基本的數(shù)據(jù)庫對象，是查詢一個表或多個表的一種另方法，通過把預(yù)先定義的查詢存儲在數(shù)據(jù)庫中，然后就可以在查詢語句中調(diào)用它。 視圖是虛擬的表，它只包含表的一部分。,4.2 創(chuàng)建和管理視圖,4.2.1 定義視圖 4.2.2 修改視圖定義 4.2.3 刪除視圖,4.2.1 定義視圖,例程4.1：創(chuàng)建一個視圖，該視圖用于顯示2002年1月1日以后簽定的所有合同。 CREATE VIEW View_Contract1 AS SELECT * FROM Contract WHERE SignDate2002-01-01 例程4.2

55、：創(chuàng)建一個視圖，用于顯示各單位簽定的所有合同。 CREATE VIEW View_Contract2 AS SELECT CompanyName,ContractID,ContractName FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID,4.2.1 定義視圖,例程4.3：創(chuàng)建一個視圖，用于顯示各單位的付款總額。 CREATE VIEW View_Contract 3 AS SELECT CompanyName,SUM(ContractPayment.Volume) as T

56、otalPayment FROM Company LEFT JOIN Contract ON Contract.CompanyID=Company.CompanyID LEFT JOIN ContractDetail ON Contract.ContractID=ContractDetail.ContractID LEFT JOIN ContractPayment ON ContractDetail.ContractDetailID=ContractPayment.ContractDetailID GROUP BY CompanyName,4.2.2

57、修改視圖定義,例如要更新上一節(jié)中例程一中的創(chuàng)建的視圖View_Contract1，使其顯示所有在2002年1月份簽定的合同，用如下的SQL語句： ALTER VIEW View_Contract1 AS SELECT * FROM Contract WHERE SignDate=2002-01-01 AND SignDate<=2002-01-31,4.2.3 刪除視圖,用DROP VIEW從當(dāng)前數(shù)據(jù)庫中刪除視圖。刪除視圖時，將從 sysobjects、syscolumns、syscomments、sysdepends 和 sysprotects 系統(tǒng)表中刪除視圖的定義及其它有關(guān)視圖的信息。

58、例程4.4：刪除視圖View_Contract1 DROP VIEW View_Contract1,4.3 利用視圖簡化查詢操作,4.3.1 查詢視圖 4.3.2 使用視圖關(guān)聯(lián)多張表 4.3.3 使用視圖提供聚合值,4.3.1 查詢視圖,視圖定義后，用戶就可以象對基本表進(jìn)行查詢一樣對視圖進(jìn)行查詢了。 例程4.5：創(chuàng)建一個視圖，用于顯示各單位簽定的所有合同，并用SQL語句查詢視圖。 創(chuàng)建視圖(如果該視圖已存在，先刪除它) IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = View_Con

59、tract ) DROP VIEW View_Contract CREATE VIEW View_Contract AS SELECT CompanyName,ContractID,ContractName FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID 查詢視圖 SELECT CompanyName,ContractID,ContractName FROM View_Contract,4.3.2 使用視圖關(guān)聯(lián)多張表,視圖可以和其他基本表一起使用，實(shí)現(xiàn)連接查詢或嵌套查詢。 例程4.6：先創(chuàng)建一個視圖，該視圖存放各

60、公司各合同情況，然后通過該視與合同明細(xì)表聯(lián)接，查詢各公司合同明細(xì)情況。 建立視圖 CREATE VIEW View_Contract AS SELECT CompanyName,ContractID FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID 通過視圖查詢： SELECT CompanyName,View_Contract.ContractID,ContractDetailID,ContractDetail.Volume FROM View_Contract,ContractDetail WHERE View

61、_Contract.ContractID=ContractDetail.ContractID,4.3.3 使用視圖提供聚合值,可以使用視圖來提供聚合值，這樣可以簡化查詢語言，建議對于常用的聚合值都采用預(yù)先定義視圖的方式，以此降低SQL語句的實(shí)現(xiàn)難度。 例程4.7：創(chuàng)建一個視圖，用于保存各合同的付款情況，用該視圖與公司信息表聯(lián)接，可以查詢各公司合同付款情況。 建立視圖 CREATE VIEW View_Contract AS SELECT Contract.ContractID,CompanyID, SUM(ContractPayment.Volume) as TotalPayment FROM

62、 Contract LEFT JOIN ContractDetail ON Contract.ContractID=ContractDetail.ContractID LEFT JOIN ContractPayment ON ContractDetail.ContractDetailID=ContractPayment.ContractDetailID GROUP BY Contract.ContractID,CompanyID 查詢各公司的合同付款情況 SELECT CompanyName,SUM(TotalPayment) FROM Company,View_Contract WHERE

63、Company.CompanyID=View_Contract.CompanyID GROUP BY CompanyName,4.4 更新視圖中的數(shù)據(jù),更新視圖包括插入（INSERT）、刪除（DELETE）和修改（UPDATE）三類操作。由于視圖是不實(shí)際存儲數(shù)據(jù)的虛表，因此對視圖的更新，最終要轉(zhuǎn)換為對基本表的更新。,4.4 更新視圖中的數(shù)據(jù),例程4.8：創(chuàng)建一個公司信息視圖，并向其中添加一條記錄：公司代碼0001、公司名稱：上海日化三廠。 創(chuàng)建視圖 CREATE VIEW View_Company AS SELECT * FROM Company 添加數(shù)據(jù) INSERT INTO View_C

64、ompany(CompanyID,CompanyName) VALUES (0001,上海日化三廠),4.4 更新視圖中的數(shù)據(jù),例程4.9：更新視圖View_Company中的數(shù)據(jù)，重新設(shè)置公司名稱為“上海日化三廠”的公司代碼為“8888”。 UPDATE View_Company SET CompanyID=8888 WHERE CompanyName=上海日化三廠,4.4 更新視圖中的數(shù)據(jù),例程4.10：刪除視圖View_Company中，公司名稱為“上海日化三廠”的記錄。 DELETE FROM View_Company WHERE CompanyName=上海日化三廠,4.5 加密視圖

65、,例如創(chuàng)建一個加密的視圖：View_Company，SQL語句如下： CREATE VIEW View_Company WITH ENCRYPTION AS SELECT * FROM Company 對視圖加密后，因?yàn)樵僖部床坏揭晥D定義，所以永遠(yuǎn)不能再對視圖進(jìn)行修改。如果需要修改加密視圖，則必須刪除該視圖并重新創(chuàng)建一個。,4.6 利用視圖加強(qiáng)數(shù)據(jù)安全,4.6.1 限制用戶的視線 4.6.2 視圖權(quán)限管理 4.6.3 調(diào)用鏈問題初探,第五章 觸發(fā)器與存儲過程,變量 5.1 SQL SERVER編程邏輯 5.2 存儲過程的創(chuàng)建與管理 5.3 觸發(fā)器的創(chuàng)建與管理 5.4 綜合實(shí)例,變量,局部

66、變量 Declare Variable_name Datatype , Variable_name Datatype 其中Variable_name 是局部變量的名字，它必須以開始，遵循SQL Server 2000的標(biāo)識符和對象的命名規(guī)范； Datatype可以是系統(tǒng)數(shù)據(jù)類型，也可以是用戶自定義數(shù)據(jù)類型。 例：declare s1 char(20) set s1=Im a student,變量,全局變量 是SQL Server 2000系統(tǒng)提供并賦值的變量，用戶不能建立全局變量，也不能用SET語句去修改全局變量的值，它以開頭，大部分全局變量的值是報告SQL Server 2000系統(tǒng)啟動后發(fā)生的活動，通常將該值賦給局部變量處理。,5.1 SQL SERVER編程邏輯,5.1.1 流程控制語句 5.1.2 游標(biāo)的使用,5.1.1 流程控制語句,IF...ELSE條件分支語句 例程5.1：如果合同號為“101”的合同總金額大于10000元，則顯示“該合同金額超過1萬元”，否則顯示“該合同金額不足1萬元”。 IF (SELECT ContractVolume FRO