La lecture en ligne est gratuite
Le téléchargement nécessite un accès à la bibliothèque YouScribe
Tout savoir sur nos offres
Télécharger Lire

MySQL Cours TP 05

De
23 pages
INSIA Bases de données ING 1 MySQL – Cours et TP 05 Création des tables et des tuples LA BIBLE : MySQL 5.0 Reference Manual http://dev.mysql.com/doc/refman/5.0/fr/index.html Site officiel MySql : http://www-fr.mysql.com/ Documentation MySQL : http://mysql.org/ La Base de Données Open Source la plus Populaire au Monde Bertrand LIAUDET SOMMAIRE SOMMAIRE 1 SQL : CREATION DE LA BASE DE DONNEES. LES TABLES : LE DDL 3 0. Présentation 3 1. Création des tables 3 2 Contrainte d’intégrité référentielle : CIR 8 3 Modification des tables 12 4 Suppression des tables 13 5. Récupération du code sous mysql : show create table 14 6. Moteur MyISAM et moteur InnoDB 15 SQL : CREATION DE LA BASE DE DONNEES. LES TUPLES : LE DML 17 1. Création des tuples 17 2. Modification et suppression des tuples 18 3. Suppression des tuples 19 4. Remplacement des tuples 19 TP N°5 : CMS DES TUPLES ET DES TABLES 21 INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 1/23 - Bertrand LIAUDET Présentation 21 Exercice 1 : charger les tables de la base de données 21 Exercice 2 : modification et création de la BD 22 Exercice 3 : création de la BD via un SELECT et des ALTER 23 Exercice 4 : passage de InnoDB à MyISAM et retour en InnoDB 23 Première édition : septembre 2007 Deuxième édition : septembre 2008 INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 2/23 - Bertrand LIAUDET SQL : CREATION DE LA BASE DE DONNEES. LES TABLES : LE DDL ...
Voir plus Voir moins


INSIA
Bases de données
ING 1
MySQL – Cours et TP 05

Création des tables et des tuples

LA BIBLE : MySQL 5.0 Reference Manual
http://dev.mysql.com/doc/refman/5.0/fr/index.html

Site officiel MySql : http://www-fr.mysql.com/
Documentation MySQL : http://mysql.org/
La Base de Données Open Source la plus Populaire au Monde
Bertrand LIAUDET


SOMMAIRE
SOMMAIRE 1
SQL : CREATION DE LA BASE DE DONNEES. LES TABLES : LE DDL 3
0. Présentation 3
1. Création des tables 3
2 Contrainte d’intégrité référentielle : CIR 8
3 Modification des tables 12
4 Suppression des tables 13
5. Récupération du code sous mysql : show create table 14
6. Moteur MyISAM et moteur InnoDB 15
SQL : CREATION DE LA BASE DE DONNEES. LES TUPLES : LE DML 17
1. Création des tuples 17
2. Modification et suppression des tuples 18
3. Suppression des tuples 19
4. Remplacement des tuples 19
TP N°5 : CMS DES TUPLES ET DES TABLES 21
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 1/23 - Bertrand LIAUDET Présentation 21
Exercice 1 : charger les tables de la base de données 21
Exercice 2 : modification et création de la BD 22
Exercice 3 : création de la BD via un SELECT et des ALTER 23
Exercice 4 : passage de InnoDB à MyISAM et retour en InnoDB 23

Première édition : septembre 2007
Deuxième édition : septembre 2008

INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 2/23 - Bertrand LIAUDET SQL : CREATION DE LA BASE DE DONNEES.
LES TABLES : LE DDL
PRINCIPALES NOTIONS
CREATE TABLE INSERT INTO
ALTER TABLE UPDATE
DROP TABLE DELETE FROM
Contraintes d’intégrité non référentielles Moteur MyISAM
Contraintes d’intégrité référentielle Moteur InnoDB
Show create table
0. Présentation
Les trois opérations fondamentales de « création » au sens large de la base de données sont :
la création, la modification et la suppression
Ces opérations correspondent au sigle : CMS
La CMS s’applique aux tables, aux tuples mais aussi aux utilisateurs.
CMS des tables : Le DDL : Data Definition Language
CREATE TABLE
ALTER TABLE
DROP TABLE
CMS des tuples : Le DML : Data Manipulation Language
INSERT INTO
UPDATE
DELETE FROM
REPLACE
1. Création des tables

Une fois le modèle des données réalisé, il reste à créer la base de données. Créer d’abord les
tables, puis les tuples.
Création des tables
http://dev.mysql.com/doc/refman/5.0/fr/create-table.html
Syntaxe MySQL
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 3/23 - Bertrand LIAUDET En première approche, la commande a la syntaxe suivante :

