Sql توابع جمع را جستجو می کند. توابع جمع MIN و MAX

GROUP BY بند(عبارت SELECT) به شما امکان می دهد داده ها (ردیف ها) را بر اساس مقدار یک ستون یا چندین ستون یا عبارت گروه بندی کنید. نتیجه مجموعه ای از ردیف های خلاصه خواهد بود.

هر ستون در لیست انتخاب باید در بند GROUP BY وجود داشته باشد، تنها استثناها ثابت ها و ستون ها هستند - عملوندهای توابع جمع.

یک جدول را می توان با هر ترکیبی از ستون های آن گروه بندی کرد.

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

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

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

توابع جمع MIN و MAXکوچکترین و بزرگترین مقادیر ستون به ترتیب محاسبه می شود. آرگومان ها می توانند اعداد، رشته ها و تاریخ باشند. تمام مقادیر NULL قبل از محاسبه حذف می شوند (یعنی در نظر گرفته نمی شوند).

تابع مجموع SUMمجموع کل مقادیر ستون ها را محاسبه می کند. فقط اعداد می توانند آرگومان باشند. استفاده از پارامتر DISTINCT هر مقدار تکراری در ستون را قبل از اعمال تابع SUM حذف می کند. به همین ترتیب، قبل از اعمال این تابع جمع، تمام مقادیر NULL را حذف کنید.

عملکرد جمع AVGمیانگین تمام مقادیر یک ستون را برمی گرداند. آرگومان ها همچنین می توانند فقط اعداد باشند و تمام مقادیر NULL قبل از ارزیابی حذف می شوند.

تابع مجموع COUNTدارای دو شکل مختلف:

• COUNT (col_name) - تعداد مقادیر موجود در ستون col_name را می شمارد، مقادیر NULL شمارش نمی شوند.
• COUNT (*) - تعداد ردیف های جدول را می شمارد، مقادیر NULL نیز در نظر گرفته می شود.

اگر پرس و جو از کلمه کلیدی DISTINCT استفاده کند، تمام مقادیر ستون تکراری قبل از استفاده از تابع COUNT حذف می شوند.

COUNT_BIG تابععملکرد مشابه COUNT. تنها تفاوت بین این دو نوع نتیجه ای است که برمی گردانند: COUNT_BIG همیشه مقادیر BIGINT را برمی گرداند، در حالی که COUNT مقادیر INTEGER داده را برمی گرداند.

V داشتن پیشنهادشرطی را تعریف می کند که برای گروهی از ردیف ها اعمال می شود. برای گروه‌های ردیف به معنای عبارت WHERE برای محتویات جدول مربوطه است (WHERE قبل از گروه‌بندی، HAVING بعد اعمال می‌شود).

بخش‌های فرعی زیر سایر بندهای SELECT را که می‌توان در پرس‌و‌جوها استفاده کرد، و همچنین توابع جمع‌آوری و مجموعه دستورات را توضیح می‌دهد. یادآوری می کنم که تا اینجا به استفاده از عبارت WHERE پرداخته ایم و در این مقاله به بندهای GROUP BY، ORDER BY و HAVING می پردازیم و نمونه هایی از استفاده از این بندها را در ترکیب با توابع جمع ارائه می کنیم. که در Transact-SQL پشتیبانی می شوند.

GROUP BY بند

جمله دسته بندی بر اساسمجموعه ردیف انتخاب شده را گروه بندی می کند تا مجموعه ای از ردیف های خلاصه را بر اساس مقادیر یک یا چند ستون یا عبارت بدست آورد. یک مورد استفاده ساده برای بند GROUP BY در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. SELECT Job FROM Works_On GROUP BY Job.

در این مثال، موقعیت های کارمند نمونه گیری و گروه بندی می شوند.

در مثال بالا، عبارت GROUP BY یک گروه جداگانه برای تمام مقادیر ممکن (از جمله NULL) برای ستون Job ایجاد می کند.

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

یک جدول را می توان با هر ترکیبی از ستون های آن گروه بندی کرد. مثال زیر گروه بندی ردیف ها در جدول Works_on را با دو ستون نشان می دهد:

از SampleDb استفاده کنید. SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;

نتیجه این پرس و جو:

بر اساس نتایج پرس و جو، می توانید ببینید که 9 گروه با ترکیب های مختلف از شماره پروژه و عنوان وجود دارد. توالی نام ستون ها در بند GROUP BY نباید مانند لیست ستون SELECT باشد.

توابع مجموع

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

توابع جمع معمولی؛

توابع جمع آماری;

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

توابع مجموع تحلیلی

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

توابع جمع منظم

Transact-SQL از شش تابع جمع زیر پشتیبانی می کند: MIN, حداکثر, جمع, AVG, شمردن, COUNT_BIG.

همه توابع انبوه محاسبات را بر روی یک آرگومان انجام می دهند که می تواند یک ستون یا یک عبارت باشد. (تنها استثنا شکل دوم دو تابع، COUNT و COUNT_BIG، به ترتیب COUNT (*) و COUNT_BIG (*) است.) هر تابع جمعی به یک مقدار ثابت ارزیابی می شود که در یک ستون نتیجه جداگانه ظاهر می شود.

توابع انبوه در لیست ستونی یک عبارت SELECT مشخص می شوند که می تواند شامل یک عبارت GROUP BY نیز باشد. اگر دستور SELECT شامل یک بند GROUP BY نباشد و لیست ستون انتخاب حداقل یک تابع تجمیع داشته باشد، در این صورت نباید دارای ستون های ساده باشد (به غیر از ستون هایی که به عنوان آرگومان های تابع جمع استفاده می شوند). بنابراین، کد موجود در مثال زیر نادرست است:

از SampleDb استفاده کنید. نام خانوادگی، MIN (Id) FROM Employee را انتخاب کنید.

در اینجا، ستون LastName جدول Employee نباید در لیست ستون انتخاب باشد زیرا آرگومان تابع جمع نیست. از سوی دیگر، یک لیست انتخابی از ستون‌ها می‌تواند حاوی نام ستون‌هایی باشد که آرگومان‌هایی برای تابع انبوه نیستند، اگر آن ستون‌ها به عنوان آرگومان‌های عبارت GROUP BY استفاده شوند.

یک آرگومان تابع مجموع را می توان با یکی از دو کلمه کلیدی ممکن قبل از آن بیان کرد:

همه

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

متمایز

مشخص می کند که فقط از مقادیر ستون منحصر به فرد برای محاسبات استفاده می شود.

توابع جمع MIN و MAX

توابع مجموع MIN و MAX به ترتیب کوچکترین و بزرگترین مقادیر را در یک ستون محاسبه می کنند. اگر پرس و جو حاوی یک عبارت WHERE باشد، توابع MIN و MAX کوچکترین و بزرگترین مقادیر ردیفی را که با شرایط مشخص شده مطابقت دارد، برمی گرداند. مثال زیر استفاده از تابع جمع آوری MIN را نشان می دهد:

از SampleDb استفاده کنید. - 2581 SELECT MIN (Id) AS "Minimum ID value" FROM Employee را برمی گرداند.

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

نتیجه اجرای پرس و جو:

استفاده از تابع تجمع MAX در مثال زیر نشان داده شده است:

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

کلمه کلیدی DISTINCT را می توان با توابع MIN و MAX استفاده کرد. تمام مقادیر NULL از ستون های آرگومان خود قبل از استفاده از توابع جمع MIN و MAX حذف می شوند.

تابع مجموع SUM

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

از SampleDb استفاده کنید. SELECT SUM (Budget) "Total Budget" FROM Project.

این مثال کل بودجه را برای همه پروژه ها محاسبه می کند. نتیجه اجرای پرس و جو:

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

از SampleDb استفاده کنید. SELECT SUM (Budget) "Total Budget" FROM Project GROUP BY ();

استفاده از پارامتر DISTINCT هر مقدار تکراری در ستون را قبل از اعمال تابع SUM حذف می کند. به همین ترتیب، قبل از اعمال این تابع جمع، تمام مقادیر NULL را حذف کنید.

عملکرد جمع AVG

تجمیع عملکرد AVGمیانگین حسابی تمام مقادیر یک ستون را برمی گرداند. آرگومان این تابع تجمیع همیشه باید از نوع داده عددی باشد. تمام مقادیر NULL قبل از اعمال تابع AVG از آرگومان حذف می شوند.

استفاده از تابع AVG aggregate در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. - بازگشت 133833 SELECT AVG (Budget) "Average Budget for a project" FROM Project;

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

توابع COUNT و COUNT_BIG را جمع کنید

تجمیع COUNT تابعدارای دو شکل مختلف:

COUNT (col_name) COUNT (*)

شکل اول تابع تعداد مقادیر موجود در ستون col_name را می شمارد. اگر پرس و جو از کلمه کلیدی DISTINCT استفاده کند، تمام مقادیر ستون تکراری قبل از استفاده از تابع COUNT حذف می شوند. این فرم از تابع COUNT هنگام شمارش تعداد مقادیر ستون، مقادیر NULL را در نظر نمی گیرد.

استفاده از اولین شکل تابع COUNT در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. SELECT ProjectNumber، COUNT (DiSTINCT Job) "Jobs in Project" FROM Works_on GROUP BY ProjectNumber.

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

همانطور که از پرس و جو در مثال مشاهده می کنید، مقادیر NULL توسط تابع COUNT در نظر گرفته نشده است. (مجموع تمام مقادیر در ستون job 7 است نه 11 آنطور که باید باشد.)

