MySQL 基础教程[8]

• 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。

问题1

在数据库db_exam中有一成绩表score(id, stuid, cname, grade), 字段含义分别是: 编号、学号、课程名、分数。有 一学生表student(stuid, name, sex), 字段含义分别是: 学号、姓名、性别。

1. 现要求编写SQL语向, 在数据库dd_exam中新建一个部门表dept(deptno, dname, addr), 其中, 字段属性如下: 部 门编号(整型、主键), 部门名称(定长字符串、长度 20、不可为空), 部门地址(变长字符串、最大长度 50)。
2. 请编写SQL语句, 从score表中查询每门课程的最高分, 查诣结果的标题 为““课程名”和“最高分”。
3. 请编写SQL语句, 向学生表studenti添加一条学生记录, 内容如下:
   学号: 907, 姓名: 张敏, 性别: 女。
4. 请编写SQL语句, 将成绩表score中学号为906学生的英语成绩修改 为 90 分。
5. 新建一个用户 $\mathrm{abc}$ 主机名为 localhost, 用户密码为 123 , 同时将数据库 db_exam上score表的SELECT权限授予该用户。

问题1代码

#【1】
create table db_exam.dept(
deptno int primary key auto_increment,
dname char(20) not null,
addr varchar(50) 
);
#【2】
select cname as "课程名",max(grade) "最高分" from score group by cname;
#【3】
insert into student values(907, "张敏","女");
#【4】
update score set grade=90 where stuid=906 and cname="英语";
#【5】
create user "abc"@"localhost" identified by "123";
grant select on db_exam.score to "abc"@"localhost" with grant option;

问题2

在数据库db_exam中有商品表goods(id,name,num,price,lasttime)和订单表orders(oid,gid,quantity,total), 其中商品表的字 段名称分别是: 商品编号、商品名称、库存数量、单价和最后一次购买日期, 订单表的字段名称分别是: 订单编号、 商品编号、购买数量和总金额。

1. 请创建一个触发器, 要求在揷入新的订单记录时, 可根据该订单所对应的商品编号修改商品表中对应的最后一次 购亜日期。

2. 请创建一个存储过程sp $s$ addorder, 实现如下功能: 当揷入新的订单记录时, 可根据该订单所对应的商品编号和购买 数量, 自动给出订单的总金额。

问题2代码

#【1】
delimiter $
create trigger  tr_goodsnum
after insert on db_exam.orders
for each row 
begin
update goods set lasttime= curdate() where id =NEW.gid;
end $
delimiter ;


#【2】
DELIMITER $
CREATE PROCEDURE sp_addorder(in goodsid int, in quantity int)
BEGIN
	declare pr double;
	select price into pr from goods where id=goodsid;
	insert into orders(gid,quantity) values (goodsid, quantity);
	update orders set total= pr*quantity where gid =goodsid;
END $
DELIMITER ;