mysql常用操作汇总

工作中经常用会遇到这种情况，可以访问mysql所在的服务器，但是服务器端口不对外暴露（通常因为安全原因）。这时，操作数据库只能通过命令行和mysql client窗口来实现。我对这些操作进行一些汇总，方便以后随时查阅。
一、登录登出mysql client

登录mysql client比较简单，执行下面的命令回车，输出root用户密码，即可进入。

1. mysql -u root -p

复制代码

登出只需执行 退出命令 exit 或使用快捷键CTRL + C。
二、操作数据

登录至mysql client后，可以操作常见的DDL、DML、DQL和DCL语句。
2.1  数据库（database）相关操作

2.1.1 数据库创建

创建数据库，执行以下语句。

1. CREATE DATABASE your_db_name DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

复制代码

上述语句创建了名为your_db_name的数据库，且设置了该数据库默认的字符集为uft8mb4。这里注意mysql数据库的命名不支持短中杠（-），多个单词的拼接用下划线（_）即可。
2.1.2 数据库删除

删除数据库，只需执行DROP DATABASE命令。

1. DROP DATABASE your_db_name;

复制代码

2.1.3 查询和展示数据库

展示当前用户下，全量的数据库列表。

1. SHOW DATABASES;

复制代码

如果要查询特定的数据，可以使用LIKE 进行过滤查询。

1. SHOW DATABASE LIKE 'your_db%';

复制代码

2.1.4  进入指定的数据库

要进入刚才创建的数据库 your_db_name,执行下面的语句。

1. USE your_db_name;

复制代码

2.2 表格相关操作

2.2.1 创建表

我们尝试创建一个表your_table_name。假如该表不存在，则创建；如果存在，则不执行。

1. CREATE TABLE IF NOT EXISTS `your_table_name` (
2.     `id` BIGINT NOT NULL COMMENT '主键' AUTO_INCREMENT,
3.     `your_field_name1` VARCAHR(10) NOT NULL DEFAULT '' COMMENT '你创建的字段1',
4.     `your_field_name2` CHAR(1) NOT NULL DEFAULT '0' COMMENT '你创建的字段2',
5.     PRIMARY KEY (`id`) USING BTREE
6. )
7. ENGINE = InnoDB
8. COLLATE = 'utf8mb4_unicode_ci'
9. COMMENT = '你的表名'
10. ;

复制代码

2.2.2 查看建表语句

如果想看某张表的建表语句，执行下面的语句：

1. SHOW CREATE TABLE your_table_name;

复制代码

2.2.3 删除表

通过drop table语句删除表。

1. DROP TABLE `your_table_name`;

复制代码

2.2.4 重命名表

如果想快速备份当前的表(your_table_name)，可以使用rename table命令，把当前表重命名为其他表（例如your_table_name_bak）。

1. RENAME TABLE your_table_name TO your_table_name_bak;

复制代码

注意：
重命名的表名最好不要带上.符号，比如 your_table_name.bak。这是因为，sql 解析器有可能误认为.符号前面的your_table_name是数据库名。
它会报下面的错误：
ERROR 1049 (42000): Unknown database 'your_table_name'

2.2.5 快速复制表

已有一张表\(A\)(your_table_name)，如果想快速复制一张表\(A_2\)(your_table_name2)，表\(A_2\)与表\(A\)完全相同，但是不含\(A\)表中的数据。我们可以这么执行：

1. CREATE TABLE your_table_name2 SELECT * FROM your_table_name LIMIT 0;

复制代码

如果只是想快速复制表\(A_3\)(your_table_name3)，\(A_3\)表只包含\(A\)表的部分字段，且同\(A_2\)一样不包含\(A\)表中的数据。可以这么执行：

1. CREATE TABLE your_table_name3 SELECT id FROM your_table_name LIMIT 0;

复制代码

如果想快速复制表\(A_4\)(your_table_name4)，\(A_4\)表包含\(A\)表的所有字段，并且包含\(A\)表的所有数据。可以这么执行：

1. CREATE TABLE your_table_name4 SELECT * FROM your_table_name;

复制代码

如果想快速复制表\(A_5\)(your_table_name5)，\(A_5\)表包含\(A\)表的所有字段，并且包含A表的若干条数据，比如1条。可以这么执行：

1. CREATE TABLE your_table_name5 SELECT * FROM your_table_name LIMIT 1;

复制代码

但是注意，上述执行方式创建的复制表，都只保留原表的字段相关信息，但是会丢弃掉原表的主键、索引等信息，如果要复制一张完全相同表结构的表，可以使用LIKE来执行。

1. CREATE TABLE your_table_same_strut_name LIKE your_table_name;

复制代码

2.2.5 快速同步表数据

