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

مباحثی درباره ی فیلتر و گزینش داده ها در Sql

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

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

در دستورات SELECT ای که تاکنون نوشته یا با آن برخورد داشتیم، صرفا ستون هایی را که می خواستیم برای نمایش واکشی کنیم را گزینش می کردیم. در زمان نگارش و ایجاد چنین دستوری، می توان شرطی را به یک ستون اعمال کرد، بدون اینکه ستون یاد شده را در نتیجه ذکر کنیم.
به عنوان مثال، Query را در نظر بگیرید که برای نمایش لیستی از دانش آموزان دختر بکار می رود. از آنجایی که می دانیم نتیجه ی کوئری فقط دخترها را لیست می کند، لازم نیست ستون Gender را در دستور ذکر کنیم. بنابراین در این نمونه ی خاص، می توان ستون مورد نظر را در نتیجه مخفی کرد.
برای مخفی کردن ستون در یک کوئری به صورت ویژوال، داخل پنجره ی Criteria، عبارت شرطی مورد نظر را در کادر Filter مربوط به آن فیلد تنظیم نمایید. سپس بر روی چک باکس Output کلیک نموده و تیک آن را حذف نمایید. برای مخفی نمودن یک فیلد با استفاده از دستورات SQL (به صورت کدنویسی)، کافی است ستون مد نظر را از دستور SELECT حذف کنید اما آن را در عبارت شرطی WHERE لحاظ نمایید.


مثال:
SELECT DateOfBirth, LastName,
       FirstName, State, ParentsNames
FROM   Registration.Students
WHERE Gender = N'Female';
GO

نتیجه:
آموزش SQL

همان طور که مشاهده می کنید، در دستور SELECT خبری از ذکر ستون Gender نیست و کوئری حاصل ستون Gender را نمایش نمی دهد.


آموزش کاربردی

1. محیط SQL Server را راه اندازی نموده و دکمه ی Connect را در پنجره ی محاوره ای اتصال به سرور کلیک نمایید.
2. در پنجره ی Object Explorer، گره Databases را باز نمایید.
3. اگر به خاطر داشته باشید، در درس های قبلی یک پایگاه داده به نام Department Store1 ایجاد کردیم. به Object Explorer مراجعه کرده و گره DepartmentStore1 را باز نمایید.
4. بر رویDepartmentStore1 راست کلیک کرده و گزینه ی Edit Top 200 Rows را انتخاب نمایید.
5. بر روی DepartmentStore1 راست کلیک کرده و گزینه ی New Query انتخاب نمایید.
6. اکنون گره Tables را باز نمایید.
7. بر روی جدول Inventory.StoreItems راست کرده و گزینه ی Edit Top 200 Rows را انتخاب کنید.
8. در نوار ابزار Query Designer، بر روی دکمه های آموزش SQL، آموزش SQLو آموزش SQLبه ترتیب کلیک نمایید.
9. در کادر SQL، دستور موجود را به صورت زیر ویرایش نمایید:


SELECT ItemNumber AS [Item #],
       Manufacturer,
       Category,
       SubCategory AS [Sub-Category], 
       ItemName AS [Name/Description], 
       UnitPrice AS [Unit Price]
FROM   Inventory.StoreItems

10. در منوی اصلی، بر روی ورودی Query Designer کلیک نموده و گزینه ی Execute SQL را انتخاب نمایید.
11. برای مشاهده ی آیتم های تولید شده توسط Kenneth Cole، در کادر Criteria، بر روی کادر Filter مربوط به فیلد Manufacturer کلیک کرده و عبارت Kenneth Cole را تایپ نمایید.
12. حال برای مشاهده ی نتیجه، در نوار ابزار Query Designer، بر روی دکمه ی آموزش SQLExecute SQL کلیک نمایید.
13. جهت پنهان کردن ستون، در پنجره ی Criteria، بر روی چک باکس Output مربوط به فیلد Manufacturer کلیک نمایید.


آموزش SQL

14. برای مشاهده ی خروجی، در نوار ابزار Query Designer، بر روی دکمه ی آموزش SQLکلیک نمایید.


آموزش SQL

کپی و جایگذاری رکوردهای انتخاب شده از یک جدول داخل جدول دیگر (Select Into)

جدول زیر را درنظر بگیرید:
CREATE DATABASE Corporation;
GO
USE Corporation;
GO
CREATE TABLE Employees
(
	EmployeeNumber int unique not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Department nvarchar(50) null,
	EmploymentStatus nvarchar(30),
	HourlySalary money
);
GO
INSERT INTO Employees
VALUES(84628, N'Anthony', N'Holms', N'Corporate', N'Full Time', 24.72),
      (40574, N'William', N'Wilson', N'Information Technology', N'Full Time', 21.18),
      (27462, N'Peter', N'Swanson', N'Corporate', N'Full Time', 22.84),
      (52835, N'Edward', N'Johansen', N'Information Technology',N'Consultant',15.50),
      (93075, N'Sharon', N'Edson', N'Accounting', N'Full Time', 28.74),
      (82718, N'Yanuh', N'Hadhah', N'Corporate', N'Full Time', 14.86),
      (29174, N'Hamin', N'Souleyman', N'Public Relations', N'Consultant', 18.76);
GO

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


    SELECT Columns INTO NewTableName FROM ExistingTable [WHERE Condition]

به منظور استفاده از تمامی ستون ها و رکوردهای یک جدول (گزینش کلیه ی سطرها)، دستور را با واژه ی کلیدی SELECT آغاز می کنیم، سپس عملگر * و به دنبال آن کلمه ی رزرو شده ی INTO، اسم جدول جدید، کلیدواژه ی FROM و در پایان اسم جدول اصلی (دربردارنده ی تمام سطرها) را ذکر می کنیم.


مثال:
USE Corporation;
GO
SELECT * INTO CompanyRecipients FROM Employees;
GO

بجای انتخاب تمامی ستون ها، می توانید فقط ستون های دلخواه را پس از کلمه ی کلیدی SELECT ذکر نموده و گزینش کنید.


مثال:
USE Corporation;
GO
SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus
INTO Salaried FROM Employees;
GO

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


مثال:
USE Corporation;
GO
SELECT *
INTO FullTimeEmployees
FROM Employees
WHERE EmploymentStatus = N'Full Time';
GO

فیلتر بر اساس الگوی شرطی (کلیدواژه ی LIKE)

همه یا بیشتر معیارهایی که پس از کلمه ی رزرو شده ی WHERE در دستور SELECT مشخص می کنیم، می بایست با مورد جستجو کاملا منطبق باشد. گاهی، ممکن است مقدار مورد جستجو رکورد را به طور دقیق به خاطر نداشته باشیم. در این صورت، برای جستجو و واکشی مقدار دلخواه بایستی نوعی جستجوی تقریبی (بر اساس یک الگوی شرطی) پیاده سازی کرد. عملگر LIKE دقیقا برای همین منظور طراحی شده است. عبارت LIKE عملا برای مشخص کردن يک فرمت خاص برای جستجو و فیلتر مورد استفاده قرار می گیرد.
اگر دستور را به صورت ویژوال نگارش می کنید، در پنجره ی Criteria، داخل کادر Filter مربوط به ستون مورد نظر (ستونی که فیلتر و شرط بر روی آن اعمال می شود)، کلیک نموده و عبارت LIKE را وارد آن نمایید.
در زبان T-SQL، عملگر LIKE به صورت زیر بکار می رود:


Expression LIKE pattern

آنچه در جایگاه Expression (در فرمول نام برده) قرار می گیرد، برای جستجو و واکشی مقدار مورد نظر ارزیابی می شود. به طور معمول در این جایگاه اسم یک ستون ذکر می گردد. لازم است این عبارت صریح، مجاز و کاملا معتبر باشد. Pattern نیز آن مقداری است که بایستی در ستون جستجو و یافت شود.
اغلب عبارت شرطی LIKE همراه با دستور WHERE بکار می رود (اول WHERE و به دنبال آن اسم ستون، کلمه ی LIKE و در نهایت عبارت جستجو ذکر می شود). LIKE برای ستون های رشته ای بکار می رود. بنابراین عملگر LIKE برای اجرای نوعی مقایسه جهت کشف تطبیق مقدار یک فیلد به طور دقیق یا تقریبی با الگوی خاص، بکار می رود.
در نتیجه مقدار بولی True یا False برگردانده می شود. پس از آن می توانید نتیجه ی بازگشتی را مطابق نیاز خود بکار ببرید.


تطبیق دقیق

بیشترین مقایسه ای که بر روی فیلد رشته ای انجام می گیرد، برای کشف برابری یا عدم برابری آن با رشته ی دیگر است (آیا دو رشته یکسان هستند یا خیر). می دانیم که این عملیات با استفاده از عملگر = امکان پذیر می باشد


مثال:
SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  State = N'VA';
GO

عملگر LIKE را می توان دقیقا برای همین منظور بکار برد. فرمول:


WHERE ColumnName LIKE Value

در این نمونه، اسم ستون در جایگاه Expression در فرمول قبلی قرار می گیرد و رشته ی موجود یا ذخیره شده در آن فیلد در جایگاه pattern درج می گردد (و عبارت جستجو می باشد).
این کد و نیز کد قبلی هر دو نتیجه ی یکسان را تولید می کنند:


آموزش SQL

برای افزایش قابلیت خوانایی عملیات (عبارت LIKE)، می توانید آن را داخل پرانتز محصور نمایید. کد بالا را می توان به صورت زیر نوشت:


SELECT FirstName, LastName,
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE (State LIKE N'VA');
GO

آموزش کاربردی

1. در پنجره ی Criteria، بر روی چک باکس Output مربوط به ستون Manufacturer کلیک نموده و آن را تیک دار کنید.
2. حال بر روی کادر Filter مربوط به آن ستون کلیک کرده و عبارت LIKE Tommy Hilfiger را وارد نمایید.


آموزش SQL

3. برای مشاهده ی نتیجه، در منوی اصلی بر روی ورودی Query Designer و سپس گزینه ی Execute SQL کلیک کنید.


آموزش SQL

نقیض الگو (نقیض کردن عملیات LIKE)

همان طور که عملیات بولی را می توان نقیض کرد، به همین نحو هم می توان عمل LIKE را با استفاده از عملگر NOT نقیض نمود. برای این منظور، کافی است عملگر یاد شده را پیش از Expression (اسم ستون، LIKE، الگوی شرطی یا همان pattern) درج نمایید.


مثال:
SELECT FirstName, LastName,
       Gender, State, ParentsNames
FROM   Registration.Students
WHERE NOT State LIKE N'VA';
GO

توصیه می شود عبارت پس از عملگر NOT را داخل پرانتز قرار دهید.


مثال:
SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE NOT (State LIKE N'VA');
GO

دستور بالا به مفسر اعلان می کند به غیر از رشته ی 'VA' تمامی مقادیر را از ستون State برگرداند.
می توان عملگر NOT را درست قبل از کلیدواژه ی LIKE بکار برد.


مثال:
آموزش SQL
نتیجه ی یکسان با نتیجه ی فرمول قبلی ارائه می کند:
آموزش SQL

می توان یک نقیض را با استفاده از یک عملگر NOT دیگر، نقیض کرد.


مثال:
SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT State NOT LIKE N'VA';
GO

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


SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT (State NOT LIKE N'VA');
GO

می توان یک نقیض را با استفاده از یک عملگر NOT دیگر، نقیض کرد. با این کار نقیضی که اعمال کرده بودیم را خنثی می کنیم، گویی اصلا عمل LIKE را از اول نقیض نکرده بودیم.


آموزش کاربردی

1. در پنجره ی SQL، عملگر NOT را به قبل از واژه ی کلیدی WHERE اضافه کنید.
2. برای اجرای دستور و مشاهده ی نتیجه، در منوی اصلی بر روی ورودی Query Designer کلیک نموده، سپس گزینه ی Execute SQL را انتخاب نمایید.
3. در کادر Criteria، ستون Manufacturer را انتخاب کرده و عبارت LIKE Ralph Lauren را داخل فیلد Filter آن درج نمایید.
4. برای اجرای کد جاری و مشاهده ی خروجی، در نوار ابزار Query Designer بر روی دکمه ی آموزش SQLکلیک نمایید.
5. در کادر Criteria، ستون Manufacturer را انتخاب کرده و سپس شرط تنظیم شده در فیلد Filter آن را حذف نمایید.


تطبیق با هر کاراکتری (علامت %)

عملگر LIKE در عبارت WHERE برای جستجو و یافتن یک الگوی خاص در یک ستون بکار می رود. در واقع هدف از بکار بردن LIKE فیلتر کردن جستجو بر اساس الگوی شرطی است که پس از آن تعریف می شود. این جستجو به صورت تقریبی انجام می گیرد، برای اینکه ممکن است مقدار دقیق آن فیلد را نداشته باشیم. به عنوان مثال تنها دو کاراکتر مقداری را داریم. حال دو کاراکتر را در الگو بکار برده و بجای کاراکتری که از آن مطمئن نیستیم علامت % را بکار می بریم.
علامت % جایگزینی برای یک یا چند کاراکتر است که در الگوی شرطی یا عبارت جستجو استفاده می شود و این کاراکتر می تواند هر چیزی باشد.
اگر پیش از علامت مزبور یک حرف، به عنوان مثال S را بکار ببرید، در آن صورت فقط رشته هایی که کاراکتر اول آن ها S می باشد، برگردانده می شوند. فرض بگیرید می خواهیم لیستی از دانشجویان که اسم فامیلی آن ها با کاراکتر S شروع می شود را واکشی کنیم. برای این منظور شرط را بدین صورت تنظیم می کنیم: LIKE 'S%'. جهت انجام این کار به صورت ویژوال، داخل کادر Criteria، در زیر ستون Filter، شرط را وارد کنید.


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

می توانید این شرط را با قرار دادن عملگر NOT قبل از آن نقیض کرد.


مثال:
SELECT FirstName [First Name], LastName "Last Name", Gender,
       [State], ParentsNames [Parents Names]
FROM   Registآموزش SQL
ration.Students WHERE NOT (LastName LIKE N'S%')

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

آموزش کاربردی

1. برای مشاهده ی آن دسته از مقادیر ستون manufacturer که اسم آن ها به e ختم می شود، در پنجره ی Criteria، در حالی که هنوز اشاره گر موس بر روی کادر Filter (مربوط به Manufacturer) قرار دارد، like %e را وارد نمایید.
2. برای مشاهده ی نتیجه در منوی اصلی بر روی Query Designer کلیک کرده و سپس گزینه یExecute SQL را انتخاب کنید.


آموزش SQL

تطبیق یک sub-string در الگوی شرطی با مقادیر ستون مورد نظر

هنگامی که تنها یک کاراکتر را تایپ کرده و بلافاصله پس از آن علامت % درج می کنید، فقط همان کاراکتر درنظر گرفته شده و با مقادیر ستون تطبیق داده می شود. حال می گوییم که بجای یک کاراکتر، اجازه دارید چند کاراکتر را که در اصطلاح sub-string خوانده می شود همراه با علامت % بکار ببرید. برای مثال، اگر تعدادی first name در لیست دارید که با Ch شروع می شود ولی حروف پایانی آن ها را به خاطر ندارید، برای بازیابی آن ها دو کاراکتر اول را درج کرده و پس از آن علامت % را به عنوان جانشین برای کاراکترهایی که به خاطر نداریم استفاده می کنیم.


مثال:
SELECT FirstName [First Name], LastName "Last Name",
       Gender, [State],
       SingleParentHome "Single Parent Home?"
FROM   Registration.Students
WHERE  LastName LIKE N'Ch%';
GO

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

می توان دستور LIKE را با علامت % آغاز کرد. به عنوان مثال می توان به "%son" اشاره نمود. این عبارت تمامی مقادیر رشته ای که به زیررشته ی son ختم می شوند همچون Johnson یا Colson را بازیابی می کند.
در صورتی که اول و آخر رشته ی مورد جستجو را به خاطر ندارید، اما زیررشته ی در دست دارید که بخشی از رشته ی مورد نظر است، می توانید زیررشته را مابین دو علامت % تایپ نمایید. مثال LIKE "%an%". با توجه به مثال ذکر شده، تمامی مقادیر رشته ای ستون که زیررشته ی "an" جزئی از آنهاست برگردانده می شوند.


آموزش SQL
این دستور را هم می توان به وسیله ی عملگر NOT نقیض کرد.

آموزش کاربردی

1. در Query Designer، به پنجره ی Criteria مراجعه کرده، بر روی کادر Filter مربوط به فیلد Manufacturer کلیک نمایید و سپس محتویات آن را پاک کنید.
2. برای اجرای کد جاری و مشاهده ی نتیجه، در نوارابزار Query Designer بر روی دکمه ی آموزش SQLکلیک نمایید.
3. با نوار پیمایش به پایین کادر Results رفته تا رشته های متعددی حاوی Ralph Lauren ظاهر گردد. در پنجره ی Criteria، بر روی کادر Filter مربوط به فیلد Manufacturer کلیک نموده و عبارت LIKE %Lauren% را وارد کنید.


آموزش SQL

4. برای اجرای دستورات و مشاهده ی خروجی آن، در نوارابزار Query Designer بر روی دکمه ی آموزش SQLکلیک نمایید.

آموزش SQL

5. در پنجره ی Criteria، بر روی کادر Filter مربوط به فیلد Manufacturer کلیک نموده و محتویات آن را حذف کنید.
6. حال بر روی کادر Filter مربوط به فیلد ItemName کلیک نموده و عبارت LIKE %dress% را درج کنید.
7. برای اجرای کد جاری، در نوار ابزار Query Designer برروی دکمه ی آموزش SQLکلیک نمایید.

آموزش SQL

مقایسه و تطبیق با مجموعه ای از کاراکترها (علامت -)

چنانچه می خواهید (به عنوان مثال) حرف آخر کاراکتر رشته ی مورد جستجو بین محدوده خاصی از حروف الفبا باشد (برای مثال حرف آخر آن از p تا s باشد)، در آن صورت می توانید آن محدوده ی خاص کاراکتر را داخل [] ذکر نمایید. علامت % را می توان قبل یا بعد از کاراکتر یا زیررشته به نشانه ی اینکه اول و آخرش هر کاراکتری باشد تایپ کرد.
برای مشخص کردن محدوده ی حروف الفبا، ابتدا ] باز، سپس اولین حرف که محدوده از آن آغاز می شود و به دنبال آن علامت " - "، آخرین کاراکتر که محدوده ی مورد نظر به آن ختم می شود و در پایان [ بسته را درج نمایید.


SELECT FirstName [First Name], LastName "Last Name",
       Gender, [State],
       CASE SingleParentHome
		WHEN 1 THEN N'Yes'
		WHEN 0 THEN N'No'
		ELSE N'Unknown'
       END "Single Parent Home?"
FROM   Registration.Students
WHERE  LastName LIKE N'%[p-s]'

آموزش SQL

همان طور که مشاهده می کنید، لیست تنها اسم دانش آموزانی را شامل می شود که فامیلی (last name) آن ها با حرفی که مابین دو کاراکتر p و s قرار دارد (از p شروع شده تا s ادامه دارد) پایان می یابد.


علامت ^ (نقیض " - ")

می توانیم عکس آنچه با استفاده از علامت " - " انجام می دادیم را به وسیله ی کاراکتر هشتک " ^ " ترتیب اثر دهیم. برای مثال، با بهره گیری از علامت یاد شده مقدار رشته ای را بازیابی کنیم که کاراکتر پایانی آن بین محدوده ی دو حرف p تا s نباشد (عملا اثر کاراکتر - را نقیض کنیم). برای این منظور (مشخص کردن محدوده ی کاراکتر حروف الفبا که نباید در رشته مورد جستجو باشد)، علامت ^ را داخل [] و پیش از محدوده ی دلخواه تایپ کنید.


مثال:
SELECT FirstName, LastName, Gender, SingleParentHome
FROM   Registration.Students
WHERE  (LastName LIKE N'%[^p-r]')

خروجی کد فوق فهرستی از دانش آموزانی است که فامیلی آن ها (مقدار فیلدهای ستون last name) به حرفی غیر از محدوده ی کاراکتری مشخص شده ختم می شود (حرف پایانی آن هر چه باشد به غیر از p، q، r یا s).
بار دیگر یاداور می شویم که با درج عملگر NOT پیش از عبارت، می توان آن را نقیض کرد. توجه داشته باشید که اگر عبارتی را که دربردارنده ی کاراکتر ^ هست نقیض کنید، در آن صورت نتیجه ای حاصل می گردد که با عدم استفاده از کاراکتر مزبور یکسان خواهد بود.


آموزش کاربردی

1. برای مشاهده ی لیستی از آیتم های تولید شده توسط Kenneth Cole، داخل پنجره ی Criteria، واژه ی Lauren را با عبارت LIKE %kenneth cole% (در کادر Filter مربوط به آن فیلد) جایگزین نمایید.
2. اکنون جهت مشاهده ی لیستی از همین آیتم ها به ترتیب حروف الفبا، در پنجره ی Criteria، بر روی کادر Sort Type کلیک نموده و گزینه ی Ascending را انتخاب نمایید.
3. به منظور اجرای کد و مشاهده ی نتیجه، در نوار ابزار Query Designer بر روی دکمه ی آموزش SQLکلیک نمایید.


آموزش SQL
  • 2966
  •    1216
  • تاریخ ارسال :   1394/11/28

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

ارسال

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

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