אנו מנתחים מסדי נתונים ושפת SQL. (חלק 4 - בדיקת שיעורי בית) - "פרויקט ג'אווה מא' עד ת'"

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

למעשה, ניתוח שיעורי בית

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

1. הבן את האופרטור HAVING וכתוב שאילתה לדוגמה עבור הטבלאות מהדוגמה שלנו. אם אתה צריך להוסיף כמה שדות או יותר ערכים כדי שיהיה ברור יותר, הוסף אותם. אם מישהו רוצה, תכתוב את הפתרון לדוגמה שלך בתגובות - כדי שאוכל גם לבדוק אם יהיה לי זמן.
2. התקן את MySQL Workbench כדי לעבוד עם מסד הנתונים דרך ממשק המשתמש. אני חושב שכבר התאמנו מספיק בעבודה מהקונסולה. התחבר למסד הנתונים. אם אתה משתמש במשהו אחר כדי לעבוד עם מסד הנתונים, אל תהסס לדלג על משימה זו. כאן ובהמשך אשתמש רק ב-MySQL Workbench.
3. כתוב בקשות קבלה באמצעות הנתונים שלנו:
   1. המדינה הקטנה/מאוכלסת ביותר;
   2. מספר תושבים ממוצע בארץ;
   3. מספר ממוצע של תושבים במדינות ששמם מסתיים ב-"a";
   4. מספר המדינות עם אוכלוסייה של יותר מארבעה מיליון;
   5. מיון מדינות לפי ירידה במספר התושבים;
   6. מיון מדינות לפי שם בסדר טבעי.

בואו נדבר על HAVING

הכרת האופרטור Having יכולה לעזור לך לעבור יותר מראיון אחד שבו יהיו בעיות SQL. לכן, הבנתו חשובה ביותר. במקרה, אינך יכול להשתמש בתנאים לצבירה של פונקציות (SUM, MIN, MAX, AVG). בנוסף, HAVING משמש לשדות שמקובצים. מה זה אומר? לדוגמה, אם אנחנו רוצים להשיג מדינות שבהן מספר התושבים הממוצע בערים הוא יותר מ-50,000 תושבים, אנחנו לא יכולים בלי להשתמש ב-HAVING. למיטב הבנתי, הדבר נעשה מכיוון שהצבירה מתרחשת לאחר ביצוע הצהרת WHERE ואי אפשר להוסיף לה ערכי צבירה שיחושבו בהמשך. גם אם עד כה הדעות שלי לא מוסיפות הרבה להבנה, אפשר פשוט לקבל זאת כעובדה וללכת עם זה. בתכנות, קורה לא פעם שאם משהו לא ברור ברגע אחד, זה בהחלט יכול לומר שהמוח פשוט עדיין לא עיכל אותו. תישן עם המחשבה הזו, ולמחרת הכל יתבהר.

התקנת MySQL Workbench

כאן ובהמשך אשתמש ב-Workbench לשאילתות. אני אראה לך מה אתה צריך כדי להתקין וליצור חיבור למסד הנתונים. מדובר במוצר של אורקל, אז אתה רק צריך להיכנס לאתר שלהם ולבחור את הגרסה ומערכת ההפעלה הרצויה. לשם כך, בצע את הקישור הבא : כאן תוכל לבחור בדיוק את מערכת ההפעלה שאתה צריך. לחץ על הורד , אבל במקום להוריד נראה את החלון הזה: אל תלך לאיבוד, פשוט חפש את הכפתור שנקרא לא תודה, פשוט התחל את ההורדה שלי , וההורדה תתחיל. למה הם עושים את זה? כנראה, כדי שיותר אנשים ירשמו אצלם, זה לא חשוב לנו. לאחר ההורדה המוצלחת, הפעל את קובץ ההתקנה. ב-MacOS זה נראה כך: פשוט גרור את הסמל וזהו, ההתקנה הושלמה. לא קשה כמו התקנת MySQL עצמה, נכון? או שפשוט התרגלנו והפכנו מנוסים יותר ;) החלק השני של המשימה הזו הוא ליצור חיבור למסד הנתונים שלנו. מה צריך בשביל זה? לחץ על סימן הפלוס ליד MySQL Connections: בחלון שמופיע, הזן את הנתונים הדרושים:

• שם החיבור - שם החיבור שלנו. כתבו שמות ברורים ככל האפשר כדי שלא יהיו בעיות בזיהוי בהמשך. אני נותן לחיבור הזה את השם JRTB_DB ;
• שם מארח - כבר יוגדר כ-local 127.0.0.1 (aka localhost). במקרה שלנו, אין צורך לשנות דבר, שכן מסד הנתונים מותקן על המחשב, אך אם מסד הנתונים נמצא במקום אחר, אזי צריך לשנות את המארח (ip של המחשב עליו פועל מסד הנתונים);
• שם משתמש - ניתן גם לציין את המשתמש שלך במידת הצורך. אם לא הוספת שום דבר במפתח זה, השאר אותו ללא שינוי;
• סיסמה - לחץ על אחסן במחזיק מפתחות והגדר בדיוק את הסיסמה שהגדרת לעצמך. השארתי הכל פשוט - שורש .

