Biz ma'lumotlar bazalarini va SQL tilini tahlil qilamiz. (6-qism - Yakuniy topshiriqni tekshirish) - "Java loyihasi A dan Z gacha"

Java loyihasini yaratish haqidagi turkum maqola (boshqa materiallarga havolalar oxirida). Uning maqsadi - asosiy texnologiyalarni tahlil qilish, natijada telegram botini yozish. Ushbu qism ma'lumotlar bazasidagi yakuniy topshiriqning tahlilini o'z ichiga oladi. Assalomu alaykum, aziz o'quvchilar. Bugun biz ma'lumotlar bazasidagi oxirgi maqoladagi vazifani tahlil qilamiz. Bu qiziq, chunki u o'rta darajadagi intervyular uchun mo'ljallangan. Ya'ni, ushbu topshiriqdan so'ng siz allaqachon intervyuga borishingiz mumkin va siz relyatsion ma'lumotlar bazalari bilan bog'liq bo'lgan narsaning kamida bir qismini muvaffaqiyatli topshirasiz. Men ushbu maqola qanchalik zarurligini bilaman va shuning uchun uni foydali va qiziqarli qilish uchun barcha tajribamni sarflayman. Va agar siz maqolaning o'rtasida uxlamasangiz, bu mening maqsadimga erishganimni anglatadi. Men butun vazifani takrorlamayman: har bir topshiriqni bajarishdan oldin uni kursiv bilan chizib, iqtibos keltiraman. Men ushbu maqolani o'qigan har bir kishi o'z ma'lumotlar bazasidagi barcha so'rovlarni bajarishini va bir xil narsani olishini kutaman. Bu biznesga maksimal foyda keltiradi. Va men qiyin ishimizda kimgadir yordam berdim degan fikrdan biroz xursand bo'laman)

1-mashq

Quyidagi maydonlar bilan "Talaba" jadvalini yaratish uchun SQL skriptini yozing: id (asosiy kalit), ism, familiya, e_mail (noyob). Biz buni allaqachon qildik, shuning uchun hech qanday muammo bo'lmasligi kerak. Skriptda siz asosiy kalitni va asosiydan farq qiladigan noyob maydonni ko'rsatishingiz kerak. Birinchidan, ushbu vazifa uchun yangi ma'lumotlar bazasini yaratamiz: $ CREATE DATABASE final_task; Va keling, ushbu ma'lumotlar bazasidan foydalanamiz: $ USE final_task; Muhit sozlangan va ishni bajarishga tayyor bo‘lgach, biz quyidagi skriptni yozishimiz mumkin: $ CREATE TABLE student (id INT AUTO_INCREMENT, nomi VARCHAR(40), familiya VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNIQUE (elektron pochta ) ); Hozircha biz boshidan kechirgan narsalar bilan solishtirganda yangi narsa yo'q. Har qanday izohlar keraksiz, keling, davom etamiz.

2-3-topshiriq

Quyidagi maydonlar bilan "Kitob" jadvalini yaratish uchun SQL skriptini yozing: id, sarlavha (id + sarlavha = asosiy kalit). "Talaba" va "Kitob" ni "Talaba" bir-ko'p "Kitob" munosabati bilan bog'lang. Tezroq va qulayroq qilish uchun ikkita vazifani birlashtiramiz. Alohida xorijiy kalitni qanday qo'shishni men oldingi maqolalarda muhokama qildim. Qo'shimcha qilish uchun biz qanday qilib va ​​nima orqali bog'lanishimizni eslashimiz kerak. Oldingi maqola sizga yordam beradi, keyin esa mana skript: $ CREATE TABLE kitobi ( id INT, sarlavha VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, nom), FOREIGN KEY (talaba_identifikatori) ADABIYOTLAR talaba (id) )); Ushbu oddiy usulda biz jadvalimizga PRIMARY KEY (id, sarlavha) uchun kompozit kalit qo'shdik , endi kalit aynan juftlik bo'ladi. Bu shuni anglatadiki, jadvalda bir nechta bir xil id maydoni qiymati bo'lishi mumkin. Sarlavha uchun ham xuddi shunday.

