MySQL Bases

1. SGBD
2. MySQL
3. Bases et tables
4. Requêtes simples
5. Requêtes CheatSheet
6. 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

Tables

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

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

1. Fonctions numériques
2. Fonctions sur les textes
3. Fonctions temporelles
4. 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

https://dev.mysql.com/doc/refman/5.5/en/numeric-functions.html

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

https://dev.mysql.com/doc/refman/5.5/en/string-functions.html

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)

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

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

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

1. Indexes
2. Jointures
3. Exercice

Indexes

3 types de clefs :

• Index
• Unique
• Primary

Indexes

Index key :

• Recherche rapide
• Utilise de l'espace disque

Indexes

Unique key :

• Une seule occurence par valeur

Indexes

Primary key

• Unique
• Peut être composite
• Identifie un enregistrement
• NOT NULL

Indexes

Foreign Key

Jointures

SELECT * FROM Utilisateurs

SELECT * FROM Competences

3 colonnes
6 lignes

4 colonnes
5 lignes

Jointures simples

SELECT * FROM Utilisateurs AS u,
    Competences AS c

7 colonnes
30 lignes

Jointures complexes

SELECT * FROM Utilisateurs 
    JOIN Competences

SELECT * FROM Utilisateurs 
    INNER JOIN Competences

7 colonnes
30 lignes

Jointures complexes

SELECT * FROM Utilisateurs 
    JOIN Competences
    ON Competences.UserID = Utilisateurs.ID

7 colonnes
4 lignes

Jointures complexes

SELECT * FROM Utilisateurs 
    LEFT JOIN Competences
    ON Competences.UserID = Utilisateurs.ID

SELECT * FROM Utilisateurs 
    RIGHT JOIN Competences
    ON Competences.UserID = Utilisateurs.ID

Jointures Complexes

INNER JOIN

LEFT JOIN

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

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

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 !