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

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

תרגיל 1

כתוב סקריפט SQL כדי ליצור את טבלת 'סטודנט' עם השדות הבאים: id (מפתח ראשי), שם, שם משפחה, דואר אלקטרוני (ייחודי). כבר עשינו את זה, אז לא אמורות להיות בעיות. בסקריפט צריך לציין את המפתח הראשי ושדה ייחודי השונה מהראשי. ראשית, בואו ניצור מסד נתונים חדש עבור משימה זו: $ CREATE DATABASE final_task; ובואו נשתמש במסד הנתונים הזה: $ USE final_task; לאחר הגדרת הסביבה ומוכנה להפעיל את העבודה, נוכל לכתוב את הסקריפט הבא: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNIQUE (email ) ); עד כה, אין חדש בהשוואה למה שכבר עברנו. כל הערה מיותרת, בואו נמשיך הלאה.

משימה 2-3

כתוב סקריפט SQL כדי ליצור את טבלת 'ספר' עם השדות הבאים: id, title (מזהה + כותרת = מפתח ראשי). קשר את 'תלמיד' ו'ספר' עם מערכת יחסים 'תלמיד' אחד לרבים 'ספר'. בואו נשלב שתי משימות לאחת כדי להפוך אותה למהירה ונוחה יותר. כבר דנתי כיצד להוסיף מפתח זר בנפרד במאמרים קודמים. כדי להוסיף, אנחנו צריכים לזכור איך אנחנו יוצרים קשרים ודרך מה. המאמר הקודם יעזור לך, ואז הנה התסריט: $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id ) ); בצורה פשוטה זו, הוספנו מפתח מורכב עבור הטבלה שלנו PRIMARY KEY (id, title) , כעת המפתח יהיה בדיוק הצמד. המשמעות היא שיכול להיות יותר מערך שדה מזהה זהה אחד בטבלה. ובדיוק אותו דבר לגבי הכותרת.

משימה 4

כתוב סקריפט SQL כדי ליצור את טבלת 'מורה' עם השדות הבאים: id (מפתח ראשי), שם, שם_משפחה, דואר אלקטרוני (ייחודי), נושא. אנו ממשיכים להכין את מסד הנתונים שלנו לשאילתות, יוצרים טבלת מורים: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), נושא VARCHAR(40), PRIMARY KEY ( id), UNIQUE (אימייל) ); עד עכשיו זה לא קשה, נכון? שלוש משימות כבר הסתיימו!

משימה 5

קשר את 'תלמיד' ו'מורה' עם מערכת יחסים 'תלמיד' רבים-לרבים מורה'. עכשיו זה יותר מעניין! בדיוק דיברנו על זה בפעם הקודמת. הרשו לי להזכיר לכם מה נדרש כדי להשיג זאת: עליכם ליצור טבלת ביניים שתאחסן זוגות תלמיד-מורים. בעזרתו ניתן יהיה ליצור מערכת יחסים של רבים לרבים. לכן, בואו ניצור טבלה students_x_techers . גישת השמות פתוחה ויכולה להיות גם: student_teacher . $ CREATE TABLE students_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (תלמיד_id, teacher_id), FOREIGN KEY (תלמיד_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES teacher(id) ); כפי שאתה יכול לראות, הכל נעשה בצורה ברורה וקוהרנטית. יש לנו מפתח מורכב לשני מפתחות זרים: student_id ו- teacher_id. למה גם מפתח זר? על מנת שנהיה בטוחים שקיימים רשומות בטבלאות התלמידים והמורים עבור הזוגות הנרשמים.

משימה 6

