Cours SQL

Cours Langage SQL : apprendre la gestion des bases de donnees avec sql server


Télécharger Cours Langage SQL : apprendre la gestion des bases de donnees avec sql server

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Cours Langage SQL : apprendre la gestion des bases de données

1.2 Normes SQL

SQL a été normalisé dès 1986 mais les premières normes, trop incomplètes, ont été ignorées par les éditeurs de SGBD.

La norme SQL2 (appelée aussi SQL92) date de 1992. Le niveau Entry Level est à peu près respecté par tous les SGBD relationnels qui dominent actuellement le marché.

SQL-2 définit trois niveaux :

~ Full SQL (ensemble de la norme)

~ Intermediate SQL

~ Entry Level (ensemble minimum à respecter pour se dire à la norme SQL-2)

SQL3 (appelée aussi SQL99) est la nouvelle norme SQL.

Malgré ces normes, il existe des différences non négligeables entre les syntaxes et fonctionnalités des différents SGBD. En conséquence, écrire du code portable n'est pas toujours simple dans le domaine des bases de données.

1.3 Utilitaires associés

Comme tous les autres SGBD, Oracle fournit plusieurs utilitaires qui faci-litent l'emploi du langage SQL et le développement d'applications de gestion s'appuyant sur une base de données relationnelle. En particulier SQLFORMS facilite grandement la réalisation des traitements effectués pendant la saisie ou la modification des données en interactif par l'utilisateur. Il permet de dessiner les écrans de saisie et d'indiquer les traitements associés à cette saisie. D'autres utilitaires permettent de décrire les états de sorties imprimés, de sauvegarder les données, d'échanger des données avec d'autres logiciels, de travailler en réseau ou de constituer des bases de données réparties entre plusieurs sites.

Ce cours se limitant strictement à l'étude du langage SQL, nous n'étudierons pas tous ces utilitaires. Pour taper et faire exécuter des commandes SQL, les séances de travaux pratiques utiliseront soit un petit utilitaire, SQL*PLUS, fourni par Oracle, soit un utilitaire gratuit que l'on peut récupérer gratuitement sur le Web et qui permet d'envoyer des commandes SQL à un SGBD distant.

Les commandes du langage SQL peuvent être tapées directement au cla-vier par l'utilisateur ou peuvent être incluses dans un programme écrit dans un langage de troisième génération (Java, Langage C, Fortran, Cobol, Ada,...) grâce à un précompilateur ou une librairie d'accès au SGBD.

1.4 Connexion et déconnexion

On entre dans SQL*PLUS par la commande :

SQLPLUS nom/mot-de-passe

Les deux paramètres, nom et mot-de-passe, sont facultatifs. Si on les omet sur la ligne de commande, SQL*PLUS les demandera, ce qui est préférable pour mot-de-passe car une commande ps sous Unix permet de visualiser les paramètres d'une ligne de commande et donc de lire le mot de passe.

Sous Unix, la variable d'environnement ORACLE_SID donne le nom de la base sur laquelle on se connecte.

Pour se déconnecter, l'ordre SQL à taper est EXIT (ou exit car on peut taper les commandes SQL en majuscules ou en minuscules).

1.5 Objets manipulés par SQL 1.5.1 Identificateurs

SQL utilise des identificateurs pour désigner les objets qu'il manipule : utilisateurs, tables, colonnes, index, fonctions, etc.

Un identificateur est un mot formé d'au plus 30 caractères, commençant obligatoirement par une lettre de l'alphabet. Les caractères suivants peuvent être une lettre, un chiffre, ou l'un des symboles # $ et _. SQL ne fait pas la différence entre les lettres minuscules et majuscules. Les voyelles accentuées ne sont pas acceptées.

Un identificateur ne doit pas figurer dans la liste des mot clés réservés (voir manuel de référence Oracle). Voici quelques mots clés que l'on risque d'utiliser comme identificateurs : ASSERT, ASSIGN, AUDIT, COMMENT, DATE, DECIMAL, DEFINITION, FILE, FORMAT, INDEX, LIST, MODE, OPTION, PARTITION, PRIVILEGES, PUBLIC, REF, REFERENCES, SELECT, SEQUENCE, SESSION, SET, TABLE, TYPE.

