Analizujemy bazy danych i język SQL. (Część 5 - połączenia i złączenia) - „Projekt Java od A do Z”

Artykuł z serii o tworzeniu projektu w Javie (linki do innych materiałów znajdują się na końcu). Jego celem jest analiza kluczowych technologii, efektem jest napisanie bota telegramowego.

• Rodzaje relacji w bazie danych
  • Jeden za dużo
  • Jeden do jednego (jeden do jednego)
  • Wiele do wielu
• Połączenia (połączenia)
  • WEWNĘTRZNE POŁĄCZENIE
  • LEWO DOŁĄCZ
  • PRAWO DOŁĄCZ
  • Zabezpieczanie połączeń
• Praca domowa

Witam wszystkich przyszłych Seniorów i Senioritas oprogramowania. Jak już wspomniałem w poprzedniej części ( sprawdzanie zadań domowych ), dzisiaj pojawi się nowy materiał. Dla szczególnie chętnych wykopałem ciekawe zadanie domowe, aby zarówno ci, którzy już wszystko wiedzą, jak i ci, którzy nie wiedzą, ale chcą poszukać w Google, mogli poćwiczyć i sprawdzić swoje umiejętności. Dzisiaj porozmawiamy o rodzajach połączeń i złączeń.

Rodzaje relacji w bazie danych

Aby zrozumieć, czym są relacje, należy pamiętać, czym jest klucz obcy. Tych, którzy zapomnieli, witamy na początku serii .

Jeden za dużo

Przypomnijmy nasz przykład z krajami i miastami. Oczywiste jest, że miasto musi mieć kraj. Jak połączyć kraj z miastem? Do każdego miasta należy dołączyć unikalny identyfikator (ID) kraju, do którego ono należy: już to zrobiliśmy. Nazywa się to jednym z typów połączeń - jeden do wielu (dobrze byłoby znać też wersję angielską - jeden do wielu). Parafrazując, można powiedzieć: do jednego kraju może należeć kilka miast. Tak powinieneś to zapamiętać: relacja jeden do wielu. Póki co wszystko jasne, prawda? Jeśli nie, to oto pierwsze zdjęcie z Internetu: Pokazuje, że są klienci i ich zamówienia. Logiczne jest, że jeden klient może mieć więcej niż jedno zamówienie. Jest jeden do wielu :) Lub inny przykład: Istnieją trzy tabele: wydawca, autor i książka. Każdy wydawca, który nie chce zbankrutować i chce odnieść sukces, ma więcej niż jednego autora, prawda? Z kolei każdy autor może mieć więcej niż jedną książkę – co do tego również nie może budzić wątpliwości. A to znowu oznacza powiązanie jednego autora z wieloma książkami, jednego wydawcy z wieloma autorami . Przykładów, które można podać, jest o wiele więcej. Na początku trudność w percepcji może polegać jedynie na nauczeniu się myślenia abstrakcyjnego: patrzenia z zewnątrz na stoły i ich interakcje.

Jeden do jednego (jeden do jednego)

Można powiedzieć, że jest to szczególny przypadek komunikacji jeden do wielu. Sytuacja, w której jeden rekord w jednej tabeli jest powiązany tylko z jednym rekordem w innej tabeli. Jakie przykłady mogą być z życia? Jeśli wykluczymy poligamię, możemy powiedzieć, że między mężem i żoną istnieje relacja jeden do jednego. Chociaż nawet jeśli powiemy, że poligamia jest dozwolona, ​​to każda żona może nadal mieć tylko jednego męża. To samo można powiedzieć o rodzicach. Każda osoba może mieć tylko jednego biologicznego ojca i tylko jedną biologiczną matkę. Wyraźna relacja jeden do jednego. Kiedy to pisałem, przyszła mi do głowy myśl: po co więc dzielić relację jeden do jednego na dwa rekordy w różnych tabelach, skoro mają już relację jeden do jednego? Sam wymyśliłem odpowiedź. Zapisy te można także powiązać z innymi zapisami w inny sposób. O czym ja mówię? Innym przykładem powiązań „jeden do jednego” są relacje między krajem a prezydentem. Czy w tabeli „kraj” można zapisać wszystkie dane o prezydencie? Tak, możesz, SQL nie powie ani słowa. Ale jeśli pomyśleć, że prezydent to też człowiek... A może też mieć żonę (kolejna relacja jeden do jednego) i dzieci (kolejna relacja jeden do wielu) i wówczas okaże się, że tak będzie konieczne, aby połączyć kraj z żoną prezydenta i dziećmi…. Brzmi szalenie, prawda? :D Przykładów takiego połączenia może być wiele innych. Co więcej, w takiej sytuacji można dodać klucz obcy do obu tabel, w przeciwieństwie do relacji jeden do wielu.

