```sql
SELECT emp_name, salary -- Outer Query
FROM employee
WHERE salary > (
	SELECT AVG(salary) -- Inner Query
	FROM employee
);
```



## 标量子查询 Scalar Subquery

返回单个值（一行一列），可以像常量一样被用于SELECT、WHERE、GROUP BY、HAVING以及ORDER BY等子句中。

```sql
-- 员工月薪与平均月薪之间的差值
SELECT emp_name AS "员工姓名", salary AS "月薪",
	salary - (SELECT AVG(salary) FROM employee) AS "差值"
FROM employee;
```

## 行子查询 Row Subquery

返回单个记录（一行多列），不常用。

```sql
-- 查找所有与“孙乾”在同一个部门并且职位相同的员工
-- oracle mysql pg sqlite
SELECT emp_name, dept_id, job_id
FROM employee
WHERE (dept_id, job_id) = (
	SELECT dept_id, job_id
	FROM employee
	WHERE emp_name = '孙乾'
)
AND emp_name != '孙乾'；
```

- mss 不支持行子查询。

## 表子查询 Table Subquery

返回一个临时表（多行多列）。

### where 中的子查询

- 因为子查询是表，所以不能使用比较运算符（`=`、`!=`、`<`、`<=`、`>`、`>=`等）。
- 可以使用 IN、NOT IN。

```sql
-- 某个部门的全体员工
SELECT emp_name
FROM employee
WHERE job_id IN (
	SELECT job_id
	FROM employee
	WHERE dept_id = 1
);
```

### ALL、ANY 子查询

> ALL 运算符相当于多个 AND 运算符的组合，IN/ANY 运算符相当于多个 OR 运算符的组合。

- ALL 运算符与比较运算符（`=`、`!=`、`<`、`<=`、`>`、`>=`）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的全部数据。

```sql
-- 查找比“研发部”全体员工更晚入职的员工信息
-- oracle mysql mss pg
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
FROM employee
WHERE hire_date >ALL (
	SELECT e.hire_date
	FROM employee e
	JOIN department d ON (d.dept_id = e.dept_id)
	WHERE d.dept_name = '研发部'
);
```

- sqlite 不支持 ALL。

```sql
-- oracle
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
FROM employee
WHERE salary >ALL (10000, 15000, 20000);
```

- ANY 运算符与比较运算符（`=`、`!=`、`<`、`<=`、`>`、`>=`）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的任何数据。

```sql
-- 某个部门的全体员工
SELECT emp_name
FROM employee
WHERE job_id =ANY (
	SELECT job_id
	FROM employee
	WHERE dept_id = 1
);
```

- sqlite 不支持 any。
- SOME 关键字可以替代 ANY。

```sql
-- oracle
SELECT emp_name
FROM employee
WHERE job_id !=ANY (1, 2, 2); -- 等价于 job_id != 1 OR job_id != 2
```

### FROM 中的子查询

FROM子句中的子查询相当于一个临时表。

```sql
-- 部门平均工资
SELECT d.dept_name AS "部门名称", ds.avg_salary AS "平均月薪"
FROM department d
LEFT JOIN (
	SELECT dept_id, AVG(salary) AS avg_salary
	FROM employee
	GROUP BY dept_id) ds
ON (d.dept_id = ds.dept_id);
```

子查询中使用排序通常没有意义。

```sql
-- mysql pg sqlite
SELECT emp_name, salary
FROM employee
WHERE job_id IN (
	SELECT job_id
	FROM job
	WHERE max_salary >= 20000
	ORDER BY job_id
)
ORDER BY emp_name;
```

- oracle mss 在子查询中使用排序会报错。

## 关联子查询 Correlated Subquery

子查询使用外部查询中的字段。

> 对于外部查询返回的每条记录，关联子查询都会执行一次（数据库可能会对此进行优化）​，非关联子查询只会独立执行一次。

