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/