Your browser doesn't support the features required by impress.js, so you are presented with a simplified version of this presentation.

For the best experience please use the latest Chrome, Safari or Firefox browser.

Introduction

 

Historique

  • Créé par Michael Widenius en 1995
  • Version 4 en 2004
    (sous-requêtes et prepared statements)
  • Version 5 en 2005
    (curseurs, procédures stockées, triggers, vues)
  • Racheté par Sun Microsystems en 2008
  • Oracle rachète Sun en 2010
  • Version 5.7 en 2015
    (dernière version : 5.7.28 publiée le 14/10/2019)
  • Version 8.0 en 2018 (stable)
    (dernière version : 8.0.18)

Avantages de MySQL

  • Communauté
  • Open Source...
  • Orienté Web, Cloud, Big Data
  • Solidité et fiabilité
  • Supports OS

Inconvénients de MySQL

  • Open Source ?
  • Fonctionnalités
  • De grands noms quittent MySQL
  • Syntaxe
  • Avenir incertain

Principe du client - serveur

Vocabulaire

  • Base de données
  • Schéma
  • Table
  • Colonne
  • Ligne
  • Requêtes
  • Contraintes

Installation

Windows

Pour installer le serveur, l'installeur MySQL est recommandé.

 Installer le client MySQL Workbench en téléchargeant l'installeur ici.

Linux (console)

Exécuter la commande suivante dans une console

sudo apt-get install mysql-community-server mysql-community-client

Le serveur

/etc/init.d/mysql start|stop|restart|reload|force-reload|status
+ R 'services.msc'
mysqld --verbose --help
mysqld --var_name=value
SHOW VARIABLES;
SHOW STATUS;
SET GLOBAL max_connections = 1000;
SET SESSION sql_mode = 'TRADITIONAL';
mysqladmin variables
mysqladmin extended-status

Le répertoire data

ls -al /var/lib/mysql
-rw-rw---- 1 mysql mysql    16384 oct.  25 21:16 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 oct.  25 21:16 aria_log_control
-rw-r--r-- 1 root  root         0 août  16 15:51 debian-5.5.flag
drwx------ 2 mysql mysql     4096 oct.  20 15:11 entreprise
-rw-rw---- 1 mysql mysql 44040192 oct.  25 21:16 ibdata1
-rw-rw---- 1 mysql mysql  5242880 oct.  26 10:00 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 oct.  26 10:00 ib_logfile1
drwx------ 2 mysql mysql     4096 août  16 15:51 mysql
-rw-rw---- 1 mysql mysql       14 août  16 15:51 mysql_upgrade_info
drwx------ 2 mysql mysql     4096 août  16 15:51 performance_schema
            
ls -al /var/lib/mysql/entreprise
-rw-rw----  1 mysql mysql   61 oct.  19 09:05 db.opt
-rw-rw----  1 mysql mysql 8822 oct.  19 09:05 Employe.frm
-rw-rw----  1 mysql mysql 8600 oct.  19 16:59 Manager.frm
-rw-rw----  1 mysql mysql 8630 oct.  19 16:59 Technicien.frm

La base système mysql

  • La gestion des droits
  • Les objets stockés dans la base
  • La journalisation
  • La réplication
  • L'optimisation
  • ...

Les logs

Type de log Informations Activation
Error log Les problèmes rencontrés lors du démarrage, de l'arrêt ou du lancement de mysqld Activé par défaut sur Windows, voir variable log_error
General query log Les connexions client et les requêtes reçues de ces clients Variables general_log_file et general_log
Binary log Les requêtes effectuant des modifications sur les données (aussi utilisés pour la réplication) Variable log-bin
Slow query log Les requêtes qui ont pris plus de long_query_time secondes à s'exécuter Variables log_slow_queries, long_query_time et log-queries-not-using-indexes

Les premiers réflexes

  • 1 : Changer le mot de passe root

    sudo mysqladmin -u root -h localhost password nouveau_mdp
  • 2 : Interdire l'accès distant pour root

    UPDATE mysql.user SET Host = 'localhost' WHERE user='root'; FLUSH PRIVILEGES;
  • 3 : Supprimer le compte anonymous

    DELETE FROM mysql.user WHERE User=''; FLUSH PRIVILEGES;
  • 4 : Supprimer les éventuelles bases de test créées

    DROP DATABASE test; DELETE FROM db WHERE db='test' OR db='test\_%';

