sql server 学生成绩管理系统

最新推荐文章于 2024-05-15 21:59:16 发布

ysh9888

最新推荐文章于 2024-05-15 21:59:16 发布

阅读量1.3w

点赞数 41

本文链接：https://blog.csdn.net/qq_43620967/article/details/107438055

版权

建表

--create table

CREATE TABLE major02
(
	Mno CHAR(20) NOT NULL,		/*专业号*/
	Mname CHAR(20),				/*专业名称*/
	Mhead CHAR(20)				/*系主任*/
)
CREATE TABLE class02
(
	Mno CHAR(20)NOT NULL,		/*专业号*/
	Year CHAR(20) NOT NULL,		/*年级*/
	Cno CHAR(20) NOT NULL,		/*班级编号*/
	Chead CHAR(20)				/*班主任*/
)
CREATE TABLE Students02
(
	Sno CHAR(20) NOT NULL,	/*学生学号*/
	Sname CHAR(20),			/*学生姓名*/
	Ssex char(5),			/*学生性别*/
	Sage INT,				/*学生年龄*/
	Sscoresum INT,			/*学分总数*/
	Pname CHAR(20),			/*生源地*/
    Mno CHAR(20), 			/*专业号*/
	Year char(20),			/*入学年份*/
	Cno CHAR(20),			/*班级编号*/
    Sphone CHAR(20)         /*学生联系方式*/
)

CREATE TABLE Teachers02
(
	Tno CHAR(20) NOT NULL,	/*教师编号*/
	Tname CHAR(20),			/*教师姓名*/
	Tprofess CHAR(20),		/*教师职称*/
	Tage CHAR(20),			/*教师年龄*/
	Tsex CHAR(5),			/*教师性别*/
	Tphone char(20)			/*教师联系方式*/
)
CREATE TABLE Courses02
(
	Csno CHAR(20) NOT NULL,		/*课程编号*/
	Csname CHAR(20),			/*课程名称*/
	Term CHAR(20),				/*学期如大二上大二下*/
	Timehour INT,				/*学时*/
	checkMeThod char(20),		/*考察方式*/
	Cscredit INT				/*学分*/
)
CREATE TABLE Reports02
(
	Csno CHAR(20) NOT NULL,			/*课程编号*/
	Sno CHAR(20) NOT NULL,			/*学生学号*/
	semester CHAR(20) NOT NULL,		/*学年17-18（2）*/
	Tno CHAR(20),					/*教师编号*/
	Score INT						/*课程分数*/
	
)
CREATE TABLE CS_Class02
(
	Csno CHAR(20) NOT NULL,			/*课程编号*/
    Mno CHAR(20) NOT NULL,			/*专业号*/
	Year CHAR(20) NOT NULL,			/*年级*/
	Cno CHAR(20) NOT NULL			/*班级编号*/
)
CREATE TABLE CS_Teacher02
(
	Csno CHAR(20) NOT NULL,			/*课程编号*/
	Tno CHAR(20) NOT NULL,			/*教师编号*/
    semester CHAR(20) NOT NULL
)

完整性约束条件

1.已经执行过的约束再执行会报错
2.约束名不能取系统保留字 eg check

--限制条件
--用户完整性约束
ALTER TABLE Students02
ADD CONSTRAINT Ssex_stu CHECK
(Ssex IN('女','男'))

ALTER TABLE Teachers02
ADD CONSTRAINT Ssex_tea CHECK
(Tsex IN('女','男'))

ALTER TABLE Courses02
ADD CONSTRAINT type_course CHECK
(checkMeThod IN('考试','考查'))

ALTER TABLE Students02
ADD CONSTRAINT Stu CHECK
(Pname IN('北京市','天津市','上海市','重庆市','河北省','山西省','辽宁省','吉林省','黑龙江省','江苏省','浙江省','安徽省','福建省','江西省','山东省','河南省','湖北省','湖南省','广东省','海南省','四川省','贵州省','云南省','陕西省','甘肃省','青海省','台湾省','内蒙古自治区','广西壮族自治区','西藏自治区','宁夏回族自治区','新疆维吾尔自治区','香港特别行政区','澳门特别行政区'))


--主键
ALTER TABLE major02
ADD CONSTRAINT pk_major PRIMARY KEY(Mno)

ALTER TABLE class02
ADD CONSTRAINT pk_class PRIMARY KEY(Cno,Year,Mno)

ALTER TABLE Students02
ADD CONSTRAINT pk_Students PRIMARY KEY(Sno)

ALTER TABLE Teachers02
ADD CONSTRAINT pk_Teachers PRIMARY KEY(Tno)

