Recherche de site Web

Apprendre MySQL/MariaDB pour les débutants - Partie 1

Dans cet article, nous montrerons comment créer une base de données (également appelée schéma), des tables (avec des types de données) et expliquerons comment exécuter le Langage de manipulation de données (DML ) opérations avec des données sur un serveur MySQL/MariaDB.

Il est supposé que vous avez préalablement 1) installé les packages nécessaires sur votre système Linux et 2) exécuté mysql_secure_installation pour améliorer la sécurité du serveur de base de données. . Sinon, suivez les guides ci-dessous pour installer le serveur MySQL/MariaDB.

  1. Installer la dernière base de données MySQL sur les systèmes Linux
  2. Installer la dernière base de données MariaDB sur les systèmes Linux

Par souci de concision, nous ferons référence à MariaDB exclusivement tout au long de cet article, mais les concepts et commandes décrits ici s'appliquent également à MySQL.

Création de bases de données, de tables et d'utilisateurs autorisés

Comme vous le savez, une base de données peut être définie en termes simples comme une collection organisée d’informations. En particulier, MariaDB est un système de gestion de bases de données relationnelles (SGBDR) et utilise le langage de requête de structure pour effectuer des opérations sur les bases de données. De plus, gardez à l’esprit que MariaDB utilise les termes base de données et schéma de manière interchangeable.

Pour stocker des informations persistantes dans une base de données, nous utiliserons des tables qui stockent des lignes de données. Souvent, deux ou plusieurs tables seront liées les unes aux autres d’une manière ou d’une autre. Cela fait partie de l'organisation qui caractérise l'utilisation des bases de données relationnelles.

Création d'une nouvelle base de données

Pour créer une nouvelle base de données nommée BooksDB, entrez l'invite MariaDB avec la commande suivante (vous serez invité à saisir le mot de passe de l'utilisateur root MariaDB) :

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

Une fois la base de données créée, nous devons y créer au moins deux tables. Mais explorons d’abord le concept de types de données.

Présentation des types de données MariaDB

Comme nous l'avons expliqué précédemment, les tables sont des objets de base de données dans lesquels nous conserverons des informations persistantes. Chaque table se compose de deux champs ou plus (également appelés colonnes) d'un type de données donné (le type d'informations) que ce champ peut stocker.

Les types de données les plus courants dans MariaDB sont les suivants (vous pouvez consulter la liste complète dans la documentation officielle en ligne de MariaDB) :

Numérique :
  1. BOOLEAN considère 0 comme faux et toute autre valeur comme vraie.
  2. TINYINT, s'il est utilisé avec SIGNED, couvre la plage de -128 à 127, tandis que la plage UNSIGNED est de 0 à 255.
  3. SMALLINT, s'il est utilisé avec SIGNED, couvre la plage de -32 768 à 32 767. La plage UNSIGNED va de 0 à 65 535.
  4. INT, s'il est utilisé avec UNSIGNED, couvre la plage de 0 à 4294967295, et de -2147483648 à 2147483647 dans le cas contraire.

Remarque : Dans TINYINT, SMALLINT et INT, la valeur par défaut SIGNED est supposée.

DOUBLE(M, D), où M est le nombre total de chiffres et D est le nombre de chiffres après la virgule, représente un nombre à virgule flottante double précision. Si UNSIGNED est spécifié, les valeurs négatives ne sont pas autorisées.

Chaîne :
  1. VARCHAR(M) représente une chaîne de longueur variable où M est la longueur de colonne maximale autorisée en octets (65 535 en théorie). Dans la plupart des cas, le nombre d'octets est identique au nombre de caractères, à l'exception de certains caractères qui peuvent occuper jusqu'à 3 octets. Par exemple, la lettre espagnole ñ représente un caractère mais occupe 2 octets.
  2. TEXT(M) représente une colonne d'une longueur maximale de 65 535 caractères. Cependant, comme c'est le cas avec VARCHAR(M), la longueur maximale réelle est réduite si des caractères multi-octets sont stockés. Si M est spécifié, la colonne est créée comme le plus petit type pouvant stocker un tel nombre de caractères.
  3. MEDIUMTEXT(M) et LONGTEXT(M) sont similaires à TEXT(M), sauf que les longueurs maximales autorisées sont de 16 777 215 et 4 294 967 295 caractères. respectivement.
Date et l'heure:
  1. DATE représente la date au format AAAA-MM-JJ.
  2. TIME représente l'heure au format HH:MM:SS.sss (heure, minutes, secondes et millisecondes).
  3. DATETIME est la combinaison de DATE et de TIME au format AAAA-MM-JJ HH:MM:SS.
  4. TIMESTAMP est utilisé pour définir le moment où une ligne a été ajoutée ou mise à jour.

Après avoir examiné ces types de données, vous serez mieux placé pour déterminer quel type de données vous devez attribuer à une colonne donnée dans un tableau.

Par exemple, le nom d'une personne peut facilement entrer dans un VARCHAR(50), alors qu'un article de blog aura besoin d'un type TEXT (choisissez M comme selon vos besoins spécifiques).

Création de tables avec des clés primaires et étrangères

Avant de nous lancer dans la création de tables, nous devons examiner deux concepts fondamentaux concernant les bases de données relationnelles : les clés primaires et étrangères.

Une clé primaire contient une valeur qui identifie de manière unique chaque ligne ou enregistrement de la table. D'autre part, une clé étrangère est utilisée pour créer un lien entre les données de deux tables, et pour contrôler les données pouvant être stockées dans la table où se trouve la clé étrangère. Les clés primaires et étrangères sont généralement des INT.

Pour illustrer, utilisons BookstoreDB et créons deux tables nommées AuthorsTBL et BooksTBL comme suit. La contrainte NOT NULL indique que le champ associé nécessite une valeur autre que NULL.

De plus, AUTO_INCREMENT est utilisé pour augmenter d'un la valeur des colonnes de clé primaire INT lorsqu'un nouvel enregistrement est inséré dans la table.

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

Nous pouvons maintenant commencer et commencer à insérer des enregistrements dans AuthorsTBL et BooksTBL.

Sélection, insertion, mise à jour et suppression de lignes

Nous allons d'abord remplir la table AuteursTBL. Pourquoi? Parce que nous devons avoir des valeurs pour AuthorID avant d'insérer des enregistrements dans le BooksTBL.

Exécutez la requête suivante à partir de votre invite MariaDB :

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

Après cela, nous sélectionnerons tous les enregistrements de AuteursTBL. N'oubliez pas que nous aurons besoin de l'AuthorID pour chaque enregistrement afin de créer la requête INSERT pour BooksTBL.

Si vous souhaitez récupérer un enregistrement à la fois, vous pouvez utiliser une clause WHERE pour indiquer une condition qu'une ligne doit remplir pour être renvoyée. Par exemple,

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

Alternativement, vous pouvez sélectionner tous les enregistrements simultanément :

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

Créons maintenant la requête INSERT pour BooksTBL, en utilisant le AuthorID correspondant pour faire correspondre l'auteur de chaque livre. Une valeur de 1 dans BookIsAvailable indique que le livre est en stock, 0 sinon :

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

À ce stade, nous ferons un SELECT pour voir les enregistrements dans BooksTBL. Alors MISE À JOUR le prix de « The Alchemist » de Paulo Coelho et SÉLECTIONNEZ à nouveau ce disque spécifique.

Notez que le champ BookLastUpdated affiche désormais une valeur différente. Comme nous l'avons expliqué précédemment, un champ TIMESTAMP affiche la valeur à laquelle l'enregistrement a été inséré ou modifié pour la dernière fois.

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

Même si nous ne le ferons pas ici, vous pouvez également supprimer un enregistrement s’il n’est plus utilisé. Par exemple, supposons que nous souhaitions supprimer « L'Alchimiste » de BooksTBL.

Pour ce faire, nous utiliserons l'instruction DELETE comme suit :

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

Comme dans le cas de UPDATE, c'est une bonne idée d'effectuer d'abord un SELECT afin de visualiser le ou les enregistrements susceptibles d'être impactés par le SUPPRIMER.

N'oubliez pas non plus d'ajouter la clause WHERE et une condition (BookID=6) pour sélectionner l'enregistrement spécifique à supprimer. Sinon, vous courez le risque de supprimer toutes les lignes du tableau !

Si vous souhaitez concaténer deux (ou plusieurs) champs, vous pouvez utiliser l'instruction CONCAT. Par exemple, disons que nous souhaitons renvoyer un ensemble de résultats composé d'un champ avec le nom et l'auteur du livre sous la forme « L'Alchimiste (Paulo Coelho) » et d'une autre colonne avec le prix.

Cela nécessitera un JOIN entre AuthorsTBL et BooksTBL sur le champ commun partagé par les deux tables (AuthorID) :

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

Comme nous pouvons le voir, CONCAT nous permet de joindre plusieurs expressions de chaîne séparées par des virgules. Vous remarquerez également que nous avons choisi l'alias Description pour représenter l'ensemble de résultats de la concaténation.

Le résultat de la requête ci-dessus est affiché dans l'image ci-dessous :

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)

