Skip to main content

[SQL] Query 常用指令

👍 PostgreSQL Tutorial

FAQ#

單引號和雙引號有什麼不同?#

What is the difference between single quotes and double quotes in PostgreSQL? @ StackOverflow

在 Postgres 中,單引號用來表示「字串」,而雙引號是針對欄位名稱或資料表名稱使用(一般來說可以省略):

select * from "employee" where "employee_name"='elina';

SELECT#

SELECT column_1, column_2, ... FROM table_name;
SELECT * FROM table_name;
  • SQL 語法本身是不區分打小寫的(case-insensitive),但慣例上來說,會把 SQL 內的關鍵字用大寫呈現,以方便閱讀。
  • 使用 * 來選出所有欄位的資料一般來說不是太好的作法,這會拖慢資料庫以及應用程式的效能。因為你可能會拿出許多沒有必要的欄位資料,同時導致伺服器的負荷增加,拖慢應用程式的效能。因此,最好只在特殊的情況使用(例如,直接操作資料庫時)。

範例#

SELECT first_name FROM customer;

Querying Data#

ORDER BY#

PostgreSQL ORDER BY

keywords: ASC, DESC, NULLS FIRST, NULLS LAST#
SELECT column_1, column_2, ...
FROM table_name
ORDER BY column_1 ASC[, column_2 DESC];
  • ORDER BY 預設是使用 ASC 排序。
  • 如果根據兩個以上的欄位排序,則會以前者為優先,例如先依照 column_1 排序,當 column_1 的值一樣的話,在根據 column_2 排序。
  • PSQL 中,即使要排序的欄位沒有透過 SELECT 選起來,也可以指定它來作為排序的依據,但在 MYSQLOracle SQL 中可能不行這麼做。

範例#

-- 排序多個欄位
SELECT first_name, last_name FROM customer ORDER BY first_name ASC, last_name DESC;
-- 將 expression 作為排序依據
SELECT first_name, LENGTH(first_name) len
FROM customer
ORDER BY len DESC;
-- 使用 NULLS FIRST 或 NULLS LAST 來搭配 NULL 進行排序
SELECT num FROM sort_demo ORDER BY num DESC NULLS FIRST;

AS#

keywords: column alias#

PostgreSQL Column Alias

透過 AS 可以將欄位改一個匿名(alias):

SELECT amount as amount_alias FROM payment;
-- 在 SELECT 中使用 AS
SELECT customer_id, SUM(amount) AS total_amount FROM payment
GROUP BY customer_id;
-- 搭配 concatenation operator
SELECT first_name || ' ' || last_name AS full_name FROM customer;
-- 搭配 JOIN 使用
SELECT title, name
FROM film
INNER JOIN language AS lan ON film.language_id = lan.language_id;

在 SQL 中甚至可以把 AS 這個關鍵字省略,用 空格 即可

-- 不寫出 AS 而是用「空格」
SELECT customer_id, SUM(amount) total_amount FROM payment
GROUP BY customer_id;
--
SELECT title, name
FROM film
INNER JOIN language lan ON film.language_id = lan.language_id;

DISTINCT#

-- 列出 column 中不重複的值
SELECT DISTINCT column FROM table_name;
-- 列出 column_1 且 column_2 不重複的值(會結合 column_1 和 column_2)
SELECT DISTINCT column_1, column_2, ... FROM table_name;
--
SELECT DISTINCT ON (column1) column_alias FROM table_name ORDER BY column1, column2;
  • 在一個 table 中常常會有許多重複的值,透過 DISTINCT 可以只列出不重複的值。
  • DISTINCT ON 的使用最好都能搭配 ORDER BY
/* DISTINCT 也可以搭配 COUNT 使用 */
SELECT COUNT(DISTINCT column_1) FROM table_name ;

Filtering Data#

WHERE#

SELECT column_1, column_2, ...
FROM table_name
WHERE conditions;

Operators#

  • >, <, =, >=, <=, <>(not equal), !=(not equal), AND, OR
/* example: =, AND*/
SELECT last_name, first_name
FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';
/* example: <=, >=, OR */
SELECT customer_id, amount, payment_date
FROM payment
WHERE amount <= 1 OR amount >= 8;

時間也可以:

SELECT * FROM vital_signs WHERE measured_at > '2020-08-17T02:42:45.702Z';

BETWEEN, NOT BETWEEN#

/* 等同於 WHERE value >= low AND value <= high */
SELECT * FROM table_name
WHERE column_1 BETWEEN low_value AND hight_value;
/* 等同於 WHERE value < low OR value > high */
SELECT * FROM table_name
WHERE column_1 NOT BETWEEN low_value AND high_value;
  • BETWEEN 等同於 WHERE value >= low AND value <= high
  • NOT BETWEEN 等同於 WHERE value < low OR value > high

也可以用來篩選時間

SELECT * FROM payment
WHERE payment_date
BETWEEN '2007-02-07' AND '2007-02-15';

IN, NOT IN:作為多個 OR 使用#

使用 IN 可以取代使用多個 OR 條件,效能又較好:

SELECT * FROM table_name
WHERE column_1 IN (value_1, value_2, ...);
/* 上面的寫法等同於 */
SELECT * FROM table_name
WHERE column_1 = value_1
OR column_1 = value_2
OR ...;

IN 裡面除了可以放一般的字串和數值外,也可以是另一個 SELECT 後的結果:

SELECT * FROM table_name
WHERE column_1 IN (SELECT value FROM table_name);
/* 使用例子 Example */
SELECT * FROM rental
WHERE customer_id IN (1, 2);

LIKE, NOT LIKE, ILIKE, NOT ILIKE:模糊比對(Pattern Matching)#

SELECT column_1, column_2
FROM table_name
WHERE column_1 LIKE '%foobar%';
  • LIKENOT LIKE 是有區分大小寫的(case sensitive),若希望不區分大小寫(case insensitive),可以使用 ILIKENOT ILIKE
  • 使用 % 可以配對任何次數的文字符號
    • 'Jen%':任何以 Jen 開頭的值(用 LIKE 需區分大小寫,ILIKE 則不用)
    • '%er%':任何值當中包含 er
  • 使用 _ 則只能配對單一次的文字符號
    • '_sther':任意文字加上 sther

記得要加上單引號 ''

/* example */
/* 任何以 Jen 開頭的 first_name */
SELECT first_name, last_name
FROM customer
WHERE first_name LIKE 'Jen%';

IS NULL#

IS 後面可以接 null

SELECT * FROM order
WHERE deleted_at IS NULL;

LIMIT#

SELECT column_1, column_2, ...
FROM table_name
LIMIT row_num_you_want;
/* example */
SELECT * FROM customer LIMIT 5;

GROUP BY#

SELECT column_1, aggregate_function(column_2)
FROM table
GROUP BY column_1;
  • 透過 GROUP BY 可以將許多列(row)的資料進行分組。
  • 每一個分組過的資料,都可以在經過 aggregate function 計算所需要的數值。

範例:取出唯一值#

SELECT payment_id FROM payment GROUP BY payment_id;
-- 等同於
SELECT DISTINCT payment_id FROM payment;
  • 針對同一個所選的欄位進行 GROUP BY 等同於使用 DISTINCT 的效果。
  • 有些 SQL 語言會要求你一定要放入 aggregate function 而不能只放入 GROUP BY 中所代入的欄位。

範例:搭配 aggregate function 計算數值#

SUM:計算分組內某欄位的加總#
-- 先根據 customer_id 分組,接著將組內的 amount 分數加總
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC;
  • 在其他 SQL 語言中,可能會需要把 GROUP BY 的欄位也一定要代到 SELECT 中才行(即,這裡的 customer_id),但在 psql 中較具彈性,因此可以不用這麼做。
  • 透過 GROUP BY 計算 SUM(amount) 的這個欄位,一樣可以放到 ORDER BY 中使用。
COUNT:計算分組內的資料數#

此外也可以搭配 COUNT 使用:

-- 先根據 staff_id 分組,接著計算每組內的資料筆數(rows)
SELECT staff_id, COUNT(*)
FROM payment
GROUP BY staff_id;
-- 先根據 rating 分組,接著計算每組內的資料筆數(rows)
SELECT rating, COUNT(*)
FROM film
GROUP BY rating;
AVG:計算分組內某欄位的平均值#
-- 先根據 rating 分組,接著計算每一組內 `rental_rate` 的平均
SELECT rating, AVG(rental_rate)
FROM film
GROUP BY rating;

