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
Pour créer une base de données, l’appel est simple :
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
Pour créer une table, on écrira :
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’unintegerfait 4 bytes, le chiffre 1 par ex fait quelques octets. Donc pour unIDon va préférer unSMALLINT!
–FLOAT
–DECIMAL
A noter queFLOATetDECIMALfonctionnent pareil : on met entre parenthèses le nombre de chiffres total puis le nombre de chiffres après la virgule. La différence est queFLOATva 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).DECIMALest 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 queTEXTn’est pas optimisé pour la recherche, alors queVARCHARl’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;
Cette commande permet de voir quelles sont les bases de données qui existent dans le fichier.
SHOW TABLES;
Cela permet de voir quelles sont les tables qui existent ds la base de données en cours d’utilisation.
DESCRIBE nomTable;
Cette commande permet de voir tous les champs d’une table.
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 ;
L’ordre d’écriture de la requête est important. On effectue d’abord une gestion des champs, puis de la table, puis de ce que l’on recherche et enfin du tri souhaité.
SELECT champ1, champ2 FROM tableName;
Pour sélectionner tous les champs, on utilise l’astérisque :
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";
Cette requête va changer d’un coup tous les champs ciblés. Il faut donc bien penser à pointer la ligne sélectionnée :
UPDATE nomTable SET champ1 = "nouvelle valeur", champ2 = "nouvelle valeur" WHERE id = 2;
Effacer
DELETE FROM nomTable;
Cette requête va vider toute la table et il n’y a pas moyen de revenir en arrière !!! Donc bien penser à cibler :
DELETE FROM nomTable WHERE id = 21;
Les jointures
Quand on fait une base de données, on essaye de dupliquer le moins possible les données. C’est plus léger, moins redondant et permet une mise à jour plus aisée.
Pour voir ce qu’il y a dans les différentes tables, on fait une jointure avec INNER JOIN
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).
Il est possible de modifier l’intitulé des champs à ce moment là :
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
« La table 1 est à gauche de table 2 »
Affiche toutes les données de la table gauche même si à droite il n’y a rien à faire correspondre.
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.
- Les fonctions numériques :
–AVG(),COUNT(),MAX(),MIN(),SUM()
– Possibilité de faire des arrondis :ROUND()supérieur par défaut /FLOOR()arrondi inférieur /CEIL() - Les fonctions scalaires :
– Possibilité d’afficher le texte différemment. Par exempleUPPER()
– Il est possible de faire des recherches de string avecSUBSTR() - Les fonctions de dates :
–NOW(), par exemple, qui est une fonction qui donne la date du jour. GROUP BY: il permet de regrouper plusieurs champs (dont certains sont agrégés) en fonction d’un champ défini.
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 !!! 😉
