SQL Server 第7次課.ppt

1、回顧,維護數(shù)據(jù)庫空間的方法有哪些？ 分離和附加數(shù)據(jù)庫是什么意思? 為什么要有數(shù)據(jù)類型的概念？ 存儲0到1000之間的數(shù)值，用bigInt和smallint有什么不同嗎？ Decimal（P，s）中的p和s表示什么意思？Decimal(2,2)? Cast（expression as data_type）功能是什么？2009-1-1是什么數(shù)據(jù)類型？,練習(xí),1、下述正確的語句是_。 A、money小數(shù)點后保留4位，而smallmoney小數(shù)點后保留2位。 B、money的整數(shù)部分和smallmoney的整數(shù)部分范圍一致。 C、money的小數(shù)位數(shù)和smallmoney的小數(shù)位數(shù)一致。 D、mone

2、y類型可以有貨幣符號，而smallmoney類型不可以。,練習(xí),2 “圖書”表中價格列是一個定點小數(shù)，小數(shù)點前3位，小數(shù)點后保留1位，正確的類型是_。 A. numeric(3,1) B. numeric(4,1) C. numeric(5,1) D. float,練習(xí),3Money數(shù)據(jù)類型和smallmoney數(shù)據(jù)類型的區(qū)別是_。 A. Money類型保留到小數(shù)位數(shù)4位，而smallmoney保留到小數(shù)位數(shù)2位 B. Money類型保留到小數(shù)位數(shù)2位，而smallmoney保留到小數(shù)位數(shù)4位 C. 存放的整數(shù)位數(shù)相同，小數(shù)位數(shù)不同 D. 存放的整數(shù)位數(shù)不同，小數(shù)位數(shù)相同,練習(xí),4用于存放定期

3、執(zhí)行任務(wù)的數(shù)據(jù)庫是 _。 A、master B、 model C、 msdb D. tempdb,練習(xí),5、下述哪個不是SQL語言的特征_。 A. 一體化語言 B. 過程化語言 C. 非過程語言 D. 簡潔,練習(xí),7創(chuàng)建完數(shù)據(jù)庫后 _ 。 A可以修改數(shù)據(jù)文件的邏輯文件名 B可以修改數(shù)據(jù)文件的物理文件名 C可以修改數(shù)據(jù)文件的初始大小 D可以修改數(shù)據(jù)文件的增長方式,練習(xí),8SmallDatetime類型精確到哪個時間單位（）。 A、小時 B、分鐘 C、秒 D、其他,練習(xí),）用圖形化工具建立滿足如下要求的數(shù)據(jù)庫： （1）數(shù)據(jù)庫名字為：abc； （2）此數(shù)據(jù)庫包含兩個數(shù)據(jù)文件，其中主數(shù)據(jù)文件的邏輯文件

4、名為：Data1,物理文件名為Data1_data.mdf，初始大小為3MB，存放在新建目錄下，增長方式為自動增長，每次增加1MB，最多增長到10MB。輔助數(shù)據(jù)文件的邏輯文件名為：Data2，物理文件名為Data2_data.ndf，初始大小為2MB，同主數(shù)據(jù)文件存放在相同的位置，不自動增長。 （3）日志文件的邏輯文件名為：log1,物理文件名為log1_log.ldf，初始大小為2MB，同主數(shù)據(jù)文件存放在相同的位置，增長方式為自動增長，每次增加15%。,4.2.1. 4 字符串型,字符串是由漢字、英文字母、數(shù)字和各種符號組成。用單引號括起來。 普通編碼字符串類型 統(tǒng)一字符編碼字符串類型 二進

5、制字符串類型,普通編碼字符串類型,Char（n）:定長存儲，n=8000 Varchar（n）：不定長存儲（按實際長度存儲），長度最大不超過n , n=8000 注：n 為字符個數(shù) Text：存儲大于8000字節(jié)的文本,統(tǒng)一字符編碼字符串類型,nchar（n）:定長存儲，n=4000 nvarchar（n）：不定長存儲，長度最大不超過n , n=4000 ntext：存儲大于8000字節(jié)的文本 特點：每個字符占兩個字節(jié),二進制字符串類型,Binary(n)：固定長度，n 8000，占用n + 4個字節(jié)的存儲空間 Varbinary(n) ：可變長度，n 8000 ，占用實際長度 + 4個字節(jié)的

