MySQL常用命令详解：从库表管理到数据导入导出

连接与管理服务器

第1步：登录MySQL客户端

目的：验证网络连接并进入交互式SQL环境。
入口：本地或远程服务器的终端。
动作：执行 mysql 命令，指定主机(-h)、用户(-u)并以密码模式启动(-p)。
注意：连接远程数据库时必须使用 -h hostname 参数指定目标IP或域名。

# [mysql dir]/bin/mysql -h hostname -u root -p

第2步：刷新系统权限缓存

目的：使新增的用户或权限配置立即生效。
入口：mysql> 提示符。
动作：运行 flush privileges;。

mysql> flush privileges;

数据库(Databases)管理

第1步：查看与切换数据库

目的：确认当前可用资源并选定工作区。
入口：mysql> 提示符。
参数/动作：
- 列出所有库：运行 show databases;
- 切换库：运行 use [db name];

mysql> show databases;
mysql> use [db name];

第2步：创建与删除数据库

目的：构建新的数据存储容器或清理废弃项目。
风险：删除操作不可逆，请谨慎执行。
动作：
- 新建：运行 create database [databasename];
- 删除：运行 drop database [database name];

mysql> create database [databasename];
mysql> drop database [database name];

表(Table)结构与数据维护

第1步：表的基本查询

目的：了解数据库架构和数据现状。
入口：mysql> 提示符。
动作：
- 查看当前库的所有表：运行 show tables;
- 查看表字段格式：运行 describe [table name];
- 获取各列详细信息：运行 show columns from [table name];

mysql> show tables;
mysql> describe [table name];
mysql> show columns from [table name];

第2步：创建新表结构

目的：为特定业务逻辑建立存储实体。
入口：mysql> 提示符。
动作：定义表名及各字段的类型、长度、主键等属性。

基本建表示例：

mysql> CREATE TABLE [table name] (
    firstname VARCHAR(20),
    lastname VARCHAR(35),
    age INT,
    PRIMARY KEY(id)
);

进阶建表示例（含自增主键）：

mysql> create table user_info (
    personid int(50) not null auto_increment primary key,
    username varchar(35),
    role varchar(50)
);

第3步：修改表结构 (Alter)

目的：适应数据结构变更需求。
入口：mysql> 提示符。
重要参数与动作：
- 增加一列： alter table [tname] add column [cname] varchar(50);
- 重命名一列： alter table [tname] change [old_col] [new_col] varchar(50);
- 修改列类型： alter table [tname] modify [colname] VARCHAR(100);
- 删除一列： alter table [tname] drop column [colname];
- 增加唯一索引： alter table [tname] add unique ([column name]);
- 删除唯一索引： alter table [tname] drop index [idx_name];
- 重命名表： alter table t1 rename t2;

第4步：数据的插入、更新与删除

目的：进行基础数据维护。
动作示例：
- 更新记录： UPDATE users SET status = 'Y' WHERE username = 'admin';
- 删除记录： DELETE FROM users WHERE username = 'expired_user';

数据检索与高级查询

第1步：基础与条件查询

目的：获取满足条件的数据子集。
入口：mysql> 提示符。
关键语法：
- 全表扫描： SELECT * FROM [table name];
- 精确过滤： SELECT * FROM [table name] WHERE [field] = "value";
- 组合过滤： SELECT * FROM [table name] WHERE id = 1 AND name = 'Bob';
- 模糊匹配： SELECT * FROM [table name] WHERE name LIKE 'Bob%';
- 范围限制（分页）：在末尾添加 LIMIT 0, 5;（显示前5条）
- 正则查找： SELECT * FROM [table name] WHERE email RLIKE '^a@';

第2步：数据排序与统计

目的：对结果集进行分析处理。
动作：
- 排序：在SQL末尾加 ORDER BY [col] DESC;（降序）。
- 去重：运行 SELECT DISTINCT [column] FROM [table];
- 计数：运行 SELECT COUNT(*) FROM [table];

第3步：多表联合查询 (Join)

目的：关联不同表的数据。
入口：mysql> 提示符。
说明：使用 LEFT JOIN 可保留左表的所有记录。

select lookup.illustration_id, lookup.person_id, person.birthday 
from lookup 
left join person on lookup.person_id = person.person_id;

用户管理与权限分配

第1步：创建新用户

目的：实现最小权限原则的应用。
入口：已以root身份登录的 mysql> 提示符。
步骤：
1. 切换到系统库： use mysql;
2. 插入用户信息： INSERT INTO user (Host,User,Password) VALUES('localhost','username',PASSWORD('password'));
3. 刷新权限生效。

第2步：授权特定数据库访问权

目的：允许外部程序或用户操作指定数据库。
动作：授予 databasename 下的所有特权给 user@localhost。

mysql> GRANT ALL PRIVILEGES ON databasename.* TO username@localhost IDENTIFIED BY 'passwd';
mysql> FLUSH PRIVILEGES;

第3步：重置Root密码（紧急场景）

目的：找回遗失的管理员密码。
风险提示：此过程需停服并跳过权限验证表(--skip-grant-tables)，操作期间严禁其他用户访问数据库，完成后必须重启服务并重新加载权限表。

# 停止MySQL服务
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root

# 登录后的操作
mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD('new_password') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> QUIT;

数据备份与恢复（Import/Export）

第1步：备份数据库

目的：生成物理或逻辑快照用于灾难恢复。
工具：使用命令行工具 mysqldump。
常用参数说明： - -u, -p: 指定用户名和密码。 - --opt: 默认开启多项优化选项（如锁定表、事务安全等）。 - --all-databases 或 --databases: 指定导出的库范围。

# 备份单个库
# mysqldump -u username -p my_database > backup.sql

第2步：导入数据库

目的：将数据文件还原至目标实例。
注意：目标库应提前存在或根据备份文件的CREATE语句自动重建。

# 从sql文件还原数据库
# mysql -u username -p new_database < /path/to/databasename.sql