Développement côté client

  • Manipuler des bases de données
  • Manipuler des tables
  • Manipuler des données
  • Exécuter des requêtes SQL issues d'un fichier
  • Manipuler des index
  • Gérer des contraintes
  • Gérer des index
  • Effectuer des requêtes complexes
  • Manipuler des vues

Le client mysql

#Connexion au serveur MySQL local avec l'utilisateur courant et sans mot de passe
mysql

#Idem mais avec l'utilisateur 'toto' et sans mot de passe
mysql -u toto

#Idem mais avec le mot de passe 'pass'
mysql -u toto -ppass

#Idem mais demande le mot de passe
mysql -u toto -p

#Idem mais se connecte à la base 'base'
mysql -u toto -p -D base

#Idem mais connexion à un serveur distant dont l'adresse est 'hote'
mysql -u toto -p -D base -h hote

#Idem mais on spécifie le port (par défaut 3306)
mysql -u toto -p -D base -h hote -P 3307

Créer une base de données

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...



create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

--Crée la base de données 'mabase'
CREATE DATABASE IF NOT EXISTS mabase;

--Crée la base 'mabase' avec l'encodage 'utf8' et la collation utf8_general_ci
CREATE DATABASE mabase CHARACTER SET utf8 COLLATE utf8_general_ci;

--Pour voir l'encodage et la collation par défaut du serveur
SELECT @@character_set_server, @@collation_server; 
--Pour sélectionner une base par défaut
USE mabase;
--Pour voir l'encodage l'encodage et la collation de la base (après un USE)
SELECT @@character_set_database, @@collation_database; 

Modifier une base de données

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

--Modifie la base 'mabase' pour utiliser l'encodage utf-8 
--et la collation utf8_general_ci
ALTER DATABASE mabase CHARACTER SET = 'utf8' COLLATE = utf8_general_ci;

Supprimer une base de données

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name




--Supprime la base 'mabase' et toutes ses données
DROP DATABASE mabase;

Les types de données

  • Types numériques
  • Types date et heure
  • Types chaînes
  • Autres types

Types de données entier

Type Occupation en mémoire Intervalle (Signed) Intervalle (Unsigned)
BIT(M) 1 bit à 8 octets Ce type est utilisé pour stocker des valeurs binaires, avec la notation b'value' (ex : b'111' = 7)
TINYINT(M) 1 octet -128 à 127 0 à 255
BOOL ou BOOLEAN 1 octet Les booléens sont représentés comme des TINYINT(1). La valeur 0 équivaut à false, toute autre valeur à true. A noter que les valeurs TRUE et FALSE sont respectivement des alias de 1 et 0.
SMALLINT(M) 2 octets -32 768 à 32 767 0 à 65 535
MEDIUMINT(M) 3 octets -8 388 608 à 8 388 607 0 à 16 777 215
INT(M) ou INTEGER(M) 4 octets -2 147 489 648 à 2 147 483 647 0 à 4 294 967 295
BIGINT(M) 8 octets -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807 0 à 18 446 744 073 709 551 615

Types de données à virgule fixe

Type Occupation en mémoire Exemple
DECIMAL(P,E) ou NUMERIC(P,E) X octets pour la partie décimale, Y octets pour la partie entière DECIMAL(5,2) permet de représenter 5 chiffres dont 2 après la virgule, soit l'intervalle -999.99 à 999.99

Types de données à virgule flottante

Type Occupation en mémoire Remarques
FLOAT(P) 4 octets P est facultatif mais s'il est spécifié et qu'il est compris entre 0 et 23, ce sera bien un type FLOAT qui sera utilisé, si c'est entre 24 et 53, c'est un type DOUBLE qui sera en fait utilisé.
DOUBLE 8 octets
FLOAT(P,E) ou REAL(P,E) ou DOUBLE PRECISION(P,E) 4 ou 8 octets FLOAT(5,2) permet de représenter 5 chiffres dont 2 après la virgule, soit l'intervalle -999.99 à 999.99

