--创建数据库
use XSCJ
---建立学生表
create table student (
sno char(10) primary key not null,
sname char(10) not null,
ssex char(2) not null,
sage smallint not null,
class_no varchar(10) not null)
alter table student add constraint c1 foreign key(class_no) references class(class_no)
--建立班级表
create table class(
class_no varchar(10) primary key not null,
class_name varchar(10) not null,
subject_no varchar(10) not null)
alter table class add constraint FK_class_subject foreign key(subject_no) references subject(subject_no)
--建立专业表
create table subject(
subject_no varchar(10) primary key not null,
subject_name varchar(10) not null,
tdept_no varchar(10) not null)
alter table class add constraint FK_s_t foreign key(subject_no) references sdept(tdept_no)
--建立系表
create table sdept(
tdept_no Varchar(10) primary key not null,
tdept_name Varchar(10) not null
)
--创建课程表
create table course(
cno char(10) not null primary key,--编号，主键约束
cname char(16) not null,--名称
ccredit smallint not null,--学分
chours smallint not null,--学时
kind varchar(10),--课程性质
semester varchar(20) not null--开课学期
)
--因为不同 专业要选不同课程因此要建立一个开设表
--创建开设课程表
create table class_compulsory(
cno char(10) not null,--编号
class_no varchar(10),
start_date date not null,--开始日期
-- 添加参照完整性约束
constraint fk3_cno_class_no primary key(class_no,cno),
constraint fk3_class_compulsory foreign key(class_no) references class(class_no),
constraint fk3_compulsory_class foreign key(cno) references course(cno)
)
--创建必修课表
create table compulsory(
cno char(10) not null primary key
constraint FK_compulsory_course foreign key(cno) references course(cno)
)
--创建选修课表
create table Selective(
cno char(10) not null primary key,--编号，主键约束
start_time date not null,--开始时间
end_time date not null,--结束时间
constraint FK_Selective_course foreign key(cno) references course(cno)
)
--创建选修表
create table student_Selective(
sno char(10) not null,
cno char(10) not null,
grade smallint,--成绩
point smallint,--绩点
--添加参照完整性约束
constraint fk1_c1_c2 primary key(sno,cno),
constraint fk1_student foreign key(sno) references student(sno),
constraint fk1_Selective foreign key(cno) references course(cno)
)
--创建必修表
create table student_compulsory(
sno char(10) not null,
cno char(10) not null,
grade smallint,--成绩
point smallint,--绩点
--添加参照完整性约束
constraint fk2_c1_c2 primary key(sno,cno),
constraint fk2_student foreign key(sno) references student(sno),
constraint fk2_Selective foreign key(cno) references course(cno)
)
--创建触发器
--学生基本信息的删除
--删除学生的基本信息，学生的选修课信息也删除
go
create trigger student_Selective_delete
on student
for delete
as if (select count(*)
from student_Selective,deleted
where student_Selective.sno = deleted.sno)>0
begin
delete student_Selective
from student_Selective inner join deleted
on student_Selective.sno = deleted.sno
end
--删除学生的基本信息，学生的必修课信息也删除
go
create trigger student_compulsory_delete
on student
for delete
as if (select count(*)
from student_compulsory,deleted
where student_compulsory.sno = deleted.sno)>0
begin
delete student_compulsory
from student_compulsory inner join deleted
on student_compulsory.sno = deleted.sno
end
--删除课程的基本信息，所有选修该课程的信息也全部删除
go
create trigger course_delete
on course
for delete
as if (select count(*)
from Selective,deleted
where Selective.cno = deleted.cno)>0
begin
delete Selective
from Selective inner join deleted
on Selective.cno = deleted.cno
delete student_Selective
from student_Selective inner join deleted
on student_Selective.cno = deleted.cno
end
--删除课程的基本信息，所有必修该课程的信息也全部删除
go
create trigger course_delete1
on course
for delete
as if (select count(*)
from compulsory,deleted
where compulsory.cno = deleted.cno)>0
begin
delete compulsory
from compulsory inner join deleted
on compulsory.cno = deleted.cno
delete student_compulsory
from student_compulsory inner join deleted
on student_compulsory.cno = deleted.cno
end
--4.2确定索引
create unique index index1 on student (sno)
create index index2 on class_compulsory (start_date)
create index index3 on student_Selective (cno)
create index index4 on student_compulsory (cno)
--6建立视图
go
create view xscj
as
select s.sno,sname,class_name,c.cname,sc.grade,sc.point
from student s,class,course c,student_compulsory sc
where s.class_no = class.class_no
and s.sno = sc.sno
and sc.cno = c.cno
union
select s.sno,sname,class_name,c.cname,ss.grade,ss.point
from student s,class,course c,student_Selective ss
where s.class_no = class.class_no
and s.sno = ss.sno
and ss.cno = c.cno
go
--7.录入数据
--教学系表
insert into tdept (tdept_no,tdept_name)
values('T001','数计学院'),
('T002','管理学院'),
('T003','体育学院'),
('T004','物理学院'),
('T005','化工学院')
--专业表
insert into subject (subject_no,subject_name,tdept_no)
values('S001','JAVA','T001'),
('S002','高等数学','T001'),
('S003','酒店管理','T002'),
('S004','工程管理','T002'),
('S005','近代史','T003'),
('S006','日语','T003'),
('S007','生物科学','T004'),
('S008','概率论','T004'),
('S009','物理化学','T005'),
('S010','应用化学','T005')
--班级表
insert into class(class_no,class_name,subject_no)
values('C001','计科2101','S001'),
('C002','计科2102','S002'),
('C003','网工2101','S002'),
('C004','网工2102','S002'),
('C005','酒管2101','S003'),
('C006','酒管2102','S003'),
('C007','工管2101','S004'),
('C008','工管2102','S004'),
('C009','英语2101','S005'),
('C010','英语2102','S005'),
('C011','日语2101','S006'),
('C012','日语2102','S006'),
('C013','生科2101','S007'),
('C014','生科2102','S007'),
('C015','食品2101','S008'),
('C016','食品2102','S008'),
('C017','化学2101','S009'),
('C018','化学2102','S009'),
('C019','应化2101','S010'),
('C020','应化2102','S010')
--学生表
insert into student (sno,sname,ssex,sage,class_no)
values('200001','张一','男',19,'C001'),
('200002','张二','男',21,'C002'),
('200003','张三','男',20,'C003'),
('200004','张四','男',18,'C004'),
('200005','张五','女',22,'C005'),
('200006','张六','女',19,'C006'),
('200007','张七','男',18,'C007'),
('200008','张八','男',20,'C008')
--课程表
insert into Course (cno,cname,ccredit,chours,kind,semester)
values('cn001','高等数学上',5,48,'公共必修课','第一学期'),
('cn002','高等数学下',5.5,48,'公共必修课','第二学期'),
('cn003','大学物理上',3,48,'公共必修课','第二学期'),
('cn004','大学物理下',3.5,48,'公共必修课','第三学期'),
('cn016','生理基础',3,40,'专业必修课','第四学期'),
('cn017','工艺流程',3,40,'专业必修课','第五学期'),
('cn018','数据库',3,40,'专业必修课','第四学期'),
('cn019','JAVAEE',4,24,'专业选修课','第五学期'),
('cn020','管理信息',4,24,'专业选修课','第六学期'),
('cn021','商务英语',4,24,'专业选修课','第五学期'),
('cn022','生物化学',4,24,'专业选修课','第六学期'),
('cn023','无机化学',4,24,'专业选修课','第五学期'),
('cn024','C++',4,24,'专业选修课','第六学期')
--必修课表
insert into compulsory(cno)
values('cn001'),
('cn002'),
('cn003'),
('cn004'),
('cn005'),
('cn006'),
('cn013'),
('cn014'),
('cn015'),
('cn016'),
('cn017'),
('cn018')
--选修课表
insert into Selective(cno,start_time,end_time)
values('cn007','2020-09-04','2021-02-02'),
('cn008','2020-09-05','2021-02-07'),
('cn009','2020-09-07','2021-02-01'),
('cn010','2020-09-01','2021-02-08'),
('cn011','2020-09-01','2021-02-11'),
('cn012','2020-09-02','2021-02-01'),
('cn019','2020-11-01','2021-01-01'),
('cn020','2020-11-01','2021-01-15'),
('cn021','2020-11-03','2021-01-01'),
('cn022','2020-11-01','2021-01-01'),
('cn023','2020-11-01','2021-01-15'),
('cn024','2020-11-05','2021-01-01')
--必修表
insert into student_compulsory(sno,cno,grade,point)
values('200001','cn001',85,3.5),
('200002','cn001',72,2),
('200003','cn001',66,1.5),
('200004','cn001',91,4),
('200005','cn001',82,3),
('200006','cn001',60,1),
('200001','cn002',85,3.5),
('200002','cn002',72,2),
('200003','cn002',66,1.5),
('200004','cn002',91,4),
('200005','cn002',82,3),
('200006','cn002',60,1)
--选修表
insert into student_Selective(sno,cno,grade,point)
values('200001','cn007',85,3.5),
('200001','cn008',72,2),
('200001','cn009',66,1.5),
('200001','cn010',91,4),
('200001','cn011',82,3),
('200001','cn012',60,1),
('200002','cn007',85,3.5),
('200002','cn008',72,2),
('200002','cn009',66,1.5),
('200002','cn010',91,4),
('200002','cn011',82,3),
('200002','cn012',60,1)
--开设课表
insert into class_compulsory(cno,class_no,start_date)
values('cn001','C001','2022-09-01'),
('cn002','C001','2022-09-01'),
('cn003','C001','2022-09-01'),
('cn004','C001','2022-09-01'),
('cn005','C001','2022-09-01'),
('cn006','C001','2022-09-01'),
('cn001','C002','2022-09-01'),
('cn002','C002','2022-09-01'),
('cn003','C002','2022-09-01'),
('cn004','C002','2022-09-01'),
('cn005','C002','2022-09-01'),
('cn006','C002','2022-09-01')
8、数据查询
--1.给定学号，按学号查询指定学生的基本信息。
use XSCJ
select * from student where sno = '200006';
--2.给定姓名，按姓名查询指定学生的基本信息
select * from student where sname = '张三';
--3.给定课程号，按课程号查询指定课程的基本信息。
select * from Course where cno = 'cn001';
--4.给定课程名，按课程名查询指定课程的基本信息。
select * from Course where cname = '数据库';
--5.给定学号和课程名，按学号和课程号查询指定学生所修指定课程的成绩和学分绩点。
select grade,point from student s,course c,student_Selective ss
where s.sno = ss.sno and c.cno = ss.cno and s.sno='200002' and c.cno='cn008'
union
select grade,point from student s,course c,student_compulsory sc
where s.sno=sc.sno and c.cno=sc.cno and s.sno='200004' and c.cno = 'cn001'
--6.给定学号，按学号查询指定学生所修全部课程的课程名、成绩和学分绩点。要求使用所建立的“学生成绩” 视图。
select * from xscj where sno='200001'
--7.给定班级和课程名，按班级和课程号查询指定班级所有学生选修指定课程的成绩，查询结果以学号、姓名、成绩、学分绩点的形式显示。要求使用所建立的“学生成绩” 视图。
select sno,sname,grade,point from 学生成绩 sg,course c where sg.cname=c.cname and class_name = '计科2101' and c.cno='cn001'
--8.查询每个学生的学分绩点的总和及平均学分绩点。
select sno,sum(point) '绩点和',avg(point) '平均绩点' from 学生成绩 group by sno;
9、数据更新
--1.插入一个学生的基本信息。
insert into student values('200019','疯玛吉','女',20,'c100')
--2.插入一门课程的基本信息
insert into course values('cn099','滋崩理论',4,36,'公共必修课','第二学期')
select * from course;
--3.插入一个学生某一门课的成绩。
insert into student_compulsory values('200002','cn099',74,2)
select * from student_compulsory where sno='200002'
--4.给定学号，按学号修改指定学生的基本信息。
update student set sname='瓦鸡',sage=18 where sno='200005'
select * from student where sno='200005'
--5.给定课程号，按课程号修改指定课程的基本信息。
update course set cname='r99理论' where cno = 'cn016';
select * from course where cno='cn016'
--6.给定学号和课程名，按学号和课程名修改指定学生所修指定课程的成绩。
update student_compulsory set grade='66' where sno='200006' and cno='cn002'
select * from student_compulsory where sno='200006' and cno='cn002'
--7.给定学号，按学号删除指定学生的基本信息及修课信息。
delete from student_Selective where sno='200002'
select * from student_Selective;
--8.给定学号和课程名，按学号和课程名删除指定学生所修指定课程及成绩信息。
delete from student_compulsory where sno='200005' and cno='cn002'
select * from student_compulsory;
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
