SQL 语法速查表

-- 选所有列
SELECT * FROM 学生;

-- 选指定列，用 AS 起别名（别名是中文时要加引号）
SELECT 姓名, 年龄, 城市 AS 所在城市 FROM 学生;

-- 表达式也能选：算出出生年份
SELECT 姓名, 2026 - 年龄 AS 出生年份 FROM 学生;

-- DISTINCT 去重
SELECT DISTINCT 班级 FROM 学生;
SELECT DISTINCT 城市 FROM 学生 ORDER BY 城市;

-- LIMIT 限制条数，OFFSET 跳过前面几条（分页就是这么做的）
SELECT 姓名, 年龄 FROM 学生 ORDER BY id LIMIT 3;
SELECT 姓名, 年龄 FROM 学生 ORDER BY id LIMIT 3 OFFSET 3;   -- 第二页

-- 比较运算符：=  <>（或 !=）  >  <  >=  <=
SELECT 姓名, 年龄 FROM 学生 WHERE 年龄 >= 17;
SELECT 姓名 FROM 学生 WHERE 班级 <> '一班';

-- AND / OR / NOT，OR 的优先级低于 AND，混用时一定要加括号
SELECT 姓名, 班级, 城市 FROM 学生 WHERE 班级 = '一班' AND 年龄 = 16;
SELECT 姓名 FROM 学生 WHERE 城市 = '北京' OR 城市 = '上海';
SELECT 姓名 FROM 学生 WHERE NOT (班级 = '一班');

-- BETWEEN 闭区间（包含两端）、IN 列表
SELECT 姓名, 年龄 FROM 学生 WHERE 年龄 BETWEEN 16 AND 17;
SELECT 姓名, 城市 FROM 学生 WHERE 城市 IN ('北京', '上海', '广州');

-- LIKE 模糊匹配：% 任意多个字符，_ 恰好一个字符
SELECT 姓名 FROM 学生 WHERE 姓名 LIKE '张%';
SELECT 姓名 FROM 学生 WHERE 姓名 LIKE '_娜';
SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE '王%';

-- NULL 只能用 IS NULL / IS NOT NULL 判断，用 = NULL 永远得不到结果
SELECT 姓名, 城市 FROM 学生 WHERE 城市 IS NOT NULL;

-- 单列排序：ASC 升序（默认）、DESC 降序
SELECT 姓名, 年龄 FROM 学生 ORDER BY 年龄 DESC;
SELECT 姓名, 年龄 FROM 学生 ORDER BY 年龄 ASC, 姓名 ASC;

-- 多列排序：先按班级，同班内按年龄从大到小
SELECT 班级, 姓名, 年龄 FROM 学生 ORDER BY 班级, 年龄 DESC;

-- 可以按序号或别名排序（序号指 SELECT 里的第几列）
SELECT 姓名, 年龄, 2026 - 年龄 AS 出生年份 FROM 学生 ORDER BY 3;

-- 有 NULL 时用 NULLS FIRST / NULLS LAST 控制它们排在哪
SELECT 姓名, 城市 FROM 学生 ORDER BY 城市 NULLS LAST;

-- 五个聚合函数
SELECT COUNT(*) AS 总人数, AVG(年龄) AS 平均年龄,
       MIN(年龄) AS 最小, MAX(年龄) AS 最大, SUM(年龄) AS 年龄总和
FROM 学生;

-- COUNT(*) 数所有行，COUNT(列) 只数该列不为 NULL 的行
SELECT COUNT(*) AS 成绩条数, COUNT(分数) AS 有分数的条数 FROM 成绩;

-- GROUP BY 分组统计：每个班多少人、平均年龄
SELECT 班级, COUNT(*) AS 人数, ROUND(AVG(年龄), 2) AS 平均年龄
FROM 学生
GROUP BY 班级
ORDER BY 人数 DESC;

-- 每科的平均分、最高分、最低分
SELECT 科目, COUNT(*) AS 人次, ROUND(AVG(分数), 1) AS 平均分,
       MAX(分数) AS 最高, MIN(分数) AS 最低
FROM 成绩
GROUP BY 科目
ORDER BY 平均分 DESC;

-- HAVING 是对"分组之后"的结果做筛选，WHERE 是对"分组之前"的行做筛选
SELECT 科目, ROUND(AVG(分数), 1) AS 平均分
FROM 成绩
GROUP BY 科目
HAVING AVG(分数) >= 80;

