‌پرکاربردترین دستورات SQL که توسعه‌دهندگان به آن‌ها نیاز دارد

پیش‌درآمدی بر این مطلب

‌مثال‌هایی که در این مطلب مشاهده می‌کنید بر مبنای چهار جدول ساده نگارش شده‌اند. توضیح این جداول به شرح زیر است:

جدول مشتریان (customer) شامل نام و سن مشتریان است (شکل 1).

جدول قد (heights) شامل نام و قد افراد است (شکل 2).

جدول کارکنان مشابه جدول مشتریان نام و سن کارکنان را شامل می‌شود (شکل 3).

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

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

1. دستور Select

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

SELECT * FROM table;

‌دستور فوق از دو بخش تشکیل شده است. بخش اول SELECT * نشان می‌دهد قصد انتخاب چه ستون‌هایی را داریم. در مثال فوق کاراکتر ستاره به این معنا است که هدف ما تمامی ستون‌های جدول است. بخش دوم FROM table به جدولی درون بانک‌اطلاعاتی که قصد واکاوی اطلاعات آن‌را داریم اشاره دارد. در مثال فوق به‌جای واژه table باید نام جدولی جایگزین شود که قرار است رکوردهای اطلاعاتی آن استخراج شود. مثال فوق به‌نام دستور انتخاب ستاره (select star) نیز معروف است. کاراکتر * برای مشاهده تمامی رکوردهای درون یک جدول خوب است، اما در برنامه‌های واقعی کمتر از آن استفاده می‌شود، زیرا هیچ مکانیزم کنترلی روی داده‌های بازگشتی وجود ندارد. زمانی که دستور انتخاب ستاره‌ای را استفاده می‌کنید، بانک‌اطلاعاتی تمامی رکوردهای ذخیره شده در جدول هدف را نشان می‌دهد. دستور فوق را می‌‌توان کمی ویرایش کرده و به شکل کارآمدتری استفاده کرد. ترکیب نحوی زیر این موضوع را نشان می‌دهد:

SELECT age, name FROM people;

‌در محاوره بالا رکوردهای درون ستون‌های age و name از جدول people برگردانده می‌شوند. دستور Select هرچه دقیق‌تر و جامع‌تر استفاده شود، نتایج خلاصه‌تری را باز گردانده و مانع بروز مشکلات مختلف می‌شود. همچنین توسعه‌دهندگان به سادگی ترکیب نحوی محاوره‌ها را درک می‌کنند. اگر به دنبال انتخاب رکورد داده‌ای خاصی هستید کافی است از ترکیب نحوی مشابه حالت زیر استفاده کنید:

SELECT age, '1234' FROM people;

2. فرمان WHERE

‌فرمان SELECT برای واکشی رکوردها عالی است، اما اگر به دنبال درج فیلتری روی نتایج باشیم باید به فکر راهکار کارآمدتری باشیم. به‌طور مثال، اگر در جست‌وجوی افرادی هستید که سن مشخصی دارند، باید از یک الگوی کارآمد استفاده کنید. این درست همان نقطه‌ای است که دستور WHERE کاربرد پیدا می‌کند. دستور فوق شروطی را برای دستور SELECT مشخص می‌کند. WHERE در انتهای دستور SELECT قرار می‌گیرد. ترکیب نحوی به‌کارگیری  WHERE به شرح زیر است:

SELECT age, name FROM people WHERE age > 10;

‌‌عملگر AND

محاوره بالا محدود به افرادی است که سن بیشتر از 10 سال دارند. عملگر AND  اجازه می‌دهد چند شرط را با یکدیگر ترکیب کرد.

SELECT age, name FROM people WHERE age > AND age < 20;

‌دستور AND کارکردی مشابه با الگویی دارد که در زبان محاوره از آن استفاده می‌کنیم. این دستور شرط دیگری را به عبارت ما اضافه می‌کند. در مثال بالا، تنها رکوردهای مربوط به افرادی که سن آن‌ها در بازه 10 تا 20 سال قرار دارد بازیابی می‌شود. با توجه به این‌که جدول ما فاقد چنین افرادی است، هیچ رکورد داده‌ای برگردانده نمی‌شود.

عملگر OR

‌‌عملگر دیگر قابل استفاده با فرمان WHERE عملگرد OR است. نحوه به‌کارگیری عملگر فوق در ترکیب نحوی زیر نشان داده شده است:

SELECT age, name FROM people WHERE age > 10 OR name = 'Joe';

عملگر OR به معنای یا است. مثال بالا رکوردهایی را باز می‌گرداند که در آن‌ها سن افراد بیشتر از 10 سال یا نام آن‌ها Joe است. دقت کنید در ترکیب نحوی بالا از یک علامت مساوی استفاده شده، اما در برخی از زبان‌ها باید از دو علامت مساوی (= =) استفاده کرد.

3. دستور ORDER

‌ORDER در ترکیب با دستور SELECT و برای مرتب‌سازی نتایج بازیابی شده استفاده می‌شود. ترکیب نحوی این دستور ساده بوده و انتهای دستور SELECT اضافه می‌شود.

