Analizujemy bazy danych i język SQL. (Część 3) - „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.

• Sprawdzanie pracy domowej
  • Dodanie klucza podstawowego
  • Dodanie Mołdawii
  • Utwórz tabelę miast
  • Dodaj klucz obcy do miast
  • Część bonusowa. Testowanie
• WYBIERZ instrukcję
  • GDZIE parametr
  • ZAMÓW PRZEZ parametr
  • Parametr GRUPUJ WEDŁUG
• Praca domowa
• Wniosek

Witam, panie i panowie, porozmawiajmy dalej o bazach danych, SQL i innych sprawach. Dzisiejszy materiał będzie zawierał teorię części i praktykę części. Przypomnę, że ostatnim razem rozmawialiśmy o tym, jak wszystko skonfigurować, jak stworzyć bazę danych, tabelę i pobrać z niej dane. Czas sprawdzić, czy coś wyszło z teledetekcją. Moim zdaniem połowę z tego można było zrobić tylko w oparciu o poprzedni artykuł. Okazało się, że aby poprawnie złożyć aplikację i uczynić wszystko mniej lub bardziej pięknym, trzeba rozmawiać o bazach danych, a żeby rozmawiać o nich trzeba poświęcić dużo czasu.

Sprawdzanie pracy domowej

Ogromny szacunek dla wszystkich, którzy pomyślnie wykonali zadania. Oznacza to, że rozumiesz, że tylko Ty tego potrzebujesz i to tylko Ci pomaga. Tym, którzy zaniedbali moje zadanie, przypomnę warunek:

1. Należy dodać klucz podstawowy (KLUCZ PODSTAWOWY) z pola ID do schematu tabeli krajów.
2. Dodaj kolejny kraj do tabeli krajów - Mołdawia.
3. Zgodnie ze schematem z poprzedniego artykułu utwórz tabelę miasta, która będzie zawierać wszystkie opisane pola. Nazwy pól będą następujące: id, nazwa, country_id, populacja.
4. Dodaj klucz podstawowy do tabeli miast.
5. Dodaj klucz obcy do tabeli miast.

Na początek skorzystajmy z pierwszej części poprzedniego artykułu i przejdźmy do terminala bazy danych.

Dodanie klucza podstawowego

Klucz podstawowy (KLUCZ PODSTAWOWY) możesz dodać na dwa sposoby: natychmiast podczas tworzenia tabeli lub po utworzeniu za pomocą ALTER TABLE.

Klucz podstawowy podczas tworzenia tabeli

Ponieważ stworzyliśmy już tabelę i bez jej usunięcia nie będziemy mogli pokazać tego podejścia w tej bazie danych, po prostu utworzymy tymczasową bazę testową, w której wszystko zrobimy. Wprowadźmy następujące polecenia:

• utwórz nową bazę danych:

  $UTWÓRZ BAZĘ DANYCH test;

• utwórz tabelę dodając klucz podstawowy:

  $ UTWÓRZ TABELĘ kraj(id INT, nazwa VARCHAR(30), KLUCZ PODSTAWOWY (id));

Generalnie nic skomplikowanego. Po zadeklarowaniu zmiennych dodawana jest część PRIMARY KEY (id) , gdzie w nawiasie podana jest nazwa pola, które będzie kluczem podstawowym. I zobaczmy jak zmienił się schemat tabeli: $ DESC kraj; Jak widać, w polu Klucz wpisu identyfikatora pojawiła się wartość PRI .

Klucz podstawowy po utworzeniu tabeli

Jak mówiłem wcześniej, pierwszy klucz po utworzeniu tabeli można przypisać za pomocą polecenia ALTER TABLE . Uruchomimy ten przykład w naszej bazie danych miast :

• przejdźmy do naszej bazy danych z testowej:

  $USE miasta;

• Sprawdźmy czy na pewno jesteśmy w naszej bazie (powinno być tam jeszcze jedno pole – populacja). W tym celu piszemy:

  $ DESC populacja;

wszystko się zgadza, stół jest nasz. Napiszmy co następuje:

$ ZMIEŃ TABELĘ kraj DODAJ KLUCZ PODSTAWOWY (id);

