vendredi 6 janvier 2012

Les Fonctions en SQL : les Index - les vues SQL - SGBD SQL requêtes

1- Introduction

2- Modèle utilisé

3- Les index
3.1- Qu'est ce qu'un index ?
3.2- Comment créer un index ?
3.3- Comment effacer un index ?
3.4- Quelques règles pour le choix d'un index

4- Les vues

4.1- Qu'est ce qu'une vue ?
4.2- La création d'une vue
4.3- Créer une vue modifiable
4.4- L'option with check option
4.5- La suppression d'une vue
4.6- Quelques remarques

5- Les fonctions en SQL

5.1- Les fonctions mathématiques
5.2- Fonctions sur les chaînes de caractères
5.3- Les fonctions sur les dates
5.3.1 Date et heure courante
5.3.2 Calcul sur les dates
5.3.3 Extraction sur les dates
5.3.4 Les fonctions de formatage
6- Les requêtes de requêtes
1 - Introduction

Dans les cours précédents, nous avons vu SQL sous ses aspects les plus courants, il nous reste à voir d'une part les différents outils nous permettant d'optimiser ou de faciliter l'accès à une base de donnée, ainsi que les fonctions de manipulation ou de conversion de dates ou autres.


2 - Modèle utilisé

Pour cette partie nous reprendrons principalement le modèle Garage :

