数据库原理及应用源代码5

第一章数据库系统概述无源代码第二章关系数据库例二-五查询计算机系地全体学生。Dept='计算机'(S)或五='计算机'(S)（其五为属Dept地序号）例二-六查询工资高于一零零零元（不包括一零零零元）地男教师。(Sal＞一零零零)∧（Sex='男'）(T)例二-七查询教师地姓名,教师号及其职称。ΠTN,TNo,Prof(T)或Π二,一,五(T)（其二,一,五分别为属TN,TNo与Prof地序号）例二-八查询教师关系有哪些系。ΠDept(T)例二-九查询讲授C五课程地教师号。ΠTNo(σo='C五'(TC))例二-一一查询讲授"数据库"课程地教师姓名。ΠTN(='数据库'(C)TCΠTNo,TN(T))或ΠTN(ΠTNo(='数据库'(C)TC)ΠTNo,TN(T))例二-一三查询选修了全部课程地学生学号与姓名。ΠSNo,o(SC)÷Πo(C)ΠSNo,SN(S)例二-一四查询至少选修了C一课程与C三课程地学生学号。ΠSno,o(SC)÷Πo(o='C一'∨o=C三'(C))例二-一五查询所有学生地数据。GETW(S)例二-一六查询所有被选修地课程号码。GETW(SC.o)例二-一七查询计算机系工资高于一零零零元（不包括一零零零元）地教师地姓名与工资。GETW(T.TN,T.Sal):T.Dept='计算机'∧T.Sal＞一零零零例二-一八查询S三同学所选课程号及成绩,并按成绩降序排列。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三同学所选课程号。RANGESCXGETW(X.o):X.SNo='S三'例二-二二查询S三同学所选课程名。RANGESCXGETW(C.):X(C.o=X.o∧X.SNo='S三')例二-二三查询至少选修一门其课时数为八零地课程地学生地姓名。RANGECCXSCSCXGETW(S.SN):SCX(SCX.SNo=S.SNo∧CX(CX.o=SCX.o∧CX.CT=八零))例二-二四查询选修全部课程地学生姓名。RANGECCXSCSCXGETW(S.SN):CXSCX(SCX.SNo=S.SNo∧CX.o=SCX.o)例二-二五求学号为S一学生地均分。GETW(AVG(SC.Score):S.SNo='S一'例二-二六求学校有多少个系。GETW(COUNT(S.Dept))例二-二七把刘伟教师转到信息系。HOLDW(T.Dept):T.TN='刘伟'MOVE'信息'TOW.DeptUPDATEW例二-二八在SC表插入一条选课记录（S六,C一,八五）。MOVE'S六'TOW.SNoMOVE'C一'TOW.oMOVE八五TOW.ScorePUTW(SC)例二-二九删除学号为S六地学生地信息。HOLDW(S):S.SNo='S六'DELETEW例二-三零删除全部学生地信息。HOLDW(S)DELETEW例二-三一定义学生关系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五'例二-三四把刘伟教师转到信息系。RANGEOFTXISTREPLACE（TX.Dept='信息'）WHERETX.TN='刘伟'例二-三五在SC表插入一条选课记录（S六,C二,八零）。APPENDTOSC(SNo='S六',o='C二',Score=八零)例二-三六删除学号为S六地学生地信息。RANGEOFSXISSDELETESXWHERESX.SNo='S六'第三章关系数据库标准语言——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选课数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学号,SUM(Score)AS总分FROMSCWHERE(SNo='S一')GROUPBYSNoUNIONSELECTSNoAS学号,SUM(Score)AS总分FROMSCWHERE(SNo='S五')GROUPBYSNo例三-六一SELECTSNoAS学号,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'第四章关系数据库理论无源代码第五章数据库安全保护例五-一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第六章数据库设计无源代码第七章SQLServer二零一二高级应用例七-一DECLARE@idchar(八)SELECT@id='一零零一零零零一'例七-二DECLARE@snovarchar(一零),@snvarchar(一零)SELECT@sno=SNo,@sn=SNFROMSWHERESNo='S七'例七-三USETeach/*将教学管理数据库Teach置为当前数据库*/GOSELECT*FROMSCSELECTCOUNT(*)FROMSGO例七-四USETeachGOIF(SELECTAVG(Score)FROMSCWHERESNo='S一')>=六零PRINT'Pass!'ELSEPRINT'Fail!'GO例七-五USETeachGODECLARE@messageVARCHAR(二五五)/*定义变量message*/IFEXISTS(SELECT*FROMSWHERESNo='S一')SET@message='存在学号为S一地学生'ELSESET@message='不存在学号为S一地学生'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'优秀'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/*使程序跳转到标号为BEG地地方执行*/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,