Questions théoriques

1. Moteurs de stockage MySQL : InnoDB vs. MyISAM et autres

InnoDB (par défaut) : Orienté OLTP, transactions, verrouillage au niveau des lignes, clés étrangères, lecture non bloquante.

MyISAM : Pas de transactions, verrouillage au niveau des tables, index de recherche plein texte, orienté OLAP.

NDB : Cluster haute disponibilité et performance pour OLTP.

Memory : Données en mémoire, perdues au redémarrage/crash.

Infobright : Stockage orienté colonnes.

TSE : Compression, cache au niveau des lignes, support transactionnel orienté mémoire à venir.

BLACKHOLE : Réplication de distribution pour les maîtres.

2. CHAR vs. VARCHAR dans MySQL

Les deux sont des types de chaînes. VARCHAR(50) peut stocker jusqu'à 50 caractères. CHAR(50) stocke 50 caractères, remplis si nécessaire.

CHAR : Longueur fixe. Rapide pour la lecture/écriture, moins économe en espace. Idéal pour des données de longueur constante.

VARCHAR : Longueur variable. Plus économe en espace, mais plus lent pour la lecture/écriture. Stocke un en-tête indiquant la longueur réelle des données.

Conseil : Pour l'efficacité, concevoir les colonnes avec CHAR avant VARCHAR dans certains cas.

3. Taille de stockage du type INT

INT utilise 4 octets (32 bits).

Avec signe : -2^31 à 2^31 - 1.

Sans signe : 0 à 2^32.

4. Caractéristiques d'une clé primaire

Unique et non nulle.

5. Compréhension des jointures SQL

• INNER JOIN : Affiche les lignes correspondantes des deux tables.
• LEFT JOIN : Affiche toutes les lignes de la table de gauche et les lignes correspondantes de la table de droite. Les lignes non correspondantes de gauche auront des valeurs NULL pour les colonnes de droite.
• RIGHT JOIN : Affiche toutes les lignes de la table de droite et les lignes correspondantes de la table de gauche. Les lignes non correspondantes de droite auront des valeurs NULL pour les colonnes de gauche.
• FULL JOIN : (Non supporté nativement par MySQL, peut être simulé avec UNION) Affiche les lignes correspondantes des deux tables, ainsi que les lignes non correspondantes de chaque table.

6. Fonctions CONCAT et GROUP\_CONCAT

CONCAT : Concatène des chaînes de caractères.

GROUP_CONCAT : Utilisée avec GROUP BY pour agréger des valeurs de plusieurs lignes dans une seule chaîne.

7. Principe des transactions

Regroupe plusieurs instructions SQL atomiques. Soit toutes réussissent (commmit), soit aucune n'est appliquée (rollback).

8. Principes, avantages et inconvénients des index

Essence : Créer une structure de données (par exemple, un arbre) qui permet de localiser rapidement les lignes sans parcourir toute la table.

Avantages : Amélioration significative de la vitesse de recherche.

Inconvénients : Prend de l'espace disque ; ralentit les opérations d'écriture (INSERT, UPDATE, DELETE) car l'index doit aussi être mis à jour.

9. Quand créer des index (et quand ne pas le faire)

Créer : Grandes tables, requêtes fréquentes sur des colonnes spécifiques, structure de table relativement stable.

Ne pas créer : Petites tables, tables fréquemment mises à jour (INSERT/UPDATE/DELETE), colonnes rarement utilisées dans les clauses WHERE ou JOIN.

10. ACID et scénarios d'utilisation des transactions

ACID :

• Atomicité : Opération indivisible.
• Consistance : La base de données passe d'un état valide à un autre.
• Isolation : Les transactions concurrentes n'interfèrent pas les unes avec les autres.
• Durabilité : Une fois validée, la transaction est permanente.

Scénarios nécessitant des transactions : Systèmes bancaires, calculs financiers critiques, opérations impliquant plusieurs étapes interdépendantes.

Scénarios n'en nécessitant pas : Requêtes de lecture simples, opérations d'analyse de données non critiques.

11. Déclencheurs (Triggers)

