MYSQL视图的学习笔记

MYSQL视图的学习笔记，学至Tarena金牌讲师，金色晨曦科技公司技术总监沙利穆 课程笔记的综合。

视图及图形化工具

 

1.       视图的定义

视图就是从一个或多个表中，导出来的表，是一个虚拟存在的表。视图就像一个窗口（数据展示的窗口），通过这个窗口，可以看到系统专门提供的数据（也可以查看到数据表的全部数据），使用视图就可以不用看到数据表中的所有数据，而是只想得到所需的数据。

在数据库中，只存放了视图的定义，并没有存放视图的数据，数据还是存储在原来的表里，视图的数据是依赖原来表中的数据的，所以原来的表的数据发生了改变，那么显示的视图的数据也会跟着改变,例如向数据表中插入数据，那么在查看视图的时候，会发现视图中也被插入了同样的数据。

视图在外观上和表很相似，但是它不需要实际上的物理存储，视图实际上是由预定义的查询形式的表所组成的。

视图可以包含表的全部或者部分记录，也可以由一个表或者多个表来创建，当我们创建一个视图的时候，实际上是在数据库里执行了SELECT语句，SELECT语句包含了字段名称、函数、运算符，来给用户显示数据。

在数据库中，视图的使用方式与表的使用方式一致，我们可以像操作表一样去操作视图，或者去获取数据。

一般来说，我们只是利用视图来查询数据，不会通过视图来操作数据。

1.1    基于视图的视图

基于已存在的视图，还可以再创建视图。

1.2    视图和表的区别

视图和表的主要区别，就是看是否占用物理空间。

 

1.3 视图的作用

（1）选取有用的信息，筛选的作用

（2）操作简单化，所见即所需，视图看到的信息，就是需要了解的信息

（3）增加数据的安全性：查询或者修改指定的数据，非指定的数据是触碰不到的。

（4）提高逻辑的独立性

1.4 视图的特点

（1）简单性（简单化）：可以展现特定的数据，而无需重复设置查询条件，简化操作。

（2）安全性：视图可以只展现数据表的一部分数据，对于我们不希望让用户看到全部数据，只希望用户看到部分数据的时候，可以选择使用视图。

（3）逻辑独立性：当真实的数据表结构发生了变化，可以通过视图来屏蔽真实表的结构变化，从而实现了视图的逻辑独立性。

 

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立：

 

①如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而应用程序可以不动。

 

②如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。

 

③如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而应用程序可以不动。

 

④如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而数据库可以不动。

 

2.       创建视图

CREATE VIEW 视图名称[(column_list)] AS SELECT 语句

例：

CREATE VIEW  province_view AS SELECT * FROM province;

SELECT * FROM province_view;

说明：创建的视图表province_view与province表一模一样。

 

2.1    指定视图显示的字段：

CREATE VIEW province_view1(id,name) AS SELECT id,pro_name FROM province;

mysql> SELECT * FROM province_view1;

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

| id  | name |

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

|   1 | 北京 |

|   2 | 上海 |

|   3 | 辽宁 |

|   4 | 天津 |

|   5 | 广东 |

|   6 | 福建 |

| 100 | 吉林 |

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

7 rows in set (0.00 sec)

 

2.2 创建基于两个表的视图：

使用WHERE连接两个表：

CREATE VIEW v3(name,score) AS SELECT s_name,score FROM student,score

WHERE student.s_id=score.s_id

and score.c_id='BY';

 

2.3 视图的算法

ALGORITHM=

UNDEFINED:MYSQL自动选择要使用的算法

MERGE：使用视图的语句与视图的定义是合并在一起的，视图定义的某一部分取代语句对应的部分

TEMPTABLE：临时表，视图的结果存入临时表，然后使用临时表来执行语句

 

WHIT [CASCADED|LOCAL] CHECK OPTION：表示更新视图的时候，要保证在视图的权限范围之内：

CASCADED 默认值，表示更新视图的时候，要满足视图和表的相关条件

LOCAL：表示更新视图的时候，要满足该视图定义的一个条件即可

 

说明：使用WHIT [CASCADED|LOCAL] CHECK OPTION选项可以保证数据的安全性

 

3.创建完整的视图

CREATE ALGORITHM VIEW 视图名称[(column_list)] AS SELECT 语句

WITH  [CASCADED|LOCAL] CHECK OPTION

 

语法提示命令：? CREATE VIEW

 

Name: 'CREATE VIEW'

Description:

Syntax:

CREATE

    [OR REPLACE]

    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    [DEFINER = { user | CURRENT_USER }]

    [SQL SECURITY { DEFINER | INVOKER }]

    VIEW view_name [(column_list)]

    AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

 

例子：

CREATE ALGORITHM=UNDEFINED VIEW user_view3(id,username,age) AS SELECT

id,username,age FROM users2 WITH CASCADED CHECK OPTION;

 

4. 查看视图

查看已创建好的视图：

4.1 查看已创建好的视图的方法：

DESC

DESCRIBE

SHOW COLUMNS FROM 视图名称

SHOW TABLE STATUS LIKE

