23/05/2024
RÉSUMÉ DE COURS par A.EL BYED
Filière BCG – Semestre 2
2023-2024
Plan
LDD: Langage de Définition de Données
(CREATE, DROP, ALTER)
LMD: Langage de Manipulation de Données (SELECT,
UPDATE, INSERT, DELETE)
LCD: Langage de Contrôle de Données (GRANT,
REVOKE)
LCT: Langage de Contrôle de Transaction (COMMIT,
SAVEPOINT, ROLLBACK)
A.EL BYED 1
23/05/2024
LDD
CREATE : Créer des objets de données (des tables, des
indexes, des vues)
DROP : Supprimer des objets de données
ALTER : Modifier la définition des objets de données.
Pour mettre à jour la structure d’un objet sans perte de
données (mise à jour progressive).
CREATE
ETUDIANT (CodeEtudiant, Nom, Prenom)
- MATIERE (CodeMat, LibelleMat, CoeffMat)
- EVALUER (#CodeEtudiant, #CodeMat, Date, Note)
Syntaxe:
CREATE TABLE nomTable (
colonne1 type1 [Primary Key] [DEFAULT valeur1] [NOT NULL],
colonne2 type2 [DEFAULT valeur2] [NOT NULL],
[CONSTRAINT nomContrainte1 typeContrainte1]…
);
Exemple
CREATE TABLE personne(
id INT PRIMARY KEY,
nom VARCHAR(30),
age INT
);
A.EL BYED 2
23/05/2024
CREATE: contraintes d’intégrité
1. PRIMARY KEY : définit l'attribut comme la clé primaire. Elle a : uniques
et non NULL
id INT PRIMARY KEY ou CONSTRAINT pk_personne PRIMARY KEY (id)
AUTO_INCREMENT: L'incrémentation automatique
2. FOREIGN KEY : contrôle l'intégrité référentielle, elle déclare une clé
étrangère.
Si rel2(…,…,#rel1.id1) on déclare dan la rel2 la contrainte:
CONSTRAINT fk_cat FOREIGN KEY (id1) REFERENCES rel1(id1)
3. UNIQUE : impose une valeur distincte au niveau de la table
libelle VARCHAR(30) UNIQUE
4. CHECK () : contrôle la validité de la valeur des attributs spécifiés dans la
condition dans le cadre d'une restriction de domaine.
CONSTRAINT chk_prix CHECK (prix > 0)
ALTER
ALTER TABLE: modifie une table existante en ajoutant, supprimant ou modifiant des
colonnes, des contraintes ou des clés
Syntaxe:
ALTER TABLE nom_table
ADD [COLUMN] nom_colonne type_donnees
DROP [COLUMN] nom_colonne
MODIFY [COLUMN] nom_colonne type_donnees
CHANGE [COLUMN] ancien_nom nouveau_nom description (Renommer)
);
Exemple
ALTER TABLE personne
ADD COLUMN surnom VARCHAR(100);
DROP COLUMN age
MODIFY COLUMN nom VARCHAR(100)
CHANGE COLUMN nom nomComplet VARCHAR(100)
CHANGE et MODIFY sont équivalente mais change permet de renommer la
colonne en même temps. Si vous ne désirez pas la renommer, il suffit d'indiquer deux
fois le même nom.
A.EL BYED 3
23/05/2024
DROP TABLE
Permet de supprimer des structures de données
(schémas, tables, vues...).
DROP TABLE nomTable [CASCADE];
CASCADE: Utilisé pour supprimer toutes les
contraintes d'intégrité référentielle faisant référence
aux clés primaires et uniques de la table supprimée.
Exemple: DROP TABLE personne;
ALTER: contraintes d’intégrité
ALTER TABLE: modifie une table existante en ajoutant, supprimant
ou modifiant des colonnes, des contraintes ou des clés
Syntaxe:
ALTER TABLE nom_table
ADD CONSTRAINT nom_contrainte definition_contrainte
DROP CONSTRAINT nom_contrainte
);
Exemple
ALTER TABLE personne
ADD CONSTRAINT chk_ageCHECK (Age >18);
DROP CONSTRAINT chk_age;
Pour modifier une contrainte existante en MySQL, vous devez
d'abord la supprimer puis en ajouter une nouvelle
A.EL BYED 4
23/05/2024
Plan
LDD: Langage de Définition de Données (CREATE,
DROP, ALTER)
LMD: Langage de Manipulation de Données
(SELECT, UPDATE, INSERT, DELETE)
LCD: Langage de Contrôle de Données (GRANT,
REVOKE)
LCT: Langage de Contrôle de Transaction (COMMIT,
SAVEPOINT, ROLLBACK)
LMD
INSERT : insérer des lignes (enregistrements) dans
une table.
UPDATE : modifier des enregistrements existants dans
une table.
DELETE : supprimer des enregistrements existants
dans une table.
SELECT : rechercher des informations en interrogeant
une ou plusieurs tables
A.EL BYED 5
23/05/2024
INSERT
L'insertion de données dans une table. Deux possibilités
1. soit donner une valeur pour chaque colonne de la ligne,
INSERT INTO nomTable
VALUES (valeur1 , valeur2…);
Ex: INSERT INTO personne VALUES (6, 'Jean', 'Mia', 30);
2. soit ne donner les valeurs que de certaines colonnes,
auquel cas il faut bien sûr préciser de quelles colonnes il
s'agit.
INSERT INTO nomTable [(colonne1, colonne2…)]
VALUES (valeur1 , valeur2…);
Ex: INSERT INTO personne (personneId, Nom, Prenom,
Age) VALUES (7, 'Bennis', 'Mohamed', 28);
A.EL BYED 6
23/05/2024
Insertion multiple
INSERT INTO personne (persId, Nom, Prenom, Age)
VALUES (8, 'Alami', 'Amal’, 38),
(9, 'Mojahid', 'Aya', 40),
(10, 'Cherkawi', 'Moad', 52);
Insertion en utilisant des fichiers
externes
LOAD DATA INFILE:
permet de charger les données d'un fichier texte ou
CSV directement dans une table MySQL
LOAD DATA INFILE '/chemin/vers/votre/fichier/donnees.csv'
INTO TABLE ma_table
FIELDS TERMINATED BY ', ' => séparateur de champ (ici, virgule)
LINES TERMINATED BY '\n' => séparateur de ligne (ici, saut de ligne)
IGNORE 1 LINES; =>ignorer la première ligne si elle contient des en-
têtes
A.EL BYED 7
23/05/2024
L’instruction SELECT
Permet de Rechercher, sélectionner et afficher des
données d’une manière structurée.
Sélection de données répondant à certaines
conditions.
Tri des données en affichage.
Elimination des données en double.
Syntaxe:
SELECT colonne1, colonne2, …
FROM nom_table
A.EL BYED 8
23/05/2024
Projection
Exemple: Sélectionner le nom et le prénom
Sélection: WHERE
SELECT colonne1, colonne2, …
FROM nom_table
WHERE condition
Exemple: Sélectionner toutes les colonnes des
personnes dont le prénom est Marie
A.EL BYED 9
23/05/2024
Condition WHERE: opérateurs
Opérateurs Simples
Opérateurs Logiques
Exemple: Sélectionner toutes les personnes dont le
prénom est "Marie" et l'âge est inférieur à 48 ans.
WHERE Prenom = "Marie" AND age < 48
Tables de vérité
AND VRAI FAUX OR VRAI FAUX
VRAI VRAI FAUX VRAI VRAI VRAI
FAUX FAUX FAUX FAUX VRAI FAUX
XOR VRAI FAUX NOT
VRAI FAUX VRAI VRAI FAUX
FAUX VRAI FAUX FAUX VRAI
A.EL BYED 10
23/05/2024
Alias sur une colonne : AS
Les alias permettent de renommer des colonnes à
l’affichage ou des tables dans la requête.
SELECT colonne_original AS ColAlias
FROM nom_table AS tableAlias
La clause DISTINCT
La clause DISTINCT permet de supprimer les
doublons des résultats.
SELECT DISTINCT Colonne
A.EL BYED 11
23/05/2024
WHERE: d'autres possibilités
1. Opérateur IN: permet de spécifier une liste de
valeurs
WHERE Prenom IN ( "Marie" , "Albert" , "Ahmad" )
2. Opérateur BETWEEN: permet de spécifier un
intervalle de valeurs.
WHERE age BETWEEN 25 AND 48
3. Opérateur LIKE: permet d’effectuer des recherches
de motifs dans les données textuelles, en utilisant
des caractères génériques "jokers" :
'_' : qui représente un seul caractère (ou aucun).
'%' : qui représente plusieurs caractères,
IS NULL / IS NOT NULL
IS NULL: permet de vérifier si une valeur dans une
colonne est NULL (aucune valeur n'est présente dans
ce champ)
IS NOT NULL: permet de vérifier si une valeur dans
une colonne n'est pas NULL.
A.EL BYED 12
23/05/2024
Clause ORDER BY
ORDER BY : Permet de trier les résultats selon une ou
plusieurs colonnes.
SELECT colonne1, colonne2, …
FROM nom_table
WHERE condition
ORDER BY col1, col2, … [ASC | DESC]
ASC: pour trier par un ordre croissant.
DESC: pour trier par un ordre décroissant.
Par défaut, le tri se fait par ordre croissant (ASC)
A.EL BYED 13
23/05/2024
Tri: Exemple
Sélectionner toutes les personnes et trier le résultat par
ordre croissant des valeurs de la colonne age :
Fonctions d’agrégation
A.EL BYED 14
23/05/2024
Les fonctions d'agrégation
Les fonctions d'agrégation, ou de groupement, sont
des fonctions qui vont regrouper les lignes,
afin d’appliquer des fonctions statistiques sur les
données groupées.
Exemple: compter des lignes, connaître une moyenne,
trouver la valeur maximale d'une colonne,...).
COUNT()
La fonction COUNT(col/*) permet de compter le nombre
des lignes sélectionnées .
Count(*) : compte toutes les lignes,
Count(col), compte seules les lignes dont la valeur de colonne
n'est pas NULL.
Exemple1: Combien de chiens avons-nous ?
SELECT COUNT(*) AS nb_chiens
FROM animal
WHERE espece = ‘chien’
Exemple2: Combien d’espèces avons-nous ?
SELECT COUNT(DISTINCT espece) AS nb_espece
FROM animal
A.EL BYED 15
23/05/2024
Fonctions: MIN, MAX, SUM, AVG
MIN(): retourne la plus petite valeur de la colonne.
MAX(): retourne la plus grande valeur de la colonne.
SUM(): calcule la somme des valeurs de la colonne.
AVG(): calcule la moyenne des valeurs de la colonne.
Exemple:
SELECT MIN(age), MAX(age), SUM(poids),
AVG(poids)
FROM animal
Clause GROUP BY
permet de définir des regroupements
Elle permet d’effectuer des calculs statistiques pour
chacun des regroupements.
⇒Un SELECT avec une clause GROUP BY produit une
table résultat comportant une ligne pour chaque
groupe.
SELECT colonne1, fonction_agrégation(colonne2)
FROM nom_table
GROUP BY colonne1
A.EL BYED 16
23/05/2024
GROUP BY: Exemple
Compter les animaux, groupés
par espèce
Compter le nombre de mâles et de femelles pour chaque espèce
SELECT espece, sexe, COUNT(*) AS nb_anim_especeETsexe
FROM animal
GROUP BY espece, sexe
Clause HAVING
HAVING est utilisée pour filtrer les résultats d'une
requête qui contient une clause GROUP BY.
SELECT colonne1, fonction_agrégation(colonne2)
FROM nom_table
GROUP BY colonne1
HAVING condition
La condition peut être n'importe quelle expression
valide qui évalue à vrai ou faux en impliquant une
fonction d'agrégation.
A.EL BYED 17
23/05/2024
WHERE versus HAVING
Contrairement à la clause WHERE, qui filtre les lignes
avant le regroupement, la clause HAVING filtre les
groupes de lignes après le regroupement.
Exemple: Compter et afficher le nombre d'animaux
pour les espèces dont le nombre d'animaux est
supérieur ou égal à 2.
Chaînes de caractères;
Dates
A.EL BYED 18
23/05/2024
Fonctions sur les chaînes de
caractères
CONCAT(chaîne1, chaîne2): renvoie la chaîne en
concaténant chaîne1 et chaîne2.
LOWER(chaîne): Renvoie la chaîne en ayant mis
toutes les lettres en minuscules.
UPPER(chaîne): Renvoie la chaîne en ayant mis
toutes les lettres en majuscules.
LENGTH(chaîne): Renvoie la longueur de la chaîne.
Exemple:
SELECT CONCAT(nom, ' ', prenom) AS 'Nom & prénom'
FROM personne
Fonctions pour manipuler les dates
NOW(), SYSDATE() : obtenir la date du jour.
MONTH(date) : donne le numéro du mois (1 à 12).
MONTHNAME(date): donne le nom du mois.
YEAR(date): extrait l'année.
DAY(date) ou DAYOFMONTH(date): donne le jour du
mois (sous forme de nombre entier de 1 à 31).
DAYOFWEEK(date) : donne l'index du jour de la semaine
(nombre de 1 à 7 avec 1 = dimanche,..).
DAYNAME(date) : donne le nom du jour de la semaine.
DAYOFYEAR(date) : retourne le numéro du jour par
rapport à l'année (de 1 à 366).
DATEDIFF(date1,date2): permet de calculer le nombre de
jours entre deux dates.
A.EL BYED 19
23/05/2024
Fonctions sur dates: Exemple
SELECT nom, date_naissance, MONTHNAME(date_naissance),
DAYNAME(date_naissance),
YEAR(date_naissance)
FROM etudiant;
LMD
UPDATE et DELETE
A.EL BYED 20
23/05/2024
Modification des données
L’instruction UPDATE est utilisée pour modifier un ou
plusieurs enregistrements d'une table.
UPDATE nomTable
SET colonne1 = val1, colonne2 = val2
WHERE condition;
À l'absence de la clause WHERE, la modification se
fera sur toutes les lignes de la table.
UPDATE personne
SET age = 70
WHERE personneId = 1;
Suppression des données
L’instruction DELETE permet de supprimer un ou
plusieurs enregistrements d'une table.
DELETE FROM nomTable
WHERE condition;
La clause WHERE précise quelles lignes doivent être
modifiées.
DELETE FROM personne
WHERE prenom = 'Marie';
Supprimer tous les étudiants nés après DELETE FROM etudiant
le 1er janvier 2000. WHERE date_naissance > '2000-01-01';
A.EL BYED 21
23/05/2024
Options des clés étrangères
Categorie (id_cat, libelle_cat)
Livre (id_livre, titre_livre, annee, #id_cat)
En spécifiant ces options lors de la définition de la clé
étrangère dans la table "Livre", nous pouvons
déterminer comment la base de données doit réagir
lorsque la catégorie parente est modifiée ou supprimée.
Options clés étrangères: ON DELETE
L'option ON DELETE est utilisée lors de la création ou de
la modification d'une clé étrangère
Avec CREATE TABLE
CREATE TABLE nom_table
CONSTRAINT nom_contrainte FOREIGN KEY (nom_colonne)
REFERENCES nom_table_parente (nom_colonne)
ON DELETE action_deletion;
Avec ALTER TABLE
ALTER TABLE nom_table
ADD CONSTRAINT nom_contrainte FOREIGN KEY (nom_colonne)
REFERENCES nom_table_parente (nom_colonne)
ON DELETE action_deletion;
action_deletion : est l'action à effectuer lorsque la ligne
parente est supprimée.
A.EL BYED 22
23/05/2024
action_deletion: Options de
l'action à effectuer :
CASCADE : si la ligne parente est supprimée, toutes les
lignes enfants correspondantes seront également
supprimées automatiquement.
SET NULL : si la ligne parente est supprimée, les valeurs
des clés des lignes enfants correspondantes sont
remplacées par NULL.
NO ACTION : on rejette toute tentative de suppression de
la ligne parente si des lignes enfants existent encore.
RESTRICT : Il s'agit d'une option similaire à NO ACTION.
SET DEFAULT : si la ligne parente est supprimée, les
valeurs des clés étrangères correspondantes dans les lignes
enfants sont remplacées par leur valeur par défaut.
CASCADE: exemple
CREATE TABLE Categorie (
CREATE TABLE Livre (
id_cat INT PRIMARY KEY,
id_livre INT PRIMARY KEY, libelle_cat VARCHAR(255));
titre_livre VARCHAR(255),
annee INT,
id_cat INT,
FOREIGN KEY (id_cat) REFERENCES Categorie(id_cat) ON DELETE
CASCADE);
DELETE FROM Categorie WHERE id_cat = 2;
1- La ligne dans la table "Categorie" avec l'ID 2 sera supprimée.
2- les "Livre" qui ont une référence à la catégorie avec l'ID 2 seront
également supprimées.
A.EL BYED 23
23/05/2024
Options clés étrangères: ON UPDATE
L'option ON UPDATE permet de spécifier le
comportement de MySQL lorsqu'une référence
parente est mise à jour
CONSTRAINT nom_contrainte FOREIGN KEY (nom_colonne)
REFERENCES nom_table_parente (nom_colonne)
ON UPDATE action_update;
action_update : peut être une des options suivantes :
CASCADE, SET NULL, NO ACTION, RESTRICT ou
SET DEFAULT
ON UPDATE: exemple
UPDATE Categorie SET id_cat = 200 WHERE id_cat =
2;
1. Modifier id_cat du cat 200 par 2
2. Si l'option ON UPDATE CASCADE est définie, la
mise à jour des clés étrangères des livres de catégorie
200 sera effectuer d’une manière automatique
A.EL BYED 24
