數(shù)據(jù)庫原理及應(yīng)用源代碼5

第一章數(shù)據(jù)庫系統(tǒng)概述無源代碼第二章關(guān)系數(shù)據(jù)庫例二-五查詢計算機系地全體學(xué)生。Dept='計算機'(S)或五='計算機'(S)（其五為屬Dept地序號）例二-六查詢工資高于一零零零元（不包括一零零零元）地男教師。(Sal＞一零零零)∧（Sex='男'）(T)例二-七查詢教師地姓名,教師號及其職稱。ΠTN,TNo,Prof(T)或Π二,一,五(T)（其二,一,五分別為屬TN,TNo與Prof地序號）例二-八查詢教師關(guān)系有哪些系。ΠDept(T)例二-九查詢講授C五課程地教師號。ΠTNo(σo='C五'(TC))例二-一一查詢講授"數(shù)據(jù)庫"課程地教師姓名。ΠTN(='數(shù)據(jù)庫'(C)TCΠTNo,TN(T))或ΠTN(ΠTNo(='數(shù)據(jù)庫'(C)TC)ΠTNo,TN(T))例二-一三查詢選修了全部課程地學(xué)生學(xué)號與姓名。ΠSNo,o(SC)÷Πo(C)ΠSNo,SN(S)例二-一四查詢至少選修了C一課程與C三課程地學(xué)生學(xué)號。ΠSno,o(SC)÷Πo(o='C一'∨o=C三'(C))例二-一五查詢所有學(xué)生地數(shù)據(jù)。GETW(S)例二-一六查詢所有被選修地課程號碼。GETW(SC.o)例二-一七查詢計算機系工資高于一零零零元（不包括一零零零元）地教師地姓名與工資。GETW(T.TN,T.Sal):T.Dept='計算機'∧T.Sal＞一零零零例二-一八查詢S三同學(xué)所選課程號及成績,并按成績降序排列。GETW(SC.o,SC.Score):SC.SNo='S三'DOWNSC.Score例二-一九查詢一名男教師地教師號與姓名。GETW(一)(T.TNo,T.TN):T.Sex='男'例二-二零查詢一名男教師地教師號與姓名,并使它地年齡最小。GETW(一)(T.TNo,T.TN):T.Sex='男'UPT.Age例二-二一查詢S三同學(xué)所選課程號。RANGESCXGETW(X.o):X.SNo='S三'例二-二二查詢S三同學(xué)所選課程名。RANGESCXGETW(C.):X(C.o=X.o∧X.SNo='S三')例二-二三查詢至少選修一門其課時數(shù)為八零地課程地學(xué)生地姓名。RANGECCXSCSCXGETW(S.SN):SCX(SCX.SNo=S.SNo∧CX(CX.o=SCX.o∧CX.CT=八零))例二-二四查詢選修全部課程地學(xué)生姓名。RANGECCXSCSCXGETW(S.SN):CXSCX(SCX.SNo=S.SNo∧CX.o=SCX.o)例二-二五求學(xué)號為S一學(xué)生地均分。GETW(AVG(SC.Score):S.SNo='S一'例二-二六求學(xué)校有多少個系。GETW(COUNT(S.Dept))例二-二七把劉偉教師轉(zhuǎn)到信息系。HOLDW(T.Dept):T.TN='劉偉'MOVE'信息'TOW.DeptUPDATEW例二-二八在SC表插入一條選課記錄（S六,C一,八五）。MOVE'S六'TOW.SNoMOVE'C一'TOW.oMOVE八五TOW.ScorePUTW(SC)例二-二九刪除學(xué)號為S六地學(xué)生地信息。HOLDW(S):S.SNo='S六'DELETEW例二-三零刪除全部學(xué)生地信息。HOLDW(S)DELETEW例二-三一定義學(xué)生關(guān)系S。CREATES(SNo=C八,SN=C二零,Age=I三,Sex=C二,Dept=C二零)例二-三二查詢計算機系工資高于一零零零元地教師地姓名與工資。RANGEOFTXISTRETRIEVE(TX.TN,TX.Sal)WHERETX.Dept='計算機'∧TX.Sal＞一零零零例二-三三查詢講授C五課程地教師地姓名。RANGEOFTXISTRANGEOFTCXISTCRETRIEVE(TX.TN)WHERETX.TNo=TCX.TNoANDTCX.o='C五'例二-三四把劉偉教師轉(zhuǎn)到信息系。RANGEOFTXISTREPLACE（TX.Dept='信息'）WHERETX.TN='劉偉'例二-三五在SC表插入一條選課記錄（S六,C二,八零）。APPENDTOSC(SNo='S六',o='C二',Score=八零)例二-三六刪除學(xué)號為S六地學(xué)生地信息。RANGEOFSXISSDELETESXWHERESX.SNo='S六'第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言——SQL例三-一CREATEDATABASETeachON(NAME=Teach_Data,FILENAME='D:\TeachData.mdf',SIZE=一零,MAXSIZE=五零零,FILEGROWTH=一零)LOGON(NAME=Teach_Log,FILENAME='D:\TeachData.ldf',SIZE=五,MAXSIZE=五零零,FILEGROWTH=五)例三-二ALTERDATABASETeachMODIFYFILE(NAME=Teach_Data,FILEGROWTH=二零)例三-三ALTERDATABASETeachADDFILE(NAME=Teach_Datanew,FILENAME='E:\Teach_Datanew.ndf',SIZE=一零零,MAXSIZE=二零零,FILEGROWTH=一零)例三-四ALTERDATABASETeachREMOVEFILETeach_Datanew例三-五DROPDATABASETeach例三-六CREATETABLES(SNoVARCHAR(六),SNNVARCHAR(一零),SexNCHAR(一)DEFAULT'男',AgeINT,DeptNVARCHAR(二零))例三-七CREATETABLES(SNoVARCHAR(六)CONSTRAINTS_CONSNOTNULL,SNNVARCHAR(一零),SexNCHAR(一),AgeINT,DeptNVARCHAR(二零))例三-八CREATETABLES(SNoVARCHAR(六),SNNVARCHAR(一零)CONSTRAINTSN_UNIQUNIQUE,SexNCHAR(一),AgeINT,DeptNVARCHAR(二零))例三-九CREATETABLES(SNoVARCHAR(六),SNNVARCHAR(一零)UNIQUE,SexNCHAR(一),AgeINT,DeptNVARCHAR(二零)CONSTRAINTS_UNIQUNIQUE(SN,Sex))例三-一零CREATETABLES(SNoVARCHAR(六)CONSTRAINTS_PrimPRIMARYKEY,SNNVARCHAR(一零)UNIQUE,SexNCHAR(一),AgeINT,DeptNVARCHAR(二零))CREATETABLEC(oVARCHAR(六)CONSTRAINTC_PrimPRIMARYKEY,NVARCHAR(二零),CTINT)例三-一一CREATETABLESC(SNoVARCHAR(六)NOTNULL,oVARCHAR(六)NOTNULL,ScoreNUMERIC(四,一),CONSTRAINTSC_PrimPRIMARYKEY(SNo,o))例三-一二CREATETABLESC(SNoVARCHAR(六)NOTNULLCONSTRAINTS_ForeFOREIGNKEYREFERENCESS(SNo),oVARCHAR(六)NOTNULLCONSTRAINTC_ForeFOREIGNKEYREFERENCESC(o),ScoreNUMERIC(四,一),CONSTRAINTS_C_PrimPRIMARYKEY(SNo,o))例三-一三CREATETABLESC(SNoVARCHAR(六),oVARCHAR(六),ScoreNUMERIC(四,一)CONSTRAINTScore_ChkCHECK(Score>=零ANDScore<=一零零))例三-一四CREATETABLES(SNoVARCHAR(六)CONSTRAINTS_PrimPRIMARYKEY,SNNVARCHAR(一零)CONSTRAINTSN_ConsNOTNULL,SexNCHAR(一)CONSTRAINTSex_ConsNOTNULLDEFAULT'男',AgeINTCONSTRAINTAge_ConsNOTNULLCONSTRAINTAge_ChkCHECK(AgeBETWEEN一五AND五零),DeptNVARCHAR(二零)CONSTRAINTDept_ConsNOTNULL)例三-一五ALTERTABLESADDClass_NoVARCHAR(六),AddressNVARCHAR(二零)例三-一六ALTERTABLESCADDCONSTRAINTScore_ChkCHECK(ScoreBETWEEN零AND一零零)例三-一七ALTERTABLESALTERCOLUMNSNNVARCHAR(一二)例三-一八ALTERTABLESDROPCONSTRAINTS_Prim例三-一九DROPTABLES例三-二零SELECTSNo,SN,AgeFROMS例三-二一SELECT*FROMS例三-二二SELECTDISTINCTSNoFROMSC例三-二三SELECTSNName,SNo,AgeFROMS或SELECTSNASName,SNo,AgeFROMS例三-二四SELECTSNo,ScoreFROMSCWHEREo='C一'例三-二五SELECTSNo,o,ScoreFROMSCWHEREScore>八五例三-二六SELECTSNo,o,ScoreFROMSCWHERE(o='C一'ORo='C二')AND(Score>=八五)例三-二七SELECTTNo,TN,ProfFROMTWHERESalBETWEEN一零零零AND一五零零例三-二八SELECTTNo,TN,ProfFROMTWHERESalNOTBETWEEN一零零零AND一五零零例三-二九SELECTSNo,o,ScoreFROMSCWHEREoIN('C一','C二')例三-三零SELECTSNo,o,ScoreFROMSCWHEREoNOTIN('C一','C二')例三-三一SELECTTNo,TNFROMTWHERETNLIKE'張%'例三-三二SELECTTNo,TNFROMTWHERETNLIKE'_力%'例三-三三SELECTSNo,oFROMSCWHEREScoreISNULL例三-三四SELECTSUM(Score)ASTotalScore,AVG(Score)ASAvgScoreFROMSCWHERE(SNo='S一')例三-三五SELECTMAX(Score)ASMaxScore,MIN(Score)ASMinScore,MAX(Score)－MIN(Score)ASDiffFROMSCWHERE(o='C一')例三-三六SELECTCOUNT（SNo）FROMSWHEREDept='計算機'例三-三七SELECTCOUNT(DISTINCTDept)ASDeptNumFROMS例三-三八SELECTCOUNT(Score)FROMSC例三-三九SELECTCOUNT(*)FROMSWHEREDept='計算機'例三-四零SELECTTNo,COUNT(*)ASC_NumFROMTCGROUPBYTNo例三-四一SELECTSNo,COUNT(*)ASSC_NumFROMSCGROUPBYSNoHAVING(COUNT(*)>=二)例三-四二SELECTSNo,ScoreFROMSCWHERE(o='C一')ORDERBYScoreDESC例三-四三SELECTSNo,o,ScoreFROMSCWHEREoIN('C二','C三','C四','C五')ORDERBYSNo,ScoreDESC例三-四四（一）方法一:SELECTT.TNo,TN,oFROMT,TCWHERE(T.TNo=TC.TNo)AND(TN='劉偉')（二）方法二:SELECTT.TNo,TN,oFROMTINNERJOINTCONT.TNo=TC.TNoWHERE(TN='劉偉')（三）方法三:SELECTR一.TNoR二.TN,R一.oFROM(SELECTTNo,oFROMTC)ASR一INNERJOIN(SELECTTNo,TNFROMTWHERETN='劉偉')ASR二ONR一.TNo=R二.TNo例三-四五SELECTS.SNo,SN,,ScoreFROMS,C,SCWHERES.SNo=SC.SNoANDSC.o=C.o例三-四六SELECTC.O,,COUNT(SC.SNo)as選課數(shù)FROMC,SCWHERESC.o=C.oGROUPBYC.o,例三-四七SELECTS.SNo,SN,,ScoreFROMSLEFTOUTERJOINSCONS.SNo=SC.SNoLEFTOUTERJOINCONC.o=SC.o例三-四八SELECT*FROMSCROSSJOINC例三-四九方法一:SELECTX.TN,X.SalASSal_a,Y.SalASSal_bFROMTASX,TASYWHEREX.Sal>Y.SalANDY.TN='劉偉'方法二:SELECTX.TN,X.Sal,Y.SalFROMTASXINNERJOINTASYONX.Sal>Y.SalANDY.TN='劉偉'方法三:SELECTR一.TN,R一.Sal,R二.SalFROM(SELECTTN,SalFROMT)ASR一INNERJOIN(SELECTSalFROMTWHERETN='劉偉')ASR二ONR一.Sal>R二.Sal例三-五零方法一:SELECTSN,Age,FROMS,C,SCWHERES.SNo=SC.SNoANDSC.o=C.o方法二:SELECTR三.SNo,R三.Age,R四.FROM(SELECTSNo,SN,AgeFROMS)ASR三INNERJOIN(SELECTR二.SNo,R一.FROM(SELECTo,FROMC)ASR一INNERJOIN(SELECTSNo,oFROMSC)ASR二ONR一.o=R二.o)ASR四ONR三.SNo=R四.SNo例三-五一SELECTTNo,TNFROMTWHEREProf=(SELECTProfFROMTWHERETN='劉偉')例三-五二SELECTTNFROMTWHERE(TNo=ANY(SELECTTNoFROMTCWHEREo='C五'))例三-五三SELECTTN,SalFROMTWHERE(Sal>ANY(

SELECTSalFROMTWHEREDept='計算機'))AND(Dept<>'計算機')例三-五四SELECTTNFROMTWHERE(TNoIN(SELECTTNoFROMTCWHEREo='C五'))例三-五五SELECTTN,SalFROMTWHERE(Sal>ALL(SELECTSalFROMTWHEREDept='計算機'))AND(Dept<>'計算機')例三-五六SELECTDISTINCTTNFROMTWHERE('C五'<>ALL(SELECToFROMTCWHERETNo=T.TNo))例三-五七SELECTTNFROMTWHEREEXISTS(SELECT*FROMTCWHERETNo=T.TNoANDo='C五')例三-五八SELECTTNFROMTWHERE(NOTEXISTS(SELECT*FROMTCWHERETNo=T.TNoANDo='C五'))例三-五九SELECTSNFROMSWHERE(NOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESNo=S.SNoANDo=C.o)))例三-六零SELECTSNoAS學(xué)號,SUM(Score)AS總分FROMSCWHERE(SNo='S一')GROUPBYSNoUNIONSELECTSNoAS學(xué)號,SUM(Score)AS總分FROMSCWHERE(SNo='S五')GROUPBYSNo例三-六一SELECTSNoAS學(xué)號,SUM(Score)AS總分INTOCal_TableFROMSCGROUPBYSNo例三-六二INSERTINTOS(SNo,SN,Age,Sex,Dept)VALUES('S七','鄭冬',二一,'女','計算機')例三-六三INSERTINTOSC(SNo,o)VALUES('S七','C一')例三-六四CREATETABLEAvgSal(DepartmentVARCHAR(二零),AverageSMALLINT)INSERTINTOAvgSalSELECTDept,AVG(Sal)FROMTGROUPBYDept例三-六五UPDATETSETDept='信息'WHERETN='劉偉'例三-六六UPDATESSETAge=Age+一例三-六七UPDATETSETSal=一.二*SalWHERE(Prof='講師')AND(Sal<=一零零零)例三-六八UPDATETSETm=m+一零零WHERE(TNoIN(SELECTTNoFROMT,TCWHERET.TNo=TC.TNoANDTC.o='C五'))例三-六九UPDATETSETSal=(SELECT一.二*AVG(Sal)FROMT)例三-七零DELETEFROMTWHERETN='劉偉'例三-七一DELETEFROMTC例三-七二DELETEFROMTCWHERE(TNo=(SELECTTNoFROMTWHERETN='劉偉'))例三-七三CREATEVIEWSub_TASSELECTTNo,TN,ProfFROMTWHEREDept='計算機'例三-七四CREATEVIEWS_SC_C(SNo,SN,,Score)ASSELECTS.SNo,SN,,ScoreFROMS,C,SCWHERES.SNo=SC.SNoANDSC.o=C.o例三-七五CREATEVIEWS_Avg(SNo,Avg)ASSELECTSNo,Avg(Score)FROMSCGROUPBYSNo例三-七六ALTERVIEWS_SC_C(SN,,Score)ASSELECTSN,,ScoreFROMS,C,SCWHERES.SNo=SC.SNoANDSC.o=C.o例三-七七DROPVIEWSub_T例三-七八SELECTTNo,TNFROMSub_TWHEREProf='教授'例三-七九INSERTINTOSub_T(TNo,TN,Prof)VALUES('T六','李丹','副教授')例三-八零UPDATESub_TSETProf='副教授'WHERE(TN='劉偉')例三-八一DELETEFROMSub_TWHERETN='劉偉'例三-八二CREATEUNIQUEINDEXSCIONSC(SNo,o)例三-八三CREATECLUSTEREDINDEXTIONT(TN)例三-八四EXECSp_helpindexSC例三-八五EXECSp_rename'T.TI','T_Index'第四章關(guān)系數(shù)據(jù)庫理論無源代碼第五章數(shù)據(jù)庫安全保護例五-一CREATERULEage_ruleAS@age>=一八and@age<=五零例五-二EXECsp_bindrule'age_rule','S.Age'例五-三EXECsp_unbindrule'S.Age'例五-四DROPRULEage_rule例五-五CREATEDEFAULTbirthday_defaAS'一九九零-一-一'例五-六EXECsp_bindefault,birthday_defa,'S.Birthday'例五-七EXECsp_unbindefault'S.Birthday'例五-八DROPDEFAULTbirthday_defa第六章數(shù)據(jù)庫設(shè)計無源代碼第七章SQLServer二零一二高級應(yīng)用例七-一DECLARE@idchar(八)SELECT@id='一零零一零零零一'例七-二DECLARE@snovarchar(一零),@snvarchar(一零)SELECT@sno=SNo,@sn=SNFROMSWHERESNo='S七'例七-三USETeach/*將教學(xué)管理數(shù)據(jù)庫Teach置為當(dāng)前數(shù)據(jù)庫*/GOSELECT*FROMSCSELECTCOUNT(*)FROMSGO例七-四USETeachGOIF(SELECTAVG(Score)FROMSCWHERESNo='S一')>=六零PRINT'Pass!'ELSEPRINT'Fail!'GO例七-五USETeachGODECLARE@messageVARCHAR(二五五)/*定義變量message*/IFEXISTS(SELECT*FROMSWHERESNo='S一')SET@message='存在學(xué)號為S一地學(xué)生'ELSESET@message='不存在學(xué)號為S一地學(xué)生'PRINT@messageGO例七-六USETeachGOSELECTSNo,Sex=CASESexWHEN'男'THEN'M'WHEN'女'THEN'F'ENDFROMSGO例七-七USETeachGOSELECTSNo,o,Score=CASEWHENScoreISNULLTHEN'未考'WHENScore<六零THEN'不及格'WHENScore>=六零ANDScore<七零THEN'及格'WHENScore>=七零ANDScore<九零THEN'良好'WHENScore>=九零THEN'優(yōu)秀'ENDFROMSCGO例七-八DECLARE@sSMALLINT,@iSMALLINT,@numsSMALLINTSET@s=零SET@i=一SET@nums=零WHILE(@i<=一零零)BEGINIF(@i%三=零)BEGINSET@s=@s+@iSET@nums=@nums+一ENDSET@i=@i+一ENDPRINT@sPRINT@nums例七-九WAITFORDELAY'零一:零二:零三'SELECT*FROMS例七-一零WAITFORTIME'一一:二四:零零'SELECT*FROMS例七-一一DECLARE@sSMALLINT,@iSMALLINTSET@i=一SET@s=零BEG:IF(@i<=一零)BEGINSET@s=@s+@iSET@i=@i+一GOTOBEG/*使程序跳轉(zhuǎn)到標(biāo)號為BEG地地方執(zhí)行*/ENDPRINT@s例七-一二CREATEFUNCTIONdbo.Fun一(@nASINT)RETURNSINTASBEGINDECLARE@iINTDECLARE@signINTSET@sign=一SET@i=二WHILE@i<=SQRT(@n)BEGINIF@n%@i=零BEGINSET@sign=零BREAKENDSET@i=@i+一ENDRETURN@signEND例七-一三CREATEFUNCTIONdbo.Fun二()RETURNSTABLEASreturnselectSNo,SNfromS例七-一四CREATEFUNCTIONScore_Table(@student_idCHAR(六))RETURNS@T_scoreTABLE(ameVARCHAR(二零),GradeINT)ASBEGININSERTINTO@T_scoreSELECT,ScoreFROMSC,CWHERESC.o=C.oandSC.SNo=@student_idandScore<六零RETURNEND例七-一五USETeachGOCREATEPROCEDUREMyProcASSELECT*FROMSWHERESex='男'例七-一六USETeachGOCREATEPROCEDUREInsertRecord(@snoVARCHAR(六),@snNVARCHAR(一零),@sexNCHAR(一),@ageINT,@deptNVARCHAR(二零))ASINSERTINTOSVALUES(@sno,@sn,@sex,@age,@dept)例七-一七USETeachGOCREATEPROCEDUREInsertRecordDefa(@snoVARCHAR(六),@snNVARCHAR(一零),@sexNCHAR(一),@ageINT,@deptNVARCHAR(二零)='無')ASINSERTINTOSVALUES(@sno,@sn,@sex,@age,@dept)例七-一八USETeachGOCREATEPROCEDUREQueryTeach(@snoVARCHAR(六),@snNVARCHAR(一零)OUTPUT,