Structured Query Language

Base de données

Utilité

  • beaucoup de données à gérer
  • traitement des données automatisée / structuration de celles-ci
  • extraction de données filtrées, triées

Type relationnel

  • premier mode de structuration : Système de Gestion de Base de Données, SGBD
  • principe ACID = être sûr des données stockées
  • Oracle, Microsoft (MSSQL), MySQL

Type NoSQL (Not Only SQL)

  • besoin de quelque chose de moins rigoureux, partageable (multi-serveur, cloud)
  • grande quantité de données pas souvent structurées de la même manière
  • Google (BigTable), Amazon (Dynamo), LinkedIn (Voldemort), Facebook (Cassandra puis HBase)

Serveur de base de données

Installation de MySQL

  • Logiciels tiers implémentant le logiciel de gestion de serveur, PhpMyAdmin : EasyPHP, MAMP, WAMP
  • Logiciel fournit par l'éditeur du serveur

Administrateur

  • un utilisateur = un login, un mot de passe
  • root
  • droits sur le serveur
  • droits sur les bases de données

Interaction avec le serveur 1/2

Ligne de commande (mysqladmin)

Simplifie l'administration de serveurs en permettant le scripting d'opérations notamment

Logiciel PhpMyAdmin

Simplifie la gestion via une interface plus agréable pour les non-initiés

Administration d'une base de données

Création

Soit en utilisant un logiciel tel que PhpMyAdmin, soit en écrivant la requête de création.

Par l'interface PhpMyAdmin
Accueil sur l'interface

Par requête
CREATE DATABASE m2cpn_db;

Suppression

DROP DATABASE m2cpn_db;

Tables d'une base de données

Type de données / encodage

Entiers
Type Valeur minimale signée Valeur minimale non signée Valeur maximale signée Valeur maximale non signée
TINYINT -128 0 127 255
SMALLINT -32768 0 32767 65535
MEDIUMINT -8388608 0 8388607 16777215
INT -2147483648 0 2147483647 4294967295
BIGINT -263 0 263-1 264-1
Nombres à virgule
  • La précision importe comme en finance : type DECIMAL
    • DECIMAL(M, N)
    • DECIMAL(5,2) : de -999.99 à 999.99
  • La précision importe peu : type FLOAT, DOUBLE
    • FLOAT(M,D) sur 4 octets
    • DOUBLE(M,D) sur 8 octets
Chaînes de caractères
  • CHAR, taille fixe dans la colonne (espace pour compléter à droite)
  • VARCHAR, taille en fonction de ce qu'il y a à stocker
  • longueur maximale : 255
Temps
  • DATE sous format YYYY-MM-DD
  • TIME sous format HHH:MM:SS (peut stoker une date comme une période)
  • DATETIME sous format YYYY-MM-DD HH:MM:SS
  • TIMESTAMP valeur numérique depuis 1970-01-01 00:00:01

La valeur NULL

Valeur par défaut représentant l'absence de valeur.

Clef primaire

Structuration des données pour mieux retrouver des données, unicité d'une données.

Création d'une table

Par l'interface PhpMyAdmin
Création d'une table

Définition des champs d'une table

Type de table

Modification de la structure d'une table

Par l'interface PhpMyAdmin
Structure de table

Suppression d'une table

Par l'interface PhpMyAdmin

En cliquant sur Supprimer au niveau de la structure de table.

Insertion de données

Par l'interface PhpMyAdmin
Insertion de données

Lors de la sélection des données, nous reviendrons sur les requêtes d'insertion.

Requêtes d'insertion

Une fois un jeu de données ajouté, il est possible de voir les données présentes dans la table.

Affichage des données

Il est également possible d'importer des données via le menu Importer.

Récupérer des données

Requête de base

SELECT * FROM etudiant_tb;
Le caractère * sert de joker pour signifier "tous les champs"

Par l'interface PhpMyAdmin
Zone de texte pour les requêtes

Il est possible de limiter les champs voulus : 

SELECT email FROM etudiant_tb;

SQL intègre aussi des fonctions fournissant des possibilités variées : 

SELECT count(*) as compte FROM etudiant_tb;

retourne le nombre d'élément correspondant. L'alias "compte" servira pour le nom de la colonne ou l'accès à la valeur.


SELECT distinct(prenom) FROM etudiant_tb;

n'affichera que chaque prénoms différents qu'une seule fois.

Sélectionner avec WHERE