假设有两张表，表\(A\)（your_table_name）和表\(B\)（another_table_name），两者字段完全相同，或部分相同，或字段含义接近，表\(A\)有全量数据，把表\(A\)的数据一次性同步到表\(B\)，可以通过下面的步骤实现。
初始条件，

1. # 已知表A （`your_table_name`）
2. CREATE TABLE IF NOT EXISTS `your_table_name` (
3.     `id` BIGINT NOT NULL COMMENT '主键' AUTO_INCREMENT,
4.     `your_field_name1` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '你创建的字段1',
5.     `your_field_name2` CHAR(1) NOT NULL DEFAULT '0' COMMENT '你创建的字段2',
6.     PRIMARY KEY (`id`) USING BTREE
7. )
8. ENGINE = InnoDB
9. COLLATE = 'utf8mb4_unicode_ci'
10. COMMENT = '你的表名'
11. ;
13. # 表B（another_table_name）
14. CREATE TABLE `another_table_name` (
15.     `id` BIGINT(20) NOT NULL COMMENT '主键' AUTO_INCREMENT,
16.     `field_name1` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '字段1' COLLATE 'utf8mb4_unicode_ci',
17.     `field_name2` CHAR(1) NOT NULL DEFAULT '0' COMMENT '字段2' COLLATE 'utf8mb4_unicode_ci',
18.     `field_name3` INT(11) NULL DEFAULT NULL COMMENT '字段3',
19.     PRIMARY KEY (`id`) USING BTREE
20. )
21. COMMENT='另外一张表'
22. COLLATE='utf8mb4_unicode_ci'
23. ENGINE=InnoDB
24. ;

复制代码

又已知表\(A\)（your_table_name）和表\(B\)（another_table_name）前3个字段含义相同。那么同步表\(A\)的数据至表\(B\)，可以按以下方式执行：

1. INSERT INTO `another_table_name` (`id`, `field_name1`, `field_name2`)
2. SELECT `id`, `your_field_name1`, `your_field_name2` FROM `your_table_name`;

复制代码

同样，同步表\(A\)（your_table_name）的部分数据至表\(B\)（another_table_name），比如只同步前2条数据。执行语句如下：

1. INSERT INTO `another_table_name` (`id`, `field_name1`, `field_name2`)
2. SELECT `id`, `your_field_name1`, `your_field_name2` FROM `your_table_name` LIMIT 2;

复制代码

2.2.6 清空表数据

如果表中数据没有用了，表又比较大，那么执行下面的语句可以快速清空并重置当前表。为什么说该操作有重置的作用，假设你的主键是自增的（AUTO INCREMENT），全量删除（DELETE *）数据后，再添加数据，主键不会从1开始递增，而是接继之前的主键值继续递增；但是执行TRUNCATE命令后，主键值会重新从1开始递增。

1. TRUNCATE TABLE `your_table_name`;

复制代码

注意：
生产环境慎用。

2.2.7 列出库中的表

1. SHOW TABLES;

复制代码

如果要列出特定的表，则可以用 LIKE进行过滤查询。

1. SHOW TABLES LIKE '%your_table_name%';

复制代码

2.2.8 查询表状态

1. SHOW TABLE STATUS WHERE `NAME`= 'your_table_name';

复制代码

这是可以查询到表当前的相关信息，比如表名、表引擎、版本、数据行格式、数据行数、表创建时间、表更新时间、字符集、表注释等等。
2.3 字段相关操作

2.3.1 添加字段

除了创建表时添加字段外，还可以通过ALTER ADD COLUMN语句添加字段。

1. -- 添加一个字段
2. ALTER TABLE `your_table_name` ADD COLUMN `your_field_name3` DECIMAL(8,2) NOT NULL DEFAULT 0 COMMENT '字段3' AFTER `your_field_name2`;
4. -- 添加多个字段 以半角逗号分割
5. ALTER TABLE `your_table_name`
6.     ADD COLUMN `your_field_name4` BIT NOT NULL DEFAULT FALSE COMMENT '字段4' AFTER `your_field_name3`,
7.     ADD COLUMN `your_field_name5` TIMESTAMP NULL COMMENT '字段5' AFTER `your_field_name4`;

复制代码

如果不指定字段在特定字段后面，则该字段默认在表的末尾处添加。
2.3.2 修改字段

变更表字段，使用 ALTER CHANGE COLUMN语句。

1. -- 变更一个字段
2. ALTER TABLE `your_table_name` CHANGE COLUMN `your_field_name3` `your_field_new_name3` DECIMAL(8,3) NOT NULL DEFAULT 0 COMMENT '新字段3';
4. -- 变更多个字段 以半角逗号分割
5. ALTER TABLE `your_table_name`
6.     CHANGE COLUMN `your_field_name4` `your_field_new_name4` TINYINT NOT NULL DEFAULT 0 COMMENT '新字段4',
7.     CHANGE COLUMN `your_field_name5` `your_field_new_name5` DATETIME NULL COMMENT '新字段5';

