数据库考研信息管理系统

最新推荐文章于 2025-08-20 12:04:57 发布

原创最新推荐文章于 2025-08-20 12:04:57 发布 · 2.4k 阅读

25 ·

本内容遵循CC 4.0 BY-SA版权协议

考研信息管理系统

这是我自己想的一个数据库
在这里插入图片描述
关系图

分离数据库

想将数据库发给其他电脑，需要先分离
1.选中要分离的数据库
任务-分离
在这里插入图片描述
2.选中删除链接，确定

附加数据库

数据库-附加

Group By

错误

/*按照专业查找某专业报考人数*/
create view count_bkmaj
as
SELECT count(学号) as 报考人数,报考专业,报考学校,学号,姓名
from stubk
group by 报考专业

在这里插入图片描述
使用Group By子句的时候，一定要记住下面的一些规则：
（1）不能Group By非标量基元类型的列，如不能Group By text，image或bit类型的列
（2）Select指定的每一列都应该出现在Group By子句中，除非对这一列使用了聚合函数；
（3）不能Group By在表中不存在的列；
（4）进行分组前可以使用Where子句消除不满足条件的行；
（5）使用Group By子句返回的组没有特定的顺序，可以使用Order By子句指定次序。
正确

/*按照专业查找某专业报考人数*/
create view count_bkmaj
as
SELECT count(学号) as 报考人数,报考专业
from stubk
group by 报考专业

select * from count_bkmaj

Rank
dense_Rank()，如果OVER 后排序后，有（id）值相同，会排名相同，后续排名，将按照记录数排名号排下一个。例如，成绩，1个第一名（排名序号1），2个第二名分数相同（排名序号都是2），第3名（排名序号4）在这里插入图片描述
降序在order那里加 desc

CREATE VIEW rankmaj AS
select  dense_Rank()
OVER(order by 专业报考人数 DESC) as 报考人数排名 ,
* from cou_bkmaj

下面方法运行会成功，但是它分的更细，分到个人那个程度

/*按照专业查找某专业报考人数*/
create view cou_bkmaj
as
SELECT count(学号) as 报考人数,报考专业,报考学校,学号,姓名
from stubk
group by 报考专业,报考学校,学号,姓名

在这里插入图片描述

基础知识

https://blog.csdn.net/venus321/article/details/78304111
输出

declare @result varchar(50)
exec @result=del_name '王莽'
1.print(@result+'名为 '+@sname +' 的学生')
2.print '删除条数：'+ @result
3.select @result as '删除条数'

select 会覆盖 print

删除表中记录
TRUNCATE TABLE Student --所有记录
Delete * from Student --所有记录
Delete from Student where Sno=‘03’

varchar Vs nvarchar

varchar比nvarchar更省空间，应该更多的用varchar。但是我不是很赞同这个观点，首先，现在空间相对来说已经不是那么缺少了，没有必要太纠结于空间这个了，还有一点就是现在的操作系统基本上内部解析基本上都是用Unicode编码的，使用了nvarchar你可以避免每次从数据库读取或写入时候，进行编码转换，转换需要时间，并且很容易出错。如果出错，恢复回来可没那么简单，并且nvarchar是支持多种语言的。所以我大部分时间做数据库设计的时候都是用nvarchar的。

SQL SERVER 导入excel表格

数据较少，也可以，表-编辑前200行，直接复制进去

建表

/*建表*/
CREATE TABLE Student
(
	Sno nvarchar(20)   NOT NULL,	/*学号*/
	Sname nvarchar(20),				/*学生姓名*/
	Sex nvarchar(4),				/*学生性别*/
	Year char(4),					/*入学年份*/		
	Acad nvarchar(20),				/*学院*/						
	Mname nvarchar(20),			    /*专业*/
	Stel nvarchar(20),				/*电话*/
	Sremark nvarchar(50)			/*备注*/
)

