MySQL配置与SQL语句——数据的管家与翻译官

生活类比：数据库就像一个巨大的"智能仓库"——MySQL是仓库管理员，SQL是你和仓库沟通的语言，存储引擎是货架的组织方式，索引是货物的标签系统，事务是确保"进货出货"不出差错的流程规范。

一、数据库概念——数据的"收纳哲学"

1.1 问题引入

场景：学校要管理10000名学生的信息（姓名、班级、成绩、考勤），用Excel存？文件越来越大、越来越慢、多人同时改就冲突。

解决思路：使用专业的数据库管理系统（DBMS），像请了一位专业的"数据管家"。

1.2 关系型数据库 vs 非关系型数据库

类比：关系型数据库像Excel表格——规规矩矩的行列；NoSQL像便签墙——灵活但不够严谨。运维中两者通常配合使用：MySQL存核心数据，Redis做缓存加速。

二、MySQL vs MariaDB——兄弟分家

2.1 历史背景

MySQL诞生(1995) → Sun收购(2008) → Oracle收购Sun(2010)
                                    ↓
                        MySQL创始人创建MariaDB(2009)
                        （用女儿Maria的名字命名）

互动提问：CentOS 8 中执行 yum install mysql 实际安装的是什么？

答案：安装的是 MariaDB！CentOS 8/RHEL 8 已将默认数据库从 MySQL 切换为 MariaDB。如果确实需要MySQL官方版本，需要从MySQL官方Yum仓库安装。

三、MySQL安装——各发行版实战

3.1 CentOS/RHEL使用yum安装

# ===== MySQL官方Yum仓库安装 =====

# 步骤1：添加MySQL官方仓库
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

# 步骤2：查看可用版本
yum repolist enabled | grep mysql

# 步骤3：安装MySQL Server
yum install -y mysql-community-server

# 步骤4：启动并设置开机自启
systemctl start mysqld
systemctl enable mysqld

# 步骤5：获取临时密码（首次安装会生成）
grep 'temporary password' /var/log/mysqld.log
# 输出示例：A temporary password is generated for root@localhost: Abc123!xyz

# 步骤6：安全初始化（修改密码、删除匿名用户等）
mysql_secure_installation

# 步骤7：验证安装
mysql -u root -p
mysql> SELECT VERSION();

3.2 Ubuntu/Debian使用apt安装

# ===== Ubuntu apt安装MySQL =====

# 步骤1：更新包索引
sudo apt update

# 步骤2：安装MySQL Server
sudo apt install -y mysql-server

# 步骤3：启动服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 步骤4：安全初始化
sudo mysql_secure_installation

# 步骤5：验证
sudo mysql -u root -p

# ===== 安装MariaDB =====
sudo apt install -y mariadb-server
sudo systemctl start mariadb
sudo mysql_secure_installation

3.3 源码编译安装（了解流程）

# 源码编译安装（适用于需要自定义的场景）

# 安装依赖
yum install -y gcc gcc-c++ cmake ncurses-devel bison openssl-devel

# 下载源码
cd /usr/local/src
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.35.tar.gz
tar xzf mysql-8.0.35.tar.gz
cd mysql-8.0.35

# cmake配置
cmake . \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DMYSQL_DATADIR=/data/mysql \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DDEFAULT_CHARSET=utf8mb4 \
  -DDEFAULT_COLLATION=utf8mb4_general_ci

# 编译安装（耗时较长，可用-j加速）
make -j$(nproc)
make install

# 初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql

# 启动
/usr/local/mysql/bin/mysqld_safe --user=mysql &

互动提问：为什么生产环境推荐用包管理器安装而非源码编译？

答案：包管理器安装更方便管理（升级、卸载、依赖处理），经过发行版测试验证，安全补丁推送及时。源码编译适用于需要特殊定制的场景，但维护成本高。

四、配置文件——MySQL的"遥控器"

4.1 my.cnf关键参数

# /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# ===== 基础配置 =====
port = 3306                          # 监听端口
datadir = /var/lib/mysql             # 数据目录
socket = /var/lib/mysql/mysql.sock   # Socket文件路径
pid-file = /var/run/mysqld/mysqld.pid

# ===== 字符集（非常重要！） =====
character-set-server = utf8mb4       # 服务器字符集（支持emoji）
collation-server = utf8mb4_unicode_ci

