• 数据库系统原理与应用教程（081）—— MySQL 视图（View）的创建与使用

    数据库系统原理与应用教程（081）—— MySQL 视图（View）的创建与使用

    目录

    一、MySQL 视图概述

    MySQL中的视图（view）是一种虚拟表，其内容由查询定义，视图本身并不包含数据。视图看起来和真实的表完全相同，但其中的数据来自定义视图时用到的基本表，并且在打开视图时动态生成。

    视图可以在基本表上定义，也可以使用其他视图定义。与直接操作基本表相比，视图具备以下优点：

    （1）简化操作：通过视图可以使用户将注意力集中在他所关心的数据上。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。

    （2）提高数据的安全性：在设计数据库时可以针对不同的用户定义不同的视图，使用视图的用户只能访问他们被允许查询的结果集。

    （3）数据独立：视图的结构定义好之后，如果增加新的关系或对原有的关系增加新的字段对用户访问的数据都不会造成影响。

    二、MySQL 创建视图的语法

    MySQL 创建视图的基本语法如下：

    CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]
/*
说明：
1、OR REPLACE：如果要创建的视图名称已存在，则替换已有视图。
2、ALGORITHM：可选参数，表示视图选择的算法，默认算法是 UNDEFINED。
3、view_name：要创建的视图名称。
4、column_list：可选参数，表示视图的字段列表。如果省略，则使用 select 语句中的字段列表。
5、AS select_statement：创建视图的 select 语句。
6、WITH CHECK OPTION：表示更新视图时要保证该视图的 WHERE 子句为真。
   比如定义视图：
   create view v1 as select * from salary > 5000;
   如果要更新视图，则必须保证 salary 字段的值在 5000 以上，否则报错。
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    三、创建 MySQL 视图

    1、创建表，并输入数据
    /*
DROP TABLE IF EXISTS student; 
CREATE TABLE student(
    s_id char(5) primary key,
    s_name char(20),
    birth datetime,
    phone char(20),
    addr varchar(100)
);

INSERT INTO student
VALUES('S2011','张晓刚','1999-12-3','13163735775','信阳市'),
('S2012','刘小青','1999-10-11','13603732255','新乡市'),
('S2013','曹梦德','1998-2-13','13853735522','郑州市'),
('S2014','刘艳','1998-6-24','13623735335','郑州市'),
('S2015','刘岩','1999-7-6','13813735225','信阳市'),
('S2016','刘若非','2000-8-31','13683735533','开封市'),
('S2021','董雯花','2000-7-30','13533735564','开封市'),
('S2022','周华建','1999-5-25','13243735578','郑州市'),
('S2023','特狼普','1999-6-21','13343735588','新乡市'),
('S2024','奥吧码','2000-10-17','13843735885','信阳市'),
('S2025','周健华','2000-8-22','13788736655','开封市'),
('S2026','张学有','1998-7-6','13743735566','郑州市'),
('S2031','李明博','1999-10-26','13643732222','郑州市'),
('S2032','达芬奇','1999-12-31','13043731234','郑州市');

DROP TABLE IF EXISTS teacher; 
CREATE TABLE teacher(
    t_id char(5) primary key,
    t_name char(20),
    job_title char(20),
    phone char(20)
);

INSERT INTO teacher
VALUES('T8001','欧阳修','教授','13703735666'),
('T8002','祖冲之','教授','13703735888'),
('T8003','李时珍','教授','13703735675'),
('T8004','牛盾','教授','13703735638'),
('T8005','李白','副教授','13703735828'),
('T8006','孔子','教授','13703735457'),
('T8007','王安石','副教授','13703735369');

DROP TABLE IF EXISTS course; 
CREATE TABLE course(
    c_id char(4) primary key,
    c_name char(20),
    t_id char(5)
);

INSERT INTO course
VALUES('C101','古代文学','T8001'),
('C102','高等数学','T8002'),
('C103','线性代数','T8002'),
('C104','临床医学','T8003'),
('C105','传染病学','T8003'),
('C106','大学物理','T8004'),
('C107','诗歌欣赏','T8005'),
('C108','教育学','T8006'),
('C109','刑事诉讼法','T8007'),
('C110','经济法','T8007');

DROP TABLE IF EXISTS score; 
CREATE TABLE score(
    s_id char(5),
    c_id char(4),
    score int,
    primary key(s_id, c_id)
);

INSERT INTO score
VALUES('S2011','C102',84),('S2011','C105',90),('S2011','C106',79),('S2011','C109',65),
('S2012','C101',67),('S2012','C102',52),('S2012','C103',55),('S2012','C104',86),
('S2012','C105',87),('S2012','C106',64),('S2012','C107',62),
('S2012','C108',73),('S2012','C109',78),('S2012','C110',89),
('S2013','C102',97),('S2013','C103',68),('S2013','C104',66),('S2013','C105',68),
('S2014','C102',90),('S2014','C103',85),('S2014','C104',77),('S2014','C105',96),
('S2015','C101',69),('S2015','C102',66),('S2015','C103',88),('S2015','C104',69),
('S2015','C105',66),('S2015','C106',88),('S2015','C107',69),
('S2015','C108',66),('S2015','C109',88),('S2015','C110',69),
('S2016','C101',65),('S2016','C102',69),('S2016','C107',82),('S2016','C108',56),
('S2021','C102',72),('S2021','C103',90),('S2021','C104',90),('S2021','C105',57),
('S2022','C102',88),('S2022','C103',93),('S2022','C109',47),('S2022','C110',62),
('S2023','C102',68),('S2023','C103',86),('S2023','C109',56),('S2023','C110',91),
('S2024','C102',87),('S2024','C103',97),('S2024','C109',80),('S2024','C110',81),
('S2025','C102',61),('S2025','C105',62),('S2025','C106',87),('S2025','C109',82),
('S2026','C102',59),('S2026','C105',48),('S2026','C106',90),('S2026','C109',73);
*/