复制代码

2.3.3 删除字段

删除字段，使用 ALTER DROP COLUMN语句。

1. -- 删除一个字段
2. ALTER TABLE `your_table_name` DROP COLUMN `your_field_new_name3`;
4. -- 删除多个字段 以半角逗号分割
5. ALTER TABLE `your_table_name`
6.     DROP COLUMN `your_field_new_name4`,
7.     DROP COLUMN `your_field_new_name5`;

复制代码

2.3.4 列出表中字段信息

有时要查看表中有哪些字段，可以通过SHOW COLUMNS语句实现。

1. SHOW COLUMNS FROM `your_table_name`;

复制代码

查询后的效果如下所示。我们可以得到字段名、字段类型、是否为Null，Key值，默认值和额外信息。

1. mysql> SHOW COLUMNS FROM `your_table_name`;
2. +------------------+-------------+------+-----+---------+----------------+
3. | Field            | Type        | Null | Key | Default | Extra          |
4. +------------------+-------------+------+-----+---------+----------------+
5. | id               | bigint(20)  | NO   | PRI | NULL    | auto_increment |
6. | your_field_name1 | varchar(10) | NO   |     |         |                |
7. | your_field_name2 | char(1)     | NO   |     | 0       |                |
8. +------------------+-------------+------+-----+---------+----------------+
9. 3 rows in set (0.00 sec)

复制代码

上述语句同下面的语句等价。

1. SHOW FIELDS FROM `your_table_name`;

复制代码

如果要展示字段的更多信息，可以使用下面的语句。相较于上面的语句，增加了字符集信息，权限信息和注释信息。

1. SHOW FULL FIELDS FROM `your_table_name`;

复制代码

2.4 索引相关操作

2.4.1 添加索引

使用ADD INDEX 执行添加索引操作：

1. ALTER TABLE `your_table_name` ADD INDEX `your_index_name` (`your_field_name1`);

复制代码

还可以添加联合索引：

1. ALTER TABLE `your_table_name` ADD INDEX `your_composite_index_name` (`your_field_name1`, `your_field_name2`);

复制代码

如果要添加唯一索引，则可以增加UNIQUE（唯一性）关键字执行：

1. ALTER TABLE `your_table_name` ADD UNIQUE INDEX `your_index_name` (`your_field_name1`);

复制代码

如果要添加主键，那么可以执行：

1. ALTER TABLE `your_table_name` ADD PRIMARY KEY (`id`);

复制代码

2.4.2 删除索引

使用DROP INDEX 执行索引的丢弃操作。

1. ALTER TABLE `your_table_name` DROP INDEX `your_index_name`;

复制代码

删除主键，执行：

1. ALTER TABLE `your_table_name` DROP PRIMARY KEY;

复制代码

2.4.3 查看已有索引

SHOW INDEX FROM命令可以用来查看某张表下所有索引信息。

1. SHOW INDEX FROM `your_table_name`;

复制代码

三、备份和还原数据

备份和还原数据，通常使用mysqldump命令和source命令（在mysql client内执行）。
3.1 备份操作

备份操作特别适合于数据的全量导出的情况，特别是数据存在二进制（binary或blob）的情况。
3.1.1 备份单库和库内数据

linux环境下，可以执行：

1. mysqldump -u root -p your_db_name > /home/your_db_name.sql

复制代码

windows环境下，需要指定具体盘符：

1. mysqldump -u root -p your_db_name > E:/your_db_name.sql

复制代码

3.1.2 备份单库库表结构

查看mysqldump操作帮助（mysqldump --help），可知，添加-d操作符，可以导出不包含数据的库表信息。

1. -d, --no-data       No row information.

复制代码

于是，备份单库但不要库内数据的执行语句如下：

1. mysqldump -u root -p  -d your_db_name > /home/your_db_name_without_data.sql

复制代码

3.1.3 备份多库及库内数据

使用-B操作符，可以指定多个库。

1. mysqldump -u root -p -B your_db_name your_db_name_bak > /home/your_db_names.sql

复制代码

3.1.4 备份单库内若干表结构及表内数据

使用--tables操作符，可以指定多个表。

1. mysqldump -u root -p your_db_name --tables your_table_name your_table_name2 > /home/your_table_names.sql

复制代码

3.1.5 备份单库内若干表结构

1. mysqldump -u root -p -d your_db_name --tables your_table_name your_table_name2 > /home/your_table_names_without_data.sql

复制代码

3.1.6 备份单库并忽略特定的表

使用--ignore-table=name操作符，可以在导出库的同时，忽略特定的表。