CREATE table NomTable (
attribut_1 type [contrainte d’intégrité],
attribut_2 type [contrainte d’intégrité],
… ,
attribut_n type [contrainte d’intégrité] ,
[contrainte d’intégrité]
);
Les contraintes sont facultatives.
L’ordre dans la liste est au choix.
La table est une coquille vide : c'est une structure. Elle permettra ensuite de créer des tuples.
Exemple

CREATE TABLE DEPT (
ND integer primary key auto_increment,
NOM varchar(14) not NULL,
VILLE varchar(13)
);

CREATE TABLE EMP (
NE integer primary key auto_increment,
NOM varchar(10) not NULL,
JOB enum ('PRESIDENT','MANAGER', 'SALESMAN', 'CLERK', 'ANALYST'),
DATEMB date,
SAL float(7,2),
COMM float(7,2),
ND integer not null, foreign key(ND) references DEPT(ND),
NEchef integer , foreign key(NEchef) references EMP(NE)
);


Les types
http://dev.mysql.com/doc/refman/5.0/fr/column-types.html

Voici quelques types de données de mysql :

char(n) : chaîne de caractères de longueur fixe (1<=n<=255).
varchar(n) : chaîne de caractère de longueur variable (A<n<2000).
integer: entier (ou int)
float(nb chiffre max, nb chiffre après la virgule) : nombre fixe ou flottant
date : date
time : heure
datetime : date et heure
year : année
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 4/23 - Bertrand LIAUDET enum : permet de proposer une liste de valeurs pour l’attribut. Remarque : les valeurs
proposées seront les seules possibles. L’enum se comporte donc comme un « check ».

La connaissance des types est spécifique au SGBD sur lequel on travaille.

Autres types MySQL :
tinyint, smallint, mediumint, bigint, double precision, numeric, decimal, blob, text, tinyblob,
tinytext, mediumblob, mediumtext, longtext, longblob, set.
Les contraintes d’intégrité
Liste des contraintes d’intégrité
PRIMARY KEY : permet de définir les clés primaires. Cette contrainte garantit le fait que
la valeur est différente de NULL et qu’elle est unique dans la table.
FOREIGN KEY : permet de définir les clés étrangères. Cette contrainte garantit que la
valeur fait bien référence à une clé primaire existant dans une autre table.
NOT NULL : impose le fait que la valeur de l’attribut doit être renseignée.
UNIQUE : impose le fait que chaque tuple de la table doit, pour l’attribut concerné, avoir
une valeur différente de celle des autres ou NULL.
DEFAULT : permet de définir une valeur par défaut.
ENUM : permet de définir un ensemble de valeurs possible pour l’attribut. Cette contrainte
garantit le fait que la valeur de l’attribut appartiendra à cet ensemble. Le SQL standard
propose le mot-clé CHECK qui permet de gérer plus de vérifications sur les valeurs
possibles de pour l’attribut.
Conséquences des contraintes d’intégrité
PRIMARY KEY, NOT NULL, UNIQUE et CHECK : ces quatre contraintes ont le
même type de conséquence : si on cherche à donner une valeur à un attribut qui n’est pas
conforme à ce qui est précisé dans la définition de l’attribut (valeur NULL s’il est défini
NOT NULL ou PRIMARY KEY, valeur existant déjà s’il est défini UNIQUE ou
PRIMARY KEY, valeur n’appartenant pas au domaine spécifié par le CHECK), alors le
SGBD renvoie un message d’erreur et ne modifie pas la base de données. Ainsi, un premier
niveau de cohérence des données est maintenu.
DEFAULT : donne une valeur par défaut si il n’y a pas de saisie.
FOREIGN KEY : cette contrainte garantit que la valeur fait bien référence à une clé
primaire existant dans une autre table. Les conséquences pratiques de cette contraintes
seront abordées au prochain chapitre.
Exemples de code
Exemple 1

-- Création de la BD : on supprime la database, on la recrée, on l'utilise

drop database if exists empdept;
create database empdept;
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 5/23 - Bertrand LIAUDET
use empdept;

-- Création des tables

CREATE TABLE DEPT (
ND integer primary key auto_increment,
NOM varchar(14) not NULL,
VILLE varchar(13)
) ENGINE InnoDB;

