User:Belomb stan/My sandbox
Contents
Initiation au langage SQL
Généralités
Qu'est ce que SQL?
L'utilisation du langage SQL (Structured Query Language) permet de normaliser le le développement des applications de base de données relationnelles dans les entreprises.
SQL est un langage évolué de manipulation de base de données relationnelles, il opère sur
des ensembles logiques appelés relations.De manière synthétique, on peut dire que SQL est un langage relationnel, il manipule donc des tables(i.e. des relations, c’est-à-dire des ensembles) par l’intermédiaire de requêtes qui produisent également des tables.
Terminologie
| Modèle relationnel | Modèle relationnel | Standard SQL |
| français | Anglais | Standard SQL |
| Relation | Relation | Table |
| Domaine | Domain | Domaine |
| Attribut | Attribute | Colonne |
| n-uplet | tuple | Ligne |
| Clé primaire | Primary key | Primary key |
Catégories d'instruction
Les instructions SQL sont regroupées en catégories en fonction de leur utilité et des entités manipulées. Nous pouvons distinguer cinq catégories, qui permettent :
- la définition des éléments d’une base de données (tables, colonnes, clefs, index, contraintes, . . .),
- la manipulation des données (insertion, suppression, modification, extraction, . . .),
- la gestion des droits d’accès aux données (acquisition et révocation des droits),
- la gestion des transactions,
- et enfin le SQL intégré.
Langage de définition de données
Le langage de définition de données (LDD, ou Data Definition Language, soit DDL en anglais) est un langage orienté au niveau de la structure de la base de données. Le LDD permet de créer,modifier,supprimer des objets. Il permet également de définir le domaine des données (nombre, chaîne de caractères, date, booléen, . . .) et d’ajouter des contraintes de valeur sur les données. Il permet enfin d’autoriser ou d’interdire l’accès aux données et d’activer ou de désactiver l’audit pour un utilisateur donné. Les instructions du LDD sont : CREATE, ALTER, DROP, AUDIT, NOAUDIT, ANALYZE, RENAME,TRUNCATE.
Définir une base – Langage de définition de données (LDD)
Introduction aux contraintes d’intégrité
Soit le schéma relationnel minimaliste suivant : -Acteur(Num-Act, Nom, Prénom) -Jouer(Num-Act, Num-Film) -Film(Num-Film, Titre, Année)
- Contrainte d’intégrité de domaine
Toute comparaison d’attributs n’est acceptée que si ces attributs sont définis sur le même domaine. Le SGBD doit donc constamment s’assurer de la validité des valeurs d’un attribut. C’est pourquoi la commande de création de table doit préciser, en plus du nom, le type de chaque colonne. Par exemple, pour la table Film, on précisera que le Titre est une chaîne de caractères et l’année une date. Lors de l’insertion de n-uplets dans cette table, le système s’assurera que les différents champs du n-uplet satisfont les contraintes d’intégrité de domaine des attributs précisées lors de la création de la base. Si les contraintes ne sont pas satisfaites, le n-uplet n’est, tout simplement, pas inséré dans la table.
- Contrainte d’intégrité de relation (ou d’entité)
Lors de l’insertion de n-uplets dans une table (i.e. une relation), il arrive qu’un attribut soit inconnu ou non défini. On introduit alors une valeur conventionnelle notée NULL et appelée valeur nulle.Cependant, une clé primaire ne peut avoir une valeur nulle. De la même manière, une clé primaire doit toujours être unique dans une table. Cette contrainte forte qui porte sur la clé primaire est appelée.
- contrainte d’intégrité de relation.
Tout SGBD relationnel doit vérifier l’unicité et le caractère défini (NOT NULL) des valeurs de la clé primaire.
- Contrainte d’intégrité de référence
Dans tout schéma relationnel, il existe deux types de relation :
- les relations qui représentent des entités de l’univers modélisé ; elles sont qualifiées de statiques,ou d’indépendantes ; les relations Acteur et Film en sont des exemples ;
- les relations dont l’existence des n-uplets dépend des valeurs d’attributs situées dans d’autres
relations ; il s’agit de relations dynamiques ou dépendantes ; la relation Jouer en est un exemple. Lors de l’insertion d’un n-uplet dans la relation Jouer, le SGBD doit vérifier que les valeurs Num-Act et Num-Film correspondent bien, respectivement, à une valeur de Num-Act existant dans la relation Acteur et une valeur Num-Film existant dans la relation Film. Lors de la suppression d’un n-uplet dans la relation Acteur, le SGBD doit vérifier qu’aucun n-uplet de la relation Jouer ne fait référence, par l’intermédiaire de l’attribut Num-Act, au n-uplet que l’on cherche à supprimer. Le cas échéant, c’est-à-dire si une, ou plusieurs, valeur correspondante de Num-Act existe dans Jouer, quatre possibilités sont envisageables :
- interdire la suppression ;
- supprimer également les n-uplets concernés dans Jouer ;
- avertir l’utilisateur d’une incohérence ;
- mettre les valeurs des attributs concernés à une valeur nulle dans la table Jouer, si l’opération est possible (ce qui n’est pas le cas si ces valeurs interviennent dans une clé primaire) ;
Créer une table : CREATE TABLE
Une table est un ensemble de lignes et de colonnes. La création consiste à définir (en fonction de l’analyse) le nom de ces colonnes, leur format (type), la valeur par défaut à la création de la ligne (DEFAULT) et les règles de gestion s’appliquant à la colonne (CONSTRAINT).
- Création simple
La commande de création de table la plus simple ne comportera que le nom et le type de chaque colonne de la table. A la création, la table sera vide, mais un certain espace lui sera alloué. La syntaxe est la suivante : CREATE TABLE nom_table (nom_col1 TYPE1, nom_col2 TYPE2, ...) Quand on crée une table, il faut définir les contraintes d’intégrité que devront respecter les données que l’on mettra dans la table (cf. section 4.2.3). Les types de données Les types de données peuvent être : INTEGER : Ce type permet de stocker des entiers signés codés sur 4 octets. BIGINT : Ce type permet de stocker des entiers signés codés sur 8 octets. REAL : Ce type permet de stocker des réels comportant 6 chiffres significatifs codés sur 4 octets. DOUBLE PRECISION : Ce type permet de stocker des réels comportant 15 chiffres significatifs codés sur 8 octets. NUMERIC[(précision, [longueur])] : Ce type de données permet de stocker des données numériques à la fois entières et réelles avec une précision de 1000 chiffres significatifs. longueur précise le nombre maximum de chiffres significatifs stockés et précision donne le nombre maximum de chiffres après la virgule. CHAR(longueur) : Ce type de données permet de stocker des chaînes de caractères de longueur fixe.longueur doit être inférieur à 255, sa valeur par défaut est 1. VARCHAR(longueur) : Ce type de données permet de stocker des chaînes de caractères de longueur variable. longueur doit être inférieur à 2000, il n’y a pas de valeur par défaut. DATE : Ce type de données permet de stocker des données constituées d’une date. TIMESTAMP : Ce type de données permet de stocker des données constituées d’une date et d’une heure. BOOLEAN : Ce type de données permet de stocker des valeurs Booléenne. MONEY : Ce type de données permet de stocker des valeurs monétaires. TEXT : Ce type de données permet des stocker des chaînes de caractères de longueur variable.
- Création avec Insertion de données
On peut insérer des données dans une table lors de sa création par la commande suivante : CREATE TABLE nom_table [(nom_col1, nom_col2, ...)] AS SELECT ... On peut ainsi, en un seul ordre SQL créer une table et la remplir avec des données provenant du résultat d’un SELECT (cf. section 4.5 et 4.7). Si les types des colonnes ne sont pas spécifiés, ils correspondront à ceux du SELECT. Il en va de même pour les noms des colonnes. Le SELECT peut contenir des fonctions de groupes mais pas d’ORDER BY car les lignes d’une table ne peuvent pas être classées.
Contraintes d’intégrité
- Syntaxe
A la création d’une table, les contraintes d’intégrité se déclarent de la façon suivante :
CREATE TABLE nom_table (nom_col_1 type_1 [CONSTRAINT nom_1_1] contrainte_de_colonne_1_1
[CONSTRAINT nom_1_2] contrainte_de_colonne_1_2
... ...
[CONSTRAINT nom_1_m] contrainte_de_colonne_2_m,
nom_col_2 type_2 [CONSTRAINT nom_2_1] contrainte_de_colonne_2_1
[CONSTRAINT nom_2_2] contrainte_de_colonne_2_2
... ...
[CONSTRAINT nom_2_m] contrainte_de_colonne_2_m,
...
nom_col_n type_n [CONSTRAINT nom_n_1] contrainte_de_colonne_n_1
[CONSTRAINT nom_n_2] contrainte_de_colonne_n_2
... ...
[CONSTRAINT nom_n_m] contrainte_de_colonne_n_m,
[CONSTRAINT nom_1] contrainte_de_table_1,
[CONSTRAINT nom_2] contrainte_de_table_2,
... ...
[CONSTRAINT nom_p] contrainte_de_table_p
)
- Contraintes de colonne
Les différentes contraintes de colonne que l’on peut déclarer sont les suivantes : NOT NULL ou NULL : Interdit (NOT NULL) ou autorise (NULL) l’insertion de valeur NULL pour cet attribut. UNIQUE : Désigne l’attribut comme clé secondaire de la table. Deux n-uplets ne peuvent recevoir des valeurs identiques pour cet attribut, mais l’insertion de valeur NULL est toutefois autorisée. Cette contrainte peut apparaître plusieurs fois dans l’instruction. PRIMARY KEY : Désigne l’attribut comme clé primaire de la table. La clé primaire étant unique, cette contrainte ne peut apparaître qu’une seule fois dans l’instruction. La définition d’une clé primaire composée se fait par l’intermédiaire d’une contrainte de table. En fait, la contrainte PRIMARY KEY est totalement équivalente à la contraite UNIQUE NOT NULL. REFERENCES table [(colonne)] [ON DELETE CASCADE] : Contrainte d’intégrité référentielle pour l’attribut de la table en cours de définition. Les valeurs prises par cet attribut doivent exister dans l’attribut colonne qui possède une contrainte PRIMARY KEY ou UNIQUE dans la table table. En l’absence de précision d’attribut colonne, l’attribut retenu est celui correspondant à la clé primaire de la table table spécifiée. CHECK (condition) : Vérifie lors de l’insertion de n-uplets que l’attribut réalise la condition condition. DEFAULT valeur : Permet de spécifier la valeur par défaut de l’attribut.
- Contraintes de table
Les différentes contraintes de table que l’on peut déclarer sont les suivantes : PRIMARY KEY (colonne, ...) : Désigne la concaténation des attributs cités comme clé primaire de la table. Cette contrainte ne peut apparaître qu’une seule fois dans l’instruction. UNIQUE (colonne, ...) : Désigne la concaténation des attributs cités comme clé secondaire de la table. Dans ce cas, au moins une des colonnes participant à cette clé secondaire doit permettre de distinguer le n-uplet. Cette contrainte peut apparaître plusieurs fois dans l’instruction. FOREIGN KEY (colonne, ...) REFERENCES table [(colonne, ...)] [ON DELETE CASCADE | SET NULL] : Contrainte d’intégrité référentielle pour un ensemble d’attributs de la table en cours de définition. Les valeurs prises par ces attributs doivent exister dans l’ensemble d’attributs spécifié et posséder une contrainte PRIMARY KEY ou UNIQUE dans la table table. CHECK (condition) : Cette contrainte permet d’exprimer une condition qui doit exister entre plusieurs attributs de la ligne. Les contraintes de tables portent sur plusieurs attributs de la table sur laquelle elles sont définies.Il n’est pas possible de définir une contrainte d’intégrité utilisant des attributs provenant de deux ou plusieurs tables. Ce type de contrainte sera mis en œuvre par l’intermédiaire de déclencheurs de base de données .
- Complément sur les contraintes
ON DELETE CASCADE : Demande la suppression des n-uplets dépendants, dans la table en cours de définition, quand le n-uplet contenant la clé primaire référencée est supprimé dans la table maître. ON DELETE SET NULL : Demande la mise à NULL des attributs constituant la clé étrangère qui font référence au n-uplet supprimé dans la table maître. La suppression d’un n-uplet dans la table maître pourra être impossible s’il existe des n-uplets dans d’autres tables référençant cette valeur de clé primaire et ne spécifiant pas l’une de ces deux options.
Supprimer une table : DROP TABLE
Supprimer une table revient à éliminer sa structure et toutes les données qu’elle contient. Les index associés sont également supprimés. La syntaxe est la suivante : DROP TABLE nom_table
Modifier une table : ALTER TABLE
- Ajout ou modification de colonnes
ALTER TABLE nom_table {ADD/MODIFY} ([nom_colonne type [contrainte], ...])
- Ajout d’une contrainte de table
ALTER TABLE nom_table ADD [CONSTRAINT nom_contrainte] contrainte La syntaxe de déclaration de contrainte est identique à celle vue lors de la création de table. Si des données sont déjà présentes dans la table au moment où la contrainte d’intégrité est ajoutée,toutes les lignes doivent vérifier la contrainte. Dans le cas contraire, la contrainte n’est pas posée sur la table.
- Renommer une colonne
ALTER TABLE nom_table RENAME COLUMN ancien_nom TO nouveau_nom
- Renommer une table
ALTER TABLE nom_table RENAME TO nouveau_nom
Langage de manipulation de données
Le langage de manipulation de données (LMD, ou Data Manipulation Language, soit DML en anglais) est l’ensemble des commandes concernant la manipulation des données dans une base de données. Le LMD permet l’ajout, la suppression et la modification de lignes, la visualisation du contenu des tables et leur verrouillage. Les instructions du LMD sont : INSERT, UPDATE, DELETE, SELECT, EXPLAIN, PLAN, LOCK,TABLE. Ces éléments doivent être validés par une transaction pour qu’ils soient pris en compte.
Insertion de n-uplets : INSERT INTO
La commande INSERT permet d’insérer une ligne dans une table en spécifiant les valeurs à insérer. La syntaxe est la suivante :
INSERT INTO nom_table(nom_col_1, nom_col_2, ...) VALUES (val_1, val_2, ...)
La liste des noms de colonne est optionnelle. Si elle est omise, la liste des colonnes sera par défaut la liste de l’ensemble des colonnes de la table dans l’ordre de la création de la table. Si une liste de colonnes est spécifiée, les colonnes ne figurant pas dans la liste auront la valeur NULL. Il est possible d’insérer dans une table des lignes provenant d’une autre table. La syntaxe est la suivante :
INSERT INTO nom_table(nom_col1, nom_col2, ...) SELECT ...
Le SELECT peut contenir n’importe quelle clause sauf un ORDER BY
Modification de n-uplets : UPDATE
La commande UPDATE permet de modifier les valeurs d’une ou plusieurs colonnes, dans une ou plusieurs lignes existantes d’une table. La syntaxe est la suivante :
UPDATE nom_table
SET nom_col_1 = {expression_1 | ( SELECT ...) },
nom_col_2 = {expression_2 | ( SELECT ...) },
...
nom_col_n = {expression_n | ( SELECT ...) }
WHERE predicat
Les valeurs des colonnes nom_col_1, nom_col_2, ..., nom_col_n sont modifiées dans toutes les lignes qui satisfont le prédicat predicat. En l’absence d’une clause WHERE, toutes les lignes sont mises à jour. Les expressions expression_1, expression_2, ..., expression_n peuvent faire référence aux anciennes valeurs de la ligne.
Suppression de n-uplets : DELETE
La commande DELETE permet de supprimer des lignes d’une table. La syntaxe est la suivante :
DELETE FROM nom_table WHERE predicat
Toutes les lignes pour lesquelles predicat est évalué à vrai sont supprimées. En l’absence de clause WHERE, toutes les lignes de la table sont supprimées.
Langage de protections d’accès
Le langage de protections d’accès (ou Data Control Language, soit DCL en anglais) s’occupe de gérer les droits d’accès aux tables. Les instructions du DCL sont : GRANT, REVOKE.
Langage de contrôle de transaction
Le langage de contrôle de transaction (ou Transaction Control Language, soit TCL en anglais) gère les modifications faites par le LMD, c’est-à-dire les caractéristiques des transactions et la validation et l’annulation des modifications. Les instructions du TCL sont : COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION
SQL intégré
Le SQL intégré (Embedded SQL) permet d’utiliser SQL dans un langage de troisième génération (C, Java, Cobol, etc.) :
- déclaration d’objets ou d’instructions ;
- exécution d’instructions ;
- gestion des variables et des curseurs ;
- traitement des erreurs.
Les instructions du SQL intégré sont : DECLARE, TYPE, DESCRIBE, VAR, CONNECT, PREPARE,EXECUTE, OPEN, FETCH, CLOSE, WHENEVER.
la suite en cours d'élaboration...