6、存儲空間 注：n為二進制數(shù)據(jù)的字節(jié)數(shù) image ：大容量、可變長二進制字符數(shù)據(jù)，可用于存儲文件,4.2.2 用戶定義數(shù)據(jù)類型,除了系統(tǒng)提供的數(shù)據(jù)類型外，用戶還可以根據(jù)需要定義數(shù)據(jù)類型。 用戶定義數(shù)據(jù)類型需要基于SqL server的系統(tǒng)數(shù)據(jù)類型。 創(chuàng)建用戶定義數(shù)據(jù)類型 刪除用戶定義數(shù)據(jù)類型,4.3 T-SQL的語法元素,語句批 T-SQL的使用約定 保留字 標識符 變量 語句批 腳本,4.3.1 T-SQL的使用約定,1、語法格式約定 2、對象引用的規(guī)范,對象引用的規(guī)范, 服務(wù)器名稱.數(shù)據(jù)庫名稱.架構(gòu)名稱. |數(shù)據(jù)庫名稱.架構(gòu)名稱. |架構(gòu)名稱. 對象名 架構(gòu)是包含表、視圖、存儲過程等數(shù)據(jù)庫

7、對象的容器。,4.3.3 注釋,注釋 有對SQL語句進行說明的注釋，注釋是不能執(zhí)行的文字字符串，或暫時禁用的部分語句。 為程序加注釋不僅能使程序易懂，更有助于日后的管理和維護。 單行注釋符：- 多行注釋符：/* */,4.3.1.3 注釋的規(guī)范,注釋是程序代碼中不執(zhí)行的文本字符串（也稱為備注）。 用于對代碼進行說明或暫時禁用正在進行診斷的T-SQL語句批。使用注釋對代碼進行說明，便于將來對程序代碼進行維護。,注釋,1行內(nèi)注釋 行內(nèi)注釋的語法格式為： 注釋文本,2塊注釋 塊注釋的語法格式為： /*注釋文本*/ 或： /* 注釋文本 */,舉例,use test go declare dept v

8、archar(50) -存儲系部信息 select dept= case sdept when cs then 計算機系 when info then 信息系 when ma then 數(shù)學(xué)系 else 出錯 end from s where sno=001 -顯示其值 select dept as系部,4.3.2 保留字,保留字是SQL server本身使用的詞。數(shù)據(jù)庫中的對象名不能使用這些詞。 如果必須使用，必須使用分隔標識符。,4.3.3 標識符,標識符是諸如表、視圖、列和服務(wù)器等對象的名稱。 是在定義時創(chuàng)建的，隨后可以被引用。 分類： 常規(guī)標識符 分隔標識符,常規(guī)標識符,是指符合標識符

9、格式規(guī)則的標識符。 規(guī)則： （1）第一個字母必須是下列之一：英文字符a-z，A-Z；其他語言的字符；下劃線_；at符號；數(shù)字符號#； （2）后續(xù)包括美元符號$以及十進制數(shù)字； （3）標識符不能是T-SqL保留字； （4）不允許嵌入空格或其他字符。,分隔標識符,分隔標識符包含在“”或者中。 符合標識符定義規(guī)則的標識符可以分隔，可以不分隔，但是不符合標識符格式規(guī)則的標識符必須進行分隔。 Select * from My Table where order=10,4.3.4 變量,變量是被賦予一定的值的語言元素。 T-SQL語言中有兩種形式的變量， 用戶自己定義的局部變量；用戶用于保存特定類型的單個

10、數(shù)據(jù)值得對象。 系統(tǒng)提供的全局變量。全局變量由系統(tǒng)提供且預(yù)先聲明的變量，用戶只能察看不能修改；,1、局部變量,局部變量的作用范圍僅限制在程序內(nèi)部。局部變量被引用時要在其名稱前加上標志“”，而且必須先用DECLARE命令定義后才可以使用。,聲明局部變量,定義局部變量的語法形式如下： DECLAER local_variable data_type n 其中，參數(shù)local_variable用于指定局部變量的名稱， 參數(shù)data_type用于設(shè)置局部變量的數(shù)據(jù)類型及其大小.data_type可以是任何由系統(tǒng)提供的或用戶定義的數(shù)據(jù)類型。但是，局部變量不能是 text，ntext 或 image 數(shù)據(jù)

