###### tags: `database`
# Interface and SQL
## 資料庫語言
對資料庫請求的用語有二
1. DDL
- Data Definition Language
- 作用於 DB 的 Schema
- 即改變資料庫的資料結構
```sql
CREATE TABLE ...
ALTER TABLE ...
DROP TABLE ...
```
2. DML
- Data Manipulate Language
- 作用於一筆筆資料 (record)
- 即對一筆筆資料的增刪改查 (Query Algebra)
```sql
INSERT INTO ... VALUES ... # 增
DELETE FROM ... WHERE ... # 刪
UPDATE ... SET ... WHERE ... # 改
SELECT ... FROM ... WHERE ... # 查
```
## Query Algebra and Query Plan
DML 的一次請求可以任意切分為一組對資料庫的操作，這些操作稱為 Query Algebra。
它們化為不唯一的「Query Plan」(Query Tree)，完成此樹的行為方能產出請求的結果。
DBMS 的一項任務就是從眾多 Query Plan 中選擇自動最快速的來執行。
## 交易操作 Transaction
> All or nothing!
例子：
```sql
BEGIN;
SELECT ...; -- query
COMMIT;
```
可以主動的為一系列操作賦予 ACID 的特性：
- Atomic
- Consistency
- Isolation
- Durability
## SQL 語法細節
> 這教學要錢哎，以下是在 Free-Trial 期間寫的，且紀錄且珍惜：）
教學網站網址…
>
> [Learn SQL | Codecademy](https://www.codecademy.com/learn/learn-sql)
## 基本操作
### Table 增改查與基本型別
```sql
CREATE TABLE table_name (
col_1 data_type, -- col mean column
col_2 data_type,
...
col_n data_type
);
```
- 常見的資料型態…
- `char` 也是常用型別
- 前綴可能有 `var` 表「可變長度」； `n` 表支援 Unicode
- 字串串接
```sql
'User ' || 1 -> 'User 1'
```
### 限制欄位型別
型別可以透過更多關鍵字去限制其值，例如以下：
```sql
CREATE TABLE celebs (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE,
date_of_birth TEXT NOT NULL,
date_of_death TEXT DEFAULT 'Not Applicable',
"autherID" INTEGER NOT NULL REFERENCES users ON DELETE CASCADE -- deletion cascade
ts BIGINT NOT NULL DEFAULT (extract(epoch from now())) -- take timestamp on creation
);
```
以下簡介之：
- `PRIMARY KEY` 這應該很好懂…
- `SERIAL` 表我們不用自行指定值，每次新加資料時會產生新的序列號
- `UNIQUE` 也就是單一欄位的 Candidate Key
- `NOT NULL` 所有值不可為 `NULL`；若加入一筆新資料，其中此欄位為空，則加入失敗
- `DEFAULT SOME_DEFAULT_VAL` 未指定某欄位值時，預設給 `SOME_DEFAULT_VAL`
- `REFERENCES table_name ON __op__ CASCADE` ：參考 `table_name` 這張表，若 `table_name` 的資料被 `__op__` ，則會連帶影響所有參考。
- 上面例子就是，若刪除 `users` 的某筆資料，則該筆資料之 `authorID` 對應的 `celebs` 所有參考都會移除。
- 此時 `celebs.authorID` 需為 foreign key
### 新增 row
```sql
INSERT INTO table_name (col_1, col_2, ...)
VALUES (..., ..., ...);
-- e.g.
INSERT INTO celebs (id, name, age)
VALUES (3, 'Jeremy Lin', 26);
```
### 新增 column
注意每一筆資料此時在新欄位中的值都是 `NULL`
```sql
ALTER TABLE table_name
ADD COLUMN col_name data_type;
-- e.g.
ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;
-- All records are NULL in this column now
```
### 刪除 column
```sql
DELETE FROM table_name
WHERE specified_val IS specified_val;
-- e.g.
DELETE FROM celebs
WHERE twitter_handle IS NULL;
```
### 修改 column
```sql
UPDATE table_name
SET col_x = some_val -- val means value
WHERE specified_col = specified_val;
-- e.g.
UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id = 4;
```
## 查詢方法
### 查詢某些欄位
```sql
SELECT col FROM table_name; -- fetch column: col in table_name
SELECT col_1, col_2, ... FROM table_name;
-- fetch columns: col_1, col_2, ... in table_name
SELECT * FROM table_name; -- fetch all column in table_name
SELECT col AS 'COL_ALIAS'
FROM table_name; -- fetch column: col and rename it as 'COL_ALAIS'
```
### 查詢「相異」欄位
```sql
SELECT DISTINCT col FROM table_name;
-- fetch distinct values in a column: col in table_name
```
### 以運算子依條件過濾
```sql
SELECT col
FROM table_name
WHERE col_x COMPARATOR val_x;
-- fetch values in a column: col in table_name
-- with a filtering condition: col_x COMPARATOR val_x
-- e.g.
SELECT *
FROM movies
WHERE imdb_rating < 5;
```
- 注意上面 `COMPARATOR` 只能是縮圖的比較子。
### 以 LIKE 搭配某 Pattern (Regex) 為條件過濾
注意 `LIKE` 不在意比對的大小寫
```sql
SELECT col
FROM table_name
WHERE col_x_txt LIKE some_pattern;
-- fetch values in column: col in table_name
-- filtering with pattern: some_pattern
-- e.g. 1: _ wildcard
SELECT *
FROM movies
WHERE name LIKE 'Se_en';
-- '_' means a wildcard character matchs a single character
-- e.g. 2: % wildcard
SELECT *
FROM movies
WHERE name LIKE 'the %';
-- '%' means a wildcard character matchs zero or multiple character
```
### 以 IS 檢查是否為 NULL
```sql
SELECT col
FROM table_name
WHERE col_x IS [ NOT ] NULL; -- NOT is optional
-- e.g. 1: is null
SELECT name
FROM movies
WHERE imdb_rating IS NULL;
-- e.g. 2: is not null
SELECT name
FROM movies
WHERE imdb_rating IS NOT NULL;
```
### 以 BETWEEN 取得閉區間範圍內的資料
注意以下範例中，被選擇的 `name` 為「字串比較」於 `'D'` 至 `'G'` 範圍內的所有字串。
```sql
SELECT col FROM table_name
WHERE col_x BETWEEN CLOSED_LEFT_RANGE AND CLOSED_RIGHT_RANGE;
-- e.g.
SELECT * FROM movies
WHERE name BETWEEN 'D' AND 'G';
-- select name with encoding-comparison
-- inside closed range 'D' to 'G', the logic
-- of comparison is just like "strcmp"
-- for example, 'D', 'Eroiko' and 'G' matches
-- but 'Kate Mirror', 'G ', 'GAGAGA bunko' does not!
```
### 使用 AND 完成邏輯 "AND" 的過濾
> 也就是增加過濾條件
```sql
SELECT col
FROM table_name
WHERE FILTER_1
AND FILTER_2
AND ...;
-- e.g. 1
SELECT *
FROM movies
WHERE year BETWEEN 1970 AND 1979
AND imdb_rating > 8;
-- notice that BETWEEN-AND together forms a single filter!!!
-- e.g. 2
SELECT *
FROM movies
WHERE year < 1985
AND genre = 'horror';
```
### 使用 OR 完成邏輯 "OR" 的過濾
也就是增加其他可以符合的條件
```sql
SELECT col
FROM table_name
WHERE FILTER_1
OR FILTER_2
OR ...;
-- e.g. 1
SELECT *
FROM movies
WHERE genre = 'comedy'
OR genre = 'romance';
```
### 對查詢的結果排序
```sql
SELECT col_1, col_2, ...
FROM table_name
ORDER BY col_ord [ ASC | DESC ];
-- DESC: sort in descending order,
-- default (ASC) is ascending
-- e.g.
SELECT name, year, imdb_rating
FROM movies
ORDER BY imdb_rating DESC;
```
### 以 LIMIT 限制回傳資料量
```sql
SELECT col
FROM table_name
LIMIT lim_num;
-- lim_num is an integer
-- that limits the numbers of selection
-- e.g.
SELECT *
FROM movies
LIMIT 10;
```
### if-else 邏輯
- 範例的需求是…
```sql
-- prototype 1
SELECT col_1, col_2, ..., -- must have a comma before CASE !!!!!!
CASE
WHEN CONDITION_1 THEN DO_SOME_THING_1
WHEN CONDITION_2 THEN DO_SOME_THING_2
...
WHEN CONDITION_N_1 THEN DO_SOME_THING_N_1
[ ELSE DO_SOME_THING_N ] -- this is optional !!!
END [ AS 'RENAMED_COLUMN' ]
FROM table_name;
-- prototype 2
SELECT CASE
WHEN CONDITION_1 THEN DO_SOME_THING_1
WHEN CONDITION_2 THEN DO_SOME_THING_2
...
WHEN CONDITION_N_1 THEN DO_SOME_THING_N_1
[ ELSE DO_SOME_THING_N ] -- this is optional !!!
END [ AS 'RENAMED_COLUMN' ]
FROM table_name;
-- e.g.
SELECT name,
CASE
WHEN genre = 'romance' THEN 'Chill'
WHEN genre = 'comedy' THEN 'Chill'
ELSE 'Intense'
END AS 'Mood'
FROM movies;
```
注意，依照 case 選出來的東西相當於一個新的 column，可用 `AS 'new_name'` 為其重新命名。
## Aggregation 聚合操作：函數式程式設計
### COUNT 算個數
```sql
SELECT COUNT(*)
FROM fake_apps
WHERE price = 0;
```
### SUM 加總
```sql
SELECT SUM(downloads)
FROM fake_apps;
```
### MAX / MIN 取極值
```sql
SELECT MAX(price)
FROM fake_apps;
SELECT MIN(downloads)
FROM fake_apps;
```
### AVG 取平均
```sql
SELECT AVG(price)
FROM fake_apps;
SELECT ROUND(AVG(price), 2)
-- AS 'rounded'
FROM fake_apps;
```
### ROUND 四捨五入至某位
```sql
SELECT ROUND(AVG(price), 2)
-- AS 'rounded'
FROM fake_apps;
```
吃兩參數：col 和四捨五入到的位數
### GROUP BY 分組
```sql
SELECT price, COUNT(*)
FROM fake_apps
WHERE downloads > 20000
GROUP BY price;
```
```sql
SELECT category, SUM(downloads)
FROM fake_apps
GROUP BY category;
```
## 分組代名詞
```sql
SELECT category,
price,
AVG(downloads)
FROM fake_apps
GROUP BY category, price;
-- equals to
SELECT category,
price,
AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;
-- since category is the first selected
-- and price is the second selected
```
### HAVING: 針對 GROUP BY 的 filter
```sql
SELECT price,
ROUND(AVG(downloads)),
COUNT(*)
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 10;
```
### strftime 函式
類似 C 語言之 `sprintf`。
[Date And Time Functions](https://www.sqlite.org/lang_datefunc.html)
### JOIN + ON 依循 column 合併 Table
用來對兩 Table 內積。
```sql
SELECT ... FROM table_1 [ LEFT | RIGHT ] JOIN table_2 ON join_condition;
-- checkpoint 1
SELECT *
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id;
-- checkpoint 2
SELECT *
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';
```
`JOIN` 分為「左、右」
```sql
-- an example of left join
-- since we're using left join,
-- even newspaper.id doesn't exist in
-- online.id, they're still be reserveed
SELECT *
FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id
WHERE online.id IS NULL;
```
### CROSS JOIN 對 Table 外積
```sql
SELECT col FROM table_1
CROSS JOIN table_2;
-- e.g.
SELECT month,
COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= month AND end_month >= month
GROUP BY 1;
```
Table 的外積， $m$ 筆資料和 $n$ 筆資料的 Table 進行 `CROSS` 後變 $m \times n$ 筆
### UNION 對特定欄位取聯集
對兩 Table 的特定 column 取聯集，在 SQL 中有嚴格的限制。
1. column 數量相同
2. column 依序型別相同
```sql
SELECT col_11, col_12, ... FROM table_1
UNION SELECT col_21, col_22, ... FROM table_2;
```
一種使用場景是對兩個表格做「疊合」。
### WITH + AS 生成暫時表格
建立臨時表格。
```sql
WITH tmp_table_name AS (
-- build a table using SELECT...
-- ...
)
-- here we have a new table: tmp_table_name
-- we can do things using it!
-- e.g.
SELECT * FROM tmp_table_name;
```
Sign in with Wallet
Connect another wallet