效率工具
- 推荐一个程序员的常用工具网站,效率加倍嘎嘎好用:程序员常用工具
云服务器
- 云服务器限时免费领:轻量服务器2核4G
- 腾讯云:2核2G4M云服务器新老同享99元/年,续费同价
- 阿里云:2核2G3M的ECS服务器只需99元/年,续费同价
MySQL查询详解:单表查询、多表查询、分组查询、子查询
在现代应用程序中,数据库是存储和管理数据的核心,而MySQL作为一种广泛使用的关系型数据库管理系统,提供了强大的查询功能。本文将详细探讨MySQL中的单表查询、多表查询、分组查询和子查询,帮助程序员掌握这些关键技能。
一、单表查询
单表查询是指在一个表中检索数据。基本的单表查询操作包括SELECT、WHERE、ORDER BY和LIMIT等。
1.1 SELECT语句
SELECT语句用于从表中检索数据。以下是一个基本的SELECT语句示例:
SELECT * FROM employees;
该语句从employees
表中检索所有列的数据。我们还可以选择特定的列:
SELECT first_name, last_name, salary FROM employees;
1.2 WHERE子句
WHERE子句用于筛选满足特定条件的记录。以下示例显示薪水大于50000的员工:
SELECT first_name, last_name, salary FROM employees WHERE salary > 50000;
可以使用多个条件组合:
SELECT first_name, last_name, salary FROM employees WHERE salary > 50000 AND department_id = 1;
1.3 ORDER BY子句
ORDER BY子句用于对结果集进行排序。以下示例按薪水从高到低排序:
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC;
1.4 LIMIT子句
LIMIT子句用于限制返回的记录数量。以下示例返回前5条记录:
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 5;
二、多表查询
多表查询是指在多个表中检索和合并数据。常用的多表查询包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。
2.1 INNER JOIN
INNER JOIN返回两个表中匹配的记录。以下示例将employees
表和departments
表按department_id
进行内连接:
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
2.2 LEFT JOIN
LEFT JOIN返回左表中的所有记录,以及右表中匹配的记录。以下示例返回所有员工及其所属的部门信息,如果没有部门,则显示NULL:
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
2.3 RIGHT JOIN
RIGHT JOIN返回右表中的所有记录,以及左表中匹配的记录。以下示例返回所有部门及其员工信息,如果没有员工,则显示NULL:
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
2.4 FULL JOIN
MySQL不直接支持FULL JOIN,可以通过UNION实现。以下示例返回所有员工和部门信息,包括没有匹配的记录:
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
三、分组查询
分组查询用于将结果集按一个或多个列进行分组,通常与聚合函数一起使用,如COUNT、SUM、AVG、MAX和MIN。
3.1 GROUP BY子句
GROUP BY子句用于将结果集按一个或多个列分组。以下示例按部门统计员工人数:
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
3.2 HAVING子句
HAVING子句用于筛选分组后的结果集,通常与GROUP BY一起使用。以下示例筛选出员工人数大于10的部门:
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
3.3 聚合函数
聚合函数用于计算一组值并返回单个值。以下示例展示了几个常用的聚合函数:
- 计算每个部门的平均薪水:
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
- 计算每个部门的最高薪水:
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;
四、子查询
子查询是嵌套在另一个查询中的查询,通常用于复杂的查询场景。子查询可以返回单个值、行或表。
4.1 标量子查询
标量子查询返回单个值。以下示例返回薪水最高的员工的名字:
SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
4.2 行子查询
行子查询返回单行数据。以下示例返回薪水最高的员工的详细信息:
SELECT first_name, last_name, salary
FROM employees
WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);
4.3 表子查询
表子查询返回一个表。以下示例返回所有薪水高于平均薪水的员工:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
4.4 关联子查询
关联子查询依赖于外部查询中的值。以下示例返回每个部门中薪水高于部门平均薪水的员工:
SELECT e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
五、总结
通过本篇文章,我们详细探讨了MySQL中的单表查询、多表查询、分组查询和子查询。无论是简单的单表查询,还是复杂的多表关联查询、分组统计和嵌套子查询,这些技术都是构建强大数据库应用的基石。