第2章 SQL 基础（MySQL 逐条对照） ​

-- MySQL
CREATE TABLE t_user (
  id     INT PRIMARY KEY AUTO_INCREMENT,
  name   VARCHAR(50) NOT NULL,
  age    INT DEFAULT 0,
  email  VARCHAR(100) UNIQUE,
  score  DECIMAL(10,2),
  created DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PostgreSQL
CREATE TABLE t_user (
  id      serial PRIMARY KEY,           -- 自增：serial = 建序列 + 默认 nextval
  name    varchar(50) NOT NULL,
  age     int DEFAULT 0,
  email   varchar(100) UNIQUE,
  score   numeric(10,2),
  created timestamp DEFAULT now()
);
-- 注释要单独写（PG 建表时不能内联 COMMENT）
COMMENT ON TABLE  t_user IS '用户表';
COMMENT ON COLUMN t_user.name IS '用户名';

-- 写法1：serial（老牌语法糖，最省事）
id serial PRIMARY KEY          -- 等价于 int + 建一个序列 + 默认 nextval(...)；要更大用 bigserial

-- 写法2：GENERATED AS IDENTITY（SQL 标准，PG10+ 推荐）
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY    -- ALWAYS 禁止手动插入该列
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY -- BY DEFAULT 允许手动指定（迁移带 id 数据时方便）

-- 写法3：手动序列（需要独立管理号段/跨表共享时）
CREATE SEQUENCE seq_user_id;
-- 插入时：id = nextval('seq_user_id')

CREATE TABLE t_product (
  id     serial PRIMARY KEY,
  name   text,
  attrs  jsonb            -- 动态属性袋
);

INSERT INTO t_product(name, attrs) VALUES
 ('衬衫', '{"size":"L","color":"白","tags":["促销","春夏"]}');

-- 按键取值（-> 返回 json，->> 返回文本）
SELECT name, attrs->>'size' AS size FROM t_product WHERE attrs->>'color' = '白';

-- 数组包含判断，并可给 jsonb 建 GIN 索引加速（第6章）
SELECT * FROM t_product WHERE attrs @> '{"tags":["促销"]}';

-- 存在某键
SELECT * FROM t_product WHERE attrs ? 'size';

-- 把上面的表跑一遍（就查 2.2 建的 t_product 那行衬衫）
SELECT attrs @> '{"size":"L"}'                        AS 包含_L,
       attrs @> '{"tags":["促销"]}'                   AS 包含_促销,
       attrs ?  'size'                                 AS 有_size_键,
       attrs ?  'weight'                               AS 有_weight_键,
       attrs ?| ARRAY['size','weight']                 AS 任一键,
       attrs ?& ARRAY['size','color']                  AS 全有键,
       attrs ->> 'size'                                AS 取文本,
       attrs #>> '{tags,0}'                            AS 路径取值
FROM t_product;

SELECT name AS "用户名", 'hello' AS greeting   -- 双引号=别名标识符，单引号=字符串
FROM t_user
WHERE name = 'O''Brien';                        -- 单引号转义：再写一个单引号

-- 单条，插入后直接拿回生成的主键和计算列（比 MySQL 再查 LAST_INSERT_ID 优雅）
INSERT INTO t_user(name, email) VALUES ('tom', 'tom@x.com') RETURNING id, created;

-- 批量插入
INSERT INTO t_user(name) VALUES ('a'), ('b'), ('c');

-- 插入查询结果
INSERT INTO t_user(name) SELECT name FROM t_archive WHERE flag = true;

-- PG 没有 MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE，改用 ON CONFLICT（见 2.4.3）

-- 普通更新
UPDATE t_user SET score = score + 10 WHERE id = 1;

-- ⚠️ PG 没有 MySQL 的 UPDATE ... LIMIT n 写法。要限量更新用 CTID/子查询：
UPDATE t_user SET status = 'vip'
WHERE id IN (SELECT id FROM t_user WHERE score > 100 ORDER BY id LIMIT 10);

-- 更新可以带 RETURNING，删也能带 RETURNING
DELETE FROM t_user WHERE id = 5 RETURNING name;

-- UPDATE ... FROM：用另一张表的数据来更新（对应 MySQL 的 UPDATE a JOIN b）
UPDATE t_user u
SET score = s.add_score
FROM t_score_delta s
WHERE u.id = s.user_id;

-- MySQL 思维：
-- INSERT INTO t_config(k,v) VALUES('theme','dark')
-- ON DUPLICATE KEY UPDATE v='dark';

-- PostgreSQL 写法：冲突目标写清楚，再指定更新动作
INSERT INTO t_config(k, v) VALUES ('theme', 'dark')
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;    -- EXCLUDED 指代"本想插入的那行"

-- 只想"冲突就忽略"：
INSERT INTO t_config(k, v) VALUES ('x', '1')
ON CONFLICT (k) DO NOTHING;

-- 写法1：PostgreSQL 风格（也和 MySQL 兼容）
SELECT * FROM t_user ORDER BY id LIMIT 10 OFFSET 20;   -- 跳过20取10

-- ⚠️ 不能用 MySQL 的逗号简写 LIMIT 20,10 —— PG 不认识这种写法，必须写 OFFSET 关键字

-- 写法2：SQL 标准写法（跨库移植性好）
SELECT * FROM t_user ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT '张' || '三' AS full_name;                    -- 拼接用 ||
SELECT now()::date AS today;                          -- :: 截断到日期
SELECT TO_CHAR(created, 'YYYY-MM-DD HH24:MI') FROM t_user;   -- 格式化
SELECT EXTRACT(YEAR FROM now()) AS this_year;         -- 取部分
SELECT date_trunc('month', now()) AS month_start;      -- 截断到月初（做报表超有用）
SELECT age(now(), created) FROM t_user;               -- 返回 interval，如 '3 days 02:10:00'
SELECT id, string_agg(name, ',') FROM ... GROUP BY id; -- 组内拼串

-- MySQL
CREATE TABLE t_user (
  id     INT PRIMARY KEY AUTO_INCREMENT,
  name   VARCHAR(50) NOT NULL,
  age    INT DEFAULT 0,
  email  VARCHAR(100) UNIQUE,
  score  DECIMAL(10,2),
  created DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE t_user (
  id      int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,   -- 写法2：SQL 标准自增
  name    varchar(50) NOT NULL,
  age     int DEFAULT 0,
  email   varchar(100) UNIQUE,
  score   numeric(10,2),
  created timestamptz DEFAULT now()                           -- 带时区，别用裸 timestamp
);

COMMENT ON TABLE  t_user      IS '用户表';
COMMENT ON COLUMN t_user.name IS '用户名';
COMMENT ON COLUMN t_user.score IS '积分';

\d+ t_user

SELECT obj_description('t_user'::regclass)               AS table_comment;
--  table_comment
-- ---------------
--  用户表

SELECT col_description('t_user'::regclass, 2) AS name_comment,
       col_description('t_user'::regclass, 5) AS score_comment;
--  name_comment | score_comment
-- --------------+---------------
--  用户名       | 积分

INSERT INTO t_user(name, score) VALUES('amy', 12.5) RETURNING id, created;
--   id |            created
--  ----+-------------------------------
--    1 | 2026-10-02 09:12:33.104822+08     ← id 从 1 起，created 由默认值 now() 填上

INSERT INTO t_user(name) VALUES('bob') RETURNING id, score, created IS NOT NULL AS has_created;
--   id | score | has_created
--  ----+-------+-------------
--    2 |       | t                 ← 没给的 score 保持 NULL（不是 0），DEFAULT 0 只作用于 age

INSERT INTO t_user(id, name) VALUES(99, 'zed');
-- ERROR:  cannot insert a non-DEFAULT value for column "id"
-- SQLSTATE: 428C9        ← ALWAYS 禁止手写值；第 7 章「迁移要带原 id」必须用 BY DEFAULT

CREATE TABLE t_2x_prod (id serial PRIMARY KEY, name text, attrs jsonb);

INSERT INTO t_2x_prod(name, attrs) VALUES
  ('衬衫', '{"size":"L","color":"白","tags":["促销","春夏"]}'),
  ('键盘', '{"size":"104键","color":"黑","tags":["新品"]}'),
  ('鼠标', '{"color":"灰"}');

SELECT id, name, attrs FROM t_2x_prod ORDER BY id;   -- 先确认输入落对了（3 行）

SELECT name, attrs->>'size' AS size FROM t_2x_prod WHERE attrs->>'color' = '白';
--  name | size
-- ------+------
--  衬衫 | L                                  ← 只有衬衫的 color 是「白」

SELECT name FROM t_2x_prod WHERE attrs ? 'size' ORDER BY id;
--  name
-- ------
--  衬衫
--  键盘                                    ← 鼠标那行没有 size 键，被排除

SELECT name FROM t_2x_prod WHERE attrs @> '{"tags":["促销"]}' ORDER BY id;
--  name
-- ------
--  衬衫

-- 要多个标签同时命中（数组内「与」）：
SELECT name FROM t_2x_prod WHERE attrs @> '{"tags":["促销","新品"]}';
--  name
-- --------            ← 空：没有一行同时带这两个标签

-- 「或」得靠索引化的两个条件并起来：
SELECT name FROM t_2x_prod
 WHERE attrs @> '{"tags":["促销"]}' OR attrs @> '{"tags":["新品"]}' ORDER BY id;
--  name
-- ------
--  衬衫
--  键盘

CREATE TABLE t_2x_note (id int PRIMARY KEY, name text);
INSERT INTO t_2x_note VALUES (1, ''), (2, NULL), (3, 'abc');

SELECT id, name, name IS NULL AS is_null, length(name) AS len, coalesce(name,'<NULL>') AS show
  FROM t_2x_note ORDER BY id;
--  id | name | is_null | len |  show
--  ----+------+---------+-----+----------
--   1 |      | f       |   0 |              ← 空串：不是 NULL，长度 0
--   2 |      | t       |     | <NULL>       ← NULL：长度也是 NULL（不是 0）
--   3 | abc  | f       |   3 | abc

SELECT id FROM t_2x_note WHERE name = '';        -- → 1（只有空串行）
SELECT id FROM t_2x_note WHERE name IS NULL;     -- → 2（只有 NULL 行）
SELECT id FROM t_2x_note WHERE name IS DISTINCT FROM '';   -- → 2,3（NULL 安全比较的写法）
SELECT count(*) FROM t_2x_note WHERE coalesce(name,'') = ''; -- → 2（把两种「没填」并成一类）

CREATE TABLE t_2x_config (
  k       text PRIMARY KEY,          -- 主键即唯一索引，可作为冲突目标
  v       text NOT NULL,
  updated timestamptz NOT NULL DEFAULT now()
);

INSERT INTO t_2x_config(k, v) VALUES('theme', 'light')
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v, updated = now()
RETURNING k, v, updated > now() - interval '10 seconds' AS just_written,
          (xmax = 0) AS inserted;
--   k     |   v     | just_written | inserted
-- --------+---------+--------------+----------
--  theme  | light   | t            | t        ← inserted=t 说明这次真的插了新行

INSERT INTO t_2x_config(k, v) VALUES('theme', 'dark')
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v, updated = now()
RETURNING k, v, (xmax = 0) AS inserted;
--   k     |  v    | inserted
-- --------+-------+----------
--  theme  | dark  | f        ← inserted=f：改的是既有行

SELECT count(*) FROM t_2x_config;    -- → 1（始终是同一行被更新，没有重复配置项）
SELECT v FROM t_2x_config WHERE k='theme';   -- → dark

-- 只在旧版本更低时才覆盖（乐观 UPSERT），否则 DO UPDATE 会变成无条件覆盖
INSERT INTO t_2x_config(k, v) VALUES('ver', 'v2')
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v
 WHERE t_2x_config.v <> EXCLUDED.v
RETURNING k, v;
--   k   | v
-- ------+----
--  ver  | v2