CM 4 - BDD

name: inverse
layout: true
class: center, middle, inverse

---
# Base de données - CM 4
## Inès de Courchelle
## 2024-2025

![image](img/CY-tech.png)

---
layout: false

# Rappel
## Rappel - Normalisation
L'ensembe des règles FN
 - FN1 => Pas d'attributs composés (tableau)
 - FN2 => Attention aux dépendances fonctionnelles élémentaires
 - FN3 => si deux attributs s'influencent entre eux on les isole dans une nouvelle entité !

## Rappel - LDD
- Langage de Création du squelette d'une BDD
- Instructions
  - CREATE
  - ALTER
  - DROP

---
# Du LDD au LMD
## Plan
1. Langage de manipulation de données (LMD)
2. Mes premiers Select

---
# LMD
## Les instructions  :
   - INSERT : Insertion d'enregistrement
   - UPDATE : Modification d'enregistrement
   - DELETE : Suppression d'enregistrement
   - SELECT : Selection d'enregistrement

## Attention
  - LDD permet d'effectuer des opérations sur les structures
  - LMD permet d'effectuer des opérations sur les données
---
# LMD
## Les Opérations
  - INSERT
  - UPDATE
  - DELETE
  - SELECT

---
# Les chats
## LDD
```sql
CREATE TABLE Animal(
    idAnimal INTEGER(30),
    appelation VARCHAR(30),
    type VARCHAR(30),
    CONSTRAINT pk_idAnimal PRIMARY KEY (idAnimal)
);

CREATE TABLE Chat (
    idChat INTEGER(30),
    prenom VARCHAR (30),
    robe VARCHAR (30),
    idAnimal INTEGER(30),
    CONSTRAINT pk_idChat PRIMARY KEY (idChat),
    FOREIGN KEY (idAnimal) REFERENCES Animal(idAnimal)

);
```

---
# Les chats
## MCD
<img src="img/chatsMCD.png" alt="drawing" width="500"/>

## MLD
<div class="cadre" id="cadre" name="cadre">
Animal(<u>idAnimal</u>, appelation,type) <br>