Créer un utilisateur pour accéder à la base de données BookstoreDB

Utiliser root pour effectuer toutes les opérations DML dans une base de données est une mauvaise idée. Pour éviter cela, nous pouvons créer un nouveau compte utilisateur MariaDB (nous l'appellerons bookstoreuser) et attribuer toutes les autorisations nécessaires pour 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)

Avoir un utilisateur dédié et distinct pour chaque base de données évitera d’endommager l’ensemble de la base de données si un seul compte était compromis.

Conseils supplémentaires sur MySQL

Pour effacer l'invite MariaDB, tapez la commande suivante et appuyez sur Entrée :

MariaDB [BookstoreDB]> \! clear

Pour inspecter la configuration d'une table donnée, faites :

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

Par exemple,

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)

Une inspection rapide révèle que le champ BookIsAvailable admet des valeurs NULL. Puisque nous ne voulons pas autoriser cela, nous allons MODIFIER le tableau comme suit :

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

(N'hésitez pas à afficher à nouveau les colonnes – le OUI en surbrillance dans l'image ci-dessus devrait maintenant être un NON).

Enfin, pour visualiser toutes les bases de données sur votre serveur, faites :

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)

L'image suivante montre le résultat de la commande ci-dessus après avoir accédé à l'invite MariaDB en tant qu'utilisateur bookstore (notez que ce compte ne peut « voir » aucune base de données autre que BookstoreDB et information_schema (disponible pour tous les utilisateurs) :

Résumé

Dans cet article, nous avons expliqué comment exécuter des opérations DML et comment créer une base de données, des tables et des utilisateurs dédiés sur une base de données MariaDB. De plus, nous avons partagé quelques conseils qui peuvent vous faciliter la vie en tant qu'administrateur système/base de données.

  1. Partie Administration de la base de données MySQL – 1
  2. Partie d'administration de la base de données MySQL – 2
  3. Réglage et optimisation des performances MySQL – Partie 3

Si vous avez des questions sur cet article, n’hésitez pas à nous le faire savoir ! N'hésitez pas à utiliser le formulaire de commentaires ci-dessous pour nous joindre.