Les bases de données/Version imprimable

Les bases de données

Une version à jour et éditable de ce livre est disponible sur Wikilivres,
une bibliothèque de livres pédagogiques, à l'URL :
https://fr.wikibooks.org/wiki/Les_bases_de_donn%C3%A9es

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la Licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans Texte de dernière page de couverture. Une copie de cette licence est incluse dans l'annexe nommée « Licence de documentation libre GNU ».

Le vocabulaire de base des BDD

Pour faire simple, la gestion des données mémorisées sur un support quelconque (disque dur, DVD, CD-ROM, …) demande de gérer deux choses bien différentes : le stockage des données et leur manipulation. Pour commencer, nous allons nous intéresser au stockage des données, comment celles-ci sont organisées pour être mémorisées durablement. Les concepts que nous allons voir dans cette page sont les bases de ce cours et les connaître est très important. La plupart des termes que nous allons voir forment le vocabulaire minimal qu'il faut savoir pour aborder ce domaine.

Abréviations

BDD (ou BD)

Base de données.

SGBD

Système de gestion de base de données.

SQL

Standard query language : Langage de requête standard.

Tables, enregistrements et attributs

Que ce soit dans un fichier ou dans une base de données, les données à mémoriser sont ce qu'on appelle des enregistrements. Ces enregistrements mémorisent toutes les informations liées à un objet, une personne, une entité, une chose. Prenons le cas d'une entreprise qui veut mémoriser la liste des commandes qu'elle a passé à ses fournisseurs : chaque commande sera mémorisée dans l'ordinateur dans un enregistrement, qui contiendra toutes les informations liées à la commande. Prenons maintenant l'exemple d'un établissement scolaire qui veut mémoriser la liste de ses élèves : chaque élève sera mémorisé par un enregistrement, qui contiendra toutes les informations sur l'élève en question. Et enfin, prenons comme dernier exemple un biologiste qui veut établir la liste de toutes les espèces animales découvertes à ce jour : chaque espèce découverte se verra attribuer un enregistrement, qui contiendra toutes les informations sur l'espèce.

Les enregistrements sont des équivalents des structures dans les langages de programmation comme le C ou le C++.

Enregistrements et attributs

Comme les exemples plus haut vous l'ont certainement fait remarquer, chaque entité, chaque enregistrement, est décrite par plusieurs informations : on a souvent besoin de mémoriser plusieurs informations distinctes pour chaque enregistrement.

Par exemple, une entreprise aura besoin de stocker, pour chaque client :

• son nom ;
• son prénom ;
• son adresse ;
• ses commandes en cours ;
• etc.

Comme autre exemple, un établissement scolaire devra mémoriser pour chaque élève :

• son nom ;
• son prénom ;
• son adresse ;
• son âge ;
• sa classe ;
• sa filière d'étude ;
• etc.

Comme dernier exemple, un garage aura besoin de mémoriser, pour chaque voiture :

• son numéro d'immatriculation ;
• sa marque ;
• son modèle ;
• etc.

Toutes ces informations sont des informations distinctes, mais qui appartiennent à la même personne, au même objet, à la même entité : on doit les regrouper dans un seul enregistrement. Pour cela, chaque enregistrement est un regroupement d'attributs, chaque attribut étant une information élémentaire, qui ne peut pas être décomposée en informations plus simples. Ces attributs sont équivalents aux variables des langages de programmation. Définir un enregistrement, c'est simplement définir l'ensemble des attributs qu'il contient : il faut préciser son type et sa valeur. Mais il faudra aussi lui donner un nom, pour pouvoir le récupérer.

Type d'un attribut

Ces attributs peuvent prendre des valeurs bien précises, définies à l'avance : les valeurs que peuvent prendre chaque attribut sont déterminés par ce qu'on appelle le type de l'attribut. Les types autorisés par les systèmes de gestion de données sont ceux couramment utilisés en informatique, à savoir : les nombres entiers et à virgule, les caractères et autres formes de texte, les booléens, etc.

Reprenons les exemples vus plus haut, pour voir quel est le type adéquat pour mémoriser chaque attribut. Pour l'exemple d'une entreprise qui a besoin de mémoriser la liste de ses clients, voici le type de chaque attribut :

• son nom : chaîne de caractère ;
• son prénom : chaîne de caractère ;
• son adresse : chaîne de caractère ;
• le numéro de la commande : nombre entier ;
• etc.

Prenons maintenant l'exemple d'une infirmerie scolaire qui veut mémoriser des informations médicales obtenues lors des visites médicales. Celui-ci doit mémoriser, pour chaque élève :

• son nom : chaîne de caractère ;
• son prénom : chaîne de caractère ;
• son numéro de sécurité sociale : nombre entier ;
• son adresse : chaîne de caractère ;
• sa taille : nombre à virgule ;
• son poids : nombre à virgule ;
• son âge : nombre entier ;
• son groupe sanguin : chaîne caractère ;
• son groupe rhésus : booléen ;
• sa tension artérielle : nombre à virgule ;
• etc.

Comme l'exemple de l'établissement scolaire qui doit mémoriser pour chaque élève :

• son nom : chaîne de caractère ;
• son prénom : chaîne de caractère ;
• son adresse : chaîne de caractère ;
• son âge : nombre entier ;
• sa classe : chaîne de caractère ;
• sa filière d'étude : chaîne de caractère ;
• etc.

Comme dernier exemple, un garage aura besoin de mémoriser, pour chaque voiture :

• son numéro d'immatriculation : chaîne de caractère ;
• sa marque : chaîne de caractère ;
• son modèle : chaîne de caractère ;
• etc.

Valeur nulle

Dans certains cas, il arrive que certaines informations ne soient pas connues lorsqu'on crée l'enregistrement. Par exemple, quand un élève rentre dans une école, on peut très bien ne pas connaître son établissement de l'année précédente. Et des cas similaires sont légions : soit l'information est inconnue, soit elle n'existe pas. On doit alors remplir l'attribut correspondant par une valeur qui indique que l'attribut a une valeur inconnue, que l'attribut n'a pas été rempli. Cette valeur est appelée la valeur NULL.

Par exemple, l'exemple suivant, qui correspond aux informations d'une personne, est parfaitement possible :

• nom de famille : Braguier ;
• premier prénom : Jean-Paul ;
• second prénom : NULL ;
• troisième prénom : NULL ;
• etc.

Modèles relationnel et de fichiers

D'ordinaire, les entreprises et autres utilisateurs ont besoin de mémoriser de grandes quantités d'informations basées sur un même moule. Par exemple, les informations à mémoriser pour chaque élève d'un établissement scolaire seront identiques : tous les élèves ont un âge, un nom, un prénom, etc. Dans une liste de commandes passées par des clients, toutes les commandes sont définies par les mêmes informations : quel est le client, le produit commandé, la quantité à fournir, etc. En clair, tous les enregistrements d'une même liste ont des attributs semblables : ils ont le même type, le même nom et la même signification, seul leur valeur change. Elles ont besoin de mémoriser des listes d'enregistrements. Généralement, les informations sont classées dans plusieurs listes, par souci de simplification. Par exemple, si on prend le cas d'une librairie, celle-ci rangera les informations liées aux commandes de livres dans une liste séparée des informations sur les clients, et ainsi de suite : on aura une liste de client, une liste de commande de livre, une liste pour les livres en rayon, etc.

Les modèles de données

Illustration du stockage par fichiers.

Dans les cas les plus simples, on peut mémoriser chaque liste dans un fichier : les enregistrements y sont placés les uns à la suite des autres. Mais l'utilisation de fichiers a un défaut : le traitement de fichiers doit être programmé de zéro. En effet, les langages de programmation ne permettent que de récupérer les informations d'un fichier unes par unes, ou d'en ajouter à la fin du fichier, mais sans plus : on ne peut pas dire au langage de programmation qu'on ne veut récupérer que les données qui respectent une certaine condition, ni fusionner le contenu de deux fichiers, etc. Un autre défaut est que ce modèle ne permet pas de représenter les relations entre listes d'enregistrements. Et ces relations peuvent être très importantes pour interpréter les données ou permettre de les manipuler efficacement. Le modèle à base de fichier se contente de placer chaque liste dans un fichier, le programme devant connaître de lui-même les relations entre données. D'autres systèmes de gestion des données permettent cependant de représenter ces liens avec une grande efficacité.

