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