Biz ma'lumotlar bazalarini va SQL tilini tahlil qilamiz. (5-qism - ulanishlar va ulanishlar) - "A dan Zgacha Java loyihasi"

Java loyihasini yaratish haqidagi turkum maqola (boshqa materiallarga havolalar oxirida). Uning maqsadi - asosiy texnologiyalarni tahlil qilish, natijada telegram botini yozish.

• Ma'lumotlar bazasidagi munosabatlar turlari
  • Birdan ko'pga
  • Birga bir (birga)
  • Ko'pdan ko'pga
• Ulanishlar (qo'shilish)
  • ICHKI QO‘SHILMA
  • CHAPGA QO'SHILING
  • TO'G'RI QO'SHILING
  • Qo'shilishlarni himoya qilish
• Uy vazifasi

Hammaga salom, dasturiy ta'minot bo'yicha bo'lajak qariyalar va qariyalar. Oldingi qismda aytganimdek ( uy vazifasini tekshirish ), bugun yangi material bo'ladi. Ayniqsa, ishtiyoqmandlar uchun men hamma narsani biladiganlar va bilmaganlar, lekin google-da qidirmoqchi bo'lganlar o'z mahoratini sinab ko'rishlari uchun qiziqarli uy vazifasini qazib oldim. Bugun biz ulanish va qo'shilish turlari haqida gapiramiz.

Ma'lumotlar bazasidagi munosabatlar turlari

Qanday munosabatlarni tushunish uchun siz chet el kaliti nima ekanligini eslab qolishingiz kerak. Unutganlar uchun seriyaning boshlanishiga xush kelibsiz .

Birdan ko'pga

Keling, mamlakatlar va shaharlar bilan misolimizni eslaylik. Shaharning mamlakati bo'lishi kerakligi aniq. Mamlakatni shaharga qanday ulash mumkin? Har bir shaharga u tegishli bo'lgan mamlakatning noyob identifikatorini (ID) biriktirish kerak: biz buni allaqachon qildik. Bu ulanish turlaridan biri deb ataladi - birdan ko'pga (inglizcha versiyasini bilish ham yaxshi bo'lardi - birdan ko'pga). Qisqacha aytganda, biz aytishimiz mumkin: bir nechta shaharlar bir mamlakatga tegishli bo'lishi mumkin. Buni shunday eslab qolishingiz kerak: bir-ko'p munosabatlar. Hozircha bu aniq, to'g'rimi? Agar yo'q bo'lsa, Internetdan olingan birinchi rasm: Bu mijozlar va ularning buyurtmalari borligini ko'rsatadi. Bitta mijoz bir nechta buyurtmaga ega bo'lishi mantiqan. Birdan ko'pga :) Yoki boshqa misol: Uchta jadval mavjud: nashriyot, muallif va kitob. Bankrot bo'lishni istamagan va muvaffaqiyatga erishmoqchi bo'lgan har bir nashriyotning bir nechta mualliflari bor, bunga qo'shilasizmi? O'z navbatida, har bir muallifning bir nechta kitoblari bo'lishi mumkin - bunga ham shubha bo'lmaydi. Va bu yana bitta muallifning ko'p kitoblarga, bir nashriyotning ko'plab mualliflarga bog'liqligini anglatadi . Yana ko'plab misollar keltirish mumkin. Dastlab idrok etishdagi qiyinchilik faqat mavhum fikrlashni o'rganishda bo'lishi mumkin: stollarga va ularning o'zaro ta'siriga tashqaridan qarash.

Birga bir (birga)

