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

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

• בודק שיעורי בית
  • הוספת מפתח ראשי
  • הוספת מולדובה
  • צור את טבלת הערים
  • הוסף מפתח זר לערים
  • חלק בונוס. בדיקה
• הצהרת SELECT
  • פרמטר WHERE
  • ORDER BY פרמטר
  • פרמטר GROUP BY
• שיעורי בית
• סיכום

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

בודק שיעורי בית

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

1. עליך להוסיף מפתח ראשי (PRIMARY KEY) משדה המזהה לסכימת טבלת המדינה.
2. הוסף עוד מדינה לטבלת המדינות - מולדובה.
3. על פי הסכימה של המאמר הקודם, צור עיר טבלה, שתכיל את כל השדות המתוארים. שמות השדות יהיו כדלקמן: id, name, country_id, population.
4. הוסף מפתח ראשי לטבלת העיר.
5. הוסף מפתח זר לטבלת העיר.

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

הוספת מפתח ראשי

ניתן להוסיף מפתח ראשי (PRIMARY KEY) בשתי דרכים: מיד בעת יצירת טבלה, או לאחר היצירה, באמצעות ALTER TABLE.

מפתח ראשי במהלך יצירת הטבלה

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

• צור מסד נתונים חדש:

  מבחן $CREATE DATABASE;

• צור טבלה המוסיף מפתח ראשי:

  $ CREATE TABLE country(id INT, name VARCHAR(30), PRIMARY KEY (id));

באופן כללי, שום דבר לא מסובך. לאחר הצהרת המשתנים, נוסף החלק הבא PRIMARY KEY (id) , כאשר שם השדה שיהיה המפתח הראשי מועבר בסוגריים. ובואו נראה כיצד השתנתה סכימת הטבלה: $ DESC country; כפי שאתה יכול לראות, הערך PRI הופיע בשדה מפתח עבור ערך המזהה .

מפתח ראשי לאחר יצירת הטבלה

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

• בוא נלך למסד הנתונים שלנו מהבדיקה:

  ערים $USE;

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

  אוכלוסיית $ DESC;

הכל נכון, הטבלה היא שלנו. בוא נכתוב את הדברים הבאים:

$ ALTER TABLE מדינה הוסף מפתח ראשי (מזהה);

• ובדוק את זה מיד עם הפקודה:

  $DESC מדינה;

כפי שניתן לראות מהתמונה, הכל נכון, ערך ה-PRI הוא בדיוק היכן שהוא צריך להיות. אגב, עבדנו עם מאגר בדיקות. עכשיו אנחנו צריכים למחוק את זה: למה אנחנו צריכים לבלבל את השרת, נכון? לשם כך, אנו משתמשים בפקודה ידועה למדי: $ DROP DATABASE test;

הוספת מולדובה

ראשית עלינו להחליט מה נרשום. המזהה הבא שלנו יהיה 4. השם יהיה מולדובה, ואוכלוסייתה היא 3550900. לכן, אנו מבצעים את הפקודה INSERT INTO שאנו כבר מכירים: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); ואנחנו בודקים אם הערך הזה נמצא בדיוק במסד הנתונים: $ SELECT * FROM country WHERE id = 4; בבקשת הנתונים קבעתי מיד באיזה שדה יחפשו, אז קיבלנו רק רשומה אחת, וזה מה שהיינו צריכים.

צור את טבלת הערים

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

• id - מזהה ייחודי;
• שם - שם עיר;
• country_id - מפתח זר למדינה;
• אוכלוסייה - אוכלוסיית העיר.

זה קצת מלחיץ לכתוב כל פעם תעודת זהות ייחודית, אתה לא חושב? אני רוצה להשאיר זאת לרשויות MySQL . ויש דרך כזו - AUTO INCREMENT . צריך להוסיף את זה לתחום הדיגיטלי, ואם לא נעביר את הערכים במפורש, MySQL עצמה תגדיל את ה-ID באחד לעומת הקודם. לכן, יצירת טבלה תיראה כך: $ CREATE TABLE city ( id INT AUTO_INCREMENT, שם VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); הבה נסתכל בתרשים הטבלה כדי לראות אם הכל נעשה כהלכה: $ DESC city; כפי שניתן לראות מתרשים הטבלה, יש לנו תיאור חדש לשדה id - auto_increment. אז עשינו הכל נכון. בואו נבדוק את הנתונים בטבלה מוגדרת במלואה. לשם כך, נעשה את החלק האחרון של המשימה - המפתח הזר.

