1.视图是MySQL5.0.1版本加入的功能，它可以理解为一个虚表。
2.只储存了一个结构，并不是存储真实的数据。它的数据是在查询过程中动态生成的。
3.视图并不是真正的优化
2.创建视图
数据表结构
mysql> desc user; +-----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_name | varchar(50) | NO | | | | | password | varchar(64) | NO | | | | | mobile | varchar(20) | YES | | | | | email | varchar(50) | YES | | | | | last_login_time | int(11) | YES | | 0 | | | status | int(1) | NO | | 0 | | +-----------------+-------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
数据
mysql> select * from user; +----+------------+-----------------------+--------+-------+-----------------+--------+ | id | user_name | password | mobile | email | last_login_time | status | +----+------------+-----------------------+--------+-------+-----------------+--------+ | 12 | admin2 | sixstar206 | wq | qwqw | 0 | 1 | | 13 | admin3213 | sixstar20621321321321 | 12 | 121 | 0 | 0 | | 14 | admin123 | sixstar206 | qw | qw | 0 | 0 | | 16 | adminwfewf | sixstar206 | da | wq | 0 | 0 | | 17 | ad | sixstar206 | sad | ad | 0 | 0 | | 19 | ad12 | sixstar206 | ad | qw | 0 | 0 | | 23 | 1213123123 | sixstar206 | 12 | qw | 0 | 0 | | 24 | admfafaf | sixstar206 | 321 | qw | 0 | 0 | | 30 | 221 | 12 | | 12 | 0 | 1 | | 31 | 221 | 12 | | 12 | 0 | 0 | | 32 | 221 | 12 | | 12 | 0 | 0 | | 33 | fdsa | fwre | | | 0 | 1 | +----+------------+-----------------------+--------+-------+-----------------+--------+ 12 rows in set (0.00 sec)
创建视图
create view user_view as select id,user_name,password,status from user;
查看视图
mysql> desc user_view; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | user_name | varchar(50) | NO | | | | | password | varchar(64) | NO | | | | | status | int(1) | NO | | 0 | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
查看视图语法
mysql> show create view user_view; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | user_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_view` AS select `user`.`id` AS `id`,`user`.`user_name` AS `user_name`,`user`.`password` AS `password`,`user`.`status` AS `status` from `user` | utf8 | utf8_general_ci | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec)
查看视图内容
mysql> select * from user_view; +----+------------+-----------------------+--------+ | id | user_name | password | status | +----+------------+-----------------------+--------+ | 12 | admin2 | sixstar206 | 1 | | 13 | admin3213 | sixstar20621321321321 | 0 | | 14 | admin123 | sixstar206 | 0 | | 16 | adminwfewf | sixstar206 | 0 | | 17 | ad | sixstar206 | 0 | | 19 | ad12 | sixstar206 | 0 | | 23 | 1213123123 | sixstar206 | 0 | | 24 | admfafaf | sixstar206 | 0 | | 30 | 221 | 12 | 1 | | 31 | 221 | 12 | 0 | | 32 | 221 | 12 | 0 | | 33 | fdsa | fwre | 1 | +----+------------+-----------------------+--------+ 12 rows in set (0.04 sec)
ps：视图查询的数据来自于原数据中的内容，而视图是一个存储了一个结构，并不是存储真是的数据
类似于php中封装了一个查询方法的封装
class db
{
public function select()
{
return 'sql:select id,user,user_name,password,statud from user';
}
}
视图的优点：
1. 简化了操作（完全不用关心视图怎么处理数据，只需要知道如何使用这个结果集即可），视图相当于一个中间层。
2.安全 ，可以起到保护原表中的某些数据。
3.降低耦合（假如修改原表结构，可以通过修改视图的定义即可，而不用修改应用程序，对访问者不会造成影响）
视图缺点
1.表结构修改则需手动修改视图
视图IUD
表是可以更新数据的，这里的更新指的是“增删改”，但对于视图来说不一定。
/*修改视图数据*/
mysql> update user_view set status = 1 where id = 13;
/*查询视图数据*/ mysql> select * from user_view; +----+------------+-----------------------+--------+ | id | user_name | password | status | +----+------------+-----------------------+--------+ | 12 | admin2 | sixstar206 | 1 | | 13 | admin3213 | sixstar20621321321321 | 1 | | 14 | admin123 | sixstar206 | 0 | | 16 | adminwfewf | sixstar206 | 0 | | 17 | ad | sixstar206 | 0 | | 19 | ad12 | sixstar206 | 0 | | 23 | 1213123123 | sixstar206 | 0 | | 24 | admfafaf | sixstar206 | 0 | | 30 | 221 | 12 | 1 | | 31 | 221 | 12 | 0 | | 32 | 221 | 12 | 0 | | 33 | fdsa | fwre | 1 | +----+------------+-----------------------+--------+ 12 rows in set (0.00 sec)
删除视图
mysql> drop view user_view2;
以下是视图不可更新的情况
1.包含聚合函数、distinct、group by、having 、union、union all
2.常量视图
3.select包含子查询
4.包含连接操作
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表
创建新的视图
mysql> create view user_view2 as select id,user_name,password,status from user where status = 0; Query OK, 0 rows affected (0.03 sec)
mysql> select * from user_view2; +----+------------+------------+--------+ | id | user_name | password | status | +----+------------+------------+--------+ | 14 | admin123 | sixstar206 | 0 | | 16 | adminwfewf | sixstar206 | 0 | | 17 | ad | sixstar206 | 0 | | 19 | ad12 | sixstar206 | 0 | | 23 | 1213123123 | sixstar206 | 0 | | 24 | admfafaf | sixstar206 | 0 | | 31 | 221 | 12 | 0 | | 32 | 221 | 12 | 0 | +----+------------+------------+--------+ 8 rows in set (0.01 sec)
新增数据 status =1
mysql> insert into user_view2(id,user_name,password,status) values (34,'admin','admin',1); Query OK, 1 row affected (0.01 sec)
可以发现不满足视图的要求数据也可以插入基表
可以通过 with check option 设定只允许修改满足视图要求的数据，对于不满足视图要求的数据操作统统拒绝
mysql> create view user_view2 as select id,user_name,password,status from user where status = 0 where check option; Query OK, 0 rows affected (0.03 sec)
mysql> insert into user_view2(id,user_name,password,status) values (35,'admin','admin',1); ERROR 1369 (HY000): CHECK OPTION failed 'community.user_view2'
视图的修改
alter view 视图名 as select 语句;
视图应用&好处
1.提高了重要应，就像一个函数
如频繁的获取user表的name和goods表的name 使用链式语句
sql：select a.name as username,b.name as goodsname from user as a,goods as b,ng as c where a.id=c.uid and c.gid=b.id；
使用视图查询，创建视图 name
sql: create view name as select a.name as username,b.name as goodsname from user as a,goods as b,ng as c where a.id=c.uid and c.gid=b.id;
select * from name
可以直接通过视图获取user表的name和goods表的name
2.对数据库重构，不影响程序的运行
3.提高了安全性。针对不同用户
4.让数据更加清晰
posted on
