Wyszukiwanie w witrynie

Dowiedz się, jak korzystać z kilku funkcji MySQL i MariaDB – część 2

To druga część serii składającej się z dwóch artykułów o podstawach poleceń MariaDB/MySQL. Zanim przejdziesz dalej, zapoznaj się z naszym poprzednim artykułem na ten temat.

  1. Poznaj podstawy MySQL/MariaDB dla początkujących – część 1

W drugiej części serii dla początkujących MySQL/MariaDB wyjaśnimy, jak ograniczyć liczbę wierszy zwracanych przez zapytanie SELECT i jak uporządkować zestaw wyników na podstawie danego warunku.

Dodatkowo nauczymy się grupować rekordy oraz wykonywać podstawowe manipulacje matematyczne na polach numerycznych. Wszystko to pomoże nam stworzyć skrypt SQL, którego będziemy mogli użyć do tworzenia przydatnych raportów.

Warunki wstępne

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

1. Pobierz przykładową bazę danych pracowników, która zawiera sześć tabel składających się łącznie z 4 milionów rekordów.

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjf employees_db-full-1.0.6.tar.bz2
cd employees_db

2. Wpisz zachętę MariaDB i utwórz bazę danych o nazwie pracownicy:

mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)

3. Zaimportuj go na swój serwer MariaDB w następujący sposób:

MariaDB [(none)]> source employees.sql

Poczekaj 1-2 minuty, aż załaduje się przykładowa baza danych (pamiętaj, że mówimy tutaj o rekordach 4M!).

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

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Utwórz specjalne konto do korzystania z bazy danych pracowników (możesz wybrać inną nazwę konta i hasło):

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

Teraz zaloguj się jako użytkownik empadmin w wierszu Mariadb.

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Przed kontynuowaniem upewnij się, że wszystkie kroki opisane na powyższym obrazku zostały wykonane.

Porządkowanie i ograniczanie liczby wierszy w zestawie wynikowym

Tabela wynagrodzeń zawiera wszystkie dochody każdego pracownika wraz z datą rozpoczęcia i zakończenia. Możemy chcieć sprawdzić wynagrodzenia emp_no=10001 na przestrzeni czasu. Pomoże to odpowiedzieć na następujące pytania:

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

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

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | 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 rows in set (0.03 sec)

A co jeśli będziemy musieli zobaczyć 5 ostatnich podbić? Możemy zrobić ORDER BY from_date DESC. Słowo kluczowe DESC wskazuje, że chcemy posortować zestaw wyników w kolejności malejącej.

Dodatkowo LIMIT 5 pozwala nam zwrócić tylko 5 górnych wierszy w zestawie wyników:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | 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 rows in set (0.00 sec)

Możesz także użyć opcji ORDER BY z wieloma polami. Przykładowo poniższe zapytanie uporządkuje zestaw wyników na podstawie daty urodzenia pracownika w formie rosnącej (domyślnie), a następnie według nazwisk w formie alfabetycznej malejącej:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| 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 rows in set (0.31 sec)

Więcej informacji na temat LIMIT znajdziesz tutaj.

Grupowanie rekordów/MAX, MIN, AVG i ROUND

Jak wspomnieliśmy wcześniej, tabela wynagrodzenia zawiera dochody każdego pracownika w czasie. Oprócz LIMIT możemy użyć słów kluczowych MAX i MIN, aby określić, kiedy zatrudniono maksymalną i minimalną liczbę pracowników:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

Czy na podstawie powyższych zestawów wyników możesz zgadnąć, co zwróci poniższe zapytanie?

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

Jeśli zgadzasz się, że zwróci średnią (określoną przez AVG) pensję w czasie zaokrągloną do 2 miejsc po przecinku (określoną przez ROUND), masz rację.

Jeśli chcemy zobaczyć sumę wynagrodzeń pogrupowanych według pracowników i zwrócić górną 5, możemy skorzystać z następującego zapytania:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

W powyższym zapytaniu wynagrodzenia są grupowane według pracowników, a następnie przeprowadzana jest suma.

Łączenie tego wszystkiego

Na szczęście nie musimy uruchamiać zapytania po zapytaniu, aby wygenerować raport. Zamiast tego możemy utworzyć skrypt zawierający serię poleceń SQL, które zwrócą wszystkie niezbędne zestawy wyników.

Gdy wykonamy skrypt, zwróci on wymagane informacje bez dalszej interwencji z naszej strony. Na przykład utwórzmy plik o nazwie maxminavg.sql w bieżącym katalogu roboczym z następującą zawartością:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

Linie zaczynające się od dwóch myślników są ignorowane, a poszczególne zapytania wykonywane są jedno po drugim. Możemy wykonać ten skrypt z wiersza poleceń systemu Linux:

mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

lub z zachęty MariaDB:

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Streszczenie

W tym artykule wyjaśniliśmy, jak używać kilku funkcji MariaDB w celu udoskonalenia zestawów wyników zwracanych przez instrukcje SELECT. Po zdefiniowaniu można wstawić do skryptu wiele pojedynczych zapytań, aby ułatwić jego wykonanie i zmniejszyć ryzyko błędu ludzkiego.

Czy masz jakieś pytania lub sugestie dotyczące tego artykułu? Możesz zostawić nam notatkę, korzystając z poniższego formularza komentarza. Oczekujemy na kontakt zwrotny!