توابع SQL مهم و کاربردی که باید بدانید – ۲۸ تابع با مثال – فرادرس

توابع SQL ابزاری هستند که برای کار با داده‌های و جدول‌ها در پایگاه‌های داده توسط خود SQL یا کاربران تعریف شده‌اند. «زبان جست‌وجوی ساختاریافته» (Structured Query Language) که به صورت متداول به عنوان SQL شناخته می‌شود، زبان استانداردی است که برای مدیریت و جست‌وجوی داده‌ها در پایگاه‌های داده رابطه‌ای به‌کار می‌رود. یادگیری زبان برنامه نویسی جدید می‌تواند ترسناک باشد. مانند هر زبان دیگری، زبان‌های برنامه‌نویسی نیز دارای دایره لغات بزرگ و مختص به خودی هستند که باید به آن‌ها مسلط شویم. SQL هم که به دلیل نیاز به تعامل کارآمد با پایگاه‌های داده بزرگ ایجاد شده، امروز به ابزاری اجتناب‌ناپذیر برای کار توسط مدیران پایگاه‌های داده، تحلیل‌گران داده و توسعه‌دهندگان تبدیل شده است.

سینتکس این زبان به کاربران کمک می‌کند که عملیات مختلفی را بر روی داده‌ها و جداول پایگاه داده از قبیل فراخوانی، حذف داده، وارد کردن اطلاعات و به‌روزرسانی را انجام دهند. به همین دلیل سینتکس SQL، اسکلت‌بندی اصلی بسیاری از اپلیکیشن‌ها و نرم‌افزارهای مدرن و تصمیمات مبتنی بر داده است. در این مطلب از مجله فرادرس، بر روی چند مورد از کاربردی‌ترین توابع SQL تمرکز می‌کنیم که هر مدیر پایگاه داده‌ای باید با آن‌ها آشنا باشد. برای هر کدام نوع داده‌های ورودی و خروجی را همراه با مثال کدنویسی شده‌ای برای کمک به درک بهتر مطلب ارائه داده‌ایم.

توابع SQL چه هستند؟

توابع SQL فرمان‌هایی هستند که عملیات خاصی را بر روی داده‌ها اجرا می‌کنند. به عنوان مثال، چند مورد از توابع SQL پُرکاربرد، شامل تابع SUM()

 برای اضافه کردن اعداد با هم، LOWER()

 برای تبدیل کردن کاراکترهای متن به حروف کوچک و تابع COUNT()

 برای شماردن عناصر درون پایگاه داده هستند.

از آن‌جا که توابع SQL حالت‌های مختلفی دارند، از قبیل حالت آنلاین، حالت اضطراری و غیره، به همین‌ ترتیب هم تعداد بسیار زیاد و متنوعی تابع در SQL تعریف شده‌اند. توابع SQL، اقدامات از پیش تعریف شده‌ای هستند که کاربران می‌توانند برای بدست آوردن نوع خاصی از جواب با سرعت بیشتر استفاده کنند. از کاربردهای این توابع می‌توان به انجام محاسبات یا نگهداری داده‌ها اشاره کرد. هدف اصلی توابع SQL خودکارسازی اجرای عملیاتی است که به صورت طبیعی پُرتکرار‌ هستند.

پرکاربردترین توابع از نوع داده رشته، عدد، تاریخ و زمان، عبارات شرطی، توابع تجمعی و غیره استفاده می‌کنند. با این روش، کار کاربران برای بدست آوردن داده‌های جواب با دقت بالا راحت‌تر می‌شود.

مراحل یادگیری کار با SQL Server در فرادرس

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

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

توابع SQL چند نوع هستند؟

توابع درونی SQL بر حسب نوع کاربرد و ورودی و خروجی به ۶ دسته مهم تقسیم می‌شوند. هر دسته هم توابع مخصوص به خود را دارد. در فهرست زیر این دسته‌های کلی را نام برده‌ایم.

• «توابع تجمعی» (Aggregate Functions)
• «توابع تحلیلی» (Analytic Functions)
• «توابع SQL مخصوص کار با بیت‌ها» (Bit Manipulation SQL Functions)
• توابع SQL برای «رتبه بندی» (Ranking Functions)
• توابع Rowset
• «توابع اسکالر» (Scaler Functions)

در هر کدام از این دسته‌بندی‌ها تعداد زیادی تابع وجود دارد که کاربران باید مورد توجه قرار دهند. در ادامه مطلب به بررسی چند مورد از مهم‌ترین توابع مربوط به هر دسته پرداخته‌ایم.

نکته مهم: شاید بعضی از توابع در چند دسته مختلف به صورت همزمان قرار بگیرند. برای همین توابع مذکور به صورت تکراری نمایش داده می‌شوند. در این مطلب برای جلوگیری از تکرار بی‌مورد، توابع یکسان در دسته‌های مختلف را نمایش نداده و فقط معرفی می‌کنیم.

توابع تجمعی همراه با مثال

توابع تجمعی اغلب برای محاسبه مجموعه خاصی از مقادیر به‌کار برده می‌شوند، که به عنوان جواب مقدار مجزایی را برمی‌گردانند. کاربران می‌توانند از طریق بند HAVING در عبارت SELECT از کاربرد این توابع بهره‌مند شوند.

در این قسمت از مطلب، ۵ مورد مهم از این توابع را همراه با مثال توضیح داده‌ایم.

۱. تابع COUNT

تابع COUNT نوعی از توابع تجمعی است که به عنوان خروجی تعداد رکوردهای مشخص شده توسط عبارت کوئری SELECT در زبان SQL را شمرده و برمی‌گرداند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1COUNT( [ ALL | DISTINCT] expression)

پارامترها و مقدار خروجی تابع

همان‌طور که در سینتکس بالا مشاهده می‌شود، این تابع دارای دو نوع پارامتر اصلی و یک عبارت expression

 است.

• ALL

   : تابع تجمعی را بر روی همه مقادیر درون گروه اعمال می‌کند. یعنی همه مقادیر شمرده می‌شوند. به صورت پیش‌فرض، این پارامتر اعمال می‌شود.

