首页 > 代码库 > MYSQL视图的学习笔记

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可以完全地对数据库进行操作。

MYSQL视图的学习笔记