מדריך מלא ודוגמאות מפורטות לפקודות SQL: מהיסודות ועד שאילתות מתקדמות

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

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

מה זה SQL ולמה הוא עדיין חיוני?

SQL, או שפת שאילתות מובנית, היא שפת שאילתות תכנות תוכנן במיוחד לניהול ותפעול של מסדי נתונים רלציוניים. למרות שפותח בשנות ה-70, הוא נותר הפתרון הסטנדרטי לתקשורת עם מסדי נתונים כגון MySQL, PostgreSQL, SQL Server, MariaDB ו-Oracle Database, בין היתר.

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

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

קטגוריות פקודות SQL: DQL, DML, DDL ו-DCL

לפני שנעבור לדוגמאות, כדאי לזכור ש ניתן לקבץ פקודות SQL לקטגוריות שונות בהתאם לתפקודן:

• DQL (שפת שאילתות נתונים): אלו המשפטים לשאילתת נתונים, כגון לבחור.
• DML (שפת מניפולציה של נתונים): הם מניפולציות רשומות, עם פקודות כמו INSERT, עדכון y למחוק.
• DDL (שפת הגדרת נתונים): הם מנהלים את מבנה מסד הנתונים, לדוגמה, ליצור, אלטר, DROP y קטע.
• DCL (שפת בקרת נתונים): הם שולטים בהרשאות ובגישה, באמצעות מענק y לְבַטֵל.

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

פקודות SQL עיקריות: יסודות ודוגמאות מעשיות

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

SELECT: מלך השאילתות

הפקודה לבחור זהו הבסיס עליו בנויות רוב הפעולות ב-SQL. הוא מאפשר לך לאחזר נתונים המאוחסנים בטבלה אחת או יותר.

תחביר כללי:

בחר עמודה1, עמודה2, ... משם_הטבלה;

כדי לקבל את כל העמודות בטבלה, פשוט השתמשו בכוכבית:

בחר * מעובדים;

אם ברצונך לסנן נתונים ספציפיים, תוכל להוסיף תנאים:

בחר שם, גיל FROM עובדים כאשר גיל > 30;

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

איפה: סינון תוצאות בצורה מדויקת

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

תחביר בסיסי:

בחר * מתוך מוצרים שבהם המחיר < 100;

ניתן לשלב תנאים באמצעות אופרטורים לוגיים כמו ו, OR y לא, כמו גם אופרטורי השוואה (=, <, >, <=, >=, <>).

לדוגמה, כדי לגייס עובדי מכירות מתחת לגיל 30:

בחר * מתוך עובדים כאשר מחלקה = 'מכירות' וגיל < 30;

INSERT: הוספת רשומות חדשות

עם INSERT ניתן להוסיף שורות חדשות לטבלה. זה חיוני להזנת מסדי הנתונים שלך במידע עדכני.

תחביר כללי:

הכנס לתוך מוצרים (שם, מחיר) ערכים ('מקלדת', 29.99);

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

הכנס לתוך מוצרים (שם, מחיר) ערכים ('עכבר', 15.50), ('צג', 120.00);

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

עדכון: שינוי נתונים קיימים

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

תחביר:

עדכון עובדים SET משכורת = משכורת * 1.05 WHERE מחלקה = 'שיווק';

בדוגמה זו, כל עובדי השיווק יראו את שכרם עולה ב-5%. זה קריטי. השתמש ב-WHERE בכל פעם שאתה מעדכןאלא אם כן אתה באמת רוצה לשנות את כל הטבלה.

מחיקה: מחיקת נתונים

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

לדוגמה:

מחק ממוצרים כאשר id_producto = 10;

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

סדר לפי: ארגון תוצאות

הסעיף מיין לפי משמש למיון תוצאות שאילתה לפי עמודה אחת או יותר. כברירת מחדל, הסדר יהיה בסדר עולה, אך ניתן לציין ASC o DESC כפי שאתה צריך.

בחר * מעובדים סדר לפי משכורת תיאור;

ניתן לשלב מספר עמודות במיון:

בחר * מתוך מוצרים סדר לפי קטגוריה עולה, מחיר תיאור;

פונקציות GROUP BY ו-Changement: SUM, AVG, MIN, MAX, COUNT

קבוצתי קבוצתי מאפשר לך לקבץ תוצאות לפי עמודה אחת או יותר, וזה מאוד שימושי בשילוב עם פונקציות כגון SUM () (תוֹסֶפֶת), ממוצע () (מְמוּצָע), MIN (מִינִימוּם), מקס (מקסימום) ו COUNT (סְפִירָה).

לדוגמה, כדי לדעת כמה מוצרים קיימים בכל קטגוריה:

בחר קטגוריה, COUNT(*) ממוצרים קבוצה לפי קטגוריה;

או כדי לחשב את החשבון הכולל עבור כל לקוח:

בחר customer_id, SUM(amount) FROM sales GROUP BY customer_id;

ניתן לסנן את הקבוצות המתקבלות באמצעות הפסוקית HAVING:

בחר מחלקה, ממוצע (שכר) מעובדים קבץ לפי מחלקה שיש לה ממוצע (שכר) > 2000;

