【数据库】MySQL：从基础到高级的SQL技巧

前言

上篇文章是对MySQL的简单介绍和基本操作，接下来会对MySQL数据查询最更进一步的讲解。

一、简单的数据查询

在 MySQL 中，字段查询是通过 SELECT 语句从表中检索某个或某些字段的数据。你可以通过指定字段名、条件、排序等来灵活查询表中的数据。以下是 MySQL 中常见的字段查询方式和操作示例：

（一）查询单个字段

如果只需要查询表中的某一个字段，可以在 SELECT 语句中指定该字段的名称。

语法：

SELECT 字段名 FROM 表名;

示例：

SELECT username FROM users;

此查询将从 users 表中查询所有用户的用户名。

（二）查询多个字段

可以同时查询多个字段，只需在 SELECT 语句中列出多个字段名，用逗号分隔。

语法：

SELECT 字段1, 字段2, ... FROM 表名;

示例：

SELECT username, email, age FROM users;

此查询将从 users 表中查询用户名、邮箱和年龄这三个字段的数据。

（三）查询所有字段

如果需要查询表中的所有字段，可以使用 * 来表示所有字段。

语法：

SELECT * FROM 表名;

示例：

SELECT * FROM users;

此查询将返回 users 表中的所有字段和所有数据。

（四）使用别名查询字段

使用 AS 关键字为字段指定别名，这在需要输出更加直观的结果时非常有用。

语法：

SELECT 字段名 AS 别名 FROM 表名;

示例：

SELECT username AS 用户名, email AS 邮箱 FROM users;

此查询将为 username 字段命名为 "用户名"，为 email 字段命名为 "邮箱"。

（五）带条件的字段查询

通过 WHERE 子句来筛选数据，返回符合条件的字段。

语法：

SELECT 字段1, 字段2 FROM 表名 WHERE 条件;

示例：

SELECT username, email FROM users WHERE city = 'Beijing';

此查询将返回 users 表中城市为 Beijing 的用户的用户名和邮箱。

（六）使用DISTINCT去重查询

如果一个字段中存在重复值，DISTINCT 可以用来查询唯一值，去除重复记录。

语法：

SELECT DISTINCT 字段名 FROM 表名;

示例：

SELECT DISTINCT city FROM users;

此查询将返回 users 表中唯一的城市名称。

（七）使用ORDER BY排序查询结果

通过 ORDER BY 子句对查询结果进行排序，默认是升序排序，使用 DESC 关键字可以实现降序排序。

语法：

SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC|DESC];

示例：

SELECT username, age FROM users ORDER BY age DESC;

此查询将按照年龄降序排序，并返回用户名和年龄。

（八）使用LIKE进行模糊查询

LIKE 关键字用于进行模糊查询，结合 % 和 _ 通配符使用。

• % 表示任意数量的字符。
• _ 表示单个字符。

语法：

SELECT 字段名 FROM 表名 WHERE 字段名 LIKE 模式;

示例：

SELECT username FROM users WHERE username LIKE 'john%';

此查询将返回用户名以 john 开头的所有用户。

（九）使用IN进行多值查询

IN 关键字用于筛选字段值等于多个给定值中的任意一个。

语法：

SELECT 字段名 FROM 表名 WHERE 字段名 IN (值1, 值2, ...);

示例：

SELECT username FROM users WHERE city IN ('Beijing', 'Shanghai');

（十）使用BETWEEN进行范围查询

BETWEEN 用于查询字段值在某个范围内的数据，通常用于数值或日期类型字段。

语法：

SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN 值1 AND 值2;

示例：

SELECT username, age FROM users WHERE age BETWEEN 20 AND 30;

此查询将返回年龄在 20 到 30 之间的用户。

（十一）限制查询结果数量LIMIT

LIMIT 用于限制查询返回的行数，特别适用于分页。

语法：

SELECT 字段名 FROM 表名 LIMIT 起始行, 行数;

示例：

SELECT username FROM users LIMIT 5;

（十二）条件查询与逻辑操作符

可以结合逻辑操作符 AND、OR 来进行更加复杂的条件查询。

示例：

SELECT username, email FROM users WHERE city = 'Beijing' AND age > 18;

二、聚合函数

MySQL的聚合函数用于对一组值进行计算，并返回单一的总结性结果。它们通常与 GROUP BY 子句一起使用，常用于统计数据和汇总结果。常见的聚合函数有以下几种：

（一）COUNT

• 功能: 计算满足条件的行数。
• 语法: COUNT(expression)

示例：

SELECT COUNT(*) FROM employees;

（二）SUM

