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)
- gender CHAR(1) -> 限制輸入一個值 男 or 女 or 否
- string -> varchar
- 可以在ruby下`t.column`
### 常用數值資料 (Numeric Data)
- int, integer 沒有不一樣
### 常用日期/時間資料 (Datetime Data)
### 布林值 (Boolean Data)
### 二進位字串(Binary)
- 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圖
### 哪些反派是被琦玉打倒的
- 主要鍵
- 某個資料表的一個欄位
- 獨一無二的值
- 不可以是空的
- 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比對
Sign in with Wallet
Connect another wallet