清遠職業(yè)技術(shù)學(xué)院教案

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流清遠職業(yè)技術(shù)學(xué)院教案.精品文檔.清遠職業(yè)技術(shù)學(xué)院教案第_5_次（單元）課 授課時間： 第810周 課程名稱SQL SERVER數(shù)據(jù)庫技術(shù)專業(yè)班級 層次大專授課教師 職稱 課 型(大、小)小學(xué)時8授課題目（章、節(jié)）項目五 編程實現(xiàn)公司數(shù)據(jù)庫系統(tǒng)的操作授課方式理論課；實訓(xùn)課教材及主要參考書SQL Server 2000 數(shù)據(jù)庫教學(xué)目的與要求：1能正確理解和使用SQL Server變量2能正確理解和使用SQL Server 函數(shù)3會使用流程控制語句編寫順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)的程序教學(xué)過程設(shè)計(內(nèi)容、時間安排、教學(xué)方法等)：理論課4學(xué)時實訓(xùn)課4

2、學(xué)時教學(xué)重點、難點：重點：順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)難點：SQL Server 函數(shù)實施情況及小結(jié)： 基 本 內(nèi) 容項目五 實現(xiàn)公司數(shù)據(jù)庫系統(tǒng)的編程一、教學(xué)目標(biāo)終極目標(biāo)：會利用變量、函數(shù)和流程控制語句編寫Transact-SQL程序促成目標(biāo)：1能正確理解和使用SQL Server變量2能正確理解和使用SQL Server 函數(shù)3會使用流程控制語句編寫順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)的程序二、工作任務(wù)通過使用Transact-SQL語句編程，實現(xiàn)公司管理數(shù)據(jù)庫系統(tǒng)的操作。模塊1 編程獲取公司某雇員的工作業(yè)績信息一、教學(xué)目標(biāo)1能正確理解Transact-SQL批處理2會用Transact-SQL語句

3、編程獲取對用戶有用的信息3能正確理解和使用SQL Server變量4能正確理解和使用SQL Server 函數(shù)二、工作任務(wù)通過Transact-SQL編程，獲取名為'王孔若'的姓名、性別、出生年月及雇傭日期等信息；然后輸出'王孔若'所做訂單的明細(xì)表、所做的訂單數(shù)目及訂單總金額。三、相關(guān)實踐知識完成本項目工作任務(wù)的操作步驟如下：1打開查詢分析器窗口，在窗口中輸入以下Transact-SQL語句： USE companyInfo-定義SQL server的變量，必須以開頭命名變量，用DECLARE定義變量DECLARE emp_Id int DECLARE name