-- 每个学生的总分和平均分（按学生分组）
SELECT s.姓名, COUNT(c.科目) AS 科目数, SUM(c.分数) AS 总分, ROUND(AVG(c.分数), 1) AS 平均分
FROM 学生 s
JOIN 成绩 c ON c.学生id = s.id
GROUP BY s.id, s.姓名
ORDER BY 总分 DESC;

-- INNER JOIN（写成 JOIN 就是内连接）：只保留两边都匹配的行
SELECT s.姓名, s.班级, c.科目, c.分数
FROM 学生 s
JOIN 成绩 c ON c.学生id = s.id
WHERE s.姓名 = '赵敏'
ORDER BY c.分数 DESC;

-- LEFT JOIN：左表全保留，右表没有匹配就是 NULL
-- 孙浩没有数学成绩，用这个查正好能看出来
SELECT s.姓名, c.科目, c.分数
FROM 学生 s
LEFT JOIN 成绩 c ON c.学生id = s.id AND c.科目 = '数学'
ORDER BY s.id;

-- 找出"缺考"的记录：LEFT JOIN 之后右表主键为 NULL
SELECT s.姓名, s.班级
FROM 学生 s
LEFT JOIN 成绩 c ON c.学生id = s.id AND c.科目 = '数学'
WHERE c.id IS NULL;

-- RIGHT JOIN：右表全保留（等价于把两表位置换过来的 LEFT JOIN）
SELECT s.姓名, c.科目
FROM 成绩 c
RIGHT JOIN 学生 s ON s.id = c.学生id
WHERE s.班级 = '三班'
ORDER BY s.姓名;

-- FULL JOIN：两边都保留，谁没匹配上就是 NULL
SELECT s.姓名, c.科目, c.分数
FROM 学生 s
FULL JOIN 成绩 c ON c.学生id = s.id AND c.科目 = '英语'
WHERE s.姓名 IS NULL OR c.分数 IS NULL;

-- CROSS JOIN：笛卡尔积，行数 = 左表行数 × 右表行数
SELECT a.姓名 AS 甲, b.姓名 AS 乙
FROM 学生 a
CROSS JOIN 学生 b
WHERE a.id < b.id
LIMIT 5;

-- 自连接：同一张表连自己，必须起不同的别名
SELECT a.姓名 AS 同学甲, b.姓名 AS 同学乙, a.城市
FROM 学生 a
JOIN 学生 b ON a.城市 = b.城市 AND a.id < b.id
ORDER BY a.城市, a.id;

-- WHERE IN：分数高于全班平均分的记录
SELECT s.姓名, c.科目, c.分数
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
WHERE c.分数 > (SELECT AVG(分数) FROM 成绩)
ORDER BY c.分数 DESC
LIMIT 5;

-- 标量子查询：SELECT 里直接放一个只返回一个值的查询
SELECT 姓名, 年龄,
       (SELECT COUNT(*) FROM 成绩 c WHERE c.学生id = 学生.id) AS 科目数
FROM 学生
ORDER BY 科目数, 姓名;

-- FROM 子查询（派生表）：先算出每人总分，再排序取前三名
SELECT * FROM (
  SELECT s.姓名, s.班级, SUM(c.分数) AS 总分
  FROM 学生 s JOIN 成绩 c ON c.学生id = s.id
  GROUP BY s.id, s.姓名, s.班级
) AS t
ORDER BY t.总分 DESC
LIMIT 3;

-- EXISTS：只要子查询有结果就为真，常用来判断"有没有"
SELECT s.姓名 FROM 学生 s
WHERE EXISTS (SELECT 1 FROM 成绩 c WHERE c.学生id = s.id AND c.分数 = 99);

SELECT s.姓名 FROM 学生 s
WHERE NOT EXISTS (SELECT 1 FROM 成绩 c WHERE c.学生id = s.id AND c.科目 = '数学');

-- 子查询配合 IN 做批量筛选
SELECT 姓名, 班级 FROM 学生
WHERE id IN (SELECT 学生id FROM 成绩 WHERE 科目 = '数学' AND 分数 < 60);

-- INSERT：指定列名，值按顺序对应
INSERT INTO 学生 (id, 姓名, 班级, 年龄, 城市) VALUES (9, '周涛', '三班', 17, '武汉');

-- 一次插入多行
INSERT INTO 学生 (id, 姓名, 班级, 年龄, 城市) VALUES
  (10, '吴静', '一班', 16, '南京'),
  (11, '郑凯', '二班', 18, '西安');

-- 自增主键可以不写 id
INSERT INTO 成绩 (学生id, 科目, 分数) VALUES (9, '数学', 88), (9, '语文', 79);

SELECT 姓名, 班级, 城市 FROM 学生 WHERE id >= 9;