1.5.2 Tables

Les relations (d'un schéma relationnel; voir polycopié du cours sur le modèle relationnel) sont stockées sous forme de tables composées de lignes et de colonnes.

Si on veut utiliser la table créée par un autre utilisateur, il faut spécifier le nom de cet utilisateur devant le nom de la table :

BERNARD.DEPT

Remarques 1.1

(a) Selon la norme SQL-2, le nom d'une table devrait être précédé d'un nom de schéma (voir 5.1).

 (b) Il est d'usage (mais non obligatoire évidemment) de mettre les noms de table au singulier : plutôt EMPLOYE que EMPLOYES pour une table d'employés.

Table DUAL

C'est une particularité d'Oracle. Cette pseudo-table ne contient qu'une seule ligne et une seule colonne et ne peut être utilisée qu'avec une requête "select".

Elle permet de faire afficher une expression dont la valeur ne dépend d'aucune table en particulier.

Exemple 1.2

Afficher la date d'aujourd'hui, le nom de l'utilisateur et le résultat d'un calcul :

select sysdate, user, round(3676 / 7) from dual

1.5.3 Colonnes

Les données contenues dans une colonne doivent être toutes d'un même type de données. Ce type est indiqué au moment de la création de la table qui contient la colonne (voir 2.1).

Chaque colonne est repérée par un identificateur unique à l'intérieur de chaque table. Deux colonnes de deux tables différentes peuvent porter le même nom. Il est ainsi fréquent de donner le même nom à deux colonnes de deux tables différentes lorsqu'elles correspondent à une clé étrangère à la clé primaire référencée. Par exemple, la colonne "Dept" des tables DEPT et EMP.

Une colonne peut porter le même nom que sa table.

Le nom complet d'une colonne est en fait celui de sa table, suivi d'un point et du nom de la colonne. Par exemple, la colonne DEPT.LIEU

Le nom de la table peut être omis quand il n'y a pas d'ambiguïté sur la table à laquelle elle appartient, ce qui est généralement le cas.

 1.6. TYPES DE DONNÉES             

1.6.1 Types numériques

CIMAL peut avoir plus de décimales) :  DECIMAL(p, d ) correspond à des nombres décimaux qui ont p chiffres significatifs et d chiffres après la virgule; NUMERIC a la même syntaxe.

~ Numériques non exacts à virgule flottante : REAL (simple précision, avec au moins 7 chiffres significatifs),     DOUBLE PRECISION ou FLOAT (double précision, avec au moins 15 chiffres significatifs).

La définition des types non entiers dépend du SGBD (le nombre de chiffres significatifs varie). Reportez-vous au manuel du SGBD que vous utilisez pour plus de précisions.

Le type BIT permet de ranger une valeur booléenne (un bit) en SQL-2.

Exemple 1.3

SALAIRE DECIMAL(8,2)

définit une colonne numérique SALAIRE. Les valeurs auront au maximum 2 décimales et 8 chiffres au plus au total (donc 6 chiffres avant le point décimal).

Les constantes numériques ont le format habituel : -10, 2.5, 1.2E-8 (ce dernier représentant 1.2 x 10−8).

Type numérique d'Oracle

Oracle n'a qu'un seul type numérique NUMBER. Par soucis de compatibilité, Oracle permet d'utiliser les types SQL-2 mais ils sont ramenés au type NUMBER.

Lors de la définition d'une colonne de type numérique, on peut préciser le nombre maximum de chiffres et de décimales qu'une valeur de cette colonne pourra contenir :

NUMBER

NUMBER(taille_maxi)

NUMBER(taille_maxi, décimales)

 Si le paramètre décimales n'est pas spécifié, 0 est pris par défaut. La valeur absolue du nombre doit être inférieure à 10128. NUMBER est un nombre à virgule flottante (on ne précise pas le nombre de chiffres après la virgule) qui peut avoir jusqu'à 38 chiffres significatifs.



L'insertion d'un nombre avec plus de chiffres que taille_maxi sera refusée (taille_maxi ne prend en compte ni le signe ni le point décimal). Les décimales seront éventuellement arrondies en fonction des valeurs données pour taille_maxi et décimales.

