高頻 SQL 語句速查手冊（持續更新中）

高頻 SQL 語句速查手冊

本文匯總開發運維場景中最高頻的 SQL 語句模板，分類整理便於快速查閱。內容持續更新中……

一、數據定義語言 (DDL)

表操作

-- 創建表
CREATE TABLE `tbl_name` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 刪除表
DROP TABLE IF EXISTS `tbl_name`;

-- 修改表結構
ALTER TABLE `tbl_name`
  ADD COLUMN `status` TINYINT NOT NULL DEFAULT 0 AFTER `name`,
  DROP COLUMN `old_col`,
  MODIFY COLUMN `name` VARCHAR(128) NOT NULL DEFAULT '',
  CHANGE COLUMN `old_name` `new_name` VARCHAR(64) NOT NULL DEFAULT '',
  ADD INDEX `idx_status` (`status`),
  DROP INDEX `idx_name`;

-- 重命名表
RENAME TABLE `old_name` TO `new_name`;

二、數據操作語言 (DML)

增刪改查核心模板

-- 插入單行
INSERT INTO `tbl` (`col1`, `col2`) VALUES ('val1', 'val2');

-- 批量插入（高性能）
INSERT INTO `tbl` (`col1`, `col2`) VALUES
  ('a', 1), ('b', 2), ('c', 3)
ON DUPLICATE KEY UPDATE `col2` = VALUES(`col2`);

-- 條件更新
UPDATE `tbl` SET `col1` = 'new_val', `updated_at` = NOW()
WHERE `id` = 100 AND `status` = 1;

-- 條件刪除
DELETE FROM `tbl` WHERE `created_at` < '2023-01-01' AND `status` = 0;

-- 分頁查詢（深分頁優化）
SELECT * FROM `tbl`
WHERE `id` > 10000  -- 利用覆蓋索引游標分頁
ORDER BY `id` LIMIT 20;

三、高級查詢技巧

多表關聯

-- 內連接
SELECT a.`id`, a.`name`, b.`order_no`
FROM `user` a
INNER JOIN `order` b ON a.`id` = b.`user_id`
WHERE a.`status` = 1;

-- 左連接（保留左表所有行）
SELECT a.`name`, COALESCE(b.`cnt`, 0) AS `order_cnt`
FROM `user` a
LEFT JOIN (
  SELECT `user_id`, COUNT(*) AS `cnt` FROM `order` GROUP BY `user_id`
) b ON a.`id` = b.`user_id`;

窗口函數

-- 行號/排名
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY `dept_id` ORDER BY `salary` DESC) AS `rn`,
  RANK() OVER (ORDER BY `salary` DESC) AS `rk`
FROM `employee`;

-- 移動平均
SELECT `dt`, `amount`,
  AVG(`amount`) OVER (ORDER BY `dt` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS `ma7`
FROM `daily_sales`;

CTE 遞歸查詢

WITH RECURSIVE `org_tree` AS (
  SELECT `id`, `name`, `parent_id`, 1 AS `level` FROM `org` WHERE `parent_id` = 0
  UNION ALL
  SELECT c.`id`, c.`name`, c.`parent_id`, p.`level` + 1
  FROM `org` c INNER JOIN `org_tree` p ON c.`parent_id` = p.`id`
)
SELECT * FROM `org_tree` ORDER BY `level`, `id`;

四、管理維護語句

-- 查看進程
SHOW FULL PROCESSLIST;

-- 殺死長事務
KILL 12345;

-- 表大小統計
SELECT TABLE_NAME, 
  ROUND(DATA_LENGTH/1024/1024, 2) AS `data_mb`,
  ROUND(INDEX_LENGTH/1024/1024, 2) AS `idx_mb`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db_name'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 索引使用情況
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'db_name';

-- 慢查詢分析
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

五、事務控制

START TRANSACTION;
  UPDATE `account` SET `balance` = `balance` - 100 WHERE `id` = 1;
  UPDATE `account` SET `balance` = `balance` + 100 WHERE `id` = 2;
COMMIT;
-- 或出錯時 ROLLBACK;

更多實用模板持續收集中，歡迎補充建議。