CREATE TABLE Major
(
	Mno nvarchar(10) NOT NULL,		/*专业号*/
	Mname nvarchar(20),				/*专业名称*/
	Tno	nvarchar(20),				/*专业指导老师*/--学校安排考研指导老师，负责该专业考生的考研相关事物的指导
	Mhot int,						/*专业热门程度排行*/--eg 经贸第一热门1 计算机第二热门2
	Msub nvarchar(50),				/*专业考试科目*/--英数政等
	Mline int,						/*专业国家线*/
	Mremark nvarchar(50)			/*备注*/
)

CREATE TABLE Teacher
(
	Tno nvarchar(20) NOT NULL,	/*教师编号*/
	Tname nvarchar(20),			/*教师姓名*/
	Tsex nvarchar(4),			/*教师性别*/
	Tacad nvarchar(20),			/*教师学院*/
	Tphone nvarchar(20),		/*教师联系方式*/
	Tremark nvarchar(50)	
)

CREATE TABLE School
(
	Sclno nvarchar(10) NOT NULL,	/*学校编号*/
	Sclname nvarchar(20),			/*学校名*/
	Area nvarchar(10),				/*学校省份*/
	Scltel nvarchar(20),			/*学校电话*/
	Type nvarchar(20),				/*学校类型*/--985 211 双一流 普通高校
	Srank int,						/*学校排名*/
	Sclremark nvarchar(50)
)


CREATE TABLE Stu_Scl
(
	Sno nvarchar(20)   NOT NULL,	/*学号*/
	Sclno nvarchar(10) NOT NULL,	/*学校编号*/
	Mno nvarchar(10)   NOT NULL,	/*专业号*/
	Succeed bit						/*是否考上*/--默认为0 即没考上，1为考上
)

CREATE TABLE Scl_Maj
(
	Sclno nvarchar(10) NOT NULL,	/*学校编号*/
	Mno nvarchar(10)   NOT NULL,	/*专业号*/
	SMnum int,						/*招生人数*/
	Evalu nvarchar(10),				/*专业等级评估*/--共9级 A+，A，A-，B，C 该校该专业属于国家什么等级
	SMremark nvarchar(50)
)

/*考研上岸同学名单*/
CREATE TABLE Schoolmate
(
	Year char(4),
	Sno nvarchar(20)   NOT NULL,	/*学号*/
	Sname nvarchar(20),				/*学生姓名*/
	Acad nvarchar(20),				/*学院*/		
	Sclname nvarchar(20),			/*学校名*/
	Mname nvarchar(20),			/*专业名*/					
	Stel nvarchar(20)			/*电话*/
)

--drop TABLE Users
--创建用户表
CREATE TABLE Users
(ID CHAR(20)	primary key,		/*用户名*/
 password CHAR(20),					/*密码*/
 role INT							/*角色*/--0系统管理员 1校方教务人员 2学生用户
 )

完整性约束

/*Student*/
--主键约束
ALTER TABLE Student
ADD CONSTRAINT stu_pk PRIMARY KEY(Sno)

--性别约束
ALTER TABLE Student
ADD CONSTRAINT stu_sex CHECK
(Sex IN('女','男'))

--学院约束
ALTER TABLE Student
ADD CONSTRAINT stu_acad CHECK
(Acad IN('化学工程学院','外国语学院','生物工程学院','人文学院','药学院','马克思主义学院','设计与建筑学院','环境学院','法学院',
'机械工程学院','材料科学与工程学院','公共管理学院 ','国际学院','食品科学与工程学院','信息工程学院','健行学院',
'计算机科学与技术学院','创业学院','土木工程学院','继续教育学院','理学院','之江学院','管理学院','体育军训部','经济学院','教育科学与技术学院'))
ALTER TABLE Student
DROP CONSTRAINT stu_acad 

--外码约束
ALTER TABLE Student
ADD CONSTRAINT s_fk FOREIGN KEY(Mname) REFERENCES Major(Mname)

/*School*/
--主键约束
ALTER TABLE School
ADD CONSTRAINT scl_pk PRIMARY KEY(Sclno)

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


--学校类型约束
ALTER TABLE School
ADD CONSTRAINT scl_type CHECK
(Type IN('985%','211%','%双一流','普通高校',NULL))
--学校类型填写时，称谓高的写在前面985>211>双一流>普通高校，
-- 985、211、双一流都不是，即为普通高校

