---
# System prepended metadata

title: SQL
tags: [SQL]

---

DB(SQL)
===
> 2023/12/01

## 重點
- CRUD
  - insert into
  - where, and, in, or, order, group by
  - avg, count, sum, max, min
  - update...set...
  - delete...from...
  - sub query


## Active Record
Active Record 是一種設計模式，把資料表的一筆資料包裝成一個物件，並可在物件上增加額外的邏輯操作，讓資料的存取更便利。

## Model = 廣義的資料抽象概念
- model不等於資料庫，model為data base的翻譯官
- 依照Active Record模式設計的產物
- Model = Active Record.new


## ORM
- 物件 v.s 資料表
- 透過物件的方式去操作資料表，為了去簡化資料庫操作語法

## 資料庫 資料表
- 一個資料庫會有很多資料表
- Excel要交叉比對時，比較不好做

## SQL
- 結構查詢語言
- domain-specific language 特定領域語言
- RDBMS關聯式資料庫（讓資料間有關聯性）
- SQL 92
	- 在1992年訂出了標準，每間資料庫語言的共識
- 資料庫系統：
	- Oracle / MSSQL（微軟） / MySQL（免費） / PostgreSQL（免費）(做網站官方推薦)  / SQLite（免費）
- 建立資料庫（資料庫必須一直開著）
- SQLite為檔案，不需要開伺服器，但效能不好

## 建立資料庫
```ruby
rails db:migrate
```
- 最常使用

```ruby
rails db:create <database_name>
```
- 目前比較少在做

```ruby
rails -T
```
可以看到目前可以做的事

## 刪除資料庫
```ruby
drop database <database_name>
```

