Cours gratuits : introduction au SQL, jointures et fonctions d'agrégation

Introduction au SQL - Vidéo

‍

Introduction au SQL - Résumé

‍
Pour interroger l’ensemble de nos données en entreprise, nous avons besoin d’outils plus adaptés à la volumétrie qu’Excel.

‍
Il existe plusieurs SGBD (système de gestion de base de données) mais elles partagent toutes, pour le modèle relationnel, le langage SQL. Seules quelques variantes peuvent apparaître.

‍
Le SQL (Structured Query Language) est une langue pour écrire des requêtes qui permettent d’interroger les bases de données relationnelles.
Le modèle relationnel est constitué de tables qui peuvent être en relation les unes aux autres.
‍

Une requête SQL est constituée de clauses, qui sont des instructions unitaires qui indiquent à la base de données quoi faire avec les data :

La clause SELECT FROM permet de récupérer une ou plusieurs colonnes d’une table.
La clause LIMIT limite le nombre de lignes retournées au nombre indiqué.
La clause WHERE permet de filtrer les lignes à retourner selon une ou plusieurs conditions.
Les clauses AND et OR sont utilisées pour combiner plusieurs critères.
Les clauses IN et BETWEEN sont utilisées pour éviter de devoir utiliser successivement plusieurs clauses AND ou OR.
La clause LIKE permet de rechercher des patterns dans des données textuelles.
La clause AS permet de renommer a posteriori une colonne dans la liste des résultats mais n’affecte en rien les données en base.
Enfin la clause ORDER BY permet de trier la liste des résultats par un ou plusieurs critères.

‍

L’ordre des clauses dans la requête est primordial, sans quoi des erreurs vont survenir. Cela doit être écrit dans cet ordre: SELECT, FROM, WHERE, ORDER BY, LIMIT.

‍
Le SQL n’est pas sensible à la casse, c'est-à-dire aux majuscules/minuscules ni aux espaces/retours à la ligne mais je vous conseille de marquer vos clauses en majuscules pour les distinguer et d’aller à la ligne à chaque changement de clause.

‍

Introduction au SQL - Cours écrit

‍

Les limites d’Excel

‍

Comme vous le savez, Excel est un outil très puissant couvrant les principaux besoins d’analyse et de visualisation simples. Néanmoins, celui-ci est fortement limité à cause de plusieurs aspects:

Tout d’abord, dans la version actuelle au moment où ces lignes sont écrites, un maximum de 1 million de lignes est autorisé dans une seule feuille Excel. 1 million de lignes peut vous sembler énorme à première vue mais si vous considérez, par exemple, la température d’une station météorologique relevée toutes les secondes pendant un an, vous auriez déjà plus de 31 millions de lignes! Quand on parle de big data, Excel n’a pas été pensé pour ça mais plutôt pour ce qu’on appelle de la small data.
Par ailleurs, le partage de fichier est relativement problématique. Il n’est pas rare qu’un fichier soit corrompu simplement parce que beaucoup de versions d’un même document ont été générées.
Il n’est pas possible de collaborer sur un même fichier en même temps. Il faut donc passer son temps à reconsolider les choses a posteriori.
Le problème de lenteur est aussi un sacré frein. En effet, dû en partie au fait qu’on puisse visualiser toutes les data à tout moment, cela demande beaucoup de mémoire à votre ordinateur réduisant ainsi ses capacités.

‍

En énumérant ses limitations les plus évidentes, on se rend bien compte qu’on a besoin d’autres outils pour venir interroger l’ensemble des données du Data warehouse ou de la base de donnée opérationnelle. Nous allons voir dans la suite de ce cours, le principe d’une base de données relationnelle et pourquoi c’est une solution satisfaisante à notre problème d’outils.

‍

Les bases de données dites relationnelles

‍

Premièrement, Il convient de rappeler la définition d’une base de données:

Une base de données est un ensemble organisé d'informations avec un objectif commun. Peu importe le support utilisé pour rassembler et stocker les données (papier, fichiers, etc.), dès lors que des données sont rassemblées et stockées d'une manière organisée dans un but spécifique, on parle de base de données.

‍

La gestion et l'accès à une base de données sont assurés par un ensemble de programmes qui constituent le système de gestion de base de données (SGBD) ou encore RDBMS en anglais (relational database management system). Un SGBD doit permettre l'ajout, la modification et la recherche de données. La plupart des SGBD fonctionnent selon un mode client/serveur. Le serveur (sous-entendu la machine qui stocke les données) reçoit des demandes de plusieurs clients. Le serveur analyse les demandes, les traite et retourne les résultats aux clients.

