آموزش SQL از صفر با PostgreSQL؛ از SELECT تا JOIN و تراکنش
آموزش SQL از صفر با PostgreSQL: ساخت جدول، درج و خواندن داده، انواع JOIN، GROUP BY، ایندکس، تراکنش و جلوگیری از SQL Injection، با تفاوتهای MySQL.

فهرست مطالب
- SQL چیست و چرا هنوز مهم است؟
- راهاندازی محیط آموزش SQL با Docker
- ساخت جدول با CREATE TABLE
- درج داده با INSERT
- خواندن داده با SELECT، WHERE و ORDER BY
- اتصال جدولها با JOIN
- گروهبندی با GROUP BY و HAVING
- ایندکس؛ فهرست کتاب برای جدول
- تراکنش؛ همه یا هیچ
- تزریق SQL و کوئری پارامتری
- تفاوتهای رایج PostgreSQL و MySQL
- جمعبندی
این آموزش SQL از صفر شروع میکند و تا جایی پیش میرود که بتوانید دادههای یک فروشگاه کوچک را بسازید، بخوانید، تحلیل کنید و امن نگه دارید. همه مثالها روی PostgreSQL اجرا و آزموده شدهاند و هر جا MySQL یا MariaDB دستور متفاوتی دارد، همانجا گفتهایم.
SQL چیست و چرا هنوز مهم است؟
SQL (Structured Query Language) زبان استاندارد کار با پایگاه دادههای رابطهای (Relational Database) است؛ پایگاههایی که داده را در جدولهایی با سطر و ستون نگه میدارند و جدولها را با کلید به هم وصل میکنند. SQL زبانی اعلانی (Declarative) است: شما میگویید چه دادهای میخواهید و خود پایگاه داده تصمیم میگیرد چطور آن را پیدا کند.
PostgreSQL، MySQL، MariaDB، SQLite و SQL Server همه SQL میفهمند. هسته زبان بین آنها مشترک است و تفاوتها بیشتر در جزئیات است؛ برای همین چیزی که اینجا یاد میگیرید تقریباً همهجا به کارتان میآید.
راهاندازی محیط آموزش SQL با Docker
سادهترین راه برای تمرین، اجرای PostgreSQL در یک کانتینر است. اگر Docker را نمیشناسید، اول آموزش Docker برای توسعهدهندهها را بخوانید.
docker run -d --name pg-lab -e POSTGRES_PASSWORD=secret -p 127.0.0.1:5432:5432 postgres
docker exec -it pg-lab psql -U postgres
حالا در خط فرمان psql هستید. چند فرمان کمکی آن را به خاطر بسپارید: \dt فهرست جدولها، \d customers ساختار یک جدول و \q خروج.
ساخت جدول با CREATE TABLE
یک فروشگاه ساده دو جدول دارد: مشتریها و سفارشها. هر سفارش با کلید خارجی (Foreign Key) به یک مشتری اشاره میکند.
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
city text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
total numeric(12, 2) NOT NULL CHECK (total >= 0),
status text NOT NULL DEFAULT 'pending',
created_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
چند نکته در همین چند خط هست:
GENERATED ALWAYS AS IDENTITYشناسه را خودکار میسازد. چونALWAYSاست، PostgreSQL اجازه نمیدهد دستی مقدار بدهید (مگر باOVERRIDING SYSTEM VALUE) و خطا برمیگرداند؛ این جلوی تداخل شناسهها را میگیرد.- کلید اصلی (Primary Key) هر سطر را یکتا مشخص میکند و
UNIQUEتکراری بودن ایمیل را ممنوع میکند. - برای مبلغ از
numericاستفاده کنید، نهfloat؛ اعداد اعشاری شناور برای پول خطای گرد کردن دارند. CHECKقاعدهای است که پایگاه داده همیشه اجرا میکند، حتی اگر کد برنامه اشتباه کند.
در MySQL و MariaDB همین جدول کمی فرق دارد:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
city VARCHAR(100),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
کلید خارجی را در هر دو پایگاه داده در سطح جدول بنویسید (مثل نمونه orders)، نه کنار تعریف ستون؛ این شکل همهجا یکسان کار میکند.
درج داده با INSERT
INSERT INTO customers (name, email, city) VALUES
('Sara Ahmadi', 'sara@example.com', 'Tehran'),
('Arash Karimi', 'arash@example.com', 'Shiraz'),
('Niloufar Rad', 'niloufar@example.com', 'Tehran'),
('Kaveh Moradi', 'kaveh@example.com', NULL);
INSERT INTO orders (customer_id, total, status) VALUES
(1, 120.00, 'paid'),
(1, 45.50, 'paid'),
(2, 300.00, 'pending'),
(3, 80.00, 'paid');
رشتهها در SQL با کوتیشن تکی نوشته میشوند. NULL یعنی «مقدار نامعلوم» و با رشته خالی فرق دارد. اگر شناسه سطر تازه را لازم دارید، در PostgreSQL انتهای INSERT بنویسید RETURNING id. MariaDB در نسخههای تازه (ما روی MariaDB 11 آزمودیم) همین را دارد، اما در MySQL باید از LAST_INSERT_ID() استفاده کنید.
خواندن داده با SELECT، WHERE و ORDER BY
SELECT name, city
FROM customers
WHERE city = 'Tehran'
ORDER BY name;
SELECT id, customer_id, total
FROM orders
WHERE status = 'paid' AND total > 50
ORDER BY total DESC
LIMIT 2;
کوئری دوم سفارشهای پرداختشده بالای ۵۰ را از بزرگ به کوچک مرتب میکند و دو تای اول را برمیگرداند؛ یعنی سفارشهای ۱ و ۴. برای پیدا کردن مقدارهای خالی هرگز = NULL ننویسید، چون نتیجهاش همیشه نامعلوم است؛ درستش WHERE city IS NULL است.
بهروزرسانی و حذف هم سادهاند، اما خطرناک:
UPDATE orders SET status = 'paid' WHERE id = 3;
DELETE FROM orders WHERE status = 'cancelled';
UPDATE یا DELETE بدون WHERE روی همه سطرها اجرا میشود. عادت کنید اول همان شرط را با SELECT امتحان کنید.
اتصال جدولها با JOIN
قدرت اصلی پایگاه داده رابطهای در اتصال (Join) جدولهاست. این جدول انواع رایج را خلاصه میکند:
| نوع | چه برمیگرداند |
|---|---|
INNER JOIN (یا فقط JOIN) |
فقط سطرهایی که در هر دو جدول جفت دارند |
LEFT JOIN |
همه سطرهای جدول چپ؛ اگر جفت نداشتند، ستونهای جدول راست NULL میشوند |
RIGHT JOIN |
برعکس LEFT JOIN |
FULL OUTER JOIN |
همه سطرهای هر دو طرف؛ در MySQL و MariaDB پشتیبانی نمیشود |
CROSS JOIN |
هر سطر چپ با هر سطر راست؛ معمولاً ناخواسته و پرهزینه |
-- every order with its customer's name
SELECT c.name, o.total, o.status
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.total DESC;
-- customers who have never ordered
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;
کوئری دوم الگویی است که زیاد به کارتان میآید: LEFT JOIN همه مشتریها را نگه میدارد و شرط o.id IS NULL آنهایی را جدا میکند که سفارشی ندارند. روی داده نمونه، نتیجه فقط Kaveh Moradi است.
در این کوئریها AS c و AS o نام مستعار (Alias) جدولاند و فقط نوشتن را کوتاه میکنند. شرط اتصال در ON میآید و شرط فیلتر در WHERE. در INNER JOIN جای این دو در نتیجه فرقی ندارد، اما در LEFT JOIN دارد: اگر شرطی روی ستونهای جدول راست را به WHERE ببرید، سطرهای بیجفت حذف میشوند و LEFT JOIN عملاً به INNER JOIN تبدیل میشود. اگر میخواهید همه مشتریها بمانند و فقط سفارشهای پرداختشده کنارشان بیاید، شرط را در ON بنویسید: ON o.customer_id = c.id AND o.status = 'paid'.
گروهبندی با GROUP BY و HAVING
برای گزارشگیری، سطرها را گروهبندی و با توابع تجمیعی مثل COUNT، SUM و AVG خلاصه میکنیم:
SELECT c.name,
COUNT(o.id) AS paid_orders,
SUM(o.total) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
HAVING SUM(o.total) > 100
ORDER BY revenue DESC;
خروجی واقعی این کوئری روی داده نمونه در PostgreSQL:
name | paid_orders | revenue
-------------+-------------+---------
Sara Ahmadi | 2 | 165.50
ترتیب منطقی اجرا را بدانید تا گیج نشوید: اول FROM و JOIN، بعد WHERE روی سطرها، بعد GROUP BY، سپس HAVING روی گروهها، بعد SELECT و در آخر ORDER BY و LIMIT. برای همین شرط روی SUM در WHERE جا نمیشود.
ایندکس؛ فهرست کتاب برای جدول
بدون ایندکس، پایگاه داده برای پیدا کردن سفارشهای یک مشتری باید کل جدول را بخواند. ایندکس (Index) ساختاری جداگانه است که این جستوجو را سریع میکند. مستندات PostgreSQL تصریح میکند که تعریف کلید خارجی بهطور خودکار روی ستون ارجاعدهنده ایندکس نمیسازد؛ پس معمولاً خودتان باید بسازید:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
EXPLAIN نقشه اجرای کوئری را نشان میدهد. روی جدول چهارسطری ما، PostgreSQL هنوز Seq Scan (خواندن کل جدول) را انتخاب میکند، چون برای چند سطر سریعتر است. ایندکس وقتی ارزشش را نشان میدهد که جدول بزرگ شود. هر ایندکس نوشتن را کمی کندتر میکند و فضا میگیرد، پس فقط ستونهایی را ایندکس کنید که در کوئریهای پرتکرار واقعاً فیلتر یا متصل میشوند.
تراکنش؛ همه یا هیچ
فرض کنید ۲۰۰ واحد از حساب سارا به حساب آرش منتقل میشود. اگر برداشت انجام شود و واریز نه، پول گم شده است. تراکنش (Transaction) چند دستور را به یک واحد تبدیل میکند:
CREATE TABLE accounts (
id integer PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sara', 500), (2, 'Arash', 100);
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
اگر وسط کار خطایی رخ دهد، با ROLLBACK همه چیز به قبل از BEGIN برمیگردد. در آزمایش ما، تلاش برای برداشت ۱۰۰۰ واحد قید CHECK را نقض کرد و PostgreSQL پس از آن هر دستور دیگری را تا پایان تراکنش رد کرد:
ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"
ERROR: current transaction is aborted, commands ignored until end of transaction block
بعد از ROLLBACK موجودیها دستنخورده ماندند. در MySQL و MariaDB تراکنش فقط روی موتورهایی مثل InnoDB کار میکند و دستورهای ساختاری مثل CREATE TABLE تراکنش جاری را خودکار ثبت میکنند.
تزریق SQL و کوئری پارامتری
خطرناکترین اشتباه در کار با SQL چسباندن ورودی کاربر به متن کوئری است؛ همان چیزی که به آن تزریق SQL (SQL Injection) میگویند. در راهنمای OWASP Top 10 نسخه ۲۰۲۵ هم تزریق با شناسه A05 در فهرست آمده است. نمونه زیر با Node.js و کتابخانه pg نوشته شده و آن را اجرا کردهایم:
const { Pool } = require("pg");
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
// UNSAFE: user input glued into the SQL string
async function findCustomerUnsafe(email) {
const sql = `SELECT id, name FROM customers WHERE email = '${email}'`;
return (await pool.query(sql)).rows;
}
// SAFE: the value travels separately from the SQL text
async function findCustomer(email) {
const result = await pool.query(
"SELECT id, name FROM customers WHERE email = $1",
[email]
);
return result.rows;
}
با ورودی ' OR '1'='1، نسخه ناامن همه مشتریها را برگرداند، چون شرط کوئری به «همیشه درست» تبدیل شد؛ نسخه پارامتری هیچ سطری برنگرداند، چون همین متن را فقط بهعنوان یک ایمیل عجیب جستوجو کرد.
| کتابخانه | پایگاه داده | نشانگر پارامتر |
|---|---|---|
pg (Node.js) |
PostgreSQL | $1، $2 |
psycopg (Python) |
PostgreSQL | %s |
mysql2 (Node.js) |
MySQL / MariaDB | ? |
دو نکته از راهنمای OWASP: نام جدول و ستون را نمیشود پارامتری کرد، پس اگر باید از ورودی بیایند، آنها را با یک فهرست مجاز (Allow-list) در کد بررسی کنید. و کاربر پایگاه داده برنامه فقط به همان جدولها و دسترسیهایی نیاز دارد که واقعاً استفاده میکند. اگر داده را از راه API در اختیار دیگران میگذارید، راهنمای API و مقایسه REST، GraphQL و gRPC قدم بعدی خوبی است.
تفاوتهای رایج PostgreSQL و MySQL
| موضوع | PostgreSQL | MySQL / MariaDB |
|---|---|---|
| شناسه خودکار | GENERATED ... AS IDENTITY |
AUTO_INCREMENT |
| گرفتن شناسه سطر تازه | RETURNING id |
LAST_INSERT_ID()؛ MariaDB RETURNING هم دارد |
FULL OUTER JOIN |
دارد | ندارد |
| نقلقول نام ستون | "name" |
`name` |
| ایندکس خودکار روی کلید خارجی | ندارد | InnoDB میسازد |
جمعبندی
با همین چند دستور، CREATE TABLE، INSERT، SELECT، JOIN، GROUP BY، CREATE INDEX و تراکنش، بخش بزرگی از کار روزمره با پایگاه داده را پوشش میدهید. دو عادت را از روز اول جدی بگیرید: قیدها (NOT NULL، UNIQUE، CHECK و کلید خارجی) را در خود پایگاه داده تعریف کنید و ورودی کاربر را فقط با کوئری پارامتری بفرستید. قدم بعدی، تمرین روی داده واقعی پروژه خودتان و خواندن خروجی EXPLAIN برای کوئریهای کند است.




دیدگاهها
دیدگاهها پس از بررسی تحریریه منتشر میشوند. توهین، تبلیغ و لینکهای بیربط حذف میشوند؛ نقد فنی و مستند همیشه خوشآمد است.
هنوز کسی چیزی ننوشته. اولین دیدگاه را شما ثبت کنید.