Les Variables

Déclaration des variables

pour déclarer des vriables local dans une fonction ou procédure on utlise le mot clé DECLARE Exemple


 Declare x int;
 declare a float default 0 ;
 declare nom varchar(25);
 declare c int ,age int,description text;

Initialisation d'une variable

Afin de modifier ou initialiser la valeur d'une variable on utilise les mots clés:

SET
SELECT ... INTO

Exemple1 avec SET


 Declare nom varchar(25);
 SET nom="nom1";
 Declare prenom varchar(25) default"prenom1";
 declare age ;
 declare nomComplet text;
 SET age=15;
 SET nomComplet=concat(nom,'  ',prenom)

Exemple avec INTO


 delimiter //
 Create procedure if not exists AfficherTotaleVente()
begin
declare totale float default 0;
  Select sum(quantiteVendue*prixVente) INTO totale from ventes;
  -- ou 
  -- Select sum(quantiteVendue*prixVente)   from ventes INTO totale;

  select totale; --afficher le totale
end;


 delimiter //
 Create procedure if not exists Test()
begin
declare totale float default 0;
declare nomProduit varcahr(25)
  Select p.nom INTO nomProduit  ,sum(quantiteVendue*prixVente) INTO totale from ventes v inner join produits p on v.idproduit=p.id;
  -- ou 
  -- Select sum(quantiteVendue*prixVente)   from ventes INTO totale;

  select totale; --afficher le totale
end;

Exemple avec la valeur d'une fonction
Créer la fonction avgVenteProduit(id_Produit int) qui permer de retrouner la moyenne des ventes d'un produit dont son id est passé en paramètre

 delimiter //
 Create function  if not exits avgVenteProduit(id_Produit int)
begin
   declare v_avg float default 0;
   select AVG(quantiteVendue*prixVente) into v_avg from ventes where idproduit =id_Produit;
   returns v_avg;
end;

Créer une fonction qui permet d'afficher la liste des produits (id, libelle....) dont leur totale des ventes(quantiteVendue*prixVente)
est suppérieur à la moyenne des ventes d'un produit dont son id est passé en paramètre

 delimiter //
 Create procedure  if not exists AfficherProduitsVenteSuperierA(id_Produit int)
begin
   declare moyenneAVG_Produit float 0;
   SET moyenneAVG_Produit=avgVenteProduit(id_Produit);
   SELECT P.* ,SUM(V.quantiteVendue*V.prixVente) as totale 
   from Ventes  V INNER JOIN Produits P 
   ON V.idProduit=P.id  
   Group By P.id
   HAVIING totale>moyenneAVG_Produit;
 
end;

Schéma de la base de données utilisé

-- Création de la table "produits"
CREATE TABLE produits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(255),
    prix DECIMAL(10, 2),
    quantiteStock INT,
    marque VARCHAR(255),
    categorie VARCHAR(255),
    promotion TINYINT(1)
);

-- Création de la table "clients"
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(255),
    prenom VARCHAR(255),
    tel VARCHAR(20),
    ville VARCHAR(255),
    dateNaissance DATE
);

