ما پایگاه داده ها و زبان SQL را تجزیه و تحلیل می کنیم. (قسمت 6 - بررسی تکلیف نهایی) - "پروژه جاوا از A تا Z"

مقاله ای از مجموعه ای در مورد ایجاد یک پروژه جاوا (لینک به سایر مواد در پایان است). هدف آن تجزیه و تحلیل فناوری های کلیدی است که نتیجه آن نوشتن یک ربات تلگرام است. این بخش شامل تجزیه و تحلیل کار نهایی در پایگاه داده است. با سلام خدمت خوانندگان عزیز. امروز وظیفه آخرین مقاله در پایگاه داده را تجزیه و تحلیل خواهیم کرد. جالب است زیرا برای مصاحبه در سطح متوسط ​​در نظر گرفته شده است. یعنی بعد از این کار می‌توانید برای مصاحبه بروید و حداقل بخشی از آنچه که به پایگاه‌های اطلاعاتی رابطه‌ای مربوط می‌شود را با موفقیت پشت سر خواهید گذاشت. من می دانم که این مقاله چقدر می تواند ضروری باشد و بنابراین تمام تجربیاتم را برای مفید و جالب کردن آن به کار خواهم گرفت. و اگر در وسط مقاله خوابتان نبرد، به این معنی است که من به هدفم رسیدم. کل کار را تکرار نمی‌کنم: قبل از تکمیل هر کار، آن را نقل می‌کنم و زیر آن به صورت مورب خط می‌کشم. من انتظار دارم که همه کسانی که این مقاله را می‌خوانند، تمام پرس‌وجوها را در پایگاه داده خود اجرا کنند و همان چیزی را دریافت کنند. این حداکثر سود را برای تجارت به همراه خواهد داشت. و من از این فکر که به کسی در کار دشوارمان کمک کردم کمی خوشحالتر خواهم شد)

تمرین 1

یک اسکریپت SQL بنویسید تا جدول "Student" را با فیلدهای زیر ایجاد کنید: id (کلید اصلی)، نام، نام خانوادگی، ایمیل (یکتا). ما قبلاً این کار را انجام داده ایم، بنابراین نباید هیچ مشکلی وجود داشته باشد. در اسکریپت باید کلید اصلی و یک فیلد منحصر به فرد که با اصلی متفاوت است را مشخص کنید. ابتدا بیایید یک پایگاه داده جدید برای این کار ایجاد کنیم: $ CREATE DATABASE final_task; و بیایید از این پایگاه داده استفاده کنیم: $ USE final_task; هنگامی که محیط راه‌اندازی شد و برای اجرای کار آماده شد، می‌توانیم اسکریپت زیر را بنویسیم: $ CREATE TABLE student ( شناسه INT AUTO_INCREMENT، نام VARCHAR(40)، نام خانوادگی VARCHAR(50)، ایمیل VARCHAR(100)، کلید اولیه ( id), UNIQUE (ایمیل ) ); تا کنون، در مقایسه با آنچه قبلاً پشت سر گذاشته ایم، چیز جدیدی وجود ندارد. هر نظری غیر ضروری است، بیایید ادامه دهیم.

وظیفه 2-3

یک اسکریپت SQL بنویسید تا جدول «کتاب» با فیلدهای زیر ایجاد شود: شناسه، عنوان (id + عنوان = کلید اصلی). «Student» و «Book» را با یک رابطه «Student» یک به چند «کتاب» پیوند دهید. بیایید دو کار را در یک کار ترکیب کنیم تا آن را سریعتر و راحت تر کنیم. قبلاً در مقاله های قبلی درباره نحوه افزودن کلید خارجی جداگانه صحبت کرده ام. برای اضافه کردن، باید به یاد داشته باشیم که چگونه و از طریق چه ارتباطی برقرار می کنیم. مقاله قبلی به شما کمک خواهد کرد، و سپس این اسکریپت است: $ 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 یکسان در جدول وجود داشته باشد. و برای عنوان دقیقاً همینطور است.

وظیفه 4

یک اسکریپت SQL بنویسید تا جدول "Teacher" را با فیلدهای زیر ایجاد کنید: id (کلید اصلی)، نام، نام خانوادگی، ایمیل (یکتا)، موضوع. ما همچنان به آماده سازی پایگاه داده خود برای پرس و جوها ادامه می دهیم، جدول معلم ایجاد می کنیم: $ CREATE TABLE معلم( شناسه INT AUTO_INCREMENT، نام VARCHAR(30)، نام خانوادگی VARCHAR(30)، ایمیل VARCHAR(100)، موضوع VARCHAR(40)، کلید اصلی ( شناسه)، UNIQUE (ایمیل) )؛ تا اینجای کار سخت نیست، درست است؟ سه کار در حال حاضر تمام شده است!

