Biz verilənlər bazası və SQL dilini təhlil edirik. (5-ci hissə - bağlantılar və birləşmələr) - "A-dan Z-yə Java layihəsi"

Java layihəsinin yaradılması haqqında silsilədən məqalə (digər materiallara keçidlər sonundadır). Onun məqsədi əsas texnologiyaları təhlil etməkdir, nəticə teleqram botunu yazmaqdır.

• Verilənlər bazasında əlaqələrin növləri
  • Birdən çoxa
  • Birə bir (birə bir)
  • Çoxdan çoxa
• Əlaqələr (Qoşulmalar)
  • DAXİLİ QOŞULUN
  • SOL QOŞULUN
  • SAĞ QOŞULUN
  • Qoşulmaların Təhlükəsizləşdirilməsi
• Ev tapşırığı

Hər kəsə salam, proqram təminatı üzrə gələcək yaşlılar və yaşlılar. Əvvəlki hissədə dediyim kimi ( ev tapşırığını yoxlamaq ), bu gün yeni material olacaq. Xüsusilə həvəsli olanlar üçün maraqlı bir ev tapşırığı hazırladım ki, onsuz da hər şeyi bilənlər və bilməyənlər, ancaq google-da araşdırmaq istəyənlər məşq edib bacarıqlarını sınasınlar. Bu gün biz əlaqə və birləşmə növləri haqqında danışacağıq.

Verilənlər bazasında əlaqələrin növləri

Münasibətlərin nə olduğunu başa düşmək üçün xarici açarın nə olduğunu xatırlamaq lazımdır. Unudanlar üçün serialın başlanğıcına xoş gəlmisiniz .

Birdən çoxa

Ölkə və şəhərlərlə nümunəmizi xatırlayaq. Aydındır ki, bir şəhərin bir ölkəsi olmalıdır. Bir ölkəni bir şəhərə necə bağlamaq olar? Hər bir şəhərə aid olduğu ölkənin unikal identifikatorunu (ID) əlavə etmək lazımdır: biz bunu artıq etmişik. Bu əlaqə növlərindən biri adlanır - birdən çoxa (ingiliscə variantını da bilmək yaxşı olardı - birdən çoxa). Demək olar ki, bir neçə şəhər bir ölkəyə aid ola bilər. Bunu belə xatırlamalısınız: birdən çoxa münasibət. İndiyə qədər aydındır, elə deyilmi? Əgər belə deyilsə, internetdən ilk şəkil budur: Müştərilərin və onların sifarişlərinin olduğunu göstərir. Bir müştərinin birdən çox sifarişinin ola biləcəyi məntiqlidir. Birdən çoxa var :) Və ya başqa bir misal: Üç ​​cədvəl var: nəşriyyat, müəllif və kitab. Müflis olmaq istəməyən və uğur qazanmaq istəyən hər bir nəşriyyatın birdən çox müəllifi var, razı deyilsiniz? Öz növbəsində, hər bir müəllifin birdən çox kitabı ola bilər - buna da şübhə ola bilməz. Bu isə yenə də bir müəllifin çox kitaba, bir nəşriyyatın bir çox müəllifə bağlanması deməkdir . Daha çoxlu misallar gətirmək olar. Əvvəlcə qavrayışdakı çətinlik yalnız mücərrəd düşünməyi öyrənməkdə ola bilər: masalara və onların qarşılıqlı təsirinə kənardan baxmaq.

Birə bir (birə bir)

