Formation SQL

Objectifs

  • Qu'est-ce qu'une base de données
  • Comment interagir avec elles

Jour 1

  • Qu'est-ce qu'une base de données
  • Types de bases de données
  • Exemples de bases de données relationnelles
  • Concept clés d'une base de donnée relationnelle
  • Éxécuter des requêtes simple
  • Éxécuter des requêtes complexe

Théorie

Pratique

Jour 2

  • Modifier les données
  • Modifier les schémas

  • Types de données

  • Indexation et optimisations

  • Contraintes étrangères

  • Modélisation forme normale

  • Et plus

Pratique

Théorie

Jour 1

Théorie

Qu'est-ce qu'une base de donnée

Permet de stocker des données et de les retrouver facilement.

 

Les bases de données sont de différents types, certaines sont optimisées pour la rapidité, d'autres la flexibilité et certaines pour stocker des quantités de données impressionnantes.

Les types de base de données

  • Relationnelles
  • Entrepôt de données
  • Documents non-structurés
  • Graph de données
  • Clé/Valeur simple
  • Fichiers

Types de bases de données

Relationnelles

  • Permet de lier des données ensemble
  • Conserve une structure intègre
  • Plus indiquées pour transactions et contrôle
  • Lentes
  • Peu flexible

Types de bases de données

Entrepôts de données

  • Permet de stocker une quantité incroyable de données
  • Permet de calculer des long rapports
  • Parfait pour les statistiques
  • Peu structurés
  • Souvent couteuses

Types de bases de données

Documents non-structurés

  • Utilisé pour une évolution rapide
  • Permet de stocker des documents a structure variable
  • Puissante expansion horizontale
  • Plus difficile de faire des requêtes complexes
  • Programmation plus complexe, paradigme différent

Types de bases de données

Graph de données

  • Utilisé pour simplifier les modèles relationnels trop complexes avec beaucoup de relation
  • Permet de faire des requêtes complexes par relations
  • Peu de support ou concurrents
  • Plus lent que certains autres types

Types de bases de données

Clé/VAleur simple

  • Utilisé surtout pour un accès ultra rapide comme les caches de données
  • Pas fait pour le processing de données

Types de bases de données

Fichiers

  • Ultra portable
  • Beaucoup d'éditeurs
  • Ultra flexible
  • Pas fait pour le processing de données
  • Pas fait pour la taille

Bases de données relationnelles

Différentes approches

  • Serveurs

 

  • Progiciels

 

  • Sans serveur

Bases de données relationnelles

Approche serveur

  • Nécessite un serveur pour laisser tourner l'application
  • Configuration plus complexes avec utilisateurs, rôles et permissions
  • Plus rapide
  • Coûte souvent de l'argent
  • Peu portable

Bases de données relationnelles

Approche Progiciel

  • Permet la création d'un logiciel intégré à la base de donnée
  • Portable
  • Offre beaucoup de flexibilité
  • Difficile d'avoir plusieurs utilisateurs concurrents
  • Coûte de l'argent pour avoir une licence

Bases de données relationnelles

Approche sans serveur

  • Très portable
  • Similaire aux serveurs mais plus lent vu qu'il se charge en mémoire à chaque utilisation
  • SQLite gratuit
  • Impossible d'utiliser des transactions mais possible d'avoir plusieurs utilisateurs
  • Très lent

Concepts des

Bases de données relationnelles

Serveur

Le serveur est un logiciel qui reste allumé de façon permanente sur un ordinateur et est utilisé pour stocker toutes les données des différentes bases de données.

 

On y configure des utilisateurs, rôles et permissions afin de contrôler l'accès aux données.

Base de données

La base de données est une structure logique qui combine tables, trigger, vues et procédures stockées que l'utilisateur se servira pour stocker des données mais aussi pour les traiter.

 

Il est possible d'avoir plusieurs bases de données dans un serveur.

Tables et Colonnes

Les tables sont les endroits où sont stockées les données. Dans une table, vous trouverez des colonnes qui définissent les données qu'on peut entrer dans la table.

Chaque colonne peut recevoir de nombreuses configurations pour définir ce qui peut aller dedans.

Rangées