Précisions sur les types numériques

Types date

Type Format Remarques
DATE 'YYYY-MM-DD' Utilisé pour les dates sans nécessité de stocker l'heure. L'intervalle des valeurs possibles est '1000-01-01' à '9999-12-31'
DATETIME 'YYYY-MM-DD HH:MM:SS[.fraction]' Utilisé pour les dates et heure. L'intervalle des valeurs possibles est '1000-01-01 00:00:00' à '9999-12-31 23:59:59'.
TIMESTAMP 'YYYY-MM-DD HH:MM:SS[.fraction]' Type correspondant au Timestamp Unix. L'intervalle des valeurs possibles est 1970-01-01 00:00:01' à '2038-01-19 03:14:07'
TIME HH:MM:SS[.fraction]' TIME peut être utilisé pour représenter une heure dans la journée, mais aussi pour représenter une durée. L'intervalle des valeurs possibles est '-838:59:59.000000' à '838:59:59.000000'.
YEAR YYYY L'intervalle des valeurs possibles est entre 1901 et 2155.

Les types chaînes

Type Occupation mémoire Remarques
CHAR(X) X octets X correspond à la taille maximum de la chaîne de caractère et doit être compris entre 0 et 255. Une chaîne de caractère dont la taille est inférieure à X est automatiquement complétée avec des espaces à droite.
VARCHAR(X) (Longueur de la chaîne + 1) octet(s) Contrairement à CHAR, les espaces en début et en fin sont conservés.
TEXT
TINYTEXT
MEDIUMTEXT
LONGTEXT
(Longueur de la chaîne + (1~4)) octet(s) TEXT 65 535 caractères
TINYTEXT 255 caractères
MEDIUMTEXT 16 777 215 caractères
LONGTEXT 4 294 967 295 caractères

Les types binaires

Type Équivalent
BINARY(X) CHAR
VARBINARY(X) VARCHAR
BLOB
TINYBLOB
MEDIUMBLOB
LONGBLOB
TEXT
TINYTEXT
MEDIUMTEXT
LONGTEXT

Les types à valeurs restreintes

Type Exemple Remarques
ENUM ENUM('XS', 'S', 'M', 'L', 'XL') Les valeurs sont stockées sous forme d'index entier et l'ordre de tri correspond à l'ordre dans lequel les valeurs sont déclarées. Un ENUM peut avoir jusqu'à 65 535 valeurs différentes.

On peut l'utiliser soit par sa valeur 'M' soit par son index 3.

SET SET('actu', 'politique', 'economie') Une valeur de type SET peut contenir 0 ou plusieurs valeurs parmi les valeurs spécifiées dans le type. Ces valeurs sont séparées par des ,.

Les autres types

  • Données géographiques
  • JSON

Ça va ?

Création de tables

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...)

  create_definition:
    col_name column_definition

  column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

    data_type: INT | FLOAT | ...

--Exemple
CREATE TABLE IF NOT EXISTS vehicule (
  id INT PRIMARY KEY AUTO_INCREMENT,
  marque VARCHAR(50) NOT NULL,
  modele VARCHAR(50),
  immatriculation VARCHAR(30) NOT NULL UNIQUE,
  date_mise_circulation DATE NOT NULL
) ;

Modification de tables

ALTER TABLE tbl_name [alter_specification [, alter_specification] ...] 
    alter_specification:
      | ADD [COLUMN] (col_name column_definition [FIRST | AFTER col_name])
      | ADD {INDEX|KEY} [index_name] [idx_type] (idx_col,...) [idx_opt] ...
      | ADD [CONSTRAINT [symbol]] PRIMARY KEY [idx_type] (idx_col,...) [idx_opt] ...
      | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [idx_name] [idx_type] (idx_col,...) [idx_opt] ...
      | ADD [CONSTRAINT [symbol]] FOREIGN KEY [idx_name] (idx_col,...) reference_definition
      
      | CHANGE [COLUMN] old_col_name new_col_name column_definition
      | MODIFY [COLUMN] col_name column_definition
      
      | DROP [COLUMN] col_name
      | DROP {INDEX|KEY} index_name
      | DROP PRIMARY KEY
      | DROP FOREIGN KEY fk_symbol
