کوئری های SQL – معرفی ۷۰ کوئری پر کاربرد با نمونه و مثال

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

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

مهم ترین کوئری های SQL

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

1. «بازیابی داده‌ها» (Data Retrieval)
2. کوئری‌های «تجمعی و خلاصه‌سازی» (Aggregations and Summarizations)
3. «نما‌ها» (Views)
4. «کلید‌ها و محدودیت‌ها» (Keys and Constraints)
5. «مدیریت پایگاه داده» (Database Management)
6. «اندیس‌گذاری و بهینه‌سازی عملکرد» (Indexing and Performance Optimization)
7. «کوئری‌های تحلیلی» (Analytical Queries)
8. کوئری‌‌های «تطبیق رشته و الگو» (String and Pattern Matching)
9. «کوئری‌‌های شرطی و مدیریت داده‌های پوچ» (Null Handling and Conditional Queries)
10. «توابع پیشرفته» (Advanced Functions) در SQL
11. سایر کوئری های SQL

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

کوئری های SQL برای بازیابی داده ها

در این قسمت از مطلب، ۱۰ مورد از مهم‌ترین کوئری های SQL درباره بازیابی داده‌ها را معرفی کرده و مثال‌های مربوط به آن‌ها را نمایش داده‌ایم.

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

کلیک کنید

۱. فراخوانی جدول ها

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

با استفاده از دستور SELECT در SQL کاربران حتی می‌توانند، مشخص کنند که از جدول‌های موجود در پایگاه داده کدام ستون‌ها در خروجی نمایش داده شوند. دستور SELECT  برای انتخاب داده در جدول به کار برده می‌‌شود. داده‌های انتخاب شده در جدول نتایج - با نام «مجموعه نتایج» (Result Set) - نمایش داده می‌شوند. داده‌های خروجی در این جدول ذخیره می‌شوند.

در این مثال، فرض می‌کنیم که نام پایگاه داده My_Schema  است.

1SELECT * FROM My_Schema.Tables;

۲. انتخاب چند ستون از جدول

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

1SELECT Student_ID FROM STUDENT;

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

1SELECT * FROM STUDENT;

۳. استخراج داده‌‌ با رعایت محدودیت‌های مشخص شده

با استفاده از کوئری زیر، تمام ویژگی‌های مشخص شده را از جدول فراخوانی می‌کنیم. البته به شرطی که Employee ID =0000  باشد.

1SELECT EMP_ID, NAME FROM EMPLOYEE_TBL WHERE EMP_ID = '0000';

۴. استخراج داده‌ های ذخیره شده با استفاده از دستور ORDER BY

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

1SELECT EMP_ID, LAST_NAME FROM EMPLOYEE
2WHERE CITY = 'Seattle' ORDER BY EMP_ID;

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

چیدمان صعودی همیشه به عنوان گزینه پیش‌فرض دستور ORDER BY  تنظیم شده است. به عبارت دیگر اگر کاربران از کدهای asc  یا desc  بعد از نام ستون استفاده نکنند، نتایج تولید شده به صورت خودکار در حالت صعودی مرتب خواهند شد.

1SELECT EMP_ID, LAST_NAME FROM EMPLOYEE_TBL
2WHERE CITY = 'INDIANAPOLIS' ORDER BY EMP_ID asc;

۵. استخراج داده های مرتب شده با استفاده از دستور Group By

دستور Group By در SQL، تمام داده‌های حاصل از اجرای کوئری را با توجه به ویژگی مشخص شده، دسته‌بندی می‌کند.

مثال اول دستور Group By

کوئری پایین ستون‌های Name  و Age  را از جدول Patients  انتخاب می‌کند. سپس با توجه به مقادیر ستون Age  این داده‌ها را فیلتر می‌کند. با این کار فقط رکورد‌هایی باقی می‌مانند که مقدار Age  در آن‌ها بیش از 40  است. به طور خلاصه یعنی داده‌های بیماران بالای ۴۰ سال را از جدول استخراج می‌کنیم. بعد از اینکه داده‌های استخراج شده با توجه به مقادیر ستون Age  دسته‌بندی شدند، در نهایت مجموعه نتایج را بر اساس حروف الفبای نام بیماران در ستون Name  به شکل مرتب شده نمایش می‌دهیم.

قاعده کلی این است که دستور GROUP BY همیشه بعد از دستور Where در SQL و قبل از دستور ORDER BY  در عبارت SELECT  قرار بگیرد.

1SELECT Name, Age FROM Patients WHERE Age > 40
2GROUP BY Name, Age ORDER BY Name;

مثال دوم دستور Group By

در کوئری زیر، رکورد‌هایی را از جدول سفارشات «Orders» واکشی کرده‌ایم که قیمتی کمتر از 70  واحد دارند. این کوئری، رکورد‌های دارای قیمت یکسان را دسته‌بندی می‌کند. داده‌های خروجی را بر اساس قیمت مرتب‌سازی کرده و در نهایت هم ستونی به نام COUNT(price)  را به مجموعه داده خروجی اضافه می‌کند. این ستون نشان می‌دهد که چه تعداد رکورد با قیمت یکسان در پایگاه داده پیدا شده‌اند.

1SELECT COUNT(price), price FROM orders 
2WHERE price < 70 GROUP BY price ORDER BY price

توجه: همیشه باید از مجموعه‌های یکسانی از ستون‌ها در دستور‌های SELECT  و GROUP BY استفاده کنید. در غیر این صورت با پیغام خطا روبه‌رو می‌شوید.

۶. جست وجو در داخل جدول‌ های SQL با استفاده از وایلدکارت‌ها

از کاراکترهای «وایلدکارت» (Wildcard) - مانند کاراکتر %  - برای جست‌وجو به دنبال الگو‌های خاص در مقادیری با نوع رشته استفاده می‌شود. در جدول‌های بزرگی که از هزاران رکورد تشکیل شده‌اند با کمک این کاراکترها بسیار راحت‌تر می‌توان رشته‌های خاص را پیدا کرد. فرض کنیم هدف، پیدا کردن مشتریانی است که نام آن‌ها با «Herb» شروع می‌شود. برای مثال می‌توان به نام‌های «Herbert» و «Herbertson» اشاره کرد. با به‌کارگیری نماد وایلدکارت %  می‌توان به چنین نتایجی را بدست آورد.

کوئری که در پایین نوشته‌ایم، در جدول مشتریان «Customer» جست‌وجو کرده و تمام ردیف‌هایی را که ابتدای نام مشتری در ستون «Customer_name» با عبارت «Herb» شروع شده پیدا می‌کند.

1SELECT * From Customers WHERE Name LIKE 'Herb%'

۷. استخراج اطلاعات از بین محدوده‌ای از داده‌ها

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

1SELECT ID FROM Orders WHERE
2Date BETWEEN ‘01/12/2018’ AND ‘01/13/2018’

۸. پیدا کردن اشتراکات بین دو جدول مجزا

هدف اصلی استفاده از پایگاه‌های داده رابطه‌ای، پیدا کردن رکورد‌های مطابق با هم در جداول مختلف است. برای رسیدن به این هدف، دستور JOIN در SQL به کاربران این پایگاه داده کمک می‌کند. استفاده از این دستور فرایند حل کردن چنین مسائلی را ساده‌تر کرده است. در کوئری زیر، فهرستی از رکورد‌های مشترک بین جدول‌های مشتری‌ها «Customers» و سفارشات «Orders» را از پایگاه داده پیدا کرده و به بیرون برمی‌گردانیم.

1SELECT ID FROM Customers INNER
2JOIN Orders ON Customers.ID = Orders.ID

در کوئری بالا، باید به نکته‌ای درباره استفاده از دستور INNER JOIN در SQL اشاره کنیم. این دستور برای استخراج ردیف‌ها از جدول Customers به کار برده شده است. اما با این شرط که شماره ID  مربوط به آن مشتری به یکی از ردیف‌های موجود در جدول Orders هم اشاره کند. پس فقط ردیف‌هایی برگشت داده می‌شود که شماره ID  آن‌ها در هر دو جدول مشترک باشد. انواع مختلفی از دستور JOIN  وجود دارد. برای مثال می‌توان به «FULL» و «SELF» و «LEFT» اشاره کرد. در ادامه مطلب به بررسی کوئری های SQL پیشرفته‌تری نیز پرداخته‌ایم.

