آموزش SQL - دستورات پيشرفته SQL

دستورات پيشرفته SQL

پيوند جدول ها :

تا اين قسمت تمام مثال ها و مسئله هايی که در SQL به آنها پاسخ داديم ، مسئله هايی بودند که اطلاعات ما فقط از يک جدول استخراج می شد . اما در برنامه نويسی واقعی پايگاه داده ها ، ما مجبور هستيم که اطلاعات خود را از بيش از يک جدول استخراج کنيم . در اين حالت ما ابتدا بايد جدول هايی که می خواهيم اطلاعات را از آنها استخراج کنيم ، با هم پيوند دهيم . هدف از ايجاد اين ارتباط تلفيق اطلاعات در جدول ها و چاپ اطلاعات مورد نظر در خروجی است .

مفاهيم اوليه :

برای پيوند دادن جدول ها ابتدا بايد چند مفهوم زير را بشناسيم :

کليد اصلی : فيلد کليد اصلی در يک جدول ، فيلدی است که شرايط زير را داشته باشد :
1. مقدار آن برای هر نمونه رکورد ( سطر ) منحصر به فرد و غير تکراری باشد . به عبارت ديگر هيچ 2 رکوردی در يک جدول در اين فيلد مقدار يکسان نداشته باشد . کليد اصلی وجه تمايز 2 نمونه رکورد مختلف در يک جدول است .
2. طول مقادير آن حدامکان کوتاه باشد .
نکته : يک جدول می تواند بيش از يک کليد اصلی داشته باشد .
مثال : فيلد شماره دانشجويی در جدول Student کليد اصلی است . هيچ دو دانشجويی نمی توانند دارای شماره دانشجويی يکسان باشند .
کليد خارجی : کليد خارجی ، فيلدی است که در يک جدول کليد اصلی و در جدول ديگر به تنهايی کليد اصلی نباشد . از کليد خارجی برای ارتباط يک به چند 2 جدول با هم استفاده می شود .

شرط ارتباط 2 جدول :

برای ارتباط بين جدول ها بايد شرط های زير برقرار باشد . بايد قبل از طراحی پايگاه داده و جدول های آن موارد زير را جهت ارتباط جدول های مورد نظر رعايت کرد .

وجود فيلد مشترک دقيقا از يک نوع و يک سايز .
فيلد مشترک در يکی از جدول ها کليد اصلی و در جدول ديگر کليد خارجی باشد .

معرفی 2 جدول ديگر :

از اين به بعد ما در مثال های خود از 2 جدول ديگر به غير از جدول Student ، به نام های Courses ( درس ها ) و Selection ( انتخاب واحد ) به شرح زير استفاده می کنيم :

Courses Table
Course ID	Co Title	Credit	Co Type
کد درس ( کليد اصلی )	عنوان درس	تعداد واحد	نوع درس

Selection Table
Student ID	Course ID	Term	Year	Grade
شماره دانشجويی ( کليد اصلی خارجی )	کد درس ( کليد اصلی خارجی )	ترم تحصيلی	سال تحصيلی	نمره

نکته مهم : در تمام مثال های قبلی ، ما در دستور Select فقط نام ستون ها را به تنهايی ذکر می کرديم ، زيرا در آن زمان ، اطلاعات ما فقط از يک جدول استخراج می شد . اما در هنگام پيوند دو جدول و استفاده از چند جدول در دستور Select بايد نام ستون را به همراه نام جدول مربوط به آن ذکر کرد . اين کار 2 دليل اصلی دارد :

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

شکل کلی اين دستور به صورت زير است :

نام ستون . نام جدول

مثال : انتخاب ستون StudedntID از جدول Student :
Student.StudentID

مثال های پيوند جدول ها :

در اين قسمت با ارائه چندين مثال ، انواع حالت های مختلف پيوند جدول ها را بررسی می کنيم . از داده های موجود در جداول زير برای مثال ها استفاده می کنيم :
توجه : جدول انتخاب واحد نشان دهنده اين است که هر دانشجو چه واحدهای درسی را در چه ترم و سال و با چه نمره ای گذارنده است .

Student Table
Student ID	Name	Family	Major	City	Grade
41252214	Ahmad	Rezaee	Hard Ware	Tehran	18
10724113	Ehsan	Amiri	Soft Ware	Karaj	14
10254861	Zahra	Hosini	Hard Ware	Tehran	17
27365187	Sahar	Ahmadi	Soft Ware	Bam	16
35654415	Hesam	Razavi	Soft Ware	Tehran	19

Courses Table
Course ID	Co Title	Credit	Co Type
1011	پايگاه داده	3	عملی
1012	مباحث ويژه	3	عملی
1013	زبان تخصصی	2	نطری

Selection Table
Student ID	Course ID	Term	Year	Grade
41252214	1011	2	85 - 86	16
10724113	1011	2	85 - 86	14
41252214	1012	1	85 - 86	17
10724113	1012	1	85 - 86	11
10254861	1013	2	85 - 86	13
10254861	1011	2	84 - 85	8
27365187	1012	1	84 - 85	19
27365187	1013	1	84 - 85	16
35654415	1011	2	84 - 85	9
35654415	1013	2	84 - 85	17

شکل کلی پيوند 2 جدول برای استخراج اطلاعات به صورت زير است :

Select نام ستون های مورد نظر برای نمايش
From نام جدول ها
where برابر قرار دادن فيلدهای مشترک 2 جدول
And بقيه شرط های مورد نظر ;

در اين حالت ابتدا در دستور Select نام ستون هايی که از 2 جدول می خواهيم نمايش دهيم را تعيين می کنيم . سپس نام 2 جدول را در مقابل دستور From نوشته و در اولين شرط دستور Where نام فيلد مشترک را از هر 2 جدول نوشته و آنها را برابر هم قرار می دهيم . اين شرط ، شرط برقراری پيوند و تلفيق اطلاعات 2 جدول است . در ادامه هم می توان شرط های ديگری را برای استخراج اطلاعات تعيين کرد . در مثال های زير اين مسئله را بررسی می کنيم :

مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که در ترم 1 سال تحصيلی 85 - 86 ، درس با کد 1012 را انتخاب کرده اند :

Select Students.Name , Students.Family , Selection.Term , Selection.Year
From Students , Selection
where Student.Student ID = Selection.Stuedent ID
AND Course ID = 1012 AND Term = 1 AND Year = '85 - 86'
Order By Students.Family;

Name	Family	Term	Year
Ehsan	Amiri	1	85 - 86
Ahmad	Rezaee	1	85 - 86

مثال : نام دروسی را ارائه دهيد که دانشجو با شماره دانشجويی 10254861 انتخاب کرده است :

Select Courses.Course ID , Courses.Co Title
from Courses , selection
Where Courses.Course ID = selection.Course ID
AND Selection.Student ID = 102548861 ;

Course ID	Course Title
1011	پايگاه داده
1013	زبان تخصصی

مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که درس با کد 1013 در سال تحصيلی 84 - 85 را با نمره بالاتر از 15 گذارنده اند :

SELECT Students.Name , Students.Family
From Students , Selection
Where Students.Studentid = Selection.Studentid
And Selection.Courseid = '1013' And Year = '84 - 85' And Grade > 15 ;

Name	Family
Sahar	Ahamdi
Hesam	Razavi

پيوند بيش از 2 جدول به هم :

گاهی اوقات لازم است که اطلاعات مورد نياز ما از 3 جدول يا بيشتر استخراج شود . در اين حالت بايد کليه جدول ها را به هم پيوند دهيم به اين صورت که معمولا از يک جدول سوم برای پيوند 2 جدول ديگر استفاده می شود و 2 به 2 جدول هايی که با هم فيلد مشترک دارند را با ذکر شرط پيوند در دستور Where به هم پيوند می دهيم . سپس بقيه شروط دلخواه را نيز ذکر می کنيم .
شکل کلی اين حالت به صورت زير است :

Select نام ستون های مورد نظر از جدول ها
From نام تمام جدول ها
Where برابر قرار دادن فيلد مشترک جدول های 1 و 2
AND برابر قرار دادن فيلدهای مشترک جدول های 2 و 3
AND ... ;

مثال : نام و نام خانوادگی دانشجويانی را بدهيد که حداقل يک درس از نوع نظری را انتخاب کرده باشند :

Select Students.Name , Students.Family , Courses.CoTitle , Courses.CoType
From Students , Courses , Selections
Where Student.StudentID = Selection.StudentID
AND Courses.CourseID = Selection.CourseID
AND Courses.CoType = ' نظری ' ;

Name	Family	CoTitle	CoType
Zahra	Hosini	زبان تخصصی	نظری
Sahar	Ahamadi	زبان تخصصی	نظری
Hesam	Razavi	زبان تخصصی	نظری

* با دقت در اطلاعات جدول های اصلی متوجه درست بودن نتايج خروجی خواهيد شد .