MySQL 数据库指令大全：从基础到进阶的完整指南

来源：腾讯云开发者社区 · 原文
说明：MySQL 是全球最流行的开源关系型数据库之一，其指令系统是开发者与 DBA 的核心工具。本文系统梳理 MySQL 常用指令，涵盖数据库管理、表操作、数据增删改查、索引优化及备份恢复等核心场景，帮助快速掌握数据库操作要点。

目录

• 一、数据库管理指令
• 二、表结构操作指令
• 三、数据操作指令
• 四、性能优化指令
• 五、备份恢复指令
• 六、高级功能指令
• 最佳实践建议

---

一、数据库管理指令

1. 基础操作

CREATE DATABASE sales_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- 创建支持 emoji 的数据库

DROP DATABASE IF EXISTS temp_db;
-- 安全删除已存在的数据库

USE marketing_db;
-- 切换当前数据库

SHOW DATABASES LIKE 'prod_%';
-- 模糊匹配查询数据库

2. 用户权限管理

CREATE USER 'api_user'@'%' IDENTIFIED BY 'SecurePass123!';
-- 创建可远程访问的用户

GRANT SELECT, INSERT ON customer_db.* TO 'api_user'@'%';
-- 授予特定权限

REVOKE INSERT ON orders.* FROM 'api_user'@'%';
-- 撤销权限

FLUSH PRIVILEGES;
-- 立即生效权限变更

---

二、表结构操作指令

1. 建表与约束

CREATE TABLE orders (
    order_id VARCHAR(32) PRIMARY KEY,
    customer_id VARCHAR(32) NOT NULL,
    amount DECIMAL(10,2) CHECK (amount > 0),
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 结构变更

ALTER TABLE products
ADD COLUMN discount_rate DECIMAL(3,2) DEFAULT 0.0 AFTER price;
-- 添加字段并指定位置

ALTER TABLE users
MODIFY COLUMN phone VARCHAR(20) COMMENT '国际电话格式';
-- 修改字段属性

ALTER TABLE logs
DROP INDEX idx_log_time;
-- 删除索引

---

三、数据操作指令

1. 高效查询

-- 条件查询与排序
SELECT product_name, stock
FROM inventory
WHERE stock > 100 AND category = 'Electronics'
ORDER BY stock DESC
LIMIT 20;

-- 复杂连接查询
SELECT o.order_id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date > '2025-01-01';

2. 数据更新与删除

-- 批量更新
UPDATE products
SET price = price * 0.9
WHERE category = 'Seasonal' AND stock > 50;

-- 安全删除（带条件）
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 10000;

---

四、性能优化指令

1. 索引管理

-- 创建复合索引
CREATE INDEX idx_order_customer_date ON orders(customer_id, order_date);

-- 分析索引使用情况
EXPLAIN SELECT * FROM orders
WHERE customer_id = 'CUST001' AND order_date > '2025-01-01';

2. 表优化

OPTIMIZE TABLE orders;
-- 重建表减少碎片

ANALYZE TABLE products;
-- 更新统计信息

SET GLOBAL innodb_buffer_pool_size = 4G;
-- 调整缓冲池大小（需权限）

---

五、备份恢复指令

1. 逻辑备份

# 备份单个数据库
mysqldump -u root -p --single-transaction sales_db > sales_backup_20251105.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > full_backup_20251105.sql

2. 数据恢复

# 创建空数据库
mysql -u root -p -e "CREATE DATABASE restored_db;"

# 恢复数据
mysql -u root -p restored_db < sales_backup_20251105.sql

---

六、高级功能指令

1. 存储过程与事务

DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN from_acc VARCHAR(32),
    IN to_acc VARCHAR(32),
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END //
DELIMITER ;

2. 视图与分区表

-- 创建视图
CREATE VIEW v_customer_orders AS
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

-- 创建分区表
CREATE TABLE sales_data (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

---

最佳实践建议

• 安全规范：生产环境禁用 root 远程访问，使用最小权限原则。
• 性能监控：定期执行 SHOW STATUS 和 SHOW ENGINE INNODB STATUS。
• 备份策略：采用 mysqldump + 二进制日志（binlog）的组合备份方案。
• 字符集：新项目统一使用 utf8mb4 以支持完整 Unicode 字符。

通过系统掌握这些指令，开发者可以高效完成从日常运维到复杂业务逻辑实现的全栈数据库操作。建议结合 MySQL 8.0+ 版本特性（如窗口函数、JSON 支持等）进一步探索高级功能。