Introduction au langage SQL

IntroductionaulangageSQL
SQL (sigle de Structured Query Language, en français langage de requête structurée) est un
langage informatique normalisé servant à effectuer des opérations sur des bases de données
relationnelles. La partie langage de manipulation de données de SQL permet de rechercher,
d'ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles.
En plus du langage de manipulation de données, la partie langage de définition de 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 de données permet d'autoriser ou d'interdire l'accès à certaines
données à certaines personnes.
Créé en 1974, normalisé depuis 1986, le langage est reconnu par la grande majorité des
systèmes de gestion de bases de données relationnelle (abrégé SGBDR) du marché.
En 1979, Relational Software, Inc. (actuellement Oracle Corporation) présenta la première
version commercialement disponible de SQL, rapidement imité par d'autres fournisseurs.
SQL a été adopté comme recommandation par l'Institut de normalisation américaine (ANSI)
en 1986, puis comme norme internationale par l'ISO en 1987 sous le nom de ISO/CEI 9075 Technologies de l'information - Langages de base de données - SQL.
Bases du langage 

Quelques rappels de vocabulaire
o base de données, table, champ (ou colonne), enregistrement (ou ligne)
o structure ou schéma relationnel d'une base
o clé primaire : un ou plusieurs champ déterminent complètement les valeurs des
autres champs de la table
o clé etrangere : il s'agit d'un champ d'une table qui joue le rôle de clé primaire
dans une autre table de la base
Types de données et création d'une table
o numériques entiers : INTEGER (entiers sur 4 octets.), SMALLINT (2 octets),
BIGINT (8),
o décimaux exacts : NUMERIC, DECIMAL (p,q): décimaux sur p chiffres dont
q forment la partie décimale.
o FLOAT(p,q) .
o CHAR(n), VARCHAR(n) : chaine de caractères de longueur fixée ou variable
(n<=255).
Comme il n'y a pas de type logique, on le simule par un CHAR(1) avec 2
valeurs
o DATE : date au format par défaut aaaaa-mm-jj (la fonction DATE_FORMAT
permet des conversions)
o TIME: heure au format hh:mm:ss
1 TIMESTAMP : date et heure sous forme du nb de secondes écoulées depuis le
1/1/1970 (date de création du monde selon UNIX ;-). Lors d'une insertion ou
mise à jour d’un champ TIMESTAMP se met à jour à la date courante
o TEXT : texte de longueur qcq
o
Il est recommandé de placer sur les valeurs des champs les contraintes NOT NULL au
moment de la création (donc éviter la valeur NULL ou absence de valeur qui ne
permet pas les comparaisons), ou de placer des valeurs par défaut avec DEFAULT
valeur ('' ou 0). Bien sûr chaque table doit posséder une clé primaire simple ou
composée, introduite par le mot-clé PRIMARY KEY.
Un exemple, à commenter :
CREATE TABLE b2i (
rne varchar(8) NOT NULL default '',
annee varchar(9) NOT NULL default '',
niv1 smallint(6) NOT NULL default '0',
niv2 smallint(6) NOT NULL default '0',
amelioration char(1) NOT NULL default 'N',
observation text,
PRIMARY KEY (rne,annee)
)

Opérateurs logiques
Ils permettent de construire des expressions logiques, donnant des valeurs logiques
vrai ou faux.
Divers cas :
AND, OR, NOT : ils portent sur les conditions simples pour donner une
condition composée :
o dans une condition simple exprimée sur des champs :
o comparaison de valeurs =, <, >,
o valeurs situées entre BETWEEN ....AND....
o ou appartenant (ou non) à un ensemble IN, NOT IN
o semblable à LIKE
o existence EXISTS
Règle générale d'écriture des commandes
o

