La création de tables LE LANGAGE SQL : LDD Lordre CREATE CREATE TABLE nom_de_table (Nom_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, ... );

LE LANGAGE SQL : LDD

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)

LE LANGAGE SQL : LDD

Exemple :

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

LE LANGAGE SQL : LDD

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));

LE LANGAGE SQL : LDD

La suppression de tables

 L’ordre DROP

DROP TABLE nom_de_table; 

syntaxe

DROP TABLE TELEPHONE; 

LE LANGAGE SQL : LDD

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; 

LE LANGAGE SQL : LDD

 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; 

LE LANGAGE SQL : LDD

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

LE LANGAGE SQL : LDD

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); 

LE LANGAGE SQL : LDD

 La suppression d’index

DROP INDEX <nom_index> 

DROP INDEX I2_TYPE;  DROP INDEX I4_COULEUR; 

LE LANGAGE SQL : LDD

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

LE LANGAGE SQL : LDD

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. 

LE LANGAGE SQL : LDD

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)); 

LE LANGAGE SQL : LDD

 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. 

LE LANGAGE SQL : LDD

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

LE LANGAGE SQL : LDD

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)); 

LE LANGAGE SQL : LDD

 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));

LE LANGAGE SQL : LDD

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. 

LE LANGAGE SQL : LDD

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);

LE LANGAGE SQL : LDD

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. 

LE LANGAGE SQL : LDD

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;

LE LANGAGE SQL : LDD

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. 

LE LANGAGE SQL : LDD

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

Supprimer cette vue.

DROP VIEW FILM2;

Mediaexport 7: LDD

LE LANGAGE SQL : LDD

LE LANGAGE SQL : LDD

LE LANGAGE SQL : LDD

LE LANGAGE SQL : LDD

LE LANGAGE SQL : LDD

LE LANGAGE SQL : LDD

LE LANGAGE SQL : LDD