Skip to content

6. نگاهی عمیق به زبان SQL

6.1. Introduction

در این فصل، ما ارائه می‌دهیم

  • گزینه‌های نحوی بیشتر برای دستور SELECT، که آن را به یک دستور پرس‌وجوی بسیار قدرتمند، به‌ویژه برای پرس‌وجوی همزمان چندین جدول، تبدیل می‌کند.
  • دستورالعمل‌های سینتکس توسعه‌یافته برای دستورات پوشش‌داده‌شده

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

6.1.1. جدول CLIENTS

این جدول اطلاعات مربوط به مشتریان را از PME ذخیره می‌کند:

 

Image

ID
یک شناسه یکتا برای مشتری – کلید اصلی
NOM
نام مشتری
STATUT
I=فردی، E=کسب‌وکار، A=بخش دولتی
PRENOM
نام (برای افراد)
CONTACT
نام شخص تماس‌گیرنده در سازمان مشتری (در صورت کسب‌وکار یا نهاد عمومی)
RUE
آدرس مشتری – خیابان
VILLE
شهر
CPOSTAL
کد پستی
TELEPH
تلفن
DEPUIS
از چه زمانی مشتری ما بوده‌اید؟
DEBITEUR
O (بله) اگر مشتری به شرکت بدهکار باشد و N (خیر) در غیر این صورت.

6.1.2. جدول ARTICLES

این اطلاعات مربوط به محصولات فروخته‌شده، در این مورد کتاب‌ها، را ذخیره می‌کند. ساختار آن به شرح زیر است:

Image

ISBN
یک شناسه یکتا برای یک کتاب (ISBN = شماره استاندارد بین‌المللی کتاب) – کلید اصلی
TITRE
عنوان کتاب
CODEDITEUR
کدی که یک ناشر را به‌طور منحصربه‌فرد شناسایی می‌کند
AUTEUR
نام نویسنده
RESUME
خلاصه کتاب
QTEANCOUR
مقدار فروش در طول سال
QTEANPREC
مقدار فروش در سال قبل
DERNVENTE
تاریخ آخرین فروش
QTERECUE
مقدار آخرین تحویل
DERNLIV
تاریخ آخرین تحویل
PRIXVENTE
قیمت فروش
COUT
هزینه خرید
MINCDE
حداقل مقدار سفارش
MINSTOCK
حداقل سطح موجودی
QTESTOCK
مقدار موجود در انبار

محتوای آن می‌تواند به شرح زیر باشد:

Image

6.1.3. جدول COMMANDES

این اطلاعات مربوط به سفارش‌های ثبت‌شده توسط مشتریان را ذخیره می‌کند. ساختار آن به شرح زیر است:

Image

NOCMD
شناسه‌ای یکتا برای یک سفارش – کلید اصلی
IDCLI
شماره مشتری ثبت‌کننده این سفارش – کلید خارجی – مرجع CLIENTS(ID)
DATE_CMD
تاریخ ثبت این سفارش
ANNULE
O (بله) اگر سفارش لغو شده باشد و N (خیر) در غیر این صورت.

Image

6.1.4. جدول DETAILS

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

Image

NOCMD
شماره سفارش – کلید خارجی که به ستون NOCMD در جدول COMMANDES ارجاع می‌دهد
ISBN
شماره کتاب سفارش‌شده – کلید خارجی که به ستون ISBN در جدول LIVRES اشاره می‌کند
QTE
مقدار سفارش‌شده

محتویات آن می‌تواند به شرح زیر باشد:

Image

در بالا می‌بینیم که سفارش شماره ۳ (NOCMD) مربوط به سه کتاب است. این بدان معناست که مشتری همزمان سه کتاب سفارش داده است. شماره مرجع این مشتری را می‌توان در جدول [COMMANDES] یافت، جایی که می‌بینیم سفارش شماره ۳ توسط مشتری شماره ۵ ثبت شده است. جدول [CLIENTS] به ما می‌گوید که مشتری شماره ۵ شرکت NetLogos در سگره است.

6.2. سفارش SELECT

در اینجا هدف ما تعمیق درک خود از سفارش SELECT با ارائه نحوهای جدید برای آن است.

