Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Historique SQL (Structured Query Language) • ’70 – centres de recherche • 1982

Historique SQL (Structured Query Language) • ’70 – centres de recherche • 1982 – introduit commercialement par IBM • Aujourd’hui – il est utilisé dans la plus part des SGBD : DB2, Oracle, MS SQL Server, Sybase Bibliographie : – Tout document sur SQL – Les spécifications « officielles » Standardisation • SQL1 – ISO 1989 – Fonctions de l’algèbre relationnelle, pour la définition, la mise à jour, la recherche • SQL2 – ISO 1992 – Extension qui supporte des types de données variés et des commandes manquantes • SQL3 – propositions (ISO 1996?) – Extension objet Rôle de SQL Outils de programmation Moteur de base de données BD Générateur de formulaires Frontal de base de données Outil de requêtes interactif Programme d’application SQL SQL SQL SQL Autres systèmes informatiques Passerelle de base de données SQL SQL SGBD d’autres éditeurs Fonctions SQL • Définitions de données • Mise à jour des données • Recherche de données • Calcul en colonne • Contrôle de l’accès aux données • … Application de test • Données compagnies aériennes – IDC, Nom, Adresse, Ville, Pays • Données passagers – IDP, Nom, Prénom, Adresse, Ville, Age • Données vols – IDV, IDC, Départ, Arrivée, Heure • Données réservations – IDR, IDV, IDP, Date Types de données (Oracle) • Nombres : – NUMBER [(précision, échelle)] – DECIMAL [(précision, échelle)] – INTEGER – FLOAT • Caractères – CHAR [(nbMaxChar)] – VARCHAR, VARCHAR2 (Oracle8) – LONG • Temps – DATE (Oracle) – TIME • Types binaires – RAW, LONG RAW Création de table CREATE TABLE <nom table> ( <élément de table>+ ) <élément de table> ::= <définition de colonne> | <contrainte de table> <définition de colonne> ::= <nom de colonne> <type de données> [<clause défaut>] [<contrainte de colonne>] Nom Table , Nom Colonne – Unique pour un compte (le nom de table) – Commence par une lettre – Compte moins de 30 caractères – Ne peux pas être un mot réservé SLQ – Il devrait être descriptif CREATE TABLE Compagnies_Aérienne (IDC Int, NomC Char(20), AdresseC Char(50), VilleC Char(20), PaysC Char(20) ) Contraintes de domaine • NOT NULL • Clause défaut : DEFAULT <val> • UNIQUE • CHECK (condition) – plage ou liste de valeurs possibles c. de colonne c. de colonne ou de table CREATE TABLE Passagers ( IDP Int NOT NULL UNIQUE, Nom Char(20), PrénomP Char(30), Adresse Char(50), Ville Char(20) DEFAULT ‘Paris’ CHECK (Ville IN (‘Paris’, ’Rome’, ’Madrid’, ’Londres’, ‘Amsterdam’, ‘Frankfort’) ,) Age Dec(2) CHECK (Age BETWEEN 18 AND 70) ) Contraintes d’entité • De colonne : PRIMARY KEY • De table : PRIMARY KEY (Atr1, …, Atrn) – Les attributs d’une clé doivent être NOT NULL CREATE TABLE Vols ( IDV Int NOT NULL PRIMARY KEY, IDC Int NOT NULL, Départ Char(20), Arrivée Char(20), Heure Num(4) ) CREATE TABLE Réservations ( IDR Int NOT NULL UNIQUE, IDV Int NOT NULL, IDP Int NOT NULL, Date DATE, [CONSTRAINT PK_Res] PRIMARY KEY (IDR, IDV, IDP) ) Contraintes référentielles • De colonne : REFERENCES table [attribut] • De table : FOREIGN KEY (attribut+) REFERENCES table [(attribut+)] CREATE TABLE Vols ( IDV Int NOT NULL PRIMARY KEY, IDC Int NOT NULL, Départ Char(20), Arrivée Char(20), Heure Num(4) , [CONSTRAINT FK_Vols] FOREIGN KEY (IDC) REFERENCES Compagnies_Aérienne ) CREATE TABLE Réservations ( IDR Int NOT NULL UNIQUE, IDV Int NOT NULL REFERENCES Vols (IDV), IDP Int NOT NULL REFERENCES Passagers, Date DATE, PRIMARY KEY (IDR, IDV, IDP) ) Les tables de l’application (1) CREATE TABLE Compagnies_Aériennes ( IDC Int NOT NULL PRIMARY KEY, NomC Char(20) UNIQUE, AdresseC Char(50), VilleC Char(20), PaysC Char(20) ) CREATE TABLE Passagers ( IDP Int NOT NULL PRIMARY KEY, NomP Char(20), PrénomP Char(30), AdresseP Char(50), Ville Char(20), Age Dec(2) CHECK (Age BETWEEN 18 AND 70) ) Les tables de l’application (2) CREATE TABLE Vols ( IDV Int NOT NULL PRIMARY KEY, IDC Int NOT NULL REFERENCES Compagnies_Aériennes, Départ Char(20), Arrivée Char(20), Heure Number(4) ) CREATE TABLE Réservations ( IDR Int NOT NULL UNIQUE, IDV Int NOT NULL REFERENCES Vols (IDV), IDP Int NOT NULL REFERENCES Passagers, Date DATE, PRIMARY KEY (IDR, IDV, IDP) ) Possibilité d’utiliser des scripts - fichiers *.sql avec les définitions Autres opérations • Visualisation des schémas – DESCRIBE <nom table> • Suppression des tables – DROP TABLE <nom table> Modifications des schémas (1) • ALTER TABLE <nom table> <altération> <altération> = ADD (<spec. colonne>) MODIFY (<spec. colonne>) DROP (<nom colonne>) ADD CONSTRAINT (<constr.>) DROP CONSTRAINT Modifications des schémas (exemples) • ALTER TABLE Vols ADD (Durée Number(4,2) ); • ALTER TABLE Vols MODIFY (Départ Char(30) ); • ALTER TABLE Vols DROP (Durée); • ALTER TABLE Passager DROP PRIMARY KEY; • ALTER TABLE Passager ADD CONSTRAINT PK_Pass PRIMARY KEY (IDP); Mise à jour des données • Insertion – INSERT INTO … • Modification – UPDATE … • Suppression – DELETE … Insertion INSERT INTO <nom table> [(<nom colonne>+)] VALUES (<valeurs>+) | <commande recherche> Exemples : INSERT INTO Compagnies_Aériennes VALUES (‘1’, ‘Air France’, ’45 Rue de Paris’, ‘Roissy’, ‘France’); INSERT INTO Compagnies_Aériennes (IDC, NomC, PaysC) VALUES (‘2’, ‘KLM’, ‘Netherlands’); INSERT INTO Compagnies_Aériennes SELECT … INSERT INTO Passagers … Vols … Modification UPDATE <nom de table> SET <nom de colonne> = {<expression > | NULL } [ WHERE { <condition de recherche> | CURRENT OF <nom de curseur> } ] Exemples : UPDATE Compagnies_Aériennes SET Ville=‘Amsterdam’ WHERE NomC=‘KLM’; UPDATE Passagers SET Age=Age+1; UPDATE Passagers SET Adresse=‘1 Rue X’ WHERE NomP=‘Dupont’ AND PrénomP=‘Pierre’; Suppression DELETE FROM <nom de table> [ WHERE { <condition de recherche> | CURRENT OF <nom de curseur> } ] Exemples : DELETE FROM Vols ; DELETE FROM Vols WHERE Arrivée=‘Bagdad’; Recherche de données SELECT ... FROM … [<condition>] Permet : • Projection • Sélection • Jointures • Tri • Fonction de calcul • Agrégats • Opérations ensemblistes • Imbrication de questions Projection SELECT [ALL | DISTINCT] <colonnes>* FROM <nom table> Exemples : SELECT * FROM Passagers; SELECT DISTINCT Arrivée FROM Vols; SELECT NomP, PrénomP, Age FROM Passagers; Par défaut, les colonnes résultat ont le même nom On peut redéfinir le nom : SELECT Arrivée AS Destinations FROM Vols Par défaut Pour éliminer les doubles Projection relationnelle ΠArrivée (Vols) Sélection / Restriction SELECT [ALL | DISTINCT] <colonnes>* FROM <nom table> WHERE <condition> La condition peut contenir AND, OR, NOT – Comparaison à une valeur – Comparaison à un intervalle de valeurs – Comparaison à une liste de valeurs – Comparaison à un filtre (pour données chaîne de caractère) – Etc. Comparaison à une valeur Condition = exp1 op exp2 exp IS NULL exp IS NOT NULL = != < > <= >= Peut contenir + - * / attributs constantes Exemples : SELECT * FROM Vols WHERE Départ=‘Paris’; SELECT NomP, PrénomP FROM Passagers WHERE Ville=‘Paris’ AND Age<26; SELECT * FROM Produits WHERE Prix*Quantité<5000; Sélection relationnelle σDépart=‘Paris’ (Vols) Comparaison à une plage de valeurs Condition = exp [NOT] BETWEEN v1 AND v2 Exemple : SELECT NomP, PrénomP, Age FROM Passagers WHERE Age BETWEEN 18 AND 30; Condition = exp [NOT] IN (liste valeurs) Exemple : SELECT * FROM Vols WHERE Arrivée IN (‘Londres’, ‘Rome’, ‘Amsterdam’); Arrivée=‘Londres’ OR Arrivée=‘Rome’ OR Arrivée =‘Amsterdam’ Comparaison à un filtre • Pour chaînes de caractères • Condition = colonne [NOT] LIKE <modèle de chaîne> • ‘_ ’ remplace un caractère • ‘%’ remplace une séquence de longueur quelconque • sous Oracle 9 : ‘%’ toujours à la fin Exemples : SELECT NomC,AdresseC FROM Compagnies_Aériennes WHERE Pays LIKE ‘F%’ ; SELECT * FROM Passagers WHERE NomP LIKE ‘A%d%’ AND PrénomP LIKE ‘J___%’ AND Adresse LIKE ‘__ Rue M%’ ; Jointures (1) • Produit cartésien : SELECT * FROM Vols, Réservations ; • Jointure (naturelle – équijointure) : SELECT NomP, Date FROM Passagers, Réservations WHERE Passagers.IDP=Réservations.IDP ; SELECT c.NomC, c.Pays, v.Arrivée FROM CompagniesAériennes c, Vols v WHERE c.IDC=v.IDC ; • Inéquijointure : SELECT p.NomP, p.Ville, v.Départ FROM Passagers p, Vols v WHERE p.Ville != v.Arrivée ; Jointures (2) • Jointures de plusieurs tables SELECT p.NomP FROM Passagers p, Réservations r, Vols v WHERE p.IDP=r.IDP AND r.IDV=v.IDV AND p.Ville=‘Paris’ AND v.Arrivée=‘Londres’ ; • Autojointure : SELECT p1.NomP, p1.Age FROM Passagers p1, Passagers p2 WHERE p1.Age>p2.Age AND p2.Nom=‘Pierre’ ; • Jointure externe (syntaxe Oracle) : SELECT r.IDR, r.Date, p.Nom FROM Réservations r, Passagers p WHERE p.IDP=r.IDP(+) ; Jointures en SQL2 (Oracle) SELECT <nom de table> [NATURAL] [{LEFT | RIGHT}] JOIN <nom de table> [ ON (<spécification de colonne>+ = <spécification de colonne>+) ] Tri des tuples • Pour trier les lignes on utilise ORDER BY • On peut trier selon plusieurs attributs SELECT NomP, PrénomP, Age FROM Passagers ORDER BY NomP, PrénomP ; SELECT NomP, Adresse FROM Passagers ORDER BY Age DESC ; Décroissant Par défaut : croissant Fonctions de group • COUNT, SUM, AVG, MIN, MAX SELECT COUNT(*) FROM Passagers ; SELECT MIN(Age) FROM Passagers ; SELECT AVG(Prix), MAX(Prix) FROM Produits ; Regroupement des lignes • La clause GROUP BY • Permet le regroupement des lignes d’une table et à l’intérieur de chaque group, l’attribut spécifié à la même valeur SELECT IDV, COUNT(*) FROM Réservations GROUP BY IDV; SELECT Ville, AVG(Age) FROM Passagers GROUP BY uploads/Geographie/ pgs-sql.pdf