基礎 SELECT 語法教學
所有操作都為 ANSI SQL compliant 的語法。ANSI SQL 相關介紹可以參考 W3School SQL Tutorial。
進階查看完整的支援項目,請點這 完整 SELECT 語法
SELECT
選擇特定欄位
SELECT column1, column2
FROM table_name;
選擇所有欄位
SELECT *
FROM table_name;
WHERE
依照條件選擇資料
SELECT *
FROM table_name
WHERE column1 > value1;
AND, OR
連接多個條件
SELECT *
FROM table_name
WHERE column1 > value1 AND column2 > value1 OR column3 > value1;
Operators
可允許的比較運算符號以及語法
<
小於SELECT *
FROM table_name
WHERE column1 < value1;>
大於SELECT *
FROM table_name
WHERE column1 > value1;<=
小於或是等於SELECT *
FROM table_name
WHERE column1 <= value1;>=
大於或是等於SELECT *
FROM table_name
WHERE column1 >= value1;=
等於SELECT *
FROM table_name
WHERE column1 = value1;<>
不等於SELECT *
FROM table_name
WHERE column1 <> value1;!=
不等於SELECT *
FROM table_name
WHERE column1 != value1;BETWEEN a AND b
介於 a 與 bSELECT *
FROM table_name
WHERE column1 BETWEEN value1 AND value2;IN (a, b, c, ...)
為 a, b, c 其中之一SELECT *
FROM table_name
WHERE column IN (12, 15, 18)IS NULL
判斷是否為Null
SELECT *
FROM table_name
WHERE column1 IS NULL;
Operators for Specified Types
以下是一些常見用法提供你針對不同型別的欄位做搜尋。
UUID
使用
uuid
前綴轉型SELECT * FROM table
WHERE id = uuid 'affa0ba3-2744-47c6-9f2a-35b8e2d56581'Timestamp
使用
timestamp
前綴轉型SELECT * FROM table
WHERE create_at > timestamp '2019-08-01 00:00'使用
date
前綴轉型SELECT * FROM table
WHERE create_at > date '2019-08-01'搭配
interval
取得其他時間SELECT * FROM table
WHERE create_at > date '2019-08-01'
AND create_at < date '2019-08-01' + interval '1' monthVARCHAR
使用
LIKE
搭配%
去匹配無或是任意字元SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
WHERE column1 LIKE '%b%'
--returns 'abc' AND 'bcd'使用
LIKE
搭配_
,匹配單一字元SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
WHERE column1 like '_b%'
--returns 'abc'
ORDER BY
由小至大排序
SELECT *
FROM table_name
ORDER BY column1 ASC
由大至小排序
SELECT *
FROM table_name
ORDER BY column1 DESC
NULL
排序在最前
SELECT *
FROM table_name
ORDER BY column1 ASC NULLS FIRST
NULL
排序在最後
SELECT *
FROM table_name
ORDER BY column1 ASC NULLS LAST
GROUP BY
依照特定欄位分組
SELECT *
FROM table_name
GROUP BY column1
依照特定欄位分組
SELECT count(*)
FROM table_name
GROUP BY column1
HAVING
篩選分組過後的資料。
SELECT count(column1), column2
FROM table_name
GROUP BY column2
HAVING count(column1) > value1
JOIN
依照特定欄位組合兩張表
SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column1
UNION
合併兩張表
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
LIMIT, OFFSET
LIMIT number
最多取得幾筆資料OFFSET number
忽略前幾筆資料
取得第 5 筆之後的 10 筆資料
SELECT *
FROM table_name
OFFSET 5
LIMIT 10