6.2.1. سینتکس یک پرس‌وجوی چندجدولی

syntaxe
SELECT column1, column2, ...
FROM table1, table2, ..., tablep
WHERE condition
ORDER BY ...
action
آنچه در اینجا جدید است این است که ستون‌های column1، column2، ... از چندین جدول table1، table2، ... می‌آیند. اگر دو جدول ستون‌هایی با نام یکسان داشته باشند، این ابهام با استفاده از نشانه‌گذاری tablei.colonnej حل می‌شود. condition ممکن است به ستون‌هایی از جداول مختلف اشاره کند.

نحوه کار

1
جدول حاصلضرب کارتزین table1، table2، …، tablep ساخته می‌شود. اگر ni تعداد سطرهای در tablei باشد، جدول ساخته‌شده بنابراین دارای n1*n2*...*np سطر حاوی تمام ستون‌های جدول‌های مختلف است.
2
condition از WHERE بر این جدول اعمال می‌شود. بدین ترتیب یک جدول جدید تولید می‌شود
3
این بر اساس روش مشخص‌شده در ORDER مرتب شده است.
4
ستون‌های درخواست‌شده در SELECT نمایش داده می‌شوند.

مثال‌ها

ما از جداول نشان‌داده‌شده در بالا استفاده می‌کنیم. می‌خواهیم جزئیات سفارش‌های ثبت‌شده پس از ۲۵ سپتامبر را ببینیم:

SQL>select details.nocmd,isbn,qte from commandes,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd

Image

توجه داشته باشید که پس از «FROM»، نام تمام جداول مرجع را وارد می‌کنیم. در مثال قبلی، ستون‌های انتخاب‌شده همگی به جدول «DETAILS» تعلق دارند. با این حال، شرط به جدول «COMMANDES» اشاره دارد. از این رو لازم است نام این جدول را پس از FROM مشخص کنید. عملیاتی که برابری بین ستون‌های دو جدول مختلف را بررسی می‌کند، اغلب «ایکویوجین» (equijoin) نامیده می‌شود.

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

SQL> select details.nocmd,isbn,qte from commandes
    inner join details on details.nocmd=commandes.nocmd
    where commandes.datecmd>'25-sep-91'

بیایید مثال‌هایمان را ادامه دهیم. ما همان نتیجه قبلی را می‌خواهیم، اما این بار عنوان کتاب مرتب باشد، نه شماره آن ISBN:

SQL>select commandes.nocmd, articles.titre, details.qte
  from commandes,articles,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn

Image

با پرس‌وجوی زیر، SQL، که خوانایی کمتری دارد، به همان نتیجه می‌رسیم:

SQL> select details.nocmd,articles.titre,details.qte from details
    inner join commandes on details.nocmd=commandes.nocmd
    inner join articles on  details.isbn=articles.isbn
    where commandes.datecmd>'25-sep-91'

در بالا، دو پیوست درونی با جدول [DETAILS] انجام می‌شود:

  • یکی با جدول [COMMANDES] برای دسترسی به تاریخ سفارش یک کتاب
  • یکی مرتبط با جدول [ARTICLES] برای دسترسی به عنوان کتاب سفارش‌داده‌شده

ما همچنین نام مشتری ثبت‌کننده سفارش را می‌خواهیم:

SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
  from commandes,details,articles,clients
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn
  and commandes.idcli=clients.id

Image

ما همچنین تاریخ‌های سفارش و نمایش این تاریخ‌ها به ترتیب نزولی را می‌خواهیم:

SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
    from commandes,details,articles,clients
    where commandes.datecmd>'25-sep-91'
    and details.nocmd=commandes.nocmd
    and details.isbn=articles.isbn
    and commandes.idcli=clients.id
    order by commandes.datecmd descending

Image

در اینجا چند قاعده برای ایجاد پیوندها آورده شده است:

  1. پس از SELECT، ستون‌هایی را که می‌خواهید نمایش دهید، فهرست کنید. اگر یک ستون در چندین جدول وجود داشته باشد، قبل از آن نام جدول را بنویسید.
  2. پس از FROM، تمام جداول را که توسط SELECT پرس‌وجو خواهند شد، فهرست کنید؛ یعنی جداولی که شامل ستون‌های فهرست‌شده پس از SELECT و WHERE هستند.

