萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL 語句 22 條實戰法寶:備份、DDL、分頁、去重、隨機、系統表查詢與進階技巧

彙整 22 條高頻實用 SQL 技巧,涵蓋備份、結構變更、分頁、去重、隨機抽樣、系統中繼資料查詢、條件聚合、記錄範圍選取等核心場景。每條附標準語法與適用資料庫版本註記。

1. 備份資料庫 (SQL Server)

-- 建立備份裝置
USE master;
EXEC sp_addumpdevice 'disk', 'testBack', 'C:\Backup\MyDB.bak';
-- 執行完整備份
BACKUP DATABASE MyDB TO testBack WITH COMPRESSION, CHECKSUM;

2. 新增欄位 (通用)

ALTER TABLE TableName ADD ColumnName DataType [NULL|NOT NULL] [DEFAULT ...];
注意:多數資料庫**不支援刪除欄位** (SQL Server 支援 DROP COLUMN),型別修改受限。

3. 主鍵管理

-- 新增
ALTER TABLE TableName ADD CONSTRAINT PK_TableName_Col PRIMARY KEY (Col);
-- 刪除
ALTER TABLE TableName DROP CONSTRAINT PK_TableName_Col;

4. 複製表結構 (不含資料)

-- SQL Server / Access / PostgreSQL
SELECT * INTO NewTable FROM SourceTable WHERE 1=0;
-- 或
SELECT TOP 0 * INTO NewTable FROM SourceTable;

5. 複製表資料 (同結構)

INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT ColA, ColB, ColC FROM SourceTable [WHERE ...];

6. 跨資料庫/連結伺服器複製

-- 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';

7. 顯示文章、提交者、最後回覆時間 (相關子查詢)

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;

8. 外部聯結 (LEFT/RIGHT/FULL JOIN)

SELECT a.Col1, a.Col2, b.Col3, b.Col4
FROM TableA a
LEFT OUTER JOIN TableB b ON a.Key = b.Key;

9. 線上檢視表 / 衍生表查詢

SELECT * FROM (SELECT Col1, Col2, Col3 FROM TableA) AS T WHERE T.Col1 > 1;

10. BETWEEN 邊界包含性

SELECT * FROM Table1 WHERE TimeCol BETWEEN @Start AND @End;  -- 含邊界
SELECT * FROM Table1 WHERE Col NOT BETWEEN 10 AND 20;         -- 不含邊界

11. 排程提醒:提前 N 分鐘

-- SQL Server
SELECT * FROM Schedule WHERE DATEDIFF(minute, StartTime, GETDATE()) > 5;
-- Access/VBA
WHERE DateDiff('n', StartTime, Now()) > 5

12. 高效分頁 (SQL Server 2005-2012 風格)

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。

13. 取前 N 筆

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

14. 每組最大值完整列 (分組 Top 1)

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)。

15. 集合差集:A 不在 B、C 中 (去重)

SELECT a FROM TableA
EXCEPT SELECT a FROM TableB
EXCEPT SELECT a FROM TableC;  -- SQL Server / PostgreSQL / Oracle (MINUS)

16. 隨機取 10 筆

SELECT TOP 10 * FROM TableName ORDER BY NEWID();  -- SQL Server
SELECT * FROM TableName ORDER BY RAND() LIMIT 10; -- MySQL

17. 產生隨機 GUID

SELECT NEWID();  -- SQL Server
SELECT UUID();   -- MySQL
SELECT SYS_GUID() FROM DUAL; -- Oracle

18. 刪除重複列 (保留最大 ID)

DELETE FROM TableName
WHERE Id NOT IN (SELECT MAX(Id) FROM TableName GROUP BY Col1, Col2, ...);
大表用 ROW_NUMBER() + CTE 刪除效能更好。

19. 列出所有使用者資料表

SELECT name FROM sysobjects WHERE xtype = 'U' ORDER BY name;  -- SQL Server 2000
SELECT name FROM sys.tables ORDER BY name;                    -- SQL Server 2005+

20. 列出表所有欄位

SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName');  -- 2000
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableName'); -- 2005+

21. 條件聚合 (CASE 聚合) — 橫向報表神器

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

22. 選取第 10-15 筆 (分頁舊技法)

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 ServerOracleMySQLPostgreSQLAccess
TOP/LIMITTOPROWNUM/FETCHLIMITLIMITTOP
分頁標準OFFSET/FETCH (2012+)OFFSET/FETCH (12c+)LIMIT/OFFSETLIMIT/OFFSET不支援
EXCEPT/MINUSEXCEPTMINUSEXCEPT (8.0+)EXCEPT不支援
ROW_NUMBER2005+8i+8.0+8.4+不支援
CASE 聚合全版本全版本全版本全版本IIF/Switch

收藏這 22 條法寶,應對 80% 日常 SQL 開發場景綽綽有餘。

copyright © 萬盛學電腦網 all rights reserved