1

Langage SQL

2

1. Classification des commandes SQL LDD : commandes de définition de données (créer, modifier, supprimer) LMD : commandes de manipulation de données (extraire et mise à jour) Commandes de contrôle de données.

2. Langage de définition de données

2.1 Définition de table a) Création de table Syntaxe : CREATE TABLE nom_table (nom_colonne type_colonne [DEFAULT expression] [contrainte de colonne],…n, [Contrainte de table]) Avec Contrainte de colonne := [CONSTRAINT nom_contrainte]{ [NOT] NULL | {UNIQUE | PRIMARY KEY} | REFERENCES table [(colonne)] [ON DELETE CASCADE ] | CHECK (condition) } Contrainte de table := [CONSTRAINT nom_contrainte] { {UNIQUE | PRIMARY KEY}(colonne[,colonne]…) | FOREIGN KEY (colonne[,colonne]…) | REFERENCES table [(colonne[, colonne]... )] [ON DELETE CASCADE ] | CHECK (condition) } Les principaux types de données sont : CHAR, VARCHAR, NUMBER et DATE. Exemples : CREATE TABLE produit (codeproduit NUMBER CONSTRAINT pk_prod PRIMARY KEY, designation CHAR(20) CONSTRAINT nn_des NOT NULL, prixunit NUMBER(7,2) ); CREATE TABLE fournisseur ( numfour NUMBER CONSTRAINT pk_numf PRIMARY KEY, nom CHAR(20) CONSTRAINT nn_nom NOT NULL, adresse CHAR(80), codpostal NUMBER(5) CHECK (codpostal BETWEEN 10000 AND 90000), ville CHAR(20));

3

CREATE TABLE commande ( numcde NUMBER CONSTRAINT pk_numc PRIMARY KEY, datecde DATE, numfour NUMBER CONSTRAINT fk_numf REFERENCES fournisseur (numfour) ); CREATE TABLE ligne_cde (codeproduit NUMBER CONSTRAINT fk_cdprod REFERENCES produit(codeproduit), numcde NUMBER CONSTRAINT fk_numcde REFERENCES commande(numcde), numligne Number, qtecmde NUMBER CONSTRAINT chek_qte CHEK(qtecmde > 0) PRIMARY KEY ( codeproduit, numcde) ); CREATE TABLE adr_fourn(nom,adr1,adrs2) AS SELECT nom, adresse, codpostal | | ',' | | ville; b) Modification des structures des tables : Concerne l'ajout de nouvelles colonnes, l'ajout de contraintes d'intégrité, la modification de colonne, l'activation, désactivation et suppression de contraintes d'intégrité. Syntaxe : ALTER TABLE nom_table ADD { {colonne | contrainte de table} | ({colonne | contrainte de table} [, {colonne | contrainte de table}…)] } | MODIFY {colonne | (colonne [, colonne]…)} | DROP {PRIMARY KEY | UNIQUE (colonne [, colonne]…) | CONSTRAINT contrainte} [CASCADE] Exemples : ALTER TABLE fournisseur ADD remarque CHAR (80), MODIFY ville CHAR(30); ALTER TABLE produit DROP CONSTRAINT pk_prod CASCADE; Ou bien ALTER TABLE produit DOP PRIMARY KEY CASCADE; c) Suppression de tables Syntaxe : DROP TABLE nom_table [CASCADE CONSTRAINTS] L'instruction CASCCADE CONSTRANT supprime toutes les contraintes d'intégrité référentielles liées aux clés unique ou primaires de la table à supprimer. Exemple : DROP TABLE adr_fourn;

4

