İlişkiler ve JOIN

Şimdiye kadar her şeyi tek bir tabloda tuttun. Gerçek uygulamalarda veriler birbirine bağlıdır: her maçın bir oyuncusu, her takımın birkaç şampiyonu vardır. Bu derste tabloları birbirine bağlamayı, bağlı tabloları tek bir sorguda yeniden birleştirmeyi ve bunu yaparken veriyi gereksiz yere tekrar etmeyen bir tasarım kurmayı öğreneceksin. Dersin sonunda beş tablodan oluşan küçük bir lig veritabanın ve onunla istatistik çıkaran sorguların olacak.

#Başlamadan: aynı adlı eski tablolar

Bu derste beş yeni tablo kuracaksın: oyuncular, maclar, sampiyonlar, takimlar ve takim_sampiyonlari. Önceki derslerden bu adlarla bir tablo kaldıysa (Ders 34'teki maç tablosu gibi) yeni CREATE TABLE komutun Ders 33 ve 35'ten tanıdığın relation "maclar" already exists hatasını verir. O tablolardaki deneme verisine artık ihtiyacın yok. SQL Editor'de şu satırları çalıştır; tablo varsa siler, yoksa yalnız bir bilgi notu (NOTICE) yazar:

DROP TABLE IF EXISTS takim_sampiyonlari;DROP TABLE IF EXISTS maclar;DROP TABLE IF EXISTS takimlar;DROP TABLE IF EXISTS sampiyonlar;DROP TABLE IF EXISTS oyuncular;DROP TABLE IF EXISTS mac_kayitlari;DROP TABLE IF EXISTS mac_gecmisi;

Dersi bir gün baştan yapmak istersen de aynı satırları çalıştırman yeter. Satırların sırası rastgele değil; nedenini dersin sonunda, sık hatalar bölümünde göreceksin. Eski bir tabloyu saklamak istiyorsan silmek yerine Ders 35'teki gibi adını değiştir: ALTER TABLE maclar RENAME TO maclar_ders34;

Bu dersin SQL'i uzun. Ders 34 ve 35'teki gibi, kalıcı tabloları kuran ve veri ekleyen komutları C:\kod\veritabani klasöründe yeni bir dosyada biriktir: ders36.sql. Komutu VS Code'da dosyaya yaz, seçip kopyala, SQL Editor'e yapıştır ve Run'a bas. Dersin sonunda dosyayı Git'e kaydedeceksin. İlk bölümdeki mac_kayitlari yalnız bir deneme; onu doğrudan SQL Editor'e yazman yeter, dosya oyuncular tablosuyla başlayacak.

#Tek tablonun üç derdi

Ders 34'te maç geçmişini tek bir tabloda tuttun; oyuncunun adı her maç satırında yeniden yazılıyordu. Bunun neden sorun olduğunu küçük bir tabloyla görelim. Bu kez oyuncunun bölgesini de tutuyoruz:

CREATE TABLE mac_kayitlari (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    oyuncu text NOT NULL,    bolge text NOT NULL,    sampiyon text NOT NULL,    sonuc text NOT NULL); INSERT INTO mac_kayitlari (oyuncu, bolge, sampiyon, sonuc) VALUES    ('Deniz', 'TR', 'Ahri', 'zafer'),    ('Deniz', 'TR', 'Lux', 'yenilgi'),    ('Ece', 'TR', 'Jinx', 'zafer'),    ('Deniz', 'TR', 'Ahri', 'zafer');

SELECT * FROM mac_kayitlari ORDER BY id;

Deniz'in adı ve bölgesi üç satırda tekrar ediyor. Şimdi Deniz hesabını EUW sunucusuna taşıdı. Kaydı düzelten arkadaşın yalnız son maçı güncelledi:

UPDATE mac_kayitlari SET bolge = 'EUW' WHERE id = 4;

Deniz hangi bölgede? Sor bakalım:

SELECT oyuncu, bolge, COUNT(*) AS macFROM mac_kayitlariWHERE oyuncu = 'Deniz'GROUP BY oyuncu, bolgeORDER BY bolge;

Veritabanı aynı soruya iki cevap veriyor. Doğrusu WHERE oyuncu = 'Deniz' ile üç satırı birden güncellemekti, ama bunu her seferinde hatırlamak zorundasın ve tek bir unutkanlık veriyi çelişkili bırakır. Sorun güncellemenin kendisi değil: aynı bilginin birden çok yerde durması.

Tek tablonun iki derdi daha var. Birincisi ekleme: hiç maç oynamamış yeni oyuncu Kaan'ı kaydedemezsin, çünkü bu tablo bir maç tablosu ve her satır bir maç ister:

INSERT INTO mac_kayitlari (oyuncu, bolge) VALUES ('Kaan', 'EUW');

ERROR:  null value in column "sampiyon" of relation "mac_kayitlari" violates not-null constraintDETAIL:  Failing row contains (5, Kaan, EUW, null, null).

NOT NULL kısıtlarını kaldırsan da kurtulamazsın; bu kez maç gibi görünen ama maç olmayan, içi boş bir satırın olur. İkincisi silme: Ece'nin tek maçını silersen Ece'nin TR'de oynadığı bilgisi de onunla birlikte gider. Oyuncu bilgisi maç bilgisine yapışık olduğu için biri gidince öbürü de gidiyor.

#Veriyi ikiye bölmek: yabancı anahtar

Çözüm, her bilgiyi tek bir yerde tutmak. Oyuncunun adı ve bölgesi oyuncular tablosunda bir kez durur. maclar tablosu oyuncunun adını değil, oyuncular tablosundaki id'sini yazar. Deniz taşınırsa tek bir satırı güncellersin; maçlar ona id ile bağlı olduğu için hepsi doğru bölgeyi gösterir.

İki tabloyu ders36.sql dosyana yaz, kopyala ve SQL Editor'de çalıştır:

1CREATE TABLE oyuncular (2    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,3    ad text NOT NULL UNIQUE,4    bolge text NOT NULL5);6 7CREATE TABLE maclar (8    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,9    oyuncu_id integer NOT NULL REFERENCES oyuncular (id),10    sampiyon text NOT NULL,11    sonuc text NOT NULL CHECK (sonuc IN ('zafer', 'yenilgi')),12    kill integer NOT NULL,13    olum integer NOT NULL,14    asist integer NOT NULL15);

Yeni olan tek şey 9. satır. Parça parça okuyalım:

• oyuncu_id integer: maçı oynayan oyuncunun id'sini tutacak sütun. Tipi, göstereceği oyuncular.id ile aynı: integer.
• NOT NULL: her maçın bir oyuncusu olmak zorunda.
• REFERENCES oyuncular (id): bu sütuna yazılan her değer, oyuncular tablosunun id sütununda gerçekten bulunmalı.

İki CREATE TABLE'ın sırası önemli. maclar tablosu oyuncular'ı gösteriyor; gösterilen tablonun önce var olması gerekir. Sırayı ters çevirirsen Postgres relation "oyuncular" does not exist der.

Şimdi oyuncuları ekle. RETURNING (Ders 35) Postgres'in verdiği numaraları gösterecek:

INSERT INTO oyuncular (ad, bolge) VALUES    ('Deniz', 'TR'),    ('Ece', 'TR'),    ('Mert', 'EUW'),    ('Selin', 'TR'),    ('Kaan', 'EUW')RETURNING id, ad;

Maçları eklerken oyuncunun adı yerine bu numaraları yazacaksın. Kaan'ın hiç maçı yok; artık bu bir sorun değil, Kaan oyuncular tablosunda kendi başına durabiliyor.

INSERT INTO maclar (oyuncu_id, sampiyon, sonuc, kill, olum, asist)VALUES    (1, 'Ahri', 'zafer', 8, 2, 6),    (1, 'Lux', 'yenilgi', 3, 5, 10),    (2, 'Jinx', 'zafer', 12, 4, 5),    (1, 'Ahri', 'zafer', 10, 1, 7),    (3, 'Garen', 'yenilgi', 2, 7, 3),    (2, 'Lux', 'zafer', 6, 2, 14),    (4, 'Teemo', 'yenilgi', 4, 9, 2),    (3, 'Yasuo', 'zafer', 9, 6, 4),    (2, 'Annie', 'yenilgi', 5, 6, 8),    (1, 'Jinx', 'zafer', 7, 3, 9);

SELECT id, oyuncu_id, sampiyon, sonuc FROM maclar ORDER BY id;

Tablo doğru ama okuması zor: adların yerinde sayılar var. Adları birazdan JOIN ile geri getireceksin. Önce yabancı anahtarın seni nelerden koruduğuna bak.

Hataları Ders 33'teki gibi psql biçimiyle gösteriyoruz: ERROR: asıl mesaj, DETAIL: ayrıntı, HINT: öneri. Neon'da düzen biraz farklı görünebilir; cümleler aynıdır.

INSERT INTO maclar (oyuncu_id, sampiyon, sonuc, kill, olum, asist)VALUES (99, 'Ahri', 'zafer', 5, 1, 3);

ERROR:  insert or update on table "maclar" violates foreign key constraint "maclar_oyuncu_id_fkey"DETAIL:  Key (oyuncu_id)=(99) is not present in table "oyuncular".

DELETE FROM oyuncular WHERE ad = 'Deniz';

ERROR:  update or delete on table "oyuncular" violates foreign key constraint "maclar_oyuncu_id_fkey" on table "maclar"DETAIL:  Key (id)=(1) is still referenced from table "maclar".

#Bire-çok ilişki

Kurduğun bağın bir adı var. Bir oyuncunun çok maçı olabilir; ama bu tablodaki her maç satırı tek bir oyuncuya aittir. Buna bire-çok ilişki denir. Veritabanlarında en sık göreceğin ilişki budur.

Kural: yabancı anahtar her zaman çok tarafına konur. maclar tablosunun her satırı tek bir oyuncuyu gösterir, sütun bu yüzden orada durur. Tersini düşün: maçları oyuncular tablosuna yazmaya kalksaydın Deniz'in satırındaki bir hücreye 1, 2, 4, 10 gibi bir liste sığdırman gerekirdi. Bir hücre tek değer tutar; bunun neden önemli olduğunu normalizasyon bölümünde göreceksin.

Oyunlardan başka bire-çok örnekleri: bir klanın çok üyesi olur, her üye tek bir klandadır. Bir maçta çok olay (kill, ejderha, kule) yaşanır, her olay tek bir maça aittir. Bir hesabın çok satın alması olur, her satın alma tek bir hesaba yazılır.

#INNER JOIN: tabloları sorguda birleştirmek

maclar tablosu artık adları bilmiyor. "Hangi maçı kim oynadı?" sorusunun cevabı iki tabloya dağıldı: oyuncu_id maçlarda, ad oyuncularda. JOIN, iki tablonun satırlarını bir koşula göre yan yana koyup tek bir sonuç tablosu yapar:

SELECT maclar.id, oyuncular.ad, maclar.sampiyon, maclar.sonucFROM maclarINNER JOIN oyuncular ON maclar.oyuncu_id = oyuncular.idORDER BY maclar.id;

Sorgunun yeni parçaları:

• FROM maclar: başlangıç tablosu.
• INNER JOIN oyuncular: yanına eklenecek tablo.
• ON maclar.oyuncu_id = oyuncular.id: eşleşme koşulu. "Maç satırındaki oyuncu_id, oyuncu satırındaki id'ye eşitse bu iki satırı birleştir."
• maclar.id, oyuncular.ad: tablo.sütun yazımı, sütunun hangi tablodan geldiğini söyler. İki tabloda da id var; tablo adını yazmasan Postgres hangisini istediğini bilemezdi.

JOIN'in ne yaptığını görmek için bir kez yıldızla bak. Ders 34'te *'ı programlarda değil, bir tabloyu tanırken kullanmayı öğrenmiştin; burada da birleşen satırı tanımak için kullanıyoruz:

SELECT *FROM maclarJOIN oyuncular ON maclar.oyuncu_id = oyuncular.idWHERE maclar.id <= 3ORDER BY maclar.id;

Her sonuç satırı, bir maç satırıyla bir oyuncu satırının yan yana yapıştırılmış hâli: önce maclar'ın yedi sütunu, sonra oyuncular'ın üç sütunu. Sonuçta iki tane id sütunu var: biri maçın, biri oyuncunun. Bu sorguda yalnız JOIN yazdığına dikkat et: INNER JOIN ile JOIN aynı şeydir. Dersin geri kalanında kısa olanı kullanacağız.

SELECT maclar.id, oyuncular.adFROM maclarJOIN oyuncular;

ERROR:  syntax error at or near ";"

SELECT COUNT(*) AS satirFROM maclar, oyuncular;

SELECT maclar.id, oyuncular.ad, maclar.sampiyonFROM maclarJOIN oyuncular ON maclar.id = oyuncular.idORDER BY maclar.id;

#Takma adlar

Tablo adlarını her sütunun önüne yazmak sorguyu uzatıyor. SQL'de bir tabloya, yalnız o sorgu içinde geçerli kısa bir ad verebilirsin:

SELECT m.id, o.ad AS oyuncu, m.sampiyon, m.killFROM maclar AS mJOIN oyuncular AS o ON m.oyuncu_id = o.idWHERE o.ad = 'Deniz'ORDER BY m.id;

maclar AS m, "bu sorguda maclar'a m diyeceğim" demek. Takma adı Ders 34'te sütunlar için kullanmıştın (kill + asist AS katki); aynı fikir tablolar için de geçerli ve yine yalnız o sorgu içinde yaşar.

Tablo takma adlarında AS'yi atlamak çok yaygındır: FROM maclar m. Sütun takma adlarında ise Ders 34'teki gibi AS'yi yazmaya devam et; o.ad AS oyuncu, sonuç tablosunun başlığını ad yerine oyuncu yapar. Tablo takma adını kısa ve tahmin edilebilir seç: genelde tablo adının ilk harfi (m, o) ya da baş harfleri (takim_sampiyonlari için ts).

SELECT id, ad, sampiyonFROM maclarJOIN oyuncular ON maclar.oyuncu_id = oyuncular.id;

ERROR:  column reference "id" is ambiguous

SELECT m.id, o.adFROM maclar mJOIN oyuncular o ON m.oyuncu_id = o.idWHERE maclar.sonuc = 'zafer';

ERROR:  invalid reference to FROM-clause entry for table "maclar"HINT:  Perhaps you meant to reference the table alias "m".

#LEFT JOIN: eşi olmayanları da göstermek

Oyuncuları maçlarıyla listelediğinde Kaan nerede? INNER JOIN yalnız eşi bulunan satırları getirir. Kaan'ın hiç maçı yok; eşleşecek bir maç satırı olmadığı için sonuçtan düştü. Bazen istediğin tam da budur. "Bütün oyuncular ve maçları, maçı olmayanlar da" istiyorsan LEFT JOIN gerekir.

SELECT o.ad, m.sampiyon, m.sonucFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idORDER BY o.id, m.id;

LEFT JOIN, soldaki tablonun (FROM'dan sonra yazılan, burada oyuncular) her satırını sonuca alır. Sağdaki tabloda (maclar) eşi bulunursa onunla birleştirir; bulunmazsa sağdaki sütunları NULL ile doldurur. Bu yüzden tabloların yeri önemli: FROM oyuncular LEFT JOIN maclar bütün oyuncuları, FROM maclar LEFT JOIN oyuncular bütün maçları tutar. Satır sayısına bak: INNER JOIN 10, LEFT JOIN 11 satır getiriyor; fark Kaan.

NULL'u işine yarayacak biçimde de kullanabilirsin. Hiç maç oynamamış oyuncuları bulmak için LEFT JOIN'den sonra eşi olmayanları seç:

SELECT o.adFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idWHERE m.id IS NULL;

m.id maç tablosunun birincil anahtarı; gerçek bir maç satırında asla NULL olmaz. Sonuçta NULL ise o oyuncunun eşi bulunamamış demektir. = NULL yazarsan hiçbir satır gelmez; Ders 34'teki gibi IS NULL kullan.

SELECT o.ad, m.sampiyonFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idWHERE m.sonuc = 'zafer'ORDER BY o.id, m.id;

SELECT o.ad, m.sampiyonFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.id AND m.sonuc = 'zafer'ORDER BY o.id, m.id;

#JOIN ile istatistik

Asıl güç, JOIN'i Ders 34'teki toplama fonksiyonlarıyla birleştirince ortaya çıkar. Her oyuncunun maç sayısı, toplam ve ortalama kill'i:

SELECT o.ad,       COUNT(*) AS mac,       SUM(m.kill) AS toplam_kill,       ROUND(AVG(m.kill), 1) AS ort_killFROM oyuncular oJOIN maclar m ON m.oyuncu_id = o.idGROUP BY o.adORDER BY mac DESC;

Ders 34'te bir sorgunun yazıldığı sırayla çalıştığı sıranın farklı olduğunu görmüştün. JOIN o sıranın en başına, FROM'un yanına oturur: önce tablolar birleşir, sonra WHERE satırları süzer, sonra GROUP BY grupları kurar, en son SELECT'teki hesaplar yapılır ve ORDER BY sıralar. Yani GROUP BY birleşmiş tablonun üzerinde çalışır; her grup bir oyuncunun maç satırlarıdır.

GROUP BY o.ad burada doğru sonuç veriyor, çünkü ad sütunu UNIQUE: iki oyuncunun adı aynı olamaz, her grup tek bir oyuncudur. Gruplanan sütun ile toplanan sütun farklı tablolarda da olabilir; tek bir tabloyla soramayacağın sorular böyle sorulur. Bölgelere göre maç ve kill sayıları:

SELECT o.bolge, COUNT(*) AS mac, SUM(m.kill) AS toplam_killFROM oyuncular oJOIN maclar m ON m.oyuncu_id = o.idGROUP BY o.bolgeORDER BY o.bolge;

bolge oyuncularda, kill maçlarda duruyor; JOIN ikisini aynı satırda buluşturdu, GROUP BY da bölgelere ayırdı.

SELECT o.ad, COUNT(*) AS macFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idGROUP BY o.adORDER BY mac DESC, o.ad;

SELECT o.ad, COUNT(m.id) AS macFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idGROUP BY o.adORDER BY mac DESC, o.ad;

#Adın yanında başka sütunlar: id'ye göre gruplamak

Oyuncunun adının yanında bölgesini de görmek istedin ve o.bolge'yi SELECT'e ekledin. Ders 34'ten tanıdığın hata geliyor:

SELECT o.ad, o.bolge, COUNT(m.id) AS macFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idGROUP BY o.ad;

ERROR:  column "o.bolge" must appear in the GROUP BY clause or be used in an aggregate function

Postgres her grubun tek bir satıra inmesini ister; bolge'yi gruplamadığın için "hangi değeri yazayım?" diye soruyor. GROUP BY o.ad, o.bolge yazabilirsin. JOIN'li sorgularda daha iyi bir yol var: birincil anahtara göre gruplamak. GROUP BY o.id yazınca Postgres her grubun tek bir oyuncu satırından geldiğini bilir ve o satırın öbür sütunlarını (ad, bolge) seçmene izin verir:

SELECT o.ad, o.bolge, COUNT(m.id) AS macFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idGROUP BY o.idORDER BY mac DESC, o.ad;

Id'ye göre gruplamanın bir yararı daha var: adı tekrar edebilen şeylerde (iki oyuncunun adı aynı olabilseydi) iki farklı oyuncu aynı gruba karışmaz. Bu dersin geri kalanında bir tablonun satırlarına göre (her takım, her kategori için) gruplarken birincil anahtarı kullanacağız.

#Çoka-çok ilişki ve ara tablo

Sıra takımlarda. Bir takımın kadrosunda birden çok şampiyon var; bir şampiyon da birden çok takımın kadrosunda olabilir: Jinx hem Gece Avcıları'nda hem Demir Duvar'da. Bu ilişkiye çoka-çok denir.

Yabancı anahtarı nereye koyacağız? takimlar tablosuna bir sampiyon_id sütunu koyarsan her takımın tek bir şampiyonu olabilir. sampiyonlar tablosuna bir takim_id koyarsan her şampiyon tek bir takımda olabilir. İkisi de yetmez. Çözüm üçüncü bir tablo: her satırı "şu takımda şu şampiyon var" diyen bir ara tablo.

Önce şampiyonlar:

CREATE TABLE sampiyonlar (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL UNIQUE,    rol text NOT NULL); INSERT INTO sampiyonlar (ad, rol) VALUES    ('Ahri', 'büyücü'),    ('Lux', 'büyücü'),    ('Garen', 'savaşçı'),    ('Jinx', 'nişancı'),    ('Yasuo', 'savaşçı'),    ('Annie', 'büyücü'),    ('Teemo', 'nişancı');

Sonra takımlar. Takımı kuran oyuncuyu da tutalım: kurucu_id bir yabancı anahtar daha. Sonundaki ON DELETE SET NULL'u ve ara tablodaki ON DELETE satırlarını bir sonraki bölümde göreceksin; şimdilik olduğu gibi yaz.

CREATE TABLE takimlar (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL UNIQUE,    kurucu_id integer REFERENCES oyuncular (id) ON DELETE SET NULL); INSERT INTO takimlar (ad, kurucu_id) VALUES    ('Gece Avcıları', 2),    ('Demir Duvar', 1),    ('Yıldız Tozu', 5),    ('Boş Kadro', 4);

kurucu_id'de NOT NULL yok: kurucusu bilinmeyen bir takım da olabilir. Şimdi ara tablo:

CREATE TABLE takim_sampiyonlari (    takim_id integer NOT NULL REFERENCES takimlar (id)        ON DELETE CASCADE,    sampiyon_id integer NOT NULL REFERENCES sampiyonlar (id)        ON DELETE RESTRICT,    PRIMARY KEY (takim_id, sampiyon_id)); INSERT INTO takim_sampiyonlari (takim_id, sampiyon_id) VALUES    (1, 1), (1, 2), (1, 4), (1, 6),    (2, 3), (2, 5), (2, 4),    (3, 1), (3, 2);

takim_sampiyonlari tablosunun kendi id'si yok. Her satırını iki sayı tanımlıyor: hangi takım, hangi şampiyon. PRIMARY KEY (takim_id, sampiyon_id) bu ikiliyi birincil anahtar yapar: aynı takım–şampiyon çifti iki kez yazılamaz, ama takım ya da şampiyon tek başına istediği kadar tekrar edebilir. (1, 4) satırı "1 numaralı takımda (Gece Avcıları) 4 numaralı şampiyon (Jinx) var" demek. Boş Kadro'nun (4) hiç satırı yok; Teemo (7) hiçbir takımda değil.

Beş tablonun hepsini birlikte çizelim:

Bileşik anahtarın işe yaradığını görmek için Jinx'i Gece Avcıları'na ikinci kez eklemeyi dene:

INSERT INTO takim_sampiyonlari (takim_id, sampiyon_id) VALUES (1, 4);

ERROR:  duplicate key value violates unique constraint "takim_sampiyonlari_pkey"DETAIL:  Key (takim_id, sampiyon_id)=(1, 4) already exists.

Mesajı Ders 33'ten tanıyorsun; bu kez DETAIL iki sütunu birlikte gösteriyor: (takim_id, sampiyon_id)=(1, 4) çifti zaten var. Ara tabloya bileşik anahtar koymasaydın bu satır sessizce eklenir, Gece Avcıları'nın şampiyon sayısı bir fazla görünürdü.

Ara tablo tek başına okunmaz: (1, 4) satırının anlamını ancak iki JOIN ile görürsün. Takımların kadroları:

SELECT t.ad AS takim, s.ad AS sampiyonFROM takimlar tJOIN takim_sampiyonlari ts ON ts.takim_id = t.idJOIN sampiyonlar s ON s.id = ts.sampiyon_idORDER BY t.ad, s.ad;

Üç tablo, iki JOIN. Her JOIN'in kendi ON'u var: önce takımdan ara tabloya, sonra ara tablodan şampiyona. İki tabloda da ad sütunu olduğu için sonuç sütunlarına takma ad (takim, sampiyon) verdik. Postgres bunu zorunlu tutmaz, ama vermeseydin sonuçta iki tane ad başlığı olurdu ve hangisinin ne olduğu anlaşılmazdı.

Aynı tablolar öbür yönden de okunur. Jinx hangi takımlarda?

SELECT t.ad AS takimFROM sampiyonlar sJOIN takim_sampiyonlari ts ON ts.sampiyon_id = s.idJOIN takimlar t ON t.id = ts.takim_idWHERE s.ad = 'Jinx'ORDER BY t.ad;

Takım başına şampiyon sayısı; kadrosu boş takım da görünsün:

SELECT t.ad AS takim, COUNT(ts.sampiyon_id) AS sampiyon_sayisiFROM takimlar tLEFT JOIN takim_sampiyonlari ts ON ts.takim_id = t.idGROUP BY t.idORDER BY sampiyon_sayisi DESC, t.ad;

Boş Kadro'nun 0 ile görünmesi için iki şey birlikte gerekti: LEFT JOIN (takımı tutmak için) ve COUNT(ts.sampiyon_id) (NULL'u saymamak için). Şampiyon adına ihtiyaç olmadığı için sampiyonlar tablosuna hiç gitmedik; sayı için ara tablo yetiyor.

#ON DELETE: bağlı satır silinince ne olacak?

Yabancı anahtar, gösterdiği satır silinmek istendiğinde ne olacağına da karar verir. Bu kararı tabloyu kurarken ON DELETE ile yazarsın:

NO ACTION ile RESTRICT arasındaki asıl fark denetimin zamanlamasındadır; bu kursta ikisi aynı işi görür, yalnız hata mesajları biraz farklıdır. Ara tabloda takim_id için CASCADE, sampiyon_id için RESTRICT seçtik; takimlar.kurucu_id için SET NULL. Üçünü de dene.

DELETE FROM sampiyonlar WHERE ad = 'Jinx';

ERROR:  update or delete on table "sampiyonlar" violates RESTRICT setting of foreign key constraint "takim_sampiyonlari_sampiyon_id_fkey" on table "takim_sampiyonlari"DETAIL:  Key (id)=(4) is referenced from table "takim_sampiyonlari".

Şimdi CASCADE. Demir Duvar dağıldı:

DELETE FROM takimlar WHERE ad = 'Demir Duvar';

Hata yok. Kadrolara yeniden bak:

SELECT t.ad AS takim, s.ad AS sampiyonFROM takimlar tJOIN takim_sampiyonlari ts ON ts.takim_id = t.idJOIN sampiyonlar s ON s.id = ts.sampiyon_idORDER BY t.ad, s.ad;

Demir Duvar'ın üç kadro satırı da gitti; sahipsiz satır kalmadı. Garen ve Yasuo ise sampiyonlar tablosunda duruyor: CASCADE yalnız silinen takıma bağlı satırları (ara tablodakileri) siler, şampiyonların kendisine dokunmaz.

Son olarak SET NULL. Kaan, Yıldız Tozu'nu kurmuştu; hiç maç oynamadan hesabını sildi:

DELETE FROM oyuncular WHERE ad = 'Kaan';

Takımları kurucularıyla listele. Kurucusu olmayan takım da görünsün diye LEFT JOIN:

SELECT t.ad AS takim, o.ad AS kurucuFROM takimlar tLEFT JOIN oyuncular o ON o.id = t.kurucu_idORDER BY t.id;

Takım duruyor, kurucusu NULL oldu. Kaan'ın maçı olsaydı bu DELETE yine reddedilirdi: maclar.oyuncu_id'nin ON DELETE'i yok, yani orada varsayılan (reddet) geçerli. Her yabancı anahtar kendi kuralını taşır; bir satırı silerken ona bağlı bütün tabloların kuralları birlikte işler. SET NULL'un çalışması için sütunun NULL kabul etmesi gerekir; kurucu_id'yi bu yüzden NOT NULL yapmadık.

#Normalizasyon sezgisi

Bu derste yaptığın şeyin bir adı var: normalizasyon. Formülü yok; üç sezgiyle yürür:

1. Bir hücreye tek değer. 'Ahri, Lux, Jinx' gibi listeler hücreye yazılmaz; liste bir ara tabloya gider.
2. Her bilgi tek yerde. Deniz'in bölgesi yalnız oyuncular tablosunda yazar; başka tablolar ona id ile işaret eder.
3. Her tablo tek bir şeyi anlatır. Oyuncu bilgisi oyuncularda, maç bilgisi maçlarda, kadro bilgisi ara tabloda.

İlk kuralı çiğneyen tasarım çok sık görülür: kadroyu takımın satırına metin olarak sıkıştırmak.

CREATE TABLE takimlar (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL,    kadro text);

Böyle bir tabloda:

• "Jinx hangi takımlarda?" sorusunu ancak LIKE '%Jinx%' ile sorabilirsin.
• Hücrenin içine yabancı anahtar konamaz; 'Jinks' diye yanlış yazılan bir ad da kabul edilir.
• Bir şampiyonun adı değişince her takımın metnini tek tek düzeltirsin.
• Aynı şampiyonun bir kadroya iki kez yazılmasını engelleyen bir kısıt yoktur.

sampiyon1, sampiyon2, sampiyon3 diye numaralı sütunlar açmak aynı hatanın başka bir kılığıdır: dördüncü şampiyon için tabloyu değiştirmen, "Jinx hangi takımlarda" için üç sütunu ayrı ayrı sorman gerekir.

Bu dersteki takim_sampiyonlari aynı bilgiyi her satıra tek bir bağlantı yazarak tutar. Yukarıdaki sorunların hiçbiri onda yok: Jinx'in takımları basit bir JOIN'le gelir, yanlış yazılmış bir şampiyonu yabancı anahtar içeri almaz, aynı bağlantıyı bileşik anahtar ikinci kez yazdırmaz.

Ne zaman durmalı? Her şeyi bölmek de hata olur. Kendine şunu sor: "Bu bilgi değişirse kaç satırı düzeltmem gerekir?" Cevap "birden çok" ise o bilgiyi kendi tablosuna taşı. bolge gibi kısa ve neredeyse hiç değişmeyen bir değeri (TR, EUW) ayrı tabloya almak zorunda değilsin; izin verilen değerleri bir CHECK ile sınırlamak çoğu zaman yeter.

Kendi tasarımımıza bu gözle bakınca bir kusur görünüyor: maclar.sampiyon hâlâ metin. "Ahri" iki maç satırında yazılı, oysa sampiyonlar tablosunda Ahri'nin bir id'si var. İkinci kuralı çiğniyor. Bunu Görev 5'te düzelteceksin.

#İndeks: aramayı hızlandırmak

Şimdiye kadarki tabloların hepsi birkaç satırdı. Gerçek bir oyunun maç tablosunda milyonlarca satır olur. WHERE mac_kodu = 'TR1_77777' dediğinde Postgres o tek maçı nasıl bulur? Elinde başka bir yol yoksa bütün satırları baştan sona tek tek okur ve her birinde koşula bakar.

İngilizce adı Ders 5'teki dizilerin sıra numarasıyla aynı (index), ama ikisi ayrı şeyler. Dizide index bir elemanın yeridir; veritabanında indeks, aramayı hızlandıran ek bir yapıdır. Hangisinden söz edildiğini cümlenin geri kalanından anlarsın.

Aslında indekslerin zaten var: Postgres her PRIMARY KEY ve her UNIQUE için kendiliğinden bir indeks açar. Görmek için pg_indexes adlı sistem tablosuna sor:

SELECT tablename, indexnameFROM pg_indexesWHERE tablename IN ('oyuncular', 'maclar')ORDER BY tablename, indexname;

oyuncular_pkey birincil anahtar için, oyuncular_ad_key adlardaki UNIQUE için. maclar.oyuncu_id için bir indeks yok: Postgres yabancı anahtar sütununa kendiliğinden indeks açmaz.

Farkı görmek için büyük bir tablo lazım. Aşağıdaki komut 100.000 sahte maçlık bir tablo kurar. Ezberlemen gerekmiyor; kopyalayıp çalıştır:

CREATE TABLE mac_gecmisi (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    mac_kodu text NOT NULL,    kill integer NOT NULL); INSERT INTO mac_gecmisi (mac_kodu, kill)SELECT 'TR1_' || sayi, sayi % 21FROM generate_series(1, 100000) AS sayi; ANALYZE mac_gecmisi;

Yeni parçalar kısaca: generate_series(1, 100000) 1'den 100.000'e kadar sayı üretir; INSERT ... SELECT her sayı için bir satır ekler. 'TR1_' || sayi metinle sayıyı yan yana yapıştırır (JavaScript'teki + gibi), sayi % 21 0 ile 20 arasında bir kill sayısı verir. ANALYZE ise Postgres'e tabloyu inceletip tahminlerini güncelletir.

||'ye dikkat: JavaScript'te "ya da" demekti, SQL'de ise metin yapıştırır. SQL'de "ya da" için Ders 34'teki gibi OR yazarsın. Tablonun ilk satırları:

SELECT * FROM mac_gecmisi ORDER BY id LIMIT 3;

Şimdi tek bir maçı ara, ama sorgunun başına EXPLAIN yaz:

EXPLAIN SELECT * FROM mac_gecmisi WHERE mac_kodu = 'TR1_77777';

Seq Scan on mac_gecmisi  (cost=0.00..1886.00 rows=1 width=17)  Filter: (mac_kodu = 'TR1_77777'::text)

EXPLAIN sorguyu çalıştırmaz; Postgres'in onu nasıl çalıştıracağını, yani sorgu planını gösterir. SQL Editor'de sonuç QUERY PLAN adlı tek sütunlu bir tablo olarak gelir; burada satırlarını düz metin olarak gösteriyoruz. Okuması:

• Seq Scan on mac_gecmisi: tabloyu baştan sona tara (sequential scan, sıralı tarama).
• Filter: her satırda bakılan koşul. 100.000 satırın hepsine bakılacak.
• cost=0.00..1886.00: tahmini maliyet. Birimi yoktur; yalnız iki planı karşılaştırmak için kullanılır.
• rows=1: Postgres'in kaç satır bulmayı beklediği.

İndeksi aç ve aynı EXPLAIN'i yeniden çalıştır:

CREATE INDEX mac_gecmisi_mac_kodu_idx ON mac_gecmisi (mac_kodu);

EXPLAIN SELECT * FROM mac_gecmisi WHERE mac_kodu = 'TR1_77777';

Index Scan using mac_gecmisi_mac_kodu_idx on mac_gecmisi  (cost=0.42..8.44 rows=1 width=17)  Index Cond: (mac_kodu = 'TR1_77777'::text)

Plan değişti. Index Scan using mac_gecmisi_mac_kodu_idx: indekste bak, doğrudan o satıra git. Tahmini maliyet 1886'dan 8,44'e indi; Index Cond da koşulun artık indekste arandığını söylüyor.

İndeksin adını sen verdin: CREATE INDEX ad ON tablo (sütun). Adını yazmazsan Postgres benzer bir ad üretir. Senin çıktındaki sayılar biraz farklı olabilir; bakacağın yer ilk satırın ilk sözcükleri: Seq Scan mı, Index Scan mı?

#JOIN'ler ve yabancı anahtar indeksleri

JOIN'in ON koşulunda da arama var: her oyuncu için maclar.oyuncu_id sütununda eşleşen satırlar aranır. Büyük tablolarda yabancı anahtar sütununa indeks açmak bu yüzden iyi bir alışkanlıktır:

CREATE INDEX maclar_oyuncu_id_idx ON maclar (oyuncu_id);

Şimdi bu küçük tabloda EXPLAIN'e bak:

EXPLAIN SELECT * FROM maclar WHERE oyuncu_id = 1;

Seq Scan on maclar  (cost=0.00..1.12 rows=1 width=84)  Filter: (oyuncu_id = 1)

İndeks var ama Postgres kullanmadı. Burada bir yanlışlık yok: maclar 10 satır ve diskte tek bir sayfaya (Postgres'in diskten bir seferde okuduğu 8 KB'lık parça) sığıyor. Bu kadar küçük bir tabloyu baştan okumak, önce indekse bakıp sonra tabloya gitmekten ucuz.

Postgres her sorguda tahmini maliyetlere bakıp en ucuz yolu seçer; tablo büyüdüğünde aynı sorgu için kendiliğinden indekse geçer. Küçük bir tabloda Seq Scan görmek bu yüzden sorun değildir.

#Ne zaman indeks açmalı?

İndeks açmaya değer sütunlar:

• Büyük bir tabloda WHERE ile sık aranan sütunlar (maç kodu, e-posta).
• JOIN'lerde kullanılan yabancı anahtar sütunları.
• Büyük bir tabloda sık ORDER BY yapılan sütunlar.

Bedeli de var:

• Her INSERT, UPDATE ve DELETE indeksleri de günceller; yazma işleri yavaşlar.
• İndeks diskte yer kaplar.
• LIKE '%777' gibi başında joker olan aramalar sıradan bir indeksi kullanamaz; yine baştan sona tarama yapılır.

Kısacası her sütuna indeks açma. Yavaş bir sorgu gördüğünde önce EXPLAIN'e bak; büyük bir tabloda Seq Scan görüyorsan aradığın sütuna indeks aç ve planı yeniden karşılaştır.

#Sık hatalar

Bu derste karşılaşabileceğin öteki hatalar. Bir hata aldığında buraya ya da bütün derslerin hatalarının toplandığı hata rehberine bak.

DROP TABLE oyuncular;

ERROR:  cannot drop table oyuncular because other objects depend on itDETAIL:  constraint maclar_oyuncu_id_fkey on table maclar depends on table oyuncularconstraint takimlar_kurucu_id_fkey on table takimlar depends on table oyuncularHINT:  Use DROP ... CASCADE to drop the dependent objects too.

CREATE TABLE yorumlar (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    oyuncu_id text NOT NULL REFERENCES oyuncular (id),    metin text NOT NULL);

ERROR:  foreign key constraint "yorumlar_oyuncu_id_fkey" cannot be implementedDETAIL:  Key columns "oyuncu_id" of the referencing table and "id" of the referenced table are of incompatible types: text and integer.

CREATE TABLE yorumlar (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    bolge text NOT NULL REFERENCES oyuncular (bolge),    metin text NOT NULL);

ERROR:  there is no unique constraint matching given keys for referenced table "oyuncular"

#Görevler

Görevleri SQL Editor'de yap ve yazdıklarını ders36.sql dosyana ekle. İlk üç görev kendi tablolarını kurar; Görev 4 ve 5 dersteki tablolarla çalışır. Bir görevi baştan kurmak istersen önce o görevin tablolarını DROP TABLE ile sil (gösteren tablo önce). Beklenen sonuçlar görevin içinde; önce kendin dene, takılırsan ipuçlarını sırayla aç, çözüme en son bak.

CREATE TABLE kategoriler (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL UNIQUE); CREATE TABLE dukkan_esyalari (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL UNIQUE,    fiyat integer NOT NULL CHECK (fiyat > 0),    kategori_id integer REFERENCES kategoriler (id)        ON DELETE SET NULL); INSERT INTO kategoriler (ad) VALUES    ('Saldırı'), ('Büyü'), ('Savunma'), ('Botlar'); INSERT INTO dukkan_esyalari (ad, fiyat, kategori_id) VALUES    ('Uzun Kılıç', 350, 1),    ('Savaş Baltası', 1300, 1),    ('Büyü Kitabı', 435, 2),    ('Kadim Asa', 1250, 2),    ('Bez Zırh', 300, 3),    ('Zincir Yelek', 800, 3);

SELECT k.ad AS kategori, e.ad AS esya, e.fiyatFROM dukkan_esyalari eJOIN kategoriler k ON k.id = e.kategori_idORDER BY k.id, e.fiyat;

INSERT INTO dukkan_esyalari (ad, fiyat, kategori_id)VALUES ('Hız Botu', 300, 9);

ERROR:  insert or update on table "dukkan_esyalari" violates foreign key constraint "dukkan_esyalari_kategori_id_fkey"DETAIL:  Key (kategori_id)=(9) is not present in table "kategoriler".

SELECT k.ad AS kategori,       COUNT(e.id) AS esya_sayisi,       MAX(e.fiyat) AS en_pahaliFROM kategoriler kLEFT JOIN dukkan_esyalari e ON e.kategori_id = k.idGROUP BY k.idORDER BY k.id;

DELETE FROM kategoriler WHERE ad = 'Savunma';

SELECT e.ad AS esya, k.ad AS kategoriFROM dukkan_esyalari eLEFT JOIN kategoriler k ON k.id = e.kategori_idORDER BY e.id;

CREATE TABLE birimler (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL UNIQUE,    maliyet integer NOT NULL CHECK (maliyet BETWEEN 1 AND 5)); CREATE TABLE ozellikler (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    ad text NOT NULL UNIQUE); CREATE TABLE birim_ozellikleri (    birim_id integer NOT NULL REFERENCES birimler (id)        ON DELETE CASCADE,    ozellik_id integer NOT NULL REFERENCES ozellikler (id)        ON DELETE RESTRICT,    PRIMARY KEY (birim_id, ozellik_id)); INSERT INTO birimler (ad, maliyet) VALUES    ('Ahri', 4), ('Lux', 3), ('Garen', 1),    ('Jinx', 4), ('Teemo', 2); INSERT INTO ozellikler (ad) VALUES    ('Büyücü'), ('Koruyucu'), ('Nişancı'),    ('Suikastçı'), ('Ejderha'); INSERT INTO birim_ozellikleri (birim_id, ozellik_id) VALUES    (1, 1), (1, 4), (2, 1), (3, 2), (4, 3), (5, 3), (5, 4);

SELECT b.ad AS birim, b.maliyetFROM ozellikler oJOIN birim_ozellikleri bo ON bo.ozellik_id = o.idJOIN birimler b ON b.id = bo.birim_idWHERE o.ad = 'Büyücü'ORDER BY b.maliyet DESC;

SELECT o.ad AS ozellik, COUNT(bo.birim_id) AS birim_sayisiFROM ozellikler oLEFT JOIN birim_ozellikleri bo ON bo.ozellik_id = o.idGROUP BY o.idORDER BY o.id;

INSERT INTO birim_ozellikleri (birim_id, ozellik_id)VALUES (2, 1);

ERROR:  duplicate key value violates unique constraint "birim_ozellikleri_pkey"DETAIL:  Key (birim_id, ozellik_id)=(2, 1) already exists.

DELETE FROM birimler WHERE ad = 'Teemo';

SELECT o.ad AS ozellik, COUNT(bo.birim_id) AS birim_sayisiFROM ozellikler oLEFT JOIN birim_ozellikleri bo ON bo.ozellik_id = o.idGROUP BY o.idORDER BY o.id;

DELETE FROM ozellikler WHERE ad = 'Büyücü';

ERROR:  update or delete on table "ozellikler" violates RESTRICT setting of foreign key constraint "birim_ozellikleri_ozellik_id_fkey" on table "birim_ozellikleri"DETAIL:  Key (id)=(1) is referenced from table "birim_ozellikleri".

DELETE FROM ozellikler WHERE ad = 'Ejderha';

Index Scan using mac_gecmisi_mac_kodu_key on mac_gecmisi  (cost=0.42..8.44 rows=1 width=17)  Index Cond: (mac_kodu = 'TR1_4242'::text)

DROP INDEX mac_gecmisi_mac_kodu_idx;

EXPLAIN SELECT * FROM mac_gecmisi WHERE mac_kodu = 'TR1_4242';

Seq Scan on mac_gecmisi  (cost=0.00..1886.00 rows=1 width=17)  Filter: (mac_kodu = 'TR1_4242'::text)

ALTER TABLE mac_gecmisi ADD UNIQUE (mac_kodu);

EXPLAIN SELECT * FROM mac_gecmisi WHERE mac_kodu = 'TR1_4242';

Index Scan using mac_gecmisi_mac_kodu_key on mac_gecmisi  (cost=0.42..8.44 rows=1 width=17)  Index Cond: (mac_kodu = 'TR1_4242'::text)

INSERT INTO mac_gecmisi (mac_kodu, kill)VALUES ('TR1_4242', 3);

ERROR:  duplicate key value violates unique constraint "mac_gecmisi_mac_kodu_key"DETAIL:  Key (mac_kodu)=(TR1_4242) already exists.

SELECT indexnameFROM pg_indexesWHERE tablename = 'mac_gecmisi'ORDER BY indexname;

SELECT id, ad FROM sampiyonlar ORDER BY id;

ALTER TABLE maclar ADD COLUMN sampiyon_id integer    REFERENCES sampiyonlar (id); UPDATE maclar SET sampiyon_id = 1 WHERE sampiyon = 'Ahri';UPDATE maclar SET sampiyon_id = 2 WHERE sampiyon = 'Lux';UPDATE maclar SET sampiyon_id = 3 WHERE sampiyon = 'Garen';UPDATE maclar SET sampiyon_id = 4 WHERE sampiyon = 'Jinx';UPDATE maclar SET sampiyon_id = 5 WHERE sampiyon = 'Yasuo';UPDATE maclar SET sampiyon_id = 6 WHERE sampiyon = 'Annie';UPDATE maclar SET sampiyon_id = 7 WHERE sampiyon = 'Teemo';

SELECT COUNT(*) AS bos_kalanFROM maclarWHERE sampiyon_id IS NULL;

ALTER TABLE maclar ALTER COLUMN sampiyon_id SET NOT NULL;ALTER TABLE maclar DROP COLUMN sampiyon;

SELECT s.rol,       COUNT(*) AS mac,       ROUND(AVG(m.kill), 1) AS ort_killFROM maclar mJOIN sampiyonlar s ON s.id = m.sampiyon_idGROUP BY s.rolORDER BY mac DESC;

DELETE FROM sampiyonlar WHERE ad = 'Teemo';

ERROR:  update or delete on table "sampiyonlar" violates foreign key constraint "maclar_sampiyon_id_fkey" on table "maclar"DETAIL:  Key (id)=(7) is still referenced from table "maclar".

#Kendini sına

SELECT o.ad, m.sampiyonFROM oyuncular oJOIN maclar m ON m.oyuncu_id = o.id; SELECT o.ad, m.sampiyonFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.id;

SELECT ad, rolFROM takimlar tJOIN takim_sampiyonlari ts ON ts.takim_id = t.idJOIN sampiyonlar s ON s.id = ts.sampiyon_id;

CREATE TABLE maclar (    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    oyuncu_id integer NOT NULL REFERENCES oyuncular (id)        ON DELETE SET NULL,    sampiyon text NOT NULL); DELETE FROM oyuncular WHERE ad = 'Deniz';

SELECT COUNT(*) FROM takimlar, sampiyonlar;

SELECT o.ad, m.killFROM oyuncular oLEFT JOIN maclar m ON m.oyuncu_id = o.idWHERE m.sampiyon = 'Lux';

#Kayıt noktası

ders36.sql dosyanı Git'e kaydet. Git'i C:\kod\veritabani klasöründe Ders 33'te başlattın; şimdi yalnız yeni bir kayıt alacaksın. VS Code terminalinde, bu klasördeyken:

Bir sonraki derste veritabanını Node.js'ten kullanacaksın: pg paketiyle Neon'a bağlanacak, parametreli sorgular yazacak ve sonuçları API'nden JSON olarak döndüreceksin.