4、 varchar(8)DECLARE sex char(2)DECLARE date1 datetimeDECLARE date2 datetime-為變量賦值SET name='王孔若' -通過查詢語句將字段的值賦值給變量SELECT emp_Id=雇員id,sex=性別,date1=出生年月 ,date2=雇傭日期 FROM employee WHERE 姓名=name -用PRINT語句結(jié)合函數(shù)格式化輸出變量的值PRINT '姓名: '+name+' 性別：'+sex +' 出生年月：'+convert(char(4),ye

5、ar(date1)+ '年' +convert(char(2),month(date1)+ '月' +convert(char(2),day(date1)+ '日' +' 雇用日期：'+convert(char(4),year(date2)+ '年' +convert(char(2),month(date2)+ '月' +convert(char(2),day(date2)+ '日' -輸出雇員'王孔若'完成的訂單明細(xì)SELECT customer.公司名稱,cust

6、omer.聯(lián)系人姓名, product.產(chǎn)品名, P_order.數(shù)量,P_order.定貨日期, employee.姓名, product.單價 FROM employee,customer,product,P_order WHERE employee.雇員id= emp_Id and P_order.雇員id=employee.雇員id and P_order.產(chǎn)品id=product.產(chǎn)品id and P_order.客戶id=customer.客戶id -輸出雇員'王孔若'完成的訂單數(shù)目以及訂單總金額SELECT count(*) as 訂單數(shù)目,sum(P_order

7、.數(shù)量*product.單價) as 總金額 FROM employee,product,P_order WHERE employee.雇員id= emp_Id and P_order.雇員id=employee.雇員id and P_order.產(chǎn)品id=product.產(chǎn)品id 2.執(zhí)行工具欄的運行命令。四、相關(guān)理論知識（一）SQL Server 2000語法規(guī)則1Transact-SQL批處理查詢是一條SQL DML語句，而批處理是一條或多條SQL語句構(gòu)成的，它被應(yīng)用程序同時發(fā)送給SQL Server執(zhí)行。SQL Server從批中讀取所有語句，并把它們編譯成可執(zhí)行的單元（執(zhí)行計劃），然

8、后，SQL Server就一次執(zhí)行計劃中的所有語句。2GO命令為了在Transact-SQL腳本中結(jié)束批處理， SQL Server用到了關(guān)鍵字GO。GO關(guān)鍵字出現(xiàn)在一系列語句之后，使它們能作為一個單獨的批處理運行。3續(xù)行Transact-SQL語句很多情況下都寫的很長。如果全部放在一行寫，則不利于閱讀與修改。可以將一條語句在多行中進行編寫，Transact-SQL會忽略空格和行尾的換行符號。4注釋“-”（雙減號）或“/*”和“*/”表示，在查詢分析器中是以藍色字體顯示。其中“-”（雙減號）用于注釋單行文。5、 SELECE語句無源查詢 SELECT語句無源查詢就是最簡單的語句。 SELECT

9、 常量|變量|函數(shù)|表達式 as 別名,n【例1】使用SELECT語句查看常量。 select '我是中國人'6、PRINT功能：將用戶定義的消息返回客戶端。語法： PRINT 字符串 | 變量 | 字符串的表達式【例2】使用 PRINT 語句有條件地返回消息。 IF EXISTS (SELECT zip FROM authors WHERE zip = '94705') PRINT 'Berkeley author' （二）SQL Server 2000變量1、局部變量局部變量用DECLARE語句聲明，只能用在聲明該變量的過程實體中，即使用范圍

10、是定義它的批、存儲過程和觸發(fā)器等。由一個符號開始。定義：DECLARE 局部變量名 數(shù)據(jù)類型,n賦值：SET 局部變量名=表達式,n select 局部變量名=表達式,n【例3】本模塊聲明了一個不超過8個字符的字符型變量name的語句為： DECLARE name varchar(8)【例4】將'王孔若'賦給變量name的賦值語句。 DECLARE name VARCHAR(10) SET name='王孔若' PRINT '選民 ' + name【例5】將'張立'賦給變量name的賦值語句。 DECLARE name VARCH

11、AR(10) SELECT name='張立' PRINT '選民 ' + name2、全局變量由SQL Server系統(tǒng)提供并賦值的變量，名字由符號開始。用戶不能建立全局變量，也不可能使用SET語句去修改全局變量的值。通常應(yīng)該將全局變量的值賦給在同一個批中的局部變量，以便保存和處理。（三）SQL Server 2000函數(shù)在本模塊中用了一些函數(shù)，如格式轉(zhuǎn)換函數(shù)convert()，日期函數(shù)year()、month()、day()等，熟練掌握SQL Server常用函數(shù)是非常必要并且是很有用的。1日期函數(shù)日期計算經(jīng)常應(yīng)用于很多商業(yè)應(yīng)用程序中，例如到期時間、利息計算

12、、生日以及孕婦產(chǎn)期計算等。SQL Server日期函數(shù)自動執(zhí)行從CHAR(或VARCHAR)到DATETIME(或SMALLDATETIME)的轉(zhuǎn)換。在使用日期函數(shù)之前，用戶應(yīng)了解以下的日期組成部分。表5-1 日期組成部分日期組成部分縮寫說明取值范圍yearyy, yyyy年份1753到9999quarterqq, q季度1到4Monthmm, m月份1到12dayofyeardy, y年內(nèi)天數(shù)1到366Daydd, d月內(nèi)天數(shù)1到31Weekwk, ww年內(nèi)周數(shù)1到53Hourhh小時0到23minutemi, n分鐘0到59secondss, s秒0到59millisecondms毫秒0到

13、999SQL Server 允許用戶配置以四年為界限的日期?？梢允褂肧QL企業(yè)管理器，也可以使用sp_configure命令進行配置。（1）DATEADD函數(shù)此函數(shù)允許用戶在某日期上加上一個時間間隔，從而獲得一個新日期。其語法如下：DATEADD (datepart, number, date)datepart是表5-1中所列的任何值，但用戶一般不會在日期上增加秒或毫秒。下面的print語句用于計算參數(shù)日期30天后的時間。PRINT DATEADD (DY, 30, '10/01/1998')輸出結(jié)果如下：10 31 1998 12:00AM(2) DATEDIFF函數(shù)DATE

14、DIFF函數(shù)返回datepart指定的日期的差。其語法如下：DATEDIFF(datepart,date1,date2)下面的print語句用于計算兩個日期之間相差的天數(shù)。PRINT DATEDIFF (DY, '08/30/1998', '10/01/1998')結(jié)果如下：32(3) DATENAME函數(shù)這個函數(shù)返回日期的字符串表示。其語法如下：DATENAME (datepart, date)使用如下的代碼得到指定日期是星期幾：PRINT DATENAME (DW, '6/24/2006')輸出結(jié)果如下：星期六即2006年6月24日是星期六(

15、4) DATEPART函數(shù)該函數(shù)返回指定datepart的整數(shù)值(注意DATEPART函數(shù)與datepart參數(shù)不同)。例如，DATEPART函數(shù)可以返回當(dāng)前月份的數(shù)值表示。其語法格式如下：DATEPART(datepart, date)下面的PRINT t語句用于從日期中得到月份的數(shù)值表示：PRINT DATEPART(MM, '6/11/2006')輸出結(jié)果如下：6 (5) GETDATE函數(shù)GETDATE函數(shù)返回當(dāng)前系統(tǒng)日期和服務(wù)器的時間。它不接受任何參數(shù)，但卻是一個十分有用的函數(shù)。其語法如下：GETDATE()下面的PRINT語句用于輸出當(dāng)前日期：PRINT GETDA

16、TE()其輸出結(jié)果如下：07 12 2006 3:06AMGETDATE的一個很好的用途是可以記錄運行該任務(wù)報告的用戶名以及運行報告產(chǎn)生的日期。因為用戶不能改變服務(wù)器日期和時間，所以該信息是一個很有效的檢查標(biāo)記。(6) DAY函數(shù)DAY函數(shù)返回某日期的'日'部分所代表的整數(shù)值，該函數(shù)等價于DATEPART(dd,date)。其語法如下：DAY(date)下面的print語句用于輸出當(dāng)前日期：PRINT DAY('08/30/1998')其輸出結(jié)果如下：30(7)MONTH函數(shù)MONTH函數(shù)返回某日期的'月'部分所代表的整數(shù)值。該函數(shù)等價于DATE

17、PART(mm,date)。其語法如下：MONTH (date)下面的print語句用于輸出當(dāng)前日期：PRINT MONTH ('08/30/1998')其輸出結(jié)果如下：8(8)YEAR函數(shù)YEAR函數(shù)返回某日期的'年'部分所代表的整數(shù)值。該函數(shù)等價于DATEPART(yy,date)。其語法如下：YEAR (date)下面的print語句用于輸出當(dāng)前日期：PRINT YEAR ('08/30/1998')其輸出結(jié)果如下：19982.字符串函數(shù)SQL Server提供了幾種功能強大的函數(shù)來操作字符串。最常用的字符串函數(shù)是LOWER、LTRIM、R

18、TRIM、REVERSE、STR、SUBSTRING和UPPER。下面將分別給出其概念及語法，并組合使用這些函數(shù)舉幾個示例。（1）LOWER函數(shù)該函數(shù)將所有字母表中存在的字符轉(zhuǎn)換為小寫形式。所有在字母表中不存在的字符保持不變。LOWER函數(shù)的語法如下：LOWER(字符串)該函數(shù)可以和其他函數(shù)結(jié)合使用來糾正數(shù)據(jù)項的錯誤。有些人全部輸入大寫字符，有些全部輸入小寫，甚至還有些以適當(dāng)?shù)拇笮戄斎?。例如：PRINT LOWER ('ABCDEF')其輸出結(jié)果如下：abcdef（2）LTRIM函數(shù)該函數(shù)刪除字符串中所有的前導(dǎo)空格。其語法如下：LTRIM(字符串)它經(jīng)常用來糾正數(shù)據(jù)項的錯誤或

19、在數(shù)據(jù)遷移、轉(zhuǎn)換、EDI或其他一些數(shù)據(jù)輸入的過程中糾正數(shù)據(jù)。用戶可以將該函數(shù)與RTRIM函數(shù)結(jié)合起來使用，刪除所有前導(dǎo)和后綴空格。例如：PRINT LTRIM (' smith ')其輸出結(jié)果如下：smith 參數(shù)中，” smith ”前后都有兩個空格，用LTRIM函數(shù)后，輸出結(jié)果可以看出前面的空格自動去除了，單后面的空格依然存在。（3）REVERSE函數(shù)該函數(shù)將字符串中的所有字符逆轉(zhuǎn)。其語法如下：REVERSE(字符串)REVERSE函數(shù)也可以將表的一列作為輸入。程序設(shè)計者可以將它嵌入到SQL查詢中，用于設(shè)置默認(rèn)口令。例如，可以將一個觸發(fā)器設(shè)在擁有一系列用戶的表上。當(dāng)一個新用

20、戶插入表中時，觸發(fā)器將其密碼字段更新為用戶名的逆轉(zhuǎn)。例如：PRINT REVERSE('ABCDEF')其輸出結(jié)果如下：FEDCBA（4）RTRIM函數(shù)該函數(shù)刪除指定字符串末尾的所有后綴空格。其語法如下：RTRIM(字符串)它經(jīng)常用于糾正數(shù)據(jù)項的錯誤，或者在數(shù)據(jù)遷移、數(shù)據(jù)轉(zhuǎn)換、EDI以及其他一些數(shù)據(jù)輸入的過程中糾正數(shù)據(jù)。另一個應(yīng)用是將它作用到SELECT語句的一個CHAR類型的列上。有時CHAR類型的列上的數(shù)據(jù)不能完全填滿該列，因此就要用空格填充。和LTRIM函數(shù)相似，例子看參考LTRIM函數(shù)例子。（5）STR函數(shù)該函數(shù)將數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符串型數(shù)據(jù)。其語法如下：STR(數(shù)值,

21、總長度,小數(shù)位數(shù))“數(shù)值”是將要轉(zhuǎn)換成字符串型數(shù)據(jù)的數(shù)值型數(shù)據(jù)?？蛇x項“總長度”可以用來指定字符串的總長度，包含符號、空格、數(shù)字和十進制小數(shù)點?！靶?shù)位數(shù)”參數(shù)也是可選項，用來指定數(shù)值型數(shù)據(jù)小數(shù)部分的位數(shù)(默認(rèn)值為10)。該函數(shù)的結(jié)果常用來和字符串?dāng)?shù)據(jù)結(jié)合起來作為輸出結(jié)果。例如：PRINT STR(123.45,7,3)其輸出結(jié)果如下：123.450 （6）SUBSTRING函數(shù)SUBSTRING函數(shù)返回字符串的一部分或全部。其語法如下：SUBSTRING(字符串,開始位置,長度)“字符串”是需要檢索子串的字符串或表的列名稱?！伴_始位置”是“字符串”中子串的起始位置?！翱傞L度”是從“字符串”中

22、“開始位置”位置開始的需要取出的字符數(shù)目。SUBSTRING函數(shù)的一些實際應(yīng)用有：從電話號碼列中分析出地區(qū)代碼，從姓名列中檢索出姓。例如：PRINT SUBSTRING('abcdefg',2,3)其輸出結(jié)果如下：bcd3.數(shù)據(jù)類型轉(zhuǎn)換函數(shù)將數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型的工作通常由SQL Server自動完成。如果要進行顯示轉(zhuǎn)換數(shù)據(jù)類型，需要使用兩種函數(shù)：CAST()或者CONVERT()函數(shù)。（1）CAST(原數(shù)據(jù) as 目的數(shù)據(jù)類型)：其功能是將數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型。（2）Convert(目的數(shù)據(jù)類型，要轉(zhuǎn)換的數(shù)據(jù)類型，日期樣式)：它可以將數(shù)據(jù)按

23、一定的格式轉(zhuǎn)換為另一種數(shù)據(jù)類型，第三個參數(shù)是可選參數(shù)，一般只有將數(shù)據(jù)轉(zhuǎn)換為日期數(shù)據(jù)時，才使用，它可以取很多值，分別代表不同的日期格式，如107代表“月，日，年”格式。例如：PRINT CAST(123 as char(15) 執(zhí)行結(jié)果為：123即將123轉(zhuǎn)換為字符型數(shù)據(jù)。本模塊中的PRINT輸出語句中，“convert(char(2),month(date1)+ '月'”功能即為先取出date1中的月份，將其轉(zhuǎn)換為字符型數(shù)據(jù)，再連接'月'，整個表達式為一個字符串。模塊2 編程實現(xiàn)訂購訂單一、教學(xué)目標(biāo)能正確理解Transact-SQL中的流程控制語句，包括判斷語句

24、、循環(huán)語句，標(biāo)簽、RETURN語句以及GOTO語句。二、工作任務(wù)雇員王孔若簽訂了一條供應(yīng)給'鹿城中學(xué)'50個優(yōu)盤的訂單，編程實現(xiàn)將訂單涉及到的相關(guān)信息寫入到數(shù)據(jù)庫中。三、相關(guān)實踐知識完成本項目的步驟如下：1打開查詢分析器窗口，在窗口中輸入以下Transact-SQL語句：USE companyInfoDECLARE userId int -保存雇員IDDECLARE max_cusId int -保存客戶ID的最大值DECLARE max_ordId int -保存訂單ID的最大值DECLARE storePro int -保存產(chǎn)品的庫存量DECLARE id_product

25、int -保存供應(yīng)產(chǎn)品的產(chǎn)品編號SELECT max_cusId=max(客戶ID) FROM customerSELECT max_cusId=max_cusId+1 -獲取新插入客戶的ID值INSERT customer VALUES(max_cusId, '鹿城中學(xué)', '李漢明', '05778235423','溫州市學(xué)院路24號', '325000')SELECT storePro=庫存量,id_product=產(chǎn)品ID FROM product WHERE 產(chǎn)品名='優(yōu)盤'SELECT

26、max_ordId=max(訂單ID) FROM P_orderSELECT max_ordId=max_ordId+1 -獲取要插入的訂單信息的訂單IDSELECT userId=雇員ID FROM employee WHERE 姓名='王孔若' IF storePro<50 PRINT '庫存量不夠'ELSEBEGIN INSERT P_order VALUES(max_ordId,id_product, 50,userId,max_cusId,getdate()UPDATE product SET 庫存量=庫存量-50 WHERE 產(chǎn)品ID=id_p

27、roductEND四、相關(guān)理論知識SQL Server提供了幾種流控制關(guān)鍵字，可用于控制SQL Server如何去執(zhí)行批處理或過程中的語句。如果不使用流控制關(guān)鍵詞， SQL Server只能按順序方式執(zhí)行腳本中的語句。如果使用了流控制語言，就可以將語句分組并根據(jù)批處理或過程中的判斷標(biāo)準(zhǔn)來控制它們的執(zhí)行。（一） BEGIN.ENDBEGIN.END關(guān)鍵詞對用來將多行SQL語句組織在一起，以達到一起執(zhí)行的目的。它之所以是一對重要的關(guān)鍵詞，是因為它可以和許多其他的流控制關(guān)鍵詞一起使用。該語句的語法如下： BEGIN SQL語句組 END其中“SQL語句組”是任何合法的SQL單行語句或多行語句。需要用

28、戶加以注意的是它必須包含在一個單獨的批處理中。（二） IF.ELSE語句1IF.ELSE語句的語法格式IF <條件表達式><SQL語句組1>ELSE<SQL語句組2>【例20】編程判斷在P_order訂單表中是否存在 “訂貨日期”在2004年5月7號以后的訂單，分別打印相關(guān)信息。程序清單如下：IF (SELECT count(*) FROM P_order WHERE 訂貨日期>'2004-05-07')>0 PRINT '存在“訂貨日期”在2004年5月7號以后的訂單'ELSE PRINT '不存“訂貨日

29、期”的在2004年5月7號以后的訂單' （三）WHILE循環(huán)WHILE語句用于創(chuàng)建一個循環(huán)，SQL Server將在該循環(huán)中連續(xù)執(zhí)行某些語句，直到循環(huán)條件為假。還可以通過設(shè)置一個永遠為真的條件，如1=1，使循環(huán)一直進行下去。1WHILE循環(huán)的語法格式WHILE語句用于創(chuàng)建一個循環(huán)。語法：1）WHILE <條件表達式> <單行SQL語句>2）WHILE <條件表達式> BEGIN <SQL語句組> END 【例21】計算并輸出1+2+3+100表達式的和。程序清單如下：DECLARE sum intDECLARE i intSELECT s

30、um=0SELECT i=1;WHILE i<=100BEGIN SET sum=sum+i SET i=i+1ENDPRINT sum【例23】分析下列程序的功能。DECLARE sum intDECLARE n intDECLARE i intSELECT n=0;SELECT i=10;WHILE (i>=10 and i<=30)BEGIN IF i%3=0 BEGIN PRINT i SET i=i+1 SET n=n+1 CONTINUE END IF i=22 BREAK SET i=i+1ENDPRINT '有'+convert(char(2)

31、,n)+ '個數(shù)能被3整除'程序功能：該程序用于輸出能被3整除的整數(shù)。其中用了CONTINUE語句和BREAK語句。當(dāng)程序執(zhí)行到i為22時，直接跳出循環(huán)。故其執(zhí)行結(jié)果如下：12151821有4個數(shù)能被3整除（四） GOTO語句和RETURN語句用戶在使用GOTO語句時必須十分謹(jǐn)慎，因為過多地使用GOTO語句會使代碼難讀，執(zhí)行速度降低。該語句通常和RETURN語句一起使用。GOTO語句用于讓SQL Server跳轉(zhuǎn)到SQL代碼中的指定標(biāo)簽處，標(biāo)簽設(shè)計成如下形式：GOTO語法如下：GOTO 標(biāo)簽名 <SQL語句組1>標(biāo)簽名: <SQL語句組2>【例24】分析

32、以下語句的執(zhí)行結(jié)果。DECLARE sum intDECLARE x intDECLARE y intSELECT x=9SELECT y=10SELECT sum=x+yGOTO label1SET sum=sum+100label1:PRINT sum Use 信息管理 WAITFOR delay 01:10:12' Select * from 學(xué)生【例26】等到11點12分后才執(zhí)行select 語句Use companyinfo WAITFOR time 11:12:00' Select * from employee（六） RETURN語句 用于無條件地終止一個查詢、存儲過程或者批處理，此時位于RETURN語句之后的程序?qū)⒉粫粓?zhí)行。 語法： RETURN integer_expression 參數(shù)：i