SQL基础

DDL——数据定义语言

用于 定义和修改数据库结构，例如数据库、表、索引、视图、约束等。

这些操作会直接影响数据库的结构，而不仅仅是表中的数据。
执行 DDL 语句后，系统会自动提交事务（即无法回滚）。

常见DDL命令

命令	功能
CREATE	创建数据库对象（如表、索引、视图、模式等）
ALTER	修改数据库对象（例如添加列、更改列类型）
DROP	删除数据库对象
TRUNCATE	清空表中所有数据（但保留表结构）
COMMENT	为数据库对象添加注释

示例：

• 创建表：

  CREATE TABLE students (
      id SERIAL PRIMARY KEY,
      name VARCHAR(100),
      age INT,
      city VARCHAR(50)
  );

• 在已有表中添加列

  ALTER TABLE 表名
  ADD COLUMN 列名 数据类型 [约束] [DEFAULT 默认值];

• 删除表

  DROP TABLE employees;

• 清空表数据

  TRUNCATE TABLE employees;

---

DML——数据操作语言

用于 查询和操作数据本身（不是结构），
即增、删、改、查操作。

DML 语句可以在事务中执行，因此可以回滚（ROLLBACK）或提交（COMMIT）。

常见DML命令

命令	功能
SELECT	查询数据
INSERT	插入新行
UPDATE	修改现有数据
DELETE	删除数据

• 插入数据：

  INSERT INTO students (name, age, city)
  VALUES ('Alice', 20, 'Beijing');

• 查询数据：

  SELECT * FROM students;
  SELECT name, city FROM students WHERE age > 18;

• 更新数据：

  UPDATE students
  SET city = 'Shanghai'
  WHERE name = 'Alice';

• 删除数据：

  DELETE FROM students
  WHERE id = 1;

分组（GROUP BY）

GROUP BY 子句用于将结果集中的行分组，每个组代表在指定列上具有相同值的记录集合。
分组通常与聚集函数（aggregate functions）（如 COUNT()、AVG()、SUM()、MAX()、MIN()）一起使用，以便对每个组进行统计计算。

基本语法：

SELECT 列名, 聚集函数(列名)
FROM 表名
[WHERE 条件]
GROUP BY 列名
[HAVING 分组条件]
[ORDER BY 排序列];

---

集合运算

PostgreSQL 支持以下四种主要集合运算：

运算符	含义	类比数学操作
UNION	合并两个结果集（去重）	∪（并集）
UNION ALL	合并两个结果集（不去重）	多重集并
INTERSECT	取两个结果集的交集	∩（交集）
EXCEPT	取两个结果集的差集	−（差集）

集合运算的基本语法格式如下：

SELECT 列名 FROM 表1
[集合运算符]
SELECT 列名 FROM 表2
[ORDER BY ...];

注意以下规则：

1. 两个 SELECT 查询的列数必须相同；
2. 对应列的数据类型必须兼容；
3. ORDER BY 只能写在最后一个查询之后。

集合运算与 DISTINCT 的关系:
UNION、INTERSECT、EXCEPT 都会隐式执行去重（相当于 DISTINCT）。
如果不需要去重，用 ALL 版本可提高性能。

---

连接 (JOIN)

连接包括几个类型：

• 内连接（INNER JOIN）：
  仅返回两个表中匹配的行。

• 左外连接（LEFT OUTER JOIN）：
  返回左表所有行，右表无匹配时补 NULL。

• 右外连接（RIGHT OUTER JOIN）：
  返回右表所有行，左表无匹配时补 NULL。

• 全外连接（FULL OUTER JOIN）：
  返回两表所有行，没匹配的地方用 NULL。

• 自连接（SELF JOIN）：
  表与自己连接，多用于层级结构。

示例：

SELECT a.id, a.name, b.dept_name
FROM employees a
INNER JOIN departments b
ON a.dept_id = b.id;

集合与连接(JOIN)的对比：

