【mysql的编程专题】视图

操作

创建视图

语法

CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(字段清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION];

• algorithm表示视图选择的算法（可选参数）
• Merge: 当引用视图时,引用视图的sql语句与定义sql视图的语句合并(相当于只存储了sql).
• Temptable:当引用视图时,根据视图的创建语句建立一个临时表.该临时表是一直存储在mysql数据库中的,除非认为删除;
• Undefined(默认):未定义,自动,让系统帮你选.

Merge

-- Merge,意味着视图只是一个规则,语句规则, 当查询视图时,把查询视图的语句(比如where那些)与创建时的语句where子句等合并,分析.再形成一条新的select语句.  -- 创建视图的语句: CREATE altorethm=merge VIEW `g2` AS SELECT `goods_id`,`cat_id`,`goods_name`,`shop_price` FROM `goods` ORDER BY `cat_id` ASC,`shop_price` DESC;  --查询视图的语句: SELECT * FROM `g2` GROUP BY `cat_id`;  -- 最终执行的语句: SELECT `goods_id`,`cat_id`,`goods_name`,`shop_price` FROM `goods` GROUP BY `cat_id` ORDER BY `cat_id` ASC,`shop_price` DESC;

temptable

-- temptable是根据创建语句瞬间创建一张临时表,然后查询视图的语句从该临时表查数据. CREATE ALTORETHM=TEMPTABLE VIEW `g2` AS SELECT `goods_id`,`cat_id`,`goods_name`,`shop_price` FROM `goods` ORDER BY `cat_id` ASC,`shop_price` DESC;  -- 查询视图的语句: SELECT * FROM `g2` GROUP BY `cat_id`;  -- 最终执行的2句话: 取数据并放在临时表,然后去查临时表.

• 字段清单表示视图中的列名，默认与SELECT查询结果中的列名相同（可选参数）
• WITH CHECK OPTION表示更新视图时要保证在该试图的权限范围之内（可选参数） 注意,with check option对于没有where条件的视图不起作用的
• CASCADED：更新视图时要满足所有相关视图和表的条件,创建试图时最好加上WITH CASCADED CHECK OPTION参数，这种方式比较严格,可以保证数据的安全性;cascaded是 WITH CHECK OPTION的默认值
• LOCAL：更新视图时，要满足该视图本身定义的条件即可

with [cascaded] check option

mysql> create view v1 as  select * from goods where stock > 100 WITH check OPTION; -- 使用了with check option 这个选项那后面对视图的插入只限于包含stock的表; Query OK, 0 rows affected  -- ↓↓ 只允许插入包含stock字段的sql mysql> INSERT into v1(gname) values('zhouzhou'); 1369 - CHECK OPTION failed 'test.v1' mysql> INSERT into v1(gname,stock) values('zhouzhou',8000); Query OK, 1 row affected  -- ↓↓ 不影响删除 mysql> delete from v1 where gname = 'ganggang'; Query OK, 2 rows affected  --  ↓↓ 貌似也不影响更新 mysql> update v1 set gname = 'test' where gid = 11; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 -- 其实错误,因为更新后查询出来的表字段里面能查询到stock > 100;所以可以更新;  -- ↓↓ 如果更新后的结构不能再通过stock>100来查询就不能进行更新; mysql> update v1 set stock = 99; 1369 - CHECK OPTION failed 'test.v1'

with local check option

Example1: 单表创建视图

CREATE VIEW v1 AS  SELECT   user_id, nickname, IF (sex = 1, '男', '女') as sex, user_money  FROM   users ORDER BY   user_money DESC LIMIT 10;  -- question: 该视图不能被更新? update v1 set user_money=5000 where user_id = 2659; [Err] 1288 - The target table v2 of the UPDATE is not updatable

Example1: 多表创建视图

CREATE VIEW v3 AS  SELECT   u.user_id,   nickname,   lhmoney,   user_recommend,   goods FROM   users AS u JOIN COMMENT AS c ON u.user_id = c.user_id ORDER BY   user_id ASC WITH CASCADED CHECK OPTION;

删除视图

mysql> drop view v1; -- drop view 视图名; Query OK, 0 rows affected

修改视图

CREATE OR REPLACE VIEW 修改视图

CREATE OR REPLACE语句非常灵活,在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图,其基本用法和CREATE VIEW 几乎一致

