آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

آموزش مباحث مربوط به واکشی داده ها

دوره های مرتبط با این مقاله

مباحث مربوط به واکشی داده ها: عبارت ها و استخراج داده ها

اسم ستون هایی که در دستور SELECT برای واکشی مقادیر ستون ها ذکر می کنید، پس از اجرای کوئری به صورت سرستون یا header نمایش داده می شوند.
در صورت تمایل می توانید بجای اسم ستون هر اسم دیگری را به عنوان سرستون انتخاب کرده و نمایش دهید.
برای این منظور، داخل Query Designer، اسم دلخواه را در زیر هدر Alias ستون مربوطه مانند تصویر زیر وارد نمایید.


آموزش SQL

اگر دستورات SELECT خود را داخل Query Editor یا در خط فرمان (PowerShell) می نویسید، در سمت راست اسم ستون، به اندازه ی یک خط فاصله فضای خالی بگذارید. سپس یک حرف یا کلمه را به عنوان نام مستعار (alias) ستون در آن جایگاه وارد نمایید.


مثال:
USE VideoCollection1;
GO
SELECT Title Name, Director Master, Rating Exclusion FROM Videos;
GO

در صورتی که alias مورد انتخاب از چندین کلمه تشکیل شده و می خواهید کلمات از هم جدا باشند، بایستی آن ها را داخل دابل کوتیشن قرار دهید.


مثال:
USE VideoCollection1;
GO
SELECT Title Name,
       Director Master,
       Rating Exclusion,
       YearReleased "Copyright Year" FROM Videos;
GO

این نتیجه را برمی گرداند:
آموزش SQL

می توان نام مستعار را بین [ باز و ] بسته قرار داد. نام مستعار یک کلمه باشد یا چند کلمه، می توان آن را داخل دابل کوتیشن یا [] قرار داد.
بجای خط فاصله بین اسم ستون و نام مستعار آن، می توان واژه ی کلیدی AS را بکار برد.


مثال:
SELECT FirstName,
       LastName,
       HomePhone AS PhoneNumber,
       ParentsNames AS NamesOfParents
FROM   Registration.Students;
GO

به خاطر داشته باشید که اگر نام مستعار از چند کلمه تشکیل شده باشد و کلمات از یکدیگر جدا باشند، بایستی آن را یا داخل دابل کوتیشن و یا [] محصور کرد.


مثال:
    
SELECT FirstName [First Name],
       LastName  "Last Name",
       HomePhone AS [Phone Number],
       ParentsNames AS "Names of Parents"
FROM   Registration.Students;
GO

خروجی:
آموزش SQL

می توانید همراه با ستون اسم جدول نیز را ذکر کنید


مثال:
SELECT Students.FirstName AS [First Name],
       Students.LastName "Last Name",
       Students.HomePhone AS [Phone Number],
       Students.ParentsNames [Names of Parents]
FROM   Registration.Students;
GO

می توان یک schema نیز تعریف کرد و نام آن را پیش از اسم جدول و ستون بکار برد، بدین صورت:


SELECT Registration.Students.FirstName AS [First Name],
       Registration.Students.LastName AS [Last Name],
       Registration.Students.HomePhone AS [Phone Number],
       Registration.Students.ParentsNames AS [Names of Parents]
FROM   Registration.Students;
GO

قبلا با نحوه ی ایجاد یک نام مستعار برای جدول آشنا شدیم. در صورت کار با Query Designer، پس از وارد کردن یک اسم به عنوان نام مستعار جدول در پنجره ی Properties، نام مستعار بلافاصله در دو کادر SQL و Criteria نوشته می شود. می توانید اسم مستعار را به صورت کدنویسی در دستور SQL ایجاد کنید. برای این منظور پیش از اسم ستون، نام مستعار و سپس عملگر نقطه را بکار ببرید.


مثال:
SELECT std.FirstName AS [First Name],
       std.LastName AS [Last Name],
       std.HomePhone AS [Phone Number],
       std.ParentsNames AS [Names of Parents]
FROM   Registration.Students std;
GO

می توانید برای ستون های خود نیز نام مستعار ایجاد نمایید.