特点	集合运算	JOIN
操作对象	整个结果集	行与行之间
匹配方式	按整行匹配	按列条件匹配
结果结构	相同列结构	列可不同
性能	通常较慢（需去重或构造临时集）	更灵活高效
常用场景	合并同结构表结果	关联不同结构表

---

聚集函数与 WHERE / HAVING / FILTER

• WHERE 子句：

  • 在分组与聚合之前过滤行。
  • 不能包含聚集函数。
  • 作用：控制哪些行进入聚合计算。

• HAVING 子句：

  • 在分组和聚合之后过滤分组结果。
  • 通常包含聚集函数。
  • 严格来说，可以写不带聚集函数的 HAVING，但通常效率低。

• 对比：

  • 在 WHERE 里过滤更高效，因为减少了分组和聚合的数据量。
  • HAVING 适合基于聚合结果进行过滤。

• FILTER 子句：

  • 为单个聚合函数设置条件，不影响其他聚合函数。
  • 类似 WHERE，但作用范围仅限特定聚合。

• 示例：

  SELECT city,
         count(*) FILTER (WHERE temp_lo < 45),
         max(temp_lo)
  FROM weather
  GROUP BY city;

  输出：

      city        | count | max
  ----------------+-------+-----
   Hayward        | 1     | 37
   San Francisco  | 1     | 46
  (2 rows)

  • count(*) 只统计 temp_lo < 45 的行。
  • max(temp_lo) 依然计算所有行。

---

窗口函数（Window Functions）

作用是：在不分组、不聚合整行的情况下，对数据集中的“窗口（window）”进行计算。
窗口函数在 SELECT 或 ORDER BY 子句中使用，一般形式如下：

function_name (expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

窗口函数与聚合函数的区别

功能	聚合函数	窗口函数
作用范围	整组聚合，行被合并	每行保留，只在“窗口”内计算
示例	AVG(salary) 计算整个部门的平均工资	AVG(salary) OVER (PARTITION BY dept) 计算每行所在部门的平均工资

常见窗口函数实例

1. 排序相关

SELECT
  name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,   # 并列时跳号（1,2,2,4）
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,   # 并列不跳号（1,2,2,3）
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num   # 每行唯一编号（无并列）
FROM employees;

2. 分区计算
每个部门内部计算平均工资，同时保留每行记录。

SELECT
  dept,
  name,
  salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

3. 累加计算
按 id 排序，计算“截至当前”的累计工资。

SELECT
  name,
  salary,
  SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

4. LAG/LESD 前后行取值
可以访问相邻行（上一行、下一行）的值，非常适合计算差值、增长率等

SELECT
  name,
  salary,
  LAG(salary, 1) OVER (ORDER BY id) AS prev_salary,
  LEAD(salary, 1) OVER (ORDER BY id) AS next_salary
FROM employees;

5. NTILE 均匀分组
将数据按工资从高到低分成 4 个“等份区间”（分位数分析）。

SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

---

SQL 查询的执行顺序

SQL 编写顺序：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

SQL 实际执行顺序：

1. FROM / JOIN：确定数据来源。
2. WHERE：过滤行。
3. GROUP BY：分组。
4. HAVING：过滤分组。
5. SELECT：计算和返回列。
6. DISTINCT：去重。
7. ORDER BY：排序。
8. LIMIT / OFFSET：限制返回结果。

注意：执行顺序和书写顺序不一致，理解这一点有助于正确使用 WHERE 与 HAVING。

---

IN,ANY/SOME,ALL操作符

在 SQL 中，IN、ANY（或 SOME）、ALL 都可以用于 比较一个值与多个值，
尤其常用于子查询中，用来实现更灵活的条件判断。

1. IN
判断某个表达式的值是否 在指定的列表或子查询结果中。
示例：

SELECT name, department
FROM employees
WHERE department IN ('HR', 'Sales', 'Finance');

表示：选择部门是 HR、Sales 或 Finance 的员工。

如果使用子查询：

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'Tokyo'
);

表示：选择所有在东京部门工作的员工。

2. ANY（或 SOME）

判断一个表达式是否 与子查询返回的任意一个值 满足条件。