/*Major*/
--主键约束
ALTER TABLE Major
ADD CONSTRAINT maj_pk PRIMARY KEY(Mno)

--唯一性
ALTER TABLE Major
ADD CONSTRAINT uni_mname UNIQUE(Mname)

--外码约束
ALTER TABLE Major
ADD CONSTRAINT m_fk FOREIGN KEY(Tno) REFERENCES Teacher(Tno)

/*Teacher*/
--主键约束
ALTER TABLE Teacher
ADD CONSTRAINT tea_pk PRIMARY KEY(Tno)

--性别约束
ALTER TABLE Teacher
ADD CONSTRAINT tea_sex CHECK
(Tsex IN('女','男'))

ALTER TABLE Teacher 
DROP CONSTRAINT tea_acad 
--学院约束
ALTER TABLE Teacher
ADD CONSTRAINT tea_acad CHECK
(Tacad IN('化学工程学院','外国语学院','生物工程学院','人文学院','药学院','马克思主义学院','设计与建筑学院','环境学院','法学院',
'机械工程学院','材料科学与工程学院','公共管理学院 ','国际学院','食品科学与工程学院','信息工程学院','健行学院',
'计算机科学与技术学院','创业学院','土木工程学院','继续教育学院','理学院','之江学院','管理学院','体育军训部','经济学院','教育科学与技术学院'))

/*Stu_Scl*/
--主键约束
ALTER TABLE Stu_Scl
ADD CONSTRAINT sscl_pk PRIMARY KEY(Sno,Sclno,Mno)
--外码约束
ALTER TABLE Stu_Scl
ADD CONSTRAINT sscl_fkm FOREIGN KEY(Mno) REFERENCES Major(Mno)
ALTER TABLE Stu_Scl
ADD CONSTRAINT sscl_fks FOREIGN KEY(Sno) REFERENCES Student(Sno)
ALTER TABLE Stu_Scl
ADD CONSTRAINT sscl_fkscl FOREIGN KEY(Sclno) REFERENCES School(Sclno)

/*Scl_Maj*/
--主键约束
ALTER TABLE Scl_Maj
ADD CONSTRAINT sclm_pk PRIMARY KEY(Sclno,Mno)
--外码约束
ALTER TABLE Scl_Maj
ADD CONSTRAINT sclm_fkm FOREIGN KEY(Mno) REFERENCES Major(Mno)
ALTER TABLE Scl_Maj
ADD CONSTRAINT sclm_fkscl FOREIGN KEY(Sclno) REFERENCES School(Sclno)
--专业评级约束
ALTER TABLE Scl_Maj
ADD CONSTRAINT sclm_evalu CHECK
(Evalu  IN('A+','A','A-','B+','B','B-','C+','C','C-',NULL))

/*Schoolmate*/
--主键约束
ALTER TABLE Schoolmate
ADD CONSTRAINT mate_pk PRIMARY KEY(Sno)

存储过程

https://blog.csdn.net/wanlong360599336/article/details/52415420
存储过程插入用户信息

/*插入学生表的存储过程*/
--drop PROCEDURE pro_insert_stu
GO
CREATE PROCEDURE pro_insert_stu
@Sno nvarchar(20),
@Sname nvarchar(10),
@Sex nvarchar(4),
@Year nvarchar(10),
@Acad nvarchar(20),
@Mname nvarchar(20),
@Stel nvarchar(20),
@Sremark nvarchar(50)
AS BEGIN
INSERT Student(Sno,Sname,Sex,Year,Acad,Mname,Stel,Sremark)
VALUES(@Sno,@Sname,@Sex,@Year,@Acad,@Mname,@Stel,@Sremark)
SELECT *
FROM Student
end

--插入学生数据
exec pro_insert_stu '201706110125','杨苏杭','女',' 2017','理学院',' 信息与计算科学','13606496470','';

--更新数据
update student set Mname=' 数学与应用数学'  where Sname='方妙双' 
SELECT *
FROM Student
go

插入多个数据

--插入数据
--插入单条数据
insert into student(stuNo,stuName,stuAge,stuSex)
values('01','王男','1996-08-27 09:00:00.000','男')

