SQL (Structured Query Language)

rappel sur les SGBDR

Les Systèmes de Gestion de Base de Données Relationnelles (SGBDR) permettent d’avoir :

Lien sur un classement des SGBD

Les langages que l’on utilise classiquement (java) sont des langages dit “procédural” ou “impératif”
SQL est un langage “déclaratif” : on ne définit pas d’étape pour arriver au résultat, on décrit le résultat

Dans la documentation, SQL est composé de “sous ensembles”, “sous langages”


Cas particulier :

Le NOSQL, exemple “MongoDB”


configuration et stockage de données

mysql> select @@datadir;

mysql>SHOW VARIABLES LIKE "%datadir%";

mysql>SHOW VARIABLES LIKE "%dir%";

mysql --help | grep cnf

(sur windows (gemini) : mysql --help | findstr /i "cnf")

 2001  sudo xed /etc/mysql/my.cnf
 2002  sudo xed /etc/mysql/mariadb.conf.d/50-server.cnf

 1142  ls /var/lib/mysql/
 1143  sudo ls /var/lib/mysql/
 1144  sudo ls /var/lib/
 1145  sudo ls /var/lib/mysql
 1146  sudo ls /var/lib/mysql/S3_BDD
 1147  sudo ls /var/lib/mysql/S2_BDD
 1148  sudo cat /var/lib/mysql/S2_BDD/ADHERENT.ibd

 1149  sudo cat /var/lib/mysql/S2_BDD/ADHERENT.frm

https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

les types de langages (sous ensembles de commandes, instructions) en SQL

Connaître ces mots clés facilite la recherche d’informations dans la documentation d’un SGBDR.

origine du SQL : algèbre relationnelle

Le SQL est issu de l’Algèbre Relationnelle introduite par CODD en 1970 (IBM). L’Algèbre Relationnelle permet de formaliser les opérations sur les ensembles. C’est une approche un peu plus mathématique avec un formalisme un peu différent.

                  -- ALGEBRE RELATIONNELLE      
SELECT            -- PROJECTION                     
FROM                                               
WHERE             -- RESTRICTION                   
GROUP BY
HAVING
ORDER BY


Création d’une table

les clés

l’Intégrité référentielle est une situation dans laquelle pour chaque information d’une table A qui fait référence à une information d’une table B, l’information référencée existe dans la table B. L’intégrité référentielle est un gage de cohérence du contenu de la base de données.


Exemple particulier : Un employé passe des commandes et quitte l’entreprise. Si on supprime l’employé de la base de données, on a plus de référence avec ses commandes. Une solution consiste à ajouter un champ départ pour signaler la date à laquelle l’employé a quitté l’entreprise.


schéma




Tester la commande ci dessous :

SELECT * FROM INFORMATION_SCHEMA.TABLES;


TEST : accès aux données d’une BDD à une autre BDD


RAPPEL : SELECT * FROM schema_name.table_name;

types de données en SQL

Documentations

les dates

format dates voir les exemples de la documentation ci-dessus (sensiblement identiques dans les différents SGBDR)

type “date/heure”

Access MySQL Oracle PostgreSQL
date date date date
time time date time
datetime datetime date timestamp

les numériques


type “Numérique exact : entier”

Access MySQL Oracle PostgreSQL
int int number(10) integer
byte tinyint number(3) smallint
smallint smallint number(5) smallint


type “Numérique exact : avec virgule”

Access MySQL Oracle PostgreSQL SQL server
decimal(n,d) decimal(n,d) number(n) numeric(n) decimal(n,d)


type “Numérique approximatif” : réel

Access MySQL Oracle PostgreSQL SQL server
real float binary_float real float
double doble binary_double double precision real


Pour enregistrer une donnée qui est un numérique avec une virgule comme un prix, utiliser de préférence un type numérique exact (DECIMAL ou NUMBER(ORACLE)), sinon la valeur stockée ne sera pas exacte.