6.2.2. پیوند خودی

ما می‌خواهیم کتاب‌هایی را پیدا کنیم که قیمت خرده‌فروشی آن‌ها از کتاب «Using SQL» بیشتر باشد:

SQL>select a.titre from articles a, articles b
  where b.titre='Using SQL'
  and a.prixvente>b.prixvente

Image

دو جدول در این پیوند در اینجا یکسان هستند: جدول articles. برای تمایز بین آنها، به آنها نام‌های مستعار می‌دهیم: از مقالات a، مقالات b. نام مستعار برای جدول اول a و برای دومی b است. این نحو را می‌توان حتی زمانی که جداول متفاوت هستند نیز استفاده کرد. هنگام استفاده از نام مستعار، باید در سراسر دستور SELECT به جای جدول مربوطه به کار رود.

6.2.3. پیوند بیرونی

ما می‌خواهیم مشتریان خریدار در ماه سپتامبر را به همراه تاریخ سفارش شناسایی کنیم. سایر مشتریان بدون این تاریخ نمایش داده می‌شوند:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where datecmd between '01-sep-91' and '30-sep-91'

Image

ما در اینجا از اینکه نتیجهٔ صحیح را دریافت نمی‌کنیم، متعجب هستیم. ما باید همه مشتریان را در جدول [CLIENTS] داشته باشیم، که اینطور نیست. وقتی بررسی می‌کنیم که join بیرونی چگونه عمل می‌کند، متوجه می‌شویم مشتریانی که خریدی انجام نداده‌اند با یک سطر خالی در جدول COMMANDES مطابقت داده شده‌اند و در نتیجه با یک تاریخ خالی (مقدار NULL در اصطلاحات SQL). بنابراین این تاریخ شرط تعیین‌شده برای تاریخ را برآورده نمی‌کند و مشتری مربوطه نمایش داده نمی‌شود. بیایید چیز دیگری را امتحان کنیم:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where (commandes.datecmd between '01-sep-91' and '30-sep-91')
        or (commandes.datecmd is null)

Image

این بار، پاسخ صحیح سؤال خود را دریافت می‌کنیم.

6.2.4. پرس‌وجوهای تودرتو

syntaxe
SELECT ستون[s] FROM جدول[s]
WHERE عبارت اپراتور پرس‌وجو
ORDER BY ...
fonctionnement
requête یک فرمان SELECT است که یک مجموعه شامل 0، 1 یا چند مقدار را بازمی‌گرداند. سپس یک شرط WHERE از نوع
عملگر عبارت (val1, val2, ..., vali)
expression و vali باید از یک نوع باشند. اگر پرس‌وجو یک مقدار واحد بازگرداند، این به یک شرط به شکل زیر تقلیل می‌یابد
عملگر عبارت مقدار
که با آن آشنا هستیم. اگر پرس‌وجو یک لیست از مقادیر را بازگرداند، می‌توان از عملگرهای زیر استفاده کرد:
IN
expression IN (val1, val2, ..., vali): درست اگر expression دارای مقداری باشد که یکی از عناصر لیست vali باشد.
NOT IN
معکوس IN
ANY
باید با =, !=, >, >=, <, <= پیش‌رود
expression >= ANY (val1, val2, …, valn): true اگر expression بزرگ‌تر یا مساوی یکی از مقادیر vali در لیست باشد
ALL
باید با =، !=، >، >=، <، <= پیش از آن باشد
expression >= ALL (val1, val2, .., valn): true اگر عبارت >= تمام مقادیر vali در لیست باشد
EXISTS
پرس‌وجو: اگر requête حداقل یک سطر بازگرداند، مقدار true.

مثال‌ها

ما به پرسشی که قبلاً با یک equijoin حل شده است بازمی‌گردیم: نمایش عناوینی که قیمت خرده‌فروشی آن‌ها بالاتر از قیمت کتاب «Using SQL» است.

SQL>select titre from ARTICLES
    where prixvente > (select prixvente from ARTICLES where titre='Using SQL')