۹. ترکیب دو دستور SELECT مختلف با استفاده از دستور UNION

با استفاده از کلمه کلیدی UNION در SQL می‌‌توانیم مجموعه نتایج بدست آمده از کوئری های SQL مختلف را با همدیگر ترکیب کنیم. فرض کنیم که باید با فهرست آخرین سفارشات مشتری‌ها، جدول جدیدی ایجاد کنیم. برای انجام این کار، لازم است که ستون‌های Customer_name  و phone  را از جدول‌های Orders و Customers با هم ترکیب کنیم. با وجود این داده‌های ترکیب شده می‌توانیم به الگوی خرید مشتری‌ها پی‌ پرده و حتی در آینده پیشنهادات جدیدی به مشتری‌های دیگر ارائه کنیم. کوئری زیر روش ساده‌ای برای اجرای این دستور است.

1SELECT phone FROM Customers 
2UNION SELECT Customer_name FROM Orders

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

۱۰. تخصیص نام مستعار به برچسب‌‌های ستون‌ها

از تخصیص نام مستعار به ستون‌ها، برای افزایش خوانایی ستون‌ها در مجموعه نتایج نمایش داده شده استفاده می‌کنند. به‌هرحال، استفاده از نام‌های کوتاه‌ شده برای ستون‌ها - بخصوص نام‌های اختصاری - بعضی وقت‌ها می‌تواند وضوح مطلب را کاهش دهد. بخصوص وقتی که به صورت روزانه از پایگاه داده استفاده می‌کنیم. برای مثال، در جدول مربوط به سفارشات «Orders» ستون مربوط به کالاها شامل توضیحی از محصولات خریداری شده است. نام‌گذاری این ستون به چیز مشخص‌تری مانند «Product_Description» باعث شفاف‌تر شدن داده‌ها در مجموعه نتایج فراخوانی شده می‌شود.

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

1SELECT Item AS item_description FROM Orders

چگونه با کمک فرادرس SQL Server یاد بگیریم؟

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

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

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

کوئری های تجمعی و خلاصه سازی

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

۱۱. کار بر روی داده‌ ها با استفاده از دستور COUNT

این کوئری از طریق شماردن ID-های مشتریان، تعداد کل مشتری‌ها را حساب کرده و اعلام می‌کند. علاوه بر آن، با توجه به کشور «Country» مربوط به هر مشتری، نتایج بدست آمده را دسته‌بندی هم می‌کند. وقتی که از دستور COUNT در SQL همراه با دستور DISTINCT در SQL استفاده می‌کنیم، در قسمتی از کوئری هم برای بخش‌بندی داده‌ها می‌توانیم دستورات مناسب را بنویسیم. این عبارت بخشی از عبارت‌های تحلیلی در SQL است. اما عبارت‌هایی مانند دستورات مرتب‌سازی و «دستورات مربوط به پنجره‌بندی» (Windowing ‌Clause) در این بخش جای نمی‌گیرند.

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

1SELECT COUNT(colname) FROM table name;

کوئری مورد استفاده برای شمارش مشتری‌ها بر اساس کشور آن‌ها را در پایین نوشته‌ایم.

1SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

۱۲. کار بر روی داده‌‌ها با استفاده از دستور SUM

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

1SELECT SUM(Salary)FROM Employee WHERE Emp_Age < 30;

۱۳. کار بر روی داده‌‌ها با استفاده از دستور AVG

کاربرد دستور AVG  بسیار ساده‌ است. وظیفه اصلی این دستور، محاسبه مقدار میانگین داده‌های موجود در ستون مشخص شده است. این تابع هم یکی از توابع تجمعی در SQL است. تابع AVG()  مقدار میانگین مقادیر «غیرتهی» (Non-NULL) در ستون را محاسبه می‌کند. تمام مقادیر «NULL» توسط این تابع نادیده گرفته می‌شوند.

1SELECT AVG(Price)FROM Products;

۱۴. کار بر روی داده‌‌ها با استفاده از دستور MIN

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

برای مثال با کمک این تابع می‌توانیم حداقل حقوق دریافتی کارمندان را بدون تغییر دادن سایر اطلاعات آن‌ها بدست بیاوریم. در کوئری زیر، روش استفاده از تابع MIN  را در عبارت SELECT  نمایش داده‌ایم.

1SELECT eno,
2       empname,
3       dtno,
4       salary,
5       MIN(salary) OVER (PARTITION BY dtno) AS min_result
6FROM   employee;

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

     ENO   EMPNAME          DTNO     SALARY MIN_RESULT
---------- ---------- ---------- ---------- ---------------
      7782 CLARK              10       2450            1300
      7839 KING               10       5000            1300
      7934 MILLER             10       1300            1300
      7566 JONES              20       2975             800
      7902 FORD               20       3000             800
      7876 ADAMS              20       1100             800
      7369 SMITH              20        800             800
      7788 SCOTT              20       3000             800
      7521 WARD               30       1250             950
      7844 TURNER             30       1500             950
      7499 ALLEN              30       1600             950
      7900 JAMES              30        950             950
      7698 BLAKE              30       2850             950
      7654 MARTIN             30       1250             950

۱۵. کار بر روی داده‌‌ها با استفاده از دستور MAX

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

برای مثال با کمک این تابع بیشینه حقوق دریافتی کارمندان را بدون تغییر دادن سایر اطلاعات آن‌ها بدست می‌آوریم. در کوئری زیر، روش استفاده از تابع MAX  را در عبارت SELECT  نمایش داده‌ایم.

1SELECT eno,
2       empname,
3       dtno,
4       salary,
5       MAX(salary) OVER () AS max_result
6FROM   employee;

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

     ENO   EMPNAME          DTNO       SALARY    MAX_RESULT
---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800       3000
      7499 ALLEN              30       1600       3000
      7521 WARD               30       1250       3000
      7566 JONES              20       2975       3000
      7654 MARTIN             30       1250       3000
      7698 BLAKE              30       2850       3000
      7782 CLARK              10       2450       3000
      7788 SCOTT              20       3000       3000
      7839 KING               10       5000       3000
      7844 TURNER             30       1500       3000
      7876 ADAMS              20       1100       3000
      7900 JAMES              30        950       3000
      7902 FORD               20       3000       3000
      7934 MILLER             10       1300       3000

۱۶. کار بر روی داده‌‌ها با استفاده از دستور HAVING

مشکل اینجاست که دستور «WHERE» در SQL نمی‌تواند بر روی توابع تجمعی عملیات انجام دهد. برای حل کردن این مشکل از دستور HAVING در SQL استفاده می‌کنیم. برای مثال، کوئری زیر، فهرست مشتری‌ها را بر اساس ناحیه استخراج می‌کند. ناحیه‌های معتبر از نظر این کوئری، مناطقی هستند که حداقل یک مشتری در آن‌ها سکونت داشته باشد.

1SELECT COUNT(ID), Region
2FROM Customers
3GROUP BY Region
4HAVING COUNT(ID) > 0;

کوئری های SQL برای استفاده در View ها

در این بخش از مطلب، ۵ نوع کوئری مختلف را درباره استفاده از View در SQL معرفی کرده و مثال‌های مربوط به آن‌ها را نمایش داده‌ایم.

فیلم آموزش کتابخانه pyodbc در پایتون برای اتصال به پایگاه داده + گواهینامه در فرادرس

کلیک کنید

۱۷. کوئری SQL برای فهرست کردن تمام View ها

با استفاده از کوئری زیر می‌توانیم تمام «View»-های موجود در اسکیما یا شِمای پایگاه داده را بدست آورد.

1SELECT * FROM My_Schema.views;

۱۸. کوئری SQL برای ساخت View

