實現(xiàn)公司管理數(shù)據(jù)系統(tǒng)的編程

1、項目五 實現(xiàn)公司管理數(shù)據(jù)系統(tǒng)的編程 終極目標(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能正確理解和使用sql server變量 3能正確理解和使用sql server 函數(shù) 4會用transact-sql語句編程獲取對用戶有用的信

2、息二、工作任務(wù) 通過transact-sql編程，獲取名為王孔若的姓名、性別、出生年月及雇傭日期等信息；然后輸出王孔若所做訂單的明細(xì)表、所做的訂單數(shù)目及訂單總金額。（一）（一）sql server 2000sql server 2000語法規(guī)則語法規(guī)則 服務(wù)器端的程序使用sql語句來編寫。一般而言，一個服務(wù)器端的程序是由以下一些成分組成：批、注釋、變量、流程控制語句、錯誤和消息處理。1transact-sql批量 批量是一條或多條sql語句構(gòu)成的，它被應(yīng)用程序同時發(fā)送給sql server執(zhí)行。批結(jié)束的符號是“go”。 sql server從批中讀取所有語句，并把它們編譯成可執(zhí)行的單元（執(zhí)行計

3、劃），然后，sql server就一次執(zhí)行計劃中的所有語句，所以可以節(jié)省系統(tǒng)開銷。2go 命令 為了在transact-sql腳本中結(jié)束批量， sql server用到了關(guān)鍵字go。go關(guān)鍵字出現(xiàn)在一系列語句之后，使它們能作為一個單獨的批量運行。3續(xù)行 transact-sql語句很多情況下都寫的很長。如果全部放在一行寫，則不利于閱讀與修改?？梢詫⒁粭l語句在多行中進(jìn)行編寫，transact-sql會忽略空格和行尾的換行符號。4注釋 注釋語句由 “-”（雙減號）或“/*”和“*/”表示。 “-”（雙減號）：用于注釋單行文本。 “/*”和“*/”：用于注釋多行文本，“/*”和“*/”一定要配套使用

4、，否則將會出現(xiàn)錯誤。5、 selectselect語句無源查詢語句無源查詢 select語句無源查詢就是最簡單的語句。 select 常量|變量|函數(shù)|表達(dá)式 as 別名,n 所謂無源查詢就是使用select語句來查詢不在表中的數(shù)據(jù)。無源查詢實質(zhì)上就是在客戶機屏幕上顯示出常量、變量或表達(dá)式的值?！纠?】使用select語句查看常量。 select 我是中國人6、print功能：將用戶定義的消息返回客戶端。語法： print 字符串 | 變量 | 字符串的表達(dá)式注意： 只允許使用常量、表達(dá)式或變量。不允許使用列名。 【例2】使用 print 語句有條件地返回消息。 if exists ( sel

5、ect 產(chǎn)品id from p_order where 產(chǎn)品id=4) print 產(chǎn)品id為4有訂單！（二）sql server 2000變量分類：v局部變量：由用戶自己創(chuàng)建并賦值的，也只有 創(chuàng)建它的用戶可以使用。v全局變量：是有那些由服務(wù)器聲明的、通常由服務(wù)器賦值的變量。 1、局部變量局部變量局部變量局部變量用用declaredeclare語句聲明，只能用在聲明該變量的過語句聲明，只能用在聲明該變量的過程實體中，即使用范圍是定義它的批、存儲過程和觸程實體中，即使用范圍是定義它的批、存儲過程和觸發(fā)器等。由一個發(fā)器等。由一個 符號開始。符號開始。定義定義：declare declare 局部變

6、量名局部變量名 數(shù)據(jù)類型數(shù)據(jù)類型,nn賦值賦值：set set 局部變量名局部變量名= =表達(dá)式表達(dá)式,nn select select 局部變量名局部變量名= =表達(dá)式表達(dá)式,nn【例【例3】本模塊聲明了一個不超過本模塊聲明了一個不超過8 8個字符的字符型變量個字符的字符型變量namename的語句為：的語句為： declare name varchar(8)declare name varchar(8)【例4】將王孔若賦給變量name的賦值語句。 declare name varchar(10) set name=王孔若 print 選民 + name【例5】將張立賦給變量name的賦值語

7、句。 declare name varchar(10) select name=張立 print 選民 + name2、全局變量全局變量：由sql server系統(tǒng)提供并賦值的變量，名字由符號開始。用戶不能建立全局變量，也不可能使用set語句去修改全局變量的值。通常應(yīng)該將全局變量的值賦給在同一個批中的局部變量，以便保存和處理。 全局變量分為兩類：與連接有關(guān)的全局變量，如：rowcount 影響的行數(shù)；與 系 統(tǒng) 內(nèi) 部 信 息 有 關(guān) 的 全 局 變 量 。 如version sql的版本號。sql常用的全局變量表名稱說明connections返回當(dāng)前到本服務(wù)器的連接的數(shù)目。rowcount返

8、回上一條t-sql語句影響的數(shù)據(jù)行數(shù)。error返回上一條t-sql語句執(zhí)行后的錯誤號。procid返回當(dāng)前存儲過程的id號remserver返回登錄記錄中遠(yuǎn)程服務(wù)器的名字。spid返回當(dāng)前服務(wù)器進(jìn)程的id標(biāo)識。version返回當(dāng)前sql server服務(wù)器的版本和處理器類型。language返回當(dāng)前sql server服務(wù)器的語言。【例6】使用select語句查詢局部變量。declare a int, s varchar(9)set a=40set s=歡迎您select a, s【例7】使用select語句查詢?nèi)肿兞?。select version as 版本select languag

