Dowiedz się, jak korzystać z kilku funkcji MySQL i Mariadb - część 2

To druga część serii 2-artykułów o podstawowych rzeczach MARIADB / MYSQL polecenia. Przed kontynuowaniem zapoznaj się z naszym poprzednim artykułem na ten temat.

1. Dowiedz się podstaw MySQL/Mariadb dla początkujących - część 1

W tej drugiej części serii MySQL/MariaDB wyjaśnimy, jak ograniczyć liczbę wierszy zwróconych przez WYBIERAĆ zapytanie i jak zamówić zestaw wyników na podstawie danego warunku.

Ponadto nauczymy się, jak grupować rekordy i wykonać podstawową manipulację matematyczną na polach numerycznych. Wszystko to pomoże nam stworzyć skrypt SQL, którego możemy użyć do tworzenia przydatnych raportów.

Wymagania wstępne

Aby rozpocząć, wykonaj następujące kroki:

1. Pobierz pracownicy Przykładowa baza danych, która zawiera sześć tabel składających się z 4 W sumie milion zapisów.

# wget https: // startpad.netto/test-db/pracownicy-DB-1/1.0.6/+pobierz/pracownicy_db-full-1.0.6.smoła.BZ2 # TAR XJF PRACOWNIKA_DB-FULL-1.0.6.smoła.BZ2 # CD PRACOWNIK_DB 

2. Wejdz do Mariadb monit i utwórz nazwaną bazę danych pracownicy:

# mysql -u root -p Wprowadź hasło: Witamy w monitor Mariadb. Polecenia kończą się; lub \ g. Twój identyfikator połączenia Mariadb to 2 wersja serwerowa: 10.1.14-Mariadb Mariadb Server Copyright (C) 2000, 2016, Oracle, Mariadb Corporation AB i inni. Typ „Pomoc”; lub „\ h”, aby uzyskać pomoc. Typ „\ c”, aby wyczyścić bieżącą instrukcję wejściową. Mariadb [(brak)]> Utwórz pracowników bazy danych; Zapytanie OK, dotknięty 1 rząd (0.00 s) 

3. Zaimportuj go na serwerze Mariadb w następujący sposób:

Mariadb [(brak)]> Pracownicy źródłowi.SQL 

Poczekaj 1-2 minuty, aż przykładowa baza danych zostanie załadowana (pamiętaj o tym 4M zapisy tutaj!).

4. Sprawdź, czy baza danych została poprawnie zaimportowana, wymieniając jej tabele:

Mariadb [pracownicy]> Używać pracowników; Baza danych zmieniła się Mariadb [pracownicy]> Pokaż tabele; +---------------------+ |. TABELE_IN_EMPLOTEEES | + ---------------------+ | Departamenty | |. DEPT_EMP | |. Dept_Manager | |. Pracownicy | |. pensje | |. tytuły | + ---------------------+ 6 wierszy w zestawie (0.02 s) 

5. Utwórz specjalne konto do użycia z pracownicy baza danych (wybierz kolejną nazwę konta i hasło):

Mariadb [pracownicy]> Utwórz użytkownika [chronione e -mail] zidentyfikowane przez „EmpAdminPass”; Zapytanie OK, 0 dotkniętych wierszy (0.03 s) Mariadb [pracownicy]> Przyznać wszystkie przywileje na pracowników.* do [e -mail chroniony]; Zapytanie OK, 0 dotkniętych wierszy (0.02 s) Mariadb [pracownicy]> Przywileje spłukiwane; Zapytanie OK, 0 dotkniętych wierszy (0.00 s) Mariadb [pracownicy]> Wyjście Do widzenia 

Teraz się zaloguj jako empadmin Użytkownik do MARIADB.

# mysql -u Empadmin -p Wprowadź hasło: Witamy w monitor Mariadb. Polecenia kończą się; lub \ g. Twój identyfikator połączenia Mariadb to 4 wersja serwerowa: 10.1.14-Mariadb Mariadb Server Copyright (C) 2000, 2016, Oracle, Mariadb Corporation AB i inni. Typ „Pomoc”; lub „\ h”, aby uzyskać pomoc. Typ „\ c”, aby wyczyścić bieżącą instrukcję wejściową. Mariadb [(brak)]> Używać pracowników; Czytanie informacji o tabeli dla zakończenia nazwy tabeli i kolumn 