HAVING#

HAVING 搭配 GROUP BY 使用,針對已經透過 GROUP BY 分組後的欄位進行某些條件過濾:

SELECT column_1, aggregate_function(column_2)
FROM table_name
GROUP BY column_1
HAVING condition;

HAVINGWHERE 的作用非常相近,主要差別在於 HAVING 作用在 GROUP BY 之後的欄位資料,而 WHERE 是作用在 GROUP BY 之前的欄位資料。

範例#

-- 先根據 customer_id 分組後,再透過分組後的 SUM(amount) 進行過濾
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 200;
-- 先根據 store_id 分組後,再透過分組後的 COUNT(customer_id) 進行過濾
SELECT store_id, COUNT(customer_id)
FROM customer
GROUP BY store_id
HAVING COUNT(customer_id) > 300;

Subquery#

假設我們希望看所有的影片中,哪些影片的價格高於平均,我們可以寫兩次不同的 query:

-- 1. 使用 query 來找出所有電影的平均價格
SELECT AVG(amount) FROM film; -- 4.8
-- 2. 使用 query 找出所有價格大於平均價格的電影
SELECT * FROM film WHERE amount > 4.8;

但若我們使用 subquery 則可以一次完成,方法是在 WHERE 中透過 () 包住另一個 query:

SELECT * FROM film
WHERE amount > (SELECT AVG(amount) FROM film);

Examples#

-- 搭配 WHERE ... IN 使用
SELECT film_id, title
FROM film
WHERE film_id IN (
SELECT film_id FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30'
);

Timestamps and EXTRACT#

透過 extract 可以截取 date 當中的部分:

SELECT
SUM(amount) AS total_amount,
extract(month from payment_date) AS month
FROM payment
GROUP BY month
ORDER BY total_amount DESC;

除了 extract,還有許多時間相關的方法可以使用,參考官方文件 9.9. Date/Time Functions and Operators @ PostgreSQL Documentation

Aggregate Function#

keywords: COUNT, MIN, MAX, AVG, SUM, ROUND#

各個 aggregate function 的使用方式大同小異。

COUNT#

SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(DISTINCT column_name) FROM table_name;
  • 使用 COUNT(column_name) 時,若該欄位有值為 NULL 時,不會被 COUNT 語法算入。

AVG, SUM#

SELECT AVG(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;

MIN, MAX#

SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;

ROUND#

SELECT ROUND(AVG(column_name), decimal) FROM table_name;
/* 取的 AVG(amount) 後四捨五入到小數第二位 */
SELECT ROUND(AVG(amount), 2) FROM payment;

Mathematical Functions#

-- 一般加減乘除
SELECT *, customer_id + rental_id AS new_id
FROM payment;
-- function
SELECT round(AVG(amount), 2 ) FROM payment;
--
SELECT 5 * 3;

Mathematical Functions and Operators @ PostgreSQL Documentation

String Functions#

  • concatenation operator (||)
-- 字串連接使用 ||
SELECT *, first_name || ' ' || last_name AS full_name
FROM customer;
-- function
SELECT char_length(first_name), first_name
FROM customer;
SELECT upper(first_name), first_name
FROM customer;
SELECT lower(first_name), first_name
FROM customer;

String Functions and Operators @ PostgreSQL Documentation

UPDATE#

UPDATE [table_name] SET [column1]=value, [column2]=value2,... WHERE [some_column]=some_value
UPDATE students SET cHeight=174, cWeight=92 WHERE cID = 11
DELETE
DELETE from [table_name] WHERE [some_column]=some_value

特殊字符#

註解(Comment)#

在 SQL 中加入註解的方式:

/* */ are inline comments
-- , # are line comments
SELECT * FROM users WHERE name = 'admin' --and pass = 'pass'

連接多行 query#

透過 ; 可以連接多行 query:

-- ; allows query chaining
SELECT * FROM users; DROP TABLE users;

連接字串(string concatenation)#

-- ',+,|| allows string concatenation
-- Char() strings without quotes
SELECT * FROM users WHERE name = '+char(27) or 1=1

其他#

縮寫說明
fkforeign key
pkprimary key

參考#

JOINS#

Last updated on