1.6.2 Types chaîne de caractères

Types chaîne de caractères de SQL-2

Les constantes chaînes de caractères sont entourées par des apostrophes ('). Si la chaîne contient une apostrophe, celle-ci doit être doublée. Exemple : 'aujourd''hui'.

Il existe deux types pour les colonnes qui contiennent des chaînes de caractères :

~ le type CHAR pour les colonnes qui contiennent des chaînes de longueur constante.

La déclaration de type chaîne de caractères de longueur constante a le format suivant :

CHAR(longueur)

où longueur est la longueur maximale (en nombre de caractères) qu'il sera possible de stocker dans le champ; par défaut, longueur est égale à 1. L'insertion d'une chaîne dont la longueur est supérieure à longueur sera refusée. Une chaîne plus courte que longueur sera complétée par des espaces (important pour les comparaisons de chaînes). Tous les SGBD imposent une valeur maximum pour longueur (255 pour Oracle).

~ le type VARCHAR pour les colonnes qui contiennent des chaînes de lon-gueurs variables.

On déclare ces colonnes par :

VARCHAR(longueur)

longueur indique la longueur maximale des chaînes contenues dans la colonne. Tous les SGBD imposent une valeur maximum pour longueur (plusieurs milliers de caractères).

Types chaîne de caractères d'Oracle

Les types Oracle sont les types SQL-2 mais le type VARCHAR s'appelle VARCHAR2 dans Oracle (la taille maximum est de 2000 caractères).

 1.6.3 Types temporels

Types temporels SQL-2

Les types temporels de SQL-2 sont :

DATE réserve 2 chiffres pour le mois et le jour et 4 pour l'année;

TIME pour les heures, minutes et secondes (les secondes peuvent comporter un certain nombre de décimales) ;

TIMESTAMP permet d'indiquer un moment précis par une date avec heures, minutes et secondes (6 chiffres après la virgule; c'est-à-dire en microsecondes) ;

INTERVAL permet d'indiquer un intervalle de temps. Types temporels d'Oracle

Oracle offre le type DATE comme en SQL-2 mais pour Oracle une donnée de type DATE inclut un temps en heures, minutes et secondes.

Une constante de type "date" est une chaîne de caractères entre apos-trophes. Le format dépend des options que l'administrateur a choisies au moment de la création de la base. S'il a choisi de "franciser" la base, le format d'une date est "jour/mois/année", par exemple, '25/11/1992' (le format "américain" par défaut donnerait '25-NOV-1992'). L'utilisateur peut saisir des dates telles que '3/8/1993' mais les dates sont toujours affichées avec deux chiffres pour le jour et le mois, par exemple, '03/08/1993'.

Remarque 1.2

Ne pas oublier de donner 4 chiffres pour l'année, sinon la date risque d'être mal interprétée par Oracle (voir remarque 4.12 page 51).

1.6.4 Types binaires

Ce type permet d'enregistrer des données telles que les images et les sons, de très grande taille et avec divers formats.

SQL-2 fournit les types BIT et BIT VARYING (longueur constante ou non).

Les différents SGBD fournissent un type pour ces données mais les noms varient : LONG RAW pour Oracle, mais IMAGE pour Sybase, BYTE pour Informix, etc.

Nous n'utiliserons pas ce type de données dans ce cours.

 1.6.5 Valeur NULL

Une colonne qui n'est pas renseignée, et donc vide, est dite contenir la valeur "NULL". Cette valeur n'est pas zéro, c'est une absence de valeur. Voir aussi 1.8.2.

1.7 Sélections simples

L'ordre pour retrouver des informations stockées dans la base est "SE-LECT".

Nous étudions dans ce chapitre une partie simplifiée de la syntaxe, suffisant néanmoins pour les interrogations courantes. Une étude plus complète sera vue au chapitre 4.

SELECT exp1, exp2,... FROM table

WHERE prédicat

table est le nom de la table sur laquelle porte la sélection. exp1, exp2,... est la liste des expressions (colonnes, constantes,... ; voir 1.8) que l'on veut obtenir. Cette liste peut être "*", auquel cas toutes les colonnes de la table sont sélectionnées.

