SQL Server2005练习记录

//求所有学员的分数总和select sum(s_mark) from stumark//求所有学员的平均分数select avg(s_mark) as 数据库平均成绩 from stumark where s_subject='数据库'select count(*) as 及格人数 from stumark where s_mark>=60//找出不及格的人并查找出学号select s_id,s_subject,s_mark  from stumark where s_mark<60 //计算出及格的人数并按课程分组select count(*) as 不及格人数,s_subject as 科目名称 from stumark where s_mark>=60 group by s_subject //不常用的语句 having count(*)>0//计算不及格人数select count(*) from stumark where s_mark<60 group by s_subject select * from stumarkupdate stumark set s_subject='java编程' where s_id between 0001 and 0011update stumark set s_subject='html网页制作' where s_id between 0012 and 0021//内联结查询students表和stumark表select students.s_id,students.s_name,stumark.s_subject,stumark.s_markfrom studentsinner join stumarkon students.s_id=stumark.s_id//同上,表用别名select s.s_id,s.s_name,k.s_subject,k.s_markfrom students as sinner join stumark as kon s.s_id=k.s_id//实验一种被淘汰的自联接查找方法(也可以,但不应这样子写的方法)select s.s_id,s.s_name,k.s_subject,k.s_markfrom students s,stumark kwhere s.s_id=k.s_id//新建一张课程表course,用于记录不同学员的不同课程的成绩(还是空表)create table course(c_courseID varchar(20),c_studentID varchar(20),c_course varchar(20),c_mark varchar(20))//左外联结students表和stumark表select *from studentsleft join stumarkon students.s_id=stumark.s_id//右外联结students表和stumark表select *from studentsright join stumarkon students.s_id=stumark.s_id//多表查询之随想查询练习//根据students中的s_id,以及stumark表中的s_id查询该学员某一课程的成绩select s.s_id,k.s_subject,k.s_markfrom students as sinner join stumark as kon s.s_id=k.s_id//在course表中插入数据以备后用insert into course (c_courseID,c_studentID,c_course,c_mark)values ('01','0001','英语','89')insert into courseselect '02','0010','高数','88' unionselect '03','0030','电影欣赏','93' unionselect '01','0002','英语','91' insert into courseselect '02','0012','高数','99' unionselect '01','0030','英语','44' unionselect '03','0010','电影欣赏','91'use studentsselect * from course//修改表course中c_mark的数据类型,把c_mark从varchar(20) 改成int 型//update course  set c_mark convert(int,c_mark)  不能使用update语句,而应该用alter语句,正确用法如下alter table course alter column c_mark varchar(20)//按学号分组查询course表中学员的所有科目的平均成绩select c_studentID as 学号,avg(convert(int,c_mark)) as 所有科目平均成绩 from course group by c_studentID//把course表中c_studentID等于0030的学员的英语成绩分数从 44 改成 66分update course set c_mark=66 where c_studentID=0030 and c_course='英语'//查询0001号学生的英语成绩,实现三表查询select k.s_id as 学号,s.s_name as 姓名,c.c_courseID as 课程号,c.c_course as 课程名称,c.c_mark as 分数from students as sinner join stumark as kon k.s_id=s.s_idinner join course as con s.s_id=c.c_studentID and c.c_studentID='0001' and c.c_course='英语'//用另一种方法实现上面一题的要求,查询结果一样select k.s_id as 学号,s.s_name as 姓名,c.c_courseID as 课程号,c.c_course as 课程名称,c.c_mark as 分数from stumark as kleft join students as son k.s_id=s.s_idright join course as con c.c_studentID=k.s_id//练习使用in 命令,要使用group by 命令的话,必须把要被分到一起的数值使用聚合命令,如avgselect avg(convert(int,c_mark)) from course where c_course in('英语','电影欣赏') group by c_courseID//////////////////////////////////////////////////////////////////////2009-7-17下午上课的内容 <!--------   子查询!>select * from course//用子查询方式查找某学员某门课程的成绩select s_id,s_subject,s_mark from stumark where (s_id =(select s_id from students where s_id='0010'))//上一题中若要显示出子表中的列值,则应用连接查询select s.s_id,s.s_name,m.s_subject,m.s_mark from students as sinner join stumark as mon s.s_id=m.s_id and m.s_id='0010' //查询stumark表中 科目成绩 大于所有人的 java编程 课程的平均成绩的人的 名字select s_name from students where s_id in(select s_id from stumark where (s_mark>(select avg(s_mark) from stumark where s_subject='java编程')))//用联接查询实现上题要求select s_id,s_subject,s_mark from stumark where s_mark>avg(s_mark) 有错!!发现这样无法达到要求//in关键字用于判断,确定给定的值是否与子查询中的值匹配.select * from students where s_id in (select s_id from stumark where stumark.s_id=students.s_id)//同样条件的效果select * from students inner join stumark on students.s_id=stumark.s_id//使用 EXISTS 关键字引入子查询后，子查询的作用就相当于进行存在测试。//外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回 TRUE 或 FALSE 值。 select * from stumark where exists (select s_id from students where students.s_id=stumark.s_id and students.s_id='0001')//UNION并集运算将多个查询运算的结果集进行“合并”。select * from course where c_courseID='01'union allselect * from students where s_age>20<--------------------2009.8.24-第二阶段作业----事务----->use students//新建q币表qqcreate table qq(qqNum varchar(20) primary key, qqMoney money)//插入记录insert qqselect '111111',100.0 unionselect '222222',1.0//给表中qqMoney列加约束alter table qq add constraint ck_qqMoney check(qqMoney>=1)//把"111111"的q币转给"222222"begin transactiondeclare @error intset @error=0update qq set qqMoney=qqMoney-99 where qqNum='111111'set @error=@error+@@errorupdate qq set qqMoney=qqMoney+99 where qqNum='222222'set @error=@error+@@errorif @error<>0beginprint '余额不足,转账失败'rollback transactionendelsebeginprint '转账成功,请放心使用'commit transactionendgoprint '查询转账结果'select * from qqgo<!------阶段记录----->//一些关键词汇transaction //事务constraint 和 check //运用于约束的创建set implicit_transactions on //把隐性事务模式设置为打开set implicit_transactions off //把隐性事务模式设置为关闭distinct //标识列<!--------阶段记录----><!-------2009.8.25第二阶段作业-----索引,视图,T-SQL编程-------->//第一题//新建成绩表create table stuMarks(ExamNo varchar(20), stuNo varchar(20), writtenExam int, LabExam int)//向成绩表插入值insert stuMarksselect 'S271811','s25303',96,58 unionselect 'S271813','s25302',66,90 unionselect 'S271816','s25301',93,82 unionselect 'S271818','s25328',61,65//对表值进行查询修改,根据如下规则对机试成绩进行反复加分,直到平均分超过85分止//90分以上:不加分//80-89分: 加1分//70-79分: 加2分//60-69分: 加3分//60分以下:加5分declare @mark int,@avgMark intset @mark=0set @avgMark=0while(1=1)beginselect @avgMark=avg(LabExam) from stuMarksif (@avgMark<85)beginselect @mark=LabExam from stuMarks where stuNo='s25303'//对当前列进行查询修改if(@mark<60)update stuMarks set LabExam=LabExam+5 where stuNo='s25303'if(@mark between 60 and 69)update stuMarks set LabExam=LabExam+3 where stuNo='s25303'if(@mark between 70 and 79)update stuMarks set LabExam=LabExam+2 where stuNo='s25303'if(@mark between 80 and 89)update stuMarks set LabExam=LabExam+1 where stuNo='s25303'if(@mark >90)update stuMarks set LabExam=LabExam where stuNo='s25303'select @mark=LabExam from stuMarks where stuNo='s25302'//对当前列进行查询修改if(@mark<60)update stuMarks set LabExam=LabExam+5 where stuNo='s25302'if(@mark between 60 and 69)update stuMarks set LabExam=LabExam+3 where stuNo='s25302'if(@mark between 70 and 79)update stuMarks set LabExam=LabExam+2 where stuNo='s25302'if(@mark between 80 and 89)update stuMarks set LabExam=LabExam+1 where stuNo='s25302'if(@mark >90)update stuMarks set LabExam=LabExam where stuNo='s25302'select @mark=LabExam from stuMarks where stuNo='s25301'//对当前列进行查询修改if(@mark<60)update stuMarks set LabExam=LabExam+5  where stuNo='s25301'if(@mark between 60 and 69)update stuMarks set LabExam=LabExam+3  where stuNo='s25301'if(@mark between 70 and 79)update stuMarks set LabExam=LabExam+2  where stuNo='s25301'if(@mark between 80 and 89)update stuMarks set LabExam=LabExam+1  where stuNo='s25301'if(@mark >90)update stuMarks set LabExam=LabExam  where stuNo='s25301'select @mark=LabExam from stuMarks where stuNo='s25328'//对当前列进行查询修改if(@mark<60)update stuMarks set LabExam=LabExam+5 where stuNo='s25328'if(@mark between 60 and 69)update stuMarks set LabExam=LabExam+3 where stuNo='s25328'if(@mark between 70 and 79)update stuMarks set LabExam=LabExam+2 where stuNo='s25328'if(@mark between 80 and 89)update stuMarks set LabExam=LabExam+1 where stuNo='s25328'if(@mark >90)update stuMarks set LabExam=LabExamwhere stuNo='s25328'endelsebreakendgoselect * from stuMarks //查看修改后的成绩表go//第二题use students//新创建 员工信息表 create table person(p_id varchar(20), p_name varchar(20), p_sex varchar(20), p_age int, p_park varchar(20), p_work varchar(20), p_salary Money)go//插入记录insert personselect '0001','张一','男',20,'人事部','员工管理',3000.0 unionselect '0002','张二','女',26,'技术部','教学',5000.0 unionselect '0003','张三','男',25,'后勤部','器械管理',3000.0 unionselect '0004','王五','男',35,'管理部门','经理',8000.0 goselect * from person//创建面向 总经理 的视图create view view_manageras select * from persongo//查看总经理视图select * from view_manager//创建面向 人事部长 的视图create view view_parkeras select person.p_id,person.p_name,person.p_sex,person.p_workfrom person where person.p_park='人事部'go//查看面向 人事部长 的视图select * from view_parker//创建面向 普通员工 的视图create view view_workeras select person.p_id,person.p_name,person.p_work,person.p_salaryfrom person where person.p_name='张一'//查看面向 普通员工 的视图select * from view_worker/*------------2009.8.31--存储过程-------------*/use test/*新建表*/create table stuMarks(ExamNo varchar(20), stuNo varchar(20), writtenExam int, LabExam int)/*向表strMarks中多添加信息*/insert stuMarksselect 'S271812','s25303',96,58 unionselect 'S271813','s25302',66,90 unionselect 'S271814','s25301',93,82 unionselect 'S271815','s25328',61,65 unionselect 'S271817','s25328',61,65 unionselect 'S271818','s25328',61,65 unionselect 'S271819','s25301',93,82 unionselect 'S271822','s25301',93,82 /*写一个存储过程,用于查询平均分满足及格线的,所有学员的信息,即根据每个人的课程求出平均分然后通过传入的平均分得出符合该平均分的学员成绩。 */drop proc proc_mark/*新建存储过程*/if exists( select * from sysobjects where name='proc_mark')begindrop proc proc_mark endgocreate procedure proc_mark@avgmark int=60asselect * from stuMarks where (writtenExam+LabExam)/2>@avgmarkgo/*查询该存储过程*/exec proc_mark @avgmark=80-------------------触发器练习--9.2-----------------创建触发器，用于insert新列值create trigger trig_stuMarkson stuMarks for insert as declare @stuNo char(20) select @stuNo=stuNo from insertedif not exists(select * from studentInfo where stuNo=@stuNo)begininsert into studentInfo values(@stuNo,null,null,null)endgo--如果已经存在存储过程if exists (select * from sysobjects where name='proc_studentInfo')begindrop proc proc_studentInfo  /*删除存储过程*/print '成功删除存储过程：proc_studentInfo'end--创建一个存储过程,create procedure proc_studentInfo    @checkStuNo varchar(20)as if exists (select * from studentInfo where stuNo=@checkStuNo)begindelete from studentInfo where stuNo=@checkStuNo /*删除表中测试列*/print '成功删除'+@checkStuNo+'记录'endelseprint '表studentInfo中不存在学员为 '+@checkStuNo+' 的记录'--传入一个参数，查询是否已经存在于 studentInfo表中exec proc_studentInfo 's25330' select * from studentInfo--创建触发器后，当向成绩表中插入新stuNo的记录时，触发studentInfo表，其自动添加该新stuNo学员记录--测试触发器时，只需要重复执行下面代码if not exists(select * from stuMarks where stuNo='s25330')begininsert into stuMarks values('S271833','s25330',80,90)select * from stuMarksselect * from studentInfoprint '触发器向studentInfo表加入一行新记录，除了stuNo，其它列默认为空值'endelsebegin print '表stuMarks中已经存在 s25330 的记录'delete from stuMarks where stuNo='s25330'print '成功删除所有该记录'end