شکل دوم تابع COUNT، یعنی. تابع COUNT (*) تعداد ردیف های جدول را می شمارد. و اگر دستور SELECT پرس و جو با تابع COUNT (*) حاوی یک عبارت WHERE با یک شرط باشد، تابع تعداد ردیف هایی را که شرایط مشخص شده را برآورده می کند، برمی گرداند. برخلاف نسخه اول تابع COUNT، فرم دوم مقادیر NULL را نادیده نمی گیرد زیرا این تابع بر روی رشته ها عمل می کند نه ستون ها. مثال زیر استفاده از تابع COUNT (*) را نشان می دهد:

از SampleDb استفاده کنید. شغل را به عنوان "نوع شغل" انتخاب کنید، COUNT (*) "کارگران مورد نیاز" FROM Works_on GROUP BY Job;

اینجاست که تعداد پست ها در همه پروژه ها محاسبه می شود. نتیجه اجرای پرس و جو:

COUNT_BIG تابععملکرد مشابه COUNT. تنها تفاوت بین این دو نوع نتیجه ای است که برمی گردانند: COUNT_BIG همیشه مقادیر BIGINT را برمی گرداند، در حالی که COUNT مقادیر INTEGER داده را برمی گرداند.

توابع جمع آماری

توابع زیر گروه توابع جمع آماری را تشکیل می دهند:

VAR

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

VARP

واریانس آماری جمعیت همه مقادیر در یک ستون یا عبارت را محاسبه می کند.

STDEV

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

STDEVP

انحراف استاندارد مجموعه تمام مقادیر در یک ستون یا عبارت را محاسبه می کند.

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

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

بند داشتن

در یک جمله داشتنشرطی را تعریف می کند که برای گروهی از ردیف ها اعمال می شود. بنابراین، این بند برای گروه‌های ردیف همان معنای عبارت WHERE برای محتویات جدول مربوطه را دارد. نحو عبارت HAVING به شرح زیر است:

شرایط داشتن

در اینجا پارامتر شرط یک شرط را نشان می دهد و حاوی توابع یا ثابت های مجموع است.

استفاده از عبارت HAVING در رابطه با تابع جمع COUNT (*) در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. - "p3" را برمی گرداند SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT (*)

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

همانطور که در مثال زیر نشان داده شده است، عبارت HAVING را می توان بدون توابع انبوه نیز استفاده کرد:

از SampleDb استفاده کنید. - "مشاور" را برمی گرداند.

این مثال ردیف های جدول Works_on را بر اساس عنوان شغل گروه بندی می کند و کارهایی را که با حرف "K" شروع نمی شوند حذف می کند.

بند HAVING را می توان بدون بند GROUP BY نیز استفاده کرد، اگرچه این روش معمول نیست. در این حالت، تمام ردیف های جدول در یک گروه بازگردانده می شوند.

ترتیب بر اساس بند

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

ترتیب مرتب سازی در پارامتر col_name مشخص شده است. Col_number یک نشانگر ترتیب مرتب سازی جایگزین است که ستون ها را به ترتیبی که در لیست انتخاب دستور SELECT ظاهر می شوند (1 ستون اول، 2 ستون دوم و غیره) شناسایی می کند. پارامتر ASCیک ترتیب صعودی را تعریف می کند و پارامتر DESC- در پایین دست پیش فرض ASC است.

نام ستون ها در بند ORDER BY لازم نیست در لیست ستون های انتخاب شده باشد. اما این در مورد جستارهایی مانند SELECT DISTINCT صدق نمی کند، زیرا در چنین پرس و جوهایی، نام ستون های مشخص شده در عبارت ORDER BY نیز باید در لیست ستون های انتخابی ظاهر شود. علاوه بر این، این بند نمی تواند شامل نام ستون هایی از جداولی باشد که در عبارت FROM مشخص نشده اند.

همانطور که از نحو عبارت ORDER BY می بینید، مرتب سازی مجموعه نتایج را می توان در چندین ستون انجام داد. این مرتب سازی در مثال زیر نشان داده شده است:

این مثال شماره بخش و نام خانوادگی و نام کارمندان را برای کارمندانی که تعداد پرسنل آنها کمتر از 20000 است انتخاب می کند و همچنین بر اساس نام خانوادگی و نام مرتب شده است. نتیجه این پرس و جو:

ستون ها در بند ORDER BY را می توان نه با نام آنها، بلکه به ترتیب در لیست انتخاب مشخص کرد. بر این اساس، جمله در مثال بالا را می توان به صورت زیر بازنویسی کرد:

این روش جایگزین برای تعیین ستون ها بر اساس موقعیت آنها به جای نام، در صورتی استفاده می شود که معیار ترتیب شامل یک تابع جمع باشد. (روش دیگر استفاده از نام ستون‌ها است که سپس در عبارت ORDER BY ظاهر می‌شوند.) با این حال، در عبارت ORDER BY، توصیه می‌شود ستون‌ها را به جای اعداد، با نام آن‌ها مشخص کنید تا به‌روزرسانی درخواست آسان‌تر شود. اگر نیاز به اضافه کردن یا حذف ستون ها در لیست انتخاب دارید. تعیین ستون ها در عبارت ORDER BY با شماره آنها در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. شماره پروژه را انتخاب کنید، COUNT (*) "تعداد کارمندان" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;

در اینجا، برای هر پروژه، تعداد پروژه و تعداد کارکنان شرکت کننده در آن انتخاب می شود و نتیجه را به ترتیب نزولی بر تعداد کارمندان مرتب می کند.

Transact-SQL مقادیر NULL را هنگامی که به ترتیب صعودی مرتب می شوند در بالای لیست قرار می دهد و هنگامی که به ترتیب نزولی مرتب می شوند مقادیر NULL را در انتهای لیست قرار می دهد.

استفاده از ORDER BY Clause برای صفحه بندی نتایج

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

برای پشتیبانی از ایجاد صفحه سمت سرور، SQL Server 2012 دو عبارت SELECT جدید را معرفی می کند: OFFSET و FETCH. کاربرد این دو جمله در مثال زیر نشان داده شده است. در اینجا، از پایگاه داده AdventureWorks2012 (که می توانید در منبع پیدا کنید)، شناسه کسب و کار، عنوان شغلی و تاریخ تولد همه کارمندان زن را استخراج می کند که بر اساس عنوان شغلی به ترتیب صعودی مرتب شده اند. مجموعه ردیف های حاصل به صفحات 10 خطی تقسیم می شود و صفحه سوم نمایش داده می شود:

در یک جمله انحرافتعداد خطوط نتیجه را برای پرش در نتیجه نمایش داده شده مشخص می کند. این مقدار پس از مرتب‌سازی ردیف‌ها با عبارت ORDER BY محاسبه می‌شود. در یک جمله واکشی بعدیتعداد ردیف های WHERE و مرتب شده برای بازگشت را مشخص می کند. پارامتر این بند می تواند یک ثابت، یک عبارت یا نتیجه یک پرس و جو دیگر باشد. عبارت FETCH NEXT مشابه بند است اول واکشی.

هدف اصلی هنگام ایجاد صفحات در سمت سرور این است که بتوان فرم های صفحه رایج را با استفاده از متغیرها پیاده سازی کرد. این کار را می توان از طریق بسته SQL Server انجام داد.

SELECT Statement and IDENTITY Property

ویژگی IDENTITYبه شما امکان می دهد مقادیر یک ستون خاص از جدول را در قالب یک شمارنده افزایشی خودکار تعیین کنید. ستون های یک نوع داده عددی مانند TINYINT، SMALLINT، INT و BIGINT می توانند این ویژگی را داشته باشند. برای چنین ستون جدولی، موتور پایگاه داده به طور خودکار مقادیر متوالی را تولید می کند که با مقدار شروع مشخص شده شروع می شود. بنابراین، از ویژگی IDENTITY می توان برای تولید مقادیر عددی تک رقمی برای ستون انتخاب شده استفاده کرد.

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

از SampleDb استفاده کنید. محصول جدول ایجاد کنید (شناسه INT IDENTITY (10000، 1) NOT NULL، نام NVARCHAR (30) NOT NULL، قیمت پول) درج در محصول (نام، قیمت) VALUES ("محصول1"، 10)، ("محصول2"، 15) , ("محصول3"، 8)، ("محصول4"، 15)، ("محصول5"، 40); - 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Product5"; - مشابه عبارت قبلی SELECT $ هویت از محصول WHERE Name = "Product5";

این مثال ابتدا یک جدول Product ایجاد می کند که حاوی یک ستون ID با ویژگی IDENTITY است. مقادیر در ستون Id به طور خودکار توسط سیستم ایجاد می شود، از 10000 شروع می شود و برای هر مقدار بعدی در یک مرحله افزایش می یابد: 10000، 10،001، 10،002 و غیره.

چندین تابع و متغیر سیستم با ویژگی IDENTITY مرتبط هستند. به عنوان مثال، کد مثال استفاده می کند متغیر سیستم $ident... همانطور که از نتایج اجرای این کد می بینید، این متغیر به طور خودکار به ویژگی IDENTITY ارجاع داده می شود. همچنین می توانید به جای آن از عملکرد سیستم استفاده کنید هویت.

مقدار اولیه و افزایش ستون با ویژگی IDENTITY را می توان با استفاده از توابع پیدا کرد IDENT_SEEDو IDENT_INCRبه ترتیب. این توابع به صورت زیر اعمال می شوند:

از SampleDb استفاده کنید. SELECT IDENT_SEED ("محصول")، IDENT_INCR ("محصول")

