cterrier.com 1/14 04/03/2008

Informatique

Initiation aux requtes SQL

Auteur : C. Terrier ; mailto:webmaster@cterrier.com ; http://www.cterrier.com Utilisation : Reproduction libre pour des formateurs dans un cadre pdagogique et non commercial

Le langage SQL (Structured Query Language) est un langage informatique normalis dinterrogation des bases de donnes relationnelles (SGBDR). Il est devenu un standard international en 1986. Il est utilis par toutes les bases de donnes (Oracle, dBase, Access...). Cest un langage est proche dun langage naturel.

Sommaire

A - Socit Exemple

B Syntaxe des commandes

C Etude des commandes

1. Requte slection : SELECT FROM

1.1 Requte simple

1.2 Requte multi-tables : Jointure 1.3 Trier les donnes : ORDER BY 1.4. Affichage avec restriction : WHERE

1.4.1 Slection sur un critre

Critre alphabtique ou numrique simple

Critre alphabtique labor : LIKE

Oprateur : NOT 1.4.2 Slection multi-critres : AND, OR, BETWEEN, NOT

Oprateur : AND

Oprateur : OR

Oprateur : BETWEEN

1.5 Les fonctions arithmtiques : MAX, MIN, AVG, SUM, COUNT 1.6 Regrouper les donnes : GROUP BY

1.6.1 Regroupement simple 1.6.2. Regroupement avec calcul 1.6.3 Regroupement limitation : HAVING

2. Requte ajout : INSERT INTOVALUE

3. Requte suppression : DELETE FROM

4. Requte mise jour : UPDATE SET

cterrier.com 2/14 04/03/2008

A Syntaxe des commandes SQL

Rappel :

Dans une base de donnes relationnelle les informations sont stockes dans des champs qui sont regroupes dans des tables.

