mysql虚拟表

虚拟表，就是实际上并不存在（物理上不存在），但是逻辑上存在的表。

在MySQL中，存在的虚拟表：临时表、内存表和视图，派生表。

只能从select语句可以返回虚拟表的是视图和派生表。

一、派生表

当select语句的from子句中使用独立子查询时，就称其为派生表。

select column_list
from (
      select column_list
      from table_1
      ) derived_table_name
where derived_table_name.c1 > 0 ;

与子查询不同，派生表必须具有别名，以便稍后在查询中引用其名字。

如果派生表没有别名，则出错。

二、视图

视图是为了方便多个表联表查询而设计的，所以视图也是多个表中的字段由各个表中的关联关系而创建的一种虚拟表。

视图创建后就保存了下来，以后可以随时用，除非drop删除视图。

在数据库中，只存放了视图的定义，并没有存放视图的数据，数据还是存储在原来的表中，

视图的数据是依赖原来表中的数据的，所以原来表的数据发生了改变，那么显示的视图的数据也会随着改变。

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

1.创建视图
-- other
create view other as 
select a.name as username, b.name as goodsname from 
user as a, goods as b where a.id=b.id;

2.删除视图
drop view if exists other;

3.调用视图
select * from other;

4.如果视图不存在，则创建视图；如果视图存在，则修改视图
create or replace view view_name as select 语句;

视图创建一遍后会在navicat premium（MySQL可视化工具）保存下来，不可重复创建视图，

所以想重复调试创建视图，需先删除已创建的视图，在执行创建视图的SQL命令或者是修改视图。

视图作用：

• 提高了重用性，就项一个函数，创建一次，可重复调用。
• 对数据库重构，却不影响原数据。
• 让数据更加清晰。想要什么样的数据，就创建什么样的视图。

视图定义的存放位置：information_schema.views

查看视图的基本信息：DESC view_name;

注意：

• 修改视图的数据，将直接修改数据表（即原表）的真实数据。
• 删除视图，不会影响原表的数据，但是删除视图的数据，则会影响到原表。

即可以从select语句可以返回虚拟表，又可以通过构建表结构创建虚拟表的是临时表和内存表。

三、临时表

MySQL临时表在保存一些临时数据时是非常有用的。

临时表是建立在系统临时文件夹中的表，使用得当，完全可以像普通表一样进行各种操作。

临时表的数据和表结构都存储在内存中。

临时表只在当前MySQL连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

1.通过构建临时表结构创建临时表
create temporary table tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
);

select * from tmp_table;

2.直接将查询结果导入临时表
create temporary table tmp_table select * from other;

3.删除临时表
drop table tmp_table;

临时表的应用：

• 当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行拆分并且得到比较小的结果集合存放在临时表中。
• 程序执行过程中可能需要存放一些临时数据，这些数据在整个程序的会话过程中需要重复使用。
• 临时表默认是MyISAM（存储引擎），但是可以修改。

注意：

• 临时表与存在的表名相同的时候，存在的表会被隐藏，当临时表被drop，存在的表就可见了。
• show tables语句不会列举临时表，但是会列出内存表。
• 临时表在数据库多个连接之间不能共享。
• 临时表的最大所需内存需要通过tmp_table_size=128MB设定。当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误。
• 临时表更多作用是系统自己创建或，组织数据以提升性能，如子查询。

四、内存表

表结构建在磁盘上，数据在内存里，当停止服务后，表中的数据会丢失，而表的结构不会丢失。

内存表也可以被看作是临时表的一种。

1.通过构建内存表结构创建内存表
create table tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)ENGINE=MEMORY;

select * from tmp_table;
show tables;
 
2.直接将查询结果导入内存表

create table tmp_table engine=memory 
　　select * from other;

3.释放占用的内存
-- 删除数据
delete from tmp_table;
-- 清空表
truncate table tmp_table;
-- 删除表
drop table tmp_table;

内存表特征：

• 对于varchar等变长类型，内存表使用固定的长度来存放。
• 内存表可以有非唯一的键。
• 内存表不能包含BLOB或TEXT列。
• 内存表支持AUTO_INCREMENT列。
• 内存表支持插入延迟，使读取优先。
• 当临时表变得很大时，MySQL会自动地把它转化为在磁盘上存储的表，而内存表不会自动转换。
• 在MySQL的主从服务器上，内存表可以被复制。
• 内存表最大的size受限于系统变量max_heap_table_size，默认值是16MB，这个变量是可以修改的。
• 内存表对所有用户的连接都是可见的，使得它非常适合做缓存。
• 内存表必须使用memory存储引擎。

五、临时内存表

1.通过构建表结构创建临时内存表
create temporary table tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)ENGINE=MEMORY;

select * from tmp_table;
show tables;

2.直接将查询结果导入内存表
create temporary table tmp_table engine=memory 
　　select * from other;

参考：

https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html

https://blog.csdn.net/qq_43678418/article/details/87702348