526互联

视图

发布时间 2023-11-15 00:50:10作者: 粥粥alg

musql视图介绍

1.1视图简介（一）

在后台，我们提供了一个 teachers 表的视图 v_teachers, 现在需要你查看这个视图中的内容。

SELECT *
FROM v_teachers;

1.2视图简介（二）

现在我们需要查看当前数据库中的所有表和视图名称和它的类型，请编写 SQL 语句来实现

show full tables;

第二章：创建视图

2.1CREATE VIEW(一)

现在，你的上级主管要求你写一个简单的视图，命名为 v_courses_teachers。要求提供 courses 表的所有信息，并且以 teacher_id、teacher_name 和 teacher_email 来展示关联表 teachers 中的主键、讲师姓名和讲师邮箱。

CREATE VIEW v_courses_teachers 
AS
SELECT c.*,t.name AS teacher_name ,t.email AS teacher_email 
FROM courses c
left join teachers t on c.teacher_id=t.id

2.2CREATE VIEW(二)

现在，你的上级主管要求你写一个简单的视图（v_courses）。要求从教师表（teachers）和课程表（courses）中查询国籍不为 USA 和 JP 的老师所教的课程和所对应的学生数量 student_count。

CREATE VIEW v_courses
AS
    SELECT c.id AS id, c.name AS name, c.student_count AS student_count
    FROM courses c    --询国籍不为 USA 和 JP 的老师所教的课程
    LEFT JOIN teachers t ON t.id = c.teacher_id
    WHERE t.`country` NOT IN ('USA', 'JP')
    OR t.country IS NULL

2.3CREATE VIEW(三)

我们提供了一个视图 v_courses，包含了教师 id 和该教师教授的总学生数。现在我们想要进一步获取更多的教师信息，请基于视图 v_courses ，创建一个新的视图 v_teachers 展示更多的信息。

CREATE VIEW v_teachers AS
SELECT teacher_id, t.name, email, age, country, c.student_count
FROM teachers t RIGHT JOIN v_courses c
ON t.id = c.teacher_id
ORDER BY t.id;

第三章 MySQL视图处理

3.1MySQL 视图处理算法（一）

请创建一个视图 v_teachers 查看 teachers 表中年龄大于25岁的教师信息，并使用 Merge 的视图算法

CREATE ALGORITHM = MERGE
VIEW v_teachers AS
SELECT * FROM teachers
WHERE age>25;

3.2MySQL 视图处理算法（二）

请创建一个视图 v_teachers 查看 teachers 表中国籍为 CN 的教师信息，并使用 TEMPTABLE 的视图算法

CREATE ALGORITHM=TEMPTABLE VIEW v_teachers 
 AS
 SELECT * FROM teachers WHERE country ='CN';

第四章：MySQL ALTER VIEW

4.1MySQL ALTER VIEW语句（一）

张三曾经有一个梦想，想要听遍学校里的每一位教师的课程，于是写了一个视图 v_mydream 展示了所有的课程与授课教师的信息，并按教师姓名升序去实现，但是张三发现课程实在是太多太多，梦想需要改一改，于是他把梦想改成了去听每一位教师最受欢迎的课程（听课学生越多越受欢迎，如果该教师没有教授课程则课程与学生数显示为 NULL），学校也非常支持他，听说他最近在学 MySQL，就给了他 2 张表的数据让他自己去找，teachers 表中记录着教师的信息，courses 表中记录着课程信息。张三一看，“ 哇~ 这也太简单了，感谢学校大大提供的数据 ”，可是他正要把原来的梦想视图改写，就发现李四从旁边过。李四肚子饿的呱呱叫，看到张三就问要不要一起去吃饭，张三灵光一闪，“ 这么巧~ 我也打算去吃饭，但是现在我这里有一个很简单的问题，你帮我解决了我就请你吃饭 ”，李四一听还有这等好事，立马答应下来了，结果发现不会。为了不掉面子并且能吃上一顿，就偷偷联系了你，请求你帮他解决。