CREATE TABLE EMP (
NE integer primary key auto_increment,
NOM varchar(10) not NULL,
JOB varchar(9),
DATEMB date,
SAL float(7,2),
COMM float(7,2),
ND integer not null, foreign key(ND) references DEPT(ND),
NEchef integer , foreign key(NEchef) references EMP(NE)
) ENGINE InnoDB;

-- création des tuples

INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON')
;

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT','1981-11-17',5000,NULL,10,NULL);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER','1981-05-1',2850,NULL,30,7839);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER','1981-06-9',2450,NULL,10,;
INSERT INTO EMP VALUES (7566,'JONES','MANAGER','1981-04-2',2975,NULL,20,7839);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN','1981-09-28',1250,1400,30,7698);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN','1981-02-20',1600,300,30,7698);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN','1981-09-8',1500,0,30,7698);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK','1981-12-3',950,NULL,30,7698);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN','1981-02-22',1250,500,30,7698);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST','1981-12-3',3000,NULL,20,7566);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK','1980-12-17',800,NULL,20,7902);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST','1982-12-09',3000,NULL,20,7566);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK','1983-01-12',1100,NULL,20,7788);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK','1982-01-23',1300,NULL,10,7782);

Remarques
Insert into : on peut faire un insert into par tuple (c’est le cas des employés) ou faire un insert
into pour tous les tuples (c’est le cas des départements).
Variante de l’exemple 1

CREATE TABLE EMP (
NE integer auto_increment,
NOM varchar(10),
JOB enum ('PRESIDENT','MANAGER', 'SALESMAN', 'CLERK', 'ANALYST'),
DATEMB date,
SAL float(7,2) check (sal >1000), -- mysql ne gère pas le check !
COMM float(7,2) default 100,
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 6/23 - Bertrand LIAUDET
ND integer not null,
NEchef integer,
primary key(NE)
) ENGINE InnoDB;

CREATE TABLE DEPT (
ND integer auto_increment,
NOM varchar(14),
VILLE varchar(13),
primary key(ND)
) ENGINE InnoDB;

ALTER TABLE EMP ADD constraint KEYND foreign key(ND) references DEPT(ND);
ALTER TABLE EMP ADD constraint KEYNECHEF foreign key(NEchef) references EMP(NE);

Remarques
JOB enum
Check : pas géré par MySQL
Defautl : Comm default 100
Les foreign key gérées en altération. De ce fait, on peut créer les tables dans n’importe quel
ordre.
Create table as select
On peut créer une table et la remplir à partir d’un select :

Create table nomTable as select … ;

Cette table contiendra les attributs et les tuples du select.
Elle ne contiendra aucune contraintes d’intégrité. Tous les attributs sont à NULL par défaut.
Pour y ajouter des contraintes d’intégrité, il faudra faire des ALTER TABLE.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 7/23 - Bertrand LIAUDET
2 Contrainte d’intégrité référentielle : CIR
Notion de contrainte d’intégrité référentielle
FOREIGN KEY est une caractéristique pour un attribut qui définit une contrainte d’intégrité
référentielle.
Une CIR garantit que la valeur de l’attribut fait bien référence à une clé primaire existant dans
une autre table.

Une CIR concerne deux attributs (et donc en général deux tables) :
• Une CIR concerne un attribut maître (attribut qui porte la CIF : la clé étrangère)
• Une CIR concerne un attribut joint (attribut auquel la CIF fait référence : la clé primaire).

La présence de CIR à des conséquences :
• Sur la création, modification, suppression des tables.
• Sur la création et la modification de tuples propriétaires d’une CIF.
• Sur la modification et la suppression de tuples référés par une CIF.
Conséquences de l’intégrité référentielle sur les tables
Création et modification d’une table maître (avec une clé étrangère)
La table jointe doit être créée avant la table maître.
Exemple : il faut créer la table des départements avant celle des employés car la table des
employés fait référence à la table des départements.
Suppression d’une table jointe (dont la clé primaire est référencée par d’autres tables)
La table maître doit être supprimée avant la table jointe.
Exemple : il faut supprimer la table des employés avant celle des départements car la table
aucune table ne fait référence à la table des employés tandis que la table des départements est
référencée par la table des employés.
Ajout et suppression des CIR indépendamment des tables
Pour pouvoir créer les tables dans n’importe quel ordre (par ordre alphabétique par exemple), il
suffit de créer les CIR après avoir créer les tables (avec un ALTER TABLE).
Pour pouvoir supprimer les tables dans n’importe quel ordre, il suffit de supprimer les CIR avant
de supprimer les tables (avec un ALTER TABLE).
Conséquences de l’intégrité référentielle sur les tuples
Création et modification d’un tuple maître (avec une clé étrangère)
Le tuple joint doit être créé avant le tuple maître.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 8/23 - Bertrand LIAUDET Suppression d’un tuple joint (dont la clé primaire est référencée par d’autres tuples)
Un tuple joint ne peut pas être supprimé.
En cas de tentative d’une telle suppression, 3 cas peuvent se présenter :
• Soit on interdit la suppression du tuple joint. Il faudra alors commencer par détruire le ou
les tuples maîtres correspondant pour pouvoir supprimer le tuple joint. C’est la situation
par défaut.
• Soit le système supprime le tuple joint et les tuples maître correspondant : ainsi il n’y a plus
de tuples maître faisant référence au tuple joint.
Dans ce cas on ajoute : ON DELETE CASCADE à la définition de la clé étrangère.

