MySQL数据视图笔记

一、视图是什么· 视图是从一个或多个基本表或视图里导出的虚拟表。·只保存 SQL 定义不存储实际数据。数据仍存放在原来的基本表中。· 作用简化复杂的查询、提高安全性隐藏机密列、提供逻辑数据独立性。与表的本质区别比较点表视图数据存储物理存储真实的行数据不存数据仅查询定义二、创建视图 vs 表1. 创建视图sql CREATE VIEW 视图名 [(列别名1,列别名2,…)] AS SELECT语句 [WITH CHECK OPTION]; 示例sql -- 创建只含成年学生的视图 CREATE VIEW v_adult_student AS SELECT id, name, age FROM student WHERE age 18 WITH CHECK OPTION; · WITH CHECK OPTION通过视图增改数据时必须满足 WHERE 条件否则操作被拒绝。2. 创建表sql CREATE TABLE 表名 ( 列名 数据类型 [约束], … ); 对比要点操作表视图语法核心定义列、类型、约束只需一条SELECT查询数据存放立刻分配空间可插入数据不存放数据执行时实时查询约束定义可定义主键、外键、CHECK等不能直接设约束靠WITH CHECK OPTION强制筛选条件三、修改定义视图 vs 表1. 修改视图定义sql -- 方法1ALTER VIEW ALTER VIEW 视图名 AS 新SELECT语句; -- 方法2CREATE OR REPLACE VIEW存在则替换不存在则创建 CREATE OR REPLACE VIEW 视图名 AS 新SELECT语句; · 修改的是视图的查询逻辑对基本表的数据毫无影响。2. 修改表结构sql ALTER TABLE 表名 ADD/DROP/MODIFY/CHANGE 列…; · 会直接改变物理存储结构可能影响已有数据。对比要点操作表视图修改方式ALTER TABLE 增删改列ALTER VIEW或CREATE OR REPLACE VIEW 重新定义查询影响范围改变实际列定义可能丢失/转化数据只改虚拟定义基本不变四、删除视图 vs 表1. 删除视图sql DROP VIEW [IF EXISTS] 视图名; · 仅删除视图定义基本表及数据毫发无损。2. 删除表sql DROP TABLE [IF EXISTS] 表名; ·表结构和所有数据永久删除依赖该表的视图将失效。对比操作表视图删除命令DROP TABLEDROP VIEW后果结构数据全部消失只删虚拟定义基本表安然无恙五、查看定义快速对比目的表视图看结构DESC 表名DESC 视图名看创建语句SHOW CREATE TABLE 表名SHOW CREATE VIEW 视图名查询元数据information_schema.TABLESinformation_schema.VIEWS六、更新视图数据INSERT / UPDATE / DELETE· 对视图执行增、删、改实质上是在操作基本表的数据。·不是所有视图都能更新 必须满足“可更新视图”的条件。1. 可更新视图的条件重点视图必须满足· 只来源于单个基本表多表连接通常不可直接更新· 视图定义中不包含以下内容· 聚合函数SUM, AVG, COUNT, MAX, MIN· DISTINCT· GROUP BY / HAVING· UNION / UNION ALL· 子查询引用了 FROM 后的表· 一般来说行列与基本表一一对应的简单视图才可更新。示例sql -- 可更新的简单视图 CREATE VIEW v_student_simple AS SELECT id, name FROM student; UPDATE v_student_simple SET name张三 WHERE id1; -- 实际更新student表 -- 不可更新的视图包含聚合 CREATE VIEW v_count AS SELECT dept, COUNT(*) cnt FROM emp GROUP BY dept; -- 对此视图执行 INSERT/UPDATE/DELETE 会失败 2. WITH CHECK OPTION 的作用sql -- 视图条件只展示 age18 的学生 CREATE VIEW v_adult AS SELECT * FROM student WHERE age18 WITH CHECK OPTION; -- 成功插入 age20 的行 INSERT INTO v_adult VALUES(101,李四,20); -- 失败插入 age17 的行会被拒绝违反视图条件 INSERT INTO v_adult VALUES(102,王五,17);与直接更新表的对比更新操作直接操作表通过视图操作限制只受表自身约束主键、非空等除表约束外还受视图更新条件限制必须视图可更新检查机制无额外检查若定义时带 WITH CHECK OPTION会强制检查 WHERE 条件使用场景所有数据维护针对特定子集的安全操作七、视图与数据表全面对比总结对比项数据表 (Table)视图 (View)数据存储物理存储真实行数据虚拟表不存储数据占用空间随数据量增长仅存储定义几乎无开销创建语法CREATE TABLE 定义列定义列 CREATE VIEW … AS SELECT修改语法ALTER TABLE 改结构ALTER VIEW 重新定义查询删除影响数据与结构全部删除只删除定义基本表不受影响索引可以创建索引不能建索引查询利用基本表索引约束主键、外键、CHECK等无法直接建约束可用 WITH CHECK OPTION更新数据无额外限制满足约束即可仅简单单表视图可更新且可能受条件检查依赖关系独立存在依赖基本表表结构变化可能导致视图失效主要用途永久存储数据查询简化、安全过滤、逻辑抽象八、考试可能出现的重点问题1. “视图是否存储数据” → 不存储只存定义是虚拟表。2. 创建视图的语法以及 WITH CHECK OPTION 的含义 → 强制通过视图增改的数据满足视图条件。3. 哪些视图不可更新 → 包含聚合、DISTINCT、GROUP BY、UNION、多表连接等。4. 删除视图会不会删除基本表数据 → 不会删除视图只删定义。5. 视图的作用 → 简化复杂查询、保护数据安全、提供逻辑独立性。6. 基本表被删后视图会怎样 → 视图依然存在定义但查询时会报错失效。