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

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

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

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

نحو:

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

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

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

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

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

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

نتیجه:

توابع جمع در SQL

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

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

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

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

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

نمونه ای از پایگاه داده "موسسه":ارزش بالاترین حقوق را در بین معلمان دریافت کنید، کل را به عنوان نمایش دهید "max_zp"

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

نتایج:

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

GROUP BY clause در SQL

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

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

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

مثال:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

توابع جمع MIN و MAX

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

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

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

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

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

توابع MIN و MAX همچنین می توانند رشته ها و تاریخ ها را به عنوان آرگومان بگیرند. در مورد آرگومان رشته ای، مقادیر با استفاده از ترتیب مرتب سازی واقعی مقایسه می شوند. برای همه آرگومان های داده موقت از نوع "تاریخ"، کوچکترین مقدار ستون بیشترین مقدار خواهد بود تاریخ اولیهو بزرگترین - جدیدترین.

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

تابع مجموع SUM

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

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

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

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

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

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

عملکرد AVG Aggregate

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

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

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

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

توابع مجموع COUNT و COUNT_BIG

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

COUNT(col_name) COUNT(*)

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

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

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

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

همانطور که از پرس و جوی مثال می بینید، مقادیر NULL توسط تابع COUNT در نظر گرفته نشده است. (مجموع تمام مقادیر در ستون شغل 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 را برمی گرداند.

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

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

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

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

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

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

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

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

داشتن پیشنهاد

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

شرایط داشتن

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

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

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

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

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

از SampleDb استفاده کنید. - «مشاور» را برمی‌گرداند. شغل از گروه Works_on را انتخاب کنید.

این مثال ردیف‌های جدول 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 استفاده کنید. SELECT شماره پروژه، COUNT(*) "تعداد کارکنان" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;

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

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

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

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

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

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

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

عبارت SELECT و ویژگی IDENTITY

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

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

از SampleDb استفاده کنید. محصول جدول ایجاد کنید (ID INT IDENTITY(10000، 1) NOT NULL، نام NVARCHAR(30) NOT NULL، Price MONEY) درج در محصول(نام، قیمت) VALUES ("Item1"، 10)، ("Item2"، 15) , ("Item3"، 8)، ("Item4"، 15)، ("Item5"، 40); -- برمی گرداند 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Product5"; -- مشابه عبارت قبلی SELECT $identity FROM Product WHERE Name = "Product5";

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

چندین تابع و متغیر سیستم با ویژگی IDENTITY مرتبط هستند. به عنوان مثال، کد مثال استفاده می کند متغیر سیستم هویت $. همانطور که از خروجی این کد می بینید، این متغیر به طور خودکار به ویژگی 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، سیستم با افزایش بزرگترین مقدار فعلی آن ستون، مقدار ستون IDENTITY بعدی را ایجاد می کند.

عبارت CREATE SEQUENCE

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

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

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

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

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

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

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

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

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

در دو جمله اختیاری بعدی MINVALUEو MAXVALUEحداقل و حداکثر مقدارشی دنباله (توجه داشته باشید که مقدار 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 = "Saint Petersburg" WHERE Id = 18316; به روز رسانی EmployeeEnh SET City = "Krasnodar" WHERE ID = 25348; UPDATE EmployeeEnh SET City="Kazan" WHERE Id=28559; UPDATE 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 به یکدیگر ملحق شده اند به یک جدول اشاره می کنند، می توانید از عملگر OR به جای عملگر 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 این است:

که در این مورداولین شرط تطبیق جستجو می شود و سپس عبارت TEN مربوطه اجرا می شود. اگر هیچ یک از شرایط مطابق با الزامات نباشد، بند 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 "Budget Category" FROM Project p1;

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

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

به عنوان مثال، تابع جمع ()AVG ستونی از اعداد را به عنوان آرگومان می گیرد و میانگین آنها را محاسبه می کند.

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

«درآمد متوسط=»، AVG(SUMD) را انتخاب کنید

SQL دارای شش تابع مجموع است که به شما امکان می دهد انواع مختلفاطلاعات خلاصه (شکل 1):

- SUM() مجموع تمام مقادیر موجود در ستون را محاسبه می کند.

- AVG() میانگین بین مقادیر موجود در ستون را محاسبه می کند.

- MIN () کوچکترین را در بین تمام مقادیر موجود در ستون پیدا می کند.

- MAX() بزرگترین را در بین تمام مقادیر موجود در ستون پیدا می کند.

- COUNT() تعداد مقادیر موجود در یک ستون را می شمارد.

– COUNT(*) تعداد ردیف های جدول نتیجه پرس و جو را می شمارد.

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

AVG را انتخاب کنید (SUMD*0.13)

این کوئری یک ستون موقت حاوی مقادیر (SUMD*0.13) برای هر ردیف در جدول PERSON ایجاد می کند و سپس میانگین ستون موقت را محاسبه می کند.

مجموع درآمد تمام ساکنان Zelenograd را می توان با استفاده از تابع جمع SUM محاسبه کرد:

جمع (SUMD) را از شخص انتخاب کنید

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

انتخاب جمع (پول)

از سود، HAVE_D

WHERE PROFIT.ID=HAVE_D.ID

AND PROFIT.SOURCE='Scholarship'

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

برای مثال می توانید تعریف کنید:

الف) کمترین درآمد کل دریافتی ساکنان و بالاترین مالیات قابل پرداخت:

SELECT MIN(SUMD)، MAX(SUMD*0.13)

ب) تاریخ تولد مسن ترین و جوانترین ساکن:

SELECT MIN(RDATE)، MAX(RDATE)

(ج) نام خانوادگی، نام و نام خانوادگی اولین و جدیدترین ساکنان فهرست، به ترتیب حروف الفبا:

SELECT MIN(FIO)، MAX(FIO)

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

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

تابع تجمعی COUNT() تعداد مقادیر موجود در یک ستون از هر نوع را می شمارد:

(الف) چند آپارتمان در منطقه کوچک 1 وجود دارد؟

SELECT COUNT(ADR) FROM FLAT WHERE ADR LIKE "%, 1_ _-%"

ب) چند نفر از ساکنان منابع درآمد دارند؟

تعداد (نام متمایز) از HAVE_D را انتخاب کنید

ج) چه تعداد منبع درآمد توسط ساکنان استفاده می شود؟

SELECT COUNT(DISTINCT ID) FROM HAVE_D (کلید کلیدی DISTINCT مشخص می کند که مقادیر غیر تکراری در یک ستون شمارش می شوند).

تابع مجموع ویژه COUNT(*) ردیف های جدول نتیجه را می شمارد نه مقادیر داده ها:

(الف) چند آپارتمان در منطقه 2 کوچک وجود دارد؟

COUNT(*) FROM FLAT WHERE ADR LIKE "%, 2__-%" را انتخاب کنید

ب) ایوانف ایوان ایوانوویچ چند منبع درآمد دارد؟

SELECT COUNT(*) FROM PERSON, HAVE_D WHERE FIO="Ivanov Ivan Ivanovich" AND PERSON.NOM=HAVE_D.NOM

(ج) چند نفر در یک آپارتمان در یک آدرس خاص زندگی می کنند؟

SELECT COUNT(*) FROM PERSON WHERE ADR="Zelenograd, 1001-45"

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

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

AVG(SUMD)، SUM(SUMD)، (100*AVG(MONEY/SUMD)) FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D.ID=PROFIT.ID

بدون توابع انبوه، پرس و جو به صورت زیر خواهد بود:

SUMD, SUMD, MONEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM و HAVE_D.ID=PROFIT.ID را انتخاب کنید

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

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

حداکثر (SUMD)-MIN(SUMD) را از شخص انتخاب کنید

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

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

FIO، SUM(SUMD) را از شخص انتخاب کنید

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

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

استاندارد 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

از جانبدارایی برای فروش

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

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

جدول 23

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

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

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

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

جدول 24

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

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

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

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

جدول 26

نتیجه پرس و جو

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

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

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

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

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

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

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

جدول 27

نتیجه پرس و جو

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

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

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

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

برنج. 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

سوالات فرعیدر این بخش، استفاده از دستورات 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

نتیجه پرس و جو

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

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

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

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

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

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

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

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

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

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

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

جدول 30

نتیجه پرس و جو

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

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

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

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

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

انتخاب کنید

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

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

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

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

از جانبدارایی برای فروش

فصل 5. SQL Language: Data Manipulation 189

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

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

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

از جانبشاخه

جایی کهstreet = "خیابان اصلی 163")) ;

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

جدول 31

نتیجه پرس و جو

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

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

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

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

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

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

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

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

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

جدول 32

نتیجه پرس و جو

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

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

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

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

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

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

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

جدول 33

نتیجه پرس و جو

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

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

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

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

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

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

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

جدول 34

نتیجه پرس و جو

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

با تعداد زیادی ردیف از جدول مشاهده (کودک). جفت‌های ردیفی که هنگام اجرای یک کوئری ایجاد می‌شوند، نتیجه همه ترکیب‌های معتبر ردیف‌ها در جداول فرزند و والد هستند. بخش 3.2.5 چگونگی ایجاد رابطه "والد-فرزند" را در یک پایگاه داده رابطه ای، کلیدهای اصلی و خارجی جداول توضیح می دهد. جدول حاوی کلید خارجی معمولاً یک فرزند است، در حالی که جدول حاوی کلید اصلی همیشه یک والد خواهد بود. برای استفاده از رابطه والد-فرزند در پرس و جوی SQL، باید یک شرط جستجو را مشخص کنید که کلیدهای خارجی و اصلی را با هم مقایسه کند. مثال 24 کلید اصلی جدول Client (v. 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

نتیجه پرس و جو

مثال 26.پیوستن به سه جدول برای هر شعبه شرکت، شماره پرسنل و اسامی کارکنانی را که مسئولیت هرگونه تأسیسات اجاره ای را بر عهده دارند، از جمله شهری که شعبه در آن واقع شده است، و شماره اشیایی که هر کارمند مسئولیت آن را بر عهده دارد، فهرست کنید.

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

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

جایی که 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

نتایج پرس و جو

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

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

پیوستن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

نتیجه پرس و جو

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

SELECT(* j columnList]

از جدولNamel CROSS JOINCayeUlte2

دوباره مثالی را در نظر بگیرید که در آن اتصال کلاینت و جداول 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 سایت
تمامی حقوق متعلق به نویسندگان آنها می باشد. این سایت ادعای نویسندگی ندارد، اما ارائه می دهد استفاده رایگان.
تاریخ ایجاد صفحه: 2016-08-07