--Exemple
ALTER TABLE vehicule 
  DROP COLUMN modele,
  CHANGE COLUMN date_mise_circulation date_circulation DATE NOT NULL,
  ADD CONSTRAINT UNIQUE (immatriculation);

Suppression de tables (et son contenu)

DROP TABLE [IF EXISTS]
  tbl_name [, tbl_name] ...

--Exemple
DROP TABLE IF EXISTS vehicule;

Vider une table

TRUNCATE [TABLE] tbl_name

--Exemple
TRUNCATE TABLE vehicule;

Insertion de données dans une table

INSERT [INTO] tbl_name [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...

    value:
        {expr | DEFAULT}

    value_list:
        value [, value] ...
  
  --Exemple
  INSERT INTO vehicule (marque, modele, immatriculation, date_mise_circulation) VALUES
    ('Peugeot', '208', 'AA-123-BB', '2017-10-02'),
    ('Renault', 'Clio', 'DD-456-CC', '2017-10-01');
  

Lecture des données des tables

SELECT [DISTINCT]
    select_expr [, select_expr ...]
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY col_name [ASC | DESC], ... ]
    [HAVING where_condition]
    [ORDER BY col_name [ASC | DESC], ...]
    [LIMIT row_count]

--Tous les véhicules BMW ordonné par modèle croissant
SELECT * FROM vehicule
WHERE marque = 'BMW'
ORDER BY modele ASC;

--Récupère toutes les marques ayant au moins trois véhicules et affiche le nombre 
SELECT marque, count(*) FROM vehicule
GROUP BY marque
HAVING count(*) > 2;

Mise à jour de données

UPDATE table_name
    SET assignment_list
    [WHERE where_condition]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

--Met à jour tous les véhicules de marque 'bmw' pour mettre la marque en majuscule
UPDATE vehicule SET marque = 'BMW' WHERE marque = 'bmw';

Suppression de données

DELETE FROM tbl_name
    [WHERE where_condition]
    [LIMIT row_count]

--Suppression de tous les véhicules dont la date de mise 
--en circulation est antérieure au 1er janvier 2017.
DELETE FROM vehicule WHERE date_mise_circulation < '2017-01-01';

Exécuter un fichier SQL


 --Fichier create.sql
 --Création de la table véhicule
 CREATE TABLE vehicule ... ;

--Insertion des valeurs
 INSERT INTO vehicule (...) VALUES (...);
 INSERT INTO vehicule (...) VALUES (...);
 INSERT INTO vehicule (...) VALUES (...);
 ...

--Depuis un shell
mysql mabase < create.sql

--Depuis le client mysql
source create.sql
\. create.sql

Les contraintes d'intégrité

--Ajout dans la table vehicule d'une clé primaire sur le champ id
ALTER TABLE vehicule ADD PRIMARY KEY(id);
--Suppression de la clé primaire
ALTER TABLE vehicule DROP PRIMARY KEY; 

--Ajout d'une contrainte d'unicité sur l'immatriculation
ALTER TABLE vehicule ADD CONSTRAINT immat_unique UNIQUE(immatriculation);
--Suppression de la contrainte
ALTER TABLE vehicule DROP INDEX immat_unique;

--Ajout d'une clé étrangère entre vehicule et proprietaire
ALTER TABLE vehicule ADD CONSTRAINT fk_vehicule_proprietaire 
FOREIGN KEY (proprietaire_id) REFERENCES proprietaire(id);
--Suppression de la clé étrangère
ALTER TABLE vehicule DROP FOREIGN KEY fk_vehicule_proprietaire;

Les index

CREATE [UNIQUE] {INDEX|KEY} index_name
    [index_type]
    ON tbl_name (index_col_name,...)

index_col_name:
    col_name [(length)]

--Index sur la marque et le modèle d'un véhicule
CREATE INDEX idx_marque ON vehicule(marque, modele);
--Suppression de l'index
DROP INDEX idx_marque ON vehicule;

--Index les deux premiers caractères de l'immatriculation
CREATE INDEX idx_immat ON vehicule(immatriculation(2));

