告别低效查询！掌握数据库高级查询的必学技巧

几种常见的高级查询方式

1. 子查询（Subquery）

子查询是嵌套在另一个查询中的查询，可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中使用。子查询的结果可以用于主查询的条件判断。

示例：
比如有两个表：employees（员工表）和 departments（部门表）。

-- employees 表结构
-- +----+--------+-----------+
-- | id | name   | dept_id   |
-- +----+--------+-----------+
-- | 1  | Alice  | 1         |
-- | 2  | Bob    | 2         |
-- | 3  | Carol  | 1         |
-- +----+--------+-----------+

-- departments 表结构
-- +----+-----------+
-- | id | dept_name |
-- +----+-----------+
-- | 1  | HR        |
-- | 2  | IT        |
-- +----+-----------+

-- 查询属于 HR 部门的所有员工
SELECT name 
FROM employees 
WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'HR');

2. 联接（Join）

联接用于从两个或多个表中根据它们之间的相关列进行数据检索。常见的联接类型有内联接（INNER JOIN）、左联接（LEFT JOIN）、右联接（RIGHT JOIN）和全外联接（FULL JOIN）。

示例：

-- 查询所有员工及其所属的部门名称
SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

3. 集合操作（Set Operations）

集合操作包括 UNION、INTERSECT 和 EXCEPT，用于组合两个或多个查询的结果集。

示例：
比如有两个表：employees_us 和 employees_uk，存储在不同国家员工。

-- employees_China 表结构
-- +----+--------+
-- | id | name   |
-- +----+--------+
-- | 1  | Alice  |
-- | 2  | Bob    |
-- +----+--------+

-- employees_uk 表结构
-- +----+--------+
-- | id | name   |
-- +----+--------+
-- | 3  | Carol  |
-- | 4  | Dave   |
-- +----+--------+

-- 查询所有员工，不论其所属国家
SELECT name FROM employees_China
UNION
SELECT name FROM employees_uk;

4. 视图（View）

视图是基于一个或多个表的 SQL 查询结果集，是一种虚拟表。视图提供了一种安全机制，允许用户只访问特定数据，而无需直接操作底层表。

示例：

-- 创建一个视图，只展示员工的姓名和部门
CREATE VIEW employee_details AS
SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

-- 查询视图中的数据
SELECT * FROM employee_details;

5. 分组和聚合（Group By and Aggregate Functions）

GROUP BY 子句用于将数据按一个或多个列进行分组，通常与聚合函数（如 COUNT、SUM、AVG、MAX、MIN）一起使用。

示例：

-- 查询每个部门的员工数量
SELECT departments.dept_name, COUNT(employees.id) as employee_count
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id
GROUP BY departments.dept_name;

6. 窗口函数（Window Functions）

窗口函数在一组相关行上执行计算，并保留详细行的返回结果。常见的窗口函数包括 ROW_NUMBER、RANK、DENSE_RANK 和 OVER 子句。

示例：

-- 对每个部门的员工进行编号
SELECT name, dept_id,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY name) as row_num
FROM employees;

7. CTE（公用表表达式，Common Table Expression）

CTE 是一个命名的临时结果集，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中使用。CTE 通过 WITH 子句定义，通常用于提高复杂查询的可读性。

示例：

-- 使用 CTE 查询员工和其部门名称
WITH employee_cte AS (
  SELECT employees.name, departments.dept_name
  FROM employees
  INNER JOIN departments ON employees.dept_id = departments.id
)
SELECT * FROM employee_cte;

8. 递归查询

CTE（公用表表达式）是一种临时结果集，可以在一个查询中多次引用。递归查询是一种特殊的 CTE，可以用于查询层次结构数据，例如组织结构图或目录结构。

示例：

-- 示例：使用递归查询计算从 1 到 10 的累加和
WITH RECURSIVE NumberCTE AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM NumberCTE
    WHERE Number < 10
)
SELECT SUM(Number) AS TotalSum FROM NumberCTE;

9. Window Function（窗口函数）

窗口函数在一组相关行上执行计算，并保留详细行的返回结果。常见的窗口函数包括 ROW_NUMBER、RANK、DENSE_RANK 和 OVER 子句。

示例：

-- 示例：为每个部门的员工分配一个排名
SELECT name, dept_id,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY name) as row_num
FROM employees;

10. 透视表（Pivot）

透视表用于将行数据转换为列数据，以便更好地进行数据分析。

示例：
比如有一个销售数据表 sales，包含 month（月份）、product（产品）和 amount（销售额）列。

-- 示例：将每个月的销售数据透视为列
SELECT product,
       SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
       SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
       SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
FROM sales
GROUP BY product;

11. Double Not Exists

双重 NOT EXISTS 查询用于查找在一个集合中存在但在另一个集合中不存在的记录。这通常用于查找不匹配的记录。

示例：
比如有两个表：employees（员工表）和 departments（部门表）。

-- 示例：查找所有不在任何部门的员工
SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE e.dept_id = d.id
)
AND NOT EXISTS (
    SELECT 1 FROM projects p WHERE e.id = p.employee_id
);

12. 写入和冲突（Insert and Conflict）

处理写入数据时的冲突是数据库管理中的一个重要方面，特别是在多用户环境中。许多数据库管理系统提供了处理冲突的方法，例如 PostgreSQL 的 ON CONFLICT 子句和 MySQL 的 INSERT IGNORE。

示例（PostgreSQL）：

-- 示例：在插入数据时处理冲突
INSERT INTO employees (id, name, dept_id)
VALUES (1, 'Alice', 1)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
dept_id = EXCLUDED.dept_id;

13. 事务（Transactions）

事务是一组操作的集合，这些操作要么全部执行，要么全部不执行。事务提供了四个特性（ACID）：原子性、一致性、隔离性和持久性。

示例：

-- 示例：在事务中执行多个操作
BEGIN TRANSACTION;

-- 插入一条新员工记录
INSERT INTO employees (id, name, dept_id) VALUES (4, 'Dave', 3);

-- 更新部门信息
UPDATE departments SET dept_name = 'Marketing' WHERE id = 3;

-- 如果所有操作成功，则提交事务
COMMIT;

-- 如果有任何操作失败，则回滚事务
ROLLBACK;

本文由mdnice多平台发布