‍

Des objectifs principaux ont été fixés aux SGBD dès leurs origines, et ce, afin de résoudre les problèmes préalablement cités:

Accès aux données: L’accès aux données doit se faire par l’intermédiaire d’un langage qui viendra interroger la base.
Administration centralisée des données: Toutes les données doivent être centralisées dans un réservoir unique commun à toutes les applications.
Non-redondance des données: Afin d'éviter les problèmes lors des mises à jour, chaque donnée ne doit être présente qu'une seule fois dans la base.
Cohérence des données: Les données sont soumises à un certain nombre de contraintes d'intégrité qui définissent un état cohérent de la base
Partage des données: Il s'agit de permettre à plusieurs utilisateurs d'accéder aux mêmes données au même moment de manière transparente. Si ce problème est simple à résoudre quand il s'agit uniquement d'interrogations, cela ne l'est plus quand il s'agit de modifications dans un contexte multi utilisateurs. En effet, il faut permettre à deux utilisateurs (ou plus) de modifier ou interroger la même donnée en même temps.
Sécurité des données: Les données doivent pouvoir être protégées contre les accès non autorisés.
Résistance aux pannes: Il faut pouvoir avoir un mécanisme de récupération d’une base dans un état sain.

‍

Outre le système de gestion, le modèle historique, et encore aujourd’hui largement utilisé comme base de données opérationnelles, est le modèle dit relationnel. Edgar Frank Codd, mathématicien et chercheur chez IBM à la fin des années 1960, est considéré comme le père de ce modèle alors qu’il étudiait de nouvelles méthodes pour gérer de grandes quantités de données. En 1970, il publia un article où il proposait de stocker des données hétérogènes dans des tables, permettant d'établir des relations entre elles.

‍

Arrêtons nous un instant sur ces deux notions qui constituent les fondements de ce modèle :

‍

Une table

Les différentes informations d’une base de données vont être organisées dans des tables, c'est-à-dire organisées sous forme de tableaux où les colonnes correspondent à des catégories de l’information (une colonne peut stocker des numéros de téléphone, une autre des noms...) et les lignes à des enregistrements, également appelés entrées. Ci-dessous, on représente l’exemple d’une table qu’on appellera acheteurs:

‍

‍

Cette table acheteurs contient trois colonnes pour l’id, le prénom et le nom des acheteurs. Chaque ligne correspond à un et un seul utilisateur. Par exemple, la première ligne décrit l’utilisateur qui a l’id 1, le prénom Paul et le nom Mochkovitch. Chaque ligne d’une table doit pouvoir être déterminée de façon unique. C’est pourquoi, il y a toujours une ou plusieurs colonnes qui jouent le rôle d'identifiant unique et qui permet de faire référence à chacune des lignes de cette table, on l’appelle clef primaire. Dans l’exemple ci-dessus, la colonne id joue justement ce rôle, observez qu’il n’y a pas deux lignes ayant le même id. Retenez bien qu’une table ne peut pas avoir deux lignes dont la clef primaire est identique !

Si nous ajoutons un nouvel utilisateur dans la table, celui-ci viendra s’ajouter en bas de la liste. Par exemple, si nous ajoutons un nouvel utilisateur Arthur Limon, la table ressemblera à:

‍

‍

Remarquez que l’id pour le nouvel utilisateur est devenu 4. Il existe 2 types d’id pour une table:

Soit il est auto-incrémental et il prend simplement la valeur maximale de la colonne id + 1
Soit il n’est pas incrémental et il peut être n’importe quelle colonne ou ensemble de colonnes tant que le critère d’unicité est respecté.

Il est plus souvent commun de voir des id incrémentaux dans les tables.

‍

Chaque table peut contenir une (qui serait l’id) ou plusieurs colonnes. Chaque colonne a son propre type qui peut être différent des autres colonnes. Un type est la nature de la donnée qui est stockée. Par exemple, on distinguera le type pour stocker du texte de celui pour stocker des chiffres. Les noms des différents types que vous pourrez croiser vont dépendre du SGBD que vous allez utiliser mais leurs natures restent identiques. Ci-dessous, nous mettons les types les plus classiques que vous pourrez croiser avec le SGBD de Bigquery que nous utiliserons dans la suite de cette formation:

STRING: pour désigner les données de type texte.
INTEGER: pour désigner les données de type entier.
FLOAT: pour désigner les données de type décimaux.
DATETIME: pour désigner les données de type dates.
TIMESTAMP: pour désigner les données de type dates mais avec les détails des heures, minutes, secondes …

Vous pouvez retrouver l’ensemble des types possibles ici

‍

L’organisation des tables en lignes et en colonnes ne vous semble-t-elle pas familière ? Elle ressemble effectivement à Excel, mais c’est bien Excel qui a repris l’organisation du modèle relationnel et non l’inverse. Cette structure a inspiré les futurs développements et est, aujourd’hui, la plus naturelle pour nous !!

‍

L’intérêt de cette structure réside notamment dans le fait que plusieurs tables peuvent être reliées.

‍

Une relation

Reprenons l’exemple de la table acheteurs. Imaginons que nous disposions de données sur les achats effectués par chacun de ces acheteurs sur notre site internet. Alors nous pourrions imaginer regrouper les informations dans une seule et même table achats comme celle-ci:

‍

‍

Notez le fait qu’un même acheteur peut apparaître plusieurs fois sans pour autant violer la règle d’unicité car c’est la colonne achat id qui joue ici le rôle d’identifiant. On a bien dans cette table unicité des achats. Puis 4 colonnes pour le prénom et le nom de la personne qui a effectué l’achat ainsi que la date d’achat et le produit acheté.

‍

Qu’est-ce qui n’est pas optimal dans cette table alors ? Les prénoms et noms des acheteurs sont autant de fois répétés que d’achats qu’ils ont effectués. On viole ici le principe de non-redondance des données que nous avons décrit plus haut.

‍

Si maintenant nous ne définissons pas une seule table mais deux tables, la table acheteurs du début et une nouvelle table achats:

‍

Table acheteurs

‍

Table achats

‍

Dans la table achats, nous avons l’identifiant unique de chaque achat dans la colonne achat id. Mais cette fois-ci, au lieu d’avoir le prénom et le nom de chaque acheteur, nous avons son identifiant unique dans la colonne acheteur id. Il y a une correspondance entre la colonne acheteur id de la table achats et la colonne id de la table acheteurs. Ainsi nous pouvons garder les informations de chaque acheteur une fois sans pour autant perdre une seule information. Nous avons défini une relation entre les deux tables achats et acheteurs !

‍

Le modèle relationnel utilise de multiples tables liées entre elles par de multiples relations pour garantir les règles définis pour un SGBD, brillant non ? Il n’y a aucune perte d’information tout en ayant aucune redondance. Le schéma d’une base de données est l’ensemble des tables qui la compose avec leurs relations.

Il existe plusieurs SGBD connus et globalement utilisés:

‍

La plupart du temps, si ce n’est quasiment tout le temps, le Data warehouse sera une base de données relationnelle. C’est super, car on en connaît maintenant les principes ! Maintenant comment pouvons nous l’interroger ?

‍

Le langage SQL

‍

Il ne faut pas confondre les SGBD et le langage SQL (Structured Query Language). Le premier désigne le système de gestion de la base de données alors que le second désigne la langue utilisée pour interroger les données. A force de créer des développements spécifiques à chacun de leurs logiciels de gestion, les SGBD ont fini par développer leur propre versions du SQL qui est très similaire au SQL origine (le SQL standard). C’est pourquoi, il n’est pas rare d’entendre la confusion entre les deux. Par exemple, MySQL est un SGBD basé sur le langage SQL. Si vous regardez la liste des SGBDs les plus connus, vous remarquerez qu’ils portent tous SQL dans leurs noms, car c’est effectivement le langage à la base de tout. C’est la syntaxe élémentaire, normalisée et indépendante de toute base de données.

‍

SQL est le premier langage commercial à avoir utilisé le modèle relationnel de Edgar F. Codd comme défini dans son article. Il a été ensuite déclaré officiellement langage standard pour interroger les bases de données relationnelles.

‍

Pour interroger une base de données, il faut donc écrire une requête en SQL. Une requête est une instruction contenant une ou plusieurs clauses. Une clause est une opération élémentaire qui dicte une opération à effectuer sur les données. Ci-dessous, nous listons les clauses les plus usuelles du langage.

‍

Pour écrire nos premières requêtes, considérons l’unique table People suivante:

Table People

SELECT FROM

‍

Comme son nom l’indique cette clause permet de sélectionner une ou plusieurs colonnes (SELECT) d’une table (FROM). Par exemple:

