Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

SQL

Introduction au SQL

SQL, pour "Structured Query Language", est un langage de programmation standard et puissant conçu pour communiquer avec et manipuler des bases de données relationnelles.

En tant que langage de requête, il permet aux utilisateurs de gérer et d'interagir facilement avec les données stockées dans une base de données.

Sa simplicité syntaxique et sa puissance en font un outil essentiel pour les développeurs, les analystes de données, et tous ceux qui travaillent avec des données stockées de manière relationnelle.

Histoire du SQL

Thèse de Edgar Frank Codd : A Relational Model of Data for Large Shared Data Banks

En 1970, Edgar Frank Codd, un informaticien travaillant chez IBM, a publié un article intitulé "A Relational Model of Data for Large Shared Data Banks". Dans cet article, Codd a proposé un modèle relationnel pour les bases de données qui a révolutionné la manière de stocker, récupérer et gérer les données dans les systèmes informatiques. Le modèle relationnel est basé sur la théorie des ensembles et utilise une structure de table pour représenter les données et les relations entre elles. Ce modèle est devenu la fondation des bases de données relationnelles modernes et du langage SQL.

MySQL : Michael Widenius

MySQL a été créé par Michael "Monty" Widenius et David Axmark dans les années 1990. MySQL est devenu l'un des systèmes de gestion de bases de données relationnelles (SGBDR) open source les plus populaires au monde. Il est réputé pour sa rapidité, sa fiabilité, sa facilité d'utilisation et sa conformité au modèle relationnel. MySQL a été racheté par Sun Microsystems en 2008, puis par Oracle Corporation en 2010.

MariaDB : Création d'une alternative Open Source Michael Widenius

Suite au rachat de MySQL par Oracle, il y avait des craintes que le produit ne reste pas entièrement open source et gratuit. En réponse, Michael Widenius a lancé MariaDB en 2009, un fork de MySQL. MariaDB est destiné à rester libre et open source et sert d'alternative directe à MySQL. MariaDB a été bien accueilli par la communauté open source et est maintenant utilisé par de grandes entreprises et de nombreux services en ligne.

MySQL 8.0

MySQL 8.0 est une version majeure du système de gestion de base de données, sortie en avril 2018. Elle introduit de nombreuses améliorations et nouvelles fonctionnalités telles que le support amélioré des jeux de caractères Unicode, les index invisibles, la persistance de la configuration du système, et de nombreuses optimisations de performances. MySQL 8.0 continue de développer la robustesse et la flexibilité du SGBDR en incorporant de nouvelles technologies et standards.

Principaux SGBD et Leurs Différences

Oracle Database

  • Type: Propriétaire/Commercial
  • Développeur: Oracle Corporation
  • Caractéristiques:
    • Orienté vers les grandes entreprises avec des fonctionnalités pour des systèmes de gestion importants.
    • Supporte une variété de types de données, y compris spatiales et multimédias.
    • Inclut des fonctionnalités avancées de réplication, de partitionnement et de clusters.
    • Version gratuite limitée disponible (Oracle XE).

Microsoft SQL Server

  • Type: Propriétaire/Commercial
  • Développeur: Microsoft
  • Caractéristiques:
    • Intégration étroite avec les produits Microsoft.
    • Services d'analyse et de reporting avancés.
    • Outils d'intégration et d'administration dédiés (SSIS, SQL Server Management Studio).

MySQL

  • Type: Open Source/Commercial
  • Développeur: Oracle Corporation (initialement développé par MySQL AB)
  • Caractéristiques:
    • Très populaire pour les applications web, souvent en association avec PHP.
    • Réputation de facilité d'utilisation et de rapidité.
    • Licence GPL pour la version open source, versions commerciales disponibles.

PostgreSQL

  • Type: Open Source
  • Développeur: PostgreSQL Global Development Group
  • Caractéristiques:
    • Conformité aux standards et extensibilité.
    • Supporte des fonctionnalités objet-relationnelles et des types de données géographiques (PostGIS).
    • Versioning des données et capacité de définir des types personnalisés.

