דף עזר SQL - הפקודות החשובות עם דוגמאות

דף עזר SQL הוא רשימה מרוכזת של פקודות SQL החשובות, עם הסבר קצר ודוגמה לכל אחת, שאפשר לפתוח באמצע עבודה ולמצוא בו את התחביר הנכון. SQL (Structured Query Language) היא השפה שבה שולפים, מוסיפים, משנים ומוחקים נתונים במסדי נתונים יחסיים כמו SQL Server, MySQL, PostgreSQL ו-Oracle.

הפקודות מחולקות לפי נושא, והדוגמאות כתובות ב-SQL תקני; במקומות שבהם SQL Server (שפת T-SQL) או MySQL שונים מצוין ההבדל. אם עוד לא ברור לכם מה זה טבלה ומה זה מסד נתונים, התחילו מהמדריך איך לומדים SQL מאפס וחזרו לכאן.

דף עזר SQL: טבלאות הדוגמה

כל הדוגמאות משתמשות בשלוש טבלאות קטנות של מכללה דמיונית:

• Students - הסטודנטים: StudentID, FirstName, LastName, City, Phone.
• Courses - הקורסים: CourseID, CourseName, Hours.
• Enrollments - ההרשמות: StudentID, CourseID, Grade. כל שורה היא סטודנט שרשום לקורס, עם הציון שלו (או NULL אם עוד אין ציון).

נקודה-פסיק (;) מסיימת פקודה, וטקסט כותבים בין גרשיים בודדים.

שליפת נתונים: SELECT, WHERE ו-ORDER BY

SELECT ... FROM

בוחרת אילו עמודות להציג ומאיזו טבלה. הכוכבית * מחזירה את כל העמודות, ו-AS נותן לעמודה שם זמני (Alias) בתוצאה.

דוגמה: SELECT CourseName, Hours * 60 AS Minutes FROM Courses;

WHERE

מסננת שורות לפי תנאי. האופרטורים: = שווה, <> שונה, < ו->, <= ו->=. מחברים כמה תנאים עם AND, OR ו-NOT, ומשתמשים בסוגריים כשמערבבים AND ו-OR.

דוגמה: SELECT CourseName FROM Courses WHERE Hours >= 30 AND Hours < 60;

BETWEEN, IN ו-LIKE

BETWEEN בודקת טווח וכוללת את שני הקצוות. IN בודקת אם הערך נמצא ברשימה. LIKE מחפשת תבנית בטקסט: % מחליף כל רצף של תווים (גם רצף ריק), ו-_ מחליף תו אחד בדיוק.

דוגמה: SELECT * FROM Courses WHERE Hours BETWEEN 20 AND 40;, SELECT * FROM Students WHERE City IN ('Haifa', 'Eilat'); או SELECT * FROM Students WHERE LastName LIKE 'Co%'; (שמות משפחה שמתחילים ב-Co)

ORDER BY

ממיינת את התוצאה. ASC (סדר עולה) היא ברירת המחדל, ו-DESC ממיין בסדר יורד. אפשר למיין לפי כמה עמודות.

דוגמה: SELECT FirstName, LastName, City FROM Students ORDER BY City, LastName DESC;

TOP ו-LIMIT: הגבלת מספר השורות

כאן מסדי הנתונים שונים זה מזה. ב-SQL Server כותבים TOP מיד אחרי SELECT; ב-MySQL, PostgreSQL ו-SQLite כותבים LIMIT בסוף השאילתה. בלי ORDER BY אין שום הבטחה אילו שורות יחזרו.

דוגמה: SELECT TOP 5 * FROM Courses ORDER BY Hours DESC; (SQL Server) או SELECT * FROM Courses ORDER BY Hours DESC LIMIT 5; (MySQL)

פונקציות צבירה, GROUP BY ו-HAVING

COUNT, SUM, AVG, MIN, MAX

מחשבות ערך אחד מתוך הרבה שורות: COUNT סופרת, SUM מסכמת, AVG מחשבת ממוצע, ו-MIN ו-MAX מחזירות את הערך הקטן והגדול. COUNT(*) סופרת את כל השורות, ואילו COUNT(Grade) סופרת רק שורות שיש בהן ערך ב-Grade. גם SUM ו-AVG מתעלמות מ-NULL.

דוגמה: SELECT COUNT(*) AS Total, AVG(Grade) AS AvgGrade FROM Enrollments;

הערה ל-SQL Server: AVG על עמודה מסוג INT מחזירה מספר שלם, והחלק העשרוני נחתך. כדי לקבל ממוצע מדויק כותבים AVG(Grade * 1.0).