Exemples 1.4

(a)          SELECT * FROM DEPT

(b)          SELECT NOME, POSTE FROM EMP

(c)          SELECT NOME , SAL + NVL(COMM,0) FROM EMP

La clause WHERE permet de spécifier quelles sont les lignes à sélectionner.

Le prédicat peut prendre des formes assez complexes. La forme la plus simple est "exp1 op exp2", où exp1 et exp2 sont des expressions (voir 1.8) et op est un des opérateurs =, != (différent), >, >=, <, <=.

Exemple 1.5

SELECT MATR, NOME, SAL * 1.15 FROM EMP

WHERE SAL + NVL(COMM,0) >= 12500

...

Chapitre 3 Langage de manipulation des données

Le langage de manipulation de données (LMD) est le langage permettant de modifier les informations contenues dans la base.

Il existe trois commandes SQL permettant d'effectuer les trois types de modification des données :

INSERT ajout de lignes

UPDATE mise à jour de lignes

DELETE suppression de lignes

Ces trois commandes travaillent sur la base telle qu'elle était au début de l'exécution de la commande. Les modifications effectuées par les autres utilisateurs entre le début et la fin de l'exécution ne sont pas prises en compte (même pour les transactions validées).

3.1 Insertion

INSERT INTO table (col1,..., coln ) VALUES (val1,...,valn )

ou

INSERT INTO table (col1,..., coln ) SELECT ...

table est le nom de la table sur laquelle porte l'insertion. col1,..., colas est la liste des noms des colonnes pour lesquelles on donne une valeur. Cette liste est optionnelle. Si elle est omise, ORACLE prendra par défaut l'ensemble des colonnes de la table dans l'ordre où elles ont été données lors 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.

20 CHAPITRE 3. LANGAGE DE MANIPULATION DES DONNÉES

Exemples 3.1

(a)          INSERT INTO dept

VALUES (10, 'FINANCES', 'PARIS')

(b)          INSERT INTO dept (lieu, nomd, dept) VALUES ('GRENOBLE', 'RECHERCHE', 20)

La deuxième forme avec la clause SELECT permet d'insérer dans une table des lignes provenant d'une table de la base. Le SELECT a la même syntaxe qu'un SELECT normal.

Exemple 3.2

Enregistrer la participation de MARTIN au groupe de projet numéro 10 :

INSERT INTO PARTICIPATION (MATR, CODEP) SELECT MATR, 10 FROM EMP WHERE NOME = 'MARTIN'

Remarque 3.1

Les dernières versions d'Oracle permettent de mettre des selects à la place des valeurs dans un values. Chaque select ne doit renvoyer qu'une seule ligne. Il faut entourer chaque select avec des parenthèses. Cette possibilité n'est pas portable et il faut donc l'éviter dans les programmes.

Exemple :

insert into emp (matr, nomE, dept)

values (

(select matr + 1 from emp where nomE = 'Dupond'), 'Dupondbis', 20)

3.2 Modification

La commande UPDATE permet de modifier les valeurs d'un ou plusieurs champs, dans une ou plusieurs lignes existantes d'une table.

UPDATE table

SET col1 = exp1, col2 = exp2, ... WHERE prédicat

ou

UPDATE table



SET (col1, col2,...) = (SELECT ...) WHERE prédicat table est le nom de la table mise à jour; col1, col2, ... sont les noms des colonnes qui seront modifiées; exp1, exp2,... sont des expressions. Elles peuvent aussi être un ordre SELECT renvoyant les valeurs attribuées aux colonnes (deuxième variante de la syntaxe).

Les valeurs de col1, col2... sont mises à jour dans toutes les lignes satisfai-sant le prédicat. La clause WHERE est facultative. Si elle est absente, toutes les lignes sont mises à jour.

Le prédicat peut contenir des sous-interrogations (voir 4.6).

Exemples 3.3

(a)          Faire passer MARTIN dans le département 10 :

UPDATE EMP SET DEPT = 10

WHERE NOME = 'MARTIN'

(b)          Augmenter de 10 % les commerciaux :

UPDATE EMP

SET SAL = SAL * 1.1