• DISTINCT

   : تابع تجمعی را فقط بر روی مقادیر متمایز اعمال می‌کند، نه مقادیر «تهی» (Null).

• expression

  : این عبارت از هر نوع داده به‌جز متن، «NTEXT» و تصویر را می‌پذیرد.

علامت ستاره *

 تعیین می‌کند که تابع COUNT()

برای شمردن مجموع ردیف‌های جدول باید همه سطرها را در نظر بگیرد.

تابع (*)COUNT برای برگرداندن مجموع ردیف‌های جدول قواعد زیر را رعایت می‌کند.

1. مقادیر تکراری و Null را هم محاسبه می‌کند.
2. هیچ پارامتر دیگری از جمله DISTINCT

   را نمی‌پذیرد.

3. از آن‌جا که هیچ اطلاعات خاصی درباره ستون‌ها را استفاده نمی‌کند، پس نیازی هم به پارامتر expression

   ندارد.

خروجی تابع COUNT()

از نوع داده Integer است. برای جدول‌های بسیار بزرگ با تعداد بیشتر از 2^31-1

 ردیف باید از تابع COUNT_BIG()

 استفاده کنیم.

مثال

در مثال پایین کل مقدار ستون DepartmentId

 را به صورت مجزا به دست می‌آوریم. همچنین تعداد مقدارهای ستون DepartmentId

 را در جدول Employee

 محاسبه می‌کنیم.

۲. تابع SUM

تابع SUM مجموع همه مقادیر یا فقط مقادیر متمایز شده در عبارت داده شده را محاسبه می‌کند. این تابع فقط در ارتباط با ستون یا عبارت‌های حاوی مقادیر عددی قابل استفاده است. تابع SUM()

مقادیر NULL را نادیده می‌گیرد. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1SUM ( [ ALL | DISTINCT ] numeric_expression)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. numeric_expression

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

2. ALL

   : این مقدار به صورت پیش‌فرض تنظیم می‌شود. یعنی تابع SUM()

   همه مقادیر را در نظر می‌گیرد.

3. DISTINCT

   : با کمک این پارامتر فقط مقادیر متمایز شده درون numeric_expression

   به صورت یکتا توسط تابع SUM()

   در نظر گرفته می‌شوند.

مقادیر برگشتی این تابع را در جدول زیر نمایش داده‌ایم.

مثال

از تابع SUM()

بر روی جدول Employee

زیر، استفاده خواهیم کرد. در واقع بیشتر مثال‌های توابع تجمعی را بر روی جدول زیر اجرا کرده‌ایم.

با کمک کد زیر میزان کل حقوق همه کارمندان را به دست می‌آوریم.

1SELECT SUM(SALARY) FROM Employee;

چون تابع SUM()

از نوع توابع تجمعی است، در کنار ستون‌هایی که در بند group by در SQl نیستند، نمی‌توان از این تابع استفاده کرد. به عنوان مثال، برای به‌کار بردن تابع SUM()

روی ستون DepartmentID

 از بند GROUP BY

 استفاده می‌کنیم.

1SELECT DepartmentID, SUM (SALARY) 
2FROM Employee
3GROUP BY DepartmentID

خروجی، مانند تصویر زیر نمایش داده می‌شود.

۳. تابع AVG

تابع AVG()

 نوعی از توابع تجمعی است که مقدار میانگین داده‌های درون ستون مشخص شده را محاسبه می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1AVG([ALL | DISTINCT] expression)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. expression

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

2. ALL

   : مقدار پیش‌فرضی است که همه مقادیر موجود در expression

   یا ستون را مشخص می‌کند.

3. DISTINCT

   : برای محاسبه میانگین، فقط مقادیر یکتا را در نظر می‌گیرد.

مقادیر برگشتی این تابع را در جدول زیر نمایش داده‌ایم.

مثال

در مثال زیر، تابع AVG()

همراه با ستون Salary

 از جدول Employee

استفاده شده است. این تابع مقدار میانگین همه حقوق‌ها را در کنار میانگین حقوق‌های متمایز محاسبه می‌کند.

1SELECT AVG (ALL Salary) AS AllSalary, AVG (DISTINCT Salary) AS DistinctSalary 
2FROM Employee

۴. تابع MIN

تابع MIN()

 کمترین مقدار داده موجود در ستون یا حاصل از عبارتی را محاسبه می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا تابع MIN()

دارای فقط یک پارامتر اصلی است.

1. expression

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

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

مثال

از تابع MIN()

 در کنار بندهای مختلفی مانند HAVING

و BETWEEN در SQL می‌توان استفاده کرد. در مثال زیر هم این تابع را در کنار بند HAVING

 به‌کار برده‌ایم. در این مثال بند GROUP BY

، کارمندان را بر طبق بخش‌سازمانی هر کدام دسته‌بندی کرده‌ است. در هر بخش‌ سازمانی میزان کمترین حقوق را محاسبه می‌کند. بعد از آن بند HAVING

نتایج را با برگداندن بخش‌هایی از سازمان که حداقل حقوق آن‌ها پایین تر از 3500

 است فیلتر کرده.

1SELECT DepartmentId, MIN(Salary) AS DeptMinSal FROM Employee
2        GROUP BY DepartmentID
3        HAVING MIN (Salary) < 35000;

۵. تابع MAX

تابع MAX()

 بیشترین مقدار داده موجود در ستون یا حاصل از عبارتی را محاسبه می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا تابع MAX()

دارای فقط یک پارامتر اصلی است.

1. expression

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

به عنوان خروجی، مقدار بیشینه داده‌های درون گروه مشخص شده را برمی‌گراند که همه از نوع داده یکسانی باشند. تابع MAX()

مقادیر NULL را نادیده می‌گیرد.

مثال

تابع MAX()

را مانند تابع MIN()

بر روی ستون‌های حاوی نوع داده رشته نیز می‌توان به‌کار برد. برای مثال، در کد زیر از تابع MAX()

بر روی ستون LastName

 در جدول Employee

