SQL 建表、读写与结果

CREATE DATABASE bookshop;   -- 等价的命令行工具：createdb bookshop

\c bookshop

DROP DATABASE bookshop;

CREATE TABLE books (
    id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- 自增主键
    title      text NOT NULL,                          -- 书名，必填
    author     text,                                   -- 作者，允许暂缺
    category   text NOT NULL,                          -- 类别
    price      numeric(10, 2) CHECK (price >= 0),      -- 价格：精确小数，不许为负
    stock      integer NOT NULL DEFAULT 0,             -- 库存
    tags       text[],                                 -- 标签：文本数组
    info       jsonb,                                  -- 扩展信息：JSON 文档
    created_at timestamptz NOT NULL DEFAULT now()      -- 上架时间，默认当前时刻
);

CREATE TABLE customers (
    id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    city  text,                                        -- 城市可能未知，允许 NULL
    email varchar(255) UNIQUE                          -- 邮箱不允许重复
);

CREATE TABLE orders (
    id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    book_id     bigint NOT NULL,                       -- 买的是哪本书
    customer_id bigint,                                -- 未登录下单时为 NULL（匿名订单）
    quantity    integer NOT NULL DEFAULT 1,            -- 数量
    amount      numeric(10, 2) NOT NULL CHECK (amount >= 0),  -- 实付金额
    ordered_at  timestamptz NOT NULL DEFAULT now()     -- 下单时间
);

DROP TABLE IF EXISTS old_logs;   -- 表不存在时静默跳过而不是报错

SELECT '{"b": 1, "a": 2, "b": 3}'::json;   -- 原样输出：{"b": 1, "a": 2, "b": 3}
SELECT '{"b": 1, "a": 2, "b": 3}'::jsonb;  -- 键排序、去重复键：{"a": 2, "b": 3}

SELECT title FROM books WHERE 'database' = ANY (tags);      -- 含某标签的书
UPDATE books SET tags = array_append(tags, 'bestseller')    -- 给书追加一个标签
WHERE  id = 2 RETURNING tags;
SELECT title, info ->> 'publisher' AS publisher FROM books; -- 取 JSON 字段为文本
SELECT title FROM books
WHERE  info @> '{"publisher": "启明出版社"}';               -- JSONB 包含匹配

INSERT INTO books (title, author, category, price, stock, tags, info)
VALUES
    ('SQL 入门经典',    '林一', 'language',  49.00, 20,
     '{sql,basic}',          '{"publisher": "启明出版社", "pages": 240}'),
    ('PostgreSQL 实战', '林一', 'database', 129.00, 10,
     '{postgres,database}',  '{"publisher": "启明出版社", "pages": 512}'),
    ('数据库原理',      '赵三', 'database', 139.00, 5,
     '{theory,database}',    '{"publisher": "学海出版社", "pages": 640}'),
    ('PostgreSQL 进阶', '林一', 'database',  99.00, 8,
     '{postgres,advanced}',  '{"publisher": "启明出版社", "pages": 380}'),
    ('Python 快速上手', '王二', 'language',  89.00, 30,
     '{python,basic}',       '{"publisher": "学海出版社", "pages": 300}');

INSERT INTO books (title, author, category, price, stock)
VALUES ('Redis 深度历险', '钱四', 'database', 79.00, 12)
RETURNING id, created_at;   -- 返回数据库实际生成的自增 id 和默认时间

INSERT INTO customers (name, city, email) VALUES
    ('张三', '北京', 'zhangsan@example.com'),
    ('李四', '上海', 'lisi@example.com'),
    ('王五', '北京', 'wangwu@example.com'),
    ('赵六', NULL,  NULL),                  -- 城市未知
    ('孙七', '广州', 'sunqi@example.com');  -- 注意：这位顾客从未下单

INSERT INTO orders (book_id, customer_id, quantity, amount) VALUES
    (1, 1,    1,  49.00),   -- 张三买《SQL 入门经典》
    (1, 2,    2,  98.00),   -- 李四买两本
    (2, 3,    1, 129.00),   -- 王五买《PostgreSQL 实战》
    (5, 1,    1,  89.00),   -- 张三买《Python 快速上手》
    (3, NULL, 1, 139.00);   -- 匿名订单：未登录顾客买《数据库原理》

-- 再造一条两年前的旧订单，供后面演示按时间清理：
INSERT INTO orders (book_id, customer_id, quantity, amount, ordered_at)
VALUES (2, 3, 1, 129.00, now() - interval '2 years');

CREATE TABLE tmp_books (
    title    text,
    author   text,
    category text,
    price    numeric(10, 2)
);

INSERT INTO tmp_books VALUES
    ('算法图解', '周五', 'language', 69.00),
    ('编译原理', '吴六', 'language', 149.00);

INSERT INTO books (title, author, category, price)
SELECT title, author, category, price
FROM   tmp_books
WHERE  price < 100;    -- 只有 69 元的《算法图解》会被搬入 books