Pour pouvoir faire cela nativement, sans avoir à programmer un morceau de programme qui le fasse à notre place, on peut utiliser ce qu'on appelle des bases de données. Ces bases de données sont en quelque sorte des fichiers, mais qui sont gérées par un logiciel qui fait le café : ces logiciels sont appelés des systèmes de gestion de base de données, ou SGBD. Avec ces SGBD, le rangement des informations dans un fichier n'est pas spécifié par le programmeur : le SGBD s'occupe de gérer tout ce qui a rapport avec la manière dont les données sont rangées sur le disque dur. En plus, les bases de données effectuent automatiquement des opérations assez puissantes et peuvent faire autre chose que lire les informations unes par unes dans un fichier, les modifier, ou y ajouter des informations : on peut décider de récupérer les données qui respectent une certaine condition, de fusionner des données, etc. Mais leur intérêt principal est de modéliser les relations entre chaque liste d'enregistrement efficacement, d'une manière qui permet de traiter les données rapidement.

Ces bases de données utilisent des méthodes assez distinctes pour représenter les relations entre informations, méthodes qui permettent de distinguer les BDD arborescentes, en réseau, relationnelles, déductives et orientées-objet. Les plus simples sont de loin les BDD en réseau et arborescentes. Celles-ci codent les relations entre listes par des pointeurs, chaque enregistrement pouvant faire référence à un autre, situé dans une autre liste. La différence entre BDD en réseau et arborescentes tient dans le fait que certaines relations sont interdites dans les BDD arborescentes, qui ont une forme d'arbre (non-binaire, le plus souvent). Le modèle relationnel sera vu plus loin : c'est lui que nous étudierons dans ce cours. Le fait est que toutes les BDDs actuelles utilisent le modèle relationnel. Les modèles orientés-objet et déductifs sont plus difficiles à décrire, du moins pour qui ne sait pas ce que sont les langages de programmation orientés-objet ou logiques.

BDD à pointeurs

BDD sans pointeurs

Le modèle relationnel

Une base de donnée relationnelle permet de gérer des listes de données, qui sont appelées des tables, sont organisées en lignes et en colonnes. Chaque ligne mémorise un enregistrement, la succession des lignes permettant de mémoriser plusieurs enregistrements les uns à la suite des autres, en liste. Comme dit plus haut, les enregistrements d'une liste ont des attributs de même nom et de même type, seul leur valeur changeant : les attributs de même nom et type sont placés sur une même colonne, par simplicité. Évidemment, tous les enregistrements d'une table ont exactement les mêmes attributs, ce qui fait que toutes les lignes d'une table ont le même nombre de colonnes.

Exemple de table simplifiée qui mémorise une liste de personnes

Nom	Prénom	Age	Ville
Dupont	Jean	25	Lille
Marais	Sophie	45	Paris
Dupond	Mathieu	35	Paris
Salomé	Saphia	24	Lyon
Queneau	Raymond	53	Marseille
Tepes	Vlad	29	Lille

Il existe un vocabulaire assez précis pour nommer les enregistrements, colonnes et autres particularités d'une table. Ceux-ci sont illustrés dans les deux schémas ci-dessous.

Figure structure relationnelle.

Une formulation équivalente à la précédente utilise les termes suivants :

Table relationnel

Évidemment, ces tables ne serviraient strictement à rien si on ne pouvait pas récupérer leur contenu : on peut récupérer une ligne dans la table sans que cela pose problème. On peut aussi ajouter des lignes dans une table, ainsi qu'en supprimer : si on prend l'exemple d'une liste de commandes tenue par une entreprise, on peut ajouter des commandes quand un client passe un contrat, ou en supprimer une fois la commande honorée. Et enfin, on peut modifier certaines lignes, quand celles-ci contiennent des données erronées ou dont la valeur a changé : si on prend une liste d'employé d'une entreprise, on peut vouloir changer le nom d'une employée suite à un mariage, par exemple. Ainsi, gérer des données dans une table demande de pouvoir faire quatre opérations de base :

• CREATE (créer) : créer une nouvelle ligne ;
• READ (lecture) : récupérer une ligne dans la table ;
• UPDATE (modifier) : modifier le contenu d'une ligne ou d'un attribut ;
• DELETE (supprimer) : supprimer une ligne devenue inutile.

Ces quatre opérations sont regroupées sous l'acronyme CRUD.

Remarque de fin

Il est évident qu'il vaut mieux éviter de mémoriser des informations inutiles dans une base de données. Par données inutiles, on veut dire : données qui peuvent se déduire à partir des données déjà présentes dans l'enregistrement. Par exemple, le prix d'une commande client peut se déduire à partir du prix unitaire d'un produit et des quantités commandées. Dans ces conditions, mieux vaut éviter de mémoriser ces données facilement déductibles : le programme qui manipulera les informations de la base de donnée les calculera lui-même, ce qui est très rapide (plus que de lire les données depuis une BDD ou un fichier). Une fois cela fait, les données de la table forment un ensemble minimal de données. Mais toutes n'ont pas le même poids, le même intérêt.

Conclusion

Ce chapitre s'est limité au vocabulaire de base, le vocabulaire des bases de données étant assez important. Pour vous en rendre compte, vous pouvez avoir une petite idée des termes du domaine en allant en voir la liste, disponible sur wiktionnaire, via ce lien :

• Lexique en français des bases de données.

Une partie des termes de cette liste seront vus dans les chapitres suivants.

Les clés primaires et secondaires

En théorie, l'ajout ou la modification d'un enregistrement dans la table ne doit pas pouvoir permettre la présence de doublons : chaque ligne n'est présente que dans un seul exemplaire dans la table. Dans la pratique, c'est une autre paire de manche : de nombreux SGBD acceptent d'avoir des lignes en plusieurs exemplaires dans une table sans problème. Mais nous allons passer cette contrainte sous le tapis pour le moment. Tout ce chapitre va répondre à cette simple question : comment le SGBD peut-il éviter les doublons ?

Clés primaires et secondaires

Une solution simple consisterait à interdire l'ajout d'une ligne si on trouve une ligne identique dans la table. Mais cette solution pose un léger problème quand les valeurs NULL sont autorisées dans la table : on peut ajouter une ligne identique à une autre dans la table, si ce n'est qu'un champ autrefois à NULL a été modifié. Or, je rappelle que NULL peut signifier que la valeur de l'attribut n'était pas connue lors de la création de la ligne. Dans ce cas, rien ne nous garantit que les deux lignes correspondent à des clients/objets/personnes différents : il se peut très bien que la ligne ajoutée corresponde à la même personne ou au même objet, mais pour lequel on dispose de plus d'informations qu'avant.

Pour éviter ce genre de désagrément, et quelques autres que nous aborderons plus tard, on doit trouver un mécanisme pour identifier chaque donnée dans la table. Ce mécanisme consiste à choisir un ensemble de colonnes qui détermine un objet ou une personne unique dans la table : on sait que si ces ensembles d'attributs ont la même valeur dans deux lignes, alors les deux lignes correspondent à une même donnée. Cet ensemble de colonnes est ce qu'on appelle une clé. Les clés peuvent très bien contenir une seule colonne, mais c'est tout sauf systématique. Dit autrement, chaque personne ou objet donnera une seule et unique valeur pour chaque attribut de la clé : deux données différentes auront au moins un attribut dont la valeur sera différente. Par exemple, prenons le cas d'un établissement scolaire qui établit une liste d'élèves. Il arrivera certainement que deux élèves aient le même prénom ou le même nom de famille : dans ce cas, l'ensemble des colonnes nom et prénom ne peuvent pas servir de clé primaire.

Un même enregistrement peut très bien avoir plusieurs clés. Dans ce cas, on choisit le plus souvent une clé parmi toutes les autres, qui sera considérée comme une meilleure clé que les autres : c'est la clé primaire. Les autres clés seront alors appelées des clés secondaires ou clés alternatives. Petit détail : dans une clé primaire, les attributs ne peuvent pas être à NULL. Dit autrement, seules les colonnes qui n'appartiennent pas à la clé primaire peuvent contenir des NULL. Généralement, on peut préciser au SGBD quelles sont les colonnes qui peuvent servir de clé primaire : tout introduction d'un NULL dans un attribut de cette colonne se soldera par un message d'erreur et un refus d'insérer la ligne dans la table. Cette contrainte est ce qu'on appelle la contrainte de clé.

Clés artificielles

Certaines clés ne sont même pas dérivées des informations présentes dans la ligne : on peut très bien ajouter un identifiant sans signification pour chaque ligne de la table. Par exemple, on peut attribuer arbitrairement un identifiant numérique pour chaque élève d'un établissement scolaire, à une personne dans la population (pensez au numéro de sécurité sociale), à tout livre publié (ISBN), ou à toute autre forme de donnée présente dans une table. Ces identifiants attribués arbitrairement sont appelés des clés artificielles, et sont généralement des nombres entiers.