WHERE POSTE = 'COMMERCIAL'

(c)          Donner à CLEMENT un salaire 10 % au dessus de la moyenne des salaires des secrétaires :

UPDATE EMP

SET SAL = (SELECT AVG(SAL) * 1.10

FROM EMP

WHERE POSTE = 'SECRETAIRE')

WHERE NOME = 'CLEMENT'

On remarquera que la moyenne des salaires sera calculée pour les va-leurs qu'avaient les salaires au début de l'exécution de la commande UPDATE et que les modifications effectuées sur la base pendant l'exé-cution de cette commande ne seront pas prises en compte.

(d)          Enlever (plus exactement, mettre à la valeur NULL) la commission de MARTIN:

UPDATE EMP

SET COMM = NULL

WHERE NOME = 'MARTIN'

3.3 Suppression

L'ordre DELETE permet de supprimer des lignes d'une table.

 22 CHAPITRE 3. LANGAGE DE MANIPULATION DES DONNÉES

DELETE FROM table WHERE prédicat

La clause WHERE indique quelles lignes doivent être supprimées. ATTENTION : cette clause est facultative; si elle n'est pas précisée, TOUTES LES LIGNES DE LA TABLE SONT SUPPRIMEES (heureusement qu'il existe ROLLBACK !).

Le prédicat peut contenir des sous-interrogations (voir 4.6).

Exemple 3.4 DELETE FROM dept WHERE dept = 10

3.4 Transactions

3.4.1 Généralités sur les transactions

Définitions

Une transaction est un ensemble de modifications de la base qui forment un tout indivisible : il faut effectuer ces modifications entièrement ou pas du tout, sous peine de laisser la base dans un état incohérent.

Exemple 3.5

Une transaction peut transférer une somme d'argent entre deux comptes d'un client d'une banque. Elle comporte deux ordres : un débit sur un compte et un crédit sur un autre compte. Si un problème empêche le crédit, le débit doit être annulé.

Une transaction est terminée :

~ soit par une validation qui entérine les modifications,

~ soit par une annulation qui remet la base dans son état initial.

Dans le modèle "plat" des transactions (celui utilisé par SQL), deux transactions ne peuvent se chevaucher ni être emboîtées l'une dans l'autre : dans une session de travail, la transaction en cours doit se terminer avant qu'une nouvelle transaction ne puisse démarrer.

Ce mécanisme est aussi utilisé pour assurer l'intégrité de la base en cas de fin anormale d'une tâche utilisateur : il y a automatiquement annulation des transactions non terminées.

Propriétés des transactions

Il est facile de se souvenir des propriétés essentielles des transactions : elles sont "ACID".

 Atomicité : un tout indivisible;

Cohérence : une transaction doit laisser la base dans un état cohérent; elle ne doit pas contredire une contrainte d'intégrité ou mettre les données dans un état anormal;

Isolation : les modifications effectuées par une transaction ne doivent être visibles par les autres transactions que lorsque la transaction est validée;

Durabilité : le SGBD doit garantir que les modifications d'une transaction validée seront conservées, même en cas de panne.

"AID" est du ressort du système transactionnel du SGBD.

"C" est du ressort de l'utilisateur (ou du programmeur) mais il est aidé ~ par "I", car ainsi il n'a pas à considérer les interactions avec les autres transactions,

~ par la vérification automatique des contraintes d'intégrité par le SGBD.

On verra dans l'étude de l'utilisation et de l'implémentation des SGBD que "I" est effectué par le système de contrôle de la concurrence et "AD" sont supportés par les procédures de reprise après panne.

3.4.2 Les transactions dans SQL

Dans la norme SQL, une transaction commence au début d'une session de travail ou juste après la fin de la transaction précédente. Elle se termine par un ordre explicite de validation (commit) ou d'annulation (rollback). Certains SGBD ne respectent pas la norme et demandent une commande explicite pour démarrer une transaction.

L'utilisateur peut à tout moment valider (et terminer) la transaction en cours par la commande COMMIT. Les modifications deviennent alors définitives et visibles à toutes les autres transactions.

