כיצד להשתמש בשאילתות מקוננות ב-SQL

הקדמה

שפת השאילתות מבנית (SQL) משמשת לניהול נתונים במערכת ניהול מסדי נתונים רציונלית (RDBMS). פונקציה שימושית ב־SQL היא יצירת שאילתה בתוך שאילתה, הידועה גם כ־שאילתה משנית או שאילתה מקוננת. שאילתה מקוננת היא פקודת SELECT שכללית מוטמעת בתוך סוגריים, ומוטבעת בתוך פקודת SELECT, INSERT, או DELETE ראשית.

במדריך זה, תשתמש בשאילות מקוננות עם הפקודות SELECT, INSERT, ו־DELETE. תשתמש גם בפונקציות כולם בתוך שאילתה מקוננת כדי להשוות בין ערכי הנתונים לערכי הנתונים הממויינים שציינת עם המילים המוקשות WHERE ו־LIKE.

דרישות מוקדמות

כדי לעקוב אחרי המדריך הזה, יהיה עליך להשתמש במחשב הפועל בסוג של מערכת ניהול מסדי נתונים רציונלית (RDBMS) המשתמשת ב־SQL. ההוראות והדוגמאות במדריך זה אומתו באמצעות הסביבה הבאה:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• התקנתי ואבטחתי את MySQL על השרת. עקוב אחרי המדריך שלנו איך להתקין את MySQL על Ubuntu 20.04 כדי להגדיר זאת. המדריך הזה מניח שגם הגדרת משתמש MySQL לא-ראשי, כפי שמתואר בשלב 3 של המדריך.

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

התחברות ל-Mysql והגדרת מסד נתונים לדוגמה

אם מסד הנתונים שלך רץ על שרת מרוחק, התחבר ב-SSH לשרת מהמחשב המקומי שלך:

ssh sammy@your_server_ip

לאחר מכן, פתח את מסד הנתונים של MySQL, תחליף את sammy עם פרטי המשתמש שלך ב-Mysql:

mysql -u sammy -p

צור מסד נתונים בשם zooDB:

CREATE DATABASE zooDB;

אם בוצע יצירת מסד הנתונים בהצלחה, תקבל את הפלט הבא:

Output
Query OK, 1 row affected (0.01 sec)

כדי לבחור במסד הנתונים zooDB, הפעל את הפקודה הבאה של USE:

USE zooDB;

Output
Database changed

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

• guest_id: מאחסן ערכים עבור אורחים שביקרו בגן החיות, ומשתמש בסוג הנתונים int. זה משמש גם כמפתח ראשי של הטבלה, המשמעות של כל ערך בעמודה זו היא זיהוי ייחודי עבור השורה המקבילה שלו.
• first_name: מחזיק את השם הפרטי של כל אורח באמצעות סוג הנתונים varchar עם מקסימום של 30 תווים.
• last_name: משתמש בסוג הנתונים varchar, שוב במקסימום של 30 תווים, כדי לאחסן את שם המשפחה של כל אורח.
• guest_type: מכיל את סוג האורח (מבוגר או ילד) עבור כל אורח באמצעות סוג הנתונים varchar עם מקסימום של 15 תווים.
• membership_type: מייצג את סוג החברות של כל אורח, באמצעות סוג הנתונים varchar כדי להחזיק מקסימום של 30 תווים.
• membership_cost: מאחסן את העלות עבור סוגי החברות השונים. עמודה זו משתמשת בסוג הנתונים decimal עם דיוק של חמישה וסולם של שני, המשמעות שערכים בעמודה זו יכולים להכיל חמישה ספרות ושתי ספרות ליד נקודת העשרוניים.
• total_visits: משתמש בסוג הנתונים int כדי לרשום את מספר הביקורים הכולל מכל אורח.

צור טבלה בשם guests שמכילה כל אחת מהעמודות הללו על ידי ביצוע הפקודה CREATE TABLE הבאה:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

לאחר מכן, הכנס נתוני דוגמה לטבלה הריקה:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

כאשר אתה מכניס את הנתונים, אתה מוכן להתחיל להשתמש בשאילתות מקוננות ב־SQL.

שימוש בשאילתות מקוננות עם SELECT

ב־SQL, שאילתה היא פעולה המאחזרת נתונים מטבלה במסד נתונים וכוללת תמיד הצהרת SELECT. שאילתה מקוננת היא שאילתה מושלמת המוטבעת בתוך פעולה אחרת. שאילתה מקוננת יכולה להכיל את כל האלמנטים המשמשים בשאילתה רגילה, וכל שאילתה חוקית יכולה להיות מוטבעת בתוך פעולה אחרת כדי להפוך לשאילתה מקוננת. למשל, שאילתה מקוננת יכולה להיות מוטבעת בתוך פעולות INSERT ו־DELETE. בהתאם לפעולה, שאילתה מקוננת צריכה להיות מוטבעת על ידי סגירת ההצהרה בתוך מספר הודעות סוגריים כדי לעקוב אחר סדר הפעולות הספציפי. שאילתה מקוננת גם מועילה בתרחישים שבהם ברצונך לבצע מספר פקודות בשאילתה אחת, במקום כתיבת מספר פקודות כדי להחזיר את התוצאה(ות) הרצויות שלך.

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

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

