[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
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
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
透過 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 |