همانطور که گفته شد، مقادیر IDENTITY به طور خودکار توسط سیستم تنظیم می شود. اما کاربر می تواند به صراحت مقادیر خود را برای رشته های خاص با اختصاص دادن به پارامتر مشخص کند. IDENTITY_INSERTمقدار ON قبل از درج مقدار صریح:

SET IDENTITY درج نام جدول ON

از آنجایی که پارامتر IDENTITY_INSERT می تواند برای تنظیم هر مقدار، از جمله مقادیر تکراری، در ستونی با ویژگی IDENTITY استفاده شود، ویژگی IDENTITY معمولاً منحصر به فرد بودن مقادیر ستون را اعمال نمی کند. بنابراین، محدودیت های UNIQUE یا PRIMARY KEY باید برای اعمال منحصر به فرد بودن مقادیر ستون اعمال شود.

هنگامی که پس از تنظیم IDENTITY_INSERT روی on، مقادیر را در جدول وارد می کنید، سیستم مقدار بعدی را در ستون IDENTITY ایجاد می کند و بالاترین مقدار فعلی را در آن ستون افزایش می دهد.

عبارت CREATE SEQUENCE

استفاده از ویژگی IDENTITY چندین اشکال مهم دارد که مهمترین آنها عبارتند از:

دارایی به جدول مشخص شده محدود می شود.

مقدار جدید ستون را به هیچ وجه نمی توان به دست آورد، مگر با اعمال آن.

ویژگی IDENTITY فقط هنگام ایجاد یک ستون قابل تعیین است.

به این دلایل، SQL Server 2012 دنباله هایی را معرفی می کند که دارای معنایی مشابه با ویژگی IDENTITY هستند، اما بدون معایب ذکر شده قبلی. در این زمینه، یک دنباله به عملکرد یک پایگاه داده اشاره دارد که به شما امکان می دهد مقادیر شمارنده را برای اشیاء مختلف پایگاه داده، مانند ستون ها و متغیرها، مشخص کنید.

توالی ها با استفاده از دستورالعمل ایجاد می شوند ایجاد دنباله... دستور CREATE SEQUENCE در استاندارد SQL تعریف شده است و توسط سایر سیستم های پایگاه داده رابطه ای مانند IBM DB2 و Oracle پشتیبانی می شود.

مثال زیر نحوه ایجاد یک دنباله در SQL Server را نشان می دهد:

از SampleDb استفاده کنید. ایجاد دنباله dbo.Sequence1 به عنوان شروع اولیه با 1 افزایش در 5 MINVALUE 1 MAXVALUE 256 CYCLE.

در مثال بالا، مقادیر Sequence1 به‌طور خودکار توسط سیستم تولید می‌شوند و از مقدار 1 شروع می‌شوند و برای هر مقدار متوالی با افزایش 5 برابری می‌شوند. بنابراین، در بند شروعمقدار اولیه نشان داده شده است، و در پیشنهاد افزایشی- گام. (مرحله می تواند مثبت یا منفی باشد.)

در دو جمله اختیاری بعدی MINVALUEو MAXVALUEحداقل و حداکثر مقدار شی sequence را مشخص می کند. (توجه داشته باشید که MINVALUE باید کمتر یا مساوی با مقدار اولیه باشد و MAXVALUE نمی تواند بزرگتر از حد بالای نوع داده مشخص شده برای دنباله باشد.) در یک جمله چرخهنشان می دهد که وقتی از حداکثر (یا حداقل برای دنباله ای با گام منفی) مقدار فراتر رفت، دنباله از ابتدا تکرار می شود. به‌طور پیش‌فرض، این بند روی NO CYCLE تنظیم می‌شود، به این معنی که تجاوز از حداکثر یا حداقل مقدار توالی یک استثنا را ایجاد می‌کند.

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

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

از SampleDb استفاده کنید. - 1 را برمی گرداند SELECT NEXT VALUE FOR dbo.sequence1. - 6 را برمی گرداند (مرحله بعدی) SELECT NEXT VALUE FOR dbo.sequence1.

می‌توانید از عبارت NEXT VALUE FOR برای اختصاص دادن نتیجه یک دنباله به سلول یا سلول ستونی استفاده کنید. مثال زیر استفاده از این عبارت برای تخصیص نتایج به یک ستون را نشان می دهد:

از SampleDb استفاده کنید. ایجاد جدول محصول (ID INT NOT NULL، نام NVARCHAR (30) NOT NULL، قیمت پول) درج در مقادیر محصول (ارزش بعدی برای dbo.sequence1، "Product1"، 10); درج در مقادیر محصول (مقدار بعدی برای dbo.sequence1، "Product2"، 15)؛ -...

مثال بالا ابتدا جدولی به نام Product با چهار ستون ایجاد می کند. در مرحله بعد، دو عبارت INSERT دو ردیف را در این جدول قرار می دهند. دو خانه اول ستون اول 11 و 16 خواهد بود.

مثال زیر استفاده از نمای کاتالوگ را نشان می دهد sys.sequencesبرای مشاهده مقدار فعلی یک دنباله بدون استفاده از آن:

به طور معمول، عبارت NEXT VALUE FOR در عبارت INSERT برای دستور دادن به سیستم برای درج مقادیر تولید شده استفاده می شود. این عبارت همچنین می تواند به عنوان بخشی از یک پرس و جو چند خطی با استفاده از عبارت OVER استفاده شود.

برای تغییر ویژگی یک دنباله موجود، اعمال کنید عبارت ALTER SEQUENCE... یکی از مهمترین کاربردهای این عبارت، گزینه RESTART WITH است که توالی مشخص شده را ریست می کند. مثال زیر استفاده از دستور ALTER SEQUENCE را برای بازنشانی تقریباً تمام خصوصیات Sequence1 نشان می دهد:

از SampleDb استفاده کنید. ALTER SEQUENCE dbo.sequence1 شروع مجدد با 100 افزایش در 50 MINVALUE 50 MAXVALUE 200 NO CYCLE.

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

عملگرها را تنظیم کنید

علاوه بر عملگرهایی که قبلاً مورد بحث قرار گرفت، Transact-SQL از سه عملگر مجموعه دیگر پشتیبانی می کند: UNION، INTERSECT، و EXCEPT.

اپراتور اتحادیه

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

شکل کلی عملگر UNION به شکل زیر است:

select_1 UNION select_2 (select_3]) ...

گزینه های select_1، select_2، ... عبارت های SELECT هستند که join را ایجاد می کنند. اگر از پارامتر ALL استفاده شود، تمام ردیف ها از جمله موارد تکراری نمایش داده می شوند. در عبارت UNION، پارامتر ALL همان معنای SELECT را دارد، اما با یک تفاوت: این پیش فرض برای یک SELECT است، اما باید به صراحت برای UNION مشخص شود.

در شکل اصلی خود، SampleDb برای نشان دادن استفاده از عملگر UNION مناسب نیست. بنابراین، این بخش یک جدول جدید EmployeeEnh ایجاد می کند که مشابه جدول Employee موجود است اما دارای یک ستون City اضافی است. این ستون محل سکونت کارکنان را نشان می دهد.

ایجاد جدول EmployeeEnh این فرصت را به ما می دهد تا استفاده از بند را نشان دهیم. بهدر عبارت SELECT دستور SELECT INTO دو عملیات را انجام می دهد. ابتدا یک جدول جدید با ستون های لیست شده در لیست SELECT ایجاد می شود. سپس سطرهای جدول اصلی به جدول جدید وارد می شوند. نام جدول جدید در عبارت INTO و نام جدول منبع در عبارت FROM مشخص شده است.

مثال زیر ایجاد جدول EmployeeEnh را از جدول Employee نشان می دهد:

از SampleDb استفاده کنید. SELECT * INTO EmployeeEnh FROM Employee. ALTER TABLE EmployeeEnh ADD City NCHAR (40) NULL;

در این مثال، دستور SELECT INTO جدول EmployeeEnh را ایجاد می کند، تمام ردیف های جدول منبع Employee را در آن قرار می دهد و سپس دستور ALTER TABLE ستون City را به جدول جدید اضافه می کند. اما ستون شهر اضافه شده حاوی هیچ مقداری نیست. مقادیر را می توان از طریق Management Studio یا با استفاده از کد زیر در این ستون درج کرد:

از SampleDb استفاده کنید. به روز رسانی EmployeeEnh SET City = "Kazan" WHERE ID = 2581; به روز رسانی EmployeeEnh SET City = "Moscow" WHERE ID = 9031; به روز رسانی EmployeeEnh SET City = "Yekaterinburg" WHERE ID = 10102; به روز رسانی EmployeeEnh SET City = "St. Petersburg" WHERE Id = 18316; به روز رسانی EmployeeEnh SET City = "Krasnodar" WHERE ID = 25348; به روز رسانی EmployeeEnh SET City = "Kazan" WHERE ID = 28559; به روز رسانی EmployeeEnh SET City = "Perm" WHERE ID = 29346;

ما اکنون آماده نشان دادن استفاده از بیانیه UNION هستیم. مثال زیر یک پرس و جو برای ایجاد پیوند بین جداول EmployeeEnh و Department با استفاده از این عبارت را نشان می دهد:

از SampleDb استفاده کنید. SELECT City AS "City" FROM EmployeeEnh UNION SELECT Location FROM Department;

نتیجه این پرس و جو:

فقط جداول سازگار را می توان با استفاده از عبارت UNION ملحق کرد. منظور ما از جداول سازگار این است که هر دو لیست ستون های انتخابی باید دارای تعداد یکسانی از ستون ها باشند و ستون های مربوطه باید دارای انواع داده های سازگار باشند. (از نظر سازگاری، انواع داده های INT و SMALLINT سازگار نیستند.)

