جلسهی 6: وصل کردن جدولها Joining Tables
جدول loans بهجای اسمها فقط شمارهها (id) را نگه میدارد. چرا؟ و چطور دوباره اسم دانشآموز و عنوان کتاب را کنارش بیاوریم؟ با JOIN ... ON جدولها را به هم وصل میکنیم، اسم جدول را جلوی ستون مینویسیم (s.name) و با LEFT JOIN و IS NULL کسانی را پیدا میکنیم که هیچ امانتی ندارند.
در این جلسه یاد میگیری
- میدانی چرا
loansشمارهی دانشآموز و کتاب را نگه میدارد و نه اسمشان را. - با
JOIN ... ONدو یا سه جدول را به هم وصل میکنی. - اسم کوتاه جدول را جلوی ستون مینویسی (
s.name،b.title) وJOINرا باWHEREوGROUP BYترکیب میکنی. - با
LEFT JOINوIS NULLردیفهایی را پیدا میکنی که جفتی در جدول دیگر ندارند.
درس
🔐 قانون طلایی این دوره: دادهی آدمها امانت است. پشت هر ردیف جدول یک آدم واقعی است: اسمش، کلاسش، کتابهایی که خوانده. دادهی کسی را بدون اجازه جمع نکن، به کسی نده و برای مسخره کردن یا سرک کشیدن به کار نبر. همهی دادههای این دوره ساختگیاند و هر بار که برنامه را اجرا میکنی از اول ساخته میشوند، پس هر چه میخواهی امتحان کن: چیزی خراب نمیشود.
🔁 یادت هست؟ GROUP BY. GROUP BY ردیفها را دسته میکند و COUNT(*) برای هر دسته جدا میشمارد؛ هر دسته یک ردیف جواب میشود. امروز همین را روی جدولهای وصلشده میزنیم تا بفهمیم هر کس چند کتاب برده.
from db import show
show("SELECT grade, COUNT(*) AS kids FROM students GROUP BY grade ORDER BY grade")چرا loans شماره نگه میدارد؟ هر ردیف loans میگوید چه کسی (student_id) چه کتابی (book_id) را کِی (day) برد و آیا برگرداند (returned: ۱ یعنی برگرداند، ۰ یعنی هنوز پیش اوست). اگر بهجای شماره اسم مینوشتیم، اسم سارا بارها تکرار میشد، یک غلط تایپی یک «سارای دیگر» میساخت و اگر دو سارا داشتیم قاطی میشدند. حالا اسم هر کس فقط یک جا نوشته شده: در students.
from db import show
show("SELECT * FROM loans WHERE student_id = 1")وصل کردن با JOIN ... ON. FROM loans JOIN students ON loans.student_id = students.id یعنی «هر ردیف loans را کنار همان ردیف students بگذار که idاش با student_id برابر است». بعد از ON میگویی کدام ستونها باید برابر باشند. چون هر دو جدول ستون id دارند، اسم جدول را جلوی ستون مینویسیم: students.name، loans.day.
from db import show
show("SELECT students.name, loans.day FROM loans JOIN students ON loans.student_id = students.id WHERE loans.book_id = 8 ORDER BY loans.day")اسم کوتاه و سه جدول. نوشتن students. هر بار طولانی است؛ بعد از اسم جدول یک اسم کوتاه بگذار: FROM loans l JOIN students s ... و بعد s.name. میشود JOIN دوم هم زد تا عنوان کتاب هم بیاید. سؤال بلند را در پایتون میشود داخل """ ... """ در چند خط نوشت تا خواناتر شود.
from db import show
show("""
SELECT l.day, s.name, b.title
FROM loans l
JOIN students s ON l.student_id = s.id
JOIN books b ON l.book_id = b.id
WHERE s.city = 'Shiraz'
ORDER BY l.day
""")JOIN با GROUP BY. «هر دانشآموز چند کتاب برده؟» اول loans را به students وصل کن، بعد با اسم گروه کن و بشمار. همهی چیزهایی که بلد بودی (WHERE، GROUP BY، HAVING، ORDER BY، LIMIT) بعد از JOIN هم کار میکنند.
from db import show
show("""
SELECT s.name, COUNT(*) AS borrowed
FROM loans l
JOIN students s ON l.student_id = s.id
GROUP BY s.name
ORDER BY borrowed DESC, s.name
""")LEFT JOIN و IS NULL. JOIN فقط ردیفهایی را نگه میدارد که جفت دارند؛ لیلا که هیچ کتابی نبرده، اصلاً در جواب نیست. LEFT JOIN همهی ردیفهای جدول چپ (اولی) را نگه میدارد و جایی که جفتی نیست، ستونهای جدول دوم NULL (هیچ) میشوند. با WHERE l.id IS NULL همانهایی را میگیری که جفت ندارند. برای پرسیدن «هیچ است» همیشه IS NULL بنویس، نه = NULL.
from db import show
show("""
SELECT s.name, l.id
FROM students s
LEFT JOIN loans l ON l.student_id = s.id
WHERE l.id IS NULL
""")اشتباه رایج: ON غلط. ON l.book_id = s.id شمارهی کتاب را با شمارهی دانشآموز مقایسه میکند؛ خطا نمیدهد، ولی جواب بیمعنی است (کتاب ۳ را به دانشآموز ۳ میچسباند). همیشه بپرس: «این شماره، شمارهی چیست؟» student_id با s.id، book_id با b.id. اشتباه دیگر: نوشتن id تنها وقتی دو جدول id دارند؛ خطای ambiguous column name: id یعنی «کدام id؟».
مثالهای این جلسه
- مثال پایتون (پایگاه داده با پایتون): show("SELECT grade, COUNT(*) AS kids FROM s…
- مثال پایتون (پایگاه داده با پایتون): show("SELECT * FROM loans WHERE student_id …
- مثال پایتون (پایگاه داده با پایتون): show("SELECT students.name, loans.day FROM …
- مثال پایتون (پایگاه داده با پایتون): show("""
- مثال پایتون (پایگاه داده با پایتون): show(""" — وصل کردن جدولها
- مثال پایتون (پایگاه داده با پایتون): show(""" — وصل کردن جدولها 2
چالشها
- How Many Genres? (آسان)
- Read a Loan (آسان)
- Who Took Python for Kids? (متوسط)
- The Last Days (متوسط)
- Favourite Genres (سخت)
- Books Nobody Took (سخت)