Cours comprendre les bases de données

Cours comprendre les bases de données, tutoriel & guide de travaux pratiques les bases de données en pdf.

Introduction, tables, lignes et colonnes

Les bases de données… Elles envahissent notre vie privée, elles deviennent incontournables en informatique, alors voici une série d’articles qui a la prétention de vous former à la compréhension, utilisation et conception des bases de données. Les articles seront publiés au rythme de un par jour ou tous les deux jours.
Un peu de vocabulaire
Comme tout le monde le sait, le jargon technique à pour principale conséquence (intérêt ?) de rendre in-accessible une technologie aux non-initiés. Alors voici une base permettant de décoder – voire décrypter – le domaine des bases de données.
Une base de données (BD) – database ou DB en anglais – est un système informatique permettant d’écrire, de socker et d’accéder à des données.
Fondamentalement, la notion de base de données est donc très vaste. Par exemple, si vous utilisez un fichier de traitement de texte pour y noter vos rendez-vous, il s’agit là d’une base de données. De même, votre disque dur est une base de données. On distingue cependant les bases de données structurées des autres. Votre fichier de traitement de texte n’est à priori pas structuré, pas plus que ne l’est votre disque dur (encore que cela peut se discuter). Par contre, quand vous envoyez un email, on peut considérer que votre mailer se comporte comme une base de données structurée, puisque qu’il va archiver les emails tout en stockant dans des champs destinés à cet effet la date, l’expéditeur, le destinataire, le texte etc.
Un système de gestion de base de données – SGBD ou DBMS en anglais – fait référence à un système informatique expressément conçu pour la gestion des données la plupart du temps structurées, à l’exception notable des SGBD à vocation documentaire (données non structurées), qui ne feront pas l’objet de cette série d’articles.
Les bases de données relationnelles sont celles qui établissent des relations entre différentes entités. Par exemple, si vous avez un dictionnaires et un atlas, et que dans le dictionnaire vous y lisez « Le Louvre : musée français situé à paris cf : atlas, Paris, p. 230 », vous avez là une relation entre deux entités (le dictionnaire et l’atlas), puisqu’en lisant une donnée dans le dictionnaire vous avez récupéré des informations vous permettant d’accéder à une donnée dans l’autre entité : l’atlas. Signalons que les bases de données relationnelles ne sont pas les seules existantes. Nous avons aussi les bases de données hiérarchiques (quasiment disparues aujour-d’hui, surtout depuis qu’il a été démontré que tout ce qui peut être fait en hiérarchique peut l’être également en relationnel). Il y a aussi les bases de données objets, censées révolutionner les SGBD, mais qui peinent à émerger. Nous n’évoquerons dans cette série d’articles que les bases de données relationnelles. Nous ne ferons qu’avoir un aperçu des capacités orientées objets de pgSQL. Un système informatique permettant de gérer des bases de données relationnelles est appelé SGBDR ou RDBMS en anglais.
Pour accéder aux données dans un SGBDR, on utilise en général un langage nommé SQL (Structured Query Language ou Langage Structuré de Requête). Le SQL est un langage ensembliste. Il s’agit en fait d’une transposition informatique des opérations algébriques sur les ensembles. Dans un SGBDR les données sont effectivement des ensembles, et pour y accéder on peut dire par exemple « Je veux l’union entre les clients qui ont achetés plus de 3 fois cette année, et ceux qui ont dépensés au total pour plus de 10 000 FRF ». Ce qui serait complètement différent de « l’intersection entre les clients qui ont achetés plus de 3 fois cette année, et ceux qui ont dépensés au total pour plus de 10 000 FRF » (dans un cas nous avons un OU, dans l’autre un ET).
Les tables
Dans un SGBDR, les données sont stockées dans des tables, voici un exemble :
lact_code | lact_lib
————+———————————————
15 | Industries alimentaires
16 | Industrie du tabac
17 | Industrie textile
18 | Industrie de l’habillement et des fourrures
19 | Industrie du cuir et de la chaussure
Dans cette table nous avons 5 lignes (on parle aussi de tuples ), et deux colonnes portant le nom de lact_code et de lact_lib. Au lieu de parler de colonnes, on peut aussi parler de champs.
Chaque colonne possède un type particulier, correspondant aux données qui y seront stockées. Ici, le champ lact_code est d’un type numérique entier, tandis que lact_lib est d’un type chaîne de caractères.
NULL, une valeur à part
Il y a dans les bases de données une valeur particulière qui s’appelle NULL. Null signifie « indéterminé ».
Attention, cela ne signifie pas « vide », c’est complètement différent.
Par exemple, si vous avez dans une table un champ nombre_enfants, si vous y mettez 0, c’est que vous n’avez pas d’enfants, si vous ne renseignez pas ce champ, alors il contiendra la valeur NULL.
De même, si une table contient le champ surnom . Si le champ contient la valeur NULL, c’est qu’on ne sait pas si le personne à un surnom. Par contre si le champ est vide (c’est à dire s’il contient la chaîne vide : « »), cela signifie que la personne n’a pas de surnom. Il faut bien faire attention à ne pas confondre la chaine vide, et NULL, ce sont deux valeurs complètement différentes [1] (déterminée dans un cas, indéterminée dans l’autre).
La valeur NULL, n’a pas de type. Elle ne peut pas être impliquée dans la moindre opération mathématique puisque sa valeur est indéterminée. L’opération aura donc un résultat indéterminé. 1 + NULL donne NULL, la concaténation d’une chaîne de caractères avec NULL donne également NULL. En quelque sorte, NULL est un élément absorbant pour la quasi-totalité des opérations.
martin=> SELECT * FROM eleves;
id | nom | prenom | age | classe
—-+——–+———-+—–+——–

Installation de postgreSQL, CREATE TABLE, SELECT, INSERT, DELETE

L’article précédent était passablement théorique. Nous allons désormais passer à un apprentissage pratique.
Pour cela, il vous faut un SGBDR opérationel sur votre machine.
Installer postgreSQL
Nous allons parler ici de l’installation sur une Mandrake 8, si vous êtes équipé d’une autre distribution, vous pouvez soit utiliser le paquetage spécifique à votre distribution, soit télécharger les sources et les compiler.
Avec une mandrake, l’installation est particulièrement simple. Si PostgreSQL n’est pas déjà intallé, un simple urpmi postgresql devrait suffire à installer tout les éléments nécessaires.
Après l’installation, le plus simple est d’effectuer un redémarrage de la machine afin que le service soit lancé correctement.
Ensuite, en tant que superutilisateur, taper la commande su – postgres , vous serez ainsi identifié comme utisateur ayant les droits d’administration du SGBDR. Il vous faut désormais vous créer un utilisateur de base de données, et une base de données. Attention, le SGBDR possède sa propre gestion des utilisateurs. Donc, si votre nom d’utilisateur Linux est martin il vous faut également créer un utilisateur martin dans postgresql. Cela s’effectue avec la ligne de commande : createuser martin. Si yous répondez Y aux deux questions qui vous sont posées à ce moment là, ce nouvel utilisateur aura également le droit de créer des utilisateurs et des bases de données.
La création de la base de données se fait très simplement avec la commande createdb martin. Nous avons donc désormais un utilisateur et une base de données tout deux nommés martin.
Ensuite, lorsque vous êtes en ligne de commande en tant qu’utilisateur Linux martin, vous pouvez vous connecter au SGBDR avec la commande psql, vous serez connecté en tant qu’utilisateur martin, et dans la base de données martin . Psql est un client permettant d’envoyer des ordres SQL à la base de données. Vous pouvez quitter à tout moment psql pour revenir au shell avec les touches CTRL+D.
Création de table
Une table est créée avec l’instruction SQL CREATE TABLE. Si nous voulons créer une table ELEVES, comportant 3 colonnes : le nom, le prenom et l’age, voici comment s’y prendre :
martin=# CREATE TABLE eleves (
martin(# nom varchar,
martin(# prenom varchar,
martin(# age integer);
CREATE
martin=#
La commande aurait pu être tapée sur une seule ligne peu importe, psql n’envoie l’ordre SQL au SGBD que lorsqu’il rencontre le « ; » qui marque la fin de l’instruction SQL. Le nom de la table à créer (eleves) suit immédiatement l’ordre CREATE TABLE, puis la liste des colonnes est spécifiée entre parenthèses. Pour chaque colonne, on donne le nom et le type. Voici donc 2 exemples de type : integer qui est un entier signé sur 32 bits, et varchar qui signifie « chaîne de caractères de longueur variable ».
Oh, un dernier détail, avec postgreSQL aucune différence n’est faite entre les majuscules et les minuscules dans les ordres SQL. On aurait donc pu taper Create Table EleVeS, cela n’aurait rien changé.
Ajouter et consulter des données
Pour mettre des données dans notre table, on utilise l’instruction INSERT INTO.
martin=# INSERT INTO eleves VALUES(‘DUPONT’,’Martin’,25);
INSERT 53638 1
martin=#
La valeur 53638 qu’affiche psql lorsque l’ordre est exécuté correspond à l’oid attribué à cette nouvelle ligne (l’oid est le numéro d’identification unique d’un objet dans pgSQL). Le numéro que vous obtiendrez sera certainement différent. Le chiffre 1 qui se trouve après l’oid indique que l’instruction INSERT a affecté une seule ligne.
On peut désormais consulter les données avec l’ordre SELECT :
martin=# SELECT nom FROM eleves;
nom
——–
DUPONT
(1 row)
martin=# SELECT nom, prenom FROM eleves;
nom | prenom
——–+——–
DUPONT | Martin
(1 row)
martin=# SELECT * FROM eleves;
nom | prenom | age
——–+——–+—–
DUPONT | Martin | 25
(1 row)
martin=#
L’argument « * » est bien pratique puisqu’il permet de sélectionner d’un seul coup toutes les colonnes. Ce-pendant il est déconseillé de l’utiliser dans le code d’une application. Il va en effet renvoyer les colonnes dans l’ordre ou elles ont été déclarées. À la moindre évolution du schéma de votre base de données vous risqueriez d’avoir des dysfonctionnements liés à l’utilisation de cet argument.
Un SELECT peut également servir à effectuer des opérations. Par exemple, l’opérateur « || » sert à effectuer une concaténation entre deux chaînes de caractères.
martin=# SELECT nom||’ ‘||prenom AS np, nom AS name FROM eleves;
np | name
—————+——–
DUPONT Martin | DUPONT
(1 row)
martin=#
Ainsi que vous pouvez le voir, on peut changer le nom de référence d’une colonne avec AS , cela est bien pratique lorsqu’on effectue des opérations complexes ou bien lorsqu’on veut faire référence à la valeur d’une colonne avant qu’elle n’ait été modifiée par une opération. Dans le jargon on appelle cela des ALIAS de colonnes.
Opération ensembliste
Ainsi que je l’ai déjà dit, SQL permet de manipuler des ensembles. Un exemple ? Et bien nous avons vu les les instructions INSERT et SELECT. Le SELECT retourne un ensemble de données issues d’une table, tandis que l’INSERT pertmet dajouter un ensemble de données dans une table. Un petit exemple, et les choses seront plus claires :
martin=# SELECT * FROM eleves;
nom | prenom | age
——–+——–+—–
martin=# INSERT INTO eleves SELECT * FROM eleves; INSERT 53639 1
martin=# SELECT * FROM eleves;
nom | prenom | age
——– +——– +—–
DUPONT | Martin | 25
DUPONT | Martin | 25
(2 rows)
martin=#
Vous avez compris ? Nous venons d’utiliser l’ensemble des données qui ont été retournées par un SELECT comme argument d’un INSERT. Comme le SELECT ne retournait qu’une seule ligne, nous avons ajouté une deuxième ligne dans notre table. Si nous recommencions une seconde fois la même opération, nous obtiendrions 4 lignes identiques dans la table eleves.

1 Article 1 : présentation de Postgresql 
1.1 Concurrence d’accès par multi-version
1.2 Héritage, structures orientées objet
1.3 Procédures stockées
1.4 Triggers, contraintes
2 Episode 1 : introduction, tables, lignes et colonnes 
2.1 Un peu de vocabulaire
2.2 Les tables
2.3 NULL, une valeur à part
3 Episode 2 : Installation de postgreSQL, CREATE TABLE, SELECT, INSERT, DELETE
3.1 Installer postgreSQL
3.2 Création de table
3.3 Ajouter et consulter des données
3.4 Opération ensembliste
3.5 Faire le ménage
4 Episode 3 : WHERE et UPDATE
4.1 La clause WHERE
4.2 UPDATE
4.3 DISTINCT et INTO
5 Episode 4 : indexes, clefs et jointures
5.1 Les index
5.2 Jointures
6 Episode 5 : Les aggrégats
6.1 Grouper les aggrégats
7 Episode 6 : Les transaction 
8 Episode 7 : Contraintes et séquences
8.1 Les contraintes de tables
9 Episode 8 : Procédures stockées, fonctions
10 Episode 9 : Triggers
10.1 Qu’est-ce que c’est ?
10.2 Comment faire un trigger ?
11 Episode 10 : Optimisations et performances
11.1 Les niveaux d’optimisation
11.2 Le niveau conceptuel
11.3 Écriture de requêtes, tables directrices et statistiques
11.4 Le matériel
11.5 La configuration du serveur
12 Episode 11 : Les utilisateurs et leurs droits : CREATE GROUP, GRANT
12.1 Utilisateurs
12.2 GRANT et REVOKE
12.3 Les groupes
12.4 Suite… ?
13 Conclusion 
13.1 Scripts de création de la base
13.2 UNION / ALL des performances très différentes
13.3 Utilisez les alias de tables
13.4 Ne présumez jamais du résultat d’une opération impliquant un NULL
13.5 Nommez systématiquement vos colonnes
13.6 Travaillez toujours de manière ensembliste
13.7 Pensez au produit cartésien d’une table sur elle-même