אחזור נתונים ושאילתת SELECT

שאילתת SELECT המורחבת

בפרק הקודם סקרנו את 4 פעולות הבסיס לאינטראקציה עם מסדי נתונים, פעולות ה-CRUD ליצירה, קריאה, עדכון ומחיקה של נתונים, ובפרט, הכרנו את השאילתה SELECT לאחזור נתונים ממסד הנתונים.

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

תחביר:

SELECT column1, column2, ... 
FROM table_name 
WHERE condition 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ... 
LIMIT [offset,] row_count;

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

SELECT - חלק זה מציין אילו שדות אנו מעוניינים לאחזר. במקום לציין את רשימת השדות אותם אנו מעוניינים לאחזר, נוכל לציין את הסימן *, על מנת לאחזר את כלל השדות בטבלה (אינו מומלץ מטעמי קריאות ומהירות).

FROM - חלק זה מציין מאיזה טבלה אנו מבקשים לאחזר את הנתונים.

WHERE (אינו חובה) - קובע תנאי לסינון הרשומות שמאוחזרות. רשומות שאינן עומדות בתנאי לא יאוחזרו. דוגמה: WHERE height < 20 יסנן לנו רק את הרשומות בהן הערך בשדה height קטן ממש מ-20.

ORDER BY (אינו חובה) - ממיין את סדר הרשומות המאוחזרות. המיון נעשה לפי הערכים בעמודות שבחרנו (column1, column2, ...) בסידור עולה (ASC) או יורד (DESC). דוגמה: ORDER BY firstname ASC, lastname ASC, age DESC יבצע מיון ראשוני לפי ערכי השדה firstname בסדר עולה. אם יש כמה תוצאות בהן ערך זהה בשדה firstname, יבוצע מיון לפי lastname בסדר עולה. אם יש כמה תוצאות שבהן ערך זהה גם בשדה lastname, יבוצע מיון לפי ערך השדה age בסדר יורד.

LIMIT (אינו חובה) - מציין כמה רשומות אנו מעוניינים לאחזר. ללא קביעת LIMIT, יאוחזרו כל הרשומות הרלוונטיות. שימוש בפרמטר offset קובע על כמה רשומות לדלג (ברירת המחדל היא ללא דילוג). דוגמה: LIMIT 5,10 ידלג על 5 התוצאות הראשונות ויחזיר את 10 התוצאות הבאות.

דוגמה:

SELECT name, age 
FROM users 
WHERE age > 25 
ORDER BY age DESC;

בדוגמה זו, אנו מאחזרים רשומות מהטבלה users. השדות המאוחזרים הם name ו-age. מאוחזרות רק רשומות בהן ערך השדה age גדול ממש מ-25. הרשומות שהשאילתה מחזירה ממויינות לפי ערך השדה age בסדר יורד (DESC). רשומה שבה ערך השדה age הוא נניח 30, תוחזר לפני רשומה שבה ערך השדה age הוא 28.

דוגמה נוספת:

SELECT name, age 
FROM users 
WHERE age > 25 
ORDER BY age DESC 
LIMIT 3;

בדוגמה זו, אנו מחזירים כמו בדוגמה שלפניה, את הרשומות שמקיימות את התנאי age > 25 מהטבלה users. גם הפעם, התוצאות מוחזרות בצורה ממויינת לפי ערך השדה age בסדר יורד. החלק של ה-LIMIT קובע כי יוחזרו לכל היותר 3 תוצאות.

מניפולציות מתקדמות בחלק ה-SELECT

ראינו כי בחלק של ה-SELECT אנו בוחרים אילו שדות אנו מעוניינים לאחזר. ניתן להחליף את שמות השדות בסימן כוכבית (*), שמשמעו איחזור של כלל השדות.

דוגמה:

SELECT * FROM products WHERE price > 100;

בדוגמה זו, אנו מאחזרים את כל הרשומות מהטבלה products שבהן ערך השדה price גדול ממש מ-100. בכל רשומה שמאוחזרת, אנו מאחזרים את כל השדות ברשומה.

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

ניתן להפעיל פונקציות שונות על שמות השדות, לדוגמה הפונקציה COUNT שסופרת את כמות הרשומות או הפונקציה MAX שמחזירה את ערך השדה הגבוה ביותר. נוכל להשתמש במילה השמורה AS, כדי לתת שם חדש לשדה עם הערכים שהוחזרו (אין זה חובה).

דוגמה:

SELECT COUNT(*) AS total_users FROM users;