Buni birdan-ko'p muloqotning alohida holati deb aytish mumkin. Bir jadvaldagi bitta yozuv boshqa jadvaldagi faqat bitta yozuv bilan bog'liq bo'lgan vaziyat. Hayotdan qanday misollar keltirish mumkin? Agar biz ko'pxotinlilikni istisno qilsak, unda er va xotin o'rtasida yakkama-yakka munosabat borligini aytishimiz mumkin. Garchi biz ko'pxotinlikka ruxsat berilgan desak ham, har bir xotinning faqat bitta eri bo'lishi mumkin. Ota-onalar haqida ham shunday deyish mumkin. Har bir insonning faqat bitta biologik otasi va faqat bitta biologik onasi bo'lishi mumkin. Yakkama-yakka aniq munosabatlar. Buni yozayotganimda xayolimga bir fikr keldi: agar ular allaqachon birma-bir munosabatlarga ega bo'lsa, nega bir-bir munosabatlarni turli jadvallardagi ikkita yozuvga bo'lish kerak? Men o'zim javob topdim. Ushbu yozuvlar boshqa yo'llar bilan boshqa yozuvlar bilan ham bog'lanishi mumkin. Men nima haqida gapiryapman? Yakkama-yakka aloqalarning yana bir misoli - mamlakat va prezident o'rtasidagi. Prezident haqidagi barcha ma'lumotlarni "mamlakat" jadvaliga yozib qo'yish mumkinmi? Ha, qila olasiz, SQL bir so'z aytmaydi. Lekin agar siz prezidentni ham shaxs deb hisoblasangiz... Va uning ham xotini (yana bitta-yakka munosabat) va bolalari (yana bitta-ko‘p munosabatlar) bo‘lishi mumkin va keyin shunday bo‘lib chiqadi. mamlakatni prezidentning rafiqasi va bolalari bilan bog'lash zarur... Aqldan ozgandek tuyuladi, to'g'rimi? :D Bu ulanish uchun boshqa ko'plab misollar bo'lishi mumkin. Bundan tashqari, bunday vaziyatda siz bir-ko'p munosabatlardan farqli o'laroq, ikkala jadvalga tashqi kalitni qo'shishingiz mumkin.

Ko'pdan ko'pga

Nomga asoslanib, siz nima haqida gaplashishimizni taxmin qilishingiz mumkin. Ko'pincha hayotda va biz hayotimizni dasturlashtiramiz, yuqoridagi turdagi ulanishlar bizga kerakli narsalarni tasvirlash uchun etarli bo'lmagan holatlar mavjud. Biz allaqachon nashriyotlar, kitoblar va mualliflar haqida gapirgan edik. Bu yerda juda ko‘p aloqalar bor... Har bir nashrda bir nechta mualliflar bo‘lishi mumkin – birdan ko‘pga bog‘lanish. Shu bilan birga, har bir muallifning bir nechta nashriyoti bo‘lishi mumkin (nega bo‘lmasin, yozuvchi bir joyda chop etilgan, pul talashib, boshqa nashriyotga ketgan, masalan). Va bu yana bir-ko'p munosabatlar. Yoki bu: har bir muallifning bir nechta kitoblari bo'lishi mumkin, lekin har bir kitobda bir nechta mualliflar ham bo'lishi mumkin. Yana yozuvchi va kitob, kitob va muallif o'rtasidagi birdan-ko'p munosabat. Ushbu misoldan biz yanada rasmiylashtirilgan xulosa chiqarishimiz mumkin: SQL-da oldingi ulanish turlarini qanday o'rnatish aniq edi: biz shunchaki uning identifikatorini ko'p bo'lgan yozuvlarga o'tkazamiz, to'g'rimi? Bir davlat o'z identifikatorini ko'plab shaharlarga xorijiy kalit sifatida beradi. Ko'p-ko'p munosabatlar bilan nima qilish kerak ? Bu usul mos emas. Ikki jadvalni bir-biriga bog'laydigan boshqa jadval qo'shishimiz kerak. Misol uchun, MySQL ga o'tamiz, yangi ma'lumotlar bazasini yaratamiz manytomany, ikkita jadval, muallif va kitob yaratamiz, unda faqat nomlar va ularning identifikatorlari bo'ladi: CREATE DATABASE manytomany; ko'p narsadan foydalanish; CREATE TABLE muallifi (id INT AUTO_INCREMENT, nomi VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE kitobi (id INT AUTO_INCREMENT, nomi VARCHAR(100), PRIMARY KEY (id) ); Endi uchinchi jadvalni yaratamiz, unda muallif va kitob jadvallarimizdan ikkita chet el kalitlari bo'ladi va bu havola noyob bo'ladi. Ya'ni, bir xil tugmalar bilan yozuvni ikki marta qo'shish mumkin bo'lmaydi: CREATE TABLE authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERANSLAR muallifi (id ), UNIQUE (kitob_id, muallif_id) ); Bu erda biz alohida sharhlanishi kerak bo'lgan bir nechta yangi xususiyatlardan foydalandik:

• NOT NULL bu maydon har doim to'ldirilishi kerakligini anglatadi, agar to'ldirmasa, SQL bizga buni aytadi;
• UNIQUE, maydon yoki maydonlar to'plami jadvalda yagona bo'lishi kerakligini aytadi. Ko'pincha noyob identifikatorga qo'shimcha ravishda har bir yozuv uchun yana bitta maydon noyob bo'lishi kerak. Va UNIQUE aynan shu masala uchun javobgardir.

Mening amaliyotimdan: eski tizimdan yangisiga o'tayotganda, biz ishlab chiquvchilar sifatida eski tizim identifikatorlarini u bilan ishlash va o'zimizni yaratishimiz uchun saqlashimiz kerak. Nega o'zingizni yaratasiz va eskilaridan foydalanmaysiz? Ular etarlicha noyob bo'lmasligi mumkin yoki identifikatorlarni yaratishda bunday yondashuv endi tegishli va cheklangan bo'lmasligi mumkin. Shu maqsadda biz eski ID-nomni ham jadvalda noyob qilib qo'ydik. Buni tekshirish uchun siz ma'lumotlarni qo'shishingiz kerak. Kitob va muallifni qo'shing: NSERT INTO book (nomi) VALUES ("book1"); INSERT INTO muallif (ism) VALUES ("muallif1"); Biz oldingi maqolalardan ularning identifikatorlari 1 va 1 bo'lishini allaqachon bilganmiz. Shuning uchun biz darhol uchinchi jadvalga yozuv qo'shishimiz mumkin: INSERT INTO authors_x_books VALUES (1,1); Va biz oxirgi buyruqni yana takrorlashni xohlamagunimizcha, hamma narsa yaxshi bo'ladi: ya'ni yana bir xil identifikatorlarni yozing: Natija tabiiy bo'ladi - xato. Dublikat bo'ladi. Kirish yozib olinmaydi. Ko'p-ko'p aloqasi shunday yaratiladi... Bularning barchasi juda ajoyib va ​​qiziqarli, ammo mantiqiy savol tug'iladi: bu ma'lumotni qanday olish mumkin? Turli jadvallardagi ma'lumotlarni qanday qilib birlashtirish va bitta javob olish mumkin? Bu haqda keyingi qismda gaplashamiz))

