MySQL笔记之视图的使用详解
什么是视图
视图是从一个或多个表中导出来的表,是一种虚拟存在的表。
视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据。
这样,用户可以不用看到整个数据库中的数据,而之关心对自己有用的数据。
数据库中只存放了视图的定义,而没有存放视图中的数据,这些数据存放在原来的表中。
使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。
视图中的数据依赖于原来表中的数据,一旦表中数据发生改变,显示在视图中的数据也会发生改变。
视图的作用
1.使操作简单化,可以对经常使用的查询定义一个视图,使用户不必为同样的查询操作指定条件
2.增加数据的安全性,通过视图,用户只能查询和修改指定的数据。
3.提高表的逻辑独立性,视图可以屏蔽原有表结构变化带来的影响。
总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率
参考表:
创建视图的语法
create [algorithm]={undefined|merge|temptable}]
view 视图名 [(属性清单)]
as select 语句
[with [cascaded|local] check option];
algorithm表示视图选择的算法(可选参数)
undefined:mysql将自动选择所要使用的算法
merge:将视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分
temptable:将视图的结果存入临时表,然后使用临时表执行语句
视图名表示要创建的视图的名称
属性清单表示视图中的列名,默认与select查询结果中的列名相同(可选参数)
with check option表示更新视图时要保证在该试图的权限范围之内(可选参数)
cascaded:更新视图时要满足所有相关视图和表的条件
local:更新视图时,要满足该视图本身定义的条件即可
tips:创建试图时最好加上with cascaded check option参数,这种方式比较严格
可以保证数据的安全性
视图操作
在单表上创建视图
mysql> create view work_view(id,name,addr) as select id,name,address from work;
query ok, 0 rows affected (0.05 sec)
此处work_view为视图名,后面括号内的参数代表视图中的列
as表示将后面select 语句中的查询结果赋给前面的视图中
在多表上创建视图
mysql> create algorithm=merge view work_view2(id,name,salary)
-> as select work.id,name,salary from work,salary
-> where work.id=salary.id
-> with local check option;
query ok, 0 rows affected (0.02 sec)
在多表中创建视图需要两表有指定联系,如上面的work.id=salary.id
select查询视图
mysql> select * from work_view;
+----+--------+--------+
| id | name | addr |
+----+--------+--------+
| 1 | 张三 | 北京 |
| 2 | 李四 | 上海 |
| 3 | 王五 | 湖南 |
| 4 | 赵六 | 重庆 |
+----+--------+--------+
rows in set (0.00 sec)
此处的select语句用法和其他表中的用法一样
别忘了,视图也是一张表,只不过它是虚拟的
describe查看视图基本信息
mysql> describe work_view;
+-------+-------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | no | | null | |
| name | varchar(20) | no | | null | |
| addr | varchar(50) | yes | | null | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
与以往一样,此处的describe可以简写为desc
show table status查看视图基本信息
mysql> show table status like 'work_view'\g
*************************** 1. row ***************************
name: work_view
engine: null
version: null
row_format: null
rows: null
avg_row_length: null
data_length: null
max_data_length: null
index_length: null
data_free: null
auto_increment: null
create_time: null
update_time: null
check_time: null
collation: null
checksum: null
create_options: null
comment: view
row in set (0.00 sec)
此处大部分信息显示为null,更加说明了视图只是一张虚拟表
如果使用show table status查看一张真实表,结果就不会如此
show create view查看视图详细信息
mysql> show create view work_view\g
*************************** 1. row ***************************
view: work_view
create view: create algorithm=undefined definer=`root`@`localhost` sql security definer view `work_view` as select `work`.`id` as `id`,`work`.`name` as `name`,`work`.`address` as `addr` from `work`
character_set_client: utf8
collation_connection: utf8_general_ci
row in set (0.00 sec)
尼玛好复杂,这里包含了视图的各个属性
在views表中查看视图详细信息
mysql> select * from information_schema.views\g
*************************** 1. row ***************************
table_catalog: def
table_schema: person
table_name: work_view
view_definition: select `person`.`work`.`id` as `id`,`person`.`work`.`name` as `name`,`person`.`work`.`address` as `addr` from `person`.`work`
check_option: none
is_updatable: yes
definer: root@localhost
security_type: definer
character_set_client: utf8
collation_connection: utf8_general_ci
*************************** 2. row ***************************
table_catalog: def
table_schema: person
table_name: work_view2
information_schema.views表内包含了所有的视图定义信息
不过,通常使用show create view 更加方便
这里信息太长,没有完全列举……
修改视图
修改视图是指修改数据库中已存在的表的定义,当基本表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致
create or replace view语句修改视图
mysql> create or replace algorithm=temptable
-> view work_view(id,name)
-> as select id,name from work;
query ok, 0 rows affected (0.03 sec)
话说,create or replace语句非常灵活
在视图存在的情况下可对视图进行修改,视图不在的情况下可创建视图
其基本用法和create view 几乎一致
alter语句修改视图
mysql> alter view work_view2(name,salary,addr)
-> as select name,salary,address from work,salary
-> where work.id=salary.id;
query ok, 0 rows affected (0.03 sec)
我这把名字、工资和地址当做字段修改了视图
如果是真实的话,对小偷来说极为方便
更新视图
更新视图是指通过视图来插入、更新和删除表中的数据,以为视图是一个虚拟表,其中木有数据
通过视图更新时,都是转换到基本表来更新
mysql> update work_view2 set salary=5899.00 where name='张三';
query ok, 1 row affected (0.03 sec)
rows matched: 1 changed: 1 warnings: 0
此处语句等价于
mysql> update salary set salary=5899.00 where id=1;
tips:视图中虽然可以更新数据,但是有很多限制
一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据
删除视图
删除视图是指删除数据库中已存在的视图,删除视图时,只能删除视图的定义,不会删除数据
mysql> drop view if exists work_view;
query ok, 0 rows affected (0.00 sec)
mysql> drop view work_view2;
query ok, 0 rows affected (0.01 sec)
这里的if exist参数用来判断视图是否存在,也可以不写
下一篇: Android实现几种推送方式解决方案