数据库期末复习资料(sql语句,触发器,存储过程).doc

《数据库期末复习资料(sql语句,触发器,存储过程).doc》由会员分享，可在线阅读，更多相关《数据库期末复习资料(sql语句,触发器,存储过程).doc（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Sql语言1、查询“00001”课程比“00002”课程成绩高的所有学生的学号select a.StuID as 学号from (select StuID,StuScores from Scores where CourseId=00001) a , (select StuID,StuScores from Scores where CourseId=00002) bwhere a.StuScoresb.StuScores and a.StuID=b.StuID select T1.StuID 学号 -使用自表连接from (select StuID,StuScores from Scores

2、 where CourseId = 00001) T1inner join (select StuID,StuScores from Scores where CourseId = 00002) T2on T1.StuID = T2.StuIDwhere T1.StuScoresT2.StuScores-2、查询平均成绩大于60分的学号和平均成绩select StuID as 学号,avg(StuScores) as 成绩from Scoresgroup by StuID having avg(StuScores)60 -3、查询所有同学的学号，姓名，选课数，总成绩select Student

3、s.StuID as 学号,Students.StuName as 姓名,count(Scores.CourseId) as 选课总数,sum(StuScores) as 总分from Students left join Scores on Students.StuID=Scores.StuIDgroup by Students.StuID,StuName -4、查询姓“易”的老师个数select COUNT(distinct(Teacher) as 个数from Coursewhere Teacher like 易%-5、查询没学过“易文龙”老师课的同学的学号，姓名select Stude

4、nts.StuID as 学号,Students.StuName as 姓名from Studentswhere StuID not in (select distinct(Scores.StuID) from Scores,Course where Scores.CourseId=Course.CourseId and Teacher=易文龙)-6、查询学过“00001”并且也学过编号“00002”课程的同学的学号，姓名select a.StuID as 学号,StuName as 姓名from Students a,Scores b,(select * from Scores where

5、courseId = 00001) cwhere b.CourseId=00002 and a.StuID = b.StuID and a.StuID = c.StuID-7、查询学过“易文龙”老师所教的所有课程同学的学号，姓名select StuID as 学号,StuName as 姓名 from Students awhere not Exists(select * from Course b where Teacher = 易文龙 and not Exists (select * from Scores c where c.StuID=a.StuID and c.CourseId=b.

6、CourseId)-8、查询课程编号“00002”的成绩比课程编号“00001”课程低的所有同学的学号，姓名select a.StuID as 学号,a.StuName as 姓名from (select Scores.StuID,StuScores,Students.StuName from Scores inner join Students on Scores.StuID = Students.StuID and CourseId=00001) a , (select StuID,StuScores from Scores where CourseId=00002) bwhere a.S

7、tuScoresb.StuScores and a.StuID=b.StuID -9、查询所有课程成绩小于60分的同学学号和姓名select StuID as 学号,avg(StuScores) as 平均分from Scoresgroup by StuID having avg(StuScores)60;select T1.StuID,StuNamefrom (select StuID from Scores group by StuID having MAX(StuScores)=60) T1inner join Students T2 on T1.StuID = T2.StuID -10

8、、查询没有学过所有课的同学的学号，姓名select Students.StuID as 学号,Students.StuName as 姓名from Students,Scoreswhere Students.StuID=Scores.StuIDgroup by Students.StuID,Students.StuName having count(CourseId)(select count(CourseId)from Course)-会漏掉未选修课的学生，应该使用以下方法select T2.*from(select StuID from Scores group by StuID havi

9、ng COUNT(*) = (select COUNT(*) from Course) T1right join Students T2 on T1.StuID=T2.StuIDwhere T1.StuID is null-11、查询至少学过“王丽秋”同学所学一门课的其他同学学号和姓名select Students.StuID as 学号,StuName as 姓名from Students,Scoreswhere Students.StuID=Scores.StuID and Students.StuName != 王丽秋 and CourseId in (select CourseId f

10、rom Scores inner join Students on Students.StuID = Scores.StuID where StuName = 王丽秋)存储过程：-存储过程(不带参数)create procedure up_GetAllComputerasselect * from Computergo-带参数的存储过程create procedure up_GetComputerByCondition(condition nvarchar(100)asdeclare sql nvarchar(max)set sql=select * from Computer where +

11、condition exec(sql)exec up_GetComputerByCondition style=华exec up_GetAllComputer触发器：-为课程表建立插入触发器if OBJECT_ID(tgr_Course_insert,tr) is not null drop trigger tgr_Course_insertgocreate trigger tgr_Course_inserton Coursefor insertasdeclare name nvarchar(10),number intselect name=CourseName from inserteds

12、elect number=count(*) from Coursewhere CourseName=nameif number1rollback tran-回滚事务go-对学生建立一个插入触发器，不允许出现相同的邮箱if OBJECT_ID(tgr_Students_insert,tr) is not null drop trigger tgr_Students_insertGOcreate trigger tgr_Students_inserton Students for insertasdeclare email varchar(50),number intselect email=St

13、uEmail from insertedselect number=count(*) from Students where StuEmail=emailif number1rollback trango-对课程表建立修改触发器if OBJECT_ID(tgr_Course_update,tr) is not nulldrop trigger tgr_Course_updategocreate trigger tgr_Course_update on Course for updateasDECLARE name nvarchar(20),number intselect name=Cours

14、eName from insertedselect number=count(*) from Coursewhere CourseName=nameif number1rollback trango-通过触发器阻止对学生表学号列的修改(列级修改触发器)if OBJECT_ID(tgr_Students_update,tr) is not nulldrop trigger tgr_Students_updategocreate trigger tgr_Students_updateon Students for updateasif update(StuId)rollback trango-删除

15、触发器if OBJECT_ID(tgr_Course_delete,tr) is not nulldrop trigger tgr_Course_deletegocreate trigger tgr_Course_deleteon Course for deleteas if object_id(backupTable,u) is not nullinsert into backupTable select * from deletedelseselect * into backupTable from deletedgoupdate Course setCourseName=物理 where

16、 CourseId=00001insert into Course(CourseId,CourseName,Teacher)values(00009,化学,王二麻子)select * from Courseselect * from Scoresselect * from Studentsdelete from Course WHERE CourseId=00008update Students set StuId= WHERE StuId=select * from backupTable相关函数使用：declare age intdeclare name nvarchar(10)set a

17、ge=10set name= 张三abcdeprint 我的年龄是:+convert(varchar(10),age)print 我的年龄是:+cast(age as varchar(10)print rtrim(ltrim(name)print nameprint charindex(张三,name)print right(name,len(name)-charindex(a,name)+1)print replace(name,张三,李四)print year(getdate()print datediff(dd,2010-10-01,getdate()update Students se

18、t StuBronDate=dateadd(yy,10,StuBornDate)where StuName=王丽秋print datename(dw,getdate()update abcde set pwd=replace(REPLACE(pwd,o,0),i,1)select ListNumberfrom exampleorder by LEFT(ListNumber,CHARINDEX(-,ListNumber)-1),RIGHT(ListNumber,LEN(ListNumber)-CHARINDEX(-,ListNumber)declare StuId CHAR(8),StuName nvarchar(5)declare temp cursor forselect StuId,StuName from Studentsopen tempfetch next from temp into StuId,StuNamewhile FETCH_STATUS=0beginselect StuId,StuNamefetch next from temp into StuId,StuNameendclose tempdeallocate temp