VIEW به جدول سفارشی گفته می‌شود که بر اثر اجرای کوئری‌ها ایجاد شده است. View-ها مانند تمام جدول‌های دیگر شامل ستون و ردیف هستند.

استفاده از VIEW در کوئری‌ روش بسیار خوبی است. زیرا بعدا به سادگی می‌توانیم نتایج بدست آمده را فراخوانی کنیم. بدون اینکه مجبور باشیم با هدف بدست آوردن مجموعه نتایج یکسان، هربار محاسبات تکراری را اجرا کنیم.

1CREATE VIEW Failing_Students AS
2SELECT S_NAME, Student_ID
3FROM STUDENT
4WHERE GPA > 40;

۱۹. کوئری SQL برای فراخوانی View

در کوئری زیر، می‌بینیم که سینتکس استاندارد برای انتخاب کردن ویژگی‌های جدول‌ را می‌توان بر روی VIEW نیز اعمال کرد.

1SELECT * FROM Failing_Students;

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

۲۰. کوئری SQL برای برای به روزرسانی View

کوئری پایین برای به‌روزرساندن VIEW با نام Product List  نوشته شده است. اما اگر این VIEW از قبل وجود نداشته باشد، با همین شکل که در کوئری تعریف شده ایجاد خواهد شد. به VIEW-ها جدول مجازی هم گفته می‌‌شود. به عبارت دیگر، VIEW نسخه کپی شده‌ای از جدول‌ها است. داده‌های VIEW توسط کوئری ذخیره شده‌ تولید می‌شوند.

VIEW کپی معتبری از داده‌های جدول یا مجموعه‌ای از جدول‌های دیگر است. VIEW-ها داده‌های خود را از جدول‌های موجود می‌گیرند. به این جدول‌ها جدول پایه گفته می‌شود. جدول‌های پایه، جدول‌های واقعی هستند. تمام عملیاتی که بر روی VIEW اتفاق می‌افتند، بر روی جدول مرتبط با آن‌ هم اثر می‌گذارند. کاربران می‌توانند از VIEW-ها دقیقا مانند جدول‌های پایه استفاده کنند. بنابراین انواع دستور‌های «DDL» و «DML» را می‌توان بر روی VIEW-ها اعمال کرد. برای مثال می‌توان VIEW-ها را به‌روزرسانی کرد. به آن‌ها داده اضافه کرد و حتی از آن‌ها داده حذف کرد. تمام دستورات update  و insert  و delete  در View-ها قابل اجرا هستند.

1CREATE OR REPLACE VIEW [ Product List] AS
2SELECT ProductID, ProductName, Category
3FROM Products
4WHERE Discontinued = No;

۲۱. کوئری SQL برای حذف View با دستور DROP

کوئری پایین وظیفه حذف کردن VIEW با نام V1  را دارد. نکته مهم درباره این دستور آن است که در صورت وجود VIEW-های وابسته به V1  دیگر امکان استفاده از دستور DROP VIEW  را نداریم. یعنی فقط VIEW-هایی را می‌توانیم حذف کنیم که هیچ VIEW دیگری به آن وابسته نباشد.

1DROP VIEW V1;

کلید‌ها و محدودیت ها

در این بخش از مطلب، ۴ مورد از مهم‌ترین کوئری های SQL را درباره کلید‌ها و محدودیت‌ها معرفی کرده و مثال‌های مربوط به آن‌ها را نمایش داده‌ایم.

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

جدول‌های تعریف شده توسط کاربر، اطلاعات خاصی را به شکل جدولی نمایش می‌دهند. از این جدول‌ها می‌توان به عنوان داده ورودی برای رویه‌ها یا توابع تعریف شده توسط کاربر هم استفاده کرد. از آن‌جا که استفاده از این جدول‌ها فایده‌های زیادی دارد، بهتر است که با به‌کارگیری کوئری زیر آن‌ها را مدیریت کنیم. «جدول‌های - تعریف شده توسط - کاربر» (User Tables) توصیفی هستند از جدول‌های رابطه‌ای متعلق به کاربر فعلی.

1SELECT * FROM Sys.objects WHERE Type='u'

۲۳. کوئری برای نمایش کلید‌های اصلی

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

«کلید‌ اصلی» (Primary Key) دو قاعده NOT NULL  و «محدودیت یکتا بودن» (Unique Constraint) را در کنار هم و در یک تعریف، ترکیب می‌کند. این مسئله به معنای آن است که هیچ دو ردیف مجزایی نمی‌توانند کلید اصلی مشترک داشته باشند. همچنین مقدار کلید اصلی در هیچ ردیفی از جدول نمی‌تواند تهی یا NULL  شود. کلید اصلی مسئول برقراری و مدیریت همه رابطه‌های بین جدول‌های مختلف است.

کوئری SQL پایین، تمام فیلد‌های کلید اصلی را در جدول، فهرست می‌کند.

1SELECT * from Sys.Objects WHERE Type='PK'

۲۴. کوئری برای نمایش کلید‌های یکتا

«کلید‌ منحصر‌به‌فرد» (Unique Key) یا یکتا تضمین می‌کند که تمام مقادیر قرار گرفته در ستون مشخصی به صورت یکتا و غیرتکراری باشند. با استفاده از این کلید‌ها تمام ردیف‌های درون جدول غیرتکراری می‌شوند. هر جدولی می‌تواند چندین کلید یکتای مختلف داشته باشد. ستون‌های کلید یکتا فقط می‌توانند شامل یک مقدار NULL  شوند. حتی وجود NULL  تکراری هم پذیرفته شده نیست.

1SELECT * FROM Sys.Objects WHERE Type='uq'

۲۵. کوئری برای نمایش کلیدهای خارجی

«کلید‌های خارجی» (Foreign Key) با هدف متصل کردن جدول‌ها به یکدیگر به‌ کار برده می‌‌شوند. کلید‌های خارجی به ویژگی‌هایی از جدول گفته می‌شود که با کلید‌های اصلی جدول دیگری یکسان باشند.

1SELECT * FROM Sys.Objects WHERE Type='f'

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

کوئری های SQL برای مدیریت پایگاه داده

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

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

کلیک کنید

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

۲۶. نمایش تریگرها

Trigger در SQL نوعی «شنونده رویداد» (Event Listener) است. یعنی اینکه تریگر مجموعه‌ای از دستور‌العمل‌های از پیش تعریف شده است که در زمان رویدادن اتفاقات مشخصی به صورت خودکار اجرا می‌شوند. با استفاده از کوئری زیر می‌توانیم لیستی از تریگر‌های تعریف شده را مشاهده کنیم.

1SELECT * FROM Sys.Objects WHERE Type='tr'

۲۷. نمایش جدول‌های داخلی

«جدول‌های داخلی» (Internal Tables) به عنوان نتیجه فعالیت‌های کاربران و به صورت خودکار ایجاد می‌شوند. معمولا نمی‌توان به این جدول‌ها به صورت مستقیم دسترسی داشت.

داده‌های درون جدول‌های داخلی را نمی‌توان دستکاری کرد. هر چند با استفاده از کوئری‌های مشخصی می‌توان «فراداده‌های» (Metadata) این جدول‌ها را مشاهده کرد.

1SELECT * FROM Sys.Objects WHERE Type='it'

۲۸. نمایش فهرستی از رویه‌ها

«رویه‌‌های ذخیره شده» (Stored Procedure) مجموعه‌ای از کوئری‌های پیشرفته SQL هستند. این مجموعه کوئری‌ها از جهت منطقی، موجودیت یکسانی را تشکیل می‌دهند و وظیفه مشخصی را اجرا می‌کنند. بنابراین با استفاده از کوئری زیر می‌توانیم رویه‌های ذخیره شده را مشاهده کنیم.

1SELECT * FROM Sys.Objects WHERE Type='p'

۲۹. اضافه کردن جدول به پایگاه داده جدید

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

1CREATE TABLE Customers (
2ID varchar(80),
3Name varchar(80),
4Phone varchar(20),
5....
6);