ANY 和 SOME 在 SQL 中完全等价，只是语法别名。
通常更推荐使用 ANY。

示例:
假设有两个表：
employees(name, salary, department_id)
departments(id, avg_salary)

查询工资 高于任意一个部门平均工资 的员工：

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT avg_salary FROM departments
);

即：如果某员工的工资高于“至少一个部门”的平均工资，则满足条件。

3. ALL
判断一个表达式是否 与子查询返回的所有值都满足条件。

示例
查询工资高于所有部门平均工资的员工：

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT avg_salary FROM departments
);

表示：该员工的工资要比所有部门的平均工资都高。

---

行构造器比较

基本规则:

1. 两个行构造器必须包含相同数量的字段。
   否则 PostgreSQL 无法逐一对应比较。

2. 比较时，系统会从左到右依次比较每一对字段。
   当遇到第一对“可以区分结果”的字段时，就会停止比较。

各类比较符的行为

1. = 与 <>（等于与不等）

这两种情况的逻辑是：

• 如果所有对应字段都 非空 且 相等，则整行相等。
• 如果任意一对字段 非空 且 不相等，则整行不相等。
• 如果所有字段都比较完毕但含有 NULL，则结果是 UNKNOWN（即 NULL）。
  示例：

  SELECT (1, NULL) = (1, NULL);   -- 结果为 NULL
  SELECT (1, 2)    = (1, 2);      -- 结果为 true
  SELECT (1, 2)    <> (1, 3);     -- 结果为 true

  2. <, <=, >, >=（大小比较）

行值的大小比较是按字典序进行的：

• 从左到右比较；
• 一旦发现不等，立即停止；
• 如果某个字段为 NULL，则结果为 NULL。

示例：

SELECT ROW(1,2,NULL) < ROW(1,3,0);
-- 比较第1列：1 = 1
-- 比较第2列：2 < 3 → 结果为 true，停止比较
-- 返回 true（不会去看第3列）

3. IS DISTINCT FROM / IS NOT DISTINCT FROM

这些是 PostgreSQL 特有的 “防空值比较”运算符。
它们与 = / <> 类似，但区别在于：

• 不会返回 NULL；

• NULL 与任何非 NULL 值被认为不相等；

• 两个 NULL 被认为相等。

示例：

SELECT (1, NULL) IS DISTINCT FROM (1, NULL);   -- false
SELECT (1, NULL) IS DISTINCT FROM (1, 2);      -- true
SELECT (1, NULL) IS NOT DISTINCT FROM (1, NULL); -- true

这样可以避免因为 NULL 导致比较结果不确定的问题。

---

约束(constraint)

约束（Constraint） 用于限制表中数据的取值范围和关系，以保证数据的准确性、一致性和完整性。

类型	说明	示例
NOT NULL	指定字段不能为 NULL	name TEXT NOT NULL
UNIQUE	字段或字段组合的值必须唯一	email TEXT UNIQUE
PRIMARY KEY	主键，自动包含 UNIQUE + NOT NULL	id SERIAL PRIMARY KEY
FOREIGN KEY	外键，保证引用的行存在	dept_id INT REFERENCES dept(id)
CHECK	检查表达式结果是否为真	salary NUMERIC CHECK (salary > 0)
EXCLUSION	排他约束，防止特定范围冲突	EXCLUDE USING gist (room WITH =, during WITH &&)
DEFERRABLE	延迟约束检查到事务提交时	DEFERRABLE INITIALLY DEFERRED

CHECK 约束

一个检查约束也可以引用多个列。例如我们存储一个普通价格和一个打折后的价格，而我们希望保证打折后的价格低于普通价格：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

前两个约束看起来很相似。第三个则使用了一种新语法。它并没有依附在一个特定的列，而是作为一个独立的项出现在逗号分隔的列列表中。列定义和这种约束定义可以以混合的顺序出现在列表中。

我们将前两个约束称为列约束，而第三个约束为表约束，因为它独立于任何一个列定义。列约束也可以写成表约束，但反过来不行，因为一个列约束只能引用它所依附的那一个列

UNIQUE 约束

