【MySQL数据库】视图 + 三范式

视图

视图的基本介绍

MySQL中的视图（View）是一种虚拟的表，其内容是从一个或多个基本表中检索出来的。视图可以简化复杂的查询操作，提高查询效率，同时也可以对敏感数据进行安全性控制。下面是关于MySQL视图的一些基本知识：

视图的基本操作

1.创建视图：

create view view_name as
select col1, col2, ...
from tbname
where condition;

创建的视图就是select查询的结果构成的一张表，有时候查询比较复杂但又比较常用，我们就可以创建视图来指向这个“临时表”，这样也不用每次都执行一组查询语句了，能够提高查询效率。 

这里as作引用，而非起别名。【创建视图涉及两个算法：合并算法、临时表算法】

2.更新视图：

视图分为可更新视图和不可更新视图：

在MySQL中，视图默认是不可更改的，要更新视图的数据，需要满足一定条件：

1.如果视图包含聚合函数、distinct、group by、union等操作，通常不可更新。

        因为这些操作改变了原始数据的结构，数据库无法确定如何将修改应用到基表

2.如果视图中使用了join，尤其是多表连接，可能也无法更新，特别涉及多个基表时。

        更新可能会导致数据不一致问题。

3.检查约束或计算列也可能影响可更新性。

        如果视图中的列是计算得出的，没有对应的基表列，那么就无法更新这样的列。

-- 可更新视图
create view abc as 
select * from emp;insert into abc(empno) values(111);update abc set empno=222 where empno=111;
insert into dept_emp(empno) values(111);--不可更新视图
create view abc2 as
select deptno，count(*) from emp group by deptno;insert into abc2(deptno) values(111);

3.查看视图：

我们所说的查询视图是查询视图的结构，而不是内容，如果要查询视图的内容，我们只需要从视图中查询数据即可。

-- 查询视图的结构
show create view view_name;

4.删除视图：

drop view view_name;

5.替换视图：

在创建视图时，如果视图名称已存在，可以使用 OR REPLACE 来替换已有的视图定义：

create or replace view view_name as
select ...

6.查看所有视图：

show full tables in 数据库名 where table_type like 'view';

7.安全权限：

在MySQL中，可以使用 GRANT 和 REVOKE 语句为用户赋予或撤销对视图的访问权限：

GRANT SELECT ON database_name.view_name TO 'username'@'hostname';
REVOKE SELECT ON database_name.view_name FROM 'username'@'hostname';

以上是关于MySQL视图的一些基本知识，视图是一个非常有用的数据库对象，可以简化查询操作，提高数据安全性，并且能够更好地组织和管理数据。

---

三范式

第一范式(1NF)：原子性

第一范式是最基础的，确保原子性，没有重复列。

比如：订单表在拆分商品到多个列的问题。

-- 错误示例：商品列包含多个值（违反原子性）
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_name VARCHAR(50),products VARCHAR(200) -- 存储如 "商品A, 商品B, 商品C"
);-- 根据1NF修改后：
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_name VARCHAR(50)
);CREATE TABLE order_items (order_item_id INT PRIMARY KEY,order_id INT,product_name VARCHAR(100),FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

第二范式(2NF)：解决部分依赖

第二范式主要解决部分依赖的问题（含有联合主键时导致的），强调非主属性完全依赖主键（所以必须要有主键，那么唯一性就是说要有主键。）

比如：一个订单明细表，其中产品名依赖于产品ID，而产品ID又依赖于订单ID，导致部分依赖。

-- 错误示例：订单明细表中，产品名称仅依赖产品ID，而非整个主键（order_id + product_id）
CREATE TABLE order_items (order_id INT,product_id INT,product_name VARCHAR(100), -- 仅依赖 product_id，而非 (order_id, product_id)quantity INT,PRIMARY KEY (order_id, product_id)
);-- 将产品信息独立为表，消除部分依赖
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100)
);CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id),FOREIGN KEY (product_id) REFERENCES products(product_id)
);

第三范式(3NF)：消除传递依赖

第三范式主要消除传递依赖的问题，确保非主属性不依赖其它非主属性，这个和第二范式的完全依赖有点像。

比如：用户表中地址依赖于城市，而城市又依赖于国家，这就会导致传递依赖。需要拆分。

-- 错误示例：用户地址依赖于城市，城市依赖于国家（传递依赖）
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),country VARCHAR(50),city VARCHAR(50), -- 依赖 countryaddress VARCHAR(100) -- 依赖 city
);-- 将国家、城市拆分为独立表
CREATE TABLE countries (country_id INT PRIMARY KEY,country_name VARCHAR(50)
);CREATE TABLE cities (city_id INT PRIMARY KEY,city_name VARCHAR(50),country_id INT,FOREIGN KEY (country_id) REFERENCES countries(country_id)
);CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),city_id INT,address VARCHAR(100),FOREIGN KEY (city_id) REFERENCES cities(city_id)
);

===结语===

虽然设计上有三大范式，但是我们要避免过度范化导致性能问题，或者理解上的偏差。三范式的坏处就是查询语句比较复杂时，需要多表查询，多表之间不存在索引对应关系，所以效率上会降低。所以根据实际应用的权衡利弊，我们还有一些反范式化的情况。

三范式的核心思想：

        1.减少冗余

        2.逻辑清晰

        3.消除异常：更新异常、插入异常、删除异常

 

更新异常：修改一处数据需要同步修改多处

插入异常：因依赖关系无法插入部分数据

删除异常：删除数据时意外丢失其它信息

---

感谢大家！