mysql> select * from student;
+-------+-----------+---------------------+-------------+-----------+
| s_id  | s_name    | birth               | phone       | addr      |
+-------+-----------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘岩      | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2016 | 刘若非    | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特狼普    | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥吧码    | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+---------------------+-------------+-----------+
14 rows in set (0.10 sec)

mysql> select * from teacher;
+-------+-----------+-----------+-------------+
| t_id  | t_name    | job_title | phone       |
+-------+-----------+-----------+-------------+
| T8001 | 欧阳修    | 教授      | 13703735666 |
| T8002 | 祖冲之    | 教授      | 13703735888 |
| T8003 | 李时珍    | 教授      | 13703735675 |
| T8004 | 牛盾      | 教授      | 13703735638 |
| T8005 | 李白      | 副教授    | 13703735828 |
| T8006 | 孔子      | 教授      | 13703735457 |
| T8007 | 王安石    | 副教授    | 13703735369 |
+-------+-----------+-----------+-------------+
7 rows in set (0.00 sec)

mysql> select * from course;
+------+-----------------+-------+
| c_id | c_name          | t_id  |
+------+-----------------+-------+
| C101 | 古代文学        | T8001 |
| C102 | 高等数学        | T8002 |
| C103 | 线性代数        | T8002 |
| C104 | 临床医学        | T8003 |
| C105 | 传染病学        | T8003 |
| C106 | 大学物理        | T8004 |
| C107 | 诗歌欣赏        | T8005 |
| C108 | 教育学          | T8006 |
| C109 | 刑事诉讼法      | T8007 |
| C110 | 经济法          | T8007 |
+------+-----------------+-------+
10 rows in set (0.00 sec)