SQLite

  • Type: Open Source
  • Développeur: D. Richard Hipp
  • Caractéristiques:
    • Léger et embarquable directement dans les applications.
    • Ne nécessite pas de serveur de base de données séparé.
    • Utilisé pour le stockage local des données dans les applications mobiles et les navigateurs web.

IBM DB2

  • Type: Propriétaire/Commercial
  • Développeur: IBM
  • Caractéristiques:
    • Intégration forte avec les systèmes IBM, en particulier les mainframes.
    • Fonctionnalités pour les transactions à grand volume et les grandes entreprises.
    • Gestion avancée de données et outils d'analytique.

MariaDB

  • Type: Open Source
  • Développeur: Michael Widenius et la communauté open source
  • Caractéristiques:
    • Engagement pour rester libre et open source.
    • Compatibilité avec MySQL.
    • Nouvelles fonctionnalités et améliorations par rapport à MySQL.

SAP HANA

  • Type: Propriétaire/Commercial
  • Développeur: SAP SE
  • Caractéristiques:
    • Base de données en mémoire pour des performances rapides.
    • Outils intégrés pour l'analyse et le traitement transactionnel.
    • Conçu pour les entreprises utilisant d'autres logiciels SAP.

Installer un serveur local

Les langages de programmation conçus pour le développement Web (PHP, MySQL) ont besoin d'un serveur.

Ici on utilisera un "Serveur local"

  • Sur windows
  • Sur Mac
    • Mamp
  • Sur Linux
    • Xampp

Pour tester nos requêtes dans Laragon on aura besoin de phpMyAdmin :

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

  • Télécharger phpMyAdmin sur : https://www.phpmyadmin.net/
  • Dezipper les fichiers dans : C:\laragon\etc\apps\phpmyadmin
  • Redémarrer les services de Laragon

Dans Laragon, le type de serveur de base de données utilisé (MySQL ou MariaDB) dépend de la configuration initiale du logiciel ou des ajustements effectués ultérieurement. Laragon permet de passer facilement de MySQL à MariaDB en fonction des besoins de l'utilisateur.

Fonctionnalités Principales

Exemples et applications

Afficher les Bases de données disponibles

SHOW DATABASES

Créer la Base de Données

Utilisez des noms en minuscules et évitez les espaces. Utilisez plutôt des underscores (_) si nécessaire.

CREATE DATABASE cinema;

Créer la Table

  • Les noms de table doivent être au singulier ou au pluriel en fonction de la préférence, mais il est important de rester cohérent. Ici, nous utiliserons le singulier "movie".
  • Utilisez également des noms en minuscules pour les noms de table et de colonne.
  • Définissez des colonnes pour les attributs pertinents d'un film, comme l'ID, le titre, la description, l'année de sortie, etc.
USE cinema;

CREATE TABLE movie (
    movie_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    release_year YEAR(4),
    duration INT,
    rating VARCHAR(50)
);

Dans cet exemple:

  • movie_id est la clé primaire de la table qui s'incrémente automatiquement pour chaque nouveau film ajouté.
  • title Le titre est une chaîne de caractères qui ne peut pas être nul (NOT NULL)
  • description La description est un texte pouvant contenir 255 caractères VARCHAR
  • release_year est de type YEAR qui peut contenir une année à 4 chiffres
  • duration pourrait représenter la durée du film en minutes
  • rating pourrait être la classification du film

Avec cette structure de base, vous avez une fondation sur laquelle vous pouvez construire en ajoutant plus de colonnes ou d'autres tables pour des acteurs, des réalisateurs, des genres, etc., selon les besoins de votre application

Il n'est pas nécessaire de spécifier USE cinema; chaque fois que vous exécutez une requête si vous travaillez déjà sur la base de données cinema. La commande USE est utilisée pour sélectionner la base de données avec laquelle vous souhaitez travailler, et après l'avoir exécutée une fois dans votre session de travail, toutes les requêtes suivantes s'appliqueront à cette base de données, à moins que vous ne changiez explicitement de base de données avec une autre commande USE.

Moteur de stockage

