前言

简介里的事真实发生, 第一次让我感受到了人生的大起大落 (原来感觉70%能pass的一下人蒙了, 而且还是公司面 😭) 不过还是有惊无险的过了 😤，吗? 参考

1. 简单查询 SELECT

在PostgreSQL中，SELECT语句用于从数据库中检索数据。它是SQL查询语言的一个基本组成部分，允许你从一个或多个表中选择数据，并根据需要筛选、排序和操作这些数据。

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • SELECT语句有以下子句
    • 使用DISTINCT运算符选择不同的行。
    • 使用ORDER BY子句对行进行排序。
    • 使用WHERE子句过滤行。
    • 使用LIMIT或FETCH子句从表中选择行的子集。
    • 使用GROUP BY子句将行分组。
    • 使用HAVING子句过滤分组。
    • 使用INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN等连接子句与其他表连接。
    • 使用UNION、INTERSECT和EXCEPT执行集合运算。

不同的子句和函数（如WHERE、ORDER BY、LIMIT和聚合函数）使得SELECT语句在处理和分析数据时非常强大。

2. 列别名

列别名Column Alias是SQL中的一种功能，用于为查询结果中的列指定临时名称。列别名在SELECT语句中使用，通常用于使输出结果更加清晰易懂，特别是在列名较长或不直观的情况下。

SELECT column_name AS alias_name
FROM table_name;

关键字AS是可选的。

3. 排序 ORDER BY

ORDER BY是SQL语句中的一个子句，用于对查询结果进行排序。通过ORDER BY，你可以指定一个或多个列，根据它们的值以升序（默认）或降序排列结果集。ORDER BY子句可以与SELECT语句一起使用，以确定查询结果的显示顺序。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

4. 去重查询 DISTINCT

SELECT DISTINCT是SQL中的一种用法，用于从查询结果中去除重复的记录，仅返回唯一的结果行。它适用于希望从数据库表中获取不重复的值或组合的场景。

SELECT DISTINCT column1, column2, ...
FROM table_name;
  • 注意事项
    • 性能考虑: SELECT DISTINCT需要对结果进行去重处理，这在大数据集上可能会影响查询性能。使用时应注意查询效率，特别是在处理大表时。

5. 分页查询

分页查询 是数据库查询的一种技术，用于将查询结果分割成更小的部分（页），以方便数据的显示和管理，特别是在结果集非常大的情况下。分页能够提高应用的性能和用户体验，因为它可以减少一次性加载的数据量，并提供更快的响应时间。

  • 分页查询通常涉及两个参数
    • 偏移量 Offset: 从结果集中跳过的记录数。
    • 限制 Limit: 每次查询返回的最大记录数。
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT limit_value OFFSET offset_value;

假设有一个名为employees的表，我们希望分页查询员工数据，每页显示10条记录

// 第一页数据
SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 0;

// 第二页数据
SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 10;

  • LIMIT 10指定每页最多显示10条记录。

  • OFFSET 0指定从结果集的第0条记录开始，也就是第一页的数据。

  • 注意事项

    • 性能问题: 当OFFSET很大时，分页查询可能会变慢，因为数据库仍然需要扫描跳过的记录。对于大数据集，通常会使用更优化的分页技术，如基于主键或索引的分页。
    • ORDER BY子句的重要性: 为了确保分页结果的稳定性和一致性，通常需要使用ORDER BY子句来明确指定排序顺序。这对于多次分页请求获取一致的结果非常重要。
    • COUNT和分页: 通常在分页查询前，会先进行COUNT查询来确定总记录数，以便客户端计算总页数。

6. WHERE 子句

WHERE子句是SQL中用于指定筛选条件的部分。它用于从数据库表中筛选出符合特定条件的行。WHERE子句可以用在SELECT、UPDATE、DELETE等SQL语句中，限制这些操作只应用于符合条件的行。

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • 常见的运算符
    • 比较运算符: =，<> (这个是 !=)，>，<，>=，<=
    • 逻辑运算符: AND，OR，NOT
    • 其他运算符: BETWEEN ... AND ...，IN (value1, value2, ...)，LIKE (这个是匹配)，IS NULL

7. LIMIT 子句

