آموزش SQL Server – قسمت ششم

در قسمت پنجم آموزش SQL Server ،جدول اتصال یا Junction table را به شما آموزش دادیم.دراین قسمت قصد داریم کد نویسی SQL Server  و همچنین aggregate functions را به شما آموزش بدیم.

آموزش SQL Server – قسمت ششم

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

 برنامه نویسی با SQL Server

ما به بانک کتابخانه که در اموزش اول ساخته ایم و به طور کامل اموزشش را داده ایم یک جدول به نام Group اضافه میکنیم که گروهای کتاب های موجود در کتابخانه را مشخص میکند.

“برای نام گزاری جدول گروها اگر بخواهیم از کلمه Group به تنهایی استفاده کنیم باید باید انرا در [] براکت قرار دهیم چون کلمه Group یک کلمه رزرو شده میباشد و نمیتوان از ان استفاده کرد حال اگر بخواهیم از ان استفاده کنیم باید انرا در براکت قرار دهیم ولی میتوان بجای اینکه از کلمه رزرو شده استفاده کنیم کلمه رزرو شده را با کلمه دیگری ترکیب کنیم در این حالت دیگر نیازی به براکت هم نیست”

پس از ایجاد جدول گروه باید کلید خارجی ای دی گروه را در جدول هایی مثل امانت و کتاب و جدول رابط و متصل Amanat_Ketab اضافه کنیم و بعد ارتباطات انهارا با کلید اصلیشان همانند دو روش قبلی که در اموزش های قبل گفته شد برقرار کنیم “پیشنهاد میشود در دیاگرام های پیچیده از روش دوم استفاده کنید” که در اخر دیاگرام به شکل زیر در میاید

آموزش SQL Server – قسمت ششم

حال برای کدنویسی باید همانند شکل زیر در نوار منو بالای برنامه به روی گزینه New Query که با ستاره مشخص شده کلیک میکنیم تا یک صفحه سفید رنگ و خالی اماده کد نویسی باز شود.

آموزش SQL Server – قسمت ششم

حال باید کد های مورد نظر را وارد و اجرا میکنیم برای مثال اولین برنامه مورد نظر برای اموزش مثالی برای نمایش محتوی جدول هاست.

همانطور که میدانید در زبان SQL Server سه دستور اصلی وجود دارد که شامل *DELET , UPDATE , INSERT , SELECT* میباشد در ابتدا ما میخواهیم با دستور معروف و بسیار کاربردی SELECT کار کنیم.

ابتدا ساده ترین نوع استفاده از دستور SELECT را مورد بررسی قرار میدهیم

آموزش SQL Server – قسمت ششم

همانطور که در تصویر بالا مشاهده میکنید در مرحله اول دستور را در محیط Query مینویسیم*توجه داشته باشید که SQL به حروف بزرگ و کوچک حساس نیست* که ما در اینجا دستور ساده select * from Tbl_Ketab را قید کرده ایم به معنای اینکه تمامی ستون های جدول کتاب را نمایش بده * به معنای تمامیه ستون ها میباشدپس از نوشتن کد باید به روی دکمه ای که با کادر مشکی رنگ مشخص شده به نام Execute کلیک کنیم تا کد ها اجرا شوند و همانگونه که در تصویر مشاهده میکنید در پایین صفحه در قسمت Result نتیجه دستور نوشته شده را نمایش میدهد که همانگونه که واضح است تمامی ستون ها و مقادیر درون جدول کتاب را به ما نشان میدهد.

همچنین در پایینه قسمت Result یک نوار به رنگ زرد وجو دارد که همانگونه که مشاهده میکنید قید کرده ۱۹ Rows بدین معنی که نتیجه دستور در ۱۹ خط نمایش داده شده.

حال میتوان گفت که به جای تمامیه ستون ها تنها بعضی از انهارا نمایش دهد مثلا فقط ستون کد و نام کتاب و ای دی گروه که برای این کار باید کد را به صورت زیر نوشت

select Code_Ketab,Name_Ketab,ID_Group from Tbl_Ketab

پس از نوشتن این دستور و کلیک کردن دکمه Execute نتیجه به صورت زیر قابل مشاهده است همانگون که میبینید فقط ستون هایی که مشخص کرده اید نمایش داده شده

آموزش SQL Server – قسمت ششم

حال میخواهیم محتویات دو جدول را برای ما نمایش دهد برای اینکار به صورت زیر کد بنویسیم

آموزش SQL Server – قسمت ششم

