پایگاه داده و زبان SQL

۱. چرا فایل کافی نیست

در فصل هجدهم داده را در فایل متنی ذخیره کردیم و در فصل بیست و هفتم در CSV و JSON. برای برنامه کوچک خوب است، ولی به محض بزرگ شدن، مشکلات شروع می‌شوند:

• برای پیدا کردن یک رکورد باید کل فایل خوانده شود.
• هیچ چیز جلوی داده تکراری یا نامعتبر را نمی‌گیرد.
• اگر دو برنامه همزمان بنویسند، فایل خراب می‌شود.
• ارتباط بین داده‌ها (دانشجو، درس، نمره) باید دستی مدیریت شود.

پایگاه داده رابطه‌ای همه این موارد را حل می‌کند. داده در جدول نگه داشته می‌شود: هر جدول ستون‌هایی دارد (فیلدها) و سطرهایی (رکوردها). زبان کار با آن، SQL است.

۲. ساختن جدول

-- DROP TABLE removes the table completely (data included).
-- "IF EXISTS" avoids an error the first time the script is run.
DROP TABLE IF EXISTS students;

CREATE TABLE students (
    -- PRIMARY KEY: the column that identifies a row uniquely.
    -- AUTOINCREMENT: SQLite fills it in for us, 1, 2, 3, ...
    student_id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- NOT NULL: this column can never be empty.
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,

    -- SQLite has no real DATE type; dates are stored as TEXT 'YYYY-MM-DD'.
    birth_date DATE,

    -- UNIQUE: two students can never have the same phone number.
    phone TEXT UNIQUE
);

اولین جدول: فایل 01_create_students.sql

در SQL، توضیح با دو خط تیره -- نوشته می‌شود. دستورها با نقطه‌ویرگول تمام می‌شوند و کلمات کلیدی معمولاً با حروف بزرگ نوشته می‌شوند، هرچند اجباری نیست.

۳. محدودیت‌ها

محدودیت قانونی است که پایگاه داده خودش اجرایش می‌کند. این یعنی حتی اگر برنامه شما باگ داشته باشد، داده خراب نمی‌شود.

CREATE TABLE teachers (
    teacher_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    teacher_name TEXT NOT NULL,

    -- DEFAULT: the value used when the INSERT does not mention this column.
    department   TEXT DEFAULT 'عمومی'
);

مقدار پیش‌فرض: فایل 02_create_teachers.sql

۴. کلید خارجی

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title     TEXT NOT NULL,

    -- CHECK: a rule the value must respect, tested at every INSERT/UPDATE.
    unit      INTEGER CHECK (unit > 0 AND unit < 5),

    -- A FOREIGN KEY says: this column must contain a teacher_id that really
    -- exists in the teachers table. This is what makes the data "relational".
    teacher_id INTEGER,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

-- IMPORTANT: SQLite ignores foreign keys unless you switch them on,
-- once per connection:
PRAGMA foreign_keys = ON;

کلید خارجی: فایل 03_create_courses.sql

کلید خارجی همان چیزی است که به پایگاه داده صفت «رابطه‌ای» می‌دهد: جدول درس‌ها به جدول استادها اشاره می‌کند و پایگاه داده تضمین می‌کند که این اشاره‌ها همیشه معتبرند.

۵. درج داده

-- Several rows in a single INSERT: much faster than several statements.
-- The last student has NULL as a phone number: "we do not know it".
-- NULL is not 0 and not an empty string - it means "no value".
INSERT INTO students (first_name, last_name, birth_date, phone) VALUES
('سارا', 'احمدی', '2001-05-22', '09122222222'),
('رضا',  'کریمی', '1999-11-02', '09123333333'),
('مریم', 'حسینی', '2002-09-10', NULL);

درج چند سطر: فایل 05_insert_many_students.sql

-- The teachers must exist BEFORE the courses that point at them.
INSERT INTO teachers (teacher_name, department) VALUES
('دکتر محمدی', 'کامپیوتر'),
('دکتر رضایی', 'ریاضی');

-- AUTOINCREMENT gave teacher_id = 1 to the first one and 2 to the second.
INSERT INTO courses (title, unit, teacher_id) VALUES
('پایگاه داده', 3, 1),
('ریاضی عمومی', 3, 2),
('برنامه نویسی پیشرفته', 4, 1);

ترتیب درج: فایل 06_insert_teachers_and_courses.sql