LIMIT ו-OFFSET: הגבלת מספר התוצאות

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

בחר * מתוך מוצרים מגבלה של 10;

עם OFFSET ניתן לדלג על מספר שורות, שימושי לעמודים:

בחר * ממוצרים גבול 10 קיזוז 20;

בין, בתוך ודומה: תנאי WHERE מתקדמים

מילות מפתח אלו מרחיבות את אפשרויות הסינון:

• בֵּין: בחר ערכים בתוך טווח.

  בחר * מתוך עובדים כאשר משכורת בין 1000 ל-3000;

• לפי: חפש ערכים מרובים.

  בחר * מתוך מוצרים WHERE קטגוריה בתוך ('מחשבים', 'אלקטרוניקה');

• כמו: חפש דפוסים בטקסט.

  בחר * מתוך לקוחות כאשר שם כמו 'A%';

ניתן להשתמש גם בתווים כלליים כגון % (כל מספר תווים) ו _ (תו בודד) כדי להתאים אישית עוד יותר חיפושים באמצעות LIKE.

JOIN: מעבר מידע בין טבלאות

אחת התכונות הגדולות של SQL היא היכולת לשלב נתונים מטבלאות שונות באמצעות להצטרף. הנפוצים ביותר הם:

• הצטרפות פנימית: מחזירה שורות תואמות בשתי הטבלאות.
• הצטרף משמאל: מחזירה את כל השורות מהטבלה השמאלית וכל התאמה מהטבלה הימנית (עם מילוי ב-NULL אם אין התאמה).
• הצטרף ימינה: מחזירה את כל השורות מהטבלה הימנית ושורות תואמות מהטבלה השמאלית.
• הצטרפות חיצונית מלאה: מחזירה את כל השורות משתי הטבלאות (ממולאות ב-NULL כאשר אין התאמה).

דוגמה לחיבור פנימי:

SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

הצטרף משמאל:

בחר employees.name, sales.amount FROM employees LEFT JOIN sales ON employees.id = sales.employee_id;

ALTER, CREATE ו-DROP: שינוי מבנה מסד הנתונים

בנוסף לניהול נתונים, SQL מאפשר לך להגדיר ולשנות את המבנה של טבלאות ואובייקטים אחרים של מסד נתונים:

• צור: כדי ליצור אובייקטים חדשים (טבלאות, תצוגות, אינדקסים, משתמשים...).

  צור טבלה עם מוצרים (id INT PRIMARY KEY, שם VARCHAR(100), מחיר DECIMAL(8,2));

• לשנות: כדי לשנות את המבנה של אובייקט קיים.

  שינוי מוצרים בטבלה הוסף תיאור VARCHAR(255);

  מוצרי ALTER TABLE מחיר ALTER COLUMN SET NOT NULL;

• יְרִידָה: כדי למחוק אובייקטים ממסד הנתונים.

  מוצרי שולחן זרוק;

  DROP משתמש example_user CASCADE;

תצוגות (VIEW): שאילתות לשימוש חוזר וסינון מתקדם

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

צור תצוגה של עובדים_פעילים כ- SELECT * FROM עובדים כאשר פעיל = 1;

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

בחר * מתוך עובדים פעילים;

תצוגות אידיאליות לניהול הרשאות ולהצגת נתונים רלוונטיים למשתמשים מסוימים בלבד.

פונקציות צבירה וחישובים על עמודות

SQL מאפשר לך לעבוד עם פונקציות צבירה ואופרטורים אריתמטיים לביצוע חישובים על הנתונים:

• סְכוּם(): סכום כולל של עמודה.
• AVG(): ממוצע אריתמטי.
• MIN() ו-MAX(): מינימום ומקסימום.
• לספור(): מספר רשומות.

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

בחר מחלקה, COUNT(*) FROM עובדים GROUP BY מחלקה;

או השכר הגבוה ביותר לפי מחלקה:

בחר מחלקה, מקסימום (שכר) מעובדים קבץ לפי מחלקה;

ניתן אפילו ליצור עמודות מחושבות ישירות בשאילתה:

בחר שם, משכורת * 0.95 AS משכורת_נטו FROM עובדים;

שימוש בשמות חלופי (AS) עבור טבלאות ועמודות

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

לדוגמה:

SELECT e.name AS employee, d.name AS department FROM employees AS e JOIN departments AS d ON e.department_id = d.id;

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

דוגמאות לשאילתות SQL מתקדמות: פתרון מקרים מהעולם האמיתי

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

שאילתת כל העובדים במחלקה וסך המכירות שלהם

SELECT e.id, e.name, SUM(v.amount) AS total_sales FROM employees e JOIN sales v ON e.id = v.employee_id WHERE e.department = 'מכירות' GROUP BY e.id, e.name;

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

חישוב השכר הממוצע והמקסימלי לפי מחלקה

בחר מחלקה, ממוצע (שכר) כמשכורת_ממוצעת, מקסימום (שכר) כמשכורת_מקסימלית. FROM עובדים: GROUP BY מחלקה;

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

ספירת מוצרים לפי טווח מחירים באמצעות CASE