• i sprawdź to natychmiast za pomocą polecenia:

  $DESC kraj;

Jak widać na zdjęciu wszystko się zgadza, wartość PRI jest dokładnie taka jak powinna. Nawiasem mówiąc, pracowaliśmy z testową bazą danych. Teraz musimy go usunąć: po co zaśmiecać serwer, prawda? W tym celu używamy dość znanego polecenia: $ DROP DATABASE test;

Dodanie Mołdawii

Najpierw musimy zdecydować, co będziemy nagrywać. Naszym następnym identyfikatorem będzie 4. Nazwa będzie Mołdawia, a jej populacja wynosi 3550900. Dlatego wykonujemy polecenie INSERT INTO, które już znamy: $ INSERT INTO country VALUES (4, 'Mołdawia', 3550900); I sprawdzamy, czy ta wartość jest dokładnie w bazie: $ SELECT * Z kraju WHERE id = 4; W żądaniu danych od razu ustaliłem, które pole będzie przeszukiwane, więc otrzymaliśmy tylko jeden rekord, a tego nam było potrzeba.

Utwórz tabelę miast

Korzystając z diagramu z pierwszego artykułu o bazie danych, uzyskujemy niezbędne informacje o tabeli. Będzie zawierać następujące pola:

• id — unikalny identyfikator;
• name — nazwa miasta;
• country_id — klucz obcy kraju;
• populacja — ludność miasta.

Wpisywanie za każdym razem unikalnego identyfikatora jest trochę stresujące, nie sądzisz? Chciałbym pozostawić tę kwestię władzom MySQL . A jest taki sposób - AUTO PRZYROST . Musimy dodać to do pola cyfrowego i jeśli nie przekażemy jawnie wartości, sam MySQL zwiększy identyfikator o jeden w porównaniu do poprzedniego. Dlatego utworzenie tabeli będzie wyglądać następująco: $ CREATE TABLE miasto ( id INT AUTO_INCREMENT, nazwa VARCHAR(30), country_id INT, populacja INT, PRIMARY KEY (id)); Spójrzmy na diagram tabeli, aby zobaczyć, czy wszystko zostało wykonane poprawnie: $ DESC miasto; Jak widać na diagramie tabeli, mamy nowy opis pola id - auto_inkrementacja. Więc zrobiliśmy wszystko dobrze. Sprawdźmy dane na w pełni skonfigurowanej tabeli. Aby to zrobić, wykonamy ostatnią część zadania - klucz obcy.

Dodaj klucz obcy do miast

Dla klucza obcego będzie następująca komenda: $ ALTER TABLE miasto DODAJ KLUCZ OBCY (id_kraju) REFERENCJE kraj(id); I od razu sprawdźmy, co jest nie tak ze schematem tabeli: czy zmienił się w ciągu godziny? $DESC miasto;

Część bonusowa. Testowanie

Zapomniałem dodać to do zadania - uzupełnij dane, które były na zrzucie ekranu pierwszej części. Zapomniałem, więc teraz zrobię to sam. A dla zainteresowanych można to zrobić samemu beze mnie i wtedy sprawdzimy ;) Był Charków, Kijów, Mińsk, Odessa, Woroneż, dodamy też Kiszyniów. Ale tym razem nie będziemy przesyłać identyfikatorów, pominiemy je: $ WSTAWIĆ W miasto (nazwa, identyfikator kraju, ludność) WARTOŚCI („Charków”, 1, 1443000), („Kijów”, 1, 3703100), („Mińsk” , 3, 2545500), („Odessa”, 1, 1017699), („Woroneż”, 2, 1058261), („Kiszyniów”, 4, 695400); Jak widać, jednym poleceniem INSERT INTO można dokonać kilku wpisów jednocześnie. Przydatna rzecz, pamiętaj) I od razu zobaczmy, co jest w tabeli: $ WYBIERZ * Z miasta; AUTO_INCREMENT - działało dokładnie tak, jak chcieliśmy. Wszystkie pliki identyfikacyjne są wypełnione, mimo że ich nie przesłaliśmy. Klucz obcy jest rzeczą zależną. Aby sprawdzić czy działa poprawnie, możesz spróbować napisać klucz obcy, którego nie ma w tabeli obcej. Załóżmy, że zdecydowaliśmy, że id = 5 to Kazachstan. Ale w rzeczywistości nie ma go w tabeli krajów. A żeby sprawdzić czy baza będzie przeklinać dodaj miasto - Astana: $ WSTAW DO miasta (nazwa, id_kraju, populacja) WARTOŚCI ('Astana', 5, 1136156); I naturalnie pojawia się błąd: Teraz klucz obcy pilnuje, abyśmy nie próbowali przypisać kraju do miasta, którego nie ma w naszej bazie danych. Tę część zadania domowego można uznać za zakończoną - przejdźmy do nowej :)

