SQL : Langage de définition de données
publicité
SQL : Langage de définition de données SQL : 4 fonctions d'exploitation de SGBD SQL (Structured Query Language, traduisez Langage de requêtes structuré) est un langage informatique ayant pour objet le dialogue avec une base de données relationnelle. SQL couvre les quatre fonctions indispensables à la mise en oeuvre et à l'exploitation de bases de données relationnelles : • langage de définition de données (LDD) : CREATE, ALTER, DROP • langage d’interrogation de données (LID): SELECT • langage de manipulation de données (LMD) : INSERT, UPDATE, DELETE • langage de contrôle de données (LCD) : GRANT, REVOKE LDD : création et suppression de bases de données • Création de bases de données : CREATE DATABASE Nom_BD; Exemple : création d'une BD nommée ”Musique” : • Suppression d'une base de données : DROP DATABASE Nom_BD; Exemple : suppression de la BD nommée ”Musique” : • Afficher la liste des bases de données : SHOW DATABASES; LDD : Création d'une table Création d'une table dans une base de données : CREATE TABLE Nom_de_la_table (Nom_de_colonne1 Type_de_donnée, Nom_de_colonne2 Type_de_donnée, ...); Exemple : Création d'un table nommée ”artiste”, avec deux colonnes : identifiant de type entier comme clé primaire, nom de 20 caractères : Les types de données Pour chaque colonne que l'on crée, il faut préciser le type de données que le champ va contenir. Celui-ci peut être un des types suivants : Type de donnée Type alphanumérique Type alphanumérique Type numérique Type numérique Type numérique Type numérique Type horaire Type horaire Type horaire Syntaxe CHAR(n) VARCHAR(n) NUMBER(n,[d]) SMALLINT INTEGER FLOAT DATE TIME TIMESTAMP Description Chaîne de caractères de longueur fixe n (n<16383) Chaîne de caractères de n caractères maximum (n<16383) Nombre de n chiffres [optionnellement d après la virgule] Entier signé de 16 bits (-32768 à 32757) Entier signé de 32 bits (-2E31 à 2E31-1) Nombre à virgule flottante Date sous la forme 16/07/99 Heure sous la forme 12:54:24.85 Date et Heure Exemples : trouver les types des colonnes suivantes : Colonne Type codealbum de ALBUM titrealbum de ALBUM ASIN de ALBUM datesortie de ALBUM SQL : LDD Page 1 / 5 Les contraintes d'intégrité Une contrainte d'intégrité est une clause permettant de s'assurer que les données saisies dans la base soient conformes aux données attendues. Ces contraintes doivent être exprimées dès la création de la table. On distingue trois types de contraintes d'intégrité : • Contrainte de domaine : contrôle le format de la donnée saisie par rapport au domaine de valeur prévu pour l'attribut. Exemple : • Contraintes de relation (ou de table): concerne la clé primaire qui doit être unique. Exemple : • Contraintes d'intégrité référentielle : limite l'ajout ou la suppression des enregistrements liés par des clés étrangères. Exemple : Les contraintes de domaines sur les champs • Éviter les champs vides dans une table : NOT NULL Exemple : obliger la saisie du codeartiste : • Définir une valeur par défaut : DEFAULT <valeur> Exemple : définir la valeur par défaut de datesortie à la date du jour : • Forcer l'unicité d'une valeur : UNIQUE Exemple : vérifier que tous les emails sont différents : • Tester une condition sur un champ : CHECK Exemple : définir le domaine du code catégorie comme entier inférieur à 6 : Les contraintes de relationn La contrainte de relation définit une clé primaire unique pour chaque enregistrement. Le mot clé PRIMARY KEY permet de définir un ou plusieurs champ qui serviront de clé primaire : PRIMARY KEY (colonne1, colonne2, ....) Exemples : • définir le champ codealbum comme clé primaire de la table album : • définir les champs codealbum et codeartiste comme clé primaire de la table participer : Les contraintes d'intégrité référentielle La contrainte d'intégrité référentielle définit une clé étrangère en référence à la clé primaire d'une autre table. Le mot clé FOREIGN KEY permet de définir la colonne qui sera clé étrangère. Le mot clé REFERENCES permet de définir la clé primaire référencée : FOREIGN KEY (clé étrangère) REFERENCES table (clé primaire) SQL : LDD Page 2 / 5 Exemple : définir la colonne codecate de la table ALBUM comme clé étrangère en référence à la clé primaire de la table CATEGORIE : LDD : Modification d'une table Clause ALTER TABLE: modification d'une table. Ajout de colonne : ALTER + ADD ALTER TABLE Nom_table ADD Nom_colonne Type ; Exemples : • Ajout de la colonne email (unique) à la table ARTISTE : • Ajout de la colonne nbtitres (entier) à la table ALBUM : Modification du type d'une colonne : ALTER + MODIFY ALTER TABLE Nom_table MODIFY Nom_colonne Nouveau_Type ; Exemple : Modification du type de la colonne nomartiste de la table ARTISTE en chaine de 20 caractères maxi : Suppression d'une colonne : ALTER + DROP COLUMN ALTER TABLE Nom_table DROP COLUMN Nom_colonne ; Exemples : • Suppression de la colonne ASIN de la table ALBUM : • Suppression de la colonne email de la table ARTISTE : LDD : Suppression d'une table Clause DROP TABLE : supprimer une table DROP TABLE Nom_table; Exemple : suppression de table ARTISTE : Clause TRUNCATE : vider une table. Supprime les données mais conserve la structure. TRUNCATE TABLE Nom_table; Exemple : Vider la table PARTICIPER : LDD : Renommer une table Clause RENAME TABLE : renommer une table. RENAME TABLE Ancien_Nom TO Nouveau_nom; Exemple : Renommer la table ARTISTE en MUSICIEN : SQL : LDD Page 3 / 5 LDD : Création d'une vue Clause CREATE VIEW : création de vue Une vue est une table virtuelle qui rassemble des informations provenant de plusieurs tables. On parle de "vue" car il s'agit simplement d'une représentation des données dans le but d'une exploitation visuelle. Les données présentes dans une vue sont définies grâce à une clause SELECT : CREATE VIEW Nom_de_la_Vue (colonnes) AS SELECT …; Exemple : création d'une vue nommée Albums2000 regroupant le titre, le label et le nom de catégorie des albums sortis après l'an 2000: LDD : Suppression d'une vue Clause DROP VIEW : supprimer une vue DROP VIEW Nom_vue; Exemple : supprimer la vue Albums2000 : Exercices à faire sur la BD MUSIQUE fournie en annexe : 1. Créer les tables CATEGORIES et ALBUM 2. Modifier la table ALBUM pour ajouter le champs media (10 caractères max.) 3. Modifier la table ARTISTE pour ajouter le champs date-naissance (date). 4. Créer une table LABEL regroupant les différents éditeurs avec deux colonnes : codelabel et nomlabel 5. Modifier la table ALBUM : supprimer la colonne label et ajouter une colonne codelabel, clé étrangère en référence à la clé primaire de la table LABEL. 6. Vider la table ALBUM 7. Créer une vue nommée album-rock qui contient le code album, le titre, le label et la date de sortie des albums de catégorie « rock » SQL : LDD Page 4 / 5 ANNEXE : BD MUSIQUE Schéma relationnel de la Base de données « MUSIQUE » : ALBUM (codealbum, titrealbum, ASIN, label, datesortie, codecate) Clé primaire : codealbum Clé étrangère : codecate en référence à codecate de CATEGORIE CATEGORIE (codecate, nomcate) Clé primaire : codecate ARTISTE (codeartiste, nomartistel) Clé primaire : codeartiste PARTICIPER (codealbum,codeartiste) Clé primaire : codealbum + codeartiste Clé étrangère : codealbum en référence à codealbum de ALBUM codeartiste en référence à codeartiste de ARTISTE Extrait du contenu des tables : TABLE ALBUM codealbum titrealbum ASIN label datesortie codecate 1 'It's All Right with Me' 'B000MM1F4Q' 'Dreyfus' 27/032007 1 2 'I'm wide awake, it's morning' 'B00070FV0M' Saddle Creek 25/01/2005 3 3 'Babylon by bus' 'B00005MK9Y' 'Polygram Records' 14/02/1978 2 4 'OK Computer' 'B000002UJQ' 'Capitol' 3 12/11/1997 TABLE CATEGORIE codecate nomcate 1 'Jazz' 2 'Reggae' 3 'Rock' 4 'Classique' 5 'Chansons' TABLE ARTISTE codeartiste nomartiste 1 'Bob Marley' 2 'Bright Eyes' 3 'Sara Lazarus' 4 'Birelli Lagrene Project' 5 'Radiohead' TABLE PARTICIPER codealbum codeartiste 1 3 1 4 2 2 3 1 4 5 SQL : LDD Page 5 / 5