وظیفه 5

«دانش‌آموز» و «معلم» را با یک رابطه «دانش‌آموز» چند به چند معلم پیوند دهید. حالا این جالب تر است! آخرین بار در مورد این موضوع صحبت کردیم. اجازه دهید به شما یادآوری کنم که برای رسیدن به این هدف چه چیزی لازم است: شما باید یک جدول میانی ایجاد کنید که جفت های دانش آموز و معلم را ذخیره کند. با کمک آن، ایجاد یک رابطه چند به چند امکان پذیر خواهد بود. بنابراین، بیایید یک جدول ایجاد کنیم student_x_techers . روش نامگذاری باز است و همچنین می تواند این باشد: student_teacher . $ CREATE TABLE student_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES معلم(ID)); همانطور که می بینید، همه چیز به وضوح و منسجم انجام می شود. ما یک کلید ترکیبی برای دو کلید خارجی داریم: student_id و teacher_id. چرا کلید خارجی هم؟ برای اینکه مطمئن شویم رکوردهایی در جداول دانش آموز و معلم برای جفت های در حال ثبت وجود دارد.

وظیفه 6

"دانشجو" را انتخاب کنید که "oro" در نام خانوادگی خود دارد، برای مثال "Sidorov"، "Voronovsky". برای اینکه برای ما جذاب و بصری شود، پیشنهاد می کنم ابتدا چند دانش آموز را اضافه کنیم تا برخی از آنها برای این درخواست مناسب باشند و برخی نه. بنابراین، بیایید کسانی را که باید در نتیجه درخواست شامل شوند، بنویسیم : $ INSERT INTO student (نام، نام خانوادگی، ایمیل) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (نام، نام خانوادگی، ایمیل) VALUES ('Nikolay'، 'Voronovsky'، 'nikolay.voronovsky@gmail.com'); و کسانی که نباید وارد شوند: $ INSERT INTO student (نام، نام خانوادگی، ایمیل) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (نام، نام خانوادگی، ایمیل) VALUES ('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); بیایید نتیجه را بررسی کنیم، به لیست داده ها در جدول دانش آموزان نگاه کنیم: $ SELECT * FROM student; و دریافت می کنیم: در مجموع چهار رکورد وجود دارد که دو تای آنها باید مناسب باشند و دو تای آنها نباید. پس از آماده کردن تمام داده‌ها برای درخواست، می‌توانیم درخواستی برای خود کار ارسال کنیم: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; در نتیجه، ایوان و نیکولای لیست را طی کردند.

وظیفه 7

در کار بعدی می خوانیم: از جدول 'Student' همه نام های خانوادگی ('last_name') و تعداد تکرارهای آنها را انتخاب کنید. در نظر بگیرید که نام هایی در پایگاه داده وجود دارد. مرتب سازی بر اساس مقدار به ترتیب نزولی. می بایست شبیه به این باشه:

نام خانوادگی	تعداد
پتروف	15
ایوانف	12
سیدوروف	3

برای وضوح، باید داده های بیشتری اضافه کنید. بدون مقدمه، بیایید پتروف ها، ایوانوف ها و سیدوروف ها را اضافه کنیم که از خویشاوندی خود اطلاعی ندارند؛) من یک آدرس ایمیل اختراع نمی کنم، فقط آن را از ورودی های جدید حذف می کنم. اجازه دهید دستور زیر را 12 بار اجرا کنیم: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); بیایید 15 پتروف اضافه کنیم: $ INSERT INTO student (نام، نام خانوادگی) VALUES ('Petr', 'Petrov'); و دو Sidorov (ما قبلاً یکی داریم))): $ 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 نام برتر را که بیشترین تکرار را دارند از "Student" انتخاب کنید. مرتب سازی بر اساس مقدار به ترتیب نزولی. می بایست شبیه به این باشه:

نام	تعداد
اسکندر	27
سرگئی	10
پیتر	7

آه، برای این منظور ما قبلاً ایوان ها، پیترز و سیدورها را داریم. بنابراین نیازی به اضافه کردن آنها نیست. ما قبلاً می دانیم که چگونه مرتب کنیم. تنها چیزی که امروز در مورد آن صحبت نکردیم این است که چگونه تعداد معینی از رکوردها را انتخاب کنیم. این قبلاً در راه حل های قبلی برای مشکلات پایگاه داده ظاهر شده است. برای کسانی که آن را نخوانده اند، بخوانند. برای بقیه، بیایید مستقیماً به اصل مطلب برویم: $ SELECT name, COUNT(*) به عنوان مقدار FROM student GROUP BY BY ORDER BY COUNT(*) DESC LIMIT 3; همانطور که از پرس و جو پیداست، اگر ترتیب عملگرها را در یک کوئری SELECT بدانید، برای اجرای چنین کوئری مشکلی وجود نخواهد داشت. و این وظیفه هنوز بر عهده ماست. و دانشی که قبلا ارائه شد برای حل این مشکل کاملاً کافی است.

وظیفه 9

شرط وظیفه: دانش آموزی را انتخاب کنید که بیشترین تعداد «کتاب» و «معلم» مرتبط را دارد. مرتب سازی بر اساس مقدار به ترتیب نزولی. می بایست شبیه به این باشه:

نام خانوادگی معلم	نام خانوادگی دانش آموز	مقدار کتاب
پتروف	سیدوروف	7
ایوانف	اسمیت	5
پتروف	کانکاوا	2

بنابراین، این کار به وضوح دشوارتر از کار قبلی است، درست است؟ جای تعجب نیست: بوی عضویت می دهد... و بیش از یک) ابتدا باید بفهمیم چه کار کنیم... واضح است که کمیت کتاب مستلزم گروه بندی است. اما چی؟ و چرا باید آنها را گروه بندی کنیم؟ پرس و جو شامل سه جدول، گروه بندی و مرتب سازی است. با قضاوت بر اساس این واقعیت که سوابقی که در آن کتاب وجود ندارد نشان داده نمی شوند، به این معنی است که شما باید از INNER JOIN استفاده کنید. همچنین درخواست LEFT JOIN را خواهیم داد تا مشکلی در این مورد وجود نداشته باشد. و چندین گزینه وجود دارد. اولین کاری که انجام می دهیم این است که سه جدول را به یک رکورد متصل می کنیم. سپس بر اساس دانش آموز گروه بندی می کنیم و نام معلم را به آن اضافه می کنیم. چه چیزی را انتخاب خواهیم کرد؟ نام استاد، شاگرد و تعداد کتاب. بیایید داده ها را برای درخواست اضافه کنیم:

• سه معلم؛
• ده کتاب؛
• ارتباط دو دانش آموز با سه معلم

سه معلم

$ INSERT INTO معلم (نام خانوادگی) VALUES ('Matvienko'); $ INSERT INTO معلم (نام خانوادگی) VALUES ('شوچنکو'); $ INSERT INTO معلم (نام خانوادگی) VALUES ('Vasilenko');

10 کتاب

من شناسنامه دانش آموزان اول و دوم را می گیرم. من کتابها را به آنها پیوست خواهم کرد. از آنجایی که AUTO_INCREMENT تنظیم نشده است، برای اینکه هر بار یک شناسه جدید بنویسید، باید موارد زیر را انجام دهید: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT. بعد، کتاب‌هایی را برای دانش‌آموز اول اضافه کنید: $ INSERT INTO book (عنوان، student_id) VALUES('book1', 1); $ INSERT INTO book (عنوان، student_id) VALUES('book2', 1); $ INSERT INTO book (عنوان، student_id) VALUES('book3', 1); $ INSERT INTO book (عنوان، student_id) VALUES('book4', 1); $ INSERT INTO book (عنوان، student_id) VALUES('book5', 1); $ INSERT INTO book (عنوان، student_id) VALUES('book6', 1); و کتاب برای دانش آموز دوم: $ INSERT INTO book (عنوان، student_id) VALUES('book7', 2); $ INSERT INTO book (عنوان، student_id) VALUES('book8', 2); $ INSERT INTO book (عنوان، student_id) VALUES('book9', 2); $ INSERT INTO book (عنوان، student_id) VALUES('book10', 2);

ارتباط معلم و دانش آموز

برای انجام این کار، student_x_teachers را به جدول اضافه کنید: $ INSERT INTO student_x_teachers VALUES (1,1); $INSERT INTO student_x_teachers VALUES(1,2); $INSERT INTO student_x_teachers VALUES(2,3);

بیایید درخواست را اجرا کنیم

ما مرحله اول را انجام می دهیم - سه جدول را به یک رکورد پیوند می دهیم: $ SELECT * FROM معلم tch INNER JOIN student_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 bON 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 student_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 as SELECT st.last_name,st.id,COUNT(*) as books 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 id = stch.student_id INNER JOIN معلم tch ON tch.id = stch.teacher_id; و حالا نتیجه این خواهد بود: ها! این یک درخواست است، درست است؟) همانطور که انتظار می رفت معلوم شد: دانش آموز با id=1 شش کتاب و دو معلم دارد و دانش آموز با id=2 چهار کتاب و یک معلم دارد.

وظیفه 10

شرایط: «معلمی» را انتخاب کنید که بیشترین تعداد «کتاب» را در بین همه «دانشجویان» خود دارد. مرتب سازی بر اساس مقدار به ترتیب نزولی. می بایست شبیه به این باشه:

نام خانوادگی معلم	مقدار کتاب
پتروف	9
ایوانف	5

در اینجا می توانیم از یک درخواست آماده از کار قبلی استفاده کنیم. چه چیزی را باید در مورد آن تغییر دهیم؟ ما قبلاً این داده ها را داریم، فقط باید یک گروه بندی دیگر اضافه کنیم و نام دانش آموز را از داده های خروجی حذف کنیم. اما ابتدا، بیایید یک دانش آموز دیگر را به معلم اضافه کنیم تا نتیجه جالب تر شود. برای این کار می نویسیم: $ INSERT INTO student_x_teachers VALUES (2, 1); و خود پرس و جو: $ SELECT tch.last_name به عنوان "Teacher"، SUM(sbw.books) به عنوان "Books" از studentbook معلم_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 student_x_teachers stch ON sbw.id = stch.student_id عضویت داخلی معلم tch ON tch.id = sttch.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM;s)(s) من بخشی را که اضافه کردم برجسته کرده ام. و در واقع نتیجه مورد انتظار: فقط واسیلنکو از این دور گذشت))

وظیفه 12

شرایط: همه 'نام_نام_خانگی' و 'نام' همه 'معلم' و 'دانشجو' را با فیلد 'نوع' (دانشجو یا معلم) چاپ کنید. به ترتیب حروف الفبا بر اساس «نام_نام خانوادگی» مرتب کنید. می بایست شبیه به این باشه:

نام خانوادگی	نوع
ایوانف	دانشجو
کانکاوا	معلم
اسمیت	دانشجو
سیدوروف	معلم
پتروف	معلم

یعنی ما باید دو خروجی را با هم ترکیب کنیم، و این دقیقاً همان چیزی است که UNION برای آن است. به عبارت دیگر، سوابق را از دانش‌آموزان و معلمان می‌گیریم و با هم چاپ می‌کنیم: $ SELECT last_name، 'معلم' به عنوان نوع از معلم UNION ALL last_name را انتخاب کنید، 'student' به عنوان نوع از دانش‌آموز ORDER BY last_name; و هم معلمان و هم دانش آموزان خواهند بود. به نظر می رسد همه چیز ساده است، اما این زمانی است که ما از قبل به نتیجه نگاه می کنیم. و بنابراین شما باید دو چیز را حدس بزنید.

وظیفه 13

شرط: یک ستون 'نرخ' را به جدول 'Student' موجود اضافه کنید، که درسی را که دانشجو در حال حاضر در آن است ذخیره می کند (مقدار عددی از 1 تا 6). ALTER TABLE دانش آموز افزودن محدودیت check_rate بررسی (نرخ > 0 و نرخ < 7); در اینجا ما از طریق ALTER TABLE و CHECK یک فیلد اضافه می کنیم تا حد این فیلد از 1 به 6 تنظیم شود.

وظیفه 14

شرایط: این مورد الزامی نیست، اما یک مزیت خواهد بود. تابعی بنویسید که تمام «کتابها» را طی کند و همه «عناوین» را که با کاما از هم جدا شده اند، خروجی دهد. در اینجا شما فقط باید یک خط را در نتیجه درخواست برگردانید که شامل تمام عناوین کتاب ها باشد. اینجا دوباره مجبور شدم گوگل کنم. چنین تابعی وجود دارد - GROUP_CONCAT ، که با آن این کار بسیار ساده انجام می شود: $ SELECT GROUP_CONCAT(title) from book; و تمام...)) همه 14 کار آماده هستند.

نتیجه گیری

آهان... آسون نبود. جالب بود. کارها ارزشش را داشتند، من مطمئنم. در حالی که ما این وظایف را انجام می دادیم، موارد زیادی را بررسی کردیم که قبلاً شناخته شده نبودند:

• SQL VIEW
• GROUP_CONCAT
• اتحاد. اتصال

و غیره از همه کسانی که قدرت خواندن و تکرار کارهای من را داشتند سپاسگزارم. چه کسی می داند چگونه بهتر درخواست کند - در نظرات بنویسید، من قطعا آنها را خواهم خواند)

فهرستی از تمام مواد این مجموعه در ابتدای این مقاله است.