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 stumark
update stumark set s_subject='java编程' where s_id between 0001 and 0011
update 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_mark
from students
inner join stumark
on students.s_id=stumark.s_id

//同上,表用别名
select s.s_id,s.s_name,k.s_subject,k.s_mark
from students as s
inner join stumark as k
on s.s_id=k.s_id
//实验一种被淘汰的自联接查找方法(也可以,但不应这样子写的方法)
select s.s_id,s.s_name,k.s_subject,k.s_mark
from students s,stumark k
where 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 students
left join stumark
on students.s_id=stumark.s_id
//右外联结students表和stumark表
select *
from students
right join stumark
on students.s_id=stumark.s_id

//多表查询之随想查询练习
//根据students中的s_id,以及stumark表中的s_id查询该学员某一课程的成绩
select s.s_id,k.s_subject,k.s_mark
from students as s
inner join stumark as k
on s.s_id=k.s_id

//在course表中插入数据以备后用
insert into course (c_courseID,c_studentID,c_course,c_mark)
values ('01','0001','英语','89')

insert into course
select '02','0010','高数','88' union
select '03','0030','电影欣赏','93' union
select '01','0002','英语','91' 

insert into course
select '02','0012','高数','99' union
select '01','0030','英语','44' union
select '03','0010','电影欣赏','91'

use students
select * 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 s
inner join stumark as k
on k.s_id=s.s_id
inner join course as c
on 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 k
left join students as s
on k.s_id=s.s_id
right join course as c
on c.c_studentID=k.s_id

//练习使用in 命令,要使用group by 命令的话,必须把要被分到一起的数值使用聚合命令,如avg
select 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 s
inner join stumark as m
on 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 all
select * from students where s_age>20





<--------------------2009.8.24-第二阶段作业----事务----->

use students
//新建q币表qq
create table qq
(qqNum varchar(20) primary key,
 qqMoney money
)
//插入记录
insert qq
select '111111',100.0 union
select '222222',1.0
//给表中qqMoney列加约束
alter table qq add constraint ck_qqMoney check(qqMoney>=1)

//把"111111"的q币转给"222222"
begin transaction
declare @error int
set @error=0
update qq set qqMoney=qqMoney-99 where qqNum='111111'
set @error=@error+@@error
update qq set qqMoney=qqMoney+99 where qqNum='222222'
set @error=@error+@@error
if @error<>0
	begin
		print '余额不足,转账失败'
		rollback transaction
	end
else
	begin
		print '转账成功,请放心使用'
		commit transaction
	end
go
	print '查询转账结果'
	select * from qq
go

<!------阶段记录----->
//一些关键词汇
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 stuMarks
select 'S271811','s25303',96,58 union
select 'S271813','s25302',66,90 union
select 'S271816','s25301',93,82 union
select 'S271818','s25328',61,65

//对表值进行查询修改,根据如下规则对机试成绩进行反复加分,直到平均分超过85分止
//90分以上:不加分
//80-89分: 加1分
//70-79分: 加2分
//60-69分: 加3分
//60分以下:加5分
declare @mark int,@avgMark int
set @mark=0
set @avgMark=0
while(1=1)
	begin
		select @avgMark=avg(LabExam) from stuMarks
		if (@avgMark<85)
			begin	
				select @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=LabExam	where stuNo='s25328'			
			end
			else
				break
	end
go
select * 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 person
select '0001','张一','男',20,'人事部','员工管理',3000.0 union
select '0002','张二','女',26,'技术部','教学',5000.0 union
select '0003','张三','男',25,'后勤部','器械管理',3000.0 union
select '0004','王五','男',35,'管理部门','经理',8000.0 
go

select * from person

//创建面向 总经理 的视图
create view view_manager
as select * from person
go

//查看总经理视图
select * from view_manager

//创建面向 人事部长 的视图
create view view_parker
as select person.p_id,person.p_name,person.p_sex,person.p_work
from person where person.p_park='人事部'
go

//查看面向 人事部长 的视图
select * from view_parker

//创建面向 普通员工 的视图
create view view_worker
as select person.p_id,person.p_name,person.p_work,person.p_salary
from 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 stuMarks
select 'S271812','s25303',96,58 union
select 'S271813','s25302',66,90 union
select 'S271814','s25301',93,82 union
select 'S271815','s25328',61,65 union
select 'S271817','s25328',61,65 union
select 'S271818','s25328',61,65 union
select 'S271819','s25301',93,82 union
select 'S271822','s25301',93,82 
/*写一个存储过程,用于查询平均分满足及格线的,所有学员的信息,即
根据每个人的课程求出平均分然后通过传入的平均分得出符合该平均分的学员成绩。 
*/
drop proc proc_mark
/*新建存储过程*/

if exists( select * from sysobjects where name='proc_mark')			
	begin
		drop proc proc_mark 
	end
go
create procedure proc_mark
@avgmark int=60
as				
	select * from stuMarks where (writtenExam+LabExam)/2>@avgmark

go
/*查询该存储过程*/
exec proc_mark @avgmark=80

-------------------触发器练习--9.2---------------

--创建触发器，用于insert新列值
create trigger trig_stuMarks
on stuMarks for insert 
as 
	declare @stuNo char(20) 
	select @stuNo=stuNo from inserted
	if not exists(select * from studentInfo where stuNo=@stuNo)
	begin
		insert into studentInfo values(@stuNo,null,null,null)
	end
go
--如果已经存在存储过程
if exists (select * from sysobjects where name='proc_studentInfo')
	begin
		drop proc proc_studentInfo  /*删除存储过程*/
		print '成功删除存储过程：proc_studentInfo'
	end

--创建一个存储过程,
create procedure proc_studentInfo   
 @checkStuNo varchar(20)
as 
	if exists (select * from studentInfo where stuNo=@checkStuNo)
		begin
			delete from studentInfo where stuNo=@checkStuNo /*删除表中测试列*/
			print '成功删除'+@checkStuNo+'记录'
		end
	else
		print '表studentInfo中不存在学员为 '+@checkStuNo+' 的记录'

--传入一个参数，查询是否已经存在于 studentInfo表中
exec proc_studentInfo 's25330' 

select * from studentInfo

--创建触发器后，当向成绩表中插入新stuNo的记录时，触发studentInfo表，其自动添加该新stuNo学员记录
--测试触发器时，只需要重复执行下面代码
if not exists(select * from stuMarks where stuNo='s25330')
	begin
		insert into stuMarks values('S271833','s25330',80,90)
		select * from stuMarks
		select * from studentInfo
		print '触发器向studentInfo表加入一行新记录，除了stuNo，其它列默认为空值'

	end
else
	begin
		 print '表stuMarks中已经存在 s25330 的记录'
		delete from stuMarks where stuNo='s25330'
		print '成功删除所有该记录'
	end