La notion de transaction

SET autocommit=0;

START TRANSACTION;

COMMIT;

ROLLBACK; 

--Exemple : Début de transaction
START TRANSACTION;
INSERT INTO vehicule ...;
INSERT INTO vehicule ...;
...
--Validation de toutes les requêtes
COMMIT;

Requêtes complexes

  • Jointures
  • Sous-requêtes
  • Union

Jointures

--Jointure interne
SELECT * FROM vehicule v
INNER JOIN proprietaire p ON v.proprietaire_id = p.id;

--Jointure externe à gauche 
SELECT * FROM vehicule v
LEFT JOIN proprietaire p ON v.proprietaire_id = p.id;

--Jointure externe à droite 
SELECT * FROM vehicule v
RIGHT JOIN proprietaire p ON v.proprietaire_id = p.id;

--Jointure externe complète non supportée en MySQL

--Autres syntaxes lorsque les champs des deux tables ont le
--même nom, ici prop_id.
SELECT * FROM vehicule INNER JOIN proprietaire USING (prop_id);
SELECT * FROM vehicule NATURAL JOIN proprietaire;

Sous-requêtes

--Propriétaire ayant une BMW et une Peugeot
SELECT p.* FROM proprietaire p
INNER JOIN vehicule v ON v.proprietaire_id = p.id 
WHERE v.marque = 'BMW'
AND EXISTS (
  SELECT p2.* FROM proprietaire p2
  INNER JOIN vehicule v2 ON v2.proprietaire_id = p2.id 
  WHERE p2.id = p.id
  AND v2.marque = 'Peugeot'
);

--Propriétaire le + jeune vs propriétaire de BMW le + jeune
SELECT MAX(prop.date_naissance), MAX(p.dtN) 
FROM proprietaire prop, (
  SELECT p2.date_naissance as dtN FROM proprietaire p2
  INNER JOIN vehicule v2 ON v2.proprietaire_id = p2.id 
  AND v2.marque = 'BMW'
 ) AS p;
--On supprime les propriétaires de Lada
DELETE FROM proprietaire p
WHERE p.id IN (
  SELECT v.proprietaire_id FROM vehicule v 
  WHERE v.marque = 'Lada'
);

--Les propriétaire de Ferrari sont chanceux...
UPDATE test.proprietaire SET commentaires = 'Chanceux !' 
WHERE id = (
  SELECT v.proprietaire_id FROM test.vehicule v 
  WHERE v.marque = 'Ferrari'
);

--Les trentenaires ont droit à une Ferrari !
INSERT INTO vehicule (marque, modele, proprietaire_id)
SELECT 'Ferrari', 'F12', id as proprietaire_id
FROM proprietaire WHERE YEAR(date_naissance) = 1987;

UNION

--Proriétaires de Citroën et/ou de Peugeot
SELECT p.* FROM test.proprietaire p
INNER JOIN test.vehicule v ON v.proprietaire_id = p.id 
WHERE v.marque = 'Citroën'
UNION
SELECT p.* FROM test.proprietaire p
INNER JOIN test.vehicule v ON v.proprietaire_id = p.id 
WHERE v.marque = 'Peugeot'

Vues

CREATE [OR REPLACE] VIEW view_name 
  [(column_list)]
  AS select_statement 



--Exemple
CREATE VIEW proprietaire_vehicule (nom, prenom, marque, modele, immatriculation) AS
SELECT p.nom, p.prenom, v.marque, v.modele, v.immatriculation FROM proprietaire p
INNER JOIN vehicule v ON v.proprietaire_id = p.id;

SELECT * FROM proprietaire_vehicule WHERE marque = 'BMW';
nom prenom marque modele immatriculation
Girard Isabelle BMW Série 4 BB-456-CC
... ... ... ... ...

Expressions et fonctions