مثال:
SELECT [Little Angels].FirstName [First Name],
       "Little Angels".LastName AS [Last Name],
       Gender, 
       [Little Angels].EmailAddress "Email Address",
       ParentsNames [Parents Names],
       [Little Angels].HomePhone AS "Home Phone"
FROM   Registration.Students [Little Angels];
GO

در این درس و دروس بعدی، پایگاه داده ای به نام FunDS را بکار خواهیم برد. واژه ی Fun همان معنای حقیقی خود را دارد و DS مخفف department store (فروشگاه بزرگ) می باشد.
FunDS یک شرکت موهوم است که لباس و لوازم جانبی در نزدیکی یک مرکز خرید می فروشد. می خواهیم یک اپلیکیشن برای شرکت نام برده بنویسیم. کار خود را با ایجاد یک بانک اطلاعاتی که فهرستی از اقلام به فروش رفته در فروشگاه را نمایش می دهد ایجاد کنیم. ابتدا یک لیست ساده از آیتم های فروشگاه تهیه می کنیم که در مدیریت و تحلیل فهرست موجودی و اقلام به فروش رفته به ما کمک می کند.
هر آیتمی که در فروشگاه به فروش می رسد دارای:
1. یک شماره ی منحصر بفرد برای آیتم مورد نظر (ItemNumber): مدیریت یا کارمندی که اطلاعات را وارد می کند آن شماره را تعیین می کند. این شماره بین 100000 و 999999 می باشد.
2. یک اسم: این اسم شرح مختصری از آیتم می باشد. بعده ها یک رده و زیر رده نیز ایجاد می کنیم اما از آنجایی که هنوز در مرحله ی طراحی به سر می بریم، به یک لیست ساده اکتفا می کنیم. به همین خاطر برخی از آیتم ها شامل یک کلمه می شوند که رده ی آن ها را تعریف می کند. مثال: dress، skirts، watch، pants، shoes و wallet.
3. تولید کننده (manufacturer): شرکت تولید کننده ی آیتمی است که در فروشگاه عرضه و به فروش می رسد.
4. اندازه ی آیتم: بیشتر اقلام توسط اندازه شان قابل شناسایی می باشند. اندازه بسته به نوع آیتم متفاوت خواهد بود. آیتم هایی هم وجود دارند که اندازه ندارند.
5. قیمت واحد (unit price): مبلغی است که خریدار بابت آیتم پرداخت می کند.
6. درصد تخفیف (discount rate): این عدد مشخص می کند آیا آیتمی تخفیف دارد یا خیر و اینکه چند درصد تخفیف می خورد.


آموزش کاربردی: استفاده از اسم مستعار

  1. فایل Department Store 1 را باز کنید. داخل فایل کلیک نموده و بعد با استفاده از Ctrl + A و Ctrl + C همه محتویات را انتخاب و سپس کپی کنید.
  2. محیط Microsoft SQL Server را راه اندازی کرده و دکمه ی Connect را برای اتصال به سرویس دهنده، کلیک کنید.
  3. در پنجره ی Object Explorer، بر روی اسم سرور راست کلیک کرده و گزینه ی New Query را انتخاب نمایید.
  4. Ctrl + V را زده تا کد مورد نظر در اینجا جایگذاری گردد.
  5. برای ایجاد پایگاه داده، داخل پنجره ی میانی راست کلیک کرده و گزینه ی Execute را کلیک کنید.
  6. در Object Explorer، بر روی Databases راست کلیک کرده و گزینه ی Refresh را انتخاب کنید.
  7. گره Databases، سپس DepartmentStore1 و در پایان Tablesرا باز نمایید.
  8. بر روی Inventory.StoreItems راست کلیک کرده و گزینه ی Edit Top 200 Rows را انتخاب نمایید.
  9. در منوی اصلی: Query Designer -> Pane -> Diagram.
  10. در منوی اصلی: Query Designer -> Pane -> Criteria.
  11. در منوی اصلی: Query Designer -> Pane -> SQL.
  12. در پنجره ی Diagram، تیک تمامی چک باکس ها را بردارید.
  13. در پنجره ی SQL، دستور delete TOP (200) را تایپ کنید.
  14. در پنجره ی Criteria، بر روی چک باکس موجود در زیر سرستون Column کلیک کنید. ممکن است یک کادر پیام خطا دریافت کنید:
آموزش SQL

پیام خطا را خوانده و دکمه ی OK را کلیک کنید، سپس آیکون هشتک لیست کشویی را باز کرده و آیتم ItemNumber را انتخاب کنید:


آموزش SQL
  1. با کلید Tab به ستون کناری رفته و Item # را به عنوان نام مستعار در زیر سرستون Alias تایپ کنید.
  2. در پنجره ی Diagram، بر روی چک باکس ItemName کلیک کنید.
  3. در پنجره ی Criteria، بر روی فیلد Alias مربوط به ItemName کلیک نمایید.
  4. حال Name/Description را در فیلد Alias وارد نمایید.
  5. در پنجره ی Diagram، بر روی چک باکس ItemSize و Alias کلیک نموده و آن ها را تیک دار نمایید.
  6. در پنجره ی Criteria، بر روی فیلد Alias کلیک نموده و سپس واژه ی Size را به عنوان نام مستعار درج نمایید.
  7. در پنجره ی Diagram، بر روی چک باکس ستون UnitPrice کلیک نموده و آن را انتخاب کنید.
  8. در پنجره ی Criteria، بر روی فیلد Alias کلیک کرده و واژه ی Unit Price را به عنوان نام مستعار فیلد UnitPrice وارد نمایید.
آموزش SQL

23. داخل پنجره راست کلیک نموده و گزینه ی Execute SQL را انتخاب نمایید.


آموزش SQL

ستون های ترکیبی و محاسباتی

در مبحث مربوط به توابع مبتنی بر رشته (توابعی که عملیاتی را بر روی رشته انجام می دهند)، درباره ی نحوه ی پیوند (concat) رشته ها مطالبی را آموختیم. گفتنی است این عملیات را می توان داخل دستور SELECT نیز انجام داد، بدین معنی که می توان مقادیر ستون های مجزا را با یکدیگر ترکیب نموده و رشته یا مقداری ایجاد کرد که در اصل یک عبارت است. بر این اساس، این اختیار را داریم که فیلد first name را با فیلد last name ترکیب کرده و در خروجی مقداری تولید کرد که یک عبارت متشکل ار مقدار فیلد دو ستون می باشد. برای مثال، می توان مقدار فیلد Date را گرفته، عددی به آن اضافه نمود و تاریخ روز دیگری را محاسبه کرد. همچنین می توان مقدار دو یا چند فیلد را گرفته محاسبه ای بر روی آن انجام داد و مقدار آن را در یک ستون محاسباتی نمایش داد. برای نمونه، مقدار ستون employees weekly hours (ساعات کاری در هفته کارکن) را با مقدار ستون hourly salary (حقوق ساعتی کارکن) ضرب کرده و مقدار حقوق در هفته ی کارکن را محاسبه کرد و در ستون محاسباتی weekly salary نمایش داد.
پرکاربرد ترین عملگر مورد استفاده در ایجاد ستون های محاسباتی، عملگر + می باشد. با استفاده از عملگر مزبور دو یا چند رشته را با یکدیگر ترکیب کرده و رشته ی جدیدی به عنوان خروجی تحویل می گیریم.


مثال:
SELECT FirstName + N' ' + LastName
FROM   Registration.Students;
GO

نتیجه ی زیر را برمی گرداند:


آموزش SQL

این عملیات را بر روی مقادیر عددی نیز می توان اجرا کرد. تمامی دیگر عملگرهای محاسباتی را نیز می توان برای تولید مقدار ترکیبی و ستون محاسباتی مورد استفاده قرار داد. به عنوان مثال، می توان ساعات کاری هفتگی را با دستمزد ساعتی ضرب کرده و دستمزد هفتگی را محاسبه کرد. برای بدست آوردن این مقدار می توان دستور زیر را مورد استفاده قرار داد:


SELECT WeeklyHours * HourlySalary
FROM Payroll

