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

DECLARE@SELECT@變量的名稱=表達(dá)式【例8.1】定義局部變量@varname,@vardepartment，并為@varname賦值“”，為@vardepartment賦值“是管理信息系的學(xué)生”。DECLARE@varnamechar(8),@vardepartmentchar(30)SET@varname=''SET@vardepartment@varname+'是管理信息系的學(xué)生SELECT@varnameas,@vardepartmentas介【例8.2】定義局部變量@var ,@varsno，并利用這些變量去查同學(xué)的與useDECLARE@var char(2@varsnochar(8)SET@var SELECT學(xué)號(hào),FROMSWHEREDECLARE@var char(2@varsnochar(8)SET@var SELECT@varsno=學(xué)號(hào)FROMSWHERE=@varSELECT@varsnoas學(xué)號(hào)SELECT@varsno=學(xué)號(hào),FROMSWHERE8.3@findUSEDECLARE@findvarchar(30)SET@find='陳%'SELECT,學(xué)號(hào),系FROMSWHERELIKE【例8.4】從S中檢索1995年1月5日后出生的學(xué)生與學(xué)號(hào)信息DECLARE@var char(2@vardatedatetimeSET@var ='女'SET@vardate80/01/05'SELECT,學(xué)號(hào),出生日期FROMWHERE and出生日期/*注釋文本*8.5SELECTdistinctSUBSTRING(,1,1)FROMS8.6DECLARE@ssSET@ss='我們是管理信息系學(xué)生SELECTx1=substring(@ss,4,5),x2DECLARE@ssset@ss='中民SELECT'我愛'+LTRIM(@ss)+RTRIM('她是我們的祖 8.751個(gè)SELECTLEFT(,1)as姓,RIGHT ,4) FROMORDERBY】SELECTtop3LOWER(課程號(hào)asLowerUPPER(課程號(hào)asUpper,REVERSE(課程號(hào))asReverseFROM【例8.9】顯示學(xué)生的和所在系，之間用逗號(hào)和2個(gè)空格分隔SELECTRTRIM()+','+SPACE(2)+LTRIM(系)as學(xué)生所在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分以上的學(xué)生的SELECT學(xué)號(hào)+''+CAST(ASvarchar(6as80分以上成績(jī)'FROMSCWHERECAST(ASchar(6LIKE或SELECT學(xué)號(hào)+''+CONVERT(varchar(6),成績(jī)as'80分以上成績(jī)'FROMSCWHERECONVERT(varchar(6),成績(jī)LIKE【例8.17】返回服務(wù)器端計(jì)算機(jī)的名稱，服務(wù)器端計(jì)算機(jī)的IDSELECTHOST_NAMEas服務(wù)器端計(jì)算機(jī)的名稱HOST_IDasID號(hào),USER_NAME()as數(shù)據(jù)庫(kù)的用戶名,DB_NAME()as數(shù)據(jù)庫(kù)的名稱8.18CONVERTGETDATEvarchar數(shù)據(jù)類型，以字符的形式器當(dāng)前的時(shí)間。PRINT今天的日期是：RTRIM(CONVERT(varchar(30),GETDATE1Transact-SQL|1IF〈SQL1|1〉[ELSE〈SQL2|2〉8.1980DECLARE@numSELECT@num=(SELECTcount(distinct學(xué)號(hào)FromWHERE成績(jī)IFSELECT@numas成績(jī)>80的人數(shù)8.207575DECLARE@text1SET@text1='75分'IF(SELECTavg(成績(jī))FROMWHERESC.課程號(hào)=C.andC.課程名='數(shù)據(jù)庫(kù)')<=75SET@text1='平均成績(jī)<=75分SELECT@text1AS8.21C036090，顯示USEDECLARE@gSET@g=(SELECTavg(成績(jī)FROMSCWHEREC03')IF(@g)<60SELECTcast(@gaschar(3))+'不及格IFSELECTcast(@gaschar(3))+'優(yōu)秀'SELECTcast(@gaschar(3))+'合格1CASECASEInput_WHENwhen_1THENresult_[WHENwhen_2THENresult_2][…n][ELSEresult_表達(dá)式2CASEWHEN1THENresult_[WHEN2THENresult_2][…n][ELSEresult_表達(dá)式【例8.22】使用CASE函數(shù)設(shè)置課程號(hào)為C01的課程的成績(jī)級(jí)別，如果學(xué)生課程成績(jī)小于60，設(shè)置類型為“不及格”；如果大于或等于90，設(shè)置類型為優(yōu)秀；其他則設(shè)USESTUDENTCOURSESELECT學(xué)號(hào),WHEN成績(jī)<60THEN不及格'WHEN成績(jī)>=90THEN'優(yōu)秀'ELSE'合格'intoFROMWHEREISNOTNULLand課程號(hào)【例8.23】使用CASE函數(shù)獲得學(xué)生選修課程名、、成績(jī)信息，并將信息存入U(xiǎn)SESTUDENTCOURSESELECT,課程名CASEWHEN'C01'THEN'數(shù)據(jù)庫(kù)'WHEN'C02'THEN'Ｃ語言'WHEN'C03'THEN'數(shù)據(jù)結(jié)構(gòu)'WHENC04'THEN計(jì)算機(jī)導(dǎo)論'WHEN'C09'THEN'操作系統(tǒng)'ELSE'NULL'END,asintoFROMWHEREISNOTNULLandS.學(xué)號(hào)=SC.【例8.24】建立視圖cgrade，要求顯示學(xué)生的學(xué)號(hào)和課程“數(shù)據(jù)結(jié)構(gòu)”的成績(jī)，如CREATEVIEWcgrade(學(xué)號(hào),成績(jī)asselectdistinctS.學(xué)號(hào),c_grade=when(selectfromwhereSC.學(xué)號(hào)=S.andSC.課程號(hào)=(select課程號(hào)fromwhereC.課程名='數(shù)據(jù)結(jié)構(gòu)'))thenCAST(SC.asCHAR(4))else'沒有成績(jī)'fromSleftouterjoinSConS.學(xué)號(hào)=SC.select課程號(hào)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計(jì)數(shù),@sumas1WHILESQL語句|}[BREAK]{SQL語句|[CONTINUE8.2660，就將成績(jī)加倍，然后選擇最高成績(jī)。如果最高成績(jī)少于或等于8080，并打印最高成績(jī)。USEWHILESELECTAVG(成績(jī)FROMsc)60UPDATESET成績(jī)=成績(jī)*2SELECTMAX(成績(jī)FROMSCIFSELECTMAX(成績(jī)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[=默認(rèn)值RETURNS[WITH<{ENCRYPTION|SCHEMABINDING}>[,…n]]RETURN】USESTUDENTCOURSECREATEFUNCTIONstudentsum(@st_snamechar(8))returnsintDECLARE@sumgradeintSELECT@sumgrade=(SELECTsum(SC.成績(jī))FROMSCWHERE學(xué)號(hào)selectfromwhere=@st_sname)GROUPBY學(xué)號(hào))RETURN【例8.29】調(diào)用標(biāo)量函數(shù)studentsum，計(jì)算同學(xué)各科成績(jī)之和。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總成績(jī)8.30studentsums_g，要求包含學(xué)生、各科成績(jī)之和。useSTUDENTCOURSECREATETABLEs_gsumgradeasdbo.studentsum()1CREATEFUNCTION[擁有者@1[AS]1[=默認(rèn)值RETURNS[WITH<{ENCRYPTION|SCHEMABINDING}>[,…n]][AS]RETURN內(nèi)嵌表CREATEviewcoursegradeviewasSELECT學(xué)號(hào),課程名,成績(jī)FROMWHERESC.=C.andC.課程名='數(shù)據(jù)結(jié)構(gòu)DECLARE@para1varchar(30)CREATEviewcoursegradeviewasSELECT學(xué)號(hào),課程名,成績(jī)FROMWHERESC.=C.andC.課程名】USESTUDENTCOURSECREATEFUNCTIONcoursegrade(@coursevarchar(30))RETURNSTABLERETURNSELECT學(xué)號(hào),課程名,FROMWHERESC.=C.andC.課程名=@course8.32selectfromcoursegrade('數(shù)據(jù)結(jié)構(gòu)1CREATEFUNCTION擁有者@1[AS]1[=默認(rèn)值]}[,…n]])RETURNS@表變量TABLE<表的屬性定義>[WITH<{ENCRYPTION|SCHEMABINDING}>[,…n]][AS]8.33course_grade，要求能夠查詢某一課程所有學(xué)生成績(jī)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】一個(gè)動(dòng)態(tài)游標(biāo)，可前后滾動(dòng)?？?DECLAREsname_cursorCURSORFORSELECT*FROMSFORUPDATEof1OPENGLOBAL|8.37sname_cursorOPENSELECT游標(biāo)數(shù)據(jù)行