البته بیشتر جدول‌های پایگاه‌های داده با استفاده از رابط‌های کاربری مانند «Access» یا «OpenOffice» ایجاد می‌شوند. اما لازم است که روش حذف و اضافه جدول‌‌ها و پایگاه‌های داده را با دستور‌های کدنویسی نیز بلد باشیم. به این منظور باید با عبارت‌های SQL مربوط به این عملیات آشنا شویم. به طور خاص در زمان راه‌اندازی وب‌اپلکیشن‌های جدید، داشتن این مهارت ارزش خود را نشان می‌دهد. معمولا در طی این فرایند‌ها رابط‌های کاربری از کابران می‌خواهند که با هدف راه‌اندازی پایگاه داده، نام‌های متنوعی را به جدول‌ها و پایگاه داده اختصاص دهند.

۳۰. اعمال تغییرات و حذف جدول‌‌ها

عبارت ALTER  به منظور تغییر دادن یا به‌روزرسانی ساختار جدول‌ها به کار برده می‌شود. عبارت ALTER در SQL جدول‌های رابطه‌ای را با استفاده از قوانین جدید یا به‌روزرسانی شده‌ تغییر می‌دهد. ALTER  بخشی از دستورات «زبان تعریف داده» (Data Definition Language | DDL) است. دستورات DDL مشخص می‌کنند که ساختار داده‌ها چگونه نمایش داده شده یا سازماندهی شوند.

اعمال تغییرات در جدول

فرض کنیم که به منظور قدردانی از خرید خوب مشتریان می‌خواهیم که کارت تبریک تولدی برای آن‌ها ارسال کنیم. بنابراین لازم است که ستونی برای ذخیره تاریخ تولد با نام Birthday  به جدول مشتری‌ها «Customers» اضافه شود. در مثال‌های پایین روش بسیار ساده اعمال تغییرات در جدول‌های موجود را نشان داده‌ایم. برای انجام این کار از عبارت ALTER  استفاده کرد‌یم.

1ALTER TABLE Customers ADD Birthday varchar(80)

حذف جدول‌

اگر جدولی به دلیل وجود داده‌های نامناسب خراب‌ شد، می‌توانیم بلافاصله با کمک کوئری زیر آن را حذف کنیم.

1DROP TABLE table_name

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

در این مثال و چند مثال دیگر از مطلب، از پایگاه‌های داده رایج در شرکت‌ها استفاده می‌کنیم. این پایگاه‌های داده معمولا چندین جدول مختلف دارند که به سادگی هم قابل درک هستند. پایگاه داده مورد استفاده در این مثال‌ها شامل جدولی برای نگهداری اطلاعات مشتریان به نام Customer و جدولی برای نگهداری سفارشات مشتری‌ها به نام Order  است. جدول مشتری‌ها شامل ستون‌هایی مانند ID  و Name  و Address  و Zip  و Email  است. در این مسئله فرض می‌کنیم که فیلد Customer_ID  کلید اصلی است. ستون کلید اصلی برای اندیس‌گذاری استفاده می‌شود.

با توجه به جدول Customer می‌‌توان در نظر گرفت که جدول Order  باید شامل ستونی به نام Customer_ID  باشد. با استفاده از این ستون به تمام جزئیات مربوط به ارتباط بین سفارشات و مشتری‌ها دسترسی داریم. این جدول‌ هم شامل ستون‌هایی مانند Number  و Quantity  و Date  و Item  و Price  است. حالا فرض کنیم که تمام شماره‌ تلفن‌های مشتری‌ها با کد‌های Zip  مربوط به آن‌ها جابه‌جا شده‌اند. یعنی اینکه به صورت اشتباهی تمام شماره‌های تلفن‌ در ستون Zip  نوشته شده‌اند.

برای حل این مشکل فقط کافیست که از عبارت SQL زیر استفاده کنیم.

1UPDATE Customers SET Zip=Phone, Phone=Zip

۳۲. مدیریت پایگاه داده

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

در کوئری زیر، می‌خواهیم پایگاه داده جدیدی ایجاد کنیم که قرار است شامل جدول‌های Customers و Orders باشد. در بخش‌های قبلی مثال‌های زیادی را با کمک این جدول‌ها نمایش داده‌ایم.

1CREATE DATABASE AllSales

اندیس گذاری و بهینه سازی عملکرد

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

۳۳. روش اندیس‌گذاری موفق

اندیس‌ها بخشی از ساختار پایگاه داده هستند. این ساختار به ازای هر عنصر در جدول مقداری را نگهداری می‌کند. برای نمایش این ساختار از ستون اندیس‌گذاری شده در جدول یا خوشه‌ها استفاده می‌شود. با کمک اندیس‌ها می‌توان به سرعت ردیف مورد نظر را پیدا کرد. اندیس‌های متنوعی در SQL وجود دارند. برای مثال می‌توان به اندیس‌های «Bitmap» و «اندیس‌های بخش‌بندی شده» (Partitioned Indexes)، اندیس‌های «Function-Based» و «اندیس‌های دامنه» (Domain Indexes) اشاره کرد.

برای اندیس‌گذاری دقیق لازم است که ستون مربوط به کلید اصلی فقط شامل مقادیر یکتا باشد. این مسئله تضمین می‌کند که عبارت‌های JOIN  از یکپارچگی پایگاه داده محافظت کرده و بعد از اجرا نتایج معتبری تولید کنند. در کوئری زیر، جدول مربوط به مشتریان Customers را ایجاد کرده‌ایم. در این جدول ستون مربوط به ID  به عنوان کلید اصلی ثبت شده است.

1CREATE TABLE Customers (
2ID int NOT NULL,
3Name varchar(80) NOT NULL,
4PRIMARY KEY (ID)
5);

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

1ID int NOT NULL AUTO_INCREMENT

۳۴. مطالب پیشرفته برای ارتقای عملکرد

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

1SELECT * FROM Customers

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

1SELECT Name, Birthday, Phone, 
2Address, Zip FROM Customers

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

• از SP_  به عنوان پیشوند نام رویه‌های ذخیره شده استفاده نکنید. با این کار مانع از آن می‌شوید که پایگاه داده «Master» هربار توسط SQL Server بررسی شود.
• هربار که دستوراتی مانند INSERT  و DELETE  و غیره اجرا می‌شوند، SQL Server یکبار ردیف‌ها را می‌‌شمارد. با تنظیم کردن NOCOUNT ON  می‌توانید زمان مورد نیاز برای شمارش ردیف‌ها را کاهش دهید.
• اجرای دستور INNER JOIN  همراه با شرط، نسبت به اجرای دستور WHERE  همراه با شروط مختلف، سرعت بسیار بیشتری دارد.

توسعه‌دهندگان به منظور رسیدن به بهره‌وری بیشتر باید کوئری‌های پیشرفته SQL را بیاموزند. رعایت این نکات در پیاده‌سازی پروژه‌های مربوط به دنیای واقعی ضروری است. در این مطلب هم بیشتر تلاش کرده‌ایم تا از INNER JOIN  استفاده کنیم.

کوئری های تحلیلی

در این بخش از مطلب، ۸ مورد از مهم‌ترین دستور‌ها را درباره کوئری‌های تحلیلی معرفی کرده و مثال‌های مربوط به آن‌ها را هم نمایش داده‌ایم.

فیلم آموزش SQLServer – راه اندازی Always On با Listener در اس کیو ال سرور در فرادرس

کلیک کنید

۳۵. تابع تحلیلی DENSE_RANK

تابع DENSE_RANK()  کوئری تحلیلی است که رتبه هر ردیف را در مجموعه‌ای از ردیف‌های مرتب شده مشخص می‌کند. رتبه خروجی عدد صحیحی است که از ۱ شروع می‌‌شود. DENSE_RANK()  یکی از مهم‌ترین کوئری های SQL است. این تابع مقادیر مربوط به رتبه‌بندی را مانند اعداد پشت سر هم تولید می‌کند. در بین این اعداد هیچ فضای خالی وجود ندارد. حتی با وجود درگیر بودن ردیف‌ها در روابط خارجی باز هم رتبه‌بندی به شکل صحیح انجام می‌شود.

برای مثال، با استفاده از کوئری زیر، کارمندان را به شکل منظم رتبه‌بندی کردیم.

