过一遍mysql基础（2）（第三篇：mysql管理员常用的一些命令；第四篇：DDL常见操作汇总）

本文链接：https://blog.csdn.net/weixin_51343138/article/details/125311933

create user ⽤户名[@主机名] [identi>ied by '密码'];

⽅式三：通过修改mysql.user表修改密码

第三篇：mysql管理员常用的一些命令

在玩 mysql 的过程中，经常遇到有很多朋友在云上⾯玩 mysql 的时候，说我创建了⼀个⽤

户为什么不能登录？为什么没有权限？等等各种问题，本⽂看完之后，这些都不是问题

了。

mysql权限工作原理：

mysql是如何来识别⼀个⽤户的呢？

mysql 为了安全性考虑，采⽤ 主机名+⽤户名 来判断⼀个⽤户的⾝份，因为在互联⽹中很难

通过⽤户名来判断⼀个⽤户的⾝份，但是我们可以通过 ip 或者主机名判断⼀台机器，某个

⽤户通过这个机器过来的，我们可以识别为⼀个⽤户，所以 mysql 中采⽤⽤户名 + 主机名

来识别⽤户的⾝份。当⼀个⽤户对 mysql 发送指令的时候， mysql 就是通过⽤户名和来源

（主机）来断定⽤户的权限。

Mysql权限验证分为2个阶段：

1. 阶段 1 ：连接数据库，此时 mysql 会根据你的⽤户名及你的来源（ ip 或者主机名称）判

断是否有权限连接

2. 阶段 2 ：对 mysql 服务器发起请求操作，如 create table 、 select 、 delete 、 update 、

create index 等操作，此时 mysql 会判断你是否有权限操作这些指令

权限生效时间

⽤户及权限信息放在库名为 mysql 的库中， mysql 启动时，这些内容被读进内存并且从此

时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息的，需要重启 mysql 或者执

⾏ flush privileges（刷新权限表） ; 才可以⽣效。

⽤户登录之后， mysql 会和当前⽤户之间创建⼀个连接，此时⽤户相关的权限信息都保存

在这个连接中，存放在内存中，此时如果有其他地⽅修改了当前⽤户的权限，这些变更的

权限会在下⼀次登录时才会⽣效。

查看mysql中所有用户

⽤户信息在mysql.user表中，如下：

创建用户：

语法：create user 用户名【@主机名】【identified by ‘密码’'】；

说明：1.主机名默认为%，表示这个用户可以从任何主机连接mysql服务器

2.密码可以省略，表示无密码登录

示例1：不指定主机名

不指定主机名时，表示这个用户可以从任何主机连接mysql服务器

其他⽰例

create user 'test2 '@' localhost' identified by ' 123 ';

说明： test2 的主机为 localhost 表⽰本机，此⽤户只能登陆本机的 mysql

create user 'test3'@% identified by ' 123 ';

说明： test3 可以从任何机器连接到 mysql 服务器

create user 'test4 '@' 192.168.11. %' identified by ' 123 ';

说明： test4 可以从 192.168.11 段的机器连接 mysql

修改密码（3种方式）

方式一：通过管理员修改密码

SET PASSWORD FOR '用户名'@‘主机名’ = PASSWORS（‘密码’）

⽅式二：

create user ⽤户名[@主机名] [identi>ied by '密码'];

set password = password（‘密码’）；

⽅式三：通过修改mysql.user表修改密码

use mysql;

update user set authentication_string = password('321') where user =

'test1' and host = '%';

flush privileges;

注意：

通过表的⽅式修改之后，需要执⾏ flush privileges; 才能对⽤户⽣效

给用户授权

创建⽤户之后，需要给⽤户授权，才有意义。

语法：

grant privileges ON database.table TO 'username'[@'host'] [with grant

option]

grant 命令说明：

•

priveleges (权限列表)，可以是all，表⽰所有权限，也可以是select、update等权

限，多个权限之间⽤逗号分开。

•

ON ⽤来指定权限针对哪些库和表，格式为数据库.表名，点号前⾯⽤来指定数据库

名，点号后⾯⽤来指定表名，*.* 表⽰所有数据库所有表。

•

TO 表⽰将权限赋予某个⽤户, 格式为username@host，@前⾯为⽤户名，@后⾯接限

制的主机，可以是IP、IP段、域名以及%，%表⽰任何地⽅。

•

WITH GRANT OPTION 这个选项表⽰该⽤户可以将⾃⼰拥有的权限授权给别⼈。注

意：经常有⼈在创建操作⽤户的时候不指定 WITH GRANT OPTION 选项导致后来该⽤

户不能使⽤ GRANT 命令创建⽤户或者给其它⽤户授权。备注：可以使⽤ GRANT 重复

给⽤户添加权限，权限叠加，⽐如你先给⽤户添加⼀个 select 权限，然后又给⽤户添

加⼀个 insert 权限，那么该⽤户就同时拥有了 select 和 insert 权限

示例：

grant all on *.* to 'test1'@‘%’;

说明：给 test1 授权可以操作所有库所有权限，相当于 dba

grant select on seata.* to 'test1 '@' %';

说明： test1 可以对 seata 库中所有的表执⾏ select

grant select,update on seata.* to 'test1 '@' %'; 说明： test1 可以对 seata 库中所有的表执⾏ select 、 update

grant select (user,host) on mysql. user to 'test1 '@' localhost';

说明： test1 ⽤户只能查询 mysql.user 表的 user,host 字段

查看用户有哪些权限

show grant for '用户'[@'主机名'];

主机名可省略，默认值为%，示例：

show grangs;

查看当前用户的权限

撤销用户的权限

语法：