d) Suppression de lignes d'une table : Syntaxe : TRUNCATE TABLE nom_table [{DROP | REUSE} STORAGE] Exemple : TRUNCATE TABLE produit REUSE STORAGE 2.2 Définition d'index a) Création d'index Syntaxe : CREATE INDEX nom_index ON table (colonne [ASC | DESC] [, colonne [ASC | DESC]]…) Exemple : CREATE INDEX ind_fourn ON fournisseur (nom DESC, ville); b) Suppression d'index Syntaxe : DROP INDEX nom_index Exemple : DROP INDEX ind_fourn; 2.3 Définition de vues a) Création d'une vue Syntaxe : CREATE [OR REPLECE] VIEW nom_vue AS requête [WITH CHECK OPTION [CONSTRAINT contrainte]] L'option WITH CHECK OPTION force la mise à jour des données à travers la vue à respecter la condition de la clause WHERE dans l'instruction SELECT qui définit la vue. Exemple : CREATE VIEW fourn_view (numfourn, nomfourn, villefourn) AS SELECT numfour, nom, ville FROM fournisseur WHERE ville='casa' WITH CHECK OPTION b) Suppression d'une vue Syntaxe : DROP VIEW nom_vue Exemple : DROP VIEW fourn_view;

5

3. Langage de manipulation de données : Permet la consultation de données et la mise à jour de données (insertion, modification et suppression). 3.1 Consultation de données Syntaxe : SELECT [ALL | DISTINCT] liste_de_sélection FROM liste_de_tables [WHERE condition] [GROUP BY liste_expression [HAVING condition]] [{UNION | UNION ALL | INTERSECT | MINUS}commande SELECT] [ORDER BY] {expression | position}[ASC | DESC] [, {expression | position} [ASC | DESC]…] a) Consultation simple Syntaxe : SELECT liste_de_selection FROM liste_de_tables Exemples : • SELECT nom, ville FROM fournisseur; • SELECT * FROM fournisseur; b) Consultation avec qualification La qualification se fait à l'aide de la clause WHERE Syntaxe : WHERE condition Trois types de conditions : comparaison, jointure et sous_requête • Conditions de comparaison : expression opérateur_relationnel expression expression [NOT] BETWEEN expression AND expression expression [NOT] IN (liste_de_valeurs) colonne [NOT] LIKE "chaîne" (% et _) colonne IS [NOT] NULL Exemples : • Les fornisseurs dont le nom se termine par mi : SELECT * FROM fournisseur WHERE nom LIKE '%mi'; Le symbole % : remplace une chaîne de caractères de n'importe quel nombre (même vide). • Les fournisseurs dont le nom comprend la chaîne de caractères 'o_i' à partir de la deuxième position. SELECT * FROM fournisseur WHERE nom LIKE '_o_i'; Le symbole _ : remplace un seul caractère. • Conditions de jointure :

─ L'équi-jointure : c'est une jointure dont a condition est une comparaison d'égalité de deux colonnes appartenant à deux tables différents.

6

─ θ-jointure : c'est une jointure dont la condition est une comparaison autre que l'éalité de deux colonnes appartenant à deux tables différents. ─ Jointure multiple : c'est une jointure qui met en relation plusieurs colonnes de plusieurs tables. Exemple : • Equi-jointure : les fournisseurs de casa qui ont passé une commande entre les dates du ‘01/01/2000’ et la date courante : SELECT DISTINCT fournisseur.numfour, nom, adresse, datecde FROM fournisseur, commande WHERE fournisseur.numfour=commande.numfour AND ville=’casa’ AND datecde BETWEEN ‘01/01/1993’ AND SYSDATE; • Jointure multiple : les produits qui n’ont pas été commandé entre la date 01/01/1993 et la date courante : SELECT produit.codeproduit, produit.designation FROM produit, commande, ligne_cde WHERE commande.numcde=ligne_cde.numcde AND produit.codeproduit<>ligne_cde.codeproduit AND commande.datecde BETWEEN ’01/01/1993’ AND SYSDATE; ─ Autojointure : c'est une jointure d'une table avec elle-même. Exemple : Les couples de fournisseurs qui ont la même ville : SELECT X.numfour, X.ville, Y.numfour, Y.ville FROM fournisseur X, fournisseur Y WHERE X.ville=Y.ville ; ─ Jointure externe : c'est une jointure qui favorise une table par rapport à une autre. Ainsi les lignes de la table dominante seront affichées même si la condition n'est pas réalisée. Exemple : Les fournisseurs de casa qui ont passé ou non une ou plusieurs commandes entre les dates de ‘01/01/1993’ et la date courante : SELECT X.numfour, nom, adresse, numcde FROM fournisseur X, commande Y WHERE X.numfour=Y.numfour(+) AND ville='casa' AND Y.datecde BETWEEN ’01/01/1993’ AND SYSDATE;

