آموزش دستور GROUP BY در SQL SQL
توابع Aggregate غالباً با عبارت GROUP BY همراه می شوند.
عبارت GROUP BY
عبارت GROUP BY در ترکیب با توابع aggregate استفاده می شود تا نتیجه را بر اساس یک یا چند ستون گروه بندی کند.
فرم دستور GROUP BY
1
2
3
4
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name; نمونه استفاده از GROUP BY در پایگاه داده
در این آموزش از نمونه پایگاه داده مشهور Northwind استفاده خواهیم کرد.
و جدول زیر از میان جدول "Shippers" انتخاب شده است:
ShipperID
ShipperName
Phone
1
Speedy Express
(503) 555-9831
2
United Package
(503) 555-3199
3
Federal Shipping
(503) 555-9931
و جدول زیر از میان جدول "Employees" انتخاب شده است:
EmployeeID
LastName
FirstName
BirthDate
Photo
Notes
1
Davolio
Nancy
1968-12-08
EmpID1.pic
Education includes a BA....
2
Fuller
Andrew
1952-02-19
EmpID2.pic
Andrew received his BTS....
3
Leverling
Janet
1963-08-30
EmpID3.pic
Janet has a BS degree....
مثال
اکنون می خواهیم تعداد سفارشاتی را که توسط هر یک از شرکت های باربری فرستاده شده است را پیدا کنیم.
عبارت SQL زیر تعداد سفارشات را بر اساس گروه بندی شرکت های باربری می شمارد:
1
2
3
4
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName; GROUP BY برای بیش از یک ستون
می توانیم عبارت GROUP BY را برای بیش از یک ستون استفاده کنیم، مانند زیر:
1
2
3
4
5
6
7
8
SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;
در این فصل می خواهیم عملگرهای IS NULL و IS NOT NULL را توضیح دهیم:
مقادیر NULL در SQL
مقادیر NULL نشانگر داده های ناشناخته و گم شده است.
به طور پیش فرض، یک ستون از جدول می تواند مقدار NULL را در خود نگه دارد.
اگر ستونی در یک جدول اختیاری است، می توانیم رکورد جدیدی را در جدول وارد کرده و یا رکورد قبلی آنرا بروز رسانی کنیم بدون اینکه بخواهیم به ستون مذکور مقداری اضافه کنیم. این بدین معنی است که فبلد فوق با مقدار NULL ذخیره خواهد شد.
با مقدار NULL نسبت به مقادیر دیگر، رفتار متفاوتی می شود.
در فیلدهای نامشخص یا نامعلوم از مقدار NULL جهت پر کردن جای خالی استفاده می شود.
نکته:
NULL و صفر را نمی توان با هم یکی دانست، این دو معادل یکدیگر نیستند.
استفاده از مقدار NULL در SQL
به جدول “Persons” در زیر نگاه کنید:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Stavanger
فرض کنید که ستون “Address” در جدول “Persons” اختیاری باشد. این بدان معناست که اگر رکوردی را بدون وارد کردن مقداری در ستون “Address” درج کنیم، ستون “Address” با مقدار NULL ذخیره خواهد شد.
چگونه می توانیم تست کنیم فیلدی مقدار NULL دارد؟
با عملگر های مقایسه ای مثل =، > و <> نمی توان مقدار NULL را تست کرد.
بلکه باید از عملگرهای IS NULL و IS NOT NULL استفاده کنیم.
IS NULL در SQL
چگونه می توان تنها رکوردهایی را که در ستون “Address” مقدار NULL دارند انتخاب کرد؟
باید از عملگر IS NULL برای این کار استفاده کنیم:
1
2
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
نتیجه چنین خواهد شد :
LastName
FirstName
Address
Hansen
Ola
Pettersen
Kari
نکته:
همیشه از IS NULL برای مشاهده فیلدهای حاوی مقدار NULL استفاده کنید.
IS NOT NULL
چگونه رکوردهایی را که مقدارشان NULL نیست را از ستون “Address” انتخاب می کنیم؟
باید از عملگر IS NOT NULL استفاده کنیم:
1
2
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
نتیجه چنین خواهد شد:
LastName
FirstName
Address
Svendson
Tove
Borgvn 23
در فصل بعد به توابع ISNULL, NVL(), IFNULL() و COALESCE() نظری خواهیم داشت.
آموزش UNION
ملگر UNION نتیجه دو یا چند دستور SELECT را با یکدیگر ترکیب می کند.
عملگر UNION برای ترکیب نتایج دو یا چند دستور SELECT استفاده می شود.
توجه داشته باشید که هر دستور SELECT در عملگر UNION باید تعداد ستونهای یکسانی را برگردانند. همچنین ستونها باید Data Type یکسانی داشته باشند. علاوه بر این ستونها در هر دستور SELECT باید به یک صورت مرتب شده باشند.
فرم عملگر UNION
1
SELECT column_name(s) FROM table1 UNION
1
SELECT column_name(s) FROM table2;
نکته:
عملگر UNION تنها داده هایی را به صورت پیش فرض انتخاب می کند که از یکدیگر متمایز باشند. برای اینکه داده های تکراری را هم بیاوریم از کلید واژه ALL به همراه UNION استفاده کنید.
فرم عملگر UNION ALL
1
SELECT column_name(s) FROM table1 UNION ALL
1
SELECT column_name(s) FROM table2;
نکته: نام ستون ها در جدول نتایج معمولا برابر نام ستونها در دستور SELECT اول در عملگر UNION می باشد.
نحوه استفاده از کلید واژه UNION در پایگاه داده
در این آموزش از نمونه پایگاه داده مشهور Northwind استفاده خواهیم کرد.
جدول زیر از میان جدول "Customers" انتخاب شده است:
آنچه در زیر می بینید گزیده ای است از جدول "Suppliers":
مثال استفاده از کلید واژه UNION
عبارت SQL زیر نام تمام شهر های مختلف را (تنها از بین مقادیر مشخص) از ستون "City" و از میان جداول Customers و Suppliers انتخاب می کند:
1
SELECT City FROM Customers
1
SELECT City FROM Suppliers
1
ORDER BY City;
نکته:
عملگر UNION نمی تواند برای لیست کردن تمام شهر های دو جدول مورد استفاده قرار گیرد. اگر تعدادی از مشتریان و عرضه کنندگانAnchor دارای نام شهر یکسانی باشند، نام آن شهر تنها یکبار در لیست قرار
می گیرد. UNION تنها مقادیر غیر تکراری را انتخاب می کند. برای انتخاب مقادیر تکراری از UNION ALL استفاده کنید.
مثال استفاده از عملگر UNION ALL
عبارت SQL زیر از عملگر UNION ALL برای انتخاب تمام (حتی مقادیر تکراری) شهر ها از جداول Customers و Suppliers استفاده می کند:
1
2
3
4
5
6
7
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
مثال استفاده از عملگر UNION ALL همراه با دستور WHERE
عبارت SQL زیر از عملگر UNION ALL برای انتخاب تمام (حتی مقادیر تکراری) شهر های آلمانAnchor از جداول
Customers و Suppliers استفاده می کند:
1
2
3
4
5
6
7
8
9
10
11
SELECT City, Country FROM Customers
ORDER BY Customers.CustomerName;
نتیجه بدست آمده از عبارت بالاAnchor به شکل زیر می باشد:
نکته:
کلید واژه FULL OUTER JOIN تمام سطرهای جدول سمت چپ (Customers) و تمام سطرهای موجود در جدول سمت راست (Orders) را بر می گرداند. اگر سطری در جدول Customers وجود دارد که تناظری در جدول Orders ندارد یا اگر سطری در جدول Orders وجود دارد که تناظری در جدول Customers ندارد با این وجود این سطرها نیز در جدول نتیجه نمایش داده خواهند شد.
آموزش RIGHT JOIN
کلید واژه RIGHT JOIN تمام سطر های جدول سمت راست (table2) و آندسته از سطر های جدول سمت چپ (table1) را که با جدول سمت راست متناظر است فرا می خواند. اگر در جدول سمت چپ سطر متناظری وجود نداشته باشد نتیجه در سمت چپ صفرAnchor خواهد بود.
ORDER BY Orders.OrderID;
نکته:
کلید واژه RIGHT JOIN تمام سطرهای جدول سمت راست (Employees) را برمی گرداند حتی اگر هیچ داده متناظری برای آن در جدول سمت چپ (Orders) وجود نداشته باشد.
آموزش LEFT JOIN
کلید واژه LEFT JOIN تمام سطر های جدول سمت چپ (table1) و آندسته از سطر های جدول سمت راست (table2) را که با جدول سمت چپ متناظر است فرا می خواند. اگر در جدول سمت راست سطر متناظری وجود نداشته باشد نتیجه در سمت راست صفرAnchor خواهد بود.
ON table1.column_name=table2.column_name;
نکته:
در برخی از پایگاه های داده LEFT JOIN به نام LEFT OUTER JOIN نیز نامیده می شود.
نحوه استفاده از کلید واژه LEFT JOIN در پایگاه داده در این آموزش از نمونه پایگاه داده مشهور Northwind استفاده خواهیم کرد. جدول زیر از میان جدول "Customers" انتخاب شده است:
آنچه در زیر می بینید گزیده ای است از جدول "Orders":
مثال استفاده از کلید واژه LEFT JOIN
عبارت SQL زیر تمام مشتریان و سفارشاتشان را لیست می کند:
ORDER BY Customers.CustomerName;
نکته:
کلید واژه LEFT JOIN تمام سطرهای جدول سمت چپ (Customers) را برمی گرداند حتی اگر هیچ داده متناظری برای آن در جدول سمت راست (Orders) وجود نداشته باشد.
ORDER BY Customers.CustomerName;
نکته: عبارت کلیدی INNER JOIN سطرهایی که حداقل یک تناظر در دو جدول داشته باشند را برمی گرداند. اگر سطری در جدول Customers وجود دارد که تناظری با جدول Orders ندارد، آن سطر لیست نمی شود.
آموزش join
کلید واژه Join برای ترکیب سطر های دو یا چند جدول به کار می رود.
رایج ترین نوع Join –Inner join (پیوند داخلی) است که تمام سطر هایی از جدول های گوناگون را که
از شرط Join پیروی می کنند فرا می خواند.
بیایید نگاهی به گزیده ای از جدول "Orders"( سفارشات) در پایین بیاندازیم.
حالا نگاهی به گزیده ای از جدول "Customers" در زیر بیاندازید:
دقت داشته باشید که ستون "Customers ID" در جدول "Orders" به ستون "Customers ID" در جدول "Customers" اشاره دارد. رابـ ـطه بین دو جدول بالا ستون "Customer ID" می باشد.
بنابراین اگر ما عبارت SQL زیر را که شامل (INNER JOIN) می باشد اجرا کنیم.