MySQL 视图

     视图是一种虚拟的表

操作简单化

增加数据的安全性

提高表的逻辑独立性

语法：

CREATE [ORREPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

   VIEW view_name[(column_list)]

   AS select_statement

    [WITH [CASCADED | LOCAL] CHECKOPTION]

查看创建视图的权限

SELECT Select_priv,Create_view_priv FROM mysql.user WHEREUSER='root';

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

| Select_priv | Create_view_priv |

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

| Y             | Y                     |

| Y             | Y                     |

| Y             | Y                     |

| Y             | Y                     |

| Y             |Y                     |

| Y             |Y                     |

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

6 rows in set (0.00 sec)

练习表（自己创建）

mysql> desc department;

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

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

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

| d_id       | int(4)        | NO    | PRI   | NULL   |      |

| d_name   | varchar(20) | NO    | UNI  |NULL    |      |

| function  | varchar(50) | YES    |       |NULL    |      |

| address   | varchar(50) | YES    |       | NULL    |      |

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

4 rows in set (0.00 sec)

mysql> desc worker;

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

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

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

| num           | int(10)        | NO    | PRI  | NULL    |      |

| d_id            | int(4)        | YES   | MUL | NULL   |      |

| NAME         |varchar(20)   | NO   |      | NULL    |      |

| sex             | varchar(4)    | NO   |       | NULL    |      |

| birthday      | datetime      | YES  |       | NULL    |      |

| homeaddress | varchar(50)  | YES |       | NULL    |      |

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

6 rows in set (0.01 sec)

单表创建

CREATEVIEW department_view1

ASSELECT * FROM department;

mysql> desc department_view1;

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

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

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

| d_id     | int(4)     | NO  |     | NULL   |      |

| d_name   | varchar(20) | NO  |     | NULL   |      |

| function | varchar(50) | YES  |    | NULL   |      |

| address  | varchar(50) | YES |     | NULL   |      |

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

4 rows in set (0.00 sec)

CREATEVIEW department_view2(NAME,funciton,location)

ASSELECT d_name,FUNCTION,address FROM department;

desc department_view2;

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

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

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

| name     | varchar(20) |NO   |     |NULL    |      |

| funciton | varchar(50) | YES  |    | NULL   |      |

| location | varchar(50) | YES  |    | NULL   |      |

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

3 rows in set (0.00 sec)

多表创建视图

mysql> CREATE ALGORITHM=MERGE VIEW

    ->worker_view1(NAME,department,sex,age,address)

   -> ASSELECT NAME,department.d_name,sex,2009-birthday,address

   -> FROM worker,department WHEREworker.d_id=department.d_id

    -> WITHLOCAL CHECK OPTION;

Query OK, 0 rows affected (0.01 sec)

mysql> desc worker_view1;

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

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

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

| NAME      | varchar(20)  | NO  |     | NULL   |      |

| department | varchar(20)  | NO  |     | NULL   |      |

| sex       | varchar(4)   | NO  |     | NULL   |      |

| age       | double(23,6) | YES  |    | NULL   |      |

| address    | varchar(50) | YES  |    | NULL    |      |

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

5 rows in set (0.01 sec)

查看视图

mysql>  SHOW TABLE STATUSLIKE 'worker_view1' \G

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

          Name:worker_view1

        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)

mysql> SHOW CREATE VIEW worker_view1\G

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

              View: worker_view1

        Create View: CREATEALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINERVIEW `worker_view1` AS select `worker`.`NAME` AS`NAME`,`department`.`d_name` AS `department`,`worker`.`sex` AS`sex`,(2009 - `worker`.`birthday`) AS `age`,`department`.`address`AS `address` from (`worker` join `department`) where(`worker`.`d_id` = `department`.`d_id`) WITH LOCAL CHECKOPTION

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

在views表中查看视图详细信息

视图存在放information_schema数据库下的views表中

