Leer hoe u verschillende functies van MySQL en MariaDB kunt gebruiken - Deel 2
Dit is het tweede deel van een serie van twee artikelen over de essentie van MariaDB/MySQL-opdrachten. Raadpleeg ons vorige artikel over dit onderwerp voordat u verdergaat.
- Leer de basisbeginselen van MySQL/MariaDB voor beginners – deel 1
In dit tweede deel van de MySQL/MariaDB-beginnersserie leggen we uit hoe je het aantal rijen kunt beperken dat wordt geretourneerd door een SELECT-query, en hoe je de resultatenset kunt rangschikken op basis van een bepaalde voorwaarde.
Daarnaast leren we hoe we de records kunnen groeperen en eenvoudige wiskundige manipulaties op numerieke velden kunnen uitvoeren. Dit alles zal ons helpen een SQL-script te maken dat we kunnen gebruiken om nuttige rapporten te produceren.
Vereisten
Volg deze stappen om te beginnen:
1. Download de voorbeelddatabase employees, die zes tabellen bevat met in totaal 4 miljoen records.
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. Voer de MariaDB-prompt in en maak een database met de naam werknemers:
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. Importeer het als volgt in uw MariaDB-server:
MariaDB [(none)]> source employees.sql
Wacht 1-2 minuten totdat de voorbeelddatabase is geladen (houd er rekening mee dat we het hier over 4 miljoen records hebben!).
4. Controleer of de database correct is geïmporteerd door de tabellen weer te geven:
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. Maak een speciaal account aan voor gebruik met de werknemers database (kies gerust een andere accountnaam en wachtwoord):
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
Meld u nu aan als empadmin-gebruiker bij de Mariadb-prompt.
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
Zorg ervoor dat alle stappen in de bovenstaande afbeelding zijn voltooid voordat u verdergaat.
Het ordenen en beperken van het aantal rijen in de resultatenset
De salaristabel bevat alle inkomens van elke werknemer met begin- en einddatum. Mogelijk willen we de salarissen van emp_no=10001 in de loop van de tijd bekijken. Dit zal helpen de volgende vragen te beantwoorden:
- Heeft hij/zij loonsverhoging gekregen?
- Zo ja, wanneer?
Voer de volgende query uit om erachter te komen:
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)
Wat als we nu de laatste 5 loonsverhogingen moeten bekijken? We kunnen ORDER BY from_date DESC. Het trefwoord DESC geeft aan dat we de resultatenset in aflopende volgorde willen sorteren.
Bovendien kunnen we met LIMIT 5 alleen de bovenste 5 rijen in de resultatenset retourneren:
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)
Je kunt ORDER BY ook gebruiken met meerdere velden. Met de volgende query wordt de resultatenset bijvoorbeeld geordend op basis van de geboortedatum van de werknemer in oplopende vorm (de standaardinstelling) en vervolgens op achternaam in alfabetisch aflopende vorm:
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)
Meer informatie over LIMIT kunt u hier bekijken.
Records groeperen/MAX, MIN, AVG en RONDE
Zoals we eerder vermeldden, bevat de tabel salarissen de inkomens van elke werknemer in de loop van de tijd. Naast LIMIT kunnen we de trefwoorden MAX en MIN gebruiken om te bepalen wanneer het maximale en minimale aantal werknemers is aangenomen:
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)
Kunt u, op basis van de bovenstaande resultatensets, raden wat de onderstaande zoekopdracht zal opleveren?
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)
Als u ermee akkoord gaat dat het gemiddelde salaris (zoals gespecificeerd door AVG) in de loop van de tijd wordt geretourneerd, afgerond op 2 decimalen (zoals aangegeven door ROND), heeft u gelijk.
Als we de som van de salarissen gegroepeerd per werknemer willen bekijken en de top 5 willen retourneren, kunnen we de volgende zoekopdracht gebruiken:
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)
In de bovenstaande zoekopdracht worden de salarissen gegroepeerd per werknemer en vervolgens wordt de som uitgevoerd.
Alles samenbrengen
Gelukkig hoeven we niet de ene na de andere zoekopdracht uit te voeren om een rapport te produceren. In plaats daarvan kunnen we een script maken met een reeks SQL-opdrachten om alle benodigde resultatensets terug te geven.
Zodra we het script uitvoeren, retourneert het de benodigde informatie zonder verdere tussenkomst van onze kant. Laten we bijvoorbeeld een bestand maken met de naam maxminavg.sql in de huidige werkmap met de volgende inhoud:
--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;
Regels die met twee streepjes beginnen, worden genegeerd en de afzonderlijke zoekopdrachten worden na elkaar uitgevoerd. We kunnen dit script uitvoeren vanaf de Linux-opdrachtregel:
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
of vanaf de MariaDB-prompt:
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
Samenvatting
In dit artikel hebben we uitgelegd hoe u verschillende MariaDB-functies kunt gebruiken om de resultatensets te verfijnen die worden geretourneerd door SELECT-instructies. Nadat ze zijn gedefinieerd, kunnen meerdere individuele queries in een script worden ingevoegd om het eenvoudiger uit te voeren en het risico op menselijke fouten te verkleinen.
Heeft u vragen of suggesties over dit artikel? Laat het ons gerust weten via het onderstaande reactieformulier. Wij horen graag van u!