MySQL 冷门重要 SQL 速查：建库建表、备份还原与用户权限命令汇总

MySQL 数据库 已经是目前最流行的数据库了。社区版开源、免费、跨平台，安装配置容易，功能也很丰富，所以被绝大多数企业选作首选数据库。对 .NET 开发人员来说，进入 .NET Core 技术时代后，大部分人也从 SQL Server 转向了 MySQL。主要原因还是 SQL Server 的跨平台起步太晚，在 Linux 平台下使用的公司和开发者太少。尤其在微服务盛行的今天，它的成熟方案相对较少，再加上不是开源免费，市场份额逐渐萎缩。

这篇文章整理的不是 MySQL 增删改查的基础 SQL，而是那些平时很需要、但一时想不起来，网上又不太好查的语句。主要目的也是方便日后快速查阅，节省时间。

一、操作数据库

-- 查询 MySQL 当前有哪些数据库
SHOW DATABASES;

-- 查看数据库的字符集，格式如下：
-- SHOW CREATE DATABASE 数据库名称;
SHOW CREATE DATABASE testdb;

-- 创建数据库，格式如下：
-- CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名称
CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8;

-- 修改数据库字符集，格式如下：
-- ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
ALTER DATABASE testdb CHARACTER SET gbk;

-- 使用某个数据库，后续 SQL 默认运行在该库上
USE testdb;

-- 查看当前正在使用哪个数据库
SELECT DATABASE();

-- 删除数据库，格式如下：
-- DROP DATABASE IF EXISTS 数据库名称;
DROP DATABASE IF EXISTS testdb;

二、操作数据表

-- 以我自己的测试库为例
USE testdb;

-- 查看 testdb 数据库中有哪些表
SHOW TABLES;

-- 查看某张表的表结构
-- 格式：DESC 表名;
DESC users;

-- 查看某张表的字符集
-- 格式：SHOW TABLE STATUS FROM 库名 LIKE '表名';
SHOW TABLE STATUS FROM testdb LIKE 'users';

-- 修改某张表的字符集
-- 格式：ALTER TABLE 表名 CHARACTER SET 字符集名称;
ALTER TABLE users CHARACTER SET gbk;

-- 参照现有表，创建一张结构相同的新表（只复制结构，不复制数据）
-- 格式：CREATE TABLE 表名 LIKE 被参照的表名;
CREATE TABLE my_user LIKE users;

-- 修改表名
-- 格式：ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE my_user RENAME TO custom_user;

-- 为表添加一列
-- 格式：ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE users ADD gender VARCHAR(10);

-- 修改某列的数据类型
-- 格式：ALTER TABLE 表名 MODIFY 列名 新数据类型;
ALTER TABLE users MODIFY gender INT;

-- 同时修改列名和数据类型
-- 格式：ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
ALTER TABLE users CHANGE gender company VARCHAR(100);

-- 删除表中的某一列
-- 格式：ALTER TABLE 表名 DROP 列名;
ALTER TABLE users DROP company;

-- 删除某张表
-- 格式：DROP TABLE IF EXISTS 表名;
DROP TABLE IF EXISTS custom_user;

三、命令行备份还原

大多数情况下，我们习惯用图形化工具备份和还原 MySQL 数据库。但如果想通过程序定期备份某个库，就可以考虑用命令行来完成。

这里要注意：命令行备份并不是登录到 MySQL 里执行备份语句，而是直接运行 MySQL bin 目录下的 mysqldump 可执行文件。Windows 上需要先远程连到 MySQL 安装的服务器；Linux 则通过 Xshell 等工具连接到服务器。

# 备份语句格式：mysqldump -u root -p 数据库名称 > 文件保存路径

# Windows 下的备份示例：
mysqldump -u root -p testdb > d:\testdb20220131.sql

# Linux 下的备份示例：
mysqldump -u root -p testdb > /databak/testdb20220131.sql

还原数据库的操作则需要登录到 MySQL 中执行。一般建议先新建一个数据库，把备份先还原到这个新库里，然后修改程序的数据库连接字符串完成切换。

MySQL 官方没有提供修改数据库名称的命令。如果一定要让还原后的库名和原库名一致，只能先删除原库，再新建同名数据库，然后把备份还原进去。这种操作相对危险一些。

在 Windows 上，同样要远程连接到 MySQL 服务器，通过 cmd 进入 MySQL 的 bin 目录；Linux 下用 Xshell 等工具连接。

# 登录 MySQL，{} 中内容可省略，默认 3306 端口、localhost
mysql -u root -p

-- 输入 root 账号密码后进入 MySQL
-- 新建一个数据库
CREATE DATABASE IF NOT EXISTS testdb2 CHARACTER SET utf8;

-- 使用新数据库
USE testdb2;

-- Windows 下的还原示例：
SOURCE d:\testdb20220131.sql

-- Linux 下的还原示例：
SOURCE /databak/testdb20220131.sql

四、操作用户

MySQL 中操作用户的绝大多数命令，都需要使用 root 账号来执行，其他账号没有权限。

-- 查看当前登录用户
SELECT USER();

-- 修改当前登录用户的密码（登录 MySQL 后执行）
SET PASSWORD = '123456';

-- 创建新用户：只有指定 IP 192.168.1.44 才能访问
CREATE USER 'wolfer'@'192.168.1.44' IDENTIFIED BY '123456';

-- 创建新用户：允许 192.168.1.* 整个网段访问
CREATE USER 'monkey'@'192.168.1.%' IDENTIFIED BY '123456';

-- 创建新用户：允许任意 IP 访问
CREATE USER 'alpha'@'%' IDENTIFIED BY '123456';

-- 删除用户
DROP USER 'wolfer'@'192.168.1.44';

-- 修改用户名
RENAME USER 'alpha'@'%' TO 'jobs'@'%';

-- 修改某个用户的密码
SET PASSWORD FOR 'monkey'@'192.168.1.%' = 'fastorder';

-- 查看权限
SHOW GRANTS FOR 'jobs'@'%';

-- 授权 jobs 用户仅对 testdb.user 表有查询、插入和更新权限
GRANT SELECT, INSERT, UPDATE ON testdb.user TO 'jobs'@'%';

-- 授权 jobs 用户对 testdb 下所有表任意操作
GRANT ALL PRIVILEGES ON testdb.* TO 'jobs'@'%';

-- 授权 jobs 用户对所有数据库的所有表任意操作
GRANT ALL PRIVILEGES ON *.* TO 'jobs'@'%';

-- 取消 jobs 用户对 testdb.user 表的操作权限
REVOKE ALL ON testdb.user FROM 'jobs'@'%';

-- 取消 jobs 用户对 testdb 所有表的权限
REVOKE ALL ON testdb.* FROM 'jobs'@'%';

-- 取消 jobs 用户对所有数据库所有表的权限
REVOKE ALL PRIVILEGES ON *.* FROM 'jobs'@'%';

-- 注意：以上授权和取消授权操作，在被操作账号不重新登录的情况下，
-- 需要执行下面的语句才能生效
FLUSH PRIVILEGES;

上面这些 SQL 虽然不算多，但都是容易被忽略又挺实用的命令，希望能帮到你。