mysql视图

定义：

视图是从数据库的一个或者多个表导出的一个虚拟表；

特点：

方便用户操作（要求所见即所需，无需加额外查询条件，直接查看）

增加数据的安全性（用户只能查看或修改指定的数据）

提高表的独立逻辑性（原有数据表结构变化不会影响视图，修改视图会改变原有表）；

1、查看视图：

查看所有视图：select table_name from information_schema.views;

查看视图状态：show table status like 'view_name';

mysql> show table status like 'view2'\G;

*************************** 1. row ***************************

           Name: view2

         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.00 sec)

查看视图创建过程：

show create table view_name;

mysql> show create table view2\G;

*************************** 1. row ***************************

                View: view2

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select 'ddf' AS `name`

character_set_client: gbk

collation_connection: gbk_chinese_ci

1 row in set (0.00 sec)

views表中查看：

select * from information_schema.views where table_name='view_name';

mysql> select * from information_schema.views where table_name='view2'\G;

*************************** 1. row ***************************

       TABLE_CATALOG: def

        TABLE_SCHEMA: example

          TABLE_NAME: view2

     VIEW_DEFINITION: select 'ddf' AS `name`

        CHECK_OPTION: NONE

        IS_UPDATABLE: NO

             DEFINER: root@localhost

       SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

1 row in set (0.02 sec)

查看视图字段：

desc view_name;

mysql> desc view2;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| name  | varchar(3) | NO   |     |         |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.01 sec)

2、修改视图：

通过create or replace修改：

create or replace view view_name (字段) as select  from 表；

mysql> create or replace view view2(name) as select name from employee;

Query OK, 0 rows affected (0.05 sec)

通过alter 修改：

alter view view_name (字段） as select  字段 from 表

mysql> alter view view2(name) as select  name from employee;

Query OK, 0 rows affected (0.07 sec)

3、更新视图

update view_name set 字段=值 where 条件;

mysql> update view2 set name ='小乔' where name='大乔';

Query OK, 1 row affected (0.06 sec)

Rows matched: 1  Changed: 1  Warnings: 0

4、删除视图

drop [if exists] view view_name;

mysql> drop view view2;

Query OK, 0 rows affected (0.00 sec)

创建视图：属性名不能是函数

mysql> create view e1( sex,oo )

    -> as select  sex,group_concat(name) from employee

    -> group by sex;

Query OK, 0 rows affected (0.07 sec)

mysql> select * from e1;

+------+---------------------+

| sex  | oo                  |

+------+---------------------+

| 男   | 孙权,曹操,张飞,孔明 |

| 女   | 大乔                |

+------+---------------------+

2 rows in set (0.01 sec)