MySQL 基础教程[9]
- 问题1
- 问题1代码
- 问题2
- 问题2代码
- 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。
问题1
在数据库newtest中现有学生表和成绩表, 表结构如下: Student(stuid, name, sex) (说明: 学号, 姓名, 性别)
Score(id, stuid, cname, grade) (说明: 编号, 学号, 课程名, 分数)
- 请使用SQL语句在数据库newtest中创建职工表: Emp(empid, empname, streetname, cityname)；说明: empid 整 型、主键), empname (定长字符串、长度20、字符集GB2312、不可空), streetname (变长字符串、最大长度 50), cityname (变长字符串、最大长度30、不可空、缺省值为“N/A")。要求编写SQL语句, 创建该表。
- 编写SQL 语句, 按课程名进行分组, 并从组内平均分高于 85 分的分组中、找出该课程的最高分, 分别以“课程 名"和“最高分数”为别名输出查询结果。
- 编写SQL语句, 香询每个学生的总成绩, 结果显示学生的name、 stuid、 total等信息, 并按total升序排序, 其中 total 为学生总成绩的别名。
- 编写SQL语句, 创建一个学生查询课程成绩的视图StuView, 包含name、cname、 grade。
- 将newtest数据库所有表上的SELECT、UPDATE权限, 授予给用户
, 密码 123 , 登录机器IP: 192.168.1.100。
问题1代码
#【1】
use newtest;
create table emp(
empid int primary key auto_increment,
empname char(20) character set GB2312 not null,
streetname varchar(50),
cityname varchar(30) not null default "N/A"
);
#【2】
select cname 课程名,max(grade) 最高分数 from score group by cname having avg(grade) > 85;
#【3】
select s.name, s.stuid, s.sex , sum(e.grade) as total from student s, score e where s.stuid=e.stuid group by s.stuid order by total ASC;
#【4】
create view stuview as select name, cname, grade from student s, score e where s.stuid=e.stuid;
#【5】
grant select update on newtest.* to 'abc'@"192.168.1.100" identified by '123';问题2
在数据库newtest中, 现有goods表和orders表, 如下所示: goods(id, name, num) (说明: 商品编号, 商品名称, 库存数量) orders(oid, gid, quantity) (说明: 订单编号, 商品编号, 购买数量)
- 请创建一个存储函数拜_orderdetail, 要求根据指定的商品名称和订单编号, 查找并返回相应订单上商品的数量。
- 请创建一个存储过程sp_order, 要求向表orders揷入记录时, 根据揷入记录的商品编号和购买数量更新表goods中的 库存数量num。
问题2代码
#【1】
DELIMITER $
CREATE function fn_orderdetail(name VARCHAR(20), oid INT)
returns INT
DETERMINISTIC
BEGIN
declare tmp INT;
SELECT quantity INTO tmp FROM goods, orders
WHERE goods.id=orders.gid AND goods.name= name AND orders.oid =oid;
RETURN tmp;
END$
DELIMITER ;
#【2】
DELIMITER $
CREATE PROCEDURE sp_order(in gid int, in quantity int)
BEGIN
declare remain int;
insert into orders(gid,quantity) values (gid, quantity);
update goods set num= num-quantity where id =gid;
select num into remain from goods where id =gid;
select remain;
END $
DELIMITER ;
