建表
--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
505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