-- UPDATE：一定要带 WHERE，否则会更新全表
UPDATE 学生 SET 城市 = '长沙市' WHERE id = 9;
UPDATE 成绩 SET 分数 = 分数 + 5 WHERE 学生id = 9 AND 科目 = '语文';
UPDATE 学生 SET 年龄 = 年龄 + 1, 城市 = '北京' WHERE 班级 = '三班';

SELECT id, 姓名, 年龄, 城市 FROM 学生 WHERE id = 9;

-- DELETE：同样必须带 WHERE
DELETE FROM 成绩 WHERE 学生id = 11;
DELETE FROM 学生 WHERE id = 11;

SELECT COUNT(*) AS 剩下几个学生 FROM 学生;

-- RETURNING：插入/更新/删除后直接返回受影响的行
INSERT INTO 学生 (id, 姓名, 班级, 年龄) VALUES (12, '冯磊', '一班', 17)
RETURNING id, 姓名, 班级;

BEGIN;
INSERT INTO 学生 (id, 姓名, 班级, 年龄) VALUES (20, '临时甲', '一班', 16);
INSERT INTO 成绩 (学生id, 科目, 分数) VALUES (20, '数学', 60);
COMMIT;

SELECT s.姓名, c.科目, c.分数 FROM 学生 s JOIN 成绩 c ON c.学生id = s.id WHERE s.id = 20;

-- ROLLBACK 会撤销 BEGIN 之后的所有改动
BEGIN;
DELETE FROM 学生;
ROLLBACK;

SELECT COUNT(*) AS 回滚后人数 FROM 学生;

-- 建一张新表，带上常见约束
DROP TABLE IF EXISTS 课程;
CREATE TABLE 课程 (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,   -- 自增主键
  名称     TEXT NOT NULL UNIQUE,                -- 非空 + 唯一
  学分     REAL NOT NULL DEFAULT 1.0,           -- 默认值
  开课学期 TEXT CHECK (开课学期 IN ('上', '下')),  -- 取值范围检查
  创建时间 TEXT DEFAULT (datetime('now'))
);

-- 不写 id 和 创建时间，让它们自动生成
INSERT INTO 课程 (名称, 学分, 开课学期) VALUES
  ('数据库原理', 3.0, '上'),
  ('数据结构', 4.0, '下');

SELECT * FROM 课程;

-- upsert：冲突时改成更新，而不是报错
INSERT INTO 课程 (名称, 学分, 开课学期) VALUES ('数据结构', 5.0, '下')
ON CONFLICT(名称) DO UPDATE SET 学分 = excluded.学分;

SELECT 名称, 学分 FROM 课程;

-- 查看表结构：每一列的名字、类型、是否可空、默认值、是不是主键
PRAGMA table_info(课程);

-- 外键：SQLite 默认不强制外键，要先打开这个开关
PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS 选课;
CREATE TABLE 选课 (
  学生id INTEGER NOT NULL,
  课程id INTEGER NOT NULL,
  分数   REAL,
  PRIMARY KEY (学生id, 课程id),                       -- 复合主键
  FOREIGN KEY (学生id) REFERENCES 学生(id) ON DELETE CASCADE
);

-- 造一个临时学生来演示，不动表里原有的数据（1 号张伟在 成绩 表里也有记录，删他会牵连别的约束）
INSERT INTO 学生 (id, 姓名, 班级, 年龄) VALUES (90, '临时生', '一班', 16);
INSERT INTO 选课 (学生id, 课程id, 分数) VALUES (90, 101, 90.5), (90, 102, 88.0), (1, 101, 95.0);
SELECT 学生id, COUNT(*) AS 选课数 FROM 选课 GROUP BY 学生id;

-- ON DELETE CASCADE：删掉学生，他名下的选课记录会跟着删掉，别人的不受影响
DELETE FROM 学生 WHERE id = 90;
SELECT 学生id, COUNT(*) AS 删掉之后 FROM 选课 GROUP BY 学生id;

-- 查看这张表定义了哪些外键
PRAGMA foreign_key_list(选课);

-- 索引：加快 WHERE / ORDER BY 的查找速度
CREATE INDEX IF NOT EXISTS idx_成绩_科目 ON 成绩(科目);
CREATE INDEX IF NOT EXISTS idx_学生_班级年龄 ON 学生(班级, 年龄 DESC);

-- 用 EXPLAIN QUERY PLAN 看查询有没有用上索引
EXPLAIN QUERY PLAN
SELECT * FROM 成绩 WHERE 科目 = '数学';

