MySQL数据库 *实验14 视图

有不足或者错误的地方还望指出~

1、查询选修了2门以上的课并且选修的所有课成绩都在80分以上的同学，查看学号、姓名、成绩（这个示例以前用子查询做过，现在把所有子查询的代码改为视图，将所有创建视图的代码和最终结果贴图，体会下用视图的好处）

代码版

mysql> create or replace view v1 as
    -> select 学号,姓名,count(课程号) as 课程数,成绩
    -> from xs_kc
    -> join xs using(学号)
    -> group by 学号
    -> having 课程数 >= 2;
Query OK, 0 rows affected (0.01 sec)

mysql> create or replace view v2 as
    -> select 学号,姓名 from xs
    -> join xs_kc using(学号)
    -> group by(学号)
    -> having count(成绩) = sum(case when 成绩 >= 80 then 1 else 0 end);
Query OK, 0 rows affected (0.00 sec)

mysql> select 学号,姓名,成绩 from xs_kc
    -> join xs using(学号)
    -> where 学号 in
    -> (select 学号 from v1 join v2 using(学号));
+--------+------+------+
| 学号   | 姓名 | 成绩 |
+--------+------+------+
| 081102 | 程明 |   91 |
| 081102 | 程明 |   80 |
+--------+------+------+
2 rows in set (0.00 sec)

图片版

2、按课程和性别分类汇总每门课程的最高分（性别要显示“男”或“女”），显示（学号，姓名，性别，课程名，最高分）

代码版

mysql> create or replace view v_xs as
    -> select 学号,姓名,专业名,
    -> case
    -> when 性别 = 1 then '男'
    -> else '女'
    -> end as 性别,出生日期,总学分,照片,备注
    -> from xs;
Query OK, 0 rows affected (0.01 sec)

mysql> create or replace view max_xs_kc as
    -> select  学号,课程名,max(成绩) as 最高分 from xs_kc
    -> join kc using(课程号)
    -> join v_xs using(学号)
    -> group by 课程号,性别;
Query OK, 0 rows affected (0.01 sec)

mysql> select  学号,姓名,性别,课程名,最高分
    -> from v_xs
    -> join max_xs_kc using(学号)
    -> group by 课程名,性别;
+--------+--------+------+----------------+--------+
| 学号   | 姓名   | 性别 | 课程名         | 最高分 |
+--------+--------+------+----------------+--------+
| 081302 | 李红庆 | 男   | 操作系统       |     81 |
| 081101 | 王林   | 男   | 程序设计与语言 |     84 |
| 081101 | 王林   | 男   | 计算机基础     |     91 |
| 081203 | 严红   | 女   | 计算机基础     |     85 |
| 081102 | 程明   | 男   | 计算机网络     |     80 |
| 081203 | 严红   | 女   | 计算机网络     |     89 |
| 081302 | 李红庆 | 男   | 软件工程       |     89 |
| 081203 | 严红   | 女   | 软件工程       |     70 |
| 081203 | 严红   | 女   | 数据结构       |     88 |
+--------+--------+------+----------------+--------+
9 rows in set (0.00 sec)

图片版