כיצד לכתוב שאילתות SQL Access של Microsoft Access מאפס

כיצד לכתוב שאילתות SQL Access של Microsoft Access מאפס

Microsoft Access הוא ללא ספק הכלי החזק ביותר בכל חבילת Microsoft Office, אך עם זאת הוא מטריף (ולפעמים מפחיד) את משתמשי הספק של Office. עם עקומת למידה תלולה יותר מ- Word או Excel, כיצד מישהו אמור לכרוך את ראשו סביב השימוש בכלי זה? ברוס אפר יבחן השבוע כמה מהנושאים שעוררים שאלה זו מאחד הקוראים שלנו.





קורא שואל:

אני מתקשה בכתיבת שאילתה ב- Microsoft Access. יש לי מסד נתונים עם שתי טבלאות מוצרים המכילות עמודה משותפת עם קוד מוצר מספרי ושם מוצר משויך. אני רוצה לברר אילו מוצרים מלוח א 'ניתן למצוא. בטבלה ב 'אני רוצה להוסיף טור בשם תוצאות המכיל את שם המוצר מטבלה א' אם הוא קיים, ואת שם המוצר מטבלה ב 'כאשר הוא אינו קיים בטבלה א' האם יש לך עצה?



התשובה של ברוס:

Microsoft Access היא מערכת ניהול מסדי נתונים (DBMS) המיועדת לשימוש במכונות Windows ו- Mac כאחד. הוא משתמש במנוע מסד הנתונים של Jet של Microsoft לעיבוד נתונים ואחסון. הוא גם מספק ממשק גרפי למשתמשים שכמעט מבטל את הצורך בהבנת שפת שאילתות מובנית (SQL).





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

נקודת התחלה

אם עדיין אין לך היכרות כלשהי עם Access או RDBMS אחר, הייתי מציע לך להתחיל במשאבים אלה לפני שתמשיך:



  • אז מהו מסד נתונים? שם ריאן דוב משתמש באקסל כדי להציג את היסודות של מאגרי מידע יחסיים.
  • מדריך מהיר לתחילת העבודה עם Microsoft Access 2007 המהווה סקירה כללית ברמה גבוהה של Access והרכיבים המרכיבים מסד נתונים של Access.
  • מדריך מהיר לטבלאות ב- Microsoft Access 2007 בוחן את יצירת מסד הנתונים והטבלאות הראשונים שלך לאחסון הנתונים המובנים שלך.
  • מדריך מהיר לשאילתות ב- Microsoft Access 2007 בוחן את האמצעים להחזרת חלקים ספציפיים של הנתונים המאוחסנים בטבלאות מסד הנתונים.

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

קשרי מסד נתונים ונורמליזציה

תארו לעצמכם שאתם מנהלים חברה שמוכרת 50 סוגים שונים של ווידג'טים בכל רחבי העולם. יש לך בסיס לקוחות של 1,250 ובחודש ממוצע תמכור 10,000 יישומונים ללקוחות אלה. אתה משתמש כעת בגיליון אלקטרוני יחיד כדי לעקוב אחר כל המכירות הללו - למעשה טבלת מסד נתונים אחת. וכל שנה מוסיף אלפי שורות לגיליון האלקטרוני שלך.



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



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

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

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

בטבלה המקורית שבה הסרנו נתונים אלה, נוסיף טור עבור מפתח חוץ (ClientID) וזה מה שמקשר לשורה הנכונה המכילה את המידע עבור לקוח מסוים זה.

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

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

הצטרף לסוגים

SQL מגדיר חמישה סוגים שונים של צירופים: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER ו- CROSS. מילת המפתח OUTER הינה אופציונלית במשפט SQL.

Microsoft Access מאפשרת שימוש ב- INNER (ברירת מחדל), LEFT OUTER, RIGHT OUTER ו- CROSS. FULL OUTER אינו נתמך ככזה, אך באמצעות LEFT OUTER, UNION ALL ו- RIGHT OUTER, ניתן לזייף אותו במחיר של יותר מחזורי מעבדים ופעולות קלט/פלט.

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

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

נתחיל ביצירת שני טבלאות, ProdA ו- ProdB, עם מאפייני העיצוב הבאים.

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

כעת, תאכלס אותם בכמה נתונים.

כדי להראות את ההבדלים באופן הפעולה של 3 סוגי ההצטרפות, מחקתי ערכים 1, 5 ו -8 מ- ProdA.

