数据库原理与应用--实验十三

实验内容：

1. 数据库：建立一个“选课系统”数据库，命名为choose，该数据库的字符集设置为gbk。
2. 表：在choose数据库中创建1张表，学生情况表(编号，学号、姓名、专业、性别、出生日期、年龄、运动爱好)，其中编号为AUTO_INCREMENT，主键为编号，性别要求只能输入“男”或“女”(提示：使用enum)，年龄在10-40岁之间，运动爱好只能在篮球、足球、排球、羽毛球、乒乓球、游泳和其他这几个选项选择，可以多选（提示：使用set）。创建完表后插入几行数据测试一下。
3. 查询

（1）建立一个“jiaoxue”数据库，通过运行jiaoxue.sql里面的SQL语句导入“jiaoxue”数据库；“jiaoxue”数据库中4张表信息如下：

教职工表Teacher(Tno, TName, age, sal, mgr, DNo)，TNo为教职工编号，TName为教职工姓名，age年龄，sal为月薪，mgr为教职工的上一级领导的职工号，DNo为部门号；

课程表Course(Cno, CName, CCredits, CTno)，Cno为课程编号，CName为课程名字，CCredits为课程学分, CTno为该课程上课老师的编号，其中外键CTno来自表Teacher的Tno键；

学生表Student(Sno, CLno, SName, SSex, SBir, Email)，其中Sno为学生学号，CLno为系别编号，SName为学生姓名，SSex为学生性别，SBir为学生出生日期；

学生选课表SC(Sno, Cno, Score)。

1. 对jiaoxue数据库进行如下检索

1. 查询课程号为001的成绩前五名的学生学号和成绩（假设成绩没有重复）。
2. 查询每个系的学生人数以及全部学生总人数（提示：with rollup）；
3. 查询每个学生的学号、姓名和选课的课程号，每个学生只显示一行数据，如果该学生选了多门课，则所选的课程号放在同一行里，用逗号隔开（提示：group_concat()）；
4. 查询课程名以英文字母开头的课程信息（提示：正则表达式查询）；
5. 查询课程名纯英文字母的课程信息（提示：正则表达式查询）。

1. 自定义函数

1. 创建一个函数，参数为学号，使函数实现通过学号查学生姓名，并调用该函数查询学号“00001”和“00002”的姓名；
2. 创建一个函数，参数为n，计算1至n之间的偶数相加并返回相加结果，分布使用while、repeat和loop实现。

5.  存储过程

（1）创建一个存储过程，参数为课程号，返回选修某门课程的总人数，然后再执行该过程后把总人数显示出来，建立完存储过程后测试该存储过程；

（2）创建一个存储过程，通过学号和课程号进行成绩查询，如果查询结果为空，则显示“该学生没有选这门课”，返回0，如果查询结果低于60分，则显示“该学生考试成绩不及格”，返回1，如果查询结果在60-89之间，则显示“该学生考试成绩为及格”，返回2，如果查询结果大于等于90，则显示“该学生考试成绩为优秀”，返回3，建立完存储过程后测试该存储过程。

6.  触发器

（1）在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，当插入数据时，规定出生年份为2003和2004的学生才能选修职业规划这门课程，否则则输出’不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00002’,’005’,null)和(‘00003’,’005’,null)看看结果；

（2）在表Teacher中创建触发器，当表Teacher进行更新操作时，如果更新工资比以前低，则把工资改回来原来值，如果更新名字时出现重名，则把名字改回原来的值，建立完触发器后测试该触发器；

（3）在表course中建立删除触发器，实现表course和表SC的级联删除，也就是只要删除表course中课程号为c1的元组，则表SC中cno为c1的元组也要删除，建立完触发器后测试触发器。

源代码：

/*1.建立一个“选课系统”数据库，命名为choose，该数据库的字符集设置为gbk*/
create database choose charset=gbk;

/*2.在choose数据库中创建1张表，学生情况表
(编号，学号、姓名、专业、性别、出生日期、年龄、运动爱好)，
其中编号为AUTO_INCREMENT，主键为编号，性别要求只能输入
“男”或“女”(提示：使用enum)，年龄在10-40岁之间，运动爱好
只能在篮球、足球、排球、羽毛球、乒乓球、游泳和其他这几个
选项选择，可以多选（提示：使用set）。*/
USE DATABASE choose
CREATE TABLE STUDENT(
id int(11) AUTO_INCREMENT PRIMARY KEY,
snum int(3) ,
sname VARCHAR(10), 
clno VARCHAR(10),
sex enum('男','女'),
borndate VARCHAR(8),
age int(10) ,
habbit set('篮球','足球','排球','羽毛球','乒乓球','游泳','其他')
check (age > 10 AND age < 40)
);
INSERT INTO student VALUE(001,00001,'小明','计算科学','男','2022/5/1',20,'篮球');

