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. Die folgende Abfrage ordnet beispielsweise die Ergebnismenge basierend auf 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 auf zwei Dezimalstellen gerundet zurückgegeben wird (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.
Haben Sie Fragen oder Anregungen zu diesem Artikel? Sie können uns gerne eine Nachricht über das Kommentarformular unten hinterlassen. Wir freuen uns von Ihnen zu hören!