קשרים בין טבלאות מתקדם

עבודה עם שתי טבלאות

לצורך דוגמה בואו ניצור טבלה 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');

ואז נקבל:

שיטה א)

  1. קודם כל נגדיר שמות לשתי טבלאות: Customers AS SecondTable, Orders AS FirstTable
    כלומר: טבלה Orders מעכשיו זה FirstTable וטבלה Customers מעכשיו זה SecondTable
  2. עכשיו נבחר עמודות מהטבלאות:
    SecondTable.OrderID, SecondTable.ShipCity, FirstTable.Name
  3. נגדיר תנאי: 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.

דילוג לתוכן