Learning SQL

1. اصول SQL: جداول، پرس‌وجوها و دستکاری داده‌ها

یک پایگاه داده چیزی جز مجموعه‌ای از اطلاعات مرتبط نیست.

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

عملیات CRUD. SQL چهار عملیات اساسی برای تعامل با داده‌ها ارائه می‌دهد:

• CREATE: وارد کردن رکوردهای جدید به جداول
• READ: بازیابی داده‌ها با استفاده از دستورات SELECT
• UPDATE: اصلاح رکوردهای موجود
• DELETE: حذف رکوردها از جداول

ساختار پرس‌وجو. یک پرس‌وجوی معمولی SQL شامل موارد زیر است:

• SELECT: مشخص می‌کند کدام ستون‌ها بازیابی شوند
• FROM: منبع جدول(ها) را شناسایی می‌کند
• WHERE: سطرها را بر اساس شرایط فیلتر می‌کند
• ORDER BY: مجموعه نتایج را مرتب می‌کند

2. اتصال جداول: باز کردن روابط در پایگاه‌های داده رابطه‌ای

نقش ایندکس‌ها تسهیل بازیابی زیرمجموعه‌ای از سطرها و ستون‌های یک جدول بدون نیاز به بررسی هر سطر در جدول است.

انواع اتصال‌ها. SQL چندین روش برای ترکیب داده‌ها از جداول مختلف ارائه می‌دهد:

• INNER JOIN: فقط سطرهای مطابق از هر دو جدول را برمی‌گرداند
• LEFT/RIGHT OUTER JOIN: شامل تمام سطرها از یک جدول، حتی بدون تطابق
• FULL OUTER JOIN: شامل تمام سطرها از هر دو جدول
• CROSS JOIN: تمام ترکیب‌های ممکن (ضرب دکارتی) را تولید می‌کند

شرایط اتصال. عبارت ON مشخص می‌کند که جداول چگونه به هم مرتبط هستند، معمولاً با استفاده از روابط کلید اصلی و خارجی. برای مثال:

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id

اتصال جداول متعدد. پرس‌وجوهای پیچیده اغلب شامل اتصال سه یا چند جدول برای جمع‌آوری اطلاعات مرتبط از سراسر طرح پایگاه داده هستند.

3. فیلتر کردن و گروه‌بندی داده‌ها: پالایش نتایج پرس‌وجو

یک عبارت where ممکن است شامل یک یا چند شرط باشد که با عملگرهای and و or جدا شده‌اند.

قدرت عبارت WHERE. فیلتر کردن به شما امکان می‌دهد فقط داده‌های مورد نیاز خود را بازیابی کنید:

• عملگرهای مقایسه: =, <>, <, >, BETWEEN, IN, LIKE
• عملگرهای منطقی: AND, OR, NOT
• تطابق الگو: با استفاده از کاراکترهای جایگزین (% و _) با LIKE

توابع تجمعی. SQL توابعی برای خلاصه‌سازی داده‌ها ارائه می‌دهد:

• COUNT(): تعداد سطرها
• SUM(): مجموع مقادیر عددی
• AVG(): میانگین مقادیر عددی
• MIN()/MAX(): کوچک‌ترین/بزرگ‌ترین مقادیر

GROUP BY و HAVING. این عبارات امکان تحلیل زیرمجموعه‌های داده را فراهم می‌کنند:

• GROUP BY: سطرها را بر اساس مقادیر ستون‌ها به گروه‌ها سازماندهی می‌کند
• HAVING: گروه‌ها را فیلتر می‌کند (مشابه WHERE، اما برای داده‌های گروه‌بندی شده)

4. زیرپرس‌وجوها: قدرت تو در تو برای بازیابی داده‌های پیچیده

یک زیرپرس‌وجو، پرس‌وجویی است که درون یک عبارت SQL دیگر قرار دارد (که در ادامه به آن عبارت حاوی می‌گویم).

انواع زیرپرس‌وجوها:

• اسکالر: یک مقدار واحد را برمی‌گرداند
• ستون: یک ستون از چندین سطر را برمی‌گرداند
• سطر: یک سطر از چندین ستون را برمی‌گرداند
• جدول: چندین ستون و سطر را برمی‌گرداند