ALTER TABLE Reports02
ADD CONSTRAINT pk_Reports PRIMARY KEY(Csno,Sno,semester)
ALTER TABLE Courses02
ADD CONSTRAINT pk_Courses PRIMARY KEY(Csno)
ALTER TABLE CS_Class02
ADD CONSTRAINT pk_CS_Class PRIMARY KEY(Csno,Cno,Year,Mno)
ALTER TABLE CS_Teacher02
ADD CONSTRAINT pk_CS_Teacher02 PRIMARY KEY(Csno,Tno,semester)

--外码
ALTER TABLE class02
ADD CONSTRAINT fk_class FOREIGN KEY(Mno) REFERENCES major02(Mno)

ALTER TABLE Students02
ADD CONSTRAINT fk_Students1 FOREIGN KEY(Cno,Year,Mno)  REFERENCES class02(Cno,Year,Mno) 

ALTER TABLE Reports02
ADD CONSTRAINT fk_Reports0 FOREIGN KEY(Sno) REFERENCES Students02(Sno)
ALTER TABLE Reports02
ADD CONSTRAINT fk_Reports1 FOREIGN KEY(Csno) REFERENCES Courses02(Csno)
ALTER TABLE Reports02
ADD CONSTRAINT fk_Reports2 FOREIGN KEY(Tno) REFERENCES Teachers02(Tno)

--修改外码约束
ALTER TABLE Reports02
DROP CONSTRAINT fk_Reports1
ALTER TABLE Reports02
DROP CONSTRAINT fk_Reports2
ALTER TABLE Reports02
ADD CONSTRAINT fk_Reports FOREIGN KEY(Csno,Tno,semester) REFERENCES CS_Teacher02(Csno,Tno,semester)

--外码
ALTER TABLE CS_Class02
ADD CONSTRAINT fk_CS_Class0 FOREIGN KEY(Csno) REFERENCES Courses02(Csno)
ALTER TABLE CS_Class02
ADD CONSTRAINT fk_CS_Class2 FOREIGN KEY(Cno,Year,Mno) REFERENCES class02(Cno,Year,Mno) 

ALTER TABLE CS_Teacher02
ADD CONSTRAINT fk_CS_Teacher0 FOREIGN KEY(Csno) REFERENCES Courses02(Csno)
ALTER TABLE CS_Teacher02
ADD CONSTRAINT fk_CS_Teacher1 FOREIGN KEY(Tno) REFERENCES Teachers02(Tno)

--外码
ALTER TABLE class02
ADD CONSTRAINT fk_chead FOREIGN KEY(Chead) REFERENCES Teachers02(Tname)
ALTER TABLE major02
ADD CONSTRAINT fk_mhead FOREIGN KEY(Mhead) REFERENCES Teachers02(Tname)


--唯一性
ALTER TABLE Teachers02
ADD CONSTRAINT uni_name UNIQUE(Tname)

插入数据

--插入数据
/*Teachers02*/
INSERT 
INTO Teachers02
VALUES('T15','杨丹科','教授',55,'男','145646756757')

/*=======================================================*/
/*表major02*/
INSERT
INTO major02
VALUES('M01','计算机科学与技术','杨丹科')

/*=====================================================*/
/*表Students02*/
INSERT 
INTO Students02
VALUES('S17010201','蒋力帆','男',20,87,'浙江省','M01','2017','C02','12354344435')

/*=========================================================*/
/*Courses*/

INSERT 
INTO Courses02
values('Cs06','算法分析','大二下',64,'考试',2)

/*=========================================================*/

INSERT 
INTO CS_Class02
VALUES('Cs06','M02','2016','C02')

/*=====================*/
/*CS_teacher*/
INSERT 
INTO CS_Teacher02
VALUES('Cs01','T01','16-17(2)')

创建用户表

--创建用户表
CREATE TABLE userInfo02
(ID CHAR(20),
 password CHAR(20),
 role INT
 )

 --插入用户 0   1   2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
INSERT 
INTO userInfo02
values('admin','000000',0)

视图

/*建立视图*/
CREATE VIEW classcs AS--专业选课表
--	班级编号，专业名，课程名，学期，专业号，入学年份，课程号
SELECT Cno ,Mname,Csname,major02.Mno,YEAR,CS_Class02.Csno
FROM Courses02,major02,CS_Class02
WHERE Major02.Mno=CS_Class02.Mno and Courses02.Csno=CS_Class02.Csno

CREATE VIEW teachercs3 AS--教师信息表
--      教师姓名，教师编号，性别，年龄，职称，电话，课程名，课程号，学年
SELECT Tname ,Teachers02.Tno,Tsex,Tage,Tprofess,Tphone,Csname,Courses02.Csno,semester
FROM Courses02,Teachers02,Reports02
WHERE Teachers02.Tno=Reports02.Tno and Courses02.Csno = Reports02.Csno

--去重
CREATE VIEW teachercs4 AS
SELECT distinct *
from teachercs3

标题

OVER(PARTITION BY… ORDER BY…)

