MySQL untuk Pemula: Panduan Lengkap

# ====== METODE 1: XAMPP (Paling Mudah untuk Pemula) ======
# Download XAMPP dari https://www.apachefriends.org/
# Jalankan installer, centang MySQL
# Start MySQL dari XAMPP Control Panel
# Buka http://localhost/phpmyadmin

# ====== METODE 2: Ubuntu/Debian ======
sudo apt update
sudo apt install mysql-server mysql-client

# Jalankan service
sudo systemctl start mysql
sudo systemctl enable mysql

# Konfigurasi keamanan awal
sudo mysql_secure_installation
# - Set root password: Ya
# - Remove anonymous users: Ya
# - Disallow root login remotely: Ya
# - Remove test database: Ya
# - Reload privilege tables: Ya

# Masuk ke MySQL shell
mysql -u root -p

# ====== METODE 3: macOS (Homebrew) ======
brew install mysql
brew services start mysql
mysql -u root -p

# ====== METODE 4: Docker ======
docker run --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=rahasia123 \
  -e MYSQL_DATABASE=belajar_db \
  -e MYSQL_USER=developer \
  -e MYSQL_PASSWORD=devpass123 \
  -p 3306:3306 \
  -d mysql:8.0

# Masuk ke container
docker exec -it mysql-dev mysql -u developer -p belajar_db

-- Buat database baru
CREATE DATABASE belajar_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Pilih database
USE belajar_db;

-- Buat user baru
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'rahasia123';

-- Berikan hak akses
GRANT ALL PRIVILEGES ON belajar_db.* TO 'developer'@'localhost';
FLUSH PRIVILEGES;

-- Cek database yang ada
SHOW DATABASES;

-- Cek tabel dalam database
SHOW TABLES;

-- Cek versi MySQL
SELECT VERSION();

-- Keluar dari MySQL
EXIT;

