第三部分——熟练使用mysql - 第十一章——熟练使用视图 - 《mysql基础教程》

11.1什么是视图
- 11.1.1视图的定义：
- 11.1.2视图的用途：
11.2使用视图：
- 11.2.1创建（定义）视图：
- 11.2.2通过视图更新列的值
11.3设置条件创建视图
- 11.3.1确认视图
11.4限制通过试图写入
11.5替换、修改删除视图

11.1什么是视图

11.1.1视图的定义：

  视图：将select提前的记录像表一样保存下来的虚表就是视图。视图不是表，他没有实体，只是一种信息。和表一样视图也可以进行select、upadate。如果更新视图，那么基表中的数据也会更新，更新基表，视图也会更新。

11.1.2视图的用途：

    可以将视图看做用户的个人喜好表<br />        视图只有在mysql5.5以上的版本才能使用

11.2使用视图：

11.2.1创建（定义）视图：

   **     语法：**

create view as 视图名 select 列名 from 表名 where 条件

       ** 示列一：根据表tb1中的name,age定义视图v1;**

mysql> update v1 set name='主任_佐藤' where name='佐藤';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from v1;
+-----------+------+
| name      | age  |
+-----------+------+
| 主任_佐藤 |   40 |
| 高桥      |   28 |
| 中川      |   20 |
| 渡边      |   23 |
| 西泽      |   35 |
+-----------+------+
5 rows in set (0.00 sec)

11.2.2通过视图更新列的值

视图值显示了基表中的一部分，如果更新了基表的值视图也会更新，如果更新了视图的值，基表也会更新。同样的更新视图的语法和更新表一样
示列一：更新v1视图的name值

mysql> update v1 set name='主任_佐藤' where name='佐藤';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from tb1;
+-------+-----------+------+
| empid | name      | age  |
+-------+-----------+------+
| A101  | 主任_佐藤 |   40 |
| A102  | 高桥      |   28 |
| A103  | 中川      |   20 |
| A104  | 渡边      |   23 |
| A105  | 西泽      |   35 |
+-------+-----------+------+
5 rows in set (0.00 sec)
mysql> select * from v1;
+-----------+------+
| name      | age  |
+-----------+------+
| 主任_佐藤 |   40 |
| 高桥      |   28 |
| 中川      |   20 |
| 渡边      |   23 |
| 西泽      |   35 |
+-----------+------+
5 rows in set (0.00 sec)

11.3设置条件创建视图

   我们可以通过where设置条件创建视图<br /> **示列**：创建销售额大于100的优秀员工信息视图

mysql> create view v2 as select tb.empid,tb1.name,tb.sales from tb join tb1 using(empid);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v2;
+-------+-----------+-------+
| empid | name      | sales |
+-------+-----------+-------+
| A103  | 中川      |   101 |
| A103  | 中川      |   101 |
| A104  | 渡边      |   181 |
| A101  | 主任_佐藤 |   184 |
| A103  | 中川      |    17 |
| A101  | 主任_佐藤 |   300 |
| A102  | 高桥      |   205 |
| A104  | 渡边      |    93 |
| A103  | 中川      |    12 |
+-------+-----------+-------+
9 rows in set (0.00 sec)

11.3.1确认视图

 和确认表一样，通过show table就可以，视图会与表交织在一起显示出来<br />**显示视图的列结构:**     <br />同样的通过desc 视图名；可以显示视图的列结构<br />**显示视图的详细信息：**

show create view 试图名

11.4限制通过试图写入

11.4.1对试图执行insert操作会出现什么结果

因为视图是从基表上随意收集的列，所以对试图执行insert操作，就意味着只能向表中的一部分内容插入数据，但是在使用了union、join、子查询的试图，不能执行insert和update。只要从一个表中提前列那么执行insertupdate是没有任何问题的<br />**示例：**向试图vj（通过基本tbj1产生的视图）中插入数据

mysql> insert into vj values('临时工_石田',18);
Query OK, 1 row affected (0.00 sec)
mysql> select *from vj;
+-------------+------+
| name        | age  |
+-------------+------+
| 佐藤        |   40 |
| 高桥        |   28 |
| 中川        |   20 |
| 渡边        |   23 |
| 西泽        |   35 |
| 临时工_石田 |   18 |
+-------------+------+
6 rows in set (0.00 sec)