insert into course values('02','思想政治','85.5'),
						 ('02','数学','70'),
						 ('02','语文','80'),
						 ('02','物理','90'),
						 ('02','化学','65'),
						 ('02','英语','96')

删除表中记录

TRUNCATE TABLE Student  --所有记录

无法截断表 ‘Student’，因为该表正由 FOREIGN KEY 约束引用。

Delete * from Student  --所有记录

消息 102，级别 15，状态 1，第 30 行
“*”附近有语法错误。

Delete from Student where Sno='03'

修改数据

update student set stuSex='男',stuAge='2016-5-9 8:0:0' where stuName='王男' 
go

查询所有数据

--查询所有数据表
 create proc select_tea
as
begin
	select * from Teacher;
end
go

exec select_tea

查询特定信息
存储过程根据条件用户名查询用户信息

--存储过程根据条件用户名查询用户信息
create proc stuname
@Sname varchar(50)   --声明全局变量

--存储过程内部设定用户名查询用户信息
--@Sname varchar(50)='杨苏杭'
as
begin
	select * from student s where s.Sname=@Sname;
end
go
 
 exec stuname '杨苏杭'

根据用户名查询是否存在这个用户信息

 --存储过程根据用户名查询是否存在这个用户信息
 --drop proc exist_stu
create proc exist_stu
@sname varchar(50),
@result varchar(8) output --输出参数
as
begin
	 if (select COUNT(1) from Student s where s.Sname=@sname)>0
	 --if exists (select COUNT(1) from Student s where s.Sname=@sname)
	 set 
		@result='存在!'
	 else
	 set 
		@result='不存在!'
	 print(@result+'名为 '+@sname +' 的学生')
end
go

exec exist_stu '张三',result
-------------------------------------------------------
--应用
declare @result varchar(8)
exec stu4 '王男1',@result output
print @result
go

根据条件用户学号查询用户名

create proc sno2sname
@sno varchar(50)
as
declare @sname varchar(50)
set @sname=(select s.Sname from student s where s.Sno=@sno)
select @sname
go
 
exec sno2sname '201706110125'

根据用户学号来查询他的平均分

create proc sno_avg
@stuNo varchar(50),
@avg int output
as 
begin
	set @avg=
	(select AVG(courseScore) 
	from course 
	where stuNo=@stuNo)
	--等同
	--select @avg=AVG(courseScore) from course where stuNo=@stuNo
end
go
 
declare @avg int
exec sno_avg '02',@avg output
print @avg

根据用户姓名来联合查询用户信息和选校信息

--多表链接
drop proc stuscl
create proc stuscl
@sname varchar(50)
as
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,scl.Sclname 报考学校,s.Acad 学院,m.Mname 报考专业,sscl.Succeed 是否考上
	from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where s.Sname=@sname
go

exec stuscl '杨苏杭'
go
--两表链接
create proc stuscl
@sname varchar(50)
as
	select s.Sno,s.Sname,s.Sex,s.Acad,sscl.Sclno,sscl.Succeed
	from Student s join Stu_Scl sscl 
	on s.Sno=sscl.Sno 
	where s.Sname=@sname
go
 
exec stuscl '杨苏杭'
go

查询报考某一学校学生信息，及人数

--查询报考某一学校学生信息，及人数
--计数功能未实现
drop proc bksclname
create proc bksclname--报考学校
@sclname varchar(50)
as
begin
	declare @sclno varchar(50)
	set @sclno=(select scl.Sclno from School scl where scl.Sclname=@sclname)
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,s.Acad 学院,scl.Sclname 报考学校,m.Mname 报考专业,sscl.Succeed 是否考上
    from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where scl.Sclno=@sclno
end 
go

查询报考某一专业学生信息

drop proc bkmajor
create proc bkmajor--报考专业
@mname varchar(50)
as
begin
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,s.Year 入学年份,s.Acad 学院,m.Mname 报考专业,scl.Sclname 报考学校,sscl.Succeed 是否考上
    from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where m.Mname=@mname
end 
go

查询考上学生信息

drop proc stusucd
 create proc stusucd
as
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,s.Year 入学年份,s.Acad 学院,m.Mname 报考专业,scl.Sclname 报考学校
	from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where sscl.Succeed=1