LIMIT子句是SQL中的一部分，用于限制查询结果中返回的行数。它通常与SELECT语句一起使用，以控制从数据库中提取的行数。LIMIT子句对于分页、性能优化和控制数据输出量非常有用。

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number_of_rows;
  • 查询 employees 表中按工资从高到低排列的前 3 名员工
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

8. FETCH 子句

FETCH子句是SQL中的一部分，用于从查询结果中返回特定数量的行。它通常与OFFSET子句一起使用，以实现更加灵活和清晰的分页操作。在不同的数据库系统中，FETCH子句的支持和语法可能略有不同。

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET number_of_rows OFFSET 
FETCH {FIRST | NEXT} number_of_rows {ROW | ROWS} ONLY;
  • FIRST: 表示从结果集的开头返回行数。
  • NEXT: 表示从当前偏移量位置继续返回行数。
  • ROW / ROWS: 用于指定返回行的单数或复数形式。两者在功能上没有差别。

9. IN 运算符语法

IN运算符是SQL中用于指定多个可能值的一种条件操作符。它通常用于WHERE子句中，用来过滤那些列值在指定集合中的行。IN运算符允许你简洁地测试某个列的值是否匹配一组值中的任何一个，而无需使用多个 OR条件。

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);
  • IN运算符可以与NOT结合使用，表示不在指定集合中的值。
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Engineering');

10. BETWEEN 运算符

BETWEEN运算符是SQL中用于筛选范围内的值的一种条件操作符。它通常用于WHERE子句中，以测试某个列的值是否在指定的上下限之间。BETWEEN运算符包括指定的边界值（即包含上下限），它可以用于数字、日期、时间等类型的数据。

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
// 数值范围
SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 60000;

// 日期和时间范围
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

// 字母范围
SELECT * FROM employees
WHERE name BETWEEN 'A' AND 'M';

11. LIKE 运算符

LIKE运算符是SQL中用于进行模式匹配的运算符。它通常用于WHERE子句中，以根据指定的模式筛选出符合条件的行。LIKE运算符可以匹配字符串中的部分内容，因此特别适合用于查找类似的字符串数据。LIKE运算符常与通配符一起使用，以定义要匹配的模式。

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
  • LIKE运算符支持两种主要的通配符
    • 百分号%: 匹配任意数量的字符，包括零个字符。
    • 下划线_: 匹配单个字符。
// 查询 employees 表中所有名字以 "A" 开头的员工
SELECT * FROM employees
WHERE name LIKE 'A%';

// 查询 employees 表中所有名字以 "son" 结尾的员工
SELECT * FROM employees
WHERE name LIKE '%son';

// 查询 employees 表中所有名字中包含 "li" 的员工
SELECT * FROM employees
WHERE name LIKE '%li%';

// 查询 employees 表中所有名字中第二个字母是 "a" 的员工
SELECT * FROM employees
WHERE name LIKE '_a%';

12. NULL 和 IS NULL 运算符

NULL是SQL中用于表示数据的缺失或未知值的特殊标识符。它不同于零、空字符串或任何其他值，因为它表示没有数据或数据不可用。由于NULL代表未知值，因此它在逻辑运算和比较中有特殊的处理方式。

  • NULL的特性

    • 没有特定类型: NULL可以出现在任何数据类型的列中。
    • 不可比较: NULL不等于任何值，包括它自己。因此，NULL = NULL的表达式结果为FALSE。正确的做法是使用IS NULL来检查NULL值。

  • IS NULL运算符

    • IS NULL运算符用于检查一个列的值是否为NULL。由于NULL不能通过常规的比较运算符（如=）进行比较，因此IS NULL是判断NULL值的唯一合法方式。
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

13. 表别名

表别名Table Alias是SQL中用于为表指定临时名称的功能。表别名通常用于简化查询语句，特别是在多表联接JOIN或子查询中，使得查询更加简洁和易读。表别名仅在查询的上下文中有效，不会影响数据库中表的实际名称。

SELECT column1, column2, ...
FROM table_name AS alias_name
WHERE condition;

在SQL中，AS是可选的。

14. INNER JOIN 内连接

INNER JOIN内连接是SQL中的一种联接操作，用于从多个表中查询相关联的数据。INNER JOIN只返回两个表中满足联接条件的行，也就是说，它只返回在两个表中都有匹配关系的记录。如果其中一个表没有匹配的记录，结果中不会包含该行。

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

假设有以下数据

  • employees表