Vazifa 4

Quyidagi maydonlar bilan "O'qituvchi" jadvalini yaratish uchun SQL skriptini yozing: id (asosiy kalit), ism, familiya, e_mail (noyob), mavzu. Biz maʼlumotlar bazasini soʻrovlar uchun tayyorlashda davom etamiz, oʻqituvchilar jadvalini yaratamiz: $ CREATE TABLE oʻqituvchi( id INT AUTO_INCREMENT, ism VARCHAR(30), familiya VARCHAR(30), email VARCHAR(100), mavzu VARCHAR(40), PRIMARY KEY ( id), UNIQUE (elektron pochta) ); Hozircha bu qiyin emas, to'g'rimi? Uchta vazifa allaqachon tugadi!

Vazifa 5

“Talaba” va “O‘qituvchi”ni “Talaba” ko‘p-ko‘p o‘qituvchi” munosabati bilan bog‘lang. Endi bu qiziqroq! Biz bu haqda oxirgi marta gaplashgan edik. Bunga erishish uchun nima kerakligini eslatib o'taman: siz o'quvchi va o'qituvchi juftlarini saqlaydigan oraliq jadval yaratishingiz kerak. Uning yordami bilan ko'p-ko'p munosabatlarni yaratish mumkin bo'ladi. Shuning uchun, keling, student_x_techers jadvalini yarataylik . Nomlash yondashuvi ochiq va u ham bo'lishi mumkin: student_teacher . $ CREATE TABLE student_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, BIRINCHI KEY (talaba_identifikatori, o'qituvchi_identifikatori), FOREIGN KEY (talaba_identifikatori) REFERANSLAR talaba(identifikatori), FOREIGN KEY (o'qituvchi_identifikatori) REFERANSLAR o'qituvchisi(id)); Ko'rib turganingizdek, hamma narsa aniq va izchil amalga oshiriladi. Bizda ikkita xorijiy kalit uchun kompozit kalit mavjud: student_id va teacher_id. Nega ham chet el kaliti? Talabalar va o'qituvchilar jadvallarida qayd etilayotgan juftliklar uchun yozuvlar mavjudligiga ishonch hosil qilishimiz uchun.

Vazifa 6

Familiyasida "oro" bo'lgan "Talaba" ni tanlang, masalan, "Sidorov", "Voronovskiy". Biz uchun qiziqarli va vizual bo'lishi uchun men birinchi navbatda bir nechta talabalarni qo'shishni taklif qilaman, shunda ulardan ba'zilari ushbu so'rovga mos keladi, ba'zilari esa mos kelmaydi. Shuning uchun, so'rov natijasida kiritilishi kerak bo'lganlarni yozamiz : $ INSERT INTO talaba (ismi, familiyasi, elektron pochta manzili) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO talaba (ismi, familiyasi, elektron pochta manzili) QIYMATLAR ('Nikolay', 'Voronovskiy', 'nikolay.voronovsky@gmail.com'); Kirmasligi kerak bo'lganlar: $ INSERT INTO talaba (ismi, familiyasi, elektron pochta manzili) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO talaba (ismi, familiyasi, elektron pochta manzili) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Natijani tekshiramiz, talabalar jadvalidagi ma'lumotlar ro'yxatini ko'rib chiqamiz: $ SELECT * FROM student; va biz olamiz: jami to'rtta yozuv bor, ulardan ikkitasi mos kelishi kerak va ikkitasi mos kelmasligi kerak. So'rov uchun barcha ma'lumotlarni tayyorlab bo'lgach, biz topshiriqning o'zi uchun so'rov yuborishimiz mumkin: $ SELECT * FROM student WHERE familiyasi '%oro%' LIKE; Natijada, Ivan va Nikolay ro'yxatni ko'rib chiqishdi.

Vazifa 7

Keyingi vazifani o'qiymiz: "Talaba" jadvalidan barcha familiyalarni ("familiya_ismi") va ularning takrorlanish sonini tanlang. Ma'lumotlar bazasida nomlar mavjudligini hisobga oling. Miqdor bo'yicha kamayish tartibida tartiblang. Bu shunday ko'rinishi kerak:

familiya	miqdori
Petrov	15
Ivanov	12
Sidorov	3

Aniqlik uchun siz qo'shimcha ma'lumotlarni qo'shishingiz kerak. Ko'p gaplarni davom ettirmasdan, qarindoshliklarini bilmagan Petrovlar, Ivanovlar va Sidorovlarni qo'shamiz ;) Men elektron pochta manzilini o'ylab topmayman, shunchaki yangi yozuvlardan chiqarib tashlayman. Quyidagi buyruqni 12 marta bajaramiz: $ INSERT INTO talaba (ism, familiya) VALUES ('Ivan', 'Ivanov'); 15 Petrovni qo'shamiz: $ INSERT INTO talaba (ismi, familiyasi) VALUES ('Petr', 'Petrov'); Va ikkita Sidorov (bizda allaqachon bitta))): $ INSERT INTO talaba (ism, familiya) VALUES ('Sidor', 'Sidorov'); Ma'lumotlar hozir tayyor. Bunday ma'lumotlarni olish uchun siz guruhlashni amalga oshirishingiz kerak, guruhlash uchun siz Group By operatoridan foydalanishingiz kerak va buni familiya maydoni bo'yicha bajarishingiz kerak. Takrorlashlar soni miqdor sifatida belgilanishini ham ko'rishingiz mumkin va bu erda siz SQLda taxalluslarni qanday qilishni ham eslab qolishingiz kerak: $ SELECT familiya, COUNT(*) miqdor sifatida Talabalar guruhidan familiya bo'yicha TARTIB COUNT(*) DESC ; Shunday qilib, men buni Petrovlar bilan haddan tashqari oshirdim - 16 bo'ldi))

Vazifa 8

Shart: "Talaba" dan eng ko'p takrorlanadigan 3 ta ismni tanlang. Miqdor bo'yicha kamayish tartibida tartiblang. Bu shunday ko'rinishi kerak:

nomi	miqdori
Iskandar	27
Sergey	10
Piter	7

Oh, bu maqsadda bizda allaqachon Ivans, Peters va Sidors bor. Shuning uchun ularni qo'shishning hojati yo'q. Biz qanday qilib saralashni allaqachon bilamiz. Bugun biz gaplashmagan yagona narsa - ma'lum miqdordagi yozuvlarni qanday tanlash kerakligi. Bu allaqachon ma'lumotlar bazasi bilan bog'liq muammolarning oldingi echimlarida paydo bo'lgan. O'qimaganlar uchun o'qing. Qolganlari uchun to'g'ridan-to'g'ri fikrga o'tamiz: $ SELECT nomi, COUNT(*) miqdori sifatida FROM student GROUP BY BY ORDER BY COUNT(*) DESC LIMIT 3; So'rovdan ko'rinib turibdiki, agar siz SELECT so'rovida operatorlar tartibini bilsangiz, bunday so'rovni bajarishda hech qanday muammo bo'lmaydi. Va bu vazifa hali ham bizning zimmamizda. Va ilgari taqdim etilgan bilim bu muammoni hal qilish uchun etarli.

9-topshiriq

Vazifa sharti: Eng ko'p "Kitob" va "O'qituvchi" bilan bog'langan "Talaba" ni tanlang. Miqdor bo'yicha kamayish tartibida tartiblang. Bu shunday ko'rinishi kerak:

O'qituvchining familiyasi	Talabaning familiyasi_	Kitob miqdori
Petrov	Sidorov	7
Ivanov	Smit	5
Petrov	Kankava	2

Xo'sh, bu vazifa avvalgisidan ko'ra qiyinroq, to'g'rimi? Buning ajablanarli joyi yo'q: qo'shilish kabi hid keladi ... va bir nechta) Birinchidan, nima qilish kerakligini tushunishimiz kerak ... Kitobning miqdori guruhlashni talab qilishi aniq. Lekin nima? Va nega biz ularni guruhlashimiz kerak? So'rov uchta jadval, guruhlash va saralashni o'z ichiga oladi. Kitoblar bo'lmagan yozuvlar ko'rsatilmaganiga ko'ra, bu siz INNER JOINni olishingiz kerakligini anglatadi. Shuningdek, biz bu borada hech qanday muammo bo'lmasligi uchun LEFT JOIN uchun so'rov yuboramiz. Va bir nechta variant mavjud. Biz qiladigan birinchi narsa uchta jadvalni bitta yozuvga birlashtirishdir. Keyinchalik, biz talaba bo'yicha guruhlaymiz va unga o'qituvchining ismini qo'shamiz. Biz nimani tanlaymiz? O'qituvchi, talabaning nomi va kitoblar soni. Keling, so'rov uchun ma'lumotlarni qo'shamiz:

• uchta o'qituvchi;
• o'nta kitob;
• ikkita o'quvchini uchta o'qituvchi bilan bog'lash.

Uchta o'qituvchi

$ INSERT INTO o'qituvchi(familiya) VALUES ('Matvienko'); $ INSERT INTO o'qituvchi(familiya) VALUES ('Shevchenko'); $ INSERT INTO o'qituvchi(familiya) VALUES ('Vasilenko');

10 kitob

Men 1 va 2 o'quvchilarning guvohnomalarini olaman. Men ularga kitoblar biriktiraman. AUTO_INCREMENT o'rnatilmaganligi sababli, har safar yangi identifikator yozmaslik uchun siz quyidagilarni bajarishingiz kerak: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Keyin birinchi talaba uchun kitoblarni qo'shing: $ INSERT INTO book (nom, student_id) VALUES('book1', 1); $ INSERT INTO kitob (nom, student_id) VALUES('book2', 1); $ INSERT INTO kitob (nom, student_id) VALUES('book3', 1); $ INSERT INTO kitob (nom, student_id) VALUES('book4', 1); $ INSERT INTO kitob (nom, student_id) VALUES('book5', 1); $ INSERT INTO kitob (nom, student_id) VALUES('book6', 1); Ikkinchi talaba uchun kitoblar: $ INSERT INTO book (nom, student_id) VALUES('book7', 2); $ INSERT INTO kitob (nom, student_id) VALUES('book8', 2); $ INSERT INTO kitob (nom, student_id) VALUES('book9', 2); $ INSERT INTO kitob (nom, student_id) VALUES('book10', 2);

O'qituvchi va talaba aloqalari

Buning uchun jadvalga student_x_teachers qo'shing: $ INSERT INTO student_x_teachers VALUES (1,1); $INSERT INTO student_x_teachers VALUES(1,2); $INSERT INTO student_x_teachers VALUES(2,3);

Keling, so'rovni amalga oshiraylik

Birinchi bosqichni bajaramiz - uchta jadvalni bitta yozuvga bog'laymiz: $ SELECT * FROM o'qituvchi 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 b kitobi .id = b.student_id; Albatta, bizda hali hech qanday yozuvlar yo'q, ammo biz uchta jadvalni muvaffaqiyatli bog'laganimizni ko'rishimiz mumkin. Endi biz kitoblarni guruhlash, saralash va bizga kerak bo'lgan maydonlarni qo'shamiz: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM student st INNER JOIN book b ON st.id = b.student_id INNER QO'SHILING student_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN o'qituvchi tch ON tch.id = st_x_tch.teacher_id GURUH BO'YICHA BUYURTIB st.id kitoblar DESC; Lekin biz SQLda xatoga yo'l qo'yamiz va quyidagi javobni olamiz: Xato kodi: 1055. SELECT ro'yxatining №1 ifodasi GROUP BY bandida yo'q va "final_task.tch.last_name" jamlanmagan ustunni o'z ichiga oladi, bu GROUP BY ustunlariga funktsional jihatdan bog'liq emas. bandi Bu elementlarni olib ishlamaydi, chunki oʻqituvchi va talaba oʻrtasida koʻp-koʻp munosabatlar mavjud. Va bu haqiqat: har bir talabaga bitta o'qituvchi to'g'ri kelmaydi. Shunday qilib, keling, boshqa yo'ldan boraylik. Keling, View SQL deb nomlangan narsadan foydalanamiz. G'oya nima: biz alohida ko'rinishni yaratamiz, bu yangi jadval bo'lib, bizga kerak bo'lgan guruhlash bilan allaqachon. Va bu jadvalga biz o'qituvchilarning kerakli ismlarini qo'shamiz. Lekin biz bir nechta o'qituvchi bo'lishi mumkinligini hisobga olamiz, shuning uchun yozuvlar takrorlanadi. Ko'rinish yaratish: $ 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=b.student_id GROUP BY BY books TARER BY DESC; Keyinchalik, biz ushbu ko'rinish bilan uchta maydondan iborat oddiy jadval sifatida ishlaymiz: talaba familiyasi, talaba_identifikatori va kitoblar soni. Talaba identifikatoriga ko'ra, biz ikkita birlashma orqali o'qituvchini ham qo'shishimiz mumkin: $ SELECT tch.last_name as 'O'qituvchi', sbw.last_name 'Student', sbw.books sifatida 'Kitoblar' talabalar kitobidan sbw INNER JOIN student_x_teachers stch ON sbw. id = stch.student_id INNER JOIN o'qituvchi tch ON tch.id = stch.teacher_id; Va endi natija bo'ladi: Huh! Bu iltimos, shunday emasmi?) Kutilganidek chiqdi: id=1 bo‘lgan talabada oltita kitob va ikkita o‘qituvchi, id=2 bo‘lgan o‘quvchida to‘rtta kitob va bitta o‘qituvchi bor.

10-topshiriq

Shart: Barcha “Talabalar” orasida eng ko‘p “Kitob”ga ega bo‘lgan “O‘qituvchi”ni tanlang. Miqdor bo'yicha kamayish tartibida tartiblang. Bu shunday ko'rinishi kerak:

O'qituvchining familiyasi	Kitob miqdori
Petrov	9
Ivanov	5

Bu erda biz oldingi vazifadan tayyor so'rovdan foydalanishimiz mumkin. Bu borada nimani o'zgartirishimiz kerak? Bizda bu ma'lumotlar allaqachon mavjud, biz faqat boshqa guruhni qo'shishimiz va chiqish ma'lumotlaridan talabaning ismini olib tashlashimiz kerak. Lekin birinchi navbatda, natijani yanada qiziqarli qilish uchun o'qituvchiga yana bir talabani qo'shamiz. Buning uchun quyidagilarni yozamiz: $ INSERT INTO student_x_teachers VALUES (2, 1); Va so'rovning o'zi: $ TANLASH tch.last_name 'O'qituvchi', SUM(sbw.books) 'Kitoblar' sifatida talabalar kitobidan sbw INNER JOIN student_x_teachers stch ON sbw.id = stch.student_id INNER JOIN o'qituvchi tch ON tch.id =s . teacher_id GROUP BY tch.id; Natijada biz shunday olamiz: o'sha o'qituvchi Vasilenkoda 10 ta, Shevchenkoda esa 6 ta kitob bor...)

11-topshiriq

Shart: Uning barcha “Talabalari” uchun “Kitoblar” soni 7 dan 11 gacha bo‘lgan “O‘qituvchi”ni tanlang. Miqdor bo'yicha kamayish tartibida tartiblang. Bu shunday ko'rinishi kerak:

O'qituvchining familiyasi	Kitob miqdori
Petrov	o'n bir
Sidorov	9
Ivanov	7

Bu erda biz HAVING dan foydalanamiz. Biz u haqida gaplashdik. So'rov avvalgidek bir xil bo'ladi, faqat kitoblar soni ma'lum diapazonda bo'lishi shartini qo'shishingiz kerak. Oldingi maqolalarda aytganimdek, guruhlash va/yoki yig‘ish funksiyalarida filtrlash zarur bo‘lganda, HAVING : $ SELECT tch.last_name dan “O‘qituvchi” sifatida, SUM(sbw.books) dan “Kitoblar” sifatida foydalanishimiz kerak. studentbook sbw INNER JOIN student_x_teachers stch ON sbw.id = stch.student_id INNER JOIN o‘qituvchisi tch ON tch.id = stch.teacher_id CH.ID BO‘YICHA GURUHLASH SUM(sbw.books) > 6 VA SUM(sbw2); Men qo'shgan qismni ta'kidladim. Va, aslida, kutilgan natija: bu turdan faqat Vasilenko o'tdi))

12-topshiriq

Shart: “Tur” (talaba yoki o‘qituvchi) maydoni bilan “O‘qituvchi” va “Talaba” ning barcha “familiyasi” va “nomi”ni chop eting. "familiya_ismi" bo'yicha alifbo tartibida tartiblang. Bu shunday ko'rinishi kerak:

familiya	turi
Ivanov	talaba
Kankava	o'qituvchi
Smit	talaba
Sidorov	o'qituvchi
Petrov	o'qituvchi

Ya'ni, biz ikkita chiqishni birlashtirishimiz kerak va UNION aynan shu uchun. Boshqacha qilib aytganda, biz talabalar va o'qituvchilardan yozuvlarni olib, birga chop qilamiz: $ SELECT familiya, 'o'qituvchi' turi sifatida o'qituvchi UNION ALL, familiya sifatida 'talaba' ni tanlang, talabadan ORDER BY BY familiya; Va o'qituvchilar ham, talabalar ham bo'ladi. Hamma narsa oddiy bo'lib tuyuladi, ammo bu biz allaqachon natijaga qaraganimizda. Va shuning uchun siz ikkita narsani taxmin qilishingiz kerak.

13-topshiriq

Vaziyat: Mavjud “Talaba” jadvaliga “stavka” ustunini qo‘shing, unda talaba hozir bo‘lgan kursni saqlaydi (raqamli qiymat 1 dan 6 gacha). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (stavka > 0 VA stavka < 7); Bu erda biz ALTER TABLE va CHECK orqali maydonni qo'shamiz va bu maydonga 1 dan 6 gacha chegarani o'rnatamiz.

14-topshiriq

Vaziyat: Bu element shart emas, lekin ortiqcha bo'ladi. Barcha "Kitoblar" orqali o'tadigan va vergul bilan ajratilgan barcha "sarlavhalarni" chiqaradigan funktsiyani yozing. Bu erda siz so'rov natijasida kitoblarning barcha nomlarini o'z ichiga olgan qatorni qaytarishingiz kerak. Bu erda yana Google'ga kirishim kerak edi. Bunday funktsiya mavjud - GROUP_CONCAT , bu juda sodda tarzda amalga oshiriladi: $ SELECT GROUP_CONCAT(nom) kitobdan; Va bu ...)) Barcha 14 ta vazifa tayyor.

xulosalar

Uuhhh... Bu oson bo'lmadi. Bu qiziq edi. Vazifalar bunga arziydi, men ishonchim komil. Ushbu vazifalarni bajarayotganimizda, biz ilgari ma'lum bo'lmagan ko'p narsalarni ko'rib chiqdik:

• SQL VIEW
• GROUP_CONCAT
• UNION

va hokazo. Men qilgan ishimni o'qish va takrorlash uchun kuchga ega bo'lgan barchaga rahmat. Qanday qilib so'rovlarni yaxshiroq qilishni kim biladi - izohlarda yozing, men ularni albatta o'qiyman)

Seriyadagi barcha materiallar ro'yxati ushbu maqolaning boshida.