go

判断学号是否存在，不存在，插入用户信息，返回消息；存在，返回信息

create proc stu11
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5),
@result varchar(50) output
as
	if exists (
	select * from student
	where stuNo=@stuNo)
		begin
			set @result='对不起，学号已存在!'
		end		
	else
		begin
			insert into student 
				(stuNo,stuName,stuAge,stuSex)
			values 
				(@stuNo,@stuName,@stuAge,@stuSex)
			set @result='恭喜你，用户信息插入成功!'	 
		end
go
 
declare @result varchar(50)
exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result output
print @result

查询当前用户的平均成绩与总的平均成绩之间的关系

create proc stu12
@stuNo varchar(50)
as
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
if exists(select * from course where stuNo=@stuNo)
	begin
		set @totalAvg=(select AVG(courseScore) from course)
		select @curAvg=AVG(courseScore) from course where stuNo=@stuNo
		print ('总的平均分:'+convert(varchar(18),@totalAvg))
		print ('该生的平均分:'+convert(varchar(18),@curAvg))
		if @curAvg>@totalAvg
			print '高于平均水平!'
		else 
			print '低于平均水平!'	
	end
else
	print '该生对应的分数信息不存在,请重新查询!'
go
 
exec stu12 '03'
go

更新学生是否考上的信息

create proc up_succeed
@sno varchar(50)
as
begin
     update Stu_Scl
      set Succeed=1
      where Stu_Scl.Sno=@sno
	  exec select_sscl
end 

exec up_succeed '201706110125'

总

在这里插入图片描述

/*存储过程*/
--查询所有数据表

create proc select_stu
as
begin
	select * from Student;
end
go
 
 create proc select_sch
as
begin
	select * from School;
end
go

 create proc select_maj
as
begin
	select * from Major;
end
go

 create proc select_sclm
as
begin
	select * from Scl_Maj;
end
go

 create proc select_sscl
as
begin
	select * from Stu_Scl;
end
go

 create proc select_mate
as
begin
	select * from Schoolmate;
end
go

 create proc select_tea
as
begin
	select * from Teacher;
end
go

exec select_stu
exec select_sch
exec select_maj
exec select_sclm
exec select_sscl
exec select_mate
exec select_tea


/*插入数据*/
/*插入学生表的存储过程*/
--drop PROCEDURE insert_stu
GO
CREATE PROCEDURE insert_stu
@Sno nvarchar(20),
@Sname nvarchar(10),
@Sex nvarchar(4),
@Year nvarchar(10),
@Acad nvarchar(20),
@Mname nvarchar(20),
@Stel nvarchar(20),
@Sremark nvarchar(50)
AS BEGIN
INSERT Student(Sno,Sname,Sex,Year,Acad,Mname,Stel,Sremark)
VALUES(@Sno,@Sname,@Sex,@Year,@Acad,@Mname,@Stel,@Sremark)
SELECT *
FROM Student
end

--插入学生数据
exec insert_stu '201706110229','宋鑫宇','男','2017','理学院','信息与计算科学','17857797788','';
exec insert_stu '201706110131','张秋月','女','2017','人文学院','中国语言文学','13732274389','';
exec insert_stu '201606110131','张明凯','男','2016','人文学院','中国语言文学','13732274389','';
exec insert_stu '201606110101','王金梦','女','2016','理学院','信息与计算科学','13732274309','';
exec insert_stu '201606110102','吴安朋','男','2016','理学院','数学与应用数学','13772274309','';
exec insert_stu '201606110103','郭豫亨','男','2016','理学院','数学与应用数学','13778997609','';
exec insert_stu '201606110104','凉爽','男','2016','理学院','数学与应用数学','13778990609','';
exec insert_stu '201706130109','徐晓新','男','2017','计算机科学与技术学院','软件工程','13773290609','';
exec insert_stu '201506110103','陆毅','女','2015','理学院','信息与计算科学','13778977609','';
exec insert_stu '201506110104','陈新宇','女','2015','理学院','信息与计算科学','18888990609','';