همانگونه که در تصویر فوق مشاهده میکنید ما نتیجه مطلوبی را دریافت نکرده ایم و چیزی که به ما نشان داده شده در واقع حاصل ضرب ستون Group_name و Name_Ketab را مشاهده میکنیم همانگونه که با کادر مشکی رنگ در پایین صفحه مشخص شد ۳۲۳ سطر تشکیل شده و اگر در سطرها دقت کنید میبینید که برای هرکدام از گروههای کتاب تعریف شده تمامی کتاب هایی که در کتابخانه وجود دارد و ما وارد سیستم کرده ایم را شامل شده مثلا در گروه اجزا کامپیوتری تمام کتاب ها وجود دارد همچنین در گروه زیست دوباره تمامی کتاب ها از ابتدا نام برده شده که این اصلا عملکرد خوبی نیست و علاوه بر اینکه وقت و زمان کامپیوتر را زیاد تلف میکند بلکه نتیجه مطلوب را نیز به ما نمیدهد.

برای رفع این مشکل باید کد را به صورت زیر تغییر داد

آموزش SQL Server – قسمت ششم

همانگونه که در تصویر فوق مشخص میباشد در ابتدا باید به یک *نکته توجه داشت که قسمت بالای صفحه که با فلش مشخص شده باید نام بانکی باشد که قصد کار و کد نویسی به ری انرا دارید* سپس برای تصحیح کردن کد نوشته شده باید از شرطی که با کادر مشکی رنگ مشخص شده استفاده کرد این شرط بدین معنی است که *هرجا فیلد ID در جدول گروه که به عنوان کلید اصلی است برابر با فیلد ID_Group که در جدول کتاب برابر با کلید خارجی است را به ما نمایش بده* در این صورت برنامه متوجه میشود که کدام کتاب مربوط به کدام گروه است و انرا درست در سطر گروه خودش نمایش میدهد همانگونه که میبینید تنها ۱۹ سطر به ما نمایش داده که دقیقا به تعداد کتابهای ثبت شده در سیستم میباشد.

این شرط را جابجا نیز میتوان نوشت هیچ تفاوتی در نتیجه ندارد و باز هم نتیجه درست و صحیح را نمایش میدهد

where Tbl_Ketab.ID_Group = Tbl_Group.ID;

حتی میتوان در دستور مشخص کرد که تنها بعضی ستون هارا با این شرایط نمایش دهد مثلا در کد زیر تنها نام کتاب و نام نویسنده و نام گروه مربوطه را نمایش میدهد

select Name_Ketab,Nevisande,ID_Group from Tbl_Group,Tbl_Ketab
where Tbl_Ketab.ID_Group = Tbl_Group.ID;

حتی میتوان اطلاعات ۳ جدول را انتخاب کرد و نمایش داد اما باید توجه به درست نوشتن شرط هم داشت چون اگر فقط مثل اولین مثال بنویسیم Select * From Tbl_Group,Tbl_ketab,Tbl_Amanat تمامی ستون های هر س جدول را با هم ضرب میکرد و حدودا چندین هزار سطر بوجود میامد پس باید شروط صحیحی قرار داد برای این دستور شروط به صورت زیر میباشد

Select * from Tbl_Group,Tbl_Ketab,Tbl_Amanat
where Tbl_Amanat.ID_Group=Tbl_Group.ID and Tbl_Amanat.Code_Ketab=Tbl_Ketab.Code_Ketab

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

حال میتوان ای کد را طوری تغییر داد که نتیجه تمیز تر و زیباتری به ما بدهد برای اینکار خودمان ستون های مشخص و مورد نیاز را برای نماش مشخص میکنیم.

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

آموزش SQL Server – قسمت ششم

حال مخواهیم نمونه دیگری از کدنویسی را مرور کنیم.به شکل زیر توجه کنید

آموزش SQL Server – قسمت ششم

همانگونه که در شکل فوق مشاهده میکنید ما قصد داشته ایم ستون های نام کتاب و نام نویسنده و نام گروه ها و همچنین حاصل ضرب ستون قیمت و ستون تعداد در جدول کتاب را نیز محاسبه و به ما نشان دهد و درضمن برای ستونی که حاصل ضرب را نیز مشاهده میکند یک نام گزاشته ایم با دستور As .

همانگونه که در عکس با خط قرمز مشخص شده برای ضرب دوستون باید ابتدا نام جدول که ستون مورد نظر در ان وجو دارد را نوشت سپس با تایپ کردن یک علامت دات(.) میتوان تمامی ستون های جدول ذکر شده را در یک لیست باز شو دید پس ستونی که قصد ضرب کردنش را داریم را انتخاب میکنیم سپس علامت ضرب (*) و بعد ستون دوم که برای ضرب نیاز است را قید میکنیم.

اگر اقدامی برای نام گزاری ستون ایجاد شده از حاصل ضرب نکنیم ستون با نام No Column Name ایجاد میشود و حاصل ضرب هر سطر را در خود قرار میدهد اما برای زیبایی کار بهتر است از نامی برای ستون ایجاد شده استفاده کنیم که اینجا از نام Total استفاده شده و همانگونه که مشخص شده رای نام گزاری نیز کافیست دقیقا بعد از ستونی که قصد ایجادش را داریم دستور As و بعد نام انتخابی را مشخص کنیم.

