MySQL的视图

视图是从过一个或者多个表中导出来的表,是一种虚拟存在的表。试图可以使用用户的操作更方便,并且可以保障数据库系统的安全。

简介

视图可以从已经存在的视图基础上定义。数据库中存放了视图的定义,而并没有视图中的数据。使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。视图的数据以来于原来表中的数据。
MySQL的视图不支持输入参数的功能。

作用

  1. 操作简单化
  2. 增加数据的安全性
  3. 提高表的逻辑独立性

创建视图

创建视图是指在已存在的数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
创建视图时,需要有create view的权限。同时,应该具有查询设计的列的select权限。在MySQL数据库下面的user表中保存这些权限的信息,可以使用select查询。
algorithem,表示试图选择的算法,包括undefined、merge和temptable。undefined表示自动选择算法,merge表示合并,temptable表示存入临时表。
cascaded是可选参数,表示更新试图要满足所有视图和表的条件,为默认值。local表示更新视图时,要满足该试图本身的条件即可。

\\在已有表上创建视图user_view1
mysql> create view user_view1 as select * from user;
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  | UNI | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   | MUL | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
mysql> desc user_view1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   |     | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
\\两者显示出来的表的结果是一样的
\\在user表中创建只包含姓名、性别、年龄的视图
mysql> create view user_view2(name,sex,age) as select name,sex,age from user;
mysql> desc user_view2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
\\在多个表中建立跨表的视图
mysql> create algorithm=merge view stu_view( name,age,sex,stu_id,grade) as select name,age,sex,stu_id,grade from user,example2 where user.id = example2.id with local check option;
mysql> desc stu_view;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(30) | YES  |     | NULL    |       |
| age    | int(4)      | NO   |     | NULL    |       |
| sex    | tinyint(1)  | YES  |     | NULL    |       |
| stu_id | int(11)     | NO   |     | NULL    |       |
| grade  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

查看视图

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有show view的权限。

\\使用describe语句
mysql> desc stu_view;
\\使用show table status语句查看视图基本信息,注意like参数和单引号''
\\stu_view是虚拟表,所以属性值大多为空
mysql> show table status like 'stu_view'\G
*********** 1. row ********
           Name: stu_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
1 row in set (0.00 sec)
\\使用show create view查看视图详细信息
mysql> show create view stu_view\G
*************** 1. row **************
                View: stu_view
         Create View: CREATE ALGORITHM=MERGE DEFINER=`sujx`@`%` SQL SECURITY DEFINER VIEW `stu_view` AS select `user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`sex` AS `sex`,`example2`.`stu_id` AS `stu_id`,`example2`.`grade` AS `grade` from (`user` join `example2`) where (`user`.`id` = `example2`.`id`) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
\\在系统views表中查看视图详细信息
\\在MySQL中,所有视图的定义都存在information_schema数据库下的views表中。
mysql> select * from information_schema.views\G
*************** 1. row **************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: stu_view
     VIEW_DEFINITION: select `sujx`.`user`.`name` AS `name`,`sujx`.`user`.`age` AS `age`,`sujx`.`user`.`sex` AS `sex`,`sujx`.`example2`.`stu_id` AS `stu_id`,`sujx`.`example2`.`grade` AS `grade` from `sujx`.`user` join `sujx`.`example2` where (`sujx`.`user`.`id` = `sujx`.`example2`.`id`)
        CHECK_OPTION: LOCAL
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
**************** 2. row ***************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: user_view1
     VIEW_DEFINITION: select `sujx`.`user`.`id` AS `id`,`sujx`.`user`.`name` AS `name`,`sujx`.`user`.`num` AS `num`,`sujx`.`user`.`phone` AS `phone`,`sujx`.`user`.`address` AS `address`,`sujx`.`user`.`age` AS `age`,`sujx`.`user`.`sex` AS `sex` from `sujx`.`user`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
************** 3. row **************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: user_view2
     VIEW_DEFINITION: select `sujx`.`user`.`name` AS `name`,`sujx`.`user`.`sex` AS `sex`,`sujx`.`user`.`age` AS `age` from `sujx`.`user`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
3 rows in set (0.01 sec)

修改视图

修改视图是指修改数据库中存在的表的定义。

\\使用create or replace view语句修改视图
mysql> create or replace algorithm=temptable view stu_view(name,sex,grade) as select name,sex,grade from user,example2 where user.id = example2.id;
Query OK, 0 rows affected (0.01 sec)

mysql> desc stu_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
\\alter语句修改视图
mysql> alter view stu_view(stu_id,name,sex,grade) as select stu_id,name,sex,grade from user,example2 where user.id=example2.id;
mysql> desc stu_view;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int(11)     | NO   |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| sex    | tinyint(1)  | YES  |     | NULL    |       |
| grade  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

更新视图

更新视图是指通过视图来插入、更新和删除表中的数据。因为视图是一个虚拟表,其中没有数据。通过视图来更新时,都是转换到基本表来更新。更新视图时,只能更新权限范围内的数据。超过范围,就不能更新。

删除视图

mysql> drop view if exists stu_view;

Leave a Reply

Your email address will not be published. Required fields are marked *