## 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';
~~~
