当前位置：首页 > article >正文

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

article 2025/4/2 1:40:13

视图

视图的基本介绍

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), -- 依赖 country
    address 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)
);