Le langage SQL : mySQL

To the non-french speaker, note that you can translate the articles using the Google Trad widget situated at the bottom of all pages.


Pour ceux qui me suivent depuis un petit moment vous allez avoir l’impression d’avoir déjà lu cet article… Et pour cause, je l’avais déjà sorti au tout début sur mon blog personnel. Puis je l’avais supprimé de whiteflagfr car il y avait trop d’informations personnelles dessus. Cependant vu le temps que j’ai passé à l’écrire je me suis dit qu’il faudrait le ressortir en l’adaptant et … Voilà !

Donc dans cet article nous verrons comment installer MySQL et s’en servir (création de bases, insertion de données etc…) ! La seule différence avec l’original est que j’ai supprimé les parties sur l’automatisation car elles n’apportent pas de réel intérêts. Bonne lecture 😉 !

I/Installer MySQL sous Debian

Comme d’habitude rien de très compliqué :

 apt-get install mysql-server mysql-client

On installe la partie serveur et la partie cliente d’un coup, comme ça pas de soucis pour les articles à venir (oui je spoil mes lecteurs 😎) !

Ensuite il faut lancer le service mysql :

sudo /etc/init.d/mysql start

Puis pour entrer dans la console mysql il suffira de taper :

sudo mysql

mysql.png

Alors oui, ici j’utilise la console MariaDB. C’est un feature qui a été implémenté par défaut sur la plupart des distributions car MariaDB est plus rapide, propose plus de fonctionnalités et de meilleurs moteurs (on verra ce que c’est plus tard) tout en fonctionnant de la même façon que MySQL.

Let’s jump into databases !

II/Notions de base

Une base de données sert à stocker des données (vous vous en doutiez). Chaque donnée à un type, cela peut-être un nombre, un texte, une date… Et chaque type a des sous types, par exemple pour le type entier (INT) on a TINYINT (nombre allant de -128 à 127), SMALLINT (-32768 à 32767) et ainsi de suite.

Voici les types de données les plus utilisés :

-Pour les nombres : INT (ou SMALLINT si on souhaite stocker un âge par exemple)
-Pour les textes : TEXT (ou VARCHAR(x)) avec x le nombre de caractère à stocker)
-Pour les dates : DATE (sous ce format : AAAA/MM/JJ)
-Pour les horaires : TIME (sous ce format : HH :MM :SS)
-Pour les dates et horaire : DATETIME (sous ce format : AAAA/MM/JJ HH :MM :SS )

Deux règles sont à respecter pour que votre SGBD fonctionne bien :

-Les noms de bases, tables ou colonnes sont en minuscules sans accents/espaces : « nomPersonne« , « nom_Personne » mais pas « nom Personne« .
-Les instructions se finissent par un « ; ».

Enfin voici quelques commandes de base à connaître :

SHOW DATABASES ; affiche les bases de données déjà créées.
SHOW TABLES ; affiche les tables de la base de données.
USE ‘nom_base’ ; Permet de choisir la base de données sur laquelle nous allons travailler.
DESCRIBE  ‘nom_table‘ ; Affiche unr récapitulatif des attributs d’une table.
DROP ‘nom_base’ ; Permet de détruire la base.
DROP table ‘nom_table’ ; Permet de détruire une table.

Si j’affiche les bases de données disponibles j’obtiens ceci :

basicdb.png

Ces trois bases de données sont extrêmement importantes donc je vous invite à ne pas les modifier/supprimer.

