آموزش جامع SQL Server (جلسه پنج: فیلتر کردن داده‌ها – ۲)
1398/02/15 13:07 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه پنج: فیلتر کردن داده‌ها – ۲)

در جلسه قبلی توضیح دادیم که برای فیلتر کردن داده‌ها در SQL Server از چه دستورات، عملگرها و تکنیک‌هایی استفاده می‌شود و همچنین دستورات SELECT DISTINCT و WHERE و عملگرهای AND و OR را به صورت کامل توضیح دادیم.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

در این جلسه مبحث فیلتر کردن داده‌ها را کامل می‌کنیم و عملگر‌های IN و BETWEEN و LIKE و تکنیک aliasهای ستون و جدول را توضیح خواهیم داد. با ما همراه باشید!

عملگر IN در SQL Server

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

کد زیر syntax عملگر IN در SQL Server را نمایش می‌دهد:

column | expression IN ( v1, v2, v3, ...)

در این syntax:

  • ابتدا، ستون یا عبارتی که باید تست شود، مشخص شده است.
  • سپس، لیستی از مقادیر که باید تست شوند مشخص شده‌اند. همه مقادیر باید نوع یکسان با ستون یا عبارت داشته باشند.

اگر یک مقدار در ستون یا عبارت برابر با هر کدام از مقادیر موجود در لیست باشد، آنگاه نتیجه عملگر IN برابر است با TRUE. عملگر IN دقیقاً مانند این است که چندین عملگر OR به کار ببریم، در نتیجه پیش‌بینی‌های زیر درست و با هم برابر هستند:

column IN (v1, v2, v3)
 
column = v1 OR column = v2 OR column = v3

برای منفی کردن عملگر IN، از عملگر NOT IN به شکل زیر استفاده می‌شود:

column | expression NOT IN ( v1, v2, v3, ...)

نتیجه عملگر NOT IN برابر است با TRUE، اگر ستون یا عبارت با هیچ‌کدام از مقادیر موجود در لیست برابر نباشند. علاوه بر لیستی از مقادیر، می‌توانید از یک subquery استفاده کنید که با استفاده از عملگر IN، لیستی از مقادیر را بازمی‌گرداند. در کد زیر نمونه‌ای از آن را مشاهده می‌کنید:

column | expression IN (subquery)

در این syntax، کد subquery شامل یک دستور SELECT است که لیستی از مقادیر تک ستونه را بازمی‌گرداند. توجه کنید که اگر یک لیست شامل NULL باشد، آنگاه نتیجه IN یا NOT IN برابر با UNKNOWN خواهد بود.

مثال‌های عملگر IN در SQL Server

برای مثال‌ها، از جدول production.products از پایگاه داده نمونه Bikestores استفاده می‌کنیم.

جدول production.products از پایگاه داده نمونه Bikestores

الف) مثال استفاده از IN در لیستی از مقادیر

کد زیر محصولاتی را می‌یابد که قیمت آن‌ها یکی از این مقادیر باشند: ۸۹.۹۹، ۱۰۹.۹۹ و ۱۵۹.۹۹.

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price IN (89.99, 109.99, 159.99)
ORDER BY
    list_price;

نتایج

Query بالا با query زیر که در آن از عملگر OR استفاده شده است، برابر است:

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price = 89.99 OR list_price = 109.99 OR list_price = 159.99
ORDER BY
    list_price;

برای یافتن محصولاتی که قیمت آن‌ها برابر با هیچ‌کدام از لیست قیمت‌های بالا نیست، می‌توانید از عملگر NOT IN استفاده کنید که در query زیر مشاهده می‌کنید:

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price NOT IN (89.99, 109.99, 159.99)
ORDER BY
    list_price;

نتایج

ب) مثال استفاده از عملگر IN در یک subquery

Query زیر لیستی از ارقام id محصولات قرار گرفته در store id =1 که دارای تعداد برابر یا بیش از ۳۰ هستند را بازمی‌گرداند:

SELECT
    product_id
FROM
    production.stocks
