[SQL] Query 常用指令

:thumbsup: 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

• PostgreSQL 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 選起來，也可以指定它來作為排序的依據，但在 MYSQL 或 Oracle 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%';

• LIKE 和 NOT LIKE 是有區分大小寫的（case sensitive），若希望不區分大小寫（case insensitive），可以使用 ILIKE 或 NOT 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;

HAVING 和 WHERE 的作用非常相近，主要差別在於 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

其他

縮寫	說明
fk	foreign key
pk	primary key

參考

• The Complete SQL BootCamp @ Udemy
• PostgreSQL Tutorial

JOINS

• A Visual Explanation of SQL Joins

• SQL Joins Explained