Exemple :
ND integer not null, foreign key(ND) references DEPT(ND) on delete cascade
Dans notre exemple, cela signifie que si on supprime un département, on supprimera aussi
tous les employés du départements… ce qui n’est certainement pas un bon choix de
modélisation !

• Soit le système supprime le tuple joint et met la clé étrangère correspondante des tuples
maîtres à NULL pour qu’il n’y ait plus de tuples maîtres qui fassent référence au tuple
joint.
Dans ce cas on ajoute : ON DELETE SET NULL à la définition de la clé étrangère.

Exemple :
ND integer not null, foreign key(ND) references DEPT(ND) on delete set NULL
Dans notre exemple, cela signifie que si on supprime un département, les employés du
département auront désormais la valeur NULL comme numéro de département. C’est
possible à condition que le numéro de département de l’employé ne soit pas déclaré NOT
NULL.
Modification d’un tuple joint (dont la clé primaire est référencée par d’autres tuples)
Un tuple joint ne peut pas être modifié.
En cas de tentative d’une telle suppression, 2 cas peuvent se présenter :
• Soit on interdit la modification du tuple joint. Il faudra commencer par modifier le ou les
tuples maîtres correspondant pour pouvoir modifier le tuple joint. C’est la situation par
défaut.
• Soit le système modifie le tuple joint et les tuples maîtres correspondants pour qu’ils
fassent correctement référence au tuple joint.
Dans ce cas on ajoute : ON UPDATE CASCADE à la définition de la clé étrangère.

Exemple:
ND integer not null, foreign key(ND) references DEPT(ND) on update cascade
Dans notre exemple, cela signifie que si on modifie la clé primaire d’un département, on
modifiera aussi les numéros de départements des employés de ce département.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 9/23 - Bertrand LIAUDET Nommer les contraintes : CONSTRAINT nomContrainte
En SQL standard, on peut nommer toutesles contraintes, ce qui permettra ensuite de désactiver
et de réactiver les contraintes en y faisant références par leur nom.
Pour cela, il suffit d’ajouter « CONSTRAINT nomContrainte » devant la déclaration de la
contrainte.
MySQL ne gère que le nom des CIR.
Syntaxe SQL
Création de CIR dans un CREATE TABLE
Syntaxe générale
FOREIGN KEY(nomAttMaître) [CONSTRAINT nomContrainte] REFERENCES
nomTableJointe(nomAttJoint)
Exemples
CREATE TABLE EMP (
NE integer primary key auto_increment,

ND integer not null, foreign key(ND) references DEPT(ND),
NEchef integer , foreign key(NEchef) references EMP(NE)
);
Ou encore :
CREATE TABLE EMP (
NE integer primary key auto_increment,

ND integer not null,
NEchef integer ,
constraint KEYND foreign key(ND) references DEPT(ND),
foreign key(NEchef) references EMP(NE)
);
Nom d’une CIR : show create table
mysql> show create table emp;
| emp | CREATE TABLE `emp` (
`NE` int(11) NOT NULL auto_increment,

CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`ND`) REFERENCES `dept` (`ND`),
CONSTRAINT `emp_ibfk_2` FOREIGN KEY (`NEchef`) REFERENCES `emp` (`NE`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=latin1 |
La CIR sur ND est nommée automatiquement : `emp_ibfk_1`.
La CIR sur NEchef est nommée automatiquement : `emp_ibfk_2`
Création de CIR par un ALTER TABLE
CREATE TABLE EMP (
NE integer auto_increment,

NEchef integer,
primary key(NE)
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 05 - page 10/23 - Bertrand LIAUDET

Un pour Un
Permettre à tous d'accéder à la lecture
Pour chaque accès à la bibliothèque, YouScribe donne un accès à une personne dans le besoin