نتیجه الحاق را فقط می توان با استفاده از عبارت ORDER BY در آخرین دستور SELECT، همانطور که در مثال زیر نشان داده شده است، مرتب کرد. بند های GROUP BY و HAVING را می توان با دستورات SELECT جداگانه استفاده کرد، اما نه در خود join.

پرس و جو در این مثال کارمندانی را انتخاب می کند که یا در بخش d1 کار می کنند یا قبل از 1 ژانویه 2008 روی پروژه ای کار می کنند.

اپراتور UNION از گزینه ALL پشتیبانی می کند. این گزینه موارد تکراری را از مجموعه نتایج حذف نمی کند. اگر تمام عبارات SELECT که توسط یک یا چند عملگر UNION به یک جدول ملحق شده اند، می تواند به جای عملگر UNION استفاده شود. در این حالت، مجموعه دستورات SELECT با یک دستور SELECT با مجموعه ای از دستورات OR جایگزین می شود.

اپراتورهای INTERSECT و EXCEPT

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

Transact-SQL از استفاده از پارامتر ALL با عملگرهای INTERSECT یا EXCEPT پشتیبانی نمی کند. استفاده از عملگر EXCEPT در مثال زیر نشان داده شده است:

به یاد داشته باشید که این سه عملگر مجموعه دارای اولویت اجرای متفاوتی هستند: INTERSECT بالاترین اولویت را دارد و پس از آن EXCEPT و UNION کمترین اولویت را دارد. عدم توجه به اولویت اجرا هنگام استفاده از چندین عملگر مجموعه مختلف می تواند منجر به نتایج غیرمنتظره شود.

عبارات موردی

در زمینه برنامه نویسی کاربردی پایگاه داده، گاهی اوقات لازم است که ارائه داده ها را اصلاح کنید. به عنوان مثال، افراد را می توان بر اساس طبقه اجتماعی خود با استفاده از مقادیر 1، 2، و 3 که به ترتیب نشان دهنده مردان، زنان و کودکان هستند، تقسیم بندی کرد. این تکنیک برنامه نویسی می تواند زمان لازم برای اجرای برنامه را کاهش دهد. عبارت CASE Transact-SQL به شما اجازه می دهد تا به راحتی این نوع رمزگذاری را پیاده سازی کنید.

برخلاف اکثر زبان های برنامه نویسی، CASE یک عبارت نیست، بلکه یک عبارت است. بنابراین، یک عبارت CASE را می توان تقریباً در هر جایی که Transact-SQL اجازه استفاده از عبارات را می دهد استفاده می شود. عبارت CASE دو شکل دارد:

عبارت CASE ساده؛

عبارت جستجو CASE.

سینتکس یک عبارت CASE ساده به صورت زیر است:

یک عبارت با یک عبارت CASE ساده ابتدا لیست تمام عبارات موجود را جستجو می کند بند WHENاولین عبارتی که با express_1 مطابقت دارد و سپس عبارت مربوطه را اجرا می کند سپس بند... اگر هیچ عبارت منطبقی در لیست WHEN وجود ندارد، پس بند ELSE.

نحو برای عبارت جستجوی CASE به شرح زیر است:

در این حالت اولین شرط واجد شرایط را جستجو می کند و سپس عبارت THEN مربوطه را اجرا می کند. اگر هیچ یک از شرایط شرایط را برآورده نکرد، بند ELSE اجرا می شود. استفاده از عبارت جستجوی CASE در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. SELECT ProjectName, CASE WHEN Budget> 0 AND Budget 100000 AND Budget 150000 AND Budget

نتیجه این پرس و جو:

این مثال بودجه همه پروژه ها را وزن می کند و وزن های محاسبه شده را همراه با نام پروژه های مربوطه نمایش می دهد.

مثال زیر روش دیگری را برای استفاده از عبارت CASE نشان می دهد که در آن عبارت WHEN شامل سوالات فرعی است که بخشی از عبارت هستند:

از SampleDb استفاده کنید. SELECT ProjectName, CASE WHEN p1.Budget (انتخاب AVG (p2.Budget) FROM Project p2) سپس "بالاتر از میانگین" END "رده بودجه" FROM Project p1;

نتیجه این پرس و جو به شرح زیر است:

این درس مبحث sql تغییر نام یک ستون (فیلدها) را با استفاده از کلمه تابع AS پوشش می دهد. همچنین به مبحث توابع انبوه در sql پرداخت. نمونه های خاصی از درخواست ها تجزیه و تحلیل خواهند شد

می توانید نام ستون ها را در پرس و جوها تغییر نام دهید. این باعث می شود نتایج خواناتر شوند.

در SQL، تغییر نام فیلدها با استفاده از کلمه کلیدی ASکه برای تغییر نام فیلدها در مجموعه نتایج استفاده می شود

نحو:

انتخاب کنید<имя поля>مانند<псевдоним>از جانب ...

بیایید به مثالی از تغییر نام در SQL نگاه کنیم:

نمونه DB "Institute":نمایش نام معلمان و حقوق آنها، برای معلمانی که حقوق آنها زیر 15000 است، نام فیلد زارپلاتا را به "دستمزد کم"

✍ راه حل:

تغییر نام ستون ها در SQL اغلب ضروری است هنگام محاسبه مقادیر مرتبط با چندین فیلدجداول بیایید یک مثال را در نظر بگیریم:

نمونه DB "Institute":از جدول معلمان، فیلد نام را خروجی بگیرید و میزان حقوق و پاداش را محاسبه کنید و نام فیلد را نام ببرید "حقوق_جایزه"

✍ راه حل:

1 2	نام را انتخاب کنید، (zarplata + premia) AS zarplata_premia FROM معلمان;

نام را انتخاب کنید، (zarplata + premia) AS zarplata_premia FROM معلمان;

نتیجه:

توابع جمع در SQL

توابع جمع در sql برای بدست آوردن مجموع و محاسبه عبارات استفاده می شود:

همه توابع مجموع یک مقدار واحد را برمی‌گردانند.

توابع COUNT، MIN و MAX برای هر نوع داده ای اعمال می شود.

توابع SUM و AVG فقط برای فیلدهای عددی استفاده می شوند.
بین توابع COUNT (*) و COUNT () تفاوت وجود دارد: دومی مقادیر NULL را هنگام شمارش در نظر نمی گیرد.

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

نمونه DB "Institute":دریافت ارزش بالاترین حقوق در بین معلمان، نمایش نتیجه به عنوان "Max_zp"

✍ راه حل:

SELECT MAX (zarplata) AS max_zp FROM معلمان;

SELECT MAX (zarplata) AS max_zp FROM معلمان;

نتایج:

بیایید یک مثال پیچیده تر از استفاده از توابع جمع در sql را در نظر بگیریم.

✍ راه حل:

GROUP BY clause در SQL

گروه با بند در sql معمولاً همراه با توابع جمع استفاده می شود.

توابع انبوه بر روی تمام ردیف های پرس و جو به دست آمده انجام می شود. اگر پرس و جو حاوی یک بند GROUP BY باشد، هر مجموعه از ردیف های مشخص شده در عبارت GROUP BY یک گروه را تشکیل می دهد و توابع انبوه اجرا می شوند. برای هر گروه جداگانه.

مثالی را با جدول درس ها در نظر بگیرید:

مثال:

مهم:بنابراین، در نتیجه استفاده از GROUP BY، تمام ردیف های خروجی پرس و جو به گروه هایی تقسیم می شوند که با ترکیبات یکسانی از مقادیر در این ستون ها مشخص می شوند (یعنی توابع جمع برای هر گروه به طور جداگانه انجام می شود).

باید در نظر داشت که وقتی توسط یک فیلد حاوی مقادیر NULL گروه بندی می شوند، همه این رکوردها در یک گروه قرار می گیرند.

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

داشتن بیانیه SQL

عبارت HAVING در SQL برای اعتبارسنجی مقادیر مورد نیاز است. که با استفاده از تابع تجمیع پس از گروه بندی به دست می آیند(پس از استفاده از GROUP BY). چنین چکی نمی توان در یک بند WHERE گنجانده شود.

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

با مقدار ستون نظم و انضباط. ما 4 گروه دریافت می کنیم که می توانیم مقادیر گروهی را برای آنها محاسبه کنیم، به عنوان مثال تعداد تاپل ها در یک گروه، حداکثر یا حداقل مقدار ستون Score. جدول 5.7. توابع مجموع

عملکرد	نتیجه
شمردن	تعداد سطرها یا مقادیر فیلد غیر خالی که پرس و جو انتخاب کرده است
جمع	مجموع تمام مقادیر انتخاب شده برای این فیلد
AVG	میانگین حسابی همه مقادیر انتخاب شده در این فیلد
MIN	کوچکترین مقدار از تمام مقادیر انتخاب شده برای این فیلد
حداکثر	بزرگترین از همه مقادیر انتخاب شده برای این فیلد

R1
	نام و نام خانوادگی	انضباط	مقطع تحصیلی
گروه 1	پتروف F.I.	پایگاه داده	5
	K. A. Sidorov	پایگاه داده	4
	میرونوف A.V.	پایگاه داده	2
	استپانووا K.E.	پایگاه داده	2
	کریلوا T.S.	پایگاه داده	5
	ولادیمیروف V.A.	پایگاه داده	5
گروه 2	K. A. Sidorov	نظریه اطلاعات	4
	استپانووا K.E.	نظریه اطلاعات	2
	کریلوا T.S.	نظریه اطلاعات	5
	میرونوف A.V.	نظریه اطلاعات	خالی