mysql> SELECT * FROM information_schema.VIEWS\G

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

      TABLE_CATALOG: def

       TABLE_SCHEMA: grzl

         TABLE_NAME:payment_view

    VIEW_DEFINITION: select`grzl`.`payment`.`payment_id` AS`payment_id`,`grzl`.`payment`.`amount` AS `amount` from`grzl`.`payment` where (`grzl`.`payment`.`amount` <10)

       CHECK_OPTION: CASCADED

       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:grzl

                    TABLE_NAME:payment_view1

           VIEW_DEFINITION: select`payment_view`.`payment_id` AS `payment_id`,`payment_view`.`amount`AS `amount` from `grzl`.`payment_view` where(`payment_view`.`amount` > 5)

              CHECK_OPTION: LOCAL

                  IS_UPDATABLE:YES

                           DEFINER:root@localhost

                  SECURITY_TYPE:DEFINER

    CHARACTER_SET_CLIENT:utf8

COLLATION_CONNECTION: utf8_general_ci

.............................................

CREATE OR REPLACE VIEW 语句修改视图

存在修改，不存在创建

mysql> CREATE OR REPLACE ALGORITHM=TEMPTABLE

   -> VIEW department_view1(department,funciton,location)

   -> ASSELECT d_name,FUNCTION,address FROM department;

Query OK, 0 rows affected (0.01 sec)

ALTER语句修改视图

mysql> ALTERVIEW department_view2(department,NAME,sex,location)

   -> ASSELECT d_name,worker.name,worker.sex,address

   -> FROM department,worker WHERE department.d_id=worker.d_id

   -> WITH CHECKOPTION;

Query OK, 0 rows affected (0.01 sec)

更新视图

mysql> SELECT * FROM department;

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

|d_id    | d_name    | function    | address    |

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

|1001    | 人事部    | 人事变动    | 2号楼3层   |

| 1002    |生产部    | 生产管理    | 5号楼1层   |

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

2 rows in set (0.00 sec)

CREATEVIEW department_view3(NAME,FUNCTION,address)

ASSELECT d_name,FUNCTION,address FROM department 

WHERE d_id=1001;

mysql> SELECT * FROM department_view3;

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

| NAME     | FUNCTION    | address     |

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

| 人事部    | 人事变动    | 2号楼3层   |

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

1 row in set (0.00 sec)

mysql> UPDATE department_view3 SET NAME='科研部',FUNCTION='新产品研发',address='3号楼五层';

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql>  SELECT * FROM department_view3;

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

| NAME     | FUNCTION       | address      |

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

| 科研部    | 新产品研发     | 3号楼五层    |

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

1 row in set (0.00 sec)

不能更新视图：

视图中含SUM(),MAX(),MIN()等函数

CREATEVIEW worker_view4(NAME,sex,total) ASSELECT NAME,sex,COUNT(NAME) FROM worker;

Query OK, 0 rows affected (0.06 sec)

mysql> UPDATE worker_view4 SET NAME='abc';

ERROR 1288(HY000): The target table worker_view4 of the UPDATE is notupdatable

视图中含UNION、UNION ALL、DISTINCT、GROUP BY、HAVING等关键字

CREATEVIEW worker_view5(NAME,sex,address) ASSELECT NAME,sex,homeaddress FROM worker GROUPBY d_id;

Query OK, 0 rows affected (0.00 sec)

UPDATE worker_view5 SET NAME='abc';

ERROR 1288(HY000): The target table worker_view5 of the UPDATE is notupdatable

常量视图

CREATEVIEW worker_view6 ASSELECT 'Aric' AS NAME;

Query OK, 0 rows affected (0.00 sec)

UPDATE worker_view6 SET NAME='abc';

ERROR 1288(HY000): The target table worker_view6 of the UPDATE is notupdatable

视图中的SELECT中包含子查询

CREATEVIEW worker_view7(NAME) ASSELECT (SELECT NAME FROM worker);