Instructions exécutées automatiquement en réponse à des événements (INSERT, UPDATE, DELETE) sur une table. Ne sont pas déclenchés par les requêtes SELECT.

Utilisation : Journalisation, validation de données complexes avant insertion/modification.

12. Procédures stockées

Blocs de code SQL précompilés stockés dans la base de données. Peuvent être appelés par leur nom.

Aavntages : Réduction du trafic réseau, réutilisabilité, encapsulation de la logique métier côté base de données.

Inconvénients : Moins de flexibilité pour les développeurs, dépendance vis-à-vis des administrateurs de bases de données pour les modifications.

13. Vues (Views)

Tables virtuelles basées sur le résultat d'une requête SQL. Permettent de simplifier des requêtes complexes et de contrôler l'accès aux données.

Avantages : Simplification, sécurité (masquage de colonnes/lignes). Ne peuvent généralement pas être modifiées directement (les modifications affectent la table sous-jacente).

Utilisation : Rapports fréquents, vues personnalisées pour différents utilisateurs.

14. Interprétation du plan d'exécution SQL (EXPLAIN)

Commande EXPLAIN : Outil essentiel pour optimiser les requêtes SQL et la conception des index.

id : Ordre d'exécution des sélections.

select_type : Type de requête (SIMPLE, PRIMARY, SUBQUERY, etc.).

table : Table accédée.

partitions : Partitions utilisées (si applicable).

type : Méthode d'accès aux données (le plus efficace au moins efficace : system, const, eq_ref, ref, range, index, ALL).

possible_keys : Index potentiellement utilisables.

key : Index réellement utilisé.

key_len : Longueur de la partie de l'index utilisée.

ref : Colonne ou constante comparée à l'index.

rows : Nombre estimé de lignes à examiner.

filtered : Pourcentage de lignes filtrées par le WHERE.

Extra : Informations supplémentaires (Using index, Using temporary, Using filesort).

• Using temporary : Nécessite une table temporaire (souvent lent).
• Using filesort : Nécessite un tri supplémentaire (souvent lent).
• Using index : Les données sont récupérées uniquement depuis l'index (rapide).

15. Interprétation du plan d'exécution pour SELECT \* FROM student

(Basé sur l'exemple fourni, l'interprétation sera générale)

• id : Ordre d'exécution.
• select_type : Type de requête.
• table : Nom de la table (student).
• partitions : NULL si non partitionnée.
• type : Type d'accès (par ex., ALL si pas d'index utilisé).
• possible_keys : Index potentiels.
• key : Index utilisé (NULL si aucun).
• key_len : Longueur de l'index utilisé.
• ref : Colonne ou constante de référence.
• rows : Nombre estimé de lignes à lire.
• filtered : Pourcentage après filtrage WHERE.
• Extra : Informations supplémentaires.

16. Types de sauvegarde de données

