Leer MySQL/MariaDB voor beginners - deel 1

In dit artikel laten we zien hoe u een database (ook wel een schema genoemd) en tabellen (met gegevenstypen) maakt, en leggen we uit hoe u Data Manipulation Language (DML) uitvoert. ) bewerkingen met gegevens op een MySQL/MariaDB server.

Er wordt aangenomen dat u eerder 1) de benodigde pakketten op uw Linux-systeem hebt geïnstalleerd en 2) mysql_secure_installation hebt uitgevoerd om de beveiliging van de databaseserver te verbeteren . Als dit niet het geval is, volgt u onderstaande handleidingen om de MySQL/MariaDB-server te installeren.

Installeer de nieuwste MySQL-database in Linux-systemen
Installeer de nieuwste MariaDB-database in Linux-systemen

Kortheidshalve verwijzen we in dit artikel uitsluitend naar MariaDB, maar de hier beschreven concepten en opdrachten zijn ook van toepassing op MySQL.

Databases, tabellen en geautoriseerde gebruikers maken

Zoals u weet, kan een database in eenvoudige bewoordingen worden gedefinieerd als een georganiseerde verzameling informatie. MariaDB is met name een relationeel databasebeheersysteem (RDBMS) en gebruikt de Structure Query Language om bewerkingen op databases uit te voeren. Houd er bovendien rekening mee dat MariaDB de termen database en schema door elkaar gebruikt.

Om persistente informatie in een database op te slaan, gebruiken we tabellen waarin rijen met gegevens worden opgeslagen. Vaak zijn twee of meer tabellen op de een of andere manier met elkaar verbonden. Dat hoort bij de organisatie die het gebruik van relationele databases kenmerkt.

Een nieuwe database maken

Om een nieuwe database met de naam BooksDB aan te maken, voert u de MariaDB-prompt in met de volgende opdracht (u wordt gevraagd het wachtwoord voor de root-MariaDB-gebruiker in te voeren):

[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)]>

Nadat de database is gemaakt, moeten we er minimaal twee tabellen in maken. Maar laten we eerst het concept van gegevenstypen verkennen.

Introductie van MariaDB-gegevenstypen

Zoals we eerder hebben uitgelegd, zijn tabellen databaseobjecten waarin we persistente informatie bewaren. Elke tabel bestaat uit twee of meer velden (ook wel kolommen genoemd) van een bepaald gegevenstype (het type informatie) dat in zo'n veld kan worden opgeslagen.

De meest voorkomende gegevenstypen in MariaDB zijn de volgende (u kunt de volledige lijst raadplegen in de officiële online documentatie van MariaDB):

Numeriek:

BOOLEAN beschouwt 0 als onwaar en alle andere waarden als waar.
TINYINT bestrijkt, indien gebruikt met SIGNED, het bereik van -128 tot 127, terwijl het UNSIGNED-bereik 0 tot 255 is.
SMALLINT bestrijkt, indien gebruikt met SIGNED, het bereik van -32768 tot 32767. Het UNSIGNED-bereik is 0 tot 65535.
INT bestrijkt, indien gebruikt met UNSIGNED, het bereik van 0 tot 4294967295, en anders -2147483648 tot 2147483647.

Opmerking: In TINYINT, SMALLINT en INT wordt uitgegaan van de standaard SIGNED.

DOUBLE(M, D), waarbij M het totale aantal cijfers is en D het aantal cijfers achter de komma is, vertegenwoordigt een drijvende-kommagetal met dubbele precisie. Als UNSIGNED is opgegeven, zijn negatieve waarden niet toegestaan.

Snaar :

