Enis BELHASSEN Rappel - Langage SQL
Rappel - langage SQL
1. CREATION DES DONNEES
Création des tables
CREATE TABLE nom_table
(Attribut1 TYPE, Attribut2 TYPE, …,
contrainte_integrité1, contrainte_integrité2, …);
Principaux type de données :
NUMBER(n) : Entier à n chiffres
NUMBER(n, m) : Réel à n chiffres au total(virgule comprise), m après la virgule
VARCHAR(n) : Chaîne de n caractères (entre ‘’)
DATE : Date
BLOB : Binary Large Object
Contraintes d'intégrité
Clé primaire : CONSTRAINT nom_contrainte PRIMARY KEY (attribut_clé [, attribut_clé2, …])
Clé étrangère : CONSTRAINT nom_contrainte FOREIGN KEY (clé_ét) REFERENCES table (attribut)
Contrainte de domaine : CONSTRAINT nom_contrainte CHECK (condition)
Exemple :
CREATE TABLE Fournisseur
(NumFour NUMBER(3), RaisonSociale VARCHAR2(20), Adresse VARCHAR2(50),
CONSTRAINT fournisseur_cle_pri PRIMARY KEY (NumFour))
CREATE TABLE Produit
(NumProd NUMBER(3), Desi VARCHAR2(30), PrixUni NUMBER(8,2), NumFour NUMBER(3),
CONSTRAINT produit_cle_pri PRIMARY KEY (NumProd),
CONSTRAINT produit_cle_etr FOREIGN KEY (NumFour) REFERENCES Fournisseur(NumFour),
CONSTRAINT prix_ok CHECK (PrixUni> 0))
Modifications structurelles
Ajout d’attributs
ALTER TABLE nom_table ADD (attribut TYPE, …);
ex. ALTER TABLE Produit ADD (qte NUMBER(3));
Modifications d’attributs
ALTER TABLE nom_table MODIFY (attribut TYPE, …);
ex. ALTER TABLE Produit MODIFY (qte NUMBER(4));
Suppression d'attributs
ALTER TABLE nom_table DROP COLUMN attribut, ...;
ex. ALTER TABLE Produit DROP COLUMN qte;
Ajout de contrainte
ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte définition_contrainte;
[Link] TABLE Produit ADD CONSTRAINT qte_ok CHECK (qte>0);
Suppression de contrainte
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte;
[Link] TABLE Produit DROP CONSTRAINT qte_ok;
Copie et destruction de tables
Destruction : DROP TABLE nom_table;
ex. DROP TABLE Produit;
Copie : CREATE TABLE nom_table_copie AS requête;
ex. CREATE TABLE Fournisseur_Copie AS SELECT * FROM Fournisseur;
Les index (accélération des accès)
Création des index :
CREATE [UNIQUE] INDEX nom_index ON nom_table (attribut [ASC|DESC], …);
NB : UNIQUE : pas de double / ASC/DESC ordre croissant ou décroissant
ex. CREATE INDEX index_num_fr ON Produit (NumFour);
Destruction d’index :
DROP INDEX nom_index;
ex. DROP INDEX index_num_fr;
Page 1 sur 4
Enis BELHASSEN Rappel - Langage SQL
2. MISE A JOUR DES DONNEES
Ajout d’un tuple
Format 1 : INSERT INTO nom_table [(Attribut1, Attribut2,…)] VALUES (val_att1, val_att2, …);
ex. INSERT INTO Produit VALUES (400, ‘Nouveau produit’, 78.90, 30);
ex. INSERT INTO Produit (NumProd, PrixUni) VALUES (500, 88.90);
Format 2 : INSERT INTO nom_table [(Attribut1, Attribut2,…)] requête;
ex. INSERT INTO Fournisseur_Copie (NumFour, Adresse)
SELECT NumFour, Adresse FROM Fournisseur WHERE Adresse = ‘Tunis’;
Mise à jour d’un tuple
UPDATE nom_table SET attribut = valeur [WHERE condition];
ex. UPDATE Produit SET PrixUni = PrixUni * 1.1 WHERE NumProd = 400;
Suppression de tuples
DELETE FROM nom_table [WHERE condition];
[Link] FROM Produit;
DELETE FROM Fournisseur WHERE Adresse = ‘Tunis’;
3. INTERROGATION DES DONNEES
SELECT [ALL|DISTINCT] attribut1, attribut2,…
FROM table1, table2m …
[WHERE condition]
[GROUP BY attribut1, attribut2,… [HAVING condition]]
[ORDER BY attribut(s) [ASC|DESC]];
Requêtes simples
Tous les tuples de la table client
ex. SELECT * FROM Client;
Tri de la table client par ordre alphabétique inverse de nom
SELECT * FROM Client ORDER BY Nom DESC;
Calcul de prix TTC
SELECT PrixUni + PrixUni * 0.206 FROM Produit;
Projection : uniquement noms et prénoms des clients
SELECT Nom, Prenom FROM Client;
Restriction :
o Clients qui habitent à Tunis
SELECT * FROM ClientWHERE Ville = ‘Tunis;
o Commandes en quantité au moins égale à 3
SELECT * FROM Commande WHERE Quantite >= 3;
o Produits dont le prix est compris entre 50 et 100 F
SELECT * FROM Produit WHERE PrixUni BETWEEN 50 AND 100;
o Commandes en quantité indéterminée
SELECT * FROM Commande WHERE Quantite IS NULL;
o Clients habitant une ville dont le nom se termine par ine
SELECT * FROM Client WHERE Ville LIKE ‘%ine’;
NB : % : remplace une sous chaîne de caractères (0 à n caractères) _ : remplace un et un seul caractère
Prédicat ensembliste IN
o Prénoms des clients dont le nom est Dupont, Durand ou Martin
SELECT Prenom FROM Client WHERE Nom IN (‘Dupont’, ‘Durand’,’Martin’);
NB : Possibilité d’utiliser la négation pour tous ces prédicats : NOT BETWEEN, NOT NULL, NOT LIKE, NOT IN
Fonctions d'agrégat
Elles opèrent sur un ensemble de valeurs.
AVG(), VARIANCE(), STDDEV() : moyenne, variance et écart-type des valeurs
SUM() : somme des valeurs
MIN(), MAX() : valeur minimum, valeur maximum
COUNT() : nombre de valeurs
o Moyenne des prix des produits
SELECT AVG(PrixUni) FROM Produit;
o Nombre total de commandes
SELECT COUNT(*) FROM Commande;
SELECT COUNT(NumCli) FROM Commande;
o Nombre de clients ayant passé commande
SELECT COUNT( DISTINCT NumCli) FROM Commande;
Page 2 sur 4
Enis BELHASSEN Rappel - Langage SQL
Jointures
Liste des commandes avec le nom des clients
SELECT Nom, Date, Quantite FROM Client, Commande WHERE [Link] =[Link];
Liste des commandes avec le nom et le numéro des clients (ordonnées selon le nom)
SELECT [Link], Nom, Date, Quantite FROM Client C1, Commande C2
WHERE [Link] = [Link] ORDER BY Nom;
NB : Utilisation d’alias (C1 et C2) pour alléger l’écriture
Jointure exprimée avec le prédicat IN
o Nom des clients qui ont commandé le 23/09/1999
SELECT Nom FROM Client
WHERE NumCli IN (SELECT NumCli FROM Commande WHERE to_char(Date,'dd/mm/yyyy') = ‘23/09/1999’);
Prédicats EXISTS / NOT EXISTS
o Clients qui ont passé au moins une commande
SELECT * FROM Client C1
WHERE EXISTS (SELECT * FROM Commande C2 WHERE [Link] = [Link]);
o Clients qui n’ont passé aucune commande
SELECT * FROM Client C1
WHERE NOT EXISTS (SELECT * FROM Commande C2 WHERE [Link] = [Link]);
Prédicats ALL /ANY
o Numéros des clients qui ont commandé au moins un produit en quantité supérieure à chacune des quantités
commandées par le client n°1.
SELECT DISTINCT NumCli FROM Commande
WHERE Quantite > ALL (SELECT Quantite FROM Commande WHERE NumCli = 1);
o Numéros des clients qui ont commandé au moins un produit en quantité supérieure à au moins une des quantités
commandées par le client n°1.
SELECT DISTINCT NumCli FROM Commande
WHERE Quantite > ANY (SELECT Quantite FROM Commande WHERE NumCli = 1);
Groupement
o Quantité totale commandée par chaque client
SELECT NumCli, SUM(Quantite) FROM Commande GROUP BY NumCli;
o Nombre de produits différents commandés...
SELECT NumCli, COUNT(DISTINCT NumProd) FROM Commande GROUP BY NumCli;
o Quantité moyenne commandée pour les produits faisant l’objet de plus de 3 commandes
SELECT NumProd, AVG(Quantite) FROM Commande
GROUP BY NumProd HAVING COUNT(*)>3;
Attention : La clause HAVING ne s’utilise qu’avec GROUP BY.
Opérateurs ensemblistes : INTERSECT, MINUS, UNION
o Numéro des produits qui soit ont un prix inférieur à 100 F, soit ont été commandés par le client n° 2
SELECT NumProd FROM Produit WHERE PrixUni < 100
UNION
SELECT NumProd FROM Commande WHERE NumCLi = 2 ;
Fonctions SQL
ABS(n) : Valeur absolue de n ASCII(ch) : Valeur ASCII de ch
MOD(m, n) : Reste de m/n INSTR(ch, ssch) : Recherche de ssch dans ch
POWER(m, n) : mn LENGTH(ch) : Longueur de ch
SQRT(n) : Racine carrée de n SYSDATE : Date/heure système
ROUND(n, m) : Arrondi à10-m ADD_MONTHS(dte, n) : Ajout de n mois à dte
TRUNC(n, m) : Troncature à10-m MONTHS_BETWEEN(dt1, dt2) : Nombre de mois entre dt1 et dt2
CHR(n) : Caractère ASCII nunéro n TO_NUMBER(ch) : Conversion de ch en nombre
INITCAP(ch) : 1ère lettre en maj. TO_CHAR(x) : Conversion de x en chaîne
LOWER(ch) : ch en minuscules TO_DATE(ch) : Conversion de ch en date
UPPER(ch) : ch en majuscules NVL(x, val) : Remplace par val si x a la valeur NULL
REPLACE(ch, car) : Remplacement de caractère UID : Identifiant numérique de l’utilisateur
SUBSTR(ch, pos, lg) : Extraction de chaîne USER : Nom de l’utilisateur
4. VUES
Une vue est table virtuelle calculée à partir d’autres tables grâce à une requête. Son intérêt est :
o Simplification de l’accès aux données (exemple : masquer les opérations de jointure)
o Sauvegarde indirecte de requêtes complexes au niveau du noyau de Oracle)
o Présentation de mêmes données sous différentes formes adaptées aux différents usagers particuliers
o Renforcement de la sécurité des données (exemple : masquer des lignes et des colonnes sensibles aux usagers
non habilités)
Page 3 sur 4
Enis BELHASSEN Rappel - Langage SQL
Création d’une vue
CREATE VIEW nom_vue AS requête;
[Link] VIEW Noms AS SELECT Nom, Prenom FROM Client;
Ex2. CREATE VIEW Prod_com AS SELECT [Link], Dési, PrixUni, Date, Quantite
FROM Produit P, Commande C WHERE [Link]=[Link];
Pour que la mise à jour de données via une vue soit possible :
o Le mot clé DISTINCT doit être absent.
o La clause FROM doit faire référence à une seule table.
o La clause SELECT doit faire référence directement aux attributs de la table concernée (pas d’attribut dérivé).
o Les clauses GROUP BY et HAVING sont interdites.
Suppression d'une vue
DROP VIEW nom_vue;
5. TRANSACTION
Transaction : ensemble de mises à jour des données (différent des modifications structurelles)
Début de transaction : début de la session de travail ou fin de la transaction précédente
Contrôle des transactions
o Validation (et fin) d’une transaction : COMMIT;
o Annulation (et fin) d’une transaction :ROLLBACK;
o Fin de session de travail (avec la commande EXIT ou QUIT)
6. CREATION DES UTILISATEURS
Création des utilisateurs de la base de données
GRANT [CONNECT] [,RESOURCE] [,DBA] TO utilisateur [IDENTIFIED BY motdepasse];
Ex. GRANT CONNECT TO user_isamm IDENTIFIED BY manouba;
Les options CONNECT, RESOURCE et DBA déterminent les droits ou privilèges qui lui sont attribués. Les droits associés à ces
options sont :
o la connexion (CONNECT) : connexion à tout outil d'ORACLE
o la création de ressources (RESOURCE) : applicable pour un utilisateur ayant le privilège CONNECT. Offre les
droits de création de tables, d'indexes et regroupement
o l'administration (DBA) : englobe les droits des deux options précédentes. Offre en plus les droits d’accès à toutes
les données, création et suppression des utilisateurs
Transmission de privilèges
GRANT privilège ON table ou vue TO utilisateur ou PUBLIC [WITH GRANT OPTION];
ex. GRANT SELECT ON Client TO PUBLIC;
o Les privilèges sont :
SELECT : lecture, INSERT : insertion, UPDATE: mise à jour, DELETE: suppression (de tuples),
ALTER : destruction totale, INDEX : construction d’index, ALL : tous les privilèges
o tout utilisateur qui reçoit un privilège sur un objet avec l'option WITH GRANT OPTION peut accorder ce
privilège à un autre utilisateur
Suppression de privilèges
REVOKE privilège ON table ou vue FROM utilisateur PUBLIC;
[Link] SELECT ON Client FROM PUBLIC;
Page 4 sur 4