Une rangée représente une entrée dans une table. Les rangées contiennent toujours toutes les données nécessaire pour satisfaire les contraintes des différentes colonnes.

Contraintes

Une contrainte est utilisées pour limiter les données que  l'on peut entrer dans une colonne.

Il existe plusieurs types de contraintes en MySQL:

 

  • Clé étrangère
  • Indexe unique
  • Vérification (Check)
  • Not Null

 

Nous étudierons ces concepts vers la fin du jour 2.

Vues

Une vue est l'équivalent d'une table mais utilise le langage SQL que nous verrons sous peu pour optimiser et transformer des données.

Triggers Et PROCS

Un trigger est un bout de code qui réagit à des événements de la base de données comme l'ajout d'information dans une table.

 

Une procédure stockée est un bout de code qui exécute des fonctions complexe dans la base de données mais qui existe dans celle-ci au lieu de dans l'application.

Jour 1

pratique

Sélection

Comment obtenir des données

Sélection

On obtient des données en envoyant la commande

 

SELECT

 

au serveur de base de donnée.

Sélection

Il faut lui dire ce que l'on souhaite obtenir:

 

  • * (Toutes les données possible)
  • Nom d'un champ
  • Expression complexe

 

Par exemple

SELECT * FROM ...

SELECT firstname FROM ...

Sélection

Il faut aussi lui dire où aller chercher ces données:

 

  • Nom d'une table
  • Sous-requête (Non-couvert)

 

Par exemple

SELECT * FROM employees

SELECT firstname FROM employees

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercices

Obtenir les informations de la table employés:

  • Tout avec *
  • Tout avec spécification des champs
  • Seulement le nom
  • Le nom et le numéro d'employé dans cet ordre

Sélection

Comment FILTRER des données

Sélection

On filtre les données en ajoutant la commande

 

WHERE

 

aux commandes SQL vues précédemment.

Sélection

Il faut lui donner des conditions:

 

  • Nom d'un champ = valeur
  • Nom d'un champ = nom d'un champ
  • Expression complexe

 

Par exemple

SELECT * FROM employees WHERE firstname = 'Mathieu'
SELECT * FROM employees WHERE department_id = 1

Sélection

Il existe beaucoup d'opérateurs de conditions:

= < >
<> <= >=

Et plus encore!

Sélection

La structure d'une expression est simple:

 

"Opérande gauche" "Opérateur" "Opérande droite"

 

Tout se lit facilement et doit répondre positivement:

Op gauche Opérateur Op droite
Le id de l'employé égale 1
Le nom de l'employé égale "Mathieu"

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercices #1

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Employé #1
  • Employés #3 à #10
    • Il existe une multitude d'approche
    • Trouvez en un maximum
  • Employés nommés Mathieu

Sélection

Vous pouvez combiner des conditions ensemble à l'aide des opérateurs:

 

OR ou AND

 

Ainsi qu'avec les parenthèses comme dans les expressions d'algèbre en mathématiques.

Sélection

Il existe beaucoup d'opérateurs de conditions:

() and or

Et plus encore!

Exercices #2

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Employés qui s’appellent Mathieu qui sont dans le département #1
  • Employés qui sont dans le département #1, #2 ou #3
  • Employés qui s'appellent Mathieu qui sont dans le département #1 ou Employés qui s'appellent Valérie qui sont dans le département #3

Sélection

Un opérateur permet de comparer des chaînes de texte de façon complexe:

 

LIKE

 

En utilisant une syntaxe particulière, vous pouvez rechercher des textes plus ou moins complexe:

SELECT * FROM employees WHERE firstname LIKE "%Math%"

Sélection

Il existe beaucoup d'opérateurs de conditions:

like

Et plus encore!

Exercices #3

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Employés qui se prénomment "Marie-" quelque chose
  • Employés qui ont le nom de famille qui finissent par "mont"
  • Employés qui se prénomment "Math" et contiennent un "e" dans le nom de famille

Sélection

Certains opérateurs facilitent l'écriture de conditions a valeurs multiple:

 

BETWEEN

IN

 

C'est opérateurs sont pratique pour simplifier :