--更新数据
update student set Mname='数学与应用数学'  where Sname='方妙双' 
SELECT *
FROM Student
Go

/*插入用户表的存储过程*/
--drop PROCEDURE insert_user
GO
CREATE PROCEDURE insert_user
@ID nvarchar(20),
@password nvarchar(10),
@role int
AS BEGIN
INSERT Users(ID,password,role)
VALUES(@ID,@password,@role)
SELECT *
FROM Users
end

--Delete from Users where ID='admin'
exec insert_user 'ysh', '11111',1
exec insert_user 'fmt', '22222',1
exec insert_user 'fms', '33333',1
exec insert_user 'clx', '44444',1
exec insert_user 'fx', '55555',1
exec insert_user 'admin', '980808',0
exec insert_user '白老师', '666666',2


/*插入教师表的存储过程*/
--drop PROCEDURE insert_tea
GO
CREATE PROCEDURE insert_tea
@no nvarchar(20),
@name nvarchar(10),
@sex nvarchar(4),
@acad nvarchar(20),
@tel nvarchar(20),
@remark nvarchar(50)
AS BEGIN
INSERT Teacher(Tno,Tname,Tsex,Tacad,Tphone,Tremark)
VALUES(@no,@name,@sex,@acad,@tel,@remark)
SELECT *
FROM Teacher
end

exec insert_tea '01','朱老师','女','理学院','13666496470',''
exec insert_tea '02','白老师','男','理学院','13666496471',''
exec insert_tea '03','王老师','男','经济学院','13666496472',''
exec insert_tea '04','马老师','男','计算机科学与技术学院','13666496473',''
exec insert_tea '05','李老师','女','国际学院','13666496474',''
exec insert_tea '06','赵老师','男','法学院','13666496475',''
exec insert_tea '07','范老师','女','人文学院','13666496476',''
exec insert_tea '08','杨老师','女','设计与建筑学院','13666496477',''
exec insert_tea '09','吕老师','男','信息工程学院','13666496478',''
exec insert_tea '10','徐老师','男','药学院','13666496479',''
exec insert_tea '11','原老师','女','教育科学与技术学院','13666496499',''
--exec select_tea

/*插入学校表的存储过程*/
--drop PROCEDURE insert_scl
GO
CREATE PROCEDURE insert_scl
@Sclno nvarchar(20),
@Sclname nvarchar(20),
@Area nvarchar(20),
@Scltel nvarchar(20),
@Type nvarchar(20),
@Srank int,
@Sclremark nvarchar(50)
AS BEGIN
INSERT School(Sclno,Sclname,Area,Scltel,Type,Srank,Sclremark)
VALUES(@Sclno,@Sclname,@Area,@Scltel,@Type,@Srank,@Sclremark)
SELECT *
FROM School
End
exec insert_scl 'S01','北京大学','北京市','010-62751407','985，211，双一流',1,''

--drop proc insert_stuscl
/*插入学生报考表的存储过程*/
GO
CREATE PROCEDURE insert_stuscl
@Sno nvarchar(20),
@Sname nvarchar(20),
@sclname nvarchar(20),
@major nvarchar(20)
AS BEGIN
	if not exists(select 1 from Stu_Scl where Sno=@sno)--如果报考表中没有该数据，则插入
		INSERT into Stu_Scl (Sno,Sclno,Mno)
		select s.Sno ,scl.Sclno ,m.Mno
		from Student s , School scl ,Major m 
		where scl.Sclname=@sclname and m.Mname = @major and s.Sno=@Sno
	else
		update Stu_Scl 								--否则修改志愿
		set Stu_Scl.Sclno=(select Sclno from School where School.Sclname=@sclname),
		Stu_Scl.Mno=(select Mno from Major m where m.Mname=@major)
		where Sno=@sno
	select * from stubk 
end

exec insert_stuscl '201706110128','fms','浙江工业大学','信息与计算科学'

--存储过程根据条件用户名查询用户信息
create proc stuname
@Sname varchar(50)   --声明全局变量
as
begin
	select * from student s where s.Sname=@Sname;
end
go
 
 exec stuname '杨苏杭'