WYBIERZ instrukcję

Cóż, wszystko nie wydaje się już takie straszne, prawda? Jeszcze raz chciałbym zaznaczyć, że dla programistów Java znajomość baz danych jest koniecznością. Bez bazy danych nie można nigdzie dojść. Tak, chcę już zacząć pisać aplikację, zgadzam się. Ale to konieczne. Zatem będziemy kontynuować tę drogę. Za pomocą instrukcji SELECT pobieramy dane z bazy danych. Oznacza to, że jest to typowa operacja DML (zapomniałeś już, co to jest?...))) Przeczytaj ponownie artykuły PRZED). Jakie są zalety relacyjnych baz danych? Mają świetną funkcjonalność do agregowania i pobierania danych. Do tego służy instrukcja SELECT. Wydawać by się mogło, że nie ma w tym nic skomplikowanego, prawda? Okazuje się jednak, że jest jeszcze wiele do zrozumienia). Ważne jest, abyśmy zrozumieli podstawy, na których możemy bazować. Najprostszym zapytaniem z instrukcją SELECT jest wybranie wszystkich danych z jednej tabeli. Bardzo spodobał mi się opis z wiki dotyczący dokładnej kolejności operatorów w zapytaniu SELECT, więc bezczelnie skopiuję go tutaj:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Tutaj możesz zobaczyć, że nie możesz najpierw umieścić operatora GROUP BY, a potem WHERE. Należy o tym pamiętać, aby później nie było żalu za błędy, które nie są jasne, skąd pochodzą. $WYBIERZ * Z miasta; Jednak wymazywanie wszystkich danych najwyraźniej nie jest dla nas zabawą. Dokładnie tak samo jest gdybyśmy chcieli wbijać gwoździe pod mikroskopem [1] , [2] . Ponieważ baza danych wykonuje operacje filtrowania, sortowania i agregacji znacznie szybciej niż kod Java, lepiej pozostawić tę kwestię bazie danych. Dlatego komplikując zadania, otworzymy nową funkcjonalność.

GDZIE parametr

Do filtrowania zaznaczenia używane jest słowo WHERE . Należy to interpretować w następujący sposób: SELECT * FROM nazwa_tabeli (wybierz wszystkie pola z tabeli nazwa_tabeli) WHERE talbe_row = 1 (gdzie w rekordach pole wiersz_tabeli ma wartość 1). Należy pamiętać, że kolejność słów kluczowych w zapytaniu ma znaczenie. Nie możesz napisać WHERE a =1 FROM nazwa_tabeli SELECT *. W przypadku języka rosyjskiego jest to w porządku i dla niektórych może nie wydawać się takie złe, ale w przypadku SQL jest to nie do przyjęcia. Piszemy następujące zapytanie: $ SELECT * FROM city WHERE country_id = 1; I wybraliśmy ukraińskie miasta. Nieźle, prawda? A co jeśli chcemy nie tylko ukraińskiego, ale i białoruskiego? W tym celu możemy wypisać zbiór wartości, jakie pole może przyjmować: $SELECT * FROM city WHERE country_id IN(1, 3); A w odpowiedzi mamy już miasta z dwóch krajów. Co się stanie, jeśli istnieje wiele warunków do filtrowania? Powiedzmy, że chcemy miast o populacji przekraczającej dwa miliony? Aby to zrobić, użyj słów OR i AND : $ WYBIERZ * Z miasta WHERE country_id IN (1, 3) ORAZ populacja > 2000000; Świetnie, ale co jeśli dodamy jeszcze jeden warunek - wyszukiwanie nazw za pomocą wyrażenia regularnego (nie będę tu opisywał wyrażeń regularnych: oto osoba, która zrobiła to „na krótko” w 4 częściach )? Na przykład pamiętamy, jak przeliterować miasto, ale nie do końca... Aby to zrobić, możesz dodać słowo kluczowe LIKE do wyrażenia filtrującego : $ SELECT * FROM city WHERE country_id IN (1, 3) AND populacja > 2000000 OR nazwa JAK „%hark%”; I w ten sposób zdobyliśmy także Charków. W rezultacie możemy powiedzieć, że nasze poszukiwania wypadły bardzo dobrze. Ale chciałbym sortować nie według identyfikatora, ale według populacji, ale jak? Tak, bardzo proste...

