La création de tables LE LANGAGE SQL : LDD Lordre CREATE CREATE TABLE nom_de_table (Nom_colonne Type_colonne, Nom_colonne Type_colonne,

La création de tables

LE LANGAGE SQL : LDD

L’ordre CREATE

CREATE TABLE nom_de_table (Nom_colonne Type_colonne, Nom_colonne Type_colonne, Nom_colonne Type_colonne, ... );


Exemple :

Voici 3 structures de tables :

TELEPHONE (NUMERO, #TYPE,#MARQUE,DATE_ACHAT, PRIX,NUM_PORTABLE,COULEUR)

MARQUE (MARQUE,LIB_MARQUE, PAYS)

TYPE_TEL(TYPE,LIB_TYPE)


Exemple :

CREATE TABLE TELEPHONE (NUMERO INTEGER , TYPE CHAR(2), MARQUE INTEGER, DATE_ACHAT DATE, PRIX DECIMAL (9,2), NUM_PROPRIETAIRE INTEGER, COULEUR VARCHAR(25));


Exemple :

CREATE TABLE TYPE_TEL (TYPE CHAR(2),

LIB_TYPE VARCHAR(25));

CREATE TABLE MARQUE_TEL (MARQUE INTEGER,

LIB_MARQUE VARCHAR(25), PAYS VARCHAR(30));


La suppression de tables

L’ordre DROP

DROP TABLE nom_de_table;

syntaxe

DROP TABLE TELEPHONE;


La modification de tables

L’ordre ALTER

ALTER TABLE nom_de_table [ADD nom_de_colonne Type_colonne] [,DROP COLUMN nom_de_colonne] [,ADD CONSTRAINT nom_contrainte] [,DROP CONSTRAINT nom_contrainte];

Exemples : Ajouter une colonne : ALTER TABLE TELEPHONE ADD TELEP_NUM_PIN INTEGER;

Supprimer une colonne :

ALTER TABLE TELEPHONE DROP COLUMN TELEP_NUM_PIN;


Renommer une table (RENAME)

RENAME nom_de_table_ancien TO nom_de_table_nouveau;

RENAME TELEPHONE TO SAV_TELEPHONE;

Attention : certains SGBDR ne connaissent pas cet ordre. Dans ce cas il faut créer une autre table avec la même structure comme ceci :

CREATE TABLE SAV_TELEPHONE AS SELECT * FROM TELEPHONE;

puis supprimer la table initiale : DROP TABLE TELEPHONE;


Les vues

CREATE VIEW <Nom_Vue> AS SELECT ...

Exemple : CREATE VIEW TEL_DATE AS SELECT TELEPHONE.DATE_ACHAT, TELEPHONE.TYPE , TYPE_TEL.LIB_TYPE, TELEPHONE.MARQUE ,MARQUE_TEL.LIB_MARQUE

FROM TELEPHONE, TYPE_TEL, MARQUE_TEL

WHERE TELEPHONE.TYPE = TYPE_TEL.TYPE AND TELEPHONE.MARQUE = MARQUE_TEL.MARQUE;

Les index


CREATE [UNIQUE] INDEX <nom index> ON <nom table> <nom colonne 1> [ASC|DESC], <nom colonne 2> [ASC|DESC], ... ...

Exemples : CREATE INDEX I2_TYPE ON TELEPHONE (TYPE); CREATE INDEX I3_TYPMAR ON TELEPHONE (TYPE, MARQUE); CREATE INDEX I4_COULEUR ON TELEPHONE (COULEUR DESC);


La suppression d’index

DROP INDEX <nom_index>

DROP INDEX I2_TYPE; DROP INDEX I4_COULEUR;


L’intégrité des données

Les contraintes d’intégrité permettent de maintenir la base cohérente. On confie au SGBDR les tâches de contrôle de la validité des données qui sont insérées.

Les contraintes se substituent aux contrôles réalisés par programmes. Il existe plusieurs types de contrôles. Il est possible d’indiquer au SGBDR : quelle valeur par défaut à affecter à une colonne (DEFAULT), qu’une colonne ne peut pas être null (NOT NULL), qu’une colonne doit être unique (UNIQUE), ou coder un contrôle sur une colonne (CHECK).

PRIMARY KEY


CREATE TABLE TELEPHONE (NUMERO INTEGER PRIMARY KEY,

TYPE VARCHAR(2), MARQUE INTEGER, DATE_ACHAT DATE, PRIX DECIMAL(9,2), NUM_PROPRIETAIRE INTEGER, COULEUR VARCHAR(25));

Exemple : déclaration de PRIMARY KEY sur une colonne

Automatiquement la colonne numéro sera NOT NULL et UNIQUE.


S’il y a plusieurs colonnes qui composent la clé, il faudra utiliser la clause CONSTRAINT qui permet de déclarer une contrainte d’intégrité.

CREATE TABLE TELEPHONE (NUMERO INTEGER ,

TYPE VARCHAR(2), MARQUE INTEGER, DATE_ACHAT DATE, PRIX DECIMAL(9,2), NUM_PROPRIETAIRE INTEGER, COULEUR VARCHAR(25), CONSTRAINT PK_TELEPHONE PRIMARY KEY (NUMERO,TYPE,MARQUE));


La FOREIGN KEY

Dans l’exemple, il faut tout d’abord créer une clé primaire sur la table TYPE_TEL et ensuite créer la clé étrangère dans la table TELEPHONE.


ALTER TABLE TYPE_TEL ADD CONSTRAINT PK_TELEPHONE PRIMARY KEY (TYPE); CREATE TABLE TELEPHONE (NUMERO INTEGER , TYPE VARCHAR(2), MARQUE INTEGER, DATE_ACHAT DATE, PRIX DECIMAL(9,2), NUM_PROPRIETAIRE INTEGER, COULEUR VARCHAR(25), CONSTRAINT FK_TYPE FOREIGN KEY (TYPE) REFERENCES TYPE_TEL );

La FOREIGN KEY


La clause « UNIQUE »

Comme indiqué dans la section qui traite des PRIMARY KEY, la clause UNIQUE permet de préciser au SGBDR que la valeur de cette colonne ne doit pas être en doublon dans la table.

CREATE TABLE TELEPHONE (NUMERO INTEGER PRIMARY KEY,

TYPE VARCHAR(2) DEFAULT ’AU’, MARQUE INTEGER, DATE_ACHAT DATE DEFAULT CURRENT_DATE, PRIX DECIMAL(9,2) DEFAULT 0, NUM_PROPRIETAIRE INTEGER UNIQUE, COULEUR VARCHAR(25));


CHECK

Attention : la clause CHECK est implémentée dans MySQL mais n’a aucun effet

Cette clause permet de réaliser toutes sortes de contrôles sur les colonnes d’une table. Il faut l’utiliser avec précaution car elle peut être source de problème de performances significatives. En effet, à chaque modification de la colonne, l’ordre est exécuté et peut ralentir sensiblement les mises à jour. CREATE TABLE TELEPHONE (

NUMERO INTEGER PRIMARY KEY, TYPE VARCHAR(2) CHECK (VALUE IN (SELECT TYPE FROM TYPE_TEL)),

MARQUE INTEGER CHECK (VALUE BETWEEN 1 AND 99), DATE_ACHAT DATE, PRIX DECIMAL(9,2) CHECK (VALUE > 0), NUM_PROPRIETAIRE INTEGER,

COULEUR VARCHAR(25));


Exercices d’application

Premier exercice

À partir du contenu de ce tableau, écrire la syntaxe de création de la table FILM. Mettre un index primaire sur la colonnes IDENT_FILM puis un index non unique sur les colonnes GENRE1 associé à PAYS.


CREATE TABLE FILM (IDENT_FILM INTEGER PRIMARY KEY,TITRE VARCHAR(50),GENRE1 VARCHAR(20),RECETTE DECIMAL(15,2),DATE_SORTIE DATE,PAYS SMALLINT,NB_ENTREE INTEGER,RESUME VARCHAR(2000),DATE_SAISIE TIMESTAMP

);

Requête de création d'index

CREATE INDEX I2_GENREPAYS ON FILM (GENRE1, PAYS);


Deuxième exercice

Ajouter une colonne complémentaire nommée NUM_REAL. Cette colonne est une clé externe sur la table REALISATEUR. Ajouter une valeur par défaut sur la colonne RECETTE avec la valeur 0. Mettre les colonnes TITRE et PAYS en NOT NULL. Supprimer la contrainte.


Requête d'ajout de colonne.ALTER TABLE FILM ADD (NUM_REAL INTEGER);

ajout d'une clé étrangère :

–- Création de la table RéalisateurDROP TABLE REALISATEUR;CREATE TABLE REALISATEUR(NUM_REAL INTEGER PRIMARY KEY,NOM VARCHAR(50));

–- AJOUT DE LA CONTRAINTE D INTEGRITEALTER TABLE FILM ADD CONSTRAINT FK_REALISATEUR FOREIGN KEY (NUM_REAL) REFERENCES REALISATEUR;


Troisième exercice

Créer une vue FILM2 à partir de la table FILM contenant les 4 premières colonnes de la table FILM ainsi que la colonne RESUME. Supprimer cette vue.


CREATE VIEW FILM2 AS SELECT IDENT_FILM, TITRE, GENRE1, RESUME FROM FILM;

Supprimer cette vue.

DROP VIEW FILM2;

Mediaexport 7: LDD








Documents

La création de tables LE LANGAGE SQL : LDD Lordre CREATE CREATE TABLE nom_de_table (Nom_colonne Type_colonne, Nom_colonne Type_colonne,