• Complète : Sauvegarde de toutes les données. Lente à sauvegarder, rapide à restaurer (une seule étape).
• Incrémentielle : Sauvegarde uniquement des données modifiées depuis la *dernière* sauvegarde (quelle qu'elle soit). Rapide à sauvegarder, lente à restaurer (plusieurs étapes nécessaires).
• Différentielle : Sauvegarde uniquement des données modifiées depuis la *dernière sauvegarde complète*. Plus lente à sauvegarder et plus volumineuse que l'incrémentielle, mais plus rapide à restaurer (deux étapes : dernière complète + dernière différentielle).

17. Ordre d'exécution des clauses SQL (SELECT)

L'ordre logique d'exécution est différent de l'ordre d'écriture:

1. FROM (et les JOIN)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT

18. Scénarios d'utilisation pour MyISAM et InnoDB

MyISAM : Scénarios à lecture intensive, calculs complexes, où la vitesse de récupération est primordiale et l'intégrité transactionnelle n'est pas critique.

InnoDB : Applications nécessitant une haute intégrité des données, transactions, et où la fiabilité est plus importante que la vitesse brute de lecture (par ex., e-commerce, systèmes financiers).

19. Types de données MySQL courants

• Numériques : INT, FLOAT (pour les montants).
• Chaînes : CHAR (fixe), VARCHAR (variable).
• Dates : DATETIME.
• Spéciaux : ENUM (choix unique), SET (choix multiple).

20. Produit Cartésien et comment l'éviter

Cause : Jointure de plusieurs tables sans condition de jointure appropriée (par exemple, SELECT * FROM table1, table2;).

Éviter : Utiliser explicitement INNER JOIN, LEFT JOIN, RIGHT JOIN avec des conditions ON claires, ou ajouter une clause WHERE pour filtrer les résultats.

21. Fonctions MySQL courantes

• Mathématiques : ROUND().
• Agrégation : COUNT(), AVG(), MAX(), MIN(), SUM().
• Chaînes : CONCAT().
• Dates : CURRENT_TIME().
• Contrôle : IF(), CASE.

22. Scénarios d'utilisation de GROUP BY

Utilisé pour regrouper des lignes ayant les mêmes valeurs dans une ou plusieurs colonnes, afin d'appliquer des fonctions d'agrégation (COUNT, SUM, AVG, etc.) sur chaque groupe.

23. Principes des index Hash et B+ Tree

Index Hash : Utilise une fonction de hachage pour calculer une valeur unique à partir de la clé. Permet un accès en temps O(1) (très rapide) pour les égalités exactes. Moins efficace pour les recherches par intervalle (<, >).

Index B+ Tree : Structure arborescente équilibrée où tous les enregistrements sont stockés dans les feuilles, et les feuilles sont liées entre elles. Permet des recherches efficaces pour les égalités et les intervalles (<, >, BETWEEN). C'est le type d'index le plus courant dans les SGBD.

Questions de programmation

1. Création de la table student

Crée une table student avec un ID auto-incrémenté comme clé primaire, un nom de longueur variable, un champ sexe énuméré et un numéro de carte de crédit de longueur variable.

CREATE TABLE student (
   ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
   sname VARCHAR(255), -- Ajuster la taille selon les besoins
   gender ENUM('M', 'F') DEFAULT 'M', -- Utilisation de M/F pour plus de clarté
   credit VARCHAR(50) -- Taille ajustée
);
   

2. Ajout d'une clé étrangère class\_id

Ajoute une colonne class\_id à la table student et la lie à la colonne cid de la table class. Les suppressions/mises à jour en cascade sont configurées.

-- Assurez-vous que la table 'class' existe
CREATE TABLE IF NOT EXISTS class (
   cid INT AUTO_INCREMENT PRIMARY KEY,
   cname VARCHAR(50) -- Taille ajustée
);

-- Ajout de la colonne à la table student si elle n'existe pas
ALTER TABLE student ADD COLUMN IF NOT EXISTS class_id INT;

-- Ajout de la contrainte de clé étrangère
ALTER TABLE student
ADD CONSTRAINT fk_class
FOREIGN KEY (class_id) REFERENCES class(cid)
ON DELETE CASCADE
ON UPDATE CASCADE;
   

3. Insertion, mise à jour et suppression de données

Insère une classe, ajoute un étudiant, modifie son nom puis le supprime.

-- Insertion d'une classe exemple (si elle n'existe pas)
INSERT IGNORE INTO class (cname) VALUES ('Classe A');

-- Insertion d'un étudiant (l'ID sera auto-généré)
INSERT INTO student (sname, gender) VALUES ('alex', 'F');

-- Mise à jour de l'étudiant avec l'ID 1 (si l'insertion précédente n'a pas généré 1)
-- Il est plus sûr de le faire sur le nom ou une autre caractéristique si l'ID n'est pas garanti
UPDATE student SET sname = 'wupeiqi' WHERE ID = 1; -- Attention: Cet ID peut ne pas être 1 si auto_increment a déjà avancé

-- Suppression de l'étudiant avec l'ID 1
DELETE FROM student WHERE ID = 1; -- Même remarque sur l'ID
   

4. Compter les étudiants par classe

Compte le nombre d'étudiants dans chaque classe en utilisant une jointure externe gauche.

