Contraintes d’intégrité : in the creation
create table produit3 (id3 int unique auto_increment,marque varchar(10),prix float, idc int,
constraint fk_cat2 foreign key (idc) references categorie2(idcat) on update cascade on delete set
null);
Contraintes d’intégrité : if you want to add it
Ajouter clé primaire dans la table catégorie :
MariaDB [ecole]> alter table categorie modify id_cat int primary key;
Rendre le champ id_cat indexé :
MariaDB [ecole]> alter table categorie add index (id_cat);
Supprimer les données depuis la table produit :
MariaDB [ecole]> delete from produit;
Ajouter la contrainte d’intégrité Foreign key dans la table produit :
MariaDB [ecole]> alter table produit add constraint key_cat foreign key (id_cat) references
categorie(id_cat);
Modification d’une contrainte Foreign Key :
La modification d’une contrainte Foreign Key passe par la suppression et recréation.
Vérification de la configuration actuelle :
MariaDB [ecole]> show create table produit2;
Le résultat de cette requête nous indique tous les champs et les contraintes créées.
Suppression de la contrainte existante :
MariaDB [ecole]> Alter table produit2 drop constraint fk_cat ; | pour Foreign key : Alter table
produit2 drop foreign key fk_cat;
Redéfinition de la contrainte avec les nouveaux paramètres :
MariaDB [ecole]> alter table produit2 add constraint fk_cat2 foreign key(idc) references
categorie2(idcat) on delete restrict on update cascade;
Les fonctions prédéfinies du système (caractère,date,numérique)
1- Fonctions chaines de caractères :
La fonction Concat :
Fonction permettant de combiner les informations de plusieurs champs en une seule chaine.
Exemple : select concat(‘’L’employé ‘’, Nom , ‘’ ‘’,prenom, ‘’ touche ‘’, salaire) from emp ;
La fonction Concat_ws :
Même principe que contact, c’est juste que ws permet de mettre un caractère séparateur prédéfini :
Exemple : select concat_ws(‘@’,nom,prenom) from employe ;
La fonction SUBSTRING:
Substring permet de couper une chaine de caractères et de renvoyer les caractères qui nous
intéresse :
Exemple : select substring(nom,3) from employes;
Renvoi les caractères à partir du 3ème caractère du nom.
Exemple 2 : select substring(nom,-3) from employes;
Renvoi les caractères à partir du 3éme caractère du nom à compter de la fin du nom.
Exemple 3 : select substring(nom,2,3) from employes;
Renvoi 3 caractères à partir du 2ème caractère de la chaine.
Exercices :
MariaDB [ecole]> select concat(substring(prenom,1,1),".",nom,"@Estem.ma") from employes where
id = 1;
+-------------------------------------------------------------------------------+
| concat(substring(prenom,1,1),".",nom,"@Estem.ma") |
+-------------------------------------------------------------------------------+
| [email protected] |
+-------------------------------------------------------------------------------+
La fonction Lenght ou char_length :
Cette fonction permet de renvoyer la longueur d’une chaine de caractère.
Exemple : select nom, lenght(nom) from employes ;
La fonction Upper et Lower :
Permettant de mettre en majuscule ou en minuscule une chaine.
Select upper(nom),lower(prenom) from employes;
La fonction Locate :
Permet de localiser la position d’un caractère dans une chaine.
select locate ("a",nom) from etudiant;
La fonction Replace :
Remplace une chaine par une autre :
Select Nom, replace(nom,’a’,’b’) from employes;
Exercice 2:
On veut afficher “Long” quand le nom de l’employé dépasse 5 caractères et ‘’Court’’ quand c’est le
contraire.
Avec Case :
MariaDB [ecole]> select nom,case when length(nom) >= 5 then "Long" when length(nom) < 5 then
"Court" END as longueur from etudiant;
OR select nom,case when length(nom) >= 5 then "Long" else "Court" END as longueur from
etudiant;
+---------+--------------+
| nom | longueur |
+---------+--------------+
| Roucadi | Long |
| MAN | Court |
+---------+-------------+
Avec IF :
select nom,if(length(nom) >= 5,"Long","Court") as longueur from etudiant;
1- Fonctions de Date :
La fonction Now() :
Permet de renvoyer la date et l’heure système.
La fonction Curdate() :
Permet de renvoyer la date système.
La fonction Curtime() :
Permet de renvoyer l’heure système.
Exemple : select now(),curdate(),curtime();
La fonction Day() : Le numéro du jour : select day(now()) ;
La fonction Dayname() : Le nom du jour : select dayname(now()) ;
La fonction Month() : Le numéro du mois : select month(now()) ;
La fonction Monthname() : Le nom du mois : select monthname(now()) ;
La fonction Year() : l’année ; select year(now()) ;
La fonction hour() : l’heure : select hour(now()) ;
La fonction minute() : la minute : select minute(now()) ;
La fonction second() : la seconde : select second(now()) ;
La fonction AddDate() : Ajouter un nombre de à une date : select adddate(now(),50) ;
La fonction Date_ADD : fonction permettant d’ajouter une durée à une date mais avec
une precision supérieur à ADDDATE
Exemple : select Date_ADD(now(),interval 1 week);
La fonction Date_Sub : permet de retrancher une durée d’une date.
Exemple : select Date_Sub(now(),interval 1 week);
La fonction Date_Diff : permet de retourner la différence entre deux date
Exemple : select datediff(now(),’2022-03-01’);
La fonction Date_format : permet de retourner une date selon un format souhaité
Exemple : select Date_format(now(),’%W%M%e%Y’);
Tableau des options utilisées avec Date_format :
%a Les jours de la semaine sur trois lettres
%b Les mois en 3 lettres : jan/fev
%c Les mois en numérique 1,2 … 12 : without leading zeros
%d Les jours en numérique 1… 31 sur with leading zeros / e% : without leading
zeros
%W Les jours en caractères : Lundi, mardi …
%M Les mois en caractères : Janvier…
%m Les mois en numérique : with leading zeros
%y L’année sur deux chiffres 22,23…
%Y L’année sur 4 chiffres 2023
%H L’heure en mode 24
%h L’heure en mode AM PM
%i Les minutes
%s Les secondes
Exercice :
Ecrivez une requête qui permet de renvoyer le nombre de jour que vous avez vécu ?
Select datediff(now(),’1992-04-08’);
Combien de mois,de semaine, et d’heure avez-vous vécu ?
Select TimeStampDiff(month,’1992-04-08’,now()), TimeStampDiff(week,’1992-04-
08’,now()),TimeStampDiff(hour,’1992-04-08’,now());
Afficher au format Samedi 13 Decembre 2023 la date de votre premier anniversaire.
Select date_format(date_add(‘1992-04-08’,interval 1 year),’%W%d%M%Y’)
Même chose que la question 3, on veut connaitre la date de votre 100éme anniversaire.
Select date_format(date_add(‘1992-04-08’,interval 100 year),’%W%d%M%Y’)
2- Fonctions numériques :
Les fonctions numériques sont créées pour nous aider à faire des traitements sur des champs
numériques avec un minimum d’effort.
Count : permet de renvoyer le nombre de lignes que nous avons dans une table ou une
sélection.
Exemple 1 : Renvoyer le nombre de produit que nous avons en stock
Select count(*) from produit ;
Exemple 2 : Renvoyer le nombre de marque hp que nous avons en stock
Select count(*) from produit where marque = ‘hp’;
Max/Min : Renvoie la valeur max ou min d’un champ numérique
Exemple 1: select Max(prix) from produit;
Exemple 2: select Min(prix) from produit;
Sum : calcul la somme des valeurs d’un champ numérique
Exemple : select sum(prix) from produit;
AVG : calcul la moyenne des valeurs d’un champ numérique :
Exemple : select avg(prix) from produit;
Groupement :
L’utilisation des fonctions sera plus significative quand on arrive à grouper les résultats en fonction
d’un ou plusieurs critères.
Exemple 1 : On veut calculer la somme des salaires en fonction des services :
select id_depart,sum(salaire) from employes group by id_depart;
Exemple 2 : On veut calculer la somme des salaires par services et par type de contrat (cdi, cdd...) :
select id_depart,id_manager,sum(salaire) from employes group by id_depart,id_manager;
30/12/2023
- Filtrage des résultats des fonctions de groupement
Ce filtre permettra de cibler les valeurs souhaitées après calcul de la somme, moyenne ,etc…
Exemple : on veut afficher la somme des salaires par services, on veut garder uniquement les
supérieures à 10000.
- Pour cela nous utiliserons Having
Select sum(salaire), code_service from employes group by code_service Having
sum(salaire) > 10000
- Différence entre Where et Having
Where c’est pour un filtrage avant calcul et groupement
Having c’est pour un filtrage après calcul et groupement.
Exemple :
On veut calculer la somme des salaires par service, ce calcul doit inclure seulement ceux qui ont un
salaire supérieur à 3000.
Select sum(salaire), id_depart from employes where salaire > 3000 group by id_depart;
Exemple : On veut calculer la somme des salaires par service, ce calcul doit inclure uniquement ceux
qui ont un salaire supérieur à 3000 et afficher uniquement les sommes inférieures à 10000.
Select sum(salaire), id_depart from employes where salaire > 3000 group by id_depart
having sum(salaire) < 10000;
- Fonctions de calcul et valeurs Nulle
Exemple : Dans votre table des employes, affecter à l’un de vos salariés un salaire null
Update employes set salaire = null where id = 1;
Calculer ensuite la Moyenne et la somme , qu’est que vous remarquez ?
Calculer la moyenne en utilisant ifnull
- Le calcul de la somme n’est pas impacté par les valeurs nulles, le système les traites
automatiquement.
- Mais pour la moyenne le résultat est erroné, donc, il faut utiliser ifnull.
Select avg(ifnull(salaire,0)) from employes.
Correction DS:
Stocked procedure :
MariaDB [ecole]> delimiter //
MariaDB [ecole]> create procedure get_employes() begin select * from employes; end //
Query OK, 0 rows affected (0.004 sec)
MariaDB [ecole]> delimiter ;
MariaDB [ecole]> call get_employes();
- Procedure avec variable interne
delimiter //
create procedure ps1(in code int)
Begin
Declare nb_lignes int default 0;
select count(*) into nb_lignes from produit where id_cat = code;
select nb_lignes;
END//
delimiter ;
call ps(1);
- Procédure avec paramètre d’entrée et paramètre de sortie
delimiter //
create procedure ps(in code int,out p2 int) Begin select count(*) into p2 from produit where id_cat =
code; END//
delimiter ;
call ps(1,@nbr);
select @nbr;
Function :
MariaDB [ecole]> delimiter //
MariaDB [ecole]> create function get_salairenet(salaire_brut decimal(10,2),prime decimal(10,2))
returns decimal(10,2) begin declare salaire_net decimal(10,2); set salaire_net = salaire_brut + prime;
return salaire_net; end //
Query OK, 0 rows affected (0.006 sec)
MariaDB [ecole]> delimiter ;
MariaDB [ecole]> select get_salairenet(5000,1200) as salaire_net;