گروه 3	تروفیموف P.A.	شبکه و مخابرات	4
	ایوانووا E.A.	شبکه و مخابرات	5
	N. V. Utkina	شبکه و مخابرات	5
گروه 4	ولادیمیروف V.A.	زبان انگلیسی	4
	تروفیموف P.A.	زبان انگلیسی	5
	ایوانووا E.A.	زبان انگلیسی	3
	پتروف F.I.	زبان انگلیسی	5

توابع مجموعمانند نام فیلدها در دستور SELECT استفاده می شود، با یک استثنا: آنها نام فیلد را به عنوان آرگومان می گیرند. فقط فیلدهای عددی را می توان با توابع SUM و AVG استفاده کرد. هر دو فیلد عددی و کاراکتری را می توان با توابع COUNT، MAX و MIN استفاده کرد. هنگامی که با فیلدهای کاراکتر استفاده می شود، MAX و MIN آنها را به معادل ASCII ترجمه کرده و بر اساس حروف الفبا پردازش می کنند. برخی از DBMS ها اجازه استفاده از توده های تودرتو را می دهند، اما این یک انحراف از استاندارد ANSI با تمام عواقب بعدی است.

به عنوان مثال، می توانید تعداد دانش آموزانی را که در هر رشته امتحانات قبول شده اند محاسبه کنید. برای انجام این کار، باید یک پرس و جو گروه بندی شده توسط فیلد "Discipline" را اجرا کنید و در نتیجه نام رشته و تعداد ردیف های گروه را برای این رشته نمایش دهید. استفاده از کاراکتر * به عنوان آرگومان برای تابع COUNT به این معنی است که تمام خطوط گروه شمارش می شوند.

رشته R1 را انتخاب کنید، تعداد (*) از R1 GROUP BY R1 Discipline

نتیجه:

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

نتیجه را می گیریم:

در این مورد، خط با دانش آموز

میرونوف A.V.	نظریه اطلاعات	خالی

قبل از گروه بندی در مجموعه تاپل ها قرار نمی گیرد، بنابراین تعداد تاپل ها در گروه برای رشته " نظریه اطلاعات"1 کمتر خواهد بود.

قابل بکارگیری است توابع کلهمچنین بدون عملیات گروه بندی مقدماتی، در این حالت کل رابطه به عنوان یک گروه در نظر گرفته می شود و برای این گروه می توان یک مقدار برای هر گروه محاسبه کرد.

با مراجعه مجدد به پایگاه داده Session (جدول R1، R2، R3)، تعداد امتحانات با موفقیت را خواهیم یافت:

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

نتیجه:

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

نتیجه:

ما نمی توانیم استفاده کنیم توابع کلدر عبارت WHERE زیرا محمول ها بر حسب یک ردیف ارزیابی می شوند و توابع کل- از نظر گروه های خط.

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

بیایید یک پرس و جو بسازیم که گروه هایی را که در آنها بیش از یک دس در یک رشته در امتحانات به دست آمده را نشان دهد:

در ادامه، به عنوان مثال، ما نه با پایگاه داده "Session"، بلکه با پایگاه داده "Bank" کار خواهیم کرد، که از یک جدول F تشکیل شده است، که رابطه F حاوی اطلاعات مربوط به حساب ها را در شاخه های یک معین ذخیره می کند. بانک:

F = (N، نام کامل، شعبه، تاریخ افتتاح، تاریخ بسته شدن، موجودی)؛ Q = (شعبه، شهر)؛

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

برای مثال فرض کنید می خواهیم کل موجودی حساب را در شعب پیدا کنیم. می توانید با انتخاب SUM (Remaining) از جدول برای هر شاخه، یک پرس و جو جداگانه برای هر یک از آنها ایجاد کنید. با این حال، GROUP BY به شما این امکان را می دهد که همه آنها را در یک دستور قرار دهید:

SELECT شعبه، جمع (موجودی) FROM F GROUP BY BY;

GROUP BY اعمال می شود توابع کلبه طور مستقل برای هر گروه، با مقدار فیلد Branch مشخص می شود. این گروه از خطوطی با همان مقدار فیلد Branch تشکیل شده است و

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

شمردن- تعداد مقادیر موجود در ستون مشخص شده را برمی گرداند.

جمع- مجموع مقادیر در ستون مشخص شده را برمی گرداند.

AVG- مقدار متوسط ​​را در ستون مشخص شده برمی گرداند.

MIN- حداقل مقدار را در ستون مشخص شده برمی گرداند.

حداکثر- حداکثر مقدار را در ستون مشخص شده برمی گرداند.

همه این توابع بر روی مقادیر موجود در یک ستون از جدول عمل می کنند و یک مقدار واحد را برمی گردانند. توابع COUNT، MIN و MAX برای فیلدهای عددی و غیر عددی اعمال می شوند، در حالی که توابع SUM و AVG فقط در مورد فیلدهای عددی قابل استفاده هستند. به استثنای COUNT (*)، هنگام ارزیابی نتایج هر یک از توابع، ابتدا همه مقادیر تهی حذف می شوند، پس از آن عملیات مورد نیاز فقط برای مقادیر باقیمانده ستون غیر تهی اعمال می شود. گزینه COUNT (*) استفاده ویژه ای از تابع COUNT است - هدف آن شمارش تمام ردیف های یک جدول است، خواه حاوی مقادیر خالی، مقادیر تکراری یا هر مقدار دیگری باشد. اگر می‌خواهید قبل از استفاده از تابع جمع، مقادیر تکراری را حذف کنید، کلمه کلیدی DISTINCT را در جلوی نام ستون در تعریف تابع قرار دهید. استاندارد ISO به استفاده از کلمه کلیدی ALL اجازه می دهد تا به صراحت نشان دهد که حذف مقادیر تکراری لازم نیست، اگرچه این کلمه کلیدی به طور پیش فرض در صورتی که واجد شرایط دیگری مشخص نشده باشد، در نظر گرفته می شود. کلمه کلیدی DISTINCT برای توابع MIN و MAX بی معنی است. با این حال، استفاده از آن می تواند بر نتایج اجرای توابع SUM و AVG تأثیر بگذارد، بنابراین باید از قبل در نظر بگیرید که آیا باید در هر مورد خاص وجود داشته باشد یا خیر. علاوه بر این، کلمه کلیدی DISTINCT را می توان حداکثر یک بار در هر درخواست مشخص کرد.

باید توجه داشت که توابع انبوه را فقط می توان در یک لیست SELECT و در یک عبارت HAVING استفاده کرد (به بخش 5.3.4 مراجعه کنید). در تمام موارد دیگر، استفاده از این توابع نامعتبر است. اگر لیست SELECT حاوی یک تابع تجمع باشد و متن پرس و جو حاوی یک بند GROUP BY که تجمیع داده ها را در گروه ها ارائه می کند، نباشد، هیچ یک از عناصر لیست SELECT نمی تواند شامل هیچ مرجع ستونی باشد، مگر اینکه از این ستون به عنوان پارامتر استفاده شود. تابع تجمع به عنوان مثال، درخواست زیر نامعتبر است:

انتخاب کنیدکارکنان نه،شمردن (حقوق)

از جانبکارکنان؛

خطا این است که این درخواست شامل ساختار نیست دسته بندی بر اساسو دسترسی به ستون staffNo در لیست SELECT بدون استفاده از تابع جمع انجام می شود.

مثال 13. با استفاده از تابع COUNT (*).تعیین کنید که چند ملک اجاره ای دارای نرخ اجاره بیش از 350 پوند در ماه هستند،

COUNT را انتخاب کنید(*) شمارش AS

از جانبPropertyForRent

جایی کهاجاره > 350;

بند WHERE محدود به شمارش موارد اجاره ای است که بیش از 350 پوند در ماه است. تعداد کل املاک اجاره ای که شرایط مشخص شده را برآورده می کنند را می توان با استفاده از تابع جمع آوری COUNT تعیین کرد. نتایج اجرای پرس و جو در جدول ارائه شده است. 23.

جدول 23

شمردن

مثال 14. با استفاده از تابع COUNT (DISTINCT).مشخص کنید که در می 2001 مشتریان چند ملک اجاره ای مختلف را مشاهده کرده اند.

تعداد را انتخاب کنید (متمایزشماره ملک) شمارش AS

از جانبدر حال مشاهده

مجدداً، محدود کردن نتایج پرس و جو به تجزیه و تحلیل تنها اجاره نامه هایی که در می 2001 مشاهده شده اند، از طریق استفاده از یک بند WHERE به دست می آید. تعداد کل اشیاء بررسی شده که شرایط مشخص شده را برآورده می کنند را می توان با استفاده از تابع COUNT جمع تعیین کرد. با این حال، از آنجایی که یک شیء مشابه را می توان چندین بار توسط کلاینت های مختلف مشاهده کرد، باید کلمه کلیدی DISTINCT را در تعریف تابع مشخص کنید تا مقادیر تکراری را از محاسبه حذف کنید. نتایج اجرای پرس و جو در جدول ارائه شده است. 24.

جدول 24

مثال 16. استفاده از توابع MIN، MAXnAVG.مقدار حداقل، حداکثر و متوسط ​​دستمزد را محاسبه کنید.

حداقل را انتخاب کنید(حقوق) ماننددقیقه، حداکثر(حقوق) مانندحداکثر، AVG(حقوق) مانندمیانگین

از جانبکارکنان؛