VARCHAR(M) vertegenwoordigt een tekenreeks met variabele lengte waarbij M de maximaal toegestane kolomlengte in bytes is (65.535 in theorie). In de meeste gevallen is het aantal bytes identiek aan het aantal tekens, met uitzondering van enkele tekens die wel 3 bytes in beslag kunnen nemen. De Spaanse letter ñ vertegenwoordigt bijvoorbeeld één teken, maar neemt twee bytes in beslag.
TEXT(M) vertegenwoordigt een kolom met een maximale lengte van 65.535 tekens. Echter, zoals bij VARCHAR(M) het geval is, wordt de werkelijke maximale lengte verminderd als tekens van meerdere bytes worden opgeslagen. Als M is opgegeven, wordt de kolom gemaakt als het kleinste type dat een dergelijk aantal tekens kan opslaan.
MEDIUMTEXT(M) en LONGTEXT(M) zijn vergelijkbaar met TEXT(M), alleen is de maximaal toegestane lengte 16.777.215 en 4.294.967.295 tekens, respectievelijk.

Datum en tijd:

DATUM vertegenwoordigt de datum in het formaat JJJJ-MM-DD.
TIJD vertegenwoordigt de tijd in UU:MM:SS.sss-formaat (uur, minuten, seconden en milliseconden).
DATETIME is de combinatie van DATE en TIME in de notatie JJJJ-MM-DD HH:MM:SS.
TIMESTAMP wordt gebruikt om het moment te definiëren waarop een rij werd toegevoegd of bijgewerkt.

Nadat u deze gegevenstypen heeft bekeken, kunt u beter bepalen welk gegevenstype u aan een bepaalde kolom in een tabel moet toewijzen.

De naam van een persoon past bijvoorbeeld gemakkelijk in een VARCHAR(50), terwijl een blogpost het type TEXT nodig heeft (kies M als volgens uw specifieke behoeften).

Tabellen maken met primaire en externe sleutels

Voordat we dieper ingaan op het maken van tabellen, zijn er twee fundamentele concepten over relationele databases die we moeten doornemen: primaire en externe sleutels.

Een primaire sleutel bevat een waarde die elke rij of record in de tabel op unieke wijze identificeert. Aan de andere kant wordt een externe sleutel gebruikt om een koppeling te maken tussen de gegevens in twee tabellen, en om de gegevens te beheren die kunnen worden opgeslagen in de tabel waarin de externe sleutel zich bevindt. Zowel primaire als externe sleutels zijn over het algemeen INT's.

Laten we ter illustratie de BookstoreDB gebruiken en als volgt twee tabellen maken met de namen AuthorsTBL en BooksTBL. De beperking NOT NULL geeft aan dat het bijbehorende veld een andere waarde dan NULL vereist.

Bovendien wordt AUTO_INCREMENT gebruikt om de waarde van INT primaire sleutelkolommen met één te verhogen wanneer een nieuw record in de tabel wordt ingevoegd.

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

Nu kunnen we doorgaan en beginnen met het invoegen van records in AuthorsTBL en BooksTBL.

Rijen selecteren, invoegen, bijwerken en verwijderen

We vullen eerst de tabel AuthorsTBL in. Waarom? Omdat we waarden nodig hebben voor AuthorID voordat we records in de BooksTBL invoegen.

Voer de volgende query uit vanaf uw MariaDB-prompt:

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

Daarna selecteren we alle records uit AuteursTBL. Houd er rekening mee dat we voor elke record de AuthorID nodig hebben om de INSERT-query voor BooksTBL te maken.

Als u één record tegelijk wilt ophalen, kunt u een WHERE-clausule gebruiken om een voorwaarde aan te geven waaraan een rij moet voldoen om te worden geretourneerd. Bijvoorbeeld,

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

Als alternatief kunt u alle records tegelijkertijd selecteren:

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

Laten we nu de INSERT-query maken voor BooksTBL, waarbij we de corresponderende AuthorID gebruiken om overeen te komen met de auteur van elk boek. Een waarde van 1 in BookIsAvailable geeft aan dat het boek op voorraad is, anders 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

Op dit punt zullen we een SELECT uitvoeren om de records in BooksTBL te bekijken. Laten we dan de prijs van “The Alchemist ” van Paulo Coelho UPDATE en die specifieke plaat opnieuw SELECTEREN.