idnamedepartment_id
1Alice1
2Bob2
3CharlieNULL
  • departments表
iddepartment_name
1HR
2Engineering
3Marketing
  • 使用INNER JOIN查询
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
  • 查询结果可能是
namedepartment_name
AliceHR
BobEngineering

15. LEFT JOIN 左连接

LEFT JOIN左连接是SQL中的一种联接操作，用于从两个表中查询相关的数据。与INNER JOIN不同，LEFT JOIN会返回左表中的所有记录，即使右表中没有匹配的记录。对于那些在右表中没有匹配的记录，结果集中相应右表的字段会显示为NULL。

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
  • LEFT JOIN的行为
    • 所有左表的行: 即使左表中的某一行在右表中没有匹配记录，该行也会包含在结果集中。
    • 右表的匹配行: 如果有匹配的右表记录，这些记录会出现在结果集中。
    • NULL值：对于在右表中没有匹配的左表记录，结果集中右表的列会显示为NULL。

假设有以下数据

  • employees表
idnamedepartment_id
1Alice1
2Bob2
3CharlieNULL
4David3
  • departments表
iddepartment_name
1HR
2Engineering
  • 使用LEFT JOIN查询
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
  • 查询结果可能是
namedepartment_name
AliceHR
BobEngineering
CharlieNULL
DavidNULL

16. SELF JOIN 自连接

自连接Self Join是SQL中的一种联接操作，其中一个表与自身进行联接。自连接用于将表中的一行与同一表中的另一行进行比较或关联。这在处理表中行与行之间的关系时非常有用，例如层次结构（如员工与经理的关系）或相邻行之间的比较。

  • 基本概念
    • 同一张表: 自连接涉及的表实际上是同一张表，只是在查询中使用了不同的别名，使得它们在逻辑上看起来像是两个不同的表。
    • 表别名: 为了区分表的不同实例，通常使用表别名。这样可以在同一个查询中引用表中的不同行。
SELECT a.column1, b.column2, ...
FROM table_name a
JOIN table_name b
ON a.common_column = b.common_column;

假设有以下数据

  • employees表
idnamemanager_id
1AliceNULL
2Bob1
3Charlie1
4David2
  • 使用自连接查询
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
  • 查询结果可能是
employee_namemanager_name
AliceNULL
BobAlice
CharlieAlice
DavidBob

17. FULL OUTER JOIN 全外连接

FULL OUTER JOIN全外连接是SQL中的一种联接操作，它返回两个表中所有符合联接条件的记录，以及不符合联接条件的记录。与INNER JOIN只返回两个表中有匹配记录的行不同，FULL OUTER JOIN会返回两个表中的所有记录，如果在某一表中没有匹配的记录，则该表的列会返回NULL。

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

假设有以下数据

  • employees表
idnamedepartment_id
1Alice1
2Bob2
3CharlieNULL
  • departments表
iddepartment_name
1HR
2Engineering
3Marketing
  • 使用FULL OUTER JOIN查询
SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;
  • 查询结果可能是
namedepartment_name
AliceHR
BobEngineering
CharlieNULL
NULLMarketing

18. CROSS JOIN 交叉连接

CROSS JOIN交叉连接是SQL中的一种联接操作，它返回两个表中所有可能的行组合，生成笛卡尔积Cartesian Product。CROSS JOIN不使用任何联接条件，因此会将左表的每一行与右表的每一行组合在一起。

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

假设有以下数据

  • products表
idproduct_name
1Shirt
2Pants
  • colors表
idcolor_name
1Red
2Blue
  • 使用CROSS JOIN查询
SELECT products.product_name, colors.color_name
FROM products
CROSS JOIN colors;
  • 查询结果可能是
product_namecolor_name
ShirtRed
ShirtBlue
PantsRed
PantsBlue

19. NATURAL JOIN 自然连接

NATURAL JOIN自然连接是SQL中的一种联接操作，它根据两个表中同名的列自动进行联接。自然连接会自动比较两个表中所有同名的列，并返回这些列值相等的行。与INNER JOIN不同，NATURAL JOIN不需要显式指定联接条件，因为它会自动查找并使用同名列进行联接。

