# 20220805 資料庫與資料表 Database and Table
###### tags: `SQL`

## SQL Structured Query Language

* DSL = Domain Specific Language
* SQL 92: 1992年提出的標準，一個SQL各自表述

關聯式資料庫 RDBMS
- Oracle/MSSQL/MySQL/PostgreSQL

### 名詞解釋
#### Active Record
一種設計模式，把資料包裝成物件，在物件上進行邏輯操作
> ActiveRecord 這個名稱的由來是它使用了 Martin Fowler 的[*Active Record*](https://martinfowler.com/eaaCatalog/activeRecord.html)設計模式。

#### Model
不等於資料庫，抽象的概念，依照active record模式設計的產物

## 建立/刪除資料庫
建立
$ create database name
刪除
$ drop database name

## 資料型態
- CHAR/VARCHAR/TEXT
- INTEGER/DECIMAL
- DATE/DATETIME
- BLOB = Binary Large Object
- 其他

VARCHAR = Variable Character

CREATE DATABASE / CREATE TABLE / ADD COLUMN
DROP DATABASE / DROP TABLE / DROP COLUMN

### 補充
- SQL語法不分大小寫，關鍵語法會用大寫
- INT、INTEGER 差別? => 無
- CHAR(10)、VARCHAR(10)型態差別?
* VARCHAR n+1 (1個紀錄有幾個) => 用於字數不固定 ex:地址
* CHAR n => 用於固定長度 ex:身分證字號

DDL 資料定義語言 / DML 資料庫操作語言 / DQL 資料查詢語言

INSERT INTO {table_name}
{[table_columns]}
VALUE
{[values]}

> 寫入資料的欄位順序不重要 (不必和表格順序相同)
> 預設值

## 資料庫查詢與修改

### 查詢全部相關

SELECT *
FROM heroes;

在指令rails hero.all (撈東西查東西)

#rails
Hero.all

### where 可以篩選

SELECT *
FROM heroes
WHERE hero_level = "S";

#rails
Hero.where(hero_level: "s")

### And 增加條件

SELECT *
FROM heroes
WHERE hero_level = "S"
AND gender = "F";

# rails
Hero.where(hero_level: "s", gender: "F")

> 搜尋時為什麼只要選部分? select* 全選不就好?
=> 可以省流量，減少讀取
BTW 圖片不應放在資料庫

### 找出年紀是空的(NULL)

SELECT name, age
FROM heroes
WHERE age is NULL

# rails
Hero.where(age: nil)

### 找包含特定字元 (背心)

SELECT *
FROM heroes
WHERE name like "%背心%";
-- %背心% 表示前後還有字，沒加上% 只會找完全等於"背心"的

# rails
# 本身沒有like的寫法
Hero.where("name like ?", "%背心%")

### 找年齡範圍(10歲到25歲)

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

# rails 
Hero.where(age: 10..25)

### 找多種條件(S級跟A級)

SELECT *
FROM heroes
-- WHERE hero_level = "S" OR hero_level = "A"
WHERE hero_level IN ("S","A"); 

# rails 
Hero.where(hero_level: ["S", "A"])

### 找不等於(S級)

SELECT *
FROM heroes
WHERE hero_level != "S";
-- WHERE hero_level <> "S";
-- WHERE NOT hero_level = "S";

### 找不是S也不是A

SELECT *
FROM heroes
WHERE hero_level NOT IN ("S","A"); 

# rails 
Hero.where.not(hero_level: ["S", "A"])

### 找災難等級為鬼

SELECT *
FROM monsters
WHERE danger_level = "鬼";

### 更新資料 update

把id為25的英雄age改成10

UPDATE heroes
SET age = 10
WHERE id = 25;

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

UPDATE heroes
SET age = age + 1;

### 刪除所有A級 delete

DELETE FROM heroes
WHERE hero_level = "A"

# rails 
Hero.where(hero_level: "A").destroy_all

* 資料庫的資料被刪除，流水編號就會空在那邊，不會補
ex:想像訂單完成後刪除，如果新增訂單補在同一個編號就會和原本對不起來

### 計算總數 count

SELECT count(*)
FROM heroes
WHERE hero_level = "A";

# rails 
Hero.where(hero_level: "A").count

### 加總 sum / 平均 avg
rails不要自己找出來算，叫資料庫算

sum 加總

SELECT sum(age)
FROM heroes
WHERE hero_level = "S";

# rails
Hero.where(hero_level: "A").sum(:age)

avg 平均

SELECT avg(age)
FROM heroes
WHERE hero_level = "S";

# rails
Hero.where(hero_level: "A").average(:age)

### max/min

SELECT max(age)
SELECT min(age)
FROM heroes;

Hero.maximum(:age)
Hero.minimum(:age)

### 分組分別計算

SELECT hero_level, avg(age)
FROM heroes
GROUP by hero_level;

### 挑掉重複 Distinct

SELECT DISTINCT danger_level
FROM monsters;

### 排序 ORDER BY

SELECT *
FROM heroes
WHERE hero_level = "S"
ORDER by hero_rank;
-- 預設正向排序 DESC逆向排序 
-- ex:ORDER by hero_rank DESC;

# rails
# 正向
Hero.where(hero_level: "S").order(:hero_rank)
# 逆向
Hero.where(hero_level: "S").order(hero_rank: :desc)

### 設取出的上限 limit

SELECT *
FROM heroes
WHERE hero_level = "S"
-- ORDER by random() 可以隨機取
ORDER by random()
LIMIT 3;

### 合併分段查詢
被埼玉打倒的怪物

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

被埼玉和傑諾斯打倒的怪物

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

### Join
rails 多對多一定會用到

找t1和t2兩個表格內名字相同的

SELECT *
FROM t1
JOIN t2
on t1.username = t2.name;

SELECT *
FROM t1
LEFT JOIN t2
on t1.username = t2.name;

SELECT monsters.name as "反派", heroes.name as "英雄"
FROM heroes
JOIN monsters
on heroes.id = monsters.kill_by;

SELECT h.name, m.name
FROM battle_histories as b
inner JOIN heroes as h, monsters as m
ON h.id = b.hero_id and m.id = b.monster_id
WHERE h.id = 35

-- 查出被 S 級英雄打倒的所有反派
SELECT m.name, h.name, h.hero_level
FROM monsters AS m
JOIN heroes AS h
on m.kill_by == h.id
WHERE hero_level = 'S'

-- 查詢每位反派是被哪位英雄打倒的（如果有的話）
SELECT m.name AS "反派", h.name AS "英雄"
FROM monsters AS m
LEFT JOIN heroes AS h
on m.kill_by == h.id

## 資料庫正規化 Normalization
<mark>**龍哥說這邊不重要**</mark>

目的:降低資料重複性
正規化: 每個資料表存取應該存的就好
* 通常做到第二第三就差不多
* 做過並不會有比較好的效能、主要是讓資料分清楚

### 第一正規化 1NF
- 每個欄位只有一筆

### 第二正規化 2NF
- 先滿足1NF
- 去除部分相依性

### 第三正規化 3NF
- 先滿足2NF
- 移除遞移相依

### 第四正規化 4NF

### 第五正規化 5NF