【数据库】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;

关键点：

GROUP BY: 指定按哪一列或多列进行分组。
聚合函数: 对每组应用的函数，如 COUNT()、SUM()、AVG()、MAX()、MIN() 等。
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 分组统计每个部门的员工数。

（三）注意事项

GROUP BY 子句中的列必须出现在 SELECT 语句中，除非它是聚合函数的参数。
WHERE 用于在分组之前过滤行，而 HAVING 用于在分组之后过滤组。
当你使用 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查询技巧，可以为实际项目中的数据库操作奠定坚实基础，帮助开发者轻松应对复杂的数据操作需求。

标签：数据库 mysql

本文转载自: https://blog.csdn.net/2401_86688088/article/details/142169735
版权归原作者 易辰君 所有，如有侵权，请联系我们删除。

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

前言

一、简单的数据查询

（一）查询单个字段

（二）查询多个字段

（三）查询所有字段

（四）使用别名查询字段

（五）带条件的字段查询

（六）使用

（七）使用

（八）使用

（九）使用

（十）使用

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

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

二、聚合函数

（一）COUNT

（二）SUM

（三）AVG

（四）MAX

（五）MIN

（六）GROUP_CONCAT

（七）VARIANCE 和 STDDEV

三、分组查询

（一）基本语法

（二）示例

（1）简单分组查询

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

（3）使用 HAVING 进行过滤

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

（5）与 WHERE 一起使用

（三）注意事项

四、分页查询

（一）基本语法

（二）示例

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

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

（3）使用分页和排序

（三）计算分页参数

（四）分页查询的优化

（1）索引优化

（2）减少offset的开销

（五）注意事项

五、连接查询

（一）内连接

（二）左连接

（三）右连接

（四）全连接

（五）交叉连接

（六）自连接

（七）多表连接

（八）注意事项

六、总结

发表评论

“【数据库】MySQL：从基础到高级的SQL技巧”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航