ReMise à niveau MySQL
Jérôme Le Naou
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3367806/MySQL.svg.png)
MySQL Bases
- SGBD
- MySQL
- Bases et tables
- Requêtes simples
- Requêtes CheatSheet
- Exercice
SGBD ?
SGBD
data 1
data 2
data 3
.....
Fichiers plats
MySQL / MariaDB
PostgreSQL
Oracle
MySQL = SGBDR
Serveur
Base
Table
mysql.serveur.com
Base MonBuilder
Joueurs
ModelesBatiments
Batiments
Quêtes
Bases
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3367967/Capture_d__cran_2016-12-31_18-35-24.png)
Tables
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3367961/Capture_d__cran_2016-12-31_18-30-41.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3367964/Capture_d__cran_2016-12-31_18-32-34.png)
Colonnes
Lignes
Requêtes simples
SELECT * FROM Batiments WHERE joueur_id=5
INSERT INTO Joueurs (Login,Email,Password) VALUES
("Jérôme", "j.lenaou@gmail.com", "gdp32016")
UPDATE Quetes SET Achevee="true" WHERE ID=5
DELETE FROM Unites
Requêtes CheatSheet 1
SELECT <champs> FROM <table>
[WHERE <condition>]
[ORDER BY <expressions> [ASC|DESC]]
[LIMIT lignes]
UPDATE <table>
SET <champ>=<expression> [,<champ2>=<exp2>, ...]
WHERE <condition>
/!\ Ne JAMAIS oublier le WHERE /!\
Requêtes CheatSheet 2
DELETE FROM <table>
WHERE <condition>
/!\ Ne JAMAIS oublier le WHERE /!\
Requêtes CheatSheet 3
INSERT INTO <table> (<colonnes>)
VALUES (<expressions>)
Requêtes CheatSheet 4
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368340/questions.gif)
Exercice
- Créer une base de donnée ISARTGdp
- Créer une table Joueurs avec 4 colonnes :
- ID (unsigned int)
- Login (string)
- Email (string)
- Genre (enum "h", "f")
- Password (string)
- Créer des requêtes pour insérer, mettre à jour, supprimer et sélectionner des données
- Pas de php, uniquement via l'interface PHPMyAdmin
Manipulations MySQL
- Fonctions numériques
- Fonctions sur les textes
- Fonctions temporelles
- Exercice
Fonctions numériques
SELECT Niveau + 1 FROM Joueurs WHERE ID=5
SELECT MIN(Niveau) as NiveauMin FROM JoueurLevels WHERE Experience > 1000
SELECT MAX(Niveau) as NiveauMax FROM JoueurLevels
Fonctions sur les textes
SELECT (Nom + ' ' + Prenom) as NomComplet FROM Utilisateurs
SELECT CONCAT(Nom, ' ', Prenom) as NomComplet FROM Utilisateurs
SELECT LENGTH(Prenom) FROM Utilisateurs
SELECT * FROM Utilisateurs WHERE Prenom LIKE 'John'
SELECT * FROM Utilisateurs WHERE Prenom LIKE 'J%'
SELECT * FROM Utilisateurs WHERE Prenom LIKE '%n'
SELECT * FROM Utilisateurs WHERE Prenom LIKE '%h%'
SELECT LOWER(Prenom) FROM Utilisateurs
SELECT UPPER(Prenom) FROM Utilisateurs
SELECT SUBSTR(Prenom, 4, 2) FROM Utilisateurs
SELECT REVERSE(Prenom) FROM Utilisateurs
SELECT REPLACE(Prenom, 'John', 'Marc') FROM Utilisateurs
SELECT TRIM(' Bob ') FROM Utilisateurs => Bob
SELECT TRIM(BOTH 'x' FROM 'xxxBobxxx') FROM Utilisateurs => Bob
SELECT TRIM(LEADING 'x' FROM 'xxxBobxxx') FROM Utilisateurs => Bobxxx
SELECT TRIM(TRAILING 'x' FROM 'xxxBobxxx') FROM Utilisateurs => xxxBob
Fonctions temporelles
SELECT *, ADDDATE(Anniversaire, 10) FROM Utilisateurs
SELECT *, ADDDATE(Anniversaire, INTERVAL 1 YEAR) FROM Utilisateurs
SELECT *, SUBDATE(Anniversaire, INTERVAL 1 MONTH) FROM Utilisateurs
SELECT *, ADDTIME(Anniversaire, '1:0:0') FROM Utilisateurs
SELECT *, SUBTIME(Anniversaire, '2:0:0') FROM Utilisateurs
SELECT * FROM Utilisateurs WHERE Anniversaire < SUBDATE('02-01-2017 10:00:00', INTERVAL 20 YEAR)
SELECT *, DATE_FORMAT(Anniversaire, '%W %d %M %Y') FROM Utilisateurs
SELECT * FROM Utilisateurs WHERE YEAR(Anniversaire) > 1987
SELECT COUNT(*), YEAR(Anniversaire) FROM Utilisateurs GROUP BY YEAR(Anniversaire)
Fonctions à retenir
- NOW()
- CURDATE()
- COS(Nombre)
- CONCAT(Chaine1, Chaine2)
- RAND()
Exemple de syntaxe pour les DATETIME :
- NOW() - INTERVAL 1 DAY
- CURDATE() + INTERVAL 1 MONTH
- CURTIME() + INTERVAL 1 SECOND
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368340/questions.gif)
Exercice
- Toutes les personnes nées avant 1970
- Toutes les personnes nées entre 1970 et 1980
- Toutes les personnes dont le prénom contient "jean"
- Toutes les personnes dont le prénom commence par "jean"
- Toutes les maisons mises en vente en France et Espagne depuis moins de 6 mois
- Toutes les maisons mises en vente sauf en France depuis plus de 6 mois
- L'age de la plus vieille et la plus jeune personne
- Ajouter une personne de 25 ans avec le prénom John et le nom Doe
- On passe l'age à 19 de la personne d'ID 42
- Supprimer toutes les personnes dont l'âge est inférieur à 18
MySQL Intermediaire
- Indexes
- Jointures
- Exercice
Indexes
3 types de clefs :
- Index
- Unique
- Primary
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368687/Capture_d__cran_2017-01-02_08-52-35.png)
Indexes
Index key :
- Recherche rapide
- Utilise de l'espace disque
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368689/375841.png)
Indexes
Unique key :
- Une seule occurence par valeur
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368690/Capture_d__cran_2017-01-02_09-01-32.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368697/Capture_d__cran_2017-01-02_09-16-45.png)
Indexes
Primary key
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368697/Capture_d__cran_2017-01-02_09-16-45.png)
- Unique
- Peut être composite
- Identifie un enregistrement
- NOT NULL
Indexes
Foreign Key
Jointures
SELECT * FROM Utilisateurs
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368282/Capture_d__cran_2017-01-01_16-00-39.png)
SELECT * FROM Competences
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368480/Capture_d__cran_2017-01-01_21-08-00.png)
3 colonnes
6 lignes
4 colonnes
5 lignes
Jointures simples
SELECT * FROM Utilisateurs AS u,
Competences AS c
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368286/Capture_d__cran_2017-01-01_16-04-26.png)
7 colonnes
30 lignes
Jointures complexes
SELECT * FROM Utilisateurs
JOIN Competences
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368286/Capture_d__cran_2017-01-01_16-04-26.png)
SELECT * FROM Utilisateurs
INNER JOIN Competences
7 colonnes
30 lignes
Jointures complexes
SELECT * FROM Utilisateurs
JOIN Competences
ON Competences.UserID = Utilisateurs.ID
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368286/Capture_d__cran_2017-01-01_16-04-26.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368289/Capture_d__cran_2017-01-01_16-05-48.png)
7 colonnes
4 lignes
Jointures complexes
SELECT * FROM Utilisateurs
LEFT JOIN Competences
ON Competences.UserID = Utilisateurs.ID
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368477/Capture_d__cran_2017-01-01_21-05-57.png)
SELECT * FROM Utilisateurs
RIGHT JOIN Competences
ON Competences.UserID = Utilisateurs.ID
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368479/Capture_d__cran_2017-01-01_21-06-15.png)
Jointures Complexes
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368299/1grnprziv.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368300/2grnprziv.png)
INNER JOIN
LEFT JOIN
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368340/questions.gif)
Exercice
- Créer deux tables post et categories
- Lier les deux tables via leurs ids
- Créer une table tags
- Un post peut avoir plusieurs tags
Tables intermédiaires
clés étrangères
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368553/Capture_d__cran_2016-12-31_23-52-10.png)
SELECT * FROM post AS P
LEFT JOIN post_tags AS PT ON P.ID = PT.post_id
LEFT JOIN tags AS T ON T.ID = PT.tags_id
WHERE P.ID = 1
MySQL et PHP
- PDO (PHP Data Object)
- Transactions et verrous
PDO
// index.php
<?php
$CONFIG = require("config.php");
try {
$dbh = new PDO($config["dbType"] . ":host=".$config["host"].";dbname=".$config["dbname"],
$config["user"],
$config["password"]
);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// some queries
}
catch (PDOException $e) {
error_log('PDO Exception: '.$e->getMessage());
die('PDO says no.');
}
// config.php
<?php
return [
"host" => "localhost",
"dbname" => "GDP3_RPG",
"user" => "root",
"password" => "root",
"dbType" => "mysql"
];
PDO
<?php
// .....
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$queryString = "SELECT * FROM Utilsateurs";
$request = $db->query($queryString);
$retour = $req->fetchAll();
foreach($retour as $data) {
// Do some stuff
}
PDO
<?php
// .....
$queryString = "UPDATE Utilisateurs SET Login=:login";
$request = $db->prepare($queryString);
$request->execute([
"login" => "john"
]);
if($request->rowCount() == 0) {
die("user not found. No update");
}
else {
// Do some stuff
}
PDO
<?php
// .....
$offset = 5;
$queryString = "SELECT * FROM Utilisateurs LIMIT 0, :limit";
$requete = $db->prepare($queryString);
$requete->execute([
"limit" => $offset
]);
/*
!!! ERREUR !!!
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5'' at line 1
*/
$queryString = "SELECT * FROM Utilisateurs LIMIT 0, 5";
$requete = $db->prepare($queryString);
$requete->execute();
// OK
PDO
<?php
// .....
$offset = 5;
$queryString = "SELECT * FROM Utilisateurs LIMIT 0, :limit";
$requete = $db->prepare($queryString);
$requete->bindValue("limit", 5, PDO::PARAM_INT);
$requete->execute();
// OK
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3368340/questions.gif)
Transactions et verrous
TP
-
Un rpg web
-
Inscription / connexion
-
Chaque joueur peut créer un personnage
-
Le joueur peut affronter des monstres aléatoires
-
Le joueur a des compétences qu'il peut utiliser en combat
-
Le joueur peut monter de niveau
TP
-
5 tables obligatoire (vous pouvez en faire plus) :
-
Table des joueurs Player
Définir l'état du joueur (login, password, hp restants etc...) -
Table des monstres Monster
Defini un modèle de monstre (attaque, défense, hp...) -
Table des compétences Skill
La liste des skills disponible, à quel niveau etc... -
Table des levels Level
Liste des niveaux du joueur (xp requis etc...). Vous pouvez créer un algorithme de calcul à la place -
Table historique History (sauvegarde de tous les
événements du jeu aka resultat de combat, montée niveau...)
-
TP
-
Pas de css ou de js. Du PHP pur
-
3 pages php :
-
index.php, je veux pouvoir :
-
me logger / m'enregister
-
-
village.php, je veux pouvoir :
-
Voir un récap du personnage du joueur
-
Avoir la possibilité d'aller attaquer un monstre
-
-
battle.php, je veux pouvoir :
-
Sélectionner un skill qui va être lancé pendant le combat
-
Lancer le combat contre un monstre
-
Voir le récap du combat et revenir au village
-
-
TP
-
Vous avez le sujet par email !!!
-
Pas de limite de temps (pour l'instant ) mais passez pas plus d'une heure
-
Exercez vous, faites un truc fun !
-
Je n'ai pas tout mis dans l'énoncer, du coup réfléchissez un peu à la conception de votre base de données !
- Mon email : j.lenaou@gmail.com
- Mon skype : veltan__
- Venez me parler si vous avez une question, n'hésitez pas !
![](https://s3.amazonaws.com/media-p.slid.es/uploads/577142/images/3369103/face-devilish-2.png)
GDP3 - MySQL
By Jérôme LE NAOU
GDP3 - MySQL
- 108