Concernant le moteur de stockage, lors de la création de tables dans une base de données via phpMyAdmin ou un autre outil de gestion, l'utilisateur a la possibilité de choisir le moteur de stockage approprié. Par défaut, le moteur de stockage est souvent configuré sur InnoDB dans les versions récentes de MySQL et MariaDB, en raison de ses avantages en termes de gestion des transactions, de récupération après incidents, et de support des clés étrangères. Cependant, d'autres moteurs de stockage tels que MyISAM ou MEMORY peuvent être sélectionnés en fonction des besoins spécifiques en matière de performance ou de fonctionnalités.

Insérer des données INSERT INTO...VALUES...

Voici à quoi ressemblerait la requête SQL pour insérer ces films dans la table movie :

INSERT INTO movie (title, description, release_year, duration, rating) VALUES
('The Godfather', 'La saga épique d\'une famille mafieuse à New York', 1972, 175, 'R'),
('Pulp Fiction', 'Des histoires entrelacées de crime et de mystère à Los Angeles', 1994, 154, 'R'),
('Forrest Gump', 'La vie incroyable d\'un homme simple et son impact sur l\'histoire américaine', 1994, 142, 'PG-13'),
('The Shawshank Redemption', 'Un homme condamné à tort trouve de l\'espoir et de l\'amitié en prison', 1994, 142, 'R');

Explication de la requête :

  • INSERT INTO movie : Cette commande indique que nous allons insérer des données dans la table movie.
  • (title, description, release_year, duration, rating) : Ici, nous spécifions les colonnes de la table dans lesquelles nous allons insérer les données.
  • VALUES: Après le mot-clé VALUES, nous listons les ensembles de valeurs à insérer dans la table. Chaque ensemble de parenthèses représente une ligne de la table et les valeurs à l'intérieur correspondent aux colonnes spécifiées plus tôt. Les chaînes de caractères sont entourées de guillemets simples et les nombres sont écrits tels quels.
  • Nous utilisons des guillemets simples pour les valeurs textuelles. Notez l'utilisation de l'antislash (\) pour échapper les apostrophes dans les descriptions des films qui en contiennent, comme dans "d'une famille".

Ajouter une ou deux entrées supplémentaires à l'aide de l'interface de phpmyadmin

Extraire des données (Récupérer / Afficher) SELECT...FROM...

Voici plusieurs exemples de requêtes utilisant SELECT dans votre base de données movie. Ces requêtes vous aideront à interroger votre base de données de différentes manières :

  1. Sélectionner tous les champs de tous les films:
​​SELECT * FROM movie;

Cette requête récupère toutes les colonnes pour tous les films de la table movie.

  1. Sélectionner le titre et l'année de sortie de tous les films:
​​SELECT title, release_year FROM movie;

Ici, seuls les titres et les années de sortie des films seront affichés.

  1. Afficher les films sortis après une certaine année:
​​SELECT * FROM movie WHERE release_year >= 1994;

Cela affiche tous les champs des films sortis à partir de 1994.

  1. Compter le nombre de films dans la base de données:
​​SELECT COUNT(*) FROM movie;

Cette requête retourne le nombre total de films présents dans la table.

  1. Afficher les films dont la durée est comprise entre 140 et 160 minutes:
​​SELECT title, duration FROM movie WHERE duration BETWEEN 140 AND 160;
  1. Rechercher des films par titre:
​​SELECT * FROM movie WHERE title LIKE '%Pulp%';

Trouve tous les films dont le titre contient le mot "Pulp".

  1. Sélectionner des films et les trier par année de sortie:
​​SELECT title, release_year FROM movie ORDER BY release_year DESC;

Affiche les titres et les années de sortie des films, triés par année de sortie de la plus récente à la plus ancienne.

  1. Afficher les films d'une classification spécifique :
​​SELECT title, rating FROM movie WHERE rating = 'PG-13';

Liste les titres des films ayant une classification 'PG-13'.

  1. Sélectionner des films et les limiter à un certain nombre :
​​SELECT * FROM movie LIMIT 3;

Affiche les informations de seulement 3 films (utile pour les grandes tables).

  1. Afficher les films et les regrouper par classification :
