ELEMENTS SUR SQL

Elements introductifs sur les bases de données

On trouve ici quelques éléments simples sur SQL. Les informations présentées sont très séléctives et partielles. La réference la plus complète : www.mysql.org.

"Structured Query Langage" ou SQL est une norme définissant un ensemble de commandes pour interroger et gérer une base de données, ces commandes étant disponibles pour l'ensemble des SGBD standards (Système de gestion des bases de données) et implémentant la norme. Les bases de données sont structurées en tables, une table est un objet à plusieurs colonnes ou champs et contenant un ensemble d'enregistrements-lignes (ou tuples ou occurences), chaque enregistrement sur la table renseignant les différents champs. On considère ici une table qu'on appelle Client :

Ici, on a 4 colonnes-champs : nom, prenom, age, pays et 4 enregistrements-lignes qui correspondent aux noms 'Dupont', 'Durand', etc...

La base de données que l'on considère ici à titre d'exemple contient la table Client et une autre table :

La table Client retient des informations sur chaque client, la table Achat retient des informations sur les achats. Noter qu'à partir d'un enregistrement de Achat, il est possible de retrouver les informations sur le client qui a fait cet achat, via le fait que l'on dispose du nom de la personne qui a acheté dans la table Achats. Une base de données sépare donc les données en tables pour assurer un stockage optimal de l'information, mais des informations évoluées reposant sur les informations contenues dans plusieurs tables peuvent être retrouvées : par exemple, on verra qu'à partir de la base de données courante, il est possible d'obtenir l'age moyen des individus qui ont acheté un produit particulier.

SQL permet de créer des tables, ie de définir le nombre et le nom des colonnes de chaque table et le type de données que l'on va avoir dans chaque colonne. SQL permet d'insérer des enregistrements dans les tables. Le langage permet enfin de consulter les informations qui ont été rentrées dans une table.

Le schéma relationnel d'une base de donnée est une représentation schématique de la base de donnée. On donne le schéma de la base de données minimaliste utilisée ici :

La démarche de conception d'une base de donnée consiste à établir un schéma relationel pour la base de donnée dont on a besoin. L'établissement de ce schéma est assez direct dans le cas où l'information manipulée n'est pas trop complexe, au delà, des méthode de conception existent : Merise, UML avec des techniques pour passer des schéma issus de ces méthodes vers des schéma relationnels.

Le document qui suit présente les fonctions de base du SQL : consultation de table, modification de table, création de tables. Il existe beaucoup d'autres fonctions définies par le langage SQL. Ces fonctions ne sont pas évoquées ici et permettent une gestion beaucoup plus poussée des bases de données (gestion des droits de différents individus, gestion des évenements...), des réferences peuvent être trouvées sur le Web ou dans des ouvrages.

Notez que toute requête SQL doit être terminée par ';'.

INSTRUCTION DE BASE : SELECT

La requête SELECT demande le renvoi d'enregistrements au sein d'une table, son schéma général est :

Ici, on appelle la récupération des valeurs des champs spécifiés après le SELECT, pour les enregistrements contenus dans la table indiquée après le FROM et pour les enregistrements répondant aux conditions spécifiées après le WHERE.

Par exemple :

Cette requête renvoie :

Autre exemple :

Cette requête renvoie :

Le logiciel auquel est passé la commande va d'abord éliminer les éléments qui ne répondent pas à la condition spécifiée par le WHERE :

Le logiciel ne renvoie que les colonnes attribuées spécifiées après le SELECT.

Quand on ne veut pas choisir quels attributs/ colonnes séléctionner :

Ici, * signifie tous les champs, la requête correspond donc au fait de renvoyer tous les champs pour l'ensemble des enregistrements tels que le champ "age" est rempli d'une valeur supérieure ou égale à 78. Ce qui se passe :

On peut enchainer des condition avec des opérateurs logiques :

On peut classer les occurences renvoyées :

On constate que l'on a les enregistrements qui sont classées par prenom dans l'ordre alphabétique.

On peut classer les occurences renvoyées par ordre décroissant :

Le mot clé DESC demande le classement dans l'ordre décroissant.

INSTRUCTION DE BASE : INSERT

Schéma général de la commande qui sert à rentrer un enregistrement dans une table :