L'utilisateur peut annuler (et terminer) la transaction en cours par la commande ROLLBACK. Toutes les modifications depuis le début de la transaction sont annulées.

IMPORTANT : cette section étudie les transactions en mode de fonctionnement "normal" (voir 6.2). Dans ce mode "READ COMMITED" les insertions, modifications et suppressions qu'une transaction a exécutées n'apparaissent aux autres transactions que lorsque la transaction est validée. Tous les autres utilisateurs voient la base dans l'état où elle était avant la transaction. Des compléments sur les transactions sont données dans le chapitre 6, en particulier en 6.2 et 6.5.1.

Les instructions SQL sont atomiques : quand une instruction provoque une erreur (par exemple si une contrainte d'intégrité n'est pas vérifiée), toutes les modifications déjà effectuées par cette instruction sont annulées. Mais cette erreur ne provoque pas nécessairement de rollback automatique de la transaction.

Remarque 3.2

Certains ordres SQL, notamment ceux de définitions de données (create table...), provoquent une validation automatique de la transaction en cours.

3.4.3 Autres modèles de transactions

Dans la norme SQL, la structure des transactions est plate et les transactions sont chaînées :

~ 2 transactions ne peuvent se chevaucher;

~ une transaction commence dès que la précédente se termine.

Ce modèle n'est pas toujours adapté aux situations concrètes, surtout pour les transactions longues et multi-sites :

~ elles peuvent être une source de frustration pour l'utilisateur si tout le travail effectué depuis le début de la transaction est annulée;

~ le fait que les transactions gardent jusqu'à leur fin les verrous qu'elles ont posés nuit à la concurrence.

D'autres modèles ont été proposés pour assouplir ce modèle.

Transactions emboîtées

Le modèle des transactions emboîtées permet à une transaction d'avoir des sous-transactions filles, qui elles-mêmes peuvent avoir des filles.

L'annulation d'une transaction parente annule toutes les transactions filles mais l'annulation d'une transaction fille n'annule pas nécessairement la transaction mère. Si la transaction mère n'est pas annulée, les autres tran-sactions filles ne sont pas annulées.

À l'annulation d'une transaction fille, la transaction mère peut

~ décider d'un traitement substitutif;

~ reprendre la transaction annulée;



~ s'annuler (si elle ne peut pas se passer de la transaction annulée) ;

~ ou même ignorer l'annulation (si le traitement que devait effectuer la transaction annulée n'est pas indispensable).

Ainsi, un traitement effectué dans une transaction fille peut être repris ou être remplacé par un traitement alternatif pour arriver au bout du traitement complet. Ce modèle est bien adapté aux transactions longues et multi-sites (une transaction fille par site sur le réseau) qui doivent faire face à des problèmes de rupture de réseau.

3.4. TRANSACTIONS      

SQL ne supporte pas ce modèle de transaction. Points de reprise

Sans passer au modèle des transactions emboîtées, les dernières versions des principaux SGBD (et la norme SQL3) ont assoupli le modèle des tran-sactions plates avec les points de reprise (appelés aussi points de sauvegarde; savepoint en anglais).

On peut désigner des points de reprise dans une transaction :

savepoint nomPoint

On peut ensuite annuler toutes les modifications effectuées depuis un point de reprise s'il y a eu des problèmes :

rollback to nomPoint

On évite ainsi d'annuler toute la transaction et on peut essayer de pallier le problème au lieu d'annuler la transaction globale.

Chapitre 5

Langage de définition des données