CREATE OR REPLACE ALGORITHM=TEMPTABLE  VIEW v2(uid,nickname,money) AS SELECT   user_id,   nickname,   user_money FROM   users ORDER BY   user_money DESC LIMIT 10;

ALTER VIEW 修改视图

AlTER VIEW  v3(uid,cid,nickname,lhb,content,goods) AS SELECT   u.user_id,   user_comment_id,   nickname,   lhmoney,   user_recommend,   goods FROM   users AS u JOIN COMMENT AS c ON u.user_id = c.user_id ORDER BY   user_id ASC WITH CASCADED CHECK OPTION;

关于更新视图数据

• 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
• DISTINCT
• GROUP BY
• HAVING
• UNION或UNION ALL
• 位于选择列表中的子查询
• Join
• FROM子句中的不可更新视图
• WHERE子句中的子查询，引用FROM子句中的表。
• 仅引用文字值（在该情况下，没有要更新的基本表）。
• ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

mysql> update v4 set sex=5 where user_id = 32; -- 一定程度上保证了基表数据的安全性 1288 - The target table v4 of the UPDATE is not updatable

• 不得有重复的视图列名称。
• 视图必须包含没有默认值的基表中的所有列。
• 视图列必须是简单的列引用而不是导出列。导出列不是简单的列引用，而是从表达式导出的。下面给出了一些导出列示例：
• 3.14159
• col1 + 3
• UPPER(col2)
• col3 / col4
• (subquery)

查看视图

• 查看视图数据与查看普通表一样

• 查看视图字段结构

  mysql> describe v3; +----------------+-------------+------+-----+---------+-------+ | Field          | Type        | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | user_id        | int(11)     | NO   |     | 0       |       | | nickname       | varchar(25) | NO   |     |         |       | | lhmoney        | int(11)     | NO   |     | 0       |       | | user_recommend | longtext    | YES  |     | NULL    |       | | goods          | int(10)     | NO   |     | 0       |       | +----------------+-------------+------+-----+---------+-------+

• 查看视图基本信息

mysql> show table status like 'v3'/G -- 该命令与查看普通表的命令是一样的,而此处大多数为NULL正是证明视图是一张虚表; *************************** 1. row ***************************      Name: v3    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 1 row in set (0.01 sec) mysql> show table status like 'users'/G *************************** 1. row ***************************      Name: users    Engine: InnoDB     Version: 10    Row_format: Compact      Rows: 171650  Avg_row_length: 82   Data_length: 14172160 Max_data_length: 0  Index_length: 11567104   Data_free: 66060288  Auto_increment: 169781   Create_time: 2015-05-01 23:03:29   Update_time: NULL    Check_time: NULL   Collation: utf8_general_ci    Checksum: NULL  Create_options:     Comment: 用户信息表 1 row in set (0.01 sec) 

• 查看视图详细信息

mysql> show create view v3/G *************************** 1. row ***************************                 View: v3          Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIE W `v3` AS select `u`.`user_id` AS `user_id`,`u`.`nickname` AS `nickname`,`u`.`lhmoney` AS `lhmoney`, `c`.`user_recommend` AS `user_recommend`,`c`.`goods` AS `goods` from (`users` `u` join `comment` `c`  on((`u`.`user_id` = `c`.`user_id`))) order by `u`.`user_id` WITH CASCADED CHECK OPTION character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.01 sec)

• 在information_schema的views表中查看视图详细信息;

mysql> SELECT * FROM information_schema.views/G *************************** 1. row ***************************  TABLE_CATALOG: def   TABLE_SCHEMA: test     TABLE_NAME: v1      VIEW_DEFINITION: select `test`.`users`.`user_id` AS `user_id`,`test`.`users`.`nickname` AS `nic kname`,if((`test`.`users`.`sex` = 1),'男','女') AS `sex`,`test`.`users`.`user_money` AS `user_money`  from `test`.`users` order by `test`.`users`.`user_money` desc limit 10   CHECK_OPTION: NONE   IS_UPDATABLE: NO        DEFINER: root@localhost  SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci 

作用

1. 可以简化查询,我们把复杂而又频繁的查询先存储为视图,下次查询的时候,直接在这个视图里面找;
2. 可以进行权限控制,Mysql的权限可以精确到表,但不能精确到列,但可以把表的权限封闭,另外创建一个视图,该视图中就是只存储开放了相应的权限列;
3. 大数据分表时可以用到:
   比如,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成4张表来存放.

比如

Select * from news2 where id = 17;```