Image

این راه‌حل شهودی‌تر از رویکرد equijoin به نظر می‌رسد. ما ابتدا با استفاده از «SELECT» یک فیلتر اولیه انجام می‌دهیم، سپس یک فیلتر دوم روی مجموعه حاصل اعمال می‌کنیم. بدین ترتیب می‌توانیم چندین فیلتر را پشت سر هم اجرا کنیم.

ما می‌خواهیم اوراق بهادارهایی را شناسایی کنیم که قیمت فروش آن‌ها بالاتر از میانگین قیمت فروش است:

SQL> select titre from ARTICLES
    where prixvente > (select avg(prixvente) from ARTICLES)

Image

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

SQL>select distinct idcli from COMMANDES,DETAILS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd

Image

توضیحات

  1. در جدول DETAILS، کدهای ISBN را که در میان کتاب‌هایی با قیمتی بالاتر از میانگین قیمت کتاب یافت می‌شوند، انتخاب کنید.
  2. در سطرهای انتخاب‌شده در مرحلهٔ قبل، کد مشتری IDCLI وجود ندارد. این کد در جدول COMMANDES یافت می‌شود. ارتباط بین دو جدول از طریق شماره سفارش NOCMD است، بنابراین شرط پیوند COMMANDES.nocmd=DETAILS.nocmd.
  3. ممکن است یک مشتری خاص، یکی از کتاب‌های مورد نظر را در چندین نوبت خریداری کرده باشد، در این صورت کد او IDCLI چندین بار ظاهر خواهد شد. برای جلوگیری از این، ما کلید DISTINCT را پس از SELECT قرار می‌دهیم. DISTINCT به طور کلی موارد تکراری را از مجموعه نتایج یک پرس‌وجوی SELECT حذف می‌کند.
  4. برای به‌دست‌آوردن نام مشتری، لازم است یک join مساوی اضافی بین جداول COMMANDES و CLIENTS انجام دهیم، همان‌طور که در پرس‌وجوی زیر نشان داده شده است. 
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd
    and COMMANDES.IDCLI=CLIENTS.ID

Image

مشتریانی را که از ۲۴ سپتامبر سفارشی ثبت نکرده‌اند، پیدا کنید:

SQL>select nom from CLIENTS
    where clients.id not in
    (select distinct commandes.idcli from commandes where datecmd>='24-sep-91')

Image

ما دیده‌ایم که می‌توان سطرها را با استفاده از روش‌هایی غیر از عبارت WHERE فیلتر کرد: با استفاده از عبارت HAVING در ترکیب با عبارات GROUP و BY. بند HAVING گروه‌هایی از سطرها را فیلتر می‌کند.

مانند عبارت WHERE، نحو


     HAVING expression opérateur requête

ممکن است، مشروط بر محدودیت پیش‌گفته‌شده که expression باید یکی از عبارت‌های expri در بند باشد


     GROUP BY expr1, expr2, ...

مثال‌ها

اعداد فروش کتاب‌هایی که ارزششان بیشتر از 200F است، چیست؟

ابتدا، بیایید مقادیر فروش را بر اساس عنوان نمایش دهیم:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre

Image

حالا، عناوین را فیلتر کنیم:

SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre
    having titre in (select titre from ARTICLES where prixvente>200)

Image

شاید واضح‌تر می‌توانستیم بنویسیم:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    and ARTICLES.prixvente>200
    group by titre

Image

6.2.5. پرس‌وجوهای تودرتو

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

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

مثال

بیایید مثالی را در نظر بگیریم که در آن می‌خواهیم نام مشتریانی را که از ۲۴ سپتامبر سفارشی ثبت نکرده‌اند، دریافت کنیم:

SQL> 
select nom from clients
    where not exists
        (select idcli from commandes
            where datecmd>='24-sep-91'
                and commandes.idcli=clients.id)

Image