مکان‌های زیرپرس‌وجو:

• SELECT: برای ستون‌های محاسبه‌شده
• FROM: به عنوان جداول مشتق‌شده
• WHERE: برای فیلتر کردن پویا
• HAVING: برای فیلتر کردن نتایج گروه‌بندی شده

زیرپرس‌وجوهای همبسته. این‌ها به ستون‌های پرس‌وجوی بیرونی ارجاع می‌دهند و امکان پردازش سطر به سطر را فراهم می‌کنند. در حالی که قدرتمند هستند، می‌توانند بر عملکرد مجموعه داده‌های بزرگ تأثیر بگذارند.

5. SQL پیشرفته: نماها، تراکنش‌ها و متاداده

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

نماها. جداول مجازی بر اساس دستورات SELECT:

• ساده‌سازی پرس‌وجوهای پیچیده
• ارائه امنیت داده با محدود کردن دسترسی
• ارائه رابط‌های ثابت به عنوان طرح‌ها تکامل می‌یابند

تراکنش‌ها. اطمینان از یکپارچگی داده‌ها برای عملیات چند مرحله‌ای:

• BEGIN: شروع یک تراکنش
• COMMIT: ذخیره دائمی تغییرات
• ROLLBACK: لغو تغییرات در صورت بروز خطا

متاداده. اطلاعات درباره ساختار پایگاه داده:

• فرهنگ داده: ذخیره تعاریف اشیاء پایگاه داده
• طرح اطلاعات: روش استاندارد برای دسترسی به متاداده
• کاتالوگ‌های سیستم: جداول متاداده خاص پایگاه داده

6. ایندکس‌گذاری و محدودیت‌ها: بهینه‌سازی عملکرد پایگاه داده

ایندکس‌ها مکانیزمی هستند که سرور پایگاه داده برای کنترل استفاده همزمان از منابع داده استفاده می‌کند.

ایندکس‌ها. بهبود عملکرد پرس‌وجو:

• B-tree: ساختار درخت متوازن، مناسب برای اکثر انواع داده
• Bitmap: کارآمد برای ستون‌هایی با کاردینالیتی پایین
• Full-text: بهینه‌سازی شده برای جستجوی اسناد متنی

محدودیت‌ها. اعمال قوانین یکپارچگی داده:

• کلید اصلی: اطمینان از شناسایی یکتای سطرها
• کلید خارجی: حفظ یکپارچگی ارجاعی بین جداول
• یکتا: جلوگیری از مقادیر تکراری در یک ستون
• بررسی: اعمال شرایط خاص بر روی مقادیر ستون

بهینه‌سازی پرس‌وجو. تکنیک‌هایی برای بهبود عملکرد:

• تحلیل برنامه‌های اجرایی
• استفاده از ایندکس‌های مناسب
• اجتناب از اسکن کامل جدول در صورت امکان
• بهینه‌سازی عملیات JOIN

7. تحلیل با SQL: توابع پنجره برای بینش‌های داده

با استفاده از توابع تحلیلی، می‌توانید همه این کارها و بیشتر را انجام دهید.

توابع پنجره. انجام محاسبات بر روی مجموعه‌ای از سطرها:

• عبارت OVER: تعریف پنجره‌ای از سطرها برای عملیات
• PARTITION BY: گروه‌بندی سطرها برای تحلیل
• ORDER BY: تعیین ترتیب سطرها درون پارتیشن‌ها

توابع رتبه‌بندی:

• ROW_NUMBER(): اختصاص شماره‌های یکتا به سطرها
• RANK(): اختصاص رتبه‌ها با فاصله برای تساوی‌ها
• DENSE_RANK(): اختصاص رتبه‌ها بدون فاصله

توابع تجمعی پنجره:

• SUM(), AVG(), COUNT() بر روی پنجره‌ها
• مجموع‌های جاری و میانگین‌های متحرک

توابع جابجایی:

• LAG(): دسترسی به داده از سطرهای قبلی
• LEAD(): دسترسی به داده از سطرهای بعدی

آخرین به‌روزرسانی:: August 22, 2024