در این مثال، شما باید اطلاعات مربوط به تمام پرسنل شرکت را پردازش کنید، بنابراین نیازی به استفاده از بند WHERE ندارید. مقادیر مورد نیاز را می توان با استفاده از توابع MIN، MAX و AVG اعمال شده در ستون حقوق جدول کارکنان محاسبه کرد. نتایج اجرای پرس و جو در جدول ارائه شده است. 26.

جدول 26.

نتیجه پرس و جو

دقیقه	حداکثر	میانگین
9000.00	30000.00	17000.00

نتایج گروه بندی (گروه بر اساس بند).نمونه‌های خلاصه داده‌های بالا مشابه ردیف‌های خلاصه معمولاً در انتهای گزارش‌ها هستند. در مجموع، تمام داده های تفصیلی گزارش در یک خط خلاصه فشرده شده است. با این حال، اغلب لازم است که جمع های فرعی در گزارش ها تشکیل شود. برای این منظور می توان یک بند GROUP BY در عبارت SELECT مشخص کرد. پرس و جوی حاوی بند GROUP BY نامیده می شود پرس و جو گروه بندیزیرا داده های حاصل از عملیات SELECT را گروه بندی می کند و سپس یک ردیف خلاصه برای هر گروه جداگانه ایجاد می کند. ستون های فهرست شده در بند GROUP BY نامگذاری می شوند ستون های گروه بندی شدهاستاندارد ISO ایجاب می کند که بندهای SELECT و GROUP BY از نزدیک مرتبط باشند. هنگام استفاده از بند GROUP BY در یک عبارت SELECT، هر آیتم لیست در لیست SELECT باید دارای تنها معنی برای کل گروهعلاوه بر این، یک بند SELECT فقط می تواند شامل انواع عناصر زیر باشد:

نام ستون ها؛

توابع جمع آوری؛

ثابت ها

عباراتی که شامل ترکیبی از موارد فوق می شود.

همه نام ستون های فهرست شده در لیست SELECT نیز باید در عبارت GROUP BY ظاهر شوند، مگر اینکه نام ستون فقط در تابع جمع استفاده شود. برعکس همیشه صادق نیست - عبارت GROUP BY ممکن است حاوی نام ستون هایی باشد که در لیست SELECT نیستند. اگر عبارت WHERE همراه با عبارت GROUP BY استفاده شود، ابتدا پردازش می‌شود و تنها ردیف‌هایی که شرایط جستجو را برآورده می‌کنند گروه‌بندی می‌شوند. استاندارد ISO مشخص می کند که هنگام انجام گروه بندی، تمام مقادیر از دست رفته به عنوان یکسان در نظر گرفته می شوند. اگر دو ردیف جدول در ستون گروه بندی یکسان حاوی مقادیر تهی و مقادیر یکسان در سایر ستون های گروه بندی شده غیرخالی باشند، در همان گروه قرار می گیرند.

مثال 17. استفاده از بند GROUP BY.تعداد پرسنل شاغل در هر یک از بخش های شرکت و همچنین کل حقوق آنها را تعیین کنید.

انتخاب کنیدشعبه نه، شمردن(خدمت کارمندان) مانندشمردن، جمع(حقوق) مانندمجموع

از جانبکارکنان

دسته بندی بر اساسشماره شعبه

سفارش توسطشماره شعبه

لازم نیست نام ستون‌ها staffNo و salary را در بند GROUP BY درج کنید، زیرا آنها فقط در لیست SELECT با توابع جمع ظاهر می‌شوند. در عین حال، ستون branchNo در لیست عبارت SELECT با هیچ تابع تجمعی مرتبط نیست و به همین دلیل باید در بند GROUP BY مشخص شود. نتایج اجرای پرس و جو در جدول ارائه شده است. 27.

جدول 27

نتیجه پرس و جو

شماره شعبه	شمردن	مجموع
B003		54000.00
B005		39000.00
B007		9000.00

از نظر مفهومی، هنگام پردازش این درخواست، مراحل زیر انجام می شود.

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

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

3. ردیف های به دست آمده از جدول به دست آمده به ترتیب صعودی شماره شاخه نشان داده شده در ستون branchNo مرتب شده اند.

شماره شعبه	شماره کارکنان	حقوق
V00Z	SG37	12000.00
V00Z	SG14	18000.00
V00Z	SG5	24000.00
B005	SL21	30000.00
B005	SL41	9000.00
B007	SA9	9000.00

COUNT (شماره کارکنان)	SUM (حقوق)
	54000.00
	39000.00
	9000.00

برنج. 1. سه گروه از رکوردهای ایجاد شده در هنگام اجرای یک پرس و جو

استاندارد SQL به سوالات فرعی اجازه می دهد تا در لیست SELECT قرار گیرند. بنابراین، پرس و جو فوق را می توان به صورت زیر نیز نمایش داد:

انتخاب کنیدشعبه شماره، (انتخاب تعداد (انتخاب کارکنان)مانند شمردن

از جانبکارکنان اس

جایی کهs.branchNo = b.branchNo)،

(انتخاب مبلغ (حقوق) به عنوان جمع

از جانبکارکنان اس

جایی کهs.branchNo = b.branchNo)

از جانبشعبه ب

سفارش توسطشماره شعبه

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

محدودیت های گروه بندی (بند HAVING).عبارت HAVING قرار است همراه با عبارت GROUP BY برای تعیین محدودیت‌های مشخص شده برای انتخاب آن‌ها استفاده شود. گروه ها،که در جدول پرس و جو به دست آمده قرار خواهد گرفت. اگرچه جملات HAVING و WHERE دارای نحو مشابهی هستند، اما هدف آنها متفاوت است. عبارت WHERE برای انتخاب ردیف های جداگانه برای پر کردن جدول پرس و جو به دست آمده طراحی شده است، در حالی که عبارت HAVING برای انتخاب استفاده می شود. گروه ها،در جدول پرس و جو به دست آمده قرار می گیرد. استاندارد ISO نیاز دارد که نام ستون‌های مورد استفاده در عبارت HAVING در فهرست موارد GROUP BY ظاهر شود یا در توابع جمع استفاده شود. در عمل، عبارات جستجو در یک عبارت HAVING همیشه حداقل یک تابع مجموع را شامل می شود. در غیر این صورت، این شرایط جستجو باید در یک عبارت WHERE قرار گیرد و برای انتخاب ردیف‌های جداگانه اعمال شود. (به یاد داشته باشید که توابع انبوه را نمی توان در یک عبارت WHERE استفاده کرد.) عبارت HAVING بخشی ضروری از زبان SQL نیست - هر درخواستی که با استفاده از عبارت HAVING نوشته شود می تواند بدون استفاده از آن به شکل متفاوتی نمایش داده شود.

مثال 18. استفاده از ساختار HAVING.برای هر بخش از شرکت با بیش از یک پرسنل، تعداد کارکنان و میزان حقوق آنها را تعیین کنید.

انتخاب کنیدشعبه نه، COUN T (تعداد کارمندان) مانندشمردن، جمع(حقوق) مانندمجموع

از جانبکارکنان

دسته بندی بر اساسشماره شعبه

داشتن تعداد(شماره کارکنان)> 1

سفارش توسطشماره شعبه

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

جدول 28

شاخه بدون مجموع شمارش

V00Z 3 54000.00

B005 2 39000.00

سوالات فرعیدر این بخش، استفاده از دستورات SELECT کامل تعبیه شده در بدنه دستور SELECT دیگر را مورد بحث قرار خواهیم داد. خارجیدستور SELECT (دوم) از نتیجه اجرا استفاده می کند درونی؛ داخلی(اول) اپراتور برای تعیین محتوای نتیجه نهایی کل عملیات. پرس و جوهای داخلی را می توان در عبارت WHERE و HAVING دستور SELECT بیرونی یافت - در این مورد، آنها نامگذاری می شوند. سوالات فرعی،یا پرس و جوهای تو در توعلاوه بر این، عبارات SELECT داخلی را می توان در دستورات INSERT، UPDATE و DELETE استفاده کرد. . سه نوع سوال فرعی وجود دارد.

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

استعلام فرعی رشتهمقادیر چندین ستون را در یک جدول برمی گرداند، اما به صورت یک سطر. پرس و جوی فرعی رشته ای را می توان هر جا که از سازنده مقدار رشته استفاده می شود استفاده کرد - معمولاً گزاره ها. گونه ای از زیرپرس و جوی رشته در مثال 15 نشان داده شده است.

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

مثال 19. استفاده از پرس و جو فرعی با بررسی برابری.آرایش لیستی از پرسنل شاغل در شعبه شرکت واقع در خیابان اصلی 463.

انتخاب کنید

از جانبکارکنان

جایی کهشعبه شماره = (انتخاب شاخه شماره

از جانبشاخه

جایی کهstreet = "163 Main S t");

بیانیه SELECT داخلی (SELECT branchNo FROM Branch ...) برای تعیین شماره شعبه شرکت واقع در خیابان اصلی 163 استفاده می شود. (فقط یک چنین دپارتمان در یک شرکت وجود دارد، بنابراین این مثال نمونه ای از یک زیرپرس و جوی اسکالر است.) هنگامی که شماره دپارتمان مورد نظر به دست آمد، یک زیرپرسی خارجی برای بازیابی جزئیات کارگران آن بخش اجرا می شود. به عبارت دیگر، عبارت SELECT داخلی جدولی متشکل از یک مقدار واحد "BOOV. این شماره شعبه شرکت واقع در" 163 Main St1 را برمی گرداند. در نتیجه، دستور SELECT بیرونی به صورت زیر می شود:

انتخاب کنیدstaffNo، fName، IName، موقعیت