SELECT c.cname, COUNT(s.ID) AS student_count
FROM class c
LEFT JOIN student s ON c.cid = s.class_id
GROUP BY c.cid, c.cname; -- Grouper par ID et nom pour plus de sécurité
   

5. Rendre le champ credit unique

Modifie la colonne credit pour qu'elle ne contienne que des valeurs uniques.

ALTER TABLE student
MODIFY COLUMN credit VARCHAR(50) UNIQUE; -- La taille peut être ajustée
   

6. Création d'un utilisateur et octroi de privilèges

Crée un utilisateur 'panda' se connectant depuis 'localhost' avec le mot de passe '123' et lui donne le droit de créer des tables.

-- Syntaxe peut varier légèrement selon la version de MySQL
CREATE USER 'panda'@'localhost' IDENTIFIED BY '123';
GRANT CREATE ON *.* TO 'panda'@'localhost';
-- Pour accorder la création de tables UNIQUEMENT sur une base spécifique (par exemple 'my_database'):
-- GRANT CREATE ON my_database.* TO 'panda'@'localhost';
FLUSH PRIVILEGES;
   

7. Connexion avec PyMySQL et insertion de données

Se connecte à une base de données locale et insère une ligne dans la table student.

import pymysql

try:
   conn = pymysql.connect(
       host='127.0.0.1',
       port=3306,
       user='root',       # Remplacer par votre nom d'utilisateur
       password='123456', # Remplacer par votre mot de passe
       db='your_database_name', # Remplacer par le nom de votre base de données
       charset='utf8mb4' # Utiliser utf8mb4 pour une meilleure compatibilité
   )
   cursor = conn.cursor()

   # Assurez-vous que les valeurs correspondent à la structure de votre table student
   # et que l'ID n'entre pas en conflit avec une clé primaire auto-incrémentée
   # Il est souvent préférable de laisser l'ID être généré par la base de données
   # sql = "INSERT INTO student (sname, gender, credit, class_id) VALUES ('park', 'M', '123456', 1)"
   # Alternative : laisser l'ID être auto-généré
   sql = "INSERT INTO student (sname, gender, credit) VALUES (%s, %s, %s)"
   values = ('park', 'M', '123456')

   cursor.execute(sql, values)
   conn.commit()
   print("Donnée insérée avec succès.")

except pymysql.Error as e:
   print(f"Erreur lors de l'insertion : {e}")
   if conn:
       conn.rollback() # Annuler en cas d'erreur

finally:
   if cursor:
       cursor.close()
   if conn:
       conn.close()
   

8. Sauvegarde de la table student avec mysqldump

Utilise l'outil mysqldump pour sauvegarder la table student de la base de données db6 dans un fichier SQL.

# Naviguez vers le répertoire bin de votre installation MySQL
# Le chemin peut varier selon votre installation
cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"

# Exécutez la commande de sauvegarde
mysqldump -u root -p'123456' db6 student > student_backup.sql
# Remplacez 'root', '123456' et 'db6' par vos informations
# Le mot de passe est collé directement après -p sans espace.
# Ou, vous pouvez omettre le mot de passe et il vous sera demandé.
# mysqldump -u root -p db6 student > student_backup.sql
   

9. Création d'un déclencheur pour journaliser les insertions

Crée un déclencheur qui enregistre l'ID et l'heure d'insertion de chaque nouvelle ligne ajoutée à la table student dans la table student\_insert\_log.

-- Créez d'abord la table de log si elle n'existe pas
CREATE TABLE IF NOT EXISTS student_insert_log (
   log_id INT AUTO_INCREMENT PRIMARY KEY,
   student_id INT NOT NULL,
   insert_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Définir le délimiteur pour pouvoir utiliser ';' dans le corps du trigger
DELIMITER //

CREATE TRIGGER tri_student_insert_log
AFTER INSERT ON student
FOR EACH ROW
BEGIN
   INSERT INTO student_insert_log (student_id, insert_time)
   VALUES (NEW.ID, NOW());
END//

-- Réinitialiser le délimiteur par défaut
DELIMITER ;