1SELECT eno,
2dno,
3salary,
4DENSE_RANK() OVER (PARTITION BY dno ORDER BY salary) AS ranking
5FROM employee;

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

ENO  DNO SALARY RANKING
---------- ---------- ---------- ----------
7933  10 1500   1
7788  10 2650   2
7831  10 6000   3
7362  20 900    1
7870  20 1200   2
7564  20 2575   3
7784  20 4000   4
7903  20 4000   4
7901  30 550    1
7655  30 1450   2
7522  30 1450   2
7844  30 1700   3
7493  30 1500   4
7698  30 2850   5

۳۶. دستورات مخصوص بخش‌بندی داده‌ها

با کمک دستور‌های مخصوص «بخش‌بندی داده‌ها» مجموعه نتایج خروجی به دسته‌ها یا مجموعه‌های کوچکتری از داده تقسیم می‌شوند. کوئری‌های تحلیلی فقط درون این بخش‌ها کار می‌کنند. این رفتار، شبیه به رفتار دستور GROUP BY در SQL در مقابل توابع تجمعی است. اگر از دستور‌های مخصوص «بخش‌بندی داده‌ها» استفاده نکنیم، کل مجموعه نتایج نهایی به عنوان یک بخش مجزا و مستقل در نظر گرفته می‌‌شود.

کوئری پایین، دستور OVER  را به کار برده است. بنابراین مقدار میانگین بر اساس تمام رکورد‌های موجود در مجموعه نتایج نهایی محاسبه شده است.

1SELECT eno, dno, salary,
2AVG(salary) OVER () AS avg_sal
3FROM employee;

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

EO  DNO SALARY AVG_SAL
---------- ---------- ---------- ----------
7364 20 900 2173.21428
7494 30 1700 2173.21428
7522 30 1350 2173.21428
7567 20 3075 2173.21428
7652 30 1350 2173.21428
7699 30 2950 2173.21428
7783 10 2550 2173.21428
7789 20 3100 2173.21428
7838 10 5100 2173.21428
7845 30 1600 2173.21428
7877 20 1200 2173.21428
7901 30 1050 2173.21428
7903 20 3100 2173.21428
7935 10 1400 2173.21428

۳۷. تابع FIRST_VALUE

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

اکنون می‌خواهیم روش کار تابع FIRST_VALUE  را بررسی کنیم. ابتدا باید سینتکس مورد استفاده برای به‌کارگیری این تابع را نمایش دهیم.

1FIRST_VALUE 
2  { (expr) [NULLS ]
3  | (expr [NULLS ])
4  }
5  OVER (analytic clause)

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

1SELECT eno,
2       dno,
3       salary,
4       FIRST_VALUE(salary) IGNORE NULLS 
5         OVER (PARTITION BY dno ORDER BY salary) AS lowest_salary_in_dept
6FROM   employee;

کوئری نوشته شده در بالا، مقادیر NULL  را نادیده می‌گیرد.

۳۸. تابع LAST_VALUE

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