Exemple : On travaille sur la table INDIVIDUS, on veut rentrer un nouvel inividu (ie un nouvel enregistrement dans cette table) : Auburtin Alexandre agé de 20 ans, français, en fonction du schéma de la commande decrit plus haut, la commande va être :

A supposer que l'on veuille rentrer un individu dont on ne connaît ni l'âge ni le pays : Renoud Lucas, on passe la commande :

INSTRUCTION DE BASE : DELETE

Schéma général de la commande qui sert à éliminer un enregistrement d'une table :

Cette requête permet d'éliminer l'ensemble des enregistrements-lignes de la tables. La requête élimine l'ensemble des enregistrements de la table spécifiée qui respectent les conditions spécifiés.

On veut enlever toutes les occurences des gens qui ont plus de 70 ans (ou 70 ans) dans la table :

On obtient :

INSTRUCTION DE BASE : UPDATE

Schéma général de la commande qui sert à mettre à jours des champs :

On donne un exemple :

On obtient :

JOINTURES, LIENS DE TABLES

On suppose avoir une autre table qui fait réference à la table INDIVIDUS : la table ACHATS qui enregistre des achats, cette table est ainsi renseignée :

On peut avoir besoin de l'information contenue dans plusieurs tables pour répondre à certaines requêtes, on considère l'instruction suivante :

Cette requête renvoie :

Supposons que l'on veuille savoir les idProduits achetés par des luxembourgeois : nous avons besoin de connaître les informations des deux tables Client et Achats.

La requête :

SELECT AVANCE et GROUP BY

On présente ici des opérateurs utiles qui permettent d'obtenir des moyennes sur des colonnes, d'obtenir les valeurs maximales d'une colonne etc...

Le schéma général pour le SELECT MAX est :

On donne un exemple :

Un autre exemple :

Un autre exemple :

Un autre opérateur possible de ce type est le SELECT MIN() On peut raffiner l'utilisation des ces opératuers. Le GROUP BY est possible avec tous les opérateurs présentés plus haut, il consiste à calculer la moyenne, le max, non pas sur l'ensemble des occurences, mais sur chaque groupe d'occurences reliées selon un certain critère. Par exemple, on peut calculer la moyenne d'age en fonction du pays, le max d'age en fonction du pays

Par exemple :

Autre exemple :

REQUETES IMBRIQUES

A partir des requêtes proposées dans la section précédente, il est possible d'envisager des requêtes plus complexes avec une sémantique plus riche. Par exemple, on peut vouloir savoir qui est la personne la plus agée parmis celles de la base. La stratégie d'exploration d'une table qui vient naturellement aux individus consiste à regarder quel est l'age maximum dans la base pour ensuite voir à quelle(s) occurence(s) cet age correspond. SQL fonctionne de la même façon, si dans notre base, on veut connaître le nom de la personne à âge max :

Cette requête renvoie :

INSTRUCTION DE CREATION DE TABLE

L'instruction CREATE TABLE a le schéma suivant :

La définition de champ a minima correspond au fait de donner un nom de champ et un type de champ.

Par exemple :

• nom VARCHAR(100) définit un champ nom qui sera une chaîne de caractères d'au plus 100 caractères.
• age INTEGER définit un champ age qui est une valeur entière.

Les types suivants sont possibles :

Certaines options peuvent être définies pour un champ après la définition de son type :

• NOT NULL empêche l'enregistrement d'occurences pour lequelles le champ ne serait pas renseigner
• PRIMARY KEY définit un champ comme clé primaire de la table

FOREIGN KEY (nomchamp1) REFERENCES [nomTable]
• PRIMARY KEY (nomChamp1,nomChamp2)

Attention, il ne faut pas définir plusieurs clés primaires : il faut qu'il n'y ait qu'une seule définition. La première instruction présentée ici définie nomChamp1 comme une clé étrangère d'une autre table. La deuxième instruction correspond au cas où un seul champ ne suffit pas à définir une clé, mais le cas où plusieurs champs sont nécessaires. Par exemple, dans la plupart des cas où on définit une référence de produit, on a ce champ qui définit une dépendance fonctionnelle sur l'ensemble des autres champs et on peut se servir de ce champ comme clé primaire.

INSTRUCTION DE DESTRUCTION DE TABLE

