---
# System prepended metadata

title: 20220805 資料庫與資料表 Database and Table
tags: [SQL]

---

# 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 資料查詢語言

```sql=
INSERT INTO {table_name}
{[table_columns]}
VALUE
{[values]}
```

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

## 資料庫查詢與修改

### 查詢全部相關
```sql=
SELECT *
FROM heroes;
```
在指令rails hero.all (撈東西查東西)
```ruby
#rails
Hero.all
```

### where 可以篩選
```sql=
SELECT *
FROM heroes
WHERE hero_level = "S";
```

```ruby
#rails
Hero.where(hero_level: "s")
```
### And 增加條件
```sql=
SELECT *
FROM heroes
WHERE hero_level = "S"
AND gender = "F";
```
```ruby
# rails
Hero.where(hero_level: "s", gender: "F")
```

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

### 找出年紀是空的(NULL)
```sql=
SELECT name, age
FROM heroes
WHERE age is NULL
```
```ruby
# rails
Hero.where(age: nil)
```

### 找包含特定字元 (背心)
```sql=
SELECT *
FROM heroes
WHERE name like "%背心%";
-- %背心% 表示前後還有字，沒加上% 只會找完全等於"背心"的
```
```ruby
# rails
# 本身沒有like的寫法
Hero.where("name like ?", "%背心%")
```

### 找年齡範圍(10歲到25歲)
```sql=
SELECT *
FROM heroes
-- WHERE age >=10 AND age <=25; 
WHERE age BETWEEN 10 AND 25; 
```
```ruby
# rails 
Hero.where(age: 10..25)
```
### 找多種條件(S級跟A級)
```sql=
SELECT *
FROM heroes
-- WHERE hero_level = "S" OR hero_level = "A"
WHERE hero_level IN ("S","A"); 
```
```ruby
# rails 
Hero.where(hero_level: ["S", "A"])
```

### 找不等於(S級)
```sql=
SELECT *
FROM heroes
WHERE hero_level != "S";
-- WHERE hero_level <> "S";
-- WHERE NOT hero_level = "S";
```

### 找不是S也不是A
```sql=
SELECT *
FROM heroes
WHERE hero_level NOT IN ("S","A"); 
```
```ruby
# rails 
Hero.where.not(hero_level: ["S", "A"])
```

### 找災難等級為鬼
```sql=
SELECT *
FROM monsters
WHERE danger_level = "鬼";
```

### 更新資料 update

把id為25的英雄age改成10
```sql=
UPDATE heroes
SET age = 10
WHERE id = 25;
```
```ruby
# rails 
Hero.find(25).update(age: 10)
```

```sql=
UPDATE heroes
SET age = age + 1;
```

### 刪除所有A級 delete
```sql
DELETE FROM heroes
WHERE hero_level = "A"
```
```ruby
# rails 
Hero.where(hero_level: "A").destroy_all
```

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


### 計算總數 count
```sql
SELECT count(*)
FROM heroes
WHERE hero_level = "A";
```
```ruby
# rails 
Hero.where(hero_level: "A").count
```

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

sum 加總
```sql
SELECT sum(age)
FROM heroes
WHERE hero_level = "S";
```
```ruby
# rails
Hero.where(hero_level: "A").sum(:age)
```
avg 平均
```sql
SELECT avg(age)
FROM heroes
WHERE hero_level = "S";
```
```ruby
# rails
Hero.where(hero_level: "A").average(:age)
```

### max/min
```sql
SELECT max(age)
SELECT min(age)
FROM heroes;
```
```ruby
Hero.maximum(:age)
Hero.minimum(:age)
```

### 分組分別計算
```sql
SELECT hero_level, avg(age)
FROM heroes
GROUP by hero_level;
```

### 挑掉重複 Distinct
```sql
SELECT DISTINCT danger_level
FROM monsters;
```

### 排序 ORDER BY
```sql
SELECT *
FROM heroes
WHERE hero_level = "S"
ORDER by hero_rank;
-- 預設正向排序 DESC逆向排序 
-- ex:ORDER by hero_rank DESC;
```
```ruby
# rails
# 正向
Hero.where(hero_level: "S").order(:hero_rank)
# 逆向
Hero.where(hero_level: "S").order(hero_rank: :desc)
```

### 設取出的上限 limit
```sql
SELECT *
FROM heroes
WHERE hero_level = "S"
-- ORDER by random() 可以隨機取
ORDER by random()
LIMIT 3;
```

### 合併分段查詢
被埼玉打倒的怪物
```sql
SELECT *
FROM monsters
WHERE kill_by = (
	SELECT id
	FROM heroes
	WHERE name = "埼玉"
);
```
被埼玉和傑諾斯打倒的怪物
```sql
SELECT *
FROM monsters
WHERE kill_by in (
	SELECT id
	FROM heroes
	WHERE name in ("埼玉","傑諾斯")
);
```

### Join
rails 多對多一定會用到
![](https://i.imgur.com/rHi5Y76.png)

找t1和t2兩個表格內名字相同的
```sql
SELECT *
FROM t1
JOIN t2
on t1.username = t2.name;
```
```sql
SELECT *
FROM t1
LEFT JOIN t2
on t1.username = t2.name;
```


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


```sql=
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
```

```sql=
-- 查出被 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'
```

```sql=
-- 查詢每位反派是被哪位英雄打倒的（如果有的話）
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