ZAMÓW PRZEZ parametr

Za pomocą ORDER BY możemy posortować otrzymane rekordy według konkretnego pola. Sortuje zarówno liczby, jak i ciągi znaków. Rozwińmy poprzednie zapytanie, posortuj według populacji, dodając ORDER BY populacji: $ SELECT * FROM miasta WHERE country_id IN (1, 3) AND populacja > 2000000 LUB nazwa LIKE „%hark%” ORDER BY populacja; Jak widać, sortowanie odbyło się w porządku naturalnym, czyli rosnącym. A co jeśli chcemy czegoś przeciwnego? Aby to zrobić, musisz dodać słowo DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) ORAZ populacja > 2000000 LUB nazwa JAK „%hark%” ORDER BY populacja DESC; Teraz sortowanie opiera się na redukcji populacji. Baza danych robi to bardzo szybko: nie można porównać żadnego zbioru Collections.sort . Teraz posortujmy według wierszy, według nazwy w odwrotnej kolejności: $ WYBIERZ * Z miasta WHERE identyfikator_kraju IN (1, 3) ORAZ populacja > 2000000 LUB nazwa JAK „%hark%” ZAMÓW WG nazwy DESC;

Parametr GRUPUJ WEDŁUG

Służy do grupowania rekordów według określonych pól. Jest to zwykle potrzebne do korzystania z funkcji agregujących... Co to są funkcje agregujące?)) Grupowanie według niektórych pól ma sens, jeśli są one takie same dla różnych rekordów. Spójrzmy, co to oznacza na naszym przykładzie. Załóżmy, że miasta mają klucze obce – identyfikatory krajów. Zatem identyfikator jest taki sam dla miast z tego samego kraju. Można więc według nich pobierać i grupować rekordy: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Ale bez funkcji agregujących wygląda to trochę słabo, trzeba przyznać. Dlatego przyjrzyjmy się kilku najczęstszym funkcjom:

• COUNT - liczba rekordów, której można używać bez grupowania, używana jako COUNT(*) . W przypadku grupowania według jakiegoś pola - COUNT(groupped_field);
• MAX - znajduje maksymalną wartość dla określonego pola;
• MIN - znajduje minimalną wartość dla konkretnego pola;
• SUM - znajduje sumę dla określonego pola;
• AVG - znajduje wartość średnią.

Generalnie z tych funkcji można korzystać bez grupowania, tylko wówczas zostanie wyświetlone tylko jedno pole. Wypróbujmy je dla populacji naszego miasta: $ WYBIERZ LICZBĘ(*) Z miasta; O co prosili, to otrzymali. Tylko ilość rekordów. Czasami jest to przydatne. Na przykład, jeśli chcemy sprawdzić liczbę artykułów określonego autora. Nie ma potrzeby wyciągania ich z bazy danych i liczenia. Możesz po prostu użyć COUNT(). $ WYBIERZ AVG(populacja) Z miasta; $ WYBIERZ MIN(ludność) Z miasta; I tu zaczyna obowiązywać grupowanie. Przykładowo zadaniem jest zdobycie najmniejszego miasta w kraju. Już wiesz jak to zrobić? Wypróbuj sam, a następnie obejrzyj: $ WYBIERZ identyfikator_kraju jako Kraj, MIN(populacja) Z miasta WHERE GROUP WG kraju_id; Na razie widzimy tylko numer identyfikacyjny kraju, ale to nie ma znaczenia – następnym razem zrobimy wszystko. I tak już jest rezultat i mamy to, czego chcieliśmy - najmniejsze miasto w kraju o ID = 1. Reszta funkcji będzie taka sama. Należy pamiętać, że zaznaczenie wszystkich pól * podczas korzystania z grupowania i agregacji nie będzie działać! Pomyśl o tym ;)

