一、视图 (View)

1. 什么是视图？

视图是一个虚拟表，它本身不存储数据，而是基于一个或多个基本表（或其他视图）的查询结果集动态生成。

• 视图的本质是对一段复杂 SQL 的封装，执行查询时才会动态计算结果。
• 对视图的操作最终会转化为对基本表的操作。

2. 为什么使用视图？

• 简化复杂查询：将多表连接、分组统计等复杂 SQL 封装为视图，调用时只需简单查询。
• 数据安全与隔离：限制用户只能访问特定列或行，隐藏敏感数据（如总分、密码）。
• 解耦应用与数据库：当基本表结构变更时，可通过修改视图保持接口稳定，避免修改应用代码。

3. 创建视图

sql

-- 语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

-- 示例：创建学生成绩视图
CREATE VIEW v_student_score AS
SELECT s.id, s.name, s.sno, s.age, s.gender, s.enroll_date,
       c.id AS class_id, c.name AS class_name,
       co.id AS course_id, co.name AS course_name,
       sc.score
FROM student s
JOIN class c ON s.class_id = c.id
JOIN score sc ON s.id = sc.student_id
JOIN course co ON sc.course_id = co.id;

⚠️ 注意：查询列名若重复，必须在视图中指定别名；列名不重复时可自动继承。

4. 查看与使用视图

sql

-- 查看所有视图
SHOW FULL TABLES WHERE table_type = 'VIEW';

-- 查询视图（和查询普通表一样）
SELECT * FROM v_student_score;

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

5. 修改视图数据

视图数据本质依赖于基本表，通过视图修改数据会直接影响基本表，但有严格限制：

• 视图必须基于单表，且包含主键。
• 不能包含 DISTINCT、GROUP BY、UNION 等聚合或集合操作。
• 不能包含计算列（如 SUM(score)）。

sql

-- 示例：通过视图修改成绩
UPDATE v_student_score SET score = 99 WHERE id = 1 AND course_id = 1;

✅ 最佳实践：视图主要用于查询，尽量避免通过视图修改数据。

6. 修改与删除视图

sql

-- 修改视图（替换原有定义）
CREATE OR REPLACE VIEW v_student_score AS
SELECT s.id, s.name, sc.score FROM student s JOIN score sc ON s.id = sc.student_id;

-- 删除视图
DROP VIEW [IF EXISTS] v_student_score;

---

二、用户与权限管理

1. 核心概念

• root 用户：MySQL 中权限最大的超级管理员，拥有所有操作权限。
• 权限：定义了用户对数据库对象（库、表、列等）的操作能力（如 SELECT、INSERT、UPDATE）。
• 主机 (host)：限制用户只能从指定的 IP 或主机名连接 MySQL。

2. 用户管理

2.1 创建用户

sql

-- 语法
CREATE USER [IF NOT EXISTS] 'user_name'@'host_name'
IDENTIFIED BY 'password';

-- 示例：创建只能从本地连接的用户
CREATE USER 'test_user'@'localhost' IDENTIFIED BY '123456';

-- 示例：创建可从任意主机连接的用户（生产环境不推荐）
CREATE USER 'test_user'@'%' IDENTIFIED BY '123456';

• host_name 常用值：
  • localhost：仅允许本地连接。
  • %：允许从任意主机连接。
  • 192.168.1.%：允许从 192.168.1.0/24 网段连接。

2.2 修改用户密码

sql

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';

-- 修改其他用户密码（需 `CREATE USER` 权限）
ALTER USER 'test_user'@'localhost' IDENTIFIED BY 'new_password';

2.3 删除用户

sql

DROP USER [IF EXISTS] 'test_user'@'localhost';

3. 权限管理

3.1 授予权限

sql

-- 语法
GRANT privilege_type [, privilege_type] ...
ON database.table
TO 'user_name'@'host_name' [, 'user_name'@'host_name'] ...
[WITH GRANT OPTION]; -- 允许该用户将权限授予他人

-- 示例：授予用户对 javal15_116 库的所有表的查询权限
GRANT SELECT ON javal15_116.* TO 'test_user'@'localhost';

-- 示例：授予用户对 javal15_116.student 表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON javal15_116.student TO 'test_user'@'localhost';

-- 示例：授予超级管理员权限（生产环境谨慎使用）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

3.2 查看权限

sql

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

-- 查看其他用户权限
SHOW GRANTS FOR 'test_user'@'localhost';

3.3 撤销权限

sql

-- 语法
REVOKE privilege_type [, privilege_type] ...
ON database.table
FROM 'user_name'@'host_name';

-- 示例：撤销用户的查询权限
REVOKE SELECT ON javal15_116.* FROM 'test_user'@'localhost';

---

三、最佳实践总结

视图最佳实践

1. 视图用于查询：优先将视图作为简化查询、数据隔离的工具，避免用于数据修改。
2. 命名规范：视图名以 v_ 或 view_ 开头，与表名区分开。
3. 避免嵌套过深：视图嵌套层数过多会影响查询性能和可读性。

用户权限最佳实践

1. 最小权限原则：只授予用户完成工作所需的最小权限，避免授予 ALL PRIVILEGES。
2. 限制主机访问：生产环境中，尽量将用户的 host 限制为具体 IP 或内网网段，避免使用 %。
3. 密码安全：使用强密码，定期更换，避免在代码中硬编码密码。
4. 权限分离：不同业务场景使用不同用户，如只读用户、读写用户、管理员用户。