```sql
-- 每个部门的平均月薪
SELECT d.dept_name AS "部门名称",
	(SELECT AVG(salary) AS avg_aslary
	FROM employee
	WHERE dept_id = d.dept_id) AS "平均月薪"
FROM department d
ORDER BY d.dept_id;

-- 每个部门中最早入职的员工
SELECT d.dept_name AS "部门名称", o.emp_name AS "员工姓名", o.hire_date AS "入职日期"
FROM employee o
JOIN department d ON (d.dept_id = o.dept_id)
WHERE o.hire_date = (SELECT MIN(i.hire_date)
					FROM employee i
					WHERE i.dept_id = o.dept_id);
```

## 横向子查询 Lateral Subquery

关联子查询可以使用外部查询中的字段，但不能使用同一级别的其他查询或者表中的字段。以下是一个错误的示例：

```sql
-- 每个部门的最大月薪
SELECT d.dept_name, t.max_salary
FROM department d
JOIN (SELECT MAX(e.salary) AS max_salary
	 FROM employee e
	 WHERE e.dept_id = d.dept_id) t;
```

横向子查询，允许派生表使用它所在的FROM子句中左侧的其他查询或者表：

```sql
-- oracle mysql pg
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS JOIN
LATERAL (SELECT MAX(e.salary) AS max_salary
		FROM employee e
		WHERE e.dept_id = d.dept_id) t;

-- mss oracle
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS APPLY (SELECT MAX(e.salary) AS max_salary
		FROM employee e
		WHERE e.dept_id = d.dept_id) t;
```

- 除 CROSS APPLY 外，还有一个 OUTER APPLY，它是左外连接的一种变体，同样允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。Microsoft SQL Server和Oracle提供了这两种查询语法。
- sqlite 不支持横向子查询。

## EXISTS 运算符

用于判断子查询结果的存在性。只要子查询返回了任何结果，就表示满足查询条件；如果子查询没有返回任何结果，就表示不满足查询条件。

```sql
-- 拥有女性员工的部门
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE EXISTS (SELECT 1
			 FROM employee e
			 WHERE e.sex = '女'
			 AND e.dept_id = d.dept_id)
ORDER BY dept_name;
```

> `​[NOT] EXISTS`运算符只检查结果的存在性，`​[NOT] IN`运算符需要比较实际的值是否相等。

当子查询的结果中只有 NULL 值时，EXISTS 运算符仍然可以返回结果，NOT EXISTS 运算符则不返回结果。但是，此时 IN 和 NOT IN 运算符都不会返回结果，因为任何数据和空值进行比较的结果均未知。

```sql
-- 不拥有女性员工的部门
SELECT d.dept_name
FROM department d
WHERE NOT EXISTS (SELECT NULL
			 FROM employee e
			 WHERE e.dept_id = d.dept_id
			 AND sex = '女');
```

NOT IN 的等价写法

```sql
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id NOT INT (3, 4, NULL);

-- 等价于
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id != 3
AND d.dept_id != 4
AND d.dept_id != NULL;
```

## 参考

