עבודה עם שתי טבלאות
לצורך דוגמה בואו ניצור טבלה Orders עם עמודות
OrderID, CustomerID, ShipCity
CREATE TABLE Orders ( OrderID int, CustomerID int, ShipCity varchar(255) );
ונוסיף נתונים
INSERT INTO Orders ( OrderID, CustomerID, ShipCity ) VALUES ('1','8','Tel Aviv'), ('2','12','Netanya'), ('3','15','Jerusalem');
ואז נקבל:
בנוסף ניצור עוד טבלה Customers עם עמודות CustomerID, Name
CREATE TABLE Customers ( CustomerID int, Name varchar(255) );
ונוסיף נתונים
INSERT INTO Customers ( CustomerID, Name) VALUES ('8','Shalom'), ('2','Daniel'), ('18','Wulf');
ואז נקבל:
שיטה א)
- קודם כל נגדיר שמות לשתי טבלאות: Customers AS SecondTable, Orders AS FirstTable
כלומר: טבלה Orders מעכשיו זה FirstTable וטבלה Customers מעכשיו זה SecondTable - עכשיו נבחר עמודות מהטבלאות:
SecondTable.OrderID, SecondTable.ShipCity, FirstTable.Name - נגדיר תנאי: FirstTable.CustomerID=SecondTable.CustomerID
SELECT SecondTable.OrderID, SecondTable.ShipCity, FirstTable.Name FROM Customers AS FirstTable, Orders AS SecondTable WHERE FirstTable.CustomerID=SecondTable.CustomerID;
ואז נקבל
שיטה ב – מומלץ)
לצורך דוגמה ניצור טבלה Customers עם עמודות CustomerID, Name
ורק מפתח ראשי.
PK: מפתח ראשי CustomerID
CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, Name varchar(255) );
ונוסיף נתונים
INSERT INTO Customers (CustomerID,Name) VALUES ('2','Daniel'), ('8','Shalom'), ('18','Wulf');
ואז נקבל:
בנוסף בואו ניצור עוד טבלה Orders עם עמודות
PK: מפתח ראשי OrderID
FK: מפתח זר CustomerID
OrderID, CustomerID, ShipCity
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID), ShipCity varchar(255) );
ונוסיף נתונים
INSERT INTO Orders ( OrderID, CustomerID, ShipCity ) VALUES ('1','8','Tel Aviv'), ('2','12','Netanya'), ('3','15','Jerusalem');
יש לשים לב שמכיוון שיש קשר בין מפתח זר CustomerID בטבלה Orders למפתח ראשי
בטבלה Customers אז ערכים של CustomerID אמורים להיות בהתאם.
ואז נקבל:
בחירת נתונים משתי טבלאות
חשוב ש-FK (מפתח זר) של טבלה שמאלית יהיה קשור ל-PK (מפתח ראשי) של טבלה ימנית.
במקרה שלנו מכיוון שבצד השמאל (לפני ON) יש טבלת "Orders"
אז יש לבחור מפתח זר מטבלה הזאת ובצד הימין (לאחר ON) יש לבחור
מפתח ראשי מטבלה Customers.
SELECT Orders.OrderID, Orders.ShipCity, Customers.Name FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
ואז נקבל תוצאה משתי טבלאות:
בחירת נתונים משלושה טבלאות
ניצור טבלה חדשה בשם Shippers
כך ש-ShipperID יהיה מפתח ראשי – PK
CREATE TABLE Shippers ( ShipperID int NOT NULL PRIMARY KEY, CompanyName varchar(255), Phone nvarchar(24) );
ונוסיף נתונים
INSERT INTO Shippers ( ShipperID , CompanyName, Phone) VALUES ('5','Speedy Express','(503) 050000000003'), ('12','United Package','(503) 050000000004'), ('24','Federal Shipping','(503) 050000000005');
נמחק נתונים ישנים בטבלה Customers
DELETE FROM Customers;
ונוסיף עוד עמודה אחד בשם ShipViaID לטבלה Customers
* למעשה זה יהיה מפתח זר אשר מקשר לטבלה Shippers
ALTER TABLE Customers
ADD ShipViaID
int FOREIGN KEY REFERENCES Shippers(ShipperID);
נוסיף נתונים לטבלה Customers
INSERT INTO Customers (CustomerID, Name, ShipViaID) VALUES ('8','Shalom','5'), ('2','Daniel','12'), ('18','Wulf','24');
יש לשים לב שמפתח זר ShipViaID בטבלה Customers אמור להיות שווה למפתח ראשי ShipperID בטבלה Shippers
נמחק נתונים ישנים בטבלה Orders
DELETE FROM Orders;
נוסיף נתונים חדשים לטבלה Orders
INSERT INTO Orders ( OrderID, CustomerID, ShipCity ) VALUES ('1','8','Tel Aviv'), ('2','2','Netanya'), ('3','18','Jerusalem');
יש לשים לב שמפתח זר CustomerID בטבלה Orders אמור להיות שווה למפתח ראשי CustomerID בטבלה Customers
כפי שניתן לראות שכאן באופן דומה לדוגמה קודמת יש קישור בין מפתחות ראשים למפתחות זרים.
*CustomerID – מפתח זר של טבלה Orders שווה למפתח ראשי של טבלה Customers
* ShipViaID – מפתח זר של טבלה Customers שווה למפתח ראשי של טבלה Shippersזאת אומרת: בהתחלה בונים טבלה מטבלאות: Orders ו-Customers
ולאחר מכן בונים טבלה מטבלאות (Orders ו-Customers) ו-Shippers
SELECT Orders.OrderID, Customers.Name, Shippers.CompanyName FROM ( (Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Customers.ShipViaID = Shippers.ShipperID );
ואז נקבל:
LEFT JOIN
לצורך דוגמאות הבאות ניצור בסיס נתונים חדש QAdb
CREATE DATABASE QAdb;
בנוסף ניצור טבלה Orders
CREATE TABLE Orders ( OrderID int, CustomerID int, ShipCity varchar(255) );
ונוסיף נתונים
INSERT INTO Orders ( OrderID, CustomerID, ShipCity ) VALUES ('1','8','Tel Aviv'), ('2','12','Netanya'), ('3','43','Tel Aviv'), ('4','23','Netanya'), ('6','11','Netanya'), ('5','17','Jerusalem');
ואז נקבל:
ניצור עוד טבלה Customers עם עמודות CustomerID, Name
CREATE TABLE Customers ( CustomerID int, Name varchar(255) );
ונוסיף נתונים
INSERT INTO Customers ( CustomerID, Name) VALUES ('12','Shalom'), ('23','Daniel'), ('17','Wulf');
ואז נקבל:
בחירה נתונים משתי טבלאות לפי פרמטר השוואה CustomerID בין טבלה שמאלית Orders
לטבלה ימנית Customers.
אם אין פרמטר הזה בטבלה ימנית אז יהיה ערך NULL בטבלה שמאלית.
*השוואה נעשית לפי טבלה שמאלית- Orders
SELECT Customers.Name, Orders.OrderID FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID ORDER BY Customers.Name;
ואז נקבל:
RIGHT JOIN
בחירה נתונים משתי טבלאות לפי פרמטר השוואה CustomerID בין טבלה שמאלית Orders
לטבלה ימנית Customers.
אם אין פרמטר הזה בטבלה שמאלית אז יהיה ערך NULL בטבלה ימנית.
*השוואה נעשית לפי טבלה ימנית – Employees
SELECT Customers.Name, Orders.OrderID FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID ORDER BY Customers.Name;
ואז נקבל:
איחוד שתי טבלאות
לצורך דוגמה ניצור שתי טבלאות City1 ו-City2
יצירת טבלה City1
CREATE TABLE City1 ( id INT NOT NULL PRIMARY KEY, CustomerName varchar(255), CityName varchar(255) );
ונוסיף נתונים
INSERT INTO City1 ( id , CustomerName, CityName) VALUES ('5','Daniel','Tel-Aviv'), ('12','Moshe','Jerusalem'), ('24','Rachel','Netanya');
נקבל:
כנ"ל ניצור טבלה City2
CREATE TABLE City2 ( id INT NOT NULL PRIMARY KEY, CustomerName varchar(255), CityName varchar(255) );
ונוסיף נתונים
INSERT INTO City2 (id , CustomerName, CityName) VALUES ('1','Nathan','Tel-Aviv'), ('2','Hannah','Jerusalem'), ('3','Esther','Jerusalem');
נקבל:
בחירת שם של הלקוח ועיר משתי טבלאות
SELECT CustomerName, CityName FROM City1 UNION SELECT CustomerName, CityName FROM City2 ORDER BY CityName;
התוצאה תהיה:
כל הכבוד לכם, אם הגעתם עד לכאן : )
כל כבוד לכם!!!
בזה נגמר הקורס מבוא ל-SQL, אני מקווה שהקורס הזה נתן לכם רקע התחלתי בבסיס נתונים וכתיבת שאילתות ב-SQL.