mysql> select * from score;
+-------+------+-------+
| s_id  | c_id | score |
+-------+------+-------+
| S2011 | C102 |    84 |
| S2011 | C105 |    90 |
| S2011 | C106 |    79 |
| S2011 | C109 |    65 |
| S2012 | C101 |    67 |
| S2012 | C102 |    52 |
| S2012 | C103 |    55 |
| S2012 | C104 |    86 |
| S2012 | C105 |    87 |
| S2012 | C106 |    64 |
| S2012 | C107 |    62 |
| S2012 | C108 |    73 |
| S2012 | C109 |    78 |
| S2012 | C110 |    89 |
| S2013 | C102 |    97 |
| S2013 | C103 |    68 |
| S2013 | C104 |    66 |
| S2013 | C105 |    68 |
| S2014 | C102 |    90 |
| S2014 | C103 |    85 |
| S2014 | C104 |    77 |
| S2014 | C105 |    96 |
| S2015 | C101 |    69 |
| S2015 | C102 |    66 |
| S2015 | C103 |    88 |
| S2015 | C104 |    69 |
| S2015 | C105 |    66 |
| S2015 | C106 |    88 |
| S2015 | C107 |    69 |
| S2015 | C108 |    66 |
| S2015 | C109 |    88 |
| S2015 | C110 |    69 |
| S2016 | C101 |    65 |
| S2016 | C102 |    69 |
| S2016 | C107 |    82 |
| S2016 | C108 |    56 |
| S2021 | C102 |    72 |
| S2021 | C103 |    90 |
| S2021 | C104 |    90 |
| S2021 | C105 |    57 |
| S2022 | C102 |    88 |
| S2022 | C103 |    93 |
| S2022 | C109 |    47 |
| S2022 | C110 |    62 |
| S2023 | C102 |    68 |
| S2023 | C103 |    86 |
| S2023 | C109 |    56 |
| S2023 | C110 |    91 |
| S2024 | C102 |    87 |
| S2024 | C103 |    97 |
| S2024 | C109 |    80 |
| S2024 | C110 |    81 |
| S2025 | C102 |    61 |
| S2025 | C105 |    62 |
| S2025 | C106 |    87 |
| S2025 | C109 |    82 |
| S2026 | C102 |    59 |
| S2026 | C105 |    48 |
| S2026 | C106 |    90 |
| S2026 | C109 |    73 |
+-------+------+-------+
60 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    2、创建视图

    （1）视图来源于一张表

    /*
create or replace view v_stu
as select s_id, s_name, year(now()) - year(birth) age
from student;
*/
mysql> create or replace view v_stu
    -> as select s_id, s_name, year(now()) - year(birth) age
    -> from student;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from v_stu;
