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 MesCinemas

Salles

Films

Horaires

Utilisateurs

Billets

Bases

Tables

Colonnes

Lignes

Requêtes simples

SELECT * FROM Billets WHERE utilisateur_id=5


INSERT INTO Utilisateurs (Login,Email,Password) VALUES 
("Jérôme", "j.lenaou@gmail.com", "pgdp2017")


UPDATE Horaires SET Date="01-03-2017", Heure="09:00:00" WHERE ID=5


DELETE FROM Films WHERE ID=2

INSERT INTO <table> (<colonnes>)
    VALUES (<expressions>)

Requêtes CheatSheet 4

DELETE FROM <table>
    WHERE <condition>

/!\ Ne JAMAIS oublier le WHERE /!\

Requêtes CheatSheet 3

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

Exercice

• Créer une base de donnée PGDP
• Créer une table Films avec 4 colonnes :
  • id
  • titre
  • description
  • date_creation
  • date_modification
• 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

• Tous les utilisateurs nées avant 1970
• Tous les utilisateurs nées entre 1970 et 1980
• Tous les utilisateurs dont le prénom contient "jean"
• Tous les utilisateurs dont le prénom commence par "jean"
• Toutes les lignes de la table Films
• L'age du plus vieux ET du plus jeune utilisateur
• Ajouter un utilisateur de 25 ans avec le prénom John et le nom Doe
• Passer l'âge d'un utilisateur à hier
• Supprimer toutes les utilisateurs dont l'âge est inférieur à 18 ans

MySQL et PHP

• PDO (PHP Data Object)

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_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8");
    // some queries
}
catch (PDOException $e) {
    error_log('PDO Exception: '.$e->getMessage());
    die('PDO says no.');
}

// config.php

<?php
return [
    "host" => "localhost",
    "dbname" => "PGDP_Cinemas",
    "user" => "root",
    "password" => "root",
    "dbType" => "mysql"
];

PDO

<?php
// .....

$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