Skip to content

Data Science › Databases › SQL 方言速查

SQL 方言速查 (SQL Dialect Cheatsheet) ​

SELECT、JOIN、GROUP BY、window function 在各資料庫大致相同(見 SQL 查詢基礎);差異集中在取前幾筆、空值、日期、型別轉換、字串與匯入匯出。先查對照表,再看各平台細節。

對照表 ​

功能PostgreSQLSQL Server (T-SQL)MySQLOracleSnowflakeDuckDB
取前 n 筆LIMIT nSELECT TOP (n) / OFFSET ... FETCHLIMIT nFETCH FIRST n ROWS ONLY(12c+)LIMIT nLIMIT n
空值替代COALESCEISNULL / COALESCEIFNULL / COALESCENVL / COALESCENVL / IFNULL / COALESCEIFNULL / COALESCE
現在時間NOW()GETDATE() / SYSDATETIME()NOW()SYSDATECURRENT_TIMESTAMP()NOW()
取年份EXTRACT(YEAR FROM d)DATEPART(year, d) / YEAR(d)YEAR(d)EXTRACT(YEAR FROM d)YEAR(d) / DATE_PART('year', d)YEAR(d) / EXTRACT
日期加減d + INTERVAL '1 month'DATEADD(month, 1, d)DATE_ADD(d, INTERVAL 1 MONTH)ADD_MONTHS(d, 1)DATEADD(month, 1, d)d + INTERVAL 1 MONTH
日期差(天)d2 - d1DATEDIFF(day, d1, d2)DATEDIFF(d2, d1)d2 - d1DATEDIFF(day, d1, d2)DATE_DIFF('day', d1, d2)
截斷到月DATE_TRUNC('month', d)DATETRUNC(month, d)(2022+)DATE_FORMAT(d, '%Y-%m-01')TRUNC(d, 'MM')DATE_TRUNC('month', d)DATE_TRUNC('month', d)
字串 → 日期TO_DATE(s, 'YYYY-MM-DD') / s::dateCAST(s AS date) / CONVERT(date, s, 23)STR_TO_DATE(s, '%Y-%m-%d')TO_DATE(s, 'YYYY-MM-DD')TO_DATE(s)STRPTIME(s, '%Y-%m-%d') / s::DATE
字串串接|| / CONCAT+ / CONCATCONCAT|||| / CONCAT|| / CONCAT
不分大小寫 LIKEILIKE依 collation(預設不分)依 collation(預設不分)UPPER(x) LIKEILIKEILIKE
字串聚合STRING_AGG(x, ',')STRING_AGG(x, ',')(2017+)GROUP_CONCAT(x)LISTAGG(x, ',')LISTAGG(x, ',')STRING_AGG(x, ',')
展開陣列 / JSONjsonb_array_elements / UNNESTOPENJSONJSON_TABLEJSON_TABLELATERAL FLATTENUNNEST
整數相除捨去小數捨去小數回傳小數回傳小數回傳小數回傳小數(// 為整數除法)
匯入 CSVCOPY / \copyBULK INSERT / bcpLOAD DATA INFILESQL*LoaderCOPY INTOread_csv('f.csv') / COPY
跨平台陷阱說明
CONCAT 遇到 NULL多數平台把 NULL 當空字串;MySQL 的 CONCAT 只要有 NULL 就回傳 NULL
|| 遇到 NULL結果為 NULL;Oracle 例外(當空字串);MySQL 預設 || 是邏輯 OR
SQL Server DATEDIFF數的是跨過的邊界數:DATEDIFF(year, '2023-12-31', '2024-01-01') = 1;算年齡不能直接用
字串比較大小寫SQL Server / MySQL 預設不分大小寫,PostgreSQL / DuckDB 區分;基因名 TP53 vs Tp53(人 vs 小鼠)在前者會被當成相同
識別字大小寫PostgreSQL 未加引號的名稱一律轉小寫,"SampleID" 加引號後必須永遠加引號;Snowflake / Oracle 轉大寫
空字串Oracle 把 '' 當 NULL

NULL 處理 ​

函式平台說明
COALESCE(x, y, ...)全部(標準 SQL)第一個非 NULL,參數個數不限;跨平台優先用這個
NVL(x, y)Oracle、Snowflake兩個參數
ISNULL(x, y)SQL Server兩個參數;回傳型別取第一個參數(ISNULL(varchar(2) 欄, 'Unknown') 會被截成 'Un')
IFNULL(x, y)MySQL、SQLite、DuckDB、Snowflake兩個參數
NULLIF(x, y)全部x = y 時回傳 NULL;常用於避免除以零
sql
SELECT COALESCE(TradeGDPPercent, ImportGoodPercent, 0) AS trade_percent   -- 依序取第一個有值的
FROM EconomicIndicators;

SQL Server (T-SQL) ​

取前 n 筆與分頁 ​

sql
SELECT TOP (10) product_id, year_intro
FROM products
ORDER BY year_intro DESC, product_id;           -- 沒有 ORDER BY 的 TOP 只是預覽,不保證哪幾列

SELECT TOP (5) PERCENT artist FROM artists;     -- 依百分比;非標準語法
SELECT TOP (3) WITH TIES name, score FROM t ORDER BY score DESC;   -- 同分的一併保留

-- 分頁(標準語法,2012+)
SELECT product_id FROM products
ORDER BY product_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

DDL / DML ​

sql
CREATE TABLE test_table (
  test_date DATE,
  test_name VARCHAR(20),
  test_int  INT
);

INSERT INTO table_name (col1, col2) VALUES ('v1', 2);   -- 明確列出欄位,schema 變動時才不會錯位
INSERT INTO table_name (col1, col2)                      -- 查詢結果寫入另一張表
SELECT column1, column2 FROM other_table WHERE flag = 1;

UPDATE table_name SET col1 = 'x', col2 = 3 WHERE some_key = 123;   -- 先用同一個 WHERE 跑 SELECT 確認影響列數
DELETE FROM table_name WHERE some_key = 123;

TRUNCATE TABLE staging_table;                            -- 清空整張表
DELETETRUNCATE TABLE
範圍可加 WHERE整張表
方式逐列刪除並記錄 log釋放資料頁,快很多
觸發 trigger是否
identity / 序號不重設重設
外鍵被參照的表可(符合約束時)不可
交易內回滾可SQL Server、PostgreSQL 可;MySQL、Oracle 會隱含提交,無法回滾

日期與數學函式 ​

sql
SELECT
  DATEPART(year, ModifiedDate)    AS yr,            -- 數字
  DATENAME(weekday, StartDate)    AS day_name,      -- 文字(受語系設定影響)
  DATEADD(day, -30, '2020-06-21') AS minus_30,      -- 2020-05-22
  DATEDIFF(day, '2020-05-22', '2020-06-21') AS diff,  -- 30
  EOMONTH('2024-02-10')           AS month_end,     -- 2024-02-29
  ROUND(DurationSeconds, -2)      AS to_hundreds,   -- 負數精度:四捨五入到百位
  ROUND(DurationSeconds, 0, 1)    AS truncated,     -- 第三參數非 0:截斷而非四捨五入
  CEILING(x), FLOOR(x), ABS(x), SQRT(x), POWER(x, 2)
FROM Incidents;

-- 星期名稱依日曆順序排序(直接 ORDER BY DATENAME 會變字母順序)
SELECT DATENAME(weekday, StartDate) AS day_of_week, SUM(Duration) AS total_duration
FROM CapitalBikeShare
GROUP BY DATENAME(weekday, StartDate), DATEPART(weekday, StartDate)
ORDER BY DATEPART(weekday, StartDate);              -- 週日 = 1(預設 DATEFIRST 7,依語系可能不同)

-- 時間欄位 sanity check:未來時間、結束早於開始
SELECT *
FROM CapitalBikeShare
WHERE StartDate > GETDATE() OR EndDate > GETDATE() OR StartDate > EndDate;

FORMAT(SUM(Duration), '#,0.00') 產生千分位字串,只用在最後的顯示層;結果還要排序或計算時保留數值欄。

變數、迴圈與 CTE ​

sql
DECLARE @Snack VARCHAR(10) = 'Cookies';          -- 宣告同時給值
DECLARE @Price DECIMAL(10, 2);
SET @Price = 2.5;                                 -- 單一賦值
SELECT @Price = price FROM snacks WHERE id = 1;   -- 從查詢取值(符合多列時取最後一列,不報錯)

DECLARE @ctr INT = 1;
WHILE @ctr < 10                                   -- 逐列處理很慢;能用集合運算就不要寫迴圈
BEGIN
  PRINT @ctr;
  SET @ctr += 1;
END;

-- CTE 可在名稱後列出欄名;先聚合再接回明細
WITH MaxBP (Age, MaxBloodPressure) AS (
  SELECT Age, MAX(BloodPressure) FROM Patients GROUP BY Age
)
SELECT p.PatientName, p.Age, p.BloodPressure
FROM Patients AS p
JOIN MaxBP AS m
  ON p.Age = m.Age AND p.BloodPressure = m.MaxBloodPressure;

交易與 TRY...CATCH 見 交易與錯誤處理;執行計畫見 查詢效能。

PostgreSQL ​

sql
SELECT NOW()::DATE;                                         -- :: 是 PostgreSQL 的 CAST 簡寫
SELECT NOW() + INTERVAL '1 month', NOW() - INTERVAL '10 years';
SELECT EXTRACT(YEAR FROM NOW()), DATE_TRUNC('day', created_at), AGE(NOW(), created_at)
FROM customers;
SELECT generate_series(DATE '2024-01-01', DATE '2024-12-01', INTERVAL '1 month');   -- 產生完整月份序列,補缺月用
SELECT name FROM people WHERE name ILIKE 'ade%';            -- 不分大小寫
SELECT x IS DISTINCT FROM y;                                -- NULL 安全的不等於

psql、COPY、DDL 與權限見 PostgreSQL:psql 與 COPY;JSONB 見 NoSQL 基礎。

MySQL:匯入匯出 ​

sql
-- 匯入 CSV(server 與 client 都要開 local_infile;client 用 mysql --local-infile=1 連線)
SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 1;

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;                                    -- 跳過標題列

-- 匯出:路徑受 secure_file_priv 限制,檔案寫在 server 端
SHOW VARIABLES LIKE 'secure_file_priv';
SELECT name, email
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Oracle:format model ​

函式用途範例
TO_CHAR(x, fmt)數字 / 日期 → 字串TO_CHAR(UnitPrice, '$999.99')、TO_CHAR(BirthDate, 'DD-MON-YYYY')
TO_DATE(s, fmt)字串 → 日期TO_DATE('2016-01-31', 'YYYY-MM-DD')
TO_NUMBER(s, fmt)字串 → 數字TO_NUMBER('$15,000.75', '$999,999.99')
NVL(x, y)空值替代NVL(HireDate, DATE '2004-11-19')

常用 token:數字 9(位數)、0(補零)、,、.、$;日期 YYYY、MM、MON、MONTH、DD、DY、DAY、HH24、MI、SS。轉換失敗幾乎都是字串與 format mask 不對齊。沒有資料表的常數查詢要寫 SELECT ... FROM DUAL(23ai 起可省略)。

Snowflake:半結構化資料 ​

Snowflake 用 VARIANT 存 JSON(另有 OBJECT、ARRAY),以 : 取欄位、LATERAL FLATTEN 把陣列展開成列:

sql
SELECT PARSE_JSON('{"name":"alice","tags":["a","b"]}') AS obj;     -- 字串 → VARIANT

SELECT
  e.payload:user_id::STRING   AS user_id,      -- :key 取值,::型別 轉型
  f.value::STRING             AS tag,
  f.index                     AS tag_position
FROM events AS e,
     LATERAL FLATTEN(input => e.payload:tags) AS f;   -- 每個陣列元素一列,原始列跟著複製
效能習慣原因
不用 SELECT *欄式儲存,只讀需要的欄就少掃描資料
篩選條件對齊 clustering key / 日期讓 micro-partition pruning 跳過不相關的區塊
查 SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY找慢查詢、回查 SQL 是否執行過(query_text ILIKE '%...%'、execution_time)

雲端資料倉儲的比較見 雲端資料倉儲。

DuckDB ​

語法接近 PostgreSQL,另加許多便利寫法:SELECT * EXCLUDE (col)、GROUP BY ALL、QUALIFY、PIVOT、直接 FROM 'file.parquet'。用法見 DuckDB 與 Parquet。