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 |
deck
By Mathieu Dumoulin
deck
- 458