SELECT column1, column2, ...
FROM table1
NATURAL JOIN table2;
  • NATURAL JOIN的行为
    • 自动联接: NATURAL JOIN自动查找并使用两个表中所有同名的列来进行联接。
    • 同名列值相等: 只有当两个表中同名列的值相等时，才会在结果集中返回这些行。
    • 不返回重复列: 结果集中不会包含重复的同名列，只会返回一次。

假设有以下数据

  • employees表
idnamedepartment_id
1Alice1
2Bob2
3Charlie3
  • departments表
iddepartment_name
1HR
2Engineering
4Marketing
  • 使用NATURAL JOIN查询
SELECT employees.id, employees.name, departments.department_name
FROM employees
NATURAL JOIN departments;
  • 查询结果可能是
namedepartment_name
AliceHR
BobEngineering

20. LATERAL JOIN 横向连接

LATERAL JOIN横向连接是SQL中的一种高级联接操作，它允许联接的子查询可以引用来自连接左侧表的列。LATERAL子句使得每一行都可以独立地进行子查询，基于当前行的值进行计算或过滤。这使得LATERAL JOIN特别适用于那些需要处理依赖于当前行的复杂子查询的场景。

SELECT column1, column2, ...
FROM table1
JOIN LATERAL (
  SELECT ...
) alias ON condition;

假设有以下数据

  • employees表
idname
1Alice
2Bob
  • projects表
employee_idproject_namestart_date
1Project X2023-01-01
2Project Y2023-06-01
3Project Z2022-09-01
  • 使用LATERAL JOIN查询
SELECT e.name, p.project_name
FROM employees e
JOIN LATERAL (
  SELECT project_name
  FROM projects p
  WHERE p.employee_id = e.id
  ORDER BY start_date DESC
  LIMIT 1
) p ON true;
  • 查询结果可能是
nameproject_name
AliceProject Y
BobProject Z

21. GROUP BY 分组

GROUP BY是SQL中的一个子句，用于将查询结果按一个或多个列进行分组。分组操作通常与聚合函数（如COUNT、SUM、AVG、MAX、MIN等）结合使用，以便对每个分组进行汇总计算。GROUP BY允许你将数据按特定的维度进行聚合，进而对这些聚合后的数据进行分析。

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;
  • 举个例子，按product_id对销售数量进行汇总，计算每个产品的总销售量。
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

22. HAVING 子句

HAVING子句是SQL中的一部分，主要用于在分组后的结果集上进行过滤。它通常与GROUP BY子句一起使用，以便对聚合后的数据进行进一步的筛选。与WHERE子句不同，HAVING子句用于过滤基于聚合函数的结果，而WHERE子句是在数据分组之前对行进行过滤。

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING aggregate_condition;
  • 举个例子，查询销售总量大于100的产品
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

23. PARTITION BY 分区计算

PARTITION BY是SQL中的一个子句，通常与窗口函数一起使用，用于将查询结果集划分为多个分区，然后在每个分区内执行特定的计算。它类似于GROUP BY，但PARTITION BY不会将数据聚合到单一行，而是保留所有行，并在每个分区内执行计算。

SELECT column1, column2, 
       window_function() OVER (PARTITION BY column_name ORDER BY column_name) AS alias_name
FROM table_name;

假设有以下数据

  • sales表
idemployee_idsale_amountsale_date
11015002023-08-01
21017002023-08-02
31023002023-08-01
41014002023-08-03
51028002023-08-02
  • 使用PARTITION BY进行分区计算
SELECT employee_id, sale_date, sale_amount,
       RANK() OVER (PARTITION BY employee_id ORDER BY sale_amount DESC) AS sales_rank
FROM sales;
  • 查询结果可能是
employee_idsale_datesale_amountsales_rank
1012023-08-027001
1012023-08-015002
1012023-08-034003
1022023-08-028001
1022023-08-013002

24. UNION 组合

UNION是SQL中的一个运算符，用于将两个或多个SELECT语句的结果组合成一个结果集。UNION运算符将多个查询的结果合并为一个，且默认情况下会去除重复的行。如果你希望保留重复行，可以使用UNION ALL。

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

使用条件

  • 列数和数据类型必须一致: 参与UNION的每个SELECT语句中，列数必须相同，且对应列的数据类型应该兼容或相同。
  • 列的顺序和名称: 在合并的结果集中，列的顺序以第一个SELECT语句中的列为准，列名也是根据第一个SELECT语句的列名。