# ===== 连接相关 =====
max_connections = 500                # 最大连接数
max_connect_errors = 100000          # 最大连接错误数（防暴力破解）
wait_timeout = 600                   # 空闲连接超时（秒）
interactive_timeout = 600            # 交互式连接超时

# ===== InnoDB引擎配置 =====
innodb_buffer_pool_size = 2G         # 缓冲池大小（建议物理内存的50-70%）
innodb_log_file_size = 256M          # redo log大小
innodb_flush_log_at_trx_commit = 1   # 事务提交时刷盘策略（1=最安全）
innodb_file_per_table = ON           # 每个表一个文件

# ===== 日志配置 =====
log-error = /var/log/mysqld/error.log    # 错误日志
slow_query_log = ON                      # 开启慢查询日志
slow_query_log_file = /var/log/mysqld/slow.log
long_query_time = 2                      # 超过2秒的查询记录为慢查询
log_queries_not_using_indexes = ON       # 记录没用索引的查询

# ===== 二进制日志（主从复制必备） =====
server-id = 1                        # 服务器ID（集群中必须唯一）
log-bin = mysql-bin                  # 开启binlog
binlog_format = ROW                  # binlog格式（ROW最安全）
expire_logs_days = 7                 # binlog过期天数
sync_binlog = 1                      # 每次事务同步binlog到磁盘

[client]
default-character-set = utf8mb4      # 客户端默认字符集

类比：my.cnf 就像MySQL的"遥控器"——不用重启就能调整很多行为。但有些参数改了需要重启才生效（如 innodb_buffer_pool_size），有些可以动态修改（用 SET GLOBAL 命令）。

4.2 配置参数调优建议

五、MySQL架构——拆开引擎盖看内部

5.1 三层架构

┌─────────────────────────────────────────┐
│              连接层 (Connection)          │  ← 客户端连接、认证、权限验证
├─────────────────────────────────────────┤
│              SQL层 (Server)               │  ← 解析、优化、执行SQL
│  ┌──────────┬──────────┬──────────────┐  │
│  │ 解析器    │ 优化器    │  执行器       │  │
│  │ Parser   │ Optimizer│  Executor    │  │
│  └──────────┴──────────┴──────────────┘  │
├─────────────────────────────────────────┤
│           存储引擎层 (Storage Engine)      │  ← 数据的存取
│  ┌──────────┬──────────┬──────────────┐  │
│  │ InnoDB   │ MyISAM   │  Memory/...  │  │
│  └──────────┴──────────┴──────────────┘  │
└─────────────────────────────────────────┘

类比：

• 连接层 = 酒店前台（验证身份、分配房间）
• SQL层 = 大堂经理（理解你的需求、安排最优方案、指挥执行）
• 存储引擎层 = 仓库管理员（负责实际取货存货，不同管理员有不同的管理方式）

5.2 一条SQL的执行流程

SELECT * FROM students WHERE age > 18 ORDER BY score DESC;

1. 连接层：验证用户名密码、检查是否有查询权限
2. 解析器：分析SQL语法，生成语法树
3. 优化器：选择最优执行计划（用哪个索引？全表扫描还是索引扫描？）
4. 执行器：调用存储引擎执行计划
5. 存储引擎：从磁盘/内存中读取数据，返回给执行器

互动提问：为什么同样的SQL，有时候快有时候慢？

答案：可能是：1）数据量变大了；2）索引失效（优化器选择了全表扫描）；3）缓冲池命中率低（冷数据）；4）锁等待。用 EXPLAIN 命令可以查看执行计划。

六、存储引擎——InnoDB vs MyISAM

6.1 对比表

结论：生产环境几乎全部使用InnoDB。MyISAM只在极少数只读场景使用。

6.2 查看和切换引擎

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'students';
SHOW CREATE TABLE students;

-- 创建表时指定引擎
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT
) ENGINE=MyISAM;

-- 修改已有表的引擎（会锁表，生产慎用）
ALTER TABLE logs ENGINE=InnoDB;

-- 查看当前默认引擎
SHOW VARIABLES LIKE 'default_storage_engine';

七、SQL语句大全——与数据库对话的语言