VEHICULE (Code, Type, M arque, Puissance) => les types de véhicule
GARAGE (Code, Nom, Adresse, Ville, Cpostal, Marque) => les garages
PLAQUE (Immat, CodeV#, Nom, Prenom, Adresse, Ville, Cpostal, Date) => véhicules immatriculé

CodeV# de PLAQUE est clé étrangère et référence Code de VEHICULE.


3 - Les index

3.1 Qu'est ce qu'un index ?

Un index correspond à un moyen d'accéder plus rapidement à une information. Imaginons une table adresse ayant cette structure :

Dans la table GARAGE, Code est clé primaire, ce qui fait que la table est triée par code et donc toute recherche à partir du code sera très rapide (méthode de tri dichotomique, par arbre ou toute autre technique).

De façon automatique, un index est créé sur une clé primaire (simple ou composé), ce qui fait que toute recherche à partir d'une clé est très rapide. Ceci n'est pas vrai à partir des autres champs, puisque dans ce cas, il faudra parcourir l'ensemble de la table pour trouver la ou les bonnes informations. Une technique consiste à créer un index supplémentaire sur la table afin d'accélérer les recherches.

Dans notre exemple, un index pourra être créé sur les champs ville si des recherches fréquentes sont sur ce champ.

Attention, néanmoins créer des index à deux inconvénients :

  • Les accès à la table sont ralentis en cas de mise à jour de données (Mise à jour, insertion ou effacement de données), puisque dans ce cas, non seulement la table est mise à jour, mais également les "tables d'index".
  • La base grossit en fonction des index, puisque les index sont stockés dans la base, dans des tables supplémentaires

La création d'index doit donc être réfléchie, et se réalise généralement sur des champs de petite taille afin de minimiser à la fois la taille de la base et les accès. Il s'agit donc d'un compromis.

3.2 Comment créer un index ?

La création d'un index correspond à une modification sur une table. La commande est

CREATE [ UNIQUE ] INDEX
ON ( nom du champ)

UNIQUE permet d'ajouter une clause d'unicité (ce qui est le cas pour une clé)

Exemple: création d'un index sur le champ Ville:
CREATE INDEX index_garage_ville
ON GARAGE (Ville);


3.3 Comment effacer un index ?

De la même manière que pour effacer une contrainte, à l'aide de la commande DROP

DROP INDEX

Ex : DROP INDEX index_garage_ville
3.4 Quelques règles pour le choix d'un index
  • Ne pas créer d'index pour de petites tables (< 300 enregistrements)
  • Ne pas créer d'index sur un champ qui ne possède que quelques valeurs différentes (ex : le nombre d'étoiles des restaurants
  • Indexer les colonnes intervenant souvent dans des clauses Where ou Order By
  • Indexer les colonnes de jointure

De manière générale, La création d'Index répond à des choix d'optimisation des accès sur la base. On y fait appel à partir du moment où des lenteurs excessives sont constatées.


4 - Les vues


4.1 Qu'est ce qu'une vue ?

Le concept de vue revêt une importance particulière dés que l'on envisage des notions de facilité d'accès à la base et de sécurité des données.

Une vue est une table virtuelle construite à partir des tables d‘une base de données existante. Elle n‘a pas d‘existence physique permanente, mais est construite dynamiquement, en cas d'utilisation, à partir des données figurant dans la base.

Par contre une vue s‘utilise, à quelques conditions près, avec les mêmes instructions qu‘une table (SELECT, DELETE, INSERT, UPDATE).

Une vue peut être :

  • La table GARAGE limitée au département de la Meuse.
  • La table GARAGE ne comprenant comme champs que le code, le nom et l'adresse e-mail
  • Le propriétaire avec ses coordonnées associées aux caractéristiques de sa voiture. (Dans ce cas, la vue provient de 2 tables)

En synthèse, une vue est une table dynamique permettant de montrer à l'utilisateur le contenu d'une table limitée à ce que le créateur de la vue désire. L'utilisateur en manipulant une vue aura l'impression d'intervenir sur une table à part entière.


4.2 La création d'une vue

Créer une vue consiste à identifier sous un nom une requête SQL qui définit la vue.

CREATE VIEW nom_vue (liste_colonne)
AS commande SELECT
WITH (optons)…
  • nom_vue correspond au nom de la vue (table virtuelle) créée
  • liste_colonne est facultatif. Dans le cas où les noms de champ ne sont pas donnés, ils héritent des noms du SELECT
  • commande correspond à une commande SELECT sur une ou plusieurs table
  • WITH permet d'ajouter des contraintes sur la vue
Ex : Vue sur les clients du département de la Meuse :

CREATE VIEW GARM EUSE
AS SELECT FROM GARAGE WHERE Cpostal LIKE '55%';
Dans le cas ci-dessus :
  • Tout ajout de Garage peut se faire sur la table.
  • Une mise à jour d'un garage de la Meuse peut se faire sur la table ou la vue
  • La mise à jour d'un garage hors Meuse ne peut se faire que sur la table
Vue uniquement sur les garages (noms et marque)

CREATE VIEW GARMARQUE
AS SELECT Code, Nom, Marque FROM GARAGE;

Dans ce cas,
  • Les mises à jour sur la vue ne peuvent se faire que sur les champs de la vue.
  • L'insertion d'un client dans la vue sera limitée aux champs visibles.
  • Si des champs non présents dans la vue ont une contrainte NOT NUL, l'insertion ne pourra se faire à partir de la vue.
Vue associant la voiture avec son propriétaire

CREATE VIEW VOITNAME
AS SELECT Immat, Nom, Marque, Type, Adresse, Vill Cpostal
FROM PLAQUE, VEHICULE
WHERE Code = CodeV;

Dans ce cas, les mises à jours sont difficiles, voire impossibles puisque l'on n'a qu'une partie des informations des 2 tables. Ce type de vue est essentiellement pour faciliter les recherches de données.


4.3 Créer une vue modifiable

Pour qu'une vue soit modifiable, indépendamment des contraintes d'intégrité sur les tables, il faut respecter un certain nombre de règles :
  • Pas de directives distinct
  • Pas de fonctions d'agrégat (AVG, COUNT …)
  • PAS de GROUP BY, ORDER BY, HAVING
  • La vue ne doit pas être déclarée en lecture seule (WITH READ ONLY)

4.4 L'option with check option

Nous avons vu précédemment qu'il est possible de modifier ou d'ajouter des enregistrements n'appartenant pas à la vue (ajout d'un garagiste n'appartenant pas à la Meuse dans la vue GARMEUSE).

La directive WITH CHECK OPTION empêche un ajout ou une modification non conforme à la définition de la vue.

EX :

CREATE VIEW GARMEUSE
AS SELECT FROM GARAGE WHERE Cpostal LIKE '55%'
WITH CHECK OPTION;


4.5 La suppression d'une vue

Important : la suppression d'une table ne supprime pas la déclaration de la vue correspondante. Il est donc dans ce cas nécessaire si on veut garder une cohérence dans la base de supprimer la vue correspondante.

La commande utilisée est :

DROP VIEW nom_vue


4.6 Quelques remarques

La notion de vue est implémentée différemment suivant les bases de données.

Si toutes les grandes bases du marché implémentent la notion de vue, les accès, et plus particulièrement les mises à jour sont autorisées différemment.

Postgresql ne supporte pas de manière native la mise à jour des données à travers une vue. Il faut créer des fonctions pour assurer les mises à jour.


5 - Les fonctions en SQL

Dans le cours précédent, nous avons vu un certain nombre de fonctions de base (Month, year…). Il existe en SQL une multitude de fonctions permettant de manipuler toutes sortes d'informations.

Les exemples ci-après sont ressortis de la doc de Postgresql.


5.1 Les fonctions mathématiques

Toutes les principales fonctions existent. Ci-joint un extrait

D'autres fonctions permettent également de mettre en forme les résultats :


5.2 Fonctions sur les chaînes de caractères

Précédemment nous avons vu les fonctions UPPER et LOWER. D'autres fonctions courantes existent comme :
  • La concaténation de chaînes de caractères : string1 || string2
  • La conversion d'une chaîne avec la première lettre en majuscule : initcap (text)
  • Le remplacement d'une chaîne par une autre :
  • L'extraction d'une partie d'une chaîne de caractère : substring
  • La longueur d'une chaîne de caractère : length

5.3 Les fonctions sur les dates

Comme nous avons vu, la date n'est pas nécessairement représentée dans le format que l'on désire. De plus, la date renvoyée peut être codée de différentes façons. Un certain nombre de fonctions permettent de manipuler ces dates :

Suivant la base, et le paramétrage, les dates sont exprimées en format anglo-saxon (mois, jour, année) ou français. De plus, certains paramètres permettent de changer les paramètres et de manipuler ces dates. Attention, ces différentes fonctions ne sont pas les même en fonction des bases, et il sera nécessaire de consulter l' de afin de voir leur syntaxe précise.


5.3.1 Date et heure courante

current_date, current_time, current time_stamp permettent de récupérer les informations courantes now() est équivalent à current_timestamp (date et heure courante)

Ex : récupération de la date courante :

SELECT current_date ;

requête de mise à jour du champ date à la date du jour

UPDATE
SET date = current_date W HERE … ;


5.3.2 Calcul sur les dates

Il est possible également de faire des calculs sur les dates:

UPDATE
SET date = current_date +5 WHERE … ;

Cette requête initialisera le champ date à date du jour + 5 jours

UPDATE COMPETITION
SET datcomp = datcomp + INTERVAL '1 month'
WHERE refcomp = 'comp9';

Dans ce cas tiré de la base SKI, le mois de datcomp sera augmenté de 1, indépendamment du nombre de jours du mois.


5.3.3 Extraction sur les dates

Il est possible à partir d'une date de récupérer certaines informations de celles-ci :

DAY, MONTH, YEAR, WEEK (no de la semaine), DOY (nombre de jour depuis le début de l'année), Hour, Minute

Ces fonctions doivent être exécutées sur des champs date ou heure.

Certaines bases de données les implémentent de façon native
MONTH (date) => le mois de la date passée en paramètre.

Postgresql utilise la fonction EXTRACT :

EXTRACT (MONTH FROM DATE)

ex : SELECT Refcomp, EXTRACT (MONTH from datcomp) from COMPETITION;

5.3.4 Les fonctions de formatage

Les fonctions de formatage fournissent un ensemble d'outils puissants pour convertir différents types de données (date/heure, entier, nombre à virgule flottante, numérique) en des chaînes formatées et pour convertir des chaînes formatées en des types de données spécifiques. Ces fonctions suivent toute une convention d'appels commune : le premier argument est la valeur à formater et le second argument est un modèle définissant le format de sortie ou d'entrée.

Tableau de formatage sous Postgresql (ces fonctions existent aussi sous Oracle):


Modèle les plus courants pour les formats de type date/heure :


Exemple d'utilsation :

SELECT refcomp, to_CHAR (datcomp,'Day,Month,YYYY') from competition;

Permet de lister les dates sous forme jour (littéral), mois (littéral) et année

UPDATE COMPETITION
SET datcomp = TO_DATE ('05 Dec 2000' 'DD M on YYYY')
WHERE refcomp = 'comp9';

Cette requête permet de mettre à jour suivant le format donné en paramètre

Il est possible également de formater la date pour toutes les requêtes :

SET datestyle TO dmy;


6 - Les requêtes de requêtes

Important, le résultat d'une requête est une table.
Cette table résultante peut donc être une table appelée dans une requête

Exemple, reprenons la requête du TP Postgresql :


Donnez le nombre moyen de participants aux compétitions se déroulant à tignes.

Si on découpe la requête, il faut dans un premier temps considérer le nombre de participants concernant les compétitions de Tignes Cette requête s'exprime sous la forme :

select count(nomski) as compteur
from classement cl, competition co
where co.refcomp=cl.refcomp
and nomstat='tgnes'
group by co.refcomp


Il faut ensuite prendre la moyenne des nombres renvoyés par la requête, ce qui donne :

select avg(compteur)
from (
select count(nomski) as compteur
from classement cl, competition co
where co.refcomp=cl.refcomp
and nomstat='tignes'
group by co.refcomp
) as req2;

La requête précédente constitue la table REQ2 qui est utilisée comme table dans la requête principale.

Ce type de requête est utilisée dés lors où un résultat d'un résultat est nécessaire.

Important, la table créée n'a d'existence que pendant la requête.

0 commentaires:

Enregistrer un commentaire

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites

 

IP