حال میتوان در ادامه این دستور شرط دیگری نیز قید کرد که مثلا تنها کتابهایی به ما نمایش بده که حاصل ضرب قیتشان و تعدادشان بیشتر از ۶۰۰۰۰ شود برای این کار کافیست یک شرط ساده به کدهایتان اضافه کنید که به صورت زیر میباشد

Select Name_Ketab, Nevisande, Group_name ,Tbl_Ketab.Qeymat *
 Tbl_Ketab.tedad as total from Tbl_Group,Tbl_Ketab
where Tbl_Group.ID=Tbl_Ketab.ID_Group and Tbl_Ketab.Qeymat * Tbl_Ketab.tedad>60000

همانگونه که مشاهده میکنید با نوشتن عبارت And گفته ایم که ما دو شرط مختلف داریم و باید هرذ=دوی انها در نتیجه لحاظ شود و پس از عبارت and نیز شرط مورد نظر را نوشته ایم بدین صورت که گفته ایم اگر حاصل ضرب قیمت کتاب و تعدادش بیش از ۶۰۰۰۰ بود را نمایش بده که پس از نوشتن این تکه کد و کلیک کردن دکمه Execute خواهید دید تنها کتابهایی نمایش میدهد که حاصل ضریب این دو ستون در انها بیشتر از ۶۰۰۰۰ بوده.

آموزش SQL Server – قسمت ششم

aggregate functions

این توابع بسیار پرکاربرد در زبان اس کیو ال میباشد که برخی از این توابع مثل Sum برای جمع و Min برای میانگین وMax برای بیشترین مقدار و … در ادامه چند مثال را با این توابع کار میکنم.

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

Select Group_name, sum(Tbl_Ketab.tedad) from Tbl_Group,Tbl_Ketab
where Tbl_Group.ID=Tbl_Ketab.ID_Group
group by Group_name

نتیجه دستورات بالا به صورت زیر است همانگونه ک مشاهده میکنید در بالا ما ازدستورSumبرای جمع تعداد کتاب های هرگروه استفاده کرده ایم *توجه داشته باشید هرگاه از aggregate function ها استفاده میکنیم باید در اخر نیز از دستور Group By استفاده کنیم.این دستور مشخص میکند که اطلاعات مورد نظر ما بر چه اساسی نمایش داده شود به عبارت دیگر هر کدام از ستون هایی که در جلوی دستور Select نوشته شده را ما در جلوی Group By بنویسیم تمام اطلاعات براساس ان به ما نمایش داده میشود. که ما در اینجا از ستون Group_Name استفاده کرده ایم چون میخواستیم براساس نام گروه ها برای ما مشخص کند هرگروه چه تعداد کتاب دارد.

وچون برای ستون ایجاد شده توسط دستور Sum هیچ نامی در نظر گرفته نشده پس از عبارت No Column Name استفاده کرده ولی بهتر است برای ان نامی در نظر بگیریم که برای این کار فقط کافیست خط اول دستورات را به صورت زیر تصحیح کنیم و خطوط بعدی را نیز به صورت قبل مینویسیم.

Select Group_name, sum(Tbl_Ketab.tedad) as totalsum from Tbl_Group,Tbl_Ketab

حال اگر بخواهیم برای همین دستور یا به عبارت دیگر کدی که در این از aggregate function استفاده شده شرطی بگزاریم دیگر نمیتوان از عبارت Where استفاده کرده بلکه باید از دستور Having برای شرط گزاری استفاده کنیم.

مثلا در همان برنامه بالا که تعداد کتاب های هرگروه را مشخص میکند میخواهیم شرطی لحاظ شود که فقط ان گروههایی را نمایش بده که تعداد کتابشان بیش از ۲۰ باشد برای این کار باید به صورت زیر نوشت

Select Group_name, sum(Tbl_Ketab.tedad) as TotalSum from Tbl_Group,Tbl_Ketab
where Tbl_Group.ID=Tbl_Ketab.ID_Group
group by Group_name
having sum(Tbl_Ketab.tedad)>20

همانگونه که مشاهده میکنید پس از دستور Group By از شرط استفاده شده که با عبارت Having مشخص کرده ایم که فقط ان گروه از کتاب ها را نشان بده که تعداد کتابشان بیشتر از ۲۰ باشد پس از اجرای این دستور نتیجه مورد نظر را خواهیم داشت که به شکل زیر میباشد

آموزش SQL Server – قسمت ششم

در اخر نیز برای ذخیره Query هایتان میتوانید با کلید های Ctrl+S با انتخاب نام مشخص انهارا ذخیره کنید.

مدرس : خانم مهندس داودی نژاد

آموزش SQL Server – قسمت پنجم

آموزش SQL Server – قسمت هفتم