/*3.2.1）查询课程号为001的成绩前五名的学生学号和成绩
（假设成绩没有重复）。*/
select sno,score
from sc
where cno='001'
ORDER BY score desc
LIMIT 0,5;
/*3.2.2）查询每个系的学生人数以及全部学生总人数*/
select clno,count(sno)
from student
GROUP BY CLno
with ROLLUP
/*3.2.3）查询每个学生的学号、姓名和选课的课程号，
每个学生只显示一行数据，如果该学生选了多门课，
则所选的课程号放在同一行里，用逗号隔开；*/
select sno,sname,group_concat(cno)
from sc,student
where SNo.sc=SNo.student
group by SNo
/*3.2.4）查询课程名以英文字母开头的课程信息；*/
select cname 
from course
where cname regexp'[a-z][A-Z]';
/*3.2.5）查询课程名纯英文字母的课程信息*/
select cname 
from course
where cname not in
(SELECT Cname from course where CName REGEXP '[^a-z]')
/*4.1创建一个函数，参数为学号，使函数实现通过学号查学生姓名，并调用该函数查询学号“00001”和“00002”的姓名*/
create function find(学号 char(5))returns VARCHAR(8)
begin 
declare 姓名 varchar(8);
select Sname into 姓名 from student where sno=学号;
return 姓名;
end;
select find('00001'),find('00002');
/*4.2创建一个函数，参数为n，计算1至n之间的偶数相加并返回相加结果，分布使用while、repeat和loop实现*/
create function find2(n int)returns INT
begin 
declare i int;
declare result int;
set i=2;
set result=0;
WHILE i<=n do
set result=result+i;
set i=i+2;
end while;
return result;
end;
select find2(8);

create function find3(n int)returns INT
Begin
declare i int;
declare result int;
set i=2;
set result=0;
repeat
set result=result+i;
set i=i+2;
until i>n end repeat;
return result;
end;
select find3(8);

create function find4(n int)returns INT
Begin
declare i int;
declare result int;
set i=2;
set result=0;
lable:loop
set result=result+i;
set i=i+2;
if i>n
 then 
 leave lable;
  end if;
end loop lable;
return result;
end;
select find4(8);

/*5.1创建一个存储过程，参数为课程号，返回选修某门课程的总人数，然后再执行该过程后把总人数显示出来，建立完存储过程后测试该存储过程*/
create procedure pro1(in 课程号 char(3),out 人数 int)
begin 
select count(Cno) from sc where cno=课程号 GROUP BY cno;
end;
set @人数=0;
call pro1('001',@人数);
select  @人数;

/*5.2创建一个存储过程，通过学号和课程号进行成绩查询，如果查询结果为空，则显示“该学生没有选这门课”，返回0，如果查询结果低于60分，则显示“该学生考试成绩不及格”，返回1，如果查询结果在60-89之间，则显示“该学生考试成绩为及格”，返回2，如果查询结果大于等于90，则显示“该学生考试成绩为优秀”，返回3，建立完存储过程后测试该存储过程*/
create procedure pro2in ssno char(5),in ccno char(20),out sscore char (5),out print char(20)character set utf8)
reads sql data
begin
if(EXISTS(select * from sc where cno =ccno and sno =ssno and score<60))then
select sscore ='1';
set print= '该学生考试成绩不及格';
end if;
if(EXISTS(select * from sc where cno =ccno and sno =ssno and score>=60 and score<=89))then
select sscore ='2';
set print= '该学生考试成绩为及格';
end if;
if(EXISTS(select * from sc where cno =ccno and sno =ssno and score>=90))then
select sscore ='3';
set print= '该学生考试成绩为优秀';
end if;
end;
set @ssno='00001';
set @ccno='001';
set @sscore='0'; 
set@print ='0';
call pro2(@ssno,@ccno,@sscore,@print);
select @print;

/*6.1在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，当插入数据时，规定出生年份为2003和2004的学生才能选修职业规划这门课程，否则则输出’不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00002’,’005’,null)和(‘00003’,’005’,null)看看结果*/
create trigger s1 after insert on sc for each row
begin
declare year char(10);
select left(sbir,4) into year from student where student.sno=new.sno;
if(year!='2003' or year!='2004') then
select '不能选修该门课程' into @sentence;
delete from sc where sc.sno=new.sno;
end if;
end;
insert into sc values('00002','005',null);
insert into sc values('00003','005',null);
SELECT @sentence;

/*6.2在表Teacher中创建触发器，当表Teacher进行更新操作时，如果更新工资比以前低，则把工资改回来原来值，如果更新名字时出现重名，则把名字改回原来的值，建立完触发器后测试该触发器*/
create trigger t2 before update on teacher for each row
begin
if (old.sal>new.sal) then 
set new.sal=old.sal;
end if;
if(EXISTS(select t1.tname from teacher as t1,teacher as t2 where t1.tNo=t2.tNo and t1.tName!=t2.TName))then
set new.tname=old.tname;
end if;
end;

/*6.3在表course中建立删除触发器，实现表course和表SC的级联删除，也就是只要删除表course中课程号为c1的元组，则表SC中cno为c1的元组也要删除，建立完触发器后测试触发器*/
create trigger course_delete_after_trigger before delete on course for each row
begin
delete from sc where sc.cno=old.cno;
end;
delete from course
where cno='001'
select *from sc where cno='001'