MySQL——10、视图和用户管理

1、视图

视图：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视
图的数据变化会影响到基表，基表的数据变化也会影响到视图。

创建一个视图：

create view 视图名 as select语句;

比如针对之前的员工表emp和部门表dept，现在我想知道所有员工及其对应的部门，我们可以采用内连接方式：

而如果我们需要频繁的查询这些数据，我们每次都需要进行内连接并设置条件筛选，这样也太麻烦了，因此我们可以将这个返回的表结构创建一个视图：

创建myview视图，就会根据我们select返回的表结构来创建一张临时的表保存数据。
此时对myview表中的数据进行修改，会影响到基表emp和dept。而对基表emp和dept进行修改也会影响到myview视图，如下：

删除视图：

drop view 视图名;

视图的限制和使用规则：

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

---

2、MySQL用户管理

2.1、用户管理

我们前面的所有操作都是直接采用root账户进行的，并且我们配置了免密码登录，从现在开始我们需要设置对应的密码并且最好不要直接使用root账户进行操作。

首先查看数据库，我们可以发现有一个mysql的数据库：

然后我们进入这个数据库，查看这个数据库的所有表，我们发现有一个user表，接下来我们看一些user表存储了哪些数据：

然后我们主要看三个字段：
Host：表示登录的主机，为localhost表示只能本地主机登录，我们在创建用户的时候可以设置对应的主机。
User：表示用户。
authentication_string：这是原始密码经过加密后形成的。
其他字段xxx_priv表示是否具有xxx权限，所以mysql对用户的管理本质上就是对user表的增删查改。

我们将这三个字段单独拿出来查看：

在之前我们root账户是在mysqld.conf中配置了skip-grant-tables，直接跳过权限验证无密码登录的，这时候我们需要先修改root的密码，然后去除掉skip-grant-tables配置，否则后续无法进行用户和权限管理操作：

我们直接使用update将root用户的密码改为空，并且使用flush privileges刷新一下。接下来我们删除配置skip-grant-tables，重启mysql服务，就可以直接无密码登录root账户了，登陆后我们再重新设置root账户密码，这时候就可以设置成功。

此时我们就可以无密码直接登录mysql，然后使用alter user修改密码，修改后刷新一下即可。

2.1.1、创建用户

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

下面使用我们创建的zzy这个账户进行登录看看：

我们发现只有这两个数据库，其他数据库比如：test_db我们是看不到的，虽然我们知道有test_db这个数据库，但是直接访问也是无法访问的，因为没有权限。

2.1.2、删除用户

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

前面我们创建的用户都是在本地，这时候我们可以创建一个所有主机都可以登录的用户，其中host使用%：

虽然我们创建了一个可以用任意主机登录的账户，但是还需要修改mysql的配置文件才能远程登录。

如图：将mysql的bind-address改为0.0.0.0，表示允许所有IP连接，然后重启mysql服务即可远程登录。

如上图，使用windows上的mysql客户端连接云服务器上的mysqld服务端。

2.1.3、修改用户密码

-- 修改当前用户密码
alter user user() identified by '新密码';

-- 修改特定用户密码
alter user '用户名'@'主机名' identified by '新密码';

-- 修改当前用户密码
set password = '新密码';

-- 修改其他用户密码
set password for '用户名'@'主机名' = '新密码';

当然也可以直接update去更新user表，包括创建用户实际上也可以直接insert，但是不推荐这种方式，

---

2.2、数据库权限

数据库提供的权限有：

2.2.1、给用户授权

刚创建的用户没有任何权限，所以我们要授予用户权限。
语法：

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

• 权限列表可以跟多个权限用逗号分隔开。
• *.* : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
• 库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)
• identified by可选。如果用户存在，赋予权限的同时修改密码，如果该用户不存在，就是创建用户。

下面我们使用root账户创建一个数据库和一张表，并给zzy用户赋予select权限。

此时我们使用zzy账户登录就可以看到testDB这个数据库，并且可以进入数据库读取user表的数据。

下面我们使用zzy账户对user表进行更新删除插入操作：

发现无法进行删除更新和插入操作，因为我们目前只有select权限。

查看用户的权限：

show grants for '用户名'@'主机';

我们也可以给zzy用户赋予delete、update、insert权限，当然还可以直接赋予all所有权限。

2.2.2、回收权限

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