數(shù)據(jù)庫技術(shù)與應(yīng)用sql server2012教程源代碼

DECLARE@SELECT@變量的名稱=表達式【例8.1】定義局部變量@varname,@vardepartment，并為@varname賦值“”，為@vardepartment賦值“是管理信息系的學生”。DECLARE@varnamechar(8),@vardepartmentchar(30)SET@varname=''SET@vardepartment@varname+'是管理信息系的學生SELECT@varnameas,@vardepartmentas介【例8.2】定義局部變量@var ,@varsno，并利用這些變量去查同學的與useDECLARE@var char(2@varsnochar(8)SET@var SELECT學號,FROMSWHEREDECLARE@var char(2@varsnochar(8)SET@var SELECT@varsno=學號FROMSWHERE=@varSELECT@varsnoas學號SELECT@varsno=學號,FROMSWHERE8.3@findUSEDECLARE@findvarchar(30)SET@find='陳%'SELECT,學號,系FROMSWHERELIKE【例8.4】從S中檢索1995年1月5日后出生的學生與學號信息DECLARE@var char(2@vardatedatetimeSET@var ='女'SET@vardate80/01/05'SELECT,學號,出生日期FROMWHERE and出生日期/*注釋文本*8.5SELECTdistinctSUBSTRING(,1,1)FROMS8.6DECLARE@ssSET@ss='我們是管理信息系學生SELECTx1=substring(@ss,4,5),x2DECLARE@ssset@ss='中民SELECT'我愛'+LTRIM(@ss)+RTRIM('她是我們的祖 8.751個SELECTLEFT(,1)as姓,RIGHT ,4) FROMORDERBY】SELECTtop3LOWER(課程號asLowerUPPER(課程號asUpper,REVERSE(課程號)asReverseFROM【例8.9】顯示學生的和所在系，之間用逗號和2個空格分隔SELECTRTRIM()+','+SPACE(2)+LTRIM(系)as學生所在FROMORDERBY【例8.10】將字符串redgreenblue中的green替換成black。判斷blueredgreenblueblueSELECTSTUFF('redgreenblue',4,5,'black')SELECTCHARINDEX('blueredgreenblueas起始位置,LENblueasSELECTrand()Random_NumberSELECTexp(1),sqrt(4),abs(-5.3)SELECTround(123.123456,0),round(123.123456,2),round(123.123456,-2)SELECTabs(-1.0),abs(0.0),abs(1.0)8.112121SELECT,出生日期,DATEADD(day,21,出生日期)ASnewtimeFROMSSELECT,出生日期,DATEADD(year,21,出生日期)ASnewtimeFROMS8.12SELECT DAY格式：DAY日期8.1303/12/2008SELECTDAY('03/12/2008')AS'Day8.1403/12/2008SELECT"YearNumber"=8.1503/12/2008SELECTMONTH('03/12/2008')as'Month8.16】SCchar(10)80分以上的學生的SELECT學號+''+CAST(ASvarchar(6as80分以上成績'FROMSCWHERECAST(ASchar(6LIKE或SELECT學號+''+CONVERT(varchar(6),成績as'80分以上成績'FROMSCWHERECONVERT(varchar(6),成績LIKE【例8.17】返回服務(wù)器端計算機的名稱，服務(wù)器端計算機的IDSELECTHOST_NAMEas服務(wù)器端計算機的名稱HOST_IDasID號,USER_NAME()as數(shù)據(jù)庫的用戶名,DB_NAME()as數(shù)據(jù)庫的名稱8.18CONVERTGETDATEvarchar數(shù)據(jù)類型，以字符的形式器當前的時間。PRINT今天的日期是：RTRIM(CONVERT(varchar(30),GETDATE1Transact-SQL|1IF〈SQL1|1〉[ELSE〈SQL2|2〉8.1980DECLARE@numSELECT@num=(SELECTcount(distinct學號FromWHERE成績IFSELECT@numas成績>80的人數(shù)8.207575DECLARE@text1SET@text1='75分'IF(SELECTavg(成績)FROMWHERESC.課程號=C.andC.課程名='數(shù)據(jù)庫')<=75SET@text1='平均成績<=75分SELECT@text1AS8.21C036090，顯示USEDECLARE@gSET@g=(SELECTavg(成績FROMSCWHEREC03')IF(@g)<60SELECTcast(@gaschar(3))+'不及格IFSELECTcast(@gaschar(3))+'優(yōu)秀'SELECTcast(@gaschar(3))+'合格1CASECASEInput_WHENwhen_1THENresult_[WHENwhen_2THENresult_2][…n][ELSEresult_表達式2CASEWHEN1THENresult_[WHEN2THENresult_2][…n][ELSEresult_表達式【例8.22】使用CASE函數(shù)設(shè)置課程號為C01的課程的成績級別，如果學生課程成績小于60，設(shè)置類型為“不及格”；如果大于或等于90，設(shè)置類型為優(yōu)秀；其他則設(shè)USESTUDENTCOURSESELECT學號,WHEN成績<60THEN不及格'WHEN成績>=90THEN'優(yōu)秀'ELSE'合格'intoFROMWHEREISNOTNULLand課程號【例8.23】使用CASE函數(shù)獲得學生選修課程名、、成績信息，并將信息存入USESTUDENTCOURSESELECT,課程名CASEWHEN'C01'THEN'數(shù)據(jù)庫'WHEN'C02'THEN'Ｃ語言'WHEN'C03'THEN'數(shù)據(jù)結(jié)構(gòu)'WHENC04'THEN計算機導論'WHEN'C09'THEN'操作系統(tǒng)'ELSE'NULL'END,asintoFROMWHEREISNOTNULLandS.學號=SC.【例8.24】建立視圖cgrade，要求顯示學生的學號和課程“數(shù)據(jù)結(jié)構(gòu)”的成績，如CREATEVIEWcgrade(學號,成績asselectdistinctS.學號,c_grade=when(selectfromwhereSC.學號=S.andSC.課程號=(select課程號fromwhereC.課程名='數(shù)據(jù)結(jié)構(gòu)'))thenCAST(SC.asCHAR(4))else'沒有成績'fromSleftouterjoinSConS.學號=SC.select課程號fromCwhereC.課程名='數(shù)據(jù)結(jié)構(gòu)1GOTO …label 8.25GOTO1，2，3，4，5DECLARE@sumint,@countintSELECT@sum=0,@count=1SELECT@sum=@sum+@countSELECT@count=@count+1IF@count<=5GOTOSELECT@count-1as計數(shù),@sumas1WHILESQL語句|}[BREAK]{SQL語句|[CONTINUE8.2660，就將成績加倍，然后選擇最高成績。如果最高成績少于或等于8080，并打印最高成績。USEWHILESELECTAVG(成績FROMsc)60UPDATESET成績=成績*2SELECTMAX(成績FROMSCIFSELECTMAX(成績FROMSC)80PRINT6080分。8.27greenASCIIDECLARE@positionint,@stringchar(8)SET@position=1SETWHILE@position<=datalength(@string)SELECTASCII(SUBSTRING(@string,@position,1asASCII碼CHAR(ASCII(SUBSTRING(@string,@position,1as字母'SET@position=@position+11CREATEFUNCTION擁有者.@1[AS]1[=默認值RETURNS[WITH<{ENCRYPTION|SCHEMABINDING}>[,…n]]RETURN】USESTUDENTCOURSECREATEFUNCTIONstudentsum(@st_snamechar(8))returnsintDECLARE@sumgradeintSELECT@sumgrade=(SELECTsum(SC.成績)FROMSCWHERE學號selectfromwhere=@st_sname)GROUPBY學號)RETURN【例8.29】調(diào)用標量函數(shù)studentsum，計算同學各科成績之和。USEselectdbo.studentsum('USEDECLARE@st_gradeEXEC@st_grade=dbo.studentsum'SELECT@st_gradeasUSEexecdbo.studentsum'USEDECLARE@st_gradeEXEC@st_grade=dbo.studentsum@st_sname=''SELECT@st_gradeas總成績8.30studentsums_g，要求包含學生、各科成績之和。useSTUDENTCOURSECREATETABLEs_gsumgradeasdbo.studentsum()1CREATEFUNCTION[擁有者@1[AS]1[=默認值RETURNS[WITH<{ENCRYPTION|SCHEMABINDING}>[,…n]][AS]RETURN內(nèi)嵌表CREATEviewcoursegradeviewasSELECT學號,課程名,成績FROMWHERESC.=C.andC.課程名='數(shù)據(jù)結(jié)構(gòu)DECLARE@para1varchar(30)CREATEviewcoursegradeviewasSELECT學號,課程名,成績FROMWHERESC.=C.andC.課程名】USESTUDENTCOURSECREATEFUNCTIONcoursegrade(@coursevarchar(30))RETURNSTABLERETURNSELECT學號,課程名,FROMWHERESC.=C.andC.課程名=@course8.32selectfromcoursegrade('數(shù)據(jù)結(jié)構(gòu)1CREATEFUNCTION擁有者@1[AS]1[=默認值]}[,…n]])RETURNS@表變量TABLE<表的屬性定義>[WITH<{ENCRYPTION|SCHEMABINDING}>[,…n]][AS]8.33course_grade，要求能夠查詢某一課程所有學生成績USESTUDENTCOURSECREATEFUNCTIONcourse_grade(@coursevarchar(30))RETURNS@scoreTABLE(s_snochar(6),ameINSERTFROMWHERESC.=C.andC.課程名=@course8.34SELECTFROMcourse_grade('數(shù)據(jù)結(jié)構(gòu)8.35course_gradeDROPFUNCTION1DECLARECURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL[STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNINGFOR[FORUPDATEOF,…n【例8.36】一個動態(tài)游標，可前后滾動?？?DECLAREsname_cursorCURSORFORSELECT*FROMSFORUPDATEof1OPENGLOBAL|8.37sname_cursorOPENSELECT游標數(shù)據(jù)行