MySQL 基础教程[12]
- 问题1
- 问题1代码
- 问题2
- 问题2代码
- 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。
问题1
41 在给定的学生选课数据库xsxk中, 有“学生”、“课程”、“选课”三张表。
其中：学生 (学号 字符型, 姓名字符型, 出生日期 日期型, 学院名称字符型), “学号”为主键; 课程 (课程名称字符 型, 课程学分整型), “课程名称"为主键;
选课 (课程名称字符型, 学号字符型, 成绩浮点型) , 其中 (“课程名称”、“学号”) 为复合主键, “学号”、“课程 名称“分别为指向“学生”、“课程”表中同名属性的外键。
- 使用SQL命令在选课表上根据学号建立一个索引"“index_选课学号".
- 使用SQLL命令查询“操作系统”课程的学分。
- 使用SQL命令查询同时选修“C语言程序设计”和“操作系统”两门课程的学生学号。
- 设计一个视图
- 创建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中, 有“学生”、“课程”、“选课”三张表。
其中:
学生 (学号 字符型, 姓名字符型, 出生日期日期型, 学院名称字符型), “学号”为主键;
课程 (课程名称字符型, 课程学分整型), “课程名称"为主键;
选课 (课程名称字符型, 学枵字符型, 成绩浮点型), 其中 (“课程名称”、“学号") 为复合主键, “学号”、“课程 名称"分别为指向“学生”、“课程"表中同名属性的外键。 在学生表和课程表中不存在, 则在相关表中插入相应记录。
- 设计一个名称为
_ 选课的触发器, 完成的功能是: 当在选课表上揷入一条记录之前, 若该记录中的学号和课程名称 在学生表和课程表中不存在, 则在相关表中揷入相应记录。
- 设计一个存储函数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 ;
