Day26 - possgreSQL(1) - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

2025 iThome 鐵人賽

DAY 26

佛心分享-IT 人自學之術

欸欸!! 這是我的學習筆記系列第 26 篇

Day26 - possgreSQL(1)

17th鐵人賽

ayeaye

2025-10-10 21:26:23

55 瀏覽

分享至

前言

前面提到了 supabase 用的是 possgreSQL，這兩天就來整理 possgreSQL 的相關基礎知識。

基礎知識

什麼是資料庫

資料庫（Database）是一個有組織地儲存和管理資料的集合，就像是一個電子化的檔案櫃。

為什麼需要資料庫？

資料持久化：應用程式關閉後資料不會消失
資料共享：多個使用者可以同時存取相同的資料
資料安全：提供存取控制和備份機制
資料一致性：確保資料的準確性和完整性

生活中的例子：

銀行系統：管理帳戶資訊、交易紀錄
電商平台：儲存商品資料、訂單資訊
社交媒體：保存使用者資料、貼文、留言

資料表的概念

資料表（Table）是資料庫中儲存資料的基本單位，可以想像成 Excel 的工作表。

資料表的組成：

列（Row）：每一列代表一筆資料記錄
欄（Column）：每一欄代表資料的某個屬性
儲存格（Cell）：列與欄的交叉點，儲存具體的值

範例：使用者資料表

id	name	email	age
1	Alice	alice@example.com	25
2	Bob	bob@example.com	30
3	Charlie	charlie@example.com	28

每一列是一個使用者
每一欄代表使用者的屬性（姓名、email、年齡）
一個資料庫通常包含多個資料表，彼此之間可以建立關聯

資料類型的概念

資料類型（Data Type）定義了欄位可以儲存什麼樣的資料，就像變數的型別。

為什麼需要定義資料類型？

節省空間：整數比文字佔用更少空間
確保資料正確：年齡欄位不應該儲存文字
提升效能：資料庫可以針對不同類型優化查詢

常見的資料類型分類：

分類	類型	說明	範例
數字	INTEGER	整數	25, -100
數字	DECIMAL	精確小數	99.99, 12.50
文字	VARCHAR	可變長度字串	"Hello", "Alice"
文字	TEXT	長文字	文章內容、描述
日期時間	DATE	日期	2024-01-15
日期時間	TIMESTAMP	日期+時間	2024-01-15 14:30:00
布林	BOOLEAN	真/假	true, false

資料庫的系統構成

一個完整的資料庫系統由三個主要部分組成：

1. 資料庫（Database）

實際儲存資料的檔案
可以包含多個資料表、視圖、索引等
例如：blog、users、products 等資料庫

2. 資料庫管理系統（DBMS - Database Management System）

管理和操作資料庫的軟體
提供資料的新增、查詢、更新、刪除功能（CRUD）
處理並發控制、資料備份、權限管理等
例如：PostgreSQL、MySQL、Oracle、MongoDB

3. 資料庫應用程式（Database Application）

使用者透過應用程式與資料庫互動
可以是網站、手機 App、桌面軟體等
透過 SQL 或 API 與 DBMS 溝通

系統架構圖：

使用者 
  ↓
應用程式（React、Django、Express）
  ↓
DBMS（PostgreSQL）
  ↓
資料庫（實際的資料檔案）

SQL 語言

SQL（Structured Query Language，結構化查詢語言）是用來與資料庫溝通的標準語言。

SQL 的主要分類：

1. DDL（Data Definition Language）- 資料定義語言

定義資料庫結構

CREATE TABLE users (...);  -- 建立資料表
ALTER TABLE users ...;     -- 修改資料表
DROP TABLE users;          -- 刪除資料表

2. DML（Data Manipulation Language）- 資料操作語言

操作資料內容

INSERT INTO users ...;     -- 新增資料
SELECT * FROM users;       -- 查詢資料
UPDATE users SET ...;      -- 更新資料
DELETE FROM users ...;     -- 刪除資料

3. DCL（Data Control Language）- 資料控制語言

