c#学习资料 SQL数据库使用

exec sp_renamedb 'MySchool','YourSchool'
go
if exists(select * from sysobjects where name='proc_GetAllStudent')
drop proc proc_GetAllStudent
go
create proc proc_GetAllStudent
as
select * from Student
go
exec proc_GetAllStudent
go
--封装一个自定义存储过程，包括查询学员名称、身份证号、所属年级、考试科目、考试成绩、考试时间
if exists(select * from sysobjects where name='proc_StudentInfo')
drop proc proc_StudentInfo
go
create proc proc_StudentInfo
as
select Student.StudentName,Student.IdentityCard,Grade.GradeName,Subject.SubjectName,Result.StudentResult,Result.ExamDate
from Student
inner join Grade on Student.GradeID=Grade.GradeID
inner join Result on Student.StudentNO=Result.StudentNO
inner join Subject on Subject.SubjectNo=Result.SubjectNo
go
exec proc_StudentInfo
go
--自定义及格分数线，查询考试及格的学员信息
if exists(select * from sysobjects where name='proc_GetStuResult')
drop proc proc_GetStuResult
go
create proc proc_GetStuResult
@passLine int
as
select * from Result where StudentResult >= @passLine
go
exec proc_GetStuResult @passLine=60
go
--自定义及格分数线和科目进行查询学员信息
if exists(select * from sysobjects where name='proc_GetStuResultCourse')
drop proc proc_GetStuResultCourse
go
create proc proc_GetStuResultCourse
@passLine int, --自定义分数线
@courseName varchar(20) --自定义科目
as
select StudentNo,SubjectName,StudentResult from Result
inner join Subject on Result.SubjectNo=Subject.SubjectNo
where StudentResult >= @passLine and SubjectName=@courseName
go
exec proc_GetStuResultCourse 60,'C#'
exec proc_GetStuResultCourse 60,@courseName='C#'
go
if exists(select * from sysobjects where name='proc_GetResultDefault')
drop proc proc_GetResultDefault
go
create proc proc_GetResultDefault
@passLine int = 60,@subjectName varchar(20)
as
select StudentNO,SubjectName,StudentResult from Result inner join Subject
on Result.SubjectNo=Subject.SubjectNo
where StudentResult >= @passLine and SubjectName=@subjectName
go
exec proc_GetResultDefault 'C#'
--查询某一门科目的考试成绩，并且获取及格人数
go
if exists(select * from sysobjects where name='proc_GetPassCount')
drop proc proc_GetPassCount
go
create proc proc_GetPassCount
@subjectName varchar(20),
@passCount int=6 output
as
select StudentNO,SubjectName,StudentResult from Result inner join Subject on Result.SubjectNo = Subject.SubjectNo
where Subject.SubjectName=@subjectName
--获取及格人数
select @passCount = COUNT(*) from Result inner join Subject
on Subject.SubjectNo=Result.SubjectNo
where StudentResult >= 60 and SubjectName=@subjectName
go
declare @passCount int
exec proc_GetPassCount @subjectName='C#',@passCount = @passCount output
select '考试通过的人数为:'+CONVERT(varchar(20),@passCount)
go
--自定义分页查询(Student)
--1、当前想看的页数 = 1
--2、每页显示的条数 = 2