SELECT name, age FROM people ORDER BY age DESC;

‌در دستور ORDER از کلیدواژه ASC برای مرتب‌سازی صعودی و DESC برای مرتب‌سازی نزولی استفاده می‌شود. همچنین این امکان وجود دارد تا فرآیند مرتب‌سازی بر مبنای چند ستون انجام شود. ترکیب نحوی زیر این موضوع را نشان می‌دهد.

SELECT name, age FROM people ORDER BY name ASC, age DESC

‌عبارت ORDER BY زمانی که با سایر دستورات ترکیب شود عملکرد مفید خود را نشان می‌دهد.

4. Join

‌Join برای متصل کردن (الحاق) داده‌های مرتبط که در چند جدول ذخیره‌سازی شده‌اند استفاده می‌شود. دستور فوق اجازه می‌دهد جدول دوم به جدول اول مرتبط شود و به این شکل ارتباط میان داده‌ها را مشخص کرد. ترکیب نحوی دستور JOIN به شرح زیر است:

SELECT age, name, height FROM people LEFT JOIN heights USING (name);

‌در زمان به‌کارگیری دستور فوق باید به چند نکته مهم دقت کنید. در زمان به‌کارگیری دستور فوق باید به تنظیماتLEFT JOIN  دقت کنید. مثال فوق اعلام می‌دارد که فرآیند اتصال (الحاق) از نوع اتصال چپ است. در ادامه جدولی که قرار است به آن فرآیند الحاق (heights) انجام دهیم را مشخص می‌کنیم. به‌کارگیری عبارت USING (name) در پایان دستور فوق اعلام می‌دارد که ستون name در هر دو جدول وجود دارد و باید به عنوان کلیدی برای اتصال جداول استفاده شود. اگر دو ستونی که قرار است به عنوان کلید اتصال استفاده شوند، نام‌های مختلفی در جداول دارند به جای عبارت USING از ON مشابه با ترکیب نحوی زیر استفاده می‌کنیم:

SELECT age, name, height FROM people LEFT JOIN heights ON (namea = nameb);

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

• INNER JOIN: ردیف‌هایی منطبق هر دو جدول را بر می‌گرداند.
• LEFT OUTER JOIN: تمامی ردیف‌های جدول چپ و ردیف‌هایی از جدول راست که منطبق هستند را بر می‌گرداند. اگر هیچ انطباقی پیدا نشود، رکوردهای جدول چپ را برمی‌گرداند.
• RIGHT OUTER JOIN: عملکردی برعکس حالت قبل دارد و تمامی جداول راست و ردیف‌هایی از جدول چپ که منطبق هستند را برمی‌گرداند.
• FULL OUTER JOIN: رکوردهایی که در هر جدول منطبق هستند را بر می‌گرداند.

دقت کنید به‌کارگیری واژگان INNER و OUTER اختیاری است و بیشتر برای فهم ساده‌تر محاوره‌ها استفاده می‌شوند.

5. فرمان Alias

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

SELECT A.Age FROM people A;

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

SELECT people.Age FROM people;

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

SELECT staff.Age, staff.Name, customers.Age, customers.Name FROM staff, customers;

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

SELECT A.Age, A.Name, B.Age, B.Name FROM staff A, customers B;

‌این مرتبه به جای نام جدول staff از نام مستعار A و جدول customers از نام مستعار B استفاده کرده‌ایم. همان‌گونه که مشاهده می‌کنید کدنویسی به این شیوه ساده‌تر و قابل فهم‌تر است. برای مستعارسازی نام ستون‌ها نیز می‌توان از کلیدواژه AS استفاده کرد. ترکیب نحوی انجام این‌کار به شرح زیر است:

 SELECT age AS person_age FROM people;

‌6. دستور Union

Union یکی از پر کاربردترین و مهم‌ترین دستورات SQL است. دستور فوق اجازه می‌دهد ردیف‌های مختلف را با یکدیگر ترکیب کنید. برعکس دستور JOINS که ستون‌ها را به یکدیگر مرتبط می‌کند، دستور UNION ردیف‌های غیر مرتبط را به شرط این‌که تعداد و نام ستون یکسانی موجود باشد به یکدیگر متصل می‌کند. ترکیب نحوی دستور فوق به شرح زیر است:

SELECT age, name FROM customers UNION SELECT age, name FROM staff;

‌دستور UNION در حقیقت ترکیب نتایج دو محاوره است. توسعه‌دهندگان می‌توانند از دستور UNION ALL برای بازیابی تمامی داده‌ها، صرف‌نظر از تکراری بودن آن‌ها استفاده کنند. ترکیب نحوی این‌کار به شرح زیر است:

SELECT age, name FROM customers UNION ALL SELECT age, name FROM staff;

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

7. فرمان Insert

