MySQL 基础教程[12]

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

问题1

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

1. 使用SQL命令在选课表上根据学号建立一个索引"“index_选课学号".
2. 使用SQLL命令查询“操作系统”课程的学分。
3. 使用SQL命令查询同时选修“C语言程序设计”和“操作系统”两门课程的学生学号。
4. 设计一个视图 $\mathrm{V}$ _成绩 (课程名称、平均成绩) , 要求显示课程名称和每门课程平均成绩（保留 2 位小数）,并按平均 成绩降序排列。 (提示: 使用函数ROUND(x float, $\mathrm{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. 设计一个名称为 $\mathrm{t}\mathrm{r}$_ 选课的触发器, 完成的功能是: 当在选课表上揷入一条记录之前, 若该记录中的学号和课程名称 在学生表和课程表中不存在, 则在相关表中揷入相应记录。

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 ;