database software คือ

Database Software

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

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 ทุกวัน

Database Software คืออะไร

ซอฟต์แวร์จัดเก็บจัดการข้อมูล RDBMS MySQL PostgreSQL NoSQL MongoDB Redis NewSQL CockroachDB SQL Query Scale Consistency Availability

สรุป

Database Software RDBMS NoSQL NewSQL MySQL PostgreSQL MongoDB Redis SQL CRUD JOIN Transaction Index Backup Replication Connection Pooling Production