‍

SELECT name
FROM People

‍

Cette requête va sélectionner la colonne name de la table People et seulement celle-ci. Le résultat va donc renvoyer une seule colonne mais bien avec toutes les lignes. Notez bien que les clauses sont en majuscules et qu’on est bien allé à la ligne entre le SELECT et le FROM. Cela ne change rien pour SQL mais ce sont des bonnes habitudes à prendre pour rendre vos requêtes faciles à lire.

‍

Si vous aviez voulu retourner pas une mais deux colonnes, par exemple name et surname, il aurait fallu modifier la précédente requête comme ceci:

‍

SELECT name, surname
FROM People

‍

Le résultat renvoyé sera bien deux colonnes et toutes les lignes. L’ordre des colonnes en sortie est donné par l’ordre d’écriture dans le SELECT (ici ce sera d’abord name puis surname).

Si vous souhaitez sélectionner toutes les colonnes, il faut utiliser le symbole * (étoile). Vous verrez plus tard qu’en informatique, le symbole * désigne souvent “tout”.

‍

LIMIT

‍

La clause LIMIT permet de contrôler le nombre de lignes en résultat. Si, par exemple, nous voulons sélectionner toutes les colonnes mais ne renvoyer que 3 lignes de résultats, il faudra écrire la requête:

‍

SELECT *
FROM People
LIMIT 3

‍

Les lignes retournées seront les trois premières de la table People et non pas 3 tirées au hasard.

‍

WHERE

‍

Si maintenant nous ne souhaitons pas voir les X premières lignes mais voir les lignes qui correspondent à un critère donné ? C’est-à-dire si on souhaite filtrer les lignes de notre table. On a besoin pour cela de la clause WHERE. Regardons l’exemple de requête suivante:

‍

SELECT *
FROM People
WHERE name = "Paul"

‍

Elle va renvoyer toutes les colonnes (* dans la clause SELECT) de la table People seulement pour les lignes qui vérifient la condition name = “Paul”, C'est-à-dire seulement les lignes qui ont “Paul” comme valeur dans la colonne name. Les “” autour de Paul sont simplement pour préciser à SQL que la valeur est bien du texte. Etant donné que dans la table People Il n’y a qu’une seule ligne ayant “Paul” comme valeur dans la colonne name, il n’y aura qu’une ligne en réponse à cette requête, c'est-à-dire la première ligne de la table.

‍

La condition dans le WHERE qui a été utilisée ici est l’égalité, mais il existe en SQL plusieurs autres conditions possibles:

‍

Strictement supérieur

Supérieur ou égal

Strictement inférieur

Inférieur ou égal

Egal

!= (ou <>)

Différent de

‍

Vous pouvez combiner plusieurs critères, mais pour cela vous devez connaître deux nouvelles clauses AND et OR.

AND, OR

‍

Critère A AND critère B est vrai uniquement si à la fois les critères A et B sont vrais. A contrario, Critère A OR critère B est vrai si au moins un des deux critères A ou B est vrai. Prenons les deux exemples de requêtes suivantes:

‍

SELECT *
FROM People
WHERE name = "Paul" AND number_of_children = 0

‍

Cette requête va retourner toutes les colonnes de la table People des lignes qui ont la valeur “Paul” dans la colonne name ET qui ont la valeur 0 dans la colonne number_of_children. C'est-à-dire la première ligne de la table. Elle est complètement différente de cette requête:

‍

SELECT *
FROM People
WHERE name = "Paul" OR number_of_children = 0

‍

Celle-ci va retourner toutes les lignes qui ont soit “Paul” dans la colonne name, soit 0 en valeur dans la colonne number_of_children. On parlera d’union dans le cas d’une clause OR et d’intersection dans le cas d’une clause AND. Cette dernière retournera deux lignes, la première et la dernière car la première ligne a “Paul” dans name et la dernière a 0 dans number_of_children. Notez que la première ligne de la table remplissait les deux conditions.

‍

Que se passe-t-il si j’ai une liste de prénoms et je souhaite retourner toutes les lignes qui ont un prénom dans celle-ci ? Avec ce que vous avez vu jusqu’à maintenant, il vous suffirait de faire une condition dans le WHERE avec autant de clauses OR que de prénoms dans votre liste ce qui nous ferait répéter inutilement les choses. Il existe pour cela la clause IN.

IN

‍