1LAST_VALUE
2  { (expr) [ { NULLS ]
3  | (expr [ NULLS ])
4  OVER (analytic clause)

کوئری تحلیلی LAST_VALUE  با تابع تحلیلی LAST  ارتباط دارد. این تابع به کاربران کمک می‌کند تا آخرین خروجی را از میان داده‌های ستون‌ مرتب‌سازی شده پیدا کنند.

1SELECT eno,
2       dno,
3       salary,
4       LAST_VALUE(salary) IGNORE NULLS
5         OVER (PARTITION BY dno ORDER BY salary) AS highest_salary_in_dept
6FROM   employee;

۳۹. تابع PERCENT_RANK

PERCENT_RANK هم یکی دیگر از توابع SQL است که برای استفاده‌های تحلیلی به کار برده می‌شود. برای استفاده از این تابع لازم است که دستور ORDER BY  را هم به کار ببریم. در عبارت OVER  هیچ بخشی برای دسته‌بندی داده‌های خروجی وجود ندارد. بنابراین، کل مجموعه نتایج خروجی به عنوان یک دسته در نظر گرفته می‌‌شوند. به اولین ردیف رتبه ۰ اختصاص داده می‌شود و به آخرین ردیف هم رتبه ۱.

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

1SELECT
2     prdid, SUM(amount),
3     PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank
4     FROM sales
5     GROUP BY prdid
6     ORDER BY prdid;

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

PRDID        SUM(AMOUNT)  PERCENT_RANK
----------- ----------- ------------
          1    22623.5            0
          2   223927.08           1

۴۰. کوئری تحلیلی NTILE

تابع NTILE  به کاربران برای تقسیم کردن مجموعه‌ای از ردیف‌ها به تعداد گروه‌های مشخص شده یا «ظرف‌ها» (Containers) کمک می‌کند. اگر تعداد ردیف‌ها از تعداد ظرف‌ کمتر باشد، تعداد ظرف‌ به اندازه ردیف‌ها کاهش داده می‌شود. یعنی اینکه در بیشترین حالت، به ازای هر ردیف یک‌ ظرف می‌تواند وجود داشته باشد.

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

1NTILE(exp) OVER ([ partition_clause ] order_by)

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

1SELECT empid,
2       name,
3       dno,
4       salary,
5       NTILE(6) OVER (ORDER BY salary) AS container_no
6FROM   employee;

۴۱. توابع تحلیلی VARIANCE و VAR_POP و VAR_SAMP

توابع تحلیلی VARIANCE  و VAR_POP  و VAR_SAMP  از نوع توابع تجمعی هستند. این توابع برای محاسبه انواع پراکندگی یا واریانس در داده‌ها به کار برده می‌شوند.

• تابع VARIANCE  مقدار پراکندگی را محاسبه می‌کند.
• تابع VAR_POP  مقدار «پراکندگی جمعیتی» (Population Variance) را محاسبه می‌کند.
• تابع VAR_SAMP  هم مقدار «پراکندگی نمونه‌ها» (Sample Variance) را محاسبه می‌کند.

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

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

1SELECT VARIANCE(salary) AS var_salary,
2       VAR_POP(salary) AS pop_salary,
3       VAR_SAMP(salary) AS samp_salary
4FROM   employee;

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

VAR_SALARY   POP_SALARY   SAMP_SALARY
------------ ----------- ------------
1479414.97  1588574.81   1388717.27

۴۲. توابع تحلیلی STDDEV و STDDEV_POP و STDDEV_SAMP

توابع تحلیلی STDDEV  و STDDEV_POP  و STDDEV_SAMP  برای محاسبه انواع مختلف «انحراف معیار» (Standard Deviation) در گروهی از داده‌ها به کار برده می‌شوند.

• تابع STDDEV  میزان انحراف معیار را محاسبه می‌کند.
• تابع STDDEV_POP  مقدار انحراف معیار جامعه را محاسبه می‌کند.
• تابع STDDEV_SAMP  مقدار انحراف معیار نمونه تراکمی را محاسبه می‌کند.

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

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

1SELECT STDDEV(salary) AS stddev_salary,
2       STDDEV_POP(salary) AS pop_salary,
3       STDDEV_SAMP(salary) AS samp_salary
4FROM   employee;

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

STDDEV_SALARY POP_SALARY SAMP_SALARY
---------- -------------- ---------------
1193.50     1159.588      1193.603

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

کوئری های SQL برای تطبیق رشته و الگو

در این بخش از مطلب، ۴ مورد از مهم‌ترین کوئری های SQL را برای تطبیق رشته و الگو معرفی کرده و مثال‌های مربوط به آن‌ها را نمایش داده‌ایم.

۴۳. کار بر روی داده‌‌هایی با نوع رشته

در این قسمت، نگاهی داریم به استفاده از توابع برای پردازش محتوای موجود در ستون‌ها. SUBSTRING احتمالا با ارزش‌ترین تابع برای کار با رشته‌ها در میان تمام توابع درونی SQL است. این تابع به توسعه‌دهندگان برای استفاده از قدرت Regex-ها کمک می‌کند. اما کار کردن با آن به اندازه Regex‌-ها پیچیده نیست. فرض کنیم باید بخش‌های رشته‌ای سمت چپ دومین نماد نقطه «.» را در آدرس‌های وب پیدا کنیم.

در کوئری زیر، روش انجام این کار را در SQL نمایش داده‌ایم.

1SELECT SUBSTRING_INDEX("www.Faradars.org", ".", 2);

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

www.Faradars 

۴۴. تطبیق الگو

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

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

1MEASURES  STRT.tstamp AS initial_tstamp,
2          LAST(UP.tstamp) AS first_tstamp,
3          LAST(DOWN.tstamp) AS finished_tstamp

۴۵. تابع SOUNDEX

تابع SOUNDEX  رشته‌ای را در خروجی برمی‌گرداند. این رشته از کاراکترها نحوه تلفظ رشته اصلی را مشخص می‌کنند. منظور از رشته اصلی، همان رشته‌ای است که تابع SOUNDEX  در ورودی دریافت کرده. بعضی از کلمات در زبان انگلیسی به شکل‌های مختلفی هجی می‌شوند، اما در نهایت صدای یکسانی دارند. با کمک این تابع می‌توانیم آن کلمات را با همدیگر تطبیق دهیم. این تابع از «CLOB» پشتیبانی نمی‌کند.

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

1SELECT last_name, first_name
2FROM hr.emp
3WHERE SOUNDEX(last_name)
4= SOUNDEX('SCOTTY');

۴۶. تابع UNISTR

تابع UNISTR  به عنوان ورودی عبارتی را می‌پذیرد که از داده‌های کاراکتری تشکیل شده است. سپس آن را به مجموعه‌ای از کاراکترهای معمولی تبدیل می‌کند. این تابع از حروف الفبا تعریف شده در رشته‌های Unicode پشتیبانی می‌کند. در نتیجه کاربران می‌توانند مقادیری از جنس Unicode تعریف کنند. کوئری که در پایین آمده روش استفاده از این تابع را نشان می‌دهد.

1SELECT UNISTR('pqr\00e4\00f3\00f9') FROM DUAL;

کوئری‌ های شرطی و مدیریت داده های پوچ

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

فیلم آموزش استفاده از جیسون JSON در اس کیو ال SQL + گواهینامه در فرادرس

کلیک کنید

۴۷. استخراج نتایج NULL

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

در مواردی که وجود مقدار NULL  در ستون‌ها مجاز شمرده شده‌ است، نتیجه انجام محاسبات بر روی آن مقدارها هم برابر با NULL  می‌شود. برای روبه‌رو نشدن با این وضعیت می‌توانیم از عملگر IFNULL استفاده کنیم. در مثال بعدی، هرجا که محاسبات با فیلد‌های شامل مقدار NULL  روبه‌رو شوند، به‌جای NULL  مقدار صفر «0» برگردانده می‌شود.

1SELECT Item, Price * 
2(QtyInStock + IFNULL(QtyOnOrder, 0)) 
3FROM Orders

۴۸. استفاده از COALESCE برای برگرداندن اولین مقدار غیر NULL

کوئری های SQL با استفاده از تابع COALESCE  مقادیر NULL  درون پایگاه داده را مدیریت می‌کنند. در این روش، مقدارهای NULL  با مقدارهای تعریف شده توسط کاربر جایگزین می‌شوند. تابع COALESCE  در SQL تمام پارامترها را به صورت یک به یک بررسی می‌کند. در طی این بررسی اولین مقدار غیر NULL  را که پیدا کرده به بیرون برمی‌گرداند.

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

1SELECT COALESCE(NULL,NULL,'Mostafa',NULL,'Byte')

بعد از اجرای کوئری بالا، عبارت Mostafa به عنوان نتیجه تولید شده و به خروجی برگشت داده می‌شود.

۴۹. نتایج حاصل از اجرای زیرکوئری‌های شرطی

عملگر EXISTS  در SQL وجود داشتن رکورد‌ها را درون زیر‌کوئری بررسی می‌کند. اگر رکوردی در زیرکوئری پیدا شد، در پاسخ مقدار «TRUE» برمی‌گرداند.

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

1SELECT Name FROM Customers WHERE EXISTS 
2(SELECT Item FROM Orders 
3WHERE Customers.ID = Orders.ID AND Price < 50)

در کوئری بالا، اگر سفارشات مشتری‌ها کمتر از ۵۰ واحد پولی باشد، دستور SELECT  مقدار «TRUE» را به عنوان نتیجه برمی‌گرداند.

آموزش انواع پایگاه داده از آشنایی تا تسلط

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

• فیلم آموزش مقدماتی اوراکل اپکس درباره تولید و توسعه نرم افزارهای تحت وب با Oracle APEX در فرادرس
• فیلم آموزش فرایند ETL و ساخت انبار داده با ابزار SSIS در SQL با فرادرس
• فیلم آموزش رایگان پایگاه داده «کلیک هاوس» (ClickHouse) مربوط به مبانی و مقدمات با فرادرس
• فیلم آموزش مقدماتی PostgreSQL برای مدیریت پایگاه داده در فرادرس
• فیلم آموزش پایگاه داده مونگو دی بی MongoDB برای تحلیل، مدل سازی و پردازش داده ها با فرادرس

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

توابع پیشرفته در کوئری های SQL

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

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

۵۰. استفاده از تابع CONVERT برای تبدیل نوع‌‌

این تابع برای تبدیل کردن مقادیر به نوع‌های تعریف شده به‌ کار برده می‌شود. برای مثال،‌ اگر بخواهید که مقدار مشخصی را به نوع «Int» تبدیل کنید، تابع CONVERT  کاربرد خود را نشان می‌دهد. در کادر زیر نمونه ساده‌ای از استفاده تابع CONVERT  را نمایش داده‌ایم.

1SELECT CONVERT(int, 27.64)

بعد از اجرای کوئری بالا در خروجی عدد 27  به کاربر نمایش داده می‌شود.

۵۱. تابع WIDTH_BUCKET در کوئری‌های SQL

این تابع با هدف کشف شماره گروه مرتبط با پارامتر دریافت شده به کار می‌رود. تابع WIDTH_BUCKET  نتیجه عبارت دریافت شده را بعد از ارزیابی آن برمی‌گرداند. در کوئری زیر، روش استفاده از این تابع را نمایش داده‌ایم.

1SELECT emp_id, first_name,last_name,dept_id,mgr_id,
2WIDTH_BUCKET(department_id,20,40,10) "Exists in Dept"
3FROM emp
4WHERE mgr_id < 300
5ORDER BY "Exists in Dept";

۵۲. تابع COSH در کوئری‌های SQL

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

1SELECT COSH(0) "COSH of 0" FROM DUAL;

۵۳. تابع TZ_OFFSET در کوئری‌های SQL

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

1SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

۵۴. تابع CARDINALITY در کوئری‌های SQL

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

1SELECT product_id, CARDINALITY(ad_mydocs_get)
2FROM my_media_table;

۵۵. تابع DUMP در کوئری‌های SQL

در زمان نوشتن کوئری های SQL بسیار پیش می‌آید که با داده‌های نوع رشته کار کنیم. تابع DUMP  یکی از مهم‌ترین توابع برای اجرای عملیات بر روی کاراکترها و رشته‌ها است. این تابع برای برگرداندن مقادیری با نوع «VARCHAR2» استفاده می‌شود. مقدار برگشت داده شده، درباره نوع داده‌ای ارسال شده به تابع به عنوان پارامتر، اطلاعات می‌دهد.

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

1SELECT DUMP('pqr', 1033)
2FROM DUAL;

۵۶. تابع PATH در کوئری‌های SQL

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

1SELECT ANY_PATH FROM RESOURCE_VIEW
2WHERE EQUALS_PATH(res, '/sys/schemas/OE/www.pqr.com')=3;

۵۷. تابع CLUSTER_SET در کوئری‌های SQL

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

• روش اول:‌ برای کسب اطلاعات می‌تواند از «شیء کاوشی» (Mining Type Object) خاصی استفاده کند.
•  روش دوم: عبارت‌های تحلیلی وجود دارند که برای بررسی داده‌ها الگوهای کاوشی مشخصی را ایجاد کرده یا مورد استفاده قرار می‌دهند. در روش دوم تابع CLUSTER_SET  با استفاده از این الگو‌ها به کاوش در میان داده‌ها می‌پردازد.

در مثال پایین، تنظیماتی وجود دارند که بیشترین تاثیر را بر روی توزیع خوشه‌ها می‌گذارند. در این مثال تمام این تنظیمات را به ازای کلاینت با شماره ID 1000  فهرست کرده‌ایم. این کوئری از توابع CLUSTER_DETAILS  و CLUSTER_SET  استفاده می‌کند. این توابع هم از مدل مخصوص خوشه‌بندی با نام my_sample  استفاده می‌کنند.

1SELECT S.cluster_id, prob,
2       CLUSTER_DETAILS(my_sample, S.cluster_id, 7 USING T.*) kset
3FROM
4  (SELECT v.*, CLUSTER_SET(my_sample, USING *) nset
5    FROM mining_data
6   WHERE client_id = 1000) T,
7  TABLE(T.nset) Q
8ORDER BY 2 DESC;

خوشه‌ به مجموعه‌ای از جدول‌ها گفته می‌شود که اطلاعات مرتبط بهم را در بلوک‌های داده‌ای یکسانی ذخیره می‌کنند. در واقع تمام جدول‌ها در کنار یکدیگر قرار گرفته‌اند. برای مثال، جدول‌های EMPLOYEE  و DEPARTMENT  با استفاده از ستون DNO  به یکدیگر متصل شده‌اند. اگر این جدول‌ها را باهمدیگر در یک خوشه قرار دهیم، تمام ردیف‌های آن‌ها در بلوک‌های داده یکسانی ذخیره می‌شوند.

۵۸. تابع NANVL در کوئری‌های SQL

از این تابع برای اجرای عکس‌العمل مناسب در زمان دریافت مقدار «غیرعددی» (Not a Number | NaN) استفاده می‌شود. فرض کنیم که مقدار «n2» را برابر با NaN قرار داده‌ایم. در این زمان با کمک تابع NANVL  و به صورت خودکار مقدار اختیاری «n1» برگشت داده می‌شود. اما اگر «n2» عددی معتبر بود، در خروجی تابع مقدار «n2» برگشت داده می‌‌شد. از این تابع فقط در زمان کار کردن با مقادیری با نوع «BINARY_FLOAT» استفاده می‌شود.

کوئری که در پایین آمده روش کار تابع NANVL  را نمایش می‌دهد.

1SELECT bin_float, NANVL(bin_float,0)
2FROM my_demo_table;

سایر کوئری‌های SQL

کوئری‌ های SQL که در این بخش نمایش داده شده‌اند، شامل کوئری‌های پرکاربردی هستند که بیشتر به قواعد کلی کار مدیریت پایگاه‌های داده اشاره می‌کنند.

فیلم آموزش کوئری‌ نویسی پیشرفته در SQL Server در فرادرس

کلیک کنید

۵۹. برگرداندن ستونی از مقادیر یکتا

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

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

1SELECT DISTINCT ID FROM Customers

۶۰. استفاده از عبارت SELECT TOP برای انتخاب برترین گزینه‌ها

فرض کنیم که جدول مشتریان Customers به‌مرور زمان بسیار بزرگ شده است. این جدول اکنون شامل هزاران ردیف داده است. اما برای دیدن سربرگ‌های ستون‌ها می‌خواهیم که فقط ۲۵ مورد اول از ردیف‌های این جدول نمایش داده شوند. به این منظور از عبارت SELECT TOP  استفاده می‌کنیم. با کمک این عبارت می‌توان تعداد ردیف برگشت داده شده بعد از اجرای کوئری‌ها را مشخص کرد. در این مثال تصمیم داریم که ۲۵ ردیف اول از جدول Customers را انتخاب کرده و به بیرون برگردانیم.

1SELECT TOP 25 FROM Customers WHERE Customer_ID<>NULL;

۶۱. کلمات کلیدی ANY  و ALL

بعضی از کوئری های SQL پیچیده‌ از عبارت‌های ANY  و ALL  استفاده می‌کنند. کلمه کلیدی ALL  کمک می‌کند تا دستورات بر روی همه ردیف‌ها اعمال شوند. البته وقتی که شرط تعیین شده به ازای همه رکورد‌ها «TRUE» باشد. در مثال زیر ردیف‌ها را از جدول Orders واکشی می‌کنیم. هدف این است که به ازای محصول مشخص شده، سفارشات با حجم بالا را پیدا کنیم. در این کوئری کد را به ازای مشتریانی اجرا می‌کنیم که بیش از ۵۰ محصول را سفارش داده‌اند.

1SELECT Item FROM Orders 
2WHERE id = ALL
3(SELECT ID FROM Orders
4WHERE quantity > 50)

۶۲. نوشتن کوئری‌های SQL مناسب برای توسعه‌دهندگان

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

اسکریپت‌های SQL مجموعه‌ای از کوئری‌ها در کنار توضیحات مربوط به این کدها هستند. تمام این موارد در کنار هم و در یک فایل ذخیره‌ می‌شوند. این فایل می‌تواند شامل دستورات SQL یا کدهای PL/SQL شود. افراد می‌توانند از اسکپریت‌های SQL برای ساخت، ویرایش، طراحی، اجرا یا حذف فایل‌ها استفاده کنند.

کاراکترهای جداکننده تک‌خطی «--» و چندخطی «/* ... */ » به توسعه‌دهندگان کمک می‌کنند که توضیحات مفیدی را به اسکریپت‌های خود اضافه کنند. از این کاراکتر‌ها می‌توان به روش ارزشمند دیگری نیز استفاده کرد. بعضی وقت‌ها لازم می‌شود که بخش خاصی از کد غیرفعال شود. اما به عنوان توسعه‌دهندگان نمی‌خواهیم که این کدها را پاک کنیم. زیرا شاید در آینده به آن کدها نیاز داشته باشیم.

کوئری پایین را در نظر بگیرید. در این کوئری به سادگی و با اضافه کردن جداکننده‌های مربوط به کامنت‌ها می‌توانیم بخش‌هایی از کدها را بلافاصله غیرفعال کنیم. به همین‌ صورت با حذف جدا کننده‌ها این کدها بلافاصله فعال می‌شوند.

1/* This query below is commented so it won't execute*/
2/*
3SELECT item FROM Orders 
4WHERE date ALL = (SELECT Order_ID FROM Orders
5WHERE quantity > 50)
6*/
7 
8/* the SQL query below the will be executed 
9ignoring the text after "--"
10*/
11 
12SELECT item -- single comment  
13FROM Orders -- another single comment
14WHERE id 
15ALL = (SELECT ID FROM Orders
16WHERE quantity > 25)

۶۳. کپی کردن داده‌‌های انتخاب شده از جدولی به جدول دیگر

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

1INSERT INTO Yearly_Orders 
2SELECT * FROM Orders 
3WHERE Date<=1/1/2018

کوئری بالا، تمام رکورد‌های وارد شده به جدول را تا سال 2018  به جدول آرشیو وارد می‌کند.

۶۴. پیدا کردن هشت رکورد آخر از جدول

اگر بخواهیم که ۸ ردیف آخر وارد شده به جدول را فراخوانی کنیم - بخصوص وقتی که جدول دارای تعداد زیادی داده است - با کار مشکلی روبه‌رو می‌شویم. برای مثال فرض کنیم که می‌خواهیم هشت ردیف از آخرین داده‌های وارد شده به جدول کارمندان Employee  را استخراج کنیم. به منظور انجام اینکار می‌توانیم از عبارت‌های rownum  و UNION  استفاده کنیم. به یاد داشته باشید که متغیر rownum  در SQL مقداری موقتی است.

در کوئری زیر، روش استخراج ۸ داده آخر وارد شده به این جدول را نمایش داده‌ایم.

1Select * from Employee A where rownum <=8
2union
3select * from (Select * from Employee A order by rowid desc) where rownum <=8;

توجه: در کوئری بالا هشت مورد از آخرین رکورد‌های وارد شده به جدول Employee  استخراج خواهند شد. rownum  در این کوئری نقش «شبه‌ستون» (Pseudo Column) را بازی می‌کند. داده‌ها با کمک این شبه‌ستون، در مجموعه خروجی اندیس‌گذاری می‌شوند.

۶۵. تابع LAG در کوئری‌های SQL

تابع LAG  برای واکشی داده از ردیف قبلی استفاده می‌شود. این تابع هم یکی از توابع تحلیلی است. برای مثال، کوئری پایین مقدار حقوق را از ردیف قبلی واکشی می‌کند. با این کار مقدار اختلاف بین حقوق ردیف فعلی با ردیف قبلی محاسبه می‌شود. عبارت ORDER BY  درون تابع LAG، ترتیب ردیف‌ها را مشخص می‌کند. به صورت پیش‌فرض آفست برابر با 1  است. البته اگر خودمان به صورت دستی آن‌ را تغییر نداده باشیم. اگر آفست از محدوده مجاز فراتر رود می‌توانیم مقدار پیش‌فرض را به صورت سفارشی را تغییر دهیم. اما اگر این کار را انجام ندهیم، SQL مقدار NULL  برمی‌گرداند.

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

1SELECT dtno,
2       eno,
3       emname,
4       job,
5       salary,
6       LAG(sal, 1, 0) OVER (PARTITION BY dtno ORDER BY salary) AS salary_prev
7FROM   employee;

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

DTNO ENO ENAME JOB SAL SAL_PREV
---------- ---------- ---------- --------- ---------- ----------
10 7931 STEVE CLERK 1300 0
10 7783 JOHN MANAGER 2450 1300
10 7834 KING PRESIDENT 5000 2450
20 7364 ROBIN CLERK 800 0
20 7876 BRIAN CLERK 1100 800
20 7567 SHANE MANAGER 2975 1100
20 7784 SCOTT ANALYST 3000 2975
20 7908 KANE ANALYST 3000 3000
30 7900 JAMES CLERK 950 0
30 7651 CONNER SALESMAN 1250 950
30 7522 MATTHEW SALESMAN 1250 1250
30 7843 VIVIAN SALESMAN 1500 1250
30 7494 ALLEN SALESMAN 1600 1500
30 7695 GLEN MANAGER 2850 1600

۶۶. استفاده از تابع LEAD

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

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

1SELECT eno,
2       empname,
3       job,
4       salary,
5       LEAD(salary, 1, 0) OVER (ORDER BY salary) AS salary_next,
6       LEAD(salary, 1, 0) OVER (ORDER BY salary) - salary AS salary_diff
7FROM   employee;

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

ENO EMPNAME JOB SALARY SALARY_NEXT SALARY_DIFF
---------- ---------- --------- ---------- ---------- ----------
7369 STEVE CLERK 800 950 150
7900 JEFF CLERK 950 1100 150
7876 ADAMS CLERK 1100 1250 150
7521 JOHN SALESMAN 1250 1250 0
7654 MARK SALESMAN 1250 1300 50
7934 TANTO CLERK 1300 1500 200
7844 MATT SALESMAN 1500 1600 100
7499 ALEX SALESMAN 1600 2450 850
7782 BOON MANAGER 2450 2850 400
7698 BLAKE MANAGER 2850 2975 125
7566 JONES MANAGER 2975 3000 25
7788 SCOTT ANALYST 3000 3000 0
7902 FORD ANALYST 3000 5000 2000
7839 KING PRESIDENT 5000 0 -5000

۶۷. N کوئری بالا

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

برای مثال، کوئری زیر، به ترتیب، قیمت‌ها مرتب شده را از بالا و پایین جدول برمی‌گرداند.

1SELECT price
2FROM   sales_order
3ORDER BY price;

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

PRICE
----------
100
100
200
200
300
300
400
400
500
500
600
 
PRICE
----------
600
700
700
800
800
900
900
1000
1000
 
20 rows selected.

۶۸. کوئری تحلیلی CORR

تابع تحلیلی CORR  برای محاسبه «ضریب همبستگی» (Coefficient Of Correlation) به کار برده می‌شود. از این کوئری برای مشخص کردن «ضریب همبستگی پیرسون» (Pearson Correlation Coefficient) هم استفاده می‌شود. تابع CORR  بر روی ردیف‌هایی کار می‌کند که شامل داده NULL  نباشند. این کوئری همیشه مقداری بین ۱+ و ۱- را برمی‌گرداند. در کادر زیر سینتکس ابتدایی استفاده از این تابع نمایش داده شده است.

1CORR(exp1, exp2) [ OVER (analytic_clause) ]

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

1SELECT empid,
2       name,
3       dno,
4       salary,
5       job,
6       CORR(SYSDATE - joiningdate, salary) OVER () AS my_corr_val
7FROM   employee;

۶۹. تابع PREDICTION

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

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

1SELECT client_gender, COUNT(*) AS ct, ROUND(AVG(age)) AS average_age
2   FROM mining_data_shop
3   WHERE PREDICTION(sample COST MODEL
4      USING client_marital_status, house_size) = 1
5   GROUP BY client_gender
6   ORDER BY client_gender;

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

CUST_GENDER         CNT    AVG_AGE
------------ ---------- ----------
F                   270         40
M                   585         41

۷۰. عبارت‌های رایج جدول CTE

«عبارت‌ رایج جدول» (Common Table Expression | CTE) مجموعه نتایج موقتی هستند که در طول زمان اجرای کوئری خاصی دوام دارند. از این عبارت می‌توان چندین بار درون آن کوئری استفاده کرد. در کوئری زیر، از CTE استفاده کرده‌ایم.

1WITH all_emp
2AS
3(
4SELECT empId, BossId, FirstName, LastName
5FROM Emp
6WHERE BossId is NULL
7 
8UNION ALL
9 
10SELECT e.empId, e.BossId, e.FirstName, e.LastName
11FROM Emp e INNER JOIN all_emp r
12ON e.BossId = r.Id
13)
14SELECT * FROM all_emp

رایج ترین کوئری های SQL

در این بخش از مطلب، ۱۳ مورد از پرکابردترین کوئری‌ های SQL را فهرست کرده‌ایم. این کوئری‌ها تقریبا در تمام پایگاه‌های داده اجرا می‌شوند.

1. SELECT  :‌ این کوئری برای استخراج داده از جدول در پایگاه‌های داده به کار برده می‌شود.
2. CREATE DATABASE : کوئری که برای ساخت پایگاه داده استفاده می‌‌شود.
3. DROP DATABASE : به وسیله این کوئری می‌توان پایگاه داده موجود را حذف کرد.
4. CREATE TABLE : با کمک این کوئری، می‌توانیم در پایگاه داده مشخص شده جدول ایجاد کنیم.
5. ALTER TABLE : کوئری که برای اعمال تغییرات در جدول‌های موجود مربوط به پایگاه داده مشخص شده استفاده می‌شود.
6. DROP TABLE : با کمک این کوئری جدول موجود را از درون پایگاه داده مشخص شده حذف می‌کنیم.
7. CREATE INDEX : این کوئری برای ساخت اندیس به کار برده می‌شود.
8. CREATE VIEW : از این کوئری برای ساخت ویو استفاده می‌شود.
9. DROP VIEW : از این کوئری برای حذف کردن ویو استفاده می‌شود.
10. CREATE PROCEDURE : این کوئری به توسعه‌دهندگان در ساخت رویه کمک می‌کند.
11. CREATE FUNCTION : این کوئری به توسعه‌دهندگان در ساخت تابع کمک می‌کند.
12. DROP PROCEDURE : از این کوئری برای حذف کردن رویه استفاده می‌شود.
13. DROP FUNCTION : این کوئری برای حذف کردن تابع به کار برده می‌شود.

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

کلیک کنید

جدول مهم‌ترین کوئری های SQL

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