ترتیب مهم است: چون درس به استاد اشاره می‌کند، استاد باید اول وجود داشته باشد. ستون student_id را ننوشتیم، چون AUTOINCREMENT خودش پرش می‌کند.

۶. دستور SELECT و WHERE

-- WHERE keeps only the rows for which the condition is true.
SELECT * FROM students WHERE first_name = 'علی';

-- The comparison operators: =  <>  !=  <  >  <=  >=
SELECT * FROM students WHERE student_id > 2;
SELECT * FROM students WHERE first_name <> 'علی';

شرط: فایل 08_where.sql

-- AND: both conditions must be true.
SELECT first_name, last_name, birth_date FROM students
WHERE first_name = 'علی' AND last_name = 'علوی';

-- OR: at least one of them.
SELECT * FROM students
WHERE first_name = 'علی' OR first_name = 'رضا';

-- NOT reverses a condition.
SELECT * FROM students WHERE NOT first_name = 'علی';

-- Use parentheses whenever AND and OR are mixed: AND binds tighter than OR.
SELECT * FROM students
WHERE (first_name = 'علی' OR first_name = 'رضا')
  AND birth_date > '1999-01-01';

ترکیب شرط‌ها: فایل 10_and_or.sql

همه چیز آشناست، درست مثل فصل چهارم پایتون. دو تفاوت کوچک: = برای مقایسه به کار می‌رود نه ==، و <> شکل استاندارد نامساوی است، هرچند != هم کار می‌کند.

۷. به‌روزرسانی و حذف

-- UPDATE changes existing rows.
-- NEVER forget the WHERE: without it, EVERY row is changed.
UPDATE students
SET phone = '09120000000'
WHERE first_name = 'علی' AND last_name = 'علوی';

-- Several columns at once.
UPDATE students
SET phone = '09129999999', birth_date = '2000-02-20'
WHERE student_id = 1;

به‌روزرسانی: فایل 11_update.sql

-- DELETE removes rows. Here again, the WHERE is what protects the data.
-- Tip: run the same condition as a SELECT first, to see what will disappear.
SELECT * FROM students WHERE phone IS NULL;

DELETE FROM students WHERE phone IS NULL;

-- "DELETE FROM students;" without a WHERE empties the whole table.

حذف: فایل 12_delete.sql

۸. مرتب‌سازی و محدود کردن

-- ASC = ascending (the default), DESC = descending.
SELECT * FROM students ORDER BY last_name ASC;
SELECT * FROM students ORDER BY last_name DESC;

-- Sorting on several columns: first by last name, then by first name.
SELECT * FROM students ORDER BY last_name ASC, first_name ASC;

مرتب‌سازی: فایل 13_order_by.sql

-- LIMIT returns at most N rows.
SELECT * FROM students LIMIT 2;

-- OFFSET skips the first rows: this is how pagination is done.
SELECT * FROM students LIMIT 2 OFFSET 2;   -- rows 3 and 4

-- LIMIT together with ORDER BY answers "the top N".
SELECT * FROM students ORDER BY birth_date DESC LIMIT 1;   -- the youngest

محدود کردن: فایل 14_limit.sql

ترکیب ORDER BY با LIMIT جواب سؤال «N تای برتر» است. و LIMIT با OFFSET دقیقاً همان صفحه‌بندی وب‌سرویس فصل قبل است، این بار در سمت پایگاه داده.

۹. جست‌وجوی پیشرفته

-- BETWEEN a AND b: both limits are INCLUDED.
SELECT * FROM students
WHERE birth_date BETWEEN '2000-01-01' AND '2002-12-31';

-- IN tests a list of values.
SELECT * FROM students
WHERE first_name IN ('علی', 'رضا');

-- NOT IN is the opposite.
SELECT * FROM students
WHERE first_name NOT IN ('علی', 'رضا');

-- LIKE searches a pattern. '%' means "any number of characters".
SELECT * FROM students WHERE first_name LIKE 'س%';    -- starts with س
SELECT * FROM students WHERE last_name  LIKE '%ی';    -- ends with ی
SELECT * FROM students WHERE last_name  LIKE '%می%';  -- contains می

-- DISTINCT removes the duplicated rows from the RESULT.
SELECT DISTINCT department FROM teachers;

جست‌وجو: فایل‌های ۱۵ تا ۲۰

در LIKE، علامت % یعنی «هر تعداد کاراکتر» و زیرخط _ یعنی «دقیقاً یک کاراکتر».