• 功能: 计算一组值的总和，通常用于数值列。
• 语法: SUM(expression)

示例：

SELECT SUM(salary) FROM employees;

（三）AVG

• 功能: 计算一组数值的平均值。
• 语法: AVG(expression)

示例：

SELECT AVG(salary) FROM employees;

（四）MAX

• 功能: 返回指定列的最大值。
• 语法: MAX(expression)

示例：

SELECT MAX(salary) FROM employees;

（五）MIN

• 功能: 返回指定列的最小值。
• 语法: MIN(expression)

示例：

SELECT MIN(salary) FROM employees;

（六）GROUP_CONCAT

• 功能: 将来自多行的数据连接成一个字符串，通常用于组合多个记录。
• 语法: GROUP_CONCAT(expression [ORDER BY ...] [SEPARATOR str])

示例：

SELECT GROUP_CONCAT(name) FROM employees;

（七）VARIANCE 和 STDDEV

• 功能: VARIANCE() 计算方差，STDDEV() 计算标准差，通常用于统计分析。

示例：

SELECT VARIANCE(salary), STDDEV(salary) FROM employees;

三、分组查询

MySQL的分组查询（GROUP BY 查询）用于将结果集按一列或多列的值进行分组，并对每组数据应用聚合函数进行计算。分组查询通常与聚合函数一起使用，用于统计或总结数据。

（一）基本语法

SELECT column1, column2, ..., aggregate_function(column)
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column;

关键点：

1. GROUP BY: 指定按哪一列或多列进行分组。
2. 聚合函数: 对每组应用的函数，如 COUNT()、SUM()、AVG()、MAX()、MIN() 等。
3. HAVING: 用于过滤分组后的结果，类似 WHERE，但 WHERE 是在分组之前进行过滤，HAVING 是在分组之后应用条件。

（二）示例

（1）简单分组查询

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

• 功能: 按 department 列进行分组，统计每个部门的员工数量。

（2）使用多个列进行分组

SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;

• 功能: 按 department 和 job_title 进行多列分组，计算每个部门每个职位的平均薪资。

（3）使用 HAVING 进行过滤

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 5;

• 功能: 统计每个部门的员工数量，使用 HAVING 过滤出员工数大于 5 的部门。

（4）使用聚合函数和分组

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

• 功能: 按 department 分组，计算每个部门的总薪资，并按总薪资从高到低排序。

（5）与 WHERE 一起使用

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;

• 功能: 先使用 WHERE 过滤出入职日期在 2020 年 1 月 1 日之后的员工，再按 department 分组统计每个部门的员工数。

（三）注意事项

1. GROUP BY 子句中的列必须出现在 SELECT 语句中，除非它是聚合函数的参数。
2. WHERE 用于在分组之前过滤行，而 HAVING 用于在分组之后过滤组。
3. 当你使用 GROUP BY 时，不能在 SELECT 中选择未聚合的列，除非它们在 GROUP BY 子句中指定。

四、分页查询

分页查询用于从大数据集中按页获取指定数量的记录，这对于处理大量数据时非常常见，尤其是在网页或应用程序中显示多页数据时。分页查询主要通过 LIMIT 子句来实现。

（一）基本语法

SELECT column1, column2, ...
FROM table
LIMIT offset, row_count;

参数解释：

• offset: 要跳过的记录数（从第几条记录开始）。
• row_count: 要返回的记录数（即每页显示多少条记录）。

（二）示例

（1）获取前 10 条记录（第一页）

SELECT * 
FROM employees 
LIMIT 0, 10;

• 功能: 从 employees 表中返回前 10 条记录。这里 offset 为 0 表示从第一条记录开始。

（2）获取第 2 页的 10 条记录

SELECT * 
FROM employees 
LIMIT 10, 10;

• 功能: 返回从第 11 条记录开始的 10 条记录（即第 2 页的数据）。offset 为 10 表示跳过前 10 条记录，row_count 为 10 表示获取 10 条记录。

（3）使用分页和排序

SELECT * 
FROM employees 
ORDER BY hire_date DESC 
LIMIT 20, 10;

• 功能: 先按 hire_date 降序排序，再从第 21 条记录开始，获取 10 条记录（即第 3 页的数据）。

（三）计算分页参数

当需要显示分页数据时，通常需要通过页码来计算 offset。 例如，假设每页显示 10 条记录：

• 第 1 页: LIMIT 0, 10 (offset = (1 - 1) * 10 = 0)
• 第 2 页: LIMIT 10, 10 (offset = (2 - 1) * 10 = 10)
• 第 3 页: LIMIT 20, 10 (offset = (3 - 1) * 10 = 20)