-- 查看表结构和已建索引
PRAGMA table_info(学生);
PRAGMA index_list(成绩);

-- 字符串
SELECT length('数据库原理') AS 字符数,
       upper('abc') AS 大写, lower('ABC') AS 小写,
       substr('数据库原理', 1, 3) AS 前三个,
       replace('一班二班', '班', '年级') AS 替换,
       trim('  空格  ') AS 去空格,
       '姓名：' || '张伟' AS 拼接;

-- printf 格式化（类似 C 的 sprintf）
SELECT 姓名, printf('%s 的年龄是 %d，平均分 %.2f', 姓名, 年龄, 年龄 * 1.0) AS 描述
FROM 学生 LIMIT 3;

-- 数值
SELECT abs(-5) AS 绝对值, round(3.14159, 2) AS 四舍五入,
       7 / 2 AS 整数除法, 7.0 / 2 AS 浮点除法, 7 % 2 AS 取余,
       max(1, 5, 3) AS 多参数最大值, random() % 100 AS 随机数;

-- NULL 处理
SELECT COALESCE(NULL, NULL, '兜底值') AS 第一个非空,
       IFNULL(NULL, 0) AS 空则取零,
       NULLIF(5, 5) AS 相等则为空,
       NULLIF(5, 6) AS 不等则原值;

-- CASE WHEN：SQL 里的 if / else
SELECT 姓名, 城市,
       CASE
         WHEN 城市 = '北京' THEN '华北'
         WHEN 城市 IN ('上海', '杭州') THEN '华东'
         WHEN 城市 = '广州' OR 城市 = '深圳' THEN '华南'
         ELSE '其它'
       END AS 地区
FROM 学生;

-- 用 CASE 做条件计数（行转列的经典写法）
SELECT 科目,
       SUM(CASE WHEN 分数 >= 90 THEN 1 ELSE 0 END) AS 优秀,
       SUM(CASE WHEN 分数 >= 60 AND 分数 < 90 THEN 1 ELSE 0 END) AS 及格,
       SUM(CASE WHEN 分数 < 60 THEN 1 ELSE 0 END) AS 不及格
FROM 成绩
GROUP BY 科目;

-- 日期时间：SQLite 没有专门的日期类型，用 TEXT 存 'YYYY-MM-DD HH:MM:SS'
SELECT date('now') AS 今天,
       datetime('now') AS 现在,
       date('now', '+7 day') AS 七天后,
       date('2026-03-01', '-1 month') AS 上个月,
       strftime('%Y年%m月', '2026-10-04') AS 格式化,
       CAST(julianday('2026-12-31') - julianday('2026-10-04') AS INTEGER) AS 相差天数;

-- 每科排名：ROW_NUMBER 不会并列，RANK 会并列且跳号，DENSE_RANK 并列不跳号
SELECT 科目, s.姓名, c.分数,
       ROW_NUMBER() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS 行号,
       RANK()       OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS 排名,
       DENSE_RANK() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS 密集排名
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
WHERE 科目 = '数学'
ORDER BY 分数 DESC;

-- 在每行旁边显示全班平均分和与平均分的差
SELECT s.姓名, c.科目, c.分数,
       ROUND(AVG(c.分数) OVER (PARTITION BY c.科目), 1) AS 该科平均,
       c.分数 - ROUND(AVG(c.分数) OVER (PARTITION BY c.科目), 1) AS 与平均差
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
ORDER BY c.科目, c.分数 DESC;

-- 累计求和、取上一行/下一行的值
SELECT s.姓名, c.分数,
       SUM(c.分数) OVER (ORDER BY c.id) AS 累计总分,
       LAG(c.分数, 1)  OVER (ORDER BY c.id) AS 上一条分数,
       LEAD(c.分数, 1) OVER (ORDER BY c.id) AS 下一条分数
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
WHERE c.科目 = '语文'
ORDER BY c.id;

-- 取出每个学生的最高分那一科（分组 Top 1 的标准做法）
SELECT 姓名, 科目, 分数 FROM (
  SELECT s.姓名, c.科目, c.分数,
         ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY c.分数 DESC) AS rn
  FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
) WHERE rn = 1
ORDER BY 分数 DESC;

-- NTILE 分档：把成绩分成四档
SELECT s.姓名, c.科目, c.分数,
       NTILE(4) OVER (PARTITION BY c.科目 ORDER BY c.分数 DESC) AS 档位
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
WHERE c.科目 = '英语'
ORDER BY c.分数 DESC;

