Website-Suche

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


In diesem Artikel zeigen wir, wie man eine Datenbank (auch als Schema bezeichnet) und Tabellen (mit Datentypen) erstellt und erklärt, wie man Data Manipulation Language (DML) ausführt ) Operationen mit Daten auf einem MySQL/MariaDB-Server.

Es wird davon ausgegangen, dass Sie zuvor 1) die erforderlichen Pakete auf Ihrem Linux-System installiert und 2) mysql_secure_installation ausgeführt haben, um die Sicherheit des Datenbankservers zu verbessern . Wenn nicht, befolgen Sie die nachstehenden Anleitungen zur Installation des MySQL/MariaDB-Servers.

  1. Installieren Sie die neueste MySQL-Datenbank in Linux-Systemen
  2. Installieren Sie die neueste MariaDB-Datenbank in Linux-Systemen

Der Kürze halber beziehen wir uns in diesem Artikel ausschließlich auf MariaDB, aber die hier beschriebenen Konzepte und Befehle gelten auch für MySQL.

Erstellen von Datenbanken, Tabellen und autorisierten Benutzern

Wie Sie wissen, kann eine Datenbank in einfachen Worten als eine organisierte Sammlung von Informationen definiert werden. Insbesondere ist MariaDB ein relationales Datenbankverwaltungssystem (RDBMS) und verwendet die Structure Query Language, um Operationen an Datenbanken durchzuführen. Beachten Sie außerdem, dass MariaDB die Begriffe Datenbank und Schema synonym verwendet.

Um persistente Informationen in einer Datenbank zu speichern, verwenden wir Tabellen, die Datenzeilen speichern. Oft stehen zwei oder mehr Tabellen in irgendeiner Weise miteinander in Beziehung. Dies ist Teil der Organisation, die den Einsatz relationaler Datenbanken kennzeichnet.

Erstellen einer neuen Datenbank

Um eine neue Datenbank mit dem Namen BooksDB zu erstellen, geben Sie die MariaDB-Eingabeaufforderung mit dem folgenden Befehl ein (Sie werden aufgefordert, das Passwort für den Root-MariaDB-Benutzer einzugeben):

[root@TecMint ~]# 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 BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 

Sobald die Datenbank erstellt wurde, müssen wir darauf mindestens zwei Tabellen erstellen. Aber lassen Sie uns zunächst das Konzept der Datentypen untersuchen.

Einführung in MariaDB-Datentypen

Wie wir bereits erklärt haben, sind Tabellen Datenbankobjekte, in denen wir persistente Informationen speichern. Jede Tabelle besteht aus zwei oder mehr Feldern (auch bekannt als Spalten) eines bestimmten Datentyps (der Art von Informationen), die in diesem Feld gespeichert werden können.

Die häufigsten Datentypen in MariaDB sind die folgenden (die vollständige Liste finden Sie in der offiziellen MariaDB-Onlinedokumentation):

Numerisch:
  1. BOOLEAN betrachtet 0 als falsch und alle anderen Werte als wahr.
  2. TINYINT deckt bei Verwendung mit SIGNED den Bereich von -128 bis 127 ab, während der UNSIGNED-Bereich 0 bis 255 beträgt.
  3. SMALLINT deckt bei Verwendung mit SIGNED den Bereich von -32768 bis 32767 ab. Der UNSIGNED-Bereich liegt zwischen 0 und 65535.
  4. INT deckt bei Verwendung mit UNSIGNED den Bereich von 0 bis 4294967295 ab, andernfalls -2147483648 bis 2147483647.

Hinweis: In TINYINT, SMALLINT und INT wird der Standardwert SIGNED angenommen.

DOUBLE(M, D), wobei M die Gesamtzahl der Ziffern und D die Anzahl der Ziffern nach dem Dezimalpunkt ist, stellt a dar Gleitkommazahl mit doppelter Genauigkeit. Wenn UNSIGNED angegeben ist, sind negative Werte nicht zulässig.