Dépendances fonctionnelles

Maintenant, on peut se demander comment déterminer quelles sont les colonnes destinées à former une clé (primaire ou non). Une solution simple est de faire marcher son cerveau et d'improviser intelligemment : cela peut fonctionner avec un peu d'expérience. Mais il existe des techniques plus mécaniques pour trouver les clés primaires. Celles-ci utilisent ce qu'on appelle des dépendances fonctionnelles. Deux attributs (ou groupes d'attributs) ont une dépendance fonctionnelle quand la connaissance de l'un permet de déterminer les valeurs de l'autre.

Exemples de dépendances fonctionnelles

Illustrons ce concept avec un exemple. Nous allons prendre le cas d'une entreprise qui utilise une BDD pour garder trace de ses ventes. La table des ventes contient cinq colonnes : le client, le produit vendu, le prix unitaire (celui d'un produit), la quantité commandée, et le prix total (pour l'ensemble de la commande). Il existe plusieurs dépendances fonctionnelles dans cette table. Premièrement, une fois qu'on connaît le produit, on connaît son prix de vente : un produit n'a qu'un seul prix unitaire. Deuxièmement, le prix total est déterminé par le prix unitaire et de la quantité vendue. Prenons maintenant un exemple plus complexe. Là encore, nous reprenons l'exemple d'une entreprise qui vend des produits finis à ses clients. La table contient les colonnes suivantes :

• IdClient : un numéro de client (une clé artificielle qui identifie un client) ;
• Nom : le nom du client ;
• Adresse : l'adresse du client pour les livraisons ;
• NuméroCommande : un numéro de commande ;
• Date : la date de commande ;
• NuméroProduit : le numéro du produit vendu au client ;
• QuantitéVendue : la quantité de produits vendus ;
• Prix : le prix unitaire de la commande.

Cette fois-ci, les dépendances fonctionnelles sont plus nombreuses. Premièrement, le numéro de commande permet de déterminer le client final, la date de commande, la quantité vendue. Une commande est reliée à un seul client, à une seule date, à un numéro de produit et à une quantité vendue bien précise. Ensuite, la connaissance du numéro de produit permet de déterminer son prix, comme pour la première table. Et enfin, la connaissance du numéro de client permet de déterminer son nom et son adresse.

Maintenant, prenons l'exemple d'une université qui veut gérer ses élèves. Pour rappel, à la fac, un professeur ne s'occupe généralement que d'un cours bien précis à chaque semestre, ce qui sera le cas dans cet exemple. Celle-ci a, dans sa base de données, une table qui associe un étudiant , un professeur, un cours, et un semestre. Cette table permet de savoir que lors de tel semestre, tel étudiant est censé être dans tel cours avec tel professeur. On peut facilement remarquer quelques dépendances fonctionnelles : la connaissance du semestre et du cours permet de déterminer le professeur, et inversement.

Détermination des clés depuis les dépendances

Il est possible de représenter graphiquement ces dépendances fonctionnelles avec ce qu'on appelle un graphe, un ensemble de machins reliés entre eux par des flèches. Dans notre cas, les machins en question seront les nom des attributs de la table, et les flèches indiqueront les dépendances fonctionnelles entre deux attributs. Une flèche d'attribut A vers un autre attribut B indique qu'il y a une dépendance fonctionnelle de A vers B : la connaissance de A détermine celle de B. Les clés primaires ou secondaires d'une table peuvent se déduire de ce graphe de dépendances. Les attributs de la clé seront ceux qui ne sont déterminés par aucun autre attribut : la connaissance de ces attributs permet de déduire tous les autres. Sur le graphe, les flèches doivent partir de ces attributs, mais pas en sortir. Les attributs d'où ne sortent ni ne partent de flèches font aussi partie de la clé.

Les liens entre tables

Maintenant que l'on sait stocker des données dans une base de données, il nous reste à stocker les liens entre ces informations. Prenons l'exemple d'une entreprise qui veut mémoriser la liste des commandes qu'elle doit honorer à ses clients. Chaque commande a été passée par un client en particulier, ce qui fait que l'on doit ajouter une table pour mémoriser les clients, en plus de la table pour les commandes. Mais comment savoir quel client a passé telle ou telle commande ? De manière plus générale, comment gérer les cas où une ligne/entité dans une table fait référence à des informations situées dans une autre table ?

Clés étrangères

Les bases de données actuelles, appelées bases relationnelles, utilisent un stratagème assez intéressant pour gérer ces liens entre enregistrements. Ces liens ne sont pas mémorisés dans la base de donnée, mais sont déduit des informations contenues dans les enregistrements, en faisant un bon usage des clés primaires. Un enregistrement A peut faire référence à un enregistrement B en contenant la clé primaire de B. La copie de la clé primaire de B, contenu dans l'enregistrement A est appelée une clé étrangère. La table qui contient l'enregistrement A est appelée la table enfant, tandis que la table qui contient l’enregistrement B auquel il est fait référence est appelée la table parente. On parle de clé étrangère pour dire que la clé est étrangère à la table enfant, et qu'elle fait référence à un enregistrement dans la table parente.

Clé étrangère : exemple.

L'utilisation de clés étrangère pose toutefois un problème quand on peut supprimer ou ajouter des enregistrements dans une table : comment garantir que les clés étrangères feront bien référence à un enregistrement valide ? Imaginez que l'enregistrement auquel il est fait référence soit supprimé : la référence sera invalide. Garantir que toute référence vers une table parente soit valide est une contrainte qui porte un nom : on parle d'intégrité référentielle. Pour éviter que cette contrainte soit violée, le SGBD utilise divers mécanismes de propagation de contraintes, qui définissent ce que l'on doit faire dans une table enfant lors de la suppression d'un enregistrement dans une table parent. On peut choisir comment le SGBD doit se comporter lors d'une telle suppression : suivant la table, on peut lui indiquer quoi faire.

Comportement	Action
CASCADE	La première méthode consiste à supprimer tous les enregistrements qui contiennent la clé étrangère, tous les enregistrements qui font référence à celui supprimé. Ainsi, quand on supprime un enregistrement dans une table, tous les enregistrements dans les autres tables qui y font référence sont supprimés : la suppression est propagée dans les tables enfant.
SET NULL et SET DEFAULT	Une autre possibilité est de ne pas supprimer les enregistrements enfants, mais de modifier leur clé étrangère. Dans le cas le plus simple, ses attributs sont mis à NULL : c'est le comportement nommé SET NULL. Dans d'autres cas, on peut demander à ce que la clé étrangère soit mise à une valeur par défaut, pas forcément nulle : c'est le comportement SET DEFAULT.
RESTRICT	Une dernière méthode de propagation consiste à interdire la suppression d'un enregistrement si celui-ci est référencé dans une autre table.

Types de liens

Dans les grandes lignes, on peut classer les liens entre deux tables en trois catégories :

• les correspondances un à un ;
• les correspondances un à plusieurs ;
• les correspondances plusieurs à plusieurs.

Correspondances un à un

Dans le premier cas, chaque enregistrement de la table enfant fait référence à un seul enregistrement dans la table parent, et réciproquement. Pour donner un exemple, nous allons prendre une table "pays" et une table "capitale" : chaque pays n'a qu'une seule capitale, et une ville n'est capitale que d'un seul pays.

Relation 1 a 1

Pour enregistrer ces relations entre tables, il suffit de placer la clé étrangère dans la table appropriée. Dans ce cas, on sélectionne une des deux tables (on peut choisir celle qu'on veut) et celle-ci contiendra les clé étrangère qui pointeront vers l'autre table.

Correspondance 1 à 1

Correspondances un à plusieurs

Le second cas correspond à la situation où :

• plusieurs lignes dans une table enfant font référence à un unique enregistrement dans une autre table ;
• mais où la réciproque n'est pas vraie : les lignes de l'autre table font systématiquement référence à une seule entité dans la première table.

Pour donner un exemple, nous allons prendre les deux tables nommée "mère" et "enfant" : une mère peut avoir plusieurs enfants, mais la réciproque n'est pas vraie (un enfant n'a qu'une seule mère). Comme autre exemple, on peut donner la relation entre les deux tables "classe" et "élève" : une classe contient plusieurs élèves, mais tout élève n'est que dans une seule classe bien précise.

Relation n a 1

Pour enregistrer ces relations entre tables, il suffit de placer la clé étrangère dans la table appropriée. Reste que cette fois-ci, on ne peut pas choisir la table qui contiendra la clé étrangère : ce sera obligatoirement une des deux tables, et jamais l'autre. Plus précisément, si on a :

• deux tables nommées A et B ;
• chaque ligne de A pointe vers plusieurs lignes de B ;
• chaque ligne de B pointe vers une seule ligne de A.

Dans ce cas, on doit ajouter une colonne dans la table B pour accueillir les clés étrangères.

Correspondance 1 à N

Par exemple, reprenons l'exemple avec les deux tables "mère" et "enfant" : il faudra placer une colonne pour les clés étrangères dans la table "enfant". Supposons que l'on ne fasse pas comme cela : il faudra rajouter plusieurs colonnes dans la table "mère" : une pour chaque enfant potentiel. On voit bien que l'on devra ajouter beaucoup plus de colonnes. Et c'est sans compter le cas où le nombre de colonnes à ajouter n'est pas borné : combien de colonnes devrait-t-on ajouter pour l'exemple avec les tables "mère" et "enfant" ? Se limiter à 3 ou 4 ne permettrait pas à la base de fonctionner avec ces familles nombreuses de 5, voire 10/15 enfants.

Correspondances plusieurs à plusieurs

Enfin, dans le dernier cas, chaque enregistrement dans une table peut pointer vers plusieurs enregistrements dans l'autre table, et cela vaut pour les deux tables. Un exemple est la relation entre une table "livre" et une table "auteur" : un auteur peut avoir écrit plusieurs livres, de même qu'un livre peut avoir été rédigé par plusieurs auteurs. Dans ce cas, les clés étrangères ne sont pas mémorisées dans une des deux tables. Elles sont mémorisées dans une table à part, qui mémorise les liens entre les deux tables. Pour chaque référence <enregistrement de la première table <-> enregistrement dans la seconde table>, cette table mémorise le couple <clés primaire de la première table - clé primaire de la seconde table>.

Correspondance N vers N

Modélisation d'une base de données

Maintenant que l'on sait comment est constituée une base de données, on peut passer à un peu de pratique. Dans ce chapitre, nous allons apprendre à créer ce qu'on appelle le schéma conceptuel d'une base de données. Ce schéma décrit les tables de la BDD ainsi que leurs liens. La description de la table est relativement simple : elle se borne à leur donner un nom, à décrire chaque colonne (nom et type) et à dire quelles sont les clés primaires. Les liens ne sont pas traduit en clés étrangères, qui ne sont d'ailleurs pas présentes dans ce schéma. Ce schéma conceptuel n'est qu'une première étape dans la modélisation d'une base de données. La seconde étape consiste à prendre le schéma et traduire les liens entre tables sous la forme de clés étrangères : on obtient alors le modèle logique de la base de données. Une fois cela fait, on peut traduire le tout dans un langage de programmation qui permet de gérer des bases de données (le SQL, par exemple) : c'est la troisième étape.

Schéma du processus de conception d'une base de donnée

Dans ce chapitre, nous allons surtout voir les deux premières étapes. Le schéma d'une BDD s'écrit avec un certain symbolisme, qu'il faut connaître par cœur, et qui décrit les tables et leurs relations. C'est ce symbolisme que nous allons décrire dans ce chapitre.

Tables

Les tables sont représentées par des rectangles, décomposées en trois cadres : un pour le nom de la table, un autre pour les attributs, et un autre pour les clés primaires. On note le nom de la table au sommet du rectangle, à l'intérieur du premier cadre. Les attributs sont notés à l'intérieur du rectangle, dans le cadre en-dessous du nom de la table : on précise non seulement leur nom, mais aussi leur type. Les clés primaires sont notées dans le dernier cadre, et précisent quels sont les attributs de la clé : ils font référence à des attributs décrits dans le cadre au-dessus.

Schéma d'une BDD - tables

Relations

Les relations entre tables sont indiquées par une flèche ou un trait entre deux tables. Le type de la relation (correspondance un à un, un à plusieurs, plusieurs à plusieurs), n'est pas indiqué directement sur le trait qui relie les deux tables. Par contre, ce qu'on appelle la cardinalité de la relation le sera. Cette cardinalité porte sur la relation entre une table A et une autre table B, et indique à combien de lignes de B fait référence une ligne de A. Il existe une cardinalité maximale et une minimale.

• Le premier cas est celui de la correspondance un vers un : chaque ligne de A pointe vers une ligne de B, et seulement une seule. Dans ce cas, la cardinalité est de 1, que ce soit pour la cardinalité minimale ou maximale.
• Le second cas est une petite variante du premier cas : il se peut que des lignes de A ne pointent pas vers une ligne de B. Dans ce cas, la cardinalité maximale ne change pas : elle est de 1. Mais la cardinalité minimale est de 0.
• Le troisième cas est celui de la correspondance un vers plusieurs : chaque ligne de A pointe vers au moins une ligne de B, potentiellement plusieurs. Dans ce cas, la cardinalité maximale n'est pas connue, mais on sait qu'elle est supérieure à 1. La cardinalité minimale est de 1.
• Le quatrième cas est une variante du cas précédent, sauf qu'il est possible qu'une ligne de A ne pointe pas vers une ligne de B.

Pour résumer, la cardinalité minimale est soit de 0, soit de 1, tandis que la maximale est soit de 1 soit de plusieurs. Ces cardinalités doivent être indiquées sur le trait de la relation, séparées par une virgule : la cardinalité minimale doit être indiquée avant la maximale. Attention : si on doit indiquer les cardinalités pour la liaison de A vers B, il faut aussi indiquer celle de B vers A sur le trait.

Schéma d'une BDD - relations

SQL : Data Description Language

Nous avons vu beaucoup de théorie dans les chapitres précédents. Il est temps de passer à un peu plus de pratique : nous allons aborder les bases du langage SQL. Pour faire simple, ce langage permet de gérer des bases de données. Ce langage permet d'envoyer des ordres à un SGBD, ces ordres permettant de créer des tables, d'y insérer des données, de les consulter, ou de les supprimer. On divise ce langage SQL en deux sous-langages :

• le SQL Data Description Language, qui permet de créer des tables et de définir comment seront stockées les données dans la base de données ;
• et le SQL Data Manipulation Language, qui permet de créer, consulter et supprimer le contenu des tables.

Tables, enregistrements et attributs

Créer une table est possible en utilisant l'expression suivante :

create table nom_de_la_table
(
    /* liste des attributs (colonnes) de la table, séparés par des virgules */
) ;

Pour définir les colonnes des tables, il suffit de préciser quels sont leurs noms et leur type.

Nom

Le nom des attributs d'une table est soumis à quelques règles relativement strictes. Premièrement, il ne peut contenir que des lettres, des chiffres, et les signes $, _, et # : pas d'espaces, pas d'accents, etc. De plus, un nom d'attribut ne peut pas commencer par un chiffre. De plus, il faut signaler que les lettres peuvent être écrites en majuscules et minuscules : cela ne change rien : les noms TAUX et taux sont strictement équivalent et référent au même attribut.

Type

En plus de préciser le nom de l'attribut, il faut aussi préciser son type. SQL ne permet de définir que quelques types bien précis, qui sont fréquemment rencontré dans les autres langages de programmation. Cela comprend les booléens, les nombres entiers et à virgule, les chaînes de caractères et quelques autres. À cela, il faut ajouter les données temporelles, comme des dates, des intervalles de temps, etc. Pour simplifier franchement, les types les plus utilisés sont les suivants :

• BOOLEAN pour les booléens ;
• INT pour les nombres entiers ;
• FLOAT pour les nombres à virgule ;
• VARCHAR pour les chaînes de caractères ;
• DATE et DATETIME pour les données temporelles.

Pour les curieux, la liste complète des types supportés est donnée dans le tableau ci-dessous.

SQLdatatype

Pour préciser le type lors de la déclaration, celui-ci est indiqué à la suite du nom de l'attribut dans la requête CREATE. Un exemple est donné ci-dessous, avec les types des attributs les plus courants.

create table PERSONNE
(
    NOM varchar ,
    PRENOM varchar ,
    ADRESSE varchar ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,
    DATE_NAISSANCE date
) ;

Entiers

L'ensemble des types entiers est détaillé dans le tableau ci-dessous.

Type	Minimum	Maximum
TINYINT	-128	127
SMALLINT	-32768	32767
MEDIUMINT	-8388608	8388607
INT	-2147483648	2147483647
BIGINT	9223372036854775808	9223372036854775807

On peut préciser qu'on peut placer le mot-clé UNSIGNED devant ces types pour préciser qu'on ne veut mémoriser que des nombres positifs, et pas des nombres négatifs. Dans ce cas, les bornes des types deviennent celles indiquées dans le tableau ci-dessous.

Type	Minimum	Maximum
UNSIGNED TINYINT	0	256
UNSIGNED SMALLINT	0	65536
UNSIGNED MEDIUMINT	0	16777216
UNSIGNED INT	0	4294967296
UNSIGNED BIGINT	0	18446744073709551615

Nombres à virgule

L'ensemble des types pour les nombres à virgules est détaillé dans le tableau ci-dessous.

Type	Description
NUMERIC et DECIMAL	S'utilise avec deux paramètres : le nombre de chiffres du nombre, et le nombre de chiffres après la virgule. On peut ainsi préciser qu'on souhaite mémoriser un nombre de 16 chiffres, dont maximum 5 après la virgule. DECIMAL fonctionne de la même manière que NUMERIC, mais avec des bornes différentes.
FLOAT, DOUBLE et REAL	Mémorisent des nombres à virgule sans qu'on ait à préciser de paramètres. Ces différents types ont des bornes maximales et minimales différentes.

Texte

Pour stocker du texte, on peut utiliser les types ci-dessous. Ces types sont équivalents.

Type	Description
CHAR	On peut préciser en paramètres le nombre d'octets que prendra le texte, maximum 255 octets.
VARCHAR	Prend un nombre variable d'octets, le nombre maximal étant précisé en paramètre lors de la définition du type, ce nombre maximal n'allant pas au-delà de 255. Il est plus économe en mémoire que le CHAR pour des textes ou noms de longueur potentiellement inférieure à 255 octets.
TEXT	Les limites sont plus grandes que les autres types.

Types temporels

L'ensemble des types temporels est détaillé dans le tableau ci-dessous.

Type	Description
DATE	Mémorise une date au format AAAA-MM-JJ
TIME	Mémorise une heure ou un intervalle de temps, au format HH:MM:SS
DATETIME	Mémorise une date et une heure (une sorte de fusion entre les types DATE et TIME), au format AAAA-MM-JJ HH:MM:SS
YEAR	Ne mémorise qu'une année
TIMESTAMP	Durée en seconde écoulée depuis une date définie à l'avance (qui dépend du SGBD)

Autres

Il est possible de définir si une colonne est obligatoire ou facultative. Par obligatoire ou facultative, on veut dire indiquer si la colonne peut contenir la valeur NULL ou non : une colonne obligatoire ne peut pas contenir de NULL alors qu'une colonne facultative le peut. Pour indiquer qu'une colonne est obligatoire, il suffit d'ajouter le mot-clé not null juste après la définition de la colonne. Dans le cas d'une colonne facultative, il ne faut rien faire.

create table PERSONNE
(
    NOM varchar not null ,
    PRENOM varchar not null ,
    ADRESSE varchar not null ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,
    DATE_NAISSANCE date
) ;

Clés primaires et étrangères

On sait donc définir des tables, et dire quels sont les attributs que doivent contenir chaque ligne de cette table. Il nous reste cependant à voir comment dire quels sont les attributs qui servent de clé primaire, et ceux qui servent de clé étrangère. De plus, dans le cas des clés étrangères, il faut aussi préciser quelle est la table parent, celle vers laquelle pointent les lignes de la table courante.

Clé primaire

Pour indiquer quels sont les attributs qui appartiennent à la clé primaire, il faut ajouter quelque chose aux déclarations de la table. Ce quelque chose est situé à la toute fin de la déclaration : il s’agit du mot-clé primary key (clé primaire en anglais), et des noms des attributs de cette clé entre parenthèses.

create table PERSONNE
(
    ID_PERSONNE int not null ,
    NOM varchar not null ,
    PRENOM varchar not null ,
    ADRESSE varchar not null ,
    DATE_NAISSANCE date not null ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,

    primary key (ID_PERSONNE) ,
) ;

Pour définir une clé alternative, en plus de la clé primaire, il faut l'indiquer avec le mot-clé unique. On peut déclarer plusieurs clés alternatives, chacune étant définie par un unique séparé des autres. Mais on ne peut définir qu'une seule clé primaire.

create table PERSONNE
(
    ID_PERSONNE int not null ,
    NOM varchar not null ,
    PRENOM varchar not null ,
    ADRESSE varchar not null ,
    DATE_NAISSANCE date not null ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,

    primary key (ID_PERSONNE) ,
    unique (NOM, PRENOM, ADRESSE)
) ;

On rappelle que les attributs d'une clé primaire ou alternative ne peuvent pas être à NULL.

Clés étrangères

Pour définir une clé étrangère, on fait comme pour les clés primaires, à ceci près que le mot-clé n'est pas le même : il faut utiliser le mot-clé foreign key (clé étrangère en anglais). De plus, il faut préciser quelle est la table à laquelle cette clé fait référence, ce qui demande d'utiliser le mot-clé reference suivi du nom de la table. Par exemple, voici ce que donnerait une table ENFANT, dont chaque ligne ferait référence à une table MERE et une table PERE.

create table ENFANT
(
    ID_ENFANT int not null ,
    NOM varchar not null ,
    PRENOM varchar not null ,
    ADRESSE varchar not null ,
    DATE_NAISSANCE date not null ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,

    MAMAN int ,
    PAPA int ,

    primary key (ID_ENFANT) ,
    unique (NOM, PRENOM, ADRESSE) ,
    foreign key (MAMAN) reference MERE ,
    foreign key (PAPA) reference PERE
) ;

Contraintes d'intégrité référentielle

Maintenant, il nous reste à préciser une dernière chose : que faire en cas de suppression d'une ligne dans la table décrite ? On a vu auparavant que l'on pouvait soit interdire la suppression si des clés étrangères pointent encore sur la ligne, mettre à zéro ces clés étrangères, ou bien tout simplement supprimer les lignes qui pointent sur la ligne à supprimer. Pour cela, il faudra utiliser un mot-clé à la fin de la ligne de code qui définit les clés étrangères :

• on delete cascade pour une suppression ;
• on delete no action pour une interdiction de la suppression ;
• on delete set null pour mettre la clé étrangère à NULL.

Les requêtes : la théorie

Dans les chapitres précédents, nous avons vu comment mémoriser des données dans une base de donnée : on utilise des tables, qui doivent respecter certaines contraintes d'intégrité. Maintenant, nous allons voir comment lire des données dans une table, et quelles opérations nous pouvons faire sur ces tables. Outre les opérations d'ajout d'une ligne, de suppression, et de modification, il existe quelques autres manipulations, que nous allons aborder dans ce chapitre. On peut classer ces opérations en deux grands groupes : un groupe d'opérations ensemblistes, inspirées de la théorie des ensembles, et un groupe d'opérations spécifiques aux bases de données relationnelles.

Opérations ensemblistes

Les opérations ensemblistes prennent en entrée deux tables et donnent comme résultat une autre table. Elles sont au nombre de quatre :

• Union ;
• Intersection ;
• Différence ;
• Produit cartésien.

Les opérations d'union, intersection et différence ne fonctionnent que sur des tables compatibles. Par compatibles, on veut dire que ces deux tables ont exactement les mêmes attributs, les mêmes colonnes : les colonnes des deux tables ont le même nom et le même type.

Union

L'union crée une table résultat qui contient toutes les lignes de la première et de la seconde table passées en entrée. En théorie, la table résultat ne contient pas de doublons : si une ligne est identique dans les deux tables, la table résultat ne contiendra qu'un seul exemplaire de cette ligne. Mais dans la réalité, il faut indiquer au SGBD que l'on souhaite éliminer les doublons.

Intersection

L'intersection crée une table résultat qui contient les lignes qui sont présentes à la fois dans la première et la seconde table. En théorie, la table résultat ne contient pas de doublons : si une ligne est identique dans les deux tables, la table résultat ne contiendra qu'un seul exemplaire de cette ligne. Mais dans la réalité, il faut indiquer au SGBD que l'on souhaite éliminer les doublons.

Différence

La différence crée une table résultat qui contient les lignes qui sont présentes dans la première table, mais pas dans la seconde. En théorie, la table résultat ne contient pas de doublons : si une ligne est identique dans les deux tables, la table résultat ne contiendra qu'un seul exemplaire de cette ligne. Mais dans la réalité, il faut indiquer au SGBD que l'on souhaite éliminer les doublons.

Produit cartésien

Le produit cartésien crée une table résultat dans laquelle chaque ligne est une combinaison des lignes des deux autres tables. Plus précisément, chaque ligne de la première table est concaténée à une ligne de la seconde table : la table résultat contient toutes les combinaisons possibles entre une ligne de la première table et une ligne de la seconde table. Ainsi, si la première table contient N lignes, et la seconde M lignes, la table résultat contiendra N×M lignes au total.

Opérations relationnelles

Les opérations relationnelles les plus importantes sont :

• la sélection ;
• la projection ;
• la jointure.

Projection

La projection est une opération qui consiste à ne garder que certaines colonnes dans une table :

• elle prend une table en opérande, ainsi qu'un ou plusieurs noms de colonnes ;
• ensuite, elle sélectionne les colonnes de la table qui lui sont indiquées ;
• elle copie ces colonnes dans une nouvelle table.

Pour les exemples qui vont suivre, nous allons reprendre la table vue plus haut.

Nom	Age	Taille
Johnson	25	172
Harry	54	180
Sally	70	164
George	29	170
Helena	14	154
Peter	8	180

Dans cette table, on peut demander de sélectionner uniquement la colonne "Nom", avec une projection.

Nom
Johnson
Harry
Sally
George
Helena
Peter

On peut aussi demander à sélectionner plusieurs colonnes, par exemple les colonnes "Age" et "Taille".

Age	Taille
25	172
54	180
70	164
29	170
14	154
8	180

Sélection

La sélection est une opération qui sélectionne les lignes d'une table qui respectent une condition bien précise :

• elle prend une table en opérande, ainsi qu'une condition ;
• ensuite, elle sélectionne les lignes de la table qui respectent la condition ;
• elle copie ces lignes dans une nouvelle table.

Conditions simples

La condition en question est souvent une sous-opération qui permet de comparer le contenu d'un attribut avec une autre information : une constante, le contenu d'un autre attribut, etc. Les comparaisons disponibles sont les comparaisons de :

• supériorité ;
• infériorité ;
• supériorité stricte (n'accepte pas l'égalité) ;
• infériorité stricte (n'accepte pas l'égalité) ;
• égalité ;
• différence.

D'autres tests sont possibles sur les SGBD actuels, mais nous laisserons cela pour plus tard.

Prenons un exemple, avec une table qui contient une liste de personnes. La table Personne mémorise, pour chaque ligne de la table : le nom de la personne, son âge, sa taille.

Nom	Age	Taille
Johnson	25	172
Harry	54	180
Sally	70	164
George	29	170
Helena	14	154
Peter	8	180

Dans cette table, on peut sélectionner toutes les personnes dont l'âge est inférieur à 18 ans (les personnes mineures). Il suffit pour cela d'indiquer à l'opération de sélection que l'on souhaite récupérer les lignes dont l'attribut âge est inférieur à 18 : age < 18. En faisant cela, on obtient une nouvelle table qui ne contiendra que les lignes pour lesquelles la condition est vérifiée. Avec la table vue plus haut, cette sélection donnerait la table suivante :

Nom	Age	Taille
Helena	14	154
Peter	8	180

Comme autre exemple, on peut sélectionner les personnes dont la taille est supérieure à 170. Dans ce cas, la comparaison vérifie, pour chaque ligne, si l'attribut Taille de la ligne est supérieur à 170 : Taille > 170. Avec la table vue plus haut, cette sélection donnerait la table suivante :

Nom	Age	Taille
Johnson	25	172
Harry	54	180
George	29	170
Peter	8	180

Enfin, dernier exemple : on peut sélectionner les lignes de la table où le nom n'est pas "Johnson". Pour cela, il suffit de vérifier si l'attribut de la ligne n'est pas "Johnson" : Nom != Johnson. Avec la table vue plus haut, cette sélection donnerait la table suivante :

Nom	Age	Taille
Harry	54	180
Sally	70	164
George	29	170
Helena	14	154
Peter	8	180

Conditions composées

Il est possible de ne sélectionner que les lignes qui respectent plusieurs conditions en même temps. Pour cela, on peut combiner plusieurs conditions en une seule, avec des opérateurs respectivement appelées ET et OU logiques.

Jointure

Les jointures (car ils en existe plusieurs types) sont des opérations assez difficiles à décrire. Dans le détail, la jointure prend en entrée deux tables et fournit une table résultat. Chaque ligne de la table résultat est le résultat de la concaténation d'une ligne de la première table et d'une ligne de la seconde. Cependant, cette concaténation n'est effectuée que si les deux lignes ont un ou plusieurs attributs en commun : on peut demander à ne garder que les lignes pour lesquelles l'attribut nommé "…" a la même valeur dans les deux tables. De plus, la colonne présente dans les deux tables n'est présente qu'en un seul exemplaire dans la table résultat. Pour simplifier, on peut dire qu'il s'agit d'une combinaison entre le produit cartésien de deux tables, d'une sélection et d'une projection. Le produit cartésien concatène les lignes des deux tables, la sélection ne conserve que les lignes pour lesquelles les attributs sont identiques, et la projection supprime la colonne en double.

Les requêtes en SQL

Maintenant que nous avons vu la théorie, nous allons passer à la pratique et voir comment traduire ces opérations en SQL. Nous allons donc étudier la partie du SQL qui permet de manipuler des tables : y insérer des données, les sélectionner, etc. Cette portion du SQL est appelée le SQL DML : Data Manipulation Language. Grosso-modo, celui-ci définit plusieurs instructions relativement simples, et nous allons nous concentrer sur :

• SELECT : Sélection de données,
• JOIN : Jointure de plusieurs sources,
• INSERT : Insertion de données,
• DELETE : Suppression de données,
• UPDATE : Mise à jour de données existantes.

SELECT...FROM...WHERE

Si on devait se fier uniquement à son nom, l'instruction SELECT devrait effectuer une sélection. Dans la réalité du SQL, elle est plus puissante et permet de faire aussi une projection, un ou plusieurs produit cartésien, et quelques autres opérations annexes. La syntaxe de l'opération SELECT est relativement complexe : elle est composée de plusieurs clauses, qui s'écrivent ainsi :

SELECT colonne
FROM table
WHERE condition
ORDER BY attribut
GROUP BY attribut
HAVING condition 
LIMIT nombre_de_lignes ;

SELECT

Dans le cas le plus simple, la syntaxe de SELECT est la suivante :

SELECT nom_colonnes /* les noms des colonnes sont séparés par des virgules */
FROM nom_table ;

Le SELECT effectue une projection : les noms des colonnes indiqués après le SELECT correspondent aux colonnes qui seront conservées, les autres étant éliminées par la projection. Le FROM indique dans quelle table il faut effectuer la projection et la sélection. On peut se limiter à faire une projection, ce qui fait que la syntaxe suivante est parfaitement possible :

SELECT nom, prénom
FROM personne ;

Doublons

On peut préciser qu'il est possible que certaines lignes donnent des doublons dans la table. Par exemple, rien n’empêche que plusieurs personnes différentes aient le même nom : une projection de la colonne nom dans une table de personne donnera fatalement quelques doublons. Mais il est possible d'éliminer ces doublons en utilisant un mot-clé. Ce mot-clé DISTINCT doit être placé entre le SELECT et les noms des colonnes.

SELECT DISTINCT nom_colonnes
FROM nom_table ;

Par exemple, prenons cette table :

CREATE TABLE Animal (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    espece VARCHAR(40) NOT NULL,
    sexe CHAR(1),
    date_naissance DATETIME NOT NULL,
    nom VARCHAR(30),

    PRIMARY KEY (id)
)

Voici la requête SQL pour obtenir une table dans laquelle on ne récupérerait que les noms des animaux (sans doublons) :

SELECT DISTINCT nom
FROM Animal

Voici la requête SQL pour obtenir une table dans laquelle on ne récupérerait que les noms et les dates de naissance des animaux (sans doublons) :

SELECT DISTINCT nom , date_naissance
FROM Animal

Omettre la projection

Si jamais vous souhaitez conserver toutes les colonnes lors d'une projection, vous devez utiliser la syntaxe suivante :

SELECT *
FROM Nom_table
WHERE condition

Par exemple, la requête SELECT suivante sélectionne toutes les lignes de la table personne où l'attribut prenom vaut "Jean", sans faire de projection.

SELECT * 
FROM personne
WHERE (prenom = "Jean")

Agrégations

Dans une requête SELECT, il est possible d'utiliser certaines fonctions qui permettent respectivement de :

• faire la somme de tous les attributs d'une colonne (leur somme) ;
• déterminer quelle est la plus petite valeur présente dans la colonne (son minimum) ;
• déterminer quelle est la plus grande valeur présente dans la colonne (son maximum) ;
• déterminer quelle est la moyenne de la colonne ;
• déterminer quelle est le nombre de lignes de la colonne projetée.

Ces fonctions sont respectivement nommées :

• SUM (somme d'une colonne) ;
• AVG (moyenne d'une colonne) ;
• MIN (minimum d'une colonne) ;
• MAX (maximum d'une colonne) ;
• COUNT (nombre de lignes d'une colonne).

Par exemple, imaginons que vous ayez une table Personne contenant une liste de personnes, avec un attribut/colonne age et un autre attribut pour le nom. Supposons que vous souhaitiez calculer la moyenne d'âge des personnes de cette liste : vous pouvez le faire en utilisant cette requête :

SELECT AVG(age)
FROM Personne ;

Comme autre exemple, vous pouvez prendre la personne la plus âgée en utilisant la requête suivante :

SELECT MAX(age)
FROM Personne ;

De même, vous pouvez savoir combien de noms différents contient la table en utilisant cette requête :

SELECT DISTINCT COUNT(nom)
FROM Personne ;

Il est aussi possible d'appliquer des fonctions numériques au résultat renvoyé par un agrégateur. S'y trouvent en particulier des fonctions d'arrondi  :

• FLOOR (partie entière d'un nombre flottant) ;
• CEILING (entier directement supérieur) ;
• ROUND (arrondi au plus proche au rang décimal choisi) ;

Par exemple, si vous souhaitez obtenir l'arrondi à l'entier le plus proche de la moyenne d'âge des personnes présentes dans la table, vous pouvez le faire en utilisant cette requête (l'argument 0 paramétrant le rang des unités pour le calcul d'arrondi, 1 celui des dixièmes, 2 des centièmes ...) :

SELECT ROUND(AVG(age),0)
FROM Personne ;

WHERE

Si on veut effectuer une sélection, c'est cette syntaxe qu'il faut utiliser

SELECT nom_colonnes
FROM nom_table
WHERE condition_selection ;

Le terme WHERE, facultatif, permet de faire une sélection : il précise la condition que les lignes doivent respecter pour être conservées dans la table résultat.

Comparaisons de base

Les tests autorisés en SQL sont les conditions les plus basiques :

• a < b ;
• a > b ;
• a <= b ;
• a >= b ;
• a = b ;
• a <> b.

De plus, les opérateurs booléens AND, OR et NOT sont possibles (avec le XOR en plus).

Les comparaisons vues plus haut ne permettent pas de vérifier si le contenu d'une colonne est NULL. Ainsi, une condition du type age = NULL ne sera tout simplement pas accepté par le SGBD. Pour vérifier si une colonne contient ou non la valeur NULL, il faut utiliser des conditions spéciales, notées respectivement :

• IS NULL ;
• IS NOT NULL.

Pour donner un exemple, la requête suivante renvoie toutes les personnes dont l'âge est inconnu :

SELECT * 
FROM Personne
WHERE age IS NULL ;

Comme autre exemple, la requête suivante renvoie toutes les personnes dont l'adresse est connue :

SELECT * 
FROM Personne
WHERE adresse IS NOT NULL ;

Condition intervallaire

Il est aussi possible de vérifier si tel attribut est compris dans un intervalle bien précis. Pour cela, la condition s'écrit comme ceci :

SELECT ...
FROM ...
WHERE attribut BETWEEN minimum AND maximum ;

Par exemple, cette requête renvoie les lignes de la table personne qui comprend les personnes dont l'âge est compris entre 18 et 62 ans (les personnes en âge de travailler) :

SELECT * 
FROM Personne
WHERE age BETWEEN 18 AND 62 ;

Cela fonctionne aussi avec les chaînes de caractère ou les dates. Par exemple, la requête suivante renvoie toutes les personnes qui sont nées entre le 2 juillet 2000 et le 3 octobre 2014 :

SELECT * 
FROM Personne
WHERE date_naissance BETWEEN '2000-07-02' AND '2014-10-3' ;

La condition inverse, qui vérifie que l'attribut n'est pas dans l'intervalle existe aussi : c'est la condition NOT BETWEEN. Elle s'utilise comme la condition BETWEEN. Par exemple, cette requête renvoie les lignes de la table personne qui comprennent les personnes dont l'âge n'est pas compris entre 18 et 62 ans (les personnes en âge de ne pas travailler) :

SELECT * 
FROM Personne
WHERE age NOT BETWEEN 18 AND 62 ;

Exemples

Prenons maintenant la table PERSONNE définie au-dessus.

Voici comment récupérer les personnes dont on connaît l'âge :

SELECT * 
FROM personne
WHERE (age IS NOT NULL) ;

Voici comment récupérer les personnes dont on ne connaît pas la taille :

SELECT * 
FROM personne
WHERE (taille IS NULL) ;

Maintenant, prenons la table suivante :

create table PERSONNE
(
    ID_PERSONNE int not null ,
    NOM varchar not null ,
    PRENOM varchar not null ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,

    primary key (ID_PERSONNE) ,
) ;

Voici la requête pour récupérer toutes les personnes dont la taille est supérieure à 170 centimètres :

SELECT * 
FROM personne
WHERE (taille > 170) ;

Et voici la requête pour récupérer les noms et prénoms des personnes qui ont la majorité (dont l'âge est supérieur ou égal à 18 ans) :

SELECT nom, prénom 
FROM personne
WHERE (age>= "18") ;

ORDER BY

On peut demander au SGBD de trier les données dans la table résultat, que ce soit dans l'ordre croissant ou dans l'ordre décroissant. Pour cela, il faut utiliser l'instruction ORDER BY, juste en dessous du WHERE. Cette instruction ORDER BY a besoin de plusieurs informations pour fonctionner :

• quelles sont les colonnes à prendre en compte lors du tri ;
• dans quel ordre les prendre en compte ;
• faut-il trier chaque colonne par ordre croissant ou décroissant.

Pour cela, ORDER BY est suivi d'un ou de plusieurs noms de colonne. De plus, chaque nom de colonne est suivi d'un mot-clé qui indique s'il faut trier dans l'ordre croissant ou décroissant : ces mot-clés sont respectivement les mot-clés ASC et DESC (pour Ascendant et Descendant).

Par exemple, la requête suivante sélectionne les personnes majeures de la table Personne, les personnes de la table résultat étant triée de l'âge le plus petit vers l'âge le plus grand.

SELECT *
FROM Personne
WHERE age > 18 AND age IS NOT NULL
ORDER BY age ASC ;

Par contre, la requête suivante trie les résultats par âge décroissant.

SELECT *
FROM Personne
WHERE age > 18 AND age IS NOT NULL
ORDER BY age DESC ;

FROM

Maintenant que nous avons étudié en détail la requête SELECT et la condition WHERE, nous allons nous pencher plus en détail sur la directive FROM. Dans les exemples précédents, nous avons vu que cette directive permet de préciser la table sur laquelle nous voulons effectuer la requête. Mais dans les faits, FROM est aussi plus puissant que prévu : il permet aussi d'effectuer des produits cartésiens entre plusieurs tables et des jointures. Il permet aussi d'effectuer ce qu'on appelle des sous-requêtes. Tout ce qu'il faut retenir, c'est que l'expression qui suit FROM doit avoir pour résultat une table, cette table étant celle sur laquelle on effectue la requête.

Produit cartésien

Effectuer un produit cartésien est relativement simple : il suffit d'indiquer plusieurs tables, séparées par des virgules à la suite de FROM. Par exemple, cette requête effectue un produit cartésien des tables Personne et Emploi :

SELECT *
FROM Personne , Emploi ;

Jointures

On peut parfaitement créer les jointures à la main, en décrivant le produit cartésien dans FROM et en mettant la condition de la jointure dans WHERE. Mais le langage SQL fournit une syntaxe spéciale pour les jointures. Mieux : il fournit plusieurs types de jointures, qui différent sur quelques points de détail. La syntaxe la plus simple est la jointure normale, aussi appelé jointure interne, ou encore INNER JOIN.

SELECT *
FROM table_1 INNER JOIN table_2 ON condition
WHERE ... ;

La jointure naturelle est un cas particulier de jointure interne, qui correspond au cas où la condition vérifie l'égalité de deux colonnes et où les deux colonnes en question ont le même nom de colonne. Avec ces jointures, il n'y a pas besoin de préciser la condition que doivent respecter les deux tables, celle-ci étant implicite : c'est l'égalité des deux colonnes qui ont le même nom dans les deux tables. Ces jointures naturelles s'écrivent avec les mots-clés NATURAL JOIN, qui sépare les deux tables :

SELECT *
FROM table_1 NATURAL JOIN table_2
WHERE ... ;

À côté de cette jointure interne, il existe des jointures externes, qui ajoutent des lignes pour lesquelles la condition n'est pas respectée. Dans tous les cas, les lignes ajoutées voient leurs colonnes vides remplies avec la valeur NULL (la table résultat a plus de colonnes que les tables initiales). Il existe trois grands types de jointures externes :

• la plus commune est la jointure gauche, dans laquelle les lignes de la première table sont ajoutées dans la table résultat, même si elles ne respectent pas la condition ;
• la jointure droite est similaire, sauf que les lignes de la seconde table remplacent les lignes de la première table ;
• la jointure totale peut être vue comme un mélange d'une jointure gauche et droite : les lignes des deux tables sont copiées, même si elles ne respectent pas la condition et les colonnes en trop sont remplies avec NULL.

Les mot-clés pour ces jointures sont respectivement :

• LEFT JOIN ;
• RIGHT JOIN ;
• FULL JOIN.

SQL Joins

GROUP BY

La clause GROUP BY permet de grouper plusieurs lignes en une seule, à la condition que ces lignes aient un attribut/colonne identique. Si on se contente de faire une projection sur la colonne identique dans les lignes, la clause GROUP BY élimine les doublons. On peut se demander à quoi cela peut servir, vu que le mot-clé distinct permet de faire exactement la même chose. Mais la différence apparaît quand on utilise des fonctions comme MAX, MIN, AVG, SUM ou COUNT : ces fonctions n'agissent plus sur une colonne complète, mais sur un groupe à la fois.

Prenons un exemple classique, avec une table ACHAT qui mémorise des informations sur des achats :

• quel client a effectué l'achat : clé étrangère client ;
• quel est le montant de l'achat : attribut montant, de type INT ;
• et d'autres dont on se moque pour cet exemple.

Il se trouve qu'un client peut faire plusieurs achats, à des jours différents, ou acheter des articles différents en une fois. Les clauses GROUP BY et une projection bien choisie nous permettent de calculer quel montant a dépensé le client dans le magasin. Pour cela, il suffit de regrouper toutes les lignes qui font référence à un même client avec un GROUP BY client. Une fois cela fait, on ajoute une fonction SUM dans la projection, afin de sommer les montants pour chaque groupe (et donc pour chaque client). Au final, on se retrouve avec une table résultat qui contient une ligne par client, chaque ligne contenant la somme totale que ce client a dépensé dans le magasin.

SELECT SUM(montant)
FROM Achats
GROUP BY client ;

HAVING

La clause HAVING est similaire à la clause WHERE, à un détail prêt : elle permet d'utiliser des conditions qui impliquent les fonctions MAX, MIN, AVG, SUM et COUNT, ainsi que quelques autres. Elle s'utilise le plus souvent avec un GROUP BY, même si ce n'est pas systématique.

Par exemple, on peut modifier l'exemple précédent de manière à ne conserver que les clients qui ont acheté plus de 500 euros dans le magasin, en utilisant cette requête :

SELECT SUM(montant)
FROM Achats
GROUP BY client 
HAVING SUM(montant) > 500 ;

INSERT, DELETE, UPDATE

Dans cet extrait, nous allons voir comment ajouter, modifier ou supprimer des lignes dans une table. Ces opérations sont respectivement prises en charge par les instructions INSERT, UPDATE et DELETE.

INSERT

INSERT sert à ajouter des lignes dans une table. Sa syntaxe est la suivante :

INSERT INTO nom_table VALUES ( liste des valeurs de chaque attribut, séparés par des virgules )

Insertion simple

Prenons la table définie comme suit :

create table PERSONNE
(
    ID_PERSONNE int not null ,
    NOM varchar not null ,
    PRENOM varchar not null ,
    AGE int ,
    CATEGORIE_PROFESSIONNELLE varchar ,
    NOMBRE_ENFANTS int ,
    TAILLE float ,

    primary key (ID_PERSONNE) ,
) ;

Supposons que nous souhaitons ajouter la personne numéro 50, nommée Pierre Dupont, qui a 25 ans, sans emploi, sans enfants et de taille 174 centimètres. Voici comment utiliser INSERT pour cela :

INSERT INTO personne VALUES (50, "Dupont", "Pierre", 25, "Sans emploi", 0, 174)

On remarquera que les informations sont données dans l'ordre des colonnes.

Maintenant, passons à l'exemple suivant. Prenons la table suivante :

create table ENFANT
(
    ID_ENFANT int not null ,
    NOM varchar not null ,
    PRENOM varchar not null ,
    ADRESSE varchar not null ,
    DATE_NAISSANCE date not null ,
) ;

Pour y ajouter l'enfant numéro 27, appelé "Jean Moreno", qui habite "22 rue des tuileries Paris", né le 17/01/2009, voici comment utiliser INSERT :

INSERT INTO enfant VALUES (27, "Moreno", "Jean", "22 rue des tuileries Paris", 17/01/2009)

Insertion multiple

Il est possible d'insérer plusieurs lignes à la fois dans une table en utilisant une seule instruction INSERT. Pour cela, les diverses lignes à ajouter sont simplement placées les unes après les autres à la suite du VALUES, entre parenthèses.

INSERT INTO nom_table VALUES 
( première ligne ) 
( seconde ligne )
( troisième ligne ) 
( ... )

DELETE

L'instruction DELETE permet de supprimer les lignes d'une table. On peut l'utiliser sous deux formes :

• une qui supprime toutes les lignes de la table ;
• une autre qui supprime seulement les lignes qui respectent une certaine condition.

Suppression totale

Pour supprimer toutes les lignes d'une table, il faut préciser quelle est la table concernée. La syntaxe suivante permet de supprimer toutes les lignes de la table nommée "nom_table" :

DELETE FROM nom_table ;

En supprimant toutes les lignes, la table n'est pas supprimée : on obtient une table vide.

Suppression conditionnelle

Pour supprimer les lignes d'une table qui respectent une condition précise, il faut ajouter quelque chose à la syntaxe précédente, histoire de préciser quelle est la condition en question. Pour préciser la condition, on fait comme avec l'instruction SELECT : on utilise une clause WHERE. La syntaxe suivante permet de supprimer toutes les lignes de la table nommée "nom_table", qui respectent la condition nommée "condition" :

DELETE FROM nom_table
WHERE condition ;

Prenons l'exemple d'une table "Mineurs" qui mémorise une liste de personnes mineures pour une application judiciaire destinée à un tribunal pour enfants. Cette table mémorise, pour chaque enfant, son nom, prénom, age, date de naissance, et bien d'autres informations dans des attributs éponymes. Tous les ans, cette table est mise à jour pour que l'age mémorisé soit le bon. Cependant, suite à cette mise à jour, des lignes ont un age qui vaut 18, ce qui fait que la ligne correspond à des personnes majeures. Voici la requête qui permet de supprimer ces lignes :

DELETE FROM Mineurs WHERE age >= 18 ;

UPDATE

Pour mettre à jour certaines lignes d'une table, on doit utiliser l'instruction UPDATE. Celle-ci fonctionne comme pour la suppression des lignes : on doit préciser quelles sont les lignes à modifier avec une condition : toutes les lignes de la table qui respectent cette condition seront modifiées, alors que les autres ne seront pas touchées. Toutes les colonnes sont mises à jour avec la valeur indiquée dans le UPDATE. Pour faire la mise à jour, il faut ainsi préciser :

• quelle table modifier à la suite de l'instruction UPDATE ;
• quelles colonnes modifier et par quoi remplacer leur contenu : c'est le rôle de l'instruction SET ;
• et enfin quelle est la condition avec, encore une fois, un WHERE.

UPDATE table
SET colonne_1 = 'valeur 1', colonne_2 = 'valeur 2', colonne_3 = 'valeur 3'
WHERE condition ;

Anatomie d'une requête UPDATE.

Les gestionnaires de bases de données

Un gestionnaire de bases de données (SGBD : Système de Gestion de Bases de Données) fournit l'ensemble des outils permettant de gérer une base de données.

Il existe différents logiciels gestionnaire de bases de données ayant des différences :

• Certains gestionnaires fournissent un serveur SQL permettant un accès à distance ;
• Chaque gestionnaire ajoute des commandes SQL non standard ;
• Le format de fichier des bases de données est spécifique.

Principaux SGBD

Ce chapitre ne détaille pas les spécificités des principaux SGBD car il existe des wikilivres détaillés.

• MySQL
• Oracle Database
• Microsoft SQL Server
• ...

GFDL

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans texte de dernière page de couverture.