Manipulation des bases de données

Afficher les bases existantes

SHOW DATABASES;

Points importants : Le langage SQL ne fait pas de distinction entre majuscules et minuscules, les instructions se terminent par un point-virgule anglais (;), et le mot-clé est DATABASES au pluriel.

Créer une base de données

CREATE DATABASE [IF NOT EXISTS] nom_base;

Note : Le mot DATABASE est au singulier, et les crochets indiquent une clause optionnelle.

Sélectionner une base de données

USE nom_base;

Supprimer une base de données

DROP DATABASE nom_base;

Types de données courants

Manipulation des tables

Lister les tables de la base courante

SHOW TABLES;

Créer une table

CREATE TABLE employe (
    id INT,
    prenom VARCHAR(30),
    salaire DECIMAL(10,2)
);

Afficher la structure d'une table

DESCRIBE employe;

Supprimer une table

DROP TABLE employe;

Opérations CRUD sur les tables

ENSERT (Ajout de données)

Insertion de plusieurs lignes :

INSERT INTO employe VALUES
(1, 'Alice', 3200.50),
(2, 'Bob', 2800.75);

Insertion à partir d'une requête :

INSERT INTO archive_employe
SELECT * FROM employe WHERE salaire > 3000;

SELECT (Requêtes)

Sélection avec tri et filtres :

SELECT prenom, salaire
FROM employe
WHERE salaire BETWEEN 2000 AND 3500
ORDER BY salaire DESC
LIMIT 10;

Requête d'agrégation avec GROUP BY :

SELECT departement, AVG(salaire) AS salaire_moyen
FROM employe
GROUP BY departement
HAVING salaire_moyen > 2500;

Jointures

Jointure interne (INNER JOIN) :

SELECT e.prenom, d.nom_departement
FROM employe e
INNER JOIN departement d ON e.id_dept = d.id;

Jointure externe gauche (LEFT JOIN) :

SELECT e.prenom, d.nom_departement
FROM employe e
LEFT JOIN departement d ON e.id_dept = d.id;

UPDATE (Mise à jour)

UPDATE employe
SET salaire = salaire * 1.10
WHERE departement = 'Informatique';

DELETE (Suppression)

DELETE FROM employe
WHERE date_embauche < '2020-01-01';

Propriétés et contraintes des colonnes

• PRIMARY KEY : Identifiant unique, non nul.
• FOREIGN KEY : Clé référençant la clé primaire d'une autre table.
• UNIQUE : Valeur unique dans la colonne (plusieurs NULL possibles).
• NOT NULL : La colonne ne peut pas contenir NULL.
• DEFAULT valeur : Valeur par défaut si aucune n'est fournie.
• AUTO_INCREMENT : Incrémentation automatique pour les entiers.

Indexation

Les index accélèrent les requêtes de lecture mais ralentissent les écritures et occupent de l'espace. Ils sont créés automatiquement pour les clés primaires, uniques et étrangères.

CREATE INDEX idx_nom ON employe(prenom);
SHOW INDEX FROM employe;
DROP INDEX idx_nom ON employe;

Transactions

Une transaction regroupe plusieurs opérations SQL en une unité logique indivisible (propriété ACID).

START TRANSACTION;

UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = solde + 100 WHERE id = 2;

-- Si tout s'est bien passé
COMMIT;
-- Sinon
ROLLBACK;

Niveaux d'isolation

1. READ UNCOMMITTED : Permet la lecture de données non validées (« dirty reads »).
2. READ COMMITTED : Empêche les dirty reads mais autorise les lectures non répétables.
3. REPEATABLE READ (défaut MySQL) : Garantit la cohérence des lectures au sein d'une transaction.
4. SERIALIZABLE : Isolation maximale, performances réduites.

Opérations JDBC de base

// 1. Création de la source de données
MysqlDataSource ds = new MysqlDataSource();
ds.setURL("jdbc:mysql://localhost:3306/ma_base?serverTimezone=Europe/Paris");
ds.setUser("utilisateur");
ds.setPassword("mot_de_passe");

// 2. Établissement de la connexion
Connection conn = ds.getConnection();

// 3. Préparation et exécution d'une requête avec paramètres
String requete = "INSERT INTO produits (nom, prix) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(requete);
ps.setString(1, "Clavier");
ps.setDouble(2, 29.99);
int lignesAffectees = ps.executeUpdate();

// 4. Exécution d'une requête SELECT et parcours des résultats
String sql = "SELECT id, nom FROM produits WHERE prix < ?";
PreparedStatement psSelect = conn.prepareStatement(sql);
psSelect.setDouble(1, 50.0);
ResultSet rs = psSelect.executeQuery();

while (rs.next()) {
    int id = rs.getInt("id");
    String nom = rs.getString("nom");
    // Traitement des données...
}

// 5. Libération des ressources dans l'ordre inverse
rs.close();
psSelect.close();
ps.close();
conn.close();