SQL VE POSTGRESQL: SORGUDAN OPTİMİZASYONA

-- Veritabanı oluştur
CREATE DATABASE blog_db;
 
-- Tablo oluştur
CREATE TABLE kullanicilar (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ad          VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    sifre_hash  TEXT NOT NULL,
    rol         VARCHAR(20) NOT NULL DEFAULT 'okuyucu'
                CHECK (rol IN ('admin', 'editor', 'okuyucu')),
    aktif       BOOLEAN NOT NULL DEFAULT true,
    olusturuldu TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    guncellendi TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE makaleler (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    baslik      VARCHAR(300) NOT NULL,
    slug        VARCHAR(350) UNIQUE NOT NULL,
    icerik      TEXT NOT NULL,
    yazar_id    UUID NOT NULL REFERENCES kullanicilar(id) ON DELETE CASCADE,
    yayinlandi  BOOLEAN NOT NULL DEFAULT false,
    gorunumler  INTEGER NOT NULL DEFAULT 0,
    olusturuldu TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE etiketler (
    id   SERIAL PRIMARY KEY,
    isim VARCHAR(50) UNIQUE NOT NULL
);
 
-- Çoka-çok ilişki (junction table)
CREATE TABLE makale_etiketler (
    makale_id UUID REFERENCES makaleler(id) ON DELETE CASCADE,
    etiket_id INTEGER REFERENCES etiketler(id) ON DELETE CASCADE,
    PRIMARY KEY (makale_id, etiket_id)
);

-- INSERT — veri ekle
INSERT INTO kullanicilar (ad, email, sifre_hash, rol)
VALUES
    ('Ali Yılmaz', 'ali@test.com', '$2b$12$...', 'admin'),
    ('Ayşe Kaya',  'ayse@test.com', '$2b$12$...', 'editor'),
    ('Mehmet Demir', 'mehmet@test.com', '$2b$12$...', 'okuyucu');
 
-- RETURNING ile insert edilen satırı geri al
INSERT INTO makaleler (baslik, slug, icerik, yazar_id)
VALUES ('Git ile Versiyon Kontrolü', 'git-versiyon-kontrolu', 'İçerik buraya...', 
        (SELECT id FROM kullanicilar WHERE email = 'ali@test.com'))
RETURNING id, slug, olusturuldu;
 
-- SELECT — veri sorgula
SELECT id, ad, email, rol FROM kullanicilar WHERE aktif = true;
 
-- UPDATE — veri güncelle
UPDATE kullanicilar 
SET rol = 'editor', guncellendi = NOW()
WHERE email = 'mehmet@test.com'
RETURNING id, rol;
 
-- DELETE — veri sil
DELETE FROM kullanicilar WHERE aktif = false AND olusturuldu < NOW() - INTERVAL '1 year';

-- INNER JOIN — her iki tabloda da eşleşen satırlar
SELECT 
    m.baslik,
    m.olusturuldu,
    k.ad AS yazar_adi,
    k.email AS yazar_email
FROM makaleler m
INNER JOIN kullanicilar k ON m.yazar_id = k.id
WHERE m.yayinlandi = true
ORDER BY m.olusturuldu DESC;
 
-- LEFT JOIN — sol tablonun tüm satırları + eşleşenler
SELECT 
    k.ad,
    k.email,
    COUNT(m.id) AS makale_sayisi
FROM kullanicilar k
LEFT JOIN makaleler m ON m.yazar_id = k.id
GROUP BY k.id, k.ad, k.email
ORDER BY makale_sayisi DESC;
 
-- Çoka-çok join
SELECT 
    m.baslik,
    STRING_AGG(e.isim, ', ' ORDER BY e.isim) AS etiketler
FROM makaleler m
LEFT JOIN makale_etiketler me ON me.makale_id = m.id
LEFT JOIN etiketler e ON e.id = me.etiket_id
WHERE m.yayinlandi = true
GROUP BY m.id, m.baslik;

-- Birden fazla koşul
SELECT * FROM makaleler
WHERE yayinlandi = true
  AND olusturuldu > '2025-01-01'
  AND yazar_id IN (
      SELECT id FROM kullanicilar WHERE rol = 'editor'
  );
 
-- LIKE ve ILIKE (büyük/küçük harf duyarsız)
SELECT * FROM makaleler 
WHERE baslik ILIKE '%python%'   -- 'Python', 'PYTHON', 'python' hepsini bulur
   OR baslik ILIKE '%javascript%';
 
-- BETWEEN
SELECT * FROM makaleler
WHERE olusturuldu BETWEEN '2025-01-01' AND '2025-12-31';
 
-- NULL kontrolü
SELECT * FROM kullanicilar WHERE profil_resmi IS NULL;
SELECT * FROM kullanicilar WHERE profil_resmi IS NOT NULL;
 
-- EXISTS ile alt sorgu
SELECT * FROM kullanicilar k
WHERE EXISTS (
    SELECT 1 FROM makaleler m 
    WHERE m.yazar_id = k.id AND m.yayinlandi = true
);

-- Temel aggregate'ler
SELECT
    COUNT(*)                    AS toplam_kullanici,
    COUNT(*) FILTER (WHERE aktif = true) AS aktif_kullanici,
    COUNT(DISTINCT rol)         AS rol_cesidi
FROM kullanicilar;
 
-- GROUP BY ile gruplama
SELECT 
    rol,
    COUNT(*) AS sayi,
    MIN(olusturuldu) AS ilk_kayit,
    MAX(olusturuldu) AS son_kayit
FROM kullanicilar
GROUP BY rol
HAVING COUNT(*) > 1   -- Gruplama sonrası filtre
ORDER BY sayi DESC;
 
-- Makale istatistikleri
SELECT 
    yazar_id,
    k.ad AS yazar,
    COUNT(m.id)        AS toplam_makale,
    SUM(m.gorunumler)  AS toplam_gorunum,
    ROUND(AVG(m.gorunumler), 0) AS ort_gorunum,
    MAX(m.gorunumler)  AS en_populer
FROM makaleler m
JOIN kullanicilar k ON k.id = m.yazar_id
WHERE m.yayinlandi = true
GROUP BY m.yazar_id, k.ad
ORDER BY toplam_gorunum DESC
LIMIT 10;

-- RANK — sıralama (GROUP BY yapmadan)
SELECT 
    baslik,
    gorunumler,
    RANK() OVER (ORDER BY gorunumler DESC) AS sira,
    yazar_id,
    RANK() OVER (PARTITION BY yazar_id ORDER BY gorunumler DESC) AS yazar_sirasi
FROM makaleler
WHERE yayinlandi = true;
 
-- LAG/LEAD — önceki/sonraki satır
SELECT 
    baslik,
    gorunumler,
    olusturuldu,
    LAG(gorunumler)  OVER (ORDER BY olusturuldu) AS onceki_gorunum,
    gorunumler - LAG(gorunumler) OVER (ORDER BY olusturuldu) AS degisim
FROM makaleler
WHERE yazar_id = '...'
ORDER BY olusturuldu;
 
-- Kümülatif toplam
SELECT 
    DATE_TRUNC('month', olusturuldu) AS ay,
    COUNT(*) AS aylik_makale,
    SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', olusturuldu)) AS kumulatif
FROM makaleler
WHERE yayinlandi = true
GROUP BY DATE_TRUNC('month', olusturuldu)
ORDER BY ay;

-- Sık sorgulanan sütunlara index ekle
CREATE INDEX idx_makaleler_yazar_id ON makaleler(yazar_id);
CREATE INDEX idx_makaleler_yayinlandi ON makaleler(yayinlandi);
CREATE INDEX idx_makaleler_olusturuldu ON makaleler(olusturuldu DESC);
 
-- Composite index
CREATE INDEX idx_makaleler_yayinlandi_tarih 
ON makaleler(yayinlandi, olusturuldu DESC);
 
-- Partial index — sadece yayınlananlar için
CREATE INDEX idx_makaleler_aktif 
ON makaleler(olusturuldu DESC) 
WHERE yayinlandi = true;
 
-- Full text search index
CREATE INDEX idx_makaleler_fts ON makaleler
USING GIN (to_tsvector('turkish', baslik || ' ' || icerik));
 
-- Sorgu planını incele
EXPLAIN ANALYZE
SELECT * FROM makaleler
WHERE yayinlandi = true
ORDER BY olusturuldu DESC
LIMIT 20;

-- Transaction — ya hepsi olur ya hiçbiri
BEGIN;
 
UPDATE kullanicilar SET aktif = false WHERE id = 'abc-123';
INSERT INTO audit_loglari (aksiyon, etkilenen_id, yapan_id)
VALUES ('hesap_deaktive', 'abc-123', 'admin-001');
 
-- Her şey yolundaysa onayla
COMMIT;
 
-- Hata varsa geri al
-- ROLLBACK;
 
-- SAVEPOINT ile kısmi geri alma
BEGIN;
INSERT INTO siparisler VALUES (...);
SAVEPOINT siparis_sonrasi;
INSERT INTO odeme_kayitlari VALUES (...);
-- Ödeme başarısız
ROLLBACK TO SAVEPOINT siparis_sonrasi;
-- Siparis kaydı kaldı, ödeme geri alındı
COMMIT;