MySQL 数据库与表操作常用命令指南

处理数据库

第1步：列出服务器上的可用数据库

目的：了解当前 MySQL 实例中存在的库名，确认目标库是否已存在或获取完整的库列表。

操作：在 mysql> 提示符下执行 SHOW DATABASES; 命令。

mysql> SHOW DATABASES;

完成状态：终端打印出所有当前用户拥有权限的数据库名称列表，包含默认的系统库（如 information_schema）。

第2步：创建新的数据库

目的：为即将存储的业务数据创建一个独立的容器空间。

操作：执行 CREATE DATABASE 语句并指定库名。

mysql> CREATE DATABASE db_test;
Query OK, 1 row affected (0.00 sec)

完成状态：显示 “Query OK”，表示库创建成功，此时该库仍是空的。

第3步：切换并使用指定数据库

目的：将后续的所有表操作上下文设定在特定的数据库中，避免每次操作都需全路径声明。

操作：使用 USE 命令指向目标库名。

mysql> USE db_test;
Database changed

完成状态：界面提示 “Database changed”，说明工作库已成功切换。

第4步：安全地删除数据库（风险操作）

注意：DROP DATABASE 会连同其中的所有表和数据一并永久清除，且不可恢复。操作前务必确认备份到位。

操作：通过客户端执行 DROP DATABASE 指令。

mysql> DROP DATABASE db_test;
Query OK, 0 rows affected (0.00 sec)

完成状态：执行完毕后，再次运行“第一步”的命令将无法找到该库名。

处理数据表

第5步：创建包含多列的基础表

目的：定义业务数据的实体结构，设定各字段的类型及约束。

入口：在已选中的数据库中。

动作：使用 CREATE TABLE 结合主键、自增、非空等参数构建表架构。

mysql> CREATE TABLE tb_test(
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   firstname VARCHAR(25) NOT NULL,
    ->   lastname VARCHAR(25) NOT NULL,
    ->   email VARCHAR(45) NOT NULL,
    ->   phone VARCHAR(10) NOT NULL,
    ->   PRIMARY KEY(id));
Query OK, 0 rows affected (0.03 sec)

进阶写法：若尚未切换至对应数据库，可直接使用 库名.表名 的全限定路径进行操作（如 CREATE TABLE db_test.tb_test(...)）。

第6步：带有容错机制的安全建表

目的：防止脚本因目标表已存在而直接报错中断，常用于自动化部署场景。

动作：在 CREATE TABLE 后加入 IF NOT EXISTS 子句。

mysql> CREATE TABLE IF NOT EXISTS db_test.tb_test(
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   firstname VARCHAR(25) NOT NULL,
    ->   lastname VARCHAR(25) NOT NULL,
    ->   email VARCHAR(45) NOT NULL,
    ->   phone VARCHAR(10) NOT NULL,
    ->   PRIMARY KEY(id));
Query OK, 0 rows affected, 1 warning (0.00 sec)

完成状态：即使表不存在也会正常创建；若已存在则忽略，并输出一个 warning 警告而非错误代码。

第7步：基于查询结果快速复制表

目的：在不手动重复编写字段定义的情况下，迅速克隆一份数据结构作为副本或测试环境。

动作 A（全量复制）：

mysql> CREATE TABLE tb_test2 SELECT * FROM db_test.tb_test;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

动作 B（选取特定列）：

首先查看原表字段结构：DESCRIBE tb_test;

然后按需抽取列进行复制：

mysql> CREATE TABLE tb_test2 SELECT id, firstname, lastname, email FROM tb_test;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

完成状态：新表 tb_test2 被生成。请注意，这种复制方式仅拷贝结构和数据，不会自动复制索引和外键约束，且原表的 AUTO_INCREMENT 属性通常会被重置为默认值。

第8步：建立临时表用于中间计算

目的：处理海量数据时，通过过滤产生少量数据集供后续分析，节省资源。

特性：数据存放在操作系统临时目录；会话断开（连接关闭）时自动删除；支持手动 DROP TEMPORARY TABLE 提前清理。

动作：在建表语句中加入 TEMPORARY 关键字。

mysql> CREATE TEMPORARY TABLE emp_temp SELECT firstname, lastname FROM tb_test;
Query OK, 0 rows affected (0.02 sec)

第9步：查看当前库内的表列表

目的：确认新建的表是否正确入库。

动作：执行 SHOW TABLES; 命令。

mysql> SHOW TABLES;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_test           |
| tb_test2          |
+-------------------+
2 rows in set (0.00 sec)

第10步：审查与核对表结构

目的：验证列名、数据类型、允许为空与否（NULL/NOT NULL）以及默认值和额外属性设置是否符合预期。

动作：可以使用 DESCRIBE 简写或 SHOW COLUMNS。

mysql> DESCRIBE tb_test;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(25)      | NO   |     | NULL    |                |
| lastname  | varchar(25)      | NO   |     | NULL    |                |
| email     | varchar(45)      | NO   |     | NULL    |                |
| phone     | varchar(10)      | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

以上展示的是通过 DESCRIBESHOW COLUMNS IN tb_test;。

第11步：彻底删除不再需要的表

警告：此操作破坏性强，执行前请再次确认业务依赖。

语法：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name...];

动作：例如同时删除两张旧表：

DROP TABLE IF EXISTS old_table_a, old_table_b;

对于之前建立的临时表，也请使用 DROP TEMPORARY TABLE ... 以保持语法规范。

第12步：利用 ALTER 动态调整表结构

目的：在现有业务不断电或不停服的前提下，对表结构进行微调（开发迭代期尤为常用），避免繁琐的“先删后建”流程。

动作 A：新增一列字段

mysql> ALTER TABLE tb_demo ADD COLUMN email VARCHAR(45);

动作 B：调整新列的位置（插入到首位）

如果要将新加的 email 放到第一列，可追加 AFTER 相关参数或使用 FIRST 关键字：

ALTER TABLE tb_demo MODIFY email VARCHAR(45) FIRST;

动作 C：修改已有列的属性（增加非空约束）

假设需要给刚才添加的 email 加上限制：

mysql> ALTER TABLE tb_demo CHANGE email email VARCHAR(45) NOT NULL;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

动作 D：移除某一列

mysql> ALTER TABLE tb_demo DROP email;
Query OK, 0 rows affected (0.09 sec)

注意：CHANGE 和 MODIFY 的区别在于前者可以同时更改列名，后者仅修改类型和约束而不改变列名。