GROUP BY

מחלקת את השורות לקבוצות לפי ערך משותף ומפעילה את פונקציית הצבירה על כל קבוצה בנפרד. כל עמודה ב-SELECT שאינה בתוך פונקציית צבירה צריכה להופיע ב-GROUP BY.

דוגמה: SELECT City, COUNT(*) AS StudentCount FROM Students GROUP BY City;

HAVING

מסננת קבוצות אחרי הצבירה. ההבדל מ-WHERE: WHERE מסננת שורות לפני החלוקה לקבוצות ולא יכולה להשתמש ב-COUNT או ב-AVG.

דוגמה: SELECT CourseID, COUNT(*) AS Enrolled FROM Enrollments GROUP BY CourseID HAVING COUNT(*) >= 10;

סוגי JOIN: איך מחברים טבלאות

JOIN מחברת שורות משתי טבלאות לפי עמודה משותפת. בדוגמאות האותיות s, e ו-c הן שמות קצרים לטבלאות. ההסבר המלא עם הדגמה על המסך נמצא בהרצאה פקודות Select ו-Join.

INNER JOIN

מחזירה רק שורות שיש להן התאמה בשתי הטבלאות: סטודנט שלא רשום לאף קורס לא יופיע. המילה JOIN לבדה פירושה INNER JOIN.

דוגמה: SELECT s.FirstName, e.CourseID, e.Grade FROM Students s INNER JOIN Enrollments e ON s.StudentID = e.StudentID;

LEFT JOIN

מחזירה את כל השורות מהטבלה השמאלית (הראשונה בשאילתה), ואת ההתאמות מהטבלה הימנית אם יש. כשאין התאמה, העמודות של הטבלה הימנית יהיו NULL. השם המלא הוא LEFT OUTER JOIN.

דוגמה: SELECT s.FirstName, e.CourseID FROM Students s LEFT JOIN Enrollments e ON s.StudentID = e.StudentID; (גם סטודנטים בלי קורס, עם NULL ב-CourseID)

RIGHT JOIN

ההפך: כל השורות מהטבלה הימנית וההתאמות מהשמאלית. כל RIGHT JOIN אפשר לכתוב כ-LEFT JOIN אם מחליפים את סדר הטבלאות, ולכן רוב המפתחים מעדיפים LEFT.

דוגמה: SELECT c.CourseName, e.StudentID FROM Enrollments e RIGHT JOIN Courses c ON e.CourseID = c.CourseID;

FULL OUTER JOIN

מחזירה את כל השורות משתי הטבלאות: שורות מותאמות מתחברות, ובשאר יופיע NULL בצד החסר. נתמכת ב-SQL Server, PostgreSQL ו-Oracle, אבל לא ב-MySQL.

דוגמה: SELECT c.CourseName, e.StudentID FROM Courses c FULL OUTER JOIN Enrollments e ON c.CourseID = e.CourseID;

CROSS JOIN

מחברת כל שורה מהטבלה הראשונה עם כל שורה מהשנייה (מכפלה קרטזית), בלי תנאי. 10 סטודנטים ו-5 קורסים נותנים 50 שורות.

דוגמה: SELECT s.FirstName, c.CourseName FROM Students s CROSS JOIN Courses c;

ערכי NULL: איך בודקים ערך חסר

NULL פירושו "אין ערך" או "לא ידוע", וזה לא אפס ולא טקסט ריק. השוואה רגילה ל-NULL, אפילו NULL = NULL, לא מחזירה אמת, וכל חישוב עם NULL נותן NULL.

IS NULL ו-IS NOT NULL

הדרך הנכונה לבדוק אם ערך חסר. התנאי WHERE Phone = NULL לא יחזיר אף שורה.

דוגמה: SELECT FirstName, LastName FROM Students WHERE Phone IS NULL;

COALESCE ו-ISNULL

COALESCE מחזירה את הערך הראשון ברשימה שאינו NULL. היא חלק מהתקן ועובדת בכל מסדי הנתונים הנפוצים. ב-SQL Server יש גם ISNULL, שמקבלת שני ערכים בדיוק, וב-MySQL יש IFNULL.

דוגמה: SELECT FirstName, COALESCE(Phone, 'N/A') AS Phone FROM Students;

שאילתות משנה (Subqueries)

שאילתת משנה היא SELECT בתוך פקודה אחרת, בתוך סוגריים. מסד הנתונים משתמש בתוצאה שלה כמו בערך בודד או ברשימה.

