MySQL的视图

MySQL的视图

创建基础表

CREATE TABLE `stu` (
 `id` int(10) NOT NULL,
 `name` varchar(10) NOT NULL DEFAULT '',
 `age` int(10) unsigned zerofill DEFAULT NULL,
 `sex` char(5) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `score` (
 `id` int(10) NOT NULL,
 `sid` int(10) NOT NULL,
 `subject` varchar(10) NOT NULL DEFAULT '',
 `score` int(10) unsigned zerofill NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

视图的测试

创建单表试图
create view stu_info as select * from stu;
单表视图的增删改查(均可以正常执行)
INSERT into stu_info(id, name, age, sex) VALUES(6, 'haha', 20, 'man');
DELETE from stu_info WHERE id=6;
UPDATE stu_info set name='Mike1' where id=3;
SELECT name from stu_info;
如果当前视图只是在一个基本表上创建的，那么对于视图的增删改查会影响基本表的数据。

创建关联视图
CREATE VIEW stu_score as SELECT stu.name, score.subject, score.score from stu join score on score.sid=stu.id;
关联视图的查询：正常执行
SELECT * from stu_score where name='Tom';
关联视图的插入：拒绝执行：1393 - Can not modify more than one base table through a join view 'test.stu_score',
INSERT INTO stu_score(name, subject, score) VALUES('Wawa', 'test', 100);
关联视图的删除：拒绝执行：1395 - Can not delete from join view 'test.stu_score', Time: 0.001000s
DELETE from stu_score where name='Tom';
关联视图的更新：正常执行
UPDATE stu_score set score=100 where name='Tom';
如果视图是多个表联合创建的，那么对于视图的查询无影响；如果对视图进行添加，则会报错1393；如果对视图进行删除，则会报错1395；如果进行更新操作，则会正常执行。

视图的分类

• 单源表视图：视图的数据可以只取自一个基本表的部分行、列，这样的视图行列与基本表行列对应。这样定义的视图一般可以进行查询和更改数据操作。
• 多源表视图：视图的数据可以来自多个表中，这样定义的视图一般只用于查询，不用于修改数据。
• 多重视图：在已有视图上定义的新视图，可以在视图上再建立视图，这时作为数据源的视图必须是已经建立好的。
• 带表达式的视图：在定义基本表时，为减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据需要设置一些派生属性列，在这些派生属性列中保存经过计算的值。这些派生属性由于在基本表中并不实际存在，因此，也称它们为虚拟列。包含虚拟列的视图也称为带表达式的视图。
• 含分组统计信息的视图：含分组统计信息的视图是指定义视图的查询语句中含有GROUP BY子句，这样的视图只能用于查询，不能用于修改数据。

作用

• 提高了重用性，就像一个函数。
• 对数据库重构，却不影响程序的运行。
• 提高了安全性能。可以对不同的用户，设定不同的视图。
• 让数据更加清晰。想要什么样的数据，就创建什么样的视图。

优点

• 简单化，数据所见即所得
• 安全性，用户只能查询或修改他们所能见到得到的数据
• 逻辑独立性，可以屏蔽真实表结构变化带来的影响

缺点

• 性能相对较差，简单的查询也会变得稍显复杂
• 修改不方便，特变是复杂的聚合视图基本无法修改

视图与中间表的比较(暂时没有构造数据进行测试，有待考究)

源自：https://blog.csdn.net/meimei726322819/article/details/68923460/

数据库设计中，经常遇到一个决策：究竟是使用视图，还是中间表？

考虑库存管理的一个场景：最普通的单据是入库和出库单，库管员需要看到当前的库存。对库存的处理，我们有两个方案：一是使用视图，所有的入库减去所有的出库，就是当前库存；另外就是使用中间表，建立一个库存表，记录当前的库存。

• 1、使用视图的方案

  • 入库时，系统记录入库单据；
  • 出库时，系统查询库存视图，判断是否有充足的库存可以出库，然后记录出库单据；
  • 可见，系统只需要记录入库和出库单据，库存的计算是由DBMS在查询视图时进行的；

• 2、使用库存表的方案

  • 入库时，系统记录入库单据，同时增加相应的库存；
  • 出库时，系统查询库存表，判断是否有充足的库存可以出库，然后记录出库单据，减少相应的库存；
  • 可见，系统除了记录入库和出库单据外，还需要更新库存表的当前库存数量；

• 3、方案的比较

  • 对系统本身的设计和编码来说，视图方案易于实现，测试方便；
  • 库存表方案则稍微复杂。从这点上看，视图方案可以在原型阶段大展身手。
  • 用户体验到的性能方面，视图方案的性能压力在查询库存上，库存表方案的性能压力在业务处理上：
  • 视图方案：由于每次查询库存，DBMS都需要扫描入库和出库单据，查询时间长；还可能会对入库和出库单据加锁，导致入库和出库处理延长，甚至失败（尤其是查询库存视图在一个事务中时）；库存视图如果和其它表或者视图连接，构成复杂的SQL时，由于索引不能有效（或无法）使用，查询速度会更慢；
  • 库存表方案：库存表上可以建索引，查询速度比视图会快很多；在入库和出库时，更新库存表的SQL会对出入库处理的速度有一些影响，但是由于更新只影响出入库的SKU，与查询库存表并发时，加锁时间非常短，影响会比较小。

• 4、结论

  • 视图方案适用情形：原型，数据量比较小；
  • 库存表方案适用情形：数据量比较大，针对库存的分析较多；