۱۰. مقدار NULL

-- NULL is not a value, so it can never be compared with '='.
-- This query returns NOTHING, even when a phone number is missing:
SELECT * FROM students WHERE phone = NULL;

-- The correct way:
SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE phone IS NOT NULL;

-- COALESCE replaces NULL with a value of our choice.
SELECT first_name, COALESCE(phone, 'no phone') AS phone FROM students;

مقدار تهی: فایل 19_is_null.sql

۱۱. توابع تجمعی

-- An aggregate function turns many rows into ONE value.
SELECT COUNT(*) AS number_of_students FROM students;

-- COUNT(column) ignores the NULLs - compare the two numbers:
SELECT COUNT(*) AS all_rows, COUNT(phone) AS rows_with_a_phone FROM students;

SELECT MIN(birth_date) AS oldest, MAX(birth_date) AS youngest FROM students;

SELECT SUM(unit) AS total_units, AVG(unit) AS average_units FROM courses;

-- ROUND makes the result readable.
SELECT ROUND(AVG(unit), 2) AS average_units FROM courses;

توابع تجمعی: فایل 21_aggregates.sql

این توابع، چندین سطر را به یک مقدار تبدیل می‌کنند. کلمه AS به ستون نتیجه نام می‌دهد. و به تفاوت COUNT(*) با COUNT(phone) دقت کنید: دومی سطرهایی که NULL دارند را نمی‌شمارد.

۱۲. گروه‌بندی

-- GROUP BY splits the rows into groups and applies the aggregate to each group.
-- "How many teachers per department?"
SELECT department, COUNT(*) AS number_of_teachers
FROM teachers
GROUP BY department;

-- "How many courses does each teacher give?"
SELECT teacher_id, COUNT(*) AS number_of_courses, SUM(unit) AS total_units
FROM courses
GROUP BY teacher_id;

-- Rule: every column of the SELECT must either be in the GROUP BY
-- or be inside an aggregate function.

گروه‌بندی: فایل 22_group_by.sql

-- WHERE filters the ROWS (before grouping).
-- HAVING filters the GROUPS (after grouping).
SELECT teacher_id, COUNT(*) AS number_of_courses
FROM courses
GROUP BY teacher_id
HAVING COUNT(*) > 1;

-- The two can be used together.
SELECT teacher_id, COUNT(*) AS number_of_courses
FROM courses
WHERE unit >= 3                 -- keep only the courses of 3 units or more
GROUP BY teacher_id
HAVING COUNT(*) >= 1            -- then keep only the teachers who have some
ORDER BY number_of_courses DESC;

فیلتر گروه‌ها: فایل 23_having.sql

۱۳. پیوند جدول‌ها

اینجا قلب پایگاه داده رابطه‌ای است: JOIN دو جدول را بر اساس یک ستون مشترک به هم می‌چسباند.

-- A JOIN reads two tables at once, matching them on a common column.
-- INNER JOIN keeps only the rows that HAVE a match on both sides.
SELECT
    courses.title         AS 'نام درس',
    teachers.teacher_name AS 'نام استاد',
    courses.unit          AS 'تعداد واحد'
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.teacher_id;

-- Table aliases make a long query much shorter.
SELECT c.title, t.teacher_name, c.unit
FROM courses c
INNER JOIN teachers t ON c.teacher_id = t.teacher_id
ORDER BY t.teacher_name;

پیوند درونی: فایل 24_inner_join.sql

-- Add a course without a teacher, so that the difference becomes visible.
INSERT INTO courses (title, unit, teacher_id) VALUES ('زبان تخصصی', 2, NULL);

-- INNER JOIN: the course without a teacher DISAPPEARS from the result.
SELECT c.title, t.teacher_name
FROM courses c
INNER JOIN teachers t ON c.teacher_id = t.teacher_id;

-- LEFT JOIN: every row of the LEFT table is kept; the missing right side
-- becomes NULL.
SELECT c.title, COALESCE(t.teacher_name, 'no teacher yet') AS teacher
FROM courses c
LEFT JOIN teachers t ON c.teacher_id = t.teacher_id;

پیوند چپ: فایل 25_left_join.sql

درسی که استاد ندارد در INNER JOIN کاملاً ناپدید می‌شود. این رایج‌ترین علت این است که کسی می‌گوید «چرا رکوردهایم گم شدند». اگر می‌خواهید همه سطرهای یک جدول را حتماً ببینید، LEFT JOIN لازم است.