7.1 DDL（数据定义语言）——"建房子、改房子、拆房子"

数据库操作

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

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

-- 切换数据库
USE school_db;

-- 删除数据库（危险！不可恢复！）
DROP DATABASE IF EXISTS test_db;

-- 修改数据库字符集
ALTER DATABASE school_db CHARACTER SET utf8mb4;

表操作

-- 创建表（完整版）
CREATE TABLE students (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '学号',
    name        VARCHAR(50) NOT NULL COMMENT '姓名',
    gender      ENUM('男','女') DEFAULT '男' COMMENT '性别',
    age         TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    class_id    INT UNSIGNED COMMENT '班级ID',
    score       DECIMAL(5,2) DEFAULT 0.00 COMMENT '分数',
    email       VARCHAR(100) UNIQUE COMMENT '邮箱',
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),                    -- 普通索引
    INDEX idx_class (class_id),               -- 普通索引
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

-- 修改表结构
ALTER TABLE students ADD phone VARCHAR(20) AFTER email;       -- 添加列
ALTER TABLE students MODIFY age SMALLINT UNSIGNED;             -- 修改列类型
ALTER TABLE students CHANGE score total_score DECIMAL(6,2);   -- 改名+改类型
ALTER TABLE students DROP COLUMN phone;                        -- 删除列
ALTER TABLE students RENAME TO student_info;                   -- 重命名表

-- 删除表
DROP TABLE IF EXISTS temp_logs;

-- 清空表（保留结构，重置自增ID）
TRUNCATE TABLE temp_logs;

类比：

• CREATE TABLE = 盖房子（定义房间布局）
• ALTER TABLE = 装修改造（加房间、改结构）
• DROP TABLE = 拆迁（连房子带东西全没了）
• TRUNCATE TABLE = 清空房间（房子还在，里面清空）

7.2 DML（数据操作语言）——"搬货进货出货"

INSERT插入数据

-- 插入单行
INSERT INTO students (name, gender, age, class_id, score)
VALUES ('张三', '男', 20, 1, 85.5);

-- 插入多行
INSERT INTO students (name, gender, age, class_id, score) VALUES
('李四', '女', 19, 1, 92.0),
('王五', '男', 21, 2, 78.5),
('赵六', '女', 20, 2, 88.0);

-- INSERT ... SELECT（从其他表插入）
INSERT INTO student_archive (name, score)
SELECT name, score FROM students WHERE graduated = 1;

-- INSERT ... ON DUPLICATE KEY UPDATE（存在则更新）
INSERT INTO students (id, name, score)
VALUES (1, '张三', 90)
ON DUPLICATE KEY UPDATE score = VALUES(score);

UPDATE更新数据

-- 基本更新
UPDATE students SET score = 95.5 WHERE id = 1;

-- 多列更新
UPDATE students SET score = score + 5, age = age + 1
WHERE class_id = 1;

-- 条件更新（CASE WHEN）
UPDATE students SET
    score = CASE
        WHEN score >= 90 THEN score * 1.05  -- 优秀的加5%
        WHEN score >= 60 THEN score * 1.10  -- 及格的加10%
        ELSE score * 1.15                    -- 不及格的加15%
    END
WHERE class_id = 1;

危险警告：不带 WHERE 条件的 UPDATE 会修改表中所有行！建议先 SELECT 确认再 UPDATE。

DELETE删除数据

-- 删除特定行
DELETE FROM students WHERE id = 5;

-- 删除多条件
DELETE FROM students WHERE age < 16 AND score < 30;

-- 多表删除（删除students中不在任何class里的学生）
DELETE s FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE c.id IS NULL;

SELECT查询——SQL的核心

-- ===== 基础查询 =====
SELECT * FROM students;                              -- 查全部列（生产不推荐）
SELECT name, score FROM students WHERE age > 18;     -- 条件查询
SELECT DISTINCT class_id FROM students;              -- 去重

-- ===== 比较与逻辑运算 =====
SELECT * FROM students WHERE score BETWEEN 60 AND 80;
SELECT * FROM students WHERE name LIKE '张%';         -- LIKE模糊匹配
SELECT * FROM students WHERE name LIKE '张_';         -- _匹配单个字符
SELECT * FROM students WHERE class_id IN (1, 2, 3);
SELECT * FROM students WHERE email IS NOT NULL;

