SQL 基礎語法速查大全：建庫、建表、索引、CTE、CASE、彙總

DDL 基礎

-- 建立/刪除資料庫
CREATE DATABASE db_name;
DROP DATABASE db_name;

-- 備份 (SQL Server)
USE master;
EXEC sp_addumpdevice 'disk', 'bak_dev', 'D:\backup\db.bak';
BACKUP DATABASE pubs TO bak_dev;

-- 建表
CREATE TABLE tbl (col1 INT, col2 VARCHAR(50));

-- 依現有表建表
CREATE TABLE new_tbl LIKE old_tbl;              -- 僅結構
CREATE TABLE new_tbl AS SELECT * FROM old_tbl WHERE 1=0;  -- 結構+資料(無資料)

-- 增加欄位 / 主鍵 / 自增
ALTER TABLE tbl ADD col3 DATETIME;
ALTER TABLE tbl ADD PRIMARY KEY (col1);
-- SQL Server 自增
CREATE TABLE t (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(20));

-- 索引
CREATE INDEX idx_name ON tbl(col1);
DROP INDEX idx_name ON tbl;

CTE 遞迴去重範例

;WITH cte AS (
    SELECT openid, ROW_NUMBER() OVER (PARTITION BY openid ORDER BY openid) AS rn
    FROM #temp
)
DELETE FROM cte WHERE rn > 1;

CASE WHEN 條件更新並輸出刪除行

UPDATE pn
SET pn.id = CASE WHEN t.id > '' THEN t.id ELSE pn.id END
OUTPUT DELETED.id INTO #tmp
FROM target_table pn WITH (NOLOCK)
JOIN #temp t ON pn.key = t.key;

常用 DML 與彙總

SELECT * FROM tbl;
INSERT INTO new_tbl (id, name) VALUES (1, '張三');
DELETE FROM tbl WHERE condition;
SELECT SUM(col1) AS sum_v FROM tbl;
SELECT AVG(col1) AS avg_v FROM tbl;
SELECT MAX(col1) AS max_v FROM tbl;

快速參考卡