mysql视图

mysql视图机制

什么是视图

视图是一张虚拟的表，为什么是虚拟呢?因为视图与数据库中存在的表不太一样，前面我们创建的4张表都是包含数据的，

如用户信息，订单信息等，而视图则是不包含数据的，下面通过一个例子来演示视图，下面的sql是查询王五的所有

订单情况，需要关联到orders表，orderd etail表 tiems表 user表

mysql> select u.username, o.number, tm.name as itemsName , tm.price, od.items_num from

(

( orders as o inner join orderdetail as od on o.id = od.orders_id)

inner join items as tm on od.items_id = tm.id

)

inner join user as u on o.user_id = u.id

where username='王五';

显然数据已如期查询出来了，但是我们发现任何需要这个数据的人都必须了解相关的表结构，并且需要知道如何创建

和对表进行联结，为了检索其他用户的相同数据必须修改where条件并带上一大段关联查询的sql语句。是的，每次这样的操作确实挺

麻烦的，加入现在可以把这个除了where条件外的sql查询出来的数据包装成一个名为user_order_data 的虚拟表，就可以使用

使用以下方式检索出数据了。

select * from user_order_data where username='王五';

按这样的方式每次查询不同的用户只需修改where条件即可也不同在写那段看起来有点恶心的长sql了，而事实上

user_order_data 就是一张视图表，也可称为虚拟表，而这就是视图最显著的作用了。

视图创建与使用

了解完什么是视图后，我们先来看看如何创建视图和使用视图，使用以下语法：

create view 视图名(列明...) as select 语句

现在我们使用前面关联查询的orders表 orderdetail表 items表 user表来创建视图user_order_data

-- 创建视图虚拟表 user_order_data

mysql> create view user_order_data(username,numer,itemname,price,items_num) as select u.username, o.number,tm

( 

( orders as o inner join orderdatail as od on o.id = od.orders_id)

inner join items as tm on od.items_id = tm.id

)

inner join user as u on o.user_id = u.id;

-- 使用视图

mysql> select * from user_order_data;

可以看出除了在select语句前面加上 create view user_order_data as 外，其他几乎没变化。在使用视图user_order_data

时，跟使用数据库表没啥区别，因此以后需要查询指定用户或者所有用户的订单情况时，就不用编写长巴巴的一段sql了

，还是蛮简洁的。除了上述的方式，还可以将视图虚拟表的字段别名移动到查询字段后面：

create or replace view user_order_data 

as

 select 

u.username as username,

o.number as number,

tm.name as name,

tm.prive as prive,

od.items_num as items_num

from

(

( orders as o inner join orderdetail as od on o.id = od.orders_id)

inner join items as tm on od.items_id = tm.id

)

innner join user as u on o.user_id = u.id

注意这里使用了 create or replace view 语句，意思就是不存在就创建，存在就替换。如果想删除视图可以使用以下语法：

drop view 视图名称

在使用视图的过程还有些需要注意的点，如下

与创建表一样，创建视图的名称必须唯一

创建视图的个数并没有限制，但是如果一张视图嵌套或者关联的表过多，同样会引发性能问题，在实际生产环节中

部署时务必进行必要的性能检测，。

在过滤条件数据时如果在创建视图的sql语句中存在where的条件语句，而在使用该视图的语句中也存在where条件语句时，

这两个where条件语句会自动组合

order by 可以在视图中使用，但如果从该视图检索数据的select语句中国也含有order by ,那么该视图中的order by

将被覆盖。

视图中不能使用索引，也不能使用触发器

使用可以和普通的表一起使用，编辑一条连接视图和普通表的sql语句是允许的。

关于使用视图对数据的进行更新（增删改）,因为视图本身并没有数据，所以这些操作都是直接作用到普通表中的，但也并非

所有的视图都可以进行更新操作，如视图中存在分组(group by) 联结 子查询 并(union) 聚合函数(sum/count等)

计算字段，distinct 等都不能对视图进行更新操作，因此我们前面的例子也是不能进行更新操作的，事实上，视图更多的

是用于数据检索而更新，因此对于更新也没有必要进行多阐述。

视图的本质

至此对于视图的创建和使用都比较清晰了，现在准备进一步认识视图的本质，前面我们反复说过，视图是一张

虚拟表，是不带任何数据的，每次查询时，只是从普通表中动态的获取数据并组合，只不过外表看起来像

一张表罢了

事实上有些时候视图还会被用于限制用户对普通表的查询操作，对于这类用户只赋予对应视图的select操作权限，仅

让他们只能读取特定的行或列的数据。这样我们也就不用直接使用数据库的权限设置行列的读取，同时也避免了权限细化的麻烦。

高效索引

使用索引的理由

由于mysql在默认情况下，表中的数据记录是没有顺序可言的，也就是说在数据检索过程中，符合条件的数据存储

在哪里，我们是完全不知情的，如果使用selec语句进行查询，数据库会从第一条记录开始检索，即使找到第一条

符合条件的数据，数据库的检索也并不会因此而停止，毕竟符合条件的数据可能并不止一条，也就是说此时

检索会把表中的数据全部检索一遍才结束，这样的检索方式也称为全表扫描，但假设表中存在巨量数据呢，指明了某个关键字

在正文中的出现的页码位置或章节的位置，这样只要找到对应页面就能找到要检索的内容了，数据库的检索也是类似

这样的原理，通过创建某个字段或者多个字段的索引，在搜索该字段时就可以根据对应的索引进行快速检索出相应

内容而无需全表扫描了。

索引的创建及其基本类型

mysql 索引可以分为单列索引，符合索引，唯一索引，主键索引等，下面分别介绍

单列索引

单列索引，也称为普通索引，单列索引是最基本的索引，他没有任何限制，创建一个单列索引，语法如下：

create index index_name on tbl_name(index_col_name)

其中index_name为索引的名称，可以自定义，tbl_name 则指明要创建索引的表，而index_col_name指明表中

那一个列要创建索引。当然我们也可以通过修改表结构的方式添加索引：

alter table tbl_name add index index_name on (index_col_name);

还可在创建表时直接指定：

-- 创建表时直接指定

create table `table`(

`id` int(11) not null auto_increment,

`name` varchar(32) not null,

..... -- 其他字段

primary key (`id`),

indexName (name(32)) -- 创建name字段索引

);

下面为user表的username字段创建单列索引：

-- 创建username字段的索引名称为index_name ,这就是基础的索引创建

mysql> create index index_name on user(username);

-- 查看user表存在的索引 G 代表优化显示方式

mysql> show index from user G;

可见user表中的username字段的索引已被创建，在使用show index from user 查看user的索引字段时，我们发现id

字段也创建了索引，事实上，当user表被创建时，主键的定义的字段id就会自动创建索引，这是一种特殊的索引，

也称为丛生索引，而刚才创建的index_name 索引属于单列索引

复合索引

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守 最左前缀 原则，即在查询条件中使用了复合索引的

第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。创建一个复合索引的语法如下：

-- index_name 代表索引名称，而index_col_name 和index_col_name2为列名，可以多个

create index index_name on  tbl_name(index_col_name,index_col_name2,.....);

-- 同样道理，也可以通过修改表结构的方式添加索引，

alter tble tbl_name add index index_name on (index_col_name1,index_col_name2,.....);

-- 创建表时直接指定

create table `table`(

`id` int(11) not null auto_increment,

`name` varchar(32) not null,

`pinyin` varchar(32),

....... -- 其他字段

primary key (`id`),

indexName (name(32),pinyin(32))

);