הוסף מפתח זר לערים

עבור מפתח זר תהיה הפקודה הזו: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); ובואו מיד נבדוק מה לא בסדר בסכימת הטבלה: האם היא השתנתה במהלך שעה? $DESC עיר;

חלק בונוס. בדיקה

שכחתי להוסיף למשימה - מלאו את הנתונים שהיו בצילום המסך של החלק הראשון. שכחתי, אז עכשיו אני אעשה את זה בעצמי. ולמי שמעוניין אפשר לעשות את זה לבד בלעדיי ואז נבדוק ;) היו חרקוב, קייב, מינסק, אודסה, וורונז', ונוסיף גם את קישינב. אבל הפעם לא נעביר מזהים, נדלג עליהם: $ INSERT INTO city (name, country_id, population) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('אודסה', 1, 1017699), ('וורונז', 2, 1058261), ('קישינב', 4, 695400); כפי שאתה יכול לראות, אתה יכול לבצע מספר ערכים בו-זמנית באמצעות פקודת INSERT INTO אחת. דבר שימושי, זכרו) ומיד בואו נראה מה יש בטבלה: $ SELECT * FROM city; AUTO_INCREMENT - עבד בדיוק כפי שרצינו. קבצי המזהה ממולאים כולם, למרות שלא הגשנו אותם. מפתח זר הוא דבר תלוי. כדי לבדוק אם זה עובד כמו שצריך, אתה יכול לנסות לכתוב מפתח זר שלא קיים בטבלה הזר. נניח שנחליט ש-id = 5 הוא קזחסטן. אבל במציאות זה לא נמצא בטבלת המדינות. וכדי לבדוק שמסד הנתונים ישבע, הוסף את העיר - אסטנה: $ INSERT INTO city (name, country_id, population) VALUES ('Astana', 5, 1136156); ובאופן טבעי אנו מקבלים את השגיאה: כעת המפתח הזר מוודא שלא ננסה להקצות מדינה לעיר שאינה במסד הנתונים שלנו. חלק זה של שיעורי הבית יכול להיחשב הושלם - קדימה לחדש :)

הצהרת SELECT

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

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

כאן אתה יכול לראות שאתה לא יכול לשים קודם את האופרטור GROUP BY ולאחר מכן את האופרטור WHERE. צריך לזכור זאת כדי שבהמשך לא תהיה טינה על טעויות שלא ברור מאיפה הן באות. $SELECT * מהעיר; אבל לגרד את כל הנתונים ברור שזה לא כיף לנו. זה בדיוק אותו הדבר אם היינו רוצים לתקוע מסמרים במיקרוסקופ [1] , [2] . מכיוון שבסיס הנתונים מבצע פעולות סינון, מיון וצבירה הרבה יותר מהר מקוד Java, עדיף להשאיר את העניין הזה למסד הנתונים. לכן, על ידי סיבוך המשימות נפתח פונקציונליות חדשה.

פרמטר WHERE

כדי לסנן בחירה, נעשה שימוש במילה WHERE . יש לפרש זאת באופן הבא: SELECT * FROM tablename (בחר את כל השדות מתוך הטבלה שם table) WHERE talbe_row = 1 (כאשר ברשומות שדה table_row שווה ל-1). חשוב לציין כי יש חשיבות לסדר מילות המפתח בשאילתה. אינך יכול לכתוב WHERE a =1 FROM table_name SELECT *. עבור השפה הרוסית זה בסדר, ולחלק זה אולי לא נראה כל כך רע, אבל עבור SQL זה לא מקובל. אנו כותבים את השאילתה הבאה: $ SELECT * FROM city WHERE country_id = 1; ובחרנו בערים אוקראיניות. לא נורא, נכון? מה אם נרצה לא רק אוקראינית, אלא גם בלארוסית? לצורך כך, נוכל לרשום את אוסף הערכים שהשדה יכול לקחת: $SELECT * FROM city WHERE country_id IN(1, 3); וכבר יש לנו ערים משתי מדינות בתגובה. מה אם יש מספר תנאים לסינון? נניח שאנחנו רוצים ערים עם אוכלוסייה של יותר משני מיליון? כדי לעשות זאת, השתמש במילים OR ו- AND : $ SELECT * FROM city WHERE country_id IN (1, 3) ואוכלוסיה > 2000000; נהדר, אבל מה אם נצטרך להוסיף עוד תנאי - לחפש שמות באמצעות ביטוי רגולרי (לא אתאר כאן ביטויים רגולריים: הנה אדם שעשה זאת "בקצרה" ב-4 חלקים )? לדוגמה, אנו זוכרים איך מאייתים עיר, אבל לא לגמרי... לשם כך, ניתן להוסיף את מילת המפתח LIKE לביטוי הסינון : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR שם LIKE "%hark%"; ובדרך זו קיבלנו גם את חרקוב. כתוצאה מכך, אנו יכולים לומר שהחיפוש שלנו התברר כטוב מאוד. אבל הייתי רוצה למיין לא לפי תעודת זהות, אלא לפי אוכלוסיה, אבל איך? כן פשוט מאוד...