​​SELECT rating, COUNT(*) FROM movie GROUP BY rating;

Cette requête compte combien de films il y a dans chaque catégorie de classification.

Ces requêtes couvrent une gamme de scénarios différents et vous montrent comment utiliser le SELECT pour récupérer des informations de manière variée à partir de votre base de données movie.

Mettre à jour une entrée UPDATE...SET...

Pour mettre à jour l'entrée que vous avez mentionnée avec les informations d'un nouveau film culte, nous pouvons utiliser la commande UPDATE. Voici un exemple en utilisant le film culte "Inception" :

UPDATE movie
SET
  title = 'Inception',
  description = "Un voleur qui s'introduit dans les rêves des gens pour y voler des secrets entreprend une dernière mission qui pourrait inverser toute sa réalité.",
  release_year = 2010,
  duration = 148,
  rating = 'PG-13'
WHERE
  movie_id = 5;

Explication de la requête :

  • UPDATE movie : Cela indique que vous allez mettre à jour la table movie.
  • SET : Ce mot-clé commence la liste des colonnes à mettre à jour et les nouvelles valeurs à leur affecter.
  • title = 'Inception' : Modifie le titre du film à "Inception".
  • description = '...' : Met à jour la description avec un résumé du film "Inception".
  • release_year = 2010 : Change l'année de sortie pour 2010, l'année de sortie d'"Inception".
  • duration = 148 : Définit la durée du film à 148 minutes.
  • rating = 'PG-13' : Met à jour la classification du film à "PG-13".
  • WHERE movie_id = 5 : Spécifie que la mise à jour ne doit s'appliquer qu'à l'entrée avec un movie_id égal à 5.

Assurez-vous que l'ID que vous souhaitez mettre à jour correspond à l'ID de l'entrée existante dans votre base de données. Si l'ID 5 est correct, cette requête mettra à jour la ligne correspondante avec les informations du film "Inception".

Effacer une entrée DELETE FROM ...WHERE...

Pour supprimer une entrée spécifique dans votre table movie, vous pouvez utiliser la commande DELETE de SQL. Voici comment vous pourriez procéder pour supprimer l'entrée avec l'id 6 :

DELETE FROM movie WHERE movie_id = 6;

Explication de la requête :

  • DELETE FROM movie : Indique que vous voulez supprimer des enregistrements de la table movie.
  • WHERE movie_id = 5 : Cette condition spécifie que vous voulez supprimer uniquement l'enregistrement où movie_id est égal à 5.

