Dernière m.à.j. : 2018-06-17
1. Que sont les données, que représente une base de données ?
Une base de données, c'est un ensemble (une "liste") de données agencées selon des critères (appelés des champs). Par exemple, une liste de contacts téléphoniques. Les données, ce sont des enregistrements d'une série de valeurs selon les critères de la base de données.
Pour comparer, conceptuellement, la base de données peut être schématisée comme un grand tableau où chaque colonne est un critère d'enregistrement, et chaque ligne est un enregistrement.
Prenons un exemple : un carnet d'adresses. Ce carnet recense les données suivantes : nom, prénom, adresse, ville, code postal, pays.
Un contact est une personne qui est définie selon les critères nom, prénom, adresse, ville, code postal, pays. Le contact est un enregistrement. Ma base de données contient alors tous mes contacts, pour chacun d'entre eux je dispose d'au moins un type de données : soit le nom, soit le prénom ... soit plusieurs ou toutes les données à la fois.
Les données sont agencées par tables, c'est à dire par "tableaux". Pour résumer, une base de données contient une ou plusieurs tables, chaque table ayant un ou plusieurs enregistrements. (Bien entendu, une table peut être vide, une base peut être vide aussi.)
Pour schématiser :
-- TABLE : contacts NOM PRENOM ADRESSE VILLE CP PAYS ----------------------------------------------------------- Alpha Alain 1 rue AAA Paris 75000 FRANCE Beta Benjamin 34 rue G. Lyon 69000 FRANCE Gamma Gaëlle 3, bakerstreet London 1234 U.K.
Voilà un exemple de données brutes : aucune mise en forme n'existe, simplement les données sont dans une sorte de tableau (la table nommée arbitrairement "contacts"), triées par critères (nom, prénom, adresse, ville, CP, pays).
2. S.G.B.D.R. ? S.Q.L. ?
Ces données, afin d'être utilisées, doivent faire l'objet d'un traitement. On doit pouvoir en ajouter, en retirer, en mettre à jour, en choisir selon certaines conditions ... C'est le travail du Système de Gestion de Base de Données (S.G.B.D.) : il a pour rôle de gérer efficacement ces données et de pouvoir être interrogé, via des requêtes, par le langage SQL (Structured Query Language (Langage de requêtes structurées)). Il existe plusieurs S.G.B.D., certains gratuits, d'autres payants : pour les gratuits, on trouve MariaDB (ex MySQL), on trouve également PostgreSQL, pour ne citer que les principaux; dans le domaine des payants, on retrouve le monde Microsoft : Access, mais aussi Microsoft SQL Server, Oracle, ou encore MySQL : il existe une version payante de ce S.G.B.D.
Présentées telles que dans l'exemple ci-dessus, les données sont très facilement utilisables. Mais le S.G.B.D. peut aller beaucoup plus loin : certains S.G.B.D. sont dits "S.G.B.D.R.", R pour "Relationnelles" : il s'agit d'un cas où les données sont liées par des relations. Voici un exemple :
-- TABLE : contacts NOM PRENOM ADRESSE VILLE CP ID_PAYS ----------------------------------------------------------- Alpha Alain 1 rue AAA Paris 75000 1 Beta Benjamin 34 rue G. Lyon 69000 1 Gamma Gaëlle 3, bakerstreet London 1234 2 -- TABLE : pays ID PAYS ----------- 1 FRANCE 2 UNITED KINGDOM
Dans cet exemple, si la table "pays" n'existe pas, je ne sais rien quant au pays de chaque contact... Il y a une relation entre la table "contacts" et la table "pays".
3. Les champs d'une base de données
Les champs correspondent, dans les exemples précédents, à une des variables qui définissent un enregistrement, par exemple "prénom". Les champs sont caractérisés par une nature, ils permettent, pour certains, d'identifier avec précision une valeur ou un groupe de valeurs.
Le champ peut être de plusieurs types, pour les 3 principaux : du texte, des valeurs numériques ou des valeurs binaires. Il sert à définir l'une des variables d'un enregistrement, mais pas seulement : certains champs ont des propriétés particulières.
Champs numériques
Les champs numériques peuvent contenir des chiffres, selon la taille disque occupée, sur des fourchettes. Par exemple, si je prends un champ INT (integer <=> 'numérique') je peux stocker tous les nombres de -2 147 483 648 à 2 147 483 647. En mettant l'attribut UNSIGNED, on stocke de 0 à 4 294 967 295 (on ne se préoccupe plus du signe avec le paramètre UNSIGNED). Il existe INT, TINYINT, BIGINT, DOUBLE qui peuvent contenir des nombres plus ou moins grands, pour faire un identifiant "clé primaire" un INT peut suffire. ATTENTION : ce type de champ stocke des nombres entiers, pour stocker des nombres décimaux, il faut avoir recours à un champ FLOAT.
Champs de texte
Les champs de texte sont logés à la même enseigne que les champs des nombres. On trouve ainsi VARCHAR pour stocker au plus 255 caractères (de 0 (champ vide) à 255 caractères, ce qui donne 256 cas possibles) - on peut "diminuer" en spécifiant VARCHAR(10) par exemple -, un champ type TEXT peut stocker 65535 caractères, LONGTEXT : 4 294 967 295 (2^32 -1) caractères... Il existe également le type ENUM : ce type de champ présente une énumération. Par exemple, ENUM('rouge', 'bleu', 'vert') assurera que la valeur du champ sera l'une des 3 de la liste.
Champs de type date et heure
MySQL par exemple gère bien les dates et heures. Mettre un champ en tant que DATE permet d'effectuer des opérations sur les dates dans les requêtes, plutôt que de stocker la date sous forme de texte.
Autres types de champs
On peut stocker des images, ou encore des fichiers complets dans une base de données : en les encodant dans un champ de type BLOB (binaire) par exemple. Ce n'est pas réellement conseillé, pour des raisons d'indexage de la base et de taille...
Clé primaire (Primary Key)
La clé primaire est une propriété d'un des champs. Ce n'est *pas* un champ à proprement parler, c'est un attribut d'un champ. Un champ texte, un champ numérique ... peuvent être clés primaires. Supposons ces contacts :
-- TABLE : contacts NOM PRENOM VILLE CP ID_PAYS ----------------------------------------------------------- Alpha Alain Paris 75000 1 Beta Benjamin Lyon 69000 1 Beta Benjamin Lyon 69000 1 Gamma Gaëlle London 1234 2 -- TABLE : pays ID PAYS ----------- 1 FRANCE 2 UNITED KINGDOM
Ce n'est pas impossible que 2 enregistrements aient les mêmes valeurs ... J'ai volontairement enlevé l'adresse pour montrer qu'à Lyon, on connait 2 "Benjamin Beta" : bien entendu, l'ajout de l'adresse permettra de les différencier, mais on n'a pas toujours ce type d'information. Comment les distinguer sans ambiguïté ? La réponse est simple, on attribue un numéro à chaque individu, généralement appelé "identifiant" et abrégé sous la forme "id" (ou "ID"). Cet identifiant est "auto incrémenté", c'est à dire qu'on ajoute +1 à sa valeur précédente pour tout nouvel enregistrement :
-- TABLE : contacts ID NOM PRENOM VILLE CP ID_PAYS ----------------------------------------------------------- 1 Alpha Alain Paris 75000 1 2 Beta Benjamin Lyon 69000 1 3 Beta Benjamin Lyon 69000 1 4 Gamma Gaëlle London 1234 2 -- TABLE : pays ID PAYS ----------- 1 FRANCE 2 UNITED KINGDOM
Et là, le problème de "Benjamin" est résolu : je peux demander à voir le contact dont l'ID est 2 ou 3, j'ai bien 2 personnes distinctes. Cette propriété qu'a l'ID de différencier les enregistrements s'appelle "clé primaire" (primary key). Cette clé primaire est une option d'un champ qui permettra de distinguer chaque enregistrement. Toutes les valeurs des clés doivent être uniques : il ne doit pas, pour une même table, y avoir 2 valeurs égales de clés primaires.
La clé primaire peut être faite sur un identifiant numérique, comme c'est le cas le plus général, mais aussi sur n'importe quel champ. Par exemple, j'aurais très bien pu prendre "nom" comme clé primaire dans ma table. L'inconvénient dans ce cas est que je n'aurais pas pu rentrer 2 personnes d'une même famille (puisque le nom est une clé primaire, il ne doit être présent qu'une seule fois). On peut créer une clé primaire sur plusieurs champs : par exemple, { nom + prénom }. Là cette fois, c'est le couple {nom, prénom} qui doit être unique. Je peux avoir { MARTIN, Jean } et { MARTIN, Sophie }, mais je ne peux pas avoir 2 fois MARTIN, Jean.
Généralement, la clé primaire est un numérique car son traitement par le moteur de base de données est plus rapide qu'un texte. Remarque : la clé sur un nombre auto incrémenté peut générer des trous si certaines valeurs sont effacées (par exemple, on supprime l'ID = 3, il reste 1, 2 et 4, un nouvel enregistrement prendra la valeur 5) : cela ne pose aucun problème, tant que chaque nouvel enregistrement ne prend pas un identifiant qui existe déjà.
Clé étrangère (Foreign Key)
La clé étrangère est un champ dans une table qui est clé primaire dans une autre table. Pour mon exemple ci-dessus, je peux très bien spécifier que ID_PAYS est une clé étrangère liée au champ ID de la table pays. L'existence d'une clé étrangère dans les S.G.B.D.R. permet de mettre à jour des enregistrements en cascade, par exemple supposons que j'efface le pays "France", je dois supprimer tous les contacts qui y vivent (puisqu'ils ne seront alors rattachés à aucun pays). On peut le faire "à la main", mais l'existence d'une clé étrangère avec une relation entre les tables "contacts" et "pays" permet de le faire automatiquement via le moteur du S.G.B.D.R.
4. Qu'est-ce que le S.Q.L. ?
Le SQL (Structured Query Language) est un langage d'interrogation de base de données. Il n'est pas un langage de programmation, simplement d'interrogation. Via un outil, on compose une requête SQL (la requête SQL est une chaine de caractères composée de mots clés) et on la soumet au SGBD, celui-ci l'exécute et retourne une réponse, qui peut être un "vrai/faux" ou une série d'enregistrements (nommée, en anglais, dataset), sorte de petite table virtuelle listant tous les enregistrements qui correspondent à la requête.
Le langage SQL n'est pas sensible à la casse (différence majuscules/minuscules) pour les commandes SQL. En revanche, il l'est pour les noms de champs !
Il admet des commentaires sur des lignes contenant soit un dièse # soit 2 traits d'union -- pour précéder le texte qui sert de commentaire.
5. Commandes S.Q.L.
Il existe plein de commandes SQL pour composer une requête. Ci-dessous sont listées les principales. On peut les classer en 3 grandes catégories :
- Les commandes qui ont trait à la manipulation de données de la base : SELECT, INSERT, UPDATE, DELETE
- Les commandes qui ont trait à la manipulation de la structure de la base : CREATE, ALTER, INDEX, DROP
- Les commandes qui ont trait à l'administration de la base : GRANT, SHOW DATABASES, LOCK TABLES, REFERENCES, CREATE USER
Remarque 1 : le langage SQL n'est pas case-sensitive, on peut aussi bien écrire "Insert" que "INSERT" que "InSeRt". Par souci de clarté dans mes exemples, je noterai les commandes SQL toujours en majuscules.
Remarque 2 : certains S.G.B.D.R. tels que Oracle, par exemple, demandent à ce que chaque transaction SQL soit validée ou non via respectivement un "commit" ou un "rollback" : ces 2 commandes permettent d'écrire dans la base les données précédemment manipulées via un INSERT, un UPDATE ou encore un DELETE.
Remarque 3 : la commande SQL se termine en principe par un point virgule. Certains S.G.B.D. peuvent accepter sans point virgule, mais par souci de rigueur, je le mettrai dans mes articles.
6. Gestion des bases de données
En réalité, gérer une base de données c'est avant tout se connecter à un serveur : il faut donc avoir un compte sur le serveur (identifiant/mot de passe). Ce compte a des droits, par exemple un compte basique ne pourra que gérer les données d'une base, il ne pourra pas créer d'autre base, etc. Les permissions attribuées à un compte le sont grâce à un compte de "super utilisateur", qui a tous les droits, qui n'est pas éditable ni supprimable.
Lorsque le "super utilisateur" crée un compte, il lui affecte donc des droits. Un serveur de base de données contient une ou plusieurs bases, chacune ayant ses propres tables et ses propres données.
Les sous-parties suivantes supposent que la connexion à un serveur de base de données est faite et qu'une base de données est choisie.
Création d'une base de données
Pour créer une base de données, il faut utiliser la commande CREATE DATABASE :
CREATE DATABASE matthieu;
Création d'une table dans la base de données
Pour créer une table dans la base de données, il faut utiliser la commande CREATE TABLE. On crée la table en listant les champs ainsi que leur nature, et éventuellement leur valeur par défaut, puis on spécifie, dans le cas de MySQL le type de table :
# Création de la table contacts - ceci est une zone de commentaire, -- comme chaque texte suivant le dièse ou les 2 traits d'union CREATE TABLE contacts ( id INT auto_increment NOT NULL, -- cet identifiant sera un entier auto incrémenté nom VARCHAR(255) NOT NULL, -- 255 caractères suffiront pour le nom prenom VARCHAR(255) NOT NULL, -- 255 caractères suffiront pour le prénom ville VARCHAR(255) NOT NULL, -- 255 caractères suffiront pour la ville cp VARCHAR(5) NOT NULL, -- le code postal n'aura au plus que 5 caractères id_pays TINYINT(1) DEFAULT 1, -- le pays sera un chiffre, si non spécifié, '1' sera la valeur par défaut PRIMARY KEY(id) -- ici on met les champs clé primaire, séparés par une virgule si plusieurs ) Engine = MyISAM;
Il est important de bien verrouiller le type du champ : ne pas mettre un texte si on est sûr que ce sera un entier : autant utiliser INT. Cela permet un degré de sécurité supplémentaire. D'autre part, certains noms de champs ne peuvent pas être utilisés, ce sont toutes les commandes SQL : SELECT, DATE, INSERT, etc. (sauf avec une protection par une quote inverse, mais ce n'est pas judicieux de donner le nom d'une commande SQL à un nom de champ ...)
Le type de la table, pour MySQL peut être MyISAM ou InnoDB pour les plus répandus. InnoDB gère les clés étrangères et les suppressions et mises à jour en cascades, alors que MyISAM ne les prend pas en charge.
Suppression d'une table ou d'une base
ATTENTION : supprimer une table ou une base est irréversible !
DROP TABLE -- effacera toutes les données de cette table, contacts; -- les autres tables de la même base ne seront pas effacées DROP DATABASE matthieu; -- effacera toutes les tables de cette base de données
Si le compte de l'utilisateur a le droit de supprimer une table, celle-ci sera effacée.