MySQL 基础教程[13]

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

问题1

kwgl数据库中有学生基本信息表student和系别表dept。表结构及说明如下:
student (sid, sname, score, deptno), 各字段的含义分别是: 学号、姓名、成绩、系别编号。 dept (deptno, deptname), 各字段的含义分别是: 系别编号、系名称。

1. 在数据库中建立数据表 $\mathrm{S}$, 包含的字段有编号 (SNO, 主键, 自动增长整数列) 、姓名 (SName, 字符类型, 长度为 10 ) 、性别 (Sex, 字符类型, 长度为 1) 、年龄 (Age, 整型) 。请使用SQL语句创建该表。
2. 在学生基本信息表student的deptno字段上创建一个名称为in_stu的升序索引。
3. 针对学生基本信息表student, 请使用SQL语句统计不同系别学生的平均成绩。要求统计输出的列标题（别名）是: 系别、平均成綪。
   student (sid, sname, score, deptno), 各字段的含义分别是: 学号、姓名、成绩、系别编号。dept (deptno, deptname), 各字段的含义分别是: 系别编号、系名称。
4. 请使用SQL语句查询“数学系”所有学生的学号、姓名和成绩。
5. 创建一个新用户, 用户名为“wang", 密码为“test1234", 指定登录服务器的IP为: 192.168.2.12。

问题1代码

#【1】
use kwgl;
create table s(
SNO int primary key auto_increment,
SName char(10),
Sex char(1),
Age int, 
);
#【2】
create index in_stu on student(deptno ASC);
#【3】
select deptno as "系别", avg(score) "平均成绩" from student group by deptno;
#【4】
select sid, sname,score from student where deptno=(select deptno from dept where deptname="数学系");
#【5】
create user "wang"@"192.168.2.12" identified by 'test1234';

问题2

1. 现有Customers表, 其中列 customerNumber为客户编号 (整数列) 、列creditlimit信贷限额（整数列) 记录了用户当 前的透支上限。现创建一个存储过程, 要求根据输入的客户编号, 通过一个输出参数返回用户的评级情况 (字符 串）若当前信贷限额大于 50000 , 则用户评级为 “1st Level”; 若当前信贷限额大于等于 10000 且小于等于 50000 , 则 用户评级为"2nd Level"; 若当前信贷限额小于 10000 , 则用户评级为 “3rd Level”。

2. kwg数据库中有学生基本信息表student, 系别表dept。表结构及说明如下:
   student (sid, sname, score, deptno), 各字段的含义分别是: 学号, 姓名、成绩、系别编号。 dept (deptno, deptname), 各字段的含义分别是: 系别编号、系名称。
   请修改函数GetAvgScoreByDeptName计算指定系的学生平均成绩, 输入系名字符串, 返回DOUBLE类型的平均成 绩。

问题2代码

#【1】
DELIMITER $$
CREATE procedure GetCustomerLevel(in  p_customerNumber int(11), out p_customerLevel  varchar(10))
BEGINDECLARE creditlim int;SELECT creditlimit INTO creditlimFROM customersWHERE customerNumber = p_customerNumber;IF creditlim > 50000 THENSET p_customerLevel = '1st Level';ELSEIF (creditlim<=50000 && 10000<=creditlim) THENSET p_customerLevel = '2nd Level';ELSEIF creditlim < 10000 THENSET p_customerLevel = '3rd Level';END IF;
END $$
DELIMITER ;#【2】
DELIMITER $$
CREATE FUNCTION GetAvgScoreByDeptName(name char(10))
RETURNS double
DETERMINISTIC
BEGIN
DECLARE tmpscore INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
DECLARE avgval DOUBLE DEFAULT 0;
DECLARE FOUND BOOLEAN DEFAULT TRUE;
DECLARE each_student CURSOR FOR
SELECT score FROM student s,dept d
WHERE d.deptname=name and s.deptno=d.deptno;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET FOUND=false;
OPEN each_student;
FETCH each_student INTO tmpscore;
WHILE FOUND DO
SET counter=counter+1;
SET total=total+tmpscore;
FETCH each_student INTO tmpscore;
END WHILE;
CLOSE each_student;
SET avgval=total/counter;
RETURN avgval;
END $$
DELIMITER ;