三张表,学生表,课程表和选课表
use [20140319];
create tableCourse(courseIdint,courseNamechar(10),primary key (courseId));
create tableStudent(studentIdint,namechar(10),primary key (studentId));
create tableChoose(studentIdint,courseIdint, primary key (studentId,courseId),foreignkey (studentId)references Student(studentId),foreignkey (courseId)references Course(courseId));
insert intoCourse values(001,'DB');
insert intoCourse values(002,'OS');
insert intoStudent values(1001,'Tom');
insert intoStudent values(1002,'Jack');
insert intoChoose values(1001,001);
insert intoChoose values(1001,002);
insert intoChoose values(1002,001);
insert intoCourse values(003,'MATH');
insert intoChoose values(1002,002);
insert intoChoose values(1002,003);
//存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
//1. 输入课程序号输出课程名称
create procedurecName //申明存储过程
@courseNo int=001 //输入参数
as
select Course.courseName
from Course
where courseId=@courseNo
go
exec cName; //执行存储过程
//2. 输入学生学号输出选课数
create procedurecNo
@stuNo int=1001
as
begin
select COUNT(*) fromChoose where studentId=@stuNo
end
exec cNo;
//3. 增加一门课程为课程代码和课程名称
create procedureaddCourse
@CNo int=0041,
@Cdd varchar='1004'//or char
as
insert intoCourse values(@CNo,@Cdd);
go
exec addCourse
//4. 输出选择两门课的学生姓名
create procedureprintNa1
@CSu int=3
as
select Student.namefrom Student,Choosewhere Student.studentId=Choose.studentIdgroup by Student.namehaving COUNT(*)=@CSu;
go
exec printNa1;
//5. 输出选修人最多的课程
create procedureprintMax
as
select Course.courseId,courseNameas num
from Course,Choose
where Choose.courseId=Course.courseId
group byCourse.courseId,courseName
having COUNT(*)=(
select MAX(tmp.num)
from
(
select Course.courseId,courseName,COUNT(*)as num
from Course,Choose
where Choose.courseId=Course.courseId
group by Course.courseId,courseName
)
as tmp
);
go
exec printMax;
//6. 游标输出学生姓名课程数量
create procedurecurse05
as
begin
//申明游标
declare curseTest cursor
for selectStudent.name,count(*)from Choose,Studentwhere Choose.studentId=Student.studentIdgroup by Student.name;
//打开游标
open curseTest
//循环一个游标
declare @name char(10),@sumint
fetch nextfrom curseTestinto @name,@sum
while @@FETCH_STATUS=0
begin
print @name
print @sum
fetch nextfrom curseTestinto @name,@sum
end
//关闭一个游标
close curseTest
//释放游标
deallocate curseTest
end
go
exec curse05;