MySQL视图介绍与实验练习

1. MySQL 中的视图（View）简介

1.1 视图的基本概念：

视图是一种虚拟表，其内容由一个查询定义。它提供了一种将复杂查询逻辑封装成可重用的结构的方式。

1.2 创建视图：

使用 CREATE VIEW 语句可以创建视图。例如：

CREATE VIEW my_view AS
SELECT column1, column2
FROM my_table
WHERE condition;

1.3 查看视图：

使用 SELECT 语句可以查询视图。例如：

SELECT * FROM my_view;

1.4 更新视图：

视图可以被更新，但有一些限制。一般来说，只有满足特定条件的视图才能被更新，例如视图中没有使用 GROUP BY 或 DISTINCT。

1.5 删除视图：

使用 DROP VIEW 语句可以删除视图。例如：

DROP VIEW my_view;

1.6 视图的嵌套：

视图可以嵌套，即在一个视图中引用另一个视图。

1.7 权限管理：

视图的访问权限和表一样，可以通过 GRANT 和 REVOKE 语句进行管理。

1.8 检查视图信息：

可以通过 SHOW CREATE VIEW 或查询 information_schema 数据库中的相关表，如 VIEWS 表，来查看视图的定义和信息。

2 关系型数据库视图和数据仓库中子图（Subset）的区别：

2.1 关系型数据库视图：

1. 定义： 视图是一个虚拟表，其内容是基于一个或多个基本表的查询结果。视图不存储实际的数据，而是根据定义的查询动态生成结果。

2. 用途： 视图通常用于简化复杂查询、提供安全性、隐藏底层表结构、以及在应用程序中重用查询逻辑。

3. 实时性： 视图是实时的，即每次查询都基于底层表的当前状态生成结果。因此，当基础表的数据发生变化时，视图的结果也会随之变化。

4. 存储： 视图不存储实际数据，它只存储查询定义。数据实际上仍存储在底层的表中。

2.2 数据仓库中的子图：

1. 定义： 在数据仓库中，子图通常指的是一个包含了数据仓库中部分数据的子集。这个子集可能是根据某些特定条件（例如，特定时间范围、特定业务区域等）而选择的。

2. 用途： 子图用于分割大型数据仓库的数据，以便更快地进行查询和分析。它可以是一个按照某个维度划分的区域，也可以是一个按照时间划分的时间段。

3. 历史数据： 数据仓库中的子图可能包含历史数据的快照，允许分析历史趋势和模式。

4. 聚合： 子图中的数据可能是经过预先计算或聚合的，以支持更快速的查询和报告生成。

5. 周期性更新： 数据仓库中的子图可能不是实时更新的，而是按照某个周期进行更新，以保持高性能查询的同时降低数据仓库的负载。

关系型数据库视图主要用于查询和简化数据库的使用，而数据仓库中的子图则更侧重于优化数据仓库的性能，支持复杂的分析和报告需求。两者在设计和使用上有不同的考虑因素，根据具体业务需求和数据特点选择合适的技术。

3 MySQL视图实验

3.1 数据准备

3.1.1 开发环境

MySQL 8.1
Windows 11

3.1.2 创建数据库

DROP DATABASE IF EXISTS shop;
CREATE DATABASE shop;
USE shop;

3.1.3 创建goods表

CREATE TABLE goods (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '商品id',
 category_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分类id',
 name VARCHAR(120) NOT NULL DEFAULT '' COMMENT '名称',
 keyword VARCHAR(255) NOT NULL DEFAULT '' COMMENT '关键词',
 content TEXT NOT NULL COMMENT '详情',
 price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '价格',
 stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
 score DECIMAL(3, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分',
 comment_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评论计数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.1.4 插入goods表

INSERT INTO goods (id, category_id, name, keyword, content,price,stock, score, comment_count) VALUES
(1, 3, '2B铅笔', '文具', '考试专用', 0.5, 500, 4.9, 40000),
(2, 3, '钢笔', '文具', '练字必不可少', 15, 300, 3.9, 500),
(3, 3, '碳素笔', '文具', '平时使用', 1, 500, 5, 98000),
(4, 12, '超薄笔记本', '电子产品', '轻小便携', 5999, 0, 2.5, 200),
(5, 6, '智能手机', '电子产品', '人人必备', 1999, 0, 5, 98000),
(6, 8, '桌面音箱', '电子产品', '扩音装备', 69, 750, 4.5, 1000),
(7, 9, '头戴耳机', '电子产品', '独享个人世界', 109, 0, 3.9, 500),
(8, 10, '办公电脑', '电子产品', '适合办公', 2000, 0, 4.8, 6000),
(9, 15, '收腰风衣', '服装', '春节潮流单品', 299, 0, 4.9, 40000),
(10, 16, '薄毛衣', '服装', '居家旅行必备', 48, 0, 4.8, 98000);

3.1.5 创建category表

CREATE TABLE category (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '分类id',
 parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级分类id',
 name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称',
 sort INT NOT NULL DEFAULT 0 COMMENT '排序',
 is_show TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否显示',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.1.6 插入category表

INSERT INTO category (id, parent_id, name) VALUES
(1, 0, '办公'), (2, 1, '耗材'), (3, 2, '文具'),
(4, 0, '电子产品'), (5, 4, '通讯'), (6, 5, '手机'),
(7, 4, '影音'), (8, 7, '音箱'), (9, 7, '耳机'),
(10, 4, '电脑'), (11, 10, '台式电脑'), (12, 10, '笔记本'),
(13, 0, '服装'), (14, 13, '女装'), (15, 14, '风衣'), (16, 14, '毛衣');

3.2 创建视图

create view gc_views as 
select goods.id, goods.name,category.name as category_name
from goods
join category on goods.category_id = category.id;

3.3 修改视图列

ALTER VIEW gc_views AS
SELECT id, name
FROM goods;

3.4 查看视图相关状态

select * from gc_views;

desc gc_views

show create table gc_views

3.5 另一种创建视图的方式

CREATE OR REPLACE VIEW gc_views AS
SELECT goods.id, goods.name FROM goods;

3.6 向视图源表插入数据

INSERT INTO goods (id,name)VALUES (11, '图书');


INSERT INTO goods (id, name, content)
VALUES (11, '图书', 'hahaha');

3.7 通过视图更新数据

UPDATE gc_views
SET name = '家电'
WHERE id = 11;

3.8 删除视图中数据

DELETE FROM gc_views
WHERE id = 11;

3.9 创建复杂筛选的视图

CREATE VIEW v1 AS
SELECT id, name
FROM category
WHERE id < 30;


CREATE VIEW v2 AS
SELECT id, name
FROM v1
WHERE id > 20;