Avant d'exécuter cette requête, assurez-vous que vous voulez vraiment supprimer cette entrée, car l'action est irréversible par des moyens standards de SQL (à moins que vous n'ayez des sauvegardes ou des points de restauration).

Alias de colonnes et jointures de tables

Pour enrichir votre guide sur la gestion d'une base de données de films, ajoutons quelques exemples et explications supplémentaires sur l'utilisation d'alias de colonnes, une introduction aux jointures, ainsi qu'un exemple spécifique avec INNER JOIN.

Utilisation d'alias de colonnes

Les alias permettent de renommer une colonne ou une table dans le résultat d'une requête SQL. Ceci est particulièrement utile pour améliorer la lisibilité des résultats ou lorsque vous travaillez avec des jointures.

Exemple d'utilisation d'un alias pour une colonne :

SELECT title AS Titre, release_year AS Année_De_Sortie FROM movie;

Dans cet exemple, title est renommé en Titre et release_year en Année_De_Sortie dans les résultats de la requête.

Introduction aux jointures

Les jointures permettent de combiner des colonnes provenant de deux ou plusieurs tables basées sur une relation entre certaines colonnes de ces tables. Habituellement, cette relation est définie par des clés primaires et des clés étrangères.

Exemple avec INNER JOIN

Supposons que nous avons une seconde table director (réalisateur) qui contient les informations des réalisateurs :

CREATE TABLE director (
    director_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    birth_year YEAR(4)
);

Et ajoutons des données dans cette table :

INSERT INTO director (name, birth_year) VALUES
('Francis Ford Coppola', 1939),
('Quentin Tarantino', 1963);

Supposons maintenant que nous voulons ajouter une colonne director_id dans la table movie pour lier chaque film à son réalisateur :

ALTER TABLE movie ADD COLUMN director_id INT;
UPDATE movie SET director_id = 1 WHERE title = 'The Godfather';
UPDATE movie SET director_id = 2 WHERE title = 'Pulp Fiction';

Pour afficher les films avec le nom de leur réalisateur, nous utiliserions INNER JOIN :

SELECT m.title AS Titre, d.name AS Réalisateur
FROM movie m
INNER JOIN director d ON m.director_id = d.director_id;

Cet exemple montre comment récupérer le titre des films et les noms

Les contraintes de données (NEW)

Définition des contraintes lors de la création de tables (NEW)

Lors de la création de tables dans MySQL, vous pouvez appliquer diverses contraintes pour assurer l'intégrité des données et définir les règles que les données dans les tables doivent suivre.

Voici les contraintes courantes, y compris celles pour les clés étrangères :

  1. PRIMARY KEY : Assure que chaque valeur dans une colonne ou un ensemble de colonnes est unique et non NULL. Chaque table doit avoir une seule clé primaire.

    ​​​​CREATE TABLE exemple (
    ​​​​    id INT AUTO_INCREMENT,
    ​​​​    PRIMARY KEY (id)
    ​​​​);
    
  2. FOREIGN KEY : Utilisée pour créer une relation entre deux tables. Elle assure que la valeur dans une colonne ou un ensemble de colonnes correspond à une valeur dans une colonne primaire ou unique d'une autre table.

    ​​​​CREATE TABLE exemple (
    ​​​​    ...
    ​​​​    other_id INT,
    ​​​​    FOREIGN KEY (other_id) REFERENCES other_table(other_table_id)
    ​​​​    ON DELETE CASCADE
    ​​​​    ON UPDATE NO ACTION
    ​​​​);
    
  3. UNIQUE : Garantit que toutes les valeurs dans une colonne ou un ensemble de colonnes sont différentes les unes des autres. Cela peut être utilisé sur des colonnes qui ne sont pas la clé primaire.

    ​​​​CREATE TABLE exemple (
    ​​​​    ...
    ​​​​    email VARCHAR(255),
    ​​​​    UNIQUE (email)
    ​​​​);
    
  4. NOT NULL : Spécifie qu'une colonne ne peut pas avoir de valeurs NULL. Chaque fois qu'une ligne est ajoutée ou modifiée, la colonne doit avoir une valeur définie.

    ​​​​CREATE TABLE exemple (
    ​​​​    ...
    ​​​​    name VARCHAR(50) NOT NULL
    ​​​​);
    
  5. CHECK : Permet de spécifier une condition que toutes les valeurs dans une colonne doivent satisfaire. (Cette contrainte est supportée à partir de MySQL 8.0.16.)

    ​​​​CREATE TABLE exemple (
    ​​​​    ...
    ​​​​    age INT,
    ​​​​    CHECK (age >= 18)
    ​​​​);
    
  6. DEFAULT : Attribue une valeur par défaut à la colonne si aucune valeur n'est spécifiée lors de l'insertion de données.

    ​​​​CREATE TABLE exemple (
    ​​​​    ...
    ​​​​    status VARCHAR(50) DEFAULT 'actif'
    ​​​​);
    

Les contraintes peuvent être appliquées lors de la création de la table avec la commande CREATE TABLE ou ajoutées à une table existante avec la commande ALTER TABLE. Elles jouent un rôle crucial dans le maintien de la qualité et de la fiabilité des données dans votre base de données.

Contraintes de clés étrangères (NEW)

Dans MySQL, les contraintes pour les clés étrangères servent à maintenir l'intégrité référentielle entre les tables. Voici un résumé des types de contraintes que l'on peut définir sur les clés étrangères :

  1. CASCADE : Si une ligne d'une table parent est supprimée (ou modifiée), les lignes correspondantes de la table enfant seront également supprimées (ou modifiées). C'est utile pour maintenir la synchronisation entre les tables.
  2. SET NULL : Lorsqu'une ligne référencée est supprimée (ou modifiée), les valeurs de clé étrangère dans la table enfant sont mises à NULL. Cela est uniquement possible si la colonne peut accepter les valeurs NULL.
  3. NO ACTION : Une vérification est effectuée pour s'assurer qu'il n'y a pas de référence à la ligne de la table parent dans la table enfant. Si une référence est trouvée, la tentative de suppression ou de modification échoue.
  4. RESTRICT : Empêche la suppression ou la modification d'une ligne référencée si celle-ci a des dépendances dans la table enfant. Cela ressemble à NO ACTION, mais la vérification est immédiate.
  5. SET DEFAULT : Lors de la suppression ou de la modification d'une ligne référencée, les valeurs de la clé étrangère sont définies à une valeur par défaut. Cette valeur doit être une valeur littérale spécifiée lors de la création de la contrainte.

Pour définir ces contraintes en MySQL, vous utiliseriez la syntaxe suivante lors de la création ou de la modification d'une table :

CREATE TABLE tablename (
    ...
    FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_table_column)
    ON DELETE [constraint_type]
    ON UPDATE [constraint_type]
);