Merk op dat het veld BookLastUpdated nu een andere waarde toont. Zoals we eerder hebben uitgelegd, toont een veld TIMESTAMP de waarde op het moment dat de record werd ingevoegd of voor het laatst werd gewijzigd.

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

Hoewel we dit hier niet doen, kunt u een record ook verwijderen als u het niet meer gebruikt. Stel dat we bijvoorbeeld “The Alchemist” uit BooksTBL willen verwijderen.

Om dit te doen, gebruiken we de DELETE-instructie als volgt:

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

Net als in het geval van UPDATE is het een goed idee om eerst een SELECT uit te voeren om de record(s) te bekijken die mogelijk worden beïnvloed door de VERWIJDEREN.

Vergeet ook niet om de WHERE-clausule en een voorwaarde (BookID=6) toe te voegen om het specifieke record te selecteren dat u wilt verwijderen. Anders loopt u het risico dat u alle rijen in de tabel verwijdert!

Als u twee (of meer) velden wilt samenvoegen, kunt u de CONCAT-instructie gebruiken. Laten we bijvoorbeeld zeggen dat we een resultatenset willen retourneren die bestaat uit één veld met de boeknaam en auteur in de vorm van 'De Alchemist (Paulo Coelho)' en een andere kolom met de prijs.

Hiervoor is een JOIN vereist tussen AuthorsTBL en BooksTBL op het gemeenschappelijke veld dat door beide tabellen wordt gedeeld (AuthorID):

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

Zoals we kunnen zien, stelt CONCAT ons in staat om meerdere tekenreeksexpressies samen te voegen, gescheiden door komma's. U zult ook merken dat we de alias Beschrijving hebben gekozen om de resultatenset van de aaneenschakeling weer te geven.

De uitvoer van de bovenstaande query wordt weergegeven in de onderstaande afbeelding:

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)

Maak een gebruiker aan voor toegang tot de BookstoreDB-database

Het is een slecht idee om root te gebruiken om alle DML-bewerkingen in een database uit te voeren. Om dit te voorkomen, kunnen we een nieuw MariaDB-gebruikersaccount aanmaken (we noemen het bookstoreuser) en alle benodigde rechten toewijzen aan BookstoreDB:

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)

Het hebben van een speciale, afzonderlijke gebruiker voor elke database voorkomt schade aan de gehele database als een enkel account in gevaar komt.

Extra MySQL-tips

Om de MariaDB-prompt op te helderen, typt u de volgende opdracht en drukt u op Enter:

MariaDB [BookstoreDB]> \! clear

Om de configuratie van een bepaalde tabel te inspecteren, doet u het volgende:

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

Bijvoorbeeld,

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)

Een snelle inspectie laat zien dat het veld BookIsAvailable NULL waarden toestaat. Omdat we dat niet willen toestaan, zullen we de tabel als volgt WIJZIGEN:

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

(Laat de kolommen gerust nog een keer zien – het gemarkeerde JA in de bovenstaande afbeelding zou nu een NEE moeten zijn).

Om ten slotte alle databases op uw server te bekijken, doet u het volgende:

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)

De volgende afbeelding toont het resultaat van de bovenstaande opdracht na toegang tot de MariaDB-prompt als bookstoreuser (merk op dat dit account geen andere databases kan “zien” dan BookstoreDB en information_schema (beschikbaar voor alle gebruikers):

Samenvatting

In dit artikel hebben we uitgelegd hoe u DML-bewerkingen uitvoert en hoe u een database, tabellen en speciale gebruikers maakt in een MariaDB-database. Daarnaast deelden we een paar tips die uw leven als systeem-/databasebeheerder gemakkelijker kunnen maken.

MySQL-databasebeheer Deel – 1
MySQL-databasebeheer Deel – 2
MySQL-prestaties afstemmen en optimaliseren – Deel 3

Mocht je naar aanleiding van dit artikel vragen hebben, laat het ons gerust weten! Gebruik gerust het onderstaande reactieformulier om ons te bereiken.