数据库期末实验

实验要求

创建“学生”，“课程”，“成绩”表格并插入相关信息，添加触发器，找出学号最大的学生学号，创建存储结构和函数并进行调用。

#创建“学生-课程”模式
create schema s_t;

#创建“学生”，“课程”和“学生选课”表，同时添加约束条件
create table Student
	(Sno char(9) PRIMARY KEY,
    Sname char(20) UNIQUE,
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
    );
create table Course
	(Cno char(4) primary key,
    Cname char(40) not null,
    Cpno char(4),
    Ccredit smallint,
    foreign key (Cpno) references Course(Cno)#定义外参照完整性
    );
create table SC
	(Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key(Sno,Cno),
foreign key(Sno) references Student(Sno)#定义参照完整性
	on delete cascade
    on update cascade,/*当删除Student表中元组时，级联删除或更新本表中相应元组*/
foreign key(Cno) references Course(Cno)
	on delete no action#当删除Course表中元组导致与本表不一致时，拒绝删除
    on update cascade/*当更新Student表中元组时，级联更新本表中相应元组*/
    );
 
 #创建索引
create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno);
create unique index Scno on SC(Sno asc,Cno desc);

#插入数据
insert 
into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS');

insert 
into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215122','刘晨','女',19,'CS');

insert 
into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215123','王敏','女',18,'MA');

insert 
into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215125','张立','女',19,'IS');

#由于参照完整性存在，Course插入时需要按照一定顺序插入
insert
into Course(Cno,Cname,Cpno,Ccredit)
values('2','数学',NULL,2);

insert
into Course(Cno,Cname,Cpno,Ccredit)
values('6','数据处理',null,2);

insert
into Course(Cno,Cname,Cpno,Ccredit)
values('7','PASCAL','6',4); insert

into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7',4);

insert
into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',4);

insert
into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1',4);

insert
into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6',3);

insert
into SC(Sno,Cno,Grade)
values('201215121','1',92);

insert
into SC(Sno,Cno,Grade)
values('201215121','2',85);

insert
into SC(Sno,Cno,Grade)
values('201215121','3',88);

insert
into SC(Sno,Cno,Grade)
values('201215122','2',90);

insert
into SC(Sno,Cno,Grade)
values('201215122','3',80);
#结果查看：
select * from course

Select * from student

Select * from SC

#设定触发器：
/*因为包含多个语句，而每个语句都包含有“；”，并且mysql中，分号是语句结束的标识符，因此必须在语句开头使用delimiter命令将语句结束标志改为其他符号，否则会导致报错*/
delimiter $
create trigger sc_t
after update on SC
for each row
begin
	if(new.Grade>=1.1*old.Grade)
        then insert into SC_U(Sno,Cno,Oldgrade,Newgrade) values(old.Sno,old.Cno,old.Grade,new.Grade);
        end if;
        end$
delimiter ;

#筛选最大学号：
select max(Sno)
from Student
#结果：

#利用存储过程实现成绩添加功能：
delimiter $
DROP PROCEDURE IF EXISTS addGrade$
create procedure addGrade(noS char(9),noC char(4),newGrade smallint)

begin
	declare noSDepositin char(9);
    declare noCDepositIn char(4);
    

	select Sno into noSDepositIn from Student where Sno=noS;
    if noSDepositIn is null then
		rollback;
	end if;
    select Cno into noCDepositIn from Course where Cno=noC;
    if noCDespositIn is null then
		rollback;
	end if;
    
    
    if newGrade<0 or newGrade>100 then
		rollback;
	end if;
    insert into SC(Sno,Cno,Grade)
		values(noS,noC,newGrade);
	commit;
end$
delimiter ;

#运行结果：

#执行存储过程：
call addGrade('201215122','1',90)；
#运行结果：

#查看SC表：
Select * from SC;

#定义函数，通过学号删除某个学生
delimiter $
DROP function IF EXISTS deleteById$
create function deleteById(noS char(9)) returns char(20)
begin
	declare noSDepositin char(9);
	select Sno into noSDepositIn from Student where Sno=noS;
    if noSDepositIn is null then
		return '该学生不存在';
	end if;
    delete from Student where Sno=noS;
	return '删除完成';
end$
delimiter ;
#测试
Call deleteById(201215121);

#结果验证：