כדי לבדוק האם החיבור יעבוד, לחצו על Test Connection : ובכן, אם הכל בוצע כמו שצריך, התוצאה לא תאחר לבוא: כעת יש לנו חיבור שמור במסד הנתונים, ולא נצטרך ליצור חיבור ולמלא בשם ובסיסמא בכל פעם. והאושר הזה ייראה כך: אנחנו נכנסים לחיבור החדש שנוצר ורואים את חלון הבקשה. כדי להיות בטוחים שהכל תקין, בואו נבדוק את רשימת מאגרי המידע, נלך אל שלנו ונקבל את כל הנתונים על הערים: והנה אנחנו מקבלים Carte Blanche למה שאנחנו צריכים. החלק הראשון אחראי על התסריט שנזין. לאחר מכן, ב- Result Grid , אנו רואים את התוצאה של הפעולה האחרונה בסקריפט. ו- Action Output מציג רשימה של פעולות והתוצאה שלה. דבר שימושי מאוד, אני רוצה לומר לך: בעזרתו אתה יכול לפקח על מהירות הביצוע של סקריפטים מסוימים. למה זה חשוב? אחת הבעיות הנפוצות ביותר במהירות ביצוע המשימות באפליקציה היא מהירות ביצוע השאילתה במסד הנתונים. כאן תוכלו לבדוק אותם במהירות ובנוחות ביד.

אנו כותבים את הבקשות הדרושות

יש לנו רק 7 בקשות להשלים, בוא נלך!

1. קבלו את המדינה המאוכלסת ביותר. כאן אתה יכול ללכת בערמומיות וללכת בכמה מסלולים:

• לפי טבלת הארץ

אז אנחנו רק צריכים למיין את השאילתה שלנו לפי אוכלוסיה ולקחת רק רשומה אחת. לשם כך, עליך להוסיף אופרטור LIMIT בסוף הסקריפט ולציין את הכמות הנדרשת: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;

• לפי טבלת העיר

הכל יותר מעניין כאן, כי הבקשה תהיה מורכבת יותר, אבל גם מעניינת יותר. מכיוון שעדיין אין לנו מושג לגבי הצטרפות, נוכל לקבל רק את מזהה המדינה: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1; כאן עשינו דבר מגניב - אספנו את סכום האוכלוסייה של כל הערים המוכרות בכל מדינה, מיינו לפי הסכום הזה ולקחנו את האלמנט הראשון. ובכן, זה נהדר? אני שמח :D אחרי זה אתה מיד מרגיש כמו גורו שאילתה... (לא להרבה זמן, כמובן))

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

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

• לפי טבלת העיר

$ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) LIMIT 1;

• לפי טבלת הארץ

$ SELECT * ממדינה הזמנה לפי מגבלת אוכלוסייה 1; ותראה את התוצאה בעצמך!

3. מספר תושבים ממוצע בארץ

כאן שוב המפרט הטכני איכשהו לא מדויק, כאילו המנהל כתב אותו... למה החלטתי את זה? כי לא ברור באיזה טבלה לעבוד. אבל זה נורמלי: פשוט אין משימות שבהן הכל יהיה ברור ומובן מיד. לכן, עליך לקרוא בעיון את המשימות, ואם יש לך שאלות, שאל אותן מיד! נכון, הערה. בהתחשב בנתונים שיש לנו במאגר, נחפש באמצעות נתונים מערים. כדי לעשות זאת, כתוב את השאילתה הבאה: $ SELECT country_id, AVG(population) FROM city GROUP BY country_id; זה פשוט כאן: אנו משתמשים בפונקציה AVG ומקבצים את רשומות העיר שלנו לפי מדינה.

4. מספר ממוצע של תושבים במדינות ששמם מסתיים ב-a

יהיה כאן שינוי קל לפי בקשה. עלינו להוסיף סינון לפי שמות לפני שנבצע קיבוץ. אני מכין שיעורי בית, כמו כל התלמידים, לפני פרסום המאמר הזה, ואני מבין שאי אפשר לפתור את הבעיה הזו בלי הצטרפות. למה? כי בנוסף לתעודת זהות המדינה, אנחנו צריכים לקבל גם את שמו. ולא ניתן לעשות זאת מבלי לחבר שני טבלאות לרשומה אחת. לכן, אני אעשה את המשימה הזו, כמובן, אבל זה ה-jamb שלי...))) רציתי להמציא משימה באמצעות LIKE בבקשה...) $ SELECT ci.country_id, AVG(ci.population ) FROM city ci INNER JOIN country co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id; מה קרה פה? ראשית, צירפנו רשומות מטבלאות העיר והמדינה באמצעות מפתח הזר country_id, מסונן לפי שמות מדינות כך שהם מסתיימים ב-"a", ורק לאחר מכן מקובצים לפי country_id.

5. מספר מדינות שאוכלוסייתן עולה על ארבעה מיליון

כאן אנחנו רק צריכים להשתמש בפונקציה COUNT ולהוסיף סינון על אוכלוסייה: $ SELECT COUNT(*) ממדינה WHERE אוכלוסייה > 4000000; כתוצאה מכך, אנו מגלים שיש 3 מדינות כאלה האם זה נכון? כן, רק מולדובה לא עוברת את ציון הדרך הזה.

6. מיין מדינות לפי ירידה במספר התושבים

לשם כך, עלינו להשתמש באופרטור ORDER BY שאנו כבר מכירים. אבל יש לזכור כי כברירת מחדל המיון הוא בסדר טבעי. עבור מספרים זה אומר שהוא ממוין בסדר עולה, עבור מחרוזות זה אומר שהוא ממוין החל מהתווים הראשונים. אם אנחנו צריכים מיון יורד, אנחנו צריכים את היפוך של הטבעי: $ SELECT * FROM country ORDER BY population DESC;

7. מיין מדינות לפי שם בסדר טבעי

זה המקום שבו ידע מה הסדר הטבעי יהיה שימושי. מכיוון שזו ברירת המחדל, זה חתיכת עוגה עבורנו: $ SELECT * FROM country ORDER BY name;

במקום פלט

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

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