-- 把复杂的子查询起个名字，读起来清楚很多
WITH 总分 AS (
  SELECT 学生id, SUM(分数) AS 总分, COUNT(*) AS 科目数
  FROM 成绩
  GROUP BY 学生id
)
SELECT s.姓名, s.班级, t.总分, t.科目数, ROUND(t.总分 * 1.0 / t.科目数, 1) AS 平均分
FROM 学生 s
LEFT JOIN 总分 t ON t.学生id = s.id
ORDER BY t.总分 DESC;

-- 可以写多个 CTE，用逗号隔开，后面的能引用前面的
WITH 每科平均 AS (
  SELECT 科目, AVG(分数) AS 平均分 FROM 成绩 GROUP BY 科目
),
高于平均 AS (
  SELECT c.学生id, c.科目, c.分数, a.平均分
  FROM 成绩 c JOIN 每科平均 a ON a.科目 = c.科目
  WHERE c.分数 > a.平均分
)
SELECT s.姓名, COUNT(*) AS 高于平均的科目数
FROM 高于平均 h JOIN 学生 s ON s.id = h.学生id
GROUP BY s.id, s.姓名
ORDER BY 高于平均的科目数 DESC, s.姓名;

-- 递归 CTE：生成 1 到 10 的数列
WITH RECURSIVE 数列(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM 数列 WHERE n < 10
)
SELECT n, n * n AS 平方 FROM 数列;

-- 用递归 CTE 生成日期序列，做"每天"的报表骨架
WITH RECURSIVE 日期(d) AS (
  SELECT date('2026-10-01')
  UNION ALL
  SELECT date(d, '+1 day') FROM 日期 WHERE d < '2026-10-07'
)
SELECT d AS 日期, strftime('%w', d) AS 星期几 FROM 日期;

-- UNION 去重合并，UNION ALL 不去重（更快）
SELECT 姓名 FROM 学生 WHERE 城市 = '北京'
UNION
SELECT 姓名 FROM 学生 WHERE 班级 = '三班';

SELECT 科目 FROM 成绩 WHERE 学生id = 1
UNION ALL
SELECT 科目 FROM 成绩 WHERE 学生id = 2
ORDER BY 科目;

-- INTERSECT 交集、EXCEPT 差集
SELECT id FROM 学生 WHERE 班级 = '一班'
INTERSECT
SELECT id FROM 学生 WHERE 年龄 = 16;

SELECT 姓名 FROM 学生
EXCEPT
SELECT s.姓名 FROM 学生 s JOIN 成绩 c ON c.学生id = s.id WHERE c.分数 < 70;

-- 视图：把常用查询存成一个"虚拟表"
CREATE VIEW IF NOT EXISTS 学生总分 AS
SELECT s.id, s.姓名, s.班级, SUM(c.分数) AS 总分, ROUND(AVG(c.分数), 1) AS 平均分
FROM 学生 s LEFT JOIN 成绩 c ON c.学生id = s.id
GROUP BY s.id, s.姓名, s.班级;

SELECT * FROM 学生总分 ORDER BY 总分 DESC;
SELECT 班级, ROUND(AVG(平均分), 1) AS 班级平均分 FROM 学生总分 GROUP BY 班级;

-- 删除视图
DROP VIEW IF EXISTS 学生总分;

-- typeof() 看出每列实际存的是什么类型
CREATE TABLE IF NOT EXISTS 类型试验 (a, b, c, d);
INSERT INTO 类型试验 VALUES (1, '文本', 3.14, NULL);

SELECT typeof(a) AS a类型, typeof(b) AS b类型, typeof(c) AS c类型, typeof(d) AS d类型 FROM 类型试验;

-- 建表时不写类型也能存（但不推荐），写了类型也只是"建议"
CREATE TABLE IF NOT EXISTS 宽松表 (数字 TEXT);
INSERT INTO 宽松表 VALUES (123), ('456');
SELECT 数字, typeof(数字) AS 实际类型 FROM 宽松表;

-- 隐式转换：字符串和数字比较时会尽量转成数字
SELECT '9' > '10' AS 字符串比较, 9 > 10 AS 数字比较, '9' + 1 AS 字符串相加;

-- 显式转换用 CAST
SELECT CAST('95' AS INTEGER) + 5 AS 转成整数,
       CAST(95 AS TEXT) || '分' AS 转成文本,
       CAST('3.9' AS REAL) AS 转成实数;

-- 排序时数字列里混进文本会导致顺序诡异
SELECT 数字 FROM 宽松表 ORDER BY 数字;              -- 按文本排：'123' < '456'
SELECT 数字 FROM 宽松表 ORDER BY CAST(数字 AS INTEGER);