Ulanishlar (qo'shilish)

Oldingi qismda men sizni birlashmalar nima ekanligini va ularni qayerda ishlatishni darhol tushunishga tayyorladim. Ishonchim komilki, tushunish paydo bo'lishi bilanoq, hamma narsa darhol juda oddiy bo'lib qoladi va qo'shilish haqidagi barcha maqolalar chaqaloqning ko'zlari kabi aniq bo'ladi :D Taxminan va umuman olganda, qo'shilishlar bir nechta jadvallardan natija oladi. ning JOIN (ingliz tilidan qo'shilish). Va bu hammasi ...) Va qo'shilish uchun siz jadvallar birlashtiriladigan maydonni belgilashingiz kerak. Iblis u bo'yalgandek qo'rqinchli emas, to'g'rimi?) Keyinchalik, biz faqat qanday turdagi birikmalar mavjudligi va ulardan qanday foydalanish haqida gaplashamiz. Birlashmalarning ko'p turlari mavjud va biz ularning barchasini ko'rib chiqmaymiz. Faqat bizga haqiqatan ham kerak bo'lganlar. Shuning uchun bizni Cross va Natural kabi ekzotik birikmalar qiziqtirmaydi. Men butunlay unutdim, yana bir nuanceni eslab qolishimiz kerak: jadvallar va maydonlar taxalluslarga ega bo'lishi mumkin - taxalluslar. Ular birlashtirish uchun qulay ishlatiladi. Masalan, buni amalga oshirishingiz mumkin: SELECT * FROM table1; agar so'rov tez-tez jadval1 dan foydalansa, siz unga taxallusni berishingiz mumkin: SELECT* FROM table1 t1 sifatida; yoki undan ham osonroq yozish: SELECT * FROM table1 t1; va keyin so'rovda t1 dan ushbu jadval uchun taxallus sifatida foydalanish mumkin bo'ladi.

ICHKI QO‘SHILMA

Eng keng tarqalgan va oddiy birlashma. Unda aytilishicha, bizda ikkita jadval va uni birlashtirish mumkin bo'lgan maydon mavjud bo'lganda, ikkita jadvalda munosabatlari mavjud bo'lgan barcha yozuvlar tanlanadi. Buni qandaydir tarzda aytish qiyin edi. Keling, misolni ko'rib chiqaylik: shaharlarimiz ma'lumotlar bazasiga bitta yozuv qo'shamiz. Shaharlar uchun bitta va mamlakatlar uchun bitta yozuv: $ INSERT INTO country VALUES(5, "O'zbekiston", 34036800); va $ INSERT INTO shahar (nomi, aholisi) VALUES("Tbilisi", 1171100); Biz jadvalimizda shahar bo'lmagan mamlakatni va jadvalimizga mamlakat bilan bog'liq bo'lmagan shaharni qo'shdik. Shunday qilib, INNER JOIN ikkita jadvaldagi ulanishlar uchun barcha yozuvlarni chiqarish bilan shug'ullanadi. Ikkita jadval1 va jadval2 jadvallarini birlashtirmoqchi bo'lganimizda umumiy sintaksis shunday ko'rinadi: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; va keyin ikkita jadvaldagi munosabatlarga ega bo'lgan barcha yozuvlar qaytariladi. Bizning holatimizda, biz shaharlar bilan birga mamlakatlar uchun ma'lumot olishni istasak, u quyidagicha chiqadi: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Bu yerda nomlar bir xil bo‘lsa-da, avvalo shaharlar, so‘ngra mamlakatlar dalalari kelishini yaqqol ko‘rish mumkin. Ammo biz yuqorida qo'shgan ikkita yozuv mavjud emas. Chunki INNER JOIN aynan shunday ishlaydi.

CHAPGA QO'SHILING

Qo'shni jadvalda bu haqda hech qanday yozuv yo'qligi sababli asosiy jadval maydonlarining yo'qolishi bizni qoniqtirmaydigan holatlar mavjud va ko'pincha. CHAPGA QO'SHILMA ana shu maqsadda. Agar avvalgi so'rovimizda ICHKI o'rniga CHAPni ko'rsatsak, javobda boshqa shaharni qo'shamiz - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Tbilisi haqida yangi yozuv bor va mamlakat bilan bog'liq bo'lgan barcha narsalar null . Bu ko'pincha shunday ishlatiladi.

TO'G'RI QO'SHILING

Bu erda LEFT JOIN dan farqi bo'ladi, chunki barcha maydonlar ulanishda chapda emas, balki o'ngda tanlanadi. Ya'ni, shaharlar emas, balki barcha mamlakatlar olinadi: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Endi bu holatda Tbilisi bo'lmasligi aniq, lekin bizda O'zbekiston bo'ladi. Shunga o'xshash narsa ...))

Qo'shilishlarni himoya qilish

Endi men sizga intervyu oldidan o'smirlar qo'shilishning mohiyatini tushunishlariga ishontirish uchun siqadigan odatiy rasmni ko'rsatmoqchiman: Bu erda hamma narsa to'plamlar shaklida ko'rsatilgan, har bir doira jadvaldir. Va u bo'yalgan joylar SELECTda ko'rsatiladigan qismlardir. Keling, qaraylik:

• INNER JOIN - bu faqat to'plamlarning kesishishi, ya'ni ikkita jadval - A va B bilan bog'langan yozuvlar;
• LEFT JOIN - bu A jadvalidagi barcha yozuvlar, shu jumladan B jadvalidagi A bilan kesishgan (ulanish) bo'lgan barcha yozuvlar;
• RIGHT JOIN - LEFT JOIN ning mutlaqo teskarisi - B jadvalidagi barcha yozuvlar va A dan o'zaro bog'liqlik mavjud yozuvlar.

Bularning barchasidan keyin bu rasm aniq bo'lishi kerak))

Uy vazifasi

Bu safar vazifalar juda qiziqarli bo'ladi va ularni muvaffaqiyatli hal qilganlarning barchasi SQL tomonida ishlashni boshlashga tayyor ekanliklariga ishonch hosil qilishlari mumkin! Vazifalar chaynalmagan va o'rta sinf o'quvchilari uchun yozilgan, shuning uchun siz uchun oson va zerikarli bo'lmaydi :) Men sizga topshiriqlarni o'zingiz bajarish uchun bir hafta vaqt beraman, keyin batafsil tahlil bilan alohida maqola e'lon qilaman. Men sizga bergan vazifalarning yechimi haqida.