בדוגמה זו, השאילתה מחזירה את כמות הרשומות בטבלה users. הערך המוחזר יוחזר תחת השם החדש total_users. נשים לב: השאילתה תחזיר רשומה אחת בלבד ובה שדה אחד בלבד עם השם total_users, ללא קשר לכמות הרשומות שבטבלה users.

דוגמה נוספת:

SELECT MAX(age) AS max_age FROM users;

בדוגמה זו, השאילתה מחזירה את ערך השדה age הגבוה ביותר שקיים בטבלה users. גם כאן, מוחזרת רשומה אחת בלבד ובה שדה אחד בלבד עם השם max_age, ללא קשר לכמות הרשומות שבטבלה users.

נוכל להשתמש במילה השמורה DISTINCT, כדי לוודא שבכל הרשומות המוחזרות יהיו ערכים שונים בשדות מסויימים.

דוגמה:

SELECT DISTINCT age AS different_age FROM users;

בדוגמה זו, אנו מחזירים את כל הערכים השונים שקיימים בשדות age בטבלה users. אם נניח שהטבלה כוללת 5 משתמשים, מהם שלושה עם ערך age = 20, אחד עם ערך age = 25 ואחד עם ערך age = 40, אז השאילתה תחזיר 3 רשומות, בכל אחת מהן שדה יחיד בשם different_age, כאשר הרשומה הראשונה תכלול את הערך 20, השניה את הערך 25 והשלישית את הערך 40.

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

דוגמה:

SELECT name, age, (age * 12) AS age_in_months FROM users;

בדוגמה זו, השאילתה מאחזרת רשומות מהטבלה users. בכל רשומה מאחזרים את ערך השדה name, את ערך השדה age ואת הערך age * 12 (ערך השדה age כפול 12). בחרנו לתת שם לערך השלישי age_in_months (אין חובה לתת שם), מכיוון שאם השדה age מחזיק את גיל המשתמש בשנים, אז age_in_months מחזיק את גילו בחודשים.

מניפולציות מתקדמות על חלק ה-WHERE

חלק ה-WHERE קובע תנאים שרק רשומות המקיימות אותן יאוחזרו.

ניתן לקבוע מספר תנאים בחלק ה-WHERE, ולהשתמש באופרטורים AND (גם) ו-OR (או).

דוגמה:

SELECT name, email, age, country
FROM users
WHERE (age > 25 AND country = 'USA') OR (age < 18 AND country = 'Canada');

בדוגמה זו, אנו מאחזרים את כל הרשומות בהן מתקיים אחד מהתנאים הבאים: או שערך השדה age גדול ממש מ-25 וערך השדה country הוא USA, או שערך השדה age קטן ממש מ-18 וערך השדה country הוא Canada.

במקרים בהם התנאי בחלק ה-WHERE הוא ביטוי שמורכב מכמה התניות עם האופרטורים AND או OR, נמליץ להשתמש בסוגריים לשמירה על הקדימות הנדרשת בחישוב התנאי ולמען הקריאות.

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

דוגמה:

-- Find users whose name starts with 'A'
SELECT name, email
FROM users
WHERE name LIKE 'A%';

-- Find users whose name ends with 'son'
SELECT name, email
FROM users
WHERE name LIKE '%son';

-- Find users whose name contains 'an'
SELECT name, email
FROM users
WHERE name LIKE '%an%';

-- Find users with email addresses from the domain 'gmail.com'
SELECT name, email
FROM users
WHERE email LIKE '%@gmail.com';

-- Find users whose name is exactly 4 characters long and ends with 'a'
SELECT name
FROM users
WHERE name LIKE '___a';  -- Each underscore (_) represents a single character

-- Find users whose name starts with any character followed by 'ohn' (e.g., John, Rohn, Sohn)
SELECT name
FROM users
WHERE name LIKE '_ohn';

בדוגמה זו, השאילתה הראשונה מחזירה את כל הרשומות בהן ערך השדה name מתחיל באות A וממשיך ברצף תווים כלשהו. השאילתה השניה מחזירה את כל הרשומות בהן ערך השדה name מתחיל ברצף תווים כלשהו ומסתיים ברצף son. השאילתה השלישית מחזירה את כל הרשומות בהן ערך השדה name מתחיל ברצף תווים כלשהו, ממשיך ברצף an ומסתיים ברצף תווים כלשהו. השאילתה הרביעית מחזירה את כל הרשומות בהן ערך השדה email מתחיל ברצף תווים כלשהו ומסתיים ברצף gmail.com@. השאילתה החמישית מחזירה את כל הרשומות בהן ערך השדה name מתחיל ברצף של 3 תווים כלשהם (הסימן _ מתאר תו כלשהו, והשאילתה כוללת שלושה סימני _) ומסתיים באות a. השאילתה השישית מחזירה את כל הרשומות בהן ערך השדה name מתחיל בתו כלשהו יחיד ומסתיים ברצף ohn.