Zeichenfolge:
  1. VARCHAR(M) stellt eine Zeichenfolge variabler Länge dar, wobei M die maximal zulässige Spaltenlänge in Bytes ist (theoretisch 65.535). In den meisten Fällen ist die Anzahl der Bytes identisch mit der Anzahl der Zeichen, mit Ausnahme einiger Zeichen, die bis zu 3 Bytes belegen können. Beispielsweise stellt der spanische Buchstabe ñ ein Zeichen dar, belegt aber 2 Bytes.
  2. TEXT(M) stellt eine Spalte mit einer maximalen Länge von 65.535 Zeichen dar. Allerdings verringert sich, wie bei VARCHAR(M), die tatsächliche maximale Länge, wenn Mehrbyte-Zeichen gespeichert werden. Wenn M angegeben ist, wird die Spalte als kleinster Typ erstellt, der eine solche Anzahl von Zeichen speichern kann.
  3. MEDIUMTEXT(M) und LONGTEXT(M) ähneln TEXT(M), nur dass die maximal zulässigen Längen 16.777.215 und 4.294.967.295 Zeichen betragen. jeweils.
Datum (und Uhrzeit:
  1. DATE stellt das Datum im Format JJJJ-MM-TT dar.
  2. TIME stellt die Zeit im Format HH:MM:SS.sss dar (Stunde, Minuten, Sekunden und Millisekunden).
  3. DATETIME ist die Kombination aus DATE und TIME im Format YYYY-MM-DD HH:MM:SS.
  4. TIMESTAMP wird verwendet, um den Zeitpunkt zu definieren, zu dem eine Zeile hinzugefügt oder aktualisiert wurde.

Nachdem Sie diese Datentypen überprüft haben, können Sie besser bestimmen, welchen Datentyp Sie einer bestimmten Spalte in einer Tabelle zuweisen müssen.

Beispielsweise kann der Name einer Person problemlos in ein VARCHAR(50) passen, während ein Blog-Beitrag einen TEXT-Typ benötigt (wählen Sie M als). je nach Ihren spezifischen Bedürfnissen).

Erstellen von Tabellen mit Primär- und Fremdschlüsseln

Bevor wir uns mit der Erstellung von Tabellen befassen, müssen wir zwei grundlegende Konzepte relationaler Datenbanken überprüfen: Primär- und Fremdschlüssel.

Ein Primärschlüssel enthält einen Wert, der jede Zeile oder jeden Datensatz in der Tabelle eindeutig identifiziert. Andererseits wird ein Fremdschlüssel verwendet, um eine Verknüpfung zwischen den Daten in zwei Tabellen herzustellen und um die Daten zu steuern, die in der Tabelle gespeichert werden können, in der sich der Fremdschlüssel befindet. Sowohl Primär- als auch Fremdschlüssel sind im Allgemeinen INTs.

Zur Veranschaulichung verwenden wir die BookstoreDB und erstellen wie folgt zwei Tabellen mit den Namen AuthorsTBL und BooksTBL. Die Einschränkung NOT NULL gibt an, dass das zugehörige Feld einen anderen Wert als NULL erfordert.

Außerdem wird AUTO_INCREMENT verwendet, um den Wert der INT-Primärschlüsselspalten um eins zu erhöhen, wenn ein neuer Datensatz in die Tabelle eingefügt wird.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> 

Jetzt können wir mit dem Einfügen von Datensätzen in AuthorsTBL und BooksTBL beginnen.

Auswählen, Einfügen, Aktualisieren und Löschen von Zeilen

Zuerst füllen wir die Tabelle AuthorsTBL. Warum? Weil wir Werte für AuthorID benötigen, bevor wir Datensätze in die BooksTBL einfügen können.

Führen Sie die folgende Abfrage über Ihre MariaDB-Eingabeaufforderung aus:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Danach wählen wir alle Datensätze aus AuthorsTBL aus. Denken Sie daran, dass wir die AuthorID für jeden Datensatz benötigen, um die INSERT-Abfrage für BooksTBL zu erstellen.

Wenn Sie jeweils einen Datensatz abrufen möchten, können Sie eine WHERE-Klausel verwenden, um eine Bedingung anzugeben, die eine Zeile erfüllen muss, um zurückgegeben zu werden. Zum Beispiel,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternativ können Sie alle Datensätze gleichzeitig auswählen:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Jetzt erstellen wir die INSERT-Abfrage für BooksTBL und verwenden dabei die entsprechende AuthorID, um den Autor jedes Buchs abzugleichen. Ein Wert von 1 in BookIsAvailable zeigt an, dass das Buch auf Lager ist, andernfalls 0:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

An dieser Stelle führen wir eine SELECT-Operation aus, um die Datensätze in BooksTBL anzuzeigen. Dann AKTUALISIEREN wir den Preis für „The Alchemist von Paulo Coelho und AUSWÄHLEN Sie dieses spezifische Album erneut.