Bunun birdən çox ünsiyyətə aid xüsusi bir hal olduğunu söyləmək olar. Bir cədvəldəki bir qeydin digər cədvəldəki yalnız bir qeydlə əlaqəli olduğu vəziyyət. Həyatdan hansı nümunələr ola bilər? Çoxarvadlılığı istisna etsək, o zaman ər-arvad arasında təkbətək münasibətin olduğunu deyə bilərik. Baxmayaraq ki, çoxarvadlılığa icazə verildiyini desək də, hər arvadın yalnız bir əri ola bilər. Eyni sözləri valideynlər haqqında da demək olar. Hər bir insanın yalnız bir bioloji atası və yalnız bir bioloji anası ola bilər. Aydın bir-bir əlaqə. Bunu yazarkən ağlıma bir fikir gəldi: niyə bir-bir əlaqəni müxtəlif cədvəllərdə iki qeydə bölmək lazımdır, əgər onlarda bir-bir əlaqə var? Cavabı özüm tapdım. Bu qeydlər başqa yollarla digər qeydlərlə də əlaqələndirilə bilər. Mən nədən danışıram? Bir-bir əlaqənin başqa bir nümunəsi ölkə ilə prezident arasındadır. Prezident haqqında bütün məlumatları “ölkə” cədvəlinə yazmaq olarmı? Bəli, edə bilərsiniz, SQL bir söz deməyəcək. Amma fikirləşirsən ki, prezident də bir şəxsdir... Onun da arvadı (başqa bir-bir münasibət) və uşaqları (birdən çox əlaqəsi) ola bilər və sonra belə çıxır ki, belə olacaq. ölkəni prezidentin həyat yoldaşı və uşaqları ilə əlaqələndirmək lazımdır... Dəli səslənir, elə deyilmi? :D Bu əlaqə üçün başqa misallar da çox ola bilər. Üstəlik, belə bir vəziyyətdə, birdən çox əlaqədən fərqli olaraq, hər iki cədvələ xarici açar əlavə edə bilərsiniz.

Çoxdan çoxa

Artıq adına əsaslanaraq, nədən danışacağımızı təxmin edə bilərsiniz. Çox vaxt həyatda və biz həyatımızı proqramlaşdırırıq, elə vəziyyətlər olur ki, yuxarıda göstərilən əlaqə növləri bizə lazım olan şeyləri təsvir etmək üçün kifayət etmir. Artıq nəşriyyatlar, kitablar və müəlliflər haqqında danışdıq. Burada o qədər əlaqə var ki... Hər nəşrin bir neçə müəllifi ola bilər - birdən çox əlaqə. Eyni zamanda, hər müəllifin bir neçə nəşriyyatı ola bilər (niyə də olmasın, yazıçı bir yerdə çap olunub, pul davası olub, məsələn, başqa nəşriyyata gedib). Və bu, yenə bir-çox əlaqəsidir. Və ya bu: hər bir müəllifin bir neçə kitabı ola bilər, lakin hər kitabın bir neçə müəllifi də ola bilər. Yenə müəllif və kitab, kitab və müəllif arasında birdən çox əlaqə. Bu nümunədən daha rəsmi bir nəticə çıxara bilərik: SQL-də əvvəlki əlaqə növlərini necə təyin etmək aydın idi: biz sadəcə olaraq onun ID-sini çoxlu qeydlərə ötürdük, elə deyilmi? Bir ölkə öz şəxsiyyət vəsiqəsini bir çox şəhərə xarici açar kimi verir. Çoxdan çoxa münasibətlərlə nə etməli ? Bu üsul uyğun deyil. İki cədvəli birləşdirəcək başqa bir cədvəl əlavə etməliyik. Məsələn, MySQL-ə gedək, yeni çoxlu verilənlər bazası yaradaq, yalnız adları və onların identifikatorlarını ehtiva edən iki cədvəl, müəllif və kitab yaradaq: CREATE DATABASE manytomany; çoxlu istifadə edin; CREATE TABLE müəllifi (id INT AUTO_INCREMENT, ad VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE kitabı (id INT AUTO_INCREMENT, adı VARCHAR(100), PRIMARY KEY (id) ); İndi müəllif və kitab cədvəllərimizdən iki xarici açarı olan üçüncü cədvəl yaradaq və bu keçid unikal olacaqdır. Yəni, eyni düymələrlə qeydi iki dəfə əlavə etmək mümkün olmayacaq: 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) REFERENCES müəllifi (id), UNİKAL (kitab_id, müəllif_id) ); Burada ayrıca şərh edilməli olan bir neçə yeni xüsusiyyətdən istifadə etdik:

• NOT NULL o deməkdir ki, sahə həmişə doldurulmalıdır və biz doldurmasaq, SQL bunu bizə bildirəcək;
• UNIQUE deyir ki, bir sahə və ya bir dəstə sahə cədvəldə unikal olmalıdır. Tez-tez olur ki, unikal identifikatordan əlavə, hər bir qeyd üçün daha bir sahə unikal olmalıdır. Və UNIQUE məhz bu məsələyə cavabdehdir.

Təcrübəmdən: köhnə sistemdən yeni sistemə keçərkən biz tərtibatçılar olaraq onunla işləmək və özümüzü yaratmaq üçün köhnə sistemin identifikatorlarını saxlamalıyıq. Niyə özünüzü yaradın və köhnələrini istifadə etmirsiniz? Onlar kifayət qədər unikal olmaya bilər və ya şəxsiyyət vəsiqələrinin yaradılmasına bu yanaşma artıq müvafiq və məhdud olmaya bilər. Bu məqsədlə köhnə ID-adını da cədvəldə unikal etdik. Bunu yoxlamaq üçün məlumat əlavə etməlisiniz. Kitab və müəllif əlavə edin: NSERT INTO book (ad) VALUES ("book1"); müəllifin (adı) DƏYƏRLƏRİNƏ DAXİL EDİN ("author1"); Əvvəlki məqalələrdən artıq bilirik ki, onların 1 və 1 ID-ləri olacaq. Buna görə də biz dərhal üçüncü cədvələ qeyd əlavə edə bilərik: INSERT INTO authors_x_books VALUES (1,1); Və sonuncu əmri yenidən təkrarlamaq istəyənə qədər hər şey yaxşı olacaq: yəni eyni identifikatorları yenidən yazın: Nəticə təbii olacaq - səhv. Dublikat olacaq. Giriş qeyd olunmayacaq. Çoxlu-çoxlu əlaqə belə yaradılacaq... Bütün bunlar çox gözəl və maraqlıdır, lakin məntiqi sual yaranır: bu məlumatı necə əldə etmək olar? Fərqli cədvəllərdən məlumatları necə birləşdirmək və bir cavab almaq olar? Növbəti hissədə bu haqda danışacağıq))

Əlaqələr (Qoşulmalar)

Əvvəlki hissədə mən sizi birləşmələrin nə olduğunu və harada istifadə edəcəyinizi dərhal başa düşməyə hazırladım. Çünki mən dərindən əminəm ki, anlayış gələn kimi dərhal hər şey çox sadələşəcək və birləşmələrlə bağlı bütün məqalələr körpənin gözü kimi aydın olacaq :D Təxminən və ümumiyyətlə, birləşmələr vasitələrlə bir neçə cədvəldən nəticə əldə edirlər. JOIN (ingilis dilindən qoşulmaq). Və hamısı budur...) Və qoşulmaq üçün cədvəllərin birləşdiriləcəyi sahəni göstərməlisiniz. Şeytan rəngləndiyi qədər qorxulu deyil, elə deyilmi?) Sonra, hansı birləşmələrin olduğunu və onlardan necə istifadə edəcəyimizi danışacağıq. Birləşmənin bir çox növləri var və biz onların hamısını nəzərdən keçirməyəcəyik. Yalnız həqiqətən ehtiyacımız olanlar. Buna görə də bizi Cross və Natural kimi ekzotik birləşmələr maraqlandırmır. Tamamilə unutdum, daha bir nüansı xatırlamaq lazımdır: cədvəllər və sahələr ləqəblərə - təxəllüslərə malik ola bilər. Onlar birləşmələr üçün rahat istifadə olunur. Məsələn, siz bunu edə bilərsiniz: SELECT * FROM table1; əgər sorğu tez-tez table1-dən istifadə edəcəksə, onda siz ona ləqəb verə bilərsiniz: SELECT* FROM table1 t1 kimi; və ya yazmaq daha asan: SEÇ * FROM table1 t1; və sonra sorğuda t1-dən bu cədvəl üçün ləqəb kimi istifadə etmək mümkün olacaq.

DAXİLİ QOŞULUN

Ən ümumi və sadə birləşmə. Orada deyilir ki, bizdə iki cədvəl və onun birləşdirilə biləcəyi sahə olduqda, iki cədvəldə əlaqələri mövcud olan bütün qeydlər seçiləcək. Nədənsə demək çətin idi. Nümunəyə baxaq: Şəhərlərimizin məlumat bazasına bir qeyd əlavə edək. Şəhərlər üçün bir, ölkələr üçün bir giriş: $ INSERT INTO country VALUES(5, "Özbəkistan", 34036800); və $ INSERT INTO şəhər (ad, əhali) VALUES("Tbilisi", 1171100); Cədvəlimizə şəhəri olmayan ölkəni və ölkə ilə əlaqəli olmayan bir şəhəri cədvəlimizə əlavə etdik. Beləliklə, INNER JOIN iki cədvəldə olan əlaqələr üçün bütün qeydləri verməklə məşğuldur. Biz iki cədvəli table1 və table2-ə qoşulmaq istəyəndə ümumi sintaksis belə görünür: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; və sonra iki cədvəldə əlaqəsi olan bütün qeydlər qaytarılacaq. Bizim vəziyyətimiz üçün, şəhərlərlə yanaşı ölkələr üçün də məlumat almaq istədikdə, belə olacaq: $ SEÇİN * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Burada adlar eyni olsa da, şəhərlərin tarlalarının, daha sonra ölkələrin tarlalarının gəldiyini aydın görmək olar. Ancaq yuxarıda əlavə etdiyimiz iki giriş orada deyil. Çünki INNER JOIN məhz belə işləyir.

SOL QOŞULUN

Qonşu cədvəldə bunun üçün heç bir qeyd olmadığı üçün əsas cədvəlin sahələrinin itirilməsi ilə kifayətlənmədiyimiz hallar və çox vaxt olur. SOL QOŞULMA bunun üçündür. Əvvəlki sorğumuzda INNER əvəzinə SOL göstərsək, cavabda başqa bir şəhər əlavə edəcəyik - Tbilisi: $ SEÇİN * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Tiflis haqqında yeni bir giriş var və ölkəyə aid olan hər şey sıfırdır . Çox vaxt belə istifadə olunur.

SAĞ QOŞULUN

Burada LEFT JOIN-dan fərq olacaq ki, bütün sahələr əlaqədə solda deyil, sağda seçiləcək. Yəni şəhərlər deyil, bütün ölkələr götürüləcək: $ SEÇİN * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; İndi aydındır ki, bu halda Tiflis olmayacaq, amma bizdə Özbəkistan olacaq. Belə bir şey...))

Qoşulmaların Təhlükəsizləşdirilməsi

İndi mən sizə yeniyetmələrin birləşmələrin mahiyyətini başa düşdüklərinə inandırmaq üçün müsahibədən əvvəl sıxdıqları tipik bir şəkil göstərmək istəyirəm: Burada hər şey dəstlər şəklində göstərilir, hər dairə bir cədvəldir. Və onun rəngləndiyi yerlər SELECT-də göstəriləcək hissələrdir. Baxaq:

• INNER JOIN yalnız dəstlərin kəsişməsidir, yəni iki cədvəllə - A və B ilə əlaqəsi olan qeydlərdir;
• LEFT JOIN A cədvəlindəki bütün qeydlər, o cümlədən B cədvəlindəki A ilə kəsişməsi (əlaqəsi) olan bütün qeydlərdir;
• RIGHT JOIN LEFT JOIN-in tam əksidir - cədvəl B-dəki bütün qeydlər və A-dan əlaqəsi olan qeydlər.

Bütün bunlardan sonra bu şəkil aydın olmalıdır))

Ev tapşırığı

Bu dəfə tapşırıqlar çox maraqlı olacaq və onları uğurla həll edənlərin hamısı əmin ola bilərlər ki, onlar SQL tərəfində işə başlamağa hazırdırlar! Tapşırıqlar çeynənmir və orta sinif şagirdləri üçün yazılıb, ona görə də sizin üçün asan və darıxdırıcı olmayacaq :) Tapşırıqları özünüz yerinə yetirmək üçün sizə bir həftə vaxt verirəm, sonra ətraflı təhlillə ayrıca məqalə dərc edəcəm. sizə verdiyim tapşırıqların həlli.

