Analizujemy bazy danych i język SQL. (Część 4 - sprawdzenie pracy domowej) - "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. Poprzednie artykuły i analiza zadań domowych z baz danych: 1 , 2 , 3 . Wszystkim, którzy mieli cierpliwość i wytrwałość, każdemu, kto towarzyszył mi przy czwartym artykule – dobra robota. Jak mówią, ten, kto idzie, może opanować drogę. W tym tygodniu ukaże się ostatni artykuł o Bazach Danych, w którym porozmawiamy o rodzajach relacji i złączeń . Zanim jednak zajmiemy się nowymi informacjami, sprawdźmy naszą pracę domową... Naprawdę poczułem się jak nauczyciel. Nie złość się na mnie: nie mam wykształcenia pedagogicznego, to pewne. Ponieważ w zeszłym tygodniu lwią część materiału pochłonęło szczegółowe sprawdzenie pilota, postanowiłem podzielić analizę pracy domowej i recenzję nowego materiału na dwie części.

Właściwie to analiza pracy domowej

Zdecydowanie cieszę się, że są ludzie, którzy zajmują się PD i mówią o tym. To jest fajne! Jestem jak najbardziej pewna, że ​​samo czytanie bez utrwalenia wiedzy to droga donikąd. Dlatego każdemu, kto to zrobił lub próbował zrobić - szacunek. Przypomnę warunki zadań:

1. 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 napisze w komentarzach swoje przykładowe rozwiązanie - żebym też mógł to sprawdzić, jeśli będę miał czas.
2. Zainstaluj MySQL Workbench, aby pracować z bazą danych za pośrednictwem interfejsu użytkownika. Myślę, że mamy już wystarczająco dużo 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.
3. Napisz prośby o odbiór korzystając z 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.

Porozmawiajmy o MIECIU

Znajomość operatora Posiadanie może pomóc Ci przejść więcej niż jedną rozmowę kwalifikacyjną, podczas której wystąpią problemy z SQL. Dlatego zrozumienie tego jest niezwykle ważne. Tak się składa, że ​​do funkcji agregujących nie można używać warunków (SUM, MIN, MAX, AVG). Ponadto HAVING jest używany w przypadku zgrupowanych pól. Co to znaczy? Przykładowo, jeśli chcemy uzyskać kraje, w których średnia liczba mieszkańców w miastach przekracza 50 000 mieszkańców, nie możemy obejść się bez użycia HAVING. Jak rozumiem dzieje się tak dlatego, że agregacja następuje po wykonaniu instrukcji WHERE i nie ma możliwości dodania do niej wartości agregacji, które zostaną wyliczone później. Nawet jeśli jak na razie moje opinie nie wnoszą wiele do zrozumienia, możesz po prostu przyjąć to jako fakt i się z tym pogodzić. W programowaniu często zdarza się, że jeśli coś jest w danym momencie niejasne, może to oznaczać, że mózg po prostu jeszcze tego nie przetrawił. Zaśpij z tą myślą, a następnego dnia wszystko stanie się jaśniejsze.

Instalowanie środowiska roboczego MySQL

Tutaj i dalej będę korzystał z Workbencha do zapytań. Pokażę Ci co potrzebujesz zainstalować i utworzyć połączenie z bazą danych. Jest to produkt firmy Oracle, więc wystarczy wejść na ich stronę internetową i wybrać żądaną wersję i system operacyjny. Aby to zrobić, kliknij ten link : Tutaj możesz wybrać dokładnie taki system operacyjny, jakiego potrzebujesz. Kliknij Pobierz , ale zamiast pobierania pojawi się takie okno: Nie zgub się, po prostu poszukaj przycisku Nie, dziękuję, po prostu rozpocznij pobieranie , a pobieranie się rozpocznie. Dlaczego to robią? Prawdopodobnie po to, aby więcej osób zarejestrowało się u nich, nie jest to dla nas ważne. Po pomyślnym pobraniu uruchom plik instalacyjny. W systemie MacOS wygląda to tak: wystarczy przeciągnąć ikonę i gotowe, instalacja jest zakończona. Nie tak trudne, jak instalacja samego MySQL, prawda? Albo po prostu się do tego przyzwyczailiśmy i staliśmy się bardziej doświadczeni ;) Druga część tego zadania polega na nawiązaniu połączenia z naszą bazą danych. Co jest do tego potrzebne? Kliknij znak plus obok Połączenia MySQL: W wyświetlonym oknie wprowadź niezbędne dane:

• Nazwa połączenia - nazwa naszego połączenia. Nazwy należy pisać możliwie czytelnie, aby później nie było problemów z identyfikacją. Nadaję temu połączeniu nazwę JRTB_DB ;
• Nazwa hosta — będzie już ustawiona jako lokalna 127.0.0.1 (inaczej localhost). W naszym przypadku nic nie trzeba zmieniać, ponieważ baza danych jest zainstalowana na komputerze, natomiast jeśli baza danych znajduje się gdzie indziej, to należy odpowiednio zmienić host (ip maszyny, na której działa baza danych);
• Nazwa użytkownika - w razie potrzeby możesz także określić swojego użytkownika. Jeśli nie dodałeś niczego w tym kluczu, pozostaw go bez zmian;
• Hasło - kliknij Zapisz w pęku kluczy i ustaw dokładnie takie hasło, jakie sobie ustaliłeś. Zostawiłem wszystko proste - root .

Aby sprawdzić, czy połączenie będzie działać, kliknij Testuj połączenie : Cóż, jeśli wszystko zostało wykonane poprawnie, wynik nie będzie długo oczekiwany: Teraz mamy zapisane połączenie w bazie danych i nie będziemy musieli tworzyć połączenia i wypełniać za każdym razem w nazwie i haśle. A to szczęście będzie wyglądało tak: Wchodzimy w nowo utworzone połączenie i widzimy okno żądania. Aby mieć pewność, że wszystko się zgadza, sprawdźmy listę baz, przejdźmy do naszej i zdobądźmy wszystkie dane o miastach: I tu dostajemy carte blanche na to, czego potrzebujemy. Pierwsza sekcja odpowiada za skrypt, który wpisujemy. Następnie w Result Grid widzimy wynik ostatniej operacji w skrypcie. A Action Output pokazuje listę operacji i ich wynik. Bardzo przydatna rzecz, chcę ci powiedzieć: za jej pomocą możesz monitorować szybkość wykonywania niektórych skryptów. Dlaczego to jest ważne? Jednym z najczęstszych problemów związanych z szybkością wykonywania zadań w aplikacji jest szybkość wykonywania zapytań w bazie danych. Tutaj możesz szybko i wygodnie sprawdzić je ręcznie.

Piszemy niezbędne wnioski

Mamy tylko 7 próśb do zrealizowania, zaczynamy!

1. Zdobądź najbardziej zaludniony kraj. Tutaj możesz iść sprytnie i wybrać kilka tras:

• Według tabeli krajów

Następnie wystarczy posortować zapytanie według populacji i wziąć tylko jeden rekord. W tym celu należy na końcu skryptu dodać operator LIMIT i podać wymaganą ilość: $ WYBIERZ * Z kraju ZAMÓW PRZEZ populację OPIS LIMIT 1;

• Według tablicy miejskiej

Tutaj wszystko jest bardziej interesujące, ponieważ żądanie będzie bardziej złożone, ale także ciekawsze. Ponieważ nadal nie mamy pojęcia o przyłączach, możemy uzyskać jedynie identyfikator kraju: $ SELECT country_id, SUM(populacja) FROM city GROUP BY country_id ORDER BY SUM(populacja) DESC LIMIT 1; Tutaj zrobiliśmy fajną rzecz - zebraliśmy sumę populacji wszystkich znanych miast w każdym kraju, posortowaliśmy według tej sumy i wzięliśmy pierwszy element. Czy jest wspaniale? Jestem zachwycony :D Po tym od razu czujesz się jak guru zapytań... (oczywiście nie na długo))

2. Zdobądź najmniejszy kraj. Tutaj można iść sprytnie i wybrać kilka tras

W tym przypadku wszystko będzie dokładnie takie samo. Jedyna różnica polega na tym, że sortowanie zostanie odwrócone – to wszystko. Dlatego po prostu piszę prośby:

• Według tablicy miejskiej

$ WYBIERZ kraj_id, SUM(populacja) Z miasta GRUPA WG kraju_id ZAMÓW WG SUMY(populacja) LIMIT 1;

• Według tabeli krajów

$ WYBIERZ * Z kraju ZAMÓW WG LIMITÓW 1; I zobacz wynik na własne oczy!

3. Przeciętna liczba mieszkańców w kraju

Tutaj znowu specyfikacja techniczna jest jakoś nie dokładna, jakby napisał ją menadżer... Dlaczego się na to zdecydowałem? Ponieważ nie jest jasne, przy którym stole pracować. Ale to normalne: po prostu nie ma zadań, w których wszystko będzie od razu jasne i zrozumiałe. Dlatego należy dokładnie zapoznać się z zadaniami, a jeśli pojawią się pytania, należy je od razu zadać! Zgadza się, uwaga. Biorąc pod uwagę dane, które posiadamy w bazie, będziemy szukać po danych z miast. Aby to zrobić, napisz następujące zapytanie: $ SELECT id_kraju, AVG(populacja) FROM city GROUP BY country_id; Tutaj jest to proste: używamy funkcji AVG i grupujemy rekordy miast według krajów.

4. Średnia liczba mieszkańców w krajach, których nazwy kończą się na „a”

Na życzenie nastąpi tu niewielka zmiana. Zanim zaczniemy grupować, musimy dodać filtrowanie według nazw. Jak wszyscy studenci, przed opublikowaniem tego artykułu odrabiam pracę domową i rozumiem, że tego problemu nie da się rozwiązać bez złączeń. Dlaczego? Ponieważ oprócz numeru identyfikacyjnego kraju musimy również uzyskać jego imię i nazwisko. Nie można tego zrobić bez połączenia dwóch tabel w jeden rekord. Dlatego oczywiście wykonam to zadanie, ale to jest mój oścież...))) Chciałem wymyślić zadanie, używając LIKE w żądaniu...) $ SELECT ci.country_id, AVG(ci.population ) Z miasta ci WEWNĘTRZNE DOŁĄCZENIE do kraju co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id; Co tu się stało? Najpierw połączyliśmy rekordy z tabel miast i krajów, używając klucza obcego country_id, przefiltrowaliśmy według nazw krajów, tak aby kończyły się na „a”, a dopiero potem pogrupowaliśmy według country_id.

5. Liczba krajów o populacji większej niż cztery miliony

Tutaj wystarczy skorzystać z funkcji COUNT i dodać filtrowanie według populacji: $ SELECT COUNT(*) z kraju GDZIE populacja > 4000000; W rezultacie dowiadujemy się, że istnieją 3 takie kraje.Czy to prawda? Tak, tylko Mołdawia nie przekracza tego kamienia milowego.

6. Sortuj kraje według malejącej liczby mieszkańców

W tym celu musimy skorzystać ze znanego nam już operatora ORDER BY. Należy jednak pamiętać, że domyślnie sortowanie odbywa się w porządku naturalnym. W przypadku liczb oznacza to, że są one sortowane w kolejności rosnącej, w przypadku ciągów znaków oznacza to, że są one sortowane począwszy od pierwszych znaków. Jeśli potrzebujemy sortowania malejącego, potrzebujemy odwrotności sortowania naturalnego: $ SELECT * Z kraju ORDER BY populacji DESC;

7. Sortuj kraje według nazwy w kolejności naturalnej

Tutaj przyda się wiedza o tym, czym jest porządek naturalny. Ponieważ jest to ustawienie domyślne, jest to dla nas bułka z masłem: $ WYBIERZ * Z kraju ZAMÓW WEDŁUG nazwy;

Zamiast wyjścia

Tak się składa, że ​​objętość rozwiązania zadania domowego będzie bardzo duża, dlatego zrobimy wyjątek: publikuję ten artykuł z czekiem, a w piątek opublikuję nowy materiał z połączeniami i złączeniami. Dziękuję wszystkim za przeczytanie. Do piątku!

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