از جانبکارکنان

جایی کهbranchNo = "B0031;

نتایج این پرس و جو در جدول ارائه شده است. 29.

جدول 29

نتیجه پرس و جو

شماره کارکنان	fName	INname	موقعیت
SG37	ان	راش	دستیار
SG14	دیوید	آب کم عمق	سرپرست
SG5	سوزان	نام تجاری	مدیر

پرسش فرعی ابزاری برای ایجاد یک جدول موقت است که محتویات آن توسط یک اپراتور خارجی بازیابی و پردازش می شود. یک پرس و جو فرعی را می توان بلافاصله پس از مقایسه عملگرها مشخص کرد (یعنی عملگرها =<, >, <=, >=, <>) در یک بند WHERE یا HAVING. متن استعلام فرعی باید در داخل پرانتز قرار گیرد.

مثال 20.استفاده از سوالات فرعی با توابع جمع. همه کارکنان با حقوق متوسط ​​را فهرست کنید و مشخص کنید چقدر حقوق آنها از میانگین حقوق شرکت بیشتر است.

انتخاب کنیدتعداد کارکنان، fName، IName، موقعیت، حقوق - ( AVG را انتخاب کنید(حقوق) از جانبکارکنان) مانند salDiff

از جانبکارکنان

جایی کهحقوق > ( AVG را انتخاب کنید(حقوق) از جانب S t a f f);

لازم به ذکر است که نمی توان مستقیماشامل در عبارت پرس و جو"WHERE salary> AVG (حقوق)"، از زمان استفاده از تجمیعتوابع در بند WHERE ممنوع هستند. برای دستیابی به نتیجه دلخواه، یک پرسش فرعی ایجاد کنید که میانگین حقوق سالانه را محاسبه می کند و سپس از آن در بیانیه SELECT خارجی برای بازیابی اطلاعات مربوط به آن دسته از کارمندان شرکت که حقوق آنها بالاتر از این میانگین است، استفاده کنید. به عبارت دیگر، استعلام فرعی میانگین حقوق و دستمزد شرکت را در سال برمی گرداند که 17000 پوند است.

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

انتخاب کنیدتعداد کارکنان، fName، IName، موقعیت، حقوق - 17000 مانند salDiff

از جانبکارکنان

جایی کهحقوق> 17000;

نتایج اجرای پرس و جو در جدول ارائه شده است. سی

جدول 30.

نتیجه پرس و جو

شماره کارکنان	fName	INname	موقعیت	salDiff
SL21	جان	سفید	مدیر	13000.00
SG14	دیوید	آب کم عمق	سرپرست	1000.00
SG5	سوزان	نام تجاری	مدیر	7000.00

برای سوالات فرعی اعمال کنیدقوانین زیر و محدودیت ها

1. بند ORDER BY نباید در پرس و جوهای فرعی استفاده شود، اگرچه ممکن است در عبارت SELECT بیرونی ظاهر شود.

2. لیست SELECT یک پرسش فرعی باید متشکل از نام ستون‌ها یا عبارات متشکل از آنها باشد، مگر اینکه از کلمه کلیدی EXISTS در جستار فرعی استفاده شود.

3. به طور پیش‌فرض، نام ستون‌ها در یک پرسش فرعی به جدولی که در عبارت FROM عبارت جستجوی فرعی نامگذاری شده است، اشاره دارد. با این حال، می توانید با استفاده از نام ستون های واجد شرایط (همانطور که در زیر توضیح داده شده است) به ستون های جدول مشخص شده در عبارت FROM پرس و جو بیرونی نیز مراجعه کنید.

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

انتخاب کنید

از جانبکارکنان

جایی که(انتخاب AVG (حقوق) از کارکنان)< salary;

مثال 21... سوالات فرعی تو در تو و استفاده از گزاره IN. لیستی از املاک اجاره ای که کارمندان در شعبه واقع در خیابان اصلی 163 مسئول آن هستند تهیه کنید.

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

از جانبPropertyForRent

فصل 5... زبان SQL: دستکاری داده ها 189

جایی کهstaffNo IN (انتخاب کارکنان شماره

از جانبکارکنان

جایی کهbrancliNo = (انتخاب شاخه شماره

از جانبشاخه

جایی کهstreet = "163 Main S t"));

اولین پرس و جو، داخلی ترین، برای تعیین شماره شعبه شرکت واقع در خیابان اصلی 463 در نظر گرفته شده است. دوم، پرس و جو میانی، اطلاعاتی را در مورد پرسنل شاغل در این بخش واکشی می کند. در این مورد، بیش از یک ردیف داده است. انتخاب شده است و بنابراین در پرس و جو خارجی نمی توانید از عملگر مقایسه = استفاده کنید. به جای آن باید از کلمه کلیدی IN استفاده کنید. پرس و جو بیرونی اطلاعاتی را در مورد اجاره نامه هایی که کارکنان شرکت مسئول آن هستند بازیابی می کند و داده هایی در مورد آنها در نتیجه به دست آمده است. نتایج پرس و جو در جدول 31 نشان داده شده است.

جدول 31

نتیجه پرس و جو

شماره ملک	خیابان	شهر	کد پستی	نوع	اتاق ها	اجاره دادن
PG16	5 نوامبر دکتر	گلاسکو	G129AX	تخت
PG36	2 Manor Rd	گلاسکو	G324QX	تخت
PG21	خیابان دیل 18	گلاسکو	G12	خانه

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

مثال 22.استفاده از کلمات کلیدی ANY و SOME. همه کارمندانی را پیدا کنید که حقوق آنها حداقل از حقوق بیشتر استیکی کارمند یکی از شعبه های شرکت به شماره «بوز».

انتخاب کنیدstaffNo، fName، IName، موقعیت، حقوق

از جانبکارکنان

جایی کهحقوق> SOME (انتخاب حقوق

از جانبکارکنان

جایی کهbranchNo = "B003");

اگرچه این استعلام را می توان با استفاده از یک استعلام فرعی که حداقل دستمزد کارکنان بخش را با شماره "BOOZ" تعیین می کند، نوشت، پس از آن پرس و جو فرعی خارجی می تواند اطلاعاتی در مورد تمام کارکنان شرکتی که حقوق آنها بیشتر از این مقدار است را انتخاب کند (به مثال مراجعه کنید. 20) رویکرد دیگری نیز امکان پذیر است که شامل استفاده از کلمات کلیدی SOME / ANY است. در این مورد، پرس و جو داخلی مجموعه ای از مقادیر را ایجاد می کند (12000، 18000، 24000) و پرس و جو بیرونی اطلاعات مربوط به آن دسته از کارمندان را انتخاب می کند که حقوق آنها از هر یک از مقادیر موجود در این بیشتر است.

مجموعه (در واقع بیش از مقدار حداقل - 12000). این روش جایگزین را می توان طبیعی تر از تعریف حداقل دستمزد در استعلام فرعی در نظر گرفت. اما در هر صورت، همان نتایج پرس و جو ایجاد می شود که در جدول ارائه شده است. 32 .

جدول 32

نتیجه پرس و جو

شماره کارکنان	fName	INname	موقعیت	حقوق
SL21	جان	سفید	مدیر	30000.00
SG14	دیوید	آب کم عمق	سرپرست	18000.00
SG5	سوزان	نام تجاری	مدیر	24000.00

مثال 23.با استفاده از کلمه کلیدی ALL. همه کارمندانی که دستمزدشان از دستمزد هر کارمندی در شعبه بوز شرکت بیشتر است را پیدا کنید.

انتخاب کنیدstaffNo, fName, INarae, موقعیت, حقوق

از جانبکارکنان

جایی کهحقوق> همه(انتخاب حقوق

از جانبکارکنان

جایی کهbranchNo = "BOG3");

به طور کلی این درخواست مشابه درخواست قبلی است. و در این مورد، می توان از یک استعلام فرعی استفاده کرد که حداکثر ارزش حقوق کارکنان بخش را با شماره "WOOZ" تعیین می کند، و سپس، با استفاده از یک پرس و جو خارجی، اطلاعاتی را در مورد همه کارکنان شرکت که حقوق آنها بیشتر است انتخاب کرد. این مقدار با این حال، در این مثال، رویکرد کلمه کلیدی ALL انتخاب شده است. نتایج اجرای پرس و جو در جدول ارائه شده است. 33 .

جدول 33

نتیجه پرس و جو

شماره کارکنان	INname	fName	موقعیت	حقوق
SL21	سفید	جان	مدیر	30000,00

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

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

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

انتخاب کنیدc.clientNo، fName، IName، propertyNo، نظر

از جانبمشتری c، مشاهده v

جایی کهc.clientNo = v.clientNo;

این گزارش به اطلاعاتی از جدول Client و Viewing جدول نیاز دارد، بنابراین در هنگام ساخت پرس و جو از مکانیسم اتصال جدول استفاده خواهیم کرد. بند SELECT تمام ستون هایی را که باید در جدول پرس و جو به دست آمده قرار گیرند را فهرست می کند. توجه داشته باشید که ستون شماره مشتری (clientNo) نیاز به اصلاح دارد، زیرا چنین ستونی ممکن است در جدول دیگری که در پیوستن شرکت می کند نیز وجود داشته باشد. بنابراین، لازم است به صراحت مشخص کنیم که به کدام مقادیر جدول علاقه داریم. (در این مثال، به خوبی می‌توانید مقادیر ستون clientNo را از جدول Viewing انتخاب کنید). نام با پیشوند نام ستون با نام جدول مربوطه (یا نام مستعار آن) مشخص می شود. در مثال ما، مقدار "c" استفاده شده است که به عنوان نام مستعار برای جدول Client مشخص شده است. برای تشکیل ردیف های به دست آمده، از ردیف هایی از جداول منبع استفاده می شود که دارای یک مقدار یکسان در ستون clientNo هستند. این شرط با تنظیم شرط جستجو با.clientNo = v.clientNo تعیین می شود. ستون های مشابه در جداول اصلی نامیده می شوند ستون های سازگارعملیات توصیف شده معادل عملیات است برابری می پیونددجبر رابطه ای نتایج اجرای پرس و جو در جدول ارائه شده است. 34.