استفاده می‌کنیم. این تابع مقادیر درون ستون را با ترتیب حروف الفبا مرتب کرده و آخرین داده را برمی‌گرداند.

1SELECT MAX(LastName) FROM Employee;

در جدول کارمندان با توجه به حروف الفبا نام ‘Troy’

 آخرین مقدار است. بنابراین این مقدار برگشت داده می‌شود.

توابع تحلیلی SQL همراه با مثال

هر زمان که محاسبه‌ای بر اساس مقدار کل داده‌های موجود در گروهی از ردیف‌ها انجام شود، عملیات انجام شده از نوع عملیات تحلیلی است. توابع تحلیلی برای هر گروه از ردیف‌ها می‌توانند سطرهای زیادی ایجاد کنند. چند مثال مهم و پرکاربرد در این بخش از مطلب درباره توابع تحیلی ارائه داده‌ایم.

۶. تابع ROW_NUMBER

تابع ROW_NUMBER در SQL عدد یکتایی را به هر رکورد در گروه مشخص شده تخصیص می‌دهد. این عملیات با عدد 1

 شروع می‌شود. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1ROW_NUMBER() OVER (
2[PARTITION BY partition_expression, ... ]
3ORDER BY sort_expression [ASC | DESC], ...
4)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ بند اصلی است.

1. OVER

    :‌ این بند مجموعه‌ای از ردیف‌ها را مشخص می‌کند که تابع بر روی آن‌ها عملیات خود را اجرا می‌کند. برای این بند می‌توان از دو عبارت کلیدی PARTITION BY

   و ORDER BY استفاده کرد.

2. PARTITION BY

    : این بند اختیاری است. با کمک این بند مجموعه جواب‌ها را می‌توان به «دسته‌های مختلفی» (Partitions) از ردیف‌ها تقسیم کرد. در آن صورت تابع ROW_NUMBER()

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

3. ORDER BY

    : این بند الزامی است و ترتیب قرارگیری ردیف‌ها را در هر دسته مشخص می‌کند. تابع ROW_NUMBER()

   یک تابع وابسته به ترتیب – قرارگیری ردیف‌ها- است.

برای درک بهتر خروجی به مثال توجه کنید.

مثال

مثال این بخش را بر روی جدول کارمندانی به شکل زیر پیاده‌سازی می‌کنیم.

در کوئری نمایش داده شده در پایین، با استفاده از تابع ROW_NUMBER()

به هر کارمند جدول بالا شماره ترتیبی اختصاص داده‌ایم.

1SELECT ROW_NUMBER() OVER (ORDER BY E_id) row_num, E_name FROM Employee;

خروجی کوئری بالا به صورت زیر است.

۷. تابع RANK

تابع RANK()

 رتبه‌ای را به هر ردیف در گروهی از مجموعه‌ داده‌ها اختصاص می‌دهد. این رتبه از نوع عدد صحیح است. به تابع RANK()

تابع پنجره‌ای یا «Window» نیز می‌گویند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1SELECT column_name,
2RANK() OVER (PARTITION BY... ORDER BY...) as rank
3FROM table_name;

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ مورد اصلی است.

1. column_name

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

2. PARTITION BY

   : در این بند، مجموعه ردیف‌های نتایج، بر اساس یک یا چند پارامتر به تکه‌های مختلفی تقسیم می‌شود.

3. ORDER BY

    : در هر تکه از داده‌ها که تابع RANK()

   اعمال شده، ردیف‌ها را مرتب می‌کند.

برای درک بهتر خروجی به مثال توجه کنید.

مثال

جدول دانش‌آموزان زیر را در نظر بگیرید. در این جدول می‌خواهیم که ستون STUDENTNAME

 را بر اساس ستون STUDENT MARKS

 رتبه‌بندی کنیم.

با کد زیر ستون STUDENTNAME

را بر اساس ستون STUDENT MARKS

رتبه‌بندی کرده و در ستون جدیدی به نام StudentRank

 ذخیره می‌کنیم.

خروجی کد بالا به صورت جدول زیر می‌شود.

۸. تابع DENSE_RANK

تابع DENSE_RANK()

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

با تابع RANK()

است.

سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۲ پارامتر اصلی است.

1. partition_by_clause

    : با کمک این پارامتر در ابتدا مجموعه جواب به دست آمده از بند FROM

    را به چند بخش تقسیم می‌کنیم و سپس تابع DENSE_RANK()

   بر روی هر بخش اعمال می‌شود.

2. order_by_clause

    : ترتیبی را مشخص می‌کند که بر اساس آن تابع DENSE_RANK()

   بر روی ردیف‌های هر بخش اعمال می‌شود.

در خروجی رتبه‌ها را بر اساس نوع داده Bigint شماره‌گذاری می‌کند.

مثال

در مثال زیر محصولات کارخانه‌ای را رتبه‌بندی می‌کنیم. مطابق با موجودی هر کالا و با توجه به مکان کالاها عملیات رتبه‌بندی انجام می‌شود. تابع DENSE_RANK()

مجموعه جواب‌ها را با توجه به LocationID

 تقسیم بندی می‌کند. سپس به صورت منطقی مجموعه جواب بدست آمده را مطابق با Quantity

 مرتب می‌کند. توجه کنید که در مثال کالاهای 494

 و 495

 تعداد موجودی یکسانی دارند. به دلیل برابر بودن تعداد موجودی هر دو رتبه شماره یک را دارند.

1USE AdventureWorks2022;
2GO
3SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
4    ,DENSE_RANK() OVER
5    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
6FROM Production.ProductInventory AS i
7INNER JOIN Production.Product AS p
8    ON i.ProductID = p.ProductID
9WHERE i.LocationID BETWEEN 3 AND 4
10ORDER BY i.LocationID;
11GO

مجموعه جواب حاصل از کد بالا به صورت زیر است.

۹. تابع NTILE

