Remerciez-le!

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

04/11/2021 1 Chapitre 3 Le langage SQL Ordre SQL 04/11/2021 2 Exemple CREATE TA

04/11/2021 1 Chapitre 3 Le langage SQL Ordre SQL 04/11/2021 2 Exemple CREATE TABLE Compagnie (comp CHAR(4), nrue INTEGER(3), rue VARCHAR(20), ville VARCHAR(15) DEFAULT 'Paris' COMMENT 'Par defaut : Paris', nomComp VARCHAR(15) NOT NULL, CONSTRAINT pk_Compagnie PRIMARY KEY(comp)); CREATE TABLE bdutil.Pilote (brevet VARCHAR(6), nom VARCHAR(30) NOT NULL, pseudo VARCHAR(8), nbHVol DECIMAL(7,2), compa CHAR(4), CONSTRAINT pk_Pilote PRIMARY KEY(brevet), CONSTRAINT ck_nbHVol CHECK (nbHVol BETWEEN 0 AND 20000), CONSTRAINT un_nom UNIQUE (nom), CONSTRAINT fk_Pil_compa_Comp FOREIGN KEY (compa) REFERENCES bdutil.Compagnie(comp)); Les moteurs de stockage MySQL Plusieurs moteurs sont proposés par la société MySQL AB, d’autres sont issus de communautés ou d’éditeurs indépendants. Parmi les moteurs natifs les plus utilisés, citons : • MyISAM : moteur par défaut. Ne supporte pas les transactions mais possède des fonctionnalités de recherche de texte. • InnoDB : sans doute le plus utilisé de nos jours. Supporte le mode transactionnel (verrouillage niveau ligne, commit et rollback) et les contraintes référentielles (clés étrangères). • MEMORY (anciennenemt HEAP) : stockage des données et index en RAM. Convient à des données non persistantes. • ARCHIVE : stockage des données sous une forme compressée (seuls les SELECT et INSERT sont possibles). Modèle idéal pour archiver des données. • CSV (Comma Separated Value) : stockage des données sous forme de fichiers texte dans lesquels les valeurs sont séparées par une virgule. • FEDERATED : convient pour les architectures réparties (plusieurs serveurs). • NDB (Network DataBase) : convient pour les architectures en cluster. 04/11/2021 3 Création de table CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [nomBase.]nomTable ( colonne1 type1 [NOT NULL | NULL] [DEFAULT valeur1] [COMMENT 'chaine1'] [, colonne2 type2 [NOT NULL | NULL] [DEFAULT valeur2] [COMMENT 'chaine2'] ] [CONSTRAINT nomContrainte1 typeContrainte1] ...) [ENGINE= InnoDB | MyISAM | ...]; Définition de contrainte CONSTRAINT nomContrainte UNIQUE (colonne1 [,colonne2]...) PRIMARY KEY (colonne1 [,colonne2]...) FOREIGN KEY (colonne1 [,colonne2]...) REFERENCES nomTablePere [(colonne1 [,colonne2]...)] [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] CHECK (condition) 04/11/2021 4 • La commande SHOW CREATE TABLE [nom_base.]nom_table: restitue l’instruction complète qui a permis la création de la table en question. • La commande SHOW FULL COLUMNS FROM [nom_base.]nom_table est plus complète que l’instruction DESCRIBE. Suppression des contraintes Contrainte NOT NULL: Il faut utiliser la directive MODIFY de l’instruction ALTER TABLE pour supprimer une contrainte NOT NULL existant sur une colonne. Dans notre exemple, détruisons la contrainte NOT NULL de la clé étrangère proprio dans la table Avion. ALTER TABLE Compagnie MODIFY nomComp VARCHAR(15) NULL; Contrainte UNIQUE: Il faut utiliser la directive DROP INDEX de l’instruction ALTER TABLE pour supprimer une contrainte d’unicité. ALTER TABLE Compagnie DROP INDEX un_nomC; Clé étrangère: L’option DROP FOREIGN KEY de l’instruction ALTER TABLE permet de supprimer une clé étrangère d’une table. La syntaxe générale est la suivante : ALTER TABLE [nomBase].nomTable DROP FOREIGN KEY nomContrainte; 04/11/2021 5 Intégrité référentielle L’intégrité référentielle forme le cœur de la cohérence d’une base de données relationnelle. Cette intégrité est fondée sur la relation entre clés étrangères et clés primaires (ou candidates : colonnes indexées uniques et non nulles) qui permettent de programmer des règles de gestion. [CONSTRAINT nomContrainte] FOREIGN KEY [id] (listeColonneEnfant) REFERENCES nomTable (listeColonneParent) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] Intégrité référentielle La désactivation des contraintes référentielles peut être intéressante pour accélérer des procédures de chargement d’importation et d’exportation massives de données externes. Ce mécanisme améliore aussi les performances de programmes batchs qui ne modifient pas des données concernées par l’intégrité référentielle. • SET FOREIGN_KEY_CHECKS=0 permet de désactiver temporairement (jusqu’à la réactivation) toutes les contraintes référentielles d’une base. • SET FOREIGN_KEY_CHECKS=1 permet de réactiver toutes les contraintes référentielles d’une base. L’intégrité est assurée de nouveau mais ne concerne que les mises à jour à venir (ajouts d’enregistrements, modifications de colonnes et suppressions d’enregistrements). Les éventuelles données présentes dans les tables qui ne vérifient pas l’intégrité sont toujours en base. 04/11/2021 6 Extraction des données SELECT [ { DISTINCT | DISTINCTROW } | ALL ] listeColonnes FROM nomTable1 [,nomTable2]... [ WHERE condition ] [ Group by clause Regroupement ] [ HAVING condition ] [ Order by clause Ordonnancement ] [ LIMIT [rangDépart,] nbLignes ] ; • clauseRegroupement : GROUP BY (expression1[,expression2]...) permet de regrouper des lignes selon la valeur des expressions (colonnes, fonction, constante, calcul). • HAVING condition : pour inclure ou exclure des lignes aux groupes (la condition ne peut faire intervenir que des expressions du GROUP BY). Group by et NULL la fonction GROUP_CONCAT() permet de regrouper les valeurs non nulles d'un groupe en une chaîne de caractère 04/11/2021 7 Création de table dynamique avec insertion de données CREATE TABLE NomsetHVoldesPilotes (nom VARCHAR(16), nbHVol DECIMAL(7,2), compa CHAR(4)); INSERT INTO NomsetHVoldesPilotes SELECT nom,nbHVol,compa FROM Pilote; CREATE TABLE NomsetHVoldesPilotes AS SELECT nom, nbHVol, compa FROM Pilote; Utilisation des alias Il n’est pas permis d’utiliser un alias de colonne dans la clause WHERE. Cette recommandation de la norme SQL s’explique par le fait que certaines expressions pourraient ne pas être déterminées pendant que la condition WHERE est évaluée. mysql> SELECT aliasDesPilotes.compa AS c1, aliasDesPilotes.nom FROM Pilote aliasDesPilotes WHERE c1 = 'AF'; >> ERROR 1054 (42S22): Unknown column 'c1' in 'where clause' 04/11/2021 8 Opérateurs ensemblistes • Intersection qui extrait des données présentes simultanément dans les deux tables ; • Union par les opérateurs UNION et UNION ALL qui fusionnent des données des deux tables ; • Différence qui extrait des données présentes dans une table sans être présentes dans la deuxième table ; • Produit cartésien par le fait de combiner des jeux de résultats, soit en utilisant plusieurs tables dans la clause FROM, soit à l’aide de l’opérateur CROSS JOIN ; • Division par le fait de pouvoir comparer deux ensembles entre eux par une notion d’égalité. Requêtes imbriquées (sous-interrogation) Une caractéristique puissante de SQL est la possibilité qu'un critère de recherche employé dans une clause WHERE (expression à droite d'un opérateur de comparaison) soit lui-même le résultat d'un SELECT ; c'est ce qu'on appelle une sous-interrogation. 04/11/2021 9 Sous-interrogations monolignes La sous requête ramène une seule ligne Exemple: Pilotes de la compagnie de nom 'Air France' ayant plus de 500 heures de vol: SELECT brevet, nom FROM Pilote WHERE compa = (SELECT comp FROM Compagnie WHERE nomComp = 'Air France') AND nbHVol>500; Pilotes ayant plus d’expérience que le pilote de brevet 'PL-2‘: SELECT brevet, nom, nbHVol FROM Pilote WHERE nbHVol > (SELECT nbHVol FROM Pilote WHERE brevet = 'PL-2'); Sous-interrogation ramenant plusieurs lignes Une sous-interrogation peut ramener plusieurs lignes à condition que l'opérateur de comparaison admette à sa droite un ensemble de valeurs. Les opérateurs permettant de comparer une valeur à un ensemble de valeurs sont : • l'opérateur IN • les opérateurs obtenus en ajoutant ANY ou ALL à la suite d'un opérateur de comparaison classique (=, !=, >, >=, <, <=) • ANY: la comparaison est vraie si elle est vraie pour au moins un des éléments de l'ensemble. • ALL: la comparaison sera vraie si elle est vraie pour tous les éléments de l'ensemble. 04/11/2021 10 Sous-interrogation ramenant plusieurs lignes Coordonnées des compagnies qui embauchent des pilotes de moins de 500 heures de vol: SELECT nomComp, nrue, rue, ville FROM Compagnie WHERE comp IN (SELECT compa FROM Pilote WHERE nbHVol < 500); Compagnies n’ayant pas de pilote: SELECT nomComp, nrue, rue, ville FROM Compagnie WHERE comp NOT IN (SELECT compa FROM Pilote WHERE compa IS NOT NULL); Intersection entre deux ensembles • L’intersection entre deux ensembles homogènes se programme à l’aide d’une requête de type: SELECT DISTINCT ensemble1 FROM Table1 WHERE ensemble1 IN (SELECT ensemble2 FROM Table2). Exemple: SELECT DISTINCT immatriculation, typeAv FROM AvionsdeSING WHERE immatriculation IN (SELECT immat FROM AvionsdeAF) AND typeAv IN (SELECT typeAvion FROM AvionsdeAF); ou (immatriculation, typeAv) in( select immat, typeAv from avionsdeAF); 04/11/2021 11 Différence La différence entre deux ensembles homogènes se programme à l’aide d’une requête du type: SELECT DISTINCT ensemble1 FROM Table1 WHERE ensemble1 NOT IN (SELECT ensemble2 FROM Table2). Table dérivée CTE (Common Table Expressions) • Une table dérivée est une table qui est utilisée comme une source de données, construite à l’occasion d’une requête donnée. • Elle consiste à déclarer les tables dérivées en amont de la requête principale. Comme une vue à usage unique, une CTE est une source de données qui sera interrogée soit dans la requête principale à l’aide d’un alias de table, soit dans une autre CTE qui sera définie après. L’utilisation de CTE facilite l’écriture de requêtes complexes. 04/11/2021 12 Une requête peut utiliser plusieurs tables dérivées en tant que sources de données. Ces tables dérivées seront exploitables à l’aide d’un alias de table. SELECT listeColonnes FROM (SELECT… FROM table1…) alias1 [,(SELECT… FROM table2…) alias2 …] [ WHERE …] [ ORDER BY …]; Sous-interrogations synchronisées Une sous-interrogation est synchronisée si elle manipule des colonnes d’une table du niveau supérieur. Une sous-interrogation synchronisée est exécutée une fois pour chaque enregistrement extrait par la requête de niveau supérieur. Cette technique peut être aussi utilisée dans les ordres UPDATE uploads/Voyage/ 3-chap3-req-imb.pdf