11、類型,局部變量,使用DECLARE命令聲明并創(chuàng)建局部變量之后，會將其初始值設(shè)為NULL，如果想要設(shè)定局部變量的值，必須使用SELECT命令或者SET命令。其語法形式為： SET local_variable = expression 或者 SELECT local_variable = expression ,.n 其中，參數(shù)local_variable是給其賦值并聲明的局部變量，參數(shù)expression是任何有效的SQL Server表達式。,局部變量,例1 創(chuàng)建一個myvar 變量，然后將一個字符串值放在變量中，最后輸出 myvar 變量的值。 程序清單如下： DECLARE myvar

12、char(20) select myvar = This is a test SELECT myvar GO,局部變量,例2 通過查詢給變量賦值。 程序清單如下： USE test GO DECLARE rows int SET rows = (SELECT COUNT(*) FROM sc),全局變量,全局變量是SQL Server系統(tǒng)內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時調(diào)用。 全局變量通常存儲一些SQL Server的配置設(shè)定值和統(tǒng)計數(shù)據(jù)。用戶可以在程序中用全局變量來測試系統(tǒng)的設(shè)定值或者是T-SQL命令執(zhí)行后的狀態(tài)值。,全局變量,在使用全局變量時應(yīng)該注意

13、以下幾點： （1）全局變量不是由用戶的程序定義的，它們是在服務(wù)器級定義的。 （2）用戶只能使用預(yù)先定義的全局變量。 （3）引用全局變量時，必須以標記符“”開頭。 （4）局部變量的名稱不能與全局變量的名稱相同，否則會在應(yīng)用程序中出現(xiàn)不可預(yù)測的結(jié)果。,全局變量,例顯示到當前日期和時間為止試圖登錄SQL Server的次數(shù)。 程序清單如下： SELECT GETDATE( ) AS 當前的時期和時間, CONNECTIONS AS 試圖登錄的次數(shù),例題,定義兩個局部變量，用他們來顯示當前的日期 本例中給出了兩種顯示方式：PRINT顯示在“消息”框，而SELECT顯示在“網(wǎng)格”框。 declare t

14、odayDate char(10),dispStr varchar(20) set todayDate=getdate() set dispStr=今天的日期為: print dispstr+todaydate select dispstr+todaydate,例題：通過SELECT語句來給多個變量賦值。 declare name varchar(50),lianxiren varchar(10),address varchar(50) declare msgstr varchar(80) -變量賦值 select name=名稱,lianxiren=聯(lián)系人,address=地址 from 供

15、應(yīng)商信息 set msgstr=供應(yīng)商名:+name+ 聯(lián)系人:+lianxiren+ 地址:+address -顯示信息 select msgstr Go,例題,例題,declare uName varchar(10),uTel varchar(10),uOrder varchar(10) declare goodN varchar(30),company varchar(30) declare MsgStr varchar(80) select uName=a.姓名,uTel=a.電話,uOrder=a.訂貨日期, goodN=b.貨品名稱,Company=b.供應(yīng)商名稱 from 客戶訂

16、單視圖 a inner join 貨品視圖 b on a.貨品編碼=b.編碼 and a.編號=2 set MsgStr=客戶+uName+聯(lián)系電話+uTel+于+uOrder+訂購了+company+的 +goodN+. print MsgStr go,局部變量的作用域，只能在聲明它的批處理內(nèi)部。一旦批處理消失，局部變量也將自動消失。 Declare dispstr varchar(20) Set dispstr=這是一個局部變量引用出錯的演示 Go -批處理在這里結(jié)束，局部變量被清除。 Print dispstr Go,例局部變量引用出錯的演示,例：利用全局變量查看SQL Server的版

17、本、當前使用的語言、服務(wù)器及服務(wù)器名稱。 print 所用SQL sever的版本信息 print version print print 服務(wù)器名稱為： +servername print 所用的語言為： +language print 所用的服務(wù)為: +servicename go,例,4.3.5語句批,語句批 一組SQL語句集合 將批處理的語句變異為一個可執(zhí)行單元，作為一個執(zhí)行單元，將執(zhí)行計劃中的語句每次執(zhí)行一條。 結(jié)束標記：GO,4.3.6腳本,腳本 腳本是批處理的存在方式，將一個或多個批處理組織到一起就是一個腳本 。 存儲在文件中的SQL語句集合 這樣可以省去重新 編寫調(diào)試SQL語句

