AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

MySQL学习知识点（二）

2026-03-24 410
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 教程来源 https://app-acda5zfcddz5.appmiaoda.com MySQL核心数据操作（CRUD）与高级查询技巧：涵盖INSERT/SELECT/UPDATE/DELETE语法及变体（REPLACE、ON DUPLICATE KEY等）；JOIN连接、子查询、UNION、窗口函数（MySQL 8.0+）；索引类型、最左前缀原则、EXPLAIN执行计划分析及分页优化等实战要点。

四、数据操作（CRUD）

4.1 INSERT（插入数据）

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

-- 插入多条数据
INSERT INTO users (username, email, password, age) VALUES 
('李四', 'li@example.com', 'hashed_pwd', 30),
('王五', 'wang@example.com', 'hashed_pwd', 28);

-- 插入部分字段
INSERT INTO users (username, email, password) 
VALUES ('赵六', 'zhao@example.com', 'hashed_pwd');

-- 插入并返回自增ID
INSERT INTO users (username, email, password) VALUES ('test', 'test@example.com', 'pwd');
SELECT LAST_INSERT_ID();

-- REPLACE（如果主键存在则替换，否则插入）
REPLACE INTO users (id, username, email, password) 
VALUES (1, '新张三', 'new@example.com', 'new_pwd');

-- INSERT IGNORE（忽略重复键错误）
INSERT IGNORE INTO users (id, username, email, password) 
VALUES (1, '重复', 'dup@example.com', 'pwd');

-- INSERT ON DUPLICATE KEY UPDATE（存在则更新）
INSERT INTO users (id, username, email, password) 
VALUES (1, '更新', 'update@example.com', 'pwd')
ON DUPLICATE KEY UPDATE 
    username = VALUES(username),
    email = VALUES(email);

4.2 SELECT（查询数据）

-- 基础查询
SELECT * FROM users;
SELECT id, username, email FROM users;
SELECT username AS 用户名, email AS 邮箱 FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 20;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE username LIKE '张%';    -- 以张开头的
SELECT * FROM users WHERE username LIKE '%三';    -- 以三结尾的
SELECT * FROM users WHERE username LIKE '%三%';   -- 包含三的
SELECT * FROM users WHERE email IN ('a@b.com', 'c@d.com');
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE age IS NOT NULL;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 0;   -- 第1页
SELECT * FROM users LIMIT 10 OFFSET 10;  -- 第2页
SELECT * FROM users LIMIT 10, 10;        -- 另一种写法

-- 去重
SELECT DISTINCT age FROM users;

-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age), SUM(age) FROM users;

-- 分组查询
SELECT gender, COUNT(*) AS count, AVG(age) AS avg_age 
FROM users 
GROUP BY gender;

-- 分组后筛选
SELECT gender, COUNT(*) AS count 
FROM users 
GROUP BY gender 
HAVING count > 1;

4.3 UPDATE（更新数据）

-- 更新单条
UPDATE users SET age = 26 WHERE id = 1;

-- 更新多条
UPDATE users SET status = 'active' WHERE age >= 18;

-- 使用表达式更新
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
UPDATE users SET age = age + 1 WHERE birthday = CURDATE();

-- 多字段更新
UPDATE users 
SET username = '新名字', email = 'new@example.com' 
WHERE id = 1;

-- 限制更新行数
UPDATE users SET status = 'inactive' LIMIT 10;

4.4 DELETE（删除数据）

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users WHERE age > 60;

-- 删除所有记录
DELETE FROM users;
TRUNCATE TABLE users;  -- 更快

-- 限制删除行数
DELETE FROM users WHERE status = 'inactive' LIMIT 100;

-- 多表删除
DELETE u, o FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'deleted';

五、查询高级技巧

5.1 连接查询（JOIN）

-- 准备示例数据
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2)
);

-- INNER JOIN（内连接，只返回匹配的记录）
SELECT u.name, o.amount 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左连接，返回左表所有记录）
SELECT u.name, o.amount 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN（右连接，返回右表所有记录）
SELECT u.name, o.amount 
FROM users u 
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN（MySQL 不支持，用 UNION 模拟）
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN（笛卡尔积）
SELECT * FROM users CROSS JOIN orders;

