keep learning

July 23, 2023

Viiisit SQL!

before_action :review

What is Active Record?

Active Record 是一個設計模式，也是 Ruby on Rails 中的一個核心概念。
用於處理資料庫和應用程式之間的 ORM（Object-Relational Mapping）模式。
Active Record 的設計目的是在程式碼中實現對資料庫記錄的操作。

Model 在 Rails 中是 ActiveRecord 的一部分，是一個用於實現對資料庫的操作。
Migration 則是用來定義資料庫結構變更的紀錄。

SQL

回歸到本次主題 - SQL，之前已經理解 Rails 裡 Model 與 Migration 的關係，
這次透過練習 SQL 語法來回顧，Rails 的 Migration 檔案是如何轉換成對應資料庫的 SQL 語法，執行資料庫結構的變更！

What is SQL?

SQL（Structured Query Language）是一種專門設計用於資料庫操作的語言，
使用簡單的指令和語法來與資料庫進行交互。
包括查詢資料、新增、更新和刪除記錄，以及創建和管理資料庫結構等。
常見的資料庫如 MySQL、PostgreSQL、SQLite、Oracle 等。

SQL 語法主要包含:

查詢 Query: 檢索資料庫中的資料，最常見的查詢是 SELECT，從資料表中檢索特定的資料。
新增 Insert: 向資料表中插入新的記錄，使用 INSERT INTO 來執行此操作。
更新 Update: 修改資料表中現有記錄的值，使用 UPDATE 來執行此操作。
刪除 Delete: 從資料表中刪除記錄，使用 DELETE FROM 來執行此操作。
建立 Create: 創建新的資料庫或資料表，使用 CREATE DATABASE 和 CREATE TABLE。
修改 Alter: 修改現有的資料表結構，如新增或刪除欄位，使用 ALTER TABLE。
索引 Index: 加快查詢操作的速度，使用 CREATE INDEX 來建立索引。
關聯 Join: 將多個資料表進行聯接，使用 JOIN 關鍵字來實現此功能。
永久刪除 Drop: 用於刪除整個資料庫中的資料表。無法復原！

char vs varchar

char（固定長度字串）：
佔用固定的儲存空間，無論實際儲存的字串長度是多少，都會佔用指定的字元數。
如果儲存的字串長度小於定義的長度，則會使用空白字元填充。
ex. char(10) 定義的欄位將始終佔用 10 個字元的儲存空間，不管實際存儲的字串長度是多少。
varchar（可變長度字串）：
僅佔用實際存儲的字串長度所需的儲存空間，不會浪費空間。只會使用所存儲字串的實際長度。
ex. varchar(10) 定義的欄位，若存儲一個長度為 5 的字串，則只佔用 5 個字元的儲存空間。

兩者在效能上的差異：空間使用 和 查詢速度！

char 的固定長度特性會造成空間浪費，尤其是當大量的資料存儲在 char 欄位中時，會佔用大量的儲存空間；varchar 則可以節省儲存空間，因為它只佔用實際存儲字串長度所需的空間。
在查詢速度方面，通常 char 的查詢速度稍微快於 varchar，因為 char 的固定長度使得查詢時更容易定位欄位的位置。

Brief Summary

當資料欄位的長度固定且不會經常變動時，使用 char，因為它的查詢速度通常較快。
而當資料欄位的長度不固定或可能變動時，使用 varchar 可以節省儲存空間並避免浪費。
選擇使用 char 還是 varchar 取決於資料的特性和應用需求。

ORM 方式 vs SQL 方式

在 Rails 中，可以使用 Migration 來建立資料庫的資料表，
透過 Migration 使用 ActiveRecord 的方式來定義資料表的欄位，
而 ActiveRecord 會將這些定義轉換成對應的 SQL 語法，進行資料庫的建立。

以下使用 ORM 和 SQL 的方式來建立資料表的範例：

ORM

Migration 使用 ActiveRecord 提供的 t.string 方法來定義 title 欄位。
在這裡，t.string 會轉換成資料庫相應的 varchar(255)，
其中 255 是 string 欄位的預設長度。
1
2
3
4
5
6
7
8
class CreateArticles < ActiveRecord::Migration[7.0]
def change
create_table :articles do |t|
t.string :title
t.timestamps
end
end
end

SQL

SQL 語法直接定義 articles 資料表，並將 title 欄位設置為 VARCHAR(255)。

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

如果想限制 title 欄位的長度為 10，可以使用以下方式：

ORM

傳入 limit: 10 參數來定義 title 欄位，這會將 title 欄位轉換成 VARCHAR(10)，限制長度為 10。