שאילתת משנה שמחזירה ערך אחד

שימושית להשוואה לממוצע או למקסימום. היא חייבת להחזיר שורה אחת ועמודה אחת, אחרת תתקבל שגיאה.

דוגמה: SELECT CourseName, Hours FROM Courses WHERE Hours > (SELECT AVG(Hours) FROM Courses);

EXISTS ו-NOT EXISTS

בודקות אם שאילתת המשנה מחזירה שורה כלשהי. NOT EXISTS בטוחה יותר מ-NOT IN: אם הרשימה של NOT IN מכילה NULL, התוצאה ריקה.

דוגמה: SELECT c.CourseName FROM Courses c WHERE NOT EXISTS (SELECT 1 FROM Enrollments e WHERE e.CourseID = c.CourseID); (קורסים שאין בהם אף רשום)

שינוי נתונים: INSERT, UPDATE ו-DELETE

INSERT INTO ... VALUES

מוסיפה שורה חדשה. כותבים את שמות העמודות ואחריהם את הערכים באותו סדר. SQL Server, MySQL ו-PostgreSQL מקבלים כמה שורות באותה פקודה, מופרדות בפסיקים.

דוגמה: INSERT INTO Courses (CourseID, CourseName, Hours) VALUES (8, 'Linux Basics', 30), (9, 'Python', 40);

UPDATE ... SET

משנה ערכים בשורות קיימות. בלי WHERE השינוי חל על כל השורות בטבלה, וזו אחת הטעויות הנפוצות והיקרות ביותר.

דוגמה: UPDATE Students SET City = 'Haifa', Phone = NULL WHERE StudentID = 12;

DELETE FROM

מוחקת שורות. גם כאן, בלי WHERE נמחקות כל השורות בטבלה.

דוגמה: DELETE FROM Enrollments WHERE CourseID = 3 AND Grade IS NULL;

הרגל שחוסך אסונות: לפני UPDATE או DELETE מריצים SELECT עם אותו WHERE, או עוטפים את השינוי בטרנזקציה (BEGIN TRANSACTION, ואחרי בדיקה COMMIT או ROLLBACK). הפקודות מודגמות בהרצאה פקודות Insert, Update ו-Delete, וטרנזקציות מוסברות בהרצאה טרנזקציה - SQL Transactions.

יצירת טבלאות ואילוצים: CREATE TABLE

CREATE TABLE

יוצרת טבלה חדשה: לכל עמודה שם, סוג נתונים ואילוצים. סוגים נפוצים: INT למספרים שלמים, DECIMAL(10,2) למספרים עם שבר מדויק (כמו סכומי כסף), VARCHAR(n) לטקסט באורך משתנה ו-DATE לתאריך. ב-SQL Server עברית שומרים בעמודת NVARCHAR, וכותבים מחרוזת עברית עם N לפניה: N'חיפה'.

דוגמה: CREATE TABLE Courses (CourseID INT PRIMARY KEY, CourseName NVARCHAR(100) NOT NULL, Hours INT DEFAULT 30);

PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT ו-CHECK

• PRIMARY KEY - מזהה כל שורה באופן חד ערכי, ולא מאפשר כפילויות או NULL.
• NOT NULL - העמודה חייבת לקבל ערך.
• UNIQUE - אסור שיהיו בעמודה שני ערכים זהים.
• DEFAULT - ערך שנכנס אוטומטית כשלא מציינים ערך ב-INSERT.
• CHECK - תנאי שכל ערך חייב לעמוד בו, למשל ציון בין 0 ל-100.

מספור אוטומטי של מפתח: ב-SQL Server כותבים IDENTITY(1,1) (מתחיל ב-1 ועולה ב-1), וב-MySQL כותבים AUTO_INCREMENT.

FOREIGN KEY ... REFERENCES

מפתח זר מקשר עמודה לטבלה אחרת ומונע ערכים שאין להם שורה מתאימה: אי אפשר לרשום לקורס סטודנט שלא קיים.

דוגמה: CREATE TABLE Enrollments (StudentID INT NOT NULL, CourseID INT NOT NULL, Grade INT CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID));

אינדקסים (Indexes)

אינדקס מאפשר למסד הנתונים למצוא שורות בלי לסרוק את כל הטבלה, בדומה למפתח בסוף ספר. הוא מאיץ חיפוש ומיון, אבל מאט מעט כל INSERT, UPDATE ו-DELETE, ולכן יוצרים אינדקסים רק לעמודות שבאמת מחפשים לפיהן. מפתח ראשי ואילוץ UNIQUE מקבלים אינדקס אוטומטית. ההסבר המלא בהרצאה מפתח ראשי, אינדקס וטריגר.