SQL offre de nombreux outils pour filtrer les données que l'on souhaite récupérer :

  • opérateur de comparaison comme = ou !=
  • opérateur "like" qui s'utilise avec les caractères magiques _ et %
    • _ : remplace un caractère précisément
    • % : remplace 1 ou plusieurs caractères
  • les critères doivent être liés entre eux en utilisant AND et/ou OR

Pour utiliser ces éléments, il faut utiliser le mot clé "where".

SELECT email FROM etudiant_tb WHERE prenom = "vincent";
SELECT email FROM etudiant_tb WHERE prenom LIKE "_incent";
SELECT email FROM etudiant_tb WHERE prenom LIKE "vin%" AND nom LIKE "%cel";

La recherche dans une table sur un critère de type chaîne de caractère nécessite d'utiliser les doubles quotes

Trier les données avec ORDER BY

SQL permet de trier, d'organiser les résultats d'une sélection / recherche en utilisant l'expression clé ORDER BY

SELECT email FROM etudiant_tb WHERE genre = "h" ORDER BY nom;

SELECT id, email FROM etudiant_tb WHERE genre = "h" ORDER BY nom, prenom;

Vous pouvez apprendre par coeur
SELECT .. FROM .. WHERE .. ORDER BY ..

Par défaut, c'est un tri ascendant qui est réalisé. Il est possible de le spécifier ou de remplacer ce comportement en utilisant les mots clés ASC ou DESC et ce, pour chacun des critères de tri. 

SELECT id, email FROM etudiant_tb WHERE genre = "h" ORDER BY nom ASC, prenom DESC;

Mise à jour de données

Bien entendu, les données peuvent être modifiée pour permettre à un utilisateur de changer son e-mail par exemple.

UPDATE etudiant_tb SET email = "m.vincent@yahoo.com" WHERE id = 1;
Le mot clé WHERE est vivement recommandé sous peine de modifier l'ensemble des lignes de la table.

Suppression de données

Bien entendu, les données peuvent être supprimées pour permettre à un utilisateur de ne plus faire partie des membres par exemple.

DELETE FROM etudiant_tb WHERE id = 1;
Là encore plus WHERE est vivement recommandé sous peine de supprimer l'ensemble des lignes de la table.

Fonctions et opérateur

MySQL permet d'utiliser un grand nombre d'opérateurs et de fonctions pré-conçues afin de permettre une grande flexibilité dans la manipulation des données. Nous savons déjà que la partie SELECT d'une requête permet de limiter les champs à récupérer alors que la partie du WHERE permet de filtrer les données. Mais nous pouvons avoir besoin de plus. C'est là que fonctions et opérateurs entrent en jeu : les opérateurs se retrouvent surtout au niveau de la partie du WHERE ; les fonctions peuvent être aussi bien dans le WHERE que dans le SELECT.

Parmi les fonctions, nous retrouvons :

  • MIN(), MAX() : pour récupérer la valeur maximale ou minimale d'une colonne
  • ABS() : donne la valeur absolue d'une valeur
  • COUNT() : retourne le nombre d'éléments trouvés

Parmi les opérateurs les plus connus, il y a :

  • AND / OR : opérateurs booléens permettant de lier des conditions
  • LIKE, accompagné :
    • de _ : remplace un et un seul caractère
    • de % : remplace un ou plusieurs caractères
  • BETWEEN..AND.. permet de récupérer des données dans un intervalle
  • +, -, *, / permettent de faire des calculs
  • =, <=, >=, <, >, != permettent les comparaisons
SELECT MAX(note) FROM note_tb WHERE matiere = "physique";	-- retourne la meilleure note
SELECT COUNT(note) FROM note_tb WHERE matiere = "physique";	-- retourne le nombre de note
SELECT ABS(note) FROM note_tb WHERE note < 0;			-- retourne la valeur positive d'une note négative

SELECT COUNT(note) AS nb FROM eleve_tb WHERE note >= 12 AND note < 14;  -- nb va contenir le nombre de notes trouvées
SELECT MAX(note) FROM note_tb WHERE matiere = "physique" OR matiere = "mathématique";

SELECT nom FROM eleve_tb WHERE nom = "ALBERT";
SELECT nom FROM eleve_tb WHERE nom LIKE "DUPON_";	-- retourne les élèves ayant comme nom DUPONT ou DUPOND
SELECT nom FROM eleve_tb WHERE nom LIKE "DU%N%";	-- retourne les élèves ayant comme nom DUPONT ou DUPOND ou DURAND

Plus d'info : ici

Acteurs et relations