جدول 34

نتیجه پرس و جو

شماره مشتری	fName	INname	شماره ملک	اظهار نظر
CR56	الین	استوارت	PG36
CR56	الین	استوارت	PA14	خیلی کوچک
CR56	الین	استوارت	PG4
CR62	مريم	Tregear	PA14	اتاق غذاخوری ندارد
CR76	جان	کی	PG4	خیلی از راه دور

اغلب، پرس و جوهای چند جدولی روی دو جدول انجام می شود که با یک رابطه یک به چند (1: *) یا والدین-فرزند به هم متصل شده اند. در مثال بالا، که شامل دسترسی به جداول Client و Viewing می‌شود، دومی با چنین رابطه‌ای به هم متصل شده‌اند. هر ردیف در جدول مشاهده (فرزند) فقط به یک ردیف در جدول مشتری (والد) پیوند داده می شود، در حالی که همان ردیف در جدول مشتری (والد) قابل پیوند است.

با تعداد زیادی ردیف از جدول مشاهده (کودک). جفت ردیف هایی که هنگام اجرای پرس و جو ایجاد می شوند، نتیجه همه ترکیبات معتبر ردیف ها در جداول فرزند و والد هستند. بخش 3.2.5 به تفصیل توضیح داد که چگونه در یک پایگاه داده رابطه ای، کلیدهای اصلی و خارجی جداول یک رابطه "والد-فرزند" ایجاد می کنند. جدول حاوی کلید خارجی معمولاً یک فرزند است، در حالی که جدول حاوی کلید اصلی همیشه والد خواهد بود. برای استفاده از رابطه والد-فرزند در پرس و جوی SQL، باید شرایط جستجویی را مشخص کنید که در آن کلیدهای خارجی و اصلی با هم مقایسه شوند. مثال 24 کلید اصلی جدول Client (با ClientNo) را با کلید خارجی جدول Viewing (v. ClientNo) مقایسه می کند.

استاندارد SQL علاوه بر این، راه های زیر را برای تعریف یک اتصال داده شده ارائه می دهد:

از جانبمشتری با پیوستنمشاهده v بر c.clientNo = v.clientNo

از جانبمشتری جی OINدر حال مشاهده استفاده كردنشماره مشتری

از جانبمشتری طبیعی بپیوندیددر حال مشاهده

در هر مورد، بند FROM جایگزین بند اصلی FROM و WHERE می شود. با این حال، گزینه اول یک جدول با دو ستون ClientNo یکسان ایجاد می کند، در حالی که در دو مورد دیگر جدول حاصل فقط شامل یک ستون clientNo خواهد بود.

مثال 25.مرتب سازی نتایج پیوستن به جداول. برای هر شعبه شرکت، شماره پرسنل و اسامی کارکنانی که مسئولیت هر گونه تاسیسات استیجاری را بر عهده دارند و همچنین امکانات مربوط به آن را ذکر کنید.

که آنها پاسخ می دهند.

انتخاب کنیدs.branchNo, s.staffNo, fName, IName, milkNo

از جانبStaff s, PropertyForRent p

جایی کهs.staffNo = p.staffNo

سفارش توسطs.branchNo, s.staffNo, milkNo;

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

جدول 35

نتیجه پرس و جو

شماره شعبه	شماره کارکنان	fName	INname	شماره ملک
که	SG14	دیوید	آب کم عمق	PG16
که	SG37	ان	راش	PG21
که	SG37	ان	راش	PG36
BOO5	SL41	مريم	لی	PL94
SBI7	SA9	جولی	هاو	PA14

مثال 26.ادغام سه جدول برای هر شعبه شرکت، شماره پرسنل و اسامی کارکنان مسئول هرگونه تاسیسات استیجاری را با ذکر شهر محل شعبه و شماره امکاناتی که هر کارمند مسئولیت آن را بر عهده دارد، ذکر کنید.

انتخاب کنید b.branchNo, b.city, s.staffNo, fName, IName, ملک شماره

از جانبشعبه ب، کارکنان، املاک برای اجاره ص

جایی که b.branchNo = s.branchNo و s.staffNo = p.staffNo

سفارش توسط b.branchNo, s.staffNo, milkNo;

ستون های سه جدول منبع - Branch، Staff و PropertyForRent - باید در جدول به دست آمده قرار گیرند، بنابراین شما باید این جداول را در جستجوی خود بپیوندید. جداول Branch و Staff را می توان با استفاده از بند b.branchNo = * s .branchNo پیوند داد که شعبه های شرکت را به پرسنل شاغل در آنها مرتبط می کند. جداول Staff و PropertyForRent را می توان با استفاده از شرط s.staffNo = p.staffNo پیوند داد. در نتیجه، هر کارمند با املاک اجاره ای که مسئولیت آنها را بر عهده دارد، در ارتباط خواهد بود. نتایج اجرای پرس و جو در جدول ارائه شده است. 36.

جدول 36

نتایج پرس و جو

شماره شعبه	شهر	staffMo	fName	INname	شماره ملک
B003	گلاسکو	SG14	دیوید	آب کم عمق	PG16
B003	گلاسکو	SG37	ان	راش	PG21
B003	گلاسکو	SG37	ان	راش	PG36
B005	لندن	SL41	جولی	لی	PL94
B007	آبردین	SA9	مريم	هاو	PA14

توجه داشته باشید که استاندارد SQL به شما امکان می دهد از فرمول بندی جایگزینی از بندهای FROM و WHERE استفاده کنید:

از جانب(شعبه b عضویت در کارکنان با استفاده از شماره شعبه) مانندلیسانس

پیوستنPropertyForRent p استفاده كردنشماره کارکنان

مثال 27.گروه بندی بر اساس چندین ستون تعداد املاک اجاره ای که هر کارمند مسئول آن است را تعیین کنید.

انتخاب کنیدs.branchNo, S.staffNo, شمردن(*) مانندشمردن

FROM Staff s, PropertyForRent p

WHERE S.staffNo = p.staffNo

دسته بندی بر اساسs.branchNo, s.staffNo

سفارش توسطs.branchNo, s.staffNo;

برای تهیه گزارش مورد نیاز، ابتدا باید دریابید که کدام یک از کارکنان شرکت مسئول اشیاء اجاره شده است. این کار را می توان با پیوستن به جداول Staff و PropertyForRent توسط ستون staffNo در بندهای FROM / WHERE حل کرد. سپس لازم است گروه هایی متشکل از شماره بخش و شماره پرسنل کارکنان آن تشکیل شود که بند GROUP BY برای آنها اعمال شود. در نهایت، جدول به دست آمده باید با تعیین یک عبارت ORDER BY مرتب شود. نتایج اجرای پرس و جو در جدول ارائه شده است. 37.

جدول 37

نتیجه پرس و جو

شماره شعبه	شماره کارکنان	شمردن
V00Z	SG14
V00Z	SG37
B005	SL41
B007	SA9

ایجاد ارتباطات. Join زیرمجموعه ای از ترکیب کلی تری از داده های دو جدول است که نامیده می شود دکارتی... حاصل ضرب دکارتی دو جدول، جدول دیگری است که از تمام جفت‌های ممکن سطر تشکیل می‌شود که هر دو جدول را تشکیل می‌دهند. مجموعه ستون‌ها در جدول به‌دست‌آمده، تمام ستون‌های جدول اول و سپس تمام ستون‌های جدول دوم است. اگر یک پرس و جو را در دو جدول بدون تعیین عبارت WHERE وارد کنید، نتیجه اجرای پرس و جو در محیط SQL حاصل ضرب دکارتی این جداول خواهد بود. علاوه بر این، استاندارد ISO فرمت خاصی را برای عبارت SELECT ارائه می دهد که به شما امکان می دهد حاصل ضرب دکارتی دو جدول را محاسبه کنید:

SELECT (* j columnList]

از جدولNamel CROSS JOINCaNeulte2

دوباره مثالی را در نظر بگیرید که در آن جداول کلاینت و Viewing با استفاده از ستون مشترک ClientNo به هم متصل شده اند.هنگام کار با جداول که محتویات آن در جدول آورده شده است. در 3.6 و 3.8، حاصل ضرب دکارتی این جداول 20 سطر خواهد بود (4 سطر در جدول Client x 5 سطر در جدول مشاهده = 20 سطر). این معادل صدور عبارت مورد استفاده در مثال 5.24 است، اما بدون اعمال عبارت WHERE. روش تولید جدول حاوی نتایج به هم پیوستن دو جدول با استفاده از دستور SELECT به شرح زیر است.

1. یک حاصل ضرب دکارتی از جداول مشخص شده در عبارت FROM تشکیل می شود.

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

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

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

5. اگر کوئری در حال اجرا حاوی یک عبارت ORDER BY باشد،

© 2015-2019 سایت
تمامی حقوق متعلق به نویسندگان آنها می باشد. این سایت ادعای نویسندگی ندارد، اما استفاده رایگان را فراهم می کند.
تاریخ ایجاد صفحه: 07/08/2016