MySQL高级特性：视图与DCL

一、视图（View）

1.1 什么是视图

视图是一个虚拟表，其内容由查询定义。视图并不存储数据，而是基于 SQL 语句的结果集。可以像操作普通表一样对视图进行查询，但视图的增删改操作有一定限制。

视图的特点：

• 视图是虚拟表，不占用实际存储空间（只保存定义）
• 可以简化复杂查询，封装业务逻辑
• 可以限制数据访问，提高安全性
• 视图中的数据随基表数据实时变化

┌─────────────────────────────────────────────┐
│                  用户层                       │
│         SELECT * FROM v_employee             │
├─────────────────────────────────────────────┤
│                  视图层                       │
│   CREATE VIEW v_employee AS SELECT ...      │
├─────────────────────────────────────────────┤
│                  基表层                       │
│         tb_employee, tb_department          │
└─────────────────────────────────────────────┘

1.2 创建视图

-- 基本语法
CREATE [OR REPLACE] VIEW 视图名 AS SELECT语句;

-- 示例1：创建简单视图
CREATE VIEW v_employee_basic AS
SELECT id, name, department_id, position, salary
FROM tb_employee
WHERE status = 1;

-- 示例2：创建多表关联视图
CREATE VIEW v_employee_detail AS
SELECT 
    e.id,
    e.name,
    e.position,
    e.salary,
    d.department_name,
    d.location
FROM tb_employee e
INNER JOIN tb_department d ON e.department_id = d.id;

-- 示例3：创建带计算的视图
CREATE VIEW v_employee_salary AS
SELECT 
    id,
    name,
    salary,
    salary * 12 AS annual_salary,
    salary * 0.1 AS bonus,
    salary * 1.1 AS total_income
FROM tb_employee
WHERE status = 1;

-- 示例4：创建聚合视图（统计各部门信息）
CREATE VIEW v_department_stats AS
SELECT 
    d.id AS department_id,
    d.department_name,
    COUNT(e.id) AS employee_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM tb_department d
LEFT JOIN tb_employee e ON d.id = e.department_id
GROUP BY d.id, d.department_name;

1.3 查看与管理视图

-- 查看数据库中所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 查看视图定义
SHOW CREATE VIEW v_employee_detail;

-- 查看视图结构（像表一样）
DESCRIBE v_employee_detail;

-- 修改视图（使用 OR REPLACE）
CREATE OR REPLACE VIEW v_employee_basic AS
SELECT id, name, department_id, position, salary, hire_date
FROM tb_employee
WHERE status = 1;

-- 删除视图
DROP VIEW IF EXISTS v_employee_basic;

-- 删除多个视图
DROP VIEW IF EXISTS v_employee_basic, v_employee_detail;

1.4 视图的数据操作

-- 通过视图查询数据（和查询表一样）
SELECT * FROM v_employee_basic WHERE salary > 5000;

-- 通过视图插入数据（需满足视图和基表的约束）
INSERT INTO v_employee_basic (id, name, department_id, position, salary)
VALUES (100, '王五', 1, '工程师', 8000);
-- 注意：插入的数据必须满足视图的 WHERE 条件（status=1）

-- 通过视图更新数据
UPDATE v_employee_basic SET salary = 9000 WHERE id = 100;

-- 通过视图删除数据
DELETE FROM v_employee_basic WHERE id = 100;

视图更新的限制：

• 视图中包含聚合函数（SUM、COUNT、AVG 等）时不可更新
• 视图中包含 DISTINCT、GROUP BY、HAVING 时不可更新
• 视图中包含 UNION、UNION ALL 时不可更新
• 视图来自多表关联时，通常只能更新单表数据
• 视图中包含计算列时，该列不可更新

1.5 视图的应用场景

场景	说明	示例
简化复杂查询	将复杂的多表 JOIN 封装成视图	员工详情视图关联部门表
数据安全	隐藏敏感字段，只暴露必要字段	员工视图不显示身份证号、工资
逻辑抽象	封装业务逻辑，变更时只需修改视图	计算税后工资、绩效等
数据独立	应用程序基于视图开发，基表结构变更不影响应用	字段重命名时调整视图即可

-- 场景：数据安全 - 创建不显示敏感信息的视图
CREATE VIEW v_employee_public AS
SELECT 
    id,
    name,
    department_id,
    position,
    hire_date