ORDER BY פרמטר

באמצעות ORDER BY, נוכל למיין את הרשומות שקיבלנו לפי שדה ספציפי. זה ממיין גם מספרים וגם מחרוזות. הבה נרחיב את השאילתה הקודמת, נמיין לפי אוכלוסייה, נוסיף ORDER BY אוכלוסייה: $ SELECT * FROM city WHERE country_id IN (1, 3) ואוכלוסיה > 2000000 או שם כמו "%hark%" ORDER BY population; כפי שאנו יכולים לראות, המיון התרחש בסדר טבעי, כלומר בסדר עולה. מה אם אנחנו רוצים את ההיפך? כדי לעשות זאת, עליך להוסיף את המילה DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) ואוכלוסיה > 2000000 או שם כמו "%hark%" ORDER BY population DESC; כעת המיון מבוסס על צמצום אוכלוסיה. ומסד הנתונים עושה זאת מהר מאוד: לא ניתן להשוות שם Collections.sort . כעת נמיין לפי שורה, לפי שם בסדר הפוך: $ SELECT * FROM city WHERE country_id IN (1, 3) ואוכלוסיה > 2000000 או שם כמו "%hark%" סדר לפי שם DESC;

פרמטר GROUP BY

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

• COUNT - מספר רשומות, ניתן להשתמש ללא קיבוץ, משמש כ- COUNT(*) . במקרה של קיבוץ לפי שדה כלשהו - COUNT(groupped_field);
• MAX - מוצא את הערך המקסימלי עבור שדה ספציפי;
• MIN - מוצא את הערך המינימלי עבור שדה ספציפי;
• SUM - מוצא את הסכום עבור שדה ספציפי;
• AVG - מוצא את הערך הממוצע.

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

שיעורי בית

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

משימות:

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

סיכום

היום דנו בפירוט בשיעורי הבית מהשיעור האחרון. יתרה מכך, אני רואה בכך חשיבות הן עבור אלה שלא עשו זאת והן עבור אלה שעשו זאת. עבור הראשונים, זו הזדמנות לגלות את התשובה, ולאחרונים, להשוות אותה לתוצאה שלך. הירשם לחשבון GitHub שלי כדי להישאר מעודכן בשינויים בפרויקט. אני אחזק את כל בסיס הקוד שם. הכל יתקיים בארגון הזה . לאחר מכן, דנו בהצהרת SELECT. הוא הכי חשוב לנו. דרכו יעברו כל הבקשות לנתונים, ועלינו להבין זאת. הדבר החשוב ביותר הוא לזכור את סדר הוספת הפרמטרים (WHERE, ORDER BY, GROUP BY וכן הלאה). כן, לא סיפרתי כל מה שאפשר, אבל לא הצבתי לעצמי יעד כזה. כן, אני יודע שאתה כבר להוט לכתוב בקשה. היה סבלני, זה כל מה שאתה צריך. גם בשביל הפרויקט וגם בשביל הצמיחה המקצועית שלך. בזמן שאתה מחכה, וודא ש-Git כבר מוכר לך. אני אשתמש בו כברירת מחדל, ככלי ידוע. תודה לכולם על הקריאה. במאמר הבא נדבר על חיבורים והצטרפות למסד נתונים. שם יהיו המשימות המגניבות))

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