考研信息管理系统
这是我自己想的一个数据库
关系图
分离数据库
想将数据库发给其他电脑,需要先分离
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