SHOW CREATE VIEW

4.1.1 DESC

mysql> desc user_view3;

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

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

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

| id       | smallint(5) unsigned | NO   |     | 0       |       |

| username | varchar(20)          | NO   |     | NULL    |       |

| age      | tinyint(3) unsigned  | YES  |     | NULL    |       |

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

3 rows in set (0.02 sec)

 

4.1.2 DESCRIBE

mysql> DESCRIBE user_view3;

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

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

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

| id       | smallint(5) unsigned | NO   |     | 0       |       |

| username | varchar(20)          | NO   |     | NULL    |       |

| age      | tinyint(3) unsigned  | YES  |     | NULL    |       |

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

3 rows in set (0.01 sec)

 

4.1.3 SHOW COLUMNS FROM 视图名称

mysql> SHOW COLUMNS FROM user_view3;

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

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

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

| id       | smallint(5) unsigned | NO   |     | 0       |       |

| username | varchar(20)          | NO   |     | NULL    |       |

| age      | tinyint(3) unsigned  | YES  |     | NULL    |       |

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

3 rows in set (0.02 sec)

 

4.2 查看视图的基本信息（也可查看原表的信息）：

SHOW TABLE STATUS LIKE ‘视图名称’;

 

mysql> SHOW TABLE STATUS LIKE 'province_view'\G;

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

           Name: province_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)

 

说明：

（1）       可以从Comment: VIEW看出它是一个视图，如果是数据表，Comment选项的值为空。

（2）       因为视图是虚拟出的一张表，所以很多选项的值都是NULL，如果SHOW TABLE STATUS LIKE ‘table_name’; 那么这些选项将会显示出数值。

 

4.3 查看指定视图的创建信息（专门查看视图信息的命令）

 

SHOW CREATE VIEW 视图名称;

 

mysql> SHOW CREATE VIEW user_view3\G;

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

                View: user_view3

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_view3` AS select `users2`.`id` AS `id`,`users2`.`username` AS `username`,`users2`.`age` AS `age` from `users2` WITH CASCADED CHECK OPTION

character_set_client: gbk

collation_connection: gbk_chinese_ci

1 row in set (0.00 sec)

 

4.4 视图数据的存储位置

 

mysql> SELECT * FROM information_schema.views\G

 

所有的视图都保存在了information_schema.views中。

 

4.5.修改视图：

 

如果视图不存在，则创建视图，如果视图存在，则修改视图：

（1）CREATE OR REPLACE VIEW 视图名称[(column_list)] AS SELECT 语句

（2）ALTER VIEW视图名称[(column_list)] AS SELECT 语句

 

4.5.1 CREATE OR REPLACE VIEW 视图名称[(column_list)] AS SELECT 语句

（1）例子：

CREATE OR REPLACE VIEW user_view3(id,username) AS SELECT id,username FROM users2;

 

（2）如果输入的视图名称不存在，这MYSQL自动创建该视图：

 

（3）修改视图：

CREATE OR REPLACE ALOGRITHM=TEMPTABLE VIEW user_view4(id) AS SELECT id FROM

users2;

 

（4）修改基于两个表的视图，两个表使用WHERE进行连接：

CREATE OR REPLACE VIEW v3 AS SELECT s_name,s_sex,score FROM student,score

WHERE student.s_id=score.s_id AND score.c_id='BY';

 

4.5.2 ALTER

ALTER VIEW 视图名称[(column_list)] AS SELECT 语句

 

ALTER VIEW user_view4(id,username,age) AS SELECT id,username,age FROM users2;

 

修改基于两个表的视图：

ALTER VIEW v3 AS SELECT s_name,score FROM student,score

WHERE student.s_id=score.s_id

AND score.c_id='TC';

 

5.更新视图

所谓更新视图，其实就是通过视图，对数据进行插入，修改和删除的操作。

5.1 修改视图的数据

注意：修改视图的数据，将直接修改数据表（即原表）的真实数据。

UPDATE v3 SET score=100 WHERE s_name='倪妮';

5.2 通过视图插入、删除数据的原理与5.1一致，均与数据表的操作语法一致

 

6.删除视图：

删除视图，不会影响原表的数据，但是删除视图的数据，则会影响到原表。

 

6.1 DROP VIEW 视图名称;

 

DROP VIEW 视图名称;

DROP VIEW user_view4;

 

6.2 DROP VIEW IF EXISTS

在删除已不存在的视图的时候，不进行任何操作：

DROP VIEW  IF EXISTS视图名称;

例：

DROP VIEW IF EXISTS v1;

 

6.3 删除多个视图

 

DROP VIEW IF EXISTS v2,v3;

图形化工具的使用

1.图形化工具：

MYSQL Workbench

PHPMyAdmin

Navicat for MySQL：是一个客户端的软件，需要安装客户端的软件才能使用。

MySQL-Front

 

2.PHPMyAdmin

PHPMYADMIN是以Web-Base的方式，架构在网站主机上的MYSQL数据库管理工具，它是使用了WEB接口管理MYSQL数据库。

通过PHPMyAdmin可以完全地对数据库进行操作。