Chat(<u>idChat</u>, prénom,robe,#idAnimal)
</div>

---
# Les chats
## LDD

---
# Tips for Tips
## Lorsqu'on lance le script plusieurs fois
<video src="video/illustration1.mp4" width="600" height="400" controls="controls"></video>

---
# Les chats
## Etape 1
Maintenant que l'on a créé la table, il faut rajouter des données

## NANNN ?

---
# Les Opérations INSERT
## Définition
```sql
    INSERT INTO <nom_table> VALUES (<valeur colonne 1>, <valeur colonne 2> ...);
```
## Solution 1 (*Attention à l'ordre des colonnes !*)
```sql
    INSERT INTO Animal VALUES (01,"tigre","sauvage");
    INSERT INTO Animal VALUES (02,"chat","domestique");

INSERT INTO Chat VALUES (01,"Lizzy", "noire");
    INSERT INTO Chat VALUES (02,"Pounette","tache isabelle");
    INSERT INTO Chat VALUES (03,"Miaoumix","grise");

```
## Solution 2
```sql
    INSERT INTO Animal (idAnimal,appelation,type) VALUES (01,"tigre","sauvage");
    INSERT INTO Animal (idAnimal,appelation,type) VALUES (02,"chat","domestique");

```

---
# Les opérations INSERT + CP
## Rappel du LDD

```sql
CREATE TABLE Animal(
    idAnimal INTEGER(30),
    appelation VARCHAR(30),
    type VARCHAR(30),
    CONSTRAINT pk_idAnimal PRIMARY KEY (idAnimal)
);

);
```

---
# Les opérations INSERT + CP
## Attention une clé primaire doit être UNIQUE
```sql
   INSERT INTO Animal VALUES (01,"chat","domestique");
   INSERT INTO Chat VALUES (01,"Lizzy", "noire",01);
   INSERT INTO Chat VALUES (01,"Pounette","tache isabelle",01);
```
- Ici, Lizzy et Pounette ne peuvent pas avoir le même identifiant
- Une Clé primaire est unique !
- Il faut renseigner la clé étrangère !!

## Solution 1
```sql
   INSERT INTO Chat VALUES (01,"Lizzy", "noire",01);
   INSERT INTO Chat VALUES (02,"Pounette","tache isabelle",01);
```

---
# Les opérations INSERT + CP
## Illustration
<video src="video/illustration2.mp4" width="600" height="400" controls="controls"></video>

---
# Les opérations INSERT + CP
## Solution 2 (The best)
Etape 1 : Création des tables
```sql
CREATE TABLE Animal(
    idAnimal INTEGER(30) NOT NULL AUTO_INCREMENT,
    appelation VARCHAR(30),
    type VARCHAR(30),
    CONSTRAINT pk_idAnimal PRIMARY KEY (idAnimal)
);

CREATE TABLE Chat (
    idChat INTEGER(30) NOT NULL AUTO_INCREMENT,
    prenom VARCHAR (30),
    robe VARCHAR (30),
    CONSTRAINT pk_idChat PRIMARY KEY (idChat)
);
```
Etape 2 : Insertion dans les tables
```sql
   INSERT INTO Chat VALUES (NULL,"Lizzy", "noire");
   INSERT INTO Chat Values (NULL,"Pounette","tache isabelle");
```

---
# Les opérations INSERT + CE
## On continue
Nous considérons l'exemple précédent en rajoutant les clés étrangères
```sql
CREATE TABLE Animal(
    idAnimal INTEGER(30),
    appelation VARCHAR(30),
    type VARCHAR(30),
    CONSTRAINT pk_idAnimal PRIMARY KEY (idAnimal)
);

---
# Les opérations INSERT + CE
## Insertion en premier de la table << mère >>
```sql
    INSERT INTO Animal VALUES (01,"tigre","sauvage");
    INSERT INTO Animal VALUES (02,"chat","domestique");
```
## Insertion en second de la table << fille >>
```sql
   INSERT INTO Chat VALUES (01,"Lizzy", "noire",02);
   INSERT INTO Chat Values (02,"Pounette","tache isabelle",02);
```

## Pourquoi ?
- On ne peut pas faire pointer une clé qui n'existe toujours pas
- Tant que la << mère >> n'est pas créée on ne peut pas créer la << fille >>

---
# Les opérations INSERT + CE
## Attention Si on utilse AUTO INCREMENT
```sql
   INSERT INTO Animal VALUES (NULL,"tigre","sauvage");
   INSERT INTO Animal VALUES (NULL,"chat","domestique");

INSERT INTO Chat VALUES (NULL,"Lizzy", "noire",02);
   INSERT INTO Chat Values (NULL,"Pounette","tache isabelle",02);
```
**Il faut connaître l'idAnimal correspondant au chat avant de le mettre en clé étrangère dans << Chat >>**

---
# INSERT partiel
## Create table
```sql
CREATE TABLE Chat (
    idChat INTEGER(30),
    prenom VARCHAR (30),
    robe VARCHAR (30),
    idAnimal INTEGER(30),
    CONSTRAINT pk_idChat PRIMARY KEY (idChat),
    FOREIGN KEY (idAnimal) REFERENCES Animal(idAnimal)
);

INSERT INTO Chat(prenom) VALUES ('Garfield');
```
| idChat    | prenom    | robe      | Clé Etrangère |
| --------- | --------- | --------- | ---------     |
| 1         | Garfiel   | NULL      | NULL          |

**En théorie c'est possible MAIS !**
<center>
<img src="img/bug_default.png"  width=400/>
</center>

---
# INSERT partiel
## Solution => AUTO_INCREMENT
```sql
CREATE TABLE Chat (
    idChat INTEGER(30) AUTO_INCREMENT,
    prenom VARCHAR (30),
    robe VARCHAR (30),
    idAnimal INTEGER(30),
    CONSTRAINT pk_idChat PRIMARY KEY (idChat),
    FOREIGN KEY (idAnimal) REFERENCES Animal(idAnimal)
);
```
<iframe src="https://giphy.com/embed/nVXzt7FSJlX7W" width="480" height="239" frameBorder="0" class="giphy-embed" allowFullScreen></iframe><p><a href="https://giphy.com/gifs/toy-story-nVXzt7FSJlX7W"></a></p>

---
# Le mot clé Default
## A quoi sert-il ?
Faciliter les INSERT quand il y en a beaucoup !
## Pourquoi ?
Attribuer une valeur par defaut lorsqu'on ne l'insère pas dans la base
## Quand ?
à la création de la table !

```sql
CREATE TABLE Animal(
    idAnimal INTEGER(30) AUTO_INCREMENT,
    appelation VARCHAR(30),
    type VARCHAR(30) DEFAULT 'sauvage',
    CONSTRAINT pk_idAnimal PRIMARY KEY (idAnimal)
);

INSERT INTO Animal VALUES(null,'tigre',default);
```

| idAnimal  | appelation | type      |
| --------- | ---------  | --------- |
| 1         | Tigre      | sauvage   |

---
# Le mot clé NOT NULL

## A quoi sert-il ?
Obliger d'attribuer des valeurs à des colonnes
## Pourquoi ?
Assurer l'intégrité des données dans la base
## Quand ?
à la création de la table !

```sql
CREATE TABLE Animal(
    idAnimal INTEGER(30) AUTO_INCREMENT,
    appelation VARCHAR(30) NOT NULL,
    type VARCHAR(30) DEFAULT 'sauvage',
    CONSTRAINT pk_idAnimal PRIMARY KEY (idAnimal)
);

INSERT INTO Animal VALUES(null,null,default);
```
<img src="img/notnull.png"  width=500/>

---
# Les opérations UPDATE
## À quoi sert un UPDATE ?
Mettre à jour les INSERT déjà réalisés
## Pourquoi ?
Modifier des données déjà présentes dans la base
## Comment ?

```sql
UPDATE < nom_table >
SET < nom_colonne1 > = < expression1 >,
    < nom_colonne2 > = < expression2 >
WHERE < condition1 >
AND < condition2 >
AND < condition3 >;
```
.underR[Attention]
- si aucune clause "WHERE" n'est spécifié
- Alors toutes les données de la tables sont supprimées
- Ne pas oublier le ; à la fin du UPDATE
---
# UPDATE exemple
## Before
| idChat | prenom | robe   | # idAnimal |
| ------ | ------ | ------ | ------     |
| 01     | Lizzy  | Grise  | 01         |

```sql
UPDATE Chat
SET robe = 'Noire'
WHERE idChat=01
AND robe='Grise';
```
**Attention pour les types INTEGER pas besoin de côtes**

## After

| idChat | prenom | robe   | # idAnimal |
| ------ | ------ | ------ | ------     |
| 01     | Lizzy  | Noire  | 01         |

---
# Les opérations DELETE
## À quoi sert un DELETE ?
Supprimer les INSERT déjà réalisés
## Pourquoi ?
Supprimer des données déjà présentes dans la base
## Comment ?
```sql
DELETE FROM < nom_table >
WHERE < condition1 >
AND < condition2 >
AND < condition3 >;
```

.underR[Attention]
- Vérifier si la ligne supprimée n'est pas en clé étrangère dans une table fille
- Si aucune clause "WHERE" n'est spécifiée, alors toutes les données de la table sont supprimées
- Ne pas oublier le ; à la fin du DELETE

---
# DELETE exemple
- Supprimer tous les chats dont la robe est noire !
```sql
DELETE FROM Chat WHERE robe='Noire';
```
- Supprimer tous les chats présents dans la table chat !
```sql
DELETE FROM Chat;
```
<center>
<img src="https://media.giphy.com/media/8OPbx53X3mFoWMw5aO/giphy.gif"  width=200/>
</center>

---
# Le Select
## À quoi çà sert ?
Sélectionner des données
## Pourquoi ?
Récupérer une ou plusieurs informations
## Comment ?

```sql
SELECT < nom_colonne1 >, < nom_colonne2 >
FROM < nom_table>
WHERE < condition1 >
AND < condition2 >
AND < condition3 >;
```
---
# Select
## Comment afficher tout le contenu de la table Chat ?

---
# La projection

## Définition
Récupérer seulement certaines colonnes
<img src="https://media.giphy.com/media/HpimxtNczl0WI/giphy.gif"  width=200/>

## Comment ?
```sql
SELECT <nom_colonne1 >, <nom_colonne2 >
FROM <nom_table>
```

**Attention**
- Séparer les noms des colonnes par des ,
- Orthographier exactement le nom des colonnes

---
# La projection
## Exemple 1
<img src="img/projection_example.gif"  width=600/>

---
# La projection
## Exemple 2
<img src="img/projection2.png"  width=400/>

---
# DISTINCT
## Pourquoi ?
Selectionner aucuns doublons dans la base de données

## Comment ?
Utiliser le mot clé DISTINCT dans le select

```sql
SELECT <nom_colonne1 >, DISTINCT(<nom_colonne2 >)
FROM <nom_table>
```
## Exemple :

Nous considérons les enregistrements suivants :

| idChat | prenom | robe   | # idAnimal |
| ------ | ------ | ------ | ------     |
| 01     | Leia   | Grise  | 02         |
| 02     | Misty  | Grise  | 02         |
| 03     | Felix  | Noire  | 02         |
| 04     | Lizzy  | Noire  | 02         |

---
# DISTINCT
## Sans Distinct
Pour chaque enregistrement on affiche la robe
<img src="img/distinct1.gif"  width=600/>

---
# DISTINCT
## Avec Distinct
Pour chaque enregistrement on affiche la robe sauf si c'est une robe qui existe déjà

---
# Les opérateurs 1/2
## De Comparaisons
=, <>, <=, >=, <, >

## Logiques
AND, OR, NOT

---
# Les opérateurs 2/2
## Exemple 1
```sql
SELECT *
FROM Chat
WHERE idChat <> 1 AND idChat <>3

```
Selectionner tous les chats dont l'identifiant est différent de 1 **AND** 3

## Exemple 2
```sql
SELECT *
FROM Chat
WHERE idChat <> 1 OR idChat <>3

```
Selectionner tous les chats dont l'identifiant est différent de 1 **OU** 3

---
# Vérifier si une donnée est nulle
## Les testeurs
- IS NULL
- IS NOT NULL

## Exemple
Sélectionner les chats qui n'ont pas de robe !
```sql
SELECT prenom
FROM Chat
WHERE robe IS NULL
```

---
# Les Likes
## Définition
Tester les chaînes de caractères
## Pourquoi ?
Utiliser pour les méta-caractères
## Comment ?
```sql
< nom_colonne > LIKE '< expression >
```
  - _ =>  remplace un caractère
  - % =>  remplace 0 ou n caractères

---
# Les Likes
## Exemple 1

Sélectionner les chats dont le prénom commencent par la lettre L

```sql
SELECT prenom
FROM Chat
WHERE prenom LIKE 'L%';
```

## Exemple 2

Sélectionner les chats dont le prénom contien un Z en troisième position

```sql
SELECT prenom
FROM Chat
WHERE prenom LIKE '__Z%';
```

---
# Les Likes
## Exemple 1

Sélectionner les chats dont le prénom commencent par la lettre L

---
# Les Likes
## Exemple 2

Sélectionner les chats dont le prénom contien un Z en troisième position

---
# UPPER-LOWER
## Pourquoi ?
- Convertir une chaîne de caractètere en majuscule ou minuscule
- Eviter les problèmes de sensibilité à la casse

## Comment ?
Utiliser les mots clés UPPER et LOWER

```sql
UPPER(< nom_colonne>) = UPPER(< chaine >)
```

---
# UPPER-LOWER
## Exemples

#### Exemple 1
<img src="img/UPPER.png"  width=220/>

#### Exemple 2
```sql
SELECT *
FROM Chat
WHERE UPPER(robe)=UPPER("grise");
```

#### Exemple 3
```sql
SELECT *
FROM Chat
WHERE LOWER(robe)=LOWER("grise");
```

---
# Le tri avec ORDER BY
## Pourquoi ?
Trier des résultats d'une projection
## Comment ?
  - Trier même s'il n'y a pas de conditions
  - ORDER BY toujours à la fin
  - ASC => par ordre croissant
  - DESC => par ordre décroissant

```sql
SELECT < nom_colonne 1 >, ...
FROM < nom_table >
WHERE ...
...
ORDER BY < nom_colonne 1 > <ASC | DESC >,
         < nom_colonne 2 > <ASC | DESC >
...
```
---
# Le tri avec ORDER BY
## Exemple 1

## Exemple 2
<img src="img/orderby2.png"  width=250/>

---
# Faire des maths 1/5
## Opérateurs dispo
\+ - * /

## Opérations déjà définies

AVG, SUM, MIN, MAX, COUNT

## Table exemple Produit

| id | quantite | prix | appelation |
|----|----------|------|------------|
| 1  | 10       | 499  | ps5        |
| 2  | 15       | 290  | switch     |
| 3  | 15       | 90   | wii        |
| 4  | 15       | 200  | xbox       |

---
# Faire des maths 2/5
## Exemple 1
```sql
SELECT quantite*prix, appelation
From Produit;
```

| quantite*prix | appelation |
|---------------|------------|
| 4990          | ps5        |
| 4350          | switch     |
| 1350          | wii        |
| 3000          | xbox       |

---
# Faire des maths 3/5
## Exemple 2
```sql
SELECT quantite*prix as total, appelation
From Produit;
```

| total | appelation |
|-------|------------|
| 4990  | ps5        |
| 4350  | switch     |
| 1350  | wii        |
| 3000  | xbox       |

---
# Faire des maths 3/5
## AVG
```sql
SELECT AVG(prix) as moyenne
From Produit;
```
| moyenne  |
|----------|
| 269.7500 |

## MIN
```sql
SELECT MIN(prix) as "moins cher"
FROM Produit;
```
| moins cher |
|------------|
| 90         |

---
# Faire des maths 4/5
## Différence entre SUM ET COUNT
- COUNT => Compter le nombre d'occurences
- SUM => Additionner les valeurs d'une propriété

## requete 1
```sql
SELECT COUNT(prix) FROM Produit;
```
## requete 2
```sql
SELECT SUM(prix) FROM Produit;
```

---
# Faire des maths 5/5

## requete 1
```sql
SELECT COUNT(prix) FROM Produit;
```
Il y a 4 prix différents !
## requete 2
```sql
SELECT SUM(prix) FROM Produit;
```
Il a 1079€ (499+290+90+200)

---
# Plus d'opérations
Ils sont tous dans la DOCUMENTATION => [LINK DOC SQL](https://sql.sh)