SELECT * FROM employees WHERE firstname = "Mathieu" OR firstname = "Jean" OR firstname = "Valérie"

Sélection

Il existe beaucoup d'opérateurs de conditions:

between in

Et plus encore!

Exercices #4

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Employés #3 à #10
  • Employés nommés Mathieu, Valérie et Jean

Sélection

Comment TRIER les données

Sélection

Il serait peu pratique d'avoir des données toujours  dans un ordre inconsistant. Nous allons donc apprendre maintenant à utiliser le mot clef :

 

ORDER BY

 

Ce mot clef doit être ajouté à la fin de la requête pour spécifier comment trier les données.

Sélection

La structure de cette portion est la suivante :

 

ORDER BY "nom d'un champ" "direction"

 

Le nom du champ est requis mais pas la direction. La direction est par défaut en ordre croissant. Donc:

 

1,2,3,4,5

Jean, Mathieu, Valérie

etc

Sélection

Vous pouvez ajouter autant de critères de tri en les séparants par des virgules :

SELECT * FROM employees ORDER BY firstname

SELECT * FROM employees ORDER BY firstname, lastname

SELECT * FROM employees ORDER BY firstname, lastname, department_id

Sélection

La direction se compose de 2 termes possible:

 

ASC ou DESC

 

Qui indiquent respectivement un tri ascendant et descendant.

 

Vous devez placer la direction après le nom du champ pour chaque champ à trier. Si omit, le champ sera trié ascendant.

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercices #1

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Triez les employés par prénom
  • Triez les employés par nom
  • Triez les employés par numéro de département
  • Triez les employés par nom et ensuite prénom

Sélection

Le champ sur lequel vous triez n'as pas besoin de faire parti du résultat. Par contre, ceci est valide seulement sous MySQL.

 