Ou pour ajouter une contrainte à une table existante :

ALTER TABLE tablename
ADD CONSTRAINT fk_name
FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_table_column)
ON DELETE [constraint_type]
ON UPDATE [constraint_type];

Dans ces commandes, remplacez tablename, foreign_key_column, parent_table, parent_table_column, fk_name, et [constraint_type] par les noms de table et de colonne appropriés et par le type de contrainte que vous souhaitez utiliser.

Les fonctions

MySQL propose une variété de fonctions intégrées qui peuvent être utilisées pour effectuer des opérations sur les données. Voici quelques exemples de fonctions MySQL couramment utilisées :

  1. Fonctions d'agrégation :
  • COUNT(): Compte le nombre de lignes.
  • SUM(): Calcule la somme d'un ensemble de valeurs.
  • AVG(): Calcule la moyenne d'un ensemble de valeurs.
  • MAX(): Retrouve la valeur maximale d'un ensemble.
  • MIN(): Retrouve la valeur minimale d'un ensemble.
  1. Fonctions de Chaîne de Caractères :
  • CONCAT(str1, str2, ...): Concatène deux ou plusieurs chaînes de caractères.
  • LENGTH(str): Retourne la longueur d'une chaîne de caractères.
  • SUBSTRING(str, pos, len): Extrait une sous-chaîne de caractères.
  • UPPER(str) et LOWER(str): Convertit une chaîne en majuscules ou en minuscules.
  • TRIM(str): Supprime les espaces au début et à la fin d'une chaîne.
  1. Fonctions de Date et Heure :
  • NOW(): Retourne la date et l'heure actuelles.
  • CURDATE(): Retourne la date actuelle.
  • CURTIME(): Retourne l'heure actuelle.
  • DATE_ADD(date, INTERVAL expr unit): Ajoute un intervalle de temps à une date.
  • DATEDIFF(expr1, expr2): Calcule la différence entre deux dates.
  1. Fonctions Mathématiques :
  • ROUND(number, decimals): Arrondit un nombre à un certain nombre de décimales.
  • FLOOR(number): Arrondit un nombre à l'entier inférieur.
  • CEIL(number): Arrondit un nombre à l'entier supérieur.
  • ABS(number): Retourne la valeur absolue d'un nombre.
  • RAND(): Génère un nombre aléatoire.
  1. Fonctions de Contrôle de Flux :
  • IF(condition, value_if_true, value_if_false): Retourne une valeur en fonction d'une condition.
  • CASE: Permet de faire des choix conditionnels dans une requête.
  • COALESCE(value1, value2, ...): Retourne la première valeur non-null de la liste.

Ces fonctions peuvent être très utiles pour effectuer des calculs, manipuler des données et effectuer des requêtes complexes. Elles représentent une partie importante de la puissance et de la flexibilité de MySQL en tant que système de gestion de base de données.