revoke privileges ON database.table from '用户名'[@'主机名']；

可以先通过 show grants 命令查询⼀下⽤户对于的权限，然后使⽤ revoke 命令撤销⽤户

对应的权限，示例：

删除用户【两种方式】

方式1：

drop user ‘用户名’[@'主机名']，示例：

方式2：通过删除mysql.user表数据的⽅式删除，如下：

delete from user where user='⽤户名' and host='主机';

flush privileges;

注意通过表的⽅式删除的，需要调⽤ flush privileges; 刷新权限信息（权限启动的时

候在内存中保存着，通过表的⽅式修改之后需要刷新⼀下）。

授权原则说明：

只授予能满⾜需要的最⼩权限，防⽌⽤户⼲坏事，⽐如⽤户只是需要查询，那就只给

select 权限就可以了，不要给⽤户赋予 update 、 insert 或者 delete 权限

•

创建⽤户的时候限制⽤户的登录主机，⼀般是限制成指定 IP 或者内⽹ IP 段

•

初始化数据库的时候删除没有密码的⽤户，安装完数据库的时候会⾃动创建⼀些⽤

户，这些⽤户默认没有密码

•

为每个⽤户设置满⾜密码复杂度的密码

•

定期清理不需要的⽤户，回收权限或者删除⽤户

总结

1. 通过命令的⽅式操作⽤户和权限不需要刷新，下次登录⾃动⽣效

2. 通过操作 mysql 库中表的⽅式修改、⽤户信息，需要调⽤ flush privileges; 刷新⼀

下，下次登录⾃动⽣效

mysql 识别⽤户⾝份的⽅式是：⽤户名 + 主机

4. 本⽂中讲到的⼀些指令中带主机的，主机都可以省略，默认值为 % ，表⽰所有机器

mysql 中⽤户和权限的信息在库名为 mysql 的库中

第四篇：DDL常见操作汇总

DDL：Data Define Language数据定义语⾔，主要⽤来对数据库、表进⾏⼀些管理操作。

如： 建库、删库、建表、修改表、删除表、对列的增删改 等等。

⽂中涉及到的语法⽤ [] 包含的内容属于可选项，下⾯做详细说明。

库管理

创建库

create database 【if not exists】库名；

删除库

drop database 【if exists】库名；

建库的统一写法

drop database 【if exists】库名；

create database 新库名；

示例：

表管理

创建表

create table 表名（

字段名1 类型1【（宽度）】【约束条件】【comment ‘字段说明’】，

字段名2 类型2【（宽度）】【约束条件】【comment ‘字段说明’】，

字段名3 类型3【（宽度）】【约束条件】【comment ‘字段说明’】，

）【表的一些设置】；

注意：

1.在同一张表中，字段名不能相同

2.宽度和约束条件为可选参数，字段名和类型是必须的

3.最后一个字段后不能加逗号

4.类型是用来限制字段必须以何种数据类型来存储记录

5.类型其实也是对字段的约束（约束字段下的记录必须为XX类型）

6.类型后写的约束条件是在类型以外的额外添加约束

约束说明

not null ：标识该字段不能为空

default value：为该字段设置默认值，默认值为value

primary key：标识该字段为该表的主键，可以唯一的标识记录，插入重复的值会报错

两种写法：如下：

方式1：跟在列后，如下：

方式2：在所有列定义之后定义，如下：

⽅式 2 ⽀持多字段作为主键，多个之间⽤逗号隔开，语法： primary key( 字段 1, 字段 2, 字段

n)，⽰例：

foreign key：为表中的字段设置外键

语法：foreign key（当前表的列名）reference 引用的外键表（外键表中字段名称）

说明：表⽰ test6 中 ts5_a 字段的值来源于表 test5 中的字段 a 。

注意⼏点：

•

两张表中需要建⽴外键关系的字段类型需要⼀致

•

要设置外键的字段不能为主键

•

被引⽤的字段需要为主键

•

被插⼊的值在外键表必须存在，如上⾯向 test6 中插⼊ ts5_a 为 2 的时候报错了，原

因： 2 的值在 test5 表中不存在

unique key（uq）：标识该字段的值是唯一的

支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败。

定义方式有2种：

方式1：跟在字段后面，如下：

方式2: 支持多字段，多个之间用逗号隔开，语法：primary key（字段1，字段2，字段n）

示例：

auto_increment：标识该字段的值自动增长（整型类型，而且为主键）

字段a为⾃动增长，默认值从1开始，每次+1

关于⾃动增长字段的初始值、步长可以在 mysql 中进⾏设置，⽐如设置初始值为

1 万，每次增长 10

注意：⾃增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中⾃增列的最⼤值作

为当前值，如果表数据被清空之后，数据库重启了，⾃增列的值将从初始值开始

我们演示一下：

上⾯删除了 test11 数据，然后插⼊了⼀条， a 的值为 3 ，执⾏下⾯操作：

删除 test11 数据，重启 mysql ，插⼊数据，然后看 a 的值是不是被初始化了？如

下：

删除表

drop table [if exists] 表名；

修改表名

alter table 表名 rename 【to】新表名；

表设置备注

alter table 表名 comm ‘备注信息’；

复制表

只复制表结构

create table 表名 like 被复制的表名；

如：

复制表结构+数据

create table 表名【as】select 字段，.......from 被复制的表【where 条件】；

如:

表中列的管理

添加列

alter table 表名 add column 列名类型【列约束】；

示例：

修改列：

alter table 表名 modify column 列名新类型【约束】；

或者

alter table 表名 change column 列名新列名新类型【约束】；

2种方式区别：modify 不能修改列名，change 可以修改列名

删除列

alter table 表名 drop column 列名；

示例：