-- 自连接（同一张表连接自己）
SELECT e.name AS employee, m.name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

-- 多表连接
SELECT u.name, o.order_date, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.id = 1;

5.2 子查询

-- WHERE 子句中的子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- IN 子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXISTS 子查询
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- FROM 子句中的子查询（派生表）
SELECT AVG(total_amount) 
FROM (
    SELECT user_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) AS user_totals;

-- SELECT 子句中的子查询（标量子查询）
SELECT name, 
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- 相关子查询
SELECT * FROM products p1
WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category);

5.3 联合查询（UNION）

-- UNION（去重）
SELECT name, email FROM users WHERE age < 18
UNION
SELECT name, email FROM users WHERE age > 60;

-- UNION ALL（不去重，效率更高）
SELECT user_id FROM orders WHERE amount > 1000
UNION ALL
SELECT user_id FROM orders WHERE amount < 100;

-- 实际应用：合并不同来源的数据
SELECT 'vip' AS type, name, email FROM vip_users
UNION ALL
SELECT 'normal', name, email FROM normal_users;

5.4 窗口函数（MySQL 8.0+）

-- ROW_NUMBER() - 行号
SELECT name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- RANK() - 排名（有并列）
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- DENSE_RANK() - 密集排名
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- LAG/LEAD - 前后行
SELECT name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary
FROM employees;

-- 聚合窗口函数
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

-- NTILE - 分桶
SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

六、索引与优化

6.1 索引类型

-- 普通索引
CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD INDEX idx_name(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 主键索引（特殊的唯一索引）
ALTER TABLE users ADD PRIMARY KEY (id);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content, title);

-- 空间索引
CREATE SPATIAL INDEX idx_location ON stores(location);

6.2 索引使用原则

-- 最左前缀原则
-- 索引 (a, b, c)
-- 能使用索引的查询：
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 不能使用索引的查询：
WHERE b = 2
WHERE c = 3
WHERE a = 1 AND c = 3

-- 索引失效的情况
-- 1. 使用函数
WHERE YEAR(created_at) = 2023  -- 改为 created_at BETWEEN '2023-01-01' AND '2023-12-31'

-- 2. 隐式类型转换
WHERE phone = 13800138000  -- phone 是 VARCHAR，改为字符串

-- 3. 使用 != 或 <>
WHERE status != 'active'  -- 索引效果差

-- 4. LIKE 以 % 开头
WHERE name LIKE '%张三'  -- 失效
WHERE name LIKE '张三%'  -- 有效

-- 5. OR 条件
WHERE age = 18 OR name = '张三'  -- 除非两个字段都有索引

-- 6. NOT IN 或 NOT EXISTS
WHERE id NOT IN (SELECT ...)

6.3 EXPLAIN 执行计划

-- 分析查询语句
EXPLAIN SELECT * FROM users WHERE age > 20;

-- 输出字段说明：
-- id: 执行顺序
-- select_type: 查询类型（SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION）
-- table: 表名
-- type: 访问类型（性能从高到低）
--   system > const > eq_ref > ref > range > index > ALL
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 索引长度
-- rows: 扫描行数
-- Extra: 额外信息（Using index, Using where, Using filesort, Using temporary）

-- 详细分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;

-- 优化建议
-- 1. 避免 ALL 全表扫描，至少达到 range 级别
-- 2. 注意 Extra 中的 Using filesort 和 Using temporary
-- 3. 关注 rows 扫描行数

6.4 查询优化技巧

-- 1. 只查询需要的字段
-- 错误
SELECT * FROM users;
-- 正确
SELECT id, name, email FROM users;

-- 2. 避免使用 SELECT DISTINCT，用 GROUP BY 替代
-- 错误
SELECT DISTINCT user_id FROM orders;
-- 正确
SELECT user_id FROM orders GROUP BY user_id;

-- 3. 使用 EXISTS 替代 IN
-- 当子查询结果集很大时
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 4. 优化分页查询
-- 深分页问题：LIMIT 100000, 10 很慢
-- 优化方案1：使用子查询
SELECT * FROM users 
WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
LIMIT 10;

-- 优化方案2：使用 JOIN
SELECT * FROM users 
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 100000, 10
) AS tmp ON users.id = tmp.id;