בחר 'תלמיד' שיש לו 'אורו' בשם המשפחה שלהם, למשל 'סידורוב', 'וורונובסקי'. כדי שזה יהיה מעניין וויזואלי עבורנו, אני מציע להוסיף תחילה מספר תלמידים, כדי שחלקם יתאימו לבקשה הזו, וחלקם לא. לכן, בואו נרשום את מי שצריך להיכלל כתוצאה מהבקשה: $ INSERT INTO student (שם, שם_שם, מייל) VALUES ('איבן', 'סידורוב', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); ומי שלא צריך להיכנס: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); בואו נבדוק את התוצאה, תסתכל על רשימת הנתונים בטבלת התלמידים: $ SELECT * FROM student; ואנחנו מקבלים: יש ארבעה רשומות בסך הכל, שניים מהם צריכים להתאים ושניים לא. לאחר שהכנו את כל הנתונים לבקשה, נוכל להגיש בקשה למשימה עצמה: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; כתוצאה מכך, איבן וניקולאי עברו על הרשימה.

משימה 7

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

שם משפחה	כַּמוּת
פטרוב	15
איבנוב	12
סידורוב	3

למען הבהירות, עליך להוסיף נתונים נוספים. בלי להכביר מילים, בואו נוסיף את הפטרובים, האיבנובים והסידורובים, שאינם יודעים את קרבתם ;) אני לא אמציא כתובת מייל, אני פשוט אוציא אותה מהערכים החדשים. הבה נריץ את הפקודה הבאה 12 פעמים: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); בואו נוסיף 15 פטרובים: $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); ושני סידורובים (כבר יש לנו אחד)): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); הנתונים מוכנים כעת. כדי לקבל נתונים כאלה, אתה צריך לעשות קיבוץ; כדי לעשות קיבוץ, אתה צריך להשתמש באופרטור Group By, ואתה צריך לעשות זאת לפי השדה last_name. אתה יכול גם לשים לב שמספר החזרות מוגדר ככמות , וכאן אתה גם צריך לזכור איך עושים כינויים ב-SQL: $ SELECT last_name, COUNT(*) ככמות FROM student GROUP BY last_name ORDER BY COUNT(*) DESC ; אז הגזמתי עם הפטרובים - התברר שזה 16))

משימה 8

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

שֵׁם	כַּמוּת
אלכסנדר	27
סרגיי	10
פיטר	7

הו, למטרה זו יש לנו כבר איוואנים, פיטרס וסידורים. לכן אין צורך להוסיף אותם. אנחנו כבר יודעים למיין. הדבר היחיד שלא דיברנו עליו היום הוא איך לבחור מספר מסוים של רשומות. זה כבר הופיע בפתרונות קודמים לבעיות במסד נתונים. למי שלא קרא, קרא. לשאר, ניגש ישר לנקודה: $ SELECT שם, COUNT(*) ככמות FROM student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; כפי שניתן לראות מהשאילתה, אם אתה יודע את סדר האופרטורים בשאילתת SELECT, לא יהיו בעיות בביצוע שאילתה כזו. והמשימה הזו עדיין תלויה בנו. והידע שהוצג קודם לכן מספיק כדי לפתור בעיה זו.

משימה 9

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

שם המשפחה של המורה	שם המשפחה של התלמיד	כמות הספר
פטרוב	סידורוב	7
איבנוב	נַפָּח	5
פטרוב	קנקבה	2

אז, ברור שהמשימה הזו קשה יותר מהקודמת, נכון? לא פלא: זה מריח כמו הצטרפות... ויותר מאחד) ראשית, צריך להבין מה לעשות... ברור שכמות הספר דורשת קיבוץ. אבל מה? ולמה אנחנו צריכים לקבץ אותם? השאילתה כוללת שלוש טבלאות, קיבוץ ומיון. אם לשפוט לפי העובדה שרשומות שבהן אין ספרים לא מוצגות, זה אומר שאתה צריך לקחת INNER JOIN. כמו כן, נגיש בקשה ל-LEFT JOIN כדי שלא יהיו בעיות עם זה. ויש כמה אפשרויות. הדבר הראשון שאנו עושים הוא לחבר שלושה טבלאות לרשומה אחת. לאחר מכן, אנו מקבצים לפי תלמיד ומוסיפים לו את שם המורה. במה נבחר? שם המורה, התלמיד ומספר הספרים. בואו נוסיף נתונים עבור הבקשה:

• שלושה מורים;
• עשרה ספרים;
• לחבר שני תלמידים עם שלושה מורים.

שלושה מורים