Toute commande SQL commence par un mot-clé qui désigne l'opération à exécuter.
On précise ensuite sur quelles données cette opération doit s'exercer. Elle doit
obligatoirement se terminer par ;
Exemple d'interrogation simple
SELECT numArticle, libellé, quantité
colonnes indiquées,
FROM inventaire
la base de données courante,
WHERE Ville= "Paris"
le champ Ville a la valeur "Paris"
ORDER BY quantite;
valeurs croissantes du champ quantité
//
projection sur les
// de la table inventaire de
// sélection des lignes où
// ordonné suivant les
2 
Syntaxe générale de la commande d'interrogation SELECT
SELECT [DISTINCT] liste_champs | *
// projection
FROM liste_tables
// table
[ WHERE condition
// condition simple ou composée portant sur
les attributs
[AND|OR ......] ]
// sur une table (sélection) ou 2
(jointure)
[GROUP BY champ]
// décrit le regroupement des lignes
[HAVING champ]
// critères de sélection portant sur les
groupes
[ORDER BY liste_champs]; // précise le tri (multi-critère)
o
o

liste désigne une énumération dont le séparateur est la virgule
l'ordre des clauses est impérative, la plupart sont facultatives
Syntaxe générale de la commande d'interrogation INSERT
La syntaxe de base de l'ordre SQL d'insertion de données dans une table est la suivante :
INSERT [INTO] nom_de_la_table_cible [(liste_des_colonnes_visées)]
{VALUES (liste_des_valeurs) | requête_select | DEFAULT VALUES }

la liste des colonnes visées peut être omise à condition que l'ordre d'insertion concerne
toutes les colonnes de la table.
Ex :
INSERT INTO T_MODE_PAIEMENT
VALUES
('CB'
, 'Carte bancaire') ;
INSERT T_TITRE (TIT_CODE, TIT_LIBELLE)
VALUES ('M.'
, 'Monsieur',
'Mlle.' , 'Mademoiselle'
'Mme.' , 'Madame') ;
INSERT INTO
T_CLIENT (CLI_ID, TIT_CODE,
CLI_NOM, CLI_PRENOM)
SELECT
PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM
T_PROSPECT ;

Syntaxe générale de la commande d'interrogation DELETE
La syntaxe de base de l'ordre SQL de suppression de données dans une table est la suivante :
DELETE [FROM] nom_table_cible
[WHERE condition]
Ex :
DELETE FROM T_PROSPECT ;
Cette requête supprime tous les prospects.
3 DELETE FROM T_PROSPECT
WHERE PRP_PRENOM LIKE '%d'
Cette requête supprime tous les prospects dont le nom se termine par la lettre 'd'.

Syntaxe générale de la commande d'interrogation UPDATE
La syntaxe de base de l'ordre SQL de modification de données dans une table est la suivante :
UPDATE nom_table_cible
SET colonne = valeur [, colonne2 = valeur2 ...]
[WHERE condition]
Ex :
UPDATE T_TARIF
SET
TRF_PETIT_DEJEUNE = 8 ;