۱۴. رابطه چندبه‌چند

-- A student follows several courses, and a course has several students:
-- this is a MANY-TO-MANY relation, and it always needs a third table.
CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id    INTEGER,
    course_id     INTEGER,
    grade         REAL,
    enroll_date   DATE DEFAULT CURRENT_DATE,

    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id),

    -- A student cannot enrol twice in the same course.
    UNIQUE (student_id, course_id)
);

-- Instead of typing the ids by hand, let SQL look them up.
INSERT INTO enrollments (student_id, course_id, grade)
SELECT s.student_id, c.course_id, 18.5
FROM students s, courses c
WHERE s.first_name = 'سارا' AND c.title = 'پایگاه داده';

جدول واسط: فایل 26_enrollments.sql

-- Four tables in one query: enrollments -> students, courses -> teachers.
SELECT
    s.first_name || ' ' || s.last_name AS 'نام دانشجو',
    c.title                            AS 'نام درس',
    t.teacher_name                     AS 'استاد',
    e.grade                            AS 'نمره'
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON c.teacher_id = t.teacher_id
ORDER BY s.last_name;

-- The average grade of every course: a JOIN together with a GROUP BY.
SELECT c.title, ROUND(AVG(e.grade), 2) AS average, COUNT(*) AS students
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.title
ORDER BY average DESC;

چهار جدول در یک پرس‌وجو: فایل 27_results_three_joins.sql

شرح مثال

• یک دانشجو چند درس دارد و یک درس چند دانشجو. این چندبه‌چند است و همیشه به جدول سومی نیاز دارد که دو طرف را به هم وصل کند.
• جدول واسط می‌تواند اطلاعات خودش را هم داشته باشد، مثل نمره و تاریخ ثبت‌نام.
• UNIQUE (student_id, course_id) یعنی ترکیب این دو ستون باید یکتا باشد. یک دانشجو نمی‌تواند دو بار در یک درس ثبت‌نام کند.
• ترفند درج با SELECT: به جای اینکه شناسه‌ها را دستی تایپ کنید، از خود SQL می‌خواهید پیدایشان کند. کد این‌طور مستقل از شماره‌های واقعی می‌شود.
• علامت || در SQLite رشته‌ها را به هم می‌چسباند، مثل + در پایتون.

۱۵. رفتار هنگام حذف

اگر دانشجویی حذف شود، ثبت‌نام‌هایش چه می‌شوند؟ سه جواب ممکن است و شما باید یکی را انتخاب کنید.

PRAGMA foreign_keys = ON;      -- WITHOUT THIS LINE SQLITE IGNORES THE RULE!

CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id    INTEGER,
    course_id     INTEGER,
    grade         REAL,

    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)  ON DELETE CASCADE
);

-- Now delete a course and look at the enrolments again:
-- the matching rows disappeared by themselves.
DELETE FROM courses WHERE title = 'ریاضی عمومی';

حذف آبشاری: فایل 28_on_delete_cascade.sql

-- A course whose teacher leaves is still a course: we only want to empty
-- the reference, not to delete the course.
CREATE TABLE courses (
    course_id  INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT NOT NULL,
    unit       INTEGER CHECK (unit > 0 AND unit < 5),
    teacher_id INTEGER,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE SET NULL
);

-- Delete the teacher number 1: their courses stay, with teacher_id = NULL.
DELETE FROM teachers WHERE teacher_id = 1;

تهی کردن ارجاع: فایل 29_on_delete_set_null.sql

۱۶. تزریق SQL

این بخش، مهم‌ترین بخش امنیتی کل دوره است. تصور کنید برنامه‌ای دستور SQL خود را با چسباندن رشته‌ها می‌سازد:

name = input('name: ')
cursor.execute("SELECT * FROM students WHERE first_name = '" + name + "'")

کد بسیار خطرناک

حالا ببینید کاربر بدخواه چه می‌تواند بکند:

cursor.execute('SELECT * FROM students WHERE first_name = ?', (name,))

تزریق SQL هنوز، بعد از بیست سال، یکی از سه آسیب‌پذیری اول وب در دنیاست. نه به این دلیل که حل کردنش سخت است، بلکه به این دلیل که یک نفر در یک جای پروژه فراموش می‌کند.

خلاصه فصل