本文匯總涵蓋 DDL、DML、DCL、進階查詢、管理維護等場景的高頻 SQL 模板，兼容 MySQL、PostgreSQL、SQL Server 主流語法，建議收藏備查。
CREATE DATABASE `db_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE IF EXISTS `db_name`;
USE `db_name`;-- 創建表
CREATE TABLE `tbl` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL DEFAULT '',
`status` TINYINT NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 基於現有表創建
CREATE TABLE `new_tbl` LIKE `old_tbl`; -- 僅結構
CREATE TABLE `new_tbl` AS SELECT * FROM `old_tbl` WHERE 1=0; -- 結構+數據(空)
CREATE TABLE `new_tbl` AS SELECT `id`, `name` FROM `old_tbl`; -- 指定列帶數據
-- 刪除表
DROP TABLE IF EXISTS `tbl`;
-- 修改表
ALTER TABLE `tbl`
ADD COLUMN `email` VARCHAR(100) NOT NULL DEFAULT '' 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_email` (`email`),
DROP INDEX `idx_status`,
RENAME TO `new_tbl_name`;CREATE [UNIQUE] INDEX `idx_name` ON `tbl` (`col1`, `col2`);
DROP INDEX `idx_name` ON `tbl`;
CREATE VIEW `v_user_order` AS
SELECT u.`id`, u.`name`, o.`order_no`, o.`amount`
FROM `user` u LEFT JOIN `order` o ON u.`id` = o.`user_id`;
DROP VIEW IF EXISTS `v_user_order`;-- 查詢
SELECT * FROM `tbl` WHERE `status` = 1;
SELECT `id`, `name` FROM `tbl` WHERE `name` LIKE '%keyword%';
SELECT * FROM `tbl` ORDER BY `created_at` DESC LIMIT 10 OFFSET 20;
-- 插入
INSERT INTO `tbl` (`name`, `status`) VALUES ('test', 1);
INSERT INTO `tbl` (`name`, `status`) VALUES ('a',1), ('b',2), ('c',3);
INSERT INTO `tbl` SELECT `name`, `status` FROM `other_tbl`;
-- 更新
UPDATE `tbl` SET `status` = 2, `updated_at` = NOW() WHERE `id` = 100;
-- 刪除
DELETE FROM `tbl` WHERE `status` = 0 AND `created_at` < '2023-01-01';
TRUNCATE TABLE `tbl`; -- 快速清空，重置自增 IDSELECT COUNT(*) AS `total` FROM `tbl`;
SELECT SUM(`amount`) AS `sum_amt`, AVG(`amount`) AS `avg_amt` FROM `order`;
SELECT MAX(`score`) AS `max_s`, MIN(`score`) AS `min_s` FROM `exam`;-- 僅結構（SQL Server）
SELECT * INTO `new_tbl` FROM `old_tbl` WHERE 1<>1;
-- 通用
CREATE TABLE `new_tbl` LIKE `old_tbl`;
-- 複製數據
INSERT INTO `target` (`a`, `b`, `c`) SELECT `d`, `e`, `f` FROM `source`;
-- 跨庫複製（MySQL）
INSERT INTO `db2`.`tbl` SELECT * FROM `db1`.`tbl`;-- IN 子查詢
SELECT * FROM `a` WHERE `id` IN (SELECT `id` FROM `b` WHERE `type` = 1);
-- 集合運算
(SELECT `id` FROM `a`) UNION (SELECT `id` FROM `b`); -- 去重合併
(SELECT `id` FROM `a`) UNION ALL (SELECT `id` FROM `b`); -- 保留重複
(SELECT `id` FROM `a`) EXCEPT (SELECT `id` FROM `b`); -- 差集
(SELECT `id` FROM `a`) INTERSECT (SELECT `id` FROM `b`); -- 交集-- 內連接
SELECT a.`name`, b.`order_no` FROM `user` a INNER JOIN `order` b ON a.`id` = b.`user_id`;
-- 左外連接
SELECT a.`name`, COALESCE(b.`cnt`, 0) FROM `user` a
LEFT JOIN (SELECT `user_id`, COUNT(*) AS `cnt` FROM `order` GROUP BY `user_id`) b ON a.`id` = b.`user_id`;
-- 右外連接 / 全外連接（MySQL 用 UNION 模擬）
SELECT * FROM `a` LEFT JOIN `b` ON a.`id` = b.`id`
UNION
SELECT * FROM `a` RIGHT JOIN `b` ON a.`id` = b.`id`;SELECT * FROM (SELECT `id`, `name` FROM `user` WHERE `status` = 1) t WHERE t.`id` > 100;SELECT * FROM `tbl` WHERE `created_at` BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM `tbl` WHERE `score` NOT BETWEEN 60 AND 100;SELECT * FROM `tbl` WHERE `status` IN (1, 2, 3);
SELECT * FROM `tbl` WHERE `code` NOT IN ('A', 'B', 'C');DELETE FROM `a` WHERE NOT EXISTS (SELECT 1 FROM `b` WHERE `a`.`id` = `b`.`a_id`);SELECT * FROM `a`
LEFT JOIN `b` ON a.`id` = b.`id`
RIGHT JOIN `c` ON a.`id` = c.`id`
INNER JOIN `d` ON a.`id` = d.`id` WHERE ...;-- 5分鐘前提醒
SELECT * FROM `schedule` WHERE TIMESTAMPDIFF(MINUTE, `start_time`, NOW()) > 5;-- 傳統 OFFSET（深分頁慢）
SELECT * FROM `tbl` ORDER BY `id` LIMIT 10 OFFSET 100000;
-- 游標分頁（利用覆蓋索引）
SELECT * FROM `tbl` WHERE `id` > 100000 ORDER BY `id` LIMIT 10;
-- 變量分頁（存儲過程）
SET @start = 0, @page_size = 10;
PREPARE stmt FROM 'SELECT * FROM `tbl` LIMIT ?, ?';
EXECUTE stmt USING @start, @page_size;SELECT a.* FROM `tbl` a
WHERE a.`score` = (SELECT MAX(`score`) FROM `tbl` b WHERE b.`group_id` = a.`group_id`);(SELECT `id` FROM `a`) EXCEPT (SELECT `id` FROM `b`) EXCEPT (SELECT `id` FROM `c`);-- MySQL
SELECT * FROM `tbl` ORDER BY RAND() LIMIT 10;
-- PostgreSQL
SELECT * FROM `tbl` TABLESAMPLE BERNOULLI (10); -- 10% 採樣
-- SQL Server
SELECT TOP 10 * FROM `tbl` ORDER BY NEWID();-- 保留最大 ID
DELETE FROM `tbl` WHERE `id` NOT IN (SELECT MAX(`id`) FROM `tbl` GROUP BY `col1`, `col2`);
-- 複製去重重建
CREATE TABLE `tmp` AS SELECT DISTINCT * FROM `tbl`;
TRUNCATE TABLE `tbl`;
INSERT INTO `tbl` SELECT * FROM `tmp`;
DROP TABLE `tmp`;
-- 自增列輔助去重
ALTER TABLE `tbl` ADD `row_id` INT AUTO_INCREMENT PRIMARY KEY;
DELETE FROM `tbl` WHERE `row_id` NOT IN (SELECT MAX(`row_id`) FROM `tbl` GROUP BY `col1`, `col2`);
ALTER TABLE `tbl` DROP `row_id`;-- 所有用戶表
SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'db_name' AND `TABLE_TYPE` = 'BASE TABLE';
-- 表所有列
SELECT `COLUMN_NAME`, `DATA_TYPE`, `COLUMN_COMMENT` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'db_name' AND `TABLE_NAME` = 'tbl_name';
-- 相關視圖/存儲過程/函數
SELECT * FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_DEFINITION` LIKE '%tbl_name%';-- 1=1 技巧
WHERE 1=1
AND (IFNULL(@name, '') = '' OR `name` LIKE CONCAT('%', @name, '%'))
AND (IFNULL(@status, -1) = -1 OR `status` = @status);ANALYZE TABLE `tbl`; -- 更新統計信息
OPTIMIZE TABLE `tbl`; -- 整理碎片、回收空間
CHECK TABLE `tbl`; -- 檢查損壞
REPAIR TABLE `tbl`; -- 修復-- SQL Server
DBCC SHRINKDATABASE ('db_name');
DBCC SHRINKFILE ('log_file', 100); -- 壓縮日誌至 100MBEXEC sp_change_users_login 'update_one', 'new_user', 'old_user';RESTORE VERIFYONLY FROM DISK = 'E:\backup.bak';
ALTER DATABASE `db` SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('db', REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK;
ALTER DATABASE `db` SET MULTI_USER;-- 核心邏輯：填充日誌 → 截斷 → 循環直到達到目標大小
-- 詳細參考原文完整腳本EXEC sp_changeobjectowner 'tbl_name', 'dbo';
-- 批量轉移存儲過程
CREATE PROC BatchChangeOwner @OldOwner VARCHAR(128), @NewOwner VARCHAR(128) AS
DECLARE cur CURSOR FOR SELECT name FROM sysobjects WHERE user_name(uid) = @OldOwner;
-- 循環執行 sp_changeobjectownerDECLARE @i INT = 1;
WHILE @i < 30 BEGIN
INSERT INTO `test` (`userid`) VALUES (@i);
SET @i = @i + 1;
END;
-- 成績補分邏輯
WHILE (SELECT MIN(`score`) FROM `tb`) < 60 BEGIN
UPDATE `tb` SET `score` = `score` * 1.01 WHERE `score` < 60;
IF (SELECT MIN(`score`) FROM `tb`) > 60 BREAK;
ENDSELECT * FROM `tbl` ORDER BY `name` COLLATE chinese_prc_stroke_ci_as;SELECT ENCRYPT('raw_pwd');
SELECT PWDENCRYPT('raw_pwd');
SELECT PWDCOMPARE('raw_pwd', 'encrypted_pwd') = 1; -- 1=匹配DECLARE @cols VARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(name), ',') FROM sys.columns WHERE object_id = OBJECT_ID('tbl');
SET @sql = 'SELECT ' + @cols + ' FROM tbl';
EXEC sp_executesql @sql;EXEC master..xp_fixeddrives;IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM `a`) = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM `b`)
PRINT '相等'; ELSE PRINT '不相等';-- 前 N 條
SELECT TOP N * FROM `tbl`;
-- N 到 M 條（需主鍵 ID）
SELECT TOP (M-N) * FROM `tbl` WHERE `id` IN (SELECT TOP M `id` FROM `tbl`) ORDER BY `id` DESC;
-- 最後 N 條
SELECT TOP N * FROM `tbl` ORDER BY `id` DESC;-- 創建鏈接服務器
EXEC sp_addlinkedserver 'REMOTE', '', 'SQLOLEDB', '192.168.1.100';
EXEC sp_addlinkedsrvlogin 'REMOTE', 'false', NULL, 'user', 'pwd';
-- 查詢
SELECT * FROM REMOTE.db.dbo.tbl;
-- 導入
SELECT * INTO local_tbl FROM REMOTE.db.dbo.tbl;
-- 導出
INSERT REMOTE.db.dbo.tbl SELECT * FROM local_tbl;
-- 移除
EXEC sp_dropserver 'REMOTE', 'droplogins';
-- OPENROWSET 臨時連接
SELECT * FROM OPENROWSET('SQLOLEDB', '192.168.1.100'; 'user'; 'pwd', db.dbo.tbl);| 分類 | 函數 | 說明 |
|---|---|---|
| 字符串 | DATALENGTH, SUBSTRING, LEFT/RIGHT, ISNULL, REPLACE, STUFF | 長度、截取、空值替換、替換、插入刪除 |
| 日期 | GETDATE, DATEADD, DATEDIFF, DATEPART, CONVERT, FORMAT | 當前時間、加減、差值、部件、格式化 |
| 轉換 | CAST, CONVERT, TRY_CAST, TRY_CONVERT | 類型轉換、安全轉換 |
| 分析 | ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE | 窗口函數（2012+） |
| 聚合 | CHECKSUM_AGG, STRING_AGG (2017+) | 校驗和、字符串聚合 |
本手冊持續更新，歡迎補充實戰中驗證的高頻語句模板。