--存储过程根据学生名查询是否存在这个学生信息
create proc exist_stu
@sname varchar(50),
@result varchar(8) output --输出参数
as
begin
	 if (select COUNT(1) from Student s where s.Sname=@sname)>0
	 --if exists (select COUNT(1) from Student s where s.Sname=@sname)
	 set 
		@result='存在!'
	 else
	 set 
		@result='不存在!'
	 print(@result+'名为 '+@sname +' 的学生')
end
go

exec exist_stu '张三',result
--存储过程根据学生学号查询学生名
create proc sno2sname
@sno varchar(50)
as
declare @sname varchar(50)
set @sname=(select s.Sname from student s where s.Sno=@sno)
select @sname
go
 
exec sno2sname '201706110125'

--查询报考某一学校学生信息
--drop proc bksclname
create proc bksclname--报考学校
@sclname varchar(50)
as
begin
	declare @sclno varchar(50)
	set @sclno=(select scl.Sclno from School scl where scl.Sclname=@sclname)
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,s.Acad 学院,scl.Sclname 报考学校,m.Mname 报考专业,sscl.Succeed 是否考上
    from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where scl.Sclno=@sclno
end 
go

exec bksclname '浙江工业大学'

--查询报考某一专业学生信息
--drop proc bkmajor
create proc bkmajor--报考专业
@mname varchar(50)
as
begin
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,s.Year 入学年份,s.Acad 学院,m.Mname 报考专业,scl.Sclname 报考学校,sscl.Succeed 是否考上
    from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where m.Mname LIKE '%'+@mname+'%'--改进：从专业名字相同的查询改为，包含该字样的查询

end 
go

exec bkmajor '计算机'

--存储过程根据学生名来删除对应的学生信息
--@@rowcount返回操作条数
--return返回信息，终止下面的操作
--drop proc del_name
create proc del_name
@sname varchar(50)
as
begin
	delete from Student where Sname=@sname
	return @@rowcount
	exec select_stu
end
go
 
declare @result varchar(50)
exec @result=del_name '王莽'
print '删除条数：'+ @result
--select @result as '删除条数'


--存储过程根据学生姓名来联合查询学生信息和选校信息
--drop proc stuscl
 create proc stuscl
@sname varchar(50)
as
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,scl.Sclname 报考学校,s.Acad 学院,m.Mname 报考专业,sscl.Succeed 是否考上
	from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where s.Sname=@sname
go

exec stuscl '杨苏杭'
go

--更新学生是否考上的信息，同时将学生信息插入到Schoolmate表中
--drop proc up_succeed
create proc up_succeed
@sno varchar(50)
as
begin
     update Stu_Scl
      set Succeed=1
      where Stu_Scl.Sno=@sno
	  if not exists(select 1 from Schoolmate where Sno=@sno)--如果Schoolmate表中没有该数据，则插入
		  insert into Schoolmate (Year,Sno,Sname,Acad,Sclname,Mname,Stel)
		  select s.Year,s.Sno,s.Sname,s.Acad,scl.Sclname,m.Mname,s.Stel
		  from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
		 join School scl on sscl.Sclno=scl.Sclno
	  	 join Major m on sscl.Mno=m.Mno
		 where s.Sno=@sno
	  exec select_mate
end 

exec up_succeed '201706110125'

--按学校类型查询学校
create proc scltype
@type nvarchar(20)
as
	select *
	from School 
	where Type  LIKE '%'+@type+'%' 
go

exec scltype '211'

--查询后来考上学生信息
--drop proc stusucd
 create proc stusucd
as
	select s.Sno 学号,s.Sname 姓名,s.Sex 性别,s.Year 入学年份,s.Acad 学院,scl.Sclname 报考学校,m.Mname 报考专业
	from Student s join Stu_Scl sscl on s.Sno=sscl.Sno
	join School scl on sscl.Sclno=scl.Sclno
	join Major m on sscl.Mno=m.Mno
	where sscl.Succeed=1
go

exec stusucd

视图

在这里插入图片描述