## 資料型態
[參考：devbricker](https://devbricker.github.io/post/database/sql-server/sqlserverbasic5/)

### 常用字串(元)資料 (Character & Strings Data)
![image](https://hackmd.io/_uploads/HJi8NmDr6.png)
- gender CHAR(1) -> 限制輸入一個值 男 or 女 or 否
- string -> varchar
- 可以在ruby下`t.column`
### 常用數值資料 (Numeric Data)
![image](https://hackmd.io/_uploads/SkkC4Xvrp.png)
- int, integer 沒有不一樣

### 常用日期/時間資料 (Datetime Data)
![image](https://hackmd.io/_uploads/S1OwV7PHp.png)


### 布林值 (Boolean Data)
![image](https://hackmd.io/_uploads/B1X_EQwrT.png)

### 二進位字串(Binary)
![image](https://hackmd.io/_uploads/rkMaVQDBp.png)



- varchar v.s. char
- varchar會把沒用的格子給別人用，並用一個Byte去紀錄用了幾格格子
- 固定長度選用char
- 不固定長度 varchar

## 追加欄位
```sql=
ALTER TABLE heroes
ADD COLUNM super_power VARCHAR(100)
```

## 刪除欄位
```sql=
ALTER TABLE heroes
DROP COLUNM super_power;
```

## 刪除資料表
```sql=
DROP TABLE heroes
```
## DDL/DML/DQL
DDL = DataDefinitionLanguage 資料操作語言
- create
- delete
- update

DML


DQL

## 新增資料 C
```sql
INSERT INTO heroes
(name, gender, age, hero_level, hero_rank)
```

```sql
INSERT INTO ccc
(name, age)
VALUES
('abc', 18); 
```
- 表格和欄位不能用數字開頭，若要的話要引號
- 可省略欄位（但不建議）
- 寫入欄位順序一定要按照表格順序嗎？
	- 如果省略某些欄位就需要
	- 通常不會用順序拿欄位，主要用key去拿
- 沒寫入欄位的預設值是什麼？
	- 要看資料庫&型態，數字型態預設值為0

- VARCHAR(10)如果放超過10個字的話會怎樣？
	- 寫不進去會壞掉
- 單引號會造成壞掉，所以改成\'（不要當單引號看，是字元）

## R
```SQL
SELECT *
FROM heroes;
```
- * 代表所有欄位
- Rails
```ruby=
Hero.all
```

挑出等級是S級的
```sql!
SELECT *
FROM heroes
WHERE hero_level = 'S'
```
- Rails 寫法
```ruby
 Hero.where(hero_level: 'S')
```

- 兩個條件找尋
```sql!
SELECT *
FROM heroes
WHERE hero_level = 'S' AND gender ='F'
```

- Rails寫法
```ruby!
Hero.where(hero_level: 'S', gender: 'F')
```

- 只搜尋部分欄位
```sql!
SELECT name, age
FROM heroes
WHERE name != '0' AND age >= '5'
```

- BLOB:可以放圖片、文字檔、壓縮檔，都可以擺，但儘量不要把圖片擺進資料表，因為SELECT * 也會把它拿出來

```sql!
SELECT name, age
FROM heroes
WHERE name like '%背心%'
```

```ruby
Hero.where("name like '%#{..}%'")
```
- 可以用字串

### 查詢年紀區間
```sql
SELECT *
FROM heroes
WHERE age >= 10 AND age <= 25
```

- ruby

```ruby
Comment.where(id: 10..20)
```

```squ!
SELECT *
FROM heroes
WHERE BETWEEN 10 AND 25
```

### 找S跟A級的

- SQL
```sql=
SELECT *
from heroes
where hero_level='A' or hero_level='S'
```
```sql
SELECT *
from heroes
where hero_level in ('S', 'A')
```

- ruby
```ruby=
Hero.where(hero_level: ['S', 'A'])
```

### 列出不是S級的英雄
- SQL
```sql=
SELECT *
from heroes
where hero_level != 'S'
```

```sql=
SELECT *
from heroes
where hero_level is not 'S'
```

```sql=
SELECT *
from heroes
where hero_level <> 'S'
```

- ruby
```ruby=
Hero.where.not(hero_level: ['S'])
```

```ruby=
Hero.where('hero_level != ?', 'S')
```
- 用字串來組

- rails會幫忙處理 SQL injection（常見的攻擊手法）

### 列出不是S也不是A
```sql
SELECT *
from heroes
where hero_level != 'S'  AND  hero_level != 'A'
```

```sql
SELECT *
from heroes
where hero_level not in ('S', 'A')
```

- ruby

```ruby=
Hero.where.not(hero_level: ['S', 'A'])
```

## U 更新資料
```sql
UPDATE heroes
SET age = 10
WHERE id = 25 --條件句，很重要
```
### 把25號id的人改成10歲
- ruby

### 更新多個欄位
```sql=
UPDATE heroes
SET age = 10
WHERE id = 25, age = 0 --條件句，很重要
```

### 所有人都老一歲
```sql=
UPDATE heroes
SET age = age + 1
```

### 調整多個東西
請把「崎玉（id = 35）」的英雄等級由原本的 C 級 388 位調整成 B 級的 101 位。
```sql=
UPDATE heroes
SET hero_level = 'b', hero_rank = 101
WHERE id = '35'
select * from heroes;
```

## Delete
```sql=
DELETE FROM heroes
WHERE id = '10'
select * from heroes;
```

## 計算總數
- Ruby
```ruby
Comment.count
```

```sql=
SELECT count(*)
from heroes
WHERE hero_level = 'S';

```

## 計算平均
```sql=
select avg(age)
FROM heroes
```

- ruby
```ruby=
Product.agerage(:price)
```

## 計算總數
```sql=
select sum(age)
WHERE hero_level = 'A'
FROM heroes
```


- ruby
```ruby=
Product.sum(:price)
```

## 找出最小值
```sql=
select id, name, min(age)
FROM heroes
```

## 分組

```sql
SELECT hero_level, sum(age)
FROM heroes
group by hero_level;
```

## 挑掉重複的

```sql
SELECT DISTINCT danger_level
FROM monsters
```

## 排序
- SQL
```sql=
SELECT *
FROM heroes
WHERE hero_level = 'S'
ORDER BY hero_rank
```

- 可以用在瀑布流

## 表格交叉查詢
ER圖
![image](https://hackmd.io/_uploads/S1j4NXDSp.png)


### 哪些反派是被琦玉打倒的

- 主要鍵
	- 某個資料表的一個欄位
	- 獨一無二的值
	- 不可以是空的

- Foreign 外部鍵
	- 參照到另一個資料表的主要鍵
	- 用來確認該資料表的紀錄跟被對照到的表格的資料是對的起來的
	- 跟PK不同，FK不需要獨一無二（可以一對多）
	- 可是空的（不需要屬於任何人）

- 使用Foreign key
	- RESTRICT 限制
	- CASCADE 階層（由上而下，你砍我跟你砍）
	- NULL 你空我就空
	- NO ACTION 不作事

- 如果原本的英雄資料被刪掉了，那被打倒的反派也要跟著刪掉嗎？
	- 某個反派被根本不存在的英雄打倒了？

- 確認資料完整性
- SQL:
	```sql
	SELECT *
	FROM monsters
	WHERE kill_by = 
	( SELECT id
	  FROM heroes
	  WHERE name = '埼玉'
	 )
	 ```
- '埼玉' 是可以換的
- querystring，做一個GET，拿進model用like比對