Wiele do wielu

Już na podstawie nazwy możesz zgadnąć, o czym będziemy rozmawiać. Często w życiu, a my programujemy swoje życie, zdarzają się sytuacje, gdy powyższe rodzaje połączeń nie wystarczą do opisania rzeczy, których potrzebujemy. Rozmawialiśmy już o wydawcach, książkach i autorach. Powiązań jest tu po prostu mnóstwo... Każda publikacja może mieć kilku autorów – połączenie jeden do wielu. Jednocześnie każdy autor może mieć kilku wydawców (czemu nie, pisarz ukazał się w jednym miejscu, pokłócił się o pieniądze, poszedł np. do innego wydawnictwa). I znowu jest to relacja jeden do wielu. Albo tak: każdy autor może mieć kilka książek, ale każda książka może mieć też kilku autorów. Ponownie, relacja jeden do wielu pomiędzy autorem a książką, książką a autorem. Z tego przykładu możemy wyciągnąć bardziej sformalizowany wniosek: Było jasne, jak ustawić poprzednie typy połączeń w SQL: po prostu przekazujemy identyfikator tego połączenia do tych rekordów, których jest wiele, prawda? Jeden kraj nadaje swój identyfikator jako klucz obcy wielu miastom. Co zrobić z relacjami wiele do wielu ? Ta metoda nie jest odpowiednia. Musimy dodać kolejną tabelę, która połączy dwie tabele. Przykładowo przejdźmy do MySQL, utwórzmy nową bazę danych manytomany, utwórzmy dwie tabele autor i książka, które będą zawierać tylko nazwiska i ich identyfikatory: CREATE DATABASE manytomany; UŻYJ wielutomanów; UTWÓRZ TABELĘ autor (id INT AUTO_INCREMENT, nazwa VARCHAR(100), KLUCZ PODSTAWOWY (id) ); UTWÓRZ TABELĘ książka (id INT AUTO_INCREMENT, nazwa VARCHAR(100), KLUCZ PODSTAWOWY (id) ); Stwórzmy teraz trzecią tabelę, która będzie miała dwa klucze obce z naszych tabel autora i książki, a ten link będzie unikalny. Oznacza to, że nie będzie możliwe dwukrotne dodanie rekordu z tymi samymi kluczami: CREATE TABLE autorzy_x_books ( book_id INT NOT NULL, autor_id INT NOT NULL, KLUCZ OBCY (book_id) REFERENCJE book(id), KLUCZ OBCY (author_id) REFERENCJE autor (id ), UNIKALNE (id_książki, id_autora) ); Zastosowaliśmy tutaj kilka nowych funkcji, które wymagają osobnego komentarza:

• NOT NULL oznacza, że ​​pole musi być zawsze wypełnione, a jeśli tego nie zrobimy, SQL nam to poinformuje;
• UNIQUE mówi, że pole lub grupa pól musi być unikalna w tabeli. Często zdarza się, że oprócz unikalnego identyfikatora, dla każdego rekordu musi być unikalne jeszcze jedno pole. A UNIQUE odpowiada właśnie za tę sprawę.

Z mojej praktyki: przenosząc się ze starego systemu na nowy, my, jako programiści, musimy przechowywać identyfikatory starego systemu, aby z nim pracować i tworzyć własne. Po co tworzyć własne i nie używać starych? Mogą nie być wystarczająco unikalne lub takie podejście do tworzenia identyfikatorów może nie być już istotne i ograniczone. W tym celu staraliśmy się, aby stara nazwa identyfikacyjna była unikalna w tabeli. Aby to sprawdzić, musisz dodać dane. Dodaj książkę i autora: NSERT INTO book (name) VALUES („book1”); INSERT INTO autora (imię) WARTOŚCI („autor1”); Z poprzednich artykułów wiemy już, że będą miały one identyfikatory 1 i 1. W związku z tym możemy od razu dodać rekord do trzeciej tabeli: INSERT INTOauthor_x_books VALUES (1,1); I wszystko będzie dobrze, dopóki nie będziemy chcieli ponownie powtórzyć ostatniego polecenia: czyli zapisać jeszcze raz te same identyfikatory: Wynik będzie naturalny - błąd. Będzie duplikat. Wpis nie zostanie zarejestrowany. Tak powstanie połączenie wiele do wielu... Wszystko to jest bardzo fajne i ciekawe, ale pojawia się logiczne pytanie: jak zdobyć tę informację? Jak połączyć dane z różnych tabel i uzyskać jedną odpowiedź? O tym porozmawiamy w następnej części))

Połączenia (połączenia)