举个例子，查询两个表中的所有员工，并将结果合并为一个结果集

SELECT id, name, department
FROM employees_2023
UNION
SELECT id, name, department
FROM employees_2024;

25. INTERSECT 交集

INTERSECT是SQL中的一个运算符，用于返回两个或多个SELECT语句结果集的交集。换句话说，它返回的是在所有参与查询的结果集中都存在的行。INTERSECT运算符在多个查询结果集中找出共有的行，而这些行必须在所有查询中都出现。

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

INTERSECT的行为

  • 返回共有行: INTERSECT只返回在所有SELECT语句结果集中都存在的行。
  • 自动去重: INTERSECT会自动去除结果中的重复行，类似于DISTINCT。

举个例子，查询两个表中在2023年和2024年都存在的员工

SELECT id, name, department
FROM employees_2023
INTERSECT
SELECT id, name, department
FROM employees_2024;

26. EXCEPT 差集

EXCEPT是SQL中的一个运算符，用于返回两个SELECT语句结果集的差集，即返回第一个查询的结果集中有但第二个查询的结果集中没有的行。EXCEPT通常用于比较两个结果集并找出其中一个结果集中独有的记录。

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

EXCEPT的行为

  • 返回差集: EXCEPT返回第一个查询结果集中有，但第二个查询结果集中没有的行。
  • 自动去重: EXCEPT会自动去除结果中的重复行，类似于DISTINCT。

举个例子，查询那些在2023年存在但在2024年不存在的员工

SELECT id, name, department
FROM employees_2023
EXCEPT
SELECT id, name, department
FROM employees_2024;

27. GROUPING SETS 分组

GROUPING SETS是SQL中的一种高级分组功能，用于在单个查询中实现多种分组方式。它允许你在一次查询中指定多个分组标准，并返回每个分组标准的聚合结果。GROUPING SETS本质上是GROUP BY子句的扩展，提供了更灵活的分组和聚合操作。

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY GROUPING SETS ( (column1), (column2), ...);

假设有以下数据

  • sales表
idemployee_idproduct_idsale_amount
11011100
21021200
31012150
41032250
  • 我们希望在一个查询中得到以下几种分组的聚合结果
    • 按员工employee_id分组并计算销售总额。
    • 按产品product_id分组并计算销售总额。
    • 对所有销售记录计算总额（没有分组）。
SELECT employee_id, product_id, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
    (employee_id),
    (product_id),
    ()
);
  • 查询结果可能是
employee_idproduct_idtotal_sales
101NULL250
102NULL200
103NULL250
NULL1300
NULL2400
NULLNULL700

28. CUBE 分组

CUBE是SQL中的一个扩展分组功能，用于在单个查询中生成所有可能组合的分组和聚合结果。CUBE是GROUP BY子句的一部分，提供了一种多维分析的方式，特别适合在需要对数据进行多维度汇总的场景中使用。它通过计算所有可能的分组组合，帮助我们全面地了解数据的分布。

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY CUBE (column1, column2, ...);

假设有以下数据

  • sales表
regionproductsale_amount
NorthA100
NorthB150
SouthA200
SouthB250
  • 按区域和产品生成销售金额的所有可能组合的聚合结果
SELECT region, product, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY CUBE (region, product);
  • 查询结果可能是
regionproducttotal_sales
NorthA100
NorthB150
NorthNULL250
SouthA200
SouthB250
SouthNULL450
NULLA300
NULLB400
NULLNULL700

29. ROLLUP 分组

ROLLUP是SQL中的一个扩展分组功能，用于生成基于层次结构的分组和聚合结果。它是GROUP BY子句的扩展，用于在一个查询中生成从详细到汇总的多个级别的聚合结果。ROLLUP常用于需要对数据进行层次分析的场景，例如销售数据按区域和时间进行汇总时。

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY ROLLUP (column1, column2, ...);

假设有以下数据

  • sales表
regionproductsale_amount
NorthA100
NorthB150
SouthA200
SouthB250
  • 按区域和产品生成从详细到汇总的销售金额数据
SELECT region, product, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY CUBE (region, product);
  • 查询结果可能是
regionproducttotal_sales
NorthA100
NorthB150
NorthNULL250
SouthA200
SouthB250
SouthNULL450
NULLNULL700

30.

附录

文件还未上传 Github