Erfahren Sie, wie Sie verschiedene Funktionen von MySQL und MariaDB nutzen – Teil 2

Dies ist der zweite Teil einer aus zwei Artikeln bestehenden Serie über die Grundlagen von MariaDB/MySQL-Befehlen. Bitte lesen Sie unseren vorherigen Artikel zu diesem Thema, bevor Sie fortfahren.

Lernen Sie MySQL/MariaDB-Grundlagen für Anfänger – Teil 1

In diesem zweiten Teil der MySQL/MariaDB-Einsteigerserie erklären wir, wie man die Anzahl der von einer SELECT-Abfrage zurückgegebenen Zeilen begrenzt und wie man die Ergebnismenge basierend auf einer bestimmten Bedingung anordnet.

Darüber hinaus lernen wir, wie man die Datensätze gruppiert und grundlegende mathematische Manipulationen an numerischen Feldern durchführt. All dies wird uns helfen, ein SQL-Skript zu erstellen, mit dem wir nützliche Berichte erstellen können.

Voraussetzungen

Führen Sie zunächst die folgenden Schritte aus:

1. Laden Sie die Beispieldatenbank employees herunter, die sechs Tabellen mit insgesamt 4 Millionen Datensätzen enthält.

# 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. Geben Sie die MariaDB-Eingabeaufforderung ein und erstellen Sie eine Datenbank mit dem Namen employees:

# 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. Importieren Sie es wie folgt in Ihren MariaDB-Server:

MariaDB [(none)]> source employees.sql

Warten Sie 1–2 Minuten, bis die Beispieldatenbank geladen ist (denken Sie daran, dass es sich hier um 4 Millionen Datensätze handelt!).

4. Überprüfen Sie, ob die Datenbank korrekt importiert wurde, indem Sie ihre Tabellen auflisten:

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. Erstellen Sie ein spezielles Konto zur Verwendung mit der Mitarbeiter-Datenbank (Sie können gerne einen anderen Kontonamen und ein anderes Passwort wählen):

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

Melden Sie sich nun als empadmin-Benutzer an der Mariadb-Eingabeaufforderung an.

# 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

Stellen Sie sicher, dass alle im obigen Bild beschriebenen Schritte abgeschlossen sind, bevor Sie fortfahren.

Ordnen und Begrenzen der Anzahl der Zeilen im Ergebnissatz

Die Gehaltstabelle enthält alle Einkommen jedes Mitarbeiters mit Beginn- und Enddatum. Möglicherweise möchten wir die Gehälter von emp_no=10001 im Laufe der Zeit anzeigen. Dies hilft bei der Beantwortung der folgenden Fragen:

Hat er/sie Gehaltserhöhungen bekommen?
Wenn ja, wann?

Führen Sie die folgende Abfrage aus, um Folgendes herauszufinden:

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)

Was ist nun, wenn wir die letzten 5 Erhöhungen sehen müssen? Wir können ORDER BY from_date DESC ausführen. Das Schlüsselwort DESC gibt an, dass wir die Ergebnismenge in absteigender Reihenfolge sortieren möchten.

Darüber hinaus ermöglicht uns LIMIT 5, nur die obersten 5 Zeilen im Ergebnissatz zurückzugeben:

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)

Sie können ORDER BY auch mit mehreren Feldern verwenden. Beispielsweise ordnet die folgende Abfrage die Ergebnismenge nach dem Geburtsdatum des Mitarbeiters in aufsteigender Form (Standard) und dann nach den Nachnamen in alphabetischer absteigender Form:

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)

Weitere Informationen zu LIMIT finden Sie hier.

Gruppieren von Datensätzen/MAX, MIN, AVG und ROUND

Wie bereits erwähnt, enthält die Tabelle Gehälter die Einkommen jedes Mitarbeiters im Zeitverlauf. Neben LIMIT können wir die Schlüsselwörter MAX und MIN verwenden, um zu bestimmen, wann die maximale und minimale Anzahl von Mitarbeitern eingestellt wurde:

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)

Können Sie anhand der obigen Ergebnismengen erraten, was die folgende Abfrage zurückgeben wird?

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)

Wenn Sie damit einverstanden sind, dass das durchschnittliche (wie durch AVG angegebene) Gehalt im Zeitverlauf zurückgegeben wird, gerundet auf zwei Dezimalstellen (wie durch ROUND angegeben), haben Sie Recht.

Wenn wir die Summe der Gehälter gruppiert nach Mitarbeiter anzeigen und die obersten 5 zurückgeben möchten, können wir die folgende Abfrage verwenden:

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 der obigen Abfrage werden die Gehälter nach Mitarbeiter gruppiert und dann die Summe gebildet.

Alles zusammenbringen

Glücklicherweise müssen wir keine Abfrage nach der anderen ausführen, um einen Bericht zu erstellen. Stattdessen können wir ein Skript mit einer Reihe von SQL-Befehlen erstellen, um alle erforderlichen Ergebnismengen zurückzugeben.

Sobald wir das Skript ausführen, werden die erforderlichen Informationen ohne weiteres Eingreifen unsererseits zurückgegeben. Erstellen wir beispielsweise eine Datei mit dem Namen maxminavg.sql im aktuellen Arbeitsverzeichnis mit folgendem Inhalt:

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

Zeilen, die mit zwei Bindestrichen beginnen, werden ignoriert und die einzelnen Abfragen werden nacheinander ausgeführt. Wir können dieses Skript entweder über die Linux-Befehlszeile ausführen:

# 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

oder über die MariaDB-Eingabeaufforderung:

# 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

Zusammenfassung

In diesem Artikel haben wir erklärt, wie Sie mehrere MariaDB-Funktionen verwenden, um die von SELECT-Anweisungen zurückgegebenen Ergebnismengen zu verfeinern. Sobald sie definiert sind, können mehrere einzelne Abfragen in ein Skript eingefügt werden, um es einfacher auszuführen und das Risiko menschlicher Fehler zu verringern.