项目3 利用存储过程-操作学生成绩信息表

一、目的与要求

1．利用存储过程创建库表

2．掌握排序和查询的使用

3．掌握如何调用存储过程进行带参数查询

二、内容要求

学生成绩信息三个表，结构如下： 

学生表：Member

1)查询各个学生语文、数学、英语、历史课程成绩，例如下表：

3)统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。

4)创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。

三、建表语句:

USE master

GO

/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/

--检验数据库是否存在，如果为真，删除此数据库--

IF exists(SELECT * FROM sysdatabases WHERE name='Student')

  DROP DATABASE Student

GO

CREATE DATABASE Student

GO

--建数据表--

USE Student

GO

CREATE TABLE Member  --学生表

(

  MID  char(10) primary key,  --学生号

  MName  CHAR(50) NOT NULL  --姓名

)

GO

CREATE TABLE F  --课程表

(

 FID   char(10) primary key,    --课程号

FName  CHAR(50) NOT NULL --课程名

)

GO

CREATE TABLE score  --学生成绩表

(

  SID int identity(1,1) primary key,  --成绩记录号

  FID char(10)  foreign key(FID) references F(FID) ,     --课程号

  MID char(10)  foreign key(MID) references Member(MID) ,     --学生号

  Score  int NOT NULL    --成绩

)

GO

--课程表中插入数据--

INSERT INTO F(FID,FName)VALUES('F001','语文')

INSERT INTO F(FID,FName)VALUES('F002','数学')

INSERT INTO F(FID,FName)VALUES('F003','英语')

INSERT INTO F(FID,FName)VALUES('F004','历史')

--学生表中插入数据--

INSERT INTO Member(MID,MName)VALUES('M001','张萨')

INSERT INTO Member(MID,MName)VALUES('M002','王强')

INSERT INTO Member(MID,MName)VALUES('M003','李三')

INSERT INTO Member(MID,MName)VALUES('M004','李四')

INSERT INTO Member(MID,MName)VALUES('M005','阳阳')

INSERT INTO Member(MID,MName)VALUES('M006','虎子')

INSERT INTO Member(MID,MName)VALUES('M007','夏雪')

INSERT INTO Member(MID,MName)VALUES('M008','璐璐')

INSERT INTO Member(MID,MName)VALUES('M009','珊珊')

INSERT INTO Member(MID,MName)VALUES('M010','香奈儿')

--成绩表中插入数据--

INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',78)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M001',67)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M001',)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M001',76)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M002',)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M003',70)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M003',87)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M003',92)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M003',56)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M004',80)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M004',78)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M004',97)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M004',66)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M006',88)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M006',86)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M006',79)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M007',77)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75)

"

-- 1)查询各个学生语文、数学、英语、历史课程成绩--

SELECT Member.MName AS 姓名,

英语 = SUM(CASE F.FName WHEN '语文' THEN Score.Score END), 

数学 = SUM(CASE F.FName WHEN '数学' THEN Score.Score END), 

语文 = SUM(CASE F.FName WHEN '英语' THEN Score.Score END), 

历史 = SUM(CASE F.FName WHEN '历史' THEN Score.Score END) 

FROM Score, Member,F 

WHERE F.FID = Score.FID AND Member.MID =Score.MID GROUP BY Member.MName 

-- 2)查询四门课中成绩低于70分的学生及相对应课程名和成绩--

select 姓名=(select MName from Member where MID=Score.MID),课程名=(select FName from F where FID=Score.FID),成绩=Score from Score where Score<70

-- 3)统计各个学生四课程的平均分，且按平均分数由高到底排序--

select 姓名=(select MName from Member where MID=Score.MID),平均分=Avg(Score) from Score group by MID order by 平均分 desc

-- 4)创建存储过程--

if exists (select * from sysobjects where name='P_stu')

drop procedure P_stu

GO

create procedure P_stu

@num int

As

print'参加'+convert(varchar(5),@num)+'门课考试的学生姓名及学号：'

select 姓名=(select MName from Member where MID=Score.MID),学号=MID from Score group by MID having count(*)=@num 

--调用存储过程--