MySQL 视图的基础操作(五)
1.为什么使用视图:
为了提高复杂sql语句的复用性和表操作的安全性(例如:工资字段不想展示给所有能查看该查询结果的人),mysql提供了视图特性。所谓视图,本质上是一种虚拟表,其内容与真实的表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用基本表,并且在具体使用视图时动态生成。
视图有如下特点;
1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表(实表)产生的表(虚表)。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新(添加、删除和修改)直接影响基本表。
5. 当视图来自多个基本表时,不允许添加和删除数据。
2.创建视图:
在创建视图时,首先要确保拥有create view的权限,并且同时确保对创建视图所引用的表也具有相应的权限。
2.1 创建视图的语法形式:
虽然视图可以被看成是一种虚拟表,但是其在物理上是不存在的,即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句,因此创建视图的语法为:
create view view_name as 查询语句
//说明:和创建表一样,视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。
示例:
use zhaojd_test; //选择一个自己创建的库 create table t_product( //创建表 id int primary key, pname varchar(20), price decimal(8,2) ); insert into t_product values(1,'apple',6.5); //向表中插入数据 insert into t_product values(2,'orange',3); //向表中插入数据 create view view_product as select id,name from t_product; //创建视图 select * from view_product;
结果为:
+------+--------+
| id | name |
+------+--------+
| 1 | apple |
| 2 | orange |
+------+--------+
//其实在创建视图时实际代码里写的是一个表查询语句,只不过把这个查询语句封装起来重新起了一个名字,方便可以重复使用。
//再者,安全性方面可以隐藏一些不希望看到的字段,比如这里的价格字段。
//注意:在sql语句的命名规范中,视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。
2.2 创建各种视图:
由于视图的功能实际上时封装查询语句,那么是不是任何形式的查询语句都可以封装在视图中呢?
2.2.1 封装实现查询常量语句的视图(常量视图):
示例:
mysql> create view view_test1 as select 3.1415926; query ok, 0 rows affected (0.07 sec) mysql> select * from view_test1; +-----------+ | 3.1415926 | +-----------+ | 3.1415926 | +-----------+ 1 row in set (0.00 sec)
2.2.2 封装使用聚合函数(sum、min、max、count等)查询语句的视图:
示例:
首先准备需要用到的两张表及其初始化数据;
create table t_group( id int primary key auto_increment, name varchar(20) ); create table t_student( id int primary key auto_increment, name varchar(20), sex char(1), group_id int, foreign key (group_id) references t_group (id) ); //t_group表中插入数据 insert into t_group (name) values('group_1'); insert into t_group (name) values('group_2'); insert into t_group (name) values('group_3'); insert into t_group (name) values('group_4'); insert into t_group (name) values('group_5'); //t_student表中插入数据 insert into t_student (name,sex,group_id) values('zjd_1','m',1); insert into t_student (name,sex,group_id) values('zjd_2','m',1); insert into t_student (name,sex,group_id) values('zjd_3','m',2); insert into t_student (name,sex,group_id) values('zjd_4','w',2); insert into t_student (name,sex,group_id) values('zjd_5','w',2); insert into t_student (name,sex,group_id) values('zjd_6','w',2); insert into t_student (name,sex,group_id) values('zjd_7','m',3); insert into t_student (name,sex,group_id) values('zjd_8','w',4); insert into t_student (name,sex,group_id) values('zjd_9','w',4); ================================================================ mysql> create view view_test2 as select count(name) from t_student; query ok, 0 rows affected (0.71 sec) mysql> select * from view_test2; +-------------+ | count(name) | +-------------+ | 9 | +-------------+ 1 row in set (0.01 sec)
2.2.3 封装了实现排序功能(order by)查询语句的视图:
示例:
mysql> create view view_test3 as select name from t_student order by id desc; query ok, 0 rows affected (0.06 sec) mysql> select * from view_test3; +-------+ | name | +-------+ | zjd_9 | | zjd_8 | | zjd_7 | | zjd_6 | | zjd_5 | | zjd_4 | | zjd_3 | | zjd_2 | | zjd_1 | +-------+ 9 rows in set (0.00 sec)
2.2.4 封装了实现表内连接查询语句的视图:
示例:(第二组学生的姓名)
mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2; query ok, 0 rows affected (0.07 sec) mysql> select * from view_test5; +-------+ | name | +-------+ | zjd_3 | | zjd_4 | | zjd_5 | | zjd_6 | +-------+ 4 rows in set (0.00 sec)
2.2.5 封装了实现表外连接(left join和right join)查询语句的视图:
示例:(第二组学生姓名)
mysql> create view view_test6 as select s.name from t_student s left join t_group g on s.group_id=g.id where g.id=2; query ok, 0 rows affected (0.09 sec) mysql> select * from view_test6; +-------+ | name | +-------+ | zjd_3 | | zjd_4 | | zjd_5 | | zjd_6 | +-------+ 4 rows in set (0.01 sec)
2.2.6 封装了实现子查询相关查询语句的视图:
示例:
mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group); query ok, 0 rows affected (0.08 sec) mysql> select * from view_test7; +-------+ | name | +-------+ | zjd_1 | | zjd_2 | | zjd_3 | | zjd_4 | | zjd_5 | +-------+ 5 rows in set (0.00 sec)
2.2.7 封装了实现记录联合(union和union all)查询语句的视图:
mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group; query ok, 0 rows affected (0.08 sec) mysql> select * from view_test8; +----+---------+ | id | name | +----+---------+ | 1 | zjd_1 | | 2 | zjd_2 | | 3 | zjd_3 | | 4 | zjd_4 | | 5 | zjd_5 | | 6 | zjd_6 | | 7 | zjd_7 | | 8 | zjd_8 | | 9 | zjd_9 | | 1 | group_1 | | 2 | group_2 | | 3 | group_3 | | 4 | group_4 | | 5 | group_5 | +----+---------+ 14 rows in set (0.01 sec)
3.查看视图:
3.1 show tables语句查看视图名:
执行show tables 语句时不仅可以显示表的名字,同时也是显示出视图的名字。
示例:
mysql> show tables; +------------------+ | tables_in_zhaojd | +------------------+ | t_group | | t_product | | t_student | | v_product | | view_test1 | | view_test2 | | view_test3 | | view_test4 | | view_test5 | | view_test6 | | view_test8 | +------------------+ 11 rows in set (0.00 sec)
3.2 show table status语句查看视图详细信息:
和show tables语句一样,show table status语句不仅会显示表的详细信息,同时也会显示视图的详细信息。
语法如下:
show table status [from db_name] [like 'pattern']
//参数db_name用来设置数据库,show tables status表示将显示所设置库的表和视图的详细信息。
//设置like关键字,可以查看某一个具体表或视图的详细信息。例如: show table status from zhaojd like 't_group' \g
示例:
mysql> show table status from zhaojd \g *************************** 1. row *************************** name: t_group engine: innodb version: 10 row_format: compact rows: 5 avg_row_length: 3276 data_length: 16384 max_data_length: 0 index_length: 0 data_free: 7340032 auto_increment: 6 create_time: 2016-08-19 16:26:06 update_time: null check_time: null collation: utf8_general_ci checksum: null create_options: comment: ============================================================= name: view_test8 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
3.3 show create view语句查看视图定义信息:
语法为:
show create view viewname;
示例:
mysql> show create view view_test8 \g *************************** 1. row *************************** view: view_test8 create view: create algorithm=undefined definer=`root`@`localhost` sql security definer view `view_test8` as select `t_student`.`id` as `id`, `t_student`.`name` as `name` from `t_student` union all select `t_group`.`id` as `id`,`t_group`.`name` as `name` from `t_group` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) //根据执行结果可以发现,show create view语句返回两个字段,分别为表示视图名的view字段和关于视图定义的create view字段。
3.4 describe | desc 语句查看视图定义信息:
语法为:
describe | desc viewname;
示例:
mysql> desc view_test8; +-------+-------------+------+-----+---------+-------+ | field | type | null | key | default | extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | no | | 0 | | | name | varchar(20) | yes | | null | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec)
3.5 通过系统表查看视图信息:
当mysql安装成功后,会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格,可以通过查看表格views来查看所有视图的相关信息。
示例:
mysql> use information_schema; database changed mysql> select * from views where table_name='view_test8' \g *************************** 1. row *************************** table_catalog: def table_schema: zhaojd table_name: view_test8 view_definition: select `zhaojd`.`t_student`.`id`as`id`,`zhaojd`.`t_student`.`name` as `name` from `zhaojd`.`t_student` union all select `zhaojd`.`t_group`.`id` as`id`,`zhaojd`.`t_group`.`name` as `name` from `zhaojd`.`t_group` check_option: none is_updatable: no definer: root@localhost security_type: definer character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.01 sec)
4.删除视图:
在删除视图时首先要确保拥有删除视图的权限。
语法为:
drop view view_name [,view_name] ......
//从语法可以看出,drop view一次可以删除多个视图
示例:
mysql> use zhaojd; database changed mysql> show tables; +------------------+ | tables_in_zhaojd | +------------------+ | t_group | | t_product | | t_student | | v_product | | view_test1 | | view_test2 | | view_test3 | | view_test4 | | view_test5 | | view_test6 | | view_test8 | +------------------+ 11 rows in set (0.00 sec) mysql> drop view view_test1, view_test2; query ok, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | tables_in_zhaojd | +------------------+ | t_group | | t_product | | t_student | | v_product | | view_test3 | | view_test4 | | view_test5 | | view_test6 | | view_test8 | +------------------+ 9 rows in set (0.00 sec)
5.修改视图:
5.1 create or replace view语句修改视图:
对于已经创建好的表,尤其是已经有大量数据的表,通过先删除,然后再按照新的表定义重新建表的方式来修改表,需要做很多额外的工作,例如数据的重载等。可是对于视图来说,由于是“虚表”,并没有存储数据,所以完全可以通过该方式来修改视图。
实现思路就是:先删除同名的视图,然后再根据新的需求创建新的视图即可。
drop view view_name; create view view_name as 查询语句;
但是如果每次修改视图,都是先删除视图,然后再次创建一个同名的视图,则显得非常麻烦。于是mysql提供了更方便的实现替换的创建视图的语法,完整语法为:
create or replace view view_name as 查询语句;
5.2 alter语句修改视图:
语法为:
alter view view_name as 查询语句;
6.利用视图操作基本表:
再mysql中可以通常视图检索基本表数据,这是视图最基本的应用,除此之后还可以通过视图修改基本表中的数据。
6.1检索(查询)数据:
通过视图查询数据,与通过表进行查询完全相同,只不过通过视图查询表更安全,更简单实用。只需要把表名换成视图名即可。
6.2利用视图操作基本表数据:
由于视图是“虚表”,所以对视图数据进行的更新操作,实际上是对其基本表数据进行的更新操作。在具体更新视图数据时,需要注意以下两点;
1. 对视图数据进行添加、删除直接影响基本表。
2. 视图来自于多个基本表时,不允许添加、删除数据。
视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。