MySQL 视图、用户与权限管理详解

查询学生的姓名和总分（隐藏学号和各科成绩）：

-- 直接使用真实表查询
SELECT s.name, SUM(sc.sco)
FROM student s, score sc
WHERE s.id = sc.student_id GROUP BY s.name;

-- 使用视图
CREATE VIEW v_total AS
SELECT s.name, SUM(sc.sco)
FROM student s, score sc
WHERE s.id = sc.student_id GROUP BY s.name;

SELECT name FROM v_total;

若直接使用真实表查询，可以随意查看所需信息，但在银行系统等场景中，这无法保证信息的安全性。视图可以与真实表进行连接查询。

1.3 修改数据

对真实表数据的修改会影响视图，因为视图本质保存的是查询语句。通过视图修改数据也会影响到基表，但并非所有视图都可以修改。

以下条件的视图不可以修改：

• 创建视图时使用聚合函数
• 创建视图时使用 DISTINCT
• 创建视图使用 GROUP BY 以及 HAVING 子句
• 创建视图使用 UNION 或者 UNION ALL
• 查询列表使用子查询
• 在 FROM 子句中引用不可更新的视图

因此，大部分情况下建议直接修改真实表。

1.4 删除视图

删除视图语法：

DROP VIEW view_name;

查看创建的视图：

SHOW TABLES;

删除视图：

DROP VIEW v_java_or_mysql, v_java_sco, v_total;

1.5 视图的优点

• 简单性：将复杂的 SQL 语句封装为简单的查询语句。
• 安全性：隐藏表中的敏感数据。
• 逻辑数据独立性：底层表结构变化时，只需修改视图定义，无需修改依赖视图的应用程序。
• 重命名列：允许用户重命名列，增强数据可读性。

二。用户

数据库服务安装完成后默认存在 root 用户（超级管理员），拥有最高权限。通常需为当前应用添加特定用户并指定权限。

2.1 查看用户

用户信息保存在系统数据库 mysql 的 user 表中。

USE mysql;
SELECT host, user, authentication_string FROM user;

• host：表示谁可以登录。
• user：表示用户名。
• authentication_string：表示加密后的用户密码。

2.2 创建用户

创建用户语法：

CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'auth_string';

• 'user_name'@'host_name'：用户描述方式。
• 'user_name'：登录用户名。
• 'host_name'：可登录的主机名或 IP。不指定相当于 '%'（所有主机），可能存在安全隐患。
• 'auth_string'：密码明文。

示例：创建只允许本机登录的用户。

CREATE USER 'sunny'@'localhost' IDENTIFIED BY '8888';

示例：创建允许从特定网段登录的用户。

CREATE USER 'rain'@'192.168.10.1/24' IDENTIFIED BY '666';

2.3 修改密码

修改密码语法：

ALTER USER 'user_name'@'host_name' IDENTIFIED BY 'auth_string';
SET PASSWORD FOR 'user_name'@'host_name' = 'auth_string';
SET PASSWORD = 'auth_string'; -- 为当前用户设置

示例：

ALTER USER 'sunny'@'localhost' IDENTIFIED BY '6';

2.4 删除用户

删除用户语法：

DROP USER 'user_name'@'host_name';

示例：

DROP USER 'rain'@'192.168.10.1/24';

三。权限

用户创建后，需赋予专属权限。

3.1 查看当前用户权限

查看语法：

SHOW GRANTS FOR 'user_name'@'host_name';

示例：

SHOW GRANTS FOR 'sunny'@'localhost';

3.2 添加权限

添加权限语法：

GRANT priv_type ON priv_level TO 'user_name'@'host_name';

• priv_type：权限类型。
• priv_level：权限级别（*.* 表示所有数据库下的所有表）。

示例：授予查看 test 数据库的权限。

GRANT SELECT ON test.* TO 'sunny'@'localhost';

示例：授予 test 数据库所有权限。

GRANT ALL ON test.* TO 'sunny'@'localhost';

3.3 回收权限

回收权限语法：

REVOKE priv_type ON priv_level FROM 'user_name'@'host_name';

示例：

REVOKE ALL ON *.* FROM 'sunny'@'localhost';

注意事项： 赋予和收回权限时需注意当前用户是否有相应能力，建议使用 root 超级管理员进行操作。