18、的麻煩，提高工作效率。這些用于執(zhí)行某項操作的T-SQL語句集合稱為腳本。T-SQL腳本存儲為文件，帶有 sql 擴展名。,1.在查詢分析器中保存腳本； 2. 在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫對象腳本。 腳本可以在查詢分析器中執(zhí)行，也可以在ISQL或OSQL實用程序中執(zhí)行。查詢分析器是編輯、調(diào)試和使用腳本的最好環(huán)境。,生成腳本有兩種方法：,生成腳本,使用腳本,4.4 流程控制語句,1語句塊 BEGIN 語句序列 END 2. 分枝語句 IF 條件表達式 語句塊 ELSE 語句塊 3循環(huán)語句 WHILE 條件表達式 語句塊,BEGINEND語句塊,BEGIN和END用來定義語句塊，必須成對出現(xiàn)。它將多個S

19、QL語句括起來，相當于一個單一語句，其語法格式如下。 BEGIN 語句1或語句塊1 語句2或語句塊2 END,IFELSE語句,IFELSE語句是條件判斷語句，其中，ELSE子句是可選的，最簡單的IF語句沒有ELSE子句部分。IFELSE語句用來判斷當某一條件成立時執(zhí)行某段程序，條件不成立時執(zhí)行另一段程序。,語法格式如下。 IF 邏輯表達式 語句1或語句塊1 ELSE 語句2或語句塊2 ,例題,例如果李四的平均成績?yōu)?0分以上，顯示其成績情況，否則顯示文本：成績狀態(tài)不理想。 declare avgs int select avgs=avg(grade) from s,sc where s.sn

20、o=sc.sno and sname=李四,if(avgs=60) select s.sno,sname,cno,grade from s,sc where s.sno=sc.sno and sname=李四 else print 成績不理想,例題,課本P93，例題4-5 課本P93，例題4-6,多分枝語句,CASE語句可以計算多個條件式，并將其中一個符合條件的結(jié)果表達式返回。CASE語句按照使用形式的不同，可以分為簡單CASE語句和搜索CASE語句。 簡單CASE語句 搜索CASE語句,搜索CASE表達式,與簡單表達式不同的是，搜索表達式中，CASE關(guān)鍵字后面不跟任何表達式，在各WHEN關(guān)鍵

21、字后面跟的都是邏輯表達式，其語法格式如下。 搜索CASE表達式 CASE WHEN 布爾表達式1 THEN 結(jié)果表達式1 WHEN 布爾表達式2 THEN 結(jié)果表達式2 ELSE 結(jié)果表達式n+1 END,例 使用簡單的CASE語句編寫。查詢客戶信息，通過地址中的城市名確定其所屬城市。 select 姓名,城市= case when 地址 like %深圳% then 深圳人 when 地址 like %北京% then 北京人 when 地址 like %上海% then 上海人 else 其它城市人 end,電話 from 客戶信息 go,例題,特點與執(zhí)行過程,特點： 1、CASE關(guān)鍵字的

22、后面沒有任何表達式； 2、各個WHEN關(guān)鍵字的后面跟的都是布爾表達式；,執(zhí)行過程： 1、按從上到下的順序為每個WHEN子句的布爾表達式求值； 2、返回第一個取值為TRUE的布爾表達式所對應(yīng)的結(jié)果表達式的值。 如果沒有取值，則參照Else,例題,例使用搜索CASE，根據(jù)成績的范圍將顯示其對應(yīng)系的文本 use test go declare score char(1) select score= case when grade=90 and grade=80 and grade=70 and grade80 then C,when grade=60 and grade70 then D else

23、E end from sc where cno=100 and sno=001 select score,CASE示例1,declare a int ,answer char(10) set a=10 set answer=case a when 1 then A when 2 then B when 3 then C when 4 then D else others end print is + answer,CASE示例2,declare answer char(2),a int set a=10 set answer = case when a700 then A when a500

