mysql用户创建、删除与修改

一、创建用户

语法格式：
create user ‘用户名’@‘要创建连接的主机称或者ip地址’ identified by ‘用户密码’;

为mysql创建一个账号，用户名：jack，密码：jack

mysql> create user 'jack'@'localhost' identified by 'jack';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jack          | localhost |  	//创建成功
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

为mysql创建一个账号，指定ip地址为192.168.156.10可以登录，用户名：tom，密码：tom

mysql>  create user 'tom'@'192.168.156.10' identified by 'tom';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| tom           | 192.168.156.10 |		//创建成功
| jack          | localhost      |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
5 rows in set (0.00 sec)

为mysql创建一个账号，指定网段为192.168.156.可以登录，用户名：linux，密码：linux

mysql> create user 'linux'@'192.168.156.%' identified by 'linux';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| linux         | 192.168.156.%  |			//创建成功
| tom           | 192.168.156.10 |
| jack          | localhost      |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
6 rows in set (0.00 sec)

为mysql创建一个账号，允许所有网段登录，用户名：jarry，密码：jarry

mysql> create user 'jarry'@'%' identified by 'jarry';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| jarry         | %              |			//创建成功
| linux         | 192.168.156.%  |
| tom           | 192.168.156.10 |
| jack          | localhost      |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
7 rows in set (0.01 sec)

二、删除用户

1、第一种语法格式：
drop user ‘用户名’@‘主机或者IP地址’;
注：如果在删除用户时没有指定主机名或是IP，那么会删除这个账号的所有信息。

删除tom这个账号

mysql> drop user 'tom'@'192.168.156.10';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| jarry         | %             |
| linux         | 192.168.156.% |
| jack          | localhost     |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
6 rows in set (0.01 sec)

2、第二种语法格式
delete from user where user=‘要删除的用户’ and host=‘该用户对应的地址’;

mysql> delete from user where user='linux' and host='192.168.156.%';
Query OK, 1 row affected (0.00 sec)

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jarry         | %         |
| jack          | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

三、 修改MySQL用户

修改mysql通常有两种：修改用户名、修改被允许登录的主机或ip
语法格式：
rename user 旧用户信息 to 新用户信息;

修改jack为jak，授权主机从localhost改为%
mysql> rename user 'jack'@'localhost' to 'jak'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jak           | %         |
| jarry         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)