می توان با استفاده از امکان alias، اسم دلخواه را برای ستون محاسباتی (expression) انتخاب کرد. برای این منظور، در سمت راست عبارت ترکیبی، کلیدواژه ی AS و به دنبال آن اسم مورد نظر را وارد نمایید. همان طور که قبلا گفته شد، در صورتی که نام مستعار متشکل است از چند واژه، بایستی آن را یا داخل تک کوتشن و یا [] قرار داد. نمونه:


SELECT FirstName + N' ' + LastName AS N'Full Name',
       EmergencyName  + N' ' + EmrgPhone AS [Emergency Contact]
FROM   Registration.Students;
GO

خروجی:
آموزش SQL

به همین نحو، می توانید عبارتی بسیار طولانی و پیچیده تر که حاوی کلیدواژه های SQL، اسم ستون های جدول و کلمات متعدد معمولی می باشد، ایجاد کرد.


مثال:
SELECT 	PropertyType + N' in ' + City + N', ' + State + N', in ' + Condition + 
       	N' condition. Equipped with ' + CAST(Bedrooms AS nvarchar(20)) + 
       	N' bedrooms, ' + CAST(Bathrooms AS nvarchar(20)) + 
       	N' bathrooms. Built in ' + CAST(YearBuilt AS nvarchar(20)) + 
  	N' and selling for ' + CAST(MarketValue AS nvarchar(20))
	AS [Property Description]
FROM    Listing.Properties

این کد نتیجه ی زیر را برمی گرداند:
آموزش SQL
یاداور می شویم که برای پیوند زدن دو یا چند رشته می توان تابع CONCAT() را صدا زد.

آموزش کاربردی: استفاده از عبارات ترکیبی در واکشی اطلاعات

  1. در پنجره ی Diagram، بر روی چک باکس مربوط به ستون DiscountRate کلیک نمایید.
  2. در کادر Criteria، بر روی اولین فیلد موجود در زیر سرستون Column کلیک نمایید. عبارت UnitPrice * DiscountRate / 100 را تایپ نموده و کلید Tab را بزنید تا به ستون کناری هدایت شوید.
  3. در فیلد alias مربوطه، عبارت Discount Amount را به عنوان نام مستعار وارد نمایید.
  4. حال بر روی کادر مربوط به ستون * DiscountRate UnitPrice کلیک نموده و عبارت UnitPrice - (UnitPrice * DiscountRate / 100) را داخل آن درج کنید.
  5. در فیلد Alias مربوطه، After Discount را تایپ کنید.
  6. برای اجرا، در منوی اصلی: Query Designer -> Execute SQL.
آموزش SQL
  1. با کلیک بر روی جدول SQLQuery1.sql آن را انتخاب کنید.
  2. با زدن کلیدهای Ctrl + A تمامی دستورات را انتخاب کرده و دستور زیر را جایگزین آن کنید (این کد مربوط به پایگاه داده ی LambdaSquare1 می باشد که در درس قبلی ایجاد کردیم):
USE LambdaSquare1;
GO
SELECT aparts.UnitNumber,
       aparts.Bedrooms, 
       aparts.Bathrooms,
       aparts.Price, 
       aparts.Deposit,
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts;
GO

9. برای اجرای کد بالا، کلید F5 را بزنید.
آموزش SQL

عملگر تخصیص (Assignment)

هنگامی که با استفاده از عملگرهای محاسباتی یک عبارت ساده می نویسید، ستون ترکیبی یا محاسباتی که در نتیجه ی عبارت تولید می شود، در ابتدای امر بی نام خواهد بود. Transact-SQL به شما این امکان را می دهد که اسمی جایگزین برای ستون مورد نظر یا عبارت ترکیبی، در حین انتخاب و واکشی اطلاعات انتخاب نمایید. برای این منظور، می توانید یک اسم تعریف کرده و آن را به واسطه ی عملگر تخصیص " = " به اسم اولیه و اصلی ستون مورد نظر اختصاص دهید (در عمل این اسم را جاگزین اسم اصلی ستون قرار دهید).
برای تغییر اسم ستون در زمان گزینش اطلاعات برای واکشی، در سمت راست دستور SELECT، اسم دلخواه را تایپ کرده و بلافاصله پس از آن عملگر تخصیص و در نهایت اسم اولیه ی ستون مورد نظر را درج نمایید. چنانچه اسم جایگزین تنها از یک کلمه تشکیل شده، در آن صورت کافی است با قرار دادن اسم دلخواه در سمت چپ عملگر تخصیص، آن را به اسم اصلی ستون تخصیص دهید (جایگزین آن نمایید). نمونه:


SELECT EmergencyName = EmergencyName
FROM   Registration.Students;
GO

در صورتی که اسم انتخابی شما بیش از یک کلمه می باشد، لازم است آن را بین تک کوتیشن، دابل کوتیشن یا [] محصور نمایید.


مثال:
SELECT CONCAT(LastName, N', ', FirstName) AS [Full Name],
       [Emergency Name] = EmergencyName,
       'Emergency Phone' = EmergencyPhone
FROM   Registration.Students;
GO

نتیجه ی زیر را برمی گرداند:
آموزش SQL

استفاده ی کاربردی از عملگر تخصیص

1. دستور را به صورت زیر ویرایش نمایید:


USE LambdaSquare1;
GO
SELECT "Unit #" = aparts.UnitNumber,
       Beds = aparts.Bedrooms, 
       Baths = aparts.Bathrooms,
       [Monthly Rent] = aparts.Price, 
       'Primary Deposit' = aparts.Deposit,
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts;
GO

2. کلید F5 را زده تا کد اجرا شود:


آموزش SQL

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

گاهی تعداد سطرهای موجود در پایگاه داده بسیار زیاد می باشد. در چنین شرایطی می توان تعداد محدودی را به دلخواه انتخاب کرده و استخراج نمود.
برای این منظور، در پنجره ی Query Designer، می توان حداکثر تعداد رکوردهایی که می خواهید استخراج و به نمایش گذاشته شود را مشخص نمایید. ابتدا به پنجره ی Properties مراجعه نموده، گره Top Specification را باز نمایید. خواهید دید که مقدار آن بر روی YES تنظیم شده است (زمانی که بر روی جدولی راست کلیک کرده و گزینه ی Edit Top 200 Rows را انتخاب می کنید، YES مقدار پیش فرض خواهد بود). اکنون فیلد Expression را انتخاب کرده و عدد مد نظرتان را (به عنوان حداکثر تعداد رکوردهای انتخابی) وارد نمایید:


آموزش SQL

دستور را اجرا نمایید. حال اگر می خواهید این کار را با کدنویسی انجام دهید، کافی است پس از عملگر SELECT، کلیدواژه ی TOP و بلافاصله پس از آن عدد صحیح را درج نمایید.


مثال:
USE Exercise;
GO
SELECT TOP 5 * FROM Employees;
GO

می توان این عدد را داخل پرانتز هم قرار داد


مثال:
USE Exercise;
GO
SELECT TOP(5) * FROM Employees;
GO

این دستور به مفسر SQL دستور می دهد، 5 رکورد اول را از جدول Employees انتخاب کرده و برگرداند.


آموزش کاربردی: انتخاب چند رکورد اول

1. در کادر SQL، در سمت راست عبارت کلیدی SELECT، یک خط فاصله انداخته و بلافاصله پس از آن عبارت TOP(12) را درج نمایید.
2. داخل پنجره راست کلیک کرده و گزینه ی Execute SQL را انتخاب نمایید.


آموزش SQL

واکشی سطرها بر حسب در صد

بجای دادن عدد صحیح به تابع TOP()، می توان تعداد سطرها را بر حسب درصد به عنوان پارامتر ورودی به تابع ذکر شده ارسال کرد.
برای مشخص کردن درصد رکوردهایی که بایستی از جدول استخراج شود، پیش از هر چیز به پنجره ی Properties مراجعه کرده و پس از تنظیم فیلد Top Specification به YES، مقدار دلخواه را داخل فیلد Expression مشخص نمایید. مقدار ورودی باید بین 1.00 و 100.00 باشد (توجه داشته باشید که این بازه خود 100.00 را نیز شامل می شود). پس از وارد کردن مقدار، فیلد Percent را بر روی YES تنظیم کنید


