Cours intensif de MySQL
Bienvenue pour cette autre publication sur les bases du PHP. Il y a quelque temps, j’ai été pris par des débats sur l’état des progrès en matière de sécurité et sur les bonnes pratiques. Aujourd’hui, j’ai choisi un autre sujet, je vais vous présenter ce que l’on peut considérer comme la pierre angulaire de toute application fonctionnant avec une base de donnée et destinée à internet.
Je vais plus spécialement aborder, les bases de données MySQL. Cet article constitue un cours intensif de SQL. Ceux d’entre vous qui ont déja travaillé avec les bases de données relationnelles comme MySQL n’ont pas besoin de lire ce qui suit, il n’y a d’ailleurs aucun code PHP. A la place, je vais introduire les concepts des bases de données relationnelles et le langage qu’il convient d’utiliser pour manipuler les données à travers elles. Si vous n’avez pas été du tout en contact avec elles, ce qui suit vous est destiné.
Remarque : pour pouvoir travailler avec les exemples proposés, vous devez avoir accès à une base de données MySQL par l’intermédiaire d’un client MySQL, comme par exemple l’application MySQL. Si vous n’avez pas accès à MySQL, il est possible de le télécharger gratuitement à partir de la page d’accueil du site officiel de MySQL. Deux versions, compatibles Unix ou Windows vous seront alors proposées. Pour obtenir plus d’informations à propos de l’installation et de l’utilisation de l’application MySQL client/serveur, veuillez consulter le site MySQL.com.
Principes des bases de données relationnelles
Les bases de données relationnelles représentent la pierre angulaire sur laquelle repose toute application internet sérieuse. Elles fournissent l’activité de fond et si on les utilise correctement, des méthodes rapides pour stocker ou récupérer de grandes quantités de données. Bien que de nombreux types de bases de données relationnelles existent (appelées de manière formelle “Relational Database Management Systems” en abrégé : RDBMS (”Systèmes de management de bases de données relationnelles”) comme Oracle ou PostgreSQL. Tous mes propos sont centrés sur MySQL RDBMS. Les principes fondamentaux et tout ce qui concerne SQL est en rapport direct avec l’environnement de base de donnée utilisé.
Les bases de données relationnelles sont conçues pour stocker de très importantes quantités de données : adresses e-mail, images… tout ce qui peut être utilisable. Cependant, les ensembles RDBMS ne tirent pas leur force du type de données qu’ils intégrent. C’est plustôt l’organisation des données opérée par RDBMS qui est bénéficiaire aux utilisateurs.
Les données sont organisées dans une ou plusieurs bases. Ces bases possèdent des tables qui ont pour fonction de stocker les données. Une des meilleures analogies avec le système RDBMS peut être ceci : considérons la prise de rendez-vous au jour le jour d’un office. chaque rendez-vous correspond à un dossier, chaque dossier contient un simple tableau à lignes et colonnes qui enregistre les données pour le dossier considéré. Dans cette perspective, le remplissage des rendez-vous peut à lui seul être considéré comme une base de donnée, chaque dossier peut être identifié comme une table appartenant à la base de donnée générale contenant l’ensemble des dossiers.
Pour récupérer une donnée précise dans le carnet de rendez-vous, vous devez d’abord choisir la base donnée, ensuite le fichier et enfin dans celui-ci vous extrairez les données recherchées.
Au point de vue du système RDBMS, cela représente concrètement la description de votre base digitale. A la place des dossiers, il y a simplement les noms des tables et au lieu d’une recherche manuelle dans chaque table pour un ou des ensembles de données, vous utilisez un langage appelé SQL “Structured Query Langage” (Langage Structuré d’Interrogation) pour récupérer les données que vous recherchez.
SQL, langage structuré d’interrogation
Avant que vous ne commenciez à vous inquiéter d’essayer d’apprendre un langage entièrement nouveau et de faire ensuite la même chose pour le PHP, laissez-moi calmer vos peurs. SQL est l’un des langage les plus accessibles qu’il soit. SQL est de toutes façons absolument indispensable parce qu’il fournit la manière par laquelle vous stockez et récupérez l’information au sein de RDBMS. Cela ne veut pas dire que les interrogations SQL ne peuvent pas devenir incroyablement complexes, soyez en tous cas assurés que les principes sous-jacents du langage sont simples à acquérir. Le plus simple moyen pour le comprendre est de commencer tout de suite par sa pratique, commençons par introduire quelques une des principales instructions.
Remarque : comme je l’ai mentionné, SQL peut être un langage très complexe où les formulations peuvent prendre de nombreux et différents aspects, ce qui est très déstabilisant pour un débutant . Pour éviter toutes confusions, considérez que les exemples qui suivent ne reflètent pas l’ensemble du langage et qu’ils ont été volontairement simplifiées pour favoriser la pratique au quotidien de SQL. Pour une syntaxe complète et une utilisation avancée, reportez-vous à la documentation sur MySQL.
Comme vous n’avez jamais utilisé de bases de données relationnelles avant aujourd’hui, votre première tâche va être d’apprendre l’instruction CREATE. Cette instruction est utilisée pour créer des bases de données mais aussi des tables. Commençons par créer une nouvelle base de données que nous appelerons fundamentals (fondamentale) :
mysql> CREATE DATABASE fundamentals; Query OK, 1 row affected (0.01 sec)
Cette instruction a simplement crée un classeur (database) qui peut être rempli avec des dossiers (tables) pour stocker des données. Evidemment, un classeur vide sans dossiers ne peut pas nous permettre de realiser correctement notre stockage de données, nous avons donc besoin de créer également des tables dans cette base de données.
Avant de procéder à la création de tables, vous devez en apprendre un peu plus à leur sujet. Comme je l’ai mentionné plus haut, les tables sont simplement des ensembles de lignes et de colonnes. Lorsque vous créez ou travaillez avec les tables d’une base de données, chaque colonne d’une table doit être préalablement définie par rapport au type de données qu’elle contient. Le type de données contenu dans une colonne peut être par exemple : integer (nombre entier), floating point (virgule flottante), string (chaîne de caractères)…On peut aussi préciser des propriétés telles que : default value (valeur par défaut), auto-incrementing number (auto-incrémentation numérique). Comme chaque colonne d’une table doit contenir un type de donnée, voyons à présent quelques-uns des types disponibles.
INT[(SIZE)] [UNSIGNED] [ZEROFILL]Un simple nombre entier compris entre -2147483648 and +2147483647 ou, si l’attributUNSIGNED(sans signe) est renseigné, entre 0 et 4294967295. L’attributZEROFILL(remplissage avec zéros) indique que ce nombre doit être complété avec des zéros pour remplir la tailleSIZE(taille) déterminant la longueur d’affichage des nombresVARCHAR[(SIZE)] [BINARY]Une chaîne sous forme d’une variable longue, dont la taille maximumSIZE(taille) précise sa longueur en nombre de caractères, celle-ci ne peut dépasser 255 caractères. A moins que l’attributBINARY(binaire) ne soit déterminé, ce type de données est considéré comme “case-insensitive” (insensible à la casse) et ne peut évidemment pas contenir de données binaires.TEXTUne chaîne de caractère “case-sensitive” (sensible à la casse) qui possède une longueur maximum de 65 535 caractères.DATETIMEUn champ date et temps qui s’étend du premier janvier de l’an 1000 à 0h 0′ 0” au 31 décembre 9999 à 23h 59′ 59”. Les dates sont exprimées dans le format : YYYY-MM-DD HH:MM:SS (années-mois-jour heure:minutes:secondes).DATEUn type de donnée semblable àDATETIME(datetemps), ne comprenant pas les dates exprimées sous le format YYYY-MM-DD (année/mois/jour).
Bien que cette liste ne soit pas complète car ne précisant pas l’ensemble des types de données qu’il est possible de définir dans les colonnes d’une table, elle vous donne malgré tout un bon aperçu de celles que vous pouvez utiliser pour stocker des données. Avec ces informations en mémoire, vous pouvez maintenant procéder à la création de deux tables à l’intérieur de votre base de donnée fundamentals (fondamentale), qui garde une trace des livres consultés. Nous souhaiterions en particulier pouvoir y faire figurer le titre du livre, son auteur, sa date de publication et le lieu de résidence de l’auteur (état). Pour des raisons que j’expliquerai plus loin, j’ai volontairement divisé authors (auteurs) et books (livres) dans deux tables séparées, chacune comprenant une colonne author_id (identifiant auteur) commun entre-elles.
Tableau 1. The books table (la table livres)
book_id |
author_id |
title |
pub_date |
|---|---|---|---|
| 1 | 1 | PHP Unleashed | 11-01-03 |
| 2 | 1 | PHP4 Programming | 10-01-02 |
| 3 | 2 | Cool Stuff | 03-23-02 |
| 4 | 3 | Another book | 02-01-01 |
Tableau 2. The authors table (la table auteurs)
author_id |
name |
state |
|---|---|---|
| 1 | John Coggeshall | MI |
| 2 | Joe Coolguy | AZ |
| 3 | Jennifer Author | KS |
Pour pouvoir créer ces tables dans notre base de données, nous allons à nouveau utiliser l’instruction CREATE (créer). Cependant, avant de pouvoir créer une où des tables, vous devez préciser à MySQL sur quelle base de données vous comptez travailler, vous pouvez faire cela en utilisant l’instruction USE (utiliser) comme indiqué ci-dessous :
mysql> USE fundamentals; Database Changed
Maintenant nous pouvons utiliser l’instruction CREATE pour créer la table books (livres) et la table authors (auteurs) :
mysql> CREATE TABLE books(
book_id INT AUTO_INCREMENT PRIMARY KEY,
author_id INT,
title VARCHAR(255),
pub_date DATE);
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE authors(
author_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
state VARCHAR(2));
Query OK, 0 rows affected (0.02 sec)
Ces deux séries d’instructions créent deux tables que nous utiliserons tout au long de ce cours. La table books (livres) contient quatre colonnes définies comme suit : un book_id (identifiant numérique de livre), un author_id (identifiant numérique d’auteur), un title (titre du livre) et une pub_date (date de publication de l’ouvrage). La table authors (auteurs) ne comprend que trois colonnes : un author_id (identifiant numérique d’auteur), un name (nom d’auteur) et un state (lieu de résidence de l’auteur).
Notez que les colonnes book_id (identifiant numérique de livre) et author_id (identifiant numérique d’auteur) ont des attributs AUTO_INCREMENT (auto-incrémentation numérique) et PRIMARY KEY (clé primaire) activés. L’attribut PRIMARY KEY (clé primaire) indique que deux des lignes d’une même colonne d’une table ne peuvent avoir la même valeur, chacune d’entre-elles est par ailleurs unique. Cela signifie que dans le cas de vos tables, les colonnes book_id (identifiant numérique de livre) et author_id (identifiant numérique d’auteur) doivent contenir un nombre entier absolument unique pour chacune de leurs lignes respectives. L’attribut AUTO_INCREMENT (auto-incrémentation numérique) confère automatiquement une valeur pour chaque ligne constituant la colonne. Lorsque des données sont insérées dans les tables, la première ligne reçoit la valeur 0 pour id, la valeur de la seconde ligne est de 1, la troisième 2 et ainsi de suite.
Remarque : bien que l’on soit ici pour présenter simplement les choses, l’attribut AUTO_INCREMENT (auto-incrémentation numérique) ne fonctionne que si la valeur NULL (nulle) est entrée dans la colonne concernée. Si vous spécifiez une valeur, elle sera utilisée à la place. En étant plus précis, si la valeur choisie est plus importante que la plus grande valeur déja présente dans la colonne, l’attribut AUTO_INCREMENT (auto-incrémentation numérique) va utiliser cette valeur plus un pour la prochaine ligne à insérer.
Pour voir précisément comment vos tables sont définies une fois qu’elles sont créées (c’est particulièrement utile lorsque vous avez oublié les détails correspondant à une table donnée), vous pouvez utiliser l’instruction DESC ou DESCRIBE (décrire). Voyez le résultat lorsque cette fonction est utilisée sur la table books (livres) :
mysql> DESC books; +-----------+--------------+-------------------+------+-----+---------+----------------+ | Field | Type | Collation | Null | Key | Default | Extra | +-----------+--------------+-------------------+------+-----+---------+----------------+ | book_id | int(11) | binary | YES | PRI | NULL | auto_increment | | author_id | int(11) | binary | YES | | NULL | | | title | varchar(255) | latin1_swedish_ci | YES | | NULL | | | pub_date | date | latin1_swedish_ci | YES | | NULL | | +-----------+--------------+-------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
Maintenant que ces tables ont été créées, vous avez besoin de remplir les tables avec les données qui sont proposées dans les tableaux 1 et 2. Utilisez l’instruction INSERT (insérer) pour procéder à l’entrée des données dans les tables.
mysql> INSERT INTO books VALUES(1, 1, "PHP Unleashed", "2003-11-01"); Query OK, 1 row affected (0.01 sec)
Répétez cette opération pour chaque ligne de la table books (livres).
mysql> INSERT INTO authors VALUES(1, "John Coggeshall","MI"); Query OK, 1 row affected (0.01 sec)
Répétez cette opération pour chaque ligne de la table authors (auteurs).
Remarque : Notez que pour la première insertion de valeurs, la colonne des dates se sert du format YYYY-MM-DD. C’est le format utilisé par la méthode standard pour insérer des dates.
Pour simplifier le processus d’insertion des données, vérifiez que vous avez entré les valeurs pour les colonnes book_id (identifiant numérique de livre) et author_id (identifiant numérique d’auteur) respectivement dans chaque table au lieu d’avoir profité de l’attribut AUTO_INCREMENT (auto-incrémentation numérique). Il est important pour les futurs développements du cours que vous ayez exactement les mêmes tables que celles présentées dans les tableaux 1 et 2.
A ce point précis, vous devez disposer d’une base de données appelée fundamentals (fondamentale) qui contient deux tables nommées books (livres) et authors (auteurs). Pour contrôler celà, vous pouvez demander au serveur de montrer toutes les tables d’une base de donnée avec l’instruction SHOW (montrer).
mysql> SHOW TABLES; +-----------------------------+ | Tables in fundamentals | +-----------------------------+ | books | | authors | +-----------------------------+ 2 rows in set (0.03 sec)
Considérant que tout c’est passé comme prévu, nous sommes alors prêts pour démarrer l’interrogation afin d’obtenir des données.
Récupérer des données à partir des tables
Maintenant que nous avons des données dans notre base de données, récupérerons-en quelques-unes. Le fait d’obtenir des données à partir d’une base de données est appelé un “result set” (ensemble de résultats), utilisez l’instruction SELECT (sélectionner) :
mysql> select * from books; +---------+-----------+------------------+------------+ | book_id | author_id | title | pub_date | +---------+-----------+------------------+------------+ | 1 | 1 | PHP Unleashed | 2003-11-01 | | 2 | 1 | PHP4 Programming | 2002-10-01 | | 3 | 2 | Cool Stuff | 2002-03-23 | | 4 | 3 | Another Book | 2001-02-01 | +---------+-----------+------------------+------------+ 4 rows in set (0.00 sec)
Dans ce cas, l’interrogation précédente a retourné un ensemble de résultats contenant toutes les données contenues dans la table books (livres). Comment cela fonctionne-t-il exactement ? Bien que l’instruction SELECT (sélectionner) est bien plus complexe que cela, la syntaxe générale est celle-ci :
SELECT <columns> FROM <tables> ... (ajouter des options)
<columns> (colonnes) il peut contenir tout nom de colonne qui existe effectivement dans les tables précisées par <tables> (tables) ou toute autre fonction MySQL valide (plus que celles vues jusqu’à maintenant). Si vous désirez obtenir uniquement une liste de tous les titres des livres contenus dans la base de données, vous pouvez utiliser l’interrogation suivante :
mysql> SELECT title FROM books; +------------------+ | title | +------------------+ | PHP Unleashed | | PHP4 Programming | | Cool Stuff | | Another Book | +------------------+ 4 rows in set (0.00 sec)
Vous pouvez également sélectionner deux colonnes à partir de la table books (livres) en séparant leurs noms respectifs par une virgule :
mysql> SELECT title, pub_date FROM books; +------------------+------------+ | title | pub_date | +------------------+------------+ | PHP Unleashed | 2003-11-01 | | PHP4 Programming | 2002-10-01 | | Cool Stuff | 2002-03-23 | | Another Book | 2001-02-01 | +------------------+------------+ 4 rows in set (0.00 sec)
A suivre…
Terminé aujourd’hui pour les notions fondamentales de PHP ! Comme c’est souvent le cas avec une introduction d’un nouvel aspect du PHP, il n’y a réellement aucune notion de PHP présente ici. Malheureusement pour ceux qui seraient impatients d’utiliser les ensembles RDBMS, ce sera seulement dans un prochain article que je pourrai traiter comment utiliser SQL à partir de PHP. Comme pour tous les autres sujets majeurs, il y a beaucoup plus à apprendre pour utiliser efficacement l’ensemble des possibilités. En fait, il faut attendre encore deux articles de “pur MySQL” avant que je puisse débuter mon introduction à l’extension à MySQL pour le PHP ! En attendant, si vous voulez en savoir plus à propos de MySQL, vous pourrez trouver votre bonheur dans la documentation complète (et probablement beaucoup plus que vous ne souhaitez) proposée par le site MySQL.
Textes originaux en anglais sur O’Reilly : MySQL Crash Course par John Coggeshall
Chargement
Commentaires récents