MySQL 使用教程 · CodeNest 知识库

MySQL 简介

MySQL 是最流行的开源关系型数据库。数据以"表"的形式组织，表由行（记录）和列（字段）构成，通过 SQL 语言进行增删改查。

SQL 基础

-- 建库建表
CREATE DATABASE shop CHARACTER SET utf8mb4;

USE shop;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入
INSERT INTO users (name, age) VALUES ('小明', 25);

-- 查询
SELECT * FROM users WHERE age > 18 ORDER BY id DESC LIMIT 10;

-- 更新 / 删除
UPDATE users SET age = 26 WHERE name = '小明';
DELETE FROM users WHERE id = 1;

✅ 建议

生产环境避免 SELECT *，只取需要的字段；WHERE 条件要能用上索引。

多表查询

-- 内连接：两表都匹配的记录
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 左连接：左表全部 + 右表匹配
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING order_count > 0;

常用聚合函数：COUNT、SUM、AVG、MAX、MIN，配合 GROUP BY 分组统计。

数据库基本概念

数据库是结构化存储数据的软件。MySQL 是最流行的开源关系型数据库,几乎每个 Web 项目都在用。

关系型数据库的层次结构:

层级	说明	类比
数据库(Database)	一个项目一套库	仓库
表(Table)	同类型数据的集合	货架
行(Row)	一条记录	一件货物
列(Column)	一个字段	货物属性

表与表之间通过主键(PK)和外键(FK)建立关联,例如"用户表"与"订单表"通过 user_id 关联。

💡 提示

MySQL 的 SQL 关键字不区分大小写,但习惯上关键字大写、表名小写,便于阅读。

连接与库操作

连接 MySQL(命令行方式):

# 连接本机
mysql -u root -p

# 连接远程(宝塔面板等)
mysql -h 59.110.240.202 -P 3306 -u site -p

库操作

-- 查看所有库
SHOW DATABASES;

-- 创建库(utf8mb4 支持中文与 emoji)
CREATE DATABASE mydb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 切换/使用库
USE mydb;

-- 查看库下所有表
SHOW TABLES;

-- 删除库(危险!)
DROP DATABASE mydb;

⚠️ 注意

生产环境字符集务必用 utf8mb4,否则插入 emoji 或生僻字会报错。

创建表与约束

CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,  -- 主键自增
    name       VARCHAR(50)  NOT NULL,           -- 必填
    email      VARCHAR(120) UNIQUE,             -- 唯一约束
    age        TINYINT      DEFAULT 0,          -- 默认值
    status     TINYINT      DEFAULT 1,
    created_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)                       -- 普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用数据类型速查:

类型	用途
`INT` / `BIGINT`	整数
`VARCHAR(n)`	变长字符串
`TEXT`	长文本(文章正文)
`DECIMAL(10,2)`	金额等精确小数
`DATETIME`	日期时间
`BOOLEAN`	布尔(实际为 TINYINT)

✅ 建议

主键统一用 id INT AUTO_INCREMENT;金额永远不要用 FLOAT,精度会丢失。

插入数据 INSERT

-- 单条插入
INSERT INTO users (name, email, age)
VALUES ('小明', 'xm@example.com', 25);

-- 批量插入(效率高)
INSERT INTO users (name, email) VALUES
    ('小红', 'xh@example.com'),
    ('小刚', 'xg@example.com');

-- 插入并获取自增 ID
INSERT INTO users (name) VALUES ('小丽');
SELECT LAST_INSERT_ID();

✅ 建议

批量插入用一条 INSERT 带多组 VALUES,比循环逐条插入快数十倍。

查询 SELECT 基础

-- 查询指定列
SELECT name, email FROM users;

-- 查询所有列(慎用,生产不推荐)
SELECT * FROM users;

-- 条件过滤
SELECT * FROM users
WHERE age >= 18 AND status = 1;

-- 去重
SELECT DISTINCT city FROM users;

-- 起别名
SELECT name AS 姓名, age AS 年龄 FROM users;

-- 限制条数
SELECT * FROM users ORDER BY id DESC LIMIT 10;

-- 模糊查询(% 任意字符)
SELECT * FROM users WHERE name LIKE '小%';

-- 区间查询
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- IN 列表
SELECT * FROM users WHERE city IN ('北京', '上海');

⚠️ 注意

线上环境避免 SELECT *,显式列出需要的列,减少传输与索引负担。

