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

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

求和、平均值、计数等：

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

复制代码

分组统计：

1. SELECT department_id, COUNT(*) AS total_employees
2. FROM employees
3. GROUP BY department_id;

复制代码

2. JOIN 操纵

内连接：

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

复制代码

外连接：

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

复制代码

3. 子查询

子查询作为列：

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

复制代码

子查询作为条件：

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

复制代码

4. 使用窗口函数

分区盘算：

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

复制代码

排名：

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

复制代码

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

1. WITH top_salaries AS (
2.     SELECT employee_id, salary
3.     FROM employees
4.     ORDER BY salary DESC
5.     LIMIT 10
6. )
7. SELECT * FROM top_salaries;

复制代码

6. UNION 和 UNION ALL

1. SELECT employee_id, first_name FROM employees
2. UNION
3. SELECT customer_id, customer_name FROM customers;

复制代码

7. 数据透视（PIVOT）

1. SELECT *
2. FROM (
3.     SELECT department_id, salary
4.     FROM employees
5. ) AS source_table
6. PIVOT (
7.     AVG(salary) FOR department_id IN (101, 102, 103)
8. ) AS pivot_table;

复制代码

8. 动态 SQL

1. SET @sql = 'SELECT * FROM employees WHERE department_id = ?';
2. PREPARE stmt FROM @sql;
3. EXECUTE stmt USING @department_id;
4. DEALLOCATE PREPARE stmt;

复制代码

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。