MySQL命令大全：从基础到高级的完整指南

一、MySQL基础操作命令

1.1 连接MySQL服务器

mysql -u [用户名] -p[密码] -h [主机地址] -P [端口]
# 示例：
mysql -u root -p -h 127.0.0.1 -P 3306

注意：-p与密码之间不要加空格，推荐安全登录方式：

mysql -u root -p
Enter password: ******

1.2 数据库操作

-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看数据库列表
SHOW DATABASES;

-- 选择数据库
USE shop;

-- 删除数据库
DROP DATABASE IF EXISTS old_shop;

二、数据表操作

2.1 表结构管理

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改表结构
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20) AFTER email,
MODIFY COLUMN username VARCHAR(60),
DROP COLUMN deprecated_column;

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;

2.2 索引管理

-- 创建复合索引
CREATE INDEX idx_name_phone ON users(username, phone);

-- 删除索引
DROP INDEX idx_email ON users;

三、数据操作命令

3.1 CRUD操作

-- 插入数据
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@test.com');

-- 查询数据
SELECT id, username, email 
FROM users 
WHERE created_at > '2023-01-01' 
ORDER BY id DESC 
LIMIT 10 OFFSET 5;

-- 更新数据
UPDATE users 
SET email = 'new_john@domain.com', phone = '13800138000'
WHERE id = 1;

-- 删除数据
DELETE FROM users 
WHERE created_at < '2020-01-01' 
LIMIT 100;

3.2 复杂查询

-- 多表联合查询
SELECT o.order_id, u.username, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'
  AND o.created_at BETWEEN '2023-01-01' AND '2023-06-30';

-- 聚合查询
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MAX(created_at) AS latest_order
FROM orders
WHERE user_id = 1001;

-- 子查询示例
SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id = 5
);

四、高级功能命令

4.1 事务控制

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE user_id = 1001;
UPDATE accounts SET balance = balance + 500 WHERE user_id = 1002;

-- 确认提交或回滚
COMMIT;
-- ROLLBACK;

4.2 存储过程

DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
    SELECT * 
    FROM orders 
    WHERE user_id = userId 
    ORDER BY created_at DESC;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserOrders(1001);

五、用户权限管理

-- 创建只读用户
CREATE USER 'reader'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT SELECT ON shop.* TO 'reader'@'%';

-- 创建管理用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AdminPass456!';
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'localhost';

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

-- 撤销权限
REVOKE DELETE ON shop.* FROM 'admin'@'localhost';

六、备份与恢复

# 备份整个数据库
mysqldump -u root -p --single-transaction --routines --triggers shop > shop_backup.sql

# 备份单个表
mysqldump -u root -p shop users products > tables_backup.sql

# 恢复数据库
mysql -u root -p shop < shop_backup.sql

七、性能优化命令

7.1 查询分析

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1001;

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

7.2 系统状态查看

-- 显示运行中的进程
SHOW PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看系统变量
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

八、日常维护命令

8.1 表维护

-- 修复损坏的表
REPAIR TABLE damaged_table;

-- 优化表空间
OPTIMIZE TABLE large_table;

-- 分析表统计信息
ANALYZE TABLE important_table;

8.2 日志管理

-- 查看二进制日志
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE '2023-08-01 00:00:00';

-- 启用通用日志
SET GLOBAL general_log = 'ON';

九、安全配置命令

-- 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePass123!';

-- 删除匿名用户
DELETE FROM mysql.user WHERE User = '';

-- 刷新权限
FLUSH PRIVILEGES;

十、MySQL 8.0新特性

-- 窗口函数示例
SELECT 
    product_id,
    sales_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sales_date) AS running_total
FROM sales;

-- CTE通用表表达式
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 1000000;