Structured Query

SQL（Structured Query Language）提供了多种高级查询方法，允许用户执行复杂数据检索和操作

1. 连接（JOIN）：
   • 用于结合两个或多个表中的行，基于相关的列。
   • 常用的JOIN类型包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。

-- 内连接
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;

-- 左连接
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;

-- 右连接
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;

-- 全外连接
SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_column = b.common_column;

1. 子查询（Subquery）：
   • 一个查询嵌套在另一个查询中。
   • 可以在SELECT、INSERT、UPDATE或DELETE语句中使用。

-- 在SELECT中使用子查询
SELECT * FROM table1 WHERE column1 = (SELECT MAX(column1) FROM table2);

-- 在FROM中使用子查询（派生表/内嵌选择）
SELECT * FROM (SELECT column1 FROM table2) AS subquery;

1. 聚合函数（Aggregate Functions）：
   • 对一组值执行计算并返回单一值。
   • 包括SUM、AVG、MIN、MAX、COUNT等。

SELECT COUNT(*), AVG(column1), MAX(column2), MIN(column3)
FROM table1
WHERE condition;

1. 窗口函数（Window Functions）：
   • 对数据集的一个子集执行计算，并返回多个行。
   • 包括ROW_NUMBER、RANK、DENSE_RANK、NTILE等。

-- 使用窗口函数计算排名
SELECT column1, column2,
       RANK() OVER (ORDER BY column1 DESC) AS rank
FROM table1;

1. 公用表表达式（Common Table Expressions, CTEs）：
   • 定义了一个临时的结果集，可以由一个SELECT或INSERT、UPDATE、DELETE语句引用。

WITH cte_name (column1, column2) AS (
    SELECT column1, column2 FROM table1 WHERE condition
)
SELECT * FROM cte_name;

1. 递归CTE：
   • 允许递归地定义CTE，用于查询层次或递归数据结构。

WITH RECURSIVE cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

1. 分组（GROUP BY）：
   • 将结果集按照一个或多个列进行分组，并使用聚合函数对每个组进行计算。

SELECT column1, SUM(column2)
FROM table1
GROUP BY column1;

1. HAVING：
   • 用于过滤分组后的结果，类似于WHERE，但是用于聚合函数。

SELECT column1, AVG(column2) AS average
FROM table1
GROUP BY column1
HAVING AVG(column2) > 100;

1. UNION和UNION ALL：
   • 用于合并两个或多个SELECT语句的结果集。

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- 或者使用UNION ALL，允许重复行
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

1. 临时表（Temporary Tables）：
   • 在会话或事务期间创建的表，用于存储中间结果。

CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype
);

INSERT INTO temp_table_name
SELECT * FROM table1 WHERE condition;