IT评测·应用市场-qidao123.com

标题: MySQL之高级查询学习笔记（四） [打印本页]
作者: 宁睿    时间: 2024-6-14 22:26
标题: 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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) Powered by Discuz! X3.4