۱۴۰۵ آبان ۲۶, سه‌شنبه
دلار آمریکا۲۶۶٬۸۹۵▼ ۰٫۶۸٪یورو۲۹۴٬۳۶۰▼ ۰٫۶۹٪درهم امارات۷۲٬۷۲۵▼ ۰٫۶۸٪سکه امامی۲۵۱٬۴۹۰٬۰۰۰▲ ۰٫۳۷٪طلای ۱۸ عیار (گرم)۲۴٬۶۶۶٬۶۰۰▲ ۰٫۴٪انس طلا۳٬۸۷۰ $▲ ۰٫۱۴٪تتر۲۶۹٬۵۴۰▲ ۰٫۰۵٪بیت‌کوین۶۴٬۲۰۰ $▼ ۱٫۵۶٪اتریوم۱٬۷۸۷ $▼ ۲٫۰۸٪سولانا۷۲٫۵۵ $▲ ۱٫۴۷٪اپل۳۵۱٫۶۹ $▼ ۰٫۳۱٪انویدیا۱۵۴٫۹۴ $▼ ۰٫۷۷٪مایکروسافت۴۹۹٫۶۲ $▼ ۳٫۵۵٪آلفابت (گوگل)۲۸۰٫۴۰ $▼ ۱٫۱۷٪تسلا۳۷۹٫۶۲ $▼ ۰٫۰۳٪شاخص نزدک۲۴٬۲۴۶ $▼ ۱٫۰۸٪
نرخ ارز

آموزش SQL از صفر با PostgreSQL؛ از SELECT تا JOIN و تراکنش

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

کاور گرافیکی تیره با عنوان SQL، لوگوی PostgreSQL و پنجره ویرایشگری که یک کوئری SELECT با JOIN و GROUP BY در آن نوشته شده است
کاور گرافیکی تیره با عنوان SQL، لوگوی PostgreSQL و پنجره ویرایشگری که یک کوئری SELECT با JOIN و GROUP BY در آن نوشته شده است
فهرست مطالب
  1. SQL چیست و چرا هنوز مهم است؟
  2. راه‌اندازی محیط آموزش SQL با Docker
  3. ساخت جدول با CREATE TABLE
  4. درج داده با INSERT
  5. خواندن داده با SELECT، WHERE و ORDER BY
  6. اتصال جدول‌ها با JOIN
  7. گروه‌بندی با GROUP BY و HAVING
  8. ایندکس؛ فهرست کتاب برای جدول
  9. تراکنش؛ همه یا هیچ
  10. تزریق SQL و کوئری پارامتری
  11. تفاوت‌های رایج PostgreSQL و MySQL
  12. جمع‌بندی

این آموزش 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 برای کوئری‌های کند است.

دیدگاه‌ها

۰/۲٬۰۰۰

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

$ comments --count۰

هنوز کسی چیزی ننوشته. اولین دیدگاه را شما ثبت کنید.

مطالب مرتبط