MySQL 基础教程[13]
- 问题1
- 问题1代码
- 问题2
- 问题2代码
- 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。
问题1
kwgl数据库中有学生基本信息表student和系别表dept。表结构及说明如下:
student (sid, sname, score, deptno), 各字段的含义分别是: 学号、姓名、成绩、系别编号。 dept (deptno, deptname), 各字段的含义分别是: 系别编号、系名称。
- 在数据库中建立数据表
, 包含的字段有编号 (SNO, 主键, 自动增长整数列) 、姓名 (SName, 字符类型, 长度为 10 ) 、性别 (Sex, 字符类型, 长度为 1) 、年龄 (Age, 整型) 。请使用SQL语句创建该表。
- 在学生基本信息表student的deptno字段上创建一个名称为in_stu的升序索引。
- 针对学生基本信息表student, 请使用SQL语句统计不同系别学生的平均成绩。要求统计输出的列标题（别名）是: 系别、平均成綪。
student (sid, sname, score, deptno), 各字段的含义分别是: 学号、姓名、成绩、系别编号。dept (deptno, deptname), 各字段的含义分别是: 系别编号、系名称。 - 请使用SQL语句查询“数学系”所有学生的学号、姓名和成绩。
- 创建一个新用户, 用户名为“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
- 现有Customers表, 其中列 customerNumber为客户编号 (整数列) 、列creditlimit信贷限额（整数列) 记录了用户当 前的透支上限。现创建一个存储过程, 要求根据输入的客户编号, 通过一个输出参数返回用户的评级情况 (字符 串）若当前信贷限额大于 50000 , 则用户评级为 “1st Level”; 若当前信贷限额大于等于 10000 且小于等于 50000 , 则 用户评级为"2nd Level"; 若当前信贷限额小于 10000 , 则用户评级为 “3rd Level”。
- 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))
BEGIN
DECLARE creditlim int;
SELECT creditlimit INTO creditlim
FROM customers
WHERE customerNumber = p_customerNumber;
IF creditlim > 50000 THEN
SET p_customerLevel = '1st Level';
ELSEIF (creditlim<=50000 && 10000<=creditlim) THEN
SET p_customerLevel = '2nd Level';
ELSEIF creditlim < 10000 THEN
SET 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 ;
