MySQL(挑战3):简化数据操作提升性能
介绍
在挑战实验1中构建的成绩管理系统中,当有同学的分数被修改时,需要通过触发器将成绩表中的这条更新记录存到新的表 modifymark 中。同时需要创建一个存储过程用来显示数学课的全部学生的成绩,并按分数降序排列。另外,现需要为数据库新增一个用户,并为他分配在数据库 gradesystem 下所有表的 select 权限。
创建成绩管理系统数据库的代码在 createdb2.sql 中,请打开终端输入下载代码:
wget http://labfile.oss.aliyuncs.com/courses/9/createdb2.sql
下载的文件将保存在/home/shiyanlou/目录下。
数据库 gradesystem 中有三张表分别用于记录学生信息、课程信息和成绩信息。
数据库表的数据如下:
1.学生表(student):学生 id 、学生姓名和性别
2.课程表(course):课程 id 和课程名
3.成绩表(mark):成绩 id 、学生 id 、课程 id 和分数
4.成绩更新表(modifymark):成绩 id (m_mid)、学生 id (m_sid)、课程 id(m_cid) 、分数(m_score)和更新时间(m_time),表结构与 mark 表大致类似。
目标
1.MySQL 服务处于运行状态
2.下载运行 createdb2.sql 文件,完成数据库的建立
3.创建触发器 trigger_modify 实现当 mark 表有分数被修改时,将更新后的整条数据插入到 modifymark 表中。测试将 Tom 的化学成绩在原来的基础上加3分,查看 modifymark 中是否插入了数据
4.创建存储过程 math_proc 将数学课的全部学生的成绩按降序排列显示。包括课程名(cname) math、学生姓名(sname)以及分数(score)
5.创建用户 testuser,密码为 123456,并为其分配在数据库 gradesystem 下所有表的 select 权限
提示
执行 .sql 文件
触发器在更新操作后触发事件
order by 排序
grant 创建用户
过程
MySQL服务处于运行状态并登录:
sudo service mysql start
mysql -u root -p
下载运行 createdb2.sql 文件,完成数据库的建立:
source /home/shiyanlou #sql文件地址
1.创建触发器 trigger_modify 实现当 mark 表有分数被修改时,将更新后的整条数据插入到 modifymark 表中:
create trigger trigger_modify before update on mark
for each row
insert into modifymark value (NULL,new.sid, new.cid, new.score,now());
测试将 Tom 的化学成绩在原来的基础上加3分
update mark set score = score + 3
where sid = (select sid from student where sname = 'Tom')
and cid = (select cid from course where cname = 'chemistry');
查看 modifymark 中是否插入了数据
select * from modifymark ;
结果:
2.创建存储过程 math_proc 将数学课的全部学生的成绩按降序排列显示。包括课程名(cname) math、学生姓名(sname)以及分数(score):
创建存储过程 :(用mysql客户端 delimiter 命令来把语句定界符从 ; 变为 // 。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。)
delimiter //
create procedure math_proc ()
begin
select 'math', sname, score
from student, mark
where mark.cid = (select cid from course where cname = 'math')
and student.sid = mark.sid
order by score desc;
end
//
delimiter ;
调用过程程序:
call math_proc();
结果:
3.创建用户 testuser,密码为 123456,并为其分配在数据库 gradesystem 下所有表的 select 权限:
grant select on *.* to 'testuser'@'localhost' identified by '123456';