- [什么是 PostgreSQL 横向子查询？](https://blog.csdn.net/horses/article/details/118769805)

嵌套子查询

[图片来源](https://x.com/ezekiel_aleke/status/1857801057721590244)
![](https://s2.loli.net/2024/12/11/sEAYh2yaXVzuCLt.png)





## 内连接 Inner Join

INNER JOIN 返回两个表中满足连接条件的数据，可以简写为 JOIN。

**等值连接**

```sql
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
JOIN job j ON (e.job_id = j.job_id)
WHERE e.emp_id = 1;
```

**非等值连接**

除了等号运算符外的条件，比如`>=`、`!=`、BETWEEN、AND 等。

```sql
-- 返回当前月薪属于开发经理级别但不是开发经理的员工
SELECT e.emp_name AS "员工姓名", e.salary "月薪"
FROM employee e
JOIN job j ON (e.job_id != j.job_id
			  AND e.salary BETWEEN j.min_salary AND j.max_salary)
WHERE j.job_title = '开发经理';
```

## 外连接 Outer Join

**左外连接**

Left Outer Join 查询首先返回左表中的全部数据。之后，如果右表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 LEFT JOIN。

```sql
-- 每个部门的员工人数
SELECT d.dept_name AS "部门名称", COUNT(e.emp_id) AS "员工人数"
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

-- 没有员工的部门
SELECT d.dept_id AS "部门编号", d.dept_name AS "部门名称"
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
WHERE e.emp_id IS NULL;
```

**右外连接**

Right Outer Join 查询首先返回右表中的全部数据。如果左表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 RIGHT JOIN。

- 右外连接和左外连接可以相互转换。

```sql
-- 每个部门员工人数
-- oracle mysql mss pg
SELECT d.dept_name AS "部门名称", COUNT(e.emp_id) AS "员工人数"
FROM employee e
RIGHT JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;
```

- sqlite 不支持右外连接（[应该支持?](https://www.sqlite.org/lang_select.html)）

**全外连接**

Full Outer Join 查询同时返回左表和右表中所有的数据。如果右表或者左表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 FULL JOIN。

```sql
-- 获取哪些组没有员工和哪些员工没有分配到组
-- oracle mss pg
SELECT g.group_name, eg.emp_id
FROM t_group g
FULL JOIN t_emp_group eg ON (eg.group_id = g.group_id)
WHERE g.group_id IS NULL OR eg.emp_id IS NULL;
```

- mysql sqlite 不支持全外连接。（[应该支持?](https://www.sqlite.org/lang_select.html)）

## 交叉连接/笛卡尔积 Cartesian Product

CROSS JOIN 将一个表的所有数据行和另一个表的所有数据行进行两两组合，返回结果的数量为两个表中的行数相乘。

```sql
SELECT COUNT(*)
FROM employee e
CROSS JOIN department d;
```

## 自然连接 Natural Join

如果连接查询同时满足以下条件，我们可以使用 USING 替代 ON 来简化连接条件的输入：

- 连接条件是等值连接。
- 两个表中的连接字段名称相同，类型也相同。

```sql
-- oracle mysql pg sqlite
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
JOIN job j USING (job_id)
WHERE e.emp_id = 1;
```

- mss 不支持 USING。

如果等值连接条件中包含了两个表中所有同名同类型的字段，查询语句可以继续进行简化。

```sql
-- oracle mysql pg sqlite
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
NATURAL JOIN job j
WHERE e.emp_id = 1;
```

- mss 不支持自然连接。

## 自连接 Self Join

Self Join 查询指的是一个表和它自己进行连接查询。自连接本质上并没有什么特殊之处，主要用于处理那些对自身进行了外键引用的表。

```sql
-- 查看员工以及他的经理
SELECT e.emp_name AS "员工姓名", m.emp_name AS "经理姓名"
FROM employee e
LEFT JOIN employee m ON (m.emp_id = e.manager)
WHERE e.emp_id = 9;
```

SQL 连接类型

自己在查看网络时总是记不住 TCP 状态，所以整理了一下关于 TCP 的图，都来源于 TCP/IP GUID 这本书。这里并没有整理所有的图，有需要再加。



## TCP Segment Format

[位置](http://www.tcpipguide.com/free/t_TCPMessageSegmentFormat-3.htm)
![Figure 216: TCP Segment Format](https://s2.loli.net/2024/11/20/nuwI3cUbD8RhSdq.png)

## The TCP Finite State Machine (FSM)

[位置](http://www.tcpipguide.com/free/t_TCPOperationalOverviewandtheTCPFiniteStateMachineF-2.htm)
![Figure 210: The TCP Finite State Machine (FSM)](https://s2.loli.net/2024/11/20/FIiKhEAl4TemrSL.png)

## TCP “Three-Way Handshake” Connection Establishment Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionEstablishmentProcessTheThreeWayHandsh-3.htm)
![Figure 211: TCP “Three-Way Handshake” Connection Establishment Procedure](https://s2.loli.net/2024/11/20/REODpuWB3Gwl1v4.png)

## TCP Simultaneous Open Connection Establishment Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionEstablishmentProcessTheThreeWayHandsh-4.htm)
![Figure 212: TCP Simultaneous Open Connection Establishment Procedure](https://s2.loli.net/2024/11/20/Yuec7DPRQWEXk5L.png)

## TCP Connection Termination Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionTermination-2.htm)
![Figure 214: TCP Connection Termination Procedure](https://s2.loli.net/2024/11/20/LHTwPhm1a8AV2E4.png)

## TCP Simultaneous Connection Termination Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionTermination-4.htm)
![Figure 215: TCP Simultaneous Connection Termination Procedure](https://s2.loli.net/2024/11/20/RJm5gMr7zdQlhF8.png)

## 参考

- [The TCP/IP Guide](http://www.tcpipguide.com/free/index.htm)

TCP 相关图整理

不要在生产环境随意升级。



1. 使用 backports 源

```bash
$ echo "deb http://deb.debian.org/debian bookworm-backports main" > /etc/apt/sources.list.d/bookworm-backports.list
$ apt update
```

2. 搜索内核

```bash
$ apt search linux-image
# 可选
$ apt search linux-header
```

3. 使用新内核

```bash
# 因为是 vps，所以选择了 cloud
$ apt install linux-image-6.11.5+bpo-cloud-amd64
# 可选
$ apt install linux-headers-6.11.5+bpo-cloud-amd64
reboot
```

4. 删除旧内核

```bash
# 查看现有内核
$ dpkg -l | grep linux-image
$ dpkg -l | grep linux-headers
# 删除
$ apt purge linux-image-6.1.0-26-cloud-amd64
$ apt purge linux-headers-6.1.0-22-cloud-amd64
```

## 参考

- [Debian Backports ›› Instructions](https://backports.debian.org/Instructions/)

Debian12 升级内核

BROKE框架模板。对于简单的问题可以不使用框架。



## BROKE框架

- 阐述背景B(Background)：说明背景，为ChatGPT提供充足的信息。
- 定义角色R(Role)：给ChatGPT定义角色。
- 定义目标O(Objectives)：指我们希望ChatGPT能够实现什么目标。
- 定义关键结果K(KeyResults)：指我们想要什么具体效果。
- 试验并调整，改进E(Evolve)：将以下三种改进方法自由组合。
	- 改进提示：从答案的不足之处着手改进背景、目标与关键结果。
	- 改进答案：在后续对话中指正ChatGPT答案的缺点。
	- 重新生成：尝试在提示不变的情况下多次生成结果，优中选优。

## 背景(Background)：信息传达与角色设计

```
Let's think step by step，

1. 接下来我会向你提一个“目标”。为了完成目标，你需要从不同方面出发，拆解问题的背景，去分析解决这个问题需要哪些信息。
	- 获得这些信息后，你应该能够对这个问题进行彻底、全面、深入的分析。
2. 根据你的分析，向我提一些问题以询问你需要知道的信息，从而达成目标。
	- 向我提尽可能多的问题，用于提供背景信息。
	- 问题要可衡量、具体，能够回答关键信息。

目标：

###

(这里写你的目标)

###
```

通过上面的提问，ChatGPT 会向你询问背景，这时你可以提供一些原始文档或链接来避免自己去思考。注意 ChatGPT 有限的上下文和单次对话的字数限制。

在处理简单问题时，我们无须花费大量时间和精力去使用复杂的框架，只需要根据实际需求来设计简洁明了的问题背景即可。

- 任务的宏观背景：提供整体背景，如行业、技术领域、业务范围等。
- 任务的微观场景：提供具体的问题背景，帮助ChatGPT理解具体发生了什么，为什么会有这个需求。
- 相关知识或信息：包括解决问题所需技能、概念、术语、已知限制或约束等。
- 提问者的角色和立场：明确提问者的身份、需求和期望，使ChatGPT能够更好地定制回应。

## 角色(Role):AI助手的角色扮演游戏

设计角色的框架

- 明确角色定位：让ChatGPT清楚地知道这个角色是做什么的。
- 界定角色功能：清晰地告诉ChatGPT这个角色可以解决哪些问题。
- 设定互动规则：指导ChatGPT如何与用户进行有效的互动。

例子

```
（明确角色定位）现在我希望你扮演一个世界一流的中国川菜厨师，（界定角色功能）你擅长将各种各样的食材灵活组合，做出好吃又营养的家常菜。（设定互动规则）我会告诉你我的冰箱里有什么食材，而你需要根据现有的食材，告诉我利用它们可以做什么菜。你可以自由灵活地组合食材，制作出各种新的菜肴，无须拘泥于已有的菜谱。你将为我提供菜谱。

- 我是贵州人，喜欢吃辣椒。
- 提供的菜肴要好吃，符合做菜的规则。
- 冰箱里有猪肉、芹菜、豆腐、胡萝卜、鸡蛋。
```

## 目标与关键结果(Object&Key Results)：给ChatGPT“打绩效”

目标与关键结果是相辅相成的：明确的目标有助于我们制定出可衡量的关键结果，而具体的关键结果则可以帮助我们对目标进行微调，以便更精确地满足需求。

### 目标

**清晰明确**

如果我们要求ChatGPT写一篇“有关××的文章”​，这个目标就太过宽泛。我们可以要求它“写一篇关于××的议论文”​“写一篇关于××的科幻剧本”等，限定的文章体裁“议论文”或“剧本”就比“文章”更清晰明确。

**目标导向**

我们需要ChatGPT帮助解决一个数学问题，我们应该让它“求解方程2x+3=7”​，而不是让它“用减法和除法求解方程”​。不过如果你有特殊的个性化需求，这条规则也是可以改变的，即可以根据需求指定过程。

**适度的范围和难度**

不要让ChatGPT预测下周的股票走势，因为这个任务超出了它的能力范围。如果目标太大，可以对其逐步拆解。

**保持目标简洁**

尽量避免过长的目标描述，以便让ChatGPT能更好地抓住问题关键与核心需求。

### 关键结果

在设计关键结果时，我们可以遵循SMAR原则，即具体(Specific)、可衡量(Measurable)、可实现(Attainable)、相关(Relevant)。

**具体**

- 明确描述预期输出，如“生成一篇1000字以上的文章”​；
- 避免过于宽泛的描述，如“写一篇长文章”​。

**可衡量**

- 为关键结果设定数量或百分比，如“列出至少10种麻辣口味的炒菜菜谱”​；
- 为关键结果设定质量标准，如“回答中的理论需要是学术领域的共识，并有权威的来源”​。

**可实现**

关键结果应符合ChatGPT的能力范围，并要考虑任务的难度和复杂性。过高的期望可能会导致不理想的输出，而过低的期望则可能无法充分发挥ChatGPT的潜力。

**相关**

- 保持关键结果与目标的一致性，如目标是“写一篇关于气候变化的文章”​，关键结果可以是“包含3个主要观点和相关证据”​；
- 避免不相关的关键结果，如“使用5种不同的文学手法”​，这可能导致输出偏离主题。不过这条规则并非固定的，在使用过程中可根据需求灵活变动。

## 改进(Evolve)：进行试验与调整

根据不同情境与生成答案的缺点，我们需要对指令进行调整、在对话中加以指正或重新生成回答。

在使用ChatGPT的过程中，我们可能会遇到三种情况

**指令给得不够清楚**

- 改进背景：检查提供给ChatGPT的信息是否充足。
- 改进角色：可以酌情添加修饰词，或更换新角色。
- 改进目标：检查是否明确说明了要生成什么内容。
- 增加/修改关键结果：若ChatGPT的回答没有体现出你想要的内容，可以在关键结果中补充说明要求。

**ChatGPT做得不好，或回答有错误**

在后续对话中要求ChatGPT改进有缺点的回答。

**运气不好，遇到糟糕的回答**

尝试在提示不变的情况下重复生成回答，优中选优。ChatGPT的回答是具有随机性的，使用相同的提示也会输出不同的回答。

> 以上三种解决方案通常需要组合起来重复、多次使用，才可以得到满意回答。