Beachten Sie, dass das Feld BookLastUpdated jetzt einen anderen Wert anzeigt. Wie bereits erläutert, zeigt ein Feld TIMESTAMP den Wert an, als der Datensatz eingefügt oder zuletzt geändert wurde.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> 

Obwohl wir dies hier nicht tun, können Sie einen Datensatz auch löschen, wenn er nicht mehr verwendet wird. Angenommen, wir möchten „The Alchemist“ aus BooksTBL löschen.

Dazu verwenden wir die DELETE-Anweisung wie folgt:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Wie im Fall von UPDATE ist es eine gute Idee, zuerst ein SELECT durchzuführen, um die Datensätze anzuzeigen, die möglicherweise von betroffen sind LÖSCHEN.

Vergessen Sie außerdem nicht, die WHERE-Klausel und eine Bedingung (BookID=6) hinzuzufügen, um den spezifischen Datensatz auszuwählen, der entfernt werden soll. Andernfalls besteht die Gefahr, dass alle Zeilen der Tabelle gelöscht werden!

Wenn Sie zwei (oder mehr) Felder verketten möchten, können Sie die CONCAT-Anweisung verwenden. Nehmen wir zum Beispiel an, wir möchten eine Ergebnismenge zurückgeben, die aus einem Feld mit dem Buchnamen und dem Autor in der Form „Der Alchemist (Paulo Coelho) und einer weiteren Spalte mit dem Preis besteht.

Dies erfordert einen JOIN zwischen AuthorsTBL und BooksTBL für das gemeinsame Feld beider Tabellen (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Wie wir sehen können, können wir mit CONCAT mehrere durch Kommas getrennte Zeichenfolgenausdrücke verbinden. Sie werden auch bemerkt haben, dass wir den Alias Description gewählt haben, um die Ergebnismenge der Verkettung darzustellen.

Die Ausgabe der obigen Abfrage ist im folgenden Bild dargestellt:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Erstellen Sie einen Benutzer für den Zugriff auf die BookstoreDB-Datenbank

Es ist keine gute Idee, root zum Ausführen aller DML-Vorgänge in einer Datenbank zu verwenden. Um dies zu vermeiden, können wir ein neues MariaDB-Benutzerkonto erstellen (wir nennen es bookstoreuser) und alle erforderlichen Berechtigungen für BookstoreDB zuweisen:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)

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

Durch einen dedizierten, separaten Benutzer für jede Datenbank werden Schäden an der gesamten Datenbank vermieden, falls ein einzelnes Konto kompromittiert wird.

Zusätzliche MySQL-Tipps

Um die MariaDB-Eingabeaufforderung zu löschen, geben Sie den folgenden Befehl ein und drücken Sie die Eingabetaste:

MariaDB [BookstoreDB]> \! clear

Um die Konfiguration einer bestimmten Tabelle zu überprüfen, gehen Sie wie folgt vor:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

Zum Beispiel,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

Eine kurze Überprüfung zeigt, dass das Feld BookIsAvailable NULL-Werte zulässt. Da wir das nicht zulassen möchten, ALTER wir die Tabelle wie folgt:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Sie können die Spalten gerne noch einmal anzeigen – das hervorgehobene JA im obigen Bild sollte jetzt ein NEIN sein.)

Um schließlich alle Datenbanken auf Ihrem Server anzuzeigen, gehen Sie wie folgt vor:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
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 [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Das folgende Bild zeigt das Ergebnis des obigen Befehls nach dem Zugriff auf die MariaDB-Eingabeaufforderung als bookstoreuser (beachten Sie, dass dieses Konto keine anderen Datenbanken als BookstoreDB und < „sehen“ kann).information_schema (für alle Benutzer verfügbar):

Zusammenfassung

In diesem Artikel haben wir erklärt, wie man DML-Vorgänge ausführt und wie man eine Datenbank, Tabellen und dedizierte Benutzer in einer MariaDB-Datenbank erstellt. Darüber hinaus haben wir einige Tipps gegeben, die Ihnen das Leben als System-/Datenbankadministrator erleichtern können.

  1. MySQL-Datenbankverwaltungsteil – 1
  2. MySQL-Datenbankverwaltung Teil – 2
  3. MySQL-Leistungsoptimierung und -optimierung – Teil 3

Wenn Sie Fragen zu diesem Artikel haben, zögern Sie nicht, uns diese mitzuteilen! Nutzen Sie gerne das Kommentarformular unten, um uns zu erreichen.