Expression ou fonction Description Syntaxe
SUM, AVG Calcule la somme ou la moyenne de valeurs numériques
SELECT AVG(prix), SUM(prix) FROM vehicule
MIN, MAX Récupère le minimum ou le maximum d'une ensemble de valeurs
SELECT MIN(date_mise_circulation), MAX(date_mise_circulation) FROM vehicule
CEIL, FLOOR, ROUND Effectue un arrondit (inférieur, supérieur et normal) sur une valeur numérique
SELECT FLOOR(3.14), CEIL(3.14), ROUND(3.14) --3, 4, 3
CONCAT, UPPER, LOWER, SUBSTRING Concatène, met en majuscule ou minuscule, extrait une partie d'une chaîne
SELECT CONCAT(UPPER('hello'), LOWER(' WORLD '), SUBSTRING('!ZZ',1,1)) --HELLO world !
LIKE, REGEXP Teste le contenu d'une chaîne de caractère
SELECT * FROM vehicule WHERE immatriculation LIKE 'AA%' AND modele REGEXP '^Série [0-9]{1}$'
CURDATE ou CURTIME Récupère la date ou l'heure courante
SELECT CURDATE(), CURTIME() --, 
YEAR, MONTH, DAYOFMONTH Extrait l'année, le mois ou le jour d'une date
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE()) --, , 
IF Teste une condition
SELECT IF(marque = 'Ferrari', 'Riche', 'Moins riche') FROM vehicule
IFNULL, NULLIF Renvoie une valeur si l'expression est nulle, null si la valeur est égale à une autre
SELECT IFNULL(proprietaire_id, 'Sans propriétaire'), NULLIF(marque, 'Lada') FROM vehicule
CASE Renvoie une valeur en fonction de cas différents
SELECT CASE marque WHEN 'Ferrari' THEN 'Riche' WHEN 'Lada' THEN 'Lol' ELSE '?' END
FROM vehicule

Serveur

  • Procédures stockées
  • Fonctions
  • Triggers
  • Requêtes préparées

Flot d'exécution et variables

Element Description Syntaxe
Variables Une variable est définir par un type et éventuellement une valeur par défaut.
DECLARE monEntier INT DEFAULT 0; 
SET monEntier = 1;
CASE Exécute les traitements en fonction de conditions
CASE monEntier
  WHEN 0 THEN ...
  WHEN 1 THEN ...
  ELSE ...
END CASE;
IF Exécute les traitements en fonction de conditions
IF monEntier > 0 THEN 
  ... 
ELSEIF monEntier < 0 THEN 
  ... 
ELSE 
  ... 
END IF;
Element Description Syntaxe
LOOP et LEAVE et ITERATE Exécute les traitements tant qu'il n'y a pas une sortie explicite de la boucle avec un LEAVE ou un RETURN par exemple.
etiquette: LOOP 
  IF monEntier = 0 THEN 
    LEAVE etiquette;
  ELSE
    ITERATE etiquette;
  END IF; 
  SET x = 1;
END LOOP;
WHILE Exécute les traitements tant qu'une condition n'est pas atteinte
WHILE x > 0 DO
    ...
END WHILE;

Procédures stockées

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type
routine_body:
    Requête(s) SQL valides

--Exemple
DELIMITER |

CREATE PROCEDURE supprime_vehicule_sans_immat(OUT nb_immat_null INT)      
BEGIN
  SELECT COUNT(*) INTO nb_immat_null FROM vehicule WHERE immatriculation IS NULL;
  DELETE FROM vehicule WHERE immatriculation IS NULL; 
END|
DELIMITER ;

CALL supprime_vehicule_sans_immat(@nb_immat_null);
SELECT @nb_immat_null;

Fonctions

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type routine_body
func_parameter: param_name type
type:           Type SQL valide
routine_body:   Requêtes SQL valides

--Exemple
DELIMITER |
CREATE FUNCTION format_immat(t VARCHAR(50)) RETURNS VARCHAR(9)
BEGIN
  IF t REGEXP '^[a-zA-Z]{2}[0-9]{3}[a-zA-Z]{2}$' THEN
    RETURN UPPER(CONCAT(SUBSTR(t,1,2), '-',SUBSTR(t,3,3), '-', SUBSTR(t,5,2)));
  ELSE
    RETURN 'INCORRECT';
  END IF;
END|
DELIMITER ;

SELECT format_immat('aa123bb');
--affiche AA-123-BB

Triggers

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
    [trigger_order] trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
trigger_body:
    Requête(s) SQL valides