W poprzedniej części przygotowałem Cię do natychmiastowego zrozumienia, czym są złączenia i gdzie je stosować. Bo jestem głęboko przekonany, że gdy tylko przyjdzie zrozumienie, wszystko od razu stanie się bardzo proste, a wszystkie artykuły o złączeniach będą jasne jak oczka dziecka :D Z grubsza i w ogóle, złączenia dostają wynik z kilku tabel za pomocą środka JOIN (join z angielskiego Join). I to wszystko...) I aby dołączyć, należy określić pole, za pomocą którego tabele będą łączone. Diabeł nie jest taki straszny, jak go malują, prawda?) Następnie porozmawiamy o tym, jakie są rodzaje złączeń i jak z nich korzystać. Istnieje wiele rodzajów złączeń i nie będziemy ich wszystkich rozważać. Tylko te, których naprawdę potrzebujemy. Dlatego nie interesują nas tak egzotyczne połączenia jak Cross i Natural. Zupełnie zapomniałem, musimy pamiętać o jeszcze jednym niuansie: tabele i pola mogą mieć aliasy - pseudonimy. Są one wygodnie używane do złączeń. Możesz na przykład zrobić tak: SELECT * FROM table1; jeśli zapytanie często będzie korzystało z tabeli1, możesz nadać jej alias: SELECT* FROM tabela1 jako t1; lub jeszcze łatwiej napisać: SELECT * FROM table1 t1; a następnie w dalszej części zapytania będzie można użyć t1 jako aliasu dla tej tabeli.

WEWNĘTRZNE POŁĄCZENIE

Najpopularniejsze i najprostsze łączenie. Mówi, że gdy mamy dwie tabele i pole, za pomocą którego można je połączyć, zostaną wybrane wszystkie rekordy, których relacje istnieją w obu tabelach. Trudno było to jakoś powiedzieć. Spójrzmy na przykład: Dodajmy jeden rekord do naszej bazy miast. Jeden wpis dla miast i jeden dla krajów: $ WSTAWIĆ WARTOŚCI kraju(5, "Uzbekistan", 34036800); i $ WSTAW DO miasta (nazwa, populacja) WARTOŚCI("Tbilisi", 1171100); Dodaliśmy kraj, który nie ma miasta w naszej tabeli, oraz miasto, które nie jest powiązane z krajem w naszej tabeli. Zatem INNER JOIN zajmuje się wydawaniem wszystkich rekordów dla połączeń znajdujących się w dwóch tabelach. Tak wygląda ogólna składnia, gdy chcemy połączyć dwie tabele, tabela1 i tabela2: SELECT * FROM tabela1 t1 INNER JOIN tabela2 ON t1.id = t2.t1_id; a następnie zwrócone zostaną wszystkie rekordy, które mają relację w obu tabelach. W naszym przypadku, gdy będziemy chcieli otrzymać informacje dla krajów wraz z miastami, będzie to wyglądać następująco: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Tutaj, choć nazwy są takie same, wyraźnie widać, że najpierw są pola miast, potem pola krajów. Ale dwóch wpisów, które dodaliśmy powyżej, tam nie ma. Ponieważ dokładnie tak działa INNER JOIN.

LEWO DOŁĄCZ

Zdarzają się przypadki, i to dość częste, gdy nie zadowala nas utrata pól stołu głównego z uwagi na to, że w tabeli sąsiedniej nie ma dla niego zapisu. Do tego właśnie służy LEFT JOIN. Jeśli w poprzednim zapytaniu podajemy LEFT zamiast INNER, w odpowiedzi dodamy kolejne miasto - Tbilisi: $ SELECT * FROM city ci LEFT DOŁĄCZ kraj co ON ci.country_id = co.id; Pojawił się nowy wpis o Tbilisi i wszystko co dotyczy tego kraju jest w null . Często tak się to wykorzystuje.

PRAWO DOŁĄCZ

Tutaj będzie różnica w stosunku do LEFT JOIN polegająca na tym, że wszystkie pola zostaną wybrane nie po lewej stronie, ale po prawej stronie połączenia. Oznacza to, że nie zostaną uwzględnione miasta, ale wszystkie kraje: $ WYBIERZ * Z miasta ci PRAWO DOŁĄCZ kraj co ON ci.country_id = co.id; Teraz jest jasne, że w tym przypadku nie będzie Tbilisi, ale będziemy mieli Uzbekistan. Coś w tym stylu…))

Zabezpieczanie połączeń

Teraz chcę pokazać typowy obrazek, który juniorzy wkuwają przed rozmową kwalifikacyjną, aby ich przekonać, że rozumieją istotę złączeń: Tutaj wszystko jest pokazane w formie zbiorów, każde koło jest stołem. A te miejsca, w których jest zamalowany, to te części, które zostaną pokazane w SELECT. Spójrzmy:

• INNER JOIN to tylko przecięcie zbiorów, czyli tych rekordów, które mają połączenia z dwiema tabelami - A i B;
• LEFT JOIN to wszystkie rekordy z tabeli A, w tym wszystkie rekordy z tabeli B, które mają przecięcie (połączenie) z A;
• RIGHT JOIN jest dokładnym przeciwieństwem LEFT JOIN - wszystkie rekordy w tabeli B i rekordy z A, które mają relację.

Po tym wszystkim ten obraz powinien być wyraźny))

Praca domowa

Tym razem zadania będą bardzo ciekawe i wszyscy, którzy pomyślnie je rozwiążą, mogą być pewni, że są gotowi, aby rozpocząć pracę po stronie SQL! Zadania nie są przeżute i zostały napisane z myślą o gimnazjalistach, więc nie będzie to dla Was łatwe i nudne :) Daję Wam tydzień na samodzielne wykonanie zadań, po czym opublikuję osobny artykuł ze szczegółową analizą rozwiązania zadań, które Ci dałem.

Rzeczywiste zadanie:

1. Napisz skrypt SQL tworzący tabelę „Student” zawierającą następujące pola: id (klucz podstawowy), imię i nazwisko, nazwisko, e_mail (unikalny).
2. Napisz skrypt SQL tworzący tabelę „Książka” zawierającą następujące pola: id, tytuł (id + tytuł = klucz podstawowy). Połącz „Studenta” i „Książkę” relacją „Student” jeden do wielu „Książka”.
3. Napisz skrypt SQL tworzący tabelę „Nauczyciel” zawierającą następujące pola: id (klucz podstawowy), imię i nazwisko, nazwisko, e_mail (unikalny), temat.
4. Połącz „Uczeń” i „Nauczyciel” relacją „Uczeń” wiele do wielu Nauczyciel.
5. Wybierz opcję „Student”, który ma „oro” w nazwisku, na przykład „Sid oro v”, „V oro novsky”.
6. Z tabeli „Student” wybierz wszystkie nazwiska („nazwisko”) i liczbę ich powtórzeń. Weź pod uwagę, że w bazie danych znajdują się imienniki. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

   nazwisko	ilość
   Pietrow	15
   Iwanow	12
   Sidorow	3

7. Wybierz 3 najczęściej powtarzające się imiona z listy „Student”. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

   nazwa	ilość
   Aleksander	27
   Siergiej	10
   Piotr	7

8. Wybierz „Uczniów”, którzy mają największą liczbę „Książek” i powiązanego „Nauczyciela”. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

   Nazwisko nauczyciela	Nazwisko ucznia	ilość książki
   Pietrow	Sidorow	7
   Iwanow	Kowal	5
   Pietrow	Kankava	2>

9. Wybierz „Nauczyciela”, który ma największą liczbę „Książek” ze wszystkich swoich „Uczniów”. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

   Nazwisko nauczyciela	ilość książki
   Pietrow	9
   Iwanow	5

10. Wybierz „Nauczyciela”, którego numer „Książki” dla wszystkich jego „Uczniów” mieści się w przedziale od 7 do 11. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

    Nazwisko nauczyciela	ilość książki
    Pietrow	jedenaście
    Sidorow	9
    Iwanow	7

11. Wydrukuj wszystkie „nazwisko” i „imię” wszystkich „Nauczyciela” i „Ucznia” z polem „typ” (uczeń lub nauczyciel). Sortuj alfabetycznie według „nazwiska”. To powinno wyglądać tak:

    nazwisko	typ
    Iwanow	student
    Kankava	nauczyciel
    Kowal	student
    Sidorow	nauczyciel
    Pietrow	nauczyciel

12. Dodaj kolumnę „stawka” do istniejącej tabeli „Student”, w której będzie przechowywany kurs, w którym aktualnie uczestniczy uczeń (wartość liczbowa od 1 do 6).
13. Ten element nie jest wymagany, ale będzie plusem. Napisz funkcję, która przejrzy wszystkie „Książki” i wyświetli wszystkie „tytuły” oddzielone przecinkami.

Wniosek

Seria o bazie danych trochę się przeciągnęła. Zgadzać się. Przeszliśmy jednak długą drogę i w efekcie wychodzimy z nią ze znajomością sprawy! Dziękuję wszystkim za przeczytanie, przypominam, że każdy kto chce ruszyć dalej i śledzić projekt musi założyć konto na GitHubie i subskrybować moje konto :) Więcej już wkrótce - porozmawiajmy o Mavenie i Dockerze. Dziękuję wszystkim za przeczytanie. Powtarzam jeszcze raz: kto idzie, drogę opanuje ;)

Lista wszystkich materiałów wchodzących w skład serii znajduje się na początku artykułu.