-- ===== 排序 =====
SELECT name, score FROM students ORDER BY score DESC;           -- 降序
SELECT name, score FROM students ORDER BY class_id ASC, score DESC;  -- 多字段排序

-- ===== 限制结果 =====
SELECT * FROM students ORDER BY score DESC LIMIT 10;            -- 前10名
SELECT * FROM students ORDER BY score DESC LIMIT 10 OFFSET 20;  -- 第3页，每页10条
-- 简写
SELECT * FROM students ORDER BY score DESC LIMIT 20, 10;        -- 同上

-- ===== 聚合函数 =====
SELECT COUNT(*) AS total FROM students;
SELECT COUNT(DISTINCT class_id) AS class_count FROM students;
SELECT MAX(score), MIN(score), AVG(score), SUM(score) FROM students;

-- ===== GROUP BY分组 =====
SELECT class_id, COUNT(*) AS cnt, AVG(score) AS avg_score
FROM students
GROUP BY class_id
HAVING avg_score > 80         -- HAVING过滤分组（WHERE过滤行）
ORDER BY avg_score DESC;

-- ===== 执行顺序（重要！） =====
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

互动提问：WHERE 和 HAVING 有什么区别？

答案：WHERE 在分组之前过滤行，不能用聚合函数；HAVING 在分组之后过滤组，可以用聚合函数。例如 WHERE score > 60 过滤个人，HAVING AVG(score) > 80 过滤班级平均分。

JOIN多表连接

-- 创建关联表
CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO classes (name, teacher) VALUES
('计算机1班', '王老师'), ('计算机2班', '李老师'), ('计算机3班', '张老师');

-- INNER JOIN（内连接：只返回两表匹配的行）
SELECT s.name AS student, s.score, c.name AS class, c.teacher
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

-- LEFT JOIN（左连接：左表全部 + 右表匹配的，不匹配为NULL）
SELECT s.name, c.name AS class
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

-- RIGHT JOIN（右连接：右表全部 + 左表匹配的）
SELECT c.name AS class, COUNT(s.id) AS student_count
FROM classes c
LEFT JOIN students s ON c.id = s.class_id
GROUP BY c.id, c.name;

-- 多表连接
SELECT s.name, s.score, c.name AS class, c.teacher
FROM students s
JOIN classes c ON s.class_id = c.id
WHERE s.score > 90
ORDER BY s.score DESC;

类比：

• INNER JOIN = 两个班级联谊，只展示双方都认识的同学
• LEFT JOIN = 左边班级全员出席，认识的右边同学也来，不认识的位置空着（NULL）

子查询

-- WHERE子查询
SELECT name, score FROM students
WHERE score > (SELECT AVG(score) FROM students);

-- IN子查询
SELECT name FROM students
WHERE class_id IN (SELECT id FROM classes WHERE teacher = '王老师');

-- EXISTS子查询（通常性能优于IN）
SELECT c.name FROM classes c
WHERE EXISTS (SELECT 1 FROM students s WHERE s.class_id = c.id AND s.score > 90);

-- 派生表（FROM子查询）
SELECT class_name, avg_score
FROM (
    SELECT c.name AS class_name, AVG(s.score) AS avg_score
    FROM students s JOIN classes c ON s.class_id = c.id
    GROUP BY c.name
) AS class_stats
WHERE avg_score > 80;

UNION联合查询

-- UNION（去重）
SELECT name, '高分' AS category FROM students WHERE score >= 90
UNION
SELECT name, '及格' AS category FROM students WHERE score BETWEEN 60 AND 89;

-- UNION ALL（不去重，性能更好）
SELECT name FROM students WHERE class_id = 1
UNION ALL
SELECT name FROM students WHERE class_id = 2;

7.3 DCL（数据控制语言）——"给钥匙和收钥匙"

-- 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

-- 授权
GRANT SELECT, INSERT, UPDATE ON school_db.* TO 'app_user'@'192.168.1.%';
GRANT ALL PRIVILEGES ON school_db.* TO 'admin_user'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

-- 撤销权限
REVOKE INSERT, UPDATE ON school_db.* FROM 'app_user'@'192.168.1.%';

-- 刷新权限（修改权限后立即生效）
FLUSH PRIVILEGES;

-- 删除用户
DROP USER 'app_user'@'192.168.1.%';

互动提问：为什么不建议用 GRANT ALL ON *.* 给应用用户？

答案：权限过大违反"最小权限原则"。如果应用被入侵，攻击者可以删除所有数据库的所有表。应该只给需要的库、需要的操作权限。

八、索引——数据的"快速检索标签"

8.1 索引的概念与类型

没有索引：翻字典从第一页开始找（全表扫描）
有索引：先查目录，直接翻到对应页（索引查找）

8.2 索引操作

-- 创建索引
CREATE INDEX idx_score ON students(score);
CREATE UNIQUE INDEX idx_email ON students(email);
CREATE INDEX idx_class_score ON students(class_id, score);  -- 联合索引

-- 查看索引
SHOW INDEX FROM students;

-- 删除索引
DROP INDEX idx_score ON students;

-- EXPLAIN查看是否使用索引
EXPLAIN SELECT * FROM students WHERE score > 80;
-- 重点看：type(扫描类型)、key(使用的索引)、rows(扫描行数)、Extra

互动提问：索引越多越好吗？

答案：不是！索引有代价：1）占用额外磁盘空间；2）INSERT/UPDATE/DELETE时需要维护索引，写入变慢；3）优化器需要在多个索引中选择。原则：查询频繁的列建索引，频繁更新的列、区分度低的列（如性别）不建。

九、视图——虚拟的"快捷查询"

-- 创建视图（保存常用查询）
CREATE VIEW v_class_summary AS
SELECT
    c.name AS class_name,
    c.teacher,
    COUNT(s.id) AS student_count,
    ROUND(AVG(s.score), 2) AS avg_score,
    MAX(s.score) AS max_score,
    MIN(s.score) AS min_score
FROM classes c
LEFT JOIN students s ON c.id = s.class_id
GROUP BY c.id, c.name, c.teacher;

-- 使用视图（像查表一样简单）
SELECT * FROM v_class_summary WHERE avg_score > 80;

-- 删除视图
DROP VIEW IF EXISTS v_class_summary;

类比：视图就像"快捷方式"——它本身不存储数据，而是保存了一条SQL，每次使用视图时执行这条SQL。好处是简化复杂查询、控制数据访问范围。

十、事务——确保操作"要么全做、要么全不做"

10.1 ACID特性

10.2 事务操作

-- 查看当前自动提交状态（默认ON）
SHOW VARIABLES LIKE 'autocommit';

-- 手动事务控制
SET autocommit = 0;  -- 关闭自动提交

START TRANSACTION;   -- 或 BEGIN;

-- 转账操作示例
UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1;  -- A扣款
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 2;  -- B入账

-- 检查是否成功
SELECT balance FROM accounts WHERE user_id IN (1, 2);

-- 成功则提交
COMMIT;

-- 失败则回滚
-- ROLLBACK;

-- 保存点（部分回滚）
START TRANSACTION;
UPDATE students SET score = score + 10 WHERE class_id = 1;
SAVEPOINT sp1;  -- 设置保存点
UPDATE students SET score = score + 10 WHERE class_id = 2;
-- 发现class_id=2的加分有误，只回滚到sp1
ROLLBACK TO SAVEPOINT sp1;
COMMIT;  -- class_id=1的加分保留了

互动提问：如果两个用户同时给同一个账户转账，会不会出问题？

答案：不会！InnoDB的行级锁会保证同一行的更新排队执行。但要注意"死锁"问题——两个事务互相等待对方释放锁，MySQL会自动检测并回滚其中一个事务。

10.3 事务隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;  -- 默认REPEATABLE-READ

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

十一、数据类型详解——选对"容器"

互动提问：存金额为什么必须用DECIMAL而不能用FLOAT？

答案：FLOAT/DOUBLE是浮点数，存在精度丢失问题。例如 0.1 + 0.2 可能得到 0.30000000000000004。DECIMAL是精确数值类型，不会有精度问题，DECIMAL(10,2) 表示总共10位、小数2位。

十二、常用查询实战

-- 1. 分页查询（第3页，每页20条）
SELECT * FROM students ORDER BY id LIMIT 40, 20;

-- 2. 统计每个班级的及格率和平均分
SELECT
    c.name AS class_name,
    COUNT(*) AS total,
    SUM(CASE WHEN s.score >= 60 THEN 1 ELSE 0 END) AS pass_count,
    CONCAT(ROUND(SUM(CASE WHEN s.score >= 60 THEN 1 ELSE 0 END) / COUNT(*) * 100, 1), '%') AS pass_rate,
    ROUND(AVG(s.score), 2) AS avg_score
FROM students s
JOIN classes c ON s.class_id = c.id
GROUP BY c.id, c.name;

-- 3. 查找每个班级的前3名
SELECT * FROM (
    SELECT
        s.*,
        c.name AS class_name,
        ROW_NUMBER() OVER (PARTITION BY s.class_id ORDER BY s.score DESC) AS rank_no
    FROM students s
    JOIN classes c ON s.class_id = c.id
) ranked
WHERE rank_no <= 3;

-- 4. 查找成绩波动大的学生（标准差）
SELECT name,
    COUNT(*) AS exam_count,
    ROUND(AVG(score), 2) AS avg_score,
    ROUND(STDDEV(score), 2) AS score_stddev
FROM exam_records
GROUP BY student_id
HAVING score_stddev > 15
ORDER BY score_stddev DESC;

-- 5. 慢查询排查
-- 开启慢查询日志后，查看慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 6. 查看正在执行的SQL
SHOW PROCESSLIST;
-- 杀掉长时间运行的查询
KILL 1234;

-- 7. 表和数据库大小
SELECT table_name,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb,
    ROUND((data_length + index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'school_db'
ORDER BY total_mb DESC;

运维常用命令精选

# ===== MySQL服务管理 =====
systemctl start mysqld           # 启动MySQL
systemctl stop mysqld            # 停止MySQL
systemctl restart mysqld         # 重启MySQL
systemctl status mysqld          # 查看状态
systemctl enable mysqld          # 开机自启

# ===== 连接MySQL =====
mysql -u root -p                 # 本地连接
mysql -h 192.168.1.10 -u admin -p -P 3306  # 远程连接
mysql -e "SHOW DATABASES;" -u root -p       # 非交互式执行

# ===== 常用运维SQL =====
SHOW VARIABLES LIKE '%max_conn%';         # 查看配置参数
SHOW GLOBAL STATUS LIKE 'Threads%';      # 查看线程状态
SHOW PROCESSLIST;                         # 查看当前连接
SHOW ENGINE INNODB STATUS\G              # InnoDB引擎状态
SHOW SLAVE STATUS\G                      # 主从复制状态
SHOW BINARY LOGS;                        # 查看binlog列表
SHOW VARIABLES LIKE 'slow_query%';       # 慢查询配置

# ===== 数据备份 =====
mysqldump -u root -p --all-databases > full_backup.sql
mysqldump -u root -p school_db students > students_backup.sql

# ===== 性能工具 =====
mysqladmin -u root -p status             # 服务器状态概览
mysqladmin -u root -p extended-status    # 扩展状态
mysqltuner                               # 自动调优建议（需安装）

趣味命令

# 查看MySQL的"复活节彩蛋"（隐藏功能列表）
mysql -u root -p -e "SELECT * FROM mysql.help_topic WHERE help_topic_id = 0\G"

# 查看MySQL的版本信息和编译信息
mysql -u root -p -e "SHOW VARIABLES LIKE 'version%';"

# 查看数据库占用的磁盘空间（Linux命令）
du -sh /var/lib/mysql/

# 实时监控MySQL查询（类似top）
mysqladmin -u root -p -i 1 processlist

# 用SQL画一个爱心（MySQL也能秀操作）
mysql -e "SELECT CONCAT(REPEAT(' ', 10-ABS(n)), REPEAT('*', 2*ABS(n)+1))
FROM (SELECT @row := @row + 1 AS n FROM
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
(SELECT @row := -1) r
WHERE @row < 10) t3;"

# 数据库"体检报告"
mysql -u root -p -e "
SELECT '数据库列表' AS info, COUNT(*) AS cnt FROM information_schema.schemata
UNION ALL
SELECT '数据表总数', COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
UNION ALL
SELECT '总数据量(MB)', ROUND(SUM(data_length+index_length)/1024/1024, 2) FROM information_schema.tables;
"