1. mysqldump -u root -p your_db_name --ignore-table=your_db_name.another_table_name --ignore-table=your_db_name.your_table_name_bak > /home/your_db_name_ignore_tables.sql

复制代码

注意：
要忽略的表名一定要书写正确。否则会出现执行了明明忽略操作，但是对应的表仍会导出的错觉。

3.2 还原操作

3.2.1 使用SOURCE还原数据

该操作需要在mysql client中执行。
假设要还原某个库里面的数据，可以通过下面简单的两步实现还原库中数据的操作。
首先，切换到要还原的库。

1. USE your_db_name;

复制代码

接着，在库内执行SOURCE命令。SOURCE后跟的是导出的或者你已准备好的数据的实际路径。

1. SOURCE /home/your_db_name.sql

复制代码

3.2.2 使用mysqldump还原数据

mysqldump同样支持把备份的数据还原回去，只需执行的时候，把指向箭头掉个方向。

1. mysqldump -u root -p your_db_name < /home/your_db_name.sql

复制代码

注意：
通常不推荐使用这种方式，因为这种方式会机械的执行库表的还原。如果库名不对应，执行的预期不是我们想要的。

四、授权和收回

4.1 授权

使用GRANT TO命令进行库表的授权。
我们先尝试创建一个本地用户custom_user, 并为其设置密码customPassword。

1. CREATE USER 'custom_user'@'localhost' IDENTIFIED BY 'customPassword';

复制代码

4.1.1 授权库

授权本地用户custom_user拥有your_db_name的所有权限。

1. GRANT ALL PRIVILEGES ON your_db_name.* TO 'custom_user'@'localhost';
2. FLUSH PRIVILEGES;

复制代码

4.1.2 授权特定表

授权本地用户custom_user拥有your_db_name的特定表(your_table_name、your_table_name2)的所有权限。

1. GRANT ALL PRIVILEGES ON your_db_name.your_table_name TO 'custom_user'@'localhost';
2. GRANT ALL PRIVILEGES ON your_db_name.your_table_name2 TO 'custom_user'@'localhost';
3. FLUSH PRIVILEGES;

复制代码

4.1.3 授权只读查询

可以授权本地用户custom_user只拥有your_db_name的特定表(your_table_name、your_table_name2)的查询权限。

1. GRANT SELECT ON your_db_name.your_table_name TO 'custom_user'@'localhost';
2. GRANT SELECT ON your_db_name.your_table_name2 TO 'custom_user'@'localhost';
3. FLUSH PRIVILEGES;

复制代码

注意：
这里授权查询操作需保证，对应库表存在。

4.2 权限收回

收回权限使用REVOKE FROM命令。
4.2.1 收回所有权限

收回所有之前赋给本地用户custom_user对于特定库your_db_name的权限。

1. REVOKE ALL PRIVILEGES ON your_db_name.* FROM 'custom_user'@'localhost';
2. FLUSH PRIVILEGES;

复制代码

注意：
授权是可以累计的。因此，权限收回要与授权保持一一对应。否则，会出现权限收回了，但仍能看到被授权相关信息的错觉。

4.2.2 收回只读查询权限

1. REVOKE SELECT ON your_db_name.your_table_name FROM 'custom_user'@'localhost';
2. REVOKE SELECT ON your_db_name.your_table_name2 FROM 'custom_user'@'localhost';
3. FLUSH PRIVILEGES;

复制代码

五、其他

5.1 查看数据磁盘空间占用

查看磁盘空间占用的目的，是方便在数据导出等操作时做出合理选择，比如，避免导出不重要但很大的数据。
5.1.1 查看数据库数据磁盘空间占用

1. SELECT
2. table_schema as '数据库',
3. SUM(table_rows) as '记录数',
4. SUM(TRUNCATE(data_length/1024/1024, 2)) as '数据容量(MB)',
5. SUM(TRUNCATE(index_length/1024/1024, 2)) as '索引容量(MB)'
6. FROM information_schema.tables
7. GROUP BY table_schema
8. ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

复制代码

5.1.2 查看库中各表数据磁盘空间占用

1. SELECT
2. table_schema as '数据库',
3. table_name as '表名',
4. table_rows as '记录数',
5. TRUNCATE(data_length/1024/1024, 2) as '数据容量(MB)',
6. TRUNCATE(index_length/1024/1024, 2) as '索引容量(MB)'
7. FROM information_schema.tables
8. WHERE table_schema='your_db_name'
9. ORDER BY data_length DESC, index_length DESC;

复制代码

5.2 释放磁盘空间

通常在执行一些大表数据的删除之后，数据库并不会立即释放磁盘空间。这时，执行OPTIMIZE命令可以用来释放相应的磁盘空间。

1. OPTIMIZE TABLE your_table_name;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！