Query OK, 0 rows affected (0.00 sec)

UPDATE worker_view7 SET NAME='abc';

ERROR 1288(HY000): The target table worker_view7 of the UPDATE is notupdatable

由不可更新的视图导出的视图

CREATEVIEW worker_view8 AS SELECT * FROM worker_view7;

Query OK, 0 rows affected (0.00 sec)

UPDATE worker_view8 SET NAME='abc';

ERROR 1288(HY000): The target table worker_view8 of the UPDATE is notupdatable

创建视图是，ALGORITHM为TEMPTABLE类型

CREATE ALGORITHM=TEMPTABLEVIEW worker_view9 ASSELECT * FROM worker;

Query OK, 0 rows affected (0.01 sec)

UPDATE worker_view9 SET NAME='abc';

ERROR 1288 (HY000): The target table worker_view9 of theUPDATE is not updatable

视图的表上存在没有默认值得列，而且该列没有包含在视图里。

例如：表中包含name字段没有默认值，但是视图中不包括该字段。那么这个视图是不能更新的。

因为在更新时，这个没有默认值得记录将没有值插入，也没有NULL值插入。

删除视图

只能删视图的定义，不能删除数据，必须有drop权限

DROP VIEW [IF EXISTS] view_name [RESTRICT|CASCADE]

查看权限

mysql> SELECT Drop_priv FROM mysql.user WHERE USER='root';

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

| Drop_priv |

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

| Y        |

| Y        |

| Y        |

| Y        |

| Y        |

| Y        |

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

6 rows in set (0.00 sec)

删除视图

DROP VIEW IFEXISTS worker_view1;

Query OK, 0 rows affected (0.00 sec)

删多个

DROP VIEW IFEXISTS department_view1,department_view2;

Query OK, 0 rows affected (0.00 sec)

实例

创建work_info表

插入数据

创建视图info_view

查看视图info_view的基本机构和详细结构

查看视图info_view的所有记录

修改视图info_view

更新视图

删除视图

work_info表内容

字段名字段描述数据类型主键外键非空唯一自增id编号INT(10)是否是是否name姓名VARCHAR(20)否否是否否sex性别VARCHAR(4)否否是否否age年龄INT(5)否否否否否address家庭住址VARCHAR(50)否否否否否tel电话号码VARCHAR(20)否否否否否

work_info表中的信息

idnamesexageaddresstel1张三M18海淀12345672李四M22昌平23456783王五F17平谷34567894赵六F25顺义4567890

mysql> CREATETABLE work_info(id INT(10) NOTNULL UNIQUE PRIMARY KEY,

    ->NAME VARCHAR(20) NOT NULL,

    ->sex VARCHAR(4) NOT NULL,

    ->age INT(5),

    ->address VARCHAR(50),

    ->tel VARCHAR(20)

    ->);

Query OK, 0 rows affected (0.02 sec)

INSERTINTO work_info VALUES(1,'张三','M',18,'海淀','1234567');

INSERTINTO work_info VALUES(2,'李四','M',22,'昌平','2345678');

INSERTINTO work_info VALUES(3,'王五','F',17,'平谷','3456789');

INSERTINTO work_info VALUES(4,'赵六','F',25,'顺义','4567890');

创建视图info_view，从work_info表中选出age>20的记录来创建视图。视图的字段包含id、name、sex和address.ALGORITHM设置为MERGE类型加上WITHLOCAL CHECK OPTION条件

mysql> CREATE ALGORITHM=MERGEVIEW info_view(

    ->id,NAME,sex,address) ASSELECT id,NAME,sex,address FROM work_info WHERE age>20

   -> WITH LOCALCHECK OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE VIEW info_view\G

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

              View: info_view

        Create View: CREATEALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINERVIEW `info_view` AS select `work_info`.`id` AS`id`,`work_info`.`NAME` AS `NAME`,`work_info`.`sex` AS`sex`,`work_info`.`address` AS `address` from `work_info` where(`work_info`.`age` > 20) WITH LOCAL CHECKOPTION

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

