MySQL 基础教程[9]

左手的ㄟ右手 2023-10-04 23:24 2阅读 0赞

#### MySQL 基础教程\[9\] ####

*  问题1
 *  问题1代码
 *  问题2
 *  问题2代码

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

## 问题1 ##

在数据库newtest中现有学生表和成绩表, 表结构如下: Student(stuid, name, sex) (说明: 学号, 姓名, 性别)  
Score(id, stuid, cname, grade) (说明: 编号, 学号, 课程名, 分数)

1.  请使用SQL语句在数据库newtest中创建职工表: Emp(empid, empname, streetname, cityname)；说明: empid 整 型、主键), empname (定长字符串、长度20、字符集GB2312、不可空), streetname (变长字符串、最大长度 50), cityname (变长字符串、最大长度30、不可空、缺省值为“N/A")。要求编写SQL语句, 创建该表。
2.  编写SQL 语句, 按课程名进行分组, 并从组内平均分高于 85 分的分组中、找出该课程的最高分, 分别以“课程 名"和“最高分数”为别名输出查询结果。
3.  编写SQL语句, 香询每个学生的总成绩, 结果显示学生的name、 stuid、 total等信息, 并按total升序排序, 其中 total 为学生总成绩的别名。
4.  编写SQL语句, 创建一个学生查询课程成绩的视图StuView, 包含name、cname、 grade。
5.  将newtest数据库所有表上的SELECT、UPDATE权限, 授予给用户  a b c \\mathrm\{abc\} abc, 密码 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) (说明: 订单编号, 商品编号, 购买数量)

1.  请创建一个存储函数拜\_orderdetail, 要求根据指定的商品名称和订单编号, 查找并返回相应订单上商品的数量。
2.  请创建一个存储过程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 ;