-- 5. 批量操作
-- 错误
for (id in ids) {
    UPDATE users SET status = 1 WHERE id = id;
}
-- 正确
UPDATE users SET status = 1 WHERE id IN (1,2,3,4,5);

-- 6. 使用覆盖索引
-- 索引包含查询所需的所有字段，避免回表
CREATE INDEX idx_name_age_email ON users(name, age, email);
SELECT name, age, email FROM users WHERE name = '张三';

-- 7. 批量插入优化
INSERT INTO users (name, email) VALUES 
('a', 'a@example.com'),
('b', 'b@example.com'),
-- ... 批量插入，减少事务开销

来源：
https://app-acda5zfcddz5.appmiaoda.com

文章标签：
关系型数据库
MySQL
索引
游客xp6ru7c63sdiu
目录
相关文章
无雨云
|
2月前
|
人工智能 安全 BI
以1挡100：一人公司终极效率工具，OpenClaw/Clawdbot 实用Skills 阿里云部署及优化实战指南
2026年，AI智能体的核心价值已从“能说会道”转向“落地执行”。OpenClaw（原名Clawdbot/Moltbot）作为开源AI助手的标杆，凭借“技能扩展+流程自动化”的核心优势，成为一人公司的效率革命利器——它不仅能替代重复劳动，更能通过技能组合搭建完整业务闭环，让单人效率媲美数十人团队。
无雨云
1104 3
sysin
|
25天前
|
安全 物联网 API
Windows 11 24H2 | 25H2 | 26H1 中文版、英文版 (x64、ARM64) 下载 (2026 年 3 月更新)
Windows 11, version 26H1 | 25H2 | 24H2 Enterprise Arm64 x64 (updated Mar 2026)
sysin
518 0
来一杯热Java
|
25天前
|
人工智能 Java iOS开发
【IDEA】IntelliJ IDEA 最新、最全快捷键指南（Windows + MacOS 完整版）
本指南整理IntelliJ IDEA 2024.3.7与2025.1.7双版本快捷键，覆盖Windows/macOS双平台，涵盖编辑、导航、调试、重构、Git、AI编码等17大类共300+高频快捷键，并附分层学习建议与2025.3统一版更新说明，助开发者高效进阶。
来一杯热Java
1031 2
浅浅33
|
11天前
|
SQL 关系型数据库 MySQL
MySQL InnoDB 锁机制全解：行锁 / 表锁 / 间隙锁 / 临键锁底层逻辑与死锁避坑指南
本文深入剖析MySQL InnoDB锁机制，直击高并发下90%性能瓶颈根源：锁认知偏差。详解事务隔离级别与索引结构两大前提，厘清行锁、间隙锁、临键锁本质及死锁成因，并提供索引设计、SQL编写、事务控制等生产级避坑实践，助开发者从原理到落地全面提升数据库稳定性与并发能力。
浅浅33
129 1
浅浅33
|
29天前
|
存储 SQL 关系型数据库
MySQL 索引底层彻底吃透：B + 树原理、聚簇索引机制与全场景优化指南
本文深入剖析MySQL InnoDB索引底层原理：从B+树为何成为最优选，到聚簇/二级索引机制、回表与覆盖索引；详解最左前缀、索引失效10大场景及根因；并给出分页优化、联合索引设计、ICP等生产级实战方案，助你真正知其所以然。
浅浅33
237 2
无雨云
|
2月前
|
人工智能 安全 JavaScript
不想上班？打造7×24小时智能AI助理帮干活：OpenClaw(Clawdbot)本地+阿里云上部署教程
2026年初，开源项目OpenClaw（曾用名Clawdbot、Moltbot）以破竹之势席卷全球开发者社区，GitHub星标短时间内飙升至14.5万以上，成为当下增长最快的开源项目之一。这款工具的核心革命在于打破了传统AI“只聊不做”的局限，通过本地部署获得系统级操作权限，能直接管理文件、控制应用、执行命令，真正实现从“AI参谋”到“数字员工”的转变。同时，阿里云为OpenClaw打造了专属部署方案，实现了云端稳定运行与大模型算力的无缝对接，让用户既能享受本地部署的隐私安全，也能拥有云端7×24小时不间断的服务能力。
无雨云
758 3
游客a226kmxu2jbvw
|
7月前
|
SQL 数据管理 BI
数据库操作三基石：DDL、DML、DQL 技术入门指南
本文围绕数据库操作核心语言 DDL、DML、DQL 展开入门讲解。DDL 作为 “结构建筑师”，通过CREATE（建库 / 表）、ALTER（修改表）、DROP（删除）等命令定义数据库结构；DML 作为 “数据管理员”，以INSERT（插入）、UPDATE（更新）、DELETE（删除）操作数据表记录，需搭配WHERE条件避免误操作；DQL 作为 “数据检索师”，通过SELECT结合WHERE、ORDER BY、LIMIT等子句实现数据查询与统计。三者相辅相成，是数据库操作的基础，使用时需注意 DDL 的不可撤销性、DML 的条件约束及 DQL 的效率优化，为数据库学习与实践奠定基础。
游客a226kmxu2jbvw
3429 0
完美少年
|
存储 数据库 数据安全/隐私保护
数据库模式
一、数据库模式 数据库模式（Database Schema）是指数据库中数据的逻辑结构和组织方式。它定义了数据库中的表、字段、关系和约束等元素，以及它们之间的关系和依赖关系。数据库模式描述了数据库的结构和组织方式，是数据库的蓝图或设计方案。 数据库模式包括以下几个方面： 1. 表结构：数据库模式定义了数据库中的表，包括表的名称、字段和数据类型等。每个表代表一个实体或关系，每个字段代表一个属性。 2. 主键和外键：数据库模式定义了表之间的关系，包括主键和外键的定义。主键是表中唯一标识记录的字段，外键是表中引用其他表主键的字段。 3. 约束：数据库模式定义了数据的约束条件，包括唯一约束、非空约束、
完美少年
529 0
卡尔特斯
|
前端开发 CDN
React 在 html 中 CDN 引入（包含useState、antd、axios ....）
React 在 html 中 CDN 引入（包含useState、antd、axios ....）
卡尔特斯
1863 0
游客h2vchmlwqitbk
|
JavaScript 前端开发
Notepad++如何格式化JS代码
Notepad++如何格式化JS代码
游客h2vchmlwqitbk
1676 1