Les tables regroupent les informations (champs) qui ont un lien commun (Information sur les salaris, sur les socits sur les visites,

Une requte SQL affiche les contenus de champs qui appartiennent des tables pour les afficher selon certains critres

MEMO DES PRINCIPALES COMMANDES

Select SELECT "nom de champ" FROM "nom de table" Affiche les donnes contenues dans les champs partir des tables listes.

Order By SELECT "nom de colonne" FROM "nom de table" [WHERE "condition"] ORDER BY "nom de colonne" [ASC, DESC] Paramtre des critres de tri.

Where SELECT "nom de colonne" FROM "nom de table" WHERE "condition" Paramtre une condition de slection.

Like SELECT "nom de colonne" FROM "nom de table" WHERE "nom de colonne" LIKE {modle} Avec WHERE. Paramtre dans une condition de slection, un critre sur une partie dun champ.

Count, Avg, Max, Min, Sum SELECT COUNT ("nom de colonne") FROM "nom de table" Oprateur arithmtique : compte, moyenne, maximum, minimum, somme.

And Or SELECT "nom de colonne" FROM "nom de table" WHERE "condition simples" {[AND|OR] "condition simples"}+ Avec WHERE. Paramtre plusieurs conditions.

Not SELECT "nom de colonne" FROM "nom de table" WHERE "condition simples" {[NOT] "condition simples"} Avec WHERE. Paramtre exclue une condition.

Between SELECT "nom de colonne" FROM "nom de table" WHERE "nom de colonne" BETWEEN 'valeur1' AND 'valeur2' Paramtre un intervalle.

Group By SELECT "nom de colonne 1", SUM("nom de colonne 2")

FROM "nom de table" GROUP BY "nom de colonne 1" Paramtre un regroupement.

Having SELECT "nom de colonne 1", SUM("nom de colonne 2") FROM "nom de table" GROUP BY "nom de colonne 1" HAVING (condition fonction) Paramtre une condition.

Insert Into INSERT INTO "nom de table" ("colonne 1", "colonne 2", ...) valeurS ("valeur 1", "valeur 2", ...) Ajoute denregistrements.

Delete From DELETE FROM "nom de table" WHERE {condition} Suppression denregistrements.

Update UPDATE "nom de table" SET "colonne 1" = [nouvelle valeur] WHERE {condition} Modifier un enregistrement.

Distinct SELECT DISTINCT "nom de colonne" FROM "nom de table" Slectionne dans un champ les donnes diffrentes.

In SELECT "nom de colonne" FROM "nom de table" WHERE "nom de colonne" IN ('valeur1', 'valeur2', ...) Slectionne des donnes prcises.

Creat Table CREATE TABLE "nom de table" ("colonne 1" "type de donnes colonne 1", "colonne 2" "type de donnes colonne 2", ...) Cre une table.

Drop Table DROP TABLE "nom de table" Supprimer une table.

Truncate Table TRUNCATE TABLE "nom de table" Supprime toute les donnes dune table.

cterrier.com 3/14 04/03/2008

B - Socit exemple Pour illustrer ce cours nous allons travailler partir de la base de donnes suivantes conue sous Access :

Description de la base de donnes :

Les salaris de cette socit sont des commerciaux.

Ils sont chargs dassurer le suivi commercial des entreprises. Dans ce cadre chaque salari est responsable de plusieurs socits. Inversement, une socit ne peut tre suivie que par un seuil commercial.

Chaque socit est visite plusieurs fois par an. La table Visites enregistre les comptes rendus de visites priodiques. Une entreprise fait lobjet de plusieurs visites par an. Chaque compte rendu concerne une seul entreprise.

Exemple : Liste des entreprises par vendeur

Exemple : Liste des visites par entreprises

cterrier.com 4/14 04/03/2008

C ETUDE DES COMMANDES

1. Requte slection : SELECT FROM L'instruction SELECT FROM est utilise pour afficher (Projeter) les enregistrements dune base de donnes

La commande SELECT slectionne les champs,

La commande FROM slectionne les tables dans lesquelles slectionner les champs.

1.1. Requte simple

Exemple : Afficher les champs : matricule, nom et tl de la table Salaris SELECT Salaris.matricule_sal, Salaris.nom_sal, Salaris.tl_sal FROM Salaris

les noms de champs et de tables doivent strictement respecter la syntaxe utilise dans la dfinition des tables.

Le symbole * affiche tous les champs de la table.

Exemple : Afficher tous les champs de la table Salaris SELECT * FROM Salaris

Entrainement

Afficher les champs : raison sociale, contact et fonction de la table socits

Afficher tous les champs de la table visites

1.2. Requte multi tables (jointure)

La jointure permet dafficher des champs provenant de plusieurs tables. Les noms de champs sont placs dans la commande SELECT et sont prcd du nom de la table. De plus les noms des tables sont spars par une virgule dans la commande FROM Attention pour slectionner uniquement les donnes en relation vous devez les filtrer laide de la commande WHERE (Voir galement 1.4) Exemple : Afficher les champs : matricule et nom de la table salaris ainsi que les champs : raison

sociale, contact, fonction et tlphone de la table socit. SELECT Salaris.matricule_sal, Salaris.nom_sal, Socits.Raison_sociale_ese, Socits.Contact_ese, Socits.fonction_ese, Socits.tl_ese FROM, Salaris, Socits WHERE Socits.Matricule_sal = Salaris.matricule_sal

cterrier.com 5/14 04/03/2008

Entrainement (Base de donnes ci-dessus)

Afficher les champs : matricule et nom de la table salaris aisi que les champs N ese, raison sociale et contact de la table Socits

Afficher les champs : N ese, Raison sociale, contact et fonction de la table socit et les champs N visite, date et rsultat de la table visites.

1.3. Trier les donnes : ORDER BY

La commande ORDER BY paramtre le nom du champ sur lequel trier les donnes ainsi que le critre de tri : ASC (croissant) ou DESC (dcroissant) Exemple : Afficher les champs matricule, nom et tl de la table salaris en les triant sur le nom tri

par ordre croissant SELECT Salaris.Matricule_sal, salaries.Nom_sal, Salaris.Tl_perso_sal FROM Salaris ORDER BY Nom_sal ASC

Il est possible dindiquer plusieurs cls de tri en les saisissant les un la suite des autres Exemple : Afficher les champs : matricule, nom et tl de la table salaris tris sur le matricule par

ordre croissant et sur le nom par ordre decroissant SELECT Salaris.Matricule_sal, Salaris.Nom_sal, Salaris.Tl_perso_sal FROM Salaris ORDER BY Matricule_sal, ASC, Nom_sal DESC

Entrainement

Afficher les champs : Raison sociale, contact et fonction de la table socits tri sur le nom du contact

Afficher tous les champs de la table visites tri sur la date et sur le numro de lentreprise

cterrier.com 6/14 04/03/2008

1.4. Affichage avec restriction : WHERE

La commande WHERE permet de filtrer les enregistrements. La syntaxe de la commande est la suivante : WHERE champs oprateur critre de filtre Une requte peut tre simple (1.4.1) ou plus labor avec des conditions imbriques (1.4.2) : Conditions : Afficher les hommes => Sexe = Homme Simples Afficher les personnes qui habitent Lyon => Ville = Lyon

Afficher les clients dont le chiffre daffaires est > 5 000 => CA > 5000 Afficher les clients qui ne sont pas de Lyon => Ville NOT Lyon

Conditions : Afficher les hommes de 20 ans => Sexe = Homme AND age >20 Imbriques Afficher les clients de Lyon et Grenoble => Ville = Lyon OR Ville = Grenoble Les oprateurs de slection sont les suivants :

1.4.1. Slection sur un critre

Critre alphabtique ou numrique simple

Exemple : Afficher les champs : matricule, nom et tl de la table salaris pour les hommes SELECT Salaries.Matricule_sal, Salaries.Nom_sal, Salaries.Tl_perso_sal FROM Salaris WHERE Civilit_sal = Homme

Entrainement

Afficher les champs : numro dentreprise, rue, code postal et ville de la table socits pour les entreprises de Paris

Afficher les champs : matricule, rue, code postal et ville, date de naissance de la table salaris pour les salaris de Lyon tris sur la date de naissance

Oprateurs disponibles Oprateurs Effets Exemples

= gal WHERE salaire = 2000 < infrieur WHERE salaire < 2000 > suprieur WHERE salaire > 2000 = 2000 diffrent de WHERE salaire 2000 PAS NULL non vide WHERE salaire = NULL

NULL vide WHERE salaire = PAS NULL

LIKE contient WHERE Prnom LIKE Luc

cterrier.com 7/14 04/03/2008

Critre alphabtique labor : LIKE La commande LIKE signifie contient. Associ la commande WHERE, elle permet de slectionner une donne contenue dans un champ. Exemple : je recherche le mot Rpublique dans le champ Rue.

Exemple Afficher les salaris dont le nom de rue contient le mot Rpublique SELECT Salaris.Nom_sal, Salaris.Rue_sal, Salaris.Ville_sal FROM Salaris WHERE Rue_sal LIKE Rpublique

La commande LIKE peut tre associe au symbole % qui remplace une chane de caractre. Dans ce cas elle affiche uniquement les enregistrements qui contiennent le texte situ avant, aprs ou entre les signe % (chaine de caractres)

Exemple 1 : Afficher les villes qui commencent par la lettre : L => WHERE Ville_sal LIKE L% Exemple 2 : Afficher les villes dont le nom se termine par : ian => WHERE Ville_sal LIKE '%ian Exemple 3 : Afficher les villes dont le nom contient les lettres : Du => WHERE Ville_sal LIKE '%Du%'

Entrainement (Base de donnes ci-dessus)

Afficher les champs : RS, rue, code postal et ville de la table socits pour les entreprises dont le nom commence par BO

Afficher les champs : civilit, nom, rue et ville de la table salaris pour les salaris qui habitent sur une place

Loprateur NON (NOT)

Loprateur NOT permet dexclure de laffichage les enregistrements qui satisfont la condition. Exemple : Afficher tous les clients qui ne sont pas des particuliers

Exemple : Afficher les champs : matricule, nom et salaire des salaris qui ne sont pas cadres SELECT Salaris.Matricule_sal, Salaris.Nom_sal, FROM Salaris WHERE NOT Fonction_sal = Cadre

Entrainement

Afficher les champs : numro dentreprise, rue, code postal et ville de la table socits des entreprises dont le code postal nest pas 75000

cterrier.com 8/14 04/03/2008

Afficher les champs : matricule, rue, code postal et ville, date de naissance de la table salaris pour les salaris qui ne sont pas ns Lyon

1.4.2. Slection sur plusieurs critres

Une slection peut imbriquer plusieurs critres entre eux :

Afficher les clients de Lyon ou Grenoble => oprateur OU (OR)

Afficher les clients de Lyon dont le chiffre daffaires est > 3000 => oprateur ET (AND) Afficher les chantiers commencs entre le 01/01/08 et le 15/05008 => oprateur ENTRE (BETWEEN)

Loprateur ET (AND)

Loprateur AND permet dassocier des critres qui sajoutent. (Les deux critres doivent tre remplis). Exemple : Je souhaite afficher les clients qui habitent Lyon et dont le chiffre daffaires est suprieur 10 000 Exemple : Afficher le matricule, le nom et le salaire des salaris masculin dont le salaire est

suprieur 2000 SELECT Salaris.Matricule_sal, Salaris.Nom_sal, Salari.Civilit_sal Salaris.Salaire_sal FROM Salaris WHERE Civilit_sal = Homme AND Salaire_sal > 2000

Entrainement (Base de donnes ci-dessus)

Afficher le numro dentreprise, la rue, le code postal et le ville des entreprises situes Paris suivi par le salari dont le matricule est 123

Afficher le matricule et le nom des salaris de Lyon dont le salaire est suprieur 3 000 .

Loprateur OU (OR)

Loprateur OR permet dassocier des critres qui sliminent. (Un des deux critres doit tre rempli). Exemple : Je souhaite afficher les clients grossistes et les administrations

Attention : La formulation de la condition OU est pigeuse car pour afficher les grossistes et les administrations. Il faut utiliser loprateur OU. Lutilisation de loprateur ET revient afficher les clients qui sont la fois des grossistes et des administrations, ce qui est impossible. Lors dune requte lorsque le rsultat est vide, lerreur provient le plus souvent de cette confusion.

Exemple : Afficher le matricule, le nom et le salaire des salaris de Lyon et Grenoble

SELECT Salaris.Matricule_sal, Salaris.Nom_sal, Salaris.Salaire_sal, Salari.Ville_sal FROM Salaris WHERE Ville_sal = Lyon OR Ville_sal = Grenoble

cterrier.com 9/14 04/03/2008

Entrainement

Afficher les champs : numro dentreprise, rue, code postal et ville de la table socits pour les entreprises de Paris ou de Lyon

Afficher les champs : RS, contact et Tl des socits dont le numro de matricule du reprsentant est 123 ou 125

Loprateur ENTRE (BETWEEN) Loprateur BETWEEN permet de paramtrer un intervalle de valeur. Exemple : Afficher tous les clients dont le chiffre daffaires est compris entre 1 000 et 2 000 .

Cet oprateur peut tre remplac par loprateur AND :

Afficher les salaris dont lage est compris entre 20 et 30 ans Afficher les salaris dont lage est > 20 et < 30

Exemple : Afficher le matricule, le nom et le salaire des salaris masculin dont le salaire est

suprieur 2 000 et infrieur 3 000 . SELECT Salaris.Matricule_sal, Salaris.Nom_sal, Salaris.Salaire_sal FROM Salaris WHERE Salaire_sal BETWEEN 2000 AND 3000

Entrainement

Afficher les champs : numro dentreprise, rue, code postal et ville de la table socits pour les entreprises dont le code postal est compris entre 38000 et 39000

Afficher le matricule, la rue, le code postal, la ville et la date de naissance des salaris dont la date_dembauche de la table salaris est comprise entre le 01/01/2000 et le 31/12/2000

cterrier.com 10/14 04/03/2008

1.5. Les fonctions arithmtiques: MAX, MIN, AVG, SUM, COUNT

Les fonctions arithmtiques permettent dafficher des statistiques sur des champs numriques ou montaires ou de compter le nombre doccurrence dun champ. La fonction est place devant le nom du champ qui est mis entre parenthse. Exemple 1 : Afficher le nombre de salaris

SELECT COUNT(Salaris.Matricule_sal) FROM Salaris

Exemple 2 : Afficher le salaire moyen des cadres SELECT AVG(Salaire_sal), Fonction_sal FROM Salaris WHERE Fonction_sal = Cadre

Les fonctions disponibles sont les suivantes :

Entrainement

Afficher le nombre de salaris de lentreprise

Afficher le salaire maximum et le salaire minimum des salaris

1.6. Regrouper les donnes : GROUP BY

1.6.1. Regroupement simple La commande GROUPE BY regroupe laffichage les donnes sur le champ spcifi. Exemple : Par dfaut les visites sont tries par ordre chronologique. Ds lors les visites concernant une mme entreprise sont spares laffichage. Le regroupement des visites sur le nom de la socit permet dafficher les visites qui concernent une mme entreprise les unes la suite des autres.

Fonctions Effets

MAX Affiche la valeur maximum dun champ

MIN Affiche la valeur minimum dun champ

AVG Affiche la moyenne des donnes dun champ

SUM Affiche la somme des donnes dun champ

COUNT Affiche le nombre de valeur dun champ

cterrier.com 11/14 04/03/2008

La fonction est place devant le nom du champ qui est mis entre parenthse. Exemple : Afficher entreprises regroupes par salaris

SELECT Salaris.matricule_sal, Salaris.nom_sal, Socits.Raison_sociale_ese, Socits.Contact_ese, Socits.Fonction_ese, Socits.Tl_ese FROM Socits, Salaris WHERE socits.Matricule_sal = Salaris.matricule_sal GROUPE BY Socits.Matricule_sal

Entrainement

Afficher le numro de lentreprise, la ville et le numro de tlphone regroups par salaris responsable

Afficher les adresses des salaris regroups par ville

1.6.2. Regroupement avec calcul La commande GROUPE BY est utilise pour raliser des calculs concernant un mme enregistrement. Exemple : Calculer le total des ventes pour chaque client :

Affichage sans regroupement Affichage avec regroupement Date Entreprise Ventes Entreprise Date

15/01/2008 Bernard 1000 Bernard 15/01/2008 1000 17/01/2008 Paule 800 20/02/2008 600 20/02/2008 Bernard 600 28/02/2008 1200 28/02/2008 Bernard 1200 15/03/2008 300 03/03/2008 Paule 400 Paule 17/01/2008 800 15/03/2008 Bernard 300 03/03/2008 400

Table Visites Affichage sans regroupement

Affichage avec regroupement

Date Entreprise Ventes Entreprise Date

15/01/2008 Bernard 1000 Bernard 15/01/2008 1000 17/01/2008 Paule 800 20/02/2008 600 20/02/2008 Bernard 600 28/02/2008 1200 28/02/2008 Bernard 1200 15/03/2008 300 03/03/2008 Paule 400 Total 4100

15/03/2008 Bernard 300 Paule 17/01/2008 800 03/03/2008 400 Total 1200

cterrier.com 12/14 04/03/2008

Exemple : Calculer le chiffre daffaires total ralis avec chaque client dans le tableau de la page prcdente SELECT Entreprise, SUM(Ventes) FROM Visites GROUPE BY Entreprise

Entrainement

Table : Lignes_Commande Champs : N_commande, rfrence, quantit, N_client

Calculer les quantits totales commandes par type de rfrence

Calculer le nombre de commandes passes par client

1.6.3. Regroupement limitation : HAVING La commande HAVING jointe un calcul permet de restreindre laffichage uniquement aux socits qui satisfont un critre Exemple : Calculer le total des ventes pour chaque client et afficher les rsultats uniquement pour les entreprises dont le chiffre daffaires est suprieur 3 000 . Exemple : Calculer le chiffre daffaires total pour chaque client et afficher ceux dont le total est suprieur 3000

SELECT Entreprise, SUM(Ventes) FROM Visites GROUPE BY Entreprise HAVING SUM(Ventes) > 3000

Entrainement

Table : Lignes_Commande Champs : N_commande, rfrence, quantit, N_client

Calculer les quantits totales commandes par type de rfrence et afficher uniquement ceux dont la quantit moyenne est suprieur 50 articles

Calculer le nombre de commandes passes par client et afficher uniquement ceux dont le nombre de commandes est suprieur 12 articles

Table Visites Affichage sans regroupement

Affichage avec regroupement

Date Entreprise Ventes Entreprise Date

15/01/2008 Bernard 1000 Bernard 15/01/2008 1000

17/01/2008 Paule 800 20/02/2008 600

20/02/2008 Bernard 600 28/02/2008 1200

28/02/2008 Bernard 1200 15/03/2008 300

03/03/2008 Paule 400 Total 4100

15/03/2008 Bernard 300

cterrier.com 13/14 04/03/2008

2. Requte ajout : INSERT INTO VALUE La commande INSERT INTO permet dajouter des enregistrements aux champs dune table Exemple : Ajouter la table visites lenregistrement suivant :

Date 15/04/2008 ; Entreprise : Paule ; Vente 600 INSERT INTO "Visites" ("Date", "Entreprise","Ventes") VALUES ("15/04/2008", "Paule", 600,)

Entrainement

Ajouter lenregistrement suivant la table Socits : N ese : 23, Socit : BongrainSA, Contact : GoudardLuc, Fonction : PDG, Adresse : 2 rue du Mont, 33000, BORDEAUX, 03 45 12 32 65, 02

3. Requte suppression : DELETE FROM La commande DELETE FROM permet de supprimer un ou des enregistrements dune table Exemple : Supprimer de la table visites les lenregistrement du 17/01/2008 :

DELETE FROM Visites WHERE Date = 17/01/2008

Entrainement

(Base de donnes ci-dessus)

Supprimer de la table socits les enregistrements qui concernent la socit BREAL SA

Supprimer de la table visites les enregistrements qui concernent la socit dont le matricule est 025

Table Visites Affichage sans regroupement

Date Entreprise Ventes

15/01/2008 Bernard 1000

17/01/2008 Paule 800

20/02/2008 Bernard 600

Table Visites

Affichage sans regroupement

Date Entreprise Ventes

15/01/2008 Bernard 1000

17/01/2008 Paule 800

20/02/2008 Bernard 600

cterrier.com 14/14 04/03/2008

4. Requte mise jour : UPDATE SET La commande UPDATE SET permet de modifier des enregistrements dune table Exemple : la date du 17/01/2008 est fausse il fallait saisir 19/01/2008:

UPDATE Visites SET Date = 19/01/2008 WHERE Date = "17/01/2008"

Entrainement

Le salari Dupont (matricule 033) est mut, dornavant les socits quil grait seront suivies par le salari Grmont Paul dont le matricule est 028. Mettre jour la table socits.

Le salari Dupont (matricule 033) a chang de N de tlphone. Ancien numro : 04 78 23 56 89 ; Nouveau numro : 04 78 43 33 28

Table Visites

Affichage sans regroupement

Date Entreprise Ventes

15/01/2008 Bernard 1000

17/01/2008 Paule 800

20/02/2008 Bernard 600