## 1. 查询有哪些用户
~~~php
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
select distinct concat('User:''',user,'''@''',host,''';') as query from mysql.user;
~~~
## 2. 创建用户
#### 语法
~~~php
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
create user 'username'@'host' identified by 'password';
~~~
##### 语法详解
username 用户名
host 允许连接地址 % 允许任何地址连接
password 密码 可以留空
>[info] ### 例子
~~~php
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
create user 'dog'@'localhost' identified by '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
create user 'pig'@'192.168.1.101_' idendified by '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
create user 'pig'@'%' identified by '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
create user 'pig'@'%' identified by '';
CREATE USER 'pig'@'%';
create user 'pig'@'%';
~~~
## 3. 授权
#### 语法
~~~php
GRANT privileges ON databasename.tablename TO 'username'@'host' identified by 'pwd';
grant privileges on databasename.tablename to 'username'@'host' identified by 'pwd';
~~~
##### 语法详解
privileges 授予权限 select、delete、update、inster、drop、all
databasename 数据库名 所有就用 \*.* 表示
tablename 表名 所有就用 * 表示
pwd 用户的密码（有密码就加没密码就忽略）
>[info] ### 例子
~~~php
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
grant select, insert on test.user to 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
grant all on *.* to 'pig'@'%';
GRANT ALL ON test.* TO 'pig'@'%';
grant all on test.* to 'pig'@'%';
~~~
>[danger] 让用户拥有赋予权限
~~~php
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
grant privileges on databasename.tablename to 'username'@'host' with grant option;
~~~
## 4. 设置与更改用户密码
#### 语法
~~~php
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
set password for 'username'@'host' = password('newpassword');
SET PASSWORD = PASSWORD("newpassword"); //当前登录用户
set password = password("newpassword");
~~~
##### 语法详解
for 后面跟用户
newpassword 填写新密码
>[info] ### 例子
~~~php
SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
set password for 'pig'@'%' = password("123456");
~~~
## 5. 取消用户权限
#### 语法
~~~php
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
revoke privilege on databasename.tablename from 'username'@'host';
~~~
##### 语法详解
privileges 授予权限 select、delete、update、inster、drop、all
databasename 数据库名 所有就用 \*.* 表示
tablename 表名 所有就用 * 表示
>[info] ### 例子
~~~php
REVOKE SELECT ON *.* FROM 'pig'@'%';
revoke select on *.* from 'pig'@'%';
~~~
>[danger]
>假如你在给用户'pig'@'%'授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO 'pig'@'%'，则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作。相反，如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。
>具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。
## 6. 删除用户
#### 语法
~~~php
DROP USER 'username'@'host';
drop user 'username'@'host';
~~~
