MySQL数据库管理语句

本文只介绍基础、常用的 MySQL 语法语句，更详细的语法语句请移步这里。

1 CREATE USER 语法

语法

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

1.1 创建 jerry 用户

CREATE USER jerry IDENTIFIED BY '123456';

2 RENAME USER 语法

语法

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

2.1 重命名 jerry 用户

RENAME USER jerry TO kevin;

3 GRANT和REVOKE 语法

语法
GRANT：

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

REVOKE：

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

3.1 给 kevin 用户增加关于 SC 表的插入权限

GRANT INSERT ON SC TO kevin
    WITH GRANT OPTION;

退出，切换为 kevin 用户登录

发现没有查询 SC 表的权限，但可以插入数据

切换回到 root 用户，删除 kevin 用户的插入权限

再切换到 kevin 用户测试是否还有 INSERT 权限

发现连数据库都进不去，因为没有任何权限。

4 SET PASSWORD 语法

语法(8.0版本)

ALTER USER user IDENTIFIED WITH mysql_native_password BY "your_password";

4.1 root 用户为 kevin 用户修改密码

ALTER USER kevin IDENTIFIED WITH mysql_native_password BY '123478';

5 DROP USER 语法

语法

DROP USER user [, user] ...

5.1 删除 kevin 用户

DROP USER kevin;

参考

1. MySQL8.0正确修改密码的姿势
2. MySQL 5.1中文文档