Def et Utilité :

Structured Query Language est un langage qui doit permettre aux utilisateurs techniques et non techniques de manipuler des bases de données relationnels.
De nombreuses déclinaisons existent :

• SQLite
• MySQL
• Postgres
• Oracle
• Microsoft SQL Server
  Mais alors c'est quoi une base de donnée relationnelle ?
  C'est une collection de feuilles excel liés en elles. Les feuilles ont un nombre de colonnes et de lignes définit.
  Voici un exemple de table :

Documentation :

Cette documentation suit l'apprentissage de ce site :
https://sqlbolt.com/

SELECT :

SELECT column, another_column, … FROM mytable;

sert à sélectionner des données spécifiques d'une base de données. Voici ce que fait chaque partie de la requête :

• SELECT column, another_column, ... : Cette partie spécifie les colonnes à extraire de la table. Vous pouvez lister autant de colonnes que vous le souhaitez, ou utiliser * pour sélectionner toutes les colonnes, par exemple :

  SELECT * FROM mytable;
  

- `FROM mytable` : Indique la table à partir de laquelle les données doivent être extraites. Ici, les données sont extraites de la table appelée `mytable`.
### Contraintes : 
----
Les contraintes permettent de filtrer nos résultats de manière plus précise : 
```SQL
SELECT column, another_column, … FROM mytable WHERE condition AND/OR _another_condition_ AND/OR …;

• WHERE condition: Cette clause est utilisée pour définir des conditions qui doivent être remplies pour qu'un enregistrement soit inclus dans les résultats retournés. Les conditions peuvent être basées sur les valeurs des colonnes, des comparaisons, des expressions, etc.

• AND/OR another_condition: Vous pouvez ajouter plusieurs conditions en utilisant les opérateurs AND et OR pour affiner davantage les résultats :

  • AND est utilisé pour s'assurer que toutes les conditions liées avec AND doivent être vraies pour qu'un enregistrement soit inclus dans les résultats.
  • OR signifie que si l'une des conditions liées avec OR est vraie, l'enregistrement sera inclus dans les résultats
    Il est possible de complexifier encore les requêtes :

SELECT name, age
FROM users
WHERE age >= 18
    AND (name = 'Alice' OR name = 'Bob');

Comment faire des recherches de text précises ?

SELECT * FROM movies WHERE title LIKE "toy story%";

DISTINCT :

DISTINCT a pour but de supprimer des résultats les doublons :

SELECT DISTINCT column, another_column, … FROM mytable WHERE _condition(s)_;

• SELECT DISTINCT column, another_column, ...: Cette partie de la requête indique que vous souhaitez récupérer des valeurs uniques pour les colonnes spécifiées. L'usage de DISTINCT garantit que chaque combinaison de valeurs dans les colonnes sélectionnées apparaîtra une seule fois dans les résultats. Si des doublons existent dans les données, ils seront filtrés pour ne présenter que des entrées uniques.

ORDER BY :

ORDER BY a pour but de mettre en ordre les résultats selon un critère.

SELECT column, another_column, … FROM mytable WHERE _condition(s)_ ORDER BY column ASC/DESC;

• ORDER BY column ASC/DESC: Cette clause est utilisée pour trier les résultats selon les valeurs d'une colonne spécifique.
  • ASC indique que les résultats doivent être triés en ordre croissant (de la plus petite à la plus grande valeur).
  • DESC indique que les résultats doivent être triés en ordre décroissant (de la plus grande à la plus petite valeur).
    ORDER BY est souvent utilisé avec LIMIT et OFFSET.

SELECT column, another_column, … FROM mytable WHERE _condition(s)_ ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset;

• ORDER BY column ASC/DESC : Trie les résultats par une colonne spécifique, soit en ordre croissant (ASC) soit en ordre décroissant (DESC).
• LIMIT num_limit : Limite le nombre d'enregistrements renvoyés à num_limit. Cela est utile pour contrôler le volume de données renvoyé, particulièrement dans les contextes de pagination ou lorsque les ensembles de données sont très grands.
• OFFSET num_offset : Spécifie le nombre d'enregistrements à sauter avant de commencer à renvoyer les résultats. C'est également une fonctionnalité courante dans les systèmes de pagination, permettant aux utilisateurs de sauter à une page spécifique de résultats.
  Exemple :

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5 OFFSET 5;

JOIN :

Le but de join est de travailler et récupérer des résultats sur plusieurs fiches SQL .

INNER JOIN :

SELECT column, another_table_column, … FROM mytable INNER JOIN another_table ON mytable.id = another_table.id WHERE _condition(s)_ ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

• FROM mytable : Indique la table principale à partir de laquelle la requête commence à collecter les données, ici nommée mytable
• INNER JOIN another_table ON mytable.id = another_table.id : Ceci effectue une jointure interne entre mytable et another_table. Les données sont combinées en fonction des lignes où l'identifiant (id) dans mytable correspond à l'identifiant dans another_table. Ce type de jointure renvoie uniquement les lignes ayant une correspondance dans les deux tables.

LEFT JOIN, RIGHT JOIN, FULL JOIN :

Ces mots clés sont utiles pour les tables qui sont asymétriques et ne possèdent pas de mot clé en commun.

SELECT column, another_column, … FROM mytable INNER/LEFT/RIGHT/FULL JOIN another_table ON mytable.id = another_table.matching_id WHERE _condition(s)_ ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

• INNER/LEFT/RIGHT/FULL JOIN another_table ON mytable.id = another_table.matching_id :
  • Cette instruction réalise une jointure entre mytable et another_table. Le type de jointure (INNER, LEFT, RIGHT, FULL) affecte les résultats :
    • INNER JOIN : Renvoie les lignes où il existe une correspondance dans les deux tables.
    • LEFT JOIN : Renvoie toutes les lignes de la première table (mytable), et les correspondances de la deuxième table (another_table). Si aucune correspondance n'est trouvée, les résultats contiendront NULL pour les colonnes de la deuxième table.
    • RIGHT JOIN : Renvoie toutes les lignes de la deuxième table (another_table), et les correspondances de la première table (mytable). Si aucune correspondance n'est trouvée, les résultats contiendront NULL pour les colonnes de la première table.
    • FULL JOIN : Combine les résultats des jointures LEFT et RIGHT. Renvoie toutes les lignes de mytable et another_table, avec les correspondances quand elles existent et NULL autrement

NULL VALUE :

Il faut éviter autant que faire ce peut les value NULL qui peuvent empêcher l'exploitation des données.

SELECT column, another_column, … FROM mytable WHERE column IS/IS NOT NULL AND/OR _another_condition_ AND/OR …;

• WHERE column IS/IS NOT NULL :
  • Cette condition filtre les lignes basées sur la présence ou l'absence de valeurs dans la colonne spécifiée. IS NULL sera utilisé pour trouver les lignes où la colonne spécifiée contient une valeur NULL (c'est-à-dire, aucun donnée), tandis que IS NOT NULL permet de trouver les lignes où la colonne contient une valeur non-NULL.

Expressions :

Les expressions permettent d'intégrer une logique mathématique pour transformer les valeurs reçues par exemple.

SELECT particle_speed / 2.0 AS half_particle_speed FROM physics_data WHERE ABS(particle_position) * 10.0 > 500;

• SELECT particle_speed / 2.0 AS half_particle_speed :
  • Cette partie de la requête calcule la moitié de la vitesse des particules. particle_speed est probablement une colonne dans la table physics_data qui contient la vitesse de chaque particule. La vitesse est divisée par 2.0 pour obtenir la moitié de cette valeur.
  • AS half_particle_speed est un alias utilisé pour nommer la colonne résultante dans le jeu de résultats. Cela signifie que dans les résultats de la requête, cette colonne calculée sera étiquetée comme half_particle_speed au lieu d'une expression.
• FROM physics_data :
  • Spécifie que la table d'où proviennent les données est physics_data. Cette table contient des informations relatives à des particules, y compris leur vitesse (particle_speed) et probablement d'autres paramètres physiques.
• WHERE ABS(particle_position) * 10.0 > 500 :
  • Cette clause WHERE filtre les lignes incluses dans les résultats finaux basée sur la condition donnée. ABS(particle_position) applique la fonction ABS, qui retourne la valeur absolue de particle_position. particle_position est vraisemblablement une autre colonne de physics_data qui indique la position de chaque particule.
  • La condition multiplie la valeur absolue de la position de la particule par 10.0 et inclut seulement les lignes où ce produit est supérieur à 500. Cela signifie que la requête sélectionne les particules dont la position, indépendamment du signe (+ ou -), et après multiplication par 10, dépasse 500 en valeur absolue.

Aggregates :

• SELECT AGG_FUNC(column_or_expression) AS aggregate_description,
  • AGG_FUNC(column_or_expression) : AGG_FUNC est un placeholder pour une fonction d'agrégation, telle que SUM, AVG (moyenne), MAX, MIN, COUNT, etc. Ces fonctions sont utilisées pour effectuer un calcul sur un ensemble de valeurs (généralement une colonne) et produire un résultat unique. Par exemple, SUM(column) additionnerait toutes les valeurs de la colonne spécifiée.
  • column_or_expression : Indique la colonne ou l'expression sur laquelle la fonction d'agrégation est appliquée. Cela peut être simplement le nom d'une colonne ou une expression plus complexe impliquant une ou plusieurs colonnes.
  • AS aggregate_description : Fournit un alias pour la colonne de résultat de l'agrégation, facilitant la référence à cette colonne dans des traitements ultérieurs ou dans les résultats de sortie. Par exemple, si vous utilisez SUM(salary) AS total_salary, total_salary serait l'alias de la somme des salaires.
    Les fonctions aggrégées sont un peu comme les fonctions EXCEL :

Grouped Aggregate :

SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description, … FROM mytable WHERE _constraint_expression_ GROUP BY column;

• SELECT AGG_FUNC(column_or_expression) AS aggregate_description, … :
  • AGG_FUNC(column_or_expression) représente une fonction d'agrégation appliquée à une colonne ou une expression spécifique. Les fonctions d'agrégation courantes incluent SUM(), AVG(), MAX(), MIN(), et COUNT().
  • AS aggregate_description permet de renommer le résultat de la fonction d'agrégation pour une utilisation plus simple dans les résultats de la requête.
    Exemple :

SELECT AVG(salary) AS average_salary FROM employees WHERE department = 'Sales' GROUP BY region;

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

• group_by_column : une colonne selon laquelle les données seront regroupées. Cette colonne est souvent celle utilisée dans la clause GROUP BY.
• AGG_FUNC(column_expression) : une fonction d'agrégation appliquée à une colonne ou une expression. Comme expliqué précédemment, il peut s'agir de fonctions telles que SUM(), AVG(), MAX(), MIN(), et COUNT().
• AS aggregate_result_alias : permet de renommer le résultat de la fonction d'agrégation pour simplifier les références dans les résultats de la requête.
• GROUP BY column :
  • Les lignes qui passent le filtre WHERE sont ensuite regroupées par les valeurs dans la colonne spécifiée. L'agrégation est calculée pour chaque groupe.

• HAVING group_condition :
  • Après que les données ont été regroupées et les agrégations calculées, HAVING est utilisé pour filtrer ces groupes. Contrairement à WHERE qui filtre les lignes, HAVING filtre les groupes basés sur le résultat de l'agrégation.

HAVING et GROUP BY :

SELECT group_by_column, AGG_FUNC(_column_expression_) AS aggregate_result_alias, … FROM mytable WHERE _condition_ GROUP BY column **HAVING _group_condition_**;

Ordre d'execution :

SELECT DISTINCT column, AGG_FUNC(_column_or_expression_), … FROM mytable JOIN another_table ON mytable.column = another_table.column WHERE _constraint_expression_ GROUP BY column HAVING _constraint_expression_ ORDER BY _column_ ASC/DESC LIMIT _count_ OFFSET _COUNT_;

1. FROM :
   • C'est la première étape de l'exécution. SQL détermine d'abord quelle(s) table(s) est/sont impliquée(s) dans la requête.
2. JOIN (si présent) :
   • Si la requête implique des jointures entre plusieurs tables, elles sont résolues après la spécification des tables dans la clause FROM.
3. WHERE :
   • Cette clause filtre les lignes avant tout regroupement, agrégation, ou tri. Seules les lignes qui satisfont la condition spécifiée sont inclues dans les étapes suivantes.
4. GROUP BY :
   • Après le filtrage initial, les lignes restantes sont regroupées en fonction des valeurs des colonnes spécifiées dans cette clause.
5. AGGREGATION :
   • Les fonctions d'agrégation (comme SUM, AVG, COUNT, etc.) sont appliquées aux groupes formés par la clause GROUP BY.
6. HAVING :
   • Cette clause filtre les groupes formés en fonction des résultats des fonctions d'agrégation. Contrairement à WHERE, HAVING opère après l'agrégation.
7. SELECT :
   • Les colonnes ou expressions spécifiées sont sélectionnées et préparées pour la sortie. Cela inclut les colonnes directes, les expressions et les résultats des fonctions d'agrégation.
8. DISTINCT (si présent) :
   • Si spécifié, cette opération élimine les doublons dans les résultats après le traitement de SELECT.
9. ORDER BY :
   • Cette étape trie les résultats finaux selon les colonnes ou critères spécifiés. C'est une des dernières étapes avant que les données soient retournées à l'utilisateur.
10. LIMIT / OFFSET (si présent) :
    • Ces clauses limitent le nombre de lignes à retourner et déterminent à partir de quelle ligne commencer à retourner les résultats. Elles sont appliquées tout à la fin du traitement de la requête.

INSERT :

Certaines bases de données contiennent des templates, des shema qui définissent des types de données dans des champs.

INSERT INTO mytable VALUES (value_or_expr, another_value_or_expr, …), (value_or_expr_2, another_value_or_expr_2, …), …;

par exemple :

INSERT INTO boxoffice **(movie_id, rating, sales_in_millions)** VALUES (1, 9.9, 283742034 / 1000000);

Donc si on ne précise pas les champs tout est inséré dans l'ordre par défaut

UPDATE :

UPDATE mytable SET column = value_or_expr, other_column = another_value_or_expr, … WHERE condition;

Il faut faire très attention avec les requêtes update qui peuvent détruire des bases de données.
Exemple :

UPDATE movies
SET director = "John Lasseter"
WHERE id = 2;

DELETE :

Sert à supprimer des lignes dans une table :

DELETE FROM mytable WHERE condition;

CREATE TABLE :

CREATE TABLE IF NOT EXISTS mytable ( column _DataType_ _TableConstraint_ DEFAULT _default_value_, another_column _DataType_ _TableConstraint_ DEFAULT _default_value_, … );

• CREATE TABLE IF NOT EXISTS mytable :
  • CREATE TABLE est la commande pour créer une nouvelle table dans la base de données.
  • IF NOT EXISTS est une condition qui vérifie si une table portant le même nom (mytable dans ce cas) existe déjà dans la base de données. Si c'est le cas, la commande n'aura aucun effet (i.e., la table ne sera pas recréée et aucune erreur ne sera générée). Si la table n'existe pas, alors elle sera créée selon la définition fournie.
  • mytable est le nom de la nouvelle table à créer.

• Définition des colonnes :
  • Chaque ligne après le nom de la table définit une colonne de la table. La syntaxe générale pour définir une colonne est :
    column_name DataType TableConstraint DEFAULT default_value
  • column_name est le nom de la colonne.
  • DataType spécifie le type de données que la colonne stockera, par exemple INT, VARCHAR, DATE, etc.
  • TableConstraint (optionnel) spécifie les contraintes appliquées à la colonne. Les contraintes de table courantes incluent PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, etc. Ces contraintes sont utilisées pour imposer certaines règles sur les données de la colonne pour maintenir l'intégrité et la logique des données.
  • DEFAULT default_value (optionnel) définit une valeur par défaut pour la colonne. Si une valeur n'est pas spécifiée lors de l'insertion d'une ligne, la valeur par défaut sera utilisée.
    Exemple :

CREATE TABLE IF NOT EXISTS employees ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, salary DECIMAL(10, 2) DEFAULT 0.00 );

CREATE TABLE Database (
    Name TEXT,
    Version FLOAT,
    Download_count INTEGER
);

Comment peut-on préciser quel type de données correspond à quel champ ?

ALTER TABLE :

ADD :

ALTER TABLE mytable ADD column _DataType_ _OptionalTableConstraint_ DEFAULT default_value;

• ALTER TABLE mytable :
  • ALTER TABLE est la commande utilisée pour modifier la structure d'une table existante dans la base de données.
  • mytable est le nom de la table à modifier.
• ADD column DataType OptionalTableConstraint DEFAULT default_value :
  ADD est l'instruction qui spécifie que vous souhaitez ajouter quelque chose à la table, dans ce cas, une nouvelle colonne.
  • column est le nom de la nouvelle colonne à ajouter.
  • DataType définit le type de données que la colonne va stocker (par exemple, INT, VARCHAR, DATE, etc.).
  • OptionalTableConstraint (facultatif) indique toute contrainte que vous souhaitez appliquer à cette colonne, comme NOT NULL, UNIQUE, PRIMARY KEY, etc. Ces contraintes aident à maintenir l'intégrité des données dans la table.
  • DEFAULT default_value (facultatif) est utilisé pour définir une valeur par défaut pour la colonne. Si une valeur pour cette colonne n'est pas fournie lors de l'insertion d'une nouvelle ligne, la valeur par défaut sera utilisée automatiquement.
    Exemple :

ALTER TABLE movies ADD Aspect_ration FLOAT

DROP :

ALTER TABLE mytable DROP column_to_be_deleted;

RENAME :

ALTER TABLE mytable RENAME TO new_table_name;

DROP TABLE :

Drop table supprime une table, toutes ses données, métadonnées, et le schéma de la table :

DROP TABLE IF EXISTS mytable;

UNION :

La commande union permet de mettre bout à bout des requêtes SQL.

SELECT * FROM table1
UNION
SELECT * FROM table2

Commandes avancées :

• DATABASE() : permet d'afficher le nom de la database
• VERSION() : donne la version de la database
• CURRENT_USER() : utilisateur courant
• SELECT * FROM information_schema.tables : lister les tables dans la base de donnée
• SELECT * FROM information_schema.columns : lister les colonnes
• MD5() : converti en md5 un résultat.
• SLEEP() : met le site en attente pdt x secondes
• LENGTH() : obtient la longueur d'une chaine de charactères

Exemples :

Cette section est dédiée à des exemples de requêtes sympa pour en apprendre plus sur la syntaxe.

SELECT city, longitude FROM north_american_cities
WHERE longitude < -87.629798
ORDER BY longitude ASC;

SELECT title, domestic_sales, international_sales, rating
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
WHERE domestic_sales <= international_sales
ORDER BY rating DESC;

SELECT title, year
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
WHERE year % 2 = 0;

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

SELECT director, SUM(domestic_sales + international_sales) as Cumulative_sales_from_all_movies
FROM movies
    INNER JOIN boxoffice
        ON movies.id = boxoffice.movie_id
GROUP BY director;