הַבָּא, ליצור שאילתה חדשה על ידי ללכת ל צור> עיצוב שאילתות . בחר את שתי הטבלאות מתיבת הדו -שיח הצג טבלה ו- לחץ על הוסף , לאחר מכן סגור .

לחץ על ProductID בטבלת ProdA, גרור אותו ל- ProductID בטבלה ProdB ושחרר את לחצן העכבר ליצירת הקשר בין הטבלאות.

לחץ באמצעות לחצן העכבר הימני על השורה בין הטבלאות המייצגות את הקשר בין הפריטים ל- בחר הצטרף למאפיינים .

כברירת מחדל, סוג הצטרפות 1 (INNER) נבחר. אפשרות 2 היא הצטרפות חיצונית שמאלית ו -3 היא צירוף ימני חיצוני.

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

במעצב השאילתות, בחר את השדות שברצוננו לראות מתוך הרשימות הנפתחות.

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

שימו לב שהתוצאות מציגות רק ערכים שבהם ProductID שווה בשתי הטבלאות. למרות שיש ערך עבור ProductID = 1 בטבלת ProdB, הוא אינו מופיע בתוצאות מכיוון ש- ProductID = 1 אינו קיים בטבלת ProdA. אותו דבר לגבי ProductID = 11. הוא קיים בטבלת ProdA אך לא ב- ProdB בטבלה.

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

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

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

כפי שאתה יכול לראות, כל ערך בטבלת ProdA מיוצג בתוצאות ואילו רק אלה ב- ProdB שיש להם ערך ProductID תואם בטבלת ProdB מופיעים בתוצאות.

המרחב הריק בעמודה ProdB.ProductName הוא ערך מיוחד (NULL) מכיוון שאין ערך תואם בטבלה ProdB. זה יתברר כחשוב בהמשך.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

נסה את אותו הדבר עם סוג ההצטרפות השלישי (RIGHT OUTER).

התוצאות מציגות הכל החל בטבלה ProdB בזמן שהיא מציגה ערכים ריקים (המכונים NULL) שבהם לטבלת ProdA אין ערך תואם. עד כה זה מביא אותנו הכי קרוב לתוצאות הרצויות בשאלת הקורא שלנו.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

שימוש בפונקציות בשאילתה

ניתן גם להחזיר את תוצאות הפונקציה כחלק משאילתה. אנו רוצים שעמודה חדשה בשם 'תוצאות' תופיע במערך התוצאות שלנו. הערך שלה יהיה התוכן של העמודה ProductName בטבלה ProdA אם ל- ProdA יש ערך (הוא אינו NULL), אחרת יש לקחת אותו מהטבלה ProdB.

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

מבנה הפונקציות המלא למצבנו נראה כך:

IIF(ProdA.ProductID Is Null, ProdB.ProductName,ProdA.ProductName)

שימו לב שפרמטר התנאי אינו בודק שוויון. ערך Null במסד נתונים אינו בעל ערך שניתן להשוותו לכל ערך אחר, כולל Null אחר. במילים אחרות, Null אינו שווה ל- Null. אֵיִ פַּעַם. כדי להתגבר על זה, אנו במקום זאת בודקים את הערך באמצעות מילת המפתח 'Is'.

יכולנו גם להשתמש ב- 'Is Not Null' ולשנות את סדר הפרמטרים True and False כדי לקבל את אותה התוצאה.

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

קוד ה- SQL המקביל לשם כך יהיה:

SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

כעת, כאשר נריץ את השאילתה הזו, היא תניב את התוצאות הללו.

כיצד לשנות משתמש ברירת מחדל ב- chrome

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

לקבלת משאבים נוספים ללימוד Microsoft Access, עיין במאמר של ג'ואל לי כיצד ללמוד Microsoft Access: 5 משאבים מקוונים בחינם.

לַחֲלוֹק לַחֲלוֹק צִיוּץ אימייל האם כדאי לשדרג ל- Windows 11?

Windows עוצב מחדש. אבל האם זה מספיק כדי לשכנע אותך לעבור מ- Windows 10 ל- Windows 11?

קרא הבא נושאים קשורים
  • פִּריוֹן
  • שאל את המומחים
על הסופר ברוס אפר(13 מאמרים פורסמו)

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

עוד מאת ברוס אפר

הירשם לניוזלטר שלנו

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

לחצו כאן להרשמה