（四）分页查询的优化

分页查询的优化主要从两个方面入手，一个是索引优化，另一个是减少offset的开销，详细如下：

（1）索引优化

大数据集分页时，尽量使用索引列进行排序和查询，如通过 ORDER BY 指定索引列，可以加快查询速度。

示例：

SELECT * 
FROM employees 
WHERE id > 100 
ORDER BY id 
LIMIT 10;

这种方式通过索引查找特定的 id 后直接获取分页数据，避免了从头遍历的性能开销。

（2）减少offset的开销

当 offset 非常大时，如 LIMIT 1000000, 10，查询性能可能会变慢。可以通过子查询或调整逻辑来优化大分页问题。

示例：

SELECT * 
FROM employees 
WHERE id > (SELECT id FROM employees ORDER BY id LIMIT 999990, 1) 
LIMIT 10;

这种方法避免了大 offset 带来的性能问题。

（五）注意事项

• offset 越大，查询越慢: 因为 MySQL 会跳过 offset 数量的行数，这意味着在大的数据集下，分页查询的性能会下降。
• 优化查询: 使用 WHERE 和合适的索引可以提升分页查询的性能，尤其是在处理大量数据时。

五、连接查询

MySQL的连接查询用于从多个表中查询相关数据。在数据库设计中，通常会把相关的数据分布在不同的表中，连接查询能够把这些表的数据组合起来，实现跨表查询。MySQL 支持几种类型的连接查询，常见的有内连接、外连接（左连接和右连接）、交叉连接等。

（一）内连接

内连接返回两个表中匹配的记录，只有在两个表中都有对应的匹配数据时才会返回结果。

语法：

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

示例：

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

• 功能: 查询员工及其所在的部门名称。只有当员工和部门表的 department_id 匹配时，才会返回结果

（二）左连接

左连接返回左表的所有记录，即使右表没有匹配的记录。对于没有匹配的右表记录，结果中对应的列会显示为 NULL。

语法：

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

（三）右连接

右连接返回右表的所有记录，即使左表没有匹配的记录。对于没有匹配的左表记录，结果中对应的列会显示为 NULL。

语法：

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

示例：

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

（四）全连接

MySQL 本身不直接支持 FULL OUTER JOIN，但可以通过使用 UNION 来模拟全连接。全连接返回两个表中所有的记录，不论是否有匹配。

语法：

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

• 功能: 查询所有员工和所有部门信息。包括那些没有匹配的员工或部门，未匹配的部分将显示为 NULL。

（五）交叉连接

交叉连接会返回两个表的笛卡尔积，即两个表中的每一条记录都会和另一个表的所有记录进行组合。除非有特殊需求，否则交叉连接通常会产生大量数据，不常使用。

语法：

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

示例：

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

• 功能: 返回所有员工和所有部门的所有可能组合，不管他们是否有实际关系。

（六）自连接

自连接是指在同一个表中进行连接查询，通常用于比较同一表中不同记录之间的关系。

语法：

SELECT a.column1, b.column2
FROM table a, table b
WHERE a.some_column = b.some_column;

示例：

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1，employees e2
ON e1.manager_id = e2.employee_id;

• 功能: 查询员工及其经理姓名。这里员工表通过自连接实现了员工与经理的对应关系。

（七）多表连接

MySQL 支持在一个查询中连接多个表，通过多个 JOIN 子句可以实现多表连接。

语法：

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column
LEFT JOIN table3 ON table2.column = table3.column;

示例：

SELECT e.name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
LEFT JOIN projects p ON e.project_id = p.project_id;

• 功能: 查询员工的姓名、部门名称及其参与的项目。如果某个员工没有对应的项目，项目名将显示为 NULL。

（八）注意事项

• 连接查询性能在大数据量时可能会受到影响，优化时可以通过添加索引来提高查询速度。
• 确保 ON 条件中的列有适当的匹配，以避免查询返回错误的数据集或产生过多的空值（NULL）。
• 在编写复杂连接查询时，应尽量简化表之间的关系，避免产生不必要的笛卡尔积。

六、总结

本篇文章对MySQL数据查询进行了详细讲解，从最基础的字段查询、条件查询、排序和分页，到更复杂的聚合函数、分组查询以及多表连接等操作，覆盖了MySQL查询中的常见场景和技巧。通过这些SQL语句的灵活使用，可以更加高效地进行数据检索、分析与处理，提升开发效率。掌握这些SQL查询技巧，可以为实际项目中的数据库操作奠定坚实基础，帮助开发者轻松应对复杂的数据操作需求。