בחר מקרה כאשר המחיר < 50 אז 'זול' כאשר המחיר < 150 אז 'בינוני' ELSE 'יקר' END AS טווח_מחירים, COUNT(*) AS כמות מתוך מוצרים GROUP BY טווח_מחירים;

השתמש ב-CASE מאפשר לך ליצור קטגוריות מותאמות אישית בתוך השאילתה.

שאילתה לדוגמה למציאת רשומות כפולות

בחר שם, COUNT(*) מלקוחות GROUP BY שם בעל COUNT(*) > 1;

זה עוזר לך לשמור על מסד הנתונים שלך נקי וללא נתונים מיותרים.

זהה את המשכורת ה-n בגובהה (שאילתות משנה)

בחר משכורת עליונה 1 מתוך (בחר משכורת עליונה N נפרדת מעובדים סדר לפי משכורת תיאור) סדר לפי משכורת עולה;

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

מחיקת רשומות ישנות באופן אוטומטי

מחק מ יומני WHERE date < NOW() - מרווח זמן של 6 חודשים;

אידיאלי עבור מסדי נתונים של ייצור שבהם עליך לשלוט בגודל הטבלאות.

טיפים וטריקים שימושיים ב-SQL

שליטה ב-SQL לא אומרת רק לדעת איך לכתוב שאילתות בסיסיות. הנה כמה טיפים להפקת המרב מהשפה:

• השתמש תמיד ב-WHERE ב-UPDATE וב-DELETE כדי למנוע שינויים או מחיקות המוניות בשוגג.
• הוסף אינדקסים לעמודות הנפוצות ביותר כדי להאיץ את החיפושים וההצטרפות.
• העדיפו JOINs מפורשים על פני WHERE כדי לחבר טבלאות: מקל על תחזוקה וקריאות.
• שימוש בפונקציות צבירה וב-GROUP BY לדיווח וניתוח אוטומטיים מבלי להסתמך על גיליונות אלקטרוניים חיצוניים.
• בדיקה על מסד נתונים של בדיקות לפני ביצוע שינויים משמעותיים.
• שלוט בהרשאות באמצעות GRANT ו-REVOKE כדי להבטיח את אבטחת הנתונים שלך.
• נצלו את הנופים (VIEW) כדי לפשט את הגישה למידע מורכב ולרכז את הלוגיקה העסקית.
• אל תשתמשו יותר מדי ב-SELECT *, מכיוון שאתה עלול להכניס יותר נתונים מהנדרש ולהאט את המערכת.
• תגובות על שאילתות מורכבות כדי להקל על תחזוקה עתידית.

שאלות נפוצות על פקודות SQL בראיונות ובמקרים אמיתיים

שליטה ב-SQL חיונית למצוינות בראיונות טכניים ולפתרון בעיות יומיומיות. הנה כמה שאלות ותשובות אופייניות שיעזרו לכם להתכונן:

• מהו INNER JOIN?
  זהו ה-join המוגדר כברירת מחדל ב-SQL; הוא מחזיר שורות שתואמות לשתי הטבלאות. דוגמה:

  בחר * מחיבור פנימי B ב-A.id = B.a_id;

• למה משמש LEFT JOIN?
  מחזירה את כל השורות מהטבלה השמאלית וכל התאמה מהטבלה הימנית. דוגמה:

  בחר * משמאל לעובדים הצטרף למכירות ב- employees.id = sales.employee_id;

• מה ההבדל בין DROP ל-DELETE?
  למחוק מוחק שורות מטבלה, בעוד DROP מסיר לחלוטין את האובייקט (טבלה, תצוגה, משתמש) ממסד הנתונים.
• האם ניתן לבצע החזרה למצב קודם לאחר ALTER?
  לא, מכיוון ש-ALTER הוא DDL ומערכות רבות מבצעות commit אוטומטית. חשוב לוודא זאת לפני השימוש בו.
• מהן עמודות מדומה?
  אלו עמודות וירטואליות שנוצרות על ידי המערכת, כגון ROWID, USER או CURRVAL ב-Oracle.
• איך ליצור תצוגה?

  צור תצוגה view_employees כ- SELECT שם, משכורת FROM עובדים;

• כיצד להעניק הרשאות?

  הענק SELECT על מלאי לאנה, ריטה;

• איך מוחקים משתמש יחד עם הנתונים שלו?

  DROP משתמש משתמש CASCADE;

ביטויים רגולריים ואופרטורים מיוחדים ב-SQL

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

• %: מייצג כל מחרוזת בכל אורך (בשימוש ב-LIKE).
• _ : מייצג תו בודד (גם ב-LIKE).
• | : לסירוגין, לחפש דבר זה או אחר.
• *: חזרה על תו אפס פעמים או יותר.

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

ניהול מסד נתונים אינו מוגבל לשאילתות ושינוי נתונים. חיוני לשלוט במי יכול לגשת, לשנות או למחוק מידע באמצעות פקודות כמו מענק, לְבַטֵל, או ניהול מתקדם של תפקידים:

צור מנהל תפקידים;

הענק בחירת עובדים, הוספה ועדכון למנהל;

מנהל גרנט TO חואן, מריה;

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