در اینجا سلسله مباحث آموزشی در مورد ساختار فیزیکی فایلهای MDF و بررسی جزئی تر به نحوه ذخیره سازی اطلاعات ، بررسی Query ها ، جداول ، ایندکسها و... مطرح خواهد شد.
Pivot :
این دستور العمل برای تبدیل مقادیر موجود در سطرها به ستونها و استفاده از توابع محاسباتی می باشد و عملیاتی مانند Cross-Tab در Excel را پیاده سازی می کند.
ساختار دستوری به شکل زیر می باشد:
Select Field1,Field2
From(
Table_Source
)
Pivot ( Aggregate Function(Value_Column)
For Pivot Column
In(
) Table Alias
در Select اول فیلدهایی را که در Column_list قسمت Pivot استفاده کرده و فیلدهایی که براساس آن میخواهیم اطلاعات گروه بندی شود را می آوریم.
در قسمت Table_Source که در واقع یک Derived Table هست ، اطلاعات مورد نیاز از جدولی که میخواهیم محاسبات را انجام دهیم فراخوانی می کنیم.
در قسمت Pivot تابع محاسباتی روی یکی از فیلدهای Select دوم صورت خواهد گرفت .
بخش For نام ستونی که میخواهیم مقادیر آن ستون را به ستونهای مختلف تبدیل کنیم می آوریم.
بخش In لیست مقادیری که میخواهیم به ستون تبدیل شود.
برای روشن شدن مطلب به مثال زیر توجه کنید:
یک جدول هدر فاکتور فروش با مشخصات زیر داریم:
Create Table HdrSale
( Id Int,
FactorDate char(8),
Cust_Name Varchar(30)
)
یک جدول ریز فاکتور فروش با مشخصات زیر داریم :
Create Table DetailSale
( Id Int,
IdHdrSale int,
Quantity int,
ProductCode int
)
در جدول اول اطلاعات زیر را اضافه می کنیم :
Insert into Hdrsale
values(1,'89/10/01','Mohammadi'),
(2,'89/10/02','Karimi'),
(3,'89/10/02','Sadeghi'),
(4,'89/10/01','Mohammadi'),
(5,'89/10/02','Karimi')
در جدول دوم اطلاعات زیر را وارد می کنیم :
Insert into DetailSale
Values(1,1,2,10),
(2,1,3,20),
(1,2,10,10),
(1,3,5,20),
(2,3,10,10),
(1,4,5,10),
(2,4,4,20),
(1,5,20,10)
حال اگر Query زیر را اجرا کنیم لیست تمام فاکتورهای مشتری در تاریخ های مختلف را خواهیم داشت .
Select Hs.FactorDate,Ds.Quantity,Hs.Cust_Name
From HdrSale Hs
Inner Join
DetailSale DS
On Ds.IdHdrSale=Hs.Id
حال ما میخواهیم به تفکیک افراد ببینیم در هر روز چه تعداد جنس فروخته شده است.
میخواهیم نتیجه زیر را داشته باشیم:
|
Date |
Karimi |
Sadeghi |
Mohammadi |
|
89/10/01 |
0 |
0 |
14 |
|
89/10/02 |
30 |
15 |
0 |
برای اینکار باید از Pivot استفاده کنیم:
با اجرای Query زیر نتیجه بالا بدست خواهد آمد :
Select FactorDate,Isnull([Karimi],0) as [Karimi],isNull([Mohammadi],0) as [Mohammadi],
IsNull([Sadeghi],0) as [Sadeghi]
From(
Select Hs.FactorDate,Ds.Quantity,Hs.Cust_Name
From HdrSale Hs
Inner Join
DetailSale DS
On Ds.IdHdrSale=Hs.Id
) As A
Pivot
(Sum(A.Quantity)
For Cust_Name In([Karimi],[Mohammadi],[Sadeghi])
) As B
Order by FactorDate
در Select دوم که داخل From هست هر فیلدی که از جداول بیاریم مانند فیلدهای جلوی Group By می ماند و نتیجه Query بدست آمده تغییر خواهد کرد. به طور مثال اگر فیلد Hs.id نیز اضافه شود دیگر نتیجه فوق را نخواهیم داشت و Sum براساس Group By براساس فیلدهای تاریخ و Id خواهد بود.
دستور Select:
در زیر ساختار بسیار ساده دستور Select را ملاحظه می کنید.
SELECT select_list
FROM table_list
Select_list شامل فیلدهایی می شود که میخواهیم از جدول مربوطه فراخوانی کنیم. اگر به جای نام فیلدها از * استفاده کنیم تمام فیلدهای موجود در Table_List را به ما نمایش میدهد.
Table_List شامل جداول و یا View هایی هست که ما میخواهیم اطلاعات را از آنها فراخوانی کنیم.
در مثال زیر با استفاده از دستور select یک سری اطلاعات را از دیتابیس AdventureWorks فراخوانی می کنیم.
USE AdventureWorks
GO
SELECT NationalIDNumber,
LoginID,
JobTitle
FROM HumanResources.Employee
با اجرای دستورات بالا نتیجه ای معادل شکل زیر بدست خواهد آمد.
|
JobTitle |
LoginID |
NationalIDNumber |
|
Chief Executive Officer Vice President of Engineering Engineering Manager Senior Tool Designer … Sales Representative Sales Representative Sales Representative |
adventure-works\ken0 adventure-works\terri0 adventure-works\roberto0 adventure-works\rob0 … adventure-works\rachel0 adventure-works\jae0 adventure-works\ranjit0 |
295847284 245797967 509647174 112457891 ... 668991357 954276278 134219713 |
(290 row(s) affected)
شرح دستور :
خط اول دستور برای تغییر دیتابیس پیش فرض هست. زیرا زمانی که برای اولین بار به SSMS (SQL Server Management Studio ) وصل می شوید پیش فرض Database در حال استفاده هست که در User شما به عنوان Default Database تعیین شده باشد. با این دستور به دیتابیس مورد نظر وصل خواهیم شد.
USE AdventureWorks
GO
خطوط زیر مشخص میکند کدام ستونها به ما نمایش داده شوند.
SELECT NationalIDNumber,
LoginID,
JobTitle
قسمت بعدی که عبارت From می باشد :
این عبارت مشخص می کند اطلاعات باید از چه منبعی فرخوانی شود. در اینجا ما اطلاعات را از یک جدول موجود در دیتابیس فراخوانی کردیم.
عبارت HumanResources.Employee شامل دو قسمت می باشد. قسمت اول نام Schema هست و قسمت دوم نام حقیقی جدول. هر Schema شامل یک سری اشیا می باشد. در درس های آینده نحوه تعریف Schema و کاربرد آنرا توضیح خواهیم داد.
اگر بخواهیم لیست تمامی ستونهای موجود را از یک منبع داده که در جلوی عبارت From آمده است ، فراخوانی کنیم باید از دستور زیر استفاده کنیم :
SELECT *
FROM HumanResources.Employee
نکته بسیار مهم : تا زمانی که نیاز به همه فیلدها نداریم بهتر است از * استفاده نکرده و نام فیلدها را به صورت جداگانه بیاوریم.چون هرچه تعداد فیلدها بیشتر باشد حجم اطلاعات جابجا شده بیشتر خواهد بود که این میتواند به خصوص روی نرم افزارهای تحت شبکه تاثیر به سزایی روی سرعت نرم افزار داشته باشد.همچنین اگر بعد از مدتی یک فیلد به جدول مورد نظر اضافه کنیم به طور ناخودآگاه این فیلد در برنامه ما ظاهر خواهد شد.چون ممکن است این فیلد یک فیلد محاسباتی باشد و نیازی به نمایش آن به کاربر نباشد.
محدود کردن نتیجه یک Query بواسطه عبارت Where.
در یک دستور Select عبارت Where برای محدود کردن نتیجه مورد نظر به کار می رود. شکل ساده دستوری عبارت where در زیر آمده است .
توجه : وقتی عبارتی داخل [] قرار میگیرد منظور این است که وجود آن در دستور اصلی به صورت دلخواه می باشد و نبود آن در اجرای دستور مشکلی ایجاد نخواهد کرد.
SELECT select_list
FROM table_list
[WHERE search_conditions]
Search_conditions ها گزارهایی هستند که مقادیر عبارات را با TRUE , FALSE,UNKHOWN ارزیابی می کنند.
UNKHOWN زمانی ظاهر می شود که در Search_Condition ها به مقدار NULL دسترسی پیدا کنیم.
یک مقدار NULL نه صفر هست نه خالی فقط و فقط شناخته نشده است.همچنین دو مقدار NULL باهم برابر نیستند و نمیتوان بدون یک نتیجه UNKHOWN آنها را باهم مقایسه کرد.
تصمیم دارم در دروس پیوسته از مباحث ابتدایی تا پیشرفته مباحث TSQL یا برنامه نویسی در SQL Server رو آموزش بدم.
دوستانی هم که سوال داشتند در قسمت نظرخواهی بپرسند پاسخ میدم.