mysql> select * from tbj1;
+-------+-------------+------+
| empid | name        | age  |
+-------+-------------+------+
| A101  | 佐藤        |   40 |
| A102  | 高桥        |   28 |
| A103  | 中川        |   20 |
| A104  | 渡边        |   23 |
| A105  | 西泽        |   35 |
| NULL  | 临时工_石田 |   18 |
+-------+-------------+------+
6 rows in set (0.00 sec)
mysql>

对视图插入数据，基表也会插入，但对于试图中没用定义的列基本中为null;

11.4.2向设置了条件的试图中insert

 如果向设置了条件的视图中插入违反条件的数据会怎么样？<br />  1.先创建tb表中sales>=100,只包含empid,sales列的视图v_tb

mysql>  create view  v_tb as select empid,sales from tb where sales>=100;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_tb;
+-------+-------+
| empid | sales |
+-------+-------+
| A103  |   101 |
| A103  |   101 |
| A104  |   181 |
| A101  |   184 |
| A101  |   300 |
| A102  |   205 |
+-------+-------+
6 rows in set (0.00 sec)

2.先视图中插入违反添加的数据


mysql> insert into v_tb values('恶意刁难',50);
Query OK, 1 row affected (0.01 sec)
mysql> select * from v_tb;
+-------+-------+
| empid | sales |
+-------+-------+
| A103  |   101 |
| A103  |   101 |
| A104  |   181 |
| A101  |   184 |
| A101  |   300 |
| A102  |   205 |
+-------+-------+
6 rows in set (0.00 sec)

mysql> select * from tb;
+----------+-------+-------+
| empid    | sales | month |
+----------+-------+-------+
| A103     |   101 |     4 |
| A103     |   101 |     4 |
| A104     |   181 |     5 |
| A101     |   184 |     4 |
| A103     |    17 |     5 |
| A101     |   300 |     5 |
| A102     |   205 |     6 |
| A104     |    93 |     5 |
| A103     |    12 |     6 |
| A107     |    87 |     6 |
| 恶意刁难 |    50 |  NULL |
+----------+-------+-------+
11 rows in set (0.00 sec)

通过上面我们发现，如果向视图中添加的条件违反创造时设置的条件，那么这条记录不会在视图中显示，但是会在基表中显示。

11.4.3当与视图条件不匹配时出错

对于通过添加创建的视图，无视条件的输入会带来麻烦。所以，我们可以在创建视图的时候将其设置为“不接受与条件不匹配的记录”
语法：

create view 视图名 as select …from 表名 where 条件 with check option

示列：创建值接受sales>100的视图

/*创建sales>100的视图v4*/
mysql> create view v4 as select empid,sales from tb where sales>=100 with  check option;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v4;
+-------+-------+
| empid | sales |
+-------+-------+
| A103  |   101 |
| A103  |   101 |
| A104  |   181 |
| A101  |   184 |
| A101  |   300 |
| A102  |   205 |
+-------+-------+
6 rows in set (0.00 sec)
/*插入不符合规定的数据*/
mysql> insert into v4 values('A105',50);
/*报错，无法插入此时*/
ERROR 1369 (HY000): CHECK OPTION failed 'db1.v4'

11.4.4通过多表连接创建的视图执行增删改查时;

1.删除、添加时都不可以：
2.在修改的时候可以，同时会对对应的基本中的列产生影响。（注意的是，修改后的记录如果不满足创建视图的添加的话，该基类就会从视图中移除）

