SQL

sigle de

Structured Query Language

en français, langage de requête structurée

Définition

Le SQL est un langage informatique servant à exploiter des bases de données.

La partie langage de manipulation des données de SQL permet de rechercher, d'ajouter, de modifier ou de supprimer des données dans les bases de données, la partie langage de définition des données permet de créer et de modifier l'organisation des données dans la base de données, la partie langage de contrôle de transaction permet de commencer et de terminer des transactions, et la partie langage de contrôle des données permet d'autoriser ou d'interdire l'accès à certaines données à certaines personnes.

UTILISATION

Le langage SQL s'utilise principalement de trois manières :

un programme écrit dans un langage de programmation donné qui utilise l'interface de programmation du SGBD pour lui transmettre des instructions en langage SQL. Ces programmes utilisent des composants logiciels tels que ODBC ou JDBC. Cette technique est utilisée par l'invite de commande qui permet à un administrateur d'effectuer des opérations sur les bases de données, opérations qu'il décrit en SQL.
technique dite embedded SQL : des instructions en langage SQL sont incorporées dans le code source d'un programme écrit dans un autre langage.
technique des procédures stockées : des fonctions écrites en langage SQL sont enregistrées dans la base de données en vue d'être exécutées par le SGBD. Cette technique est utilisée pour les triggers - procédures déclenchées automatiquement sur modification du contenu de la base de données.

Syntaxe générale

Les instructions SQL s'écrivent d'une manière qui ressemble à celle de phrases ordinaires en anglais. Cette ressemblance voulue vise à faciliter l'apprentissage et la lecture. On classe les ordres suivant 4 domaines : LDD, LMD, LCD, LCT.

Les instructions de manipulation du contenu de la base de données commencent par les mots clés SELECT, UPDATE, INSERT ou DELETE qui correspondent respectivement aux opérations de recherche de contenu, modification, ajout et suppression. Divers mots clés tels que FROM, JOIN et GROUP permettent d'indiquer les opérations d'algèbre relationnelle à effectuer en vue d'obtenir le contenu à manipuler.

Les instructions de manipulation des métadonnées - description de la structure, l'organisation et les caractéristiques de la base de données - commencent avec les mots clés CREATE, ALTER ou DROP qui correspondent aux opérations d'ajouter, modifier ou supprimer une métadonnée. Ces mots clés sont immédiatement suivis du type de métadonnée à manipuler - TABLE, VIEW, INDEX, ...

Les mots clés GRANT et REVOKE permettent d'autoriser des opérations à certaines personnes, d'ajouter ou de supprimer des autorisations. Tandis que les mots clés COMMIT et ROLLBACK permettent de confirmer ou annuler l'exécution de transactions.

Le langage de définition de données (LDD) est un langage orienté au niveau de la structure de la base de données. C'est-à-dire que les commandes manipulent les structures de données et non les données elles-mêmes. Tout d'abord, on peut définir le domaine des données (ensemble des valeurs que peut prendre une donnée : nombre, chaîne de caractères, date, booléen). Ensuite, on regroupe des données ayant un lien conceptuel au sein d'une même entité. Puis on peut définir les liens entre plusieurs entités de nature différente. Enfin, on peut ajouter des contraintes de valeur sur les données.

Ordres SQL :

CREATE : création de la structure
ALTER : modification de la structure
DROP : suppression des données et de la structure
RENAME : renommage, qui peuvent porter sur les objets de type suivants :

TABLE : table
INDEX : index
VIEW : table virtuelle
SEQUENCE : compteur ou de suite de nombres
SYNONYM : synonyme
USER : utilisateur

Exemples :

Création d'une vue, à partir d'une table :

Les vues sont des tables virtuelles issues de l'assemblage d'autres tables en fonction de critères. Techniquement les vues sont créées à l'aide d'une requête SELECT. Elles ne stockent pas les données qu'elles contiennent mais conservent juste la requête permettant de les créer.

CREATE VIEW IG1
AS SELECT Numero, Nom, Prenom, age
FROM eleve

Modification d'une table, avec ajout d'une colonne de type entier, pas obligatoirement renseignée :

ALTER TABLE table1 ADD COLUMN colonne5 INTEGER NULL;

Suppression d'un objet :

DROP VIEW nom_vue;

Renommage d'un objet :

RENAME VIEW nom_vue TO nouveau_nom;

Typage des champs pour la création d'une table

Lors de la création d'une table, il faut en particulier décider du type des champs, qui sera approprié aux données et à leur manipulation. Si par exemple, on décide de stocker des données définissant des individus dans une base de données, on pourra définir les données de l'entité individu comme suit dans une table 'individu', comportant les champs :

id : entier
Numéro_de_sécurité_sociale : chaîne de caractères
Nom : chaîne de caractères
Prénom : chaîne de caractères
Date de naissance : date. La date de naissance doit être forcément antérieure à la date du jour

Maintenant, considérons l'entité voiture définie comme suit, modélisée par la table 'voiture', comportant les champs :

Id_proprietaire : entier
Marque : chaîne de caractères
Modèle : chaîne de caractères
Centralisation : booléen

On peut maintenant considérer que les tables sont reliées à travers ces ID de propriétaires. Mais mysql ne fait pas la relation entre les deux tables. Il va falloir lui expliquer cette relation dans une requête SQL : on va faire ce qu'on appelle une jointure entre les deux tables. Il existe plusieurs types de jointures, qui nous permettent de choisir exactement les données que l'on veut récupérer. Deux des plus importantes sont :