$ INSERT INTO teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO teacher(last_name) VALUES ('Shevchenko'); $ INSERT INTO teacher(last_name) VALUES ('Vasilenko');

10 ספרים

אני אקח את תעודות הזהות של תלמיד ראשון ושני. אני אצרף להם ספרים. מאחר ש-AUTO_INCREMENT לא הוגדר, כדי לא לכתוב מזהה חדש בכל פעם, עליך לבצע את הפעולות הבאות: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; לאחר מכן, הוסף ספרים עבור התלמיד הראשון: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO book (title, student_id) VALUES('book2', 1); $ INSERT INTO book (title, student_id) VALUES('book3', 1); $ INSERT INTO book (title, student_id) VALUES('book4', 1); $ INSERT INTO book (title, student_id) VALUES('book5', 1); $ INSERT INTO book (title, student_id) VALUES('book6', 1); וספרים לתלמיד השני: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO book (title, student_id) VALUES('book8', 2); $ INSERT INTO book (title, student_id) VALUES('book9', 2); $ INSERT INTO book (title, student_id) VALUES('book10', 2);

קשרי מורה-תלמיד

לשם כך, הוסף לטבלה students_x_teachers: $ INSERT INTO students_x_teachers VALUES (1,1); $INSERT INTO students_x_teachers VALUES(1,2); $INSERT INTO students_x_teachers VALUES(2,3);

בואו ליישם את הבקשה

אנו עושים את השלב הראשון - אנו מקשרים שלוש טבלאות לרשומה אחת: $ SELECT * FROM teacher tch INNER JOIN students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; כמובן, עדיין אין לנו רשומות, עם זאת, אנו יכולים לראות שחיברנו בהצלחה שלוש טבלאות. כעת אנו מוסיפים קיבוץ ספרים, מיון ואת השדות הדרושים לנו: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) בתור ספרים FROM student st INNER JOIN book b ON st.id = b.student_id INNER JOIN students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN מורה tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY books DESC; אבל אנחנו מקבלים שגיאה ב-SQL ואת התשובה הבאה: קוד שגיאה: 1055. ביטוי מס' 1 של רשימת SELECT אינו בסעיף GROUP BY ומכיל עמודה לא מצטברת 'final_task.tch.last_name' שאינה תלויה פונקציונלית בעמודות ב-GROUP BY סעיף זה לא עובד קח את האלמנטים האלה כי יש מערכת יחסים של הרבה לרבים בין המורה לתלמיד. וזה נכון: אנחנו לא יכולים לקבל רק מורה אחד לכל תלמיד. אז בואו נלך לכיוון השני. בוא נשתמש במשהו שנקרא View SQL. מה הרעיון: אנחנו יוצרים תצוגה נפרדת, שהיא טבלה חדשה, כבר עם הקיבוץ שאנחנו צריכים. ולטבלה זו נוסיף את שמות המורים הדרושים. אבל אנחנו לוקחים בחשבון את העובדה שייתכן שיש יותר ממורה אחד, ולכן הערכים יחזרו על עצמם. צור תצוגה: $ CREATE VIEW studentBooks כ-SELECT st.last_name,st.id,COUNT(*) כספרים FROM student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; לאחר מכן, אנו עובדים עם תצוגה זו כטבלה פשוטה הכוללת שלושה שדות: שם_משפחה, זיהוי_תלמיד וספירת ספרים. לפי תעודת הזהות של התלמיד, נוכל להוסיף מורה גם באמצעות שני חיבורים: $ SELECT tch.last_name בתור 'Teacher', sbw.last_name 'Student', sbw.books כ'Books' מתוך studentbook sbw INNER JOIN students_x_teachers stch ON sbw. id = stch.student_id INNER JOIN מורה tch ON tch.id = stch.teacher_id; ועכשיו התוצאה תהיה: הא! זו בקשה, נכון?) יצא כצפוי: לתלמיד עם id=1 יש שישה ספרים ושני מורים, ולתלמיד עם id=2 יש ארבעה ספרים ומורה אחד.

משימה 10

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

שם המשפחה של המורה	כמות הספר
פטרוב	9
איבנוב	5

כאן נוכל להשתמש בבקשה מוכנה מהמשימה הקודמת. מה אנחנו צריכים לשנות בזה? יש לנו כבר את הנתונים האלה, אנחנו רק צריכים להוסיף קיבוץ נוסף ולהסיר את שם התלמיד מנתוני הפלט. אבל ראשית, בואו נוסיף עוד תלמיד אחד למורה כדי להפוך את התוצאה למעניינת יותר. לשם כך, אנו כותבים: $ INSERT INTO students_x_teachers VALUES (2, 1); והשאילתה עצמה: $ SELECT tch.last_name בתור 'מורה', SUM(sbw.books) בתור 'ספרים' מתוך ספר סטודנטים sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN מורה tch ON tch.id = stch . teacher_id GROUP BY tch.id; כתוצאה מכך, אנו מקבלים: למורה וסילנקו יש 10 ספרים, ולשבצ'נקו יש 6...)

משימה 11

תנאי: בחר 'מורה' שמספר 'הספרים' שלו עבור כל 'תלמידיו' הוא בין 7 ל-11. מיין לפי כמות בסדר יורד. זה אמור להיראות כך:

שם המשפחה של המורה	כמות הספר
פטרוב	אחד עשר
סידורוב	9
איבנוב	7

זה המקום שבו נשתמש ב-HAVING. דיברנו עליו. הבקשה תהיה בדיוק כמו בעבר, רק שצריך להוסיף את התנאי שמספר הספרים יהיה בטווח מסוים. וכפי שאמרתי במאמרים קודמים, כשאנחנו צריכים לעשות סינון במהלך קיבוץ ו/או על פונקציות צבירה, אנחנו צריכים להשתמש ב-HAVING : $ SELECT tch.last_name בתור 'Teacher', SUM(sbw.books) בתור 'Books' מתוך studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id GROUP לפי tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books;) הדגשתי את החלק שהוספתי. ולמעשה, התוצאה הצפויה: רק וסילנקו עבר את הסיבוב הזה))

משימה 12

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

שם משפחה	סוּג
איבנוב	סטוּדֶנט
קנקבה	מוֹרֶה
נַפָּח	סטוּדֶנט
סידורוב	מוֹרֶה
פטרוב	מוֹרֶה

כלומר, אנחנו צריכים לשלב שני תפוקות, ובשביל זה בדיוק נועד UNION. במילים אחרות, ניקח רשומות מתלמידים וממורים ונדפיס ביחד: $ SELECT last_name, 'teacher' כסוג מהמורה UNION ALL בחר last_name, 'תלמיד' כסוג מתלמיד ORDER BY last_name; ויהיו גם מורים וגם תלמידים. נראה שהכל פשוט, אבל זה כשאנחנו כבר מסתכלים על התוצאה. אז אתה צריך לנחש שני דברים.

משימה 13

תנאי: הוסף עמודה 'שיעור' לטבלת 'סטודנט' הקיימת, שתשמור את הקורס בו הסטודנט נמצא כעת (ערך מספרי מ-1 עד 6). ALTER TABLE תלמיד ADD CONSTRAINT check_rate CHECK (שיעור > 0 ושיעור < 7); כאן אנו מוסיפים שדה דרך ALTER TABLE ו-CHECK כדי להגדיר את המגבלה על שדה זה מ-1 עד 6.

משימה 14

מצב: פריט זה אינו חובה, אך יהיה יתרון. כתוב פונקציה שתעבור על כל 'הספרים' ותוציא את כל ה'כותרות' מופרדות בפסיקים. כאן אתה רק צריך להחזיר שורה כתוצאה מהבקשה, שתכיל את כל כותרות הספרים. כאן שוב הייתי צריך לחפש בגוגל. ישנה פונקציה כזו - GROUP_CONCAT , שבה זה נעשה בצורה פשוטה מאוד: $ SELECT GROUP_CONCAT(title) מתוך ספר; וזהו...)) כל 14 המשימות מוכנות.

מסקנות

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

• תצוגת SQL
• GROUP_CONCAT
• הִתאַחֲדוּת

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

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