/* choose all columns */
SELECT * FROM table
/* choose specific columns */
SELECT col1, col2 FROM table1
/* choose unique values in the specifc columns */
SELECT DISTINCT col1, col2 FROM table1
/* Use conditions to choose what I want */
SELECT col1, col2 FROM table1 WHERE conditions;
/* Example */
SELECT first_name, last_name FROM customer WHERE first_name='Jamie' AND last_name='Waugh';
conditions 支援基本運算子及邏輯判斷
/* 顯示 table 有幾個 row */
SELECT COUNT(*) FROM tabel1
/* 顯示 col1 有幾個 row */
SELECT COUNT(col1) FROM table1
/* 顯示 unique 值有多少 */
SELECT COUNT(DISTINCT(col1)) FROM table1
/* 只顯示 5 個 row 就好 */
SELECT * FROM table1 LIMIT 5;
/* 根據 first_name 來做遞升的排序 */
SELECT first_name, last_name FROM customer
ORDER BY first_name ASC;
/* 根據 first_name 來做遞減的排序 */
SELECT first_name, last_name FROM customer
ORDER BY first_name DESC;
/* 可以多重 columns 排序, */
/* 先對 fisrt_name 遞減排序, 再對 last_name 遞升排序 */
SELECT first_name, last_name FROM customer
ORDER BY first_name DESC,last_name ASC;
/* PostgreSQL 特有 */
/* 可以依據沒被 SELECT 的 columns 來進行排序 */
/* 其他 SQL engine 如 MySQL 就必須一定要 SELCET*/
SELECT first_name FROM customer
ORDER BY last_name ASC;
/* 挑選值介於 8 和 9 之間 */
SELECT customer_id,amount FROM payment
WHERE amount BETWEEN 8 AND 9;
/* 挑選值不在 8 和 9 之間 */
SELECT customer_id,amount FROM payment
WHERE amount NOT BETWEEN 8 AND 9;
/* 時間也可以做挑選, 日期介於 17 號和 18 號之間 */
SELECT customer_id,payment_date from payment
WHERE payment_date BETWEEN '2007-02-17' AND '2007-02-18';
/* 只選取 customer id 為 1,3,7 的 row */
SELECT customer_id,rental_id,return_date FROM rental
WHERE customer_id in (1,3,7)
ORDER BY return_date DESC;
/* 選取 customer id 為 1,3,7 之外的 row */
SELECT customer_id,rental_id,return_date FROM rental
WHERE customer_id NOT in (1,3,7)
ORDER BY return_date DESC;
/* 尋找 first_name 中以 Jan 開頭的 row */
SELECT first_name,last_name FROM customer
WHERE first_name LIKE 'Jan%';
/* 尋找中間有 er 的名字 */
SELECT first_name,last_name FROM customer
WHERE first_name LIKE '%er%';
/* 尋找不要有 y 結尾的名字 */
SELECT first_name,last_name FROM customer
WHERE first_name NOT LIKE '%y';
/* 尋找 first_name 中以任意 單字 開頭, */
/* 接著為 an, 且以任意 字串 結尾的 row */
SELECT first_name,last_name FROM customer
WHERE first_name LIKE '_an%';
/* 尋找有 er 的名字, 不管大小寫, */
SELECT first_name,last_name FROM customer
WHERE first_name ILIKE '%er%';