实例详解MySQL数据库视图

推荐学习：mysql视频教程

1 视图的介绍与作用

视图的介绍：

视图的作用：

2 视图的创建

创建视图的语法如下：

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]

参数说明：

3 视图的修改

修改视图是指修改数据库中已存在的表的定义。当基本表中的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间的一致。

语法格式：

alter view 视图名 as select语句;

4 视图的更新

并不是所有的视图都可以更新。可以在UPDATE、DELETE或INSERT等语句中使用视图，以更新基本表的内容。对于可更新的视图，在视图中的行和基本表中的行之间必须具有一对一的关系，如果视图包含下述结构中的任何一种，则该视图不可更新：

聚合函数（SUM()、MIN()、MAX()等）；

DISTINCT；

HAVING；

UNION或者UNION ALL；

位于选择列表中的子查询；

JOIN；

FROM子句中的不可更新视图；

WHERE子句中的子查询，引用FROM子句中的表；

仅使用文字值（在该情况下，没有要更新的基本表）。

注意：
视图中虽然可以更新数据，但是有很多限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。
当真实表中修改了某个存在视图中的字段时，视图需要更新，否则该视图就会变成无效视图！

5 视图的重命名与删除

重命名视图：

rename table 视图名 to 新视图名;

删除视图：

drop view if exists 视图名;

删除视图时，只删除了视图的定义，而并不会删除真实表中的数据

如果想同时删除多个视图，则使用下面的语法格式：

drop view if exists 视图名1, 视图名2, 视图名3...;

6 视图的练习

6.1 数据准备

在进行练习时可以先根据下面代码创建用于练习的两个基本表：

create table college(
    cno   int         null,
    cname varchar(20) null);

create table student(
    sid     int         null,
    name    varchar(20) null,
    gender  varchar(20) null,
    age     int         null,
    birth   date        null,
    address varchar(20) null,
    score   double      null);

两表的基本数据如下图所示：

6.2 查询平均分最高的学校名称

结合之前学过的知识可以 尝试使用子查询和连接查询 来实现，参考代码如下：

SELECT cname
FROM (SELECT cname, rank() over (order by avg_score desc ) item
      FROM (SELECT cname, avg(score) avg_score
            FROM student
                     JOIN college ON sid = cno
            GROUP BY cname) t) tt
WHERE item = 1;

在上述代码中，先将student 与 college两表关联，将关联的查询作为子表，并根据子表进行平均数的排序，平均数序号为1的平均分数最高，再以此为子表进行子查询，查询出了平均分最高的学校。具体结果如下：

这种方式虽然能够解决问题，但是相对复杂，不容易看懂，为了简化代码，我们可以将每一个子查询创建为一个视图

视图解决方式代码：

-- 1 视图一，连接两表并计算平均数
CREATE VIEW t_view AS
SELECT cname, avg(score) avg_score
FROM student
         JOIN college ON sid = cno
GROUP BY cname;

-- 2 视图二，利用视图一对平均分数进行排序标号
CREATE VIEW tt_view AS
SELECT cname, rank() over (order by avg_score desc ) item
FROM (t_view);

-- 3 利用视图查询
SELECT cname
FROM (tt_view)
WHERE item = 1;

在创建完视图后，如果想要查询平均分前三名学校，则方便很多，创建好的视图可以直接使用！

参考代码及结果：

SELECT cnameFROM (tt_view)WHERE item = 1
   OR item = 2
   OR item = 3;

推荐学习：mysql视频教程

以上就是实例详解MySQL数据库视图的详细内容，更多请关注恒创科技其它相关文章！