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

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

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

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

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

فیلم آموزش کار با پایگاه داده اس کیو ال سرور SQL Server در فرادرس

کلیک کنید

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

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

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

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

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

• فیلم آموزش 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)

فیلم آموزش پروژه محور اس کیو ال سرور – ساخت دیکشنری برای دیتابیس با SQL Server در فرادرس

کلیک کنید

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

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

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

توابع تجمعی اغلب برای محاسبه مجموعه خاصی از مقادیر به‌کار برده می‌شوند، که به عنوان جواب مقدار مجزایی را برمی‌گردانند. کاربران می‌توانند از طریق بند 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() کمترین مقدار داده موجود در ستون یا حاصل از عبارتی را محاسبه می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1MIN (expression)

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

با توجه به سینتکس بالا تابع 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() بیشترین مقدار داده موجود در ستون یا حاصل از عبارتی را محاسبه می‌کند. سینتکس پایه برای استفاده از این تابع به صورت زیر است.

1MAX(expression)

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

با توجه به سینتکس بالا تابع MAX() دارای فقط یک پارامتر اصلی است.

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

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

مثال

تابع MAX() را مانند تابع MIN() بر روی ستون‌های حاوی نوع داده رشته نیز می‌توان به‌کار برد. برای مثال، در کد زیر از تابع MAX() بر روی ستون LastName در جدول Employeeاستفاده می‌کنیم. این تابع مقادیر درون ستون را با ترتیب حروف الفبا مرتب کرده و آخرین داده را برمی‌گرداند.

1SELECT MAX(LastName) FROM Employee;

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

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

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

فیلم آموزش استفاده از جیسون JSON در اس کیو ال 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، را می‌توان برای انجام عملیات بیتی بر روی اعداد یا گروهی از رکورد‌های عددی به‌کار برد. این کار بسیار بهتر از به دست آوردن نتایج از طریق بیت‌های مجزا است.

فیلم آموزش پایگاه داده اس کیو لایت SQLite در پایتون در فرادرس

کلیک کنید

۱۴. تابع BITAND

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

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

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

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

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> [ , '<padside>' ] )

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

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

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> [ , '<padside>' ] )

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

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

1. expr1: عبارتی که باید ارزیابی شود. این عبارت باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.
2. expr2: عبارت دومی که باید ارزیابی شود. این عبارت هم باید از نوع داده‌های Integer یا Binary یا نوع داده‌ قابل تبدیل شدن به عدد صحیح باشد.
3. padside: وقتی دو مقدار باینری طول یکسان نداشته باشند این پارامتر تعیین می‌کند که به کدام سمت مقدار کوتاه‌تر پدینگ اضافه شود. یکی از دو حالت LEFT و RIGHT قابل تعیین هستند. در صورت تفاوت طول دو عدد این پارامتر ضروری می‌شود.

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

مثال

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

1EVALUATE { BITXOR(9, 10) }

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

۱۷. تابع BITNOT

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

1BITNOT( <expr> )

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

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

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 را از فرادرس مشاهده کنیم. برای راحتی بیشتر مخاطبان محترم، لینک مورد نظر را در ادامه قرار داده‌ایم.

فیلم آموزش کوئری‌ نویسی پیشرفته در 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 Server در فرادرس

کلیک کنید

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

۲۱. تابع UCASE/UPPER

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

1UCASE(text)

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

1UPPER(text)

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

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

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

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

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

مثال

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

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

۲۲. تابع LCASE/LOWER

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

1LCASE(text)

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

1LOWER(text)

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

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

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

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

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

مثال

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

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

۲۳. تابع LEN/LENGTH

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

1LENGTH(string))

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

1LEN(string)

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

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

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

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

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

1LTRIM(string)

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

1RTRIM(string)

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

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

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 با سینتکس مشابه هم استفاده می‌شود. اما هر کدام بسته به هدفی که از تولیدشان وجود داشت برای کار در سناریو‌های مختلفی و با سخت‌افزارهای مختلفی تولید شده‌اند.

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

• فیلم آموزش پایگاه داده MySQL در فرادرس
• فیلم آموزش مقدماتی مدیریت بانک اطلاعاتی با Oracle فرادرس
• فیلم آموزش رایگان مبانی و مقدمات پایگاه داده کلیک هاوس ClickHouse در فرادرس
• فیلم آموزش پایگاه داده مونگو دی بی MongoDB برای تحلیل، مدل سازی و پردازش داده ها در فرادرس
• فیلم آموزش مقدماتی PostgreSQL برای مدیریت پایگاه داده با فرادرس

جمع بندی

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

فیلم مجموعه آموزش پایگاه داده – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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