当前位置：首页 > article >正文

MySQL 最新数据库查询指南

article 2025/1/15 0:13:55

MySQL 最新数据库查询指南

1. 引言

MySQL 是目前广泛使用的关系型数据库管理系统，其强大的查询功能让开发者能够高效地获取和操作数据。在这篇博客中，我们将深入探讨 MySQL 的查询功能，包括单表查询、多表查询、子查询、聚合查询、以及最新的 SQL 功能和优化实践。

2. 单表查询

2.1 基本查询

使用 SELECT 语句可以查询表中的所有数据或部分列的数据。

-- 查询所有列
SELECT * FROM employees;

-- 查询指定列
SELECT name, salary FROM employees;

2.2 条件查询

通过 WHERE 子句对数据进行条件筛选。

-- 查询工资大于 5000 的员工
SELECT * FROM employees WHERE salary > 5000;

-- 查询部门为 "销售部" 的员工
SELECT * FROM employees WHERE department = '销售部';

2.3 模糊查询

使用 LIKE 进行模式匹配。

-- 查询名字以 "张" 开头的员工
SELECT * FROM employees WHERE name LIKE '张%';

-- 查询包含 "华" 字的名字
SELECT * FROM employees WHERE name LIKE '%华%';

2.4 排序查询

使用 ORDER BY 对查询结果进行排序。

-- 按工资升序排列
SELECT name, salary FROM employees ORDER BY salary ASC;

-- 按入职日期降序排列
SELECT name, hire_date FROM employees ORDER BY hire_date DESC;

2.5 分页查询

使用 LIMIT 进行分页。

-- 查询第一页数据，每页 10 条记录
SELECT * FROM employees LIMIT 0, 10;

-- 查询第二页数据
SELECT * FROM employees LIMIT 10, 10;

3. 聚合查询

3.1 聚合函数

MySQL 提供了多个聚合函数来对数据进行统计。

-- 求平均工资
SELECT AVG(salary) FROM employees;

-- 求最大工资和最小工资
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;

-- 统计员工总数
SELECT COUNT(*) FROM employees;

-- 计算总工资
SELECT SUM(salary) FROM employees;

3.2 分组查询

使用 GROUP BY 进行数据分组，并结合聚合函数。

-- 按部门统计员工人数
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;

-- 按部门统计平均工资，并筛选平均工资大于 7000 的部门
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING avg_salary > 7000;

4. 多表查询

4.1 内连接查询

使用 INNER JOIN 获取多个表中匹配的数据。

-- 查询员工及其所在部门名称
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

4.2 外连接查询

左外连接（LEFT JOIN）：返回左表的所有记录和匹配的右表记录。
右外连接（RIGHT JOIN）：返回右表的所有记录和匹配的左表记录。

-- 查询所有员工及其部门（包括未分配部门的员工）
SELECT e.name, e.salary, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 查询所有部门及其员工（包括没有员工的部门）
SELECT e.name, e.salary, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

4.3 交叉连接查询

交叉连接会返回两张表的笛卡尔积。

-- 查询所有可能的员工和项目组合
SELECT e.name, p.project_name FROM employees e CROSS JOIN projects p;

5. 子查询

5.1 子查询作为条件

-- 查询工资高于平均工资的员工
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

5.2 子查询作为伪表

子查询的结果可以作为临时表使用。

-- 查询每个部门工资最高的员工
SELECT name, department, salary FROM (
  SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
  FROM employees
) AS ranked_employees WHERE rank = 1;

6. 联合查询与视图

6.1 联合查询

使用 UNION 合并多个查询结果。

-- 查询开发部和财务部的员工
SELECT * FROM employees WHERE department = '开发部'
UNION
SELECT * FROM employees WHERE department = '财务部';

6.2 创建视图

视图是一种虚拟表，用于简化查询。

-- 创建视图
CREATE VIEW high_salary_employees AS
SELECT name, salary, department FROM employees WHERE salary > 10000;

-- 使用视图查询
SELECT * FROM high_salary_employees;

7. MySQL 查询优化

7.1 使用索引

索引可以加快查询速度。

-- 创建索引
CREATE INDEX idx_department_id ON employees(department_id);

-- 查询
SELECT * FROM employees WHERE department_id = 3;

7.2 EXPLAIN 分析查询

使用 EXPLAIN 语句分析查询执行计划。

-- 查看查询执行计划
EXPLAIN SELECT * FROM employees WHERE salary > 5000;

7.3 避免不必要的全表扫描

确保 WHERE 子句中的字段已建立索引，以减少查询时间。

7.4 限制返回数据量

使用 LIMIT 限制返回的数据行数，以减少资源消耗。

8. MySQL 8.0 新特性

MySQL 8.0 为查询功能引入了许多新特性：

8.1 窗口函数

窗口函数允许对查询结果中的每一行进行聚合运算，而不会改变行数。

-- 查询每个部门员工的工资排名
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

8.2 JSON 数据类型支持

MySQL 支持 JSON 数据类型，便于存储和查询结构化数据。

-- 插入 JSON 数据
INSERT INTO orders (order_details) VALUES ('{"product": "手机", "quantity": 2}');

-- 查询 JSON 数据
SELECT order_details->>'$.product' AS product_name FROM orders;

9. 总结

MySQL 提供了强大的查询能力，包括简单查询、聚合查询、连接查询、子查询等多种方式。随着 MySQL 不断发展，新版本提供了如窗口函数、JSON 支持等新特性，使得查询功能更加灵活高效。在实际开发中，结合索引优化、EXPLAIN 分析等方法，可以进一步提升查询性能。希望这篇博客能帮助大家更好地掌握 MySQL 查询技术，提高开发效率。

查看全文

http://www.kler.cn/a/502863.html