# MYSQL : Pokémons DB

## Créer la base de données

// Créer la base de donnée _db_introprog_pokemons_

CREATE DATABASE db_introprog_pokemons;

## Créer les tables

// Créer la table avec les types de Pokémons, _pk_types_

```mysql
CREATE TABLE `db_introprog_pokemons`.`pk_types` ( `Id` INT NOT NULL AUTO_INCREMENT , `Type` VARCHAR(30) NOT NULL , PRIMARY KEY (`Id`)) ENGINE = InnoDB;
```

INSERT INTO `pk_types` (`Id`, `Type`) VALUES (NULL, 'Acier');

```mysql
INSERT INTO `pk_types` (`Id`, `Type`) VALUES (NULL, 'Acier'), (NULL, 'Combat'), (NULL, 'Dragon'), (NULL, 'Eau'), (NULL, 'Electrik'), (NULL, 'Fée'), (NULL, 'Feu'), (NULL, 'Glace'), (NULL, 'Insecte'), (NULL, 'Normal'), (NULL, 'Plante'), (NULL, 'Poison'), (NULL, 'Psy'), (NULL, 'Roche'), (NULL, 'Sol'), (NULL, 'Spectre'), (NULL, 'Ténèbres'), (NULL, 'Vol');
```

// Créer la table des Pokémons, _pk_list_

```mysql
CREATE TABLE `db_introprog_pokemons`.`pk_list` ( `Id` INT NOT NULL AUTO_INCREMENT , `Name` VARCHAR(30) NOT NULL , `Pv` INT NOT NULL , `Image` VARCHAR(30) NOT NULL , `Type1` INT NOT NULL , `Type2` INT NULL , `Editable` BOOLEAN NOT NULL , PRIMARY KEY (`Id`), INDEX `Type` (`Type1`, `Type2`)) ENGINE = InnoDB;
```

// Lier le champ type de la table `db_introprog_pokemons` au champ type de la table `pk_types`

```mysql
ALTER TABLE `pk_list` ADD FOREIGN KEY (`Type1`) REFERENCES `pk_types`(`Id`), ADD FOREIGN KEY (`Type2`) REFERENCES `pk_types`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
```

// Insérér un Pokémon

```mysql
INSERT INTO `pk_list` (`Id`, `Name`, `Pv`, `Image`, `Type1`, `Type2`, `Editable`) VALUES (NULL, 'Pikachu', '35', 'pikachu.png', '5', NULL, '0');
```

// Update d'un Pokémon, par exemple le nom de l'image de Pikachu

```mysql
UPDATE `pk_list` SET `Image` = 'Pikachu.png' WHERE `pk_list`.`Id` = 3;
```

// Effacer un Pokémon (par exemple sur le nom)

```mysql
DELETE FROM `pk_list`
WHERE Name = 'Pikachu'
```

// Effacer tous les Pokémons

```mysql
DELETE FROM `pk_list`;
```

// Résinsérer tous les pokémons

```mysql
INSERT INTO `pk_list` (`Id`, `Name`, `Pv`, `Image`, `Type1`, `Type2`, `Editable`) VALUES
(NULL, 'Bulbizarre', '45', 'Bulbizarre.png', '11', '12', '0'),
(NULL, 'Herbizarre', '60', 'Herbizarre.png', '11', '12', '0'),
(NULL, 'Florizarre', '80', 'Florizarre.png', '11', '12', '0'),
(NULL, 'Salamèche', '39', 'Salamèche.png', '7', NULL, '0'),
(NULL, 'Reptincel', '58', 'Reptincel.png', '7', NULL, '0'),
(NULL, 'Dracaufeu', '78', 'Dracaufeu.png', '7', '18', '0'),
(NULL, 'Carapuce', '44', 'Carapuce.png', '4', NULL, '0'),
(NULL, 'Carabaffe', '59', 'Carabaffe.png', '4', NULL, '0'),
(NULL, 'Tortank', '79', 'Tortank.png', '4', NULL, '0'),
(NULL, 'Chenipan', '45', 'Chenipan.png', '9', NULL, '0'),
(NULL, 'Chrysacier', '50', 'Chrysacier.png', '9', NULL, '0'),
(NULL, 'Papilusion', '90', 'Papilusion.png', '9', '18', '0'),
(NULL, 'Aspicot', '40', 'Aspicot.png', '9', '12', '0'),
(NULL, 'Coconfort', '45', 'Coconfort.png', '9', '12', '0'),
(NULL, 'Dardargnan', '65', 'Dardargnan.png', '9', '12', '0'),
(NULL, 'Roucool', '40', 'Roucool.png', '10', '18', '0'),
(NULL, 'Roucoups', '63', 'Roucoups.png', '10', '18', '0'),
(NULL, 'Roucarnage', '83', 'Roucarnage.png', '10', '18', '0'),
(NULL, 'Rattata', '30', 'Rattata.png', '10', NULL, '0'),
(NULL, 'Rattatac', '55', 'Rattatac.png', '10', NULL, '0'),
(NULL, 'Piafabec', '40', 'Piafabec.png', '10', '18', '0'),
(NULL, 'Rapasdepic', '63', 'Rapasdepic.png', '10', '18', '0'),
(NULL, 'Abo', '35', 'Abo.png', '12', NULL, '0'),
(NULL, 'Arbok', '60', 'Arbok.png', '12', NULL, '0'),
(NULL, 'Pikachu', '35', 'Pikachu.png', '5', NULL, '1');
```

## Pokedex

https://eternia.fr/fr/pokedex/liste-pokemon/