Base de données avec mySQL

Milieu de formation, fin des cours sur le front !
Nous passons donc sur les connaissances à avoir pour un développeur back…

Le rôle du développeur back est de manipuler les données puis de les envoyer au front pour qu’elles s’affichent.
A noter que le format d’envoi de ces données est le JSON (JavaScript Object Notation).
Le stockage des données se fait à l’aide d’une base de données.

Qu’est ce qu’une base de données ?

Une base de données peut être comparée à un grand tableur excel en plus poussé : le nom du fichier excel correspond à la base de données, les feuilles (ou onglets) aux tables de la base de données ; les colonnes correspondent aux champs et les lignes aux entrées de la base de données.

Pour utiliser la base de données et récupérer les informations qu’elle contient, il faut faire des requêtes. Pour cela, on utilise le langage SQL (Structured Query Langage). Ce langage permet de regrouper toutes les requêtes et de les afficher de façon lisible pour un humain !
Les requêtes sont dites simples quand elles concernent une recherche spécifique dans une table. Elles sont dites complexes quand la recherche se fait sur plusieurs tables combinées.

La finalité d’une base de données est d’avoir des données les plus génériques possible. Ce qui permet ensuite de pouvoir faire n’importe quel type de table.

mySQL

Il existe différentes façon de faire des bases de données.
Ma formation est axée sur mySQL : il peut y avoir de nombreux champs et c’est une base de données très puissante pour manipuler de nombreuses données. mySQL est très performante pour indexer les données.

En base de données, on travaille toujours en « case insensitive » (il faut donc bien penser à indiquer que la base de données est encodée en utf8_general_ci). En effet, lorsque l’on cherche à enregistrer/récupérer une donnée on ne peut pas obliger l’utilisateur à l’écrire d’une façon qui nous convient !

Création d’une base de données et de ses tables

Base de données

CREATE DATABASE IF NOT EXISTS nom_base;
USE nom_base;

Si une base de données existe déjà, il y a une erreur et la lecture du script s’arrête. Pour éviter et maîtriser ces erreurs, il faut bien penser à ajouter IF NOT EXISTS juste avant le nom de la base.

Table

CREATE TABLE IF NOT EXISTS Animal (
    id          SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    espece      VARCHAR(40) NOT NULL,
    sexe        CHAR(1),
    birthdate   DATETIME NOT NULL,
    nom         VARCHAR(30),
    commentaires TEXT,
    PRIMARY KEY (id)
)
ENGINE=INNODB;

Typage des variables

Attention : Il faut bien typer les variables !

• Pour les chiffres INT :
  – SMALLINT
  – MEDIUMINT
  Sachant qu’un integer fait 4 bytes, le chiffre 1 par ex fait quelques octets. Donc pour un ID on va préférer un SMALLINT!
  – FLOAT
  – DECIMAL
  A noter que FLOAT et DECIMAL fonctionnent pareil : on met entre parenthèses le nombre de chiffres total puis le nombre de chiffres après la virgule. La différence est que FLOAT va trouver une fraction qui se rapproche le plus du nombre inscrit ds la base de données, on va donc manquer de précision (la somme des cellules sera ensuite un arrondi et non pas la somme exacte). DECIMAL est quand à lui plus lourd mais il garde le chiffre exact.
  – NOT NULL : défini que l’on n’a pas le droit d’ajouter une ligne qui aurait ce champ vide.
  – UNSIGNED : on l’ajoute pour n’avoir que des chiffres positifs.
  – AUTO_INCREMENT : permet d’ajouter un élément qui va s’auto incrémenter.
• Pour les chaines de caractères TEXT :
  – SMALLTEXT
  – MEDIUMTEXT
  – CHAR : indique le nombre de caractères. Si le nombre de caractères de la donnée est plus petit alors SQL complète en espaces blancs.
  – VARCHAR : utilisé pour moins de 250 caractères. On précise alors le nombre de caractères.
  A noter que TEXT n’est pas optimisé pour la recherche, alors que VARCHAR l’est car il est limité en nombre de caractères.
  Il est donc très important de prévoir en amont la taille maxi des données pour alléger la table.
• Pour les dates, on a le choix :
  – DATE
  – DATETIME
  – TIMESTAMP : la date est convertie en secondes depuis le  01/01/1970. Cela permet un calcul rapide (et facile) des écarts entre deux dates. Les conversions sont aussi plus aisées (en fonction des formats de chaque pays par ex.). Et cela permet une manipulation facile des dates.

Fin de création