-- 显式指定 id = 1 重录这本书；该 id 已存在，则更新书名和价格
INSERT INTO books (id, title, author, category, price, stock)
VALUES (1, 'SQL 入门经典（第 2 版）', '林一', 'language', 59.00, 25)
ON CONFLICT (id) DO UPDATE
    SET title = EXCLUDED.title, price = EXCLUDED.price
RETURNING id, title, price;

-- 邮箱已存在就什么都不做（email 列上有 UNIQUE 约束）
INSERT INTO customers (name, city, email)
VALUES ('张三', '北京', 'zhangsan@example.com')
ON CONFLICT (email) DO NOTHING;

UPDATE books SET price = price * 0.9   -- 九折促销（这里只对 id = 1 这本书）
WHERE  id = 1
RETURNING id, title, price;

-- 第一步：这条 WHERE 会命中谁？
SELECT id, amount, ordered_at
FROM   orders
WHERE  ordered_at < now() - interval '1 year';

BEGIN;                                                 -- 开启事务
DELETE FROM orders
WHERE  ordered_at < now() - interval '1 year'
RETURNING *;
ROLLBACK;                                              -- 先撤销，把旧订单留给 2.9 节再用

SELECT title, price FROM books WHERE price < 100;   -- 比较条件
SELECT title FROM books WHERE author LIKE '林%';    -- 模糊匹配：作者以"林"开头
SELECT name FROM customers WHERE city IS NULL;      -- 判断空值

SELECT title, price FROM books
ORDER BY price DESC, title ASC;        -- 先按价格降序，同价再按书名升序

SELECT id, title FROM books
ORDER BY id
LIMIT 10 OFFSET 20;                    -- 每页 10 条、跳过前 20 条，即第 3 页

SELECT name, city FROM customers ORDER BY city NULLS FIRST;   -- 空城市排最前

SELECT DISTINCT city FROM customers;   -- 去掉完全重复的行

-- 第 1 步：全表计数
SELECT count(*) FROM books;

-- 第 2 步：按类别分组统计
SELECT category, count(*) AS n, min(price) AS min_price, max(price) AS max_price
FROM   books
GROUP BY category;

-- 第 3 步：先用 WHERE 减少参与分组的行（输入过滤）
SELECT category, count(*) AS n, max(price) AS max_price
FROM   books
WHERE  category LIKE 'd%'            -- 只看 database 类
GROUP BY category;

-- 第 4 步：再用 HAVING 过滤组（结果过滤），如"最高价低于 150 元的类别"
SELECT category, count(*) AS n, max(price) AS max_price
FROM   books
WHERE  category LIKE 'd%'
GROUP BY category
HAVING max(price) < 150;

SELECT title, price
FROM   books
WHERE  price = (SELECT max(price) FROM books);   -- 先算出最贵价，再找等值的书

SELECT o.id, c.name AS buyer, b.title AS book, o.quantity, o.amount
FROM   orders o
JOIN   customers c ON c.id = o.customer_id     -- 订单接顾客
JOIN   books     b ON b.id = o.book_id         -- 再接图书
ORDER BY o.id;

SELECT c.name, o.id AS order_id
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

SELECT c.name
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE  o.id IS NULL;      -- 右表列 IS NULL = 没有订单的顾客

SELECT c.name, o.amount
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 100;  -- 条件放 ON：孙七保留

SELECT b1.title AS book1, b2.title AS book2
FROM   books b1
JOIN   books b2 ON b1.author = b2.author AND b1.id < b2.id;

SELECT name FROM customers
WHERE  id NOT IN (SELECT customer_id FROM orders);   -- 危险写法！

SELECT name
FROM   customers c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

WITH city_sales AS (
    SELECT c.city, sum(o.amount) AS total
    FROM   orders o
    JOIN   customers c ON c.id = o.customer_id
    GROUP BY c.city
)
SELECT city, total
FROM   city_sales
WHERE  total > 200;      -- 先按城市汇总销售额，再挑出总额超过 200 元的城市

WITH city_sales AS (
    SELECT c.city, sum(o.amount) AS total
    FROM   orders o
    JOIN   customers c ON c.id = o.customer_id
    GROUP BY c.city
),
big_cities AS (
    SELECT city FROM city_sales WHERE total > 200
)
SELECT b.city
FROM   big_cities b
ORDER BY b.city;

WITH RECURSIVE t(n) AS (
    VALUES (1)                          -- 起点：第一行是 1
    UNION ALL
    SELECT n + 1 FROM t WHERE n < 100   -- 递归：从上一轮结果推出下一轮
)
SELECT sum(n) FROM t;                   -- 结果 5050

-- 先建归档表（列与 orders 相同）
CREATE TABLE orders_archive (
    id          bigint PRIMARY KEY,
    book_id     bigint NOT NULL,
    customer_id bigint,
    quantity    integer NOT NULL,
    amount      numeric(10, 2) NOT NULL,
    ordered_at  timestamptz NOT NULL
);

WITH moved AS (
    DELETE FROM orders
    WHERE  ordered_at < now() - interval '1 year'
    RETURNING *                         -- 被删的行在这里"交接"
)
INSERT INTO orders_archive SELECT * FROM moved;