آشنایی با دستور GROP BY

  1   تعریف SUB Query  و عملکرد آنها

2     آشنایی با زبان تعریف داده ها و عملکرد اجزای آن

   

 

 

 

 

 

 

 

   آشنایی با دستور GROP BY

از این دستور برای دسته بندی اطلاعات یک جدول استفاده می شود . میتوانیم بر اساس چیزی که در نظر داریم مثلا : بر اساس کد شهر ، سن و .... داده های خود را دسته بندی کنیم .پیش از این که به نحوه ی استفاده از این دستور بپردازیم باید با AggregatedFunctions آشنایی داشته باشیم که شامل دستوراتی مانند : COUNT ، MIN ، MAX ، AVG ، SUM و ... میباشد که بیشتر با دستورات ذکر شده سرو کار داریم .

همانطور که در عکس زیر مشاهده میکنید در قسمت 1 طریقه ی استفاده از دستورات ذکر شده را بیان کرده است . پس از عبارت AS نام ستونی را که میخواهیم نتیجه در آن درج شود را مینویسیم . تمامی دستورات قسمت 1 پس از بررسی شرطی که در قسمت 2 آمده اجرا میشوند . یعنی ابتدا آنهایی را که سنشان بالای 25 سال است انتخاب و سپس دیگر دستورات رو آنها اعمال میشود . در قسمت 3 هم نتایج به دست آمده را میبینید :

حال میخواهیم ببینیم که دستور  GroupBy چگونه استفاده میشود . همانطور که قبلا ذکر شد این دستور درون Select استفاده میشود . برای یاد آوری الگوی زیر را ببینید :

 

همانطور که میبینیم دستور GroupBy پس از دستور Where می آید . حال میخواهیم با استفاده از GroupBy داده های جدول را دسته بندی و عملیات مورد نظر را روی آنها انجام دهیم طبق قسمت 1 شکل زیر ابتدا داده ها بر اساس کد شهر دسته بندی میشوند . یعنی مثلا آنهایی که CityID آنها مشابه است در یک گروه قرار میگیرند . سپس دستورات قسمت 2 برای هر گروه اجرا میشوند .

حال لازم است دو نکته در این قسمت ذکر شود :

1-  برای آوردن یک شرط پس از GroupBy از دستور  Having استفاده میکنیم .

2-  در دستور Having مجاز به استفاده از اسامی مستعار نیستیم .

میخواهیم با یک مثال دیگر مطلب روشن تر و واضح تر شود . میخواهیم اطلاعات آماری شهرهایی که میانگین سنی افراد آن زیر 40سال است را به دست آوریم . بدیهی است که شرط بالای 40 سال باید پس از گروه بندی داده ها اعمال شود . پس باید از دستور Having  استفاده کنیم . همانطور که در تصویر میبینید از ستون AvgAge استفاده نکردیم چون نمیتوان در Having از نام مستعار استفاده کرد . پس باید از عبارتی که نتیجه آن در آن ستون نمایش داده میشود استفاده کنیم (AVG(Age)) :

حال میخواهیم کل عملیات بالا را برای افراد بالای 25سال انجام دهیم . یعنی اطلاعات آماری شهرهایی را که میانگین سنی افراد آنها کمتر از 40سال است ، از شهرهایی بدست آید که افراد آن بالای 25سال سن دارند . یعنی ابتدا باید اطلاعات شهرهایی که سن افراد آن بالای 25سال است جدا شوند و سپس شهرهایی که میانگین سنی در آن زیر 40 سال است از میان آن انتخاب و نمایش داده شود :

تعریف SUB Query  و عملکرد آنها

به Query های تو در تو گفته میشود . یعنی میتوان از یک Query در دل یک Query دیگر استفاده نمود . SubQueryها در اهای خاصی از Select استفاده میشوند . بعد از Select و From و Where میتوانند استفاده شوند .

نکته : نتیجه SubQuery ها فقط باید یک سطر و ستون باشد . یعنی باید فقط یک مقدار را برگردانند .

نحوه استفاده بعد از عبارت Select :

 

نحوه استفاده بعد از عبارت From :

نحوه استفاده بعد از عبارت Where :

-  حال میخواهیم اطلاعات دانشجویانی را به دست آوریم که در شهری  هستند که بیشترین جمعیت را دارد .

ابتدا باید کدی بنویسیم که مجهول سوال ما پیدا شود یعنی بدست آوردن شهری که بیشترین جمعیت را دارد . پس با استفاده از دستور  MAX بیشترین جمعیت را پیدا کرده و سپس آنرا در قسمت Where که در Selectی که آی دی(ID) شهر را برمیگرداند قرار میدهیم . در نهایت کل دستورات را در قسمت Where که در Selectی که نام دانشجوها را برمیگرداند قرار دارد ، قرار میدهیم . پس ما برای حل این مساله از داخلی ترین قسمت شروع و تا بیرونی ترین قسمت آن به ترتیب حرکت کردیم .شکل زیر دستورات مربوطه را نشان میدهد :

نکته : اگر ما مثلا 3شهر پرجمعیت که جمعیت آنها برابر باشد داشته باشیم دستورات بالا فقط یکی از آنها را برمیگرداند . که میتوانیم با استفاده از دستور IN در قسمت CityID = به جای = ، این ایراد را برطرف کنیم.

یک مثال دیگر :

دستوری بنویسیم که میانگین سنی افراد بالای 25 آن شهرها از میانگین سنی کل افراد کمتر است .

خب ابتدا میانگین سن کل افراد را به دست می آوریم . سپس باید دستوری بنویسیم که افراد بالای 25سال را برگرداند پس شرط بالای 25سال را در Where که در Select آمده باید بنویسیم . همچنین این شرط باید بر روی دانشجویان هر شهر اعمال شود پس باید شهر ها با GroupBy گروه بندی شوند . حالا عبارت دستوری ای که برای میانگین سنی کل افراد نوشتیم در Having (میانگین سنی افراد بالای 25سال را با میانگین سنی کل افراد مقایسه میکند) که پس از GroupBy می آید قرار میدهیم . و در ادامه مانند شکل زیر عمل میکنیم :

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

DDL(Data Difinition Language) : زبان تعریف داده ها مانند :CREATE و ALTER و DROP . که در حقیقت با Objectهای پایگاه داده سروکار دارند مانند جداول وViewها و Functions و ....  .

در این قسمت میخواهیم طریقه کار با دستورات DDL را یاد بگیریم . دستور CREATE برای ایجاد کردن یک Object کاربرد دارد :

 

به جای DataBase میتوانیم نام شی مورد نظر را وارد کنیم مانند : Table یا View . TestDB هم نام شی است که میتوانیم به دلخواه تغییر دهیم . نوع دیگری از استفاده دستور CREATE را میبینید :

 شکل زیر نشان میدهد که چگونه با CREAT می توان یک View ساخت :

 شکل زیر نشان میدهد که چگونه میتوانیم با استفاده از دستور ALTER آن View که ساختیم را ویرایش کنیم :