Naucz się podstawowych poleceń mysql dla początkujących

Upewnij się, że wszystkie kroki przedstawione na powyższym obrazie zostały zakończone przed kontynuowaniem.

Zamawianie i ograniczenie liczby wierszy w zestawie wyników

Tabela wynagrodzeń zawiera wszystkie dochody każdego pracownika z datami początkowymi i końcowymi. Możemy chcieć zobaczyć pensje emp_no = 10001 nadgodziny. Pomoże to odpowiedzieć na następujące pytania:

1. Czy on / ona dostała wszelkie podwyżki?
2. Jeśli tak, to kiedy?

Wykonaj następujące zapytanie, aby dowiedzieć się:

Mariadb [pracownicy]> Wybierz * z wynagrodzeń, gdzie emp_no = 10001 Zamów przez od_date; +--------+--------+------------+------------+ |. emp_no | wynagrodzenie | From_Date | to_date | +--------+--------+------------+------------+| 10001 | 60117 | 1986-06-26 | 1987-06-26 | |. 10001 | 62102 | 1987-06-26 | 1988-06-25 | |. 10001 | 66074 | 1988-06-25 | 1989-06-25 | |. 10001 | 66596 | 1989-06-25 | 1990-06-25 | |. 10001 | 66961 | 1990-06-25 | 1991-06-25 | |. 10001 | 71046 | 1991-06-25 | 1992-06-24 | |. 10001 | 74333 | 1992-06-24 | 1993-06-24 | |. 10001 | 75286 | 1993-06-24 | 1994-06-24 | |. 10001 | 75994 | 1994-06-24 | 1995-06-24 | |. 10001 | 76884 | 1995-06-24 | 1996-06-23 | |. 10001 | 80013 | 1996-06-23 | 1997-06-23 | |. 10001 | 81025 | 1997-06-23 | 1998-06-23 | |. 10001 | 81097 | 1998-06-23 | 1999-06-23 | |. 10001 | 84917 | 1999-06-23 | 2000-06-22 | |. 10001 | 85112 | 2000-06-22 | 2001-06-22 | |. 10001 | 85097 | 2001-06-22 | 2002-06-22 | |. 10001 | 88958 | 2002-06-22 | 9999-01-01 | +--------+--------+------------+------------+17 wierszy w zestawie ( 0.03 s) 

Teraz, jeśli będziemy musieli wyświetlić ostatnie 5 podwyższeń? Możemy zrobić Zamów przez DESC z_date. Desc Słowo kluczowe wskazuje, że chcemy sortować zestaw wyników w kolejności malejącej.

Dodatkowo, Limit 5 pozwala nam zwrócić tylko górę 5 Rzędy w zestawie wyników:

Mariadb [pracownicy]> Wybierz * z wynagrodzeń, gdzie emp_no = 10001 Zamów przez From_Date DESC Limit 5; +--------+--------+------------+------------+ |. emp_no | wynagrodzenie | From_Date | to_date | +--------+--------+------------+------------+| 10001 | 88958 | 2002-06-22 | 9999-01-01 | |. 10001 | 85097 | 2001-06-22 | 2002-06-22 | |. 10001 | 85112 | 2000-06-22 | 2001-06-22 | |. 10001 | 84917 | 1999-06-23 | 2000-06-22 | |. 10001 | 81097 | 1998-06-23 | 1999-06-23 | +--------+--------+------------+------------+5 wierszy w zestawie ( 0.00 s) 

Zapytanie Mysql Tabela według zamówienia daty

Możesz także użyć ZAMÓW PRZEZ z wieloma pól. Na przykład następujące zapytanie zamówi zestaw wyników na podstawie daty urodzenia pracownika w formie rosnącej (domyślnie), a następnie według nazwisk w alfabetycznej formie malejącej:

Mariadb [pracownicy]> Wybierz Conat (Last_name, ',', First_name) jako nazwa, płeć jako płeć, hire_date jako „data wynajęcia” z zamówienia pracowników według ddate, limit Last_name DESC 10; +--------------------+--------+------------+ |. Nazwa | Płeć | Data zatrudniania | +--------------------+--------+------------+| Whitcomb, kiyokazu | M | 1988-07-26 | |. Schaad, Ronghao | M | 1988-07-10 | |. Remmele, supot | M | 1989-01-27 | |. Pocchiola, Jouni | M | 1985-03-10 | |. Kuzuoka, Eishiro | M | 1992-02-12 | |. Decaestecker, Moni | M | 1986-10-06 | |. Wiegley, Mircea | M | 1985-07-18 | |. Vendrig, Sachar | M | 1985-11-04 | |. Tsukuda, Cedric | F | 1993-12-12 | |. Tischendorf, Percy | M | 1986-11-10 | +--------------------+--------+------------+10 wierszy w zestawie (0.31 sekund) 

Zapytanie Mysql Tabela według daty urodzenia

Możesz wyświetlić więcej informacji o LIMIT Tutaj.

Grupowanie rekordów / Max, Min, AVG i rundy

Jak wspomnialiśmy wcześniej, pensje Tabela zawiera dochody każdego pracownika z czasem. Oprócz LIMIT, Możemy użyć Max I Min Słowa kluczowe w celu ustalenia, kiedy zatrudniono maksymalną i minimalną liczbę pracowników:

Mariadb [pracownicy]> Wybierz Conat (Last_name, ',', First_name) jako nazwa, Max (b.wynagrodzenie) jako „maks. wynagrodzenie „od pracowników A pensje B na a.emp_no = b.emp_no gdzie a.emp_no w grupie (10001, 10002, 10003) według a.emp_no; +-----------------+-------------+ |. Nazwa | Max. wynagrodzenie | +-----------------+-------------+| Facello, Georgi | 88958 | |. Simmel, Bezalel | 72527 | |. Bamford, Parto | 43699 | +-----------------+-------------+3 wiersze w zestawie (0.02 s) Mariadb [pracownicy]> Wybierz Conat (Last_name, ',', First_name) jako nazwa, min (b.wynagrodzenie) jako „min. wynagrodzenie „od pracowników A pensje B na a.emp_no = b.emp_no gdzie a.emp_no w grupie (10001, 10002, 10003) według a.emp_no; +-----------------+-------------+ |. Nazwa | Min. wynagrodzenie | +-----------------+-------------+| Facello, Georgi | 60117 | |. Simmel, Bezalel | 65828 | |. Bamford, Parto | 40006 | +-----------------+-------------+3 wiersze w zestawie (0.00 s) 

Grupowanie rekordów mysql za pomocą słów kluczowych max i min

Na podstawie powyższych zestawów wyników, czy możesz zgadnąć, co powróci poniższe zapytanie?

Mariadb [pracownicy]> Wybierz Conat (Last_name, ',', First_name) jako nazwa, okrągła (AVG (b.wynagrodzenie), 2) jako „AVG. wynagrodzenie „od pracowników A pensje B na a.emp_no = b.emp_no gdzie a.emp_no w grupie (10001, 10002, 10003) według a.emp_no; +-----------------+-------------+ |. Nazwa | Avg. wynagrodzenie | +-----------------+-------------+| Facello, Georgi | 75388.94 | |. Simmel, Bezalel | 68854.50 | |. Bamford, Parto | 43030.29 | +-----------------+-------------+3 wiersze w zestawie (0.01 s) 

Jeśli zgodzisz się, że zwróci średnią (jak określono przez Avg) wynagrodzenie w czasie zaokrąglonym do 2 dziesiętnych (jak wskazuje OKRĄGŁY), masz rację.

Jeśli chcemy zobaczyć sumę wynagrodzeń pogrupowanych przez pracownika i zwrócić górę 5, Możemy użyć następującego zapytania:

Mariadb [pracownicy]> Wybierz emp_no, suma (wynagrodzenie) jako pensję z grupy wynagrodzeń według zamówienia emp_no według wynagrodzenia limit 5; +--------+---------+ |. emp_no | Wynagrodzenie | +--------+---------+| 109334 | 2553036 | |. 43624 | 2492873 | |. 66793 | 2383923 | |. 237542 | 2381119 | |. 47978 | 2374024 | +--------+---------+5 wierszy w zestawie (2.22 sekundy) 

W powyższym zapytaniu pensje są pogrupowane przez pracownika, a następnie suma jest wykonywana.

Łączenie tego wszystkiego

Na szczęście nie musimy prowadzić zapytania po zapytaniu, aby stworzyć raport. Zamiast tego możemy utworzyć skrypt z serią poleceń SQL, aby zwrócić wszystkie niezbędne zestawy wyników.

Po wykonaniu skryptu zwróci wymagane informacje bez dalszej interwencji z naszej strony. Na przykład utwórzmy plik o nazwie Maxminavg.SQL W obecnym katalogu roboczym z następującymi treściami:

--Wybierz bazę danych Użyj pracowników; -Calculate maksymalne pensje Wybierz Concat (Last_name, ',', First_name) jako nazwa, maks. (B.wynagrodzenie) jako „maks. wynagrodzenie „od pracowników A pensje B na a.emp_no = b.emp_no gdzie a.emp_no w grupie (10001, 10002, 10003) według a.emp_no; -Calculate Minimalne wynagrodzenie Wybierz Concat (Last_name, ',', First_name) jako nazwa, min (b.wynagrodzenie) jako „min. wynagrodzenie „od pracowników A pensje B na a.emp_no = b.emp_no gdzie a.emp_no w grupie (10001, 10002, 10003) według a.emp_no; -Calculate średnie, okrągłe do 2 miejsc dziesiętnych Wybierz Concat (Last_name, ',', First_name) jako nazwa, okrągła (AVG (b (b.wynagrodzenie), 2) jako „AVG. wynagrodzenie „od pracowników A pensje B na a.emp_no = b.emp_no gdzie a.emp_no w grupie (10001, 10002, 10003) według a.emp_no; 

Linie zaczynające się od dwóch kreśników są ignorowane, a poszczególne zapytania są wykonywane jeden po drugim. Możemy wykonać ten skrypt albo z wiersza polecenia Linux:

# mysql -u Empadmin -p < maxminavg.sql Wprowadź hasło:  Nazwa max. Wynagrodzenie Facello, Georgi 88958 Simmel, Bezalel 72527 Bamford, Parto 43699 Nazwa Min. wynagrodzenie Facello, Georgi 60117 Simmel, Bezalel 65828 Bamford, Parto 40006 Nazwa AVG. wynagrodzenie Facello, Georgi 75388.94 Simmel, Bezalel 68854.50 Bamford, Parto 43030.29 

lub z monitu MariaDB:

# mysql -u Empadmin -p Wprowadź hasło: Witamy w monitor Mariadb. Polecenia kończą się; lub \ g. Twój identyfikator połączenia Mariadb to 4 wersja serwerowa: 10.1.14-Mariadb Mariadb Server Copyright (C) 2000, 2016, Oracle, Mariadb Corporation AB i inni. Typ „Pomoc”; lub „\ h”, aby uzyskać pomoc. Typ „\ c”, aby wyczyścić bieżącą instrukcję wejściową. Mariadb [(brak)]> Źródło Maxminavg.SQL Czytanie informacji o tabeli dla zakończenia nazwy tabeli i kolumn 

Skrypt mysql do uruchamiania poleceń SQL

Streszczenie

W tym artykule wyjaśniliśmy, jak korzystać z kilku funkcji MariaDB w celu udoskonalenia zestawów wyników zwróconych przez WYBIERAĆ sprawozdania. Po ich zdefiniowaniu można włożyć wiele indywidualnych zapytań w skrypcie, aby łatwiej go wykonać i zmniejszyć ryzyko błędu ludzkiego.

Czy masz jakieś pytania lub sugestie dotyczące tego artykułu? Zapraszam do upuszczenia nam notatki za pomocą poniższego formularza komentarza. Oczekujemy na kontakt zwrotny!