Cette instruction est à manipuler avec une certaine prudence, elle permet d'éliminer une table et toutes les occurences enregistrées dans cette table. Son schéma est le suivant :

INSTRUCTION DE MODIFICATION DE TABLE

Ici il ne s'agit pas de modifier les occurences d'une table, il s'agit de modifier la définition d'une table. On peut ajouter des champs à une table, enlever des champs d'une table etc... La commande est ALTER TABLE, sa syntaxe est la suivante pour ajouter un champ :

Par exemple, on ajoute un champ date de naissance pour les clients :

La table Client devient alors :

Pour éliminer un champ :

Par exemple, puisque on a rajouter un champ date de naissance, on pourra recalculer l'age des clients à partir de leur date de naissance (se posera un problème pour l'ensemble des clients pour lesquels on n'a pas récupéré la date de naissance au moment de leur enregistrement) :

On passe la requête :

La table Client devient :

Divers

• Description d'une table

L'instruction :

DESC [nomTable]

Renvoie une description de la table. Par exemple :

DESC Client;

Renvoie :

Field	Type	Null	Key	Default	Extra
nom	varchar(200)	YES
prenom	varchar(100)	YES
pays	varchar(70)	YES
dateNaissance	date	YES



• Date

Dans le cas où vous voulez renseigner un champ date, utiliser le format :

'AAAA-MM-JJ'

Par exemple : '2005-06-21' sera interprété comme le 21 juin 2005. Dans le cas d'une date non valide, la date sera '0000-00-00'. Pour le cas d'un format time stamp, on peut communiquer avec en utilisant le format : 'AAAA-MM-JJ HH:MN:SS.nnn'. Pour la manipulation de dates, on dispose de différente fonctions. Soit date un champ DATE, YEAR(date) renvoie l'année de la date, MONTH(date) et DAY(date) renvoient respectivement les mois et dates. Enfin, now() renvoie la date courante.

A supposer que l'on ait une table individus avec un champ dateNaissance. On peut approximer l'age des individus par : YEAR(now())-YEAR(dateNaissance) est une apprxoximation de l'age d'un individu. Une syntaxe avancée pour avoir la moyenne d'age des individus de cette table :

SELECT (YEAR(now())-YEAR(dateNaissance)) AS moyenneAge FROM Individus


• Comparaison à une chaîne de caractères

'%' est un symbole qui signifie toutes les chaines de caractères. Si on veut récupérer tous les individus de Client dont le nom commence par 'D', on utilise LIKE qui compare deux chaines de caractères :

SELECT *
FROM Client
WHERE Client.nom LIKE 'D%';




nom	prenom	pays	dateNaissance
Durand	Henri	Luxembourg

On veut maintenant récupérer l'ensemble des chaines qui contiennent 'on' :

SELECT *
FROM Client
WHERE Client.nom LIKE '%ur%';




nom	prenom	pays	dateNaissance
Durand	Henri	Luxembourg
Auburtin	Alexandre	français

Quelques élements théoriques relatifs à la conception des bases de données

• Notion de dépendance fonctionnelle

La dépendance fonctionnelle est une relation entre attributs. On dit qu'un attribut a1 est en dépendance fonctionnelle sur un autre attribut a2 si pour toute occurence de a1, on a une unique occurence de a1 qui lui correspond. Par exemple, soient un attribut referenceProduit qui correspond à la reference d'un produit et un attribut prix, on a une relation de dépendance fonctionnelle entre referenceProduit et prix. Ceci signifie que pour tout produit, on a un prix unique qui lui est associé. On note : referenceProduit -> Prix.

La notion de dépendance fonctiontielle intervient dans la conception des bases de données. Les bases de données sont un moyen de stocker de l'information à partir d'une modélisation de ces données. La démarche de conception consiste à retenir des donnnées que l'on analyse les relations de dépendances fonctionnelles. On dispose ensuite d'un certains nombres de règles qui nous permettent de déduire des relations de dépendance fonctionnelle des tables de base de données. La conception des tables à partir des bases de données, si elle répond à certaines propriétés, est dite en forme normale. On a plusieurs niveaux de normalisation. La normalisation centrale est la troisième forme normale d'une table. Ces formes normales garantissent une optimisation du stockage de l'information.