-- Membuat tabel produk
CREATE TABLE produk (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nama_produk VARCHAR(200) NOT NULL,
    deskripsi   TEXT,
    harga       DECIMAL(12,2) NOT NULL DEFAULT 0,
    stok        INT NOT NULL DEFAULT 0,
    kategori    VARCHAR(100),
    aktif       BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert satu baris
INSERT INTO produk (nama_produk, harga, stok, kategori)
VALUES ('Laptop ASUS ROG', 15000000, 10, 'Elektronik');

-- Insert multiple baris
INSERT INTO produk (nama_produk, harga, stok, kategori) VALUES
    ('Keyboard Mekanik', 850000, 25, 'Aksesoris'),
    ('Mouse Gaming', 450000, 30, 'Aksesoris'),
    ('Monitor 27 inch', 3500000, 8, 'Elektronik'),
    ('Kaos Polos', 85000, 100, 'Pakaian'),
    ('Headset Gaming', 650000, 15, 'Aksesoris');

-- Insert dari SELECT (salin data)
CREATE TABLE produk_elektronik AS
SELECT * FROM produk WHERE kategori = 'Elektronik';

-- Select semua data
SELECT * FROM produk;

-- Select kolom tertentu
SELECT nama_produk, harga, stok FROM produk;

-- WHERE — filter data
SELECT nama_produk, harga FROM produk
WHERE harga > 1000000 AND aktif = TRUE;

-- LIKE — pencarian pattern
SELECT nama_produk FROM produk
WHERE nama_produk LIKE '%Gaming%';     -- Mengandung "Gaming"
WHERE nama_produk LIKE 'Mouse%';       -- Dimulai dengan "Mouse"

-- IN — cek multiple values
SELECT nama_produk, harga FROM produk
WHERE kategori IN ('Elektronik', 'Aksesoris');

-- BETWEEN — range nilai
SELECT nama_produk, harga FROM produk
WHERE harga BETWEEN 100000 AND 1000000;

-- ORDER BY — urutkan
SELECT nama_produk, harga, stok FROM produk
ORDER BY harga DESC, nama_produk ASC;

-- LIMIT — batasi hasil
SELECT nama_produk, harga FROM produk
ORDER BY harga DESC
LIMIT 5;  -- 5 produk termahal

-- GROUP BY & Aggregate Functions
SELECT
    kategori,
    COUNT(*) AS jumlah_produk,
    AVG(harga) AS rata_rata_harga,
    MIN(harga) AS harga_termurah,
    MAX(harga) AS harga_termahal,
    SUM(stok) AS total_stok
FROM produk
WHERE aktif = TRUE
GROUP BY kategori
HAVING COUNT(*) >= 2
ORDER BY rata_rata_harga DESC;

-- Alias kolom
SELECT
    nama_produk AS 'Nama Produk',
    CONCAT('Rp ', FORMAT(harga, 0)) AS 'Harga',
    stok AS 'Stok Tersisa'
FROM produk;

-- Fungsi string
SELECT
    UPPER(nama_produk) AS nama_upper,
    LENGTH(nama_produk) AS panjang_nama,
    LEFT(nama_produk, 10) AS singkat,
    REPLACE(nama_produk, 'Gaming', 'Pro') AS nama_baru
FROM produk;

-- Fungsi tanggal
SELECT
    nama_produk,
    created_at,
    DATE(created_at) AS tanggal,
    DATEDIFF(NOW(), created_at) AS hari_yang_lalu,
    DATE_FORMAT(created_at, '%d %M %Y') AS tanggal_format
FROM produk;

-- Update harga satu produk
UPDATE produk
SET harga = 14500000
WHERE id = 1;

-- Update multiple kolom
UPDATE produk
SET harga = 800000, stok = 30
WHERE nama_produk = 'Keyboard Mekanik';

-- Diskon 10% untuk semua aksesoris
UPDATE produk
SET harga = harga * 0.90
WHERE kategori = 'Aksesoris';

-- Nonaktifkan produk yang stoknya habis
UPDATE produk
SET aktif = FALSE
WHERE stok = 0 AND aktif = TRUE;

-- Hapus satu produk
DELETE FROM produk
WHERE id = 7;

-- Hapus semua produk nonaktif
DELETE FROM produk
WHERE aktif = FALSE;

-- ⚠️ HATI-HATI: DELETE tanpa WHERE!
-- Selalu cek dengan SELECT dulu sebelum DELETE
-- SELECT * FROM produk WHERE ...;  -- Cek dulu
-- DELETE FROM produk WHERE ...;    -- Baru hapus

-- ============================================
-- ONE-TO-MANY: Pelanggan → Pesanan
-- ============================================

CREATE TABLE pelanggan (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    nama    VARCHAR(100) NOT NULL,
    email   VARCHAR(150) UNIQUE NOT NULL,
    telepon VARCHAR(20),
    alamat  TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE pesanan (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    pelanggan_id   INT NOT NULL,
    tanggal        DATE NOT NULL,
    total          DECIMAL(12,2) NOT NULL DEFAULT 0,
    status         ENUM('pending','diproses','dikirim','selesai','batal')
                   DEFAULT 'pending',
    catatan        TEXT,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pelanggan_id) REFERENCES pelanggan(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE pesanan_item (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    pesanan_id  INT NOT NULL,
    produk_id   INT NOT NULL,
    jumlah      INT NOT NULL DEFAULT 1,
    harga_satuan DECIMAL(12,2) NOT NULL,
    subtotal    DECIMAL(12,2) GENERATED ALWAYS AS (jumlah * harga_satuan) STORED,
    FOREIGN KEY (pesanan_id) REFERENCES pesanan(id) ON DELETE CASCADE,
    FOREIGN KEY (produk_id) REFERENCES produk(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Insert data contoh
INSERT INTO pelanggan (nama, email, telepon) VALUES
    ('Budi Santoso', 'budi@email.com', '08123456789'),
    ('Sari Dewi', 'sari@email.com', '08567890123'),
    ('Andi Pratama', 'andi@email.com', '08987654321');

INSERT INTO pesanan (pelanggan_id, tanggal, total, status) VALUES
    (1, '2026-06-20', 15850000, 'selesai'),
    (2, '2026-06-22', 450000, 'dikirim'),
    (1, '2026-06-24', 900000, 'diproses');

INSERT INTO pesanan_item (pesanan_id, produk_id, jumlah, harga_satuan) VALUES
    (1, 1, 1, 15000000),
    (1, 2, 1, 850000),
    (2, 3, 1, 450000),
    (3, 6, 1, 650000),
    (3, 5, 1, 85000);

-- ============================================
-- MANY-TO-MANY: Siswa ↔ Mata Kuliah
-- ============================================

CREATE TABLE siswa (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    nim   VARCHAR(20) UNIQUE NOT NULL,
    nama  VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE mata_kuliah (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    kode   VARCHAR(10) UNIQUE NOT NULL,
    nama   VARCHAR(100) NOT NULL,
    sks    INT NOT NULL
) ENGINE=InnoDB;

-- Junction table (tabel penghubung)
CREATE TABLE siswa_mata_kuliah (
    siswa_id      INT NOT NULL,
    mata_kuliah_id INT NOT NULL,
    semester      VARCHAR(10),
    nilai         DECIMAL(5,2),
    PRIMARY KEY (siswa_id, mata_kuliah_id),
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (mata_kuliah_id) REFERENCES mata_kuliah(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- INNER JOIN: Pesanan dengan nama pelanggan
SELECT
    p.id AS no_pesanan,
    pl.nama AS pelanggan,
    p.tanggal,
    p.total,
    p.status
FROM pesanan p
INNER JOIN pelanggan pl ON p.pelanggan_id = pl.id
ORDER BY p.tanggal DESC;

-- Detail pesanan dengan item
SELECT
    p.id AS no_pesanan,
    pl.nama AS pelanggan,
    pr.nama_produk,
    pi.jumlah,
    CONCAT('Rp ', FORMAT(pi.harga_satuan, 0)) AS harga,
    CONCAT('Rp ', FORMAT(pi.subtotal, 0)) AS subtotal
FROM pesanan p
JOIN pelanggan pl ON p.pelanggan_id = pl.id
JOIN pesanan_item pi ON p.id = pi.pesanan_id
JOIN produk pr ON pi.produk_id = pr.id
WHERE p.id = 1;

-- LEFT JOIN: Semua pelanggan termasuk yang belum pernah pesan
SELECT
    pl.nama,
    COUNT(ps.id) AS jumlah_pesanan,
    COALESCE(SUM(ps.total), 0) AS total_belanja
FROM pelanggan pl
LEFT JOIN pesanan ps ON pl.id = ps.pelanggan_id
GROUP BY pl.id, pl.nama
ORDER BY total_belanja DESC;

-- JOIN Many-to-Many: Nilai siswa per mata kuliah
SELECT
    s.nim,
    s.nama AS nama_siswa,
    mk.nama AS mata_kuliah,
    mk.sks,
    sm.nilai
FROM siswa s
JOIN siswa_mata_kuliah sm ON s.id = sm.siswa_id
JOIN mata_kuliah mk ON sm.mata_kuliah_id = mk.id
ORDER BY s.nama, mk.nama;

# ====== BACKUP ======

# Backup satu database
mysqldump -u root -p belajar_db > backup_belajar_db.sql

# Backup dengan timestamp
mysqldump -u root -p belajar_db > "backup_$(date +%Y%m%d_%H%M%S).sql"

# Backup satu tabel saja
mysqldump -u root -p belajar_db produk pelanggan > backup_tabel.sql

# Backup semua database
mysqldump -u root -p --all-databases > backup_semua.sql

# Backup dengan kompresi (hemat ruang)
mysqldump -u root -p belajar_db | gzip > backup_belajar_db.sql.gz

# Backup hanya struktur (tanpa data)
mysqldump -u root -p --no-data belajar_db > backup_struktur.sql

# Backup hanya data (tanpa struktur)
mysqldump -u root -p --no-create-info belajar_db > backup_data.sql

# Backup dari remote server
mysqldump -h db.example.com -u root -p belajar_db > backup_remote.sql

# ====== RESTORE ======

# Restore database
mysql -u root -p belajar_db < backup_belajar_db.sql

# Restore ke database baru
mysql -u root -p -e "CREATE DATABASE belajar_db_restore;"
mysql -u root -p belajar_db_restore < backup_belajar_db.sql

# Restore dari file terkompresi
gunzip < backup_belajar_db.sql.gz | mysql -u root -p belajar_db

# ====== OTOMATASI BACKUP (Cron Job) ======
# Tambahkan ke crontab: crontab -e

# Backup setiap hari jam 2 pagi
0 2 * * * mysqldump -u root -p*** belajar_db | gzip > /backups/db_$(date +\%Y\%m\%d).sql.gz

# Hapus backup lebih dari 30 hari
0 3 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

-- ============================================
-- 1. SELALU gunakan PRIMARY KEY
-- ============================================
-- ❌ Buruk
CREATE TABLE produk (nama VARCHAR(100), harga DECIMAL(10,2));

-- ✅ Baik
CREATE TABLE produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    harga DECIMAL(10,2) NOT NULL DEFAULT 0
);

-- ============================================
-- 2. Gunakan INDEX untuk kolom yang sering dicari
-- ============================================
CREATE INDEX idx_produk_kategori ON produk(kategori);
CREATE INDEX idx_pesanan_tanggal ON pesanan(tanggal);
CREATE INDEX idx_pesanan_status ON pesanan(status);

-- ============================================
-- 3. Gunakan ENUM untuk nilai yang terbatas
-- ============================================
-- ❌ Buruk: VARCHAR yang bisa diisi apa saja
status VARCHAR(20)

-- ✅ Baik: ENUM membatasi pilihan
status ENUM('pending','diproses','dikirim','selesai','batal')

-- ============================================
-- 4. Gunakan TRANSACTIONS untuk operasi kompleks
-- ============================================
START TRANSACTION;

INSERT INTO pesanan (pelanggan_id, tanggal, total)
VALUES (1, CURDATE(), 100000);

SET @pesanan_id = LAST_INSERT_ID();

INSERT INTO pesanan_item (pesanan_id, produk_id, jumlah, harga_satuan)
VALUES (@pesanan_id, 1, 1, 100000);

UPDATE produk SET stok = stok - 1 WHERE id = 1;

COMMIT;  -- Atau ROLLBACK jika ada error

-- ============================================
-- 5. Cek performa dengan EXPLAIN
-- ============================================
EXPLAIN SELECT * FROM pesanan WHERE pelanggan_id = 1;
EXPLAIN SELECT * FROM produk WHERE harga BETWEEN 100000 AND 500000;

-- ============================================
-- 6. Hindari SELECT * di production
-- ============================================
-- ❌ Buruk: mengambil semua kolom
SELECT * FROM produk;

-- ✅ Baik: ambil hanya kolom yang dibutuhkan
SELECT nama_produk, harga FROM produk WHERE kategori = 'Elektronik';