实验 存储过程的创建与使用
一、实验目的：
(1) 理解存储过程和函数的概念。
(2) 掌握创建各种存储过程和函数的方法。
(3) 掌握执行存储过程和函数的方法。
(4) 掌握查看、修改、删除存储过程和函数的方法。
二、 实验内容及步骤
- 使用SQL语句创建存储过程
(1) 创建不带参数的存储过程。
① 创建一个从student表查询班级号为“20070301”班的学生资料的存储过程proc_1，其中包括学号、
姓名、性别、出生年月等。调用proc_1存储过程，观察执行结果。
use gradem;
CREATE PROCEDURE proc_1()
BEGIN
SELECT * from student WHERE classno="20070301";
END
call proc_1;
② 在gradem数据库中创建存储过程proc_2，要求实现如下功能：存在不及格情况的学生选课情况列表，
其中包括学号、姓名、性别、课程号、课程名、成绩、系别等。调用proc_2存储过程，观察执行结果。
CREATE PROCEDURE proc_2()
BEGIN
SELECT s.sno,s.sname,s.ssex,sc.cno,sc.degree,deptname,cname
FROM student s,sc,class,department,course
WHERE s.sno=sc.sno and degree<60
and s.classno=class.classno
and class.deptno=department.deptno
and sc.cno=course.cno;
END
call proc_2;
(2) 创建带输入参数的存储过程。
创建一个从student表查询学生资料的存储过程 proc_3，其中包括学号、姓名、性别、出生年月、班级等。
要查询的班级号通过执行语句中的输入参数传递给存储过程。
并调用该存储过程查询班级号为“20070303”的学生的相关资料。
CREATE PROCEDURE proc_3(in nclassno VARCHAR(10))
BEGIN
SELECT * FROM student WHERE classno=nclassno;
END
call proc_3("20070303");
(3) 创建带输出参数的存储过程。
创建一个从sc表查询某一门课程考试成绩总分的存储过程proc_4。
在以上存储过程中，要查询的课程号通过执行语句中的输入参数cno_tmp传递给存储过程，
sum_degree作为输出参数用来存放查询得到的总分。执行此存储过程，观察执行结果。
CREATE PROCEDURE proc_4(in cno_tmp VARCHAR(10),out sum_degree int)
BEGIN
SELECT cno,sum(degree) as sum_degree from sc WHERE cno="a01" GROUP BY cno ;
END
call proc_4("a01",@asd);
(4) 创建存储函数。在sc表中，创建一个存储函数func_5，实现如下功能：输入学生学号，
根据该学生所选课程的平均分显示提示信息，平均分大于等于90，则显示“该生成绩优秀”，
平均分小于90但大于等于80，则显示“该生成绩良好”，平均分小于80但大于等于60，显示“该生成绩合格”，
小于60则显示“该生成绩不及格”。调用此存储函数，显示“2007030301”学生的成绩情况。
set global log_bin_trust_function_creators=TRUE;
CREATE FUNCTION func_5 (sno_no varchar(10)) RETURNS VARCHAR(50)
BEGIN
DECLARE avg_degree FLOAT;
DECLARE abc VARCHAR(50);
set avg_degree=(SELECT AVG(degree) from sc WHERE sno=sno_no);
if avg_degree>=90 THEN
set abc='该生成绩优秀';
ELSEif avg_degree>=80 and avg_degree<90 THEN
set abc='该生成绩良好';
ELSEIF avg_degree>=60 and avg_degree<80 THEN
set abc='该生成绩合格';
ELSEif avg_degree<60 and avg_degree is not null then
SET abc='该生成绩不及格';
ELSE
set abc='该生不存在';
end if;
return abc;
end
set @sno_no="2007030301";
set @f05=func_5(@sno_no);
SELECT @f05;
- 使用SQL语句查看、修改和删除存储过程
(1) 查看存储过程。
分别利用SHOW PROCEDURE STATUS语句和SHOW CREATE语句查看存储过程proc_1、proc_3的状态和定义。
show PROCEDURE STATUS;
show CREATE PROCEDURE proc_1;
show CREATE PROCEDURE proc_3;
(2) 删除存储过程。
将存储过程 proc_1删除。
drop PROCEDURE proc_1;
数据库综合实验（选做）
一、实验目的：
(1) 掌握数据库及表的创建与管理。
(2) 掌握数据查询及数据更新命令的使用。
(3) 掌握存储过程和触发器的创建与管理。
(3) 掌握数据备份与恢复的方法。
二、实验内容及步骤
1、使用SQL语句创建数据库
要求：以software+自己的学号后2位为数据库名创建数据库，如班级1号同学的数据库名为software01。
2、在上述数据库上使用SQL语句创建下列关系表
student表（学生表）：
| 字段名 | 字段说明 | 字段类型 | 长度 | 是否允许为空 | 说明 |
|---|---|---|---|---|---|
| stuID | 学生学号 | char | 10 | 否 | 主键 |
| stuName | 学生姓名 | char | 8 | 否 | |
| stuSex | 学生性别 | char | 2 | 是 | 默认值为“男” |
| Birthday | 出生日期 | date | 是 |
course表（课程表）：
| 字段名 | 字段说明 | 字段类型 | 长度 | 是否允许为空 | 说明 |
|---|---|---|---|---|---|
| courseID | 课程号 | char | 3 | 否 | 主键 |
| courseName | 课程名 | varchar | 20 | 否 |
sc表（学生选课表）：
| 字段名 | 字段说明 | 字段类型 | 长度 | 是否允许为空 | 说明 |
|---|---|---|---|---|---|
| stuID | 学生学号 | char | 10 | 否 | 主键 |
| courseID | 课程号 | char | 3 | 否 | 主键 |
| Grade | 成绩 | decimal | 4,1 | 是 |
（提示：此处需设置SC表与student表和course表之间的外键约束，stuID字段与student表的stuID字段存在外键约束；courseID字段与course表的courseID字段存在外键约束。）
\
teacher表（教师表）：
| 字段名 | 字段说明 | 字段类型 | 长度 | 是否允许为空 | 说明 |
|---|---|---|---|---|---|
| teacherID | 教师编号 | char | 3 | 否 | 主键 |
| teacherName | 教师姓名 | char | 8 | 否 | |
| teacherSex | 教师性别 | char | 2 | 是 |
teaching表（教师授课表）：
| 字段名 | 字段说明 | 字段类型 | 长度 | 是否允许为空 | 说明 |
|---|---|---|---|---|---|
| teacherID | 教师编号 | char | 3 | 否 | 主键 |
| courseID | 课程号 | char | 3 | 否 | 主键 |
（提示：此处需设置teaching表与teacher表和course表之间的外键约束，teacherID字段与teacher表的teacherID字段存在外键约束；courseID字段与course表的courseID字段存在外键约束。）
3、使用SQL语句插入下列所示的数据
student表：
| stuID | stuName | stuSex | Birthday |
|---|---|---|---|
| 2014030701 | 张三 | 男 | 1987-01-12 |
| 2014020701 | 王宁 | 男 | 1988-03-20 |
| 2014030702 | 王芳 | 女 | 1987-11-15 |
| 2014020704 | 李立 | 男 | 1986-12-30 |
| 2014030703 | 田甜 | 女 | 1987-09-10 |
course表 sc表
| stuID | courseID | Grade |
|---|---|---|
| 2014030701 | C02 | 98 |
| 2014030701 | C03 | 86 |
| 2014030703 | C01 | 79 |
| 2014030703 | C02 | 94 |
| 2014030703 | C03 | 55 |
\
| courseID | courseName |
|---|---|
| C01 | 大学英语 |
| C02 | 数据库原理及应用 |
| C03 | 操作系统 |
\
| teacherID | teacherName | teacherSex |
|---|---|---|
| t01 | 李勇 | 男 |
| t02 | 钱军 | 男 |
| t03 | 王旺 | 男 |
| t04 | 张成 | 男 |
| t05 | 李丽 | 女 |
\
\
teacher表 teaching表
| teacherID | courseID |
|---|---|
| t01 | C01 |
| t02 | C02 |
| t04 | C03 |
\
4、用SQL语句实现如下查询
1）查询所有男生的stuID、stuName、Birthday，要求显示为“学号”、“姓名”、 “出生日期”。
2）查询SC表中前3条选课记录信息。
3）查询所有姓“王”的学生的信息。
4）统计每门课程的选课人数和最高分。
5）查询选修了2门及2门以上课程的学生的学号。
6）查询选修“C03”课程的学生的平均年龄。
7）查询选修课程名为“数据库原理及应用”的学生的学号和姓名。
8）查询同时选修了“C02”和“C03”课程的学生的学号。
9）查询成绩比该课程平均成绩高的学生的学号及成绩。
10）把“钱军”老师所讲授课程的选课记录全部删除。
\
5、创建如下存储过程并调用
要求：在该数据库中创建一个名为proc_stuID的存储过程，实现当用户输入某一学号，然后显示出该学号对应的学生所选课程的课程名及成绩。并调用该存储过程查看学号为’ 2014030701’的同学所选修的课程名及考试成绩。
\
6、将创建的数据库进行备份
要求：利用Navicat图形工具，对自己创建的数据库进行全部备份，备份文件名为softwarebackup，（其中为自己学号的后两位，如1号同学的备份文件名应为：software01backup），并将该备份文件复制到桌面上。
