SQL 查询与数据库设计小结

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 DESC
LIMIT 10;

-- INNER JOIN：两表都有的记录
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- LEFT JOIN：左表全部记录，右表没有的为 NULL
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- RIGHT JOIN：右表全部记录
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- FULL JOIN：两表全部记录
SELECT * FROM a FULL JOIN b ON a.id = b.a_id;

-- CROSS JOIN：笛卡尔积
SELECT * FROM a CROSS JOIN b;

SELECT
  COUNT(*) AS total,
  COUNT(DISTINCT category) AS categories,
  AVG(price) AS avg_price,
  SUM(quantity) AS total_quantity,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM products;

-- 每个分类的商品数量
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- 筛选出商品数大于 10 的分类
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- 标量子查询（返回单个值）
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 行子查询（IN）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

-- EXISTS 子查询
SELECT * FROM categories c
WHERE EXISTS (
  SELECT 1 FROM products p WHERE p.category_id = c.id
);

-- ROW_NUMBER：行号
SELECT
  name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

-- 分组排名
SELECT
  category,
  name,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

-- LAG / LEAD：前后行访问
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_revenue;

-- 普通 CTE
WITH active_users AS (
  SELECT * FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM active_users WHERE plan = 'premium';

-- 递归 CTE（树形结构）
WITH RECURSIVE org_tree AS (
  -- 根节点
  SELECT id, name, parent_id, 1 AS level
  FROM employees WHERE parent_id IS NULL

  UNION ALL

  -- 递归子节点
  SELECT e.id, e.name, e.parent_id, t.level + 1
  FROM employees e
  JOIN org_tree t ON e.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

-- B-Tree 索引（最常用）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（列顺序很重要）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 部分索引（只索引需要的行）
CREATE INDEX idx_active_orders ON orders(status)
WHERE status = 'pending';

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

WHERE a = 1                        -- ✅
WHERE a = 1 AND b = 2              -- ✅
WHERE a = 1 AND b = 2 AND c = 3    -- ✅
WHERE a = 1 AND c = 3              -- ✅ 只用到了 a 列
WHERE b = 2                        -- ❌ 不走索引
WHERE c = 3                        -- ❌ 不走索引

-- 查看查询计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 查看实际执行时间和计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

BEGIN;                              -- 开始事务
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                             -- 提交

-- 回滚
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;                           -- 撤销所有改动

-- 设置当前事务的隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       VARCHAR(255) NOT NULL UNIQUE,
  nickname    VARCHAR(100) NOT NULL,
  age         INTEGER CHECK (age >= 0 AND age < 150),
  plan        VARCHAR(20) DEFAULT 'free',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id   UUID NOT NULL REFERENCES users(id),
  amount    NUMERIC(10,2) NOT NULL,
  status    VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 外键上通常需要建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 传统分页（OFFSET 在数据量大时性能差）
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20 OFFSET 0;
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- 游标分页（推荐：性能好，不受数据变动影响）
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20;
-- 取最后一条的 created_at 作为游标
SELECT * FROM articles
WHERE created_at < '2024-09-15 10:30:00'
ORDER BY created_at DESC LIMIT 20;

-- PostgreSQL
INSERT INTO users (id, email, nickname)
VALUES ('abc', 'test@example.com', 'Test')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,
    nickname = EXCLUDED.nickname;

-- MySQL
INSERT INTO users (id, email, nickname)
VALUES ('abc', 'test@example.com', 'Test')
ON DUPLICATE KEY UPDATE
email = VALUES(email),
nickname = VALUES(nickname);

INSERT INTO users (email, nickname) VALUES
('alice@example.com', 'Alice'),
('bob@example.com', 'Bob'),
('carol@example.com', 'Carol');