Le langage de définition des données (LDD est la partie de SQL qui permet de décrire les tables et autres objets manipulés par le SGBD.

5.1 Schéma

Un schéma est un ensemble d'objets (tables, vues, index, autorisations, etc...) gérés ensemble. On pourra ainsi avoir un schéma lié à la gestion du personnel et un autre lié à la gestion des clients. Un schéma est créé par la commande CREATE SCHEMA AUTHORIZATION.

Cette notion introduite par la norme SQL2 n'est pas vraiment prise en compte par Oracle qui identifie pour le moment un nom de schéma avec un nom d'utilisateur.

Autre notion de SQL2, le catalogue, est un ensemble de schémas. Un cata-logue doit nécessairement comprendre un schéma particulier qui correspond au dictionnaire des données (voir 5.8).

5.2 Tables

5.2.1 CREATE TABLE AS

La commande CREATE TABLE a déjà été vue au chapitre 2.1. Une variante (d'Oracle mais pas de la norme SQL2) permet d'insérer pendant la création de la table des lignes venant d'autres tables :

CREATE TABLE table (col type......) AS SELECT .....

On peut aussi spécifier des contraintes d'intégrité de colonne ou de table.

 Exemple 5.1

CREATE TABLE MINIDEPT(CLE INTEGER, NOM VARCHAR(20)) AS SELECT DEPT, NOMD FROM DEPT

Cet ordre créera une table MINIDEPT et la remplira avec deux co-lonnes des lignes de la table DEPT.

Il faut évidemment que les définitions des colonnes de la table créée et du résultat de la sélection soient compatibles en type et en taille.

On peut également ne pas donner les noms et type des colonnes de la table créée. Dans ce cas les colonnes de cette table auront les mêmes noms, types et tailles que celles de l'interrogation :

CREATE TABLE DEPT10 AS SELECT * FROM DEPT WHERE DEPT = 10

5.2.2 ALTER TABLE

Les sections 2.2.2 et 2.2.3 montrent comment gérer les contraintes d'intégrité avec la commande ALTER TABLE. Cette commande permet aussi de gérer les colonnes d'une table : ajout d'une colonne (après toutes les autres colonnes), suppression et modification d'une colonne existante.

Ajout d'une colonne - ADD

ALTER TABLE table

ADD (col1 type1, col2 type2, ...)

permet d'ajouter une ou plusieurs colonnes à une table existante. Les types possibles sont les mêmes que ceux décrits avec la commande CREATE TABLE. Les parenthèses ne sont pas nécessaires si on n'ajoute qu'une seule colonne.

L'attribut 'NOT NULL' peut être spécifié seulement si la table est vide (si la table contient déjà des lignes, la nouvelle colonne sera nulle dans ces lignes existantes et donc la condition 'NOT NULL' ne pourra être satisfaite).

Il est possible de définir des contraintes de colonne.

Exemple 5.2

alter table personne

add (email_valide char(1)

constraint personne_email_valide check(email_valide in ('o', 'n')))

Modification d'une colonne - MODIFY

ALTER TABLE table

MODIFY (col1 type1, col2 type2, ...)

col1, col2... sont les noms des colonnes que l'on veut modifier. Elles doivent

 bien sûr déjà exister dans la table. type1, type2,... sont les nouveaux types que l'on désire attribuer aux colonnes.

Il est possible de modifier la définition d'une colonne, à condition que la colonne ne contienne que des valeurs NULL ou que la nouvelle définition soit compatible avec le contenu de la colonne :

~ on ne peut pas diminuer la taille maximale d'une colonne.

~ on ne peut spécifier 'NOT NULL' que si la colonne ne contient pas de valeur nulle.

Il est toujours possible d'augmenter la taille maximale d'une colonne, tant qu'on ne dépasse pas les limites propres à SQL, et on peut dans tous les cas spécifier 'NULL' pour autoriser les valeurs nulles.

Exemple 5.3

alter table personne

modify (

prenoms null,

nom varchar(50))

On peut donner une contrainte de colonne dans la nouvelle définition de la colonne.

Exemple 5.4

alter table personne modify (

sexe char(1)

constraint personne_sexe_ck check(sexe in ('m', 'f')))

Suppression d'une colonne - DROP COLUMN

Oracle n'a ajouté cette possibilité que depuis la version 8i. Auparavant, il fallait se contenter de mettre toutes les valeurs de la colonne à NULL (si on voulait récupérer de la place). On pouvait aussi se débarrasser de la colonne en créant une nouvelle table sans la colonne en copiant les données par create table as, et en renommant la table du nom de l'ancienne table.

ALTER TABLE table DROP COLUMN col

La colonne supprimée ne doit pas être référencée par une clé étrangère ou être utilisée par un index.

Renommer une colonne - RENAME COLUMN

ALTER TABLE table

RENAME COLUMN ancien_nom TO nouveau_nom permet de renommer une table.



308