實(shí)訓(xùn)七KUTransact-SQL程序設(shè)計(jì)

1、實(shí)訓(xùn)七 Transact-SQL程序設(shè)計(jì)一、實(shí)訓(xùn)目的1. 掌握Transact-SQL中全局變量和局部變量的使用方法；2. 掌握Transact-SQL函數(shù)的使用方法；3. 掌握自定義函數(shù)的方法；4. 初步掌握Transact-SQL流程控制語(yǔ)句的使用。二、實(shí)訓(xùn)步驟 (一) 變量的使用1. 全局變量的使用(1) 顯示到當(dāng)前日期和時(shí)間為止試圖登錄SQL Server的次數(shù)。SELECT GETDATE() AS '當(dāng)前登陸日期和時(shí)間' , CONNECTIONS AS '企圖登陸次數(shù)'當(dāng)前登陸日期和時(shí)間 企圖登陸次數(shù)- -2011-10-25 11:44:23.4

2、20 196(1 行受影響)(2) 顯示SQL Server安裝的日期、版本和處理器類型。SELECT VERSION AS '當(dāng)前的SQL Server信息' 當(dāng)前的SQL Server信息-Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft CorporationStandard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)(1 行受影響)2. 局部

3、變量的使用(1) 編程實(shí)現(xiàn)將student數(shù)據(jù)庫(kù)中t_student表的記錄個(gè)數(shù)存入到局部變量，并通過(guò)局部變量顯示出來(lái)。 USE STUDENTGODECLARE RowsReturn intSET RowsReturn = (SELECT COUNT(*) FROM T_STUDENT)SELECT RowsReturn AS 'SELECT 返回的記錄數(shù)'GOSELECT 返回的記錄數(shù)-20(1 行受影響)(2) 編程實(shí)現(xiàn)將student數(shù)據(jù)庫(kù)中t_score表中的分?jǐn)?shù)的最大值和最小值分別存入到兩個(gè)局部變量，并通過(guò)局部變量顯示出來(lái)。 -聲明變量DECLARE MaxScor

4、e real,MinScore real-給變量賦值SELECT MaxScore = MAX(SCORE), MinScore = MIN(SCORE)FROM T_SCORESELECT MaxScore as '最大值', MinScore as '最小值'GO最大值 最小值- -93 52(1 行受影響) (二) 函數(shù)的使用1. 系統(tǒng)函數(shù)的使用(1) 顯示服務(wù)器端計(jì)算機(jī)的名稱 select host_name() as '服務(wù)器端計(jì)算機(jī)名稱'(2) 顯示用戶的數(shù)據(jù)庫(kù)用戶名 select user_name() as '數(shù)據(jù)庫(kù)用戶名

5、'2. 字符串函數(shù)的使用(1) 顯示字符A的ASCII碼。SELECT ascii('A') as 'A的ASCII碼'A的ASCII碼-65(1 行受影響)(2) 顯示ASCII碼為68的字符。SELECT char(68) as 'ASCII碼為的字符'ASCII碼為的字符-D(1 行受影響)(3) 利用substring、right和len函數(shù)，顯示t_student表中班級(jí)、班內(nèi)學(xué)號(hào)、姓名字段的內(nèi)容。（參考教材P119例6-23）SELECT SUBSTRING(S_NUMBER,1,LEN(S_NUMBER)-2) AS 

6、9;班級(jí)', RIGHT(RTRIM(S_NUMBER),2) AS 學(xué)號(hào), S_NAME AS 姓名, SEX AS 性別, BIRTHDAY AS 出生日期, POLITY AS 政治面貌FROM T_STUDENTWHERE LEFT(S_NUMBER,5)='05541'班級(jí) 學(xué)號(hào) 姓名 性別 出生日期 政治面貌- - - - - -05541 01 劉洋 男 1984-10-14 00:00:00.000 黨員05541 02 李志娟 女 1984-11-12 00:00:00.000 團(tuán)員05541 03 趙雪梅 女 1984-06-25 00:00:00.

7、000 團(tuán)員05541 04 王華 女 1984-09-11 00:00:00.000 團(tuán)員05541 05 張金輝 男 1985-05-16 00:00:00.000 群眾05541 06 賈波 男 1984-10-03 00:00:00.000 黨員05541 07 周萍 女 1984-07-12 00:00:00.000 群眾05541 08 馬帥 男 1984-05-22 00:00:00.000 團(tuán)員05541 09 唐蕓 女 1985-08-25 00:00:00.000 群眾05541 10 王華 男 1984-01-20 00:00:00.000 團(tuán)員(10 行受影響)3. 日

