# 0409 Database、SQL

###### tags: `Ruby / Rails` `SQL`

<!-- ## ＊ DB Browser 使用-->

- 資料庫與 rails 的關係
- 在 model 上的驗證屬於軟體層的 ( 可視為前端驗證 )
- 資料庫上的驗證屬於硬體層的 ( 可視為後段驗證 )
- 資料庫為真正寫入的地方，以管理資料庫的職位的人的意見為主，因應他的要求去 migration 處理。
- 資料庫的資料不會跟 migration 作連動，所以要 CRUD 都在 migration 裡執行
- migration 處理的是結構，而非資料

## 資料庫簡介
[0329 資料庫簡介](https://hackmd.io/PqieyNqYQtSc4d8x-6H0vQ?view#%E8%B3%87%E6%96%99%E5%BA%AB-database)
[WIKI 中文簡介](https://zh.wikipedia.org/wiki/SQL)
* 一個資料庫有很多資料表
* 
* 是個框架
- 建立資料庫要指定的數據：
- 欄位名稱
- 型態：[常用資料型態](http://www.eion.com.tw/Blogger/?Pid=1155)
- 選項：e.g. `auto increment`、`NOT NULL`、`primary key`、`unique`
- 說明

### 資料型態 (Data Type)
* VARCHAR(n) = ==VAR==iable ==CHAR==acter
* 可變動的字元

#### VARCHAR vs CHAR
* 兩者都是 1字元 1 位元組 (Byte)
* [範例見後方題外話解釋](https://hackmd.io/19RGr-SwTOaMUmlsWYNx0g?both#CHAR-VS-VARCHAR)
* 兩種型態在超過設定大小時會出錯
* VARCHAR(n)
* 可變動
* 在指定的 n 個 Byte 內，依使用者輸入的內容調整儲存的大小
* 存入內容資訊後，再額外用 1 Byte 存放長度資訊
* CHAR(n)
* 固定長度，不足會補空白
* 固定儲存為 n 個 Byte，輸入少於 n 的話一樣會被補到儲存大小為 n
* 例：

| | CHAR(10) | VARCHAR(10) |
| -------- | -------- | -------- |
| 存 abc |存 3 個字元＋剩下 7 個字元填空白 | 只存 3 個字元＋1 Byte 長度 |
|存 abcdefg|存 7 個字元＋3 空白|存 7 個字元＋1 Byte|
|較適用時機|確定長度|不確定長度|

---
### SQLite 欄位
* NN = Not Null 不能空白
* PK = Primary Key ( 主鍵 ) 識別資料用
* AI = Auto Increment 自動遞增
* U = Unique 不能允許重複值

### Primary Key 主鍵

* 是一種 index，PK 會自動建立 index
* 不能為空值(NULL)
* 每個 table 只能有一個 Primary Key
* 但是 primary key 可以包含多個欄位
* 每組數據有一個獨特的 primary key
* 語法為 CREATETABLE tablename ( […], PRIMARY KEY (列的列表) );
* 在 Rails 裡面，默認 id 為 primary key

### Foreign Key 外部鍵
* 與其他資料表有連結的內容欄位
* 用來確認該資料表的紀錄跟被對照到的表格資料式可對應的
* 不須為獨特的存在，可以重複，也可以是 NULL
- 為了資料完整性（data integrity）而建立

### 資料表關聯
(ER model)

## SQL：結構查詢語言 Structured Query Language
* SQL 不是程式語言，只是專門拿來管理資料庫用的特定語言
* 關連式資料庫 RDBMS (Relational Database Management System)
* 目前常見的RDBMS有：Oracle, MSSQL, MySQL, PostgreSQL
* SQLite / MSSQL為微軟專用 / MySQL / PostgreSQL
* pg & SQLite 出自開源圈
* 建議在書寫時指令部分用全大寫
* 在資料庫系統中使用 `SELECT`

---

### 寫入資料：

INSERT INTO heroes (name, hero_level)
VALUES ('kk','S');

>輸入的內容含有單引號的話，可以加反斜線這個跳脫字元表示他是單純的字串
>```'kk\'kkkk' => 實際得到 kk'kkkk 的字串```
* Rails 指令（通常在 migration 裡執行）：

Hero.create(name: 'kk', hero_level: 'S')

* 寫入資料時是否按照順序？（一個蘿蔔一個坑）
* 在指令中有寫欄位名稱 => 只要欄位名稱跟值有對到就好，不一定要按原始順序
* 在指令中沒指定欄位名稱 => 一定要按照原始順序，不然會出錯

---

### 查詢資料：
1. 查詢所有資料

SELECT *
FROM heroes;
--查詢 heroes 資料表中所有資料（同時顯示所有欄位）

- Rails 指令（通常在 controller 裡面執行）：

Hero.all

2. 篩選特定欄位查詢資料

SELECT *
FROM heroes
WHERE hero_level = 'S';
-- 查詢 heroes 資料表中所有 hero_level 是 S 的資料（同時顯示所有欄位）

- Rails 指令（通常在 controller 裡面執行）：

Hero.where(hero_level: 'S')

3. 篩選特定欄位查詢資料

SELECT *
FROM heroes
WHERE hero_level = 'S' AND gender = 'F';
-- 查詢 heroes 資料表中所有 hero_level 是 S 且 gender 為 F 的資料（同時顯示所有欄位）

- Rails 指令（通常在 controller 裡面執行）：

Hero.where(hero_level: 'S', gender: 'F')


4. 篩選特定欄位查詢資料

SELECT hero_level, gender
FROM heroes
WHERE hero_level = 'S';
-- 查詢 heroes 資料表中 hero_level 是 S 的資料
-- 但只顯示 hero_level 跟 gender 欄位


- Rails 指令（通常在 controller 裡面執行）：

Hero.where(hero_level: 'S').select(:name, :gender)

5. 查詢數值為 NULL 的資料

SELECT *
FROM heroes
WHERE name IS NULL;

6. 查詢夾帶關鍵字的資料
[0408 SQL-LIKE 語法說明](https://hackmd.io/-fgcjJmnRPioD8T76JfafA#SQL-%E8%AA%9E%E6%B3%95)

SELECT *
FROM heroes
WHERE name LIKE '%關鍵字%';  
-- Rails 無 LIKE 寫法

7. 查詢特定數值範圍的資料
- 列出 10 到 25 歲的英雄

SELECT *
FROM heroes
WHERE age >= 10 AND age <=25;
-- 或是
SELECT *
FROM heroes
WHERE age BETWEEN 10 AND 25;

8. 查詢多個條件的資料（不用同時符合）
- 列出 S 級跟 A 級的英雄

SELECT *
FROM heroes
WHERE hero_level = 'S' OR hero_level = 'A';
-- 寫法2
SELECT *
FROM heroes
WHERE hero_level IN ('S', 'A');

9. 查詢排除條件外的資料
- 列出不是 S 級的英雄

SELECT *
FROM heroes
WHERE hero_level != 'S';
--寫法2
SELECT *
FROM heroes
WHERE hero_level <> 'S';
--寫法3
SELECT *
FROM heroes
WHERE NOT hero_level = 'S';

- Ruby 指令

Hero.where.not(hero_level: 'S');

10. 查詢排除多個條件外的資料且須同時符合條件（負面表示）
- 列出不是 A 級也不是 S 級的英雄

SELECT *
FROM heroes
WHERE NOT hero_level = 'A' AND NOT hero_level = 'S';
---寫法2
FROM heroes
WHERE NOT hero_level NOT INT ('S', 'A');

11. 查詢符合條件數據的數量

SELECT count(*)
FROM heroes
WHERE hero_level = 'S';

- Rails

Hero.where(hero_level: 'S').count
Hero.count(hero_level: 'S')

12. 查詢特定條件下加總的結果
- 把所有 S 級英雄的年齡加總

SELECT SUM(age)
FROM heroes
WHERE hero_level = 'S';

- Rails

Hero.where(hero_level: 'S').sum(:age)

13. 查詢特定條件下數值平均的結果

SELECT AVG(age)
FROM heroes
WHERE hero_level = 'S';

- Rails

Hero.where(hero_level: 'S').average(:age)

14. 查詢特定條件下的最小值與最大值
- 把前面的 AVG, SUM 換成 MIN, MAX

15. 混合式 分群分組

SELECT hero_level, avg(age), max(age), min(age)  -- 資料調出來的欄位名稱
FROM heroes
WHERE gender = 'M'    -- WHERE 放置位置很重要，放錯就錯了
GROUP BY hero_level;   -- 用這個條件分門別類排

16. 挑出特定欄位不重複的資料

SELECT DISTINCT danger_level
FROM monsters;

17. 排序
- 正向

SELECT *
FROM heroes
WHERE hero_level ='S'
ORDER BY hero_rank;

- 反向

SELECT *
FROM heroes
WHERE hero_level ='S'
ORDER BY hero_rank DESC;

18. 只取特定順位的資料

SELECT *
FROM heroes
WHERE hero_level ='S'
ORDER BY hero_rank
LIMIT 3;

19. 隨機取資料

SELECT *
FROM heroes
ORDER BY random()
LIMIT 1;

20. 子查詢

SELECT *
FROM monsters
WHERE kill_by = (
  SELECT id
  FROM heroes
  WHERE name = '埼玉'
  );

21. 子查詢外加多個條件

SELECT *
FROM monsters
WHERE kill_by IN (
  SELECT id
  FROM heroes
  WHERE name IN ('埼玉', '傑諾斯')
  );

22. 交集 Join (在結果中同時顯示兩個工作表的內容)
- 用 `ON` 加條件
- inner join
- 兩個都有的列出
- 把 t1 裡的資料一個一個對到 t2 裡面的資料
- 所以 John 重複執行對應到 Amy, Alice.... 之後換 Mary 一個一個對到.....
- 資料會顯示在同一排中


SELECT *
FROM t1
INNER JOIN t2;

SELECT *
FROM t1
INNER JOIN t2
ON t1.username = t2.name; --加條件

- left join
- 左邊有的才做選取跟篩選，一筆僅對一筆
- 但左邊的東西如果對不到也會顯示出來


SELECT *
FROM t1 --左
LEFT JOIN t2 --右
ON t1.username = t2.name

- right join


SELECT *
FROM t1
RIGHT JOIN t2 --SQLite 沒有支援這個語法
ON t1.username = t2.name

- 反派是被誰打倒的

SELECT m.name, m.danger_level, h.name
FROM monsters as m
LEFT JOIN heroes as h
ON m.kill_by = h.id
WHERE m.kill_by IS NOT NULL


---

### 更新資料
1. 更新單個欄位
- 將第25號英雄的年齡改成10歲

UPDATE heros
SET age = 10
WHERE id = 25

- Rails 指令（通常在 controller 裡面執行）：

Hero.find(25).update(age: 10)

h = Hero.find(25)
h = update(age: 10)

2. 同時更新多個欄位的資料內容

UPDATE heroes
SET age = 10, hero_level = 'A', hero_rank = 5
WHERE id = 25

- Rails 指令（通常在 controller 裡面執行）：

Hero.find(25).update(age: 10, hero_level: 'A', hero_rank: 5)
# 寫法 2
h = Hero.find(25)
h = update(age: 10, hero_level: 'A', hero_rank: 5)

3. 用運算式執行更新
- 將所有英雄都加一歲！

UPDATE heroes
SET age = age + 1;
-- 總共找 1 次

- Rails 指令（通常在 controller 裡面執行）

Hero.all.each do |h|
  h.update(age: h.age + 1)
end
# 總共找 11 次 （包含一開始的 .all)
# 或是
Hero.update_all('age = age + 1')

在 rails 方法中， where 跟 update_all 後面的參數可直接書寫 SQL

---

### 刪除資料
1. 依條件刪除資料

DELETE FROM heroes
WHERE hero_level ='C'
-- 將 heroes 中所有 C 級英雄都刪掉

- Rails 指令（通常在 controller 裡面執行）

Hero.where(hero_level: 'C').each do |h|
  h.destroy
end
#寫法2
Hero.destroy_all(hero_level: 'C')

---

### SQLite
* 檔案式資料庫
#### 建立資料庫：
* 直接建立一個副檔名為 `.db` 的檔案
* 在 `DB Browser for SQLite` 程式裡面直接建立設定
* 設定時程式會自動生成資料庫語言

### MySQL 或其他非檔案型資料庫
注意最後面的分號要加，他代表一段話的結尾
* 建立資料庫

CREATE DATABASE name;

* 刪除資料庫

DROP DATABASE awesome_name;

* 建立資料表語法

CREATE TABLE heroes(
  id INT NOT NULL AUTO_INCREMENT,
  name CARCHAR(100) NOT NULL,
  gender CHAR(1),
  age INT,
  gero_level CHAR(1) NOT NULL,
  hero_rank INT,
  description TEXT,
  PRIMARY KEY (id)
)

---
## 題外話
* 建立 Ruby 註解
* 在編輯器使用 `ctrl 或 command` + `/` 可以直接建立註解
* 鍵入 `=begin` (註解內容) `=end`
* 不要用`"""`包住內容，這只是建立字串，Ruby 不會自動印出字串，但會執行該段原始碼
* 建議專案使用 PG 或 MySQL
* 預設的資料庫為sqlite，想換其他資料庫指令輸入`rails new 檔名 -d mysql`

### database 寫入 lock
可自己找一下

### CHAR VS. VARCHAR

範例：
建立兩個欄位
| 欄位名稱 | 資料型別 |
|--------|---------|
| A | VARCHAR(10)|
| B | CHAR(10) |

此時 A、B 差異不大，都可以用來儲存 10 bytes 的字元；
假設 A、B 都放了 Book 字串在裡面，這時候才會可以發現差異。

對於 A 欄位來說，因為它是VARCHAR可變動字元，所以實際儲存進去只有Book四個英文字，這時候它的大小為 4 Bytes。如果字元越長，大小就會跟著變大。

但是！對於B欄位而言，CHAR 是固定字元，所以當存入 Book的字元時，它會在後面繼續補上**虛擬**的空白，把原本僅有的 4 Bytes補足到 10 Bytes。

因此在這個情境下，使用 VARCHAR 資料型比較節省空間。

所以像是龍哥舉例的欄位，由於【姓名】這樣的欄位可能放入有長有短的字串，因為有些人的姓名可能是四個字、可能更多，像是前陣子的鮭魚們。所以比較常使用VARCHAR這樣的型別，比較彈性。
另外像是【性別】欄位，我們可能可以用1=男生、2=女生、3=多元性別之類的定義，因為要存入此欄位的值是123這樣的單一數字，我們可以選擇使用CHAR型別來固定此欄位的資料大小，讓他一直都保持是1 Byte。

### 避免發生忘記撰寫 WHERE 條件而導致資料無法挽回的情況
- 可使用以下語法

BEGIN TRANSACTION;

COMMIT;

ROLLBACK;

使用方式：
1. 首先執行 `BEGIN TRANSACTION` 指令，可以進入類似沙盒環境。
2. 此時可以去執行各種增刪修的動作
3. 覺得執行的動作，結果都是預期中的，那就可以執行 `COMMIT` 指令告訴資料庫，要依照剛剛下的語法執行。
4. 但發覺下錯語法的話，可以透過輸入 `ROLLBACK` 來還原所有剛剛執行的結果。

---

### Rails 裡修改 Migration

* 若執行完`$rails db:migrate`後發現有欄位名字打錯或是想刪除新增，==無法直接透過==編輯 migration 這個檔後再次執行指令`$rails db:migrate` 去覆寫，因為 `$rails db:migrate` 這個指令只會對還沒轉檔過的 Migration 檔案執行。

#### 如何判斷Migration是否執行？

專案裡`schema.rb`的檔案中，會記錄哪些 Migration 是已經被執行過的

ActiveRecord::Schema.define(version: 2021_04_06_131411) do

  create_table "books", force: :cascade do |t|
    t.string "title"
    t.text "content"
    t.string "contry"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end
end

也可以透過`$rails db:migrate:status`看執行狀況


其中狀態是 `up` 的表示這個 Migration 已執行過，`down` 則是尚未執行。

#### `down`的檔案修改
由於還沒有執行，可以直接在編輯器做修改，然後透過`$rails db:migrate`執行

#### `up`的檔案修改

1. 使用`$rails db:rollback`，若想一次退回 n 個步驟，使用`$rails db:rollback STEP=n`

$ rails db:rollback
== 20210401061449 CreateRestaurants: reverting ================================
-- drop_table(:books)
   -> 0.0075s
== 20210401061449 CreateRestaurants: reverted (0.0378s) =======================

> ==BUT==
使用 rollback 是有風險的，因為他執行的是刪除的動作，所以那個欄位的資料也會被刪除，除非是剛建立，不然建議另外新增一個Migration 來做修正

2. 新增一個 migration
`$rails g migration add_author_to_books`
並沒有強制規定命名規格，但建議命名成容易理解的方式

- 新增了一個 migration 後，進到他建立出的 migration 裡

class AddTitleToBook < ActiveRecord::Migration[6.1]
  def change
    add_column :books, :author, :string, default: "abc" (default為加入預設值)
    # add_column :資料表名稱, :要新增的欄位名稱, :資料型態,
    remove_column :books, :contry, :string
    # 刪除建議將完整的資料型態一併完成，這樣 rollback 才會知道要back的資料是什麼資料型態
  end
end

- 執行 `$rails db:migrate`
再回到`schema.rb`看就會發現，新增的欄位在底下出現，這裡的欄位順序並不影響

ActiveRecord::Schema.define(version: 2021_04_06_131411) do

  create_table "books", force: :cascade do |t|
    t.string "title"
    t.text "content"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.string "author"
  end
end

---
## 示意圖
#### 查詢資料(R)
- 查詢所有欄位資料
`*` 指所有欄位
- 加入條件：只查S級英雄

#### 資料庫系統備份
- 在系統裡面指定固定在特定時間執行特定指令
crontab 例行性工作排程
cronjob

分 時 日 月 星期 指令
* * * * * ls -al
1 * * * * ls -al => 每小時一分執行
10 2 * * * ls -al => 每天的 2:10 執行
10 * * * * ls -al => 每10分鐘執行
0 12 1 1,4,7,10 * ls -al =>4,7,10月的1日的12點

[例行性工作排程(crontab)](http://linux.vbird.org/linux_basic/0430cron.php)