MySQL 基础教程[12]

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

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

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

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

## 问题1 ##

41 在给定的学生选课数据库xsxk中, 有“学生”、“课程”、“选课”三张表。  
其中：学生 (学号 字符型, 姓名字符型, 出生日期 日期型, 学院名称字符型), “学号”为主键; 课程 (课程名称字符 型, 课程学分整型), “课程名称"为主键;  
选课 (课程名称字符型, 学号字符型, 成绩浮点型) , 其中 (“课程名称”、“学号”) 为复合主键, “学号”、“课程 名称“分别为指向“学生”、“课程”表中同名属性的外键。

1.  使用SQL命令在选课表上根据学号建立一个索引"“index\_选课学号".
2.  使用SQLL命令查询“操作系统”课程的学分。
3.  使用SQL命令查询同时选修“C语言程序设计”和“操作系统”两门课程的学生学号。
4.  设计一个视图  V \\mathrm\{V\} V \_成绩 (课程名称、平均成绩) , 要求显示课程名称和每门课程平均成绩（保留 2 位小数）,并按平均 成绩降序排列。 (提示: 使用函数ROUND(x float,  y \\mathrm\{y\} y int) 取小数位数)
5.  创建test用户, 主机名为localhost, 并将xsxk的所有权限赋予test用户。

## 问题1代码 ##

#【1】
    create index index_选课学号 on 选课(学号);
    #【2】
    select 课程学分 from 课程 where 课程名称="操作系统";
    #【3】
    select  学号 from 选课 where 课程名称="C语言程序设计" and 课程名称="操作系统"; //写错了！
    select  学号 from 选课 where 课程名称="C语言程序设计" and 学号 in (select  学号 from 选课 where 课程名称="操作系统);
    
    #【4】
    create view V_成绩(课程名称, 平均成绩) as select 课程名称， round(avg(成绩),2) from 选课 group by 课程名称 order by avg(成绩) DESC;
    #【5】
    create user test @localhost;
    grant all on xsxk.* to test@localhost;

## 问题2 ##

在给定的学生选课数据库xsxk中, 有“学生”、“课程”、“选课”三张表。  
其中:  
学生 (学号 字符型, 姓名字符型, 出生日期日期型, 学院名称字符型), “学号”为主键;  
课程 (课程名称字符型, 课程学分整型), “课程名称"为主键;  
选课 (课程名称字符型, 学枵字符型, 成绩浮点型), 其中 (“课程名称”、“学号") 为复合主键, “学号”、“课程 名称"分别为指向“学生”、“课程"表中同名属性的外键。 在学生表和课程表中不存在, 则在相关表中插入相应记录。

1.  设计一个名称为  t r \\mathrm\{tr\} tr\_ 选课的触发器, 完成的功能是: 当在选课表上揷入一条记录之前, 若该记录中的学号和课程名称 在学生表和课程表中不存在, 则在相关表中揷入相应记录。
2.  设计一个存储函数FN\_平均成绩, 根据学生姓名返回学生的平均成绩。

## 问题2代码 ##

#【1】
    DELIMITER $$
    CREATE TRIGGER tr_选课  before  INSERT ON 选课
    FOR EACH ROW
    BEGIN
      DECLARE sno,cno INT;
      SELECT COUNT(*) INTO sno FROM 学生 WHERE 学号=NEW.学号;
      SELECT COUNT(*) INTO cno FROM 课程 WHERE 课程名称=NEW.课程名称;
      IF(sno=0) THEN
        INSERT INTO 学生(学号) values(NEW.学号); 
      END IF;
      IF(cno=0) THEN
        INSERT INTO 课程(课程名称) values(NEW.课程名称); 
      END IF;
    END $$
    DELIMITER ;
    
    
    #【2】
    DELIMITER $$
    CREATE FUNCTION FN_平均成绩(XM VARCHAR(10)) 
    returns FLOAT
    DETERMINISTIC
    BEGIN 
    declare Grade float;
    SELECT AVG(成绩) INTO Grade FROM 选课, 学生
    WHERE 选课.学号=学生.学号 AND 姓名=XM; 
    RETURN Grade ;
    END $$
    DELIMITER ;