La clause IN permet de remplacer une série de OR dans la condition. Imaginons que je souhaite retourner les lignes dont les prénoms sont soit Paul, Louis ou Clara. Je peux écrire la requête comme cela:

‍

SELECT *
FROM People
WHERE name IN ("Paul", "Louis", "Clara")

‍

Beaucoup plus simple ! De même si je souhaite retourner toutes les lignes ayant entre 1 et 4 enfants, je peux m’éviter les clauses OR ou AND et n’utiliser que la condition BETWEEN.

‍

BETWEEN

‍

Considérons donc la requête suivante:

‍

SELECT *
FROM People
WHERE number_of_children BETWEEN 1 AND 4

‍

Les bornes du BETWEEN sont inclusives, C'est-à-dire que la condition écrite au-dessus est équivalente à :

‍

WHERE number_of_children >= 1 AND number_of_children <= 4

‍

Une autre clause très utilisée dans la condition du WHERE est la clause LIKE.

‍

LIKE

‍

La clause LIKE (ou ILIKE en fonction de certains SGBD) permet de faire des recherches dans du texte en essayant de trouver les résultats qui correspondent au pattern que vous lui avez donné. Par exemple:

‍

SELECT *
FROM People
WHERE name LIKE 'C%'

‍

Cette requête va renvoyer toutes les colonnes pour les lignes qui ont une valeur dans name qui commence par C. On distingue plusieurs formats dans le LIKE:

‍

‘C%’

Retourne toutes les valeurs qui commencent par la lettre C

‘%C’

Retourne toutes les valeurs qui finissent par la lettre C

‘%C%’

Retourne toutes les valeurs qui contiennent C (que ce soit la première, la dernière ou une lettre au milieu)

`_C%’

Retourne toutes les valeurs qui ont la lettre C en deuxième lettre

‍

Vous avez, avec ces premières clauses, une base vous permettant d’interroger n’importe quelle table. Il nous reste à voir comment changer les noms des colonnes en sortie dans les résultats pour faciliter la compréhension.

‍

AS

‍

Imaginons que la colonne number_of_children soit un nom un peu trop long, et que nous souhaiterions dans les résultats avoir simplement children. Il va falloir utiliser ce qu’on appelle un alias avec la clause AS. Observez la requête suivante qui ne retourne que la colonne number_of_children:

‍

SELECT number_of_children AS children
FROM People

‍

Le AS, ici, va renommer la colonne en sortie en children mais ne changera pas le nom de cette colonne en base de données. Ce n’est que dans les résultats de sortie ! Le concept d’alias semble superflu mais il est indispensable en SQL et largement utilisé. Remarque importante, vous ne pouvez pas faire référence aux alias du SELECT dans la condition WHERE de la même requête !

‍

Il nous reste une dernière clause à voir dans cette introduction, c’est le tri des résultats.

‍

ORDER BY

‍

La clause ORDER BY permet de trier les résultats d’une requête par un ou plusieurs critères. On peut trier dans l’ordre croissant ou décroissant une colonne ou bien combiner les critères de tri. Prenons par exemple la requête suivante:

‍

SELECT *
FROM People
ORDER BY number_of_children DESC

‍

Ici, on trie les lignes en sortie par number_of_children décroissant (à cause de la clause DESC qui la suit pour descending en anglais). Le problème est que Paul et David ont le même nombre d’enfants et on pourrait vouloir ensuite trier par nom de famille croissant, on modifiera la requête comme suit :

‍

SELECT *
FROM People
ORDER BY number_of_children DESC, surname ASC

‍

Remarques:

Trier par une colonne contenant du texte revient à trier par ordre lexicographique.
Le tri se fait par ordre d’apparition dans la clause ORDER BY. On va d’abord trier par number_of_children décroissant puis par nom de famille croissant.
La clause ASC n’est pas nécessaire, car par défaut le tri est croissant.

‍

Une pratique très courante est de faire référence aux colonnes qui serviront au tri par leur indice de position dans le SELECT. A titre d’exemple, la requête précédente est équivalente à celle-ci:

‍

SELECT name, surname, birth_date, number_of_children
FROM People
ORDER BY 4 DESC, 2 ASC

‍

Le premier 4 dans la clause ORDER BY fait référence au 4ème nom de colonne dans le SELECT, à savoir number_of_children. De même, le 2 fait référence à la colonne surname.

‍

L’ordre des clauses est fondamental ! Il doit toujours suivre SELECT, FROM, WHERE, ORDER BY. Si vous changez cet ordre, une erreur sera levée.

‍