mysql> SELECT * FROM info_view;

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

| id    |NAME  | sex  | address |

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

| 2    | 李四     | M   | 昌平     |

|  4    |赵六     | F     | 顺义   |

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

2 rows in set (0.00 sec)

修改视图，使其显示age<20的信息，其他条件不变。

ALTER ALGORITHM=MERGE VIEW

info_view(id,NAME,sex,address)

ASSELECT id,NAME,sex,address FROM work_infoWHERE age<20

WITH LOCAL CHECK OPTION;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM info_view;

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

| id | NAME   | sex | address |

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

|  1 | 张三     | M   | 海淀     |

|  3 | 王五     | F    | 平谷    |

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

2 rows in set (0.00 sec)

mysql> UPDATE info_view SET sex='M' WHERE id=3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql> SELECT * FROM info_view;

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

| id | NAME   | sex | address |

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

|  1 | 张三    | M    | 海淀   |

|  3 | 王五    | M   | 平谷   |

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

2 rows in set (0.01 sec)

mysql> SELECT * FROM work_info;

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

| id | NAME   | sex | age  |address | tel     |

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

|  1 | 张三   | M  |   18 | 海淀   | 1234567 |

|  2 | 李四   | M  |   22 | 昌平   | 2345678 |

|  3 | 王五  | M  |   17| 平谷    | 3456789 |

|  4 | 赵六   | F  |   25 | 顺义   | 4567890 |

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

4 rows in set (0.00 sec)

DROP VIEW IFEXISTS info_view;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT * FROM info_view;

ERROR 1146 (42S02): Table'tianfan.info_view' doesn't exist

实践

创建college表

在student表上创建视图college_view。视图的字段包括student_num、student_name、student_age和department。ALGORITHM设置为UNDEFINED类型。加入WITHLOCAL CHECK OPTION条件

查看视图college_view的详细结构

更新视图。向视图中插入三条记录。

修改视图，使其显示专业为‘计算机’的信息，其他条件不变

删除视图college_view

college表内容

字段名字段描述数据类型主键外键非空 唯一自增number学好INT(10)是否是是否name姓名VARCHAR(10)否否是否否mejor专业VARCHAR(10)否否是是否age年龄INT(5)否否否否否

collegeview表信息

numbernamemajorage0901张三外语200902李四计算机220903王五计算机19

CREATETABLE college(number INT(10) NOTNULL UNIQUE PRIMARY KEY,

NAME VARCHAR(20) NOT NULL,

major VARCHAR(20)  NOTNULL ,

age INT(5)

);

CREATE ALGORITHM=UNDEFINEDVIEW 

college_view(student_num,student_name,student_age,department)

ASSELECT number,NAME,age,major FROM college

WITH LOCAL CHECK OPTION;

mysql> SHOWCREATE VIEW college_view \G

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

                 View:college_view

       Create View: CREATE ALGORITHM=UNDEFINEDDEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `college_view`AS select `college`.`number` AS `student_num`,`college`.`NAME` AS`student_name`,`college`.`age` AS `student_age`,`college`.`major`AS `department` from `college` WITH LOCAL CHECK OPTION

character_set_client:utf8

collation_connection:utf8_general_ci

1 row in set (0.00sec)

INSERTINTO college_viewVALUES(0901,'张三',20,'外语');

INSERTINTO college_viewVALUES(0902,'李四',22,'计算机');

INSERTINTO college_viewVALUES(0903,'王五',19,'计算机');

CREATE OR REPLACE ALGORITHM=UNDEFINEDVIEW 

college_view(student_num,student_name,student_age,department)

ASSELECT number,NAME,age,major FROM college WHERE major='计算机'

WITH LOCAL CHECK OPTION;

DROP VIEW IFEXISTS college_view;