-- Création de la table "ventes"
CREATE TABLE ventes (
    idvente INT AUTO_INCREMENT PRIMARY KEY,
    idClient INT,
    idProduit INT,
    dateVente DATE,
    prixVente DECIMAL(10, 2),
    QuantiteVendue INT,
    remise DECIMAL(5, 2),
    FOREIGN KEY (idClient) REFERENCES clients(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (idProduit) REFERENCES produits(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Les Types de données En SQL

Types de données numériques :

INT : Entier signé de 32 bits. Exemple : 10, -5.
TINYINT : Entier signé de 8 bits. Exemple : 3, -2.
FLOAT : Nombre à virgule flottante simple précision. Exemple : 3.14, -0.5.
DOUBLE : Nombre à virgule flottante double précision. Exemple : 3.14159265359, -0.12345.

Types de données de chaîne :

VARCHAR : Chaîne de caractères variable avec une longueur maximale définie. Exemple : 'Hello, World!', 'MySQL'.
CHAR : Chaîne de caractères de longueur fixe. Exemple : 'OpenAI', 'SQL'.
TEXT : Texte de longueur variable pour les documents plus longs. Exemple : 'Ceci est un texte long...'

Types de données temporelles :

DATE : Date au format 'YYYY-MM-DD'. Exemple : '2023-11-02'.
TIME : Heure au format 'HH:MM:SS'. Exemple : '14:30:00'.
DATETIME : Date et heure au format 'YYYY-MM-DD HH:MM:SS'. Exemple : '2023-11-02 14:30:00'.

Types de données booléennes :

BOOLEAN ou BOOL : Valeurs booléennes TRUE ou FALSE. Exemple : TRUE, FALSE.

Types de données binaires :

BLOB : Données binaires de longueur variable pour stocker des fichiers, images, etc.
VARBINARY : Données binaires de longueur variable.

Types de données géospatiales :

GEOMETRY : Stocke des données géospatiales telles que des points, des lignes ou des polygones.
POINT : Stocke des coordonnées géographiques (latitude et longitude).

Types de données enumérés :

ENUM : Une liste de valeurs prédéfinies. Exemple : ENUM('Rouge', 'Vert', 'Bleu').

Types de données SET :

SET : Une liste de valeurs prédéfinies, où plusieurs options peuvent être sélectionnées. Exemple : SET('Option 1', 'Option 2', 'Option 3').

Types de données JSON :

JSON : Stocke des données JSON au format texte.

Types de données spatiales (avec le support de l'extension GIS) :

POINT : Stocke des données de points géographiques.
LINESTRING : Stocke des données de lignes géographiques.
POLYGON : Stocke des données de polygones géographiques.

Exemple des procédures

Q Créez une procédure stockée nommée "calculerPrixMoyenMarque" qui calcule le prix moyen de tous les produits d'une marque donnée. Utilisez une variable pour stocker le résultat. Comment appelez-vous cette procédure?
```
CREATE PROCEDURE calculerPrixMoyenMarque(marqueProduit VARCHAR(25))
BEGIN
    DECLARE prixMoyen FLOAT;
    SELECT AVG(prix) INTO prixMoyen FROM produit WHERE marque = marqueProduit;
    SELECT prixMoyen;
END;
```
CALL calculerPrixMoyenMarque('NomMarque');
Q Comment pouvez-vous créer une procédure stockée nommée "incrementerPrix" qui augmente le prix de tous les produits de 10 %? Utilisez une variable pour stocker le facteur d'augmentation.
```
CREATE PROCEDURE incrementerPrix()
BEGIN
    DECLARE facteurAugmentation FLOAT;
    SET facteurAugmentation = 1.10;
    UPDATE produit SET prix = prix * facteurAugmentation;
END;
```
CALL incrementerPrix();
Q Créez une procédure stockée nommée "calculerSommePrixTotal" qui calcule la somme totale des prix de tous les produits et stocke le résultat dans une variable. Comment appelez-vous cette procédure?
```
CREATE PROCEDURE calculerSommePrixTotal()
BEGIN
    DECLARE sommePrixTotal FLOAT;
    SELECT SUM(prix) INTO sommePrixTotal FROM produit;
    SELECT sommePrixTotal;
END;
```
CALL calculerSommePrixTotal();
Q Comment pouvez-vous créer une procédure stockée nommée "modifierPrixMinimum" qui modifie le prix de tous les produits dont le prix est inférieur à une valeur spécifiée? Utilisez une variable pour stocker la valeur de référence.
```
CREATE PROCEDURE modifierPrixMinimum(prixMinimum FLOAT)
BEGIN
    DECLARE nouvelleValeur FLOAT;
    SET nouvelleValeur = 50.0;
    UPDATE produit SET prix = nouvelleValeur WHERE prix < prixMinimum;
END;
```
CALL modifierPrixMinimum(30.0);
Q Pouvez-vous créer une procédure stockée nommée "calculerPrixTotalMarque" qui calcule le prix total de tous les produits d'une marque donnée et stocke le résultat dans une variable? Comment appelez-vous cette procédure?
```
CREATE PROCEDURE calculerPrixTotalMarque(marqueProduit VARCHAR(25))
BEGIN
    DECLARE prixTotal FLOAT;
    SELECT SUM(prix) INTO prixTotal FROM produit WHERE marque = marqueProduit;
    SELECT prixTotal;
END;
```
CALL calculerPrixTotalMarque('NomMarque');

QCréez une procédure stockée nommée "modifierPrixProduit" qui prend en entrée l'ID d'un produit et le nouveau prix, et met à jour le prix du produit. Utilisez des variables pour stocker les valeurs. Comment appelez-vous cette procédure?


CREATE PROCEDURE modifierPrixProduit(idProduit INT, nouveauPrix FLOAT)
BEGIN
    DECLARE ancienPrix FLOAT;
    SET ancienPrix = (SELECT prix FROM produits WHERE id = idProduit);
    UPDATE produits SET prix = nouveauPrix WHERE id = idProduit;
    -- Vous pouvez également effectuer d'autres opérations ou vérifications ici.
END;

CALL modifierPrixProduit(1, 49.99);

Q Comment pouvez-vous créer une procédure stockée nommée "creerVente" qui prend en entrée l'ID du client, l'ID du produit, la date de la vente, le prix de vente et la quantité vendue, puis enregistre une nouvelle vente dans la table "ventes"? Utilisez des variables pour stocker les valeurs.


CREATE PROCEDURE creerVente(idClient INT, idProduit INT, dateVente DATE, prixVente FLOAT, quantiteVendue INT)
BEGIN
    DECLARE idVente INT;
    INSERT INTO ventes (idClient, idProduit, dateVente, prixVente, QuantiteVendue) 
    VALUES (idClient, idProduit, dateVente, prixVente, quantiteVendue);
    SET idVente = LAST_INSERT_ID(); -- Obtient l'ID de la vente insérée.
    -- Vous pouvez effectuer d'autres opérations ou vérifications ici.
END;

CALL creerVente(1, 3, '2023-10-15', 19.99, 5);

Q Créez une procédure stockée nommée "modifierQuantiteProduit" qui prend en entrée l'ID d'un produit et la nouvelle quantité en stock, puis met à jour la quantité en stock du produit. Utilisez des variables pour stocker les valeurs. Comment appelez-vous cette procédure?


CREATE PROCEDURE modifierQuantiteProduit(idProduit INT, nouvelleQuantiteStock INT)
BEGIN
    DECLARE ancienneQuantiteStock INT;
    SET ancienneQuantiteStock = (SELECT quantiteStock FROM produits WHERE id = idProduit);
    UPDATE produits SET quantiteStock = nouvelleQuantiteStock WHERE id = idProduit;
    -- Vous pouvez également effectuer d'autres opérations ou vérifications ici.
END;

CALL modifierQuantiteProduit(2, 100);

Q Comment pouvez-vous créer une procédure stockée nommée "creerClient" qui prend en entrée le nom, le prénom, le numéro de téléphone et la ville d'un client, puis crée un nouveau client dans la table "clients"? Utilisez des variables pour stocker les valeurs.


CREATE PROCEDURE creerClient(nomClient VARCHAR(50), prenomClient VARCHAR(50), telClient VARCHAR(15), villeClient VARCHAR(50))
BEGIN
    DECLARE idClient INT;
    INSERT INTO clients (nom, prenom, tel, ville) 
    VALUES (nomClient, prenomClient, telClient, villeClient);
    SET idClient = LAST_INSERT_ID(); -- Obtient l'ID du client inséré.
    -- Vous pouvez effectuer d'autres opérations ou vérifications ici.
END;

CALL creerClient('John', 'Doe', '555-123-4567', 'New York');

Q Comment pouvez-vous créer une procédure stockée nommée "totalVentesClient" qui prend en entrée l'ID d'un client et renvoie le montant total des ventes réalisées par ce client?


CREATE PROCEDURE totalVentesClient(idClient INT)
BEGIN
    DECLARE totalVentes FLOAT;
    SELECT SUM(prixVente * QuantiteVendue) INTO totalVentes FROM ventes WHERE idClient = idClient;
    SELECT totalVentes;
END;

CALL totalVentesClient(1);

Q Créez une procédure stockée nommée "modifierVente" qui prend en entrée l'ID de la vente, la nouvelle date de vente et la nouvelle quantité vendue, puis met à jour ces informations dans la table "ventes". Utilisez des variables pour stocker les valeurs.


CREATE PROCEDURE modifierVente(idVente INT, nouvelleDate DATE, nouvelleQuantiteVendue INT)
BEGIN
    DECLARE ancienneDate DATE;
    DECLARE ancienneQuantite INT;
    
    -- Récupérer les valeurs existantes.
    SELECT dateVente, QuantiteVendue INTO ancienneDate, ancienneQuantite FROM ventes WHERE idvente = idVente;
    
    -- Mettre à jour la vente.
    UPDATE ventes SET dateVente = nouvelleDate, QuantiteVendue = nouvelleQuantiteVendue WHERE idvente = idVente;
    
    -- Vous pouvez effectuer d'autres opérations ou vérifications ici.
END;

CALL modifierVente(1, '2023-10-15', 8);

Q Comment pouvez-vous créer une procédure stockée nommée "creerProduit" qui prend en entrée le libellé, le prix et la quantité en stock d'un produit, puis crée un nouveau produit dans la table "produits"? Utilisez des variables pour stocker les valeurs.


CREATE PROCEDURE creerProduit(libelleProduit VARCHAR(100), prixProduit FLOAT, quantiteStockProduit INT)
BEGIN
    INSERT INTO produits (libelle, prix, quantiteStock) 
    VALUES (libelleProduit, prixProduit, quantiteStockProduit);
    -- Vous pouvez effectuer d'autres opérations ou vérifications ici.
END;

CALL creerProduit('Nouveau Produit', 29.99, 100);

Q Créez une procédure stockée nommée "ventesProduit" qui prend en entrée l'ID d'un produit et renvoie le nombre total d'unités vendues de ce produit.


CREATE PROCEDURE ventesProduit(idProduit INT)
BEGIN
    DECLARE quantiteVendue INT;
    SELECT SUM(QuantiteVendue) INTO quantiteVendue FROM ventes WHERE idProduit = idProduit;
    SELECT quantiteVendue;
END;

CALL ventesProduit(3);

Q Comment pouvez-vous créer une procédure stockée nommée "modifierClient" qui prend en entrée l'ID du client, le nouveau nom, le nouveau prénom, le nouveau numéro de téléphone et la nouvelle ville, puis met à jour les informations du client dans la table "clients"? Utilisez des variables pour stocker les valeurs.
```
CREATE PROCEDURE modifierClient(idClient INT, nouveauNom VARCHAR(50), nouveauPrenom VARCHAR(50), nouveauTel VARCHAR(15), nouvelleVille VARCHAR(50))
BEGIN
    UPDATE clients 
    SET nom = nouveauNom, prenom = nouveauPrenom, tel = nouveauTel, ville = nouvelleVille
    WHERE id = idClient;
    -- Vous pouvez effectuer d'autres opérations ou vérifications ici.
END;
```
CALL modifierClient(1, 'NouveauNom', 'NouveauPrenom', '555-999-1234', 'NouvelleVille');

Exemple des fonctions

Q Créez une fonction stockée nommée "totalVentesClient" qui prend en entrée l'ID d'un client et renvoie le montant total des ventes réalisées par ce client.


CREATE FUNCTION totalVentesClient(idClient INT) RETURNS FLOAT
BEGIN
    DECLARE totalVentes FLOAT;
    SELECT SUM(prixVente * QuantiteVendue) INTO totalVentes FROM ventes WHERE idClient = idClient;
    RETURN totalVentes;
END;

Q Comment pouvez-vous créer une fonction stockée nommée "prixMoyenProduit" qui prend en entrée l'ID d'un produit et renvoie le prix moyen de ce produit basé sur l'historique des ventes?


CREATE FUNCTION prixMoyenProduit(idProduit INT) RETURNS FLOAT
BEGIN
    DECLARE prixMoyen FLOAT;
    SELECT AVG(prixVente) INTO prixMoyen FROM ventes WHERE idProduit = idProduit;
    RETURN prixMoyen;
END;

Q Créez une fonction stockée nommée "quantiteStockProduit" qui prend en entrée l'ID d'un produit et renvoie la quantité en stock actuelle de ce produit.


CREATE FUNCTION quantiteStockProduit(idProduit INT) RETURNS INT
BEGIN
    DECLARE quantiteStock INT;
    SELECT quantiteStock INTO quantiteStock FROM produits WHERE id = idProduit;
    RETURN quantiteStock;
END;

Q Comment pouvez-vous créer une fonction stockée nommée "produitsEnRupture" qui renvoie la liste des produits ayant une quantité en stock inférieure à un seuil donné? Utilisez une variable pour stocker le seuil.


CREATE FUNCTION produitsEnRupture(seuil INT) RETURNS TEXT
BEGIN
    DECLARE produitsRupture TEXT;
    SELECT GROUP_CONCAT(libelle SEPARATOR ', ') INTO produitsRupture FROM produits WHERE quantiteStock < seuil;
    RETURN produitsRupture;
END;

Q Créez une fonction stockée nommée "ventesClient" qui prend en entrée l'ID d'un client et renvoie le nombre total d'unités vendues par ce client.


CREATE FUNCTION ventesClient(idClient INT) RETURNS INT
BEGIN
    DECLARE totalVentes INT;
    SELECT SUM(QuantiteVendue) INTO totalVentes FROM ventes WHERE idClient = idClient;
    RETURN totalVentes;
END;

Q Comment pouvez-vous créer une fonction stockée nommée "nomClient" qui prend en entrée l'ID d'un client et renvoie le nom complet du client (nom et prénom)?


CREATE FUNCTION nomClient(idClient INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE nomComplet VARCHAR(100);
    SELECT CONCAT(nom, ' ', prenom) INTO nomComplet FROM clients WHERE id = idClient;
    RETURN nomComplet;
END;

Q Créez une fonction stockée nommée "produitsVendus" qui renvoie la liste des produits vendus par un client donné (en utilisant l'ID du client).


CREATE FUNCTION produitsVendus(idClient INT) RETURNS TEXT
BEGIN
    DECLARE produitsVendus TEXT;
    SELECT GROUP_CONCAT(libelle SEPARATOR ', ') INTO produitsVendus FROM produits
    WHERE id IN (SELECT idProduit FROM ventes WHERE idClient = idClient);
    RETURN produitsVendus;
END;

Q Comment pouvez-vous créer une fonction stockée nommée "ventesProduit" qui prend en entrée l'ID d'un produit et renvoie le nombre total d'unités vendues de ce produit?


CREATE FUNCTION ventesProduit(idProduit INT) RETURNS INT
BEGIN
    DECLARE totalVentes INT;
    SELECT SUM(QuantiteVendue) INTO totalVentes FROM ventes WHERE idProduit = idProduit;
    RETURN totalVentes;
END;

Q Créez une fonction stockée nommée "meilleurClient" qui renvoie le nom du client ayant effectué le plus grand nombre de ventes.


CREATE FUNCTION meilleurClient() RETURNS VARCHAR(100)
BEGIN
    DECLARE nomClient VARCHAR(100);
    SELECT clients.nom INTO nomClient FROM clients
    INNER JOIN (SELECT idClient, SUM(QuantiteVendue) AS totalVentes
                FROM ventes
                GROUP BY idClient
                ORDER BY totalVentes DESC
                LIMIT 1) AS meilleuresVentes
    ON clients.id = meilleuresVentes.idClient;
    RETURN nomClient;
END;

Q Comment pouvez-vous créer une fonction stockée nommée "produitMoinsVendu" qui renvoie le libellé du produit le moins vendu?


CREATE FUNCTION produitMoinsVendu() RETURNS VARCHAR(100)
BEGIN
    DECLARE libelleProduit VARCHAR(100);
    SELECT produits.libelle INTO libelleProduit FROM produits
    INNER JOIN (SELECT idProduit, SUM(QuantiteVendue) AS totalVentes
                FROM ventes
                GROUP BY idProduit
                ORDER BY totalVentes ASC
                LIMIT 1) AS moinsVendu
    ON produits.id = moinsVendu.idProduit;
    RETURN libelleProduit;
END;

Q Créez une fonction stockée nommée "ventesMoyennesClient" qui prend en entrée l'ID d'un client et renvoie la quantité moyenne d'unités vendues par ce client.


CREATE FUNCTION ventesMoyennesClient(idClient INT) RETURNS FLOAT
BEGIN
    DECLARE ventesMoyennes FLOAT;
    SELECT AVG(QuantiteVendue) INTO ventesMoyennes FROM ventes WHERE idClient = idClient;
    RETURN ventesMoyennes;
END;

Q Créez une fonction stockée nommée "quantiteStockMoyenneProduits" qui renvoie la quantité moyenne en stock de tous les produits.


CREATE FUNCTION quantiteStockMoyenneProduits() RETURNS FLOAT
BEGIN
    DECLARE quantiteMoyenne FLOAT;
    SELECT AVG(quantiteStock) INTO quantiteMoyenne FROM produits;
    RETURN quantiteMoyenne;
END;

Q Comment pouvez-vous créer une fonction stockée nommée "ventesClientParDate" qui prend en entrée l'ID d'un client et une date, puis renvoie le montant total des ventes de ce client à cette date spécifique?


CREATE FUNCTION ventesClientParDate(idClient INT, dateVente DATE) RETURNS FLOAT
BEGIN
    DECLARE totalVentes FLOAT;
    SELECT SUM(prixVente * QuantiteVendue) INTO totalVentes FROM ventes WHERE idClient = idClient AND dateVente = dateVente;
    RETURN totalVentes;
END;

Q Créez une fonction stockée nommée "clientsParVille" qui prend en entrée le nom d'une ville et renvoie la liste des noms de clients résidant dans cette ville.


CREATE FUNCTION clientsParVille(nomVille VARCHAR(50)) RETURNS TEXT
BEGIN
    DECLARE nomsClients TEXT;
    SELECT GROUP_CONCAT(nom SEPARATOR ', ') INTO nomsClients FROM clients WHERE ville = nomVille;
    RETURN nomsClients;
END;

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables