دستورات در SQL
مقدمه ای بر Microsoft SQL Server
MS SQL Server یکی از سیستم های مدیریت بانک های اطلاعاتی رابطه ای (Relational) است که توسط شرکت مایکروسافت ارائه شده است .SQL Server از مدل سرویس دهنده - سرویس گیرنده (Client/Server) تبعیت می نماید . در این مدل ، درخواست های (InQuery) سرویس گیرندگان برای سرویس دهنده ارسال و در سمت سرویس دهنده بررسی و آنالیز می گردند . در ادامه ، پردازش های مورد نیاز بر روی اطلاعات ذخیره شده در بانک های اطلاعاتی انجام و در نهایت ، نتایج برای سرویس گیرنده ارسال خواهد شد .
MS SQL Server با استفاده از مجموعه عناصری ( Components ) که به صورت هدفمند اجراء می گردند ، قادر به تامین نیازها و درخواست ها از مخازن داده (Data Storages) می باشد . مخازن داده در SQL Server به دو روش زیر مدیریت می گردند :
▪ OLTP ( برگرفته از OnLine Transaction Processing System )
▪ OLAP ( برگرفته از OnLine Analytical Processing System )
در مدل OLTP ، مخازن داده به صورت جداول رابطه ای که عموما" به جهت جلوگیری از تکرار و ناهمگونی اطلاعات به صورت هنجار (Normalize) درآمده اند ، سازماندهی می شوند . این نوع از بانک های اطلاعاتی برای درج و تغییر سریع اطلاعات توسط چندین کاربر بطور همزمان مناسب می باشند .
در مدل OLAP مخازن داده جهت تجزیه و تحلیل و خلاصه سازی حجم زیادی از اطلاعات سازماندهی می شوند . مخازن داده و ارتباط بین اطلاعات در این مدل توسط SQL Server مدیریت می گردد .
یکی از اهداف مهم سیستم های مدیریت بانک های اطلاعاتی ، قابلیت رشد و توسعه (Scalability) است . MS SQL Server مجموعه ای از پتانسیل ها را به منظور تامین هدف فوق ارائه نموده است که به برخی از مهمترین آنها اشاره می گردد :
▪ قابلیت کار با بانکهای اطلاعاتی حجیم (در حد ترابایت)
▪ قابلیت دسترسی هزاران کاربر بطور همزمان به بانک اطلاعاتی
▪ قابلیت خود سازگاری (Self Compatibility) . با استفاده از ویژگی فوق ، منابع مورد نیاز هر کاربر (نظیر حافظه ، فضای دیسک و ...) به محض اتصال به سرور (Log in) به صورت اتوماتیک به وی تخصیص داده می شود و پس از Log off ، منابع اختصاص یافته به منظور استفاده سایر کاربران آزاد می شوند .
▪ قابلیت اعتماد و در دسترس بودن (Reliability) . با استفاده از ویژگی فوق می توان بسیاری از فعالیت های مدیریتی را بدون توقف سرور انجام داد (نظیر BackUp) .
▪ برخورداری از سطوح امنیتی بالا . بدین منظور اعتبار سنجی کاربران توسط SQL با اعتبار سنجی ویندوز تجمیع می گردد . در چنین مواردی ، ضرورتی به تعریف کاربر در MS SQL نخواهد بود و اعتبار سنجی وی توسط ویندوز انجام خواهد شد .
▪ پشتیبانی از حجم بالائی از حافظه در سرور (در نسخه ۲۰۰۰ تا ۶۴ گیگابایت و در نسخه ۲۰۰۵ متناسب با حافظه ای که سیستم عامل از آن حمایت می نماید ) .
▪ استفاده از چندین پردازنده به صورت موازی (در نسخه ۲۰۰۰ تا ۳۲ پردازنده همزمان و در نسخه ۲۰۰۵ محدودیتی وجود ندارد )
▪ پشتیبانی از لایه ها و سوکت های امنیتی نظیر SSL ، خصوصا" جهت استفاده در وب .
یکی دیگر از ویژگی های مهم سیستم های مدیریت بانک های اطلاعاتی ، ایجاد تسهیلات لازم به منظور مدیریت بانک های اطلاعاتی است . SQL Server با ارائه برنامه های جانبی نظیر Enterprise Manager استفاده و مدیریت بانک های اطلاعاتی را آسان نموده است .
MS SQL Server بطور اتوماتیک در Active Directory ثبت می شود (Register) ، بنابراین کاربران شبکه به راحتی می توانند آن را در Active Directory جستجو و در صورت نیاز به آن متصل شوند . همچنین ، MS SQL Server توسط IIS پشتیبانی می گردد و مرورگرها با استفاده از پروتکل HTTP قابلیت استفاده از آن را خواهند داشت .
از جمله نکات مهم در خصوص MS SQL Server ، اجرای آن به صورت یک سرویس است . بنابراین ، در صورتی که کاربری به ماشینی که MS SQL Server بر روی آن اجراء شده است ، Log on نکرده باشد ، همچنان سیستم در دسترس کاربران خواهد بود . علاوه بر این ، می توان از سیستم مانیتورینگ ویندوز به منظور مانیتورینگ SQL Server استفاده نمود .
یکی از مهمترین و شاخص ترین ویژگی های MS SQL Server که از نسخه ۲۰۰۰ در آن ایجاد شده است ، امکان نصب چندین نسخه SQL بر روی یک ماشین می باشد (Multi Instance) ، بطوریکه هر یک از نسخه ها فایل های باینری مخصوص به خود را داشته و بطور جداگانه مدیریت و راهبری می گردند ، ولی تمام نسخه ها بطور همزمان اجرا می شوند (دقیقا" مشابه این است که چندین نسخه بر روی چندین کامپیوتر نصب شده باشد ) .
با توجه به این که نسخه (Instance) قراردادی فاقد نام است و سایر نسخه ها می بایست دارای نام باشند به آنها نسخه های دارای نام (Named Instance) می گویند . نسخه های دارای نام را می توان هر زمان نصب نمود (قبل و یا بعد از نسخه قراردادی) . ابزارهای همراه SQL نظیر SQL Enterprise Manager یک مرتبه نصب خواهند شد و در تمامی نسخه های SQL به صورت مشترک استفاده خواهند شد .
تاکنون نسخه های متعددی از MS SQL Server ارائه شده است . استفاده از نسخه های ۲۰۰۰ و ۲۰۰۵ بیش از سایر نسخه ها ، متداول است . هر یک از نسخه های MS SQL Server ، در مدل های مختلف ( با توجه به نوع استفاده و اندازه سازمان متقاضی ) ، ارائه شده اند که در یک مقاله جداگانه به بررسی ویژگی هر یک از آنها خواهیم پرداخت .
معرفی زبان SQL :
- SQL مخفف عبارت Structured Query Language ( زبان جستجويي ساخت يافته ) است و به کاربر امکان اتصال و دسترسی یه اطلاعات موجود در يک پايگاه داده را می دهد .
- زبان SQL قادر است تا برای يک پايگاه داده عمل جستجو و گزينش اطلاعات را انجام دهد و همچنين اطلاعات ذخيره شده در يک پايگاه داده را بازيابی ، حذف ، ذخيره ، اضافه و يا به روز کند - زبان SQL يک استاندارد بين المللی است .
نکات مهم :
زبان SQL يک زبان برنامه نويسی و يا ايجاد بانک های اطلاعاتی به طور کامل نيست . بلکه برای دسترسی و ويرايش بانک های اطلاعاتی مورد استفاده قرار می گيرد . شما بايد قبلا پايگاه داده خود را ايجاد کرده باشيد و از SQL برای تغيير ساختار آن استفاده کنيد .
قبل از استفاد از دستورات SQL ، بايد با ساختار و معماری پايگاه داده ها آشنايي کافی را داشته و بتوانيد در يک محيط مستقل مثل Access يا SQL Server يگ پايگاه داده را طراحی کرده و سپس با دستورات SQL ، به کنترل ، تغيير و استخراج اطلاعات از پايگاه داده مذکور نماييد .
ما در قسمت آموزش SQL فقط به معرفی دستورات SQL پرداخته و با معماری و طراحی پايگاه داده ها کاری نداشته و فقط برخی از اصطلاحات و اجزاي مورد استفاده در سيستم های پايگاه داده ای را معرفی و به اختصار شرح می دهيم .
ساختار و اجزای مهم مورد استفاده در زبان SQL :
1 ) Table :
جداول مهمترين عناصر سيستم های پايگاه داده هستند ، که برای ذخيره و نگهداری سازمان يافته اطلاعات مورد استفاده قرار می گيرند . Table ها دارای سطر و ستون هستند و توسط يک نام منحصر به فرد در سطح برنامه شناسايي می شوند .
به سطرهاي يک جدول رکورد ( Record ) و به هر يک از خانه های ستون يک جدول فيلد ( Field ) می گويند .
هر رکورد محجموعه ای از اطلاعات طبقه بندی شده درباره يک موجوديت خاص است و هر فيلد يکی از خصوصيات آن موجوديت را به همراه مقدار آن مشخص می کند .
موجوديت پديده ، شی يا فردی در محيط پايگاه داده است که می خواهيم اطلاعات مربوط به آن را نگهداری کنيم .
مثال : به طور مثال در محيط پايگاه داده دانشگاه ، انواع موجوديت ها عبارتند از : دانشجو ، درس ، استاد ، دانشکده و ... .
هر فيلد در بر گيرنده يک صفت و ويژگی برای موجوديت می باشد ، که دارای 2 جزء اصلی است :
اسم صفت خاصه : نام صفت مورد نظر را تعيين می کند . برای مثال فيلد نام ، نام خانوادگی ، شماره دانشجويي و ... در جدول اطلاعات مربوط به دانشجو .
مقدار صفت خاصه : در برگيرنده مقدار برای صفت مورد نظر است . برای مثال مقدار " احمد " به عنوان مقدار برای فيلد نام .
2 ) Query :
از Query برای ايجاد يک پرسش و جستجو در جدول و استخراج اطلاعات مورد نظر استفاده می شود . نتايج حاصل از يک Query در يک جدول موقت که دارای فيلد ها و سطر های واجد شرايط جستجو است ، نمايش داده می شود .
مثال : به عنوان مثال می خواهيم ، مشخصات دانشجويانی که در ترم دوم سال تحصيلی 85 - 86 موفق به گذارندن درس پايگاه داده شده اند را از جدول دانشجويان استخراج کنيم . برای اين منظور از يک Query استفاده می کنيم . عمده دستورات SQL برای ايجاد و مديريت Query استفاده می شوند . در مباحث بعدی کار با يک Query را بررسی می کنيم .
3 ) SQL Data Manipulation Language ( DML )a
زبان دستکاری اطلاعات SQL :
اين بخش از دستورات SQL ، شامل دستوراتی برای به روز کردن ، ورود اطلاعات و يا حذف رکوردها در محيط پايگاه داده است . عمده دستئرات اين بخش عبارتند از :
Select : برای استخراج اطلاعات از يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
Update : برای به روز کردن اطلاعات يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
Delete : برای حذف اطلاعات از يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
Insert : برای ورود اطلاعات به يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
4 ) SQL Data Definition Language ( DDL )a
زبان تعريف اطلاعات SQL :
اين قسمت شامل دستورات زبان SQL برای ايجاد يا حذف جداول از بانک اطلاعاتی مورد استفاده قرار می گيرد . از اين قسمت همچنين می توان برای تعريف انديس ها ، کليد واژه ها ، ايجاد ارتباط و پيوند بين جدول ها و يا ايجاد محدوديت های دسترسی استفاده می شود .
عمده دستورات اين بخش عبارتند از :
Create Table : برای ايجاد يک جدول جديد به کار می رود .
Alter Table : برای تغيير اطلاعات موجود در يک جدول به کار می رود .
Drop Table : برای حذف يک يا چند جدول از بانک اطلاعاتی به کار می رود .
* Create Index : برای ايجاد انديس يا کليد واژه در يک جدول به کار می رود .
* Drop Index : برای حذف انديس يا کليد واژه در يک جدول به کار می رود .
* تعريف انديس Index :
انديس عبارتست از يک شماره که به هر يک از فيلدها در سطرهای يک جدول اختصاص داده می شود . انديس ها در پشت پرده جداول ايجاد شده و از ديد کاربر کاملا مخفی هستند . استفاده از انديس ها باعث می شود تا برنامه بتواند مقادير سطرهای مختلف را بر حسب مقدار يک فيلد و بر حسب شماره انديس آنها از کم به زياد يا بر عکس مرتب کند و در عمليات جستجو باعث بالا رفتن سرعت جستجو می شود .
دستور Select :
دستور Select برای انتخاب و استخراج اطلاعات مورد نظر از يک يا چند جدول و سپس مشاهده نتايج در يک جدول موقت استفاده می شود . شکل کلی اين دستور به شکل زير است :
Select نام ستون يا ستون های مورد نظر
From نام جدول
مثال : به طور مثال فقط می خواهيم ستون های Name و Family را از جدول Student ، مشاهده کنيم :
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
مثال
Select Name , Family
From Student کد
me Family
Ahamd Rezaee
Ehsan Amiri
Zahra Hoseini
Sahar Ahmadi
خروجی
نکته :
برای انتخاب کليه ستون های جدول می توان از نشانگر * در قسمت نام ستون استفاده کرد . در اين صورت تمامی ستون های جدول در خروجی نمايش داده خواهند شد :
Select *
From Students
واژه کليدی DISTINCT :
چنانچه در ستون های مورد جستجو ، موارد تکراری وجود داشته باشد در نتيجه خروجی نمايش داده خواهند شد . برای جلوگيری از چنين موردی و عدم نمايش موارد تکراری پس از دستور Select عبارت DISTINCT نوشته می شود :
Orders Table
Company Order Number
Dell 1012
Sega 1014
Dell 1016
Sony 1018
مثال
کد خروجی
Select DISTINCT Company
From Orders Company
Dell
Sega
Sony
Select Company
From Orders Company
Dell
Sega
Dell
Sony
دستور Where :
دستور Where برای افزودن شرط يا شرط هايی جهت محدود کردن نتايج جستجو و استخراج نتايج دقيقتر استفاده می شود . اين ذستور بايد پس از دستور Select و تعيين ستون ها از جدول مورد نظر به کار رود . با استفاده از عملگرهای AND ، OR و پرانتز می توان چندين شرط را با هم ترکيب کرد . برنامه فقط سطرهايي از جدول را که با شرط های ارائه شده سازگاری دارند را نمايش خواهد داد . اين دستور با عملگرهای زير می تواند به کار رود :
عملگر مفهوم عملگر مفهوم
= برابر با - مساوی != نا برابر با - نامساوی
> کوچکتر يا کمتر >= کوچکتر مساوی
< بزرگتر يا بيشتر <= بزرگتر مساوی
Between مابين دو مقدار Like تعيين يک قالب برای جستجو
مثال : انتخاب نام و نام خانوادگی دانشجويانی که شهر محل سکونت آنها تهران است از جدول Student به صورت زير انجام می شود :
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
مثال
کد خروجی
Select Name , Family
From Students
Where City = " Tehran " Name Family
Ahmad Rezaee
Zahra Hoseini
Hesam Razavi
* اعمال بيش از يک شرط و ادغام شرط ها با عملگر AND .
مثال : مشخصات نام و نام خانوادگی دانشجويانی که شهر آنها تهران بوده و نمره آنها بيش از پانزده است :
مثال
کد خروجی
Select Name , Family
From Students
Where City = " Tehran"
AND Grade > 15 Name Family
Ahmad Rezaee
Hesam Razavi
نکته : در هنگام اعمال شرط جستجو ، بايد مقادير رشته ای ( متن ) بين دو علامت " " قرار گرفته ، ولی درباره موارد عددی اين کار لازم نيست .
* عبارت like :
اين عبارت برای تعيين يک قالب خاص برای جستجو به کار می رود :
مثال : نام دانشجويانی که شهر محل سکونت آنها با حرف K شروع می شود :
Select Name From Students
Where City like "K%"
مثال : نام دانشجويانی که نام شهر محل سکونت آنها به an ختم می شود :
Select Name From Student
Where City Like "%an"
مثال : نام دانشجويانی که شهر محل سکونت آنها دارای حروف st است :
Select Name From Student
Where City Like "%st%"
مثال : نام دانشجويانی که شهر محل سکونت آنها 6 حرفی بوده و با حرف T شروع می شود :
Select Name From Student
Where City Like "T-----"
نتيجه گيری :
علامت % به معنای هر چند کاراکتر يا حرف دلخواه در قالب ارائه شده و علامت - نمايانگر يک کاراکتر دلخواه است .
دستور Insert :
این دستور برای وارد کردن و ايجاد يک رکورد جديد در جدول استفاده می شود .
شکل کلی این دستور به شرح زير است :
Insert into نام جدول
Values ( مقدار 1 ، مقدار 2 ، ... )
*می توان تعيين کرد که مقادير مورد نظر به ترتيب به کدام ستون های جدول وارد شوند . برای اين منظور ابتدا نام ستون های مورد نظر را به ترتيب جلوی نام جدول در يک پرانتز وارد کرده و با کاما از هم جدا می کنيم . سپس مقادير متناظر را به همان ترتيب پس از واژه Vaues در پرانتز وارد می کنيم .
نکته : چنانچه برای فيلد يا فيلدهايی مقداری در نظر گرفته نشود ، جای آنها در جدول خالی می ماند . فقط بايد به ترتيب نام ستون ها و مقادير دقت کرد .
مثال : مشخصات نام و نام خانوادکی را برای يک دانشجو به نام اميد صفوی را در جدول Student وارد کنيد :
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
- شرح دستور وارد کردن نام و وضعيت جديد جدول :
مثال
Insert into Student ( Name , Family )
Values ( "Omid" , "Safavi" ) کد
Student Table
Student ID Name Fa
ly 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
Omid Safavi
خروجی
دستور Update :
اين دستور برای تغيير اطلاعات موجود در يک فيلد و جايگزينی آن با يک مقدار جديد به کار می رود .
شکل کلی اين دستور به صورت زير است :
Update نام جدول
Set نام فيلد = مقدار جديد
Where نام فيلد = مقدار قبلی
مثال : نام دانشجو احمد رضايي را به علی رضايي تغيير دهيد :
Update Student
Set Name =" Ali "
Where Name = " Ahmad "
- همچنين می توان برای تعيين رکوردی که می خواهيم تغيير دهيم ، از مقدار يک فيلد ديگر به غير از فيلدی که می خواهد تغيير کنيد استفاده کنيم . در اين حالت از فيلد مذکور ، برای آدرس دهی رکورد مورد نظر استفاده می شود .
مثال : نام دانشجو با شماره دانشجويی 41252214 را به علی تغيير دهيد :
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
- اعمال دستور تغيير و وضعيت جديد جدول :
مثال
Update Student Set Name = " Ali "
Where StudentID = " 41252214 " کد
Student Table
tudent ID Name Family Major City Grade
41252214 Ali 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
خروجی
تغيير اطلاعات در بيش از يک فيلد در رکورد :
در اين حالت تمامی مقادير جديدی که می خواهيم تغيير دهيم ، جلوی واژه کليدی Set به ترتيب نوشته و با کاما از هم جدا می کنيم . سپس به همان ترتيب ، فيلدها با مقادير قبلی را در جلوی واژه Where می نوسيم . شکل کلی آن به صورت زير ااست :
Update نام جدول
Set فيلد 1 = مقدار جديد 1 ، فيلد 2 = مقدار جديد 2 ، ... .
Where فيلد 1 = مقدار قبلی 1 ، فيلد 2 = مقدار قبلی 2 ، ... .
- همچنين می توان فقط از يک فيلد در قسمت شرط برای آدرس دهی رکورد مورد نظر استفاده کرد .
مثال : نام ، نام خانوادگی و رشته تحصيلی دانشجو به شماره دانشجويي 41252214 را به ترتيب به علی ، سعيدی و نرم افزار تغيير دهيد :
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
- اعمال دستور تغيير و وضعيت جديد جدول :
مثال
Update Student
Set Name = " Ali " , Family = " Saeedi " , Major =" Soft Ware "
Where StudentID = 41252214 کد
Student Table
Student ID Name Family Major City Grade
41252214 Ali Saeedi Soft Ware Tehran 18
10724113 Ehsan Amiri Soft Ware Karaj 14
10254861 Zahra Hosini Hard Ware Tehran 17
27365187 Sahar Ahmadi Soft Ware Bam 16
3565441
Hesam Razavi Soft Ware Tehran 19
خروجی
دستور Delete :
از اين دستور برای حذف اطلاعات يک رکورد در جدول استفاده می شود .
شکل کلی اين دستور به شکل زير است :
Delete From نام جدول
Where نام و مقدار يک فيلد از رکورد مورد نظر
مثال : رکورد اطلاعات دانشجو به شماره دانشجويي 10724113 را حذف کنيد :
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
- اعمال دستور حذف و وضعيت جديد جدول :
مثال
Delete From Students
Where StudentID = "10724113 " کد
Student Table
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
10254861
hra Hosini Hard Ware Tehran 17
27365187 Sahar Ahmadi Soft Ware Bam 16
35654415 Hesam Razavi Soft Ware Tehran 19
خروجی
حذف کليه رکوردهای يک جدول :
اين امکان وجود دارد که بدون حذف يک جدول ، کليه رکوردهای درون آن را پاک کرد . اين به منزله خالی کردن جدول از اطلاعات است ، ولی ساختار ، انديس ها و ويژگی های جدول دست نخورده باقی مانده و فقط مقادير درون آن از بين می رود .
شکل کلی اين دستور به صورت زير است :
Delete From نام جدول
يا
Delete * From نام جدول
مثال : حذف کليه رکوردهای جدول Student :
Delete From Student
دستورات اوليه SQL
دستور Order By :
اطلاعات خروجی در دستور select به طور کلی بی نظم است و مقادير خروجی در ستون های جدول بر حسب مقدار هيج ستونی مرتب نيستند . اين دستور برای مرتب کردن اطلاعات جدول بر اساس مقادير يک يا چند ستون برحسب شاخص هايی مثل ترتيب حروف الفبا ، بزرگتر يا کوچکتر بودن اعداد و ... استفاده می شود .
شکل کلی اين دستور به صورت زير است :
Select نام فيلدهای مورد نظر From جدول مورد نظر
Order By فيلد مورد نظر
* فيلدهای معرفی شده در دستور Select از جدول مذکور نمايش داده شده و اطلاعات بر حسب ستون مقابل دستور Order By مرتب می شوند .
- عبارت ASC : به کار بردن اين دستور پس از دستور Order By باعث مرتب شدن اطلاعات درون جدول از کم به زياد می شود که البته اتخاب پيش فرض SQL است و نيازی به درج آن نيست .
عبارت DESC : به کار بردن اين واژه پس از دستور Order By باعث مرتب شدن اطلاعات جدول از زِياد به کم در اعداد و در حروف از z تا a می شود ، که در صورت نياز درج آن ضروری است .
مثال : از جدول Student ، مشخصات نام و نام خانوادگی دانشجويان رشته نرم افزار را بر حسب حروف الفبا نمايش دهيد :
Student Table
Student I
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
مثال
Select
ame , Family From Student
Where Major ="Soft Ware"
Order By Family کد
Name Family
Sahar Ahmadi
Ehsan Amiri
Hesam Razavi
خروجی
نکته :
همچنين می توان يک جدول را بر حسب بيش از يک فيلد مرتب کرد ، برای اين منظور پس از دستور Order By نام تمام فيلدهای مورد نظر را نوشته و آنها را با کاما از هم دا می کنيم . اولويت مرتب سازی با فيلدهای است که نام آنها زودتر ذکر شده باشند .
مثال : جدول مشخصات دانشجويان را بر حسب فيلد نام ( بر حسب حروف از a تا z ) و بر حسب نمره ( از کم به زياد ) مرتب کنيد :
Student Table
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 15
10724113 Ehsan Amiri Soft Ware Karaj 19
10254861 Zahra Hosini Hard Ware Tehran 18
27365187 Sahar Ahmadi Soft Ware Bam 20
35654415 Hesam Razavi Soft Ware Tehran 14
نکته : در جدول خروجی مثال زير به ارتباط مقادير ستون های Family , Grade توجه کنيد . باوجود اينکه نمره درسی دانشجو حسام رضوی از احمد رضايی کمتر است ، ولی به دليل اينکه اولويت مرتب سازی با نام خانوادگی است ، نام رضوی بالاتر از رضايی قرار گرفته است .
مثال
Select Name , Family , Grade From Student
Order By Family ASC , Grade DESC کد
Name Family Grade
Sahar Ahmadi 20
Ehsan Amiri 19
Zahra Hoseini 18
Hesam Razavi 14
Ahmad Rezaee 16
خروجی
نکته : می توان در دستور Order By به جای ذکر نام ستون ها از يک عدد به جای آن استفاده کرد . عدد استفاده شده شماره قرار گيری ستون مورد نظر در دستور Select است . برای مثال اولين ستون دستور Select عدد 1 و دومين ستون عدد 2 و ... را خواهند داشت . بازنویسی دستور بالا با استفاده از اين روش به صورت زير خواهد بود :
مثال
Select Name , Family , Grade from Student
Order By 2 ASC , 3 DESC کد
دستور AND & OR :
از And و Or برای ترکيب شرط ها در دستور Where استفاده می شود .
گاهی اوقات اطلاعات مورد نياز ما در دستور Select دارای چندين شرط مختلف است . به طور مثال ما مشخصات دانشجويان ترم دوم رشته نرم افزار که معدل آنها بيش از 15 است را می خواهيم . در اين حالت بايستی هر يک از شرط های مسئله را به تنهايی تعريف کرده و سپس آنها را با هم ترکيب کنيم . برنامه به ارزيابی هر يک از شرط ها پرداخته ، سپس نتايج حاصله را با هم ترکيب کرده و خروجی را تعيين می کند .
عملگر And برای اجرای دستور نياز دارد تا تمام شرط های تعيين شده برای آن درست باشد . در حالی که عمگر Or فقط نياز دارد که حداقل يکی از شرط های آن درست باشد . در جداول زير چگونگی برقراری نتيجه نهايی در هنگام استفاده از اين دو اوپراتور را مشاهده می کنيم :
در حالت 2 شرط :
OR
شرط 1 شرط 2 نتيجه
درست درست درست
درست غلط درست
غلط درست درست
غلط غلط غلط
AND
شرط 1 شرط 2 نتيجه
درست درست درست
درست غلط غلط
غلط درست غلط
غلط غلط غلط
چگونگی حالت های فوق را در مثال های زير بررسی می کنيم :
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
مثال 1 : مشخصات دانشجويانی را از جدول Student ارائه دهيد که نمره آنها بيش از 15 و رشته تحصيلی آنها نرم افزار باشد :
مثال
Select * From Student
Where Major = 'Soft Ware' AND Grade > 15 ; کد
Student ID Name Family Major City Grade
27365187 Sahar Ahmadi Soft Ware Bam 16
35654415 Hesam Razavi Soft Ware Tehran 19
خروجی
مثال 2 : مشخصات دانشجويانی را از جدول Student ارائه دهيد که نمره آنها بيش از 17 بوده يا رشته تحصيلی آنها نرم افزار باشد :
مثال
Select * From Student
where Major = 'Soft Ware' OR Grade > 17 ; کد
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
35654415 Hesam Razavi Soft Ware Tehran 19
خروجی
نکته مهم : همچنين می توان با استفاده از عملگر پرانتز چندين شرط مختلف را به صورت دسته های مجزا تعريف کرده و سپس همه آنها را با هم ترکيب کرد . در اين حالت شرط های هر گروه ابتدا در داخل پرانتز با هم ترکيب شده ، سپس نتيجه حاصله به عنوان نتيجه نهايي آن گروه شرط به دستور Where ارسال می شود و درنهايت نتايج تمام گروه ها با هم ترکيب می شوند .
مثال مهم : از جدول Student مشخصات دانشجويانی را ارائه دهيد که نمره آنها 18 و رشته تحصيلی آنها سخت افزار بوده يا نمره آنها 16 و رشته تحصيلی شان نرم افزار باشد :
مثال
Select * From Student
Where ( ( Grade = 18 AND Major = 'Hard Ware' ) OR ( Garde = 16 AND Major = 'Soft Ware' ) ) " ; کد
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
27365187 Sahar Ahmadi Soft Ware Bam 16
خروجی
________________________________________
دستور In :
اين دستور برای مشاهده اطلاعات رکوردهايی از جدول به کار می رود که شما مقدار دقيق حداقل يکی از فيلدهای آنرا می دانيد .
شکل کلی اين دستور به صورت زير است :
Select نام ستون From نام جدول
Where نام فيلد IN ( مقدار 1 ، مقدار 2 و ... )
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
مثال : از جدول Student مشخصات دانشجويانی را بدهيد که نام خانوادگی آنها احمدی يا رضايی است :
مثال
Select * From student
Where Family IN ( "Ahmadi" , "Rezaee" ); کد
Student Table
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
27365187 Sahar Ahmadi Soft Ware Bam 16
خروجی
دستور Between ... And :
اين دستور برای انتخاب اطلاعات در يک محدوده خاص ، در بين دو مقدار تعيين شده استفاده می شود . اين مقادير می تواند از نوع عددی ، متن يا تاريخ باشد . ( بايد نوع مدنظر با نوع داده ای فيلد های استفاده شده در دستور يکسان باشد ).
شکل کلی اين دستور به شکل زير است :
Select نام فيلدهای مورد نظر برای نمايش From نام جدول
Where نام فيلد مورد نظر
Between مقدار 1 And مقدار 2
نکته : در مورد داده های متنی ، ترتيب قرار گيری حروف الفبا مد نظر برنامه است .
مثال : از جدول Student مشخصات دانشجويانی را ارائه دهيد ، که نام خانوادگی آنها بين دو مقدار احمدی و حسينی باشد . در ضمن خروجی بر حسب نام خانوادگی به ترتيب حروف الفبا مرتب باشد :
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
مثال
Select * From Student
Where Family Between 'Ahmadi' And 'Hoseini'
Order By family; کد
Student ID Name Family Major City Grade
27365187 Sahar Ahmadi Soft Ware Bam 16
10724113 Ehsan Amiri Soft Ware Karaj 14
10254861 Zahra Hosini Hard Ware Tehran 17
مثال
نکته :
قرار گرقتن خود رکوردهای 2 مقدار تعيين شده در خروجی دستور Select ، بسته به نوع برنامه پايگاه و مديريت آن دارد . در برخی از پايگاه های داده رکوردهای هر 2 مقدار ابتدا و انتها نمايش داده می شود و در برخی هم فقط يکی از آنما نمايش داده می شود .
نکته :
برای نمايش اطلاعات خارج از محدوده تعيين شده از يک عملگر NOT قبل از دستور Between ... And استفاده می شود .
مثال : از جدول student مشخصات دانشجويانی زا ارائه دهيد که نمره آنها خارج از محدوده 12 تا 15 باشد . در ضمن مقادير خروجی بر حسب نام خانوادگی به ترتيب حروف الفبا مرتب باشد :
مثال
Select * From Student
Where Grade NOT Between 12 And 15
Order By Family ; کد
Student ID Name Family Major City Grade
27365187 Sahar Ahmadi Soft Ware Bam 16
10254861 Zahra Hosini Hard Ware Tehran 17
35654415 Hesam Razavi Soft Ware Tehran 19
41252214 Ahmad Rezaee Hard Ware Tehran 18
خروجی
ويژگی Alias :
همانطور که در تمام مثال های ذکر شده از دستور Select مشاهده کرديد ، نام يا عنواني که در بالای هر ستون در خروجی نمايش داده می شود ، همان نام فيلد مربوط به داده در جدول اصلی پايگاه داده است . گاهی اوقات تمايل بر اين است که اين نام را تغيير دهيم ، يا در مواردی که در بخش توابع SQL خواهيم ديد ، يک ستون جديد ايجاد می کنيم که بايد نامی برای آن تعيين شود . از ويژگی Alias برای در نظر گرفتن يک نام مستعار و مجازی برای قرار گرفتن در بخش عنوان هر ستون در خروجی دستور Select استفاده می شود .
شکل کلی اين دستور به صورت زير است :
Select نام فيلد As عنوان ) Alias ) From نام جدول
مثال : از جدول Student نام و نام خانوادگی دانشجويان رشته نرم افزار را بر حسب حروف الفبا در 2 ستون تحت عنوان های " نام " و " نام خانوادگی " نمايش دهيد :
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
مثال
Select Name As نام , Family As خانوادگی From Student
Where Major = 'Soft Ware'
Order By Family ; کد
نام خانوادگی
Sahar Ahmadi
Ehsan Amiri
Hesam Razavi
مثال
نکته مهم :
می توان بر روی مقادير ستون هايی که داده عددی دارند ، عمليات رياضی ( مثل ضرب ، تقسيم و ... ) انجام داده و سپس نتايج محاسبات را در يک ستون جديد با عنوان دلخواه تعيين شده توسط ويژگی Alias در خروجی دستور Select نمايش داد .در اين حالت ستون يا ستون های به جدول خروجی اضافه می شود .
در اين ارتباط مثالی را ذکر می کنيم :
مثال : فرض کنيد جدولی داريم به نام Books ، که در آن مشخصات چند کتاب مختلف قرار دارد . در اين جدول قيمت کتاب ها به دلار است و ما می خواهيم قيمت کتاب ها را در يک ستون جديد به تومان مشاهده کنيم . در اين به روش زير عمل می کنيم :
Books Table
Radif Name Publisher Price $
1 Learning Access Microsoft 8
2 Java Script Samples DeveloperStudio 6
3 HTML Reference DeveloperStudio 7
مثال
Select Radif , Name , Publisher , Price , Price * 840 As قيمت به تومان From Books ; کد
Books Table
Radif Name Publisher Price $ قيمت به تومان
1 Learning Access Microsoft 8 6720
2 Java Script Samples DeveloperStudio 6 5040
3 HTML Reference DeveloperStudio 7 5880
خروجی
پيوند جدول ها :
تا اين قسمت تمام مثال ها و مسئله هايی که در SQL به آنها پاسخ داديم ، مسئله هايی بودند که اطلاعات ما فقط از يک جدول استخراج می شد . اما در برنامه نويسی واقعی پايگاه داده ها ، ما مجبور هستيم که اطلاعات خود را از بيش از يک جدول استخراج کنيم . در اين حالت ما ابتدا بايد جدول هايی که می خواهيم اطلاعات را از آنها استخراج کنيم ، با هم پيوند دهيم . هدف از ايجاد اين ارتباط تلفيق اطلاعات در جدول ها و چاپ اطلاعات مورد نظر در خروجی است .
مفاهيم اوليه :
برای پيوند دادن جدول ها ابتدا بايد چند مفهوم زير را بشناسيم :
کليد اصلی : فيلد کليد اصلی در يک جدول ، فيلدی است که شرايط زير را داشته باشد :
مقدار آن برای هر نمونه رکورد ( سطر ) منحصر به فرد و غير تکراری باشد . به عبارت ديگر هيچ 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 زبان تخصصی نظری
خروجی
* با دقت در اطلاعات جدول های اصلی متوجه درست بودن نتايج خروجی خواهيد شد .
دستورات پيشرفته SQL
دستور Join :
از دستور Join نيز همانند قسمت قبلی ( پيوند جدول ها ) ، برای پيوند جدول ها در پايگاه داده ها استفاده می شود . تمام شرايط و ضوابط ذکر شده در قسمت قبل در مورد دستور Join نيز صادق است . به عبارت ديگر دستور Join روشی متفاوت برای پيوند جدول هاست که همان خروجی را توليد می کند .
برای پيوند جدول ها با استفاده از دستور Join ، از روش کلی زير استفاده می شود :
Select نام ستونهای مورد نظر جهت نمايش از هر 2 جدول
From نام جدول 1
Inner Join نام جدول 2
On برابر قرار دادن فيلدهای مشترک 2 جدول با هم
Where بقيه شرط ها ;
برخی از مثال های قسمت قبل را با دستور Join باز نويسی می کنيم .
مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که در ترم 1 سال تحصيلی 85 - 86 ، درس با کد 1012 را انتخاب کرده اند :
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
مثال
Select Students.Name , Students.Family , Seleciton.Term , Selection.Year
From Students
Inner Join Seleciton
On Students.Student ID = Selection.Student ID
Where Selection.Course ID = 1012 AND Term = 1 AND Year = ' 85 - 86 ' ; کد
Name Family Term Year
Ehsan Amiri 1 85 - 86
Ahmad Rezaee 1 85 - 86
خروجی
مثال : نام دروسی را ارائه دهيد که دانشجو با شماره دانشجويی 10254861 انتخاب کرده است :
مثال
Select Courses.Course ID , Courses.Co Title
From Courses
Inner Join Selection
ON Courses.Course ID = Selection.Course ID
Where Selection.Student ID = 102548861 ; کد
Course ID Course Title
1011 پايگاه داده
1013 زبان تخصصی
خروجی
Select های تو در تو :
می توان در درون يک دستور Select ، دستور Select ديگری را تعريف کرد . در اين حالت ابتدا دستور Select درونی اجرا شده و نتايج مورد نظر خود را که سطرهايی از جدول مربوط به آن هستند را استخراج کرده و آنرا به Select بيرونی انتقال می دهد . سپس Select بيرونی نيز اجرا شده و نتايج استخراجی خود را با استفاده از فيلد مشترک بين 2 جدول با سطرهای Select درونی پيوند زده و سطرهای مطابق با شروط تعيين شدهخ را نمايش می دهد .
از حالت Select های تو در تو برای پيوند جدول ها استفاده می شود و برای زمانی مناسب است که بخواهيم ستون های خروجی در دستور Select فقط از يک جدول که در بيرونی ترين Select تعريف شده اند ، نمايش داده شوند .
نکته : به تعداد دستورات Select تو در تو ، سطح آن می گويند .
Select های 2 سطحی :
در اين حالت يک دستور Select در درون دستور Select ديگری تعريف می شود و برای پيوند 2 جدول با استفاده از فيلد مشترک استفاده می شود . نتايج خروجی فقط می تواند شامل سطرهای از جدول Select بيرونی باشد .
شکل کلی پيوند 2 جدول بااستفاده از Select 2 سطحی به صورت زير است :
Select نام ستون های مورد نظر برای نمايش از جدول بيرونی
From نام جدول 1
Where نام فيلد مشترک 2 جدول IN ( Select نام فيلد مشترک 2 جدول
From نام جدول 2
Where شرط ها ... ) ;
مثال های قسمت های قبل پيوند جدول ها را با استفاده از Select های تو در تو بازنويسی می کنيم .
مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که در ترم 1 سال تحصيلی 85 - 86 ، درس با کد 1012 را انتخاب کرده اند :
مثال
Select Name , Family
From Students
Where Student ID IN ( Select Student ID
From Selection
Where Course ID = 1012 AND Term = 1 AND Year = ' 85 - 86 ' ) ; کد
Name Family
Ehsan Amiri
Ahmad Rezaee
خروجی
نکته : تفاوت اين حالت با مثال های قبلی در اين است که ستون های نمايش داده شده فقط از يک جدول ( جدول Select بيرونی ) نمايش داده می شوند .
مثال : نام دروسی را ارائه دهيد که دانشجو با شماره دانشجويي 10254861 انتخاب کرده است :
مثال
Select Coures ID , CoTitle
From Courses
Where Course ID IN ( Select Course ID
From Selection
Where Student ID = 10254861 ) ; کد
Course ID Course Title
1011 پايگاه داده
1013 زبان تخصصی
خروجی
Select های 3 سطحی :
در اين حالت 3 دستور Select به صورت تو در تو تعريف شده اند و برای پيوند 3 جدول به صورت 2 به 2 و با استفاده از فيلد های مشترک استفاده می شود . نتايج خروجی فقط می تواند شامل ستون هايي از بيرونی ترين Select تعريف شده باشد . در اين حالت از جدول دوم برای پيوند جدول های 1 و 3 استفاده می شود .
شکل کلی پيوند 3 جدول با استفاده از Select های 3 سطحی به صورت زير است :
Select نام ستون های مورد نظر جهت نمايش از جدول 1
From نام جدول 1
where نام فيلد مشترک جدول 1 و 2 IN ( Select نام فيلد مشترک جدول 1 و2
From نام جدول 2
Where نام فيلد مشترک جدول 2 و 3 IN ( Select نام فيلد مشترک جدول 2 و 3
From نام جدول 3
Where بقيه شرط ها ... )) ;
مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که حداقل يک درس از نوع نظری را انتخاب کرده باشد :
مثال
Select Name , Family
From Students
where Student ID IN ( Select Student ID
From Selection
Where Course ID IN ( Select Course ID
From Courses
Where CoType = ' نظری ' )) ; کد
Name Family
Zahra Hosini
Sahar Ahamadi
Hesam Razavi
خروجی
________________________________________
دستور Unoin :
از دستور Union برای ترکيب و ادغام دو يا چند ستون مختلف از 2 يا چند جدول و نشان دادن آنها در يک ستون مشترک استفاده می شود .
در اين دستور ، نوع داده ای ستون های انتخاب شده برای ترکيب بايد يکسان باشند . دستور Union در هنگام ترکيب فيلد ها ، در صورت برخورد با مقادير تکراری آنها را حذف کرده و از هر مقدار يک نمونه را نمايش می دهد . برای مشاهده تمام مقادير ، حتی مقادير تکراری بايد از دستور Union ALL استفاده کرد .
شکل کلی استفاده از اين دستور به صورت زير است :
SQL Statment 1 ( نام فيلد 1 اتخابی )
Union
SQL Statment 1 ( نام فيلد 2 اتخابی )
مثال : شماره دانشجويی ، دانشجويانی را بدهيد که يا دانشجوی رشته نرم افزار بوده يا در درس زبان تخصصی با کد 1013 ثبت نام کرده اند :
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
مثال
Select Stuedent ID From Students
Where Major = ' Soft '
Union
Select Student ID From Selection
Where Course ID = 1013 ; کد
Student ID
10254861
10724113
27365187
35654415
خروجی
توجه : می توان دستورات Select تعريف شده در دستور Union را تا حد نياز گسترش د اد . به عبارت ديگر هر دستور مجاز Select در اين قسمت قابل تعريف است . برای مثال می توان در دستورات Select تعريف شده ، جدول ها را به هم پيوند داد و فقط يک ستون خروجی توليد کرد و سپس آن را با ستون طرف ديگر پيوند زد .
نکته : می توان در دستور Union بيش از يک ستون مجزا را تعريف کرد . فقط بايد تعداد ستون های و ترتيب نوع داده ای ستون های تعريف شده يکسان باشند . برنامه مقادير ستون ها را به ترتيب با هم پيوند می دهد .
مثال : شماره دانشجويي ، نام و نام خانوادگی دانشجويانی را بدهيد که در رشته سخت افزار تحصيل کرده يا حداقل يک درس از نوع نظری را انتخاب کرده باشند :
مثال
Select Student ID , Name , Family
From Students
Where Major = ' Hard '
Union
Select Student ID , Name , Family
From Students , Seleciton , Courses
where Students.Student ID = Selection.Student ID
AND Selection.Course ID = Courses.Course ID
AND CoType = ' نظری ' ; کد
Student ID Name Family
10254861 Zahra Hosini
107241113 Ehsan Amiri
27365187 Sahar Ahmadi
35654415 Hesam Razavi
خروجی
1. سایت
http://www.developercenter.ir
2 . سایت
www.cpn-pc.blogfa.co
3. سایت
www.mspsoft.com
MS SQL Server یکی از سیستم های مدیریت بانک های اطلاعاتی رابطه ای (Relational) است که توسط شرکت مایکروسافت ارائه شده است .SQL Server از مدل سرویس دهنده - سرویس گیرنده (Client/Server) تبعیت می نماید . در این مدل ، درخواست های (InQuery) سرویس گیرندگان برای سرویس دهنده ارسال و در سمت سرویس دهنده بررسی و آنالیز می گردند . در ادامه ، پردازش های مورد نیاز بر روی اطلاعات ذخیره شده در بانک های اطلاعاتی انجام و در نهایت ، نتایج برای سرویس گیرنده ارسال خواهد شد .
MS SQL Server با استفاده از مجموعه عناصری ( Components ) که به صورت هدفمند اجراء می گردند ، قادر به تامین نیازها و درخواست ها از مخازن داده (Data Storages) می باشد . مخازن داده در SQL Server به دو روش زیر مدیریت می گردند :
▪ OLTP ( برگرفته از OnLine Transaction Processing System )
▪ OLAP ( برگرفته از OnLine Analytical Processing System )
در مدل OLTP ، مخازن داده به صورت جداول رابطه ای که عموما" به جهت جلوگیری از تکرار و ناهمگونی اطلاعات به صورت هنجار (Normalize) درآمده اند ، سازماندهی می شوند . این نوع از بانک های اطلاعاتی برای درج و تغییر سریع اطلاعات توسط چندین کاربر بطور همزمان مناسب می باشند .
در مدل OLAP مخازن داده جهت تجزیه و تحلیل و خلاصه سازی حجم زیادی از اطلاعات سازماندهی می شوند . مخازن داده و ارتباط بین اطلاعات در این مدل توسط SQL Server مدیریت می گردد .
یکی از اهداف مهم سیستم های مدیریت بانک های اطلاعاتی ، قابلیت رشد و توسعه (Scalability) است . MS SQL Server مجموعه ای از پتانسیل ها را به منظور تامین هدف فوق ارائه نموده است که به برخی از مهمترین آنها اشاره می گردد :
▪ قابلیت کار با بانکهای اطلاعاتی حجیم (در حد ترابایت)
▪ قابلیت دسترسی هزاران کاربر بطور همزمان به بانک اطلاعاتی
▪ قابلیت خود سازگاری (Self Compatibility) . با استفاده از ویژگی فوق ، منابع مورد نیاز هر کاربر (نظیر حافظه ، فضای دیسک و ...) به محض اتصال به سرور (Log in) به صورت اتوماتیک به وی تخصیص داده می شود و پس از Log off ، منابع اختصاص یافته به منظور استفاده سایر کاربران آزاد می شوند .
▪ قابلیت اعتماد و در دسترس بودن (Reliability) . با استفاده از ویژگی فوق می توان بسیاری از فعالیت های مدیریتی را بدون توقف سرور انجام داد (نظیر BackUp) .
▪ برخورداری از سطوح امنیتی بالا . بدین منظور اعتبار سنجی کاربران توسط SQL با اعتبار سنجی ویندوز تجمیع می گردد . در چنین مواردی ، ضرورتی به تعریف کاربر در MS SQL نخواهد بود و اعتبار سنجی وی توسط ویندوز انجام خواهد شد .
▪ پشتیبانی از حجم بالائی از حافظه در سرور (در نسخه ۲۰۰۰ تا ۶۴ گیگابایت و در نسخه ۲۰۰۵ متناسب با حافظه ای که سیستم عامل از آن حمایت می نماید ) .
▪ استفاده از چندین پردازنده به صورت موازی (در نسخه ۲۰۰۰ تا ۳۲ پردازنده همزمان و در نسخه ۲۰۰۵ محدودیتی وجود ندارد )
▪ پشتیبانی از لایه ها و سوکت های امنیتی نظیر SSL ، خصوصا" جهت استفاده در وب .
یکی دیگر از ویژگی های مهم سیستم های مدیریت بانک های اطلاعاتی ، ایجاد تسهیلات لازم به منظور مدیریت بانک های اطلاعاتی است . SQL Server با ارائه برنامه های جانبی نظیر Enterprise Manager استفاده و مدیریت بانک های اطلاعاتی را آسان نموده است .
MS SQL Server بطور اتوماتیک در Active Directory ثبت می شود (Register) ، بنابراین کاربران شبکه به راحتی می توانند آن را در Active Directory جستجو و در صورت نیاز به آن متصل شوند . همچنین ، MS SQL Server توسط IIS پشتیبانی می گردد و مرورگرها با استفاده از پروتکل HTTP قابلیت استفاده از آن را خواهند داشت .
از جمله نکات مهم در خصوص MS SQL Server ، اجرای آن به صورت یک سرویس است . بنابراین ، در صورتی که کاربری به ماشینی که MS SQL Server بر روی آن اجراء شده است ، Log on نکرده باشد ، همچنان سیستم در دسترس کاربران خواهد بود . علاوه بر این ، می توان از سیستم مانیتورینگ ویندوز به منظور مانیتورینگ SQL Server استفاده نمود .
یکی از مهمترین و شاخص ترین ویژگی های MS SQL Server که از نسخه ۲۰۰۰ در آن ایجاد شده است ، امکان نصب چندین نسخه SQL بر روی یک ماشین می باشد (Multi Instance) ، بطوریکه هر یک از نسخه ها فایل های باینری مخصوص به خود را داشته و بطور جداگانه مدیریت و راهبری می گردند ، ولی تمام نسخه ها بطور همزمان اجرا می شوند (دقیقا" مشابه این است که چندین نسخه بر روی چندین کامپیوتر نصب شده باشد ) .
با توجه به این که نسخه (Instance) قراردادی فاقد نام است و سایر نسخه ها می بایست دارای نام باشند به آنها نسخه های دارای نام (Named Instance) می گویند . نسخه های دارای نام را می توان هر زمان نصب نمود (قبل و یا بعد از نسخه قراردادی) . ابزارهای همراه SQL نظیر SQL Enterprise Manager یک مرتبه نصب خواهند شد و در تمامی نسخه های SQL به صورت مشترک استفاده خواهند شد .
تاکنون نسخه های متعددی از MS SQL Server ارائه شده است . استفاده از نسخه های ۲۰۰۰ و ۲۰۰۵ بیش از سایر نسخه ها ، متداول است . هر یک از نسخه های MS SQL Server ، در مدل های مختلف ( با توجه به نوع استفاده و اندازه سازمان متقاضی ) ، ارائه شده اند که در یک مقاله جداگانه به بررسی ویژگی هر یک از آنها خواهیم پرداخت .
معرفی زبان SQL :
- SQL مخفف عبارت Structured Query Language ( زبان جستجويي ساخت يافته ) است و به کاربر امکان اتصال و دسترسی یه اطلاعات موجود در يک پايگاه داده را می دهد .
- زبان SQL قادر است تا برای يک پايگاه داده عمل جستجو و گزينش اطلاعات را انجام دهد و همچنين اطلاعات ذخيره شده در يک پايگاه داده را بازيابی ، حذف ، ذخيره ، اضافه و يا به روز کند - زبان SQL يک استاندارد بين المللی است .
نکات مهم :
زبان SQL يک زبان برنامه نويسی و يا ايجاد بانک های اطلاعاتی به طور کامل نيست . بلکه برای دسترسی و ويرايش بانک های اطلاعاتی مورد استفاده قرار می گيرد . شما بايد قبلا پايگاه داده خود را ايجاد کرده باشيد و از SQL برای تغيير ساختار آن استفاده کنيد .
قبل از استفاد از دستورات SQL ، بايد با ساختار و معماری پايگاه داده ها آشنايي کافی را داشته و بتوانيد در يک محيط مستقل مثل Access يا SQL Server يگ پايگاه داده را طراحی کرده و سپس با دستورات SQL ، به کنترل ، تغيير و استخراج اطلاعات از پايگاه داده مذکور نماييد .
ما در قسمت آموزش SQL فقط به معرفی دستورات SQL پرداخته و با معماری و طراحی پايگاه داده ها کاری نداشته و فقط برخی از اصطلاحات و اجزاي مورد استفاده در سيستم های پايگاه داده ای را معرفی و به اختصار شرح می دهيم .
ساختار و اجزای مهم مورد استفاده در زبان SQL :
1 ) Table :
جداول مهمترين عناصر سيستم های پايگاه داده هستند ، که برای ذخيره و نگهداری سازمان يافته اطلاعات مورد استفاده قرار می گيرند . Table ها دارای سطر و ستون هستند و توسط يک نام منحصر به فرد در سطح برنامه شناسايي می شوند .
به سطرهاي يک جدول رکورد ( Record ) و به هر يک از خانه های ستون يک جدول فيلد ( Field ) می گويند .
هر رکورد محجموعه ای از اطلاعات طبقه بندی شده درباره يک موجوديت خاص است و هر فيلد يکی از خصوصيات آن موجوديت را به همراه مقدار آن مشخص می کند .
موجوديت پديده ، شی يا فردی در محيط پايگاه داده است که می خواهيم اطلاعات مربوط به آن را نگهداری کنيم .
مثال : به طور مثال در محيط پايگاه داده دانشگاه ، انواع موجوديت ها عبارتند از : دانشجو ، درس ، استاد ، دانشکده و ... .
هر فيلد در بر گيرنده يک صفت و ويژگی برای موجوديت می باشد ، که دارای 2 جزء اصلی است :
اسم صفت خاصه : نام صفت مورد نظر را تعيين می کند . برای مثال فيلد نام ، نام خانوادگی ، شماره دانشجويي و ... در جدول اطلاعات مربوط به دانشجو .
مقدار صفت خاصه : در برگيرنده مقدار برای صفت مورد نظر است . برای مثال مقدار " احمد " به عنوان مقدار برای فيلد نام .
2 ) Query :
از Query برای ايجاد يک پرسش و جستجو در جدول و استخراج اطلاعات مورد نظر استفاده می شود . نتايج حاصل از يک Query در يک جدول موقت که دارای فيلد ها و سطر های واجد شرايط جستجو است ، نمايش داده می شود .
مثال : به عنوان مثال می خواهيم ، مشخصات دانشجويانی که در ترم دوم سال تحصيلی 85 - 86 موفق به گذارندن درس پايگاه داده شده اند را از جدول دانشجويان استخراج کنيم . برای اين منظور از يک Query استفاده می کنيم . عمده دستورات SQL برای ايجاد و مديريت Query استفاده می شوند . در مباحث بعدی کار با يک Query را بررسی می کنيم .
3 ) SQL Data Manipulation Language ( DML )a
زبان دستکاری اطلاعات SQL :
اين بخش از دستورات SQL ، شامل دستوراتی برای به روز کردن ، ورود اطلاعات و يا حذف رکوردها در محيط پايگاه داده است . عمده دستئرات اين بخش عبارتند از :
Select : برای استخراج اطلاعات از يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
Update : برای به روز کردن اطلاعات يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
Delete : برای حذف اطلاعات از يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
Insert : برای ورود اطلاعات به يک جدول بانک اطلاعاتی مورد استفاده قرار می گيرد .
4 ) SQL Data Definition Language ( DDL )a
زبان تعريف اطلاعات SQL :
اين قسمت شامل دستورات زبان SQL برای ايجاد يا حذف جداول از بانک اطلاعاتی مورد استفاده قرار می گيرد . از اين قسمت همچنين می توان برای تعريف انديس ها ، کليد واژه ها ، ايجاد ارتباط و پيوند بين جدول ها و يا ايجاد محدوديت های دسترسی استفاده می شود .
عمده دستورات اين بخش عبارتند از :
Create Table : برای ايجاد يک جدول جديد به کار می رود .
Alter Table : برای تغيير اطلاعات موجود در يک جدول به کار می رود .
Drop Table : برای حذف يک يا چند جدول از بانک اطلاعاتی به کار می رود .
* Create Index : برای ايجاد انديس يا کليد واژه در يک جدول به کار می رود .
* Drop Index : برای حذف انديس يا کليد واژه در يک جدول به کار می رود .
* تعريف انديس Index :
انديس عبارتست از يک شماره که به هر يک از فيلدها در سطرهای يک جدول اختصاص داده می شود . انديس ها در پشت پرده جداول ايجاد شده و از ديد کاربر کاملا مخفی هستند . استفاده از انديس ها باعث می شود تا برنامه بتواند مقادير سطرهای مختلف را بر حسب مقدار يک فيلد و بر حسب شماره انديس آنها از کم به زياد يا بر عکس مرتب کند و در عمليات جستجو باعث بالا رفتن سرعت جستجو می شود .
دستور Select :
دستور Select برای انتخاب و استخراج اطلاعات مورد نظر از يک يا چند جدول و سپس مشاهده نتايج در يک جدول موقت استفاده می شود . شکل کلی اين دستور به شکل زير است :
Select نام ستون يا ستون های مورد نظر
From نام جدول
مثال : به طور مثال فقط می خواهيم ستون های Name و Family را از جدول Student ، مشاهده کنيم :
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
مثال
Select Name , Family
From Student کد
me Family
Ahamd Rezaee
Ehsan Amiri
Zahra Hoseini
Sahar Ahmadi
خروجی
نکته :
برای انتخاب کليه ستون های جدول می توان از نشانگر * در قسمت نام ستون استفاده کرد . در اين صورت تمامی ستون های جدول در خروجی نمايش داده خواهند شد :
Select *
From Students
واژه کليدی DISTINCT :
چنانچه در ستون های مورد جستجو ، موارد تکراری وجود داشته باشد در نتيجه خروجی نمايش داده خواهند شد . برای جلوگيری از چنين موردی و عدم نمايش موارد تکراری پس از دستور Select عبارت DISTINCT نوشته می شود :
Orders Table
Company Order Number
Dell 1012
Sega 1014
Dell 1016
Sony 1018
مثال
کد خروجی
Select DISTINCT Company
From Orders Company
Dell
Sega
Sony
Select Company
From Orders Company
Dell
Sega
Dell
Sony
دستور Where :
دستور Where برای افزودن شرط يا شرط هايی جهت محدود کردن نتايج جستجو و استخراج نتايج دقيقتر استفاده می شود . اين ذستور بايد پس از دستور Select و تعيين ستون ها از جدول مورد نظر به کار رود . با استفاده از عملگرهای AND ، OR و پرانتز می توان چندين شرط را با هم ترکيب کرد . برنامه فقط سطرهايي از جدول را که با شرط های ارائه شده سازگاری دارند را نمايش خواهد داد . اين دستور با عملگرهای زير می تواند به کار رود :
عملگر مفهوم عملگر مفهوم
= برابر با - مساوی != نا برابر با - نامساوی
> کوچکتر يا کمتر >= کوچکتر مساوی
< بزرگتر يا بيشتر <= بزرگتر مساوی
Between مابين دو مقدار Like تعيين يک قالب برای جستجو
مثال : انتخاب نام و نام خانوادگی دانشجويانی که شهر محل سکونت آنها تهران است از جدول Student به صورت زير انجام می شود :
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
مثال
کد خروجی
Select Name , Family
From Students
Where City = " Tehran " Name Family
Ahmad Rezaee
Zahra Hoseini
Hesam Razavi
* اعمال بيش از يک شرط و ادغام شرط ها با عملگر AND .
مثال : مشخصات نام و نام خانوادگی دانشجويانی که شهر آنها تهران بوده و نمره آنها بيش از پانزده است :
مثال
کد خروجی
Select Name , Family
From Students
Where City = " Tehran"
AND Grade > 15 Name Family
Ahmad Rezaee
Hesam Razavi
نکته : در هنگام اعمال شرط جستجو ، بايد مقادير رشته ای ( متن ) بين دو علامت " " قرار گرفته ، ولی درباره موارد عددی اين کار لازم نيست .
* عبارت like :
اين عبارت برای تعيين يک قالب خاص برای جستجو به کار می رود :
مثال : نام دانشجويانی که شهر محل سکونت آنها با حرف K شروع می شود :
Select Name From Students
Where City like "K%"
مثال : نام دانشجويانی که نام شهر محل سکونت آنها به an ختم می شود :
Select Name From Student
Where City Like "%an"
مثال : نام دانشجويانی که شهر محل سکونت آنها دارای حروف st است :
Select Name From Student
Where City Like "%st%"
مثال : نام دانشجويانی که شهر محل سکونت آنها 6 حرفی بوده و با حرف T شروع می شود :
Select Name From Student
Where City Like "T-----"
نتيجه گيری :
علامت % به معنای هر چند کاراکتر يا حرف دلخواه در قالب ارائه شده و علامت - نمايانگر يک کاراکتر دلخواه است .
دستور Insert :
این دستور برای وارد کردن و ايجاد يک رکورد جديد در جدول استفاده می شود .
شکل کلی این دستور به شرح زير است :
Insert into نام جدول
Values ( مقدار 1 ، مقدار 2 ، ... )
*می توان تعيين کرد که مقادير مورد نظر به ترتيب به کدام ستون های جدول وارد شوند . برای اين منظور ابتدا نام ستون های مورد نظر را به ترتيب جلوی نام جدول در يک پرانتز وارد کرده و با کاما از هم جدا می کنيم . سپس مقادير متناظر را به همان ترتيب پس از واژه Vaues در پرانتز وارد می کنيم .
نکته : چنانچه برای فيلد يا فيلدهايی مقداری در نظر گرفته نشود ، جای آنها در جدول خالی می ماند . فقط بايد به ترتيب نام ستون ها و مقادير دقت کرد .
مثال : مشخصات نام و نام خانوادکی را برای يک دانشجو به نام اميد صفوی را در جدول Student وارد کنيد :
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
- شرح دستور وارد کردن نام و وضعيت جديد جدول :
مثال
Insert into Student ( Name , Family )
Values ( "Omid" , "Safavi" ) کد
Student Table
Student ID Name Fa
ly 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
Omid Safavi
خروجی
دستور Update :
اين دستور برای تغيير اطلاعات موجود در يک فيلد و جايگزينی آن با يک مقدار جديد به کار می رود .
شکل کلی اين دستور به صورت زير است :
Update نام جدول
Set نام فيلد = مقدار جديد
Where نام فيلد = مقدار قبلی
مثال : نام دانشجو احمد رضايي را به علی رضايي تغيير دهيد :
Update Student
Set Name =" Ali "
Where Name = " Ahmad "
- همچنين می توان برای تعيين رکوردی که می خواهيم تغيير دهيم ، از مقدار يک فيلد ديگر به غير از فيلدی که می خواهد تغيير کنيد استفاده کنيم . در اين حالت از فيلد مذکور ، برای آدرس دهی رکورد مورد نظر استفاده می شود .
مثال : نام دانشجو با شماره دانشجويی 41252214 را به علی تغيير دهيد :
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
- اعمال دستور تغيير و وضعيت جديد جدول :
مثال
Update Student Set Name = " Ali "
Where StudentID = " 41252214 " کد
Student Table
tudent ID Name Family Major City Grade
41252214 Ali 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
خروجی
تغيير اطلاعات در بيش از يک فيلد در رکورد :
در اين حالت تمامی مقادير جديدی که می خواهيم تغيير دهيم ، جلوی واژه کليدی Set به ترتيب نوشته و با کاما از هم جدا می کنيم . سپس به همان ترتيب ، فيلدها با مقادير قبلی را در جلوی واژه Where می نوسيم . شکل کلی آن به صورت زير ااست :
Update نام جدول
Set فيلد 1 = مقدار جديد 1 ، فيلد 2 = مقدار جديد 2 ، ... .
Where فيلد 1 = مقدار قبلی 1 ، فيلد 2 = مقدار قبلی 2 ، ... .
- همچنين می توان فقط از يک فيلد در قسمت شرط برای آدرس دهی رکورد مورد نظر استفاده کرد .
مثال : نام ، نام خانوادگی و رشته تحصيلی دانشجو به شماره دانشجويي 41252214 را به ترتيب به علی ، سعيدی و نرم افزار تغيير دهيد :
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
- اعمال دستور تغيير و وضعيت جديد جدول :
مثال
Update Student
Set Name = " Ali " , Family = " Saeedi " , Major =" Soft Ware "
Where StudentID = 41252214 کد
Student Table
Student ID Name Family Major City Grade
41252214 Ali Saeedi Soft Ware Tehran 18
10724113 Ehsan Amiri Soft Ware Karaj 14
10254861 Zahra Hosini Hard Ware Tehran 17
27365187 Sahar Ahmadi Soft Ware Bam 16
3565441
Hesam Razavi Soft Ware Tehran 19
خروجی
دستور Delete :
از اين دستور برای حذف اطلاعات يک رکورد در جدول استفاده می شود .
شکل کلی اين دستور به شکل زير است :
Delete From نام جدول
Where نام و مقدار يک فيلد از رکورد مورد نظر
مثال : رکورد اطلاعات دانشجو به شماره دانشجويي 10724113 را حذف کنيد :
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
- اعمال دستور حذف و وضعيت جديد جدول :
مثال
Delete From Students
Where StudentID = "10724113 " کد
Student Table
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
10254861
hra Hosini Hard Ware Tehran 17
27365187 Sahar Ahmadi Soft Ware Bam 16
35654415 Hesam Razavi Soft Ware Tehran 19
خروجی
حذف کليه رکوردهای يک جدول :
اين امکان وجود دارد که بدون حذف يک جدول ، کليه رکوردهای درون آن را پاک کرد . اين به منزله خالی کردن جدول از اطلاعات است ، ولی ساختار ، انديس ها و ويژگی های جدول دست نخورده باقی مانده و فقط مقادير درون آن از بين می رود .
شکل کلی اين دستور به صورت زير است :
Delete From نام جدول
يا
Delete * From نام جدول
مثال : حذف کليه رکوردهای جدول Student :
Delete From Student
دستورات اوليه SQL
دستور Order By :
اطلاعات خروجی در دستور select به طور کلی بی نظم است و مقادير خروجی در ستون های جدول بر حسب مقدار هيج ستونی مرتب نيستند . اين دستور برای مرتب کردن اطلاعات جدول بر اساس مقادير يک يا چند ستون برحسب شاخص هايی مثل ترتيب حروف الفبا ، بزرگتر يا کوچکتر بودن اعداد و ... استفاده می شود .
شکل کلی اين دستور به صورت زير است :
Select نام فيلدهای مورد نظر From جدول مورد نظر
Order By فيلد مورد نظر
* فيلدهای معرفی شده در دستور Select از جدول مذکور نمايش داده شده و اطلاعات بر حسب ستون مقابل دستور Order By مرتب می شوند .
- عبارت ASC : به کار بردن اين دستور پس از دستور Order By باعث مرتب شدن اطلاعات درون جدول از کم به زياد می شود که البته اتخاب پيش فرض SQL است و نيازی به درج آن نيست .
عبارت DESC : به کار بردن اين واژه پس از دستور Order By باعث مرتب شدن اطلاعات جدول از زِياد به کم در اعداد و در حروف از z تا a می شود ، که در صورت نياز درج آن ضروری است .
مثال : از جدول Student ، مشخصات نام و نام خانوادگی دانشجويان رشته نرم افزار را بر حسب حروف الفبا نمايش دهيد :
Student Table
Student I
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
مثال
Select
ame , Family From Student
Where Major ="Soft Ware"
Order By Family کد
Name Family
Sahar Ahmadi
Ehsan Amiri
Hesam Razavi
خروجی
نکته :
همچنين می توان يک جدول را بر حسب بيش از يک فيلد مرتب کرد ، برای اين منظور پس از دستور Order By نام تمام فيلدهای مورد نظر را نوشته و آنها را با کاما از هم دا می کنيم . اولويت مرتب سازی با فيلدهای است که نام آنها زودتر ذکر شده باشند .
مثال : جدول مشخصات دانشجويان را بر حسب فيلد نام ( بر حسب حروف از a تا z ) و بر حسب نمره ( از کم به زياد ) مرتب کنيد :
Student Table
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 15
10724113 Ehsan Amiri Soft Ware Karaj 19
10254861 Zahra Hosini Hard Ware Tehran 18
27365187 Sahar Ahmadi Soft Ware Bam 20
35654415 Hesam Razavi Soft Ware Tehran 14
نکته : در جدول خروجی مثال زير به ارتباط مقادير ستون های Family , Grade توجه کنيد . باوجود اينکه نمره درسی دانشجو حسام رضوی از احمد رضايی کمتر است ، ولی به دليل اينکه اولويت مرتب سازی با نام خانوادگی است ، نام رضوی بالاتر از رضايی قرار گرفته است .
مثال
Select Name , Family , Grade From Student
Order By Family ASC , Grade DESC کد
Name Family Grade
Sahar Ahmadi 20
Ehsan Amiri 19
Zahra Hoseini 18
Hesam Razavi 14
Ahmad Rezaee 16
خروجی
نکته : می توان در دستور Order By به جای ذکر نام ستون ها از يک عدد به جای آن استفاده کرد . عدد استفاده شده شماره قرار گيری ستون مورد نظر در دستور Select است . برای مثال اولين ستون دستور Select عدد 1 و دومين ستون عدد 2 و ... را خواهند داشت . بازنویسی دستور بالا با استفاده از اين روش به صورت زير خواهد بود :
مثال
Select Name , Family , Grade from Student
Order By 2 ASC , 3 DESC کد
دستور AND & OR :
از And و Or برای ترکيب شرط ها در دستور Where استفاده می شود .
گاهی اوقات اطلاعات مورد نياز ما در دستور Select دارای چندين شرط مختلف است . به طور مثال ما مشخصات دانشجويان ترم دوم رشته نرم افزار که معدل آنها بيش از 15 است را می خواهيم . در اين حالت بايستی هر يک از شرط های مسئله را به تنهايی تعريف کرده و سپس آنها را با هم ترکيب کنيم . برنامه به ارزيابی هر يک از شرط ها پرداخته ، سپس نتايج حاصله را با هم ترکيب کرده و خروجی را تعيين می کند .
عملگر And برای اجرای دستور نياز دارد تا تمام شرط های تعيين شده برای آن درست باشد . در حالی که عمگر Or فقط نياز دارد که حداقل يکی از شرط های آن درست باشد . در جداول زير چگونگی برقراری نتيجه نهايی در هنگام استفاده از اين دو اوپراتور را مشاهده می کنيم :
در حالت 2 شرط :
OR
شرط 1 شرط 2 نتيجه
درست درست درست
درست غلط درست
غلط درست درست
غلط غلط غلط
AND
شرط 1 شرط 2 نتيجه
درست درست درست
درست غلط غلط
غلط درست غلط
غلط غلط غلط
چگونگی حالت های فوق را در مثال های زير بررسی می کنيم :
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
مثال 1 : مشخصات دانشجويانی را از جدول Student ارائه دهيد که نمره آنها بيش از 15 و رشته تحصيلی آنها نرم افزار باشد :
مثال
Select * From Student
Where Major = 'Soft Ware' AND Grade > 15 ; کد
Student ID Name Family Major City Grade
27365187 Sahar Ahmadi Soft Ware Bam 16
35654415 Hesam Razavi Soft Ware Tehran 19
خروجی
مثال 2 : مشخصات دانشجويانی را از جدول Student ارائه دهيد که نمره آنها بيش از 17 بوده يا رشته تحصيلی آنها نرم افزار باشد :
مثال
Select * From Student
where Major = 'Soft Ware' OR Grade > 17 ; کد
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
35654415 Hesam Razavi Soft Ware Tehran 19
خروجی
نکته مهم : همچنين می توان با استفاده از عملگر پرانتز چندين شرط مختلف را به صورت دسته های مجزا تعريف کرده و سپس همه آنها را با هم ترکيب کرد . در اين حالت شرط های هر گروه ابتدا در داخل پرانتز با هم ترکيب شده ، سپس نتيجه حاصله به عنوان نتيجه نهايي آن گروه شرط به دستور Where ارسال می شود و درنهايت نتايج تمام گروه ها با هم ترکيب می شوند .
مثال مهم : از جدول Student مشخصات دانشجويانی را ارائه دهيد که نمره آنها 18 و رشته تحصيلی آنها سخت افزار بوده يا نمره آنها 16 و رشته تحصيلی شان نرم افزار باشد :
مثال
Select * From Student
Where ( ( Grade = 18 AND Major = 'Hard Ware' ) OR ( Garde = 16 AND Major = 'Soft Ware' ) ) " ; کد
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
27365187 Sahar Ahmadi Soft Ware Bam 16
خروجی
________________________________________
دستور In :
اين دستور برای مشاهده اطلاعات رکوردهايی از جدول به کار می رود که شما مقدار دقيق حداقل يکی از فيلدهای آنرا می دانيد .
شکل کلی اين دستور به صورت زير است :
Select نام ستون From نام جدول
Where نام فيلد IN ( مقدار 1 ، مقدار 2 و ... )
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
مثال : از جدول Student مشخصات دانشجويانی را بدهيد که نام خانوادگی آنها احمدی يا رضايی است :
مثال
Select * From student
Where Family IN ( "Ahmadi" , "Rezaee" ); کد
Student Table
Student ID Name Family Major City Grade
41252214 Ahmad Rezaee Hard Ware Tehran 18
27365187 Sahar Ahmadi Soft Ware Bam 16
خروجی
دستور Between ... And :
اين دستور برای انتخاب اطلاعات در يک محدوده خاص ، در بين دو مقدار تعيين شده استفاده می شود . اين مقادير می تواند از نوع عددی ، متن يا تاريخ باشد . ( بايد نوع مدنظر با نوع داده ای فيلد های استفاده شده در دستور يکسان باشد ).
شکل کلی اين دستور به شکل زير است :
Select نام فيلدهای مورد نظر برای نمايش From نام جدول
Where نام فيلد مورد نظر
Between مقدار 1 And مقدار 2
نکته : در مورد داده های متنی ، ترتيب قرار گيری حروف الفبا مد نظر برنامه است .
مثال : از جدول Student مشخصات دانشجويانی را ارائه دهيد ، که نام خانوادگی آنها بين دو مقدار احمدی و حسينی باشد . در ضمن خروجی بر حسب نام خانوادگی به ترتيب حروف الفبا مرتب باشد :
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
مثال
Select * From Student
Where Family Between 'Ahmadi' And 'Hoseini'
Order By family; کد
Student ID Name Family Major City Grade
27365187 Sahar Ahmadi Soft Ware Bam 16
10724113 Ehsan Amiri Soft Ware Karaj 14
10254861 Zahra Hosini Hard Ware Tehran 17
مثال
نکته :
قرار گرقتن خود رکوردهای 2 مقدار تعيين شده در خروجی دستور Select ، بسته به نوع برنامه پايگاه و مديريت آن دارد . در برخی از پايگاه های داده رکوردهای هر 2 مقدار ابتدا و انتها نمايش داده می شود و در برخی هم فقط يکی از آنما نمايش داده می شود .
نکته :
برای نمايش اطلاعات خارج از محدوده تعيين شده از يک عملگر NOT قبل از دستور Between ... And استفاده می شود .
مثال : از جدول student مشخصات دانشجويانی زا ارائه دهيد که نمره آنها خارج از محدوده 12 تا 15 باشد . در ضمن مقادير خروجی بر حسب نام خانوادگی به ترتيب حروف الفبا مرتب باشد :
مثال
Select * From Student
Where Grade NOT Between 12 And 15
Order By Family ; کد
Student ID Name Family Major City Grade
27365187 Sahar Ahmadi Soft Ware Bam 16
10254861 Zahra Hosini Hard Ware Tehran 17
35654415 Hesam Razavi Soft Ware Tehran 19
41252214 Ahmad Rezaee Hard Ware Tehran 18
خروجی
ويژگی Alias :
همانطور که در تمام مثال های ذکر شده از دستور Select مشاهده کرديد ، نام يا عنواني که در بالای هر ستون در خروجی نمايش داده می شود ، همان نام فيلد مربوط به داده در جدول اصلی پايگاه داده است . گاهی اوقات تمايل بر اين است که اين نام را تغيير دهيم ، يا در مواردی که در بخش توابع SQL خواهيم ديد ، يک ستون جديد ايجاد می کنيم که بايد نامی برای آن تعيين شود . از ويژگی Alias برای در نظر گرفتن يک نام مستعار و مجازی برای قرار گرفتن در بخش عنوان هر ستون در خروجی دستور Select استفاده می شود .
شکل کلی اين دستور به صورت زير است :
Select نام فيلد As عنوان ) Alias ) From نام جدول
مثال : از جدول Student نام و نام خانوادگی دانشجويان رشته نرم افزار را بر حسب حروف الفبا در 2 ستون تحت عنوان های " نام " و " نام خانوادگی " نمايش دهيد :
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
مثال
Select Name As نام , Family As خانوادگی From Student
Where Major = 'Soft Ware'
Order By Family ; کد
نام خانوادگی
Sahar Ahmadi
Ehsan Amiri
Hesam Razavi
مثال
نکته مهم :
می توان بر روی مقادير ستون هايی که داده عددی دارند ، عمليات رياضی ( مثل ضرب ، تقسيم و ... ) انجام داده و سپس نتايج محاسبات را در يک ستون جديد با عنوان دلخواه تعيين شده توسط ويژگی Alias در خروجی دستور Select نمايش داد .در اين حالت ستون يا ستون های به جدول خروجی اضافه می شود .
در اين ارتباط مثالی را ذکر می کنيم :
مثال : فرض کنيد جدولی داريم به نام Books ، که در آن مشخصات چند کتاب مختلف قرار دارد . در اين جدول قيمت کتاب ها به دلار است و ما می خواهيم قيمت کتاب ها را در يک ستون جديد به تومان مشاهده کنيم . در اين به روش زير عمل می کنيم :
Books Table
Radif Name Publisher Price $
1 Learning Access Microsoft 8
2 Java Script Samples DeveloperStudio 6
3 HTML Reference DeveloperStudio 7
مثال
Select Radif , Name , Publisher , Price , Price * 840 As قيمت به تومان From Books ; کد
Books Table
Radif Name Publisher Price $ قيمت به تومان
1 Learning Access Microsoft 8 6720
2 Java Script Samples DeveloperStudio 6 5040
3 HTML Reference DeveloperStudio 7 5880
خروجی
پيوند جدول ها :
تا اين قسمت تمام مثال ها و مسئله هايی که در SQL به آنها پاسخ داديم ، مسئله هايی بودند که اطلاعات ما فقط از يک جدول استخراج می شد . اما در برنامه نويسی واقعی پايگاه داده ها ، ما مجبور هستيم که اطلاعات خود را از بيش از يک جدول استخراج کنيم . در اين حالت ما ابتدا بايد جدول هايی که می خواهيم اطلاعات را از آنها استخراج کنيم ، با هم پيوند دهيم . هدف از ايجاد اين ارتباط تلفيق اطلاعات در جدول ها و چاپ اطلاعات مورد نظر در خروجی است .
مفاهيم اوليه :
برای پيوند دادن جدول ها ابتدا بايد چند مفهوم زير را بشناسيم :
کليد اصلی : فيلد کليد اصلی در يک جدول ، فيلدی است که شرايط زير را داشته باشد :
مقدار آن برای هر نمونه رکورد ( سطر ) منحصر به فرد و غير تکراری باشد . به عبارت ديگر هيچ 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 زبان تخصصی نظری
خروجی
* با دقت در اطلاعات جدول های اصلی متوجه درست بودن نتايج خروجی خواهيد شد .
دستورات پيشرفته SQL
دستور Join :
از دستور Join نيز همانند قسمت قبلی ( پيوند جدول ها ) ، برای پيوند جدول ها در پايگاه داده ها استفاده می شود . تمام شرايط و ضوابط ذکر شده در قسمت قبل در مورد دستور Join نيز صادق است . به عبارت ديگر دستور Join روشی متفاوت برای پيوند جدول هاست که همان خروجی را توليد می کند .
برای پيوند جدول ها با استفاده از دستور Join ، از روش کلی زير استفاده می شود :
Select نام ستونهای مورد نظر جهت نمايش از هر 2 جدول
From نام جدول 1
Inner Join نام جدول 2
On برابر قرار دادن فيلدهای مشترک 2 جدول با هم
Where بقيه شرط ها ;
برخی از مثال های قسمت قبل را با دستور Join باز نويسی می کنيم .
مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که در ترم 1 سال تحصيلی 85 - 86 ، درس با کد 1012 را انتخاب کرده اند :
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
مثال
Select Students.Name , Students.Family , Seleciton.Term , Selection.Year
From Students
Inner Join Seleciton
On Students.Student ID = Selection.Student ID
Where Selection.Course ID = 1012 AND Term = 1 AND Year = ' 85 - 86 ' ; کد
Name Family Term Year
Ehsan Amiri 1 85 - 86
Ahmad Rezaee 1 85 - 86
خروجی
مثال : نام دروسی را ارائه دهيد که دانشجو با شماره دانشجويی 10254861 انتخاب کرده است :
مثال
Select Courses.Course ID , Courses.Co Title
From Courses
Inner Join Selection
ON Courses.Course ID = Selection.Course ID
Where Selection.Student ID = 102548861 ; کد
Course ID Course Title
1011 پايگاه داده
1013 زبان تخصصی
خروجی
Select های تو در تو :
می توان در درون يک دستور Select ، دستور Select ديگری را تعريف کرد . در اين حالت ابتدا دستور Select درونی اجرا شده و نتايج مورد نظر خود را که سطرهايی از جدول مربوط به آن هستند را استخراج کرده و آنرا به Select بيرونی انتقال می دهد . سپس Select بيرونی نيز اجرا شده و نتايج استخراجی خود را با استفاده از فيلد مشترک بين 2 جدول با سطرهای Select درونی پيوند زده و سطرهای مطابق با شروط تعيين شدهخ را نمايش می دهد .
از حالت Select های تو در تو برای پيوند جدول ها استفاده می شود و برای زمانی مناسب است که بخواهيم ستون های خروجی در دستور Select فقط از يک جدول که در بيرونی ترين Select تعريف شده اند ، نمايش داده شوند .
نکته : به تعداد دستورات Select تو در تو ، سطح آن می گويند .
Select های 2 سطحی :
در اين حالت يک دستور Select در درون دستور Select ديگری تعريف می شود و برای پيوند 2 جدول با استفاده از فيلد مشترک استفاده می شود . نتايج خروجی فقط می تواند شامل سطرهای از جدول Select بيرونی باشد .
شکل کلی پيوند 2 جدول بااستفاده از Select 2 سطحی به صورت زير است :
Select نام ستون های مورد نظر برای نمايش از جدول بيرونی
From نام جدول 1
Where نام فيلد مشترک 2 جدول IN ( Select نام فيلد مشترک 2 جدول
From نام جدول 2
Where شرط ها ... ) ;
مثال های قسمت های قبل پيوند جدول ها را با استفاده از Select های تو در تو بازنويسی می کنيم .
مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که در ترم 1 سال تحصيلی 85 - 86 ، درس با کد 1012 را انتخاب کرده اند :
مثال
Select Name , Family
From Students
Where Student ID IN ( Select Student ID
From Selection
Where Course ID = 1012 AND Term = 1 AND Year = ' 85 - 86 ' ) ; کد
Name Family
Ehsan Amiri
Ahmad Rezaee
خروجی
نکته : تفاوت اين حالت با مثال های قبلی در اين است که ستون های نمايش داده شده فقط از يک جدول ( جدول Select بيرونی ) نمايش داده می شوند .
مثال : نام دروسی را ارائه دهيد که دانشجو با شماره دانشجويي 10254861 انتخاب کرده است :
مثال
Select Coures ID , CoTitle
From Courses
Where Course ID IN ( Select Course ID
From Selection
Where Student ID = 10254861 ) ; کد
Course ID Course Title
1011 پايگاه داده
1013 زبان تخصصی
خروجی
Select های 3 سطحی :
در اين حالت 3 دستور Select به صورت تو در تو تعريف شده اند و برای پيوند 3 جدول به صورت 2 به 2 و با استفاده از فيلد های مشترک استفاده می شود . نتايج خروجی فقط می تواند شامل ستون هايي از بيرونی ترين Select تعريف شده باشد . در اين حالت از جدول دوم برای پيوند جدول های 1 و 3 استفاده می شود .
شکل کلی پيوند 3 جدول با استفاده از Select های 3 سطحی به صورت زير است :
Select نام ستون های مورد نظر جهت نمايش از جدول 1
From نام جدول 1
where نام فيلد مشترک جدول 1 و 2 IN ( Select نام فيلد مشترک جدول 1 و2
From نام جدول 2
Where نام فيلد مشترک جدول 2 و 3 IN ( Select نام فيلد مشترک جدول 2 و 3
From نام جدول 3
Where بقيه شرط ها ... )) ;
مثال : نام و نام خانوادگی دانشجويانی را ارائه دهيد که حداقل يک درس از نوع نظری را انتخاب کرده باشد :
مثال
Select Name , Family
From Students
where Student ID IN ( Select Student ID
From Selection
Where Course ID IN ( Select Course ID
From Courses
Where CoType = ' نظری ' )) ; کد
Name Family
Zahra Hosini
Sahar Ahamadi
Hesam Razavi
خروجی
________________________________________
دستور Unoin :
از دستور Union برای ترکيب و ادغام دو يا چند ستون مختلف از 2 يا چند جدول و نشان دادن آنها در يک ستون مشترک استفاده می شود .
در اين دستور ، نوع داده ای ستون های انتخاب شده برای ترکيب بايد يکسان باشند . دستور Union در هنگام ترکيب فيلد ها ، در صورت برخورد با مقادير تکراری آنها را حذف کرده و از هر مقدار يک نمونه را نمايش می دهد . برای مشاهده تمام مقادير ، حتی مقادير تکراری بايد از دستور Union ALL استفاده کرد .
شکل کلی استفاده از اين دستور به صورت زير است :
SQL Statment 1 ( نام فيلد 1 اتخابی )
Union
SQL Statment 1 ( نام فيلد 2 اتخابی )
مثال : شماره دانشجويی ، دانشجويانی را بدهيد که يا دانشجوی رشته نرم افزار بوده يا در درس زبان تخصصی با کد 1013 ثبت نام کرده اند :
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
مثال
Select Stuedent ID From Students
Where Major = ' Soft '
Union
Select Student ID From Selection
Where Course ID = 1013 ; کد
Student ID
10254861
10724113
27365187
35654415
خروجی
توجه : می توان دستورات Select تعريف شده در دستور Union را تا حد نياز گسترش د اد . به عبارت ديگر هر دستور مجاز Select در اين قسمت قابل تعريف است . برای مثال می توان در دستورات Select تعريف شده ، جدول ها را به هم پيوند داد و فقط يک ستون خروجی توليد کرد و سپس آن را با ستون طرف ديگر پيوند زد .
نکته : می توان در دستور Union بيش از يک ستون مجزا را تعريف کرد . فقط بايد تعداد ستون های و ترتيب نوع داده ای ستون های تعريف شده يکسان باشند . برنامه مقادير ستون ها را به ترتيب با هم پيوند می دهد .
مثال : شماره دانشجويي ، نام و نام خانوادگی دانشجويانی را بدهيد که در رشته سخت افزار تحصيل کرده يا حداقل يک درس از نوع نظری را انتخاب کرده باشند :
مثال
Select Student ID , Name , Family
From Students
Where Major = ' Hard '
Union
Select Student ID , Name , Family
From Students , Seleciton , Courses
where Students.Student ID = Selection.Student ID
AND Selection.Course ID = Courses.Course ID
AND CoType = ' نظری ' ; کد
Student ID Name Family
10254861 Zahra Hosini
107241113 Ehsan Amiri
27365187 Sahar Ahmadi
35654415 Hesam Razavi
خروجی
1. سایت
http://www.developercenter.ir
2 . سایت
www.cpn-pc.blogfa.co
3. سایت
www.mspsoft.com
+ نوشته شده در سه شنبه بیست و ششم آذر ۱۳۹۲ ساعت 21:45 توسط
|