Database Software คือ

Database Software

Database Software RDBMS NoSQL NewSQL SQL MySQL PostgreSQL MongoDB Redis Cassandra Data Modeling Schema ACID Transaction Scale Production

Type	Examples	Data Model	Scale	ACID	เหมาะกับ
RDBMS	MySQL PostgreSQL	Table Row Column	Vertical	Full	Structured Data
Document	MongoDB CouchDB	JSON Document	Horizontal	Partial	Semi-structured
Key-Value	Redis DynamoDB	Key → Value	Horizontal	Partial	Cache Session
Column	Cassandra HBase	Column Family	Horizontal	Tunable	Time Series IoT
Graph	Neo4j ArangoDB	Node Edge	Limited	Full	Relationships
NewSQL	CockroachDB TiDB	Table (Distributed)	Horizontal	Full	Scale + ACID

SQL Fundamentals

=== SQL Fundamentals ===

CREATE TABLE

CREATE TABLE users (

id SERIAL PRIMARY KEY,

username VARCHAR(50) UNIQUE NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

password_hash VARCHAR(255) NOT NULL,

role VARCHAR(20) DEFAULT 'user',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

user_id INT REFERENCES users(id),

total DECIMAL(10,2) NOT NULL,

status VARCHAR(20) DEFAULT 'pending',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE order_items (

id SERIAL PRIMARY KEY,

order_id INT REFERENCES orders(id),

product_name VARCHAR(100) NOT NULL,

quantity INT NOT NULL,

price DECIMAL(10,2) NOT NULL

);

-- INDEX

CREATE INDEX idx_orders_user_id ON orders(user_id);

CREATE INDEX idx_orders_status ON orders(status);

CREATE INDEX idx_orders_created ON orders(created_at);

CRUD Operations

-- INSERT

INSERT INTO users (username, email, password_hash)

VALUES ('john', 'john@example.com', '$2b$12$...');

-- SELECT with JOIN

SELECT u.username, o.id as order_id, o.total, o.status,

COUNT(oi.id) as item_count

FROM users u

JOIN orders o ON u.id = o.user_id

JOIN order_items oi ON o.id = oi.order_id

WHERE o.status = 'completed'

GROUP BY u.username, o.id, o.total, o.status

ORDER BY o.created_at DESC

LIMIT 10;

-- UPDATE

UPDATE orders SET status = 'shipped', updated_at = NOW()

WHERE id = 123 AND status = 'completed';

-- DELETE

DELETE FROM order_items WHERE order_id = 123;

Transaction

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;

from dataclasses import dataclass

@dataclass

class DBComparison:

database: str

db_type: str

license_type: str

max_size: str

best_for: str

popularity: str

databases = [

DBComparison("MySQL", "RDBMS", "Open Source (GPL)", "Unlimited", "Web Apps WordPress", "Very High"),

DBComparison("PostgreSQL", "RDBMS", "Open Source (BSD)", "Unlimited", "Complex Queries GIS", "Very High"),

DBComparison("MongoDB", "Document", "Open Source (SSPL)", "Unlimited", "Flexible Schema", "High"),

DBComparison("Redis", "Key-Value", "Open Source (BSD)", "RAM limited", "Cache Real-time", "High"),

DBComparison("SQLite", "RDBMS", "Public Domain", "281 TB", "Embedded Mobile", "Very High"),

DBComparison("Cassandra", "Column", "Open Source (Apache)", "Unlimited", "Write-heavy IoT", "Medium"),

]

print("=== Database Comparison ===")

for d in databases:

print(f" [{d.database}] {d.db_type} | License: {d.license_type}")

print(f" Best For: {d.best_for} | Popularity: {d.popularity}")

NoSQL Examples

=== NoSQL Database Examples ===

MongoDB — Document Database

Insert

db.users.insertOne({

username: "john",

email: "john@example.com",

profile: { age: 30, city: "Bangkok" },

orders: []

});

Query

db.users.find({ "profile.city": "Bangkok" }).sort({ username: 1 });

Aggregation

db.orders.aggregate([

{ $match: { status: "completed" } },

{ $group: { _id: "$user_id", total: { $sum: "$amount" }, count: { $sum: 1 } } },

{ $sort: { total: -1 } },

{ $limit: 10 }

]);

Redis — Key-Value / Cache

SET user:1:session "abc123" EX 3600

GET user:1:session

HSET user:1 name "John" email "john@example.com"

HGETALL user:1

ZADD leaderboard 100 "player1" 95 "player2" 87 "player3"

ZREVRANGE leaderboard 0 9 WITHSCORES

LPUSH queue:orders '{"id":1,"product":"laptop"}'

BRPOP queue:orders 0

Python — SQLAlchemy ORM

from sqlalchemy import create_engine, Column, Integer, String, DateTime

from sqlalchemy.orm import declarative_base, Session

engine = create_engine("postgresql://user:pass@localhost/mydb")

Base = declarative_base()

class User(Base):

__tablename__ = "users"

id = Column(Integer, primary_key=True)

username = Column(String(50), unique=True)

email = Column(String(100), unique=True)

with Session(engine) as session:

user = User(username="john", email="john@example.com")

session.add(user)

session.commit()

users = session.query(User).filter(User.username.like("j%")).all()

@dataclass

class UseCase:

use_case: str

recommended_db: str

reason: str

alternatives: str

cases = [

UseCase("E-commerce", "PostgreSQL", "ACID Transaction Complex Queries", "MySQL"),

UseCase("Content Management", "MongoDB", "Flexible Schema nested documents", "PostgreSQL JSONB"),

UseCase("Caching/Session", "Redis", "In-memory ultra-fast TTL", "Memcached"),

UseCase("IoT Time Series", "Cassandra/TimescaleDB", "Write-heavy time partition", "InfluxDB"),

UseCase("Social Network", "Neo4j", "Relationship traversal friends graph", "ArangoDB"),

UseCase("Mobile App", "SQLite/Realm", "Embedded no server needed", "Hive"),

UseCase("Analytics", "ClickHouse", "Columnar fast aggregation", "BigQuery Redshift"),

]

print("\n=== Database by Use Case ===")

for c in cases:

print(f" [{c.use_case}] → {c.recommended_db}")

print(f" Reason: {c.reason} | Alt: {c.alternatives}")

Production Operations

# === Database Production Checklist ===

@dataclass
class ProdCheck:
    category: str
    item: str
    command: str
    frequency: str
    status: str

checklist = [
    ProdCheck("Backup", "Automated daily backup", "pg_dump -Fc mydb > backup.dump", "Daily 02:00", "Done"),
    ProdCheck("Backup", "Test restore monthly", "pg_restore -d test_db backup.dump", "Monthly", "Done"),
    ProdCheck("Performance", "Slow query log", "log_min_duration_statement = 200", "Always on", "Done"),
    ProdCheck("Performance", "EXPLAIN ANALYZE", "EXPLAIN ANALYZE SELECT ...", "On slow queries", "Done"),
    ProdCheck("Performance", "Index analysis", "SELECT * FROM pg_stat_user_indexes", "Weekly", "Done"),
    ProdCheck("Security", "Encrypted connections", "ssl = on in postgresql.conf", "Always", "Done"),
    ProdCheck("Security", "Role-based access", "GRANT SELECT ON table TO role", "On change", "Done"),
    ProdCheck("Monitoring", "Connection count", "SELECT count(*) FROM pg_stat_activity", "Real-time", "Done"),
    ProdCheck("Monitoring", "Disk usage", "SELECT pg_database_size('mydb')", "Daily", "Done"),
    ProdCheck("HA", "Replication setup", "Streaming replication primary/standby", "Always", "Done"),
]

print("Production Checklist:")
done = sum(1 for c in checklist if c.status == "Done")
for c in checklist:
    print(f"  [{c.status}] [{c.category}] {c.item}")
    print(f"    Command: {c.command} | Freq: {c.frequency}")
print(f"\n  Progress: {done}/{len(checklist)}")

scaling = {
    "Read Replicas": "เพิ่ม Replica สำหรับ Read Query",
    "Connection Pooling": "PgBouncer จัดการ Connection",
    "Partitioning": "แบ่ง Table ตาม Date Range",
    "Sharding": "แบ่งข้อมูลไปหลาย Server",
    "Caching Layer": "Redis Cache ลด Query Load",
    "Query Optimization": "Index EXPLAIN ANALYZE Vacuum",
}

print(f"\n\nScaling Strategies:")
for k, v in scaling.items():
    print(f"  [{k}]: {v}")

เคล็ดลับ

Start Simple: เริ่มจาก PostgreSQL หรือ MySQL ก่อน
Index: สร้าง Index สำหรับ Column ที่ WHERE และ JOIN บ่อย
Backup: Backup ทุกวัน Test Restore ทุกเดือน
Pool: ใช้ Connection Pooling เสมอใน Production
Monitor: ดู Slow Query Log และ Connection Count ทุกวัน