热门文章

最新文章

  • 1
    教你用Python发现即将流失的客户（附代码、安装教程、学习资源）
  • 2
    什么是缓存击穿？
  • 3
    tensorflow：流程，概念和简单代码注释
  • 4
    阿里云CDN加速和全站加速DCDN区别及如何选择？
  • 5
    iGraph 2015双促复盘总结
  • 6
    阿里云学生服务器购买价格及学生免费服务器配置领取条件
  • 7
    SpringCloud Alibaba - Nacos 作为配置中心 & 读取Properties配置信息
  • 8
    各大在线地图，使用的经纬度坐标为什么都精确到小数点之后第六位？
  • 9
    加载exe的PE信息并输出相关PE信息的一段c++代码
  • 10
    RhinoMock入门（7）——Do，With和Record-playback
  • 1
    Hermes Agent纯免费部署教程：适合新手尝鲜，阿里云提供ECS免费部署Hermes Agent
    36
  • 2
    新手也能搭！阿里云轻量服务器一键部署 Hermes Agent，开源自进化 AI 智能体
    66
  • 3
    Hermes Agent 部署教程：阿里云轻量服务器新手0基础搭建自进化AI智能体，太简单了
    76
  • 4
    超详细！Hermes Agent 一键部署全流程指南，轻松上手不踩坑
    155
  • 5
    普通摄像头秒变“透视仪”：黎曼分形透镜如何让微弱瑕疵无处遁形（军工项目之外研究）
    50
  • 6
    SpringBoot对接黄金白银期货数据API
    50
  • 7
    Maven 本地仓库替代私仓配置指南
    36
  • 8
    【SpringSecurity新手村系列】（4）验证码功能实现
    36
  • 9
    【SpringSecurity新手村系列】（3）自定义登录页与表单认证
    30
  • 10
    当旅行规划开始理解礼仪边界，Agent协同与​D​М‌X​Α‌РΙ才有用武之地
    40

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    开通oss服务