אך שאילתה המשתמשת בתחביר זה תחזיר שגיאה:

Output
ERROR 1111 (HY000): Invalid use of group function

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

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

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

עם זאת, ניתן לקבל את אותו סט תוצאות עם שאילתה יחידה על ידי לוחצנים את השאילתה הראשונה (SELECT AVG(total_visits) FROM guests;) בתוך השני. יש לזכור כי בשאילות מקוננות, שימוש בכמות המתאימה של סוגריים נחוצה כדי להשלים את הפעולה שתרצה לבצע. זאת מכיוון שהשאילתה המקוננת היא הפעולה הראשונה שמתבצעת:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

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

שימוש בשאילתות מקוננות עם INSERT

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

להמחיש, נניח כי גן חיות מחוזק מבקש מידע על האורחים שלך משום שהם מעוניינים להציע הנחה של 15% לאורחים הרוכשים מנוי "תושב" במקומם. כדי לעשות זאת, השתמש ב־CREATE TABLE כדי ליצור טבלה חדשה בשם upgrade_guests שמכילה שש עמודות. שים לב רב עם הסוגים של הנתונים, כמו int ו־varchar, והתווים המקסימליים שהם יכולים להחזיק. אם הם לא מסתדרים עם סוגי הנתונים המקוריים מהטבלה guests שיצרת בחלק ה־הגדרת בסיס נתונים לדוגמה, אז תקבל שגיאה כאשר תנסה להכניס נתונים מהטבלה guests באמצעות שאילתה מקוננת והנתונים לא יועברו כהלכה. צור את הטבלה שלך עם המידע הבא:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

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

בפעולה זו, כתוב INSERT INTO והטבלה החדשה upgrade_guests, כך שיהיה הוראות ברורות לאיפה הנתונים מתכנסים. לאחר מכן, כתוב את השאילתה המקוננת שלך עם ההצהרה SELECT כדי לאחזר את ערכי הנתונים הרלוונטיים ו־FROM כדי לוודא שהם באים מהטבלה guests.

בנוסף, יש להחיל את ההנחה של 15% על כל אחד מהחברים ה"תושבים" על ידי כלול את הפעולה החשבונית של הכפל, *, כדי להכפיל ב־0.85, בתוך ההצהרה המקוננת (membership_cost * 0.85). לאחר מכן יש להשתמש במונח WHERE כדי למיין את הערכים בעמודת membership_type. ניתן להגביל את זה עוד יותר לתוצאות רק עבור חברויות "תושב" באמצעות הביטוי LIKE clause ולשים את סימן האחוז % לפני ואחרי המילה "תושב" בגרשיים יחידים כדי לבחור כל חברויות המתאימות לאותו אופן או, במקרה זה, אותו דיבור. השאילתה תיכתב כך:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

הפלט מציין כי התוויות חמישה נוספו לטבלת ה־upgrade_guests החדשה. כדי לאשר שהמידע שביקשת הועבר בהצלחה מטבלת ה־guests אל תוך טבלת ה־upgrade_guests הריקה שיצרת, ועם התנאים שציינת עבור השאילתה המקוננת והתנאי ה־WHERE, יש להפעיל את השאילתה הבאה:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

לפי הפלט מטבלת ה־upgrade_guests החדשה שלך, נכנס מידע הקשור לחברי האורחים מטבלת ה־guest באופן נכון. בנוסף, עלות החברות החדשה חושבה מחדש עם ההנחה של 15% המוחלטת. כתוצאה מכך, הפעולה הזו עזרה לפרק ולפתור את הקהל המתאים ויש לידו את המחירים המוזלים לשיתוף עם החברים החדשים המצפים.

שימוש בשאילתות מקוננות עם DELETE

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

התחל את הפעולה הזו עם הצהרת DELETE FROM כך שיהיה ברור מאיפה הנתונים נמחקים, במקרה זה, מטבלת upgrade_guests. לאחר מכן, השתמש במשפט ה-WHERE כדי למיין כל total_visits שהם יותר מהכמות שמוגדרת בשאילתה מקוננת. בשאילתה המקוננת המוטבעת שלך, השתמש ב-SELECT כדי למצוא את הממוצע, AVG, של total_visits, כך שמשפט ה-WHERE הקודם יכיל את ערכי הנתונים המתאימים להשוואה. לבסוף, השתמש ב-FROM כדי לאחזר את המידע הזה מהטבלה guests. ההצהרה המלאה על השאילתה תהיה כמו הבאה:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

אשר את הרשומות הללו נמחקו בהצלחה מהטבלה upgrade_guests והשתמש ב-ORDER BY כדי לארגן את התוצאות לפי total_visits בסדר ספרתי ועולה:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

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

מסקנה

שאילתות מקוננות שימושיות מכיוון שהן מאפשרות לך לקבל תוצאות גרנולריות ביותר שאחרת לא היית יכול לקבל רק דרך הרצת שאילתות נפרדות. בנוסף, שימוש בהצהרות INSERT ו־DELETE עם שאילתות מקוננות מספק לך דרך נוספת להוסיף או למחוק נתונים בשלב אחד. אם ברצונך ללמוד עוד על איך לארגן את הנתונים שלך, ראה את סדרת הפרקים שלנו על איך להשתמש ב־SQL.