Investigator Veritatis

01-MySql常用语句

6 min read

1 增删改查核心语句

1.1 INSERT — 增

1.1.1 单行插入：最基础形式，明确指定列与值的对应关系。

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

1.1.2 多行批量插入：一次提交多条记录，性能远优于循环单条插入。

INSERT INTO table_name (col1, col2)
VALUES (val1, val2),
       (val3, val4),
       (val5, val6);

1.1.3 从查询结果插入：将 SELECT 的结果集直接写入目标表，ETL 场景核心手法。

INSERT INTO target_table (col1, col2)
SELECT col_a, col_b
FROM   source_table
WHERE  condition;

1.1.4 冲突处理（Upsert）：主键/唯一键冲突时自动转为更新，避免重复写入报错。

INSERT INTO table_name (id, col1, col2)
VALUES (1, 'a', 'b')
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2);

1.2 DELETE — 删

1.2.1 条件删除：WHERE 是生命线，裸跑 DELETE 将清空全表。

DELETE FROM table_name
WHERE condition;

1.2.2 多表关联删除：删除满足 JOIN 条件的 t1 行，t2 仅作筛选依据。

DELETE t1
FROM   table1 t1
JOIN   table2 t2 ON t1.fk = t2.id
WHERE  t2.status = 'invalid';

1.2.3 子查询删除：用子查询精确圈定待删集合。

DELETE FROM table_name
WHERE id IN (
    SELECT id FROM other_table WHERE condition
);

1.3 UPDATE — 改

1.3.1 单表条件更新：同样，WHERE 缺失即全表更新。

UPDATE table_name
SET    col1 = val1,
       col2 = val2
WHERE  condition;

1.3.2 关联更新（JOIN UPDATE）：依据另一张表的数据更新当前表，数据同步场景高频使用。

UPDATE table1 t1
JOIN   table2 t2 ON t1.fk = t2.id
SET    t1.col1 = t2.col_a,
       t1.col2 = t2.col_b
WHERE  t2.status = 'active';

1.3.3 条件表达式更新：CASE WHEN 实现一条语句多分支赋值，避免多次 UPDATE 往返。

UPDATE table_name
SET col1 = CASE
               WHEN condition1 THEN val1
               WHEN condition2 THEN val2
               ELSE val3
           END
WHERE id IN (1, 2, 3);

1.4 SELECT — 查（大一统核心）

现实中 90% 的复杂查询都是对这一骨架的扩展与组合。

1.4.1 完整骨架：子句的书写顺序与执行顺序不同，执行顺序见注释。

SELECT   col1, col2, AGG_FUNC(col3)   -- ⑦ 投影
FROM     table_name                    -- ① 确定数据源
JOIN     other_table ON ...            -- ② 关联
WHERE    condition                     -- ③ 行级过滤（聚合前）
GROUP BY col1, col2                    -- ④ 分组
HAVING   AGG_FUNC(col3) > value        -- ⑤ 组级过滤（聚合后）
ORDER BY col1 DESC                     -- ⑥ 排序
LIMIT    N OFFSET M;                   -- ⑧ 分页

1.4.2 JOIN 家族：所有多表关联的底层原语，现实中绝大多数用 INNER / LEFT。

-- 交集：两表都匹配才返回
INNER JOIN table2 ON t1.id = t2.fk
 
-- 左全保留：左表不匹配行以 NULL 填充右侧
LEFT  JOIN table2 ON t1.id = t2.fk
 
-- 右全保留（可用 LEFT JOIN 反写替代，保持风格统一）
RIGHT JOIN table2 ON t1.id = t2.fk
 
-- 笛卡尔积（慎用）
CROSS JOIN table2

1.4.3 子查询三形态：

-- 标量子查询：返回单值，用于 SELECT / WHERE
SELECT name, (SELECT MAX(score) FROM scores WHERE uid = u.id) AS best
FROM users u;
 
-- IN 子查询：返回集合做过滤条件
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE city = 'Tokyo');
 
-- 派生表（内联视图）：FROM 后的子查询，当作临时表使用
SELECT t.dept, t.avg_sal
FROM (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept
) t
WHERE t.avg_sal > 50000;

1.4.4 CTE（公用表表达式）：用 WITH 给子查询命名，逻辑更清晰，可复用，支持递归。

WITH
  active_users AS (
      SELECT id, name FROM users WHERE status = 'active'
  ),
  user_orders AS (
      SELECT user_id, COUNT(*) AS cnt
      FROM orders
      GROUP BY user_id
  )
SELECT u.name, o.cnt
FROM   active_users u
JOIN   user_orders  o ON u.id = o.user_id
ORDER BY o.cnt DESC;

1.4.5 窗口函数：在保留原始行的前提下叠加聚合/排名，是分析型查询的核武器。

SELECT
    name,
    dept,
    salary,
    RANK()        OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept,
    SUM(salary)   OVER (PARTITION BY dept)                      AS dept_total,
    LAG(salary,1) OVER (PARTITION BY dept ORDER BY salary DESC) AS prev_salary
FROM employees;

2 执行顺序速查

理解执行顺序是写对 WHERE vs HAVING、列别名能否复用的关键。

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
  ①      ②      ③        ④          ⑤        ⑥        ⑦         ⑧
  • 1 个陷阱：SELECT 里定义的别名，WHERE / HAVING 中不能直接用（执行时 SELECT 还没跑），ORDER BY 可以用。
  • 1 个推论：窗口函数在 SELECT 阶段计算，因此也不能出现在 WHERE / GROUP BY 里，需套一层派生表或 CTE 才能过滤。

3 变种归一表

现实场景底层语句组合
分页列表SELECT + ORDER BY + LIMIT/OFFSET
去重统计SELECT DISTINCT / COUNT(DISTINCT)
排行榜SELECT + RANK()/DENSE_RANK() 窗口函数
同比/环比SELECT + LAG()/LEAD() 窗口函数
树形递归WITH RECURSIVE CTE + SELECT
数据同步INSERT … ON DUPLICATE KEY / UPDATE JOIN
软删除UPDATE SET deleted_at = NOW()
批量导入INSERT INTO … SELECT FROM
多条件分流UPDATE / SELECT + CASE WHEN

Recent Notes

Graph View

Backlinks