les jointures internes : elles ne sélectionnent que les données qui ont une correspondance entre les deux tables.

les jointures externes : elles sélectionnent toutes les données, même si certaines n'ont pas de correspondance dans l'autre table.

Par exemple, si une personne dans la table individu ne possede aucune voiture, elle n'apparaitra pas dans les résultats de la requête. La jointure interne force les données d'une table à avoir une correspondance dans l'autre. Par contre avec la jointure externe, on récupère toutes les données de la table individu, même s'il n'y a pas de correspondance dans l'autre table voiture.

On peut dire que la jointure interne est plus stricte et que la jointure externe est plus permissive.

Une jointure interne peut être effectuée de deux façons différentes :

à l'aide du mot-clé WHERE c'est l'ancienne syntaxe.

à l'aide du mot-clé JOIN : c'est la nouvelle syntaxe qu'il est recommandé d'utiliser.

Jointure interne avec WHERE (ancienne syntaxe) :

SELECT voiture.Marque, individu.Numéro_de_sécurité_sociale FROM individu, voiture WHERE voiture.Id_proprietaire = individu.id;

On récupère bien la marque de la voiture ainsi que le numéro de sécurité sociale.

Jointure externe avec JOIN (nouvelle syntaxe) :

SELECT voiture.Marque, individu.Numéro_de_sécurité_sociale FROM individu INNER JOIN voiture ON voiture.Id_proprietaire = individu.id;

On récupère bien la marque de la voiture ainsi que le numéro de sécurité sociale.

Les jointures externes permettent de récupérer toutes les données, même celles qui n'ont pas de correspondance. On pourra ainsi obtenir une personne dans la liste même s'il ne possède pas de voiture.

SELECT voiture.Marque, individu.Numéro_de_sécurité_sociale FROM individu LEFT JOIN voiture ON voiture.Id_proprietaire = individu.id;

individu est appelée la « table de gauche » et voiture la « table de droite ». Le LEFT JOIN demande à récupérer tout le contenu de la table de gauche, donc tous les individus, même si ces derniers n'ont pas d'équivalence dans la table voiture.

RIGHT JOIN : récupérer toute la table de droite :

Le RIGHT JOIN demande à récupérer toutes les données de la table dite « de droite », même si celle-ci n'a pas d'équivalent dans l'autre table.

SELECT voiture.Marque, individu.Numéro_de_sécurité_sociale FROM individu RIGHT JOIN voiture ON voiture.Id_proprietaire = individu.id;

La table de droite étant voiture, on récupère donc toutes les voitures, même celles qui n'ont pas de propriétaire associé (individus).

Le langage de manipulation de données (LMD) est un sous ensemble constitué des commandes logiques opérant la manipulation des données (lectures et écritures) dans une base de données, par exemple une base de données relationnelle (SGBDR).

Dans le langage SQL ce sont par exemple les commandes : SELECT, INSERT, DELETE, UPDATE et MERGE. Parmi ces instructions, celles qui modifient les données doivent être validées à l'issue d'une transaction pour être prises en compte.

Exemples :

Insertion d'une ligne dans la table acteurs :

INSERT INTO acteurs (Nom,Prénom) VALUES ('Dupont','Kevin');

Suppression des lignes répondant à une condition :

DELETE FROM Store_Information WHERE Store_Town = 'Paris';

Mise à jour des lignes répondant à une condition :

UPDATE Eleves SET Prénom = "Henry" WHERE Nom = "Leroy";

Le Langage de contrôle de données (ou LCD) est un langage de programmation et un sous-ensemble de SQL pour contrôler l'accès aux données d'une base de données. On trouve typiquement, les commandes suivantes :

GRANT : autorise certains utilisateurs à effectuer une certaine opération
DENY : empêche certains utilisateurs à effectuer une certaine opération
REVOKE : annule une commande de contrôle précédente
COMMIT : valide une transaction en cours dans la base
ROLLBACK : annule une transaction en cours
LOCK : pour poser un verrou sur un objet

Exemples :

GRANT UPDATE (Nom,Prénom) ON Controleurs TO Pierre,Paul WITH GRANT OPTION;

DENY SELECT ON Person.Adress;

REVOKE DELETE ON individu FROM Paul;

START TRANSACTION ;

UPDATE table2 SET summary='Excellente page' WHERE type=1;

COMMIT ;

LOCK TABLEStableWRITE ;

Le langage de contrôle des transactions (LCT) est utilisé pour le contrôle transactionnel dans une base de données, c’est-à-dire les caractéristiques des transactions, la validation et l’annulation des modifications.

Exemples d'instructions du LCT : COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION

Définition du niveau d'isolement le moins restrictif et mode d'accès d'une transaction :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SAVEPOINT Cette instruction permet de placer une étiquette savepoint dans le corps du code. Elle permet au traitement d'annuler, avec l'instruction ROLLBACK, les modifications effectuées à partir de cette étiquette :

SAVEPOINT mise_a_jour;
UPDATE blabla...;
ROLLBACK TO SAVEPOINT mise_a_jour;

VOILÀ C'EST TERMINÉ BANDE DE BONOBOS !!