+-------+-----------+------+
| s_id  | s_name    | age  |
+-------+-----------+------+
| S2011 | 张晓刚    |   23 |
| S2012 | 刘小青    |   23 |
| S2013 | 曹梦德    |   24 |
| S2014 | 刘艳      |   24 |
| S2015 | 刘岩      |   23 |
| S2016 | 刘若非    |   22 |
| S2021 | 董雯花    |   22 |
| S2022 | 周华建    |   23 |
| S2023 | 特狼普    |   23 |
| S2024 | 奥吧码    |   22 |
| S2025 | 周健华    |   22 |
| S2026 | 张学有    |   24 |
| S2031 | 李明博    |   23 |
| S2032 | 达芬奇    |   23 |
+-------+-----------+------+
14 rows in set (0.05 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    （2）视图来源于多张表

    /*
create or replace view v_score
as select s.s_id, s.s_name, c.c_id, c.c_name, sc.score
from student s join score sc on s.s_id = sc.s_id
join course c on sc.c_id = c.c_id;
*/
mysql> create or replace view v_score
    -> as select s.s_id, s.s_name, c.c_id, c.c_name, sc.score
    -> from student s join score sc on s.s_id = sc.s_id
    -> join course c on sc.c_id = c.c_id;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from v_score;
+-------+-----------+------+-----------------+-------+
| s_id  | s_name    | c_id | c_name          | score |
+-------+-----------+------+-----------------+-------+
| S2011 | 张晓刚    | C102 | 高等数学        |    84 |
| S2011 | 张晓刚    | C105 | 传染病学        |    90 |
| S2011 | 张晓刚    | C106 | 大学物理        |    79 |
| S2011 | 张晓刚    | C109 | 刑事诉讼法      |    65 |
| S2012 | 刘小青    | C101 | 古代文学        |    67 |
| S2012 | 刘小青    | C102 | 高等数学        |    52 |
| S2012 | 刘小青    | C103 | 线性代数        |    55 |
| S2012 | 刘小青    | C104 | 临床医学        |    86 |
| S2012 | 刘小青    | C105 | 传染病学        |    87 |
| S2012 | 刘小青    | C106 | 大学物理        |    64 |
| S2012 | 刘小青    | C107 | 诗歌欣赏        |    62 |
| S2012 | 刘小青    | C108 | 教育学          |    73 |
| S2012 | 刘小青    | C109 | 刑事诉讼法      |    78 |
| S2012 | 刘小青    | C110 | 经济法          |    89 |
| S2013 | 曹梦德    | C102 | 高等数学        |    97 |
| S2013 | 曹梦德    | C103 | 线性代数        |    68 |
| S2013 | 曹梦德    | C104 | 临床医学        |    66 |
| S2013 | 曹梦德    | C105 | 传染病学        |    68 |
| S2014 | 刘艳      | C102 | 高等数学        |    90 |
| S2014 | 刘艳      | C103 | 线性代数        |    85 |
| S2014 | 刘艳      | C104 | 临床医学        |    77 |
| S2014 | 刘艳      | C105 | 传染病学        |    96 |
| S2015 | 刘岩      | C101 | 古代文学        |    69 |
| S2015 | 刘岩      | C102 | 高等数学        |    66 |
| S2015 | 刘岩      | C103 | 线性代数        |    88 |
| S2015 | 刘岩      | C104 | 临床医学        |    69 |
| S2015 | 刘岩      | C105 | 传染病学        |    66 |
| S2015 | 刘岩      | C106 | 大学物理        |    88 |
| S2015 | 刘岩      | C107 | 诗歌欣赏        |    69 |
| S2015 | 刘岩      | C108 | 教育学          |    66 |
| S2015 | 刘岩      | C109 | 刑事诉讼法      |    88 |
| S2015 | 刘岩      | C110 | 经济法          |    69 |
| S2016 | 刘若非    | C101 | 古代文学        |    65 |
| S2016 | 刘若非    | C102 | 高等数学        |    69 |
| S2016 | 刘若非    | C107 | 诗歌欣赏        |    82 |
| S2016 | 刘若非    | C108 | 教育学          |    56 |
| S2021 | 董雯花    | C102 | 高等数学        |    72 |
| S2021 | 董雯花    | C103 | 线性代数        |    90 |
| S2021 | 董雯花    | C104 | 临床医学        |    90 |
| S2021 | 董雯花    | C105 | 传染病学        |    57 |
| S2022 | 周华建    | C102 | 高等数学        |    88 |
| S2022 | 周华建    | C103 | 线性代数        |    93 |
| S2022 | 周华建    | C109 | 刑事诉讼法      |    47 |
| S2022 | 周华建    | C110 | 经济法          |    62 |
| S2023 | 特狼普    | C102 | 高等数学        |    68 |
| S2023 | 特狼普    | C103 | 线性代数        |    86 |
| S2023 | 特狼普    | C109 | 刑事诉讼法      |    56 |
| S2023 | 特狼普    | C110 | 经济法          |    91 |
| S2024 | 奥吧码    | C102 | 高等数学        |    87 |
| S2024 | 奥吧码    | C103 | 线性代数        |    97 |
| S2024 | 奥吧码    | C109 | 刑事诉讼法      |    80 |
| S2024 | 奥吧码    | C110 | 经济法          |    81 |
| S2025 | 周健华    | C102 | 高等数学        |    61 |
| S2025 | 周健华    | C105 | 传染病学        |    62 |
| S2025 | 周健华    | C106 | 大学物理        |    87 |
| S2025 | 周健华    | C109 | 刑事诉讼法      |    82 |
| S2026 | 张学有    | C102 | 高等数学        |    59 |
| S2026 | 张学有    | C105 | 传染病学        |    48 |
| S2026 | 张学有    | C106 | 大学物理        |    90 |
| S2026 | 张学有    | C109 | 刑事诉讼法      |    73 |
+-------+-----------+------+-----------------+-------+
60 rows in set (0.07 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78

    （3）指定视图的列名

    /*
create or replace view v_stu_avg_score(sid, sname, avg_score)
as select s.s_id, s.s_name, avg(sc.score)
from student s join score sc on s.s_id = sc.s_id
group by s.s_id, s.s_name;
*/
mysql> create or replace view v_stu_avg_score(sid, sname, avg_score)
    -> as select s.s_id, s.s_name, avg(sc.score)
    -> from student s join score sc on s.s_id = sc.s_id
    -> group by s.s_id, s.s_name;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_stu_avg_score;
+-------+-----------+-----------+
| sid   | sname     | avg_score |
+-------+-----------+-----------+
| S2011 | 张晓刚    |   79.5000 |
| S2012 | 刘小青    |   71.3000 |
| S2013 | 曹梦德    |   74.7500 |
| S2014 | 刘艳      |   87.0000 |
| S2015 | 刘岩      |   73.8000 |
| S2016 | 刘若非    |   68.0000 |
| S2021 | 董雯花    |   77.2500 |
| S2022 | 周华建    |   72.5000 |
| S2023 | 特狼普    |   75.2500 |
| S2024 | 奥吧码    |   86.2500 |
| S2025 | 周健华    |   73.0000 |
| S2026 | 张学有    |   67.5000 |
+-------+-----------+-----------+
12 rows in set (0.06 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    （4）指定 WITH CHECK OPTION 参数限制更新

    /*
create or replace view v_stu_zz
as select * from student where addr = '郑州市'
with check option;
*/
mysql> create or replace view v_stu_zz
    -> as select * from student where addr = '郑州市'
    -> with check option;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_stu_zz;
+-------+-----------+---------------------+-------------+-----------+
| s_id  | s_name    | birth               | phone       | addr      |
+-------+-----------+---------------------+-------------+-----------+
| S2013 | 曹梦德    | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2022 | 周华建    | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2026 | 张学有    | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+---------------------+-------------+-----------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    更新视图 v_stu_zz，更新失败：

    mysql> update v_stu_zz set addr = '新乡市' where s_id = 'S2014';
ERROR 1369 (HY000): CHECK OPTION failed 'mydb.v_stu_zz'

    • 1
    • 2

    四、查看 MySQL 视图信息

    1、使用 show tables 命令查看数据库中的视图名称
     mysql> show tables;
+-----------------+
| Tables_in_mydb  |
+-----------------+
| course          |
| president       |
| score           |
| student         |
| teacher         |
| v_score         |
| v_stu           |
| v_stu_avg_score |
| v_stu_zz        |
+-----------------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    2、使用 desc 命令查看视图结构
    mysql> desc v_stu;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| s_id   | char(5)  | NO   |     | NULL    |       |
| s_name | char(20) | YES  |     | NULL    |       |
| age    | int(5)   | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.08 sec)

mysql> desc v_score;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| s_id   | char(5)  | NO   |     | NULL    |       |
| s_name | char(20) | YES  |     | NULL    |       |
| c_id   | char(4)  | NO   |     | NULL    |       |
| c_name | char(20) | YES  |     | NULL    |       |
| score  | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    3、使用 show create view 命令查看创建视图语句
    mysql> show create view v_score\G
*************************** 1. row ***************************
                View: v_score
         Create View: 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_score` AS select `s`.`s_id` AS `s_id`,`s`.`s_name` AS `s_name`,`c`.
`c_id` AS `c_id`,`c`.`c_name` AS `c_name`,`sc`.`score` AS `score` from ((`student` `s` join `score` `sc` on((`s`.`s_id` = `sc`.`s_id`))) join `course` `c` on((`sc`.`c_id` = `c`.`c_id`)))character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    五、更新视图数据

    更新视图中的数据，实际上是更新创建视图时用到的基本表中的数据。以下类型的视图不可更新：

    （1）包含以下关键字的 SQL 语句：聚合函数、distinct、group by 、having、union 或 uinon all；

    （2）select 中包含子查询；

    （3）from 一个不可更新的试图；

    （4）where 子句的子查询引用了 from 子句中的表。

    1、更新包含聚合函数的视图
    mysql> show create view v_stu\G
*************************** 1. row ***************************
                View: v_stu
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_stu` AS select `student`.`s_id` AS `s_id`,`student`.`s_name` AS `s_
name`,(year(now()) - year(`student`.`birth`)) AS `age` from `student`character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> select * from v_stu;
+-------+-----------+------+
| s_id  | s_name    | age  |
+-------+-----------+------+
| S2011 | 张晓刚    |   23 |
| S2012 | 刘小青    |   23 |
| S2013 | 曹梦德    |   24 |
| S2014 | 刘艳      |   24 |
| S2015 | 刘岩      |   23 |
| S2016 | 刘若非    |   22 |
| S2021 | 董雯花    |   22 |
| S2022 | 周华建    |   23 |
| S2023 | 特狼普    |   23 |
| S2024 | 奥吧码    |   22 |
| S2025 | 周健华    |   22 |
| S2026 | 张学有    |   24 |
| S2031 | 李明博    |   23 |
| S2032 | 达芬奇    |   23 |
+-------+-----------+------+
14 rows in set (0.00 sec)

mysql> update v_stu set s_name = '刘艳艳' where s_id = 'S2014';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from v_stu;
+-------+-----------+------+
| s_id  | s_name    | age  |
+-------+-----------+------+
| S2011 | 张晓刚    |   23 |
| S2012 | 刘小青    |   23 |
| S2013 | 曹梦德    |   24 |
| S2014 | 刘艳艳    |   24 |
| S2015 | 刘岩      |   23 |
| S2016 | 刘若非    |   22 |
| S2021 | 董雯花    |   22 |
| S2022 | 周华建    |   23 |
| S2023 | 特狼普    |   23 |
| S2024 | 奥吧码    |   22 |
| S2025 | 周健华    |   22 |
| S2026 | 张学有    |   24 |
| S2031 | 李明博    |   23 |
| S2032 | 达芬奇    |   23 |
+-------+-----------+------+
14 rows in set (0.00 sec)

-- 更新失败
mysql> update v_stu set age = 25 where s_id = 'S2014';
ERROR 1348 (HY000): Column 'age' is not updatable

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    2、更新包含 distinct 的视图
    mysql> create or replace view v_stu001 
    -> as select distinct s_id, s_name from student;
Query OK, 0 rows affected (0.03 sec)

-- 更新失败
mysql> update v_stu001 set s_name = '刘艳' where s_id = 'S2014';
ERROR 1288 (HY000): The target table v_stu001 of the UPDATE is not updatable

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    3、更新包含 group by 和 having 的视图
    /*
create or replace view v_stu002
as select addr, count(*) cnt 
from student group by addr
having cnt > 2;
*/
mysql> create or replace view v_stu002
    -> as select addr, count(*) cnt 
    -> from student group by addr
    -> having cnt > 2;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from v_stu002;
+-----------+-----+
| addr      | cnt |
+-----------+-----+
| 信阳市    |   3 |
| 开封市    |   3 |
| 郑州市    |   6 |
+-----------+-----+
3 rows in set (0.03 sec)

-- 更新失败
mysql> update v_stu002 set addr = '郑州' where addr = '郑州市';
ERROR 1288 (HY000): The target table v_stu002 of the UPDATE is not updatable

mysql> update v_stu002 set cnt = 8 where addr = '郑州市';
ERROR 1288 (HY000): The target table v_stu002 of the UPDATE is not updatable

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    4、更新包含子查询的视图
    /*
create or replace view v_stu003
as select * from student
where birth > (select birth from student where s_id = 'S2021');
*/
mysql> create or replace view v_stu003
    -> as select * from student
    -> where birth > (select birth from student where s_id = 'S2021');
Query OK, 0 rows affected (0.04 sec)

mysql> select * from v_stu003;
+-------+-----------+---------------------+-------------+-----------+
| s_id  | s_name    | birth               | phone       | addr      |
+-------+-----------+---------------------+-------------+-----------+
| S2016 | 刘若非    | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2024 | 奥吧码    | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
+-------+-----------+---------------------+-------------+-----------+
3 rows in set (0.02 sec)

-- 更新失败
mysql> update v_stu003 set addr = '郑州市' where s_id = 'S2025';
ERROR 1288 (HY000): The target table v_stu003 of the UPDATE is not updatable

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    5、更新包含 with check option 参数的视图
    /*
create or replace view v_stu004
as select * from student
where phone like '138%'
with check option;
*/
mysql> create or replace view v_stu004
    -> as select * from student
    -> where phone like '138%'
    -> with check option;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_stu004;
+-------+-----------+---------------------+-------------+-----------+
| s_id  | s_name    | birth               | phone       | addr      |
+-------+-----------+---------------------+-------------+-----------+
| S2013 | 曹梦德    | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2015 | 刘岩      | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2024 | 奥吧码    | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
+-------+-----------+---------------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> update v_stu004 set phone = '13805641222' where s_id = 'S2013';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from v_stu004;
+-------+-----------+---------------------+-------------+-----------+
| s_id  | s_name    | birth               | phone       | addr      |
+-------+-----------+---------------------+-------------+-----------+
| S2013 | 曹梦德    | 1998-02-13 00:00:00 | 13805641222 | 郑州市    |
| S2015 | 刘岩      | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2024 | 奥吧码    | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
+-------+-----------+---------------------+-------------+-----------+
3 rows in set (0.00 sec)

-- 更新失败
mysql> update v_stu004 set phone = '13723736666' where s_id = 'S2013';
ERROR 1369 (HY000): CHECK OPTION failed 'mydb.v_stu004'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    六、修改和删除视图

    1、使用 create or replace view 命令修改视图
    mysql> desc v_stu;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| s_id   | char(5)  | NO   |     | NULL    |       |
| s_name | char(20) | YES  |     | NULL    |       |
| age    | int(5)   | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)

/*
create or replace view v_stu 
as select s_id, s_name, year(now()) - year(birth) age, addr
from student;
*/
mysql> create or replace view v_stu 
    -> as select s_id, s_name, year(now()) - year(birth) age, addr
    -> from student;
Query OK, 0 rows affected (0.02 sec)

mysql> desc v_stu;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| s_id   | char(5)      | NO   |     | NULL    |       |
| s_name | char(20)     | YES  |     | NULL    |       |
| age    | int(5)       | YES  |     | NULL    |       |
| addr   | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    2、使用 alter view 命令修改视图
    mysql> show create view v_stu004\G
*************************** 1. row ***************************
                View: v_stu004
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_stu004` AS select `student`.`s_id` AS `s_id`,`student`.`s_name` AS 
`s_name`,`student`.`birth` AS `birth`,`student`.`phone` AS `phone`,`student`.`addr` AS `addr` from `student` where (`student`.`phone` like '138%') WITH CASCADED CHECK OPTION character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

/*
alter view v_stu004
as select * from student
where phone like '136%';
*/
mysql> alter view v_stu004
    -> as select * from student
    -> where phone like '136%';
Query OK, 0 rows affected (0.00 sec)

mysql> show create view v_stu004\G
*************************** 1. row ***************************
                View: v_stu004
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_stu004` AS select `student`.`s_id` AS `s_id`,`student`.`s_name` AS 
`s_name`,`student`.`birth` AS `birth`,`student`.`phone` AS `phone`,`student`.`addr` AS `addr` from `student` where (`student`.`phone` like '136%')character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    3、删除视图

    删除视图的命令如下：

    drop view [if exists] view_name;

    • 1

    删除视图 v_stu：

    mysql> show tables;
+-----------------+
| Tables_in_mydb  |
+-----------------+
| course          |
| president       |
| score           |
| student         |
| teacher         |
| v_score         |
| v_stu           |
| v_stu001        |
| v_stu002        |
| v_stu003        |
| v_stu004        |
| v_stu_avg_score |
| v_stu_zz        |
+-----------------+
13 rows in set (0.00 sec)

mysql> drop view v_stu;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-----------------+
| Tables_in_mydb  |
+-----------------+
| course          |
| president       |
| score           |
| student         |
| teacher         |
| v_score         |
| v_stu001        |
| v_stu002        |
| v_stu003        |
| v_stu004        |
| v_stu_avg_score |
| v_stu_zz        |
+-----------------+
12 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
  • 相关阅读:
    （66）MIPI DSI LLP介绍（六）
    好的架构是进化来的，不是设计来的
    buuctf web [极客大挑战 2019]LoveSQL
    列表常用方法
    935. 骑士拨号器
    QT pyside2 线程嵌套子线程 实现开始运行和停止运行
    k8s之图形界面DashBoard【九】
    Python数据结构：数字与字符串
    vue+antd——实现table表格的打印——分页换行，每页都有表头——基础积累
    Day 10：100322. 删除星号以后字典序最小的字符串
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126431872