#[1] 定义视图
#(1) 定义信息系学生基本情况视图 V_IS
CREATE VIEW V_IS
AS
SELECT *
FROM student
WHERE department='信息系'
#(2) 将 Student,Course 和 SC表中学生的学号,姓名,课程号,课程名,成绩定义为视图 V_S_C_G
CREATE VIEW V_S_C_G
AS
SELECT student.sno,student.sname,course.cno,course.cname,score.grade
FROM student,course,score
WHERE student.sno=score.sno AND score.cno=course.cno
#(3) 将各系学生人数,平均身高定义为视图 V_NUM_AVG
CREATE VIEW V_NUM_AVG
AS
SELECT department,COUNT(*),AVG(height)
FROM student
GROUP BY department
#(4) 定义一个反映学生出生年份的视图 V_YEAR[即出生不同年份的学生人数总数]
CREATE VIEW V_YEAR
AS
SELECT COUNT(*)
FROM student
GROUP BY YEAR(birthday)
#(5) 将各位学生选修课程的门数及平均成绩定义为视图 V_AVG_S_G
CREATE VIEW V_AVG_S_G
AS
SELECT student.sno,COUNT(*),AVG(grade) AS avg #考虑null值是否参与计算平均值
FROM student JOIN score
ON student.sno=score.sno
GROUP BY student.sno
#(6) 将各门课程的选修人数及平均成绩定义为视图 V_AVG_C_G
CREATE VIEW V_AVG_C_G
AS
SELECT course.cname,COUNT(*),AVG(grade) AS avg
FROM course JOIN score
ON course.cn
MySQL——homework04
最新推荐文章于 2021-06-10 17:51:27 发布
本文详细探讨了MySQL数据库在作业中的应用,涵盖了SQL查询、表管理、索引优化等多个方面,旨在帮助读者深入理解MySQL的基本操作和优化技巧。
摘要由CSDN通过智能技术生成