[MySQL#14] 视图 | 用户管理 | 权限设置

目录

一. 视图

1. 什么是视图？

2. 基本使用

1. 语法

2. 示例

数据同步实验

3. 视图规则和限制

二. 用户管理

1. 用户信息

2. 创建用户

3. 删除用户

4. 修改用户密码

5. 数据库的权限

5.1 给用户授权

5.2 回收权限

总结

一. 视图

1. 什么是视图？

• 定义：视图是一个虚拟表，其内容由查询定义。
• 结构：同真实的表一样，视图包含一系列带有名称的列和行数据。
• 意义：基于常用的条件查询建立为视图，方便查看与调用，可以理解为一种对常用的显示
• 非持久化：视图的数据不会持久化到磁盘上，而是基于查询结果动态生成。
• 数据同步：视图的数据变化会影响到基表，基表的数据变化也会影响到视图。

2. 基本使用

1. 语法

• 创建视图：

create view 视图名 as select语句;

• 删除视图：

drop view 视图名;

2. 示例

• 创建视图：

create view myview as select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

• 查询视图：

select * from myview;

输出：

数据同步实验

• 修改视图：

update v_ename_dname set ename='TEST' where ename='CLARK';

• 查询基表：

select * from EMP where ename='CLARK';
select * from EMP where ename='TEST';

• 修改基表：

mysql> update EMP set deptno=10 where ename='JAMES'; -- 修改基表

• 查询视图：视图中的数据也发生了变化

mysql> select * from v_ename_dname where ename='JAMES';
+-------+----------+
| ename | dname    |
+-------+----------+
| JAMES | RESEARCH | <== 视图中的数据也发生了变化
+-------+----------+

3. 视图规则和限制

• 命名：与表一样，必须唯一命名（不能出现同名视图或表名）。
• 数量：创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响。
• 索引和触发器：视图不能添加索引，也不能有关联的触发器或者默认值。
• 安全性：视图可以提高安全性，必须具有足够的访问权限。
• 排序：order by 可以用在视图中，但如果从该视图检索数据 select 中也含有 order by，那么该视图中的 order by 将覆盖原 select 中的 order by（就近原则）。
• 使用：视图可以和表一起使用，如：笛卡尔积、内连、外连等。

二. 用户管理

目前为止我们使用mysql都是用的root账号，但是不建议直接使用root进行mysql的操作。

我们推荐使用普通用户对数据的访问。而root作为管理员可以对普通用户对应的权限进行设置和管理。

如给张三和李四这样的普通用户权限设定后。就只能操作给你权限的库了。

1. 用户信息

MySQL 有自己的用户管理机制，用户信息以表结构形式存储。这些信息保存在系统数据库 mysql 的 user 表中。

常用的 ：

• User：用户名
• Host：允许用户从哪台机器登录 MySQL，localhost 表示只允许从本地登录。
• authentication_string：用户的密码
• password_expired：密码是否过期
• password_last_changed：密码上次更改的时间

查看用户信息：

select user, host, authentication_string from user;

• mysql和linux一样可以允许创建多个用户，这些用户都是普通用户，你可以给他们开账号
• 所谓在mysql内部给用户开账号，就是把用户的用户名，允许从哪里登录，以及用户的密码信息放在mysql中的user表里。然后就有了这个用户了。
• 如果我们现在想在mysql新建一个用户，有一个特点简单粗暴的方式，也不用后面学的专门的创建用户的sql语句。
• 可以用insert 向这个user里面插入也是可以的。但是这太麻烦了。
• 其实未来学的创建用户，删除用户，修改用户其实说到底就是在user表里做增加删除修改！

2. 创建用户

创建用户的基本语法：

create user '用户名'@'登陆主机/ip' identified by '密码';

• 用户名：自定义
• 登陆主机/ip：本机登录用 localhost 或 127.0.0.1，远程登录用 % 表示任意主机。
• 密码：设置用户密码，会被哈希加密保存到 user 表中。

❗ 报错处理：

编辑 MySQL 配置文件：

通常配置文件位于 /etc/my.cnf 。打开配置文件并查找 [mysqld] 部分。

sudo vi /etc/my.cnf

移除 --skip-grant-tables 选项：

重启：sudo systemctl restart mysql

示例：

create user 'zhangsan'@'localhost' identified by 'password123';
create user 'lisi'@'%' identified by 'password123';

创建用户后，刷新权限以确保用户信息生效：

flush privileges;

3. 删除用户

删除用户的基本语法：

drop user '用户名'@'主机名';

示例：

drop user 'zhangsan'@'localhost';
drop user 'lisi'@'%';

4. 修改用户密码

• 用户自己修改密码：

set password=password('新的密码');

• root 用户修改指定用户的密码：

set password for '用户名'@'主机名' = password('新的密码');

示例：

set password for 'zhangsan'@'localhost' = password('newpassword123');

ERROR 1064 (42000): You have an error in your SQL syntax 错误是因为在 MySQL 8.0 及以上版本中，PASSWORD() 函数已经被弃用。你需要使用新的语法来设置用户密码

在 MySQL 8.0 及以上版本中，可以使用 ALTER USER 语句来设置用户密码

对于远端链接的一些碎碎念：

把 mysql 暴露到公网上十分的不建议和不安全，博主尝试用两台机器去尝试连接了，有一台是朋友的，可能是哪里权限没打开，危险的事情大概明白思路就好啦，就不尝试了

5. 数据库的权限

当我们在MySQL中把一个用户创建好了，不是就所有工作做好了，而是说把用户创建好了 ，用户还要有他对应的权限也要处理的。

MySQL数据库提供的权限列表：

当前新建的用户只能看到这 两个数据库。

下面给新建用户授权，对新用户做相关限制。

5.1 给用户授权

授权的基本语法：

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码'];

• 权限列表：多个权限用逗号分隔

• • grant select on ...
  • grant select, delete, create on ...
  • grant all [privileges] on ... – 表示赋予该用户在该对象上的所有权限

• 库.对象名：指定数据库和对象（表、视图、存储过程等）

• • *.*：所有数据库的所有对象
  • 库.*：某个数据库中的所有对象

示例：

grant select, insert, update on mydb.* to 'zhangsan'@'localhost';

查看用户权限：

show grants for 'zhangsan'@'localhost';

5.2 回收权限

回收权限的基本语法：

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';

示例：

revoke insert on mydb.* from 'zhangsan'@'localhost';

总结

• 用户管理：MySQL 通过 user 表管理用户信息。
• 创建用户：使用 create user 语句创建用户。
• 删除用户：使用 drop user 语句删除用户。
• 修改密码：使用 set password 语句修改用户密码。
• 权限管理：使用 grant 和 revoke 语句为用户授予权限和回收权限。

通过这些操作，可以有效地管理 MySQL 用户及其权限，确保系统的安全性和数据的完整性~