qahV17Fm

1PART.1
2
31) 
4CREATE TABLE IF NOT EXISTS classes (
5classes_id INT primary key,
6classes_mask INT NULL,
7classes_name VARCHAR(20) NULL,
8classes_power_type VARCHAR(11) NULL,
9races_id INT NULL
10)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
11INSERT INTO classes VALUES
12(1,1,"Guerrier","rage",1),
13(2,2,"Paladin","mana",1),
14(3,4,"Chasseur","focus",5),
15(4,8,"Voleur","energy",6),
16(5,16,"Prêtre","mana",8),
17(7,64,"Chaman","mana",3),
18(8,128,"Mage","mana",4),
19(9,256,"Démoniste","mana",5),
20(10,512,"Moine","energy",2),
21(11,1024,"Druide","mana",9);
22
232)
24CREATE TABLE IF NOT EXISTS races (
25races_id INT primary key,
26races_mask INT NULL,
27races_name VARCHAR(20) NULL,
28races_side VARCHAR(8) NULL
29)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
30INSERT INTO races VALUES
31(1,1,"Humain","alliance"),
32(2,2,"Orc","horde"),
33(3,4,"Nain","alliance"),
34(4,8,"Elfe de la nuit","alliance"),
35(5,16,"Mort-vivant","horde"),
36(6,32,"Tauren","horde"),
37(7,64,"Gnome","alliance"),
38(8,128,"Troll","horde"),
39(9,256,"Gobelin","horde"),
40(10,512,"Elfe de sang","horde");
41
423)
43ALTER TABLE classes DROP classes_mask;
44ALTER TABLE races DROP races_mask;
45
464)
47CREATE TABLE personnage(
48personnage_id INT primary key,
49personnage_name VARCHAR(20),
50personnage_date DATE,
51personnage_level INT,
52classes_id INT
53)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
54
555)
56Personnage cree sur Adminer:
57personnage_id = 1
58personnage_name = Daril
59peronnage_date = 2019-02-20
60personnage_level = 60
61classes_id = 1
62
636)
64DELETE FROM races WHERE races_name = "Humain";
65Si on supprime la race "humain", cela va engendrer une erreur invisible dans les tables qui risque d influencer les autres races.
66
67
687)
69ALTER TABLE personnage ADD CONSTRAINT toto FOREIGN KEY(classes_id) REFERENCES classes(classes_id);
70
718)
72INSERT INTO races VALUES
73(1,"Humain","alliance");
74
759)
76DELETE FROM races WHERE races_name = "Gobelin";
77
78PART.2
79
801)
81J'ajoute les personnages Saperlipopette, Zigomar, Sperpinette
82
83INSERT INTO personnage (personnage_id, personnage_name, personnage_date, personnage_level, classes_id)
84VALUES (6, "Saperlipopette", "2018-09-18", 50, 5);
85
86
87INSERT INTO personnage (personnage_id, personnage_name, personnage_date, personnage_level, classes_id)
88VALUES (8, "Zigomar", "2018-09-18", 10, 10);
89
90
91INSERT INTO personnage (personnage_id, personnage_name, personnage_date, personnage_level, classes_id)
92VALUES (11, "Sperpinette", "2018-09-18", 105, 5);
93
94Quand on ajoute un prêtre, sa race doit correspondre à un troll or ici Saperlipopette est un Prêtre de la race Tauren ce qui n'est pas possible, la classe prêtre es 
95
962)
97Personnage(personnage_id, personnage_name, personnage_date, personnage_level, races_id) 
98 
99CP: personnage_id
100CE: races_id
101
1023)
103je supprime races_id à la table classe
104ALTER TABLE classes DROP races_id;
105
106j'ajoute la colonne races_id à la table personnage :
107
108ALTER TABLE personnage ADD (races_id int);
109
1104)
111ALTER TABLE personnage ADD CONSTRAINT RP FOREIGN KEY(races_id) REFERENCES races(races_id);
112
113PART 3
114
1151)
116CREATE TABLE montures (mounts_creature_id, mounts_icon, mounts_item_id, mounts_name, mounts_quality_id, mounts_spell_id)
117Clé primaire : mounts_creature_id
118
1192)
120Dans le script la clé 305 existe 2 fois, je suprrime donc la 2ème clé 305 du script:
121(305,	'PIGY',	12353,	'SUPPRIMER',	4,	16083),
122
123
1241)
125ALTER TABLE personnage ADD (mounts_creature_id int);
126
127ALTER TABLE personnage ADD CONSTRAINT mt FOREIGN KEY(mounts_creature_id) REFERENCES mounts(mounts_creature_id);
128
1292)
130UPDATE `personnage` SET
131`personnage_id` = '4',
132`personnage_name` = 'Zakyku',
133`personnage_date` = '2018-10-18',
134`personnage_level` = '115',
135`classes_id` = '11',
136`races_id` = '9',
137`mounts_creature_id` = '29046'
138WHERE `personnage_id` = '4';
139
1403)
141DELETE FROM mounts WHERE mounts_creature_id = "29046";
142Erreur dans la requête (1451): Cannot delete or update a parent row: a foreign key constraint fails (`hanquj`.`personnage`, CONSTRAINT `mt` FOREIGN KEY (`mounts_creature_id`) REFERENCES `mounts` (`mounts_creature_id`))
143On ne peux pas supprimer cette monture car elle est relié avec une clé primaire ce qui vérifie la contrainte.
144
145
146PART 4
147
1481)
149mounts_user (id, personnage_id, mounts_creature_id)
150cle primaire: id
151clé étrangere: personnage_id en reference a personnage(personnage_id)
152clé étrangere mounts_creature_id en reference a mounts(mounts_creature_id)
153
1542)
155CREATE TABLE mounts_user (
156id INT primary key,
157mounts_creature_id INT,
158FOREIGN KEY (personnage_id) REFERENCES personnage(personnage_id),
159FOREIGN KEY (mounts_creature_id) REFERENCES mounts(mounts_creature_id)
160)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
161
1623)
163ALTER TABLE personnage DROP mounts_creature_id
164
1654)
166INSERT INTO mounts_user(id, personnage_id, mounts_creature_id) VALUES
167 (1, 1, 27541),
168 (2, 2, 15711),
169 (3, 3, -61804);
170 
1715)
172INSERT INTO mounts_user(id, personnage_id, mounts_creature_id) VALUES 
173 (4, 5, -74104),
174 (5, 5, -74135),
175 (6, 7, -76311),
176 (7, 7, -76424);