آموزش دستورات SQL

برای آموزش دستورات SQL نیاز به یک مثال واقعی داریم. بدین منظور با دیتابیس university که شامل جداول زیر است کار می‌کنیم.
این دیتابیس شامل 5 جدول student (دانشجو)، clg(دانشکده یا بخش)، course (درس)، prof (استاد) و sec(گروه) است.

Student (s# , sname , city , clg#)
Clg(clg#, clgname , prof# , city)
Course(crs# , cname , unit , clg#)
Prof(prof# , pname , degree , office)
Sec(s# , crs#, prof# , term , score)

در جدول دانشجو اطلاعاتی نظیر شماره دانشجو ، نام دانشجو، شهر دانشجو و کد دانشکده وجود دارد.
در جدول دانشکده اطلاعاتی نظیر شماره دانشکده، نام دانشکده، کد رییس دانشکده و شهر مکان دانشکده وجود دارد.
در جدول course اطلاعاتی نظیر کد درس، نام درس، تعداد واحد درس، کد دانشکده ای که این درس را ارائه کرده است وجود دارد.
در جدول prof اطلاعاتی نظیر کد استاد، نام استاد، مدرک استاد و اتاق کار استاد وجود دارد.
در جدول sec که مهم‌ترین جدول دیتابیس است مشخص می‌شود که چه دانشجویی در چه درسی و با چه استادی در چه ترمی چه نمره ای گرفته است!

1- دستور SELECT

دستور SELECT برای انتخاب و استخراج اطلاعات مورد نظر از يک يا چند جدول استفاده می شود . قالب استفاده از این دستور به صورت زیر است:

SELECT   column_name(s)
FROM   table_name

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

SELECT  s#, sname
FROM   student

چناچه در صورت سوال خواسته شده بود که همه اطلاعات دانشجو را نمایش دهید یا باید همه ستونها را در دستور select لیست کنید یا به صورت زیر از * استفاده کنید. * به معنی همه است:

SELECT  *
FROM   student

2- دستور WHERE

برای اضافه کردن شرط يا شرط هايی جهت محدود کردن نتایج جستجو از دستور Where می‌کنیم . اين ذستور بايد پس از دستور Select در کد قرار گیرد:

SELECT   column_name(s)
FROM   table_name
WHERE  condition(s)

مثال 2- پرس‌وجویی بنویسید که نام دروس 3 واحدی را نمایش دهد.

SELECT   cname
FROM   course
WHERE  unit=3

با استفاده از عملگرهای AND ، OR و پرانتز می توان چندين شرط را با هم ترکيب کرد .خروجی برنامه با شرط هایی که روی دستور داده شده است مطابقت داده خواهد شد. 

3- دستور ORDER BY

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

SELECT   column_name(s)
FROM   table_name
WHERE  condition(s)
ORDER BY column_name(s) [ASC | DESC]

ASC به معنای صعودی بودن (a to z)و DESC به معنای نزولی بودن است (z to a).پیش فرضش ACS است.

چنانچه لازم باشد مرتب سازی بر مبنای بیش از یک ستون انجام گیرد بخش مرتب سازی کد بالا باید بصورت زیر نوشته می‌شود:

    ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

توضیح خط بالا در قالب مثال زیر ارائه میکنیم.

مثال 3 – پرس‌وجویی بنویسید که نمرات دانشجویان به همراه شماره دانشجوییشان را به ترتیب نزولی مرتب کند.

select s# , score
from sec
order by score desc

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

select s# , score
from sec
order by score desc, s# asc

در کد بالا اولویت اول بر روی مرتب سازی نمرات است و چنانچه نمرات یکسان باشد آن نمرات را بر اساس شماره دانشجویی مرتب می‌کند.

4- عملگر IN و NOT IN

ساختار زیر نحوه استفاده از عملگر IN را نشان می‌دهد:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

همانطور که از کد پیداست در خروجی رکوردهایی ظاهر می‌شود که در شرط where صدق می‌کنند. در این شرط مشخص شده که اگر مقدار column_name برابر با یکی از مقادیر لیست باشد رکورد متناظر در خروجی نمایش داده شود. مثال زیر این موضوع را ساده‌تر نمایش می‌دهد.

مثال 4- پرس‌وجویی بنویسید که اطلاعات دانشجویانی با نام رضایی ، محمودی و یوسفی را در خروجی نمایش دهد.

select *
from student
where sname in ("رضایی","محمودی ","یوسفی")

تا اینجا با مثالهایی روبرو شدیم که فقط به یک جدول نیاز داشت. با استفاده از عملگر in می‌توان چند جدول را به هم پیوند زد. مثلا فرض کنید بخواهیم اطلاعات دانشجویان رشته کامپیوتر را در خروجی نمایش دهیم. اطلاعات دانشجویان در جدول student قراردارد و clgname (که بر اساس آن باید رشته کامپیوتر را فیلتر کنیم) در جدول clg قرار دارد. مثال زیر را به دقت دنبال کنید تا با مفهوم پیوند جدول با استفاده از عملگر in آشنا شوید.

مثال 5- پرس‌وجویی بنویسید که اطلاعات دانشجویان رشته کامپیوتر را در خروجی نمایش دهد.

 select *
 from student
 where clg# in (select clg#
         from clg
         where clgname = 'کامپیوتر')

به دستور سِلکتی که در پرانتز نوشته شده است (خط 4 تا 6) اصطلاحا sub-query یا زیر پرس‌وجو می‌گویند. اگر بخواهیم به صورت دقیق پیمایش این پرس‌وجو را شرح دهیم به صورت زیر خواهد بود:

برای پرس‌وجوی بالا، کامپایلر sql در جدول student سطر به سطر (رکورد به رکورد) حرکت میکند و سطرهایی که در شرط where صدق می‌کنند در خروجی نمایش می‌دهد. به عبارت دقیق‌تر به ازای هر سطر جدول student زیر پرس‌وجو (یا همان sub-query) اجرا می‌شود و شماره دانشکده (#clg) رشته علوم کامپیوتر را برمی‌گرداند. بنابراین اگر جدول student صد سطر داشته باشد صد بار زیر پرس و جو اجرا می‌شود و مقدار #clg را برمیگرداند.

نکته مهم در زیر پرس‌وجو ها این است که از select داخلی (خط 4 تا 6 در این مثال) به ستونهای جدول بالایی ( در این مثال student) دسترسی داریم ولی بر عکس آن نه! با دو مثال زیر این موضوع را بهتر توضیح می‌دهیم:

select *
from student
where clg# in (select clg#
		from clg
		where clgname = 'کامپیوتر' and student.city='شیراز')

همانطور که در کد بالا می‌بینید در زیرپرس‌وجو شرط student.city=’شیراز’ داریم. همانطور که قبلا گفتیم کامپایلر به ازای هر سطر student دستورات 4 تا 6 را اجرا می‌کند. پس در هر اجرا سطری از جدول student موجود است و بنابراین به student.city دسترسی داریم. در ادامه کد اشتباه زیر را با هم بررسی میکنیم:

select clgname
from student
where clg# in (select clg#
		from clg
		where clgname = 'کامپیوتر') 

در کد بالا میخواهیم clgname را در خروجی نمایش دهیم که کاری اشتباه است. زیرا جدول اصلی که با آن کار میکنیم جدول student است و همانطور که گفتیم از Select بالا به ستونهای select پایینی (زیر پرس‌وجو) دسترسی نداریم.

مثال 6- پرس‌وجویی بنویسید که نام دانشجویانی که نمرات بالای 17 دارند را در خروجی نمایش دهد.

select sname
from student
where s# in (select s#
		from sec
		where score>17)

5- دستور GROUP BY

با استفاده از این عملگر می‌توان نتایج را بر اساس یک یا چند فیلد گروه‌بندی کرد. به همراه group by میتوان از توابع تجمعی مانند count, sum, avg, max, min استفاده کرد. در مثال ساده زیر این موارد را توضیح می‌دهیم.

مثال 7 – پرس‌وجویی بنویسید که معدل دانشجویان را در خروجی نمایش دهد.

نمرات دانشجویان در جدول sec قرار دارد. بنابراین برای محاسبه نمره باید از این جدول استفاده کرد. برای اینکه بهتر بتوانیم مثال را دنبال کنیم جدول زیر را در نطر بگیرید:

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

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

select s#,avg(score)
from sec
group by s#

در کد بالا جدول Sec بر اساس #s گروه بندی شده است و معدل هر گروه با استفاده از تابع avg محاسبه می‌شود و در خروجی جدول زیر نمایش داده می‌شود:

نکات مهم مربوط به گروه‌بندی:

1- هر صفتی که در گروه‌بندی ظاهر می‌شود حتما باید در select ظاهر شود. (مانند #s در این مثال)
2- هر صفتی غیر از صفت گروه بندی بخواهد در select ظاهر شود حتما باید با توابع تجمعی باشد. (مانند score که در این مثال با avg همراه شده است)
3- توابع تجمعی را در شرط where استفاده نمی‌کنند ولی در شرط having که در ادامه توضیح داده خواهد شد می‌توان استفاده کرد.

دستور شرطی HAVING

می‌توان روی گروهها شرط گذاشت. به این معنی که می‌توان گروههایی که در شرط خاصی صدق می‌کنند در خروجی نمایش داد. با مثالی این دستور را توضیح می‌دهیم.

مثال 8- پرس‌وجویی بنویسید که معدل دانشجویانی که معدل آنها بالای 17 هستند را نمایش دهد.

select s#,avg(score)
from sec
group by s#
havin avg(score)>17

6- دستور INNER JOIN

با استفاده از این دستور می‌توان دو جدول را به هم پیوند داد. فرض کنید شما به دنبال نام دانشجویانی هستید که در دانشکده کامپیوتر تحصیل می‌کنند . برای این پرس‌وجو به دو جدول نیاز است، یکی student (زیرا به فیلد sname نیاز داریم) و دیگری clg (چون به clgname نیاز داریم). برای اینکه بهتر بحث را جلو ببریم با دو جدول student و clg ادامه می‌دهیم:

پیوند جدول student و clg به این معنی است که هر سطر از جدول student در کنار هر سطر از جدول clg قرار بگیرد. پس پیوند دو جدول جدولی خواهد بود با 5 ستون (ستوهای جدول student + ستون‌های جدول clg) و 6 سطر به صورت زیر:

از سطر اول جدول بالا می‌توان نتیجه گرفت که Ali متعلق به رشته کامپیوتر است ولی از سطر دوم همانطور که پیداست استنباط خاصی نمی‌توان کرد. در این مثالی که بررسی می‌کنیم سطرهایی که #clg آنها با رنگ قرمز مشخص شده اطلاعات بی ربط در کنار هم قرار گرفته اند (چرا؟). بنابراین در پیوند جداول حتما باید بر روی یک یا چند ستون مشخص و با معنی این کار را انجام داد. مثلا در این مثال باید سطرهایی از جدول student در کتار سطرهایی از جدول clg قرار گیرند که #clg آنها با هم برابر باشند:

قالب زیر پیوند دو جدول را نشان می‌دهد:

SELECT *
FROM Table1 INNER JOIN Table2
ON condition(s)

مثال 9- پرس‌وجویی بنویسید که نام دانشجویان رشته کامپیوتر را در خروجی نمایش دهد.

select sname
from student inner join clg
on student.clg# = clg.clg#

مثال 10- پرس‌وجویی بنویسید که نام اساتیدی که درس پایگاه داده را تدریس کرده‌اند را در خروجی نمایش دهد

در این مثال به سه جدول نیاز داریم: prof (به pname نیاز داریم) ، course (به cname نیاز داریم) و sec (دروس ارائه شده). برای پیوند بین سه جدول نیاز است ابتدا دو جدول را پیوند بزنیم , و جدول حاصل را با جدول سوم پیوند می‌زنیم:

select pname 
from prof inner join (select  *
			from sec inner join course
			on sec.crs# = course.crs# 
				and cname = 'پایگاه داده') as table1
on prof.prof# = table1.prof#

در پرس‌وجوی بالا، در خط 2 تا 5 دو جدول course و sec با هم پیوند زده شده اند و با استفاده از کلمه کلیدی as یک نام برای جدول حاصل انتخاب شده است (table1) بنابراین به طور دقیق‌تر می‌توان گفت که در خط 2 جدول prof و جدول table1 بر روی #prof مشترک پیوند خورده‌اند.