kgJbCemf

1DROP DATABASE IF EXISTS ecole;
2CREATE DATABASE IF NOT EXISTS ecole DEFAULT CHARACTER SET UTF8;
3use ecole;
4
5CREATE TABLE IF NOT EXISTS classe(
6id_classe		int(10) 	UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
7classe			SET('A','B','C')	NOT NULL,
8maitre_classe	int(10)		UNSIGNED NOT NULL
9) ENGINE=INNODB DEFAULT CHARACTER SET UTF8;
10
11
12CREATE TABLE IF NOT EXISTS eleve(
13id_eleve		int(10)		UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
14prenom			varchar(30)	NOT NULL,
15nom				varchar(30) NOT NULL,
16date_naissance	date		NOT NULL,
17id_classe		int(10)		UNSIGNED NOT NULL,
18FOREIGN KEY (id_classe) REFERENCES classe (id_classe)
19						ON UPDATE CASCADE
20						ON DELETE RESTRICT
21) ENGINE=INNODB DEFAULT CHARACTER SET UTF8;
22
23
24INSERT INTO classe
25(classe, maitre_classe) VALUES 	("A",1),
26								("B",2);
27
28INSERT INTO eleve
29(prenom,nom,date_naissance,id_classe) VALUES 	("Mathieu","Mesquita","1999-11-02",1),
30												("Didier","Deschamps","2000-11-22",2);