管理權限和存取控制

GRANT SELECT ON users TO john;   -- 授予權限
REVOKE SELECT ON users FROM john; -- 撤銷權限

4. TCL（Transaction Control Language）- 交易控制語言

管理交易

BEGIN;           -- 開始交易
COMMIT;          -- 提交交易
ROLLBACK;        -- 回滾交易

SQL 的特點：

語法接近英文，相對易學
不區分大小寫（習慣上關鍵字用大寫）
標準化，大部分語法在不同資料庫通用

常用的資料庫訪問技術

應用程式需要透過特定的技術來連接和操作資料庫。

1. ODBC（Open Database Connectivity）

微軟開發的資料庫訪問標準
主要用於 Windows 平台
支援多種程式語言（C、C++、Python 等）
優點：跨資料庫，可以用相同的程式碼訪問不同資料庫

2. JDBC（Java Database Connectivity）

Java 專用的資料庫訪問 API
需要下載對應資料庫的 JDBC 驅動程式

// JDBC 連接範例
Connection conn = DriverManager.getConnection(
  "jdbc:postgresql://localhost:5432/mydb",
  "username", 
  "password"
);

3. 現代的連接方式

ORM（Object-Relational Mapping）框架

將資料表映射成程式物件
不需要寫 SQL，用程式語言操作
常見框架：
- Python: SQLAlchemy, Django ORM
- Node.js: Sequelize, Prisma, TypeORM
- Java: Hibernate

// Prisma ORM 範例
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    age: 25
  }
});

原生驅動程式

各語言提供的資料庫連接套件
直接執行 SQL 語句
常見套件：
- Python: psycopg2、asyncpg
- Node.js: pg、node-postgres
- Go: pgx

// Node.js pg 套件範例
const { Client } = require('pg');
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword'
});

await client.connect();
const result = await client.query('SELECT * FROM users');

4. REST API / GraphQL

現代 Web 應用常見的架構
前端透過 HTTP 請求呼叫後端 API
後端負責與資料庫溝通
優點：前後端分離、安全性高

選擇建議：

新手學習：先學 SQL，再學 ORM
小型專案：ORM 更方便快速
複雜查詢：原生 SQL 更靈活
大型專案：混合使用，視情況選擇

什麼是 PostgreSQL

PostgreSQL 是一個開源的關聯式資料庫管理系統（RDBMS），以穩定性、擴展性和標準遵循性聞名。

postgresql官網
 全球資料庫排行榜

為什麼選擇 PostgreSQL

特性	PostgreSQL	MySQL	MongoDB
類型	關聯式資料庫	關聯式資料庫	NoSQL 文件資料庫
ACID 支援	✅ 完整	✅ 完整	⚠️ 部分
JSON 支援	✅ JSONB（高效能）	⚠️ 基本	✅ 原生
複雜查詢	✅ 強大	✅ 良好	⚠️ 有限
開源授權	✅ MIT-style	⚠️ GPL	✅ SSPL
適合場景	企業應用、複雜查詢	Web 應用、簡單查詢	非結構化資料

PostgreSQL 的優勢：

支援複雜的資料型別（JSONB、Array、Geo 等）
強大的查詢優化器
完整的 ACID 保證
活躍的社群與豐富的擴充套件

快速安裝

使用 Docker（推薦）

最簡單的方式是使用 Docker：

# 拉取 PostgreSQL 映像檔
docker pull postgres:16

# 啟動 PostgreSQL 容器
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:16

參數說明：

--name: 容器名稱
-e POSTGRES_PASSWORD: 資料庫密碼
-e POSTGRES_USER: 資料庫使用者（預設為 postgres）
-e POSTGRES_DB: 預設建立的資料庫名稱
-p 5432:5432: 將容器的 5432 port 映射到本機

使用 Docker Compose

建立 docker-compose.yml：

version: '3.8'

services:
  postgres:
    image: postgres:16
    container_name: my-postgres
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mysecretpassword
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

啟動：

docker-compose up -d