--Exemple
DELIMITER |
CREATE TRIGGER commentaire_insert BEFORE UPDATE ON vehicule FOR EACH ROW
  BEGIN
    SET NEW.commentaire = CONCAT('Immat précédente : ', OLD.immatriculation);    
  END|
DELIMITER ;
INSERT INTO vehicule (...) VALUES ('Peugeot', '208', 'AA-123-BB', '2017-10-02');
UPDATE vehicule SET immatriculation = 'BB-456-CC' WHERE id = 5;
--dans le champ commentaire : Immat précédente : AA-123-BB

Utilisation des fonctions, triggers et procédures stockées

  • Questions ou reflexions
  • Il est important que ce code soit portable, testable et versionné
  • Ce code effectue des traitements purement fonctionnels
  • Ce code doit être évolutif
  • Ce code n'est utilisé qu'à un seul endroit
  • L'ensemble des clients est maîtrisé
  • Le serveur a une capacité de calcul limitée
  • Ce code est utilisé par plusieurs clients différents
  • Ce code effectue des traitements purement techniques
  • Le client a une capacité de calcul limitée
  • Vous ne maîtriser pas tous les clients
Triggers Procédures stockées Fonctions

Requêtes préparées

PREPARE stmt_name FROM preparable_stmt

EXECUTE stmt_name [USING @var_name [, @var_name] ...]

DEALLOCATE PREPARE stmt_name

--Exemple
PREPARE vehicule_par_marque_modele FROM 
SELECT * FROM vehicule WHERE marque = ? AND modele = ?;

SET @marque = 'BMW'; SET @modele = 'Série 3';
EXECUTE vehicule_par_marque_modele USING @marque, @modele;

SET @modele = 'Série 5';
EXECUTE vehicule_par_marque_modele USING @marque, @modele;

DEALLOCATE vehicule_par_marque_modele;

Importer ou exporter une base

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --all-databases

#Exemples
#Exporter la base db_name dans un fichier SQL
mysqldump db_name > backup-file.sql

#Importer un dump dans une base
mysqlimport -u root -p db_name backup-file.sql

Gestion des utilisateurs

  • user: Comptes utilisateurs et privilèges globaux
  • db: Privilèges niveau base
  • tables_priv: Privilèges niveaux table
  • columns_priv: Privilèges niveaux colonnes
  • procs_priv: Privilèges sur les procédures stockées et les fonctions
CREATE USER [IF NOT EXISTS] 'user'@'host' IDENTIFIED BY 'password';
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';
GRANT ALL ON base.* TO 'user'@'host';
REVOKE INSERT ON base.* FROM 'user'@'host';
DROP USER 'user'@'host';

La réplication

  • La sécurité : Les données sont dupliquées à un ou plusieurs endroits.
  • La performance : Les données peuvent être analysés sur les esclaves sans affecter les performances du maître.
  • La scalabilité : Les données peuvent être lues sur les esclaves en laissant les opérations de mises à jour sur le maître.

Le partitionnement

  • La taille : Les éventuelles contraintes de taille sur les tables sont contournées.
  • La performance : Les données sont réparties entre plusieurs bases pour équilibrer la charge.

Les clusters

  • La tolérance aux pannes : Les données sont toujours disponibles à condition qu'au moins un nœud soit accessible.
  • La performance : Les requêtes peuvent être lancées en parallèle sur plusieurs noeuds.

Curseurs

DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_name
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name

--Exemple
CREATE PROCEDURE ex_vehicules() BEGIN
  DECLARE v_id, v_incr, v_fin INT DEFAULT 0;
  DECLARE c_vehicules CURSOR FOR SELECT id FROM vehicule ORDER BY immatriculation;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fin = 1;
  OPEN c_vehicules;
  loop_vehicules: LOOP
    SET v_incr = v_incr + 1;
    FETCH FROM c_vehicules INTO v_id;
    IF v_fin = 1 THEN LEAVE loop_vehicules; END IF;
    UPDATE vehicule SET commentaire = CONCAT('Véhicule n°', v_incr) WHERE id = v_id;
  END LOOP;
  CLOSE c_vehicules;
END|
CALL ex_vehicules;