Weboldal keresés

Ismerje meg a MySQL és a MariaDB számos funkciójának használatát – 2. rész

Ez a második része egy 2 cikkből álló sorozatnak, amely a MariaDB/MySQL parancsok alapvető tulajdonságairól szól. Mielőtt továbblépne, olvassa el a témával kapcsolatos korábbi cikkünket.

  1. Ismerje meg a MySQL/MariaDB alapjait kezdőknek – 1. rész

A MySQL/MariaDB kezdő sorozatának e második részében elmagyarázzuk, hogyan korlátozható a SELECT lekérdezés által visszaadott sorok száma, és hogyan lehet az eredménykészletet egy adott feltétel alapján rendezni.

Ezenkívül megtanuljuk, hogyan csoportosítsuk a rekordokat, és hogyan végezzünk alapvető matematikai manipulációkat a numerikus mezőkön. Mindez segít egy SQL-szkript létrehozásában, amellyel hasznos jelentéseket készíthetünk.

Előfeltételek

A kezdéshez kövesse az alábbi lépéseket:

1. Töltse le a alkalmazotti mintaadatbázist, amely hat táblát tartalmaz, amelyek összesen 4 millió rekordot tartalmaznak.

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. Írja be a MariaDB parancsot, és hozzon létre egy munkavállalók nevű adatbázist:

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. Importálja a MariaDB-kiszolgálóra a következők szerint:

MariaDB [(none)]> source employees.sql

Várjon 1-2 percet, amíg a mintaadatbázis betöltődik (ne feledje, hogy itt 4 millió rekordról beszélünk!).

4. A táblák felsorolásával ellenőrizze, hogy az adatbázist megfelelően importálta-e:

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. Hozzon létre egy speciális fiókot az alkalmazotti adatbázishoz (bátran válasszon másik fióknevet és jelszót):

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

Most jelentkezzen be empadmin felhasználóként a Mariadb promptba.

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

A folytatás előtt győződjön meg arról, hogy a fenti képen vázolt összes lépést végrehajtotta.

A sorok számának rendezése és korlátozása az eredményhalmazban

A fizetési táblázat tartalmazza az egyes alkalmazottak összes jövedelmét kezdő és befejező dátummal. Előfordulhat, hogy idővel megtekinthetjük emp_no=10001 fizetését. Ez segít megválaszolni a következő kérdéseket:

  1. Kapott-e fizetésemelést?
  2. Ha igen, mikor?

Hajtsa végre a következő lekérdezést, hogy megtudja:

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)

Most mi van, ha meg kell néznünk a legutóbbi 5 emelést? Megtehetjük a ORDER BY from_date DESC lehetőséget. A DESC kulcsszó azt jelzi, hogy az eredményhalmazt csökkenő sorrendbe szeretnénk rendezni.

Ezenkívül az LIMIT 5 lehetővé teszi, hogy csak a legfelső 5 sort adjuk vissza az eredménykészletben:

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)

A ORDER BY parancsot több mezővel is használhatja. Például a következő lekérdezés az eredménykészletet a munkavállaló születési dátuma alapján rendezi növekvő formában (az alapértelmezett), majd a vezetéknevek szerint alfabetikusan csökkenő formában:

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)

Itt további információkat tekinthet meg a LIMIT-ről.

Rekordok csoportosítása/MAX, MIN, AVG és ROUND

Ahogy korábban említettük, a fizetések táblázat tartalmazza az egyes alkalmazottak jövedelmét az idő függvényében. A LIMIT mellett a MAX és a MIN kulcsszavakat is használhatjuk annak meghatározására, hogy mikor vették fel a maximális és minimális számú alkalmazottat:

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)

A fenti eredményhalmazok alapján kitalálhatja, hogy az alábbi lekérdezés mit fog visszaadni?

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)

Ha beleegyezik abba, hogy az átlagos (az AVG által meghatározott) fizetést adja vissza az idő múlásával 2 tizedesjegyre kerekítve (a ROUND szerint), akkor igaza van.

Ha meg akarjuk tekinteni a fizetések összegét munkavállalónként csoportosítva, és a legfelső 5et szeretnénk visszaadni, akkor a következő lekérdezést használhatjuk:

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)

A fenti lekérdezésben a fizetések alkalmazottankénti csoportosításra kerülnek, majd összegzésre kerül sor.

Összehozva mindezt

Szerencsére nem kell lekérdezést lekérdezés után futtatnunk a jelentés elkészítéséhez. Ehelyett létrehozhatunk egy szkriptet SQL-parancsok sorozatával, hogy visszaadja az összes szükséges eredménykészletet.

Miután végrehajtottuk a szkriptet, minden további beavatkozás nélkül visszaadja a szükséges információkat. Például hozzunk létre egy maxminavg.sql nevű fájlt az aktuális munkakönyvtárban a következő tartalommal:

--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;

A két kötőjellel kezdődő sorokat figyelmen kívül hagyja, és az egyes lekérdezéseket egymás után hajtja végre. Ezt a szkriptet akár a Linux parancssorból is végrehajthatjuk:

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

vagy a MariaDB promptból:

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

Összegzés

Ebben a cikkben elmagyaráztuk, hogyan használhatunk több MariaDB-függvényt a SELECT utasítások által visszaadott eredményhalmazok finomításához. Miután meghatározták, több egyedi lekérdezés is beilleszthető egy szkriptbe, hogy könnyebben lehessen végrehajtani, és csökkenjen az emberi hibák kockázata.

Van kérdése vagy javaslata ezzel a cikkel kapcsolatban? Nyugodtan írjon nekünk az alábbi megjegyzés űrlap segítségével. Várom válaszukat!