这是一份从 MySQL 初始化配置 到 日常维护，再到 核心 SQL 操作 的全流程指南。涵盖了从安装后第一次修改密码到日常增删改查的所有高频命令。

第一阶段：初始化与安全配置 (安装后必做)#

刚安装完 MySQL (特别是 5.7+ 和 8.0+)，默认是处于不安全状态或带有临时密码的。

1. 获取临时密码 (仅限首次安装)#

MySQL 5.7/8.0 安装后会生成一个随机临时密码，通常在日志文件中：

# CentOS/RedHat
grep 'temporary password' /var/log/mysqld.log

# Ubuntu/Debian
grep 'temporary password' /var/log/mysql/error.log

2. 登录 MySQL#

mysql -u root -p
# 输入上述获取的临时密码

3. 修改 root 密码 (核心安全步骤)#

MySQL 8.0+ 推荐方式 (使用 alter user):

-- 修改密码并应用加密插件 (mysql_native_password 兼容旧版，caching_sha2_password 为 8.0 默认)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewStrongPassword123!';

-- 如果允许远程登录 (将 localhost 改为 %)
ALTER USER 'root'@'%' IDENTIFIED BY 'YourNewStrongPassword123!';

-- 刷新权限
FLUSH PRIVILEGES;

MySQL 5.7 及旧版方式:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YourNewStrongPassword123!');
FLUSH PRIVILEGES;

4. 创建远程用户 (最佳实践：不要用 root 远程)#

为了安全，通常创建一个专门的管理员或应用用户用于远程连接。

-- 1. 创建用户 (用户：admin, 允许任意IP连接: %)
CREATE USER 'admin'@'%' IDENTIFIED BY 'StrongPassword123!';

-- 2. 授权 (授予所有数据库的所有权限)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

-- 3. 或者只授予特定数据库权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';

-- 4. 生效
FLUSH PRIVILEGES;

第二阶段：数据库与表的基础管理 (DDL)#

1. 数据库操作#

-- 查看所有数据库
SHOW DATABASES;

-- 创建数据库 (指定字符集 utf8mb4，支持 Emoji)
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 选择/切换数据库
USE mydb;

-- 查看当前选中的数据库
SELECT DATABASE();

-- 删除数据库 (慎用！)
DROP DATABASE mydb;

-- 查看建库语句
SHOW CREATE DATABASE mydb;

2. 表操作#

-- 查看当前库所有表
SHOW TABLES;

-- 创建表 (标准模板)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESC users; 
-- 或更详细
SHOW CREATE TABLE users;

-- 修改表名
RENAME TABLE users TO members;

-- 添加列
ALTER TABLE members ADD COLUMN phone VARCHAR(20);

-- 修改列类型
ALTER TABLE members MODIFY COLUMN phone VARCHAR(30);

-- 删除列
ALTER TABLE members DROP COLUMN phone;

-- 删除表
DROP TABLE members;

-- 清空表数据 (速度比 DELETE 快，重置自增 ID)
TRUNCATE TABLE members;

第三阶段：核心增删改查 (DML)#

这是日常开发中最常用的部分。

1. 增 (INSERT)#

-- 插入单条数据
INSERT INTO users (username, email, age) VALUES ('zhangsan', 'zhang@example.com', 25);

-- 插入多条数据
INSERT INTO users (username, email, age) VALUES 
('lisi', 'li@example.com', 28),
('wangwu', 'wang@example.com', 30);

-- 插入并忽略错误 (如主键冲突则不插入)
INSERT IGNORE INTO users (id, username) VALUES (1, 'duplicate');

-- 插入或更新 (如果主键存在则更新，不存在则插入)
INSERT INTO users (id, username, age) VALUES (1, 'zhangsan_new', 26) 
ON DUPLICATE KEY UPDATE age=26, updated_at=NOW();

2. 删 (DELETE)#

-- 删除特定条件数据
DELETE FROM users WHERE id = 1;

-- 删除多个条件
DELETE FROM users WHERE age < 18 OR username = 'test';

-- ⚠️ 高危：删除所有数据 (不带 WHERE)
DELETE FROM users; 
-- 建议：生产环境尽量用 TRUNCATE 清空全表，或用 LIMIT 防止误删
DELETE FROM users LIMIT 10;

3. 改 (UPDATE)#

-- 修改单条
UPDATE users SET age = 26 WHERE username = 'zhangsan';

-- 修改多字段
UPDATE users SET age = 27, email = 'new@example.com' WHERE id = 1;

-- ⚠️ 高危：不带 WHERE 会更新全表！
UPDATE users SET age = 0; 

-- 安全更新习惯：始终带上 LIMIT
UPDATE users SET age = 28 WHERE username = 'lisi' LIMIT 1;

4. 查 (SELECT) - 最复杂也最常用#

-- 1. 基础查询
SELECT * FROM users;
SELECT username, email FROM users;

