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

دسته‌بندی:
یک دیدگاه

برای آموزش دستورات SQL نیاز به یک مثال واقعی داریم. بدین منظور با دیتابیس university که شامل جداول زیر است کار می‌کنیم.
این دیتابیس شامل ۵ جدول 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 که مهم‌ترین جدول دیتابیس است مشخص می‌شود که چه دانشجویی در چه درسی و با چه استادی در چه ترمی چه نمره ای گرفته است!

۱- دستور SELECT

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

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

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

۲- دستور WHERE

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

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

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

۳- دستور ORDER BY

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

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

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

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

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

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

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

۴- عملگر IN و NOT IN

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

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

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

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

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

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

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

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

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

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

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

۵- دستور GROUP BY

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

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

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

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

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

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

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

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

دستور شرطی HAVING

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

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

۶- دستور INNER JOIN

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

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

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

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

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

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

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

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

  • نویسنده
    حمید جهانگیری
  • تعداد بازدید
    1,248
۱دیدگاه فرستاده شده است.
شما هم دیدگاه خود را بنویسید
نوشته‌های ویژه
اخبار ویژه

با عضویت در خبرنامه، تازه‌ترین نوشته‌های وبلاگ را در ایمیل‌تان دریافت کنید.
برای عضویت نشانی ایمیل خود را وارد کرده و بر روی دکمه عضویت کلیک نمایید.