SQL / MySQL en profondeur
Un cours complet de SQL en vidéo, incluant la définition des structures (bases, tables, champs, types), la manipulation des données (insertion, mise à jour, destruction), les requêtes select (simples, multi critères, utilisant des fonctions), les jointures et les vues…
Vous apprendrez aussi comment programmer vos fonctions, procédures, triggers, ainsi que le contrôle des transaction (commit/rollback), la gestion des utilisateurs, et des cas pratiques pour une immersion dans le concrêt.
+Présentation
Introduction : Présenter ce qu’est une base de données, rappeler l’importance d’une bonne conception via la modélisation, présenter le SQL en bref, et les moyens de l’utiliser (seul, en ligne de commande, via MySQL Workbench, via PhpMyAdmin, couplé avec un langage tels php ou python…)
Installations : Installer les logiciels, gratuits, si vous désirez effectuer les manipulations (facultatif, mais fortement conseillé). Apprendre à effectuer des sauvegardes et des restaurations de vos données.
Définition des données : Créer les structures (bases, tables). Définir des champs avec leurs types. Ajouter des contraintes, des index. Modifier ces structures, les afficher. Y voir plus clair dans les jeux de caractères.
Manipulation des données : Maîtriser les opérations d’insertion, de suppression et de modification de données. Aborder les mises à jour calculées.
Interrogation des données : Faire connaissance avec l’instruction SELECT, pour afficher le contenu d’une table. Choisir ce que l’on affiche, dans quel ordre. Limiter le nombre de résultat. Effectuer des calculs.
L’art du WHERE : Affiner l’affichage en filtrant quels informations l’on souhaite. Effectuer des recherche selon de nombreux critères.
Utilisation des fonctions : Apprendre ce que sont les fonctions, et comment s’en servir. Passer en revue des fonctions utiles, selon leurs types (nombres, dates, chaînes de caractères, tests…). Utiliser les colonnes générées.
L’art du GROUP : Apprendre à regrouper les lignes pour effectuer des statistiques (somme, moyenne…). Concaténer des données en ligne. Construire des tableaux croisés.
L’art du NULL : Gérer l’absence de valeur dans un champs, et les conséquences de celle ci. Comprendre les précautions à prendre pour que tout ne soit pas null…
Utiliser plusieurs tables : Voir comment unir des tables pour mélanger des données. Relier des tables entre elles selon leurs clefs primaires et secondaires. Effectuer des sous requêtes. Dupliquer des tables.
Travailler avec des VUES : Voir comment effectuer des vues, c’est à dire des raccourcis vers des requêtes souvent complexes, pour éviter de les refaire. Étudier les tables temporaires.
Programmer : Survoler comment décupler les possibilités du langage en créant ses propres fonctions et procédures. Automatiser leurs exécutions via des déclencheurs et des évènements.
Contrôle de Transaction : Comprendre l’importance des notions ACID. Maîtriser les validations (commit) et le retour en arrière (rollback) dans des séries de transactions.
Administration Système : Étudier la gestion des utilisateurs et de leurs privilèges. Survoler les outils d’administration système. Comprendre les moteurs de stockage.
Cas pratiques : Observer une série de cas pratiques en mode démonstration, pour étudier la mise en oeuvre de SQL dans des situations concrètes et variées, utilisant python, R, la création de fonctions…
Conclusion
+Objectifs du cours
Comprendre les requêtes SQL au travers de MySQL, outils le plus populaire de gestion de base de données relationnelles
La création des databases, tables, utilisateurs, vues, fonctions, procédures ; les opérations CRUD : Create (insertion de données), Read (sélection), Update (mise à jour) et Delete (suppression)
Travailler avec une ou plusieurs tables (unions, jointures de différents types)
Manipuler des données aux travers de nombreux exemples réalistes, simples puis plus complexes
Des cas pratiques pour voir des utilisations concrètes
Un cours vivant, où l’on prend le temps d’aller en profondeur en multipliant les exemples pratiques et les explications
+Pré-requis
Un début d’expérience professionnelle ou personnelles des bases de données relationnelles est un plus, mais n’est pas indispensable
Quelques notions de modélisations de bases de données peuvent servir (on revient sur les bases)
Posséder un ordinateur (mac/win) et pouvoir installer quelques logiciels (gratuits, et optionnel: vous pouvez suivre sans faire les manipulations)
Quelques notions basiques d’anglais peuvent aider…
+Programme détaillé
Préambule…
- SQL / MySQL en profondeur
- Comment se déroule cette formation?
- IMPORTANT – Confinement
Introduction
Présenter ce qu’est une base de données, rappeler l’importance d’une bonne conception via la modélisation, présenter le SQL en bref, et les moyens de l’utiliser (seul, avec php, python…)
- Introduction
- Conception et modélisation
- Présentation du langage SQL
- Où trouver / faire du SQL ?
Installations
Installer les logiciels, gratuits, si vous désirez effectuer les manipulations (facultatif, mais fortement conseillé). Apprendre à effectuer des sauvegardes et des restaurations de vos données.
- Installations AMP
- Mysql Workbench / PhpMyAdmin
- Sauvegarde et restauration
Définition des données
Créer les structures (bases, tables). Définir des champs avec leurs types. Ajouter des contraintes, des index. Modifier ces structures, les afficher. Y voir plus clair dans les jeux de caractères.
- Introduction
- Créer une base, la détruire
- Créer une table, la détruire
- Types de données
- Contraintes
- Index
- Modifier une table
- Afficher des infos
- Jeux de caractères et interclassement
Manipulation des données
Maîtriser les opérations d’insertion, de suppression et de modification de données. Aborder les mises à jour calculées.
- Introduction
- Insérer
- Supprimer
- Modifier
- Bonus : mise à jour calculée
Interrogation des données
Faire connaissance avec l’instruction SELECT, pour afficher le contenu d’une table. Choisir ce que l’on affiche, dans quel ordre. Limiter le nombre de résultat. Effectuer des calculs.
- Introduction
- Sélectionner, Trier, Limiter
- Calcul, alias, distinct
L’art du WHERE
Affiner l’affichage en filtrant quels informations l’on souhaite. Effectuer des recherche selon de nombreux critères.
- Introduction
- Prédicat
- Multi critères
- Dans une liste
- Recherche FullText
- Expressions régulières
Utilisation des fonctions
Apprendre ce que sont les fonctions, et comment s’en servir. Passer en revue des fonctions utiles, selon leurs types (nombres, dates, chaînes de caractères, tests…). Utiliser les colonnes générées.
- Introduction
- Nombres
- Dates
- Chaîne
- Tests
- Bonus : colonnes générées
L’art du GROUP
Apprendre à regrouper les lignes pour effectuer des statistiques (somme, moyenne…). Concaténer des données en ligne. Construire des tableaux croisés.
- Introduction
- Agrégats
- Concaténation
- Tableaux croisés
L’art du NULL
Gérer l’absence de valeur dans un champs, et les conséquences de celle ci. Comprendre les précautions à prendre pour que tout ne soit pas null…
- Introduction
- Null, not null
- Null et calculs
Utiliser plusieurs tables
Voir comment unir des tables pour mélanger des données. Relier des tables entre elles selon leurs clefs primaires et secondaires. Effectuer des sous requêtes. Dupliquer des tables.
- Introduction
- Unions
- Jointures : petit panorama…
- Jointures : à fond
- Jointures : plus de deux tables…
- Sous requêtes
- Bonus : dupliquer une table
Travailler avec des VUES
Voir comment effectuer des vues, c’est à dire des raccourcis vers des requêtes souvent complexes, pour éviter de les refaire. Étudier les tables temporaires.
- Introduction
- Vue simple
- Vue multi tables
- Bonus : tables temporaires
Programmer
Survoler comment décupler les possibilités du langage en créant ses propres fonctions et procédures. Automatiser leurs exécutions via des déclencheurs et des évènements.
- Introduction
- Variables
- Fonctions
- Procédures
- Triggers
- Curseurs
- Events
Contrôle de Transaction
Comprendre l’importance des notions ACID. Maîtriser les validations (commit) et le retour en arrière rollback) dans des séries de transactions.
- Introduction
- Commit, Rollback
Administration Système
Étudier la gestion des utilisateurs et de leurs privilèges. Survoler les outils d’administration système. Comprendre les moteurs de stockage.
- Introduction
- Utilisateurs et droits
- Management
- Moteurs de stockage
Cas pratiques
Observer une série de cas pratiques en mode démonstration, pour étudier la mise en oeuvre de SQL dans des situations concrètes et variées, utilisant python, R, la création de fonctions…
- Introduction
- CSV
- JSON
- GeoJSON, Géométrie, lat, lng…
- Mots de passe pour vos utilisateurs
- UUID et clef primaire
- Procédure de sauvegardes
- Statistiques : R et SQL
- Site Web en PHP
- Version 8
Conclusion
- Conclusion
- Bêtisier
Fichiers
ATTI, le schéma de la base Atontour_info
Conseil : imprimez le schéma de la base, et gardez le sous les yeux pendant les exercices.
ATTI, tables et données de la base Atontour_info
Ce fichier contient la sauvegarde des tables pour les manipulations SQL. Il permettra de reconstituer les données, et est constitué de données aléatoires.
Ne pas oublier de faire use atontour_info; avant de lancer le traitement...
Esope
Liste des fables attribuées à Esope
Collèges 92
Les collèges du 92 (à la date du 25 mars 2019)
Divers SQL
Tables et données ap_salles, ap_cours, parents, network_ping
ODSEN
Copie à des fins de sauvegarde du fichier ODSEN_GENERAL.json en date du 6/11/2019, voir https://data.senat.fr/les-senateurs/