class CreateArticles < ActiveRecord::Migration[7.0]
  def change
    create_table :articles do |t|
      t.string :title, limit: 10
      t.timestamps
    end
  end
end

SQL

將 title 欄位設置為 VARCHAR(10)，限制長度為 10。

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(10),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

Brief Summary

ORM 的方式使用 ActiveRecord 提供的方法來定義資料表的欄位，並由 ActiveRecord 轉換成相應的 SQL 語法。
而直接使用 SQL 的方式則直接使用原生的 SQL 語法來定義資料表的欄位。
使用 ORM 的方式在 Rails 中更容易使用 Ruby 語法來定義資料表，而不需要使用 SQL 語法。

Remark:
關鍵字寫大寫，易讀性高！
INT 跟 INTEGER 是一樣的，只是因為不同時代留下來的渣渣

練習 SQL 語法！

Remark:
每段最後同時寫上呼應的 Rails 語法，底下的 Hero 是 Model 喔！

SELECT 查詢資料

1
2
3

SELECT * -- 挑出所有欄位
FROM heroes;
-- Rails: Hero.all

SELECT *
FROM heroes
WHERE hero_level = 'S';
-- Rails: Hero.where(hero_level: 'S')

-- Method 1
SELECT *
FROM heroes
WHERE (hero_level, gender) =  ('S', 'F');

-- Method 2
SELECT *
FROM heroes
WHERE hero_level = 'S';
AND gender = 'F';
-- Rails: Hero.where(hero_level: 'S', gender: 'F')

只要名字與等級欄位：

SELECT name, hero_level
FROM heroes
WHERE hero_level = 'S';
-- Rails: Hero.select(:name, :hero_level).where(hero_level: 'S')

判斷 age 是 NULL：

SELECT *
FROM heroes
WHERE age is NULL;
-- Rails: Hero.where(age: nil)

練習搜尋的語法 LIKE
找有背心開頭兩個字的詞：

SELECT *
FROM heroes
WHERE name LIKE '背心%' ;
-- Rails: Hero.where("name LIKE '%背心%'")

尋找特定範圍：

-- Method 1
SELECT *
FROM heroes
WHERE age BETWEEN 10 AND 25; -- 有包含 10 and 25

-- Method 2
SELECT *
FROM heroes
WHERE age > 10 AND age < 25;
-- Rails: Hero.where(age: 10...25)

尋找 hero_level 是 S 級跟 A 級的：

-- Method 1
SELECT *
FROM heroes
WHERE hero_level IN ('S', 'A');

-- Method 2
SELECT *
FROM heroes
WHERE hero_level = 'S' OR hero_level = 'A';
-- Rails: Hero.where(hero_level: ['S', 'A'])

尋找 hero_level 不是 S 級的：

-- Method 1
SELECT *
FROM heroes
WHERE hero_level != 'S';

-- Method 2 用大於小於組合！ SQL 專屬！
SELECT *
FROM heroes
WHERE hero_level <> 'S';
-- Rails: Hero.where.not(hero_level: 'S')
-- Rails: Hero.where("hero_level != 'S'")

尋找 hero_level 不是 S 級，也不是 A 級的：

SELECT *
FROM heroes
WHERE hero_level NOT IN ('S', 'A');
-- Rails: Hero.where.not(hero_level: ['S', 'A'])

尋找是 A 級的男性，列出姓名與年齡：

1
2
3

SELECT name, age
FROM heroes
WHERE gender = 'M' AND hero_level = 'A';

UPDATE 更新資料

1
2
3

UPDATE heroes
SET age = 10
WHERE id = 25

讓所有的 heroes 年齡都加一：

1 2	UPDATE heroes SET age = age + 1;

請把 id = 35 的英雄等級由原本的 C 級 388 位調整成 B 級的 101 位：

1
2
3

UPDATE heroes
SET hero_level = 'B' ,  hero_rank = 101
WHERE ID = 35;

DELETE 刪除資料
1
2
DELETE FROM
WHERE

進階查詢 - 計算總數

計算所有 S 級的總數：

SELECT count(*)
FROM heroes
WHERE hero_level = 'S';
-- Rails: Hero.where(hero_level: 'S').count

計算 A 級年齡的總和：

SELECT sum(age)
FROM heroes
WHERE hero_level = 'A' AND age IS NOT NULL;
-- Rails: Hero.where(hero_level: 'A').sum

計算 A 級年齡的平均：

SELECT AVG(age)
FROM heroes
WHERE hero_level = 'A' AND age IS NOT NULL;
-- Rails: Hero.where(hero_level: 'A').where.not(age: nil).average(:age)

分組

以每個等級做分組且算出每組的平均年齡：