تابع NTILE()

 نوعی از توابع پنجره‌ای است که ردیف‌های درون گروه مرتب شده‌ای را به مجموعه‌ای از اعداد با قسمت‌های تقریبا مساوی تقسیم می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1NTILE(number_expression) OVER ( [PARTITION BY partition_expression ] ORDER BY sort_expression [ASC | DESC])

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. number_expression

    : این عبارت عدد صحیح را نشان می‌دهد که ردیف‌ها باید بر اساس آن تقسیم شوند.

2. PARTITION BY

   : بند PARTITION BY

    اختیاری است. این بند در جایی که تابع NTILE()

   استفاده شود، ردیف‌های مجموعه نتیجه را به بخش‌های مختلف تقسیم می‌کند.

3. ORDER BY

   : ترتیب ردیف‌ها را در هر بخش مشخص می‌کند.

برای درک بهتر خروجی به مثال زیر توجه کنید.

مثال

جدول ساده‌ای به نام Faradars_demo

 را به شکل زیر در نظر گرفته‌ایم.

با کمک تابع NTILE()

در کوئری زیر، ردیف‌های بالا را به ۳ گروه مختلف تقسیم می‌کنیم.

1SELECT ID,
2NTILE (3) OVER (
3ORDER BY ID
4) Group_number
5FROM Faradars_demo; 

خروجی حاصل از اجرای کوئری بالا به شکل زیر است.

۱۰. تابع LEAD

تابع LEAD()

 ، به داده‌های درون ردیف‌ بعدی در مجموعه نتایج یکسان، بدون استفاده از «Self-Join» دسترسی پیدا می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
2      OVER ( [ partition_by_clause ] order_by_clause )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۵ پارامتر اصلی است.

1. scalar_expression

    : عبارت مربوط به این پارامتر، مقداری که باید برگشت داده شود را بر اساس offset

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

2. offset

   : تعداد ردیف‌هایی که از ردیف فعلی تا محل کسب جواب فاصله دارند. اگر مقداری تعیین نشده بود به صورت پیش‌فرض برابر با 1

     قرار می‌گیرد. offset

   می‌تواند ستون، زیرکوئری یا هر عبارت دیگری باشد که منتج به عدد صحیح مثبت می‌شود. offset

   نباید عدد منفی یا تابع تحلیلی باشد.

3. default

    : وقتی که offset

   بیش‌تر از محدوده قطعه محل اجرای تابع است، این مقدار باید برگشت داده ‌شود. اگر مقدار خاصی مشخص نشده باشد، NULL به خروجی برگردانده می‌شود. نوع داده default

   باید با scalar_expression

   سازگار باشد.

4. [ IGNORE NULLS | RESPECT NULLS ]

    : در زمان محاسبه اولین مقدار هر پارتیشن، IGNORE NULLS

    مقادیر NULL را نادیده می‌گیرد و بر عکس، RESPECT NULLS

    مقادیر NULL را نیز در نظر می‌گیرد. حالت RESPECT NULLS

   به صورت پیش‌فرض در نظر گرفته شده است.

5. OVER ( [ partition_by_clause ] order_by_clause )

    : کد partition_by_clause

   مجموعه نتایج بند FROM

   را برای استفاده توسط تابع به گروه‌های کوچکتری دسته‌بندی می‌کند. همچنین order_by_clause

    قبل از استفاده شدن تابع، داده‌ها را منظم می‌سازد.

در صورتی که scalar_expression

برابر با NULL شود یا مقدار default

بر روی NULL تنظیم شده باشد، NULL برگشت داده می‌شود. در غیر این صورت نوع داده scalar_expression

برگشت داده می‌شود.

مثال

کوئری زیر از تابع LEAD()

برای نشان دادن اختلاف پورسانت فروش برای کارمند مشخصی در سال‌های بعد، استفاده کرده است. چون‌که هیچ مقدار بعدی برای آخرین ردیف وجود ندارد، مقدار پیش‌فرض 0

 برگشت داده می‌شود.

