SQL命令使用教程：从入门到精通

-- SQL 标准 / MySQL / PostgreSQL
CREATE DATABASE school_db;

-- 带存在性检查（MySQL 专有；PG 不支持 CREATE DATABASE IF NOT EXISTS）
CREATE DATABASE IF NOT EXISTS school_db;

USE school_db;           -- MySQL；PG 用 \c school_db；SQLite 用 .open
SHOW DATABASES;          -- MySQL；PG 用 \l；SQLite 用 .databases

DROP DATABASE school_db;          -- 不可恢复，慎用
DROP DATABASE IF EXISTS school_db;

-- MySQL
CREATE TABLE students (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(50) NOT NULL,
  age   INT,
  email VARCHAR(100) UNIQUE
);

-- PostgreSQL
CREATE TABLE students (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  age   INT,
  email VARCHAR(100) UNIQUE
);

-- SQLite（类型是"建议"，VARCHAR(100) 的长度不生效）
CREATE TABLE students (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT NOT NULL,
  age   INTEGER,
  email TEXT UNIQUE
);

CREATE TABLE students (
  id         INT PRIMARY KEY,
  name       VARCHAR(50) NOT NULL,
  age        INT CHECK (age >= 0),
  email      VARCHAR(100) UNIQUE,
  class_id   INT,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);

-- 添加列（三方通用）
ALTER TABLE students ADD COLUMN major VARCHAR(50);

-- 修改列类型
ALTER TABLE students MODIFY COLUMN age SMALLINT;          -- MySQL
ALTER TABLE students ALTER COLUMN age TYPE SMALLINT;      -- PostgreSQL
-- SQLite：不支持直接改列类型，需"建新表→拷数据→换名"

-- 改列名
ALTER TABLE students CHANGE COLUMN major department VARCHAR(50);  -- MySQL
ALTER TABLE students RENAME COLUMN major TO department;           -- PostgreSQL / SQLite(3.25+)

-- 删除列
ALTER TABLE students DROP COLUMN email;                   -- 三方均可（SQLite 需 3.35+）

-- 查看表结构
DESCRIBE students;                  -- MySQL
\d students                         -- PostgreSQL（psql 元命令）
PRAGMA table_info(students);        -- SQLite

-- 删除
DROP TABLE students;                -- 删表（含结构）
DROP TABLE IF EXISTS students;      -- 存在才删，建议加
TRUNCATE TABLE students;            -- 清空数据保留结构（MySQL/PG）
DELETE FROM students;               -- SQLite 用这个替代 TRUNCATE

INSERT INTO students (name, age, email) VALUES
  ('张三', 18, 'zhangsan@example.com'),
  ('李四', 19, 'lisi@example.com');

SELECT * FROM students;                              -- 全部列
SELECT name, age FROM students;                      -- 指定列
SELECT DISTINCT major FROM students;                 -- 去重
SELECT * FROM students WHERE age > 18 AND major = 'CS';
SELECT * FROM students ORDER BY age DESC, name ASC;  -- 多列排序
SELECT * FROM students ORDER BY age DESC LIMIT 5;                       -- 前 5
SELECT * FROM students ORDER BY age DESC LIMIT 5 OFFSET 10;             -- 第 11-15 行

UPDATE students SET age = 20 WHERE name = '张三';
DELETE FROM students WHERE name = '王五';

-- PostgreSQL / SQLite
INSERT INTO students (id, name) VALUES (1, '张三')
ON CONFLICT (id) DO UPDATE SET name = excluded.name;

-- MySQL
INSERT INTO students (id, name) VALUES (1, '张三')
ON DUPLICATE KEY UPDATE name = VALUES(name);

SELECT * FROM students WHERE age = NULL;    -- ❌ 永远返回空
SELECT * FROM students WHERE age IS NULL;   -- ✅ 正确
SELECT * FROM students WHERE age IS NOT NULL;

SELECT COUNT(*) AS cnt_all, COUNT(age) AS cnt_age FROM students;

3|2

SELECT name, COALESCE(age, 0) AS safe_age FROM students;   -- 三方通用，推荐
SELECT name, IFNULL(age, 0) FROM students;                 -- MySQL / SQLite

ORDER BY age ASC NULLS LAST      -- PostgreSQL / SQLite(3.30+)
ORDER BY age IS NULL, age        -- MySQL 的变通写法

SELECT
    COUNT(*)          AS total,
    AVG(age)          AS avg_age,
    MIN(age)          AS min_age,
    MAX(age)          AS max_age
FROM students;

SELECT major, COUNT(*) AS cnt, AVG(age) AS avg_age
FROM students
GROUP BY major
HAVING COUNT(*) > 5
ORDER BY cnt DESC;

-- 错的：WHERE 用聚合
SELECT major FROM students WHERE COUNT(*) > 5 GROUP BY major;
-- 对的：
SELECT major FROM students GROUP BY major HAVING COUNT(*) > 5;

CREATE TABLE classes (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), class_id INT);

-- 内连接：只列出有班级的学生
SELECT s.name, c.name AS class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

-- 左连接：所有学生，没班级的也列出（班级名显示 NULL）
SELECT s.name, c.name AS class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

-- 左连接 + 过滤出"没班级的学生"（反连接模式）
SELECT s.name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE c.id IS NULL;

-- 标量子查询：返回单个值
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

-- IN 子查询：返回一列
SELECT * FROM students
WHERE class_id IN (SELECT id FROM classes WHERE name LIKE 'CS%');