/*视图*/
--drop  VIEW stubk
CREATE VIEW stubk AS--报考专业人数表
--	学号，姓名，学校，专业
SELECT scl.Sclname 报考学校,m.Mname 报考专业, sclm.SMnum 招生人数, s.Sno 学号,s.Sname 姓名
FROM Student s,Major m,Stu_Scl sscl ,School scl,Scl_Maj sclm
WHERE s.Sno=sscl.Sno and sscl.Mno=m.Mno and scl.Sclno =sscl.Sclno and sclm.Sclno =scl.Sclno and sclm.Mno =m.Mno
select * from stubk

--大学专业报考人数表
CREATE VIEW sclmajnum AS
select  count(*) 报考人数,s.报考学校 ,s.报考专业 
from stubk s
group by 报考学校,报考专业

--drop  VIEW sclmaj
CREATE VIEW sclmaj AS--学校专业表
--	学校，专业，招生人数，专业评估
SELECT scl.Sclname 学校,m.Mname 专业,sclm.SMnum  招生人数,sclm.Evalu 专业等级评估
FROM School  scl,Major m,Scl_Maj  sclm
WHERE scl.Sclno=sclm.Sclno and sclm.Mno=m.Mno 

select *
from sclmaj
where 学校='北京大学'

select *
from sclmaj
where 专业='软件工程' and 专业等级评估='A'
--改进，新增多表联接，联合报考人数与招生人数
--学校专业报考人数联合表
CREATE VIEW sclnum AS
select sm.学校 ,sm.专业 ,sm.专业等级评估 ,sm.招生人数 ,sn.报考人数  
from sclmaj sm, sclmajnum sn 
where  sn.报考专业 =sm.专业 and sm.学校 =sn.报考学校

CREATE VIEW teamaj AS--教师指导专业表
--	教师，专业，教师电话
SELECT t.Tname 教师,m.Mname 专业,t.Tphone 教师电话
FROM Teacher t,Major m
WHERE t.Tno=m.Tno 

--根据专业查找指导老师信息
select *
from teamaj
where 专业='物理学'

/*按照专业查找某专业报考人数*/
--drop view cou_bkmaj
create view cou_bkmaj
as
SELECT 报考专业,count(学号) as 专业报考人数
from stubk
group by 报考专业

--select * from cou_bkmaj
--select * from stubk

--DROP VIEW rankmaj
--报考专业人数排名视图
CREATE VIEW rankmaj AS
select  dense_Rank()
OVER(order by 专业报考人数 DESC) as 报考人数排名 ,
* from cou_bkmaj

--DROP trigger tri_updatemhot 
--专业热度修改触发器，当有学生填报考信息时,修改，Major表 专业热度
create trigger tri_updatemhot 
on Stu_Scl
for insert
as
	begin
	update Major 
	set Mhot  = rankmaj.报考人数排名 
	from rankmaj 
	where rankmaj.报考专业 = Major.Mname 
	end

触发器

https://blog.csdn.net/legendaryhaha/article/details/80368114
在这里插入图片描述

--AFTER触发器的应用实现
--创建updata触发器，要求当在学校信息表（School）修改学校编号字段时，Scl_Maj中的学校编号也相应修改
create trigger Sclno_trig
on School
for update
as
if update(Sclno)
update Scl_Maj
set Scl_Maj.Sclno=(select Sclno from inserted)
where Scl_Maj.Sclno=(select Sclno from deleted)
go

--创建delete触发器，实现学生信息级联删除
create trigger Sno_trig on Student
instead of delete
as
begin
declare @sno varchar(20)
select @sno=Sno from deleted
delete from Stu_Scl where Sno=@sno
delete from Student where Sno=@sno
end;
--创建delete触发器，实现学校信息级联删除
create trigger dSclno_trig on School
instead of delete
as
begin
declare @sclno varchar(20)
select @sclno=Sclno from deleted
delete from Stu_Scl where Sclno=@sclno
delete from Scl_Maj where Sclno=@sclno
delete from School where Sclno=@sclno
end;

--创建update触发器，要求当在学生信息表（Student）修改电话字段时，其他表中的电话也相应修改
create trigger Stel_trig
on Student
for update
as
if update(Stel)
update Schoolmate
set Schoolmate.Stel=(select Stel from inserted)
where Schoolmate.Stel=(select Stel from deleted)
go