La majorité des langages de SQL requièrent que le nom (ou l'expression) qui sert à trier fasse partie des données!

Sélection

Comment limiter les

données reçues

Sélection

Vous pouvez limiter la quantité de données retournée par le SQL en utilisant le mot clef suivant :

 

LIMIT quantité

 

La quantité doit toujours être un nombre!

Sélection

Vous pouvez aussi changer les données limitées pour obtenir une tranche plus loin dans le résultat. Parfait pour paginer des résultats :

 

OFFSET quantité

 

La quantité doit toujours être un nombre!

Sélection

Vous pouvez aussi utiliser une forme plus simple de LIMIT et  OFFSET en utilisant :

 

LIMIT limite, offset

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercices #1

Obtenir les informations de la table employés:

  • Les premiers 5 employés
  • Les 5 employés suivants
  • Les 2 premiers employés qui commencent par "Math"

Sélection

Expressions

Sélection

Jusqu'à présent, nous avons utilisé les nom des champs pour faire des sélections, conditions et tris. Vous pouvez par contre toujours utiliser des expressions à la place des noms des champs.

SELECT 1

SELECT 10 FROM employees

Ces instructions sont valide dans la plupart des bases de données!

Sélection

Une expression est une série d'opérations logique ou mathématique. Quelques expressions des plus simples:

 

valeur

nom de champ

 

Mais vous pouvez créer des expressions plus complexes comme:

 

(valeur + valeur) * nom de champ

nom de champ - valeur

Sélection

Une autre chose que vous pouvez faire est d'utiliser des fonctions. Une fonction est un bout de code qui exécute une opération complexe sur des données:

 

SUBSTRING(nom de champ, 3)

DAY(date_of_birth)

CURRENTDATE()

 

Ces fonctions ont toutes leur utilité et il en existe au dessus de 250 dans le langage SQL de MySQL. Nous allons en regarder quelques unes au cours de cette section.

Sélection

Utiliser des expression dans la portion SELECT est très important. Elle permet de transformer les données brutes de vos tables en quelques chose de plus compréhensible.

SELECT CONCAT(firstname, ' ', lastname) AS fullname
FROM employees

Dans cette approche, vous collez les deux portions du nom d'un utilisateur ensemble dans une seule colonne de résultat.

Sélection

Utiliser des expression dans la portion WHERE est déjà chose commune. En fait, chaque condition que vous avez fait jusqu'à présent est une expression.

SELECT * FROM employees WHERE employee_id = 1
SELECT * FROM employees WHERE (employee_id = 1) OR (employee_id = 2)

Ici, "employee_id = 1" est une expression.

Ici, "(employee_id = 1)" et "(employee_id = 2)" sont toutes les deux des expressions.

Pouvez-vous en identifier une 3e?

Sélection

Apprenons de nouvelles fonctions de gestion de date.

Fonction Utilité
DAY(date) Retourne le jour du mois de la date
MONTH(date) Retourne le numéro 1-12 du mois de la date
YEAR(date) Retourne l'année à 4 chiffres de la date
DAYOFYEAR(date) Retourne le jour de l'année 1-366
MKDATE(year, days) Retourne la date représenté par l'année "year" et le jour de l'année "days"

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercices #1

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Une date selon les critères ci-dessous
  • Le numéro du mois de fête de chaque employé
  • La date de fête de l'année en cours pour chaque employés
  • Les employés qui sont nés en 1981

Sélection

Utiliser des expression dans la portion ORDER BY vous permet de trier de façon arbitraire.

Fonction Utilité
SUBSTRING(texte, début, longueur) Retourne une portion du texte
RAND() Retourne une valeur aléatoire

Exercices #2

Obtenez, dans tous les exercices suivants:

  • Le prénom et nom de l'employé
  • Son ID
  • Les employés doivent être trié aléatoirement
  • Les employés doivent être trié selon les 2 premières lettres de leur nom et ensuite leur prénom

Sélection

Certains mots clé servent à construire des résultats complexes sans toutefois êtres des fonctions:

 

CASE WHEN ... THEN ... ELSE ... END

 

En utilisant cette syntaxe particulière, vous pouvez obtenir des résultats différents selon certain cas.

SELECT CASE WHEN firstname = "Mathieu" 
    THEN "Mattew" ELSE firstname END AS firstname 
FROM employees

Exercises

Mise en contexte

sales

sale_id

employee_id

amount

date_of_sale

Exercices #3

Obtenez, dans tous les exercices suivants:

  • Le montant de vente ajusté
  • Le numéro de l'employé
  • Le mois de la vente
  • Les ventes doivent être de 1000$ minimum, si moins afficher 1000$
  • Les ventes de plus de 5000$ doivent être divisées par 2 et le numéro de l'employé doit être multiplié par 20 mais seulement lors que la vente est de plus de 5000$

Sélection

Jointures

Sélection

Vous ne pouvez pas seulement utiliser une table pour générer vos données. La force principale d'une base de données relationnelle est justement de mettre les données en relation.

 

Pour y arriver, il faut utiliser les jointures:

 

LEFT JOIN, INNER JOIN, RIGHT JOIN, FULL OUTER JOIN

 

Voyons comment cela fonctionne!

Sélection

Une sert à coller les données d'une table/résultat à une autre. Elle est accompagnée d'une ou plusieurs conditions.

SELECT *
FROM employees
JOIN sales ON sales.employee_id = employees.employee_id

Sélection

Vous pouvez utiliser une jointure plusieurs fois pour augmenter les données retournées.

SELECT *
FROM employees
JOIN sales ON sales.employee_id = employees.employee_id
JOIN departments ON departments.department_id = employees.department_id

À chaque jointure, les différentes rangées de chaque tables sont inspectées et jointes ensemble. Le résultat est donc une multiplication de toutes les rangées de toutes les tables mais seulement pour les données qui sont valide.

Sélection

Une version équivalente mais à proscrire est la suivante :

SELECT *
FROM employees, sales, departments
WHERE
    sales.employee_id = employees.employee_id
    AND departments.department_id = employees.department_id

Selon la complexité de la requête (ici ce ne serait pas le cas) le système pourrait dupliquer un montant impressionnant de rangées et ensuite filtrer les données ce qui peut rendre un serveur inacessible.

Sélection

Il existe 4 types de jointures :

SELECT * FROM employees LEFT JOIN sales ON ...

La jointure left prends toute les lignes de la tables employees et y ajoute toutes les lignes de sales.

 

Si aucune ligne ne fonctionne dans la table sales, alors vous n'aurez que des lignes de la table employees.

Sélection

Il existe 4 types de jointures :

SELECT * FROM employees JOIN sales ON ...
SELECT * FROM employees CROSS JOIN sales ON ...

La jointure cross (ou juste un join normal) prends toute les lignes de la tables employees et y ajoute toutes les lignes de sales.

 

Seulement les lignes qui concordent de chaque coté sont retournées. Donc si vous n'avez aucune ventes pour les employés retenus, vous n'aurez aucun résultat.

Sélection

Il existe 4 types de jointures :

SELECT * FROM employees RIGHT JOIN sales ON ...

La jointure right prends toute les lignes de la tables sales et y ajoute toutes les lignes de employees.

 

Si aucune ligne ne fonctionne dans la table employees, alors vous n'aurez que des lignes de la table sales.

Sélection

Il existe 4 types de jointures :

SELECT * FROM employees INNER JOIN sales ON ...

La jointure inner prends toute les lignes de la tables employees ou de la table sales.

 

Seulement les lignes qui concordent sont retournées jointes aux autres sauf si aucune lignes ne concordent. Alors, les lignes qui n'ont aucune concordances sont retournées avec des informations vides de l'autre coté.

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercises

Mise en contexte

sales

sale_id

employee_id

amount

date_of_sale

Exercises

Mise en contexte

departments

department_id

name

Exercices #1

  • Trouvez chaque employé (nom, prénom) avec le nom de son département
  • Trouvez toutes les ventes de tous les employés (nom, prénom, date de vente et montant de vente)
  • Trouvez toutes les ventes des employés du département "Informatique" en utilisant le nom du département, pas son ID. (Nom, Prénom, Date de la vente, Montant)

Sélection

Lorsque vous utilisez une jointure de type left, right ou inner, vous obtiendrez parfois des informations NULL. 

 

Cette information est importante dans le contexte des jointures et vous permet de trouver des trous ou problèmes d'intégrité.

SELECT * FROM sales LEFT JOIN departments ON sales.employee_id = departments.department_id

Ici, vous obtiendez forcément des NULLs car nous n'utilisons pas les bonnes clefs pour joindre les données.

Sélection

En recherchant des valeur NULLs dans des jointures de type left ou right, vous pouvez trouver des informations manquantes :

SELECT * FROM employees LEFT JOIN sales 
ON sales.employee_id = employees.employee_id
WHERE sales.employee_id IS NULL

Ici, on tente de trouver les employés qui n'ont pas de ventes.

Exercices #2

  • Les départements qui n'ont pas d'employés
  • Les départements qui ont pas d'employés
  • Les employés qui n'ont pas de ventes de 5000$ ou plus

Sélection

Regroupements

Sélection

Un regroupement permet de faire des calculs sur des ensembles de données. Pour ce faire, il faut ajouter l'instruction :

GROUP BY regroupement

 

Juste après les conditions WHERE.

SELECT employees.employee_id, firstname, lastname, SUM(amount) 
FROM employees 
    LEFT JOIN sales ON sales.employee_id = employees.employee_id 
WHERE firstname LIKE "%Math%"
GROUP BY employees.employee_id

Sélection

Il existe quelques opérateurs de regroupement. Tous doivent êtres utilisés dans la section SELECT.

Opérateur Utilité
COUNT(expr) Compte le nombre d'expressions
SUM(expr) Additionne toutes les expressions
AVG(expr) Retourne la moyenne de toutes les expressions
MIN(expr) Retourne la valeur minimale de toutes les expressions
MAX(expr) Retourne la valeur maximale de toutes les expressions

Sélection

Les opérateurs de regroupement doivent être utilisés dans la section SELECT. Ils doivent être utilisé sur une expression. Ils sont donc une expression en soit.

SELECT COUNT(employee_id) FROM employees WHERE firstname LIKE "%Math%"
SELECT SUM(amount) FROM sales
SELECT MIN(amount), employees.firstname FROM sales JOIN employees USING(employee_id) GROUP BY employee_id

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercises

Mise en contexte

sales

sale_id

employee_id

amount

date_of_sale

Exercises

Mise en contexte

departments

department_id

name

Exercices #1

  • Trouvez les ventes totales de chaque département. Affichez la somme des ventes et le nom du département
  • Trouvez le nombre de ventes pour chaque employés dans le mois de décembre 2018, si un employé n'as pas de vente,  ne pas l'afficher
  • Trouvez la plus petite vente de chaque département dans le mois de janvier 2019
  • Trouvez la plus petite vente et la plus grande vente de chaque département

Sélection

Vous pouvez ajouter des conditions sur les valeurs regroupées. Si les expressions dans la section WHERE s'appliquent aux données retenues pour la section SELECT, alors la section HAVING contient les expression qui filtre les données finales regroupées.

 

GROUP BY ... HAVING ...

 

Vous ne pouvez pas utiliser HAVING sans GROUP BY.

Exercices #2

  • Trouvez les employés ayant vendu au
    minimum 10 000$ depuis leurs débuts
  • Trouvez les départements qui ont au moins 4 employés

Jour 1

Révision

Révision

  • On sélectionne les données à retourner dans la section SELECT.
  • Chaque élément de la section SELECT est une expression, il est possible de changer la valeur finale.
  • Chaque expression peut recevoir un nom final ou un alias.
  • Chaque expression peux utiliser des regroupements.

Révision

  • Les données proviennent de tables indiquées par la section FROM.
  • Il est possible joindre d'autres tables au résultat en utilisant des jointures telles que INNER, LEFT, RIGHT et CROSS JOIN.
  • Les jointures doivent recevoir une ou plusieurs conditions sous la forme ON expression.

Révision

  • Les données qui seront utilisées dans la section SELECT peuvent être filtrées en utilisant la section WHERE.
  • La section WHERE est une grande expression qui défini si la/les rangées en cours de sélection doivent être retenues dans le résultat final.
  • Vous pouvez utiliser des opérateurs AND, OR, NOT  et des parenthèses pour contrôler le résultat des conditions.

Révision

  • Les données qui seront utilisées dans la section SELECT peuvent regroupées pour générer différents totaux en utilisant GROUP BY.
  • Chaque élément de la section GROUP BY est une référence à une colonne qui doit être utilisé générer plusieurs groupes de valeur.
  • Vous pouvez utiliser des opérateurs AVG, MIN, MAX  et plus encore dans la section SELECT pour regrouper des données.

Révision

  • Les données finales qui font suite à la SELECTion, peuvent être filtrées lorsqu'un regroupement GROUP BY a été appliqué.
  • On utilise la clause HAVING pour spécifier une expression de sélection.
  • Tout comme la section WHERE, la section HAVING  peut avoir plusieurs expression complexe.

Révision

  • Les données finales qui font suite à la SELECTion, peuvent être triées en utilisant un opérateur ORDER BY.
  • Chaque expression de la section ORDER BY permet de trier les données finales selon une expression.
  • Lorsque vous regroupez, vous devez trier sur une valeur retenue sinon vous pouvez utiliser une valeur non sélectionnée si aucun regroupement n'est fait.

Révision

  • Les données finales qui font suite à la SELECTion et au tri ORDER BY peuvent êtres limitées par l'instruction LIMIT et OFFSET.
  • Les valeurs de LIMIT ou OFFSET doivent être des valeurs absolues et numériques.
  • Vous pouvez utiliser ces instructions pour paginer les données à retourner.

Jour 2

Pratique

INSERTIONS

Comment ajouter des données

Insertions

Vous pouvez ajouter des données dans n'importe qu'elle table en utilisant la commande :

 

INSERT

 

Cette commande est très flexible, nous verrons différentes utilisations dans les prochaines diapositives.

Insertions

Les insertions de base se font sous la forme suivante :

 

INSERT INTO table (column, column, ...)
VALUES (value, value, ...), (value, value, ...), ...

 

Cette version requiert le nom de chaque colonne qui recevra une valeur et dans la portion VALUES vous ajoutez chaque valeur dans le même ordre.

Insertions

Les insertions peuvent remplacer des données si elles existent déjà :

 

REPLACE INTO table (column, column, ...)
VALUES (value, value, ...), (value, value, ...), ...

 

Cette version requiert qu'une des colonnes soit une clé de la table sinon ce sera toujours une insertion.

 

Si les valeurs données à REPLACE contiennent une clef et que la clef existe déjà, alors la rangée sera mise à jour.

Insertions

La version REPLACE peut aussi s'écrire comme ceci:

 

INSERT INTO table (column, column, ...)
VALUES (value, value, ...), (value, value, ...), ...
ON DUPLICATE KEY UPDATE
column = VALUES(column), column = value, ...

 

Cette version est plus flexible que REPLACE car vous pouvez faire des mises à jour complètement différentes de l'insertion.

Insertions

Une clef primaire est une colonne d'une table qui contient une information unique servant à retrouver une rangée.

 

Souvent, les clefs primaires sont des nombres, en séquence automatique, par exemple: 1, 2, 3, 4, 5, 6, 7. On les appellent des clefs de substitution.

 

Dans d'autres cas, les clefs primaires sont des valeurs dites naturelle soit, une information réelle comme un numéro de téléphone, une adresse courriel, etc

Insertions

Lorsque vous insérez des données, vous pouvez omettre une clef primaire seulement si celle-ci est automatique comme dans le cas d'une clef de substitution.

INSERT INTO department (name) VALUES ("Nouveau départment")

Si vous n'avez pas de clef de substitution à séquence automatique, vous devez fournir une valeur.

Exercises

Mise en contexte

departments

department_id

name

Exercices #1

Insérez 3 nouveau départements en utilisant la méthode normale sans clef primaire :

 

  • Mécanique
  • Transport
  • Conciergerie
INSERT [INTO] {tableName} (columnName, columnName, ...) VALUES (value, value, ...), (value, value, ...), ...

Exercises

Mise en contexte

products

product_id

name

price

Exercices #2

Insérez 3 nouveaux produits en utilisant la méthode REPLACE INTO avec les valeurs suivantes :

id nom prix
6 Popcorn 1.25
7 Caramel en barre 1
8 Toaster 25 
REPLACE [INTO] {tableName} (columnName, columnName, ...) VALUES (value, value, ...), (value, value, ...), ...

Exercices #3

Insérez 2 nouveaux produits en utilisant la méthode INSERT ON DUPLICATE avec les valeurs suivantes :

id nom prix
3 Grilled cheese 3
9 Tournevis 14

Important : Les nouveaux produits doivent augmenter le prix actuel de 1$ s'ils existent déjà. Ils ne doivent pas remplacer le nom ou le prix.

INSERT [INTO] {tableName} (columnName, columnName, ...) VALUES (value, value, ...), (value, value, ...), ...
ON DUPLICATE KEY UPDATE 
    column = column + value, 
    column = VALUES(column), 
    column = fixed_value, ...

Insertion

Vous pouvez utiliser une dernière forme pour insérer des données soit la forme :

 

INSERT SELECT

 

Cette forme permet d'insérer le résultat d'une sélection. Très pratique pour générer des données!

Sélection

Pour se faire, générez premièrement un résultat à l'aide d'une instruction SELECT. Lorsque vous êtes satisfait, vous pouvez ajouter en avant du SELECT la portion INSERT INTO ... (column, column, ...)​ :

INSERT INTO sales (employee_id, date_of_sale, amount)
    SELECT employee_id, NOW(), FLOOR(RAND() * 1000)
    FROM employees

Ceci ajoutera 1 vente à chaque employé en date actuelle avec une valeur au hasard entre 0$ et 1000$.

Exercises

Mise en contexte

products

product_id

name

price

Exercices #4

  • Ajoutez 1 produit pour chaque employé existant. Le nom sera "Consultation - {Nom de l'employé}" et le prix sera l'âge de la personne. Assurez-vous que le ID du produit soit 5000 + le ID de l'employé.

 

  • Ajoutez ensuite un nombre variable de ventes pour chaque employé. La vente sera d'un seul produit, le produit qui correspond à leurs heures de consultation (5000 + ID) et la quantité sera au hasard entre 1 et 100.

MISES à jour

Comment changer les données

Mise à jour

Présenter la méthode UPDATE :

 

  • Update table set column = value where expression
  • Update sans where
  • Update avec limit

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercices #1

  • Changez le nom de l'employé #1 pour votre nom et prénom
  • Changez la date de naissance de tous les employés pour votre date de naissance
  • Changez le nom de famille de tous les employés nés en {Votre année de naissance} pour "Est un ZoZo"
  • Vérifiez que le premier employé né en {Votre année de naissance} s'appelle bien "{Votre prénom} Est un ZoZo"

Mise à jour

Présentation des mises à jour multi-tables:

 

  • Pourquoi utiliser une mise à jour multi-table
  • Comment structurer une mise à jour multi-table

Exercises

Mise en contexte

employees

employee_id

lastname

department_id

date_of_birth

firstname

Exercises

Mise en contexte

departments

department_id

name

Exercices #2

  • Mettez à jour le prénom de chaque employé en utilisant la table produit. Les noms des employés devraient tous contenir "Consultation - {Nom original}"

SUPRESSION

comment enlever des données

Supression

Présenter les commandes de suppression :

 

  • DELETE FROM avec conditions
  • DELETE FROM sans conditions
  • DELETE FROM avec limite
  • DELETE FROM multi-table
  • TRUNCATE

Exercises

Mise en contexte

departments

department_id

name

Exercises

Mise en contexte

sale_items

sale_item_id

sale_id

qty

product_id

Exercices #1

  • Supprimez le dernier département de la liste en utilisant son ID
  • Supprimez tous sales items
  • Supprimez les 10 premières ventes
  • Supprimez tous les départements qui n'ont pas d'employés

Structure

Comment modifier la structure de votre serveur

STRUCTURE

Pour ajouter ou supprimer des bases de données entières de votre serveur, utilisez :

DROP DATABASE 'nom de la bd'

CREATE DATABASE 'nom de  la bd'

Vous pouvez ajouter un mot clé pour éviter les erreurs :

DROP DATABASE IF EXISTS 'nom de la bd'

CREATE DATABASE IF NOT EXISTS 'nom de  la bd'

Exercices #1

  • Créez une nouvelle base de donnée intitulée "{votre prénom}_bd". Notez que le nom doit être composé de lettres, chiffres et barres de soulignement seulement.
  • Créez une 2e base de donnée intitulée "{votre prénom}_bd2" et ensuite détruisez la.

structure

Pour ajouter des tables dans votre base de données :

CREATE TABLE [IF NOT EXISTS] {NOM DE TABLE} (
    column1,
    column2,
    column3,
    indexes and keys
) ENGINE=innodb;

La mention if not exists est optionnelle. Le nom de la table doit être composé de lettre, chiffres et barres de soulignement et ne peut pas commencer par un chiffre.

 

Vous devriez toujours nommer vos tables en mode "snake_case" soit aucune majuscule.

structure

Les colonnes d'une table sont définies comme suit :

{NOM DE LA COLONNE} {TYPE ET LONGUEUR} {OPTIONS}

Par exemple :

sale_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
employee_id INT UNSIGNED NOT NULL,
amount DECIMAL(8,2) NOT NULL,
confirmed TINYINT NOT NULL DEFAULT 1

Tout comme pour les tables, le nom de la colonne doit être composé de lettre, chiffres et barres de soulignement et ne peut pas commencer par un chiffre. Il existe un grand nombre de type de données et d'options que nous verrons sous peu.

structure

Il existe beaucoup plus de type de données que ci-dessous mais ceci vous donne une bonne idée de ce que vous pouvez utiliser :

Type Long? Notes
Int, BigInt, TinyInt Non Nombres
Char, VarChar Oui Chaines de texte
Decimal Oui, 2x Pour les valeur monétaires
Float, Double Non Pour les fractions
Text, Blob Non Pour les long textes/Data
Date, DateTime Non Pour les dates

structure

Il existe beaucoup de modificateurs et options pour les colonnes, en voici quelques un :

Modificateur Notes
auto_increment Rends une colonne auto séquencée, valide pour les types integer ou plus et doit être pour une clef primaire seulement.
not null Impossible d'insérer NULL
default {val} Valeur par défaut si non fournie
unsigned Force les nombres à être positifs
Made with Slides.com