ניתן להשתמש באופרטור NOT כדי לתאר תנאי שאינו מתקיים.

דוגמה:

SELECT name, email, age, country
FROM users
WHERE (
    (country = 'USA' AND name LIKE 'A%' AND age > 30)
    OR
    (country != 'Canada' AND NOT (email LIKE '%@gmail.com') AND NOT is_active)
);

בדוגמה זו, השאילתה מחזירה רשומות שמקיימות לפחות אחד מבין 2 התנאים הבאים:

1. ערך השדה country הוא USA, ערך השדה name מתחיל באות A וערך השדה age גדול ממש מ-30.
2. ערך השדה country שונה מ-Canada, לא מתקיים שערך השדה email מסתיים ברצף gmail.com@, וערך השדה is_active הוא false (שדה בוליאני)

ניתן להשתמש באופרטור IN כדי לבדוק האם ערך נמצא בתוך רשימה.

דוגמה:

SELECT name, email, country
FROM users
WHERE country IN ('USA', 'Canada', 'UK');

בדוגמה זו, השאילתה מחזירה את כל הרשומות בהן ערך השדה country הוא אחד מבין הערכים ברשימה: USA, Canada, UK.

שימוש מתקדם ב-LIMIT

חלק ה-LIMIT מגביל את כמות התוצאות שהשאילתה מחזירה.

דוגמה:

SELECT id, name, content 
FROM articles
LIMIT 5;

בדוגמה זו, השאילתה מחזירה רק 5 רשומות מהטבלה articles, לכל רשומה מאוחזרים השדות id, name, content.

נוכל לקבוע לדלג על מספר תוצאות ולהחזיר את התוצאות הבאות בתור, באמצעות האופרטור OFFSET.

שימוש נפוץ בשאילתות עם חלק LIMIT שכולל את האופרטור OFFSET הוא לצורכי "דיפדוף" בתוצאות. אם לדוגמה יש לנו אתר אינטרנט עם עשרות מאמרים, ונרצה לפצל את הצגת כל המאמרים בין עמודים שונים (בעמוד המאמרים הראשי יוצגו נניח 10 מאמרים, בעמוד השני 10 מאמרים נוספים וכן הלאה), שימוש ב-LIMIT יתן לנו את המענה לכך.

דוגמה:

SELECT id, name, content 
FROM articles
LIMIT 5 OFFSET 10;

בדוגמה זו, השאילתה מחזירה 5 רשומות כמו בדוגמה הקודמת, רק שהפעם היא מדלגת על 10 התוצאות הראשונות. יש לשים לב שלמעשה מוחזרות הרשומות ה-11, ה-12 וכן הלאה (הרשומה ה-10 אינה מוחזרת).

ניתן להשתמש בפורמט אלטרנטיבי ל-LIMIT עם OFFSET, אשר כולל 2 פרמטרים. במקרה כזה, הפרמטר הראשון מייצג את ה-OFFSET (על כמה רשומות מדלגים) והפרמטר השני מייצג את כמות הרשומות שמוחזרות.

דוגמה:

SELECT id, name, content 
FROM articles
LIMIT 10, 5;

דוגמה זו זהה לחלוטין לדוגמה הקודמת. אנו מדלגים על 10 רשומות ומחזירים את 5 הרשומות הבאות.

שימוש נפוץ הוא לשלב את החלק WHERE (שקובע תנאי שהרשומות המוחזרות חייבות לקיים) עם החלק ORDER BY (שקובע את סדר הרשומות) ועם החלק LIMIT (שקובע כמה רשומות מוחזרות).

דוגמה:

SELECT name, age
FROM users
WHERE age >= 18
ORDER BY age ASC
LIMIT 1;

בדוגמה זו, השאילתה מאתרת את כל הרשומות המקיימות את התנאי age >= 18 (גיל המשתמש שווה או גדול מ-18). היא ממיינת את התוצאות לפי ערך השדה age בסדר עולה (ASC), כלומר מהגיל הנמוך למבוגר יותר. השאילתה מחזירה רשומה אחת בלבד. למעשה, השאילתה מאתרת את הרשומה שבה נמצא המשתמש הצעיר ביותר שגילו לפחות 18.