一文掌握MySQL：基础、进阶与优化实战

一文掌握MySQL：基础、进阶与优化实战

在当今数字化时代，数据库技术已经成为软件开发和数据管理的核心技能之一。作为全球最流行的开源关系型数据库管理系统，MySQL凭借其开源免费、跨平台支持、高性能、可靠性、灵活的存储引擎、易用性以及扩展性等特点，广泛应用于各种Web应用程序和网站。掌握MySQL不仅能够让你在技术领域游刃有余，还能为你的职业发展打开新的大门。

无论你是刚刚接触数据库的新手，还是希望提升技能的开发者，通过实战演练是掌握MySQL的最佳途径。本文将带你从零基础开始，逐步进阶到高级应用，通过一系列精心设计的练习题，帮助你全面提升MySQL的使用和优化能力。

MySQL入门指南

在开始实战之前，让我们先了解一些MySQL的基础知识。MySQL使用结构化查询语言（SQL）进行数据的管理和操作。SQL是一种专门用于数据库管理的标准计算机语言，主要用于数据的查询、更新和管理。

如果你是完全的初学者，建议先从以下几个教程快速入门：

• 21分钟MySQL入门教程：https://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html
• MySQL中文文档：https://www.mysqlzh.com
• MySQL入门教程：https://www.w3cschool.cn/mysql/mysql-tutorial.html
• MySQL基础快速入门：https://mp.weixin.qq.com/s/pnbI7xSDk7_hQMLgVryPaA

基础操作练习

让我们从一些简单的SQL查询开始，熟悉基本的数据库操作。

1. 创建数据库和表

   首先，我们需要创建一个数据库和表。假设我们要创建一个学生信息管理系统，包含学生表和课程表。

   CREATE DATABASE school;
   USE school;
   
   CREATE TABLE students (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100),
       age INT,
       gender ENUM('M', 'F')
   );
   
   CREATE TABLE courses (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100),
       teacher VARCHAR(100)
   );
   

2. 插入数据

   接下来，我们向表中插入一些数据。

   INSERT INTO students (name, age, gender) VALUES
   ('Alice', 18, 'F'),
   ('Bob', 19, 'M'),
   ('Charlie', 20, 'M');
   
   INSERT INTO courses (name, teacher) VALUES
   ('Math', 'Mr. Smith'),
   ('English', 'Mrs. Johnson'),
   ('Science', 'Mr. Brown');
   

3. 查询数据

   现在，我们可以尝试一些基本的查询操作。

   SELECT * FROM students;
   SELECT * FROM courses WHERE teacher = 'Mr. Smith';
   

4. 更新和删除数据

   数据库中的数据是动态的，我们需要学会如何更新和删除数据。

   UPDATE students SET age = 21 WHERE name = 'Charlie';
   DELETE FROM courses WHERE name = 'Science';
   

推荐学习资源

对于想要系统学习MySQL的读者，以下几本书籍非常值得参考：

• 《SQL学习指南》：内容循序渐进，涵盖SQL基本查询、过滤、多数据表查询、集合、数据操作、分组和聚合、子查询、连接、条件逻辑、事务、索引和约束、视图等内容。
• 《MySQL是怎样使用的》：从零基础开始，详细介绍了MySQL的服务器程序和客户端程序的使用、数据类型、数据库和表的基本操作、列的属性、表达式和函数、简单和复杂的增删改查语句等入门知识。
• 《MySQL是怎样运行的》：采用诙谐幽默的表达方式，深入介绍了MySQL的底层运行原理，包括记录、页面、索引、表空间的结构和用法，单表查询、连接查询的执行原理，事务概念的来源，MySQL如何实现事务等核心概念。

掌握了基础操作后，让我们进入更复杂的查询和实战演练阶段。这个阶段将重点介绍多表连接、子查询、窗口函数等高级SQL技巧，并通过具体的练习题来巩固这些知识。

多表连接查询

多表连接是SQL查询中非常重要的一个概念，它允许我们从多个表中获取数据。常见的连接类型包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。

假设我们有以下两个表：

• students 表：包含学生信息
• scores 表：包含学生的成绩信息

CREATE TABLE scores (
    student_id INT,
    course VARCHAR(100),
    score INT
);

INSERT INTO scores VALUES
(1, 'Math', 90),
(1, 'English', 85),
(2, 'Math', 88),
(2, 'Science', 92);

1. INNER JOIN：返回两个表中满足连接条件的记录。

   SELECT students.name, scores.course, scores.score
   FROM students
   INNER JOIN scores ON students.id = scores.student_id;
   

2. LEFT JOIN：返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则返回NULL。

   SELECT students.name, scores.course, scores.score
   FROM students
   LEFT JOIN scores ON students.id = scores.student_id;
   

3. RIGHT JOIN：与LEFT JOIN相反，返回右表中的所有记录。

4. FULL JOIN：返回两个表中的所有记录，如果没有匹配的记录，则返回NULL。

子查询

子查询是在一个查询中嵌套另一个查询。子查询可以作为条件的一部分，也可以作为数据源。

1. 作为条件的子查询

   SELECT name
   FROM students
   WHERE id IN (SELECT student_id FROM scores WHERE score > 90);
   

2. 作为数据源的子查询

   SELECT s.name, AVG(sc.score) AS average_score
   FROM students s
   JOIN (SELECT student_id, AVG(score) AS score FROM scores GROUP BY student_id) sc
   ON s.id = sc.student_id
   GROUP BY s.name;
   

窗口函数