24、then B when a300 then C else D end print answer,簡單 CASE 表達式,CASE 測試表達式 WHEN 簡單表達式1 THEN 結(jié)果表達式1 WHEN 簡單表達式2 THEN 結(jié)果表達式2 ELSE 結(jié)果表達式n +1 END,例：每個銷售人員的銷售人員訂單視圖 ，給出各銷售人員的業(yè)績等級，這里只以每兩個訂單進行分級。 select 工號,姓名,業(yè)績等級= case 訂單數(shù)/2 when 0 then 初級 when 1 then 中級 when 2 then 高級 end,訂單數(shù) from 銷售人員訂單視圖,簡單 CASE 表達式,說明,測試表

25、達式：可以是一個變量名、字段名、函數(shù)或子查詢； 簡單表達式中不能包含比較運算符，他們給出被比較的表達式或值，其數(shù)據(jù)類型必須與測試表達式的數(shù)據(jù)類型相同，或者可以隱式轉(zhuǎn)換為測試表達式的數(shù)據(jù)類型。,簡單CASE表達式的執(zhí)行過程,1、計算測試表達式，然后按從上到下的順序?qū)γ總€when子句的簡單表達式進行計算。 2、如果某個簡單表達式的值與測試表達式的值匹配，則返回與第一個取值為TRUE的WHEN相對應(yīng)的結(jié)果表達式的值。 3、如果所有的簡單表達式的值都不予測試表達式的值匹配，則當指定Else子句時，返回Else子句中指定的表達式的值；如果沒有指定，則返回NULL。,例題,例根據(jù)sdept的數(shù)據(jù)顯示其詳細

26、內(nèi)容 use test go declare dept varchar(50) select dept= case sdept when cs then 計算機系 when info then 信息系 when ma then 數(shù)學(xué)系,else 出錯 end from s where sno=001 -顯示其值 select dept as系部,While 語句,WHILECONTINUEBREAK語句用于設(shè)置重復(fù)執(zhí)行 SQL 語句或語句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語句。其中，CONTINUE語句可以使程序跳過CONTINUE語句后面的語句，回到WHILE循環(huán)的第一行命令。BREA

27、K語句則使程序完全跳出循環(huán)，結(jié)束WHILE語句的執(zhí)行。,WHILE語句的語法形式,WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE ,計算1到10之間的奇數(shù)之和,declare i tinyint,sum int set sum=0 set i=0 while i=0 begin if(i=10) begin select 總和=sum break end,else begin set i=i+1 if(i % 2) = 0 contin

28、ue else set sum=sum+i end end,示例: 計算1+2+3+100的和,DECLARE i tinyint, sum smallint SET sum = 0 SET i = 1 While (i = 100 ) Begin SET sum = sum + i SET i = i + 1 end print sum,1RETURN語句 RETURN語句實現(xiàn)無條件退出執(zhí)行的批處理命令、存儲過程或觸發(fā)器。,其他語句,GOTO語句是無條件轉(zhuǎn)移語句，語法格式為： GOTO 標號,2GOTO語句,GOTO語句,GOTO語句可以使程序直接跳到指定的標有標識符的位置處繼續(xù)執(zhí)行，而位于

29、GOTO語句和標識符之間的程序?qū)⒉粫粓?zhí)行。GOTO語句和標識符可以用在語句塊、批處理和存儲過程中，標識符可以為數(shù)字與字符的組合，但必須以“: ”結(jié)尾。如：a1: 。在GOTO語句行，標識符后面不用跟“: ”。GOTO語句的語法形式為： GOTO label label:,GOTO語句,例7-57利用GOTO語句求出從1加到5的總和。 程序清單如下： declare sum int, count int select sum=0, count=1 label_1: select sum=sum+count select count=count+1 if count=5 goto label_1 select count sum,3RAISERROR RAISERROR語句通常用在錯誤處理中，它既可在屏幕上顯示用戶的信息，又可將錯誤號保存在ERROR全局變量中，以備錯誤處理時使用。其語法為： RAISERROR ( 消息標識 | 消息串 , 錯誤等級 , 狀態(tài) , 參數(shù) ,.n ) WITH 選項 ,.n ,RAISERROR 語句,4.5 常用內(nèi)置函數(shù),聚合函數(shù) 日期和時間函數(shù)