اکنون که روش‌های بازیابی رکوردها از بانک‌های اطلاعاتی را بررسی کردیم، نوبت به اضافه کردن رکوردها به پایگاه داده فرا رسیده است. فرمان INSERT برای این منظور در نظر گرفته شده است. ترکیب نحوی دستور فوق به شرح زیر است:

INSERT INTO people(name, age) VALUES('Joe', 102);

‌در دستور فوق نام جدول people و ستون‌هایی که تغییراتی در آن‌ها اعمال می‌شود name و age هستند. در ادامه کلمه کلیدی VALUES مقادیری که باید در این ستون‌ها درج شوند را مشخص می‌کند. مقادیر وارد شده در این بخش باید به ترتیب ستون‌ها وارد شده و هماهنگ با نوع داده‌ای ستون‌ها باشند. دقت کنید در فرمان INSERT نمی‌توان از کلیدواژه WHERE استفاده کرد، به همین دلیل پیش از درج رکوردهای جدید باید نکات مختلف به دقت رعایت شوند.

8. دستور Update

‌در هر بانک‌اطلاعاتی لازم است تغییراتی در رکوردهای اطلاعاتی به وجود آید. دستور UPDATE برای این منظور در نظر گرفته شده است. ترکیب نحوی دستور فوق به شرح زیر است:

UPDATE people SET name = 'Joe', age = 101;

‌‌در دستور UPDATE باید نام جدولی که قرار است تغییرات در آن اعمال شود مشخص شود. در ادامه از کلیدواژه SET برای تعیین ستون‌ها و مقادیری که قرار است جایگزین مقادیر قبلی شوند استفاده می‌کنیم. در دستور UPDATE امکان به‌کارگیری کلیدواژه WHERE مشابه با فرمان SELECT وجود دارد. ترکیب نحوی انجام این‌کار به شرح زیر است:

UPDATE people SET name = 'Joe', age = 101 WHERE name = 'James';

‌در صورت نیاز توسعه‌دهندگان می‌توانند از عملگرهای AND و OR برای اعمال شروط استفاده کنند:

UPDATE people SET name = 'Joe', age = 101 WHERE (name = 'James' AND age = 100) OR name = 'Ryan';

‌9. دستور Upsert

یکی دیگر از دستورات کاربردی SQL که در حقیقت ترکیبی از دستور اضافه و به‌روزرسانی است، Upsert نام دارد. به‌طور مثال، فرض کنید محدودیتی روی یک جدول قرار داده‌ایم و مشخص کرده‌ایم تنها به دنبال رکوردهایی با نام منحصر به‌فرد هستیم. به بیان دیگر، در نظر نداریم هیچ دو ردیفی نام یکسان داشته باشند. در این حالت اگر سعی کنیم چند مقدار با نام Joe وارد کنیم، بانک‌اطلاعاتی پیغام خطایی تولید کرده و اجازه انجام این‌کار را نمی‌دهد. دستور UPSERT اجازه به‌روزرسانی در شرایطی که رکورد از قبل وجود داشته باشد را می‌دهد. اگر دستور فوق وجود نداشت، ابتدا باید بررسی می‌کردیم که رکورد موردنظر وجود دارد یا خیر و اگر وجود داشت از دستور insert استفاده کرده یا از کلید اصلی برای بازیابی و به‌روزرسانی استفاده می‌کردیم. دستور Upsert در بانک‌های اطلاعاتی به روش‌های مختلف پیاده‌سازی می‌شود. روش استفاده از دستور فوق در زبان MySQL به شرح زیر است:

INSERT INTO people(name, age) VALUES('Joe', 101) ON DUPLICATE KEY UPDATE age = 101;

‌همان‌گونه که مشاهده می‌کنید محاوره بالا دو دستور update و insert را ترکیب کرده و اعلام می‌دارد اگر قادر به اضافه کردن نیستی، آن‌را به‌روز کن.

10. دستور DELETE

‌دستور DELETE رکورد یا رکوردهایی از یک جدول را حذف می‌کند. ترکیب نحوی دستور فوق به شرح زیر است:

DELETE FROM people;

‌برای آن‌که دستور فوق تمامی رکوردها را پاک نکند باید از فیلترهایی برای مشخص کردن رکوردهای خاص استفاده کنیم. کلیدواژه WHERE در انتهای دستور فوق برای این منظور استفاده می‌شود:

DELETE FROM people WHERE name = 'Joe';

‌‌11. دستور Create Table

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

CREATE TABLE people (name TEXT, age, INTEGER, PRIMARY KEY(name));

‌ترکیب نحوی دستور Create Table به این صورت است که نام ستون‌ها و قیدهای مربوطه در پرانتز قرار می‌گیرد و در ادامه نوع داده‌ ستون‌ها مشخص می‌شود. در انتهای دستور کلید اصلی مشخص می‌شود. 

12. دستور ALTER TABLE

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

ALTER TABLE people ADD height integer;

‌در مثال بالا ستونی به‌نام height از نوع مقدار صحیح (integer) به جدول اضافه می‌شود.

13. دستور DROP TABLE

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

DROP TABLE people;

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