-- 2. 去重
SELECT DISTINCT age FROM users;

-- 3. 条件过滤 (WHERE)
SELECT * FROM users WHERE age > 20 AND username LIKE 'zhang%';
-- 常用操作符：=, !=, >, <, >=, <=, IN, NOT IN, BETWEEN, IS NULL, IS NOT NULL

-- 4. 排序 (ORDER BY)
SELECT * FROM users ORDER BY age DESC, created_at ASC; -- 年龄降序，时间升序

-- 5. 分页 (LIMIT) - MySQL 特色，常用于后端分页
SELECT * FROM users LIMIT 0, 10; -- 从第 0 条开始，取 10 条 (第一页)
SELECT * FROM users LIMIT 10, 10; -- 从第 10 条开始，取 10 条 (第二页)
-- 公式：LIMIT (页码-1)*每页数量, 每页数量

-- 6. 聚集函数 (统计)
SELECT COUNT(*) AS total FROM users;
SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users;
SELECT AVG(age) AS avg_age FROM users;
SELECT SUM(age) FROM users;

-- 7. 分组 (GROUP BY) - 配合聚集函数
-- 统计每个年龄有多少人
SELECT age, COUNT(*) as count FROM users GROUP BY age;
-- 统计每个年龄有多少人，且只显示人数大于 1 的组
SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 1;

-- 8. 多表连接 (JOIN)
-- 假设有订单表 orders (id, user_id, amount)
-- 内连接 (只查有订单的用户)
SELECT u.username, o.amount 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接 (查所有用户，即使没有订单)
SELECT u.username, o.amount 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

第四阶段：日常维护与监控 (DBA 常用)#

1. 用户与权限管理#

-- 查看当前有哪些用户
SELECT user, host FROM mysql.user;

-- 查看某个用户的权限
SHOW GRANTS FOR 'admin'@'%';

-- 回收权限
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';
FLUSH PRIVILEGES;

-- 删除用户
DROP USER 'old_user'@'localhost';

2. 进程与锁监控 (排障神器)#

-- 查看当前正在运行的线程/进程
SHOW PROCESSLIST; 
-- 进阶：查看完整 SQL (防止被截断)
SHOW FULL PROCESSLIST;

-- 杀掉卡死的进程 (替换 ID)
KILL 12345;

-- 查看表锁状态
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看引擎状态 (InnoDB 详细信息)
SHOW ENGINE INNODB STATUS;

3. 变量与配置查看#

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看字符集设置
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 动态修改全局变量 (重启失效，仅临时)
SET GLOBAL max_connections = 500;

-- 动态修改会话变量 (仅当前连接有效)
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

4. 备份与还原 (命令行操作，非 SQL)#

注意：以下命令在 Linux Shell 中执行，不是在 mysql 提示符下。

# 1. 备份整个数据库 (逻辑备份)
mysqldump -u root -p mydb > mydb_backup.sql

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

# 3. 只备份表结构 (不含数据)
mysqldump -u root -p -d mydb > mydb_structure.sql

# 4. 还原数据库
# 先创建库 (如果不存在)
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb;"
# 导入
mysql -u root -p mydb < mydb_backup.sql

5. 性能分析与优化#

-- 开启慢查询分析 (临时)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒的算慢查询

-- 使用 EXPLAIN 分析 SQL 执行计划 (优化索引必用)
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';
-- 重点关注：type (是否走索引), key (实际用的索引), rows (扫描行数)

-- 查看表的大小和行数
SELECT table_name, table_rows, data_length, index_length 
FROM information_schema.tables 
WHERE table_schema = 'mydb';

💡 避坑指南与最佳实践#

  1. 永远不要信任 DELETE 和 UPDATE 不带 WHERE：

    • 在操作前，先写 SELECT 确认条件匹配的行是否正确。
    • 习惯性加上 LIMIT，例如 DELETE FROM table WHERE ... LIMIT 100;。

  2. 字符集陷阱：

    • 建库建表时务必指定 utf8mb4，否则存储 Emoji 表情会报错或乱码。
    • 避免使用 MySQL 古老的 utf8 (其实是 utf8mb3)。

  3. 索引优化：

    • 在 WHERE, ORDER BY, GROUP BY, JOIN 的字段上建立索引。
    • 利用 EXPLAIN 检查索引是否生效。
    • 索引不是越多越好，会影响写入速度。

  4. 事务控制 (针对 InnoDB 引擎)：

    START TRANSACTION;
-- 执行多条 SQL
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 确认无误后提交
COMMIT;
-- 如果有错回滚
-- ROLLBACK;

  5. 生产环境规范：

    • 禁止使用 SELECT *，只查询需要的字段（减少网络传输和内存消耗）。
    • 大表删除数据不要一次性 DELETE 几百万行，要分批进行。
    • 定期备份，并测试备份文件的可用性。
Backward MySQL数据库 达梦数据库 Forward
Linux邪修 - © 2024 Linux邪修