A8QqdLjK

1
2
3DROP DATABASE IF EXISTS data_leroy;
4CREATE DATABASE data_leroy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
5USE data_leroy;
6
7CREATE USER 'johnsnow'@'localhost' IDENTIFIED BY '123';
8GRANT ALL PRIVILEGES ON * . * to 'johnsnow'@'localhost';
9FLUSH PRIVILEGES;
10
11
12
13 
14CREATE TABLE users
15(
16    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
17    mail VARCHAR(30) UNIQUE NOT NULL,
18    mdp VARCHAR(191) NOT NULL,
19    nom VARCHAR(20) UNIQUE NOT NULL,
20    prenom VARCHAR(20) UNIQUE NOT NULL,
21    adress VARCHAR(60),
22    code_postal VARCHAR(15),
23    ville VARCHAR(40),
24    telephone VARCHAR(20),
25    siege_le_plus_proche VARCHAR(20),
26    PRIMARY KEY (id)
27)
28ENGINE = INNODB;
29 
30
31 
32CREATE TABLE projets
33(
34    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
35    USER VARCHAR(30) UNIQUE NOT NULL,
36    nom_projet VARCHAR(20) UNIQUE NOT NULL,
37    desc_projet TEXT,
38    date_creation DATETIME NOT NULL,
39    date_fin DATETIME NOT NULL,
40    PRIMARY KEY (id)
41)
42ENGINE = INNODB;
43 
44
45 
46CREATE TABLE produc_data
47(
48    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
49    nom_produc VARCHAR(20) UNIQUE NOT NULL,
50    date_ajout DATETIME NOT NULL,
51    PRIMARY KEY (id)
52
53)
54ENGINE = INNODB;
55 
56
57 
58CREATE TABLE projets_to_product
59(
60    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
61    id_projet INT UNSIGNED NOT NULL,
62    id_product INT UNSIGNED NOT NULL,
63    longeur INT UNSIGNED NOT NULL,
64    largeur INT UNSIGNED NOT NULL,
65    epaisseur INT UNSIGNED NOT NULL,
66    prix_HT INT UNSIGNED NOT NULL,
67    prix_TVA INT UNSIGNED NOT NULL,
68    PRIMARY KEY (id)
69)
70ENGINE = INNODB;
71
72 
73CREATE TABLE data_door
74(
75    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
76    color VARCHAR(20) UNIQUE NOT NULL,
77    id_materials INT UNSIGNED NOT NULL,
78    longeur INT UNSIGNED NOT NULL,
79    largeur INT UNSIGNED NOT NULL,
80    PRIMARY KEY (id)
81)
82ENGINE = INNODB;
83 
84
85CREATE TABLE materials
86(
87    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
88    product_name VARCHAR(20) UNIQUE NOT NULL,
89    price_HT INT UNSIGNED NOT NULL,
90    price_TVA INT UNSIGNED NOT NULL,
91    PRIMARY KEY (id)
92)
93ENGINE = INNODB;