9、e as 語言（三）sql server 2000函數(shù) 在t-sql 編程語言中函數(shù)可分系統(tǒng)定義函數(shù)和用戶定義函數(shù)。本節(jié)介紹的是系統(tǒng)定義函數(shù)中最常用的數(shù)學(xué)函數(shù)、字符串函數(shù)、日期時間函數(shù)、聚合函數(shù)、系統(tǒng)函數(shù)、系統(tǒng)統(tǒng)計函數(shù)的最常用的部分。1日期和時間函數(shù) 對日期和時間輸入值執(zhí)行操作，返回一個字符串、數(shù)字或日期和時間值。名稱函數(shù)現(xiàn)在日期時間 getdate()年year(日期型表達(dá)式)月month(日期型表達(dá)式)日day(日期型表達(dá)式)日期 部分datepart(格式串, 日期型表達(dá)式)日期 加dateadd(格式串,數(shù)值,日期)日期 差datediff(格式串,日期1,日期2) (1) getda

10、te函數(shù)功能：返回當(dāng)前系統(tǒng)日期和服務(wù)器的時間。語法： getdate()【例8】輸出當(dāng)前日期： print getdate()其輸出結(jié)果如下： 07 12 2006 3:06am(2) day函數(shù)功能：返回某日期的日部分所代表的整數(shù)值，該函數(shù)等價于datepart(dd,date)。語法： day(date)【例9】 輸出指定日期的日期。 print day(08/30/1998)其輸出結(jié)果如下：30(3)month函數(shù)功能：返回某日期的月部分所代表的整數(shù)值。該函數(shù)等價于datepart(mm,date)。語法： month (date)【例10】 輸出指定日期的月份。 print month

11、 (08/30/2006)其輸出結(jié)果如下：8(4)year函數(shù)功能：返回某日期的年部分所代表的整數(shù)值。該函數(shù)等價于datepart(yy,date)。語法： year (date)【例11】 輸出指定日期的年份。 print year (08/30/2006) 其輸出結(jié)果如下： 2006（5）dateadd函數(shù)功能：在某日期上加上一個時間間隔，從而獲得一個新日期。語法： dateadd(datepart, number, date)參數(shù): datepart：是指定應(yīng)返回的日期部分的參數(shù)p123 日期組成部分表日期組成部分表日期部分日期部分縮寫縮寫yearyy, yyyymonthmm, mda

