MySQL 基础教程[13]

梦里梦外; 2023-10-04 23:24 17阅读 0赞

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

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

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

## 问题1 ##

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

1.  在数据库中建立数据表  S \\mathrm\{S\} 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))
    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 ;