2016-02-05 Séances de cours 6 et 7 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 1 S’initier au langage SQL utilisé pour interroger les bases de données (BD) à partir du schéma relationnel. Traduire les questions d’affaires en requêtes SQL permettant d’extraire des données d’intérêt de la BD. Formuler les requêtes en utilisant le langage de manipulation de données (LMD SQL). TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. Requêtes SQL Opérations de mise à jour en SQL 2 SELECT INSERT DELETE UPDATE Gestion des transactions en SQL COMMIT ROLLBACK TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 3 1 2016-02-05 Requêtes SQL Opérations de mise à jour en SQL SELECT INSERT DELETE UPDATE Gestion des transactions en SQL COMMIT ROLLBACK TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 4 Structured Query Language Norme établie pour SGBD relationnel Partie LDD (Langage de définition de données) Conceptuel : CREATE SCHEMA, TABLE,... Externe : CREATE VIEW, GRANT,… Interne : CREATE INDEX, CLUSTER,... Partie LMD (Langage de manipulation de données) Partie LCD (Langage de contrôle de données) SELECT, INSERT, DELETE, UPDATE GRANT, REVOKE TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 5 Requête Ensemble de commande SQL dont l’exécution (par le SGBD) permet d’extraire des données désirées Interroge les données de la base de données ▪ Ne crée pas une copie des données Génère un résultat qui se présente sous forme d’un tableau contenant des lignes et des colonnes ▪ Une ligne correspond à un enregistrement ▪ Une colonne correspond à un attribut ▪ Un champ correspond à l’intersection d’une ligne et d’une colonne (une valeur) Permet de générer une vue partielle de l’ensemble des données de la BD TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 6 2 2016-02-05 Requête Entrée : les conditions que doivent respecter les données Sortie : les données dans la BD qui respectent ces conditions Langage pour formuler la requête Résultat LMD SQL Une table virtuelle TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 7 Syntaxe de requêteSQL selectSQL | (requêteSQL) {UNION|INTERSECT|EXCEPT} (requêteSQL) Syntaxe du selectSQL SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY ... TCH054 Bases de données {[ALL|DISTINCT] expression [AS nomColonne] [,expression [AS nomColonne]]…}|* table [AS nomTable [(nomColonne[,nomColonne])]]] [,table [AS nomTable [(nomColonne[,nomColonne])]]]]… conditionSQL] nomColonne [,nomColonne]… conditionSQL] nomColonne [ASC|DESC] [,nomColonne[ASC|DESC]]…] © Robert Godin, Lévis Thériault, Hiver 2016. 8 Produire les noClient et dateCommande de toutes les SELECT noClient, dateCommande Commandes FROM noClient 10 20 10 10 30 20 40 40 SELECT FROM TCH054 Bases de données Commande dateCommande 01/06/2000 02/06/2000 02/06/2000 05/07/2000 09/07/2000 09/07/2000 15/07/2000 15/07/2000 Multi-ensemble ! ALL noClient, dateCommande Commande © Robert Godin, Lévis Thériault, Hiver 2016. 9 3 2016-02-05 Produire les noClient et dateCommande de toutes les SELECT DISTINCT noClient, dateCommande Commandes FROM noClient 10 20 10 10 30 20 40 Commande dateCommande 01/06/2000 02/06/2000 02/06/2000 05/07/2000 09/07/2000 09/07/2000 15/07/2000 noClient, dateCommande (Commande) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 10 Sélectionner les Articles dont le prix est inférieur à $20.00 et le numéro est supérieur à 30 SELECT FROM WHERE * Article prixUnitaire < 20 AND noArticle > 30 noArticle 60 70 95 description Erable argenté Herbe à puce Génévrier prixUnitaire 15.99 10.99 15.99 prixUnitaire < 20.00 ET noArticle > 30 (Article) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 11 {conditionSimple (conditionSQL)| NOT(conditionSQL)| conditionSQL AND conditionSQL | conditionSQL OR conditionSQL} Syntaxe (incomplète) de la conditionSimple : {expression {=|<|>|<=|>=|<>} expression| expression BETWEEN expression AND expression| expression {IS NULL |IS NOT NULL}| expression {IN |NOT IN} listeConstantes| expression {LIKE |NOT LIKE} patron} TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 12 4 2016-02-05 Sélectionner les Commandes du mois de juin de l'année 2000 SELECT FROM WHERE SELECT FROM WHERE TCH054 Bases de données * Commande dateCommande BETWEEN '01/06/2000' AND '30/06/2000' * Commande dateCommande >= '01/06/2000' AND dateCommande <='30/06/2000' © Robert Godin, Lévis Thériault, Hiver 2016. Sélectionner les Commandes du Client dont le noClient est 10 ou 40 ou 80 SELECT FROM WHERE SELECT FROM WHERE TCH054 Bases de données * Commande noClient IN (10, 40, 80) * Commande noClient = 10 OR noClient = 40 OR noClient = 80 © Robert Godin, Lévis Thériault, Hiver 2016. 14 Sélectionner les Clients dont le nomClient contient le mot Le SELECT * FROM WHERE 13 Client nomClient LIKE '%Le%' 2ième lettre du nomClient est un o et dernière lettre est un k SELECT * FROM WHERE TCH054 Bases de données Client nomClient LIKE '_o%k' © Robert Godin, Lévis Thériault, Hiver 2016. 15 5 2016-02-05 Sélectionner les Articles dont la description n'est pas une valeur nulle SELECT FROM WHERE TCH054 Bases de données * Article description IS NOT NULL © Robert Godin, Lévis Thériault, Hiver 2016. Produire les noClient et dateCommande des Commandes dont la date est supérieure au 05/07/2000 SELECT FROM WHERE noClient, dateCommande Commande dateCommande > '05/07/2000' noClient 30 20 40 40 TCH054 Bases de données 16 dateCommande 09/07/2000 09/07/2000 15/07/2000 15/07/2000 © Robert Godin, Lévis Thériault, Hiver 2016. 17 Produire toutes les combinaisons possibles de lignes de Client et de Commande... SELECT FROM * Client, Commande Client Commande TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 18 6 2016-02-05 Produire les informations au sujet des Clients et de leurs Commandes SELECT Client.noClient, nomClient, noTéléphone, noCommande, dateCommande Client, Commande Client.noClient = Commande.noClient FROM WHERE Client.noClient 10 10 10 20 20 30 40 40 nomClient Luc Sansom Luc Sansom Luc Sansom Dollar Tremblay Dollar Tremblay Lin Bô Jean Leconte Jean Leconte noTéléphone (999)999-9999 (999)999-9999 (999)999-9999 (888)888-8888 (888)888-8888 (777)777-7777 (666)666-6666 (666)666-6666 noCommande 1 3 4 2 6 5 7 8 dateCommande 01/06/2000 02/06/2000 05/07/2000 02/06/2000 09/07/2000 09/07/2000 15/07/2000 15/07/2000 Client.noClient, nomClient, noTéléphone, noCommande, dateCommande ( Client.noCliente = Commande.noClient (Client Commande)) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 19 Produire les informations au sujet des Clients et de leurs Commandes SELECT FROM * Client NATURAL JOIN Commande {SQL2} Client Commande Client NATURAL JOIN Commande {Illégal!} ▪ Il faut absolument enchâsser le JOIN dans un SELECT Jointure- (si les noms de colonnes de la jointure sont différents) SELECT FROM TCH054 Bases de données * Client JOIN Commande ON Client.noClient = Commande.numéroCLient {SQL2} © Robert Godin, Lévis Thériault, Hiver 2016. 20 Sélectionner les nomClient des Clients qui ont commandé au moins un plant d'herbe à puce SELECT FROM WHERE nomClient Client, Commande, LigneCommande, Article description = 'Herbe à puce' AND Client.noClient = Commande.noClient AND Commande.noCommande = LigneCommande.noCommande AND LigneCommande.noArticle = Article.noArticle nomClient ( description = "Herbe à puce" (Client Commande LigneCommande Article)) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 21 7 2016-02-05 ~Algèbre relationnelle SELECT FROM WHERE nomClient Client, Commande, LigneCommande, Article description = 'Herbe à puce' AND Client.noClient = Commande.noClient AND Commande.noCommande = LigneCommande.noCommande AND LigneCommande.noArticle = Article.noArticle Ordre quelconque des tables du FROM AND commutatif… Processus d ’évaluation de requête la plupart du temps… TCH054 Bases de données 22 ~ renommer () SELECT FROM WHERE SELECT FROM WHERE TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. Client.noClient, nomClient, noTéléphone, noCommande, dateCommande Client, Commande Client.noClient = Commande.noClient Cl.noClient, nomClient, noTéléphone, noCommande, dateCommande Client AS Cl, Commande AS Co Cl.noClient = Co.noClient © Robert Godin, Lévis Thériault, Hiver 2016. 23 Quels sont les Clients qui ont le même numéro de téléphone? SELECT FROM WHERE Client.noClient, Client2.noClient Client, Client AS Client2 Client.noTéléphone = Client2.noTéléphone Client.noClient, Client2.noClient, ( Client.noTéléphone = Client2.noTéléphone (Client Client2 (Client))) SELECT FROM noClient, noClient2 Client NATURAL JOIN {SQL2} Client AS Client2(noClient2, nomClient2, noTéléphone) noClient, noClient2 (Client Client2(noClient2, nomClient2, noTéléphone) (Client)) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 24 8 2016-02-05 Produire les informations au sujet des Clients et de leurs Commandes incluant les informations sur les Clients qui n’ont pas placé de Commande Client = Commande SELECT FROM Oracle « + » après colonne pour inclure la valeur NULL SELECT FROM WHERE TCH054 Bases de données * Client NATURAL LEFT OUTER JOIN Commande {SQL2} * Client,Commande Client.noClient = Commande.noClient(+) © Robert Godin, Lévis Thériault, Hiver 2016. 25 Produire les noms et numéros de téléphone des Employés qui sont aussi des Clients de la pépinière noClient 10 20 30 40 50 60 70 80 Table Client nomClient Luc Sansom Dollard Tremblay Lin Bô Jean Leconte Hafedh Alaoui Marie Leconte Simon Lecoq Dollard Tremblay noTéléphone (999)999-9999 (888)888-8888 (777)777-7777 (666)666-6666 (555)555-5555 (666)666-6666 (444)444-4419 (333)333-3333 codeEmployé CASD1 PIOY1 LAFH1 HASC1 Table Employé nomEmployé Dollard Tremblay Yan Piochuneshot Yvan Lafleur Jean Leconte noTéléphone (888)888-8888 911 (111)111-1111 (666)666-6666 (SELECT nomClient as nomPersonne, noTéléphone FROM Client) INTERSECT (SELECT nomEmployé as nomPersonne, noTéléphone FROM Employé) nomPersonne Dollard Tremblay Jean Leconte TCH054 Bases de données noTéléphone (888)888-8888 (666)666-6666 © Robert Godin, Lévis Thériault, Hiver 2016. 26 La liste des noArticle avec le prixUnitaire avant et après inclusion de la taxe de 15% SELECT noArticle, prixUnitaire, prixUnitaire*1.15 AS prixPlusTaxe FROM Article noArticle 10 20 40 50 60 70 80 81 90 95 TCH054 Bases de données prixUnitaire 10.99 12.99 25.99 22.99 15.99 10.99 26.99 25.99 25.99 15.99 prixPlusTaxe 12.64 14.94 29.89 26.44 18.39 12.64 31.04 29.89 29.89 18.39 © Robert Godin, Lévis Thériault, Hiver 2016. 27 9 2016-02-05 Produire le détail de chacun des Articles commandés la Commande #1 incluant le prix total avant et après la taxe de 15% pour chacun des Articles commandés SELECT L.noArticle, quantité, prixUnitaire, prixUnitaire*quantité AS total, prixUnitaire*quantité*1.15 AS totalPlusTaxe FROM LigneCommande AS L, Article AS A WHERE L.noArticle = A.noArticle AND L.noCommande = 1 noArticle 10 70 90 quantité 10 5 1 TCH054 Bases de données prixUnitaire 10.99 10.99 25.99 total 109.90 54.95 25.99 totalPlusTaxe 126.38 63.19 29.89 © Robert Godin, Lévis Thériault, Hiver 2016. 28 Les Articles dont le prixUnitaire incluant la taxe de 15% est inférieur à $16.00 SELECT noArticle, prixUnitaire, prixUnitaire*1.15 AS prixPlusTaxe FROM Article WHERE prixUnitaire*1.15 < 16 noArticle 10 20 70 TCH054 Bases de données prixPlusTaxe 12.64 14.94 12.64 © Robert Godin, Lévis Thériault, Hiver 2016. Symbole + * / || prixUnitaire 10.99 12.99 10.99 29 Signification Somme Différence Produit Division Concaténation de chaîne (SQL2) Conversions automatiques entre types compatibles TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 30 10 2016-02-05 Les Commandes de la journée SELECT * FROM WHERE Commande dateCommande = CURRENT_DATE CURRENT TIME CURRENT TIMESTAMP CURRENT_USER (ou USER) SESSION_USER SYSDATE Oracle TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 31 +, - (unaire) *, / +, -, || =, <>, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN, SIMILAR NOT AND OR TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. POSITION(patron IN chaîne) CHARACTER_LENGTH(chaîne) OCTET_LENGTH (chaîne) BIT_LENGTH(chaîne) EXTRACT(champ FROM dateOuTime) SUBSTRING (chaîne FROM indiceDébut FOR nombreCaractères) UPPER | LOWER (chaîne) TRIM ([LEADING|TRAILING|BOTH] caractère FROM chaîne) CAST(expression AS type) … Voir documentation du SGBD TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 32 33 11 2016-02-05 SQL> SELECT SYSDATE FROM DUAL; SYSDATE -------02-02-05 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'; Session altered. SQL> SELECT SYSDATE FROM DUAL; SYSDATE ------------------05-02-2002 09:08:26 SQL> SELECT TO_DATE('05/02/2000', 'DD/MM/YYYY') FROM DUAL; TO_DATE('05/02/2000 ------------------05-02-2000 00:00:00 SQL> SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL; TO_CHAR(SY ---------22/01/2002 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 34 SQL> SELECT SYSDATE + INTERVAL '1' DAY FROM DUAL; SYSDATE+INTERVAL'1' ------------------23-01-2002 16:02:18 SQL> SELECT SYSDATE - INTERVAL '1' DAY FROM DUAL; SYSDATE-INTERVAL'1' ------------------21-01-2002 16:02:18 SQL> SELECT SYSDATE + 1 FROM DUAL; SYSDATE+1 ------------------23-01-2002 16:02:18 SQL> SELECT SYSDATE + 1/24 FROM DUAL; SYSDATE+1/24 ------------------22-01-2002 17:02:18 SQL> SELECT SYSDATE + INTERVAL '30' SECOND FROM DUAL; SYSDATE+INTERVAL'30 ------------------22-01-2002 16:02:48 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. Arithmétique Comparaison (>, <, …) 35 opérande NULL => NULL opérande NULL => UNKNOWN x TRUE TRUE TRUE UNKNOWN UNKNOWN UNKNOWN FALSE FALSE FALSE TCH054 Bases de données y TRUE UNKNOWN FALSE TRUE UNKNOWN FALSE TRUE UNKNOWN FALSE x AND y TRUE UNKNOWN FALSE UNKNOWN UNKNOWN FALSE FALSE FALSE FALSE x OR y TRUE TRUE TRUE TRUE UNKNOWN UNKNOWN TRUE UNKNOWN FALSE © Robert Godin, Lévis Thériault, Hiver 2016. NOT x FALSE FALSE FALSE UNKNOWN UNKNOWN UNKNOWN TRUE TRUE TRUE 36 12 2016-02-05 Si x est NULL SELECT * FROM T WHERE x = 0 OR x <> 0 UNKNOWN OR UNKNOWN = UNKNOWN ▪ pas dans le résultat ! TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 37 Le nombre d'Articles différents à vendre ainsi que le prixUnitaire moyen des Articles SELECT FROM COUNT(*) AS nombreArticles, AVG(prixUnitaire) AS prixMoyen Article nombreArticles 10 TCH054 Bases de données SELECT FROM prixMoyen 19.49 © Robert Godin, Lévis Thériault, Hiver 2016. 38 Count(DISTINCT prixUnitaire) AS nombrePrix Article nombrePrix 6 SELECT FROM Count(prixUnitaire) AS nombrePrixNonNull Article nombrePrixNonNull 10 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 39 13 2016-02-05 Produire le nombre de Commandes passées par chacun des Clients qui ont passé au moins une Commande SELECT FROM GROUP BY noClient, COUNT(*) AS nombreCommandes Commande noClient Table Commande noCommande dateCommande 1 01/06/2000 3 02/06/2000 4 05/07/2000 2 02/06/2000 6 09/07/2000 5 09/07/2000 7 15/07/2000 8 15/07/2000 TCH054 Bases de données SELECT FROM GROUP BY noLivraison 100 101 100 102 102 100 103 104 105 noClient 10 10 10 20 20 30 40 40 noClient 10 20 30 40 nombreCommandes 3 2 1 2 © Robert Godin, Lévis Thériault, Hiver 2016. 40 noCommande, noArticle, SUM(quantitéLivrée) AS totalLivré, COUNT(*)AS nombreLivraisons DétailLivraison noCommande, noArticle Table DétailLivraison noCommande noArticle 1 10 1 10 1 70 2 40 2 95 3 20 1 90 4 40 5 70 quantitéLivrée 7 3 5 2 1 1 1 1 2 noCommande 1 1 1 2 2 3 4 5 noArticle 10 70 90 40 95 20 40 70 totalLivré 10 5 1 2 1 1 1 2 nombreLivraisons 2 1 1 1 1 1 1 1 Calcul de plusieurs agrégats à la fois avec CUBE et ROLLUP SQL:1999 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 41 Produire le nombre de Commandes passées par chacun des Clients qui ont passé deux Commandes ou plus SELECT FROM GROUP BY HAVING noClient, COUNT(*) AS nombreCommandes Commande noClient COUNT(*) >= 2 Table Commande noCommande dateCommande 1 01/06/2000 3 02/06/2000 4 05/07/2000 2 02/06/2000 6 09/07/2000 5 09/07/2000 7 15/07/2000 8 15/07/2000 TCH054 Bases de données noClient 10 10 10 20 20 30 40 40 noClient 10 20 30 40 © Robert Godin, Lévis Thériault, Hiver 2016. nombreCommandes 3 2 1 2 42 14 2016-02-05 SELECT FROM WHERE GROUP BY HAVING noClient, COUNT(*) AS nombreCommandes Commande dateCommande > '02/06/2000' noClient COUNT(*) >= 2 Table Commande noCommande dateCommande 1 01/06/2000 3 02/06/2000 4 05/07/2000 2 02/06/2000 6 09/07/2000 5 09/07/2000 7 15/07/2000 8 15/07/2000 TCH054 Bases de données noClient 10 10 10 20 20 30 40 40 noClient 10 20 30 40 nombreCommandes 1 1 1 2 © Robert Godin, Lévis Thériault, Hiver 2016. 43 Produire la quantité qui a été livrée pour l'Article #50 de la Commande #4 noLivraison 100 100 101 102 102 100 103 104 105 Table DétailLivraison noCommande noArticle 1 10 1 70 1 10 2 40 2 95 3 20 1 90 4 40 5 70 quantitéLivrée 7 5 3 2 1 1 1 1 2 SELECT CASE WHEN SUM(quantitéLivrée) IS NULL THEN 0 ELSE SUM(quantitéLivrée) END AS quantitéTotaleLivrée FROM DétailLivraison WHERE noArticle = 50 AND noCommande = 4 quantitéTotaleLivrée 0 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 44 Les Clients en ordre alphabétique du nom SELECT FROM ORDER BY SELECT FROM ORDER BY TCH054 Bases de données * Client nomClient * Client nomClient DESC, noTéléphone ASC © Robert Godin, Lévis Thériault, Hiver 2016. 45 15 2016-02-05 Les Clients qui ont passé au moins une Commande SELECT * FROM Client WHERE noClient IN (SELECT noClient FROM Commande) Client Commande SELECT FROM WHERE TCH054 Bases de données DISTINCT Client.noClient, nomClient, noTéléphone Client, Commande Client.noClient = Commande.noClient © Robert Godin, Lévis Thériault, Hiver 2016. 46 Chercher les LigneCommandes pour lesquelles au moins une Livraison a été effectuée SELECT * FROM LigneCommande WHERE (noCommande,noArticle) IN (SELECT noCommande, noArticle FROM DétailLivraison) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 47 Sélectionner les Commandes du Client Hugh Paycheck SELECT * FROM Commande WHERE noClient = (SELECT noClient FROM Client WHERE nomClient = 'Hugh Paycheck') Exception si plusieurs lignes retournées par SELECT imbriqué TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 48 16 2016-02-05 Produire les informations au sujet des Clients qui ont passé au moins une Commande SELECT * Référence à une colonne FROM Client non locale WHERE 0 < (SELECT COUNT(*) FROM Commande WHERE noClient = Client.noClient) POUR chaque ligne de Client Exécuter le SELECT suivant : (SELECT COUNT(*) FROM Commande WHERE noClient = Client.noClient) SI le compte retourné > 0 Placer la ligne de Client dans la table du résultat à retourner FIN SI FIN POUR TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 49 Produire les informations au sujet des Clients qui ont passé au moins une Commande SELECT * FROM Client WHERE EXISTS (SELECT * FROM Commande WHERE noClient = Client.noClient) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 50 Les Clients qui ont passé au moins deux Commandes SELECT * FROM Client WHERE NOT UNIQUE (SELECT noClient FROM Commande WHERE noClient = Client.noClient) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 51 17 2016-02-05 Commandes passées après la dernière Livraison (date ultérieure) SELECT * FROM Commande WHERE dateCommande > ALL (SELECT dateLivraison FROM Livraison) Commandes passées après au moins une des Livraisons SELECT * FROM Commande WHERE dateCommande > ANY (SELECT dateLivraison FROM Livraison) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 52 T1 T2 NOT EXISTS (T1 EXCEPT T2) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. noArticle 10 70 SELECT noCommande FROM Commande WHERE NOT EXISTS ((SELECT noArticle FROM Article WHERE prixUnitaire = 10.99 ) EXCEPT (SELECT noArticle FROM LigneCommande WHERE noCommande = Commande.noCommande ) ) noCommande 1 5 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 53 noCommande 1 1 1 2 2 3 4 4 5 5 5 6 6 7 7 noArticle 10 70 90 40 95 20 40 50 70 10 20 10 40 50 95 54 18 2016-02-05 Produire les noClient et dateCommande des Commandes dont la dateCommande est supérieure au 05/07/2000 SELECT noClient, dateCommande FROM (SELECT * {SQL 2} FROM Commande WHERE dateCommande > '05/07/2000' ) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. Requêtes SQL Opérations de mise à jour en SQL 55 SELECT INSERT DELETE UPDATE Gestion des transactions en SQL COMMIT ROLLBACK TCH054 Bases de données INSERT DELETE UPDATE © Robert Godin, Lévis Thériault, Hiver 2016. 56 Insertion dans une table Suppression de lignes Modification de lignes TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 57 19 2016-02-05 Insérer une nouvelle ligne dans la table Client INSERT INTO Client VALUES (100, 'G. Lemoyne-Allaire', '911') Changer l ’ordre de défaut INSERT INTO Client(nomClient, noClient, noTéléphone) VALUES ('G. Lemoyne-Allaire', 100, '911') TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. CREATE TABLE Article (noArticle INTEGER description VARCHAR(20), prixUnitaire DECIMAL(10,2) quantitéEnStock INTEGER PRIMARY KEY (noArticle) ) 58 NOT NULL, NOT NULL, DEFAULT 0 NOT NULL , INSERT INTO Article(noArticle, prixUnitaire) VALUES (30, 5.99) INSERT INTO Article(noArticle, description, prixUnitaire, quantitéEnStock) VALUES (30, NULL, 5.99, 0) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 59 Produire les lignes de DétailLivraison pour la Livraison #106 à partir des LigneCommandes de la Commande #7 INSERT INTO DétailLivraison SELECT 106, noCommande, noArticle, quantité FROM LigneCommande WHERE noCommande = 7 TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 60 20 2016-02-05 Supprimer toutes les lignes de la table Client DELETE FROM Client Supprimer le Client #70 de la table Client DELETE WHERE FROM Client noClient = 70 Supprimer les Clients qui n'ont pas passé de Commande DELETE FROM Client WHERE noClient NOT IN (SELECT DISTINCT noClient FROM Commande) TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. Supprimer toutes les lignes de la table Client Plus performant que DELETE Outrepasse les mécanismes transactionnels normaux Meilleure récupération d’espace 61 TRUNCATE TABLE Client ROLLBACK peut être interdit TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. Changer le noTéléphone du Client #10 pour (222)222-2222 UPDATE SET WHERE Client noTéléphone = '(222)222-2222' noClient = 10 Augmenter tous les prixUnitaires des Articles de 10% UPDATE SET 62 Article prixUnitaire = prixUnitaire * 1.1 Modification de plusieurs colonnes à la fois UPDATE SET WHERE TCH054 Bases de données Article prixUnitaire = 12.99, quantitéEnStock = 5 noArticle = 10 © Robert Godin, Lévis Thériault, Hiver 2016. 63 21 2016-02-05 Requêtes SQL Opérations de mise à jour en SQL SELECT INSERT DELETE UPDATE Gestion des transactions en SQL COMMIT ROLLBACK TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. COMMIT WORK ROLLBACK WORK Début de transaction implicite 64 confirme la transaction en cours annule la transaction en cours début de session fin de la précédente Commande LDD provoque un COMMIT TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 65 Ouverture d'une connexion SQL Fermeture de la connexion SQL Début d'une transaction SQL COMMIT Transaction SQL Début d'une transaction SQL COMMIT Transaction SQL Début d'une transaction SQL COMMIT Transaction SQL Session SQL TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 66 22 2016-02-05 Godin, R. (2012). Systèmes de gestion de bases de données par l’exemple. 3ième édition, Montréal, Canada: Loze‐Dion, Chapitre 4. TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 67 Interface entre SQL et un programme TCH054 Bases de données © Robert Godin, Lévis Thériault, Hiver 2016. 68 23