PARTITION 中文是分割的意思，ORDER 是排序的意思，先把一组数据按照制定的字段进行分割成各种组，然后组内按照某个字段排序。
https://blog.csdn.net/fygkchina/article/details/80521550
用rank（）排序情况
在这里插入图片描述

/*score_view_end*/
CREATE VIEW scoreview_end--学生选课成绩表
as		--姓名，学号，专业名，课程名，学年，教师名，分数，学分
SELECT Sname ,Students02.Sno,major02.Mname,Csname,semester,Tname,Score,Cscredit
FROM Students02,Courses02,Teachers02,Reports02,major02
WHERE Students02.Sno=Reports02.Sno and  Courses02.Csno=Reports02.Csno and Teachers02.Tno=Reports02.Tno
and Students02.Mno = major02.Mno

/*calstu根据课程进修排序*/
CREATE VIEW calstu_AVG		--学生选课成绩按课程号、学年分组，分数降序排序
as		--学号，	姓名，			课程号，		课程名，		学年，	教师名，分数
select students02.sno,scoreview_end.sname,csno,scoreview_end.csname,semester,Tname,score,RANK() 
OVER(partition by csno , semester order by score desc) as rank
from scoreview_end,students02,courses02
where students02.sname=scoreview_end.sname and courses02.csname=scoreview_end.csname

/*按照课程查找某门课程平均分*/
create view AVG_CS
as
SELECT avg(score) as avg,csname,semester
from scoreview_end
group by csname, semester

存储过程

/*存储过程*/
/*插入学生表的存储过程*/
GO
CREATE PROCEDURE PRO_INSERT_STUDENTS
@Sno char(12),
@Sname char(10),
@Ssex char(6),
@Sage INT,
@Sscoresum INT,
@Pname CHAR(20),
@Mno CHAR(20),
@Year CHAR(20),
@Cno CHAR(20),
@Sphone CHAR(20)
AS BEGIN
INSERT Students02(Sno,Sname,Ssex,Sage,Sscoresum,Pname,Mno,Year,Cno,Sphone)
VALUES(@Sno,@Sname,@Ssex,@Sage,@Sscoresum,@Pname,@Mno,@Year,@Cno,@Sphone)
SELECT *
FROM Students02
end

/*插入课程表的存储过程*/
GO
CREATE PROCEDURE PRO_INSERT_Courses
@Csno char(20),
@Csname char(20),
@Term char(20),
@Timehour INT,
@checkMethod char(20),
@Cscredit INT
AS BEGIN
INSERT Courses02(Csno,Csname,Term,Timehour,checkMethod,Cscredit)
VALUES(@Csno,@Csname,@Term,@Timehour,@checkMethod,@Cscredit)
SELECT *
FROM Courses02
end

/*更新学分的存储过程*/
GO 
CREATE PROCEDURE PRO_UPDATE_CREDIT
@Csno CHAR(20),
@Sno CHAR(20),
@semester CHAR(20),
@Tno CHAR(20),
@Score CHAR(20)
AS BEGIN
     INSERT INTO Reports
     values(@Csno,@Sno,@semester,@Tno,@Score)
     if(@Score >= 60)
     update Students
     set Sscoresum = Sscoresum +
     (
	select Cscredit
        from Courses
        where Csno = @Csno
      )
      where Students02.Sno = @Sno
END

/*插入教师信息的存储过程*/
GO
CREATE PROCEDURE PRO_INSERT_TEACHERS
@Tno char(20),
@Tname char(20),
@Tprofess char(20),
@Tage INT,
@Tsex char(20),
@Tphone char(20)
AS
Begin
INSERT Teachers(Tno,Tname,Tprofess,Tage,Tsex,Tphone)
VALUES(@Tno,@Tname,@Tprofess,@Tage,@Tsex,@Tphone)
END

触发器

/*教师删除触发器*/
create trigger tri_Del 
ON Teachers02
instead of delete
as
begin
   if((select count(*)from Reports02 where 
   Tno in
   (select Tno from deleted))=0)
   begin 
     delete from Teachers02 where Tno in
	 (select Tno from deleted)
	end
end

/*学生班级修改*/
create trigger tri_updateStu 
on class02
for update
as
	if update(Cno)
	begin

	update Students02
	set Cno = i.Cno
	from Students02 tmp,deleted d ,Inserted i
	where tmp.Cno = d.Cno
	end

ysh9888

关注

41
点赞
踩
376

收藏

觉得还不错? 一键收藏
7
评论
sql server 学生成绩管理系统

建表--create tableCREATE TABLE major02( Mno CHAR(20) NOT NULL, /*专业号*/ Mname CHAR(20), /*专业名称*/ Mhead CHAR(20) /*系主任*/)CREATE TABLE class02( Mno CHAR(20)NOT NULL, /*专业号*/ Year CHAR(20) NOT NULL, /*年级*/ Cno CHAR(20) NOT NULL, /*班级编号*/
复制链接

扫一扫