mysql视图

1.创建视图

1.1在单个表上创建视图

首先新建表格

+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |      85 |
|    2 | jack |   80 |      90 |
|    3 | lucy |   73 |      80 |
+------+------+------+---------+

创建视图

create view view_stu as select math,chinese,math+chinese from student;

使用select语句查看视图

select * from view_stu;
+------+---------+--------------+
| math | chinese | math+chinese |
+------+---------+--------------+
|   89 |      85 |          174 |
|   80 |      90 |          170 |
|   73 |      80 |          153 |
+------+---------+--------------+

还可以使用create view view_stu(math,chi,sum) as select math,chinese,math+chinese from student;为视图列进行重命名。

1.2多表创建视图

新创建一个表格：

+------+--------+-----------+
| s_id | class  | addr      |
+------+--------+-----------+
|    1 | erban  | anhui     |
|    2 | sanban | chongqing |
|    3 | yiban  | shandong  |
+------+--------+-----------+

创建视图

create view stu_class(id,name,glass) as select student.s_id,student.name,stu_idfo.class from student,stu_info where student.s_id=stu_info.s_id;

结果：

select * from stu_class;
+------+------+--------+
| id   | name | glass  |
+------+------+--------+
|    1 | tom  | erban  |
|    2 | jack | sanban |
|    3 | lucy | yiban  |
+------+------+--------+
3 rows in set (0.01 sec)

使用show table status like '[表名|视图名]'可以查看是否是视图或者是表。
使用show create view [视图名]查看详细信息。

2.修改视图

使用create or replace view [视图名] as select [...]
对1中的student表的视图进行修改

create or replace view view_stu as select * from student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id    | int(3)      | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| math    | float       | YES  |     | NULL    |       |
| chinese | float       | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

也可以使用alter语句修改视图

alter view view_stu as select chinese from student;

3.更新视图

视图是虚拟的表，所以当对视图进行更新增删时，实际是对真是的数据表进行处理。

3.1使用update更新视图。

我们更新使chinese=100，在更新视图之前，先查看原视图和student表中的数据。

mysql> select chinese from student;
+---------+
| chinese |
+---------+
|      85 |
|      90 |
|      80 |
+---------+
mysql> select chinese from view_stu;
+---------+
| chinese |
+---------+
|      85 |
|      90 |
|      80 |
+---------+

表中数据为85，90，80。现在使用update更新数据：

update view_stu set chinese=100;

结果如下：

mysql>select chinese from view_stu;
+---------+
| chinese |
+---------+
|     100 |
|     100 |
|     100 |
+---------+

此时再查看student表中的数据：

mysql> select * from student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |     100 |
|    2 | jack |   80 |     100 |
|    3 | lucy |   73 |     100 |
+------+------+------+---------+

chinese成绩已经被修改。

3.2使用insert语句更新视图

效果其实与插入数据是一样的，可以理解为：随着数据表的实际变化，视图也会随着变化。举例如下：
注意三个表随着sql语句而变化。

mysql> select * from student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |     100 |
|    2 | jack |   80 |     100 |
|    3 | lucy |   73 |     100 |
+------+------+------+---------+
3 rows in set (0.00 sec)

mysql> insert into student values(4,'leo',90,99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |     100 |
|    2 | jack |   80 |     100 |
|    3 | lucy |   73 |     100 |
|    4 | leo  |   90 |      99 |
+------+------+------+---------+
4 rows in set (0.00 sec)

mysql> select * from view_stu;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |     100 |
|    2 | jack |   80 |     100 |
|    3 | lucy |   73 |     100 |
|    4 | leo  |   90 |      99 |
+------+------+------+---------+
4 rows in set (0.00 sec)

3.3 使用delete语句更新视图

删除视图某些数据，会同步更新到实际的数据表中。

mysql> delete from view_stu where name='leo';
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_stu;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |     100 |
|    2 | jack |   80 |     100 |
|    3 | lucy |   73 |     100 |
+------+------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | tom  |   89 |     100 |
|    2 | jack |   80 |     100 |
|    3 | lucy |   73 |     100 |
+------+------+------+---------+
3 rows in set (0.00 sec)

注意有些情况下，视图将不能被更新。

4.删除视图

使用drop view if exists [表名]

mysql> drop view if exists view_stu;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from view_stu;
ERROR 1146 (42S02): Table 'db1.view_stu' doesn't exist