پرسش والد بر روی جدول clients اجرا می‌شود. پرسش فرزند یک پیوند بین جدول‌های clients و commandes برقرار می‌کند. بنابراین این یک پرس‌وجوی مرتبط است. برای هر سطر در جدول clients، پرس‌وجوی فرزند اجرا می‌شود: این پرس‌وجو کد مشتری id را در سفارش‌های ثبت‌شده پس از ۲۴ سپتامبر جستجو می‌کند. اگر هیچ‌کدام را پیدا نکند (NOT EXISTS)، نام مشتری نمایش داده می‌شود. سپس به ردیف بعدی در جدول clients می‌رویم.

6.2.6. معیارهای انتخاب برای نوشتن SELECT

ما در چندین مورد دیده‌ایم که می‌توان با استفاده از پرس‌وجوهای مختلف برای جدول SELECT به نتیجه یکسانی دست یافت. بیایید یک مثال بزنیم: نمایش مشتریانی که سفارش داده‌اند:

پیوست

SQL>
select distinct nom from clients,commandes
    where clients.id=commandes.idcli

Image

پرس‌وجوهای تودرتو

SQL> 
select nom from clients
    where id in (select idcli from commandes)

نتیجه یکسانی می‌دهد.

پرس‌وجوهای همبسته

SQL>
select nom from clients
    where exists (select * from commandes where commandes.idcli=clients.id)

نتیجه یکسان می‌دهد.

نویسندگان کریستین MAREE و گای LEDANT، در کتاب خود «SQL: مقدمه‌ای، برنامه‌نویسی و تسلط»، تعدادی معیار انتخاب را پیشنهاد می‌کنند:

عملکرد

کاربر نمی‌داند که SGBD چگونه «مدیریت» می‌کند تا نتایج مورد نظرشان را پیدا کند. بنابراین تنها از طریق تجربه است که آنها خواهند فهمید یک ساختار پرس‌وجو بهتر از دیگری عمل می‌کند. MAREE و LEDANT بر اساس تجربه خود بیان می‌کنند که پرس‌وجوهای مرتبط عموماً کندتر از پرس‌وجوهای تو در تو یا الحاق‌ها به نظر می‌رسند.

فرمول‌بندی

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

  • جدول‌هایی که حاوی ستون‌های آرگومان SELECT (SELECT col1, col2, ...) هستند، باید پس از کلمه کلیدی FROM فهرست شوند. سپس حاصلضرب کارتزین این جدول‌ها انجام می‌شود که به آن «جون» (join) گفته می‌شود.
  • وقتی یک پرس‌وجو نتایج را از یک جدول واحد برمی‌گرداند و فیلتر کردن سطرهای آن جدول نیازمند مراجعه به جدول دیگری است، می‌توان از پرس‌وجوهای تو در تو استفاده کرد.

6.3. افزایش‌های نحوی

برای سهولت، ما عمدتاً نحوهای مخفف‌شده را برای دستورات مختلف ارائه کرده‌ایم. در این بخش، نحوهای کامل‌شده آنها را ارائه می‌دهیم. این نحوها خودگویا هستند، زیرا مشابه نحوهای دستور SELECT می‌باشند که به تفصیل پوشش داده شده است.

INSERT

syntaxe1
INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...)
syntaxe2
INSERT INTO table (col1, col2, ..) (requête)
explication
این دو نحوی ارائه شده‌اند

DELETE

syntaxe1
DELETE FROM table WHERE condition
explication
این نحو شناسایی می‌شود. باید توجه داشت که شرط می‌تواند شامل یک پرس‌وجو با استفاده از عملگر عبارت WHERE باشد.

UPDATE

syntaxe1
UPDATE table
SET col1=expr1, col2=expr2, ...
WHERE condition
explication
این نحو قبلاً توضیح داده شده است. باید توجه داشت که شرط می‌تواند شامل یک پرس‌وجو با استفاده از عملگر عبارت WHERE باشد.
syntaxe2
UPDATE table
SET (col1, col2, ...) = query1, (cola, colb, ...) = query2, ...
WHERE condition
explication
مقادیر اختصاص‌یافته به ستون‌های مختلف ممکن است از یک پرس‌وجو بیایند.
https://stahe.github.io
این آموزش دوره, نوشته شده توسط سرگئه تاه, تحت شرایط مجوز Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported در دسترس عموم قرار گرفته است.