1USE AdventureWorks2022;
2GO
3
4SELECT BusinessEntityID,
5    YEAR(QuotaDate) AS SalesYear,
6    SalesQuota AS CurrentQuota,
7    LEAD(SalesQuota, 1, 0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
8FROM Sales.SalesPersonQuotaHistory
9WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005', '2006');

در اثر اجرای کد بالا نتیجه زیر نمایش داده می‌شود.

۱۱. تابع LAG

تابع LAG()

 به داده‌های درون ردیف‌ بعدی در مجموعه نتایج یکسان، بدون استفاده از «Self-Join» دسترسی پیدا می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
2     OVER ( [ partition_by_clause ] order_by_clause )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. scalar_expression

   : عبارت مربوط به این پارامتر، مقداری که باید برگشت داده شود را بر اساس offset

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

2. [ IGNORE NULLS | RESPECT NULLS ]

   : در زمان محاسبه اولین مقدار هر پارتیشن، IGNORE NULLS

   مقادیر NULL را نادیده می‌گیرد و بر عکس، RESPECT NULLS

   مقادیر NULL را نیز در نظر می‌گیرد. حالت RESPECT NULLS

   به صورت پیش‌فرض در نظر گرفته شده است.

3. OVER ( [ partition_by_clause ] order_by_clause )

    : کد partition_by_clause

   مجموعه نتایج بند FROM

   را برای استفاده توسط تابع به گروه‌های کوچکتری دسته‌بندی می‌کند. همچنین order_by_clause

   قبل از استفاده شدن تابع، داده‌ها را منظم می‌سازد.

در صورتی که scalar_expression

برابر با NULL شود یا مقدار default

بر روی NULL تنظیم شده باشد، NULL برگشت داده می‌شود. در غیر این صورت نوع داده scalar_expression

برگشت داده می‌شود.

مثال

کوئری زیر از تابع LAG()

برای نشان دادن اختلاف پورسانت فروش برای کارمند مشخص شده، در سال‌های قبل استفاده کرده است. چون‌که هیچ مقداری قبل از اولین ردیف وجود ندارد، مقدار پیش‌فرض 0

 برگشت داده شده است.

1USE AdventureWorks2022;
2GO
3SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
4       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
5FROM Sales.SalesPersonQuotaHistory
6WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

در اثر اجرای کد بالا نتیجه زیر نمایش داده می‌شود.

۱۲. تابع FIRST_VALUE

تابع FIRST_VALUE()

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

1FIRST_VALUE ( [ scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
2    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. scalar_expression

   : مقداری را نشان می‌دهد که باید برگشت داده شود. به ازای این عبارت نمی‌توان از توابع تحلیلی استفاده کرد.

2. [ IGNORE NULLS | RESPECT NULLS ]

   : در زمان محاسبه اولین مقدار هر پارتیشن، IGNORE NULLS

   مقادیر NULL را نادیده می‌گیرد و بر عکس، RESPECT NULLS

   مقادیر NULL را نیز در نظر می‌گیرد. حالت RESPECT NULLS

   به صورت پیش‌فرض در نظر گرفته شده است.

3. [ partition_by_clause ] order_by_clause[ rows_range_clause ]

    : کد partition_by_clause

   مجموعه نتایج بند FROM

   را برای استفاده توسط تابع به گروه‌های کوچکتری دسته‌بندی می‌کند. همچنین order_by_clause

   قبل از استفاده شدن تابع، داده‌ها را منظم می‌سازد. این پارامتر ضروری است. rows_range_clause

    با مشخص کردن نقاط شروع و پایان، محدودیت‌های بیشتری را بر روی ردیف‌های درون بخش‌ها اعمال می‌کند.

خروجی این تابع از نوع داده scalar_expression

است.

برای پیاده‌سازی هر کدام از این مثال‌های لازم است که در ابتدا وارد نرم افزار پایگاه داده SQL خود شوید. سپس پایگاه داده‌ای فرضی ایجاد کرده و در پایگاه داده فرضی ایجاد شده یک یا چند جدول نیز تعریف کنید. در صورت نیاز به کمک در این مراحل، بهتر است مطالب آموزش از صفر تا صد ساخت دیتابیس در SQL و آموزش کامل ساخت جدول در SQL همراه با مثال و کد دستورات را از مجله فرادرس مطالعه کنید.

مثال

مثال زیر، از تابع FIRST_VALUE()

برای برگرداندن نام کم‌ هزینه‌ترین محصول در دسته‌بندی محصولات استفاده کرده‌ایم.

1USE AdventureWorks2022;
2GO
3
4SELECT Name,
5    ListPrice,
6    FIRST_VALUE(Name) OVER (
7        ORDER BY ListPrice ASC
8    ) AS LeastExpensive
9FROM Production.Product
10WHERE ProductSubcategoryID = 37;

خروجی حاصل از اجرای کوئری بالا به شکل زیر است.

۱۳. تابع LAST_VALUE

تابع LAST_VALUE()

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

1LAST_VALUE ( [ scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
2    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. scalar_expression

   : مقداری را نشان می‌دهد که باید برگشت داده شود. به ازای این عبارت نمی‌توان از توابع تحلیلی استفاده کرد.

2. [ IGNORE NULLS | RESPECT NULLS ]

   : در زمان محاسبه اولین مقدار هر پارتیشن، IGNORE NULLS

   مقادیر NULL را نادیده می‌گیرد و بر عکس، RESPECT NULLS

   مقادیر NULL را نیز در نظر می‌گیرد. حالت RESPECT NULLS

   به صورت پیش‌فرض در نظر گرفته شده است.

3. [ partition_by_clause ] order_by_clause [ rows_range_clause ]

   : کد partition_by_clause

   مجموعه نتایج بند FROM

   را برای استفاده توسط تابع به گروه‌های کوچکتری دسته‌بندی می‌کند. همچنین order_by_clause

   قبل از استفاده شدن تابع، داده‌ها را منظم می‌سازد. این پارامتر ضروری است. rows_range_clause

   با مشخص کردن نقاط شروع و پایان، محدودیت‌های بیشتری را بر روی ردیف‌های درون بخش‌ها اعمال می‌کند.

خروجی این تابع از نوع داده scalar_expression

است.

مثال

در کد زیر با استفاده از تابع LAST_VALUE()

، تاریخ استخدام آخرین کارمند هر بخش، با توجه به میزان حقوق مشخص شده «Rate» تعیین می‌شود. سپس با استفاده از بند PARTITION BY

کارمندان را برحسب بخش‌کاری تقسیم می‌کنیم. تابع LAST_VALUE()

بر روی هر بخش به صورت مستقل اعمال می‌شود. بند ORDER BY

که درون بند OVER

مشخص شده، نظم منطقی را مشخص می‌کند که تابع LAST_VALUE()

بر روی ردیف‌های هر بخش باید با توجه به آن ترتیب، اعمال شود.

1USE AdventureWorks2022;
2GO
3
4SELECT Department,
5    LastName,
6    Rate,
7    HireDate,
8    LAST_VALUE(HireDate) OVER (
9        PARTITION BY Department ORDER BY Rate
10    ) AS LastValue
11FROM HumanResources.vEmployeeDepartmentHistory AS edh
12INNER JOIN HumanResources.EmployeePayHistory AS eph
13    ON eph.BusinessEntityID = edh.BusinessEntityID
14INNER JOIN HumanResources.Employee AS e
15    ON e.BusinessEntityID = edh.BusinessEntityID
16WHERE Department IN (N'Information Services', N'Document Control');

خروجی حاصل از اجرای کوئری بالا به شکل زیر است.

توابع SQL مخصوص کار با بیت ها

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

۱۴. تابع BITAND

تابع BITAND()

 عملیات بیتی AND

 را بین دو مقدار عددی یا باینری انجام می‌دهد. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1BITAND( <expr1> , <expr2> [ , '' ] )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. expr1

    : عبارتی که باید ارزیابی شود. این عبارت باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

2. expr2

   : عبارت دومی که باید ارزیابی شود. این عبارت هم باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

3. padside

    : وقتی دو مقدار باینری طول یکسان نداشته باشند، این پارامتر تعیین می‌کند که به کدام سمت مقدار کوتاه‌تر، پدینگ اضافه شود. یکی از دو حالت LEFT

    و RIGHT

    قابل تعیین هستند. در صورت تفاوت طول دو عدد این پارامتر ضروری می‌شود.

تابع BITAND()

می‌تواند خروجی با نوع داده‌های Integer یا Binary یا NULL داشته باشد.

مثال

در کد زیر مثال ساده‌ای از استفاده این تابع را نشان داده‌ایم.

1DECLARE 
2   Test_Number number1 := 5;
3   Test_Number number2 := 3;
4   
5BEGIN 
6   dbms_output.put_line(BITAND(Test_Number number1, 
7                               Test_Number number2)); 
8
9END;

خروجی کوئری بالا برابر با عدد 1

 است.

۱۵. تابع BITOR

تابع BITOR()

 عملیات بیتی OR

 را بین دو مقدار عددی یا باینری انجام می‌دهد. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1BITOR( <expr1> , <expr2> [ , '' ] )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. expr1

   : عبارتی که باید ارزیابی شود. این عبارت باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

2. expr2

   : عبارت دومی که باید ارزیابی شود. این عبارت هم باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

3. padside

   : اگر دو مقدار باینری طول یکسان نداشته باشند، این پارامتر تعیین می‌کند که به کدام سمت مقدار کوتاه‌تر پدینگ اضافه شود. یکی از دو حالت LEFT

   و RIGHT

   قابل تعیین هستند. در صورت تفاوت طول دو عدد این پارامتر ضروری می‌شود.

تابع BITOR()

می‌تواند خروجی با نوع داده‌های Integer یا Binary یا NULL داشته باشد.

مثال

در کد زیر مثال ساده‌ای از استفاده این تابع را نشان داده‌ایم.

1EVALUATE 
2    { BITOR(9, 10) }

خروجی کوئری بالا برابر با عدد 11

 است.

۱۶. تابع BITXOR

تابع BITXOR()

 عملیات بیتی XOR

 را بین دو مقدار عددی یا باینری انجام می‌دهد. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1BITXOR( <expr1> , <expr2> [ , '' ] )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. expr1

   : عبارتی که باید ارزیابی شود. این عبارت باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

2. expr2

   : عبارت دومی که باید ارزیابی شود. این عبارت هم باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

3. padside

   : وقتی دو مقدار باینری طول یکسان نداشته باشند این پارامتر تعیین می‌کند که به کدام سمت مقدار کوتاه‌تر پدینگ اضافه شود. یکی از دو حالت LEFT

   و RIGHT

   قابل تعیین هستند. در صورت تفاوت طول دو عدد این پارامتر ضروری می‌شود.

تابع BITXOR()

می‌تواند خروجی با نوع داده‌های Integer یا Binary یا NULL داشته باشد.

مثال

در کد زیر مثال ساده‌ای از استفاده این تابع را نشان داده‌ایم.

1EVALUATE { BITXOR(9, 10) }

خروجی کوئری بالا برابر با عدد 3

 است.

۱۷. تابع BITNOT

تابع BITNOT()

 عملیات بیتی NOT

 یا نقیض را بر روی مقدار گرفته شده از نوع عدد صحیح یا باینری انجام می‌دهد. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای یک پارامتر اصلی است.

1. expr

   : عبارتی که ارزیابی می‌شود. این عبارت باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.

تابع BITNOT()

می‌تواند خروجی با نوع داده‌های Integer یا Binary یا NULL داشته باشد.

مثال

در کد زیر مثال ساده‌ای از استفاده این تابع را نشان داده‌ایم.

1SELECT BIT_COUNT(5) AS result

خروجی کوئری بالا برابر با عدد 2

 است.

۱۸. تابع BIT_COUNT

تابع BIT_COUNT()

 تعداد «یک»‌ها را در پارامتر باینری که تحویل گرفته شمرده و برمی‌گرداند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1BIT_COUNT ( expression_value )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای یک پارامتر اصلی است.

1. expression_value

    : هر عبارت منتج به عدد صحیح یا باینری که بزرگ نباشد.

تابع BIT_COUNT()

 در خروجی عددی با نوع داده Bigint برمی‌گرداند.

مثال

1SELECT BIT_COUNT ( 0xabcdef ) as Count;

جواب کوئری بالا برابر با 17

 است. زیرا شکل عدد 0xabcdef

 در نوع داده باینری برابر با «1010 1011 1100 1101 1110 1111» است. در این عدد ۱۷ بیت با مقدار 1

 وجود دارند.

توابع SQL مخصوص رتبه بندی

کاربران باید این مسئله را در نظر داشته باشند که توابع رتبه‌بندی ماهیت غیر منطقی دارند. بنابراین، این توابع به هر کاربری مقدار رتبه‌بندی را بر اساس هر ردیف در نظر گرفته شده برمی‌گردانند. تعداد عمده این توابع را مثل RANK()

و DENSE_RANK()

و ROW_NUMBER()

و NTILE()

در بخش‌های بالاتر بررسی کرده‌ایم.

همه این توابع برای این استفاده می‌شوند که مدیران پایگاه داده بتوانند در بهترین حالت ممکن کوئری‌ها را طراحی کنند. کوئری‌های طراحی شده با کمترین موارد اضافی و در خواناترین حالت ممکن باید بهترین نتایج مورد انتظار را از خود بروز دهند. برای آشنا شدن با تکنیک‌های کوئری‌نویسی می‌توانیم فیلم آموزش کوئری‌ نویسی پیشرفته در SQL Server را از فرادرس مشاهده کنیم. برای راحتی بیشتر مخاطبان محترم، لینک مورد نظر را در ادامه قرار داده‌ایم.

توابع Rowset

Rowset به مجموعه‌ای از ردیف‌های می‌گویند که شامل ستون‌هایی برای نگهداری داده‌ها در فیلد‌های مختلف می‌شوند. Rowset-های اشیا مهمی هستند که به همه فراهم‌کنندگان داده OLE DB در نمایش مجموعه جواب‌ها به شکل جدول کمک می‌کنند. برای درک بهتر این مطلب به مثال‌های پیاده‌سازی شده در ادامه توجه کنید.

۱۹. تابع OPENQUERY

تابع OPENQUERY()

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

1OPENQUERY ( linked_server ,'query' )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۲ پارامتر اصلی است.

1. linked_server

    : «مشخص کننده هویت» است. این identifier

    به نام سرور متصل شده اشاره می‌کند.

2. ‘ query ‘

    : رشته کوئری است که باید در سرور متصل شده اجرا شود. بیشترین طول قابل استفاده برای این رشته برابر با ۸KB است.

به غیر از خروجی مربوط به کوئری که در سرور متصل اجرا می‌شود، این کوئری خروجی دیگری ندارد. برای درک بهتر به مثال زیر توجه کنید.

مثال

در مثال زیر، کاری شبیه به استفاده از کوئری UPDATE در SQL به صورت «pass-through» را بر روی سرور متصل شده فرضی OracleSvr

 انجام می‌دهیم.

1UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101') 
2SET name = 'ADifferentName';

۲۰. تابع OPENDATASOURCE

تابع OPENDATASOURCE()

 بدون استفاده از نام سرور متصل شده، اطلاعات اتصال موقتی را به عنوان بخشی از نام چهار قسمتی شیء، ارائه می‌دهد. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1OPENDATASOURCE ( 'provider_name', 'init_string' )

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. ‘provider_name’

    : نامی است که برای دسترسی به منبع داده ثبت شده است. ‘provider_name’

   از نوع داده «Char» است و هیچ مقدار پیش‌فرضی ندارد.

2. ‘init_string’

    : رشته ارتباطی است که به رابط IDataInitialize

    از معرفی کننده مقصد ارسال شده است. این رشته ارتباطی از مقادیر کلید-مقداری استفاده می‌کند که با نقطه ویرگول از هم جدا شده‌ و به شکل ‘keyword1=value; keyword2=value’

    نمایش داده می‌شوند.

مثال

در مثال زیر برای دسترسی به جدول HumanResources.Department

 درون پایگاه داده AdventureWorks2022

 که بر روی سرور راه دور Seattle1

 قرار دارد از Microsoft OLE DB Driver for SQL Server

 استفاده کرده‌ایم. برای مشخص کردن مجموعه ردیف‌هایی که باید برگشت داده شوند، عبارت SELECT

 را به‌کار برده‌ایم. رشته فراهم کننده از کلمات کلیدی Server

 و Trusted_Connection

 استفاده می‌کند. این کلمات کلیدی توسط « SQL Server OLE DB Driver

 » شناسایی شده‌اند.

1SELECT GroupName, Name, DepartmentID
2FROM OPENDATASOURCE('MSOLEDBSQL', 'Server=Seattle1;Database=AdventureWorks2022;TrustServerCertificate=Yes;Trusted_Connection=Yes;').HumanResources.Department
3ORDER BY GroupName, Name;

توابع اسکالر

نوع خاصی از توابع SQL را به نام می‌شناسیم. وقتی که عبارت‌ مشخص شده معتبر باشد، در هر سناریوی، کاربران می‌توانند در SQL Server از توابع اسکالر استفاده کنند. این توابع مقدار مجزایی را به عنوان داده ورودی می‌پذیرند و همچنین مقدار مجزایی را به عنوان داده خروجی – نتیجه اجرای کوئری – برگشت می‌دهند.

در این بخش چند مورد از توابع اسکالر مورد استفاده در SQL را بررسی خواهیم کرد.

۲۱. تابع UCASE/UPPER

از این توابع برای تبدیل کردن حروف تشکیل دهنده رشته به حروف بزرگ زبان انگلیسی – معادل هر حرف در رشته اصلی – استفاده می‌شود. سینتکس پایه برای استفاده از این توابع به صورت زیر است.

سینتکس تابع بعدی در زیر آورده شده است.

هردوی توابع بالا با یکدیگر برابر هستند.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس‌های بالا این توابع دارای یک پارامتر اصلی هستند.

1. text

    : رشته‌ای که باید به حروف بزرگ زبان انگلیسی تبدیل شوند.

خروجی این توابع هم همان رشته اصلی با حروف بزرگ است.

مثال

1SELECT UPPER(CustomerName) AS UppercaseCustomerName
2FROM Customers;

تابع UCASE()

 هم دقیقا به شکل بالا مورد استفاده قرار می‌گیرد.

۲۲. تابع LCASE/LOWER

از این توابع برای تبدیل کردن حروف تشکیل دهنده رشته به حروف کوچک زبان انگلیسی – معادل هر حرف در رشته اصلی – استفاده می‌شود. سینتکس پایه برای استفاده از این توابع به صورت زیر است.

سینتکس تابع بعدی در زیر آورده شده است.

هردوی توابع بالا با یکدیگر برابر هستند.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس‌های بالا این توابع دارای یک پارامتر اصلی هستند.

1. text

    : رشته‌ای که باید به حروف کوچک زبان انگلیسی تبدیل شوند.

خروجی این توابع هم همان رشته اصلی با حروف کوچک است.

مثال

1SELECT LOWER(CustomerName) AS LowercaseCustomerName
2FROM Customers;

تابع LCASE()

 هم دقیقا به شکل بالا مورد استفاده قرار می‌گیرد.

۲۳. تابع LEN/LENGTH

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

سینتکس تابع بعدی در زیر آورده شده است.

هردوی توابع بالا با یکدیگر برابر هستند.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس‌های بالا این توابع دارای یک پارامتر اصلی هستند.

1. string

    : رشته‌ای که باید طول آن شمرده و اعلام شود.

مثال

1SELECT LENGTH(CustomerName) AS LengthOfName
2FROM Customers;

تابع LEN()

 هم دقیقا به شکل بالا مورد استفاده قرار می‌گیرد.

۲۴. تابع LEFT/RIGHT

این توابع به اندازه مشخص شده از سمت چپ یا راست رشته کاراکتر برمی‌گردانند. سینتکس پایه برای استفاده از این توابع به صورت زیر است.

1LEFT(string, number_of_chars)

سینتکس تابع بعدی در زیر آورده شده است.

1RIGHT(string, number_of_chars)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس‌های بالا این توابع دارای ۲ پارامتر اصلی هستند.

1. string

   : رشته‌ای که کاراکترهای مورد نظر از آن استخراج می‌شوند. این پارامتر ضروری است.

2. number_of_chars

    : تعداد کاراکترهایی که باید استخراج شوند. اگر تعداد این کاراکترها از اندازه رشته مورد نظر بزرگتر بود، خود رشته برگشت داده می‌شود. این پارامتر ضروری است.

مثال

1SELECT RIGHT(CustomerName, 5) AS ExtractString
2FROM Customers;

تابع LEFT()

 هم دقیقا به شکل بالا مورد استفاده قرار می‌گیرد.

۲۵. تابع LTRIM/RTRIM

این توابع فضاهای خالی را از سمت چپ یا راست رشته داده شده حذف می‌کنند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

سینتکس تابع بعدی در زیر آورده شده است.

پارامترها و مقدار خروجی تابع

با توجه به سینتکس‌های بالا این توابع دارای یک پارامتر اصلی هستند.

1. string

   : رشته‌ای که فضای خالی باید از سمت چپ یا راست آن حذف شود. این پارامتر ضروری است.

مثال

1SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString;

تابع RTRIM()

 هم دقیقا به شکل بالا مورد استفاده قرار می‌گیرد.

۲۶. تابع CONCAT

دو یا چند رشته را به یکدیگر می‌چسباند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1CONCAT(expression1, expression2, expression3,...)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع می‌تواند دارای چند پارامتر اصلی باشد.

1. expression1

2. expression2

3. expression3

4. و غیره

وجود حداقل یک پارامتر ضروری است. اگر یکی از چندین پارامتر ارسالی به تابع برابر با مقدار NULL باشند، در خروجی مقدار NULL برگشت داده می‌شود.

مثال

1SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address
2FROM Customers;

توجه کنید که خروجی بالا در شکل ستونی به نام Address

 است که مقادیر آن رشته‌ای متشکل از مقادیر ستون‌های Address

و PostalCode

 و City

 از جدول Customers

 هستند.

۲۷. تابع SUBSTRING

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

1SUBSTRING(string, start, length)

یا به شکل زیر هم می‌توان این سینتکس را پیاده کرد.

1SUBSTRING(string FROM start FOR length)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس بالا این تابع دارای ۳ پارامتر اصلی است.

1. string

   : این پارامتر ضروری است. رشته‌ای را نشان می‌دهد که باید از درون آن زیر رشته را استخراج کنیم.

2. start

    : این پارامتر هم ضروری است. موقعیت شروع زیر رشته‌ای را نشان می‌دهد که باید استخراج شود. این پارامتر مقدار منفی هم می‌پذیرد که به معنای شمارش از انتهای رشته است.

3. length

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

   تا انتها برگشت داده می‌شود.

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

مثال

1SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString
2FROM Customers;

۲۸. تابع CAST/CONVERT

این توابع نتایج حاصل از عبارتی را از یک نوع داده به نوع داده دیگری تبدیل می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1CAST(expression AS datatype(length))

سینتکس دیگری نیز برای این تابع در ادامه آمده است.

1CONVERT(data_type(length), expression, style)

پارامترها و مقدار خروجی تابع

با توجه به سینتکس‌های بالا این توابع دارای ۳ پارامتر اصلی هستند.

1. expression

   : مقداری که باید تبدیل نوع داده شود. وجود این مقدار ضروری است.

2. datatype

    : نوع داده‌ای که مقدار expression

   باید به آن تبدیل شود. وجود این پارامتر هم ضروری است.

3. length

   : این پارامتر اختیاری است و بیشترین اندازه طول جواب را با نوع داده‌های «char» و «varchar» و «nchar» و «nvarchar» و «binary» و «varbinary» مشخص می‌کند.

• style

   : این پارامتر مربوط به تابع CONVERT()

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

مثال

در این بخش برای هر کدام از این توابع چند نوع کوئری مختلف را مثال زده‌ایم.

• مثال‌های زیر مربوط به تابع CONVERT()

  هستند.

1SELECT CONVERT(varchar, 25.65);

در کوئری زیر از نوع داده مربوط به تاریخ استفاده شده است.

1SELECT CONVERT(datetime, '2017-08-25');

در کوئری زیر از پارامتر اختیاری style

هم استفاده شده است.

1SELECT CONVERT(varchar, '2017-08-25', 101);

• مثال‌های زیر مربوط به تابع CAST()

   هستند. در مثال اول از نوع داده varchar

   استفاده کرده‌ایم.

1SELECT CAST(25.65 AS varchar);

و در کوئری زیر از نوع داده تاریخ استفاده کردیم.

1SELECT CAST('2017-08-25' AS datetime);

آشنایی و تسلط بر روی پایگاه های داده مختلف با فرادرس

پایگاه‌های داده متنوعی از زبان کوئری نویسی SQL استفاده می‌کنند. در همه این پایگاه‌های داده اکثر توابع SQL با سینتکس مشابه هم استفاده می‌شود. اما هر کدام بسته به هدفی که از تولیدشان وجود داشت برای کار در سناریو‌های مختلفی و با سخت‌افزارهای مختلفی تولید شده‌اند.

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

جمع بندی

تابع‌های تجمعی و اسکالر ابزار مهمی در توابع SQL هستند که به صورت انعطاف‌پذیری برای کار و استخراج داده‌ها از پایگاه داده به‌کار برده می‌شوند. سایر گروه‌ توابع درونی که بر روی نوع داده‌های خاصی کار می‌کنند – مانند تابع MONTH()

 - نیز می‌توانند مانند توابع اسکالر استفاده شوند. درک اینکه چگونه از توابع استفاده کنیم، به شکل زیادی توانایی و عملکرد کوئری‌های SQL نوشته شده را ارتقا می‌دهد.

در این مطلب از مجله فرادرس با چند نوع از توابع SQL آشنا شدیم و برای هر کدام کارکرد، پارامترها، سینتکس و مثال کدنویسی شده‌ای را مشاهده کردیم.