• Conditions de sous-requête : Syntaxe :

-WHERE expression opérateur_de_comparaison {ALL | ANY | SOME} (reqête select) - WHERE colonne [NOT] IN (requête select) -WHERE [NOT] EXISTS (requête select) ALL : la condition est vraie si la comparaison est vraie pour chacune des valeurs retournées ANY=SOME : la condition est vraie si la comparaison est vraie pour au moins une des valeurs retournées IN : la condition est vraie si la valeur de colonne est es égale à l'une des valeurs retournées par la sous-requête EXISTS : retourne vraie si l'évaluation de la sous-requête donne lieu à une ou plusieurs lignes et faux sinon.

7

c) Groupement de données et condition de groupement : • GROUP BY : permet de grouper des lignes de données aillant des valeurs comunnes et faire des traitements spécifiques sur ces groupes. Syntaxe : GROUP BY liste_de_groupes Exemples : - le nombre de ligne par commande : SELECT numcde, COUNT(*) FROM ligne_cde GROUP BY numcde; - le montant de chaque de chaque commande : SELECT numcde, SUM(Y.prixunit * qtecmde) FROM ligne_cde X, produit Y WHERE X.codeproduit=Y.codeproduit GROUP BY numcde ; • HAVING : liée à GROUP BY et permet de une restriction portant sur un groupe de lignes. Syntaxe : HAVING condition Exemple : La somme de toues les comandes dont le nombre de lignes est supérieur à 4 : SELECT numcde, SUM(Y.prixunit * qtecmde) FROM ligne_cde X, produit Y WHERE X.codeproduit=Y.codeproduit GROUP BY numcde HAVING COUNT(numligne) > 4; d) Tri du résultat (order by colonne) Syntaxe : ORDER BY colonne | position [ASC | DESC] Exemple : Les fournisseurs par ordre de ville et nom : SELECT * FROM fournisseur ORDER BY ville, nom; Ou bien SELECT * FROM fournisseur ORDER BY 5, 2; 3.2 Opérateurs ensemblistes a) Union Syntaxe : Requête_select UNION [ALL] requête_select [UNION [ALL] requête_select...] b) Intersection Syntaxe : requête_select INTERSECT requête_select c) Différence Syntaxe : requête_select MINUS requête_seect

8

3.3 La mise à jour de données a) Insertion de données externes • Insertion de données externes : Syntaxe : INSERT INTO {table | vue} [(liste_de_colonnes)] VALUES (liste_de_valeurs) Exemple : INSERT INTO fournisseur( 1, ‘fahmi’, ’20 rue alkods’, 20000, ’Casablanca’) ; • Insertion de données internes : Syntaxe : INSERT INTO nom_table [(liste_de_selection)] requête_select Exemple : INSERT INTO fourn_view SELECT numfourn, nom, ville FROM fournisseur WHERE ville=’casa’ ; b) Modification de données Syntaxe : • UPDATE {table | vue} SET colonne = expression,… • UPDATE {table | vue} SET (liste_colonne)=(requête_select) [WHERE condition] • UPDATE {table | vue} SET (liste_colonne) = (requête_select), colonne=expression,… [WHERE condition] Exemple : UPDATE fournisseur SET ville=’Rabat’ WHERE numfour=1 ; c) Suppression de données : Syntaxe : DELETE FROM {table | vue} [WHERE condition] Exemple : DELETE FROM fournisseur WHERE ville=’Rabat’ ;