CREATE INDEX

יוצרת אינדקס על עמודה אחת או על כמה עמודות.

דוגמה: CREATE INDEX IX_Students_LastName ON Students (LastName);

DROP INDEX

מוחקת אינדקס. התחביר שונה בין מסדי הנתונים: ב-SQL Server וב-MySQL מציינים גם את שם הטבלה, וב-PostgreSQL רק את שם האינדקס.

דוגמה: DROP INDEX IX_Students_LastName ON Students; (SQL Server, MySQL)

תצוגות (Views)

View היא שאילתה שמורה עם שם, שמתנהגת כמו טבלה וירטואלית: View רגילה לא שומרת עותק של הנתונים, והשאילתה שלה רצה בכל פנייה. משתמשים בה כדי להסתיר JOIN מסובך ולחשוף רק חלק מהעמודות. יצירת טבלאות ו-View מודגמת בהרצאה טבלאות ו-View.

CREATE VIEW

מגדירה View חדשה מתוך שאילתת SELECT.

דוגמה: CREATE VIEW StudentGrades AS SELECT s.FirstName, s.LastName, c.CourseName, e.Grade FROM Students s JOIN Enrollments e ON s.StudentID = e.StudentID JOIN Courses c ON e.CourseID = c.CourseID;

שימוש ב-View ו-DROP VIEW

פונים ל-View כמו לטבלה, כולל WHERE ו-ORDER BY. DROP VIEW מוחקת רק את ההגדרה, לא את הנתונים שבטבלאות.

דוגמה: SELECT * FROM StudentGrades WHERE Grade < 60 ORDER BY LastName; או DROP VIEW StudentGrades;

תרגול: 5 משימות על פקודות SQL

כל המשימות על טבלאות הדוגמה. כתבו את השאילתה לבד ורק אחר כך השוו לפתרון; לרוב יש יותר מדרך נכונה אחת.

משימה 1: כל הסטודנטים מחיפה, ממוינים לפי שם משפחה

פתרון: SELECT FirstName, LastName FROM Students WHERE City = 'Haifa' ORDER BY LastName;

משימה 2: כמה סטודנטים יש בכל עיר, רק ערים עם יותר משלושה סטודנטים

פתרון: SELECT City, COUNT(*) AS StudentCount FROM Students GROUP BY City HAVING COUNT(*) > 3; - התנאי על COUNT חייב להיות ב-HAVING, כי WHERE רץ לפני החלוקה לקבוצות.

משימה 3: סטודנטים שלא רשומים לאף קורס

פתרון: SELECT s.FirstName, s.LastName FROM Students s LEFT JOIN Enrollments e ON s.StudentID = e.StudentID WHERE e.StudentID IS NULL; - ה-LEFT JOIN משאיר גם סטודנטים בלי התאמה, ואצלם העמודות של Enrollments הן NULL. פתרון שקול: NOT EXISTS עם שאילתת משנה.

משימה 4: להוסיף 5 נקודות לכל הציונים שמתחת ל-95 בקורס מספר 7

פתרון: UPDATE Enrollments SET Grade = Grade + 5 WHERE CourseID = 7 AND Grade < 95; - שורות עם NULL לא ישתנו, כי התנאי Grade < 95 לא מתקיים עבור NULL.

משימה 5: הקורסים שהממוצע בהם גבוה מהממוצע הכללי

פתרון: SELECT c.CourseName, AVG(e.Grade * 1.0) AS AvgGrade FROM Courses c JOIN Enrollments e ON c.CourseID = e.CourseID GROUP BY c.CourseID, c.CourseName HAVING AVG(e.Grade * 1.0) > (SELECT AVG(Grade * 1.0) FROM Enrollments); - שאילתת המשנה מחשבת את הממוצע הכללי, ו-HAVING משווה אליו את הממוצע של כל קורס.

איפה לומדים SQL לעומק

את ההבנה בונים מתרגול על מסד נתונים אמיתי. קורס SQL ו-DBA אפליקטיבי למתחילים של PracticU מתחיל בהתקנת שרת SQL ויצירת מסד נתונים, עובר דרך פקודות Select ו-Join, Insert, Update ו-Delete, טבלאות ו-View, מפתח ראשי ואינדקסים, פונקציות, T-SQL, טרנזקציות ו-Execution Plan, ומסתיים בפרויקט גמר.