MySQL虚拟表以及索引（笔记整理）

临时表注意事项;

二、内存表

定义

是指使用Memory引擎的表;这种表的结构在磁盘里;数据都保存在内存里;系统重启时候数据会被清空;但表结构还在。内存表也可以被看作是临时表的一种。

语法;需要将存储引擎设置为;ENGINE =MEMORY

CREATE TABLE IF NOT  EXISTS tmp_table(...

....)

ENGINE = MEMORY;

内存表注意事项;

• 当MySQL服务重启之后;内存表的数据会丢失;表结构依旧存。
• data目录下只有tmp_memory.frm;表结构放在磁盘上;数据放在内存中。
• 可以创建索引;删除索引;支持唯一索引。
• 不影响主备;主库上插入的数据;备库也可以查到。
• SHOW TABLES语句可以查看的到表。
• 内存表使用哈希散列索引把数据保存在内存中;因此具有极快的速度;适合缓存中小型数据。
• 内存表不能包含BLOB或者TEXT列

临时表与内存表的区别;

三、视图 

 定义

视图是SELECT 语句组成的查询定义的虚拟表;它的定义存储在数据库中。但与表不同的是; 视图实际上不包含任何数据;不分配空间;

视图可以是 建立在一个或多个表上;也可以建立在视图上;但是对视图数据的操作最终都会转换为对基本表的操作。

语法

➢ 创建和修改视图只需在SELECT语句之前添加CREATE OR REPLACE VIEW 视图名 AS…即可

#创建视图;统计每门课程的平均分;行为学号;列为每个课程id
CREATE OR REPLACE VIEW v_score AS
SELECT id,
AVG(CASE cid WHEN ;C001; THEN score ELSE 0 END) ;C001;,
AVG(CASE cid WHEN ;C002; THEN score ELSE 0 END) ;C002;,
AVG(CASE cid WHEN ;C003; THEN score ELSE 0 END) ;C003;,
…
FROM scores GROUP BY id;

#查看存在的视图
SHOW TABLES LIKE ;v_%;
#查看视图
SELECT * FROM v_score where id<3;
#查看创建视图的命令
SHOW CREATE VIEW v_score;
#查看视图数据内容
DESC v_score;
SHOW FIELDS FROM v_score;

# 示例
WITH a AS(SELECT id, age, address, IF(sex=;男;,0,1) FROM student)
SELECT * FROM a
WHERE age>40;

CREATE INDEX index1 ON student(id);   #添加普通索引
CREATE INDEX index2 ON student(id,sname);   #添加联合索引
CREATE UNIQUE index3 ON student(id);   #添加唯一索引
ALTER TABLE student ADD PRIMARY KEY(id);  #添加主键索引
ALTER TABLE student ADD UNIQUE index4(id);   #添加主键索引

#建表时指定索引
CREATE TABLE IF NOT EXISTS test(id INT ,sname VARCHAR(10),
PRIMARY KEY(id),UNIQUE indexid(id));