Praca domowa

Z wyników poprzednich artykułów jasno wynika, że ​​praca domowa jest odrabiana, więc kontynuujmy)) Tak, każdy, kto odrabia pracę domową, będzie nadal wstawiał „+” w komentarzach. Zależy mi na tym, aby temat zadań domowych był dla Was interesujący, dzięki czemu będę je kontynuować w przyszłości. Tak, regularnie czytam Twoje komentarze. Oczywiście odpowiadam rzadziej. Widziałem, że poprosili o podanie trudniejszych problemów SQL. Dopóki nie nauczymy się złączeń, nie będzie ciekawych problemów, więc pozostaną takie, które będą mi potrzebne do dalszego materiału.

Zadania:

Zapoznaj się z operatorem HAVING i napisz przykładowe zapytanie do tabel z naszego przykładu. Jeśli chcesz dodać jakieś pola lub więcej wartości, żeby było bardziej przejrzyście, dodaj je. Jeśli ktoś chce, niech w komentarzu napisze swoje przykładowe rozwiązanie: w ten sposób też będę mógł to sprawdzić, jeśli będę miał czas.
1. Zainstaluj MySQL Workbench, aby pracować z bazą danych za pośrednictwem interfejsu użytkownika. Myślę, że mamy już dość praktyki w pracy z konsoli. Połącz się z bazą danych. Jeśli do pracy z bazą danych używasz czegoś innego, możesz pominąć to zadanie. Tutaj i dalej będę korzystał wyłącznie z MySQL Workbench.
2. Napisz prośby o otrzymanie z wykorzystaniem naszych danych:
   1. najmniejszy/najbardziej zaludniony kraj;
   2. średnia liczba mieszkańców w kraju;
   3. średnia liczba mieszkańców w krajach, których nazwy kończą się na „a”;
   4. liczba krajów o populacji przekraczającej cztery miliony;
   5. sortuj kraje według malejącej liczby mieszkańców;
   6. sortuj kraje według nazw w porządku naturalnym.

Wniosek

Dzisiaj szczegółowo omówiliśmy zadanie domowe z ostatniej lekcji. Co więcej, uważam to za ważne zarówno dla tych, którzy tego nie zrobili, jak i dla tych, którzy to zrobili. Dla pierwszego jest to szansa na znalezienie odpowiedzi, dla drugiego na porównanie jej z uzyskanym wynikiem. Subskrybuj moje konto GitHub , aby być na bieżąco ze zmianami w projekcie. Będę tam utrzymywać całą bazę kodu. Wszystko będzie się działo w tej organizacji . Następnie omówiliśmy instrukcję SELECT. On jest dla nas najważniejszy. To dzięki niemu będą rozpatrywane wszystkie prośby o dane i musimy to zrozumieć. Najważniejsze jest, aby pamiętać o kolejności dodawania parametrów (WHERE, ORDER BY, GROUP BY i tak dalej). Tak, nie powiedziałem wszystkiego, co było możliwe, ale nie stawiam sobie takiego celu. Tak, wiem, że już nie możesz się doczekać napisania aplikacji. Bądź cierpliwy, to wszystko, czego potrzebujesz. Zarówno dla projektu, jak i dla Twojego rozwoju zawodowego. Czekając, upewnij się, że Git jest już Ci znany. Będę go domyślnie używać, jako dobrze znanego narzędzia. Dziękuję wszystkim za przeczytanie. W następnym artykule porozmawiamy o połączeniach i złączeniach z bazami danych. Tam będą fajne zadania))

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