8、期型函數(shù)的使用(1) 利用getdate和year函數(shù)顯示t_student表中的學(xué)號(hào)、姓名、年齡字段的內(nèi)容。（參考教材P121例6-25）SELECT S_NUMBER AS 學(xué)號(hào), S_NAME AS 姓名, SEX AS 性別, YEAR(GETDATE()-YEAR(BIRTHDAY) AS 年齡FROM T_STUDENT學(xué)號(hào) 姓名 性別 年齡- - - -0554101 劉洋 男 270554102 李志娟 女 270554103 趙雪梅 女 270554104 王華 女 270554105 張金輝 男 260554106 賈波 男 270554107 周萍 女 270554108

9、 馬帥 男 270554109 唐蕓 女 260554110 王華 男 27B0451101 張小航 男 27B0451102 王文廣 男 26B0451103 李艷紅 女 27B0451104 張麗霞 女 27B0451105 王強(qiáng) 男 27B0451106 張保田 男 27B0451107 李博文 男 27B0451108 劉芳芳 女 26B0451109 李海 男 27B0451110 常江寧 男 27(20 行受影響)(2) 利用month或datepart函數(shù)顯示t_student表中學(xué)號(hào)、姓名和出生月份（參考教材P122例6-26）。SELECT S_NUMBER AS 學(xué)號(hào), S

10、_NAME AS 姓名, SEX AS 性別, DATEPART(Month,BIRTHDAY) AS 出生月份FROM T_STUDENT學(xué)號(hào) 姓名 性別 出生月份- - - -0554101 劉洋 男 100554102 李志娟 女 110554103 趙雪梅 女 60554104 王華 女 90554105 張金輝 男 50554106 賈波 男 100554107 周萍 女 70554108 馬帥 男 50554109 唐蕓 女 80554110 王華 男 1B0451101 張小航 男 12B0451102 王文廣 男 5B0451103 李艷紅 女 6B0451104 張麗霞 女

11、7B0451105 王強(qiáng) 男 11B0451106 張保田 男 7B0451107 李博文 男 8B0451108 劉芳芳 女 4B0451109 李海 男 2B0451110 常江寧 男 3(20 行受影響)(3) 顯示今天距2003年1月21日相隔的天數(shù)。提示：select datediff(dd,'2003-1-21',getdate() AS 相隔天數(shù)SELECT DATEDIFF(day,'2003-1-21',getdate() AS '今天距離1月21日相隔的天數(shù)'今天距離1月21日相隔的天數(shù)-3201(1 行受影響)4. 轉(zhuǎn)換函數(shù)

12、的使用(1) 利用convert函數(shù)查詢t_student表中出生年份為1985年的學(xué)生信息。 SELECT S_NUMBER AS 學(xué)號(hào), S_NAME AS 姓名, SEX AS 性別, BIRTHDAY AS 出生日期 POLITY AS 政治面貌FROM T_STUDENTWHERE CONVERT ( char(40),BIRTHDAY,102)LIKE '1985%'(2) 按yy.mm.dd格式顯示t_student表中的出生日期。select CONVERT(char(40),BIRTHDAY,102) as 出生日期from t_student(3) 按dd/

13、mm/yy格式顯示t_student表中的出生日期。select CONVERT(char(40),BIRTHDAY,103) as 出生日期from t_student(4) 按hh:mi:ss的格式顯示當(dāng)前時(shí)間。select CONVERT(char(40),getdate(),108) as 當(dāng)前時(shí)間5. 數(shù)學(xué)函數(shù)的使用(1) 利用rand函數(shù)隨機(jī)產(chǎn)生一個(gè)0到1之間的小數(shù)。select rand(8) as '0到之間的一個(gè)小數(shù)'(2) 利用rand和floor函數(shù)隨機(jī)產(chǎn)生一個(gè)0-99之間的整數(shù)。select floor(rand(6)*100) as '在至產(chǎn)生

14、一個(gè)整數(shù)的值'6. 用戶自定義函數(shù)的使用(1) 編寫一個(gè)用戶自定義函數(shù)fun_sumscores，要求根據(jù)輸入的班級(jí)號(hào)和課程號(hào)，求此班此門課程的總分。（參考教材P125例6-30）CREATE FUNCTION Fun_SumScores(ClassName AS char(10), CourseNumber AS char(10)RETURNS realBEGIN DECLARE SumResult AS real SELECT SumResult=sum(score) FROM t_score WHERE SUBSTRING(s_number,1,LEN(s_number)-2)

15、=ClassName AND c_number=CourseNumber GROUP BY SUBSTRING(s_number,1,LEN(s_number)-2) RETURN SumResultEND(2) 利用上面用戶自定義函數(shù)fun_sumscores,求t_score表中各個(gè)班級(jí)的各門課程的總分。（參考教材P125例6-31）SELECT DISTINCT 班級(jí)名稱=SUBSTRING(s_number,1,LEN(s_number)-2), 課程名稱=t_course.c_name, 總分=dbo.Fun_SumScores(SUBSTRING(s_number,1,LEN(s_

16、number)-2), t_score.c_number)FROM t_score,t_courseWHERE t_score.c_number=t_course.c_number(3) 編寫一個(gè)用戶自定義函數(shù)，完成以下功能：根據(jù)兩個(gè)輸入的參數(shù)（成績(jī)的上限和下限），求t_score表中滿足輸入條件的學(xué)生人數(shù)。Create function fun_sumren(maxline real,minline real)returns intbegindeclare personcount as intselect personcount=count(*) from t_scorewhere scor

17、e<=maxline and score>=minlinereturn personcountend(4) 利用上面的用戶自定義函數(shù)，求t_score表中成績(jī)?cè)?0-90分之間的學(xué)生人數(shù)。select dbo.fun_sumren(60,90) as '60到分的學(xué)生人數(shù)'(三) 流程控制語(yǔ)句的使用1根據(jù)t_score表中的考試成績(jī)，查詢B04511班學(xué)生課程號(hào)為10010218的課程的平均成績(jī)，若平均成績(jī)大于75,顯示“成績(jī)較理想”，否則顯示“成績(jī)不理想”。利用if語(yǔ)句編寫程序?qū)崿F(xiàn)以上功能。（參考教材P126例6-32）if (select avg(score)

18、from t_score where left(s_number,6)='B04511'and c_number='10010218')>75begin print 'B04511班課程的平均成績(jī)較理想!'print ''select avg(score) as 平均成績(jī)from t_scorewhere left(s_number,6)='B04511'and c_number='10010218'endelsebeginprint 'B04511班課程的平均成績(jī)不理想!'p

19、rint ''select avg(score) as 平均成績(jī)from t_scorewhere left(s_number,6)='B04511'and c_number='10010218'end2根據(jù)t_score表中B04511班的考試成績(jī)，并根據(jù)考試分?jǐn)?shù)輸出考試等級(jí)。當(dāng)分?jǐn)?shù)大于等于90分，輸出“優(yōu)”；當(dāng)分?jǐn)?shù)在80-90之間，輸出“良”；當(dāng)分?jǐn)?shù)在70至80之間，輸出“中”；當(dāng)分?jǐn)?shù)在60至70之間，輸出“及格”；當(dāng)分?jǐn)?shù)在60分以下，輸出“不及格”。利用case語(yǔ)句編寫程序?qū)崿F(xiàn)以上功能。（參考教材P129例6-34,不需要輸出課程名稱）se

20、lect s_number as 學(xué)號(hào),考試等級(jí)=casewhen score>=90 then '優(yōu)'when score>=80 then '良'when score>=70 then '中'when score>=60 then '及格'else'不及格'endfrom t_scorewhere left(s_number,6)='B04511'order by s_number三、思考題1、全局變量和局部變量有什么區(qū)別？答：局部變量必須以標(biāo)記作為前綴 ，局部變量的使用也是先聲明，再賦值 全局變量必須以標(biāo)記作為前綴，全局變量由系統(tǒng)定義和維護(hù)，我們只能讀取，不能修改全局變量的值 2、說(shuō)明下列字符串函數(shù)的作用：ltrim() rtrim() lower() upper() replicate() reverse() stuff()substring() str() len()答：ltrim()刪除數(shù)據(jù)前面的空格rtrim() 去掉字符串后面的空格 lower() 轉(zhuǎn)換成小寫字母upper() 把給定的字符串變成大寫字母 replicate()按照給定的次數(shù)，重復(fù)表達(dá)式的值 reverse(