Utilisation de quelques fonctions Mysql
Les fonctions permettent en général d'agir sur les valeurs extraites par l'exécution
d'une commande.
Noter aussi qu'on peut introduire des chaines constantes.
SELECT 225*25 ;
SELECT version(), now(), curdate() as date, curtime() as heure ;
SELECT DATE_FORMAT(now(),'%d/%m/%Y') AS date ;
SELECT concat('Voici la date d\'aujourd\'hui :',
DATE_FORMAT(now(),'%d/%m/%Y')) AS date ;
SELECT DATE_ADD(now(), INTERVAL -1 DAY); autre période second,
minute,hour,month,year
SELECT substring('chaine', 1, 2) ; autres fonctions LENGTH
SELECT if(12 > 10, '12 est plus grand', '12 est plus petit');
Il peut etre utile de regrouper plusieurs informations et même plusieurs champs. Par
exemple sur la base établissements
SELECT * FROM etablissements WHERE left(CodePostal,2)='94' ORDER BY
Ville;
SELECT concat(nom,' ', type,' ',ville) FROM etablissements ORDER BY
nom;
4 Exemplederequêtes
1. Voici la structure de la base CDI
documents (id_document,titre, auteur, genre, editeur, disponible)
inscrits (id_inscrit, nom, prenom, classe)
prets (id_pret, id_inscrit #, id_document #, date_pret, date_retour,
retour)
demandes (id_demande, id_inscrit #, id_document #)
2. Interrogations simples sur une table
SELECT * FROM inscrits ;
SELECT count(*) FROM inscrits ;
SELECT count(*) AS nb FROM inscrits WHERE classe = '6a' ;
autres fonctions AVG (moyenne), MIN, MAX, SUM
" Quelle est la moyenne des valeurs d'un champ note dans une table
carnet_notes ? "
SELECT AVG(note) as moyenne from carnet_notes ;
" Combien de documents disponibles sur leur total ?"
SELECT SUM(disponible) disponible, count(*) nombre FROM documents ;
SELECT
SELECT
SELECT
SELECT
SELECT
;
SELECT
;
SELECT
auteur FROM documents ;
DISTINCT auteur FROM documents ;
titre, auteur FROM documents LIMIT 0,10 ;
titre, auteur FROM documents ORDER BY auteur ;
prenom, nom, classe FROM inscrits ORDER BY nom DESC LIMIT 0,10
prenom, nom, classe FROM inscrits ORDER BY classe, nom, prenom
* FROM documents WHERE BINARY titre='ivanohé' ";
3. Requêtes avec condition simple
Une condition simple est construite avec les opérateurs de comparaison usuels < > = !=
portant sur des valeurs de champs. On dispose de quelques opérateurs spécifiques : LIKE et
NOT LIKE
SELECT nom, prenom, classe FROM inscrits WHERE nom >= 'x' ;
SELECT DISTINCT titre, auteur FROM documents WHERE titre LIKE '%mis%'
;
SELECT titre, auteur FROM documents WHERE titre LIKE '% mis%' ;
SELECT auteur, titre FROM inscrits WHERE classe = '6a' ;
SELECT titre, auteur FROM documents WHERE titre LIKE '%mis%' ;
SELECT titre, auteur FROM documents WHERE titre LIKE '% mis%' ;
SELECT titre, auteur FROM documents WHERE titre LIKE '%mis%' AND
auteur NOT LIKE '%hugo%' ;
4. Requêtes avec condition composée
5 AND, OR, NOT portant sur les conditions simples (attention, AND étant prioritaire sur OR, il
peut être nécessaire de parenthéser)
SELECT nom,prenom,classe FROM inscrits WHERE (nom >='v' OR nom <'c')
AND classe='6a' ;
SELECT titre, auteur FROM inscrits WHERE nom BETWEEN 'h' AND 'm' ;
Les opérateurs d'appartenance à une liste discrète ou à un intervalle "continu" permettent
d'abréger les conditions composées : [NOT] IN (liste_valeurs) et BETWEEN valeur1
AND valeur2.
SELECT nom,prenom,classe FROM inscrits WHERE classe IN
('6A','6c','6f');
5. Approche notion de regroupement
"Quels sont les effectifs par classe des inscrits ?"
La fonction de comptage count() dans ce contexte s'applique à chaque ensemble résultant de la
partition suivant la valeur du champ classe
SELECT classe, count(*) as nombre
FROM inscrits
GROUP BY classe ;
"Quelles sont les classes dont l'effectif est limité à 24 élèves ?"
Une condition portant sur les enregistrements regroupés s'exprime avec la clause HAVING (et
non WHERE)
SELECT classe, count(*) as nombre
FROM inscrits
GROUP BY classe
HAVING nombre <= 24;
6. Jointures simples
SELECT count(*) FROM inscrits, documents ;
SELECT count(*) as nb, 225*25 FROM inscrits, documents WHERE
classe='6a';
"Quels sont les documents actuellement empruntés ? ("actuellement" : tester le champ
prets.retour=0)"
SELECT d.id_document, titre, auteur, editeur
FROM prets p, documents d
WHERE d.id_document = p.id_document
AND retour=0 ;
" Quels sont les documents empruntés par les élèves de la 6A ? "
SELECT prenom, nom, classe, auteur, titre
FROM inscrits, prets, documents
WHERE classe = '6a'
AND inscrits.id_inscrit = prets.id_inscrit
AND documents.id_document = prets.id_document ;
7. Sous-requêtes
On peut alors copier des enregistrements de documents satisfaisant à une requete (le titre
commence par "les"), dans la nouvelle table doc
INSERT INTO doc SELECT * FROM documents WHERE titre LIKE
'les%' ;
6 8. Requetes avec expressions régulières
Le langage des expressions régulières est compris par Mysql. On place le motif dans une
clause WHERE après le mot-clé REGEX.
Exemples à tester et à commenter :
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
*
*
*
*
*
*
FROM
FROM
FROM
FROM
FROM
FROM
documents
documents
documents
documents
documents
documents
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
titre
titre
titre
titre
titre
titre
REGEXP
REGEXP
REGEXP
REGEXP
REGEXP
REGEXP
'^l.*[rt]$' ;
'^a.{5}r' ;
' et ' ;
'vie| bon' ;
'[[:digit:]]+' ;
'[a-z]{5}' ;
7 TPSQL
1. A partir du script sql SchemaFilms.sql, vous devez créer le schéma de base de données dans une base de données Films sous Mysql. Ensuite vous insérez les données dans cette base à partir du script BaseFilms.sql. Vous pouvez remarquer que l’ordre de création des tables respecte le référencement entre PRIMARY KEY et FOREIGN KEY. Les tables qui sont référencées par cette dernière clause doivent être créées avant celles qui les référencent. Par exemple la table Artiste est créée avant la table Film à cause de la clé étrangère idMES. C’est en revanche l’ordre inverse qui est suivi pour les commandes DROP : on ne peut pas détruire une table qui est référencée par une commande FOREIGN KEY. Notez qu’en principe on ne place pas les commandes DROP dans un script de création puisqu’on ne souhaite pas prendre le risque de détruire des données existantes. Comme il s’agit ici d’une base de test, la situation est différente. La base contient un échantillon de films avec leur metteur en scène, leurs acteurs et les notations de quelques internautes. À vous de jouer : il faut concevoir, saisir et exécuter les ordres SQL correspondant aux requêtes qui suivent. 1.1 Sélections simples
1. Tous les titres de films. 2. Nom et prénom des internautes auvergnats. 3. Titre et année de tous les drames, triés par année ascendante. Donnez ensuite le tri par année descendante. 4. Nom et année de naissance des artistes nés avant 1950. 5. Titre et année de tous les films parus entre 1960 et 1980 6. Tous les genres de films (éliminez les doublons). 7. Titre, genre et résumé des films qui sont soit des drames, soit des westerns (utilisez la construction IN), et dont le résumé contient la chaîne de caractères « vie ». 8. Les artistes dont le nom commence par ’H’ (commande LIKE). 1.2 Jointures
1. Qui joué le rôle de Morpheus (nom et prénom) ? 2. Qui est le réalisateur de Alien ? 3. Prénom et nom des internautes qui ont donné une note de 4 à un film (donner aussi le titre du film). 4. Quels acteurs ont joué quel rôle dans le film Vertigo ? 5. Films dont le réalisateur est Tim Burton, et un des acteurs est Johnny Depp. 6. Titre des films dans lesquels a joué Bruce Willis. Donner aussi le nom du rôle. 7. Quel metteur en scène a tourné dans ses propres films ? Donner le nom, le rôle et le titre des films. 8. Quel metteur en scène a tourné en tant qu’acteur (mais pas dans son propre film) ? Donner le nom,le rôle et le titre des films où le metteur en scène a joué. 9. Dans quels films le metteur en scène a‐t‐il le même prénom que l’un des interprètes ? (titre, nom 8 du metteur en scène, nom de l’interprète). Le metteur en scène et l’interprète ne doivent pas être la même personne. 1.3 Requêtes imbriquées
Les requêtes suivantes peuvent s’exprimer avec une imbrication des clauses SELECT. 1. Donnez les nom et prénom des artistes qui on mis en scène un film. 2. Donnez le titre et année des films qui ont le même genre que Matrix. 3. Donnez le nom des internautes qui ont noté le film Alien. Donnez également la note. 1.4 Négation
1. Les films sans rôle. 2. Nom et prénom des acteurs qui n’ont jamais mis en scène de film. 3. Les internautes qui n’ont pas noté de film paru en 1999. 1.5 Fonctions de groupe
1. Quelle est le nombre de films notés par l’internaute [email protected], quelle est la moyenne des notes données, la note minimale et la note maximale ? 2. Combien de fois Bruce Willis a‐t‐il joué le role de McClane ? 3. Année du film le plus ancien et du film le plus récent. 4. id, Nom et prénom des réalisateurs, et nombre de films qu’ils ont tournés. 9