通常情况下，如果表中有多行，其中包含约束中包含的所有列的值相等，则违反了唯一约束。 默认情况下，在此比较中，两个空值不被视为相等。这意味着即使存在唯一约束，也可以存储包含至少一个受约束列中的空值的重复行。 可以通过添加子句NULLS NOT DISTINCT来更改此行为，例如

CREATE TABLE products (
    count int UNIQUE,
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

或者

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE NULLS NOT DISTINCT (product_no)
);

主键(PRIMARY KEY)

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。因此id PRIMARY KEY 相当于 id UNIQUE NOT NULL。

一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。

外键(FOREIGN KEY)

• 定义：外键是用于保持 引用完整性（Referential Integrity） 的约束。一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。
• 作用：建立子表与父表的联系，保证数据一致性。外键值必须匹配父表主键/唯一键的值，或者为 NULL。防止“孤儿数据”。

在创建表时添加外键

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date DATE,
);

也可以写成

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

• customer_id 是子表中的外键。
• 它引用 customers 表的 id 字段。
• 插入 orders 时，customer_id 必须在 customers.id 中存在。

带约束动作的写法：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

常见动作：

• ON DELETE CASCADE：父表记录删除时，子表相关记录也被删除。
• ON UPDATE CASCADE：父表更新时，子表同步更新。
• 也可设置 SET NULL 或 RESTRICT。
• ON DELETE RESTRICT: 阻止删除一个被引用的行
• SET NULL 和 SET DEFAULT: 将导致在被引用行被删除后，引用行中的引用列被置为空值或它们的默认值

已有表如何添加外键

假设 weather 表中 city 字段唯一，可以加主键(PRIMARY KEY)：

ALTER TABLE weather
ADD CONSTRAINT weather_pkey 
PRIMARY KEY (city);

weather_pkey是主键名

添加外键（FOREIGN KEY）

让 weather.city 引用 cities.name：

ALTER TABLE weather
ADD CONSTRAINT weather_city_fkey
FOREIGN KEY (city) REFERENCES cities(name);

weather_city_fkey是外键名
一个表可以有多个外键。

删除外键

ALTER TABLE orders
DROP CONSTRAINT weather_city_fkey;

---

域 (Domain)

在 SQL 中，域（Domain） 是一种 基于已有数据类型创建的新“类型”，
它可以带有默认值、检查约束（CHECK）或 NOT NULL 限制。

📘 简单来说：
域 = 数据类型 + 约束规则

它本质上是一个可复用的类型定义模板。
当你在多个表中有相同字段类型和规则时，定义域可以让结构更统一、更易维护。

例子：
假设多个表中都有一个字段是“邮箱地址”：

email VARCHAR(100) CHECK (email LIKE '%@%');

如果几十张表都有类似定义，那改规则时就得全部改一遍，非常麻烦。
而如果定义一个域：

CREATE DOMAIN email_domain AS VARCHAR(100)
CHECK (VALUE LIKE '%@%');

之后只需要：

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email email_domain
);

查看已有域:

\dD        -- 查看所有域
\dD domain_name  -- 查看具体域定义

---

WITH 查询（CTE）

WITH 查询（CTE, Common Table Expression）用于在一个 SQL 语句中定义临时的结果集，这些结果集在后续的主查询中可以像普通表一样使用。
它让复杂查询更清晰、可读性更强，也能避免重复子查询。

使用场景 多步逻辑、复用子查询、递归结构

基础语法：

WITH 临时表名 AS (
    子查询
)

SELECT ...
FROM 临时表名
WHERE ...;

递归CET
WITH RECURSIVE 可以实现递归查询（例如层级结构、组织树、路径查询等）：

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Alice'  -- 起点

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

CET和子查询对比：

特性	CTE (WITH)	子查询
可读性	✅ 高，可分步描述复杂逻辑	❌ 难以理解
重用性	✅ 可在同一查询中多次引用	❌ 每次都重新写
性能	稍慢（因为物化）	通常更快（优化器可合并）
是否递归	✅ 支持递归 (WITH RECURSIVE)	❌ 不支持

---