WHERE
    store_id = 1 AND quantity >= 30;

نتایج

می‌توانید از query بالا به عنوان یک subquery برای عملگر IN استفاده کنید که در مثال زیر مشاهده می‌کنید:

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    product_id IN (
        SELECT
            product_id
        FROM
            production.stocks
        WHERE
            store_id = 1 AND quantity >= 30
    )
ORDER BY
    product_name;

نتایج

در این مثال:

  • ابتدا، subquery لیستی از product id را بازگرداند.
  • سپس، query بیرونی نام و قیمت محصولاتی که product id آن‌ها با مقادیر بازگردانده شده توسط subquery مطابقت داشتند را به دست آورد.

در نتیجه به این شکل، آموختید که چگونه می‌توانید از عملگر IN در SQL Server برای بررسی اینکه آیا یک مقدار با هر مقداری در یک لیست یا مقادیر بازگردانده شده توسط یک subquery برابر است یا خیر، استفاده کنید.

عملگر BETWEEN در SQL Server

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

کد زیر syntax عملگر BETWEEN را نشان می‌دهد:

column | expression BETWEEN start_expression AND end_expression

در این syntax:

  • ابتدا، ستون یا عبارتی که باید تست شود مشخص شده است.
  • سپس، عبارات ابتدایی و انتهایی بین کلیدواژه‌های BETWEEN و AND قرار گرفته‌اند. عبارات ابتدایی و انتهایی و عبارتی که باید تست شود باید یک نوع داده (data type) داشته باشند.

می‌توانید از عملگرهای بزرگ‌تر یا برابر با (=<) و کوچک‌تر یا برابر با (=>) برای جایگزینی عملگر BETWEEN به شکل زیر استفاده کنید:

column | expression <= end_expression AND column | expression >= start_expression   

کدی که از عملگر BETWEEN استفاده می‌کند، خوانایی خیلی بیشتری نسبت به کدی دارد که عملگر‌های مقایسه و عملگر منطقی AND استفاده می‌کند.

برای منفی کردن نتیجه عملگر BETWEEN، می‌توانید از عملگر NOT BETWEEN به شکل زیر استفاده کنید:

column | expression NOT BETWEEN start_expression AND end_expresion

عملگر NOT BETWEEN در صورتی که مقدار ستون یا عبارت کمتر از مقدار عبارت آغازین (start expression) و بزرگ‌تر از مقدار عبارت پایانی (end expression) باشد، مقدار TRUE را بازمی‌گرداند.

کد بالا با کد زیر برابر است:

column | expression < start_expression AND column | expression > end_expression

توجه کنید که اگر حتی یک ورودی برای BETWEEN یا NOT BETWEEN برابر با NULL باشد، آنگاه نتیجه UNKNOWN خواهد شد.

مثال‌های BETWEEN در SQL Server

اجازه دهید چند مثال با استفاده از عملگر BETWEEN ارائه دهیم تا دقیقاً عملکرد آن را درک کنید.

الف) مثال استفاده از BETWEEN برای اعداد در SQL Server

برای مثال‌ها از جدول products از پایگاه داده نمونه Bikestores استفاده می‌کنیم.

جدول products از پایگاه داده نمونه Bikestores

Query زیر محصولاتی را می‌یابد که قیمت‌های آن‌ها بین ۱۴۹.۹۹ و ۱۹۹.۹۹ باشند:

SELECT
    product_id,
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price BETWEEN 149.99 AND 199.99
ORDER BY
    list_price;

نتایج

برای به دست آوردن محصولاتی که قیمت آن‌ها در بازه ۱۴۹.۹۹ و ۱۹۹.۹۹ نیست، می‌توانید از عملگر NOT BETWEEN به شکل زیر استفاده کنید:

SELECT
    product_id,
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price NOT BETWEEN 149.99 AND 199.99
ORDER BY
    list_price;

نتایج

ب) مثال استفاده از عملگر BETWEEN برای تاریخ‌ها در SQL Server

جدول orders را در نظر بگیرید:

جدول orders

Query‌ زیر، سفارش‌هایی را می‌یابد که مشتریان بین تاریخ‌های ۱۵ ژانویه ۲۰۱۷ و ۱۷ ژانویه ۲۰۱۷ ثبت کرده‌اند:

SELECT
    order_id,
    customer_id,
    order_date,
    order_status
FROM
    sales.orders
WHERE
    order_date BETWEEN '20170115' AND '20170117'
ORDER BY
    order_date;

نتایج

توجه کنید که برای مشخص کردن یک ثابت تاریخ، می‌توانید از فرمت ‘YYYYMMDD’ استفاده کنید که YYYY نشان دهنده رقم سال با ۴ رقم است (مثل ۲۰۱۷)، MM نشان دهنده ماه با ۲ رقم (مانند ۰۱) و DD نشان دهنده روز با ۲ رقم (مانند ۱۵) هستند.

با این حساب، به طور کامل با عملکرد و طرز استفاده از عملگر BETWEEN در SQL Server آشنا شدید.

عملگر LIKE در SQL Server

LIKE در SQL Server یک عملگر منطقی است که مشخص می‌کند آیا یک رشته از کاراکترها با یک الگوی خاص تطابق دارند یا خیر. این الگو ممکن است شامل کاراکترهای معمولی یا کاراکترهای خاص باشد.

عملگر LIKE در دستور WHERE که زیرمجموعه دستورات SELECT، UPDATE و DELETE می‌تواند باشد، به کار می‌رود. این عملگر ردیف‌ها را بر اساس تطابق الگو فیلتر می‌کند. کد زیر syntax عملگر LIKE در SQL Server را نمایش می‌دهد:

column | expression LIKE pattern [ESCAPE escape_character]
الگو

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

  • علامت درصد (%): هر رشته از ۰ تا چند کاراکتر.
  • آندرلاین (_): هر تک کاراکتر.
  • ] لیست کاراکترها[: هر تک کاراکتر درون مجموعه مشخص شده.
  • ]کاراکتر – کاراکتر[: هر تک کاراکتر در بازه مشخص شده.
  • [^]: هر تک کاراکتر که در لیست یا یک بازه قرار ندارد.

کاراکترهای خاص باعث می‌شوند عملگر LIKE انعطاف‌پذیری بیشتری نسبت به عملگرهای مقایسه رشته مساوی (=) و نا مساوی (=!) داشته باشد.

کاراکتر فرار (Escape)

کاراکتر فرار یا Escape به عملگر LIKE دستور می‌دهد تا با کاراکترهای خاص همانند کاراکترهای معمولی رفتار کند. کاراکتر فرار هیچ مقدار پیش‌فرضی ندارد و باید فقط برای یک کاراکتر ارزیابی شود.

عملگر LIKE در صورتی که ستون یا عبارت با الگوی خاصی مطابقت داشته باشد، مقدار TRUE بازمی‌گرداند.

برای منفی کردن نتیجه عملگر LIKE، می‌توانید از عملگر NOT به شکل زیر استفاده کنید:

column | expression NOT LIKE pattern [ESCAPE escape_character]

مثال‌های LIKE در SQL Server

برای مثال‌ها از جدول customers در پایگاه داده نمونه Bikestores استفاده می‌کنیم:

جدول customers در پایگاه داده نمونه Bikestores

 مثال علامت % (درصد)

مثال زیر مشتریانی را می‌یابد که نام خانوادگی آن‌ها با حرف z شروع می‌شود:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE 'z%'
ORDER BY
    first_name;

نتایج

مثال زیر مشتریانی را بازمی‌گرداند که نام خانوادگی آن‌ها با رشته er پایان می‌یابد:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '%er'
ORDER BY
    first_name;

نتایج

کد زیر مشتریانی را بازمی‌گرداند که نام‌ خانوادگی آن‌ها با حرف t شروع و با حرف s پایان می‌یابد:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE 't%s'
ORDER BY
    first_name;

نتایج

مثال کاراکتر خاص آندرلاین (_)

آندرلاین یک کاراکتر تنها را نشان می‌دهد. برای مثال، کد زیر مشتریانی را بازمی‌گرداند که کاراکتر دوم آن‌ها حرف u باشد:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '_u%'
ORDER BY
    first_name; 

U

در الگوی %u_:

  • منظور اولین کاراکتر یعنی آندرلاین (_) این است که رشته با هر تک کاراکتری تطابق بیابد.
  • دومین حرف یعنی u دقیقاً با حرف u تطابق بیابد.
  • سومین کاراکتر یا % یعنی رشته با هر توالی از کاراکترها تطابق بیابد.
مثال از [لیست کاراکترها]

وقتی در یک لیست از کاراکترها کروشه به کار می‌رود (مانند [ABC])، یعنی یک تک کاراکتر که حتماً باید با یکی از کاراکترهای مشخص شده در لیست تطابق داشته باشد.

برای مثال، query زیر مشتریانی را بازمی‌گرداند که اولین کاراکتر در نام خانوادگی آن‌ها Y یا Z است:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '[YZ]%'
ORDER BY
    last_name;

نتایج

مثال از [کاراکتر – کاراکتر]

وقتی برای یک بازه از کاراکترها از کروشه استفاده می‌شود (مانند [A-C])، یعنی یک تک کاراکتر که باید درون یک بازه خاص قرار گیرد.

برای مثال، query زیر مشتریانی را می‌یابد که اولین کاراکتر نام خانوادگی آن‌ها یکی از حروف موجود در بازه حروف A تا C است:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '[A-C]%'
ORDER BY
    first_name;

نتایج

مثال از [لیست یا بازه کاراکتر^ ]

وقتی کروشه‌ها با علامت (^) و یک بازه از کاراکترها به کار می‌روند (مانند [A-C^] یا یک لیست کاراکتر (مانند [ABC])، یعنی یک تک کاراکتر که در بین بازه یا لیست کاراکتر مشخص شده قرار ندارد.

برای مثال، query زیر مشتریانی را بازمی‌گرداند که اولین کاراکتر نام خانوادگی ‌آن‌ها از حروف موجود در بازه حروف A تا X نباشد:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '[^A-X]%'
ORDER BY
    last_name;

نتایج

مثال عملگر NOT LIKE

مثال زیر از عملگر NOT LIKE برای یافتن مشتریانی استفاده می‌کند که کاراکتر اول نام خانوادگی آن‌ها حرف A نیست:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    first_name NOT LIKE 'A%'
ORDER BY
    first_name;

نتایج

بدین صورت، روش‌های مختلف استفاده از عملگر LIKE در SQL Server برای بررسی اینکه آیا یک رشته کاراکتر با یک الگوی خاص تطابق دارد یا خیر را آموختید.

Alias در SQL Server

وقتی از دستور SELECT برای query نویسی داده‌های یک جدول استفاده می‌کند، SQL Server از نام‌های ستون به عنوان سر تیتر برای خروجی استفاده می‌کنند. مثال زیر را مشاهده کنید:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;

نتایج

همان‌طور که در خروجی مشاهده می‌کنید، برای سر تیتر ستون‌ها از first_name و last_name استفاده شده ‌است.

برای به دست آوردن نام کامل مشتریان، می‌توانید با استفاده از عملگر متمرکز ساز (یعنی عملگر +) برای متمرکز کردن نام، space و نام خانوادگی استفاده کنید که مثال آن را در کد زیر مشاهده می‌کنید:

SELECT
    first_name + ' ' + last_name
FROM
    sales.customers
ORDER BY
    first_name;

نتایج

SQL Server ستون نام کامل را با عنوان No column name بازگرداند که در این مثال اصلاً درست و معنادار نیست. برای اختصاص یک نام موقتی به یک ستون یا یک عبارت در طول اجرای query، باید از alias ستون استفاده کنید.

کد زیر سینتکس alias ستون را نمایش می‌دهد:

column_name | expression  AS column_alias

در این syntax، از کلیدواژه AS برای مجزا کردن نام ستون یا عبارت از alias استفاده می‌شود. چون به کار بردن کلیدواژه AS اختیاری است، می‌توانید به شکل زیر یک alias به ستون اضافه کنید:

column_name | expression column_alias

حالا برگردیم به سراغ مثال بالا، می‌توانید query را با استفاده از یک alias ستون بازنویسی کنید:

SELECT
    first_name + ' ' + last_name AS full_name
FROM
    sales.customers
ORDER BY
    first_name;

توجه کنید که اگر alias ستون شامل space باشد، آنگاه همانند مثال زیر، باید آن را با استفاده از تک کوتیشن (‘ ‘) نمایش دهید:

SELECT
    first_name + ' ' + last_name AS 'Full Name'
FROM
    sales.customers
ORDER BY
    first_name;

نتایج

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

SELECT
    category_name 'Product Category'
FROM
    production.categories;

نتایج

در این مثال، alias ستون product category خیلی تمیزتر و قشنگ‌تر از نام ستون category name است. وقتی یک alias به یک ستون اختصاص می‌دهید، در دستور ORDER BY می‌توانید یا از نام ستون یا از alias ستون استفاده کنید، که در مثال زیر مشاهده می‌کنید:

SELECT
    category_name 'Product Category'
FROM
    production.categories
ORDER BY
    category_name;  
 
 
SELECT
    category_name 'Product Category'
FROM
    production.categories
ORDER BY
    'Product Category';

توجه کنید که دستور ORDER BY آخرین دستوری است که پردازش می‌شود، در نتیجه aliasهای ستون در زمان مرتب‌سازی شناخته می‌شوند.

Alias جدول در SQL Server

می‌توانیم به جدول یک alias بدهیم که همچنین با عنوان نام وابسته یا متغیر بازه شناخته می‌شود.

مشابه alias ستون، alias جدول هم می‌تواند با استفاده یا بدون استفاده از کلیدواژه AS اختصاص یابد:

table_name AS table_alias
table_name table_alias

مثال زیر را در نظر بگیرید:

SELECT
    sales.customers.customer_id,
    first_name,
    last_name,
    order_id
FROM
    sales.customers
INNER JOIN sales.orders ON sales.orders.customer_id = sales.customers.customer_id;

نتایج

در این مثال، هر دو جدول‌های customers و orders دارای ستونی با نام مشابه (customer_id) هستند، در نتیجه باید با استفاده از syntax زیر به ستون اشاره کنید:

table_name.column_name

مثلاً:

sales.custoners.customer_id
sales.orders.customer_id

اگر این کار را انجام ندهید، SQL Server یک ارور می‌دهد.

خواندن Query بالا خیلی مشکل است. خوشبختانه، می‌توانید خوانایی را با استفاده از alias جدول به شکل زیر افزایش دهید:

SELECT
    c.customer_id,
    first_name,
    last_name,
    order_id
FROM
    sales.customers c
INNER JOIN sales.orders o ON o.customer_id = c.customer_id;

در این query، c یک alias برای جدول sales.customers است و o یک alias برای جدول sales.orders است. وقتی یک alias به یک جدول اختصاص می‌دهید، باید از alias برای اشاره به ستون جدول استفاده می‌کنید. در غیر این صورت، SQL Server ارور می‌دهد.

خلاصه

در نتیجه تمام دستورات و تکنیک‌های فیلتر داده‌ها را با مثال، به صورت کامل بررسی کردیم.

در این دو جلسه انواع استفاده از دستورات SELECT DISTINCT و WHERE و عملگرهای AND، OR، IN، BETWEEN و LIKE و تکنیک اختصاص دادن یک Alias به ستون یا جدول را مورد بررسی قرار دادیم و عملکرد و کاربرد آن‌ها را آموزش دادیم.

البته این‌ها همه جزئی از اصول اولیه SQL Server هستند و راه خیلی طولانی‌ای تا انتهای دوره داریم.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات