[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 ...;