La base information_schema stocke l’ensemble des informations sur les données que vous utilisez dans votre base de données (nom de la base, nom des tables, nom des colonnes etc… (des metadatas en somme). La base performance_schema est utilisée afin de monitorer les performances du serveur. Quant à la base MySQL, eh bien elle contient toutes les informations nécessaires au bon fonctionnement de MySQL.

Pour jouer avec le SGBD il va donc falloir créer une nouvelle base. Voici la syntaxe à utiliser :

CREATE DATABASE 'nom_table' CHARACTER SET ‘utf8’ ;

Utf-8 permet un meilleur encodage, il permet d’utiliser TOUS les symboles de toutes les langues. Pour ma part je vais créer une nouvelle base « table_test » :

createdb.png

Ok super on a crée la base mais ça ne veut pas dire que l’on peut l’utiliser pour le moment. Avant toute chose il va falloir la sélectionner :

USE 'nom_table';

use.png

Maintenant on peut commencer à jouer avec notre base de données !

III/Créer des tables

Comme je vous l’ai dit plus haut, lors de la création des tables de la base de données il faut réfléchir aux types de données que l’on utilisera. Pour cet article nous allons créer une table « client » qui contiendra toutes les informations d’un client : identifiant client (qui sera la clé primaire de la table (j’y reviendrai)), nom du client, prénom, peut-être son âge, son adresse, son numéro de téléphone, son email… Et voilà je pense qu’on a tout !

Je vous ai parlé d’identifiants et de clés primaires. Pour comprendre ce que c’est, je vais vous donner un exemple rapide. Imaginez que je veuille stocker deux clients qui s’appellent tous les deux Pierre Lucas, je vais avoir un souci : comment différencier l’un de l’autre ? L’identifiant est là pour ça. Il est attribué (le plus souvent) automatiquement, de manière incrémentale, est constitué d’une suite de chiffre et est unique. Des identifiants comme ça vous en retrouvez souvent : votre numéro d’identification national, votre plaque d’immatriculation etc… Cet identifiant en SGDB est appelé clé primaire.

MySQL utilise un système de moteur qui lui est propre. Il en existe deux prépondérants :
-MyISAM : généralement rapide mais ne gère pas le système de clé étrangère
-INNODB : plus lent mais gère les clés étrangères.

Bon en soit, on choisit l’un ou l’autre en fonction de la base que l’on utilise. J’ai quand même une préférence pour INNODB (rien que pour le principe de clés étrangères).

Dernier point, en SQL, les colonnes de nos tables peuvent avoir comme valeur NULL. Cette option est à spécifier dès la création de la table. Dans notre table « client », à priori l’âge du client n’est pas réellement utile. A part faire des calculs statistiques et des moyennes cette colonne ne nous servira pas. On va donc lui donner la possibilité d’avoir la valeur NULL.

Récapitulons. Nous allons créer la table « client ». Dans cette table nous aurons :
-un champs « idClient » clé primaire, unique, not null, int qui s’auto incrémente
-deux champs « nomClient » et « prenomClient », not null, text
-un champ « âge » int
-deux champs « email » et « adresse » not null, text
-un champ « telephone » not null, int

Voici la syntaxe :

client.png

VARCHAR(30) indique au moteur SGBD que l’on va stoker une chaîne de caractère de maximum 30 caractères. La ligne ENGINE=INNODB indique que le moteur de base de données à utiliser est INNODB.

IV/Modifier sa base de données

Parfois il peut arriver qu’on foire la création de la table, qu’on oublie une colonne, qu’on en ait mis une en trop… Bref sachez que vous pouvez très facilement ajouter, supprimer modifier voir renommer une colonne. Pour chacune de ces actions je vous donne la syntaxe générale ainsi qu’un exemple.

  • Ajouter une colonne :
ALTER TABLE 'table' ADD 'colonne' 'attribut1' 'attribut2';

alteradd.png

  • Supprimer une colonne :
ALTER TABLE 'table' DROP 'colonne';

alterdel.png

  • Modifier une colonne :
ALTER TABLE 'table' MODIFY 'colonne' 'attribut1' 'attribut2'

modify.png

(L’attribut âge pouvait ne pas avoir de valeur. Avec la modification ce n’est plus possible.)

  • Renommer une colonne :
ALTER TABLE 'table' CHANGE 'nom_actuel' 'nouveau_nom' 'type de donnees';

changename.png

Nous avons fait beaucoup de modifications sur notre base donc ce que je vous invite à faire c’est d’utiliser cette commande :

DESCRIBE client;

Afin d’obtenir un récapitulatif complet de votre table ainsi que de ses attributs :

describe

V/ Lire/écrire des données

Voici la syntaxe à utiliser pour insérer des données dans une base de données :

INSERT INTO 'nom_de_la_table'("attribut1","attribut2") VALUES("valeur1", "valeur2");

Par exemple :

insertinto.png

Notez que nous n’avons pas besoin de spécifier l’identifiant du client puisque celui-ci s’incrémente tout seul. Pour lire des données dans une base de données il faut utiliser cette syntaxe

SELECT 'nom_de_la_colonne FROM 'table' (WHERE 'attribut'='valeur');

Si j’ai mis la clause WHERE entre parenthèses c’est parce qu’elle n’est pas obligatoire. En effet on peut demander au SGBD de nous sortir toutes les informations de la base. Il n’y a donc pas de condition (symbolisé par le WHERE). Pour cela on utilisera cette syntaxe :

SELECT * FROM 'table';

select*.png

Ici le  » *  » indique que nous voulons récupérer les informations contenues dans toutes les colonnes. Du coup si on voulait afficher juste les informations concernant Luke on utiliserait cette commande :

selectsomeone.png

Une question que vous pourriez vous poser est : « Pourquoi est ce que l’incrémentation commence à 2 ? » Eh bien en fait c’est parce que j’avais ajouté un premier client et il a fallu que je le supprime ce qui a eu pour conséquence de décaler toutes les valeurs.

Mais pas de paniques, nous pouvons modifier tout ça via la commande update ! Voici sa syntaxe :

UPDATE 'table' SET 'attribut'='valeur' WHERE 'attribut'='valeur';

updatetable.png

Dernière chose, vous pouvez tout à fait supprimer une ligne de données. Par exemple si on voulait supprimer l’utilisateur anakin on utiliserait la syntaxe suivante :

DELETE FROM 'table' WHERE 'attribut'='valeur';

delete.png

Voilà ! Vous disposez d’à peu près tous les outils nécessaires pour créer des base de données.

VI/ Clé primaire et secondaire

Pour la suite de l’article nous utiliserons d’autres tables donc voici les syntaxes :

  • La table article :

article.png

  • La table commande :

commande

Vous remarquerez que dans la table  « commande » j’ai réutilisé les attributs « idArticle » et  « idClient ». En fait, j’ai utilisé ce que l’on appelle des clés étrangères. Ces clés permettent de maintenir une certaine cohésion au sein de la base de données. En effet que se passerait-il si j’insérais dans la table commande une commande faite par un client qui n’est pas dans la table client ? Tout va boguer et ça sera un sérieux calvaire pour trouver d’où vient l’erreur. Il va donc falloir que je lie les colonnes idArticle et idClient aux clés primaires des tables correspondant (Article et Client).

Pour cela on dispose d’une syntaxe :

ALTER TABLE nom_table ADD CONSTRAINT nom_de_la_contrainte
FOREIGN KEY (nom_de_la_colonne_concernée)
REFERENCES nom_de_la_table_reference(nom_de_la_colonne_de_la_table_référencée);

Voilà ce que ça donne pour la table clé étrangère concernant idArticle :

article

Et voici la même chose pour idClient :

idclient.png

Maintenant qu’on a notre base de données bien paramétrées il va falloir tester que tout fonctionne comme nous le voulons. Pour commencer on va insérer des articles :

insertarticl.png

Ensuite on va insérer une commande :

insertcommande.png

Tout à l’air de fonctionner si ce n’est la dernière requête.

La raison est simple, j’ai demandé à notre base d’ajouter une commande venant du client dont l’identifiant est 1, avec l’article dont l’identifiant est 2. Or dans notre base il n’y a pas de clients ayant comme identifiant 1 (nous l’allons supprimer tout à l’heure). La contrainte (clé étrangère) fk_idClient a donc empêcher l’insertion de cette dernière ligne.

VII/ Union de table

Dans la table commande on dispose de l’identifiant du client qui a passé la commande, de l’identifiant du produit commandé et de sa quantité. Seulement voilà, comment faire pour récupérer le nom du client à partir de son identifiant ?

Eh bien il va falloir joindre les deux tables en utilisant les informations qui leurs sont communes. Dans notre cas l’information commune entre la table client et la table commande est l’idClient.

Voici la syntaxe pour joindre des tables :

SELECT 'nom_colonne" FROM 'table' 
INNER JOIN 'table_a_joindre'
ON (information_commune_table_1=information_commune_table_2)
WHERE 'condition';

Et voilà ce que ça donne quand on veut récupérer le nom du client via son identifiant :

innerjoin

VIII/Les fonctions utiles

Le langage SQL met à disposition de l’utilisateur de nombreuses fonctions permettant -par exemple- de récupérer un minimum, un maximum, une somme etc… En voici quelques-unes :

  • La fonction MIN :

Je veux récupérer l’article dont le prix est le moins cher :

min.png

  • La fonction MAX :

Je veux récupérer l’article dont le prix est le plus cher :

max

  • La fonction COUNT :

Je veux connaître le nombre de commande effectuée :

count

count

  • La fonction AVG :

Je veux connaître la moyenne des prix des articles :

avg

  • La fonction SUM :

Je veux connaître la somme des prix de chaque article de notre base :

sum.png

  • La fonction ORDER BY :

Je veux trier toute la table article en fonction des prix des articles :

orderby

Il en existe encore d’autres mais vous trouverez toute la documentation nécessaire sur le web 😉 !

IX/ Sécuriser sa base de données

Une base de données, c’est un outil qui peut stocker des données ultra sensibles, il existe donc différents procédés qui permettent de sécuriser la base. Parmi eux on retrouve la transaction.

Ce genre de procédé est utilisé en cas de panne de courant ou bien de bug au niveau de la base de données qui mettraient fin à l’exécution des données. En fait une transaction c’est un ensemble de requêtes qui sont effectuées en un seul bloc. C’est-à-dire que si l’une des requêtes échoue alors toutes les requêtes du groupe seront annulées.
Seules les tables créées avec le moteur INNODB supportent les transactions.

Par défaut lorsque l’on donne une requête à notre SGBD, celui-ci l’exécute et la valide. C’est ce qu’on appelle « l’autocommit ». Pour quitter ce mode il faut entrer cette commande :

SET autocommit=0 ;

A partir de là, après chaque instruction, il faudra soit valider la requête (lancer la requête COMMIT 😉 soit la refuser (ROLLBACK ;).

Essayons tout ça !

autoco

On va essayer de créer une nouvelle table avec un champ que l’on va modifier :

tabletestautommit

Puis on va insérer une donnée :

insertac

On fait le ROLLBACK  puis un select :

rollback

Et on se rend bien compte que la table n’a pas été créée.


Voila ! C’est la fin de cet article d’introduction sur MySQL. Si je l’ai fait c’est afin de vous faire découvrir la gestion de bases de données mais aussi car j’aurai besoin de certaines notions (notamment le ORDER BY et le INNER JOIN) pour vous présenter le fonctionnement de la faille SQL.

Il reste encore pas mal de choses à voir sur le fonctionnement des bases de données mais nous verrons ça plus en détails au fil des articles sur l’injection SQL 😉 !

Comme d’habitude je vous invite à vous abonner à ma page Facebook, vous y trouverez des posts sur l’actualité cybersécurité, des news vis à vis des articles à venir ainsi que des outils que je suis entrain de créer… Enfin bref pas mal d’infos qui pourraient vous intéresser !

3 commentaires

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l'aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s