alter view v_mydream as
select t.`name` as teacher_name, c.`name` as course_name, c.`student_count` as student_count
    from teachers t
    left join courses c on t.id = c.teacher_id
    where student_count = (select max(student_count) from courses where teacher_id = t.id)
        or student_count is null
    order by student_count,t.id;

第五章：创建 MySQL 可更新视图

5.1 MySQL 可更新视图(一)

这里提供了一个可更新的视图 v_teachers，请你编写 SQL 语句对 Linghu Chong 老师的年龄修改为 30 岁。

update v_teachers 
set age = 30 
where name = 'Linghu Chong';

5.2 MySQL 可更新视图(二)

这里提供了一个可更新的视图 v_teachers，请你编写 SQL 语句删除 Linghu Chong 老师的信息。
DELETE from v_teachers
where name = 'Linghu Chong';

第六章：MySQL 视图和 WITH CHECK OPTION

WITH CHECK OPTION 子句

现在要求创建一个可更新视图 v_teachers，只允许查看和插入年龄小于 30 岁的教师信息，请编写 SQL 语句实现

CREATE VIEW v_teachers AS
SELECT * FROM teachers WHERE age<30
WITH CHECK OPTION;

第七章：MySQL 显示视图

MySQL SHOW VERSION

现在我们需要查看当前数据库中的所有视图，请编写 SQL 语句来实现

SHOW FULL TABLES
WHERE table_type = 'VIEW'

第八章：MySQL 重命名视图

MySQL RENAME VIEW

现在我们有一个视图 v_teachers 需要重命名为 v_teachers_1，请编写 SQL 语句来实现

RENAME TABLE v_teachers to v_teachers_1;

第九章：MySQL 删除视图

9.1MySQL DROP VIEW（一）

现在我们要删除一个视图，名为 v_teachers ，请编写 SQL 语句来实现

DROP VIEW v_teachers;

9.2MySQL DROP VIEW（二）

现在我们要删除一个视图，名为 v_courses_teachers ，请编写 SQL 语句来实现

DROP VIEW IF EXISTS v_courses_teachers;

第十章：MySQL 视图本地和级联

现在我们一个可更新视图 v_teachers，只允许查看年龄小于 30 岁的教师信息，要求基于 v_teachers 视图实现另一个视图 v_teachers_1，查看年龄小于 20 岁的教师信息，视图采用本地检查，请编写 SQL 语句实现。

CREATE OR REPLACE VIEW v_teachers_1 AS 
    SELECT id,name,email,age,country
    FROM v_teachers 
    WHERE age<20;

第十一章：MySQL 管理视图

11.1MySQL 管理视图（一）

我们有一个视图 v_teachers，但是现在我们想要显示这个视图的定义，请编写 SQL 语句来实现
show create view v_teachers;

11.2MySQL 管理视图（二）

张三在宿舍里打游戏，今天运气不太好，总是被匹配的队友坑，气得张三直接就把游戏给卸载了。没心情去吃饭的张三打开 QQ，发现有 999+ 的信息，原来是学校的老师给他发来的消息，想要他帮忙创建一个 SQL 视图 v_best_teachers，要他赶紧写好发过去。张三看了看需求，边看要求边写 SQL，结果发现...不对！学校的要求是查出每个国家最受欢迎的老师信息（听课学生数越多越受欢迎），并按国籍升序排序。现在张三在知识分享过程中，拿这道题来考你，并嘲讽你不能做出来……小伙子，打脸张三的时候到了，还在等什么 ？冲

create or replace view v_best_teachers
as 
select teacher_id, student_count, country, email
from(
    select teacher_id, sum(student_count) student_count, country, email
    from courses c 
    inner join teachers t 
    on c.teacher_id = t.id
    group by teacher_id, country, email) a
where (student_count, country) in(
    select max(student_count), country
    from(
        select teacher_id, sum(student_count) student_count, country, email
        from courses c 
        inner join teachers t 
        on c.teacher_id = t.id
        group by teacher_id, country, email) aa
    group by country)
order by country;