مثال:
آموزش SQL

فرمول تعریف واکشی رکوردهای مورد نظر بر حسب درصد در زیر ذکر شده است:


SELECT TOP Value PERCENT WhatColumns FROM WhatObject

پس از کلیدواژه ی TOP، یک عدد تایپ کنید، سپس برای اینکه به مفسر بفهمانیم چند درصد از رکوردها را باید واکشی کند، واژه ی کلیدی PERCENT را بکار می بریم. نمونه:


SELECT TOP 25 PERCENT FirstName, LastName, HourlySalary
FROM Employees;
GO

می توان value را داخل پرانتز به تابع TOP پاس داد:


SELECT TOP (25) PERCENT FirstName, LastName, HourlySalary
FROM Employees;
GO

این دستور یک چهارم کل رکوردها (25%) جدول را برمی گرداند. برای مثال، اگر جدول دارای 12 رکورد می باشد، مفسر سه سطر از کل رکوردهای جدول را برمی گرداند. در حقیقت، موتور DB تعداد رکوردها را به مقدار مشخص شده داخل پرانتز تقسیم می کند، سپس آن مقدار را به نزدیکترین عدد صحیح تبدیل نموده و در نهایت تعداد رکوردها را برحسب درصد برمی گرداند. فرض کنید جدول 11 رکورد دارد و شما درخواست بازیابی 25% آن ها را می کنید، در آن صورت مفسر این محاسبات را انجام می دهد: 11 / (100/25) = 11 / 4 = 2.75. بزرگترین عدد صحیح نزدیک به این مقدار، 3 می باشد. بر این اساس، موتور DB 3 رکورد از کل رکوردهای جدول را برمی گرداند.


زمانی که مقدار یک فیلد شرایطی را برآورده می کند: دستور شرطی WHEN.. CASE

می توانید با استفاده از دستور WHEN واکشی اطلاعات را اصلاح کنید (بر اساس شرایط خاصی ویرایش نمایید و نتایجی که برمی گردانید را مطابق نیاز تنظیم کنید). جدول Persons را درنظر بگیرید:


USE Exercise;
GO
CREATE TABLE Persons(FirstName nvarchar(20), LastName nvarchar(20), GenderID int);
GO
INSERT INTO Persons VALUES(N'Gertrude', N'Monay', 2),
			  (N'Horace', N'Taylor', 1),
			  (N'Marc', N'Knights', 2),
			  (N'Tiernan', N'Michael', 3),
			  (N'Paul', N'Yamo', 1),
			  (N'Mahty', N'Shaoul', 3),
			  (N'Hélène', N'Mukoko', 2);
GO

فرض بگیرید می خواهید ستون GenderID را گزینش و واکشی کنید:


آموزش SQL

در صورتی که یک ستون دارای مقادیری می باشد که برای کاربر قابل فهم نیست (شناسایی آن سخت است)، می توان از دستور شرطی CASE استفاده کرده و نتایج را به گونه ای قابل فهم و مطوب تر نمایش داد.


مثال:
SELECT FirstName, LastName, Gender =
    CASE GenderID
	WHEN 1 THEN N'Male'
	WHEN 2 THEN N'Female'
	ELSE N'Unknown'
    END
FROM Persons

در خلاصه می توان گفت که برای مقادیر مشخص همچون جنسیت (GenderID در مثال فوق)، ایام هفته ، ماه های سال می توان به جای متوسل شدن به دستور Left Join، از دستور شرطی Then ..When .. Case بهره گرفت.


آموزش SQL
  • 2202
  •    1332
  • تاریخ ارسال :   1394/11/25

دانلود PDF دانشجویان گرامی اگر این مطلب برای شما مفید بود لطفا ما را در GooglePlus محبوب کنید
رمز عبور: tahlildadeh.com یا www.tahlildadeh.com
ارسال دیدگاه نظرات کاربران
شماره موبایل دیدگاه
عنوان پست الکترونیک

ارسال

آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

تمامی حقوق این سایت متعلق به آموزشگاه تحلیل داده می باشد .