窗口函数允许我们在查询结果集中进行更复杂的计算，如排名、累计和移动平均等。

1. RANK()：为每一行分配一个排名

   SELECT student_id, course, score,
       RANK() OVER (PARTITION BY student_id ORDER BY score DESC) AS rank
   FROM scores;
   

2. LEAD() 和 LAG()：访问当前行的前一行或后一行

   SELECT student_id, course, score,
       LEAD(score, 1) OVER (PARTITION BY student_id ORDER BY course) AS next_score
   FROM scores;
   

实战练习题

1. 连续3天登录用户

   给定一个用户登录表user_login，包含user_id和login_date两个字段，找出所有连续3天登录的用户。

   SELECT DISTINCT user_id
   FROM (
       SELECT user_id, login_date,
           LEAD(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS lead_date
       FROM user_login
   ) t
   WHERE DATEDIFF(lead_date, login_date) = 2;
   

2. 品类销售商品的种类数及销量最高的商品

   给定商品销售表order_detail、商品信息表sku_info和品类信息表category_info，查询每个品类的销售商品种类数及销量最高的商品。

   SELECT category_id, category_name, sku_id, name, order_num, sku_cnt
   FROM (
       SELECT od.sku_id, sku.name, sku.category_id, cate.category_name, order_num,
           RANK() OVER (PARTITION BY sku.category_id ORDER BY order_num DESC) AS rk,
           COUNT(DISTINCT od.sku_id) OVER (PARTITION BY sku.category_id) AS sku_cnt
       FROM (
           SELECT sku_id, SUM(sku_num) AS order_num
           FROM order_detail
           GROUP BY sku_id
       ) od
       LEFT JOIN sku_info sku ON od.sku_id = sku.sku_id
       LEFT JOIN category_info cate ON sku.category_id = cate.category_id
   ) t1
   WHERE rk = 1;
   

3. 推荐朋友收藏的商品

   给定好友关系表friendship_info和收藏表favor_info，向所有用户推荐其朋友收藏但自己未收藏的商品。

   SELECT DISTINCT t1.user_id, friend_favor.sku_id
   FROM (
       SELECT user1_id AS user_id, user2_id AS friend_id
       FROM friendship_info
       UNION
       SELECT user2_id, user1_id
       FROM friendship_info
   ) t1
   LEFT JOIN favor_info friend_favor ON t1.friend_id = friend_favor.user_id
   LEFT JOIN favor_info user_favor ON t1.user_id = user_favor.user_id AND friend_favor.sku_id = user_favor.sku_id
   WHERE user_favor.sku_id IS NULL;
   

随着数据量的增长，数据库性能优化变得至关重要。本节将通过实际案例，介绍索引优化、查询优化等关键技巧。

索引优化

索引是提高数据库查询性能的关键。通过创建合适的索引，可以显著加快查询速度。

1. 创建索引

   假设我们有一个包含500万条数据的用户表user，包含id、user_id、user_name、phone、lan_id、region_id和create_time等字段。我们需要优化以下查询：

   SELECT * FROM `user` WHERE phone = '59207212709' AND lan_id = '787161448' AND region_id = '938860528';
   

   初始查询时间超过1秒，通过EXPLAIN分析发现使用了全表扫描。我们可以通过创建联合索引来优化：

   ALTER TABLE `user` ADD INDEX idx_phone_lan_region(phone, lan_id, region_id);
   

   优化后查询时间降至0.025秒，提升了108倍。

2. 索引优化要点

   • 最左前缀法则：复合索引的顺序要按照建立时的顺序，从左到右使用。
   • 避免索引失效：不要对索引列进行计算、函数调用或类型转换。
   • 范围查询：索引不要放在范围查询的右边。

查询优化

除了索引优化，合理的查询设计也是提升性能的关键。

1. 避免全表扫描

   尽量使用索引列作为查询条件，减少扫描的行数。

2. 选择合适的数据类型

   使用更小的数据类型可以减少存储空间，提高查询效率。

3. 使用LIMIT

   限制结果集大小，减少数据传输时间。

4. 避免在WHERE子句中使用函数

   函数会阻止索引的使用，尽量在查询外部处理数据转换。

实战案例：同时在线人数统计

假设我们有一个用户登录详情表user_login_detail，包含login_ts和logout_ts两个时间戳字段，我们需要统计同时在线最多的人数。

-- 登录标记1 下线标记-1
SELECT login_ts AS l_time, 1 AS flag
FROM user_login_detail
UNION
SELECT logout_ts AS l_time, -1 AS flag
FROM user_login_detail;

-- 按照时间求和
SELECT SUM(flag) OVER (ORDER BY l_time) AS sum_l_time
FROM (
    SELECT login_ts AS l_time, 1 AS flag
    FROM user_login_detail
    UNION
    SELECT logout_ts AS l_time, -1 AS flag
    FROM user_login_detail
) t1;

-- 拿到最大值 就是同时在线最多人数
SELECT MAX(sum_l_time)
FROM (
    SELECT SUM(flag) OVER (ORDER BY l_time) AS sum_l_time
    FROM (
        SELECT login_ts AS l_time, 1 AS flag
        FROM user_login_detail
        UNION
        SELECT logout_ts AS l_time, -1 AS flag
        FROM user_login_detail
    ) t1
) t2;

通过以上实战演练，相信你已经掌握了MySQL从基础到高级的使用技巧。记住，理论知识固然重要，但真正的掌握来自于不断的实践和探索。希望你能将所学知识应用到实际项目中，不断挑战自己，成为一名MySQL高手！