Archives mensuelles: September 2016

Revue de presse

SQL — Bases

Une base de données est un outil logiciel permettant de créer, modifier et interroger des données organisées sous forme de tables. Chaque table comporte un certain nombre de lignes et de colonnes (appelées champs).

SQL (Simple Query Langage) est un des langages les plus populaires pour travailler sur ces bases de données, et est utilisé (avec quelques variations par rapport au standard) par de nombreux SGBD (Systèmes de gestion de bases de données), parmis lesquels Oracle, MySQL, Microsoft SQL server etc..

Propriétés des SGBD

Les bases de données servent à conserver des données structurées, de manière permanente et sécurisée, tout en conservant un accès rapide. Ces qualités en font un outil idéal pour le contenu des sites webs. On retrouve d’ailleurs souvent des sites internet bâtis sur une architecture WAMP (ou MAMP, LAMP, XAMP etc…), soit Windows/Apache/Mysql/Php.

Conception

La conception d’une base de données consiste à décider comment organiser les données d’un projet dans les différentes tables et les différents champs.
Mettre toutes les données dans une même table pourrait théoriquement fonctionner, mais pose plusieurs problèmes :

  • Redondance : certaines des données sont susceptibles d’apparaître plusieurs fois. Avec une seule table, on est obligé de répéter ces données dans la tables, alors qu’avec plusieurs tables on peut simplement indiquer plusieurs l’identifiant de la ligne d’une autre table contenant les informations à répéter. Eviter les redondances permet de gagner de la place, et facilite le maintien de la cohérence de la base.
  • Rapidité d’accès : plus une base est grosse, moins l’accès est efficace. L’accès à plusieurs tables plus petites est généralement plus rapide que l’accès à une seule table contenant toutes les
  • Blocage d’accès : pour garantir la cohérence des données, il peut être nécessaire de bloquer l’accès à une table pendant qu’on la modifie. Avec une seule table, c’est toute la base qui est alors bloquées.

La répartition des informations dans plusieurs tables utilise le mécanisme de clef primaire/clef étrangère :

  • Une clef primaire est un ensemble de champs permettant d’identifier de manière unique chaque ligne d’une table. Autrement dit, à chaque valeur de la clef primaire est unique, et correspond donc à une seule ligne de la table.
  • Une clef étrangère est un champ qui sert à désigner des lignes d’une autre table, en utilisant les valeurs de la clef primaire de cette autre table.

Quand les informations de certains champs sont très souvent dupliqués, il est généralement préférable de déplacer ces informations dans une nouvelle table en ajoutant un champ id qui servira de clef primaire, et de remplacer dans la table d’origine les champs dupliqués par une clef étrangère, qui désigne la ligne de la nouvelle table contenant les informations d’origine.

Requètes

Utilisation d’une seule table

La forme générale d’une requète SQL sur une seule table est la suivante ;


SELECT champ,champ,champ FROM table
WHERE condition
ORDER BY champ

On notera que les mots-clefs sont par convention mis en majuscules, et généralement placés sur une nouvelle ligne.
Une requète sous cette forme affiche certaines colonnes et certaines lignes de la table. Le SELECT permet d’indiquer les colonnes souhaitées, le WHERE précise une condition à tester sur chaque ligne, la ligne n’étant affichée que si la condituion s’évalue à vrai. Enfin, le ORDER (optionnel) permet de classer les lignes obtenues en fonction du critère indiqué.

Jointure

L’utilisation de plusieurs tables dans une même requète se fait généralement en utilisant la jointure (JOIN). La syntaxe est la suivante :

SELECT champ,champ,champ FROM table1
JOIN table2 ON condition

La ligne JOIN sert à indiquer quelle table est à ajouter, et en utilisant quelle paire de clef primaire/clef étrangère

  • Comme on utilise plusieurs tables, il est parfois nécessaire d’indiquer pour les champs de quelle table ils sont issus. Cela se fait avec la notation table.champ
  • La partie condition est de la forme table1.id = table2.id_table1 avec id la clef primaire de table1 et id_table1 la clef étrangère de table2 désignant des lignes de table1
  • La signification de la jointure est donnée par la paire clef étrangère/clef primaire, et non seulement par la paire table1/table2. En effet, il est possible de joindre les mêmes tables de plusieurs manières si il y a plusieurs liens entre les deux

L’ajout de la ligne JOIN .. ON .. complète chaque ligne de table1 avec toutes les informations des lignes de table2 pour lesquelles les clefs étrangère correspondent à la clef primaire de la ligne de table1. Si il y a plusieurs lignes de table2 qui correspondent, la ligne de table1 est répétée et complétée par chaque ligne possible. Dans la jointure par défaut (INNER JOIN), les lignes de table1 ou table2 sans correspondance ne sont pas conservées.

Une table obtenue par jointure (ajout de la ligne JOIN ON) se comporte exactement comme une table classique (attention toutefois au nommage des champs : il faut éviter les ambiguités). En particulier, on peut faire plusieurs jointures dans une même requète. Il faut alors faire attention à l’ordre d’ajout des clauses JOIN, chacune utilisant la table obtenue uniquement avec les JOIN précédant.

Calcul et alias

Il est possible dans la clause SELECT d’indiquer des expressions à calculer au lieu de champs. Il est alors courant de renommer le résultat. La syntaxe est de la forme expression AS alias, avec expression un calcul à effectuer (éventuellement avec des appels de fonction) et alias le nom à donner au résultat dans la table qui sera affiché (nom de la colonne)

Les alias peuvent aussi être utilisés pour les tables, ce qui permet de se servir plusieurs fois de la même table dans une requète.

Les calculs sont habituellement effectués pour chaque ligne.

Agrégation

Certains calculs nécessite des opérations sur toute une colonne, comme la moyenne ou la somme. Ces opérateurs sont appelés opérateurs d’agrégation. Leur utilisation est très particulière, puisque le résultat du calcul est une seule valeur pour toute la colonne, au lieu d’un résultat par ligne.
Comme le résultat final de la requète doit être une table, il n’est pas possible d’avoir un opérateur d’agrégation pour certaines colonnes et pas pour d’autres. Donc, si vous utilisez un opérateur d’agrégation il en faut un pour toutes les colonnes.

Remarque : Si vous utilisez un opérateur d’agrégation pour une colonne, MySql ajoute un opérateur d’agrégation par défaut aux colonnes qui n’en comportent pas, sans afficher de message d’erreur. L’opérateur par défaut est de prendre la première valeur de la colonne (sans tri).

Groupements

Le groupement permet, comme son nom l’indique, de regrouper plusieurs lignes en une seule. Toutes les lignes ayant une même valeur pour un champ (ou un calcul) donné seront agrégées en une seule.

Cela nécessite :

  • la ligne GROUP BY champ (placée après le WHERE)
  • l’utilisation d’opérateurs d’agrégation dans le SELECT, sauf pour le champ groupé et les champs dépendant de celui-là

Quand cela est possible, il est recommandé de grouper par rapport à une clef. Par exemple, au lieu de grouper par nom,prenom, on regroupe par l’id de la table personne. Le principal avantage est que le groupement fonctionne même avec des homonymes, une clef primaire garantissant l’unicité de la ligne contrairement au couple nom,prenom. De plus, tous les champs de la table personne dépendant de la clef, ils peuvent être utilisés directement sans opérateur d’agrégation dans le SELECT.
Quand on groupe selon une clef étrangère, on peut de la même manière utiliser directement tous les champs des tables jointes à cette clef étrangère (la clef primaire pour ces tables est égale à la clef étrangère, et donc unique pour le groupe. Les champs de la table sont donc uniques).

Le résultat du GROUP BY est une nouvelle table. Pour faire des tests sur les lignes de ces tables, on utilise le mot-clef HAVING de la même manière que l’on aurait utilisé le mot-clef WHERE. La différence entre les deux est que le HAVING est effectué après le regroupement du GROUP BY, alors que le WHERE est calculé avant. Le HAVING peut donc utiliser les champs agrégés, alors que le WHERE ne peut pas.

Sous-requètes

Il est possible de combiner plusieurs requètes en une seule. Pour cela, on place la requète à évaluer entre parenthèses. Lors de l’exécution, tout se passe comme si la requète était évaluée puis remplacée par le résultat.

Par exemple, si on veut trouver les informations sur la ou les lignes obtenant le maximum pour la colonne valeur :

On cherche la valeur du maximum

SELECT maximum(valeur) FROM table;

On cherche les informations de la ligne pour laquelle la valeur atteint le maximum :

SELECT * FROM table WHERE valeur = (SELECT maximum(valeur) FROM table);

On peut généralement distinguer deux cas de sous requètes :

  • Celles ne retournant qu’une valeur. Elles utilisent généralement un opérateur d’agrégation. La sous-requète remplace alors directement
    une valeur dans un test, par exemple = ou < .
  • Celles retournant un ensemble de valeurs. Ces sous-requètes sont utilisées avec des opérateurs ensemblistes comme IN, NOT IN, >ALL etc…
    Attention dans ce cas, le comportement de la valeur NULL est généralement peu intuitif (et source d’erreur), il est généralement préférable d’exclure les valeurs nulles dans la sous-requète.

Dans tous les cas, il faut bien faire attention à utiliser un seul champ dans la sous-requète.

Ordre des différents calculs lors d’un SELECT

La forme générale d’un SELECT est la suivante :

SELECT champ1,champ2,… FROM table1
JOIN table2 on table2.id =idTable1
JOIN …
WHERE condition1
GROUP BY idTable3
HAVING condition2

  1. Les éventuelles sous-requètes sont calculées, et les parenthèses les contenant sont remplacées par le résultat
  2. Les jointures sont effectuées, dans l’ordre ou elles sont indiquées
  3. Les lignes ne vérifiant pas la condition du WHERE sont ignorées
  4. Les lignes avec une valeur identique pour le ou les champ(s) indiqué(s) dans le GROUP BY sont regroupés. Les champs du SELECT sont calculés en appliquant les opérateurs d’agrégation si nécessaire.
  5. Les lignes ne vérifiant pas les conditions du HAVING sont ignorées. Les lignes restantes sont affichées

Remarque : quand il y a des opérateurs d’agrégation dans le SELECT, et pas de GROUP BY, tout se passe comme si un GROUP BY 1 était ajouté. C’est pourquoi les opérateurs d’agrégation utilisés seuls utilisent toute la table comme un seul groupe.

Revue de presse

MySql TP 1 — Révisions

L’objectif de ce TP est de réfléchir à la conception et l’utilisation d’une base de données pour un site web permettant de consulter, créer et échanger des parcours à énigmes type chasse au trésor ou jeu de piste. Le site (ou une application mobile) permet ensuite de suivre un parcours, en offrant si nécessaire les indices.

La base de donnée initialement prévue utilise les relations suivantes :

  • parcours(id,titre, description, duree)
  • enigme(id, texte, idLieuDépart, idLieuArrivee, duree)
  • lieu (id, lat, lon, description)
  • indice(id, idEnigme, ordre, texte)
  • composition (idParcours, idEnigme, ordre)

Dans les tables indice et composition, les valeurs commencent toujours à 1 et sont consécutives pour un même parcours ou une même énigme

Schéma

Représentez graphiquement le schéma de cette base.

Création de la base

Pour créer la base correspondante sous mysql, vous utiliserez phpMyAdmin disponible à l’addresse suivante : http://ipabdd/phpMyAdmin   (le mot de passe dans le fichier caché .my.cnf dans votre répertoire home sous linux.)

Créez la base de données sous mysql (avec phpMyAdmin). Vous ferez particulièrement attention au choix des types de données et des clés primaires. N’oubliez pas de mettre les clés qui peuvent l’être en auto_increment.

Import des tables

Pour remplir la base, utilisez la fonction mysqlimport (ou la fonction d’import de phpMyAdmin) et les fichiers tabulés présents dans /export/commun/mmi/bd  correspondants à chaque table. Attention, les fichiers utilisent la tabulation comme séparateur (\t).

 

Requètes

Ecrivez et testez les requètes donnant les informations suivantes :

  1. Requètes simples (une seule table)
    • La liste des parcours disponibles, classés par durée
    • La liste des lieux, avec leur description
    • La liste des énigmes de moins de 5 minutes
    • La description des trois lieux les plus à l’ouest (plus petites longitude)
  2. Jointures
    • Toutes les énigmes, avec la description du lieu de départ pour chacune.
    • Le texte de chaque énigme, avec tous les textes des indices affichés dans l’ordre.
    • La description de chaque parcours, avec la description du lieu de départ
    • Le lieu de départ et d’arrivée de toutes les énigmes ayant au moins un indice
    • Les lieux jamais utilisés
  3. Agrégation et groupements
    • Le nombre de parcours dans la base
    • Pour chaque parcours, le nombre d’énigme.
    • Pour chaque énigme, le nombre d’indices.
    • Pour chaque parcours, la somme des durées des énigmes
    • Pour chaque lieu, le nombre de fois où il sert de point de départ pour une énigme
  4. Sous-requètes
    • l’énigme avec le plus d’indices
    • le lieu d’arrivée de la dernière étape de chaque parcours
    • le parcours avec le plus d’étapes

Insertion

Ajoutez une énigme à la fin d’un parcours.

Quelles difficultés poserait une insertion au milieu d’un parcours ?

Requètes plus complexes

Comment trouver dans la base (donnez la ou les requètes si possible, et sinon expliquez ce qui pose problème et comment le résoudre):

  • Si un parcours est une boucle ou pas ?
  • Les différents “sauts” dans les parcours, c’est à dire toutes les énigmes qui ne partent pas du lieu d’arrivée de l’énigme précédente

Changement de structure

On envisage de remplacer le champ ordre de la table composition par un champ idEnigmeSuivante.

  1. Peut-on toujours répondre aux mêmes questions qu’avec la structure précédente ? (en particulier, comment trouver la première et la dernière énigme de chaque parcours ?)
  2. Quelles question du 3) deviennent plus facile ? Plus difficiles ?