FROM tb_employee;
-- 此视图不包含 salary、email、phone 等敏感信息

-- 场景：简化报表查询
CREATE VIEW v_monthly_report AS
SELECT 
    DATE_FORMAT(o.create_time, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS total_amount,
    COUNT(DISTINCT o.user_id) AS unique_users
FROM tb_order o
WHERE o.status = '已完成'
GROUP BY DATE_FORMAT(o.create_time, '%Y-%m');

-- 直接查询视图获取报表数据
SELECT * FROM v_monthly_report ORDER BY month DESC LIMIT 12;

---

二、DCL 数据控制语言

DCL（Data Control Language）用于定义数据库的访问权限和安全级别，主要关键字包括 CREATE USER、GRANT、REVOKE 等。

2.1 用户管理

创建用户

-- 基本语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 示例1：创建本地访问用户
CREATE USER 'qff'@'localhost' IDENTIFIED BY '123456';

-- 示例2：创建任意主机访问用户
CREATE USER 'admin'@'%' IDENTIFIED BY 'Admin@123';

-- 示例3：创建指定IP访问用户
CREATE USER 'backup'@'192.168.1.%' IDENTIFIED BY 'Backup@123';

-- 示例4：创建无密码用户（不推荐）
CREATE USER 'test'@'localhost';

主机名说明：

主机名格式	含义
localhost	仅本地访问
%	任意主机访问
192.168.1.%	192.168.1.x 网段
%.example.com	example.com 域名下的主机

修改用户

-- 修改密码
ALTER USER 'qff'@'localhost' IDENTIFIED BY 'NewPass@123';

-- MySQL 8.0+ 推荐使用
ALTER USER 'qff'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPass@123';

-- 修改用户名
RENAME USER 'qff'@'localhost' TO 'qiufenfen'@'localhost';

-- 锁定用户
ALTER USER 'qff'@'localhost' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'qff'@'localhost' ACCOUNT UNLOCK;

删除用户

-- 删除用户
DROP USER 'qff'@'localhost';

-- 删除用户（如果不存在不报错）
DROP USER IF EXISTS 'qff'@'localhost';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'%';

查看用户

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 查看当前登录用户
SELECT CURRENT_USER();
SELECT USER();

-- 查看用户权限
SHOW GRANTS FOR 'qff'@'localhost';

-- 查看当前用户权限
SHOW GRANTS;

2.2 权限管理

MySQL 常用权限

权限	说明	适用对象
ALL / ALL PRIVILEGES	所有权限	全局/数据库/表
SELECT	查询数据	表/列
INSERT	插入数据	表/列
UPDATE	修改数据	表/列
DELETE	删除数据	表
CREATE	创建数据库/表	全局/数据库/表
DROP	删除数据库/表/视图	全局/数据库/表
ALTER	修改表结构	表
INDEX	创建/删除索引	表
EXECUTE	执行存储过程	存储过程
REFERENCES	创建外键	表
RELOAD	刷新权限/日志	全局
SHUTDOWN	关闭服务器	全局
PROCESS	查看进程	全局
FILE	读写服务器文件	全局
SUPER	超级权限	全局

授予权限（GRANT）

-- 基本语法
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';

-- 示例1：授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';

-- 示例2：授予查询和更新权限
GRANT SELECT, UPDATE ON shop_db.* TO 'qff'@'localhost';

-- 示例3：授予特定表的特定权限
GRANT SELECT, INSERT, UPDATE ON shop_db.tb_customer TO 'qff'@'localhost';

-- 示例4：授予列级权限
GRANT SELECT (id, name, tel), UPDATE (address) 
ON shop_db.tb_customer TO 'qff'@'localhost';

-- 示例5：授予创建数据库/表的权限
GRANT CREATE ON *.* TO 'qff'@'localhost';

-- 示例6：授予并允许转授（WITH GRANT OPTION）
GRANT ALL ON shop_db.* TO 'manager'@'%' WITH GRANT OPTION;

-- 刷新权限（使权限立即生效）
FLUSH PRIVILEGES;

权限范围说明：

范围	写法	含义
全局	*.*	所有数据库的所有表
数据库	shop_db.*	shop_db 数据库的所有表
表	shop_db.tb_customer	shop_db 数据库的 tb_customer 表
列	shop_db.tb_customer(name)	tb_customer 表的 name 列

撤销权限（REVOKE）

-- 基本语法
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';

-- 示例1：撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'qff'@'localhost';

-- 示例2：撤销特定权限
REVOKE INSERT, DELETE ON shop_db.* FROM 'qff'@'localhost';

-- 示例3：撤销转授权限
REVOKE GRANT OPTION ON shop_db.* FROM 'manager'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

2.3 权限管理实战案例

-- ============================================
-- 场景：电商数据库权限管理
-- ============================================

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS shop_db CHARACTER SET utf8mb4;

-- 2. 创建管理员（拥有所有权限）
CREATE USER 'shop_admin'@'%' IDENTIFIED BY 'Admin@Shop123';
GRANT ALL PRIVILEGES ON shop_db.* TO 'shop_admin'@'%';

-- 3. 创建应用账号（只能读写业务表）
CREATE USER 'shop_app'@'192.168.1.%' IDENTIFIED BY 'App@Shop123';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.tb_customer TO 'shop_app'@'192.168.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.tb_order TO 'shop_app'@'192.168.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.tb_product TO 'shop_app'@'192.168.1.%';

-- 4. 创建只读账号（数据分析用）
CREATE USER 'shop_read'@'%' IDENTIFIED BY 'Read@Shop123';
GRANT SELECT ON shop_db.* TO 'shop_read'@'%';

-- 5. 创建备份账号
CREATE USER 'shop_backup'@'localhost' IDENTIFIED BY 'Backup@Shop123';
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON shop_db.* TO 'shop_backup'@'localhost';

-- 6. 刷新权限
FLUSH PRIVILEGES;

-- 7. 查看各用户权限
SHOW GRANTS FOR 'shop_admin'@'%';
SHOW GRANTS FOR 'shop_app'@'192.168.1.%';
SHOW GRANTS FOR 'shop_read'@'%';

2.4 角色管理（MySQL 8.0+）

MySQL 8.0 引入了角色（Role）功能，方便批量管理权限。

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 给角色授权
GRANT SELECT ON shop_db.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_write';
GRANT ALL ON shop_db.* TO 'app_admin';

-- 将角色授予用户
GRANT 'app_read' TO 'shop_read'@'%';
GRANT 'app_write' TO 'shop_app'@'192.168.1.%';
GRANT 'app_admin' TO 'shop_admin'@'%';

-- 设置用户默认角色
SET DEFAULT ROLE 'app_read' TO 'shop_read'@'%';
SET DEFAULT ROLE ALL TO 'shop_app'@'192.168.1.%';

-- 激活角色（登录后）
SET ROLE 'app_read';
SET ROLE ALL;

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

-- 撤销角色
REVOKE 'app_write' FROM 'shop_app'@'192.168.1.%';

-- 删除角色
DROP ROLE 'app_read';

---

三、视图与权限结合的应用

3.1 行级安全（通过视图实现）

-- 场景：普通员工只能查看自己部门的信息

-- 1. 创建按部门过滤的视图
CREATE VIEW v_employee_dept AS
SELECT * FROM tb_employee
WHERE department_id = (
    SELECT department_id FROM tb_employee 
    WHERE id = CURRENT_USER_ID()  -- 假设有函数获取当前用户ID
);

-- 2. 授予用户对视图的查询权限，不授予基表权限
GRANT SELECT ON shop_db.v_employee_dept TO 'employee'@'%';
REVOKE SELECT ON shop_db.tb_employee FROM 'employee'@'%';

3.2 列级安全（通过视图实现）

-- 场景：HR可以查看员工全部信息，普通员工只能查看基本信息

-- 1. 创建公开视图（不含敏感信息）
CREATE VIEW v_employee_public AS
SELECT id, name, department_id, position, hire_date
FROM tb_employee;

-- 2. 创建HR视图（含全部信息）
CREATE VIEW v_employee_hr AS
SELECT * FROM tb_employee;

-- 3. 分别授权
GRANT SELECT ON shop_db.v_employee_public TO 'employee'@'%';
GRANT SELECT ON shop_db.v_employee_hr TO 'hr'@'%';

---

💡 小结：本章介绍了 MySQL 的两大高级特性——视图和 DCL。视图是虚拟表，可简化查询、保障数据安全；DCL 用于用户管理和权限控制，通过 GRANT/REVOKE 精确控制数据库访问权限。在实际项目中，建议遵循最小权限原则，结合视图实现行列级数据安全。