連線測試

# 進入容器
docker exec -it my-postgres psql -U myuser -d mydb

# 看到以下提示代表成功
mydb=#

GUI 工具

pgAdmin（官方工具）

下載：https://www.pgadmin.org/download/

連線設定：

Host: localhost
Port: 5432
Database: mydb
Username: myuser
Password: mysecretpassword

DBeaver

下載：https://dbeaver.io/download/

支援多種資料庫，介面友善，完全免費。

SQL 基礎操作

建立資料庫

-- 建立新資料庫
CREATE DATABASE blog;

-- 切換資料庫
\c blog

建立資料表

-- 建立 users 資料表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  age INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

欄位說明：

SERIAL: 自動遞增的整數
PRIMARY KEY: 主鍵，唯一識別每一列
VARCHAR(n): 可變長度字串，最多 n 個字元
UNIQUE: 欄位值必須唯一
NOT NULL: 欄位不可為空
DEFAULT: 預設值

新增資料（INSERT）

-- 新增單筆資料
INSERT INTO users (email, name, age)
VALUES ('alice@example.com', 'Alice', 25);

-- 新增多筆資料
INSERT INTO users (email, name, age)
VALUES 
  ('bob@example.com', 'Bob', 30),
  ('charlie@example.com', 'Charlie', 28),
  ('david@example.com', 'David', 35);

查詢資料（SELECT）

-- 查詢所有資料
SELECT * FROM users;

-- 查詢特定欄位
SELECT name, email FROM users;

-- 條件查詢
SELECT * FROM users WHERE age > 25;

-- 多重條件
SELECT * FROM users 
WHERE age > 25 AND name LIKE 'A%';

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 限制筆數
SELECT * FROM users LIMIT 2;

-- 組合使用
SELECT name, age FROM users 
WHERE age >= 25 
ORDER BY age ASC 
LIMIT 3;

常用運算子：

=: 等於
>, <, >=, <=: 大於、小於
!= 或 <>: 不等於
LIKE: 模糊比對（% 代表任意字元）
IN: 在某個集合中
BETWEEN: 在某個範圍內

更新資料（UPDATE）

-- 更新特定資料
UPDATE users 
SET age = 26 
WHERE email = 'alice@example.com';

-- 更新多個欄位
UPDATE users 
SET name = 'Bob Smith', age = 31 
WHERE email = 'bob@example.com';

-- 批次更新
UPDATE users 
SET age = age + 1 
WHERE age < 30;

刪除資料（DELETE）

-- 刪除特定資料
DELETE FROM users 
WHERE email = 'david@example.com';

-- 刪除所有資料（危險）
DELETE FROM users;

-- 更安全的做法：加上條件
DELETE FROM users 
WHERE created_at < NOW() - INTERVAL '1 year';

常用資料型別

數字型別

CREATE TABLE products (
  id SERIAL,                    -- 自動遞增整數（1, 2, 3...）
  quantity INTEGER,             -- 整數（-2147483648 到 2147483647）
  price DECIMAL(10, 2),         -- 精確小數（總共10位，小數2位）
  views BIGINT,                 -- 大整數
  rating REAL                   -- 浮點數
);

選擇建議：

一般整數用 INTEGER
金額用 DECIMAL 或 NUMERIC（避免浮點數誤差）
大數字用 BIGINT

字串型別

CREATE TABLE posts (
  id SERIAL,
  title VARCHAR(200),           -- 可變長度，最多200字元
  content TEXT,                 -- 不限長度的文字
  slug CHAR(10)                 -- 固定長度，不足補空白
);

選擇建議：

已知長度上限用 VARCHAR(n)
長文章、描述用 TEXT
CHAR 較少用，除非真的需要固定長度

日期時間型別

CREATE TABLE events (
  id SERIAL,
  event_date DATE,              -- 日期（2024-01-15）
  event_time TIME,              -- 時間（14:30:00）
  created_at TIMESTAMP,         -- 日期+時間
  updated_at TIMESTAMPTZ        -- 日期+時間+時區（推薦）
);

