彙整 22 條高頻實用 SQL 技巧,涵蓋備份、結構變更、分頁、去重、隨機抽樣、系統中繼資料查詢、條件聚合、記錄範圍選取等核心場景。每條附標準語法與適用資料庫版本註記。
-- 建立備份裝置
USE master;
EXEC sp_addumpdevice 'disk', 'testBack', 'C:\Backup\MyDB.bak';
-- 執行完整備份
BACKUP DATABASE MyDB TO testBack WITH COMPRESSION, CHECKSUM;
ALTER TABLE TableName ADD ColumnName DataType [NULL|NOT NULL] [DEFAULT ...];
注意:多數資料庫**不支援刪除欄位** (SQL Server 支援 DROP COLUMN),型別修改受限。
-- 新增
ALTER TABLE TableName ADD CONSTRAINT PK_TableName_Col PRIMARY KEY (Col);
-- 刪除
ALTER TABLE TableName DROP CONSTRAINT PK_TableName_Col;
-- SQL Server / Access / PostgreSQL
SELECT * INTO NewTable FROM SourceTable WHERE 1=0;
-- 或
SELECT TOP 0 * INTO NewTable FROM SourceTable;
INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT ColA, ColB, ColC FROM SourceTable [WHERE ...];
-- SQL Server 連結伺服器
INSERT INTO LocalDB.dbo.Target (Col1, Col2)
SELECT Col1, Col2 FROM [LinkedServer].RemoteDB.dbo.Source WHERE ...;
-- Access 絕對路徑
INSERT INTO Target (Col1) SELECT Col1 FROM Source IN 'C:\Path\Data.mdb';
SELECT a.Title, a.UserName, b.LastReplyTime
FROM Articles a
JOIN (SELECT ArticleId, MAX(ReplyTime) AS LastReplyTime FROM Replies GROUP BY ArticleId) b
ON a.ArticleId = b.ArticleId;
SELECT a.Col1, a.Col2, b.Col3, b.Col4
FROM TableA a
LEFT OUTER JOIN TableB b ON a.Key = b.Key;
SELECT * FROM (SELECT Col1, Col2, Col3 FROM TableA) AS T WHERE T.Col1 > 1;
SELECT * FROM Table1 WHERE TimeCol BETWEEN @Start AND @End; -- 含邊界
SELECT * FROM Table1 WHERE Col NOT BETWEEN 10 AND 20; -- 不含邊界
-- SQL Server
SELECT * FROM Schedule WHERE DATEDIFF(minute, StartTime, GETDATE()) > 5;
-- Access/VBA
WHERE DateDiff('n', StartTime, Now()) > 5
SELECT TOP @PageSize b.*
FROM (
SELECT TOP (@PageIndex * @PageSize) KeyCol, SortCol
FROM TableName ORDER BY SortCol DESC
) a
JOIN TableName b ON b.KeyCol = a.KeyCol
ORDER BY a.SortCol;
SQL Server 2012+ **強烈建議**使用標準 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY。
SELECT TOP 10 * FROM Table1 WHERE ...; -- SQL Server / Access
SELECT * FROM Table1 WHERE ... LIMIT 10; -- MySQL / PostgreSQL / SQLite
SELECT * FROM Table1 WHERE ... FETCH FIRST 10 ROWS ONLY; -- Oracle 12c+ / ANSI
SELECT a, b, c FROM TableName ta
WHERE a = (SELECT MAX(a) FROM TableName tb WHERE tb.b = ta.b);
現代寫法:ROW_NUMBER() OVER (PARTITION BY b ORDER BY a DESC) = 1 (SQL Server 2005+/Oracle/PostgreSQL)。
SELECT a FROM TableA
EXCEPT SELECT a FROM TableB
EXCEPT SELECT a FROM TableC; -- SQL Server / PostgreSQL / Oracle (MINUS)
SELECT TOP 10 * FROM TableName ORDER BY NEWID(); -- SQL Server
SELECT * FROM TableName ORDER BY RAND() LIMIT 10; -- MySQL
SELECT NEWID(); -- SQL Server
SELECT UUID(); -- MySQL
SELECT SYS_GUID() FROM DUAL; -- Oracle
DELETE FROM TableName
WHERE Id NOT IN (SELECT MAX(Id) FROM TableName GROUP BY Col1, Col2, ...);
大表用 ROW_NUMBER() + CTE 刪除效能更好。
SELECT name FROM sysobjects WHERE xtype = 'U' ORDER BY name; -- SQL Server 2000
SELECT name FROM sys.tables ORDER BY name; -- SQL Server 2005+
SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName'); -- 2000
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableName'); -- 2005+
SELECT Type,
SUM(CASE Vendor WHEN 'A' THEN Pcs ELSE 0 END) AS VendorA,
SUM(CASE Vendor WHEN 'B' THEN Pcs ELSE 0 END) AS VendorB,
SUM(CASE Vendor WHEN 'C' THEN Pcs ELSE 0 END) AS VendorC
FROM TableName
GROUP BY Type;
結果:
Type VendorA VendorB VendorC
電腦 2 0 0
光盤 2 1 0
手機 0 1 1
SELECT TOP 5 * FROM (
SELECT TOP 15 * FROM TableName ORDER BY Id ASC
) AS T ORDER BY Id DESC;
現代標準:OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY (SQL Server 2012+/Oracle 12c+/PostgreSQL/MySQL 8.0+)。
| 技巧 | SQL Server | Oracle | MySQL | PostgreSQL | Access |
|---|---|---|---|---|---|
| TOP/LIMIT | TOP | ROWNUM/FETCH | LIMIT | LIMIT | TOP |
| 分頁標準 | OFFSET/FETCH (2012+) | OFFSET/FETCH (12c+) | LIMIT/OFFSET | LIMIT/OFFSET | 不支援 |
| EXCEPT/MINUS | EXCEPT | MINUS | EXCEPT (8.0+) | EXCEPT | 不支援 |
| ROW_NUMBER | 2005+ | 8i+ | 8.0+ | 8.4+ | 不支援 |
| CASE 聚合 | 全版本 | 全版本 | 全版本 | 全版本 | IIF/Switch |
收藏這 22 條法寶,應對 80% 日常 SQL 開發場景綽綽有餘。