ReMise à niveau MySQL

Jérôme Le Naou

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

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

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

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

Indexes
Jointures
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 !

ReMise à niveau MySQL

MySQL Bases

SGBD ?

MySQL = SGBDR

Bases

Tables

Requêtes simples

Requêtes CheatSheet 1

Requêtes CheatSheet 2

Requêtes CheatSheet 3

Requêtes CheatSheet 4

Exercice

Manipulations MySQL

Fonctions numériques

Fonctions sur les textes

Fonctions temporelles

Fonctions à retenir

Exercice

MySQL Intermediaire

Indexes

Indexes

Indexes

Indexes

Indexes

Jointures

Jointures simples

Jointures complexes

Jointures complexes

Jointures complexes

Jointures Complexes

Exercice

Tables intermédiaires clés étrangères

MySQL et PHP

PDO

PDO

PDO

PDO

PDO

Transactions et verrous

TP

TP

TP

TP

Tables intermédiaires
clés étrangères