MySQL之高级查询学习笔记（四）

数据库高级查询通常涉及复杂的查询需求，需要利用 SQL 的各种高级功能和语法来实现。以下是一些常见的数据库高级查询示例：

1. 聚合函数和 GROUP BY

求和、平均值、计数等：

SELECT SUM(salary) AS total_salary, AVG(age) AS average_age, COUNT(*) AS total_employees
FROM employees;

分组统计：

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

2. JOIN 操作

内连接：

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

外连接：

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

3. 子查询

子查询作为列：

SELECT product_name, (SELECT AVG(price) FROM products) AS average_price
FROM products;

子查询作为条件：

SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE customer_name = 'Alice');

4. 使用窗口函数

分区计算：

SELECT employee_id, salary, department_id, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_dept
FROM employees;

排名：

SELECT employee_id, salary, department_id,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

5. WITH 语句（公共表表达式）

WITH top_salaries AS (
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 10
)
SELECT * FROM top_salaries;

6. UNION 和 UNION ALL

SELECT employee_id, first_name FROM employees
UNION
SELECT customer_id, customer_name FROM customers;

7. 数据透视（PIVOT）

SELECT *
FROM (
    SELECT department_id, salary
    FROM employees
) AS source_table
PIVOT (
    AVG(salary) FOR department_id IN (101, 102, 103)
) AS pivot_table;

8. 动态 SQL

SET @sql = 'SELECT * FROM employees WHERE department_id = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @department_id;
DEALLOCATE PREPARE stmt;

这些是一些数据库高级查询的示例，涉及了聚合函数、GROUP BY、JOIN 操作、子查询、窗口函数、公共表表达式、UNION、PIVOT、动态 SQL 等各种高级 SQL 技巧。掌握这些技巧可以让你更有效地查询和分析数据库中的数据。