-- EXISTS：判断存在性（通常比 IN 快，且对 NULL 更安全）
SELECT * FROM students s
WHERE EXISTS (SELECT 1 FROM classes c WHERE c.id = s.class_id);

-- 相关子查询：子查询引用外层列
SELECT name,
       (SELECT COUNT(*) FROM students s2 WHERE s2.class_id = s1.class_id) AS classmates
FROM students s1;

SELECT name FROM students_a
UNION            -- 并集，去重
SELECT name FROM students_b;

SELECT name FROM students_a
UNION ALL        -- 并集，不去重（更快，能保留重复）
SELECT name FROM students_b;

SELECT name FROM students_a
INTERSECT        -- 交集
SELECT name FROM students_b;

SELECT name FROM students_a
EXCEPT           -- 差集（A 有 B 没有）
SELECT name FROM students_b;

SELECT name,
       CASE
           WHEN age IS NULL THEN '未知'
           WHEN age >= 30   THEN '资深'
           ELSE '新人'
       END AS tag
FROM students;

-- 按自定义顺序排序（比如状态：待处理 → 处理中 → 已完成）
SELECT * FROM tasks
ORDER BY CASE status
             WHEN 'pending'   THEN 1
             WHEN 'running'   THEN 2
             WHEN 'done'      THEN 3
             ELSE 4
         END;

SELECT
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age)      AS 行号,
    RANK()       OVER (ORDER BY age)      AS 排名_跳号,
    DENSE_RANK() OVER (ORDER BY age)      AS 排名_不跳号
FROM students;

-- 分组内排名：每个班级按年龄排名
SELECT name, class_id, age,
       ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY age DESC) AS rn
FROM students;

-- 移动平均：最近 3 行的平均（时间序列分析常用）
SELECT day, amount,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM sales;

WITH class_stats AS (
    SELECT class_id, COUNT(*) AS cnt, AVG(age) AS avg_age
    FROM students
    GROUP BY class_id
)
SELECT c.name, cs.cnt, cs.avg_age
FROM class_stats cs
JOIN classes c ON c.id = cs.class_id
WHERE cs.cnt > 5;

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY level;

-- 创建
CREATE INDEX idx_students_name ON students(name);                  -- 单列
CREATE INDEX idx_students_age_name ON students(age, name);         -- 复合（顺序重要）
CREATE UNIQUE INDEX idx_students_email ON students(email);         -- 唯一索引

-- 删除（注意方言）
DROP INDEX idx_students_name ON students;      -- MySQL（要带 ON 表名）
DROP INDEX idx_students_name;                  -- PostgreSQL / SQLite

EXPLAIN SELECT * FROM students WHERE name = '张三';                     -- MySQL / PG
EXPLAIN QUERY PLAN SELECT * FROM students WHERE name = '张三';          -- SQLite
EXPLAIN ANALYZE SELECT * FROM students WHERE name = '张三';             -- PG / MySQL 8（实际执行并计时）

CREATE VIEW student_summary AS
SELECT s.id, s.name, COUNT(e.course_id) AS courses
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name;

SELECT * FROM student_summary WHERE courses > 3;

CREATE OR REPLACE VIEW student_summary AS ...;   -- 更新视图定义
DROP VIEW student_summary;

BEGIN;   -- 或 START TRANSACTION
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 成功提交；失败用 ROLLBACK 撤销

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- 标准写法
-- 查看/设置（会话级）
SELECT @@transaction_isolation;                   -- MySQL
SHOW transaction_isolation;                       -- PostgreSQL

-- MySQL：用户和主机绑定
GRANT SELECT, INSERT ON school_db.* TO 'app'@'localhost';
REVOKE DELETE ON school_db.* FROM 'app'@'localhost';

-- PostgreSQL：用户是角色，不绑主机
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app;
REVOKE DELETE ON students FROM app;

-- MySQL：反引号
CREATE TABLE `user` (`id` INT, `order` INT);

-- PostgreSQL / SQLite：双引号
CREATE TABLE "user" (id INT, "order" INT);

-- ❌ 危险：把用户输入直接拼进 SQL
query = "SELECT * FROM users WHERE name = '" + input + "'";

-- ❌ 索引失效：对列做了函数运算
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- ✅ 改成范围比较，走索引
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- —— DDL ——
CREATE DATABASE db;                    -- 非 SQLite
DROP DATABASE IF EXISTS db;
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50));
ALTER TABLE t ADD COLUMN x INT;
DROP TABLE IF EXISTS t;
TRUNCATE TABLE t;                      -- 非 SQLite（用 DELETE FROM）

-- —— DML ——
INSERT INTO t (a,b) VALUES (1,2);
UPDATE t SET b=3 WHERE a=1;
DELETE FROM t WHERE a=1;

-- —— DQL ——
SELECT a, b FROM t WHERE a>1 ORDER BY b DESC LIMIT 10 OFFSET 20;
SELECT k, COUNT(*) FROM t GROUP BY k HAVING COUNT(*)>1;
SELECT a.*, b.* FROM a INNER JOIN b ON a.id=b.a_id;
SELECT a FROM t1 UNION ALL SELECT a FROM t2;
SELECT x, ROW_NUMBER() OVER (PARTITION BY k ORDER BY x) FROM t;
WITH cte AS (SELECT * FROM t) SELECT * FROM cte;

-- —— TCL ——
BEGIN; ... COMMIT;   -- 或 ROLLBACK;

-- —— 元信息 ——
DESCRIBE t;            -- MySQL
\d t                   -- PostgreSQL
PRAGMA table_info(t);  -- SQLite
EXPLAIN QUERY PLAN SELECT ...;   -- SQLite