从入门到精通【MySQL】视图与用户权限管理

📕1. 视图

✏️1.1 视图的基本概念

视图是一个虚拟的表，它是基于一个或多个基本表或其他视图的查询结果集。视图本身不存储数据，而是通过执行查询来动态生成数据。用户可以像操作普通表⼀样使用视图进行查询、更新和管理。视图本身并不占用物理存储空间，它仅仅是一个查询的逻辑表示，物理上它依赖于基础表中的数据。

✏️1.2 试图的基本操作

🔖1.2.1 创建视图

create view 列名1，列名2，列名3 as select 查询语句...

view 是视图的关键字

🔖1.2.2 使用视图

🌰例如：只查询用户的姓名和总分(隐藏学号和各科成绩)

# 使⽤真实表进⾏查询
select s.name, sum(sc.score) total from student s, score sc 
where s.id = sc.student_id
group by sc.student_id order by s.id;-- 缺点：可以随时在select关键字后加上学号和各科成绩字段，会暴露学生信息，不安全

# 创建视图
create view v_student_total_points as
select s.id, s.name, sum(sc.score) total from student s, score sc 
where s.id = sc.student_id 
group by s.id order by s.id;-- 使用视图进行查询
select * from v_student_total_points;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 469 |
| 孙悟空 | 179.5 |
| 猪悟能 | 200 |
| 沙悟净 | 218 |
| 宋江 | 118 |
| 武松 | 178 |
| 李逹 | 172 |
+-----------+-------+
-- 只能查询出姓名和总分，进一步保护了学生的个人信息

🌰例如：视图和真实表进行表连接查询

select * from v_student_total_points v, student s where v.id = s.id;-- 视图本质上是一张虚拟的表，所以可以用视图与真实表进行表连接查询

🔖1.2.3 修改数据

• 通过真实表修改数据，会影响视图

# 修改唐三藏的JAVA成绩为99分update score set score = 99 where student_id = 1 and course_id = 1;# 查询视图，发现唐三藏这条记录已被修改
select * from v_student_socre;

• 通过视图修改数据会影响基表

# 更新视图
update v_student_socre_v1 set score = 99 where score_id = 3;# 是看真实表数据已被修改
select * from score where student_id = 1 and course_id = 5;

注意事项：

1. 修改真实表会影响视图，修改视图同样也会影响真实表

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

🔖1.2.4 删除视图

# 语法
drop view 视图名;

✏️1.3 视图的优点

1. 简单性：视图可以将复杂的查询封装成一个简单的查询。例如，针对一个复杂的多表连接查询，可以创建一个视图，用户只需查询视图而无需了解底层的复杂逻辑。
2. 安全性：通过视图，可以隐藏表中的敏感数据。例如，⼀个系统的用户表中，可以创建一个不包含密码列的视图，普通用户只能访问这个视图，而不能访问原始表，进一步保证了安全问题。
3. 逻辑数据独立性：视图提供了一种逻辑数据独立性，即使底层表结构发生变化，只需修改视图定义，而无需修改依赖视图的应用程序。确保了应用程序与数据库的解耦
4. 重命名列：视图允许用户重命名列名，以增强数据可读性。

📕2. 用户与权限管理

数据库服务安装成功后默认有一个root用户，可以新建和操纵数据库服务中管理的所有数据库。在真
实的使用过程中，通常每个应用对应着一个数据库，我们只希望某个用户只能操纵和管理当前应用对
应的那个数据库，而不能操纵和管理其他应用的数据库，这时就可以添加⼀个用户并指定用户的权限

如上图所示：
root 可以访问和操纵所有的数据库：DB1, DB2, DB3, DB4
普通用户1 只能访问和操纵数据库DB1
普通用户2 只能访问和操纵数据库DB3
只读用户1 只能访问数据库DB3
只读用户2 只能访问数据库DB4

✏️2.1 用户

🔖2.1.1 查看用户

-- 选择数据库
use mysql;-- 查看表结构
desc user;-- 查看用户表
select * from user;

host： 允许登录的主机，相当于白名单，如果是localhost，表示只能从本机登陆
user： 用户名
*_priv： 用户拥有的权限,Y表示有权限,N表示没有权限
authentication_string： 加密后的用户密码

🔖2.1.2 创建用户

create user if  not exists 'user_name'@'host_name' identified by 'auth_string';

user_name: 用户名，用单引号包裹，区分大小写
host_name: 主机或IP(段)，⽤单引号包裹
auth_string: 真实密码，有些密码策略不允许使用简单密码

例如:创建名为zhuxulong 密码为123456 的账户

create user if  not exists 'zhuxulong'@'172.20.109.85' identified by '123456';

注意事项:

• 如果不指定host_name相当于’user_name’@‘%’， %表示所有主机都可以连接到数据库，强烈建
  议不要这样设置，因为会导致严重的安全问题
• user_name和host_name分别用单引号包裹，如果写成’user_name@host_name’, 相当于’user_name@host_name’@‘%’
• host_name可以通过子网掩码设置主机范围
  A: 198.0.0.0 : A段网络中的任意一台主机
  B: 198.51.0.0 B段网络中的任意一台主机’
  C: 198.51.100.0 C段网络中的任意一台主机
  D: 198.51.100.1 :只包含特定IP地址的主机

🔖2.1.3 修改密码

# 为指定⽤⼾设置密码 【推荐】
ALTER USER 'user_name'@'host_name' IDENTIFIED BY 'auth_string';# 为指定⽤⼾设置密码
SET PASSWORD FOR 'user_name'@'host_name' = 'auth_string';# 为当前登录⽤⼾设置密码
SET PASSWORD = 'auth_string';

🔖2.1.4 删除用户

DROP USER [IF EXISTS] 'user_name'@'host_name'[, ...];

✏️2.2 权限与授权

MySQL内置支持的权限列表

🔖2.2.1 给用户授权

刚刚创建的用户没有任何权限,我们需要手动为新用户授权.

grant 权限名 on priv_level to 'user_name'@'host_name' [WITH GRANT OPTION]

权限名：根据类型，参考根据列表4.1中的Privilege列

priv_level： * | . | db_name.* | db_name.tbl_name | tbl_name，比如*.*表示所有数据库下的所有表

‘user_name’@‘host_name’：指定用户

[WITH GRANT OPTION]：可选，允许用户将自己的权限授权给其它用户

示例: 为刚刚创建的zhuxulong用户授予test数据库student表的select权限

grant select on  test.student to 'zhuxulong'@'172.20.109.85';

🔖2.2.2 回收用户授权

revoke 权限名  on 数据库名.表名 from 'user_name'@'host_name';