Faktiki vəzifə:

1. Aşağıdakı sahələrlə 'Tələbə' cədvəlini yaratmaq üçün SQL skripti yazın: id (əsas açar), ad, soyad, e_mail (unikal).
2. Aşağıdakı sahələrlə "Kitab" cədvəlini yaratmaq üçün SQL skripti yazın: id, başlıq (id + başlıq = əsas açar). "Tələbə" və "Kitab"ı "Tələbə" bir-çox "Kitab" əlaqəsi ilə əlaqələndirin.
3. Aşağıdakı sahələrlə 'Müəllim' cədvəlini yaratmaq üçün SQL skripti yazın: id (əsas açar), ad, soyad, e_mail (unikal), mövzu.
4. 'Tələbə' və 'Müəllim'i 'Tələbə' çoxdan çox Müəllim' əlaqəsi ilə əlaqələndirin.
5. Soyadında 'oro' olan 'Tələbə' seçin, məsələn, 'Sid oro v', 'V oro novsky'.
6. 'Tələbə' cədvəlindən bütün soyadları ('soyad_ad') və onların təkrar sayını seçin. Nəzərə alın ki, verilənlər bazasında adlar var. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

   Soyad	kəmiyyət
   Petrov	15
   İvanov	12
   Sidorov	3

7. "Tələbə"dən ən çox təkrarlanan 3 ad seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

   ad	kəmiyyət
   İskəndər	27
   Sergey	10
   Peter	7

8. Ən çox "Kitab" və əlaqəli "Müəllim" sayına malik "Tələbələri" seçin. Kəmiyyətə görə azalma qaydasında çeşidləyin. Bu belə görünməlidir:

   Müəllimin soyadı	tələbənin soyadı	Kitabın miqdarı
   Petrov	Sidorov	7
   İvanov	Smith	5
   Petrov	Kankava	2>

9. Bütün “Tələbə”lərindən ən çox “Kitab”ı olan “Müəllim”i seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

   Müəllimin soyadı	Kitabın miqdarı
   Petrov	9
   İvanov	5

10. Bütün "Tələbə" üçün "Kitab" sayı 7 ilə 11 arasında olan "Müəllim"i seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

    Müəllimin soyadı	Kitabın miqdarı
    Petrov	on bir
    Sidorov	9
    İvanov	7

11. "Növ" (tələbə və ya müəllim) sahəsi ilə "Müəllim" və "Tələbə"nin bütün "soyad_adı" və "adı"nı çap edin. 'soyad_adına' görə əlifba sırası ilə çeşidləyin. Bu belə görünməlidir:

    Soyad	növü
    İvanov	tələbə
    Kankava	müəllim
    Smith	tələbə
    Sidorov	müəllim
    Petrov	müəllim

12. Mövcud "Tələbə" cədvəlinə tələbənin hazırda keçdiyi kursu saxlayacaq "dərəcə" sütunu əlavə edin (1-dən 6-ya qədər rəqəm).
13. Bu maddə tələb olunmur, lakin bir artı olacaq. Bütün "Kitablar"ı keçəcək və vergüllə ayrılmış bütün "başlıqları" çıxaracaq bir funksiya yazın.

Nəticə

Verilənlər bazası ilə bağlı seriyalar bir qədər uzandı. Razılaşmaq. Bununla belə, biz uzun bir yol qət etmişik və nəticədə məsələnin biliyi ilə ortaya çıxırıq! Oxuduğunuz üçün hamınıza təşəkkür edirəm, xatırladıram ki, layihəni davam etdirmək və izləmək istəyən hər kəs GitHub- da hesab yaratmalı və mənim hesabıma abunə olmalıdır :) Gələcəkdə daha çox - Maven və Docker haqqında danışaq. Oxuduğunuz üçün hər kəsə təşəkkür edirəm. Bir daha təkrar edirəm: gedən yolda ustalaşar;)

Serialdakı bütün materialların siyahısı bu məqalənin əvvəlindədir.