Imaginons la mise en place d'un site qui réunisse des blogs. Quels sont les différents acteurs impliqués et leurs relations ?
Et dans la mise en place d'un jeu de type MMO ?

Jointures

Les jointures servent à extraire des données liées entre elles mais réparties sur deux tables ou plus.

Jointures simples

Ce type de jointure ne récupére les jeux de données que s'il existe une correspondance dans chacune des tables liées : un étudiant qui n'a pas de note n'apparaitra pas dans le résultat. 

SELECT email FROM etudiant_tb as e, note_tb as n WHERE e.id = n.eleve AND matiere = "sql" ORDER BY nom;

Les alias "e" et "n" permettent de s'assurer que les bons champs sont récupérés.

Jointures orientées

Ce type de jointure va donner la priorité à une des tables par rapport à l'autre de sorte que l'ensemble du jeu de données trouvé sur la table prioritaire sera affiché même si par endroit il n'y a pas de correspondance. 

SELECT email FROM etudiant_tb as e LEFT JOIN note_tb as n ON e.id = n.eleve WHERE matiere = "sql" ORDER BY nom;

Dans cette requête, la priorité est donnée à la table etudiant car elle est située à gauche (LEFT) du mot clé JOIN. Cela signifie que le résultat contiendra tous les élèves même ceux qui n'ont pas de note.
De la même manière, il est possible d'avoir une jointure orientée à droite en utilisant RIGHT et dans ce cas, c'est la table située à droite qui aura priorité.

Mise en pratique

Fichier pour la base de données.

Téléchargez le fichier et importez le après avoir créée la base de données "bibliotheque".

Exercice n°1

En utilisant une requête :

  1. affichez uniquement le nom des auteurs
  2. affichez uniquement le nom des auteurs par ordre alphabétique
  3. affichez uniquement le nom des auteurs ayant le prénom Vladimir
  4. affichez uniquement le nom des auteurs ayant le prénom David ou Edward
  5. affichez uniquement le nom des auteurs dont le prénom commence par V
  6. affichez le nombre de genre qui existe
  7. affichez le nombre de genre qui existe commençant par "po"
  8. affichez le titre d'un livre et le nom de son auteur avec une jointure simple
  9. affichez le titre des livres écrit par Eddings avec une jointure simple
  10. affichez le mot clef et le titre d'un livre auquel il est associé, avec une jointure simple
  11. affichez le mot clef et le titre d'un livre auquel il est associé, avec une jointure à gauche avec priorité sur les mots clefs

Interaction avec le serveur 2/2

Pour accéder à une table d'une base de données, il faut se connecter au serveur en fournissant l'adresse du serveur, le nom de l'utilisateur et son mot de passe. Enfin, il faut indiquer le nom de la base de données.

Extension MySQLi

Sous ce moteur de base de données, la syntaxe pour créer une connexion est la suivante : 

$mysqli = new mysqli("localhost", "root", "admin123", "m2cpn_db");
if ($mysqli->connect_errno) {
    echo "Echec lors de la connexion à MySQL : (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

La variable $mysqli, qui est un objet, contient le point d'accès à la base de données. C'est par elle que nous allons transmettre nos requêtes. 

$mysqli->query("INSERT INTO etudiant_tb (nom, prenom, email, date) VALUES ("ANTOINE", "Valérie", "v.antoine@gmail.com", now()), ("ALBERT", "Pierre", "", now())");

$res = $mysqli->query("SELECT id, nom FROM etudiant_tb ORDER BY id ASC");
while ($row = $res->fetch_assoc()) {
    echo " id = " . $row['id'] . "<br>";
}

http://php.net/manual/fr/mysqli.quickstart.connections.php

Extension PDO

Ce moteur ne sera pas étudié là mais est présenté car il est l'un des plus utilisés. Il fonctionne sensiblement de la même manière que MySQLi pour la création d'un point d'accès vers la base de données.

Lexique

  • base de données : structure contenant les tables
  • table : structure contenant les données
  • champ (ou nom de champs ou nom de colonne) : label des informations qu'il définit ; nom définit à la création de la table pour les données qui vont y être conservées ; valeur d'une colonne
  • colonne : ensemble des données enregistrées sous un nom de champs (la colonne code postal contient les codes postaux enregistrés dans la table)
  • ligne : un jeu complet de données (au niveau d'une table adresse, une ligne représente une adresse)
  • id : identifiant unique permettant d'extraire un jeu précis de données
  • filtrer : action permettant de limiter les jeux de données