On fini la création de la Table avec :
– PRIMARY KEY : la clé primaire est la clé d’indexation de la table. C’est ce qui va la rendre unique (on préférera alors utiliser comme clé primaire l’id de la table !).
Attention, comme elle est écrite à la fin de la table (donc avant la fermeture de la parenthèse), on ne met pas de virgule après !
– ENGINE=INNODB : c’est la configuration du moteur de la base de données.
– CHARACTER SET=utf8 ou DEFAULT CHARACTER SET : correspond au charset (configuration des caractères d’écriture).

Autres types d’affichage

SHOW DATABASES;

SHOW TABLES;

DESCRIBE nomTable;

Effectuer une requête SQL

la requête de base s’effectue à l’aide des mots clés SQL :
– SELECT (liste des champs à afficher / avec une virgule entre eux / s’affiche ds l’ordre écrit)
– FROM (table visée mises entre guillemets inversés « )
Cette requête permet d’afficher une liste de données.
– On fini toujours la requête par un point virgule ;

SELECT  champ1, champ2
FROM    tableName;

SELECT  *
FROM    tableName

Affiner une requête

Il est possible d’affiner la requête en ajoutant :
WHERE : on ajoute alors le champ de recherche et la valeur recherchée avec un = (ou < ou > ou <= ou =>).
Dans ce cas là, la recherche se porte sur la valeur exacte écrite.

On peut aussi utiliser BETWEEN pour un intervalle, NOT pour mettre l’inverse, et on peut cumuler avec AND.

Pour être moins précis et élargir la recherche, ou pour faire en sorte que la case de la donnée ne soit pas prise en compte on utilise : LIKE après WHERE puis la recherche entre guillemets à laquelle on ajoute la wildcard (%) avant, après ou les 2 en fonction de là où on veut que ce soit « flou ».

Trier la recherche

Il est aussi possible de trier la recherche. On ajoute alors :
– ORDER BY : on indique comment se fait le tri, et dans quel ordre (ascendant ASC (du plus petit au plus grand) ou descendant (du plus grand au plus petit) avec DESC).
– LIMIT / OFFSET : tri entre 2 bornes. On peut aussi l’écrire 0, 4 pour les 4 éléments à partir de l’index 0.
– DISTINCT : à placer dans SELECT, pour supprimer les doublons

Autres variations

On peut aussi imbriquer des requêtes, toujours en gardant l’ordre et en mettant la deuxième requête entre parenthèses.

Important : il faut aller progressivement ds la rédaction des requêtes pour vérifier qu’à chaque étape tout fonctionne correctement !

Mise à jour de champs

UPDATE nomTable SET champ1 = "nouvelle valeur", champ2 = "nouvelle valeur";

UPDATE nomTable SET champ1 = "nouvelle valeur", champ2 = "nouvelle valeur" 
WHERE id = 2;

Effacer

DELETE FROM nomTable;

DELETE FROM nomTable WHERE id = 21;

Les jointures

SELECT *
FROM table
INNER JOIN table2 ON table1.champ = table2.champ;

La table 2 se rajoute à la fin de la table 1, à condition que les deux champs correspondent entre eux !
Quand on cherche à faire une jointure entre deux tables avec des champs dont le nom est identique, il faut absolument éviter les ambiguïtés. Dans tous les cas, il faut penser à bien mettre le nom de la table avant le champ (table.champ).

SELECT table1.champ1 as "Nom Générique du champ1"
              table1.champ2 as "Nom Générique du champ2"
              table2.champ2 as "Nom Générique du champ2bis"
FROM table
INNER JOIN table2 ON table1.champ3 = table2.champ3;

INNER JOIN

C’est une jointure fermée qui ne récupère pas de données s’il n’y a pas de croisements. La jointure ne se fait que s’il y a des données « jointables » des 2 cotés.

LEFT JOIN

LEFT JOIN ... WHERE table2.champ IS NULL

Ici, on affiche tous les éléments de la table 1 qui n’ont pas de liens avec la table 2.

RIGHT JOIN

idem que LEFT JOIN mais avec la table de droite !

CONSEIL : il est préférable de ne faire que des LEFT JOIN pour garder les repères lors de la jointure des tables !

Les fonctions d’agrégation

Ces fonctions retournent une valeur unique et non une liste d’entrées. En effet, elles permettent d’agréger des résultats en une seule donnée et le résultat de ces fonctions est alors un sous dossier des champs agrégés.
Elles sont bien expliquées sur le site de w3schools .

On distingue différents types de fonctions.

SELECT  champ1, count(*) as 'Nombre de résultats'
FROM    tableName
GROUP BY champ1

J’ai appris beaucoup de nouvelles notions. Et bien qu’ayant l’habitude de manipuler des tableurs, la création et la manipulation d’une base de donnée sont de grandes nouveautés pour moi !
Et ce n’est pas fini !!! 😉