les chaînes de caractères

type “TEXTE”

Access MySQL Oracle PostgreSQL
varchar(n) varchar(n) varchar2(n) varchar(n)
char(n) char(n) char(n) char(n)
text text text clob

http://www.chine-nouvelle.com/outils/dictionnaire.html

Tester la requête ci-dessous:

SELECT '你好';

Le TD suivant porte sur les problèmes liés aux chaînes de caractères

gros objets

les booleens

valeur “vrai” ou “faux” codée sur un octet

Access MySQL Oracle PostgreSQL SQL server
logical tinyint(1) number(1) booleen byte

les autres types

currency : valeur monétaire codée sur 8 octets comprenant 15 chiffres + 4 décimales

Access MySQL Oracle PostgreSQL SQL server
currency decimal(19,4) number(19,4) money money

Propriété AUTO_INCREMENT sur MSQL

AUTO_INCREMENT : on doit mettre à NULL le champ lors de la création d’un enregistrement. Il est interdit de mettre une valeur sur SQLSERVER, c’est autorisé sur MYSQL.

Access MySQL Oracle PostgreSQL SQL server
AUTOINCREMENT int AUTO_INCREMENT number(10) + trigger serial identity(seed,increment)

documentation mariadb

ORACLE : c’est plus compliqué, utilisation d’un mécanisme de séquenceur (objet qui gère l’incrémentation des numéros => déclencheur(comme un TRIGGER qui incrémente ce numéro)


Ce mot clé prend une forme différente selon le SGBDR, voir AUTO_INCREMENT ou sur postgresql 

DROP TABLE IF EXISTS TD_demo1;
CREATE TABLE TD_demo1(
id INT AUTO_INCREMENT,
CONSTRAINT PK_id_TD_demo1 PRIMARY KEY (id)
);

INSERT INTO TD_demo1 VALUES (NULL);
INSERT INTO TD_demo1 VALUES (NULL);
SELECT LAST_INSERT_ID();

SELECT * FROM TD_demo1;
SHOW CREATE TABLE TD_demo1;

DELETE FROM TD_demo1;

INSERT INTO TD_demo1 VALUES (NULL);
INSERT INTO TD_demo1 VALUES (NULL);
SELECT LAST_INSERT_ID();

SELECT * FROM TD_demo1;
SHOW CREATE TABLE TD_demo1;

TRUNCATE TABLE  TD_demo1;

INSERT INTO TD_demo1 VALUES (NULL);
SELECT LAST_INSERT_ID();

SELECT * FROM TD_demo1;
SHOW CREATE TABLE TD_demo1;
DROP TABLE IF EXISTS TD_demo1;

rôle de : ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 en fin de création de table



LAST_INSERT_ID

SELECT LAST_INSERT_ID();

TEST :

DROP TABLE IF EXISTS TD_demo1;
CREATE TABLE TD_demo1(
id SERIAL
);
DESCRIBE TD_demo1;
SHOW CREATE TABLE TD_demo1;
INSERT INTO TD_demo1 VALUES (NULL);
DROP TABLE IF EXISTS TD_demo1;

REMARQUE : ATTENTION, les clés étrangéres doivent avoir le même type que les clés primaires

les clés étrangéres

-- pas d'utilisation de compteur : AUTO_INCREMENT

DROP TABLE IF EXISTS TD_etudiant;
DROP TABLE IF EXISTS TD_groupe;

CREATE TABLE TD_groupe (
    idGroupe int
    , libelle varchar(25)
    , PRIMARY KEY (idGroupe)
);
SHOW CREATE TABLE TD_groupe;

CREATE TABLE TD_etudiant (
    noEtudiant int
    , nomEtudiant varchar(25)
    , idGroupe int
    , PRIMARY KEY (noEtudiant)
    , CONSTRAINT fk_TD_etudiant_groupe
        FOREIGN KEY (idGroupe) 
        REFERENCES TD_groupe(idGroupe)
);
SHOW CREATE TABLE TD_etudiant;

INSERT INTO TD_groupe VALUES(1,'S2A1');  -- id 1
INSERT INTO TD_groupe VALUES(2,'S2A2'); -- id 2
INSERT INTO TD_groupe VALUES(3,'S2bisA1'); -- id 3

INSERT INTO TD_etudiant VALUES(1,'paul',1);
INSERT INTO TD_etudiant VALUES(2,'pierre',2);
INSERT INTO TD_etudiant VALUES(3,'toto',3);

SELECT * FROM TD_etudiant;
UPDATE TD_groupe SET idGroupe=25 WHERE idGroupe=2;
SELECT * FROM TD_etudiant;
DELETE FROM  TD_groupe  WHERE idGroupe=1;
SELECT * FROM TD_etudiant;
SELECT * FROM TD_groupe;

DROP TABLE IF EXISTS TD_etudiant;
DROP TABLE IF EXISTS TD_groupe;
SHOW TABLES;






UUID : universally Unique Identifier

wikipedia : Il existe plusieurs algorithmes (versions) pour calculer un UUID


UUID existe en python, javascript

documentation SQL uuid ; SQL uuid type


DROP TABLE IF EXISTS TD_etudiant;


CREATE TABLE TD_etudiant (
    noEtudiant varchar(255)
    , nomEtudiant varchar(25)
    , idGroupe int
    , PRIMARY KEY (noEtudiant)
);
SHOW CREATE TABLE TD_etudiant;


INSERT INTO TD_etudiant VALUES(UUID(),'paul',1);
INSERT INTO TD_etudiant VALUES(UUID(),'pierre',2);
INSERT INTO TD_etudiant VALUES(UUID(),'toto',3);

SELECT * FROM TD_etudiant;

DROP TABLE IF EXISTS TD_etudiant;
SHOW TABLES;

Avantages :


Inconvénient :


Un peu plus loin :








UUID vs AUTO_INCREMENT dans MySQL

Le problème de performance des UUID

Les UUID classiques (v4) sont aléatoires, ce qui pose un problème majeur avec les index B-Tree de MySQL (InnoDB) : chaque insertion se fait à une position aléatoire dans l’index, causant des page splits fréquents et une fragmentation importante. Les UUID stockés en VARCHAR(36) aggravent encore la situation (36 octets vs 4-8 octets pour un entier).

MySQL 8.0 introduit UUID_TO_BIN(uuid, 1) qui réordonne les bits du timestamp pour rendre l’UUID ordonné (similaire à UUID v1), ce qui atténue fortement le problème de fragmentation.

Tableau comparatif

Critère AUTO_INCREMENT UUID
Taille de stockage 4 octets (INT) / 8 octets (BIGINT) 16 octets (BIN) / 36 octets (VARCHAR)
Performance INSERT Excellente — insertion séquentielle en fin d’index Mauvaise (v4 aléatoire) / Bonne (v7 ou UUID_TO_BIN(...,1))
Performance SELECT Très bonne — index compact et dense Moins bonne — index plus lourd
Fragmentation index Quasi nulle Élevée (UUID v4) / Faible (UUID ordonné)
Unicité globale (multi-BDD) Non — collision si merge de bases Oui — globalement unique
Sécurité / prédictibilité Prédictible (enumerable) Opaque — difficile à deviner
Scalabilité horizontale / sharding Complexe — nécessite un mécanisme centralisé Natif — génération décentralisée sans coordination
Portabilité applicative Couplé à la BDD pour la génération Générable côté applicatif avant insertion
Lisibilité / debug Facile (id=42) Peu lisible (550e8400-e29b-41d4...)
Taille des clés étrangères Petite → jointures rapides Grande → jointures plus lentes
Réplication / merge Risque de conflits Aucun risque

Recommandations pratiques

Utiliser AUTO_INCREMENT si tu as une application monolithique, des volumes importants avec beaucoup d’écritures, et que la performance est prioritaire.

Utiliser UUID si tu fais du sharding, de la réplication multi-master, de la synchronisation offline, ou si tu veux éviter l’exposition d’IDs séquentiels dans les URLs.

Le meilleur des deux mondes : utiliser un UUID v7 (ordonné par timestamp, disponible nativement depuis MySQL 8.4, ou via lib applicative) ou UUID_TO_BIN(UUID(), 1) stocké en BINARY(16) — tu gardes l’unicité globale tout en minimisant la fragmentation.

UUID_TO_BIN et BIN_TO_UUID — Guide pratique

Pourquoi ces fonctions ?

Stocker un UUID en VARCHAR(36) est coûteux. Ces fonctions permettent de le convertir en BINARY(16) (16 octets au lieu de 36), et le second paramètre 1 réordonne les bits pour rendre l’UUID séquentiel.

Création de la table

CREATE TABLE users (
    id        BINARY(16)   NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
    username  VARCHAR(100) NOT NULL,
    email     VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT

-- Laisser MySQL générer l'UUID
INSERT INTO users (username, email)
VALUES ('jean_dupont', 'jean@example.com');

-- Ou générer l'UUID explicitement
INSERT INTO users (id, username, email)
VALUES (UUID_TO_BIN(UUID(), 1), 'marie_curie', 'marie@example.com');

-- Ou depuis un UUID applicatif (ex: généré en PHP, Python, etc.)
INSERT INTO users (id, username, email)
VALUES (UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000', 1), 'albert', 'albert@example.com');

SELECT — reconvertir en lisible

-- Récupérer l'UUID lisible
SELECT BIN_TO_UUID(id, 1) AS id, username, email
FROM users;

-- Résultat :
-- 
+--------------------------------------+-------------+--------------------+
-- | id                                   | username    | email           |
-- 
+--------------------------------------+-------------+--------------------+
-- | 550e8400-e29b-41d4-a716-446655440000 | albert      | albert@example.com |
-- 
+--------------------------------------+-------------+--------------------+

WHERE — rechercher par UUID

-- Toujours convertir l'UUID dans le WHERE pour utiliser l'index
SELECT BIN_TO_UUID(id, 1) AS id, username
FROM users
WHERE id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000', 1);

⚠️ Ne jamais faire WHERE BIN_TO_UUID(id) = '...' — cela empêche l’utilisation de l’index (full scan).

Le paramètre swap_flag (le 1)

UUID_TO_BIN(uuid, 1)  -- swap activé  : réordonne le timestamp → UUID séquentiel ✅
UUID_TO_BIN(uuid, 0)  -- swap désactivé : stockage brut, reste aléatoire ❌
Sans swap (0) Avec swap (1)
Ordre en BDD Aléatoire Séquentiel (chronologique)
Fragmentation index Élevée Faible
Compatible UUID v1 Non Oui (optimisé pour v1)
Compatible UUID v4 Stockage uniquement Stockage uniquement (pas de gain séquentiel)

Le swap n’a un vrai intérêt séquentiel qu’avec des UUID v1 (basés sur le timestamp). Avec UUID v4 (aléatoire), le swap ne change rien à l’ordre.

Exemple complet avec une Vue

Pour éviter de jongler avec les conversions partout dans le code, on peut créer une vue :

CREATE VIEW v_users AS
SELECT
    BIN_TO_UUID(id, 1) AS id,
    username,
    email
FROM users;

-- Utilisation transparente
SELECT * FROM v_users;

Côté applicatif (exemple Python)

import uuid
import mysql.connector

new_uuid = str(uuid.uuid1())  # UUID v1 pour profiter du swap

cursor.execute(
    "INSERT INTO users (id, username) VALUES (UUID_TO_BIN(%s, 1), %s)",
    (new_uuid, 'jean')
)

# En lecture
cursor.execute("SELECT BIN_TO_UUID(id, 1), username FROM users")