mysql> show create view v2 \G
*************************** 1. row ***************************
                View: v2
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `tb`.`empid` AS `empid`,`tb`.`sales` AS `sales`,`tb1`.`name` AS `name` from (`tb` join `tb1` on((`tb`.`empid` = `tb1`.`empid`))) where (`tb`.`sales` >= 100)
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)
##:添加的时候
mysql> insert into v2 values('S121',105,'中东');
ERROR 1394 (HY000): Can not insert into join view 'db2.v2' without fields list
mysql> select * from v2;
+-------+-------+------+
| empid | sales | name |
+-------+-------+------+
| A101  |   184 | 佐藤 |
| A101  |   300 | 佐藤 |
| A102  |   205 | 高桥 |
| A103  |   101 | 中川 |
| A104  |   181 | 渡边 |
+-------+-------+------+
5 rows in set (0.00 sec)
##删除的时候
mysql> delete from v2 where empid='A104';
ERROR 1395 (HY000): Can not delete from join view 'db2.v2'
##修改的时候
mysql> update v2 set name='中东' where empid='A104';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from v2;
+-------+-------+------+
| empid | sales | name |
+-------+-------+------+
| A101  |   184 | 佐藤 |
| A101  |   300 | 佐藤 |
| A102  |   205 | 高桥 |
| A103  |   101 | 中川 |
| A104  |   181 | 中东 |
+-------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb1;
+-------+------+------+
| empid | name | age  |
+-------+------+------+
| A101  | 佐藤 |   40 |
| A102  | 高桥 |   28 |
| A103  | 中川 |   20 |
| A104  | 中东 |   23 |
| A105  | 西泽 |   35 |
| N111  | 松田 |   38 |
| N121  | 日下 |   20 |
+-------+------+------+
##修改后的条件不满足视图创建时的条件后
mysql> update v2 set sales=82 where empid='A104';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from v1;
+------------+------+
| name       | age  |
+------------+------+
| 主任·佐藤 |   40 |
| 高桥       |   28 |
| 中川       |   20 |
| 渡边       |   23 |
| 西泽       |   35 |
| 松田       |   38 |
| ss         |   20 |
+------------+------+
7 rows in set (0.00 sec)

mysql> select *from v2;
+-------+-------+------+
| empid | sales | name |
+-------+-------+------+
| A101  |   184 | 佐藤 |
| A101  |   300 | 佐藤 |
| A102  |   205 | 高桥 |
| A103  |   101 | 中川 |
+-------+-------+------+
4 rows in set (0.00 sec)

mysql> select* from tb;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |    61 |     4 |
| A103  |   101 |     4 |
| A104  |    82 |     5 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+
10 rows in set (0.00 sec)

mysql>

11.5替换、修改删除视图

11.5.1替换视图

         当存在同名的视图是如何替换？可以在创建视图的同时加入or raplace 来替换视图，也就是会删除存在的同名视图。<br />   **语法：**

create or replace view 视图名 as select …

  该语句不管同名的视图是否存在都可以创建视图<br />** 示列：创建同名视图v1；**

mysql> select * from v1;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A103  |   101 |     4 |
| A104  |   181 |     5 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+
10 rows in set (0.00 sec)

mysql> create or replace view v1 as select now();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1;
+---------------------+
| now()               |
+---------------------+
| 2021-06-02 08:58:29 |
+---------------------+
1 row in set (0.00 sec)

mysql>

11.5.2修改视图

       当修改视图结果是和修改表结构一样，使用alter view <br /> **   语法：**

alter view 视图名 as select 列名 from 表名;

示列：修改视图v1的结构

mysql> select * from v1;
+---------------------+
| now()               |
+---------------------+
| 2021-06-02 08:58:29 |
+---------------------+
1 row in set (0.00 sec)

mysql> desc v1;
+-------+----------+------+-----+---------------------+-------+
| Field | Type     | Null | Key | Default             | Extra |
+-------+----------+------+-----+---------------------+-------+
| now() | datetime | NO   |     | 0000-00-00 00:00:00 |       |
+-------+----------+------+-----+---------------------+-------+
1 row in set (0.00 sec)

mysql> alter view v1 as select name,age from tb1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v1;
+-------+-------------+------+-----+------------+-------+
| Field | Type        | Null | Key | Default    | Extra |
+-------+-------------+------+-----+------------+-------+
| name  | varchar(10) | YES  |     | 未输入名字 |       |
| age   | int(11)     | YES  |     | NULL       |       |
+-------+-------------+------+-----+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from v1;
+------+------+
| name | age  |
+------+------+
| 佐藤 |   40 |
| 高桥 |   28 |
| 中川 |   20 |
| 渡边 |   23 |
| 西泽 |   35 |
+------+------+
5 rows in set (0.00 sec)

mysql>

11.5.3删除视图

 语法：

drop view (if exists) 视图名

如果加上if exists既是视图不存在也不会报错
示列：删除视图v1

mysql> drop  view if exists v1;
Query OK, 0 rows affected (0.00 sec)
/*再次删除依然成功*/
mysql> drop  view if exists v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>