-- 插入範例
INSERT INTO events (event_date, event_time, created_at, updated_at)
VALUES 
  ('2024-12-25', '18:00:00', NOW(), NOW());

選擇建議：

只需要日期用 DATE
需要完整時間用 TIMESTAMP
需要時區用 TIMESTAMPTZ（強烈推薦）

布林型別

CREATE TABLE users (
  id SERIAL,
  email VARCHAR(255),
  is_active BOOLEAN DEFAULT true,
  is_verified BOOLEAN DEFAULT false
);

-- 查詢範例
SELECT * FROM users WHERE is_active = true;
SELECT * FROM users WHERE NOT is_verified;

UUID 型別

-- 啟用 UUID 擴充
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id INTEGER,
  token VARCHAR(255)
);

-- 插入時自動產生 UUID
INSERT INTO sessions (user_id, token)
VALUES (1, 'abc123');

使用場景：

API Token
Session ID
分散式系統的唯一識別

JSONB 型別（PostgreSQL 特色）

CREATE TABLE user_settings (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  preferences JSONB
);

-- 插入 JSON 資料
INSERT INTO user_settings (user_id, preferences)
VALUES (1, '{"theme": "dark", "language": "zh-TW", "notifications": true}');

-- 查詢 JSON 欄位
SELECT preferences->>'theme' as theme FROM user_settings;

-- 條件查詢
SELECT * FROM user_settings 
WHERE preferences->>'language' = 'zh-TW';

-- 更新 JSON 欄位
UPDATE user_settings 
SET preferences = jsonb_set(preferences, '{theme}', '"light"')
WHERE user_id = 1;

JSONB vs JSON：

JSON: 儲存原始文字，快速寫入
JSONB: 二進位格式，查詢更快（推薦）

常用指令速查

-- 查看所有資料表
\dt
-- 查看資料表結構
\d users
-- 查看資料庫列表
\l
-- 切換資料庫
\c database_name
-- 離開
\q
-- 執行 SQL 檔案
\i /path/to/file.sql

-- 清空資料表
TRUNCATE TABLE users;
-- 刪除資料表
DROP TABLE users;
-- 刪除資料庫
DROP DATABASE blog;

小技巧

1. 查看執行時間

\timing on
SELECT * FROM users;
-- 顯示：Time: 0.234 ms

2. 格式化輸出

\x on  -- 垂直顯示
SELECT * FROM users WHERE id = 1;

\x off -- 恢復水平顯示

3. 匯出資料

# 匯出整個資料庫
docker exec my-postgres pg_dump -U myuser mydb > backup.sql

# 匯入資料
docker exec -i my-postgres psql -U myuser mydb < backup.sql

Day25 - supabase

系列文

欸欸!! 這是我的學習筆記共 26 篇

RSS系列文訂閱系列文

0 人訂閱

完整目錄

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

902 組

團體組數

37 組

累計文章數

17924 篇

完賽人數

223 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 17th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# linux windows server css react

IT邦幫忙

欸欸!! 這是我的學習筆記系列 第 26 篇

Day26 - possgreSQL(1)

前言

基礎知識

什麼是資料庫

資料表的概念

資料類型的概念

資料庫的系統構成

SQL 語言

常用的資料庫訪問技術

什麼是 PostgreSQL

為什麼選擇 PostgreSQL

快速安裝

使用 Docker（推薦）

使用 Docker Compose

連線測試

GUI 工具

pgAdmin（官方工具）

DBeaver

SQL 基礎操作

建立資料庫

建立資料表

新增資料（INSERT）

查詢資料（SELECT）

更新資料（UPDATE）

刪除資料（DELETE）

常用資料型別

數字型別

字串型別

日期時間型別

布林型別

UUID 型別

JSONB 型別（PostgreSQL 特色）

常用指令速查

小技巧

1. 查看執行時間

2. 格式化輸出

3. 匯出資料

尚未有邦友留言

標記使用者

欸欸!! 這是我的學習筆記系列第 26 篇