SELECT hero_level, avg(age)
FROM heroes
GROUP BY hero_level;
-- Rails: Hero.group(:hero_level).average(:age)

DISTINCT

在 SQL 中，DISTINCT 用於去除查詢結果中重複的行，使得查詢結果只包含唯一值。

挑出不同的級數：
1
2
SELECT DISTINCT danger_level
FROM monsters;

排序

對 S 級做排序：

-- 升冪排序
SELECT *
FROM heroes
WHERE hero_level = 'S'
ORDER BY hero_rank ASC;

-- 降冪排序
SELECT *
FROM heroes
WHERE hero_level = 'S'
ORDER BY hero_rank DESC;

SELECT *
FROM heroes
WHERE hero_level = 'S'
AND age IS NOT NULL -- 可以排除掉沒有的值
ORDER BY age;

SELECT *
FROM heroes
WHERE hero_level = 'S'
ORDER BY hero_rank
LIMIT 5; -- 限定數量

Remark:
蝦皮的捲軸分頁視窗：
如果 page = params[:page] || 1

1
2
3
4
5
SELECT *
FROM items
ORDER BY id
LIMIT 6
OFFSET (page - 1) * 6;

INNER JOIN 兩者交集
LEFT JOIN 左邊為主對照右邊；RIGHT JOIN 右邊為主對照左邊

SELECT *
FROM t1
INNER JOIN t2
on t1.username = t2.name; -- 比對 t1 跟 t2 的相同名稱

反派是被誰打倒的？

SELECT monsters.name, heroes.name
FROM monsters
INNER JOIN heroes
on monsters.kill_by = heroes.id
WHERE kill_by IS NOT NULL;

簡化：

SELECT m.name, h.name
FROM monsters as m
INNER JOIN heroes as h
on m.kill_by = h.id
WHERE m.kill_by IS NOT NULL;

透過 battle_histories 將資訊由 id 轉為 name：

SELECT h.name,  m.name
FROM battle_histories as bh
INNER JOIN heroes as h
INNER JOIN monsters as m
on bh.hero_id = h.id AND bh.monster_id = m.id;

ERD - Entity Relationship Diagram 實體關係圖

一種用來描述資料庫中資料實體和它們之間關係的視覺化工具。

運用 子查詢，尋找被埼玉與傑諾斯幹掉的怪獸：

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 ('埼玉', '傑諾斯')
)

after_action :與 excel 的差別

資料庫和 Excel 是兩種不同的資料管理工具，在設計和使用上有很多差異：

資料結構：
- 資料庫使用表格（Table）來組織和儲存資料，包含多個欄位（Column）和資料列（Row）。
- Excel 是一個電子試算表軟體，使用單一工作表來儲存資料，每個工作表包含多個儲存格。
多用戶支援：
- 資料庫可以支援多個使用者同時存取和修改資料，通過設置權限保護資料的一致性和安全性。
- Excel 常常是單用戶的工具，一般情況下不支援多個使用者同時編輯。
資料容量：
- 資料庫可以處理大量的資料，並且支援擴展來應對日益增長的資料量。
- Excel 對於大型資料集的處理可能受限，因為它主要是設計為處理小型資料集。
查詢和分析：
- 資料庫提供更強大的查詢語言（如 SQL），能夠進行複雜的查詢和分析操作。
- Excel 也提供一些基本的查詢和過濾功能，但相對於資料庫來說功能有限。
適用範圍：
- 資料庫主要用於大型企業或應用程式，用於儲存和管理大量結構化資料。
- Excel 常用於個人或小型組織，用於較小的資料集的儲存和分析。

Brief Summary

資料庫適用於大型結構化資料的管理和處理，並且支援多用戶存取和複雜的查詢需求。
Excel 則適合用於較小的資料集，用於個人或小型組織的資料管理和分析。

Summary

透過以上的說明，可以了解到資料庫提供了很多有效的方法來儲存、管理和查詢資料，確保資料的安全、完整性和一致性，並且擁有大量處理資料的能力也以 Excel 來比較之間的差異，之後要不停回顧與更新，這樣才會抓牢 SQL 基礎知識，不能忘記！

參考資料：
➫ RailsGuides

Viiisit SQL!

before_action :review

What is Active Record?

SQL

What is SQL?

char vs varchar

Brief Summary

ORM 方式 vs SQL 方式

Brief Summary

練習 SQL 語法！

ERD - Entity Relationship Diagram 實體關係圖

after_action :與 excel 的差別

Brief Summary

Summary

Viiisit [Ruby on Rails] - 常見的查詢方法!

Viiisit [Ruby on Rails] - Model!