12、yofyeardy, ydaydd, dweekwk, wwweekdaydwhourhhminutemi, n【例12】 獲得指定時間后的新日期 print dateadd (dy, 35, 11/30/2007)輸出結(jié)果如下： 01 4 2008 12:00am注意：日期函數(shù)完成所有的字符串轉(zhuǎn)換操作，并返回sql server標(biāo)準(zhǔn)日期格式的輸出結(jié)果。所有這些日期函數(shù)都會自動執(zhí)行該轉(zhuǎn)換。(6) datediff函數(shù)功能：返回datepart指定的日期的差。語法： datediff(datepart,date1,date2)【例13】 計算兩個日期之間相差的天數(shù)。print datediff(

13、dy, 11/30/2007, 1/04/2008)結(jié)果如下：35(7) datename函數(shù)功能：返回日期的字符串表示。語法： datename (datepart, date)【例14】指定日期是星期幾： print datename (dw, 11/29/2007) 輸出結(jié)果如下： 星期四 即2007年11月29日是星期四(8) datepart函數(shù)功能：指定datepart的整數(shù)值(注意datepart函數(shù)與datepart參數(shù)不同)。語法： datepart(datepart, date)【例15】從日期中得到月份的數(shù)值表示： print datepart(mm, 6/11/2006

14、)輸出結(jié)果：62.字符串函數(shù) 字符串函數(shù)對字符串輸入值執(zhí)行操作，返回一個字符串或數(shù)字值。 參考：p127 常用的字符串函數(shù)名稱函數(shù)ascii碼ascii(字符表達(dá)式)字符char(數(shù)字表達(dá)式)字符char(數(shù)字表達(dá)式)小寫lower(字符表達(dá)式)大寫upper(字符表達(dá)式)長度len(字符表達(dá)式)空格space(數(shù)字表達(dá)式)名稱函數(shù)截取左左字串left(字符表達(dá)式,整數(shù))截取右右字串right(字符表達(dá)式,整數(shù))截取中間中間字串substring(字符表達(dá)式,起始點,n)求子串位置charindex(字符表達(dá)式1, 字符表達(dá)式2,開始位置)剪去左左空格ltrim(字符表達(dá)式)剪去右右空格rtr

15、im(字符表達(dá)式)重復(fù)字串replicate(字符表達(dá)式,n)倒置字串reverse(字符表達(dá)式)數(shù)值轉(zhuǎn)字串str(數(shù)字表達(dá)式)（1）lower函數(shù)功能：將所有字母表中存在的字符轉(zhuǎn)換為小寫形式。所有在字母表中不存在的字符保持不變。語法： lower(字符串)【例16】 print lower (abcdef)其輸出結(jié)果如下：abcdef（2）ltrim函數(shù) 功能：刪除字符串中所有的前導(dǎo)空格。 語法： ltrim(字符串)（3）rtrim函數(shù) 功能：刪除指定字符串末尾的所有后綴空格。 語法： rtrim(字符串)（4）reverse函數(shù)功能：將字符串中的所有字符逆轉(zhuǎn)。語法： reverse(字符

16、串)【例17】 print reverse(abcdef)其輸出結(jié)果如下：fedcba （5）str函數(shù)功能：將數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符串型數(shù)據(jù)。語法： str(數(shù)值,總長度,小數(shù)位數(shù))q “數(shù)值”是將要轉(zhuǎn)換成字符串型數(shù)據(jù)的數(shù)值型數(shù)據(jù)。q “總長度”可以用來指定字符串的總長度。q “小數(shù)位數(shù)”參數(shù)也是可選項，用來指定數(shù)值型數(shù)據(jù)小數(shù)部分的位數(shù)(默認(rèn)值為10)。3.數(shù)據(jù)類型轉(zhuǎn)換函數(shù) 將數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型的工作通常由sql server自動完成。如果要進(jìn)行顯示轉(zhuǎn)換數(shù)據(jù)類型，需要使用兩種函數(shù)：cast()或者convert()函數(shù)。（1）cast功能：將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另

17、一種數(shù)據(jù)類型。語法 cast ( 表達(dá)式 as 數(shù)據(jù)類型 ) 【例18】將 int 列（“庫存量 ” 列）轉(zhuǎn)換為varchar(20) 列，以便使用 like 子句。select 產(chǎn)品名, 單價, 庫存量from productwhere cast( 庫存量 as varchar(20) like 1_（2）convert功能：將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型。語法： convert (目的數(shù)據(jù)類型目的數(shù)據(jù)類型, 要轉(zhuǎn)化的表達(dá)式，要轉(zhuǎn)化的表達(dá)式，日期樣式日期樣式)【例19】print convert(char(2),month(getdate()工作任務(wù) 通過transact-s

18、ql編程，獲取名為“王孔若”的姓名、性別、出生年月及雇傭日期等信息；然后輸出王孔若所做訂單的明細(xì)表、所做的訂單數(shù)目及訂單總金額。 程序代碼模塊2 編程實現(xiàn)訂購訂單一、教學(xué)目標(biāo) 能正確理解transact-sql中的流程控制語句，包括判斷語句、循環(huán)語句，標(biāo)簽、return語句以及goto語句。工作任務(wù) 雇員王孔若簽訂了一條供應(yīng)給鹿城中學(xué)50個優(yōu)盤的訂單，編程實現(xiàn)將訂單涉及到的相關(guān)信息寫入到數(shù)據(jù)庫中。 流程控制語句流程控制語句 流程控制語句是指那些用來控制程序執(zhí)行和流程分支的命令，在sql server 2000中，流程控制語句主要用來控制sql語句、語句塊或者存儲過程的執(zhí)行流程。 （一） beg

19、in.end 功能：對用來將多行sql語句組織在一起，以達(dá)到一起執(zhí)行的目的。 語法： begin sql語句組 end 注意：必須包含在一個單獨的批處理中。（二） if.else語句語法格式： if else 注意：使用select語句作為這個條件表達(dá)式時，必須用圓括號將select語句括起來?！纠?0】編程判斷在p_order訂單表中是否存在 “訂貨日期”在2007年5月7號以后的訂單，分別打印相關(guān)信息。程序清單1：if (select count(*) from p_order where 訂貨日期 2007-05-07) 0 print 存在2007年5月7號以后的訂單else prin

20、t 不存在2007年5月7號以后的訂單 程序清單2：if exists (select * from p_order where 訂貨日期 2007-05-07) print 存在2007年5月7號以后的訂單else print 不存在2007年5月7號以后的訂單 （三）while循環(huán) while語句用于創(chuàng)建一個循環(huán)。語法：1）while 2）while begin end 【例21】計算并輸出1+2+3+100表達(dá)式的和。程序清單：declare sum intdeclare i intselect sum=0select i=1while i=10 and i=30)begin if i%

21、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),n)+ 個數(shù)能被3整除【例22】程序功能： 該程序用于輸出能被3整除的整數(shù)。其中用了continue語句和break語句。當(dāng)程序執(zhí)行到i為22時，直接跳出循環(huán)。故其執(zhí)行結(jié)果如下：12151821有4個數(shù)能被3整除（四） goto語句和return語句 goto語句用于讓sql server跳轉(zhuǎn)到sql代碼中的指定標(biāo)簽處，標(biāo)簽設(shè)計成如下形式：語法：goto 標(biāo)簽名 標(biāo)簽名: 【例23】分析以下語句

22、的執(zhí)行結(jié)果。declare sum int,x int,y intselect x=9,y=10select sum=x+ygoto label1set sum=sum+100label1: print x+y的值為+convert(char(3),sum)【例24】使用循環(huán)計算所給的局部變量的變化值 declare r int , s int , t intselect r=2, s=3 print rwhile r 5 begin select t=100*r+s print t select r=r+2 select s=1end（五） waitfor語句 功能： 指定觸發(fā)語句塊、存儲過

23、程或事務(wù)執(zhí)行的時間、時間間隔或事件。語法 waitfor delay time | time time 參數(shù)：q delay：指示 一直等到指定的時間過去，最長可達(dá) 24 小時。q time：要等待的時間?？梢园?datetime 數(shù)據(jù)可接受的格式指定 time，也可以用局部變量指定此參數(shù)。不能指定日期。q time：指示 sql server 等待到指定時間。 使用 waitfor delay【例25】等待30秒后執(zhí)行select語句 use companyinfo go waitfor delay 00:00:30 select * from product使用 waitfor time【

24、例26】等到11點12分后才執(zhí)行select 語句use companyinfogo waitfor time 11:12:00 slect * from product（六） return語句 用于無條件地終止一個查詢、存儲過程或者批處理，此時位于return語句之后的程序?qū)⒉粫粓?zhí)行。 語法： return integer_expression 參數(shù)integer_expression為返回的整型值。存儲過程可以給調(diào)用過程或應(yīng)用程序返回整型值。拓展知識拓展知識 系統(tǒng)內(nèi)置函數(shù)系統(tǒng)內(nèi)置函數(shù) 在transact-sql語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持sql server的標(biāo)準(zhǔn)命令。transact-sql 編程語言提供了三種函數(shù)： 標(biāo)量函數(shù)：標(biāo)量函數(shù)用于對傳遞給它的一個或者多個參數(shù)值進(jìn)行處理和計算，并返回一個單一的值。聚合函數(shù)：聚合函數(shù)用于對一組值執(zhí)行計算并返回一個單一的值。 行集函數(shù)：行集函數(shù)可以在transact-sql語句中當(dāng)作表引用。(一一)系統(tǒng)函數(shù)系統(tǒng)函數(shù) 系統(tǒng)函數(shù)用于返回有關(guān)sql server系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語句，根