SQL

w3school
SQL 樣式指南
cheatSheet
Quick SQL Cheatsheet

syntax

select

選擇欄位

SELECT column1, column2, ...
FROM table_name;

選擇唯一

SELECT DISTINCT column1, column2, ...
FROM table_name;

where

條件判斷

SELECT column1, column2, ...
FROM table_name
WHERE condition;

AND, OR, NOT

條件判斷

AND

當條件都為 True 就是 true

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR

當其中一個條件是 true 就是 true

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT

原本條件相反

SELECT * FROM Customers
WHERE NOT Country='Germany';

ORDER BY

根據某個 column 去排序

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

INSERT INTO

新增紀錄

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

NULL

//是 null
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

//不是 NULL
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

UPDATE

更新紀錄

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE

刪除紀錄

DELETE FROM table_name
WHERE condition;

LIMIT

選擇幾筆

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

aggregate functions

// MIN() 最小
SELECT MIN(column_name)
FROM table_name
WHERE condition;

// MAX() 最大
SELECT MAX(column_name)
FROM table_name
WHERE condition;

// Count() 幾筆
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

// AVG() 平均
SELECT AVG(column_name)
FROM table_name
WHERE condition;

// SUM() 總和
SELECT SUM(column_name)
FROM table_name
WHERE condition;

LIKE

類似

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

IN

是否在這些值裡面

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

BETWEEN

在這個區間的值

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Aliases

縮寫

// coulumn
SELECT column_name AS alias_name
FROM table_name;

//table
SELECT column_name(s)
FROM table_name AS alias_name;

JOIN

集合

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

GROUP BY

根據某個 coulmn 去分群

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

HAVING

因為 where 不能使用 aggregate functions

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.
Each SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in each SELECT statement must also be in the same order

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

function

uninx 時間 => UNIX_TIMESTAMP() //  沒有設定就是當下時間

其它

1. 風格

問題

用 alias name 當作 where 判斷

//不可能 取代方案是
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1
FROM MyTable
WHERE SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) = 'MySearch'

取得 最常出現的欄位值 https://stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column