Haqiqiy vazifa:

1. Quyidagi maydonlar bilan "Talaba" jadvalini yaratish uchun SQL skriptini yozing: id (asosiy kalit), ism, familiya, e_mail (noyob).
2. 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.
3. Quyidagi maydonlar bilan "O'qituvchi" jadvalini yaratish uchun SQL skriptini yozing: id (asosiy kalit), ism, familiya, e_mail (noyob), mavzu.
4. “Talaba” va “O‘qituvchi”ni “Talaba” ko‘p-ko‘p o‘qituvchi” munosabati bilan bog‘lang.
5. Familiyasida "oro" bo'lgan "Talaba" ni tanlang, masalan, "Sid oro v", "V oro novskiy".
6. "Talaba" jadvalidan barcha familiyalarni ("familiya_ism") 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

7. "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

8. Eng koʻp “Kitob” va “Oʻqituvchi” bilan bogʻlangan “Talabalar”ni tanlang. Miqdori 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>

9. Barcha “Talaba”lari orasidan 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

10. Uning barcha “Talabasi” uchun “Kitob” 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

11. “Tur” (talaba yoki o‘qituvchi) maydoni bilan “O‘qituvchi” va “Talaba” ning barcha “familiyasi” va “ismi”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

12. Mavjud “Talaba” jadvaliga “baho” ustunini qo‘shing, unda talaba hozir bo‘lgan kursni saqlaydi (raqamli qiymat 1 dan 6 gacha).
13. Bu element shart emas, lekin ortiqcha bo'ladi. Barcha "Kitoblar" dan o'tadigan va vergul bilan ajratilgan barcha "sarlavhalar" ni chiqaradigan funktsiyani yozing.

Xulosa

Ma'lumotlar bazasi haqidagi seriyalar biroz cho'zildi. Rozi. Biroq, biz uzoq yo'lni bosib o'tdik va natijada biz bu masala bo'yicha bilimga egamiz! O'qiganingiz uchun barchangizga rahmat, shuni eslatib o'tamanki, loyihani davom ettirish va kuzatib borishni istagan har bir kishi GitHub'da akkaunt yaratishi va mening akkauntimga obuna bo'lishi kerak :) Yana oldinda - Maven va Docker haqida gaplashamiz. O'qiganingiz uchun barchaga rahmat. Yana bir bor takrorlayman: yurgan kishi yo'lni o'zlashtiradi;)

Seriyadagi barcha materiallar ro'yxati ushbu maqolaning boshida.