# MySQL中常用的查询子句有哪些
## 引言
MySQL作为最流行的关系型数据库管理系统之一，其强大的查询功能是开发者日常工作中不可或缺的工具。掌握各种查询子句的使用方法，能够帮助我们更高效地从数据库中提取、分析和处理数据。本文将详细介绍MySQL中常用的查询子句，包括它们的基本语法、使用场景以及实际示例。
---
## 1. SELECT子句
### 1.1 基本语法
```sql
SELECT column1, column2, ...
FROM table_name;
*选择所有列-- 选择特定列
SELECT first_name, last_name FROM employees;
-- 使用列别名
SELECT product_name AS name, unit_price * 0.9 AS discount_price FROM products;
-- 计算字段
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
FROM table_name [AS alias]
-- 单表查询
SELECT * FROM orders;
-- 使用表别名
SELECT e.employee_id, e.last_name
FROM employees AS e;
-- 多表连接
SELECT o.order_id, c.customer_name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
WHERE condition
-- 简单条件
SELECT * FROM products WHERE price > 100;
-- 组合条件
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 5000;
-- 使用IN操作符
SELECT * FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');
-- 模糊查询
SELECT * FROM products
WHERE product_name LIKE '%Laptop%';
GROUP BY column1, column2, ...
-- 简单分组
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- 多列分组
SELECT country, city, COUNT(*) AS customer_count
FROM customers
GROUP BY country, city;
-- 配合HAVING使用
SELECT product_id, SUM(quantity) AS total_sold
FROM order_details
GROUP BY product_id
HAVING total_sold > 100;
HAVING condition
-- 过滤分组结果
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 5;
-- 复杂条件
SELECT product_category, AVG(price) AS avg_price
FROM products
GROUP BY product_category
HAVING avg_price > 100 AND COUNT(*) > 10;
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
-- 简单排序
SELECT * FROM products ORDER BY price DESC;
-- 多列排序
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY department ASC, hire_date DESC;
-- 使用表达式排序
SELECT product_name, unit_price * units_in_stock AS stock_value
FROM products
ORDER BY stock_value DESC;
LIMIT [offset,] row_count
-- 限制返回行数
SELECT * FROM customers LIMIT 10;
-- 分页查询
SELECT * FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40; -- 等价于LIMIT 40, 20
-- 与ORDER BY配合使用
SELECT * FROM orders
WHERE order_date > '2023-01-01'
ORDER BY total_amount DESC
LIMIT 5;
FROM table1
[JOIN_TYPE] JOIN table2
ON table1.column = table2.column
-- 内连接
SELECT o.order_id, c.customer_name
FROM orders o INNER JOIN customers c
ON o.customer_id = c.customer_id;
-- 左外连接
SELECT e.employee_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
-- 多表连接
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;
SELECT_statement1
UNION [ALL]
SELECT_statement2
-- 合并两个查询结果
SELECT product_id, product_name FROM current_products
UNION
SELECT product_id, product_name FROM discontinued_products;
-- 使用UNION ALL提高性能(当确定无重复时)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
SELECT * FROM table1
WHERE column1 IN (SELECT column1 FROM table2)
-- WHERE中的子查询
SELECT * FROM products
WHERE category_id IN (
SELECT category_id FROM categories
WHERE category_name LIKE '%Electronics%'
);
-- FROM中的派生表
SELECT avg_sales.category, avg_sales.avg_amount
FROM (
SELECT category, AVG(sales_amount) AS avg_amount
FROM sales_data
GROUP BY category
) AS avg_sales
WHERE avg_sales.avg_amount > 1000;
-- 相关子查询
SELECT e.employee_id, e.last_name
FROM employees e
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.employee_id = e.employee_id
AND o.order_date > '2023-01-01'
);
function_name OVER (
[PARTITION BY partition_expression, ...]
[ORDER BY sort_expression [ASC|DESC], ...]
[frame_clause]
)
-- 计算移动平均
SELECT
order_date,
daily_sales,
AVG(daily_sales) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM sales_data;
-- 部门内薪资排名
SELECT
employee_id,
last_name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;
WITH cte_name AS (
SELECT_statement
)
SELECT * FROM cte_name;
-- 简单CTE
WITH high_value_customers AS (
SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 10000
)
SELECT c.customer_name, h.total_spent
FROM customers c JOIN high_value_customers h
ON c.customer_id = h.customer_id;
-- 递归CTE(查询组织结构)
WITH RECURSIVE org_hierarchy AS (
-- 基础查询(顶级管理者)
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归查询(下属员工)
SELECT e.employee_id, e.name, e.manager_id, h.level + 1
FROM employees e JOIN org_hierarchy h
ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy
ORDER BY level, employee_id;
MySQL提供了丰富的查询子句，从基础的SELECT、WHERE到高级的窗口函数和CTE，能够满足各种复杂的数据查询需求。掌握这些子句的用法和适用场景，可以显著提高数据库查询的效率和灵活性。在实际应用中，我们常常需要组合使用多个子句来构建复杂的查询语句，因此理解每个子句的执行顺序和作用范围尤为重要。
通过合理运用这些查询子句，我们可以从MySQL数据库中高效地提取、转换和分析数据，为业务决策提供有力支持。 “`
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