更新与删除

-- 更新(务必带 WHERE!)
UPDATE users SET age = 26 WHERE id = 1;

-- 多字段更新
UPDATE users
SET status = 0, updated_at = NOW()
WHERE status = 1 AND age > 60;

-- 删除(务必带 WHERE!)
DELETE FROM users WHERE id = 10;

-- 清空表(保留结构,重置自增)
TRUNCATE TABLE users;

⚠️ 危险操作预警

UPDATE / DELETE 不带 WHERE 会作用于全表!养成习惯:先 SELECT 确认影响行数,再执行 UPDATE/DELETE。

执行前确认影响范围:

-- 先查
SELECT COUNT(*) FROM users WHERE age > 60;
-- 再删
DELETE FROM users WHERE age > 60;

排序与聚合

-- 排序
SELECT * FROM orders
ORDER BY created_at DESC;      -- 倒序(最新在前)

-- 多条件排序
SELECT * FROM products
ORDER BY category_id ASC, price DESC;

-- 聚合函数:COUNT / SUM / AVG / MAX / MIN
SELECT
    COUNT(*)          AS 订单数,
    SUM(amount)       AS 总金额,
    AVG(amount)       AS 平均金额,
    MAX(amount)       AS 最大单,
    MIN(amount)       AS 最小单
FROM orders;

函数	作用
`COUNT(*)`	行数
`SUM(col)`	求和
`AVG(col)`	平均值
`MAX / MIN`	最大 / 最小

分组 GROUP BY

分组统计是最常用的分析手段:GROUP BY 按某列分组,配合聚合函数统计每组:

-- 按城市统计用户数
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
ORDER BY cnt DESC;

-- 按分类统计销售额,只保留总额 > 1000 的分组
SELECT category_id, SUM(price * qty) AS total
FROM order_items
GROUP BY category_id
HAVING total > 1000;

-- WHERE 分组前过滤,HAVING 分组后过滤
SELECT status, COUNT(*)
FROM orders
WHERE created_at >= '2026-01-01'   -- 先过滤
GROUP BY status
HAVING COUNT(*) > 5;               -- 再分组过滤

⚠️ 注意

WHERE 在分组前过滤行,HAVING 在分组后过滤组,两者不能互换。

索引原理

索引好比书的目录:没有索引,MySQL 要全表扫描(逐行找);有索引,直接定位到目标行,速度提升几个数量级。

MySQL 默认使用 B+ 树索引,底层结构不用深究,记住它的特性:

等值查询、范围查询、排序都能加速
索引列越多,写入越慢(每次插入都要维护索引)
索引不是越多越好

💡 提示

用 EXPLAIN 看执行计划:EXPLAIN SELECT * FROM users WHERE email='x@y.com'; 观察 type 列:ALL 是全表扫描(危险),ref/range/const 说明用到了索引。

创建与管理索引

-- 建表时创建(前面已见)
-- 单独创建
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX uk_phone ON users(phone);   -- 唯一索引

-- 联合索引(最左前缀原则)
CREATE INDEX idx_city_age ON users(city, age);

-- 查看表的索引
SHOW INDEX FROM users;

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

最左前缀原则:联合索引 (city, age) 可以命中 WHERE city=... 或 WHERE city=... AND age=...,但单独 WHERE age=... 无法使用该索引。

✅ 建议

高频查询的 WHERE 列、JOIN 的关联列、ORDER BY 的列,是建立索引的首选目标。

慢查询优化套路

一条 SQL 变慢,按以下顺序排查:

问题	对策
全表扫描	给 WHERE / JOIN 列加索引
SELECT * 取多余列	只查需要的列
LIKE '%xx%' 前置通配	无法走索引,改用其他方案
函数包裹索引列	`WHERE DATE(created_at)='2026-01-01'` 会失效,改用范围: `WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'`
大表分页过深	用 `WHERE id > 10000 LIMIT 20` 替代 OFFSET

-- 深分页优化:游标式分页(快得多)
-- ❌ SELECT * FROM posts LIMIT 100000, 20;
-- ✅
SELECT * FROM posts
WHERE id > (SELECT id FROM posts ORDER BY id LIMIT 1 OFFSET 99999)
ORDER BY id LIMIT 20;

⚠️ 注意

优化前先用 EXPLAIN 看执行计划,不要凭感觉加索引;数据量小时先关注查询逻辑是否正确。

第 1 / 14 节