_______    ________    __
                   /       |  |        |  |  |
                  |      __|  |   __   |  |  |
                   \_   |_    |  | _|  |  |  |
                   __\    \   |  |_|   |  |  |_____
                  |        |  |        |  |        |
                  |_______/   |_____/\_|  |________|
                              SQL SERVER



                          sql.md 19/01/2024
                           ultimeccol.com

Sommaire

  Definition
  CHAPITRE 1 - Syntaxe TSQL
     1.1. Avant de commencer
     1.2. Modifications de la base
     1.3. Requêtes <b>de base</b>
     1.4. SQL Sous-requête
     1.5. Jointure SQL
     1.6. Traitement de masse
     1.7. Arbres de donnees

  CHAPITRE 2 - SQL Avancé
     2.1. Index SQL
     2.2. SQL EXPLAIN
     2.3. Modes de verrouillage
     2.4. Le transactionnel
     2.5. Les triggers, spécialités du C/S
     2.6. Les procédures stockées, régals du C/S
     2.7. Les curseurs
     2.8. Le journal, particularité des SGBD C/S
     2.9. Les vues
    2.10. Impact du nombre de colonnes
    2.11. Gestion de base de donnée

  CHAPITRE 3 - Architecture 
     3.1. Architecture avec une base synchronisé en lecture seule
     3.2. Architecture de la réplication
     3.3. Transfer de journaux
     3.4. Architecture du Miroir

Definition:

Le SQL est un langage de requêtes descriptif, standard pour toutes les bases de données qui suivent le modèle relationnel. Ce langage permet toutes les opérations sur les données dans tous les cas d'utilisation de la base. Avec Oracle, on peut associer au SQL un langage procédural, le PL/SQL, qui ajoute de nombreuses possibilités dans la manipulation des données.

CHAPITRE 1 - Syntaxe TSQL

1.1. Avant de commencer

Commentaires en SQL

Il peut être intéressant d'insérer des commentaires dans les requêtes SQL pour mieux s'y retrouver lorsqu'il y a de grosses requêtes complexes. Il y a plusieurs façons de faire des commentaires dans le langage SQL, qui dépendent notamment du Système de Gestion de Base de Données utilisé (SGBD) et de sa version.

Ligne de commentaire :

Le double tiret permet de faire un commentaire jusqu'à la fin de la ligne.

Exemple

Commentaire multi-lignes : /* et */

Le commentaire multi-ligne à l'avantage de pouvoir indiquer où commence et où se termine le commentaire. Il est donc possible de l'utiliser en plein milieu d'une requête SQL sans problème.

Exemple

Bug potentiel pour les commentaires sur une ligne

Attention, dans certains contextes, si vous utilisez un système qui va supprimer les retours à la ligne, votre requête sera uniquement sur une ligne. Dans une telle situation, un commentaire effectué avec « – » dans une requête peut donc créer un bug.

Par conséquent il faut se méfier de librairies externes qui peuvent ré-écrire les requêtes SQL ou alors tout simplement se méfier de copier/coller.

Nom des tables et colonnes

la notation des noms de table est différente selon les SGBD, par ailleurs un nom de table ou colonne qui est aussi un mot clé du langage SQL du moteur doit être strictement nomé avec la notation propre au langage du SGBD.

1.2. Modifications de la base

SQL CREATE DATABASE

La création d'une base de données en SQL est possible en ligne de commande. Même si les systèmes de gestion de base de données (SGBD) sont souvent utilisés pour créer une base, il convient de connaître la commande à utiliser, qui est très simple.

Pour créer une base de données qui sera appelé « ma_base » il suffit d'utiliser la requête suivante qui est très simple :

Base du même nom qui existe déjà

Avec MySQL, si une base de données porte déjà ce nom, la requête retournera une erreur. Pour éviter d'avoir cette erreur, il convient d'utiliser la requête suivante pour MySQL :

L'option IF NOT EXISTS permet juste de ne pas retourner d'erreur si une base du même nom existe déjà. La base de données ne sera pas écrasée.

Options

Dans le standard SQL la commande CREATE DATABASE n'existe normalement pas. En conséquence il revient de vérifier la documentation des différents SGBD pour vérifier les syntaxes possibles pour définir des options. Ces options permettent selon les cas, de définir les jeux de caractères, le propriétaire de la base ou même les limites de connexion.

SQL DROP DATABASE

En SQL, la commande DROP DATABASE permet de supprimer totalement une base de données et tout ce qu'elle contient. Cette commande est à utiliser avec beaucoup d'attention car elle permet de supprimer tout ce qui est inclus dans une base: les tables, les données, les index …

Pour supprimer la base de données « ma_base », la requête est la suivante :

Attention : cela va supprimer toutes les tables et toutes les données de cette base. Si vous n'êtes pas sûr de ce que vous faites, n'hésitez pas à effectuer une sauvegarde de la base avant de supprimer.

Ne pas afficher d'erreur si la base n'existe pas

Par défaut, si le nom de base utilisé n'existe pas, la requête retournera une erreur. Pour éviter d'obtenir cette erreur si vous n'êtes pas sûr du nom, il est possible d'utiliser l'option IF EXISTS. La syntaxe sera alors la suivante :

SQL CREATE SCHEMA

Un schéma est un support logique d'objet de base de données. Un schéma de base de données d'un système de base de données est sa structure décrite dans un langage formel pris en charge par le système de gestion de base de données. La définition formelle d'un schéma de base de données est un ensemble de formules (phrases) appelées contraintes d'intégrité imposées à une base de données. Ces contraintes d'intégrité assurent la compatibilité entre les différentes parties du schéma. Toutes les contraintes peuvent être exprimées dans le même langage.

La création de schémas peut s'avérer utile lorsque des objets ont des références circulaires, c'est-à-dire lorsque nous devons créer deux tables dont chacune possède une clé étrangère faisant référence à l'autre table. Les différentes implémentations traitent les schémas de manière légèrement différente.

Le schema par défaut que sqlserver est dbo, il est possible d'attribuer un schema particulier selon l'utilisateur connecté.

Apres la création d'un schema il est possible de créer des tables dans un schema. Pour y acceder il faudra obligatoirement préfixer le nom de la table avec le nom du schéma

A savoir : il est possible d'accéder à une table d'une autre base avec spécifiant son "chemin" complet qui est composé du nom de la base, du nom du schema puis du nom de la table, par exemple : ma_base.dbo.ma_table

SQL CREATE TABLE

La commande CREATE TABLE permet de créer une table en SQL. Un tableau est une entité qui est contenu dans une base de données pour stocker des données ordonnées dans des colonnes. La création d'une table sert à définir les colonnes et le type de données qui seront contenus dans chacune des colonne (entier, chaîne de caractères, date, valeur binaire …).

La syntaxe générale pour créer une table est la suivante :

Dans cette requête, 4 colonnes ont été définies. Le mot-clé « type_donnees » sera à remplacer par un mot-clé pour définir le type de données (INT, DATE, TEXT …). Pour chaque colonne, il est également possible de définir des options telles que (liste non-exhaustive) :

• NOT NULL : empêche d'enregistrer une valeur nulle pour une colonne.
• DEFAULT : attribuer une valeur par défaut si aucune données n'est indiquée pour cette colonne lors de l'ajout d'une ligne dans la table.
• PRIMARY KEY : indiquer si cette colonne est considérée comme clé primaire pour un index.

Exemple

Imaginons que l'on souhaite créer une table utilisateur, dans laquelle chaque ligne correspond à un utilisateur inscrit sur un site web. La requête pour créer cette table peut ressembler à ceci :

Voici des explications sur les colonnes créées :

• id : identifiant unique qui est utilisé comme clé primaire et qui n'est pas NULL
• nom : nom de l'utilisateur dans une colonne de type VARCHAR avec 100 caractères au maximum
• prenom : idem mais pour le prénom
• email : adresse email enregistrée sous 255 caractères au maximum
• date_naissance : date de naissance enregistrée au format AAAA-MM-JJ (ex : '1973-11-17T00:00:00.000')
• pays : nom du pays de l'utilisateur sous 255 caractères au maximum
• ville : idem pour la ville
• code_postal : 5 caractères du code postal
• nombre_achat : nombre d'achat de cet utilisateur sur le site

SQL ALTER TABLE

La commande ALTER TABLE en SQL permet de modifier une table existante. Il est ainsi possible d'ajouter une colonne, d'en supprimer une ou de modifier une colonne existante, par exemple pour changer le type.

D'une manière générale, la commande s'utilise de la manière suivante :

Le mot-clé « instruction » ici sert à désigner une commande supplémentaire, qui sera détaillée ci-dessous selon l'action que l'on souhaite effectuer : ajouter, supprimer ou modifier une colonne.

Ajouter une colonne

L'ajout d'une colonne dans une table est relativement simple et peut s'effectuer à l'aide d'une requête ressemblant à ceci :

Exemple

Pour ajouter une colonne qui correspond à une rue sur une table utilisateur, il est possible d'utiliser la requête suivante :

Supprimer une colonne

Une syntaxe permet également de supprimer une colonne pour une table. Il y a 2 manières totalement équivalentes pour supprimer une colonne :

Ou (le résultat sera le même)

Modifier une colonne

Pour modifier une colonne, comme par exemple changer le type d'une colonne, il y a différentes syntaxes selon le SGBD.

Ici, le mot-clé « type_donnees » est à remplacer par un type de données tel que INT, VARCHAR, TEXT, DATE …

Renommer une colonne

Pour renommer une colonne, il convient d'indiquer l'ancien nom de la colonne et le nouveau nom de celle-ci.

Pour MySQL, il faut également indiquer le type de la colonne.

Ici « type_donnees » peut correspondre par exemple à INT, VARCHAR, TEXT, DATE …

Pour PostgreSQL la syntaxe est plus simple et ressemble à ceci (le type n'est pas demandé) :

SQL DROP TABLE

La commande DROP TABLE en SQL permet de supprimer définitivement une table d'une base de données. Cela supprime en même temps les éventuels index, trigger, contraintes et permissions associés à cette table.

**Attention : ** il faut utiliser cette commande avec attention car une fois supprimée, les données sont perdues. Avant de l'utiliser sur une base importante il peut être judicieux d'effectuer un backup (une sauvegarde) pour éviter les mauvaises surprises.

Pour supprimer une table « nom_table » il suffit simplement d'utiliser la syntaxe suivante :

A savoir : s'il y a une dépendance avec une autre table, il est recommandé de la supprimer avant de supprimer la table. C'est le cas par exemple s'il y a des clés étrangères.

Intérêts

Il arrive qu'une table soit créée temporairement pour stocker des données qui n'ont pas vocation à être réutilisées. La suppression d'une table non utilisée est avantageux sur plusieurs aspects :

• Libérer de la mémoire et alléger le poids des backups,
• éviter des erreurs dans le futur si une table porte un nom similaire ou qui porte à confusion,
• Lorsqu'un développeur ou administrateur de base de données découvre une application, il est plus rapide de comprendre le système s'il n'y a que les tables utilisées qui sont présentes.

Exemple de requête

Imaginons qu'une base de données possède une table « client_2009 » qui ne sera plus jamais utilisé et qui existe déjà dans un ancien backup. Pour supprimer cette table, il suffit d'effectuer la requête suivante :

L'exécution de cette requête va permettre de supprimer la table.

SQL TRUNCATE TABLE

En SQL, la commande TRUNCATE permet de supprimer toutes les données d'une table sans supprimer la table en elle-même. En d'autres mots, cela permet de purger la table. Cette instruction diffère de la commande DROP qui a pour but de supprimer les données ainsi que la table qui les contient.

**A noter : ** l'instruction TRUNCATE est semblable à l'instruction DELETE sans utilisation de WHERE. Parmi les petites différences TRUNCATE est toutefois plus rapide et utilise moins de ressources. Ces gains en performance se justifient notamment parce que la requête n'indiquera pas le nombre d'enregistrement supprimés et qu'il n'y aura pas d'enregistrement des modifications dans le journal.

Cette instruction s'utilise dans une requête SQL semblable à celle-ci :

Dans cet exemple, les données de la table « table » seront perdues une fois cette requête exécutée.

Exemple

Pour montrer un exemple concret de l'utilisation de cette commande, nous pouvons imaginer un système informatique contenant la liste des fournitures d'une entreprise. Ces données seraient tout simplement stockées dans une table « fourniture ».

Table « fourniture » :

Il est possible de supprimer toutes les données de cette table en utilisant la requête suivante :

Une fois la requête exécutée, la table ne contiendra plus aucun enregistrement. En d'autres mots, toutes les lignes du tableau présenté ci-dessus auront été supprimées.

1.3. Requêtes de base

SQL SELECT

L'utilisation la plus courante de SQL consiste à lire des données issues de la base de données. Cela s'effectue grâce à la commande SELECT, qui retourne des enregistrements dans un tableau de résultat. Cette commande peut sélectionner une ou plusieurs colonnes d'une table. L'utilisation basique de cette commande s'effectue de la manière suivante :

Cette requête va sélectionner (SELECT) le champ « nom_du_champ » provenant (FROM) du tableau appelé « nom_du_tableau ».

Exemple

Imaginons une base de données appelée « client » qui contient des informations sur les clients d'une entreprise.

Table « client » :

Si l'on veut avoir la liste des nom et prénom des clients, il suffit d'effectuer la requête suivante :

Résultat :

Il est possible de retourner automatiquement toutes les colonnes d'un tableau sans avoir à connaître le nom de toutes les colonnes. Au lieu de lister toutes les colonnes, il faut simplement utiliser le caractère « * » (étoile). C'est un joker qui permet de sélectionner toutes les colonnes. Il s'utilise de la manière suivante :

Cette requête retourne exactement les mêmes colonnes qu'il y a dans la base de données. Dans notre cas, le résultat sera donc :

Il est très utile également d'afficher en premier une ou plusieurs colonnes spécifique sur des table comprenant beaucoup de colonnes pour retrouver rapidement l'information recherchée, avant d'afficher toutes les informations.

Ordre des commandes du SELECT

Cette commande SQL est relativement commune car il est très fréquent de devoir lire les données issues d'une base de données. Il existe plusieurs commandes qui permettent de mieux gérer les données que l'ont souhaite lire. Voici un petit aperçu des fonctionnalités possibles qui sont abordées sur le reste du document:

• Joindre un autre tableau aux résultats
• Filtrer pour ne sélectionner que certains enregistrements
• Classer les résultats
• Grouper les résultats pour faire uniquement des statistiques (note moyenne, prix le plus élevé …)

Un requête SELECT peut devenir assez longue. Juste à titre informatif, voici une requête SELECT qui possède presque toutes les commandes possibles :

A noter : cette requête imaginaire sert principalement d'aide-mémoire pour savoir dans quel ordre sont utilisées chacune des commandes au sein d'une requête SELECT.

SQL DISTINCT

L'utilisation de la commande SELECT en SQL permet de lire toutes les données d'une ou plusieurs colonnes. Cette commande peut potentiellement afficher des lignes en double. Pour éviter des redondances dans les résultats il faut simplement ajouter DISTINCT après le mot SELECT. L'utilisation basique de cette commande consiste alors à effectuer la requête suivante :

Cette requête sélectionne le champ « ma_colonne » de la table « nom_du_tableau » en évitant de retourner des doublons.

Requête pour Oracle

Pour le Système de Gestion de Bases de Données (SGBD) Oracle, cette requête est remplacée par la commande « UNIQUE » :

Exemple

Prenons le cas concret d'une table « client » qui contient des noms et prénoms :

En utilisant seulement SELECT tous les noms sont retournés, or la table contient plusieurs fois le même prénom (cf. Pierre). Pour sélectionner uniquement les prénoms uniques il faut utiliser la requête suivante :

Résultat :

Ce résultat n'affiche volontairement qu'une seule fois le prénom « Pierre » grâce à l'utilisation de la commande DISTINCT qui n'affiche que les résultats distincts.

Intérêt L'utilisation de la commande DISTINCT est très pratique pour éviter les résultats en doubles. Cependant, pour optimiser les performances il est préférable d'utiliser la commande SQL GROUP BY lorsque c'est possible.

Quand on utilise la commande DISTINCT il peut être judicieux d'inclure une clé primaire parmis les champs retournés même si on en a pas besoin. Par exemple, dans le cas d'une jointure avec une autre table "facture" ou chacunes de ses lignes possède un id et un champ numeroFacture unique par rapport à l'id. Si dans le distinct on ne met pas l'id de la table "facture", mais uniquement le champ numeroFacture, SQL mettra beaucoup de temps.

Immaginons un échéancier de paiements. Chaque paiement à échéance peut encaisser une ou plusieurs factures

Pour cette requette, sql server ne semble pas bien optimisé et prendra beaucoup de temps. Mais si on rajoute l'id dans le select, sql server optimisera correctement l'exécution.

SQL AS (alias)

Dans le langage SQL il est possible d'utiliser des alias pour renommer temporairement une colonne ou une table dans une requête. Cette astuce est particulièrement utile pour faciliter la lecture des requêtes.

Alias sur une colonne

Permet de renommer le nom d'une colonne dans les résultats d'une requête SQL. C'est pratique pour avoir un nom facilement identifiable dans une application qui doit ensuite exploiter les résultats d'une recherche.

Cas concrets d'utilisations :

• Une colonne qui s'appelle normalement c_iso_3166 peut être renommée « code_pays » (cf. le code ISO 3166 correspond au code des pays), ce qui est plus simple à comprendre dans le reste du code par un développeur.

• Une requête qui utilise la commande UNION sur des champs aux noms différents peut être ambigu pour un développeur. En renommant les champs avec un même nom il est plus simple de traiter les résultats.

• Lorsqu'une fonction est utilisée, le nom d'une colonne peut-être un peu complexe. Il est ainsi possible de renommer la colonne sur laquelle il y a une fonction SQL. Exemple : SELECT COUNT(*) AS nombre_de_resultats FROM table.

• Lorsque plusieurs colonnes sont combinées il est plus simple de renommer la nouvelle colonne qui est une concaténation de plusieurs champs.

La syntaxe pour renommer une colonne de colonne1 à c1 est la suivante :

Cette syntaxe peut également s'afficher de la façon suivante :

> **A noter : ** à choisir il est préférable d'utiliser la commande «AS» pour > que ce soit plus explicite (plus simple à lire qu'un simple espace), d'autant > plus que c'est recommandé dans le standard ISO pour concevoir une requête SQL.

Exemple

Imaginons une site d'e-commerce qui possède une table de produits. Ces produits sont disponibles dans une même table dans plusieurs langues , dont le français. Le nom du produit peut ainsi être disponible dans la colonne « nom_fr_fr », ou « nom_en_gb ». Pour utiliser l'un ou l'autre des titres dans le reste de l'application sans avoir à se soucier du nom de la colonne, il est possible de renommer la colonne de son choix avec un nom générique. Dans notre cas, la requête pourra ressemble à ceci :

Résultat :

Comme nous pouvons le constater les colonnes ont été renommées.

Alias sur une table

Permet d'attribuer un autre nom à une table dans une requête SQL. Cela peut aider à avoir des noms plus court, plus simple et plus facilement compréhensible. Ceci est particulièrement vrai lorsqu'il y a des jointures.

La syntaxe pour renommer une table dans une requête est la suivante :

Cette requête peut également s'écrire de la façon suivante :

Exemple

Imaginons que les produits du site e-commerce soient répartis dans des catégories. Pour récupérer la liste des produits en même temps que la catégorie auquel il appartient il est possible d'utiliser une requête SQL avec une jointure. Cette requête peut utiliser des alias pour éviter d'utiliser à chaque fois le nom des tables. La requête ci-dessous renomme la table « produit » en « p » et la table « produit_categorie » en « pc » (plus court et donc plus rapide à écrire) :

Cette astuce est encore plus pratique lorsqu'il y a des noms de tables encore plus compliqués et lorsqu'il y a beaucoup de jointures.

SQL WHERE

La commande WHERE dans une requête SQL permet d'extraire les lignes d'une base de données qui respectent une condition. Cela permet d'obtenir uniquement les informations désirées.

La commande WHERE s'utilise en complément à une requête utilisant SELECT. La façon la plus simple de l'utiliser est la suivante :

Exemple

Imaginons une base de données appelée « client » qui contient le nom des clients, le nombre de commandes qu'ils ont effectuées et leur ville :

Pour obtenir seulement la liste des clients qui habitent à Paris, il faut effectuer la requête suivante :

Résultat :

**Attention : ** dans notre cas tout est en minuscule donc il n'y a pas eu de problème. Cependant, si une table est sensible à la casse, il faut faire attention aux majuscules et minuscules.

Opérateurs de comparaison

Il existe plusieurs opérateurs de comparaison. La liste ci-jointe présente quelques uns des opérateurs les plus couramment utilisés.

> **Attention : ** il y a quelques opérateurs qui n'existent pas dans des > vieilles versions de systèmes de gestion de bases de données (SGBD). De plus, > il y a de nouveaux opérateurs non indiqués ici qui sont disponibles avec > certains SGBD. N'hésitez pas à consulter la documentation de MySQL, PostgreSQL > ou autre pour voir ce qu'il vous est possible de faire.

SQL AND et OR

Une requête SQL peut être restreinte à l'aide de la condition WHERE. Les opérateurs logiques AND et OR peuvent être utilisés au sein de la commande WHERE pour combiner des conditions.

Les opérateurs sont à ajoutés dans la condition WHERE. Ils peuvent être combinés à l'infini pour filtrer les données comme souhaité.

L'opérateur AND permet de s'assurer que la condition1 ET la condition2 soient vraies :

L'opérateur OR vérifie quant à lui que la condition1 OU la condition2 est vraie :

Ces opérateurs peuvent être combinés à l'infini et mélangés. L'exemple ci-dessous filtre les résultats de la table « nom_table » si condition1 ET (condition2 OU condition3) est vrai :

Attention : il faut penser à utiliser des parenthèses lorsque c'est nécessaire. Cela permet d'éviter les erreurs et améliore la lecture d'une requête par un humain.

Exemples de données

Pour illustrer les prochaines commandes, nous allons considérer la table « produit » suivante :

Opérateur AND

L'opérateur AND permet de joindre plusieurs conditions dans une requête. En gardant la même table que précédemment et pour filtrer uniquement les produits informatique qui sont presque en rupture de stock (moins de 20 produits disponibles) il faut exécuter la requête suivante :

Résultat :

Opérateur OR

Pour filtrer les données afin d'avoir uniquement celles sur les produits « ordinateur » ou « clavier » il faut effectuer la recherche suivante :

Résultats :

Combiner AND et OR

Il ne faut pas oublier que les opérateurs peuvent être combinés pour effectuer de puissantes recherches. Il est possible de filtrer les produits « informatique » avec un stock inférieur à 20 et les produits « fourniture » avec un stock inférieur à 200 avec la recherche suivante :

Résultats :

SQL IN

L'opérateur logique IN dans SQLs'utilise avec la commande WHERE pour vérifier si une colonne est égale à une des valeurs comprise dans un set de valeurs déterminées. C'est une méthode simple pour vérifier si une colonne est égale à une valeur OU une autre valeur OU une autre valeur et ainsi de suite, sans avoir à utiliser de multiple fois l'opérateur OR.

Pour chercher toutes les lignes où la colonne « nom_colonne » est égale à ‘valeur 1' OU ‘valeur 2' ou ‘valeur 3', il est possible d'utiliser la syntaxe suivante :

``

> A savoir : il n'y a pas de limite au nombre d'arguments.

Cette syntaxe peut être associée à l'opérateur NOT pour rechercher toutes les lignes qui ne sont pas égales à l'une des valeurs stipulées.

Simplicité de l'opérateur IN

La syntaxe utilisée avec l'opérateur est plus simple que d'utiliser une succession d'opérateurs OR. Pour le montrer concrètement avec un exemple, voici 2 requêtes qui retourneront les mêmes résultats, l'une utilise l'opérateur IN, tandis que l'autre utilise plusieurs OR.

Requête équivalent à OR avec l'opérateur IN

Exemple

Imaginons une table « adresse » qui contient une liste d'adresses associées à des utilisateurs d'une application.

Si l'on souhaite obtenir les enregistrements des adresses de Paris et de Graimbouville, il est possible d'utiliser la requête suivante :

Résultats :

SQL BETWEEN

L'opérateur BETWEEN est utilisé dans une requête SQL pour sélectionner un intervalle de données dans une requête utilisant WHERE. L'intervalle peut être constitué de chaînes de caractères, de nombres ou de dates. L'exemple le plus concret consiste par exemple à récupérer uniquement les enregistrements entre 2 dates définies.

L'utilisation de la commande BETWEEN s'effectue de la manière suivante :

La requête suivante retournera toutes les lignes dont la valeur de la colonne « nom_colonne » sera comprise entre valeur1 et valeur2.

Exemple : filtrer entre 2 dates

Imaginons une table « utilisateur » qui contient les membres d'une application en ligne.

Si l'on souhaite obtenir les membres qui se sont inscrit entre le 1 avril 2012 et le 20 avril 2012 il est possible d'effectuer la requête suivante :

Résultat :

Exemple : filtrer entre 2 entiers

Si l'on souhaite obtenir tous les résultats dont l'identifiant n'est pas situé entre 4 et 10, il faudra alors utiliser la requête suivante :

Résultat :

L'autre élément important à savoir c'est que toutes les bases de données ne gèrent pas l'opérateur BETWEEN de la même manière. Certains systèmes vont inclure les valeurs qui définissent l'intervalle tandis que d'autres systèmes considèrent ces valeurs sont exclues. Il est important de consulter la documentation officielle de la base de données que vous utilisez pour avoir une réponse exacte à ce sujet.

SQL LIKE

L'opérateur LIKE est utilisé dans la clause WHERE des requêtes SQL. Ce mot-clé permet d'effectuer une recherche sur un modèle particulier. Il est par exemple possible de rechercher les enregistrements dont la valeur d'une colonne commence par telle ou telle lettre. Les modèles de recherches sont multiple.

La syntaxe à utiliser pour utiliser l'opérateur LIKE est la suivante :

Dans cet exemple le « modèle » n'a pas été défini, mais il ressemble très généralement à l'un des exemples suivants :

• LIKE ‘%a' : le caractère « % » est un caractère joker qui remplace tous les autres caractères. Ainsi, ce modèle permet de rechercher toutes les chaines de caractères qui se terminent par un « a ».
• LIKE ‘a%' : ce modèle permet de rechercher toutes les lignes de « colonne» qui commencent par un « a ».
• LIKE ‘%a%' : ce modèle est utilisé pour rechercher les enregistrements qui utilisent le caractère « a ».
• LIKE ‘pa%on' : ce modèle permet de rechercher les chaines qui commence par « pa » et qui se terminent par « on », comme « pantalon » ou « pardon ».
• LIKE ‘a_c' : peu utilisé, le caractère « _ » (underscore) peut être remplacé par n'importe quel caractère, mais un seul caractère uniquement (alors que le symbole pourcentage « % » peut être remplacé par un nombre variable de caractères . Ainsi, ce modèle permet de retourner les lignes « aac », « abc » ou même « azc ».

Exemple

Imaginons une table « client » qui contienne les enregistrements d'utilisateurs

Obtenir les résultats qui commencent par « N »

Si l'on souhaite obtenir uniquement les clients des villes qui commencent par un « N », il est possible d'utiliser la requête suivante :

Avec cette requête, seul les enregistrements suivants seront retournés :

Obtenir les résultats terminent par « e »

Requête :

Résultat :

SQL IS NULL / IS NOT NULL

Dans le langage SQL, l'opérateur IS permet de filtrer les résultats qui contiennent la valeur NULL. Cet opérateur est indispensable car la valeur NULL est une valeur inconnue et ne peut par conséquent pas être filtrée par les opérateurs de comparaison (cf. égal, inférieur, supérieur ou différent).

Pour filtrer les résultats où les champs d'une colonne sont à NULL il convient d'utiliser la syntaxe suivante :

A l'inverse, pour filtrer les résultats et obtenir uniquement les lignes qui ne sont pas NULL, il convient d'utiliser la syntaxe suivante :

> A savoir : l'opérateur IS retourne en réalité un booléen, c'est à dire > une valeur TRUE si la condition est vraie ou FALSE si la condition n'est pas > respectée. Cet opérateur est souvent utilisé avec la condition WHERE mais > peut aussi trouver son utilité lorsqu'une sous-requête est utilisée.

Exemple

Imaginons une application qui possède une table contenant les utilisateurs. Cette table possède 2 colonnes pour associer les adresses de livraison et de facturation à un utilisateur (grâce à une clé étrangère). Si cet utilisateur n'a pas d'adresse de facturation ou de livraison, alors le champ reste à NULL

*

Table « utilisateur » :

Exemple 1 : utilisateurs sans adresse de livraison

Il est possible d'obtenir la liste des utilisateurs qui ne possèdent pas d'adresse de livraison en utilisant la requête SQL suivante :

Résultat :

Les enregistrements montrent bien que seuls sont retournés les utilisateurs ayant la valeur NULL pour le champ de l'adresse de livraison.

Exemple 2 : utilisateurs avec une adresse de livraison

Pour obtenir uniquement les utilisateurs qui possèdent une adresse de livraison il convient de lancer la requête SQL suivante :

Résultat :

Les lignes retournées sont exclusivement celles qui n'ont pas une valeur NULL pour le champ de l'adresse de livraison.

SQL GROUP BY

La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats et utiliser une fonction de totaux sur un groupe de résultat. Sur une table qui contient toutes les ventes d'un magasin, il est par exemple possible de regrouper les ventes par clients identiques et d'obtenir le coût total des achats pour chaque client.

De façon générale, la commande GROUP BY s'utilise de la façon suivante :

> A noter : cette commande doit toujours s'utiliser après la commande WHERE > et avant la commande HAVING.

Exemple d'utilisation

Prenons en considération une table « achat » qui liste les ventes d'une boutique

Ce tableau contient une colonne qui sert d'identifiant pour chaque ligne, une autre qui contient le nom du client, le coût de la vente et la date d'achat.

Pour obtenir le coût total de chaque client en regroupant les commandes des mêmes clients, il faut utiliser la requête suivante :

La fonction SUM() permet d'additionner la valeur de chaque tarif pour un même client. Le résultat sera donc le suivant :

La manière simple de comprendre le GROUP BY c'est tout simplement d'assimiler qu'il va éviter de présenter plusieurs fois les mêmes lignes. C'est une méthode pour éviter les doublons.

A titre informatif, voici ce qu'on obtient de la requête sans utiliser GROUP BY.

Requête :

Résultat :

Utilisation d'autres fonctions de statistiques

Il existe plusieurs fonctions qui peuvent être utilisées pour manipuler plusieurs enregistrements, il s'agit des fonctions d'agrégation statistiques, les principales sont les suivantes :

• AVG() pour calculer la moyenne d'un set de valeur. Permet de connaître le prix du panier moyen pour de chaque client
• COUNT() pour compter le nombre de lignes concernées. Permet de savoir combien d'achats a été effectué par chaque client
• MAX() pour récupérer la plus haute valeur. Pour savoir l'achat le plus cher.
• MIN() pour récupérer la plus petite valeur. Utile par exemple pour connaître la date du premier achat d'un client
• SUM() pour calculer la somme de plusieurs lignes. Permet par exemple de connaître le total de tous les achats d'un client

Ces petites fonctions se révèlent rapidement indispensables pour travailler sur des données.

Utilisation de Group By pour concatener des chaines

Il est possible d'utiliser une fonction de concaténation de donnée, par exemple sur SQLServer

Il n'est pas possible d'inclure la colonne non agrégée dans la liste de sélection lorsque vous utilisez une clause "group by" dans SQL Server. Pour afficher les colonnes non agrégée tout en conservant le regroupement, voir le paragraphe SQL SELF JOIN.

SQL HAVING

La condition HAVING en SQL est presque similaire à WHERE à la seule différence que HAVING permet de filtrer en utilisant des fonctions telles que SUM(), COUNT(), AVG(), MIN() ou MAX().?

L'utilisation de HAVING s'utilise de la manière suivante :

Cela permet donc de SELECTIONNER les colonnes de la table « nom_table » en GROUPANT les lignes qui ont des valeurs identiques sur la colonne « colonne1 » et que la condition de HAVING soit respectée.

Important : HAVING est très souvent utilisé en même temps que GROUP BY bien que ce ne soit pas obligatoire.

Exemple

Pour utiliser un exemple concret, imaginons une table « achat » qui contient les achats de différents clients avec le coût du panier pour chaque achat.

Si dans cette table on souhaite récupérer la liste des clients qui ont commandé plus de 40? ??, toutes commandes confondues alors il est possible d'utiliser la requête suivante :

Résultat :

La cliente « Marie » a cumulée 38€ d'achat (un achat de 18€ et un autre de 20€) ce qui est inférieur à la limite de 40€ imposée par HAVING. En conséquent cette ligne n'est pas affichée dans le résultat.

SQL ORDER BY

La commande ORDER BY permet de trier les lignes dans un résultat d'une requête SQL. Il est possible de trier les données sur une ou plusieurs colonnes, par ordre ascendant ou descendant.

Une requête où l'on souhaite filtrer l'ordre des résultats utilise la commande ORDER BY de la sorte :

Par défaut les résultats sont classés par ordre ascendant, toutefois il est possible d'inverser l'ordre en utilisant le suffixe DESC après le nom de la colonne. Par ailleurs, il est possible de trier sur plusieurs colonnes en les séparant par une virgule. Une requête plus élaborée ressemblerait alors cela :

``

> **A noter : ** il n'est pas obligatoire d'utiliser le suffixe « ASC » sachant > que les résultats sont toujours classés par ordre ascendant par défaut. > Toutefois, c'est plus pratique pour mieux s'y retrouver, surtout si on a > oublié l'ordre par défaut.

Exemple

Pour l'ensemble de nos exemples, nous allons prendre une base « utilisateur » de test, qui contient les données suivantes :

Pour récupérer la liste de ces utilisateurs par ordre alphabétique du nom de famille, il est possible d'utiliser la requête suivante :

Résultat :

En utilisant deux méthodes de tri, il est possible de retourner les utilisateurs par ordre alphabétique ET pour ceux qui ont le même nom de famille, les trier par ordre décroissant de date d'inscription. La requête serait alors la suivante :

Résultat :

SQL LIMIT

La clause LIMIT est à utiliser dans une requête SQL pour spécifier le nombre maximum de résultats que l'on souhaite obtenir. Cette clause est souvent associée à un OFFSET, c'est-à-dire effectuer un décalage sur le jeu de résultat. Ces 2 clauses permettent par exemple d'effectuer des systèmes de pagination (exemple : récupérer les 10 articles de la page 4).

*

ATTENTION : selon le système de gestion de base de données, la syntaxe ne sera pas pareille. Ce tutoriel va donc présenter la syntaxe pour MySQL et pour PostgreSQL.

La syntaxe commune aux principaux systèmes de gestion de bases de données est :

Cette requête permet de récupérer seulement les 10 premiers résultats d'une table. Bien entendu, si la table contient moins de 10 résultats, alors la requête retournera toutes les lignes.

**Bon à savoir : ** la bonne pratique lorsque l'on utilise LIMIT consiste à utiliser également la clause ORDER BY pour s'assurer que quoi qu'il en soit ce sont toujours les bonnes données qui sont présentées. En effet, si le système de tri est non spécifié, alors il est en principe inconnu et les résultats peuvent être imprévisible.

Limit et Offset avec PostgreSQL

L'offset est une méthode simple de décaler les lignes à obtenir. La syntaxe pour utiliser une limite et un offset est la suivante :

Cette requête permet de récupérer les résultats 6 à 15 (car l'OFFSET commence toujours à 0). A titre d'exemple, pour récupérer les résultats 16 à 25 il faudrait donc utiliser: LIMIT 10 OFFSET 15

A noter : Utiliser OFFSET 0 revient au même que d'omettre l'OFFSET.

Limit et Offset avec MySQL

La syntaxe avec MySQL est légèrement différente :

Cette requête retourne les enregistrements 6 à 15 d'une table. Le premier nombre est l'OFFSET tandis que le suivant est la limite.

**Bon à savoir : ** pour une bonne compatibilité, MySQL accepte également la syntaxe LIMIT nombre OFFSET nombre. En conséquent, dans la conception d'une application utilisant MySQL il est préférable d'utiliser cette syntaxe car c'est potentiellement plus facile de migrer vers un autre système de gestion de base de données sans avoir à ré-écrire toutes les requêtes.

Performance

Ce dernier chapitre est destiné à un public averti. Il n'est pas nécessaire de le comprendre entièrement, mais simplement d'avoir compris les grandes lignes.

Certains développeurs pensent à tort que l'utilisation de LIMIT permet de réduire le temps d'exécution d'une requête. Or, le temps d'exécution est sensiblement le même car la requête va permettre de récupérer toutes les lignes (donc temps d'exécution identique) PUIS seulement les résultats définis par LIMIT et OFFSET seront retournés. Au mieux, utiliser LIMIT permet de réduire le temps d'affichage car il y a moins de lignes à afficher.

#

SQL CASE

Dans le langage SQL, la commande « CASE … WHEN … » permet d'utiliser des conditions de type « si / sinon » (cf. if / else) similaire à un langage de programmation pour retourner un résultat disponible entre plusieurs possibilités. Le CASE peut être utilisé dans n'importe quelle instruction ou clause, telle que SELECT, UPDATE, DELETE, WHERE, ORDER BY ou HAVING.

L'utilisation du CASE est possible de 2 manières différentes :

• Comparer une colonne à un set de résultat possible
• élaborer une série de conditions booléennes pour déterminer un résultat

Comparer une colonne à un set de résultat

Voici la syntaxe nécessaire pour comparer une colonne à un set d'enregistrement:

Dans cet exemple les valeurs contenues dans la colonne « a » sont comparées à 1, 2 ou 3. Si la condition est vraie, alors la valeur située après le THEN sera retournée.

> A noter : la condition ELSE est facultative et sert de ramasse-miettes. > Si les conditions précédentes ne sont pas respectées alors ce sera la valeur > du ELSE qui sera retournée par défaut.

élaborer une série de conditions booléennes pour déterminer un résultat

Il est possible d'établir des conditions plus complexes pour récupérer un résultat ou un autre. Cela s'effectue en utilisant la syntaxe suivante :

Dans cet exemple les colonnes « a », « b » et « c » peuvent contenir des valeurs numériques. Lorsqu'elles sont respectées, les conditions booléennes permettent de rentrer dans l'une ou l'autre des conditions.

Il est possible de reproduire le premier exemple présenté sur cette page en utilisant la syntaxe suivante :

Exemples

Pour présenter le CASE dans le langage SQL il est possible d'imaginer une base de données utilisées par un site de vente en ligne. Dans cette base il y a une table contenant les achats, cette table contient le nom des produits, le prix unitaire, la quantité achetée et une colonne consacrée à une marge fictive sur certains produits.

Table « achat » :

Afficher un message selon une condition

Il est possible d'effectuer une requête qui va afficher un message personnalisé en fonction de la valeur de la marge. Le message sera différent selon que la marge sera égale à 1, supérieure à 1 ou inférieure à 1. La requête peut se présenter de la façon suivante :

`

Résultat :

Ce résultat montre qu'il est possible d'afficher facilement des messages personnalisés selon des conditions simples.

Afficher un prix unitaire différent selon une condition

Avec un CASE il est aussi possible d'utiliser des requêtes plus élaborées. Imaginons maintenant que nous souhaitions multiplier le prix unitaire par 2 si la marge est supérieure à 1, la diviser par 2 si la marge est inférieure à 1 et laisser le prix unitaire tel quel si la marge est égale à 1. C'est possible grâce à la requête SQL :

``

Résultat :

Comparer un champ à une valeur donnée

Imaginons maintenant que l'application propose des réductions selon le nombre de produits achetés :

• 1 produit acheté permet d'obtenir une réduction de -5% pour le prochain achat
• 2 produits achetés permettent d'obtenir une réduction de -6% pour le prochain achat
• 3 produits achetés permettent d'obtenir une réduction de -8% pour le prochain achat
• Pour plus de produits achetés il y a un réduction de -10% pour le prochain achat

Pour effectuer une telle procédure, il est possible de comparer la colonne « quantite » aux différentes valeurs spécifiées et d'afficher un message personnalisé en fonction du résultat. Cela peut être réalisé avec cette requête SQL :

Résultat :

Astuce : la condition ELSE peut parfois être utilisée pour gérer les erreurs.

UPDATE avec CASE

Comme cela a été expliqué au début, il est aussi possible d'utiliser le CASE à la suite de la commande SET d'un UPDATE pour mettre à jour une colonne avec une donnée spécifique selon une règle. Imaginons par exemple que l'on souhaite offrir un produit pour tous les achats qui ont une surcharge inférieure à 1 et que l'on souhaite retirer un produit pour tous les achats avec une surcharge supérieure à 1. Il est possible d'utiliser la requête SQL suivante :

SQL UNION

La commande UNION de SQL permet de mettre bout-à-bout les résultats de plusieurs requêtes utilisant elles-mêmes la commande SELECT. C'est donc une commande qui permet de concaténer les résultats de 2 requêtes ou plus. Pour l'utiliser il est nécessaire que chacune des requêtes à concaténer retourne le même nombre de colonnes, avec les mêmes types de données et dans le même ordre.

A savoir : par défaut, les enregistrements exactement identiques ne seront pas répétés dans les résultats. Pour effectuer une union dans laquelle même les lignes dupliquées sont affichées il faut plutôt utiliser la commande UNION ALL.

La syntaxe pour unir les résultats de 2 tableaux sans afficher les doublons est la suivante :

Schéma explicatif

L'union de 2 ensembles A et B est un concept qui consiste à obtenir tous les éléments qui correspondent à la fois à l'ensemble A ou à l'ensemble B. Cela se résume très simplement par un petit schéma où la zone en bleu correspond à la zone que l'on souhaite obtenir (dans notre cas : tous les éléments).

Union de 2 ensembles

Exemple

Imaginons une entreprise qui possède plusieurs magasins et dans chacun de ces magasins il y a une table qui liste les clients. La table du magasin n°1 s'appelle « magasin1_client » et contient les données suivantes :

La table du magasin n°2 s'appelle « magasin2_client » et contient ces données :

Sachant que certains clients sont présents dans les 2 tables, pour éviter de retourner plusieurs fois les mêmes enregistrements, il convient d'utiliser la requête UNION. La requête SQL est alors la suivante :

Résultat :

Le résultat de cette requête montre bien que les enregistrements des 2 requêtes sont mis bout-à-bout mais sans inclure plusieurs fois les mêmes lignes.

SQL UNION ALL

La commande UNION ALL de SQL est très similaire à la commande UNION. Elle permet de concaténer les enregistrements de plusieurs requêtes, à la seule différence que cette commande permet d'inclure tous les enregistrements, même les doublons. Ainsi, si un même enregistrement est présent normalement dans les résultats des 2 requêtes concaténées, alors l'union des 2 requêtes avec UNION ALL retournera 2 fois ce même résultat.

A savoir : tout comme la commande UNION, il convient que les 2 requêtes retournent exactement le même nombre de colonnes, avec les mêmes types de données et dans le même ordre.

La syntaxe de la requête SQL pour unir les résultats des 2 tables est :

Exemple

Imaginons une entreprise qui possède des bases de données dans chacun de ses magasins. Sur ces bases de données il y a une table de la liste des clients avec quelques informations et le total des achats dans l'entreprise.

La table « magasin1_client » correspond au premier magasin :

La table « magasin2_client » correspond au deuxième magasin :

Pour concaténer les tous les enregistrements de ces tables, il est possible d'effectuer une seule requête utilisant la commande UNION ALL, comme l'exemple ci-dessous :

Résultat :

Le résultat de cette requête montre qu'il y a autant d'enregistrements que dans les 2 tables réunies. A savoir, il y a quelques clients qui étaient présents dans les 2 tables d'origines, par conséquent ils sont présents 2 fois dans le résultat de cette requête SQL.

SQL INTERSECT

La commande SQL INTERSECT permet d'obtenir l'intersection des résultats de deux requêtes. Cette commande permet donc de récupérer les enregistrements communs à 2 requêtes. Cela peut s'avérer utile lorsqu'il faut trouver s'il y a des données similaires sur 2 tables distinctes.

> A savoir : pour l'utiliser convenablement il faut que les deux requêtes > retournent le même nombre de colonnes, avec les mêmes types et dans le même ordre.

La syntaxe à adopter pour utiliser cette commande est la suivante :

Dans cet exemple, il faut que les 2 tables soient similaires (mêmes colonnes, mêmes types et même ordre). Le résultat correspondra aux enregistrements qui existent dans table1 et dans table2.

Schéma explicatif

L'intersection de 2 ensembles A et B correspond aux éléments qui sont présent dans A et dans B, et seulement ceux-là. Cela peut être représenté par un schéma explicatif simple ou l'intersection de A et B correspond à la zone en bleu.

? Intersection de 2 ensembles

Exemple

Prenons l'exemple de 2 magasins qui appartiennent au même groupe. Chaque magasin possède sa table de clients.

La table du magasin n°1 est « magasin1_client » :

La table du magasin n°2 est « magasin2_client » :

Pour obtenir la liste des clients qui sont présents de façon identiques dans ces 2 tables, il est possible d'utiliser la commande INTERSECT de la façon suivante :

Résultat :

Le résultat présente 2 enregistrements, il s'agit des clients qui sont à la fois dans la table « magasin1_client » et dans la table « magasin2_client ». Sur certains systèmes une telle requête permet de déceler des erreurs et d'enregistrer seulement à un seul endroit la même information.

SQL EXCEPT / MINUS

Dans le langage SQL la commande EXCEPT s'utilise entre 2 instructions pour récupérer les enregistrements de la première instruction sans inclure les résultats de la seconde requête. Si un même enregistrement devait être présent dans les résultats des 2 syntaxes, ils ne seront pas présent dans le résultat final.

> **A savoir : ** cette commande s'appelle différemment selon les Systèmes de > Gestion de Base de Données (SGBD) :

• EXCEPT : PostgreSQL et SQLServer
• MINUS : MySQL et Oracle

Dès lors, il faut remplacer tout le reste de ce cours par MINUS pour les SGBD correspondants.

La syntaxe d'une requête SQL est toute simple :

Cette requête permet de lister les résultats du table 1 sans inclure les enregistrements de la table 1 qui sont aussi dans la table 2.

> **Attention : ** les colonnes de la première requête doivent être similaires > entre la première et la deuxième requête (même nombre, même type et même > ordre).

Schéma explicatif

Cette commande permet de récupérer les éléments de l'ensemble A sans prendre en compte les éléments de A qui sont aussi présent dans l'ensemble B. Dans le schéma ci-dessous seule la zone bleu sera retournée grâce à la commande EXCEPT (ou MINUS).

? Sélection d'un ensemble avec exception

Exemple

Imaginons un système informatique d'une entreprise. Ce système contient 2 tables contenant des listes de clients :

• Une table « clients_inscrits » qui contient les prénoms, noms et date d'inscription de clients

• Une table « clients_refus_email » qui contient les informations des clients qui ne souhaitent pas être contactés par email

Cet exemple aura pour objectif de sélectionner les utilisateurs pour envoyer un email d'information. Les utilisateurs de la deuxième table ne devront pas apparaître dans les résultats.

Table « clients_inscrits » :

Table « clients_refus_email » :

Pour pouvoir sélectionner uniquement le prénom et le nom des utilisateurs qui acceptent de recevoir des emails informatifs. La requête SQL à utiliser est la suivante :

Résultats :

Ce tableau de résultats montre bien les utilisateurs qui sont dans inscrits et qui ne sont pas présents dans le deuxième tableau. Par ailleurs, les résultats du deuxième tableau ne sont pas présents sur ce résultat final.

SQL INSERT INTO

L'insertion de données dans une table s'effectue à l'aide de la commande INSERT INTO. Cette commande permet au choix d'inclure une seule ligne à la base existante ou plusieurs lignes d'un coup.

Insertion d'une ligne à la fois

Pour insérer des données dans une base, il y a 2 syntaxes principales :

• Insérer une ligne en indiquant les informations pour chaque colonne existante (en respectant l'ordre)
• Insérer une ligne en spécifiant les colonnes que vous souhaiter compléter. Il est possible d'insérer une ligne en renseignant seulement une partie des colonnes

Insérer une ligne en spécifiant toutes les colonnes

La syntaxe pour remplir une ligne avec cette méthode est la suivante :

Cette syntaxe possède les avantages et inconvénients suivants :

• Obligation de remplir toutes les données, en respectant l'ordre des colonnes
• Il n'y a pas le nom de colonne, donc les fautes de frappe sont limitées. Par ailleurs, les colonnes peuvent être renommées sans avoir à changer la requête
• L'ordre des colonnes doit rester identique sinon certaines valeurs prennent le risque d'être complétées dans la mauvaise colonne.

Insérer une ligne en spécifiant seulement les colonnes souhaitées

Cette deuxième solution est très similaire, excepté qu'il faut indiquer le nom des colonnes avant « VALUES ». La syntaxe est la suivante :

> A noter : il est possible de ne pas renseigner toutes les colonnes. De > plus, l'ordre des colonnes n'est pas important.

Insertion de plusieurs lignes à la fois

Il est possible d'ajouter plusieurs lignes à un tableau avec une seule requête. Pour ce faire, il convient d'utiliser la syntaxe suivante :

**A noter : ** lorsque le champ à remplir est de type VARCHAR ou TEXT il faut indiquer le texte entre guillemet simple. En revanche, lorsque la colonne est un numérique tel que INT ou BIGINT il n'y a pas besoin d'utiliser de guillemet, il suffit juste d'indiquer le nombre.

Un tel exemple sur une table vide va créer le tableau suivant :

Insertion avec les index

SET IDENTITY_INSERT nomTable ON / OFF

Active, désactive l'insertion automatique de colonne auto incrémentées (identity) dans la table spécifiée.

Exemple : insertion de lignes dont la clef est spécifiée dans une table données pourvue d'un auto incrément :

SQL ON DUPLICATE KEY UPDATE

L'instruction ON DUPLICATE KEY UPDATE est une fonctionnalité de MySQL qui permet de mettre à jour des données lorsqu'un enregistrement existe déjà dans une table. Cela permet de n'avoir qu'une seule requête SQL pour effectuer selon la convenance un INSERT ou un UPDATE. Cette commande s'effectue au sein de la requête INSERT INTO avec la syntaxe suivante :

A noter : cette requête se traduit comme suit :

1. insérer les données a, b et c avec les données respectives de 1, 20 et 68
2. Si la clé primaire existe déjà pour ces valeurs alors seulement faire une mise à jour de a = a+1

Exemple avec la commande WHERE

Grâce à la commande « ON DUPLICATE KEY » Il est possible d'enregistrer la date à laquelle la données est insérée pour la première fois et la date de dernière mise à jour, comme le montre la commande ci-dessous :

A noter : cette requête se traduit comme suit :

1. insérer les données a, b, c et date_insert, avec les données respectives de 1, 20, 1 ainsi que la date et l'heure actuelle
2. Si la clé primaire existe déjà pour ces valeurs alors mettre à jour la date et l'heure du champ « date_update »
3. Effectuer la mise à jour uniquement sur les champs où c = 1

Exemple

Imaginons une application qui laisse les utilisateurs voter pour les produits qu'ils préfèrent. Le système de vote est très simple et est basé sur des +1. La table des votes contient le nombre de votes par produit avec la date du premier vote et la date du dernier vote.

Table vote :

Pour n'utiliser qu'une seule ligne qui permet d'ajouter des votes dans cette table, sans se préoccuper de savoir s'il faut faire un INSERT ou un UPDATE, il est possible d'utiliser la requête SQL suivante :

Dans cette requête la date et l'heure sont générées automatiquement avec la fonction NOW().

Résultat après la première exécution de la requête :

Ce résultat montre bien l'ajout d'une ligne en fin de table, donc la requête a été utilisé sous la forme d'un INSERT. Après une deuxième exécution de cette même requête le lendemain, les données seront celles-ci:

Ces résultats montrent bien qu'il y a eu un vote supplémentaire et que la date du dernier vote a été mis à jour.

Insérer une ligne ou ne rien faire

Dans certains cas il est intéressant d'utiliser un INSERT mais de ne rien faire si la commande a déjà été insérée précédemment. Malheureusement, si la clé primaire existe déjà la requête retournera une erreur. Et s'il n'y a rien à mettre à jour, la commande ON DUPLICATE KEY UPDATE (ODKU) ne semble pas convenir. Toutefois il y a une astuce qui consiste à utiliser une requête de ce type :

Cette requête insère les données et ne produit aucune erreur si l'enregistrement existait déjà dans la table.

> **A savoir : ** théoriquement il aurait été possible d'utiliser INSERT IGNORE > mais malheureusement cela empêche de retourner des erreurs telles que des > erreurs de conversions de données.

Compatibilité

Pour le moment cette fonctionnalité n'est possible qu'avec MySQL depuis la version 4.1 (date de 2003). Les autres Systèmes de Gestion de Bases de Données (SGBD) n'intègrent pas cette fonctionnalité. Pour simuler cette fonctionnalité il y a quelques alternatives :

• **PostgreSQL : ** il y a une astuce en utilisant une fonction. L'astuce est expliquée dans la documentation officielle : fonction INSERT/UPDATE.

• **Oracle : ** il est possible d'utiliser la commande MERGE pour effectuer la même chose.

• SQL Server : il est possible d'utiliser une procédure.

SQL UPDATE

La commande UPDATE permet d'effectuer des modifications sur des lignes existantes. Très souvent cette commande est utilisée avec WHERE pour spécifier sur quelles lignes doivent porter la ou les modifications.

La syntaxe basique d'une requête utilisant UPDATE est la suivante :

Cette syntaxe permet d'attribuer une nouvelle valeur à la colonne nom_colonne_1 pour les lignes qui respectent la condition stipulée avec WHERE. Il est aussi possible d'attribuer la même valeur à la colonne nom_colonne_1 pour toutes les lignes d'une table si la condition WHERE n'était pas utilisée.

A noter, pour spécifier en une seule fois plusieurs modifications, il faut séparer les attributions de valeurs par des virgules. Ainsi la syntaxe deviendrait la suivante :

Exemple

Imaginons une table « client » qui présente les coordonnées de clients.

Pour modifier l'adresse du client Pierre, il est possible d'utiliser la requête suivante :

Résultat :

SQL DELETE

La commande DELETE en SQL permet de supprimer des lignes dans une table. En utilisant cette commande associée à WHERE il est possible de sélectionner les lignes concernées qui seront supprimées.

**Attention : ** Avant d'essayer de supprimer des lignes, il est recommandé d'effectuer une sauvegarde de la base de données, ou tout du moins de la table concernée par la suppression. Ainsi, s'il y a une mauvaise manipulation il est toujours possible de restaurer les données.

La syntaxe pour supprimer des lignes est la suivante :

Attention : s'il n'y a pas de condition WHERE alors toutes les lignes seront supprimées et la table sera alors vide. La commande DELETE peut générer l'écriture de beaucoup de log. Si le but est de vider la table préférez la commande TRUNCATE qui ne générera pas de log.

Exemple

Imaginons une table « utilisateur » qui contient des informations sur les utilisateurs d'une application.

Si l'on souhaite supprimer les utilisateurs qui se sont inscrit avant le « 10/04/2012 », il va falloir effectuer la requête suivante :

La requête permettra alors de supprimer les utilisateurs « Daniel » et « Constantin ». La table contiendra alors les données suivantes :

Il ne faut pas oublier qu'il est possible d'utiliser d'autres conditions pour sélectionner les lignes à supprimer.

SQL MERGE

Dans le langage SQL, la commande MERGE permet d'insérer ou de mettre à jour des données dans une table. Cette commande permet d'éviter d'effectuer plusieurs requêtes pour savoir si une donnée est déjà dans la base de données et ainsi adapter l'utilisation d'une requête pour ajouter ou une autre pour modifier la donnée existante. Cette commande peut aussi s'appeler « upsert ».

Attention : bien que l'instruction ait été ajoutée dans le standard SQL:2003 les différentes SGBD n'utilisent pas toutes les mêmes méthodes pour effectuer un upsert.

La syntaxe standard pour effectuer un merge consiste à utiliser une requête SQL semblable à celle ci-dessous :

Voici les explications détaillées de cette requête :

• MERGE INTO permet de sélectionner la table à modifier
• USING et ON permet de lister les données sources et la condition de correspondance
• WHEN MATCHED permet de définir la condition de mise à jour lorsque la condition est vérifiée
• WHEN NOT MATCHED permet de définir la condition d'insertion lorsque la condition n'est pas vérifiée

Compatibilité

Les systèmes de gestion de bases de données peuvent implémenter cette fonctionnalité soit de façon standard, en utilisant une commande synonyme ou en utilisant une syntaxe non standard.

• Syntaxe standard : SQL Server, Oracle, DB2, Teradata et EXASOL
• Utilisation du terme UPSERT : Microsoft SQL Azure et MongoDB
• Utilisation non standard : MySQL, SQLite, Firebird, IBM DB2 et Microsoft SQL

1.4. SQL Sous-requête

Dans le langage SQL une sous-requête (aussi appelé « requête imbriquée » ou « requête en cascade ») consiste à exécuter une requête à l'intérieur d'une autre requête. Une requête imbriquée est souvent utilisée au sein d'une clause WHERE ou de HAVING pour remplacer une ou plusieurs constantes.

Il y a plusieurs façons d'utiliser les sous-requêtes. De cette façon il y a plusieurs syntaxes envisageables pour utiliser des requêtes dans des requêtes.

Requête imbriquée qui retourne un seul résultat

L'exemple ci-dessous est une exemple typique d'une sous-requête qui retourne un seul résultat à la requête principale.

Cet exemple montre une requête interne (celle sur « table2? ) qui renvoie une seule valeur. La requête externe quant à elle, va chercher les résultats de « table » et filtre les résultats à partir de la valeur retournée par la requête interne.

> A noter : il est possible d'utiliser n'importe quel opérateur d'égalité > tel que =, >, <, >=, <= ou <>.

Requête imbriquée qui retourne une colonne

Une requête imbriquée peut également retourner une colonne entière. Dès lors, la requête externe peut utiliser la commande IN pour filtrer les lignes qui possèdent une des valeurs retournées par la requête interne. L'exemple ci-dessous met en évidence un tel cas de figure :

Exemple

Imaginons un site web qui permet de poser des questions et d'y répondre. Un tel site possède une base de données avec une table pour les questions et une autre pour les réponses.

Table « question » :

Table « reponse » :

Requête imbriquée qui retourne un seul résultat

Avec une telle application, il est peut-être utile de connaître la question liée à la dernière réponse ajoutée sur l'application. Cela peut être effectué via la requête SQL suivante :

Une telle requête va retourner la ligne suivante :

Ce résultat démontre que la question liée à la dernière réponse sur le forum est bien trouvée à partir de ce résultat.

Requête imbriquée qui retourne une colonne

Imaginons maintenant que l'on souhaite obtenir les questions liées à toutes les réponses comprises entre 2 dates. Ces questions peuvent être récupérées par la requête SQL suivante :

Résultats :

Une telle requête permet donc de récupérer les questions qui ont eu des réponses entre 2 dates. C'est pratique dans notre cas pour éviter d'obtenir des questions qui n'ont pas eu de réponses du tout ou pas de nouvelles réponses depuis longtemps.

SQL EXISTS

**A noter : ** cette commande n'est pas à confondre avec la clause IN. La commande EXISTS vérifie si la sous-requête retourne un résultat ou non, tandis que IN vérifie la concordance d'une à plusieurs données.

L'utilisation basique de la commande EXISTS consiste à vérifier si une sous-requête retourne un résultat ou non, en utilisant EXISTS dans la clause conditionnelle. La requête externe s'exécutera uniquement si la requête interne retourne au moins un résultat.

Dans l'exemple ci-dessus, s'il y a au moins une ligne dans table2 dont nom_colonne3 contient la valeur 10, alors la sous-requête retournera au moins un résultat. Dès lors, la condition sera vérifiée et la requête principale retournera les résultats de la colonne nom_colonne1 de table1.

Exemple

Dans le but de montrer un exemple concret d'application, imaginons un système composé d'une table qui contient des commandes et d'une table contenant des produits.

Table commande :

Table produit :

Il est possible d'effectuer une requête SQL qui affiche les commandes pour lesquelles il y a effectivement un produit. Cette requête peut être interprétée de la façon suivante :

Résultat :

Le résultat démontre bien que seules les commandes n°1 et n°2 ont un produit qui se trouve dans la table produit (cf. la condition c_produit_id = p_id). Cette requête est intéressante sachant qu'elle n'influence pas le résultat de la requête principale, contrairement à l'utilisation d'une jointure qui va concaténer les colonnes des 2 tables jointes.

SQL ALL

Dans le langage SQL, la commande ALL permet de comparer une valeur dans l'ensemble de valeurs d'une sous-requête. En d'autres mots, cette commande permet de s'assurer qu'une condition est « égale », « différente », « supérieure », « inférieure », « supérieure ou égale » ou « inférieure ou égale» pour tous les résultats retourné par une sous-requête.

Cette commande s'utilise dans une clause conditionnelle entre l'opérateur de condition et la sous-requête. L'exemple ci-dessous montre un exemple basique :

A savoir : les opérateurs conditionnels peuvent être les suivants : =, <, >, <>, !=, <=, >=, !> ou !<.

Exemple

Imaginons une requête similaire à la syntaxe de base présentée précédemment :

Avec cette requête, si nous supposons que dans table1 il y a un résultat avec la valeur 10, voici les différents résultats de la conditions selon le contenu de table2 :

• La condition est vraie (cf. TRUE) si table2 contient {-5,0,+5} car toutes les valeurs sont inférieures à 10
• La condition est fausse (cf. FALSE) si table2 contient {12,6,NULL,-100} car au moins une valeur est inférieure à 10
• La condition est non connue (cf. UNKNOW) si table2 est vide

SQL ANY / SOME

Dans le langage SQL, la commande ANY (ou SOME) permet de comparer une valeur avec le résultat d'une sous-requête. Il est ainsi possible de vérifier si une valeur est « égale », « différente », « supérieure », « supérieure ou égale », « inférieure » ou « inférieure ou égale » pour au moins une des valeurs de la sous-requête.

> A noter : le mot-clé SOME est un alias de ANY, l'un ou l'autre des termes > peut être utilisé.

Cette commande s'utilise dans une clause conditionnelle juste après un opérateur conditionnel et juste avant une sous-requête. L'exemple ci-dessous démontre une utilisation basique de ANY dans une requête SQL :

Cette requête peut se traduire de la façon suivante : sélectionner toutes les colonnes de table1, où la condition est supérieure à n'importe quel résultat de la sous-requête.

Exemple

En se basant sur l'exemple relativement simple présenté ci-dessus, il est possible d'effectuer une requête concrète qui utilise la commande ANY :

Supposons que la table1 possède un seul résultat dans lequel colonne1 est égal à 10.

• La condition est vraie (cf. TRUE) si table2 contient {21,14,7} car il y a au moins une valeur inférieure à 10
• La condition est fausse (cf. FALSE) si table2 contient {20,10} car aucune valeur est strictement inférieure à 10
• La condition est non connue (cf. UNKNOW) si table2 est vide

Astuce : La commande IN est équivalente à l'opérateur = suivi de ANY.

1.5. Jointure SQL

Les jointures en SQL permettent d'associer plusieurs tables dans une même requête. Cela permet d'exploiter la puissance des bases de données relationnelles pour obtenir des résultats qui combinent les données de plusieurs tables de manière efficace.

Exemple

En général, les jointures consistent à associer des lignes de 2 tables en associant l'égalité des valeurs d'une colonne d'une première table par rapport à la valeur d'une colonne d'une seconde table. Imaginons qu'une base de données possède une table « utilisateur » et une autre table « adresse » qui contient les adresses de ces utilisateurs. Avec une jointure, il est possible d'obtenir les données de l'utilisateur et de son adresse en une seule requête.

On peut aussi imaginer qu'un site web possède une table pour les articles (titre, contenu, date de publication …) et une autre pour les rédacteurs (nom, date d'inscription, date de naissance …). Avec une jointure il est possible d'effectuer une seule recherche pour afficher un article et le nom du rédacteur. Cela évite d'avoir à afficher le nom du rédacteur dans la table « article ».

Il y a d'autres cas de jointure, incluant des jointures sur la même table ou des jointures d'inégalité. Ces cas étant assez particuliers et pas si simple à comprendre, ils ne seront pas élaborés dans ce document.

Types de jointures

Il y a plusieurs méthodes pour associer 2 tables ensemble. Voici la liste des différentes techniques qui sont utilisées :

• INNER JOIN : jointure interne pour retourner les enregistrements quand la condition est vraie dans les 2 tables. C'est l'une des jointures les plus communes.

• CROSS JOIN : jointure croisée permettant de faire le produit cartésien de 2 tables. En d'autres mots, permet de joindre chaque ligne d'une table avec chaque ligne d'une seconde table. Attention, le nombre de lignes résultant est en général très élevé.

• **LEFT JOIN (ou LEFT OUTER JOIN) : ** jointure externe pour retourner tous les enregistrements de la table de gauche (LEFT = gauche) même si la condition n'est pas vérifiée dans l'autre table.

• **RIGHT JOIN (ou RIGHT OUTER JOIN) : ** jointure externe pour retourner tous les enregistrements de la table de droite (RIGHT = droite) même si la condition n'est pas vérifiée dans l'autre table.

• **FULL JOIN (ou FULL OUTER JOIN) : ** jointure externe pour retourner les résultats quand la condition est vraie dans au moins une des 2 tables.

• **SELF JOIN : ** permet d'effectuer une jointure d'une table avec elle-même comme si c'était une autre table.

• **NATURAL JOIN : ** jointure naturelle entre 2 tables s'il y a au moins une colonne qui porte le même nom entre les 2 tables SQL

• UNION JOIN : jointure d'union

SQL INNER JOIN

Dans le langage SQL la commande INNER JOIN, aussi appelée EQUIJOIN, est un type de jointure très communes pour lier plusieurs tables entre-elles. Cette commande retourne les enregistrements lorsqu'il y a au moins une ligne dans chaque colonne qui correspond à la condition.

Pour utiliser ce type de jointure il convient d'utiliser une requête SQL avec cette syntaxe :

La syntaxe ci-dessus stipule qu'il faut sélectionner les enregistrements des tables table1 et table2 lorsque les données de la colonne « id » de table1 sont égales aux données de la colonne fk_id de table2.

La jointure SQL peux aussi être écrite de la façon suivante :

La syntaxe avec la condition WHERE est une manière alternative de faire la jointure mais qui possède l'inconvénient d'être moins facile à lire s'il y a déjà plusieurs conditions dans le WHERE.

Exemple

Imaginons une application qui possède une table utilisateur ainsi qu'une table commande qui contienne toutes les commandes effectuées par les utilisateurs.

Table utilisateur :

Table commande :

Pour afficher toutes les commandes associées aux utilisateurs, il est possible d'utiliser la requête suivante :

Résultats :

Le résultat de la requête montre parfaitement la jointure entre les 2 tables. Les utilisateurs 3 et 4 ne sont pas affichés puisqu'il n'y a pas de commande associée à ces utilisateurs.

*

Attention : il est important de noter que si un utilisateur à été supprimé, alors on ne verra pas ses commandes dans la liste puisque INNER JOIN retourne uniquement les résultats où la condition est vraie dans les 2 tables.

SQL CROSS JOIN

Dans le langage SQL, la commande CROSS JOIN est un type de jointure sur 2 tables SQL qui permet de retourner le produit cartésien. Autrement dit, cela permet de retourner chaque ligne d'une table avec chaque ligne d'une autre table. Ainsi effectuer le produit cartésien d'une table A qui contient 30 résultats avec une table B de 40 résultats va produire 1200 résultats (30 x 40 = 1200). En général la commande CROSS JOIN est combinée avec la commande WHERE pour filtrer les résultats qui respectent certaines conditions.

Attention, le nombre de résultat peut facilement être très élevé. S'il est effectué sur des tables avec beaucoup d'enregistrements, cela peut ralentir sensiblement le serveur.

Pour effectuer un jointure avec CROSS JOIN, il convient d'effectuer une requête SQL respectant la syntaxe suivante :

Méthode alternative pour retourner les mêmes résultats :

L'une ou l'autre de ces syntaxes permettent d'associer tous les résultats de table1 avec chacun des résultats de table2.

Exemple

Imaginons une application de recettes de cuisines qui contient 2 tables d'ingrédients, la table legume et la table fruit.

Table legume :

Table fruit :

Pour une raison quelconque l'application doit associer tous les légumes avec tous les fruits. Toutes les combinaisons doivent être affichées. Pour cela il convient d'effectuer l'une ou l'autre des requêtes suivantes :

``

ou :

Résultats :

Le résultat montre bien que chaque légume est associé à chaque fruit. Avec 3 fruits et 3 légumes, il y a donc 9 lignes de résultat (3x3 = 9).

SQL LEFT JOIN

Dans le langage SQL, la commande LEFT JOIN (aussi appelée LEFT OUTER JOIN) est un type de jointure entre 2 tables. Cela permet de lister tous les résultats de la table de gauche (left = gauche) même s'il n'y a pas de correspondance dans la deuxième table.

Pour lister les enregistrements de table1, même s'il n'y a pas de correspondance avec table2, il convient d'effectuer une requête SQL utilisant la syntaxe suivante.

La requête peut aussi s'écrire de la façon suivante :

Cette requête est particulièrement intéressante pour récupérer les informations de table1 tout en récupérant les données associées, même s'il n'y a pas de correspondance avec table2. A savoir, s'il n'y a pas de correspondance, les colonnes de table2 vaudront toutes NULL.

Exemple

Imaginons une application contenant des utilisateurs et des commandes pour chacun de ces utilisateurs. La base de données de cette application contient une table pour les utilisateurs et sauvegarde leurs achats dans une seconde table. Les 2 tables sont reliées grâce à la colonne utilisateur_id de la table des commandes. Cela permet d'associer une commande à un utilisateur.

Table utilisateur :

Table commande :

Pour lister tous les utilisateurs avec leurs commandes et afficher également les utilisateurs qui n'ont pas effectué d'achat, il est possible d'utiliser la requête suivante :

``

Résultats :

Les dernières lignes montrent des utilisateurs qui n'ont effectué aucune commande. La ligne retourne la valeur NULL pour les colonnes concernant les achats qu'ils n'ont pas effectués.

Filtrer sur la valeur NULL

Attention, la valeur NULL n'est pas une chaîne de caractère. Pour filtrer sur ces caractères il faut utiliser la commande IS NULL. Par exemple, pour lister les utilisateurs qui n'ont pas effectué d'achat il est possible d'utiliser la requête suivante.

Résultats :

SQL RIGHT JOIN

En SQL, la commande RIGHT JOIN (ou RIGHT OUTER JOIN) est un type de jointure entre 2 tables qui permet de retourner tous les enregistrements de la table de droite (right = droite) même s'il n'y a pas de correspondance avec la table de gauche. S'il y a un enregistrement de la table de droite qui ne trouve pas de correspondance dans la table de gauche, alors les colonnes de la table de gauche auront NULL pour valeur.

L'utilisation de cette commande SQL s'effectue de la façon suivante :

La syntaxe de cette requête SQL peut aussi s'écrire de la façon suivante :

Cette syntaxe stipule qu'il faut lister toutes les lignes du tableau table2 (tableau de droite) et afficher les données associées du tableau table1 s'il y a une correspondance entre ID de table1 et FK_ID de table2. S'il n'y a pas de correspondance, l'enregistrement de table2 sera affiché et les colonnes de table1 vaudront toutes NULL.

Exemple

Prenons l'exemple d'une base de données qui contient des utilisateurs et un historique d'achat de ces utilisateurs. Ces deux tables sont reliées entre elles grâce à la colonne utilisateur_id de la table des commandes. Cela permet de savoir à quel utilisateur est associé un achat.

Table utilisateur :

Table commande :

Pour afficher toutes les commandes avec le nom de l'utilisateur correspondant il est normalement habituel d'utiliser INNER JOIN en SQL. Malheureusement, si l'utilisateur a été supprimé de la table, alors cela ne retourne pas l'achat. L'utilisation de RIGHT JOIN permet de retourner tous les achats et d'afficher le nom de l'utilisateur s'il existe. Pour cela il convient d'utiliser cette requête :

?```sql
SELECT id, prenom, nom, utilisateur_id, date_achat, num_facture
FROM utilisateur
RIGHT JOIN commande ON utilisateur.id = commande.utilisateur_id

Résultats :

Ce résultat montre que la facture A00107 est liée à l'utilisateur numéro 5. Or, cet utilisateur n'existe pas ou n'existe plus. Grâce à RIGHT JOIN, l'achat est tout de même affiché mais les informations liées à l'utilisateur sont remplacées par NULL.

SQL FULL JOIN

Dans le langage SQL, la commande FULL JOIN (ou FULL OUTER JOIN) permet de faire une jointure entre 2 tables. L'utilisation de cette commande permet de combiner les résultats des 2 tables, de les associer entre eux grâce à une condition et de remplir avec des valeurs NULL si la condition n'est pas respectée.

Pour retourner les enregistrements de table1 et table2, il convient d'utiliser une requête SQL avec une syntaxe telle que celle-ci :

Cette requête peut aussi être conçue de cette façon :

La condition présentée ici consiste à lier les tables sur un identifiant, mais la condition peut être définie sur d'autres champs.

Exemple

Prenons l'exemple d'une base de données qui contient une table utilisateur ainsi qu'une table commande qui contient toutes les ventes.

Table utilisateur :

Table commande :

Il est possible d'utiliser FULL JOIN pour lister tous les utilisateurs ayant effectué ou non une vente, et de lister toutes les ventes qui sont associées ou non à un utilisateur. La requête SQL est la suivante :

Résultat :

Ce résultat affiche bien l'utilisateur numéro 4 qui n'a effectué aucun achat. Le résultat retourne également la facture A00107 qui est associée à un utilisateur qui n'existe pas (ou qui n'existe plus). Dans les cas où il n'y a pas de correspondance avec l'autre table, les valeurs des colonnes valent NULL.

SQL NATURAL JOIN

Dans le langage SQL, la commande NATURAL JOIN permet de faire une jointure naturelle entre 2 tables. Cette jointure s'effectue à la condition qu'il y ait des colonnes du même nom et de même type dans les 2 tables. Le résultat d'une jointure naturelle est la création d'un tableau avec autant de lignes qu'il y a de paires correspondant à l'association des colonnes de même nom.

> **A noter : ** puisqu'il faut le même nom de colonne sur les 2 tables, cela > empêche d'utiliser certaines règles de nommages pour le nom des colonnes. Il > n'est par exemple pas possible de préfixer le nom des colonnes sous peine > d'avoir malheureusement 2 noms de colonne différents.

La jointure naturelle de 2 tables peut s'effectuer facilement, comme le montre la requête SQL suivante :

L'avantage d'un NATURAL JOIN c'est qu'il n'y a pas besoin d'utiliser la clause ON.

Exemple

Une utilisation classique d'une telle jointure pourrait être l'utilisation dans une application qui utilise une table utilisateur et une table pays. Si la table utilisateur contient une colonne pour l'identifiant du pays, il sera possible d'effectuer une jointure naturelle.

*

Table « utilisateur » :

Table « pays » :

Pour avoir la liste de tous les utilisateurs avec le pays correspondant, il est possible d'effectuer une requête SQL similaire à celle-ci :

Cette requête retournera le résultat suivant :

Cet exemple montre qu'il y a bien eu une jointure entre les 2 tables grâce à la colonne « pays_id » qui se trouve dans l'une et l'autre des tables.

SQL SELF JOIN

En SQL, un SELF JOIN correspond à une jointure d'une table avec elle-même. Ce type de requête n'est pas si commun mais très pratique dans le cas où une table lie des informations avec des enregistrements de la même table.

Pour effectuer un SELF JOIN, la syntaxe de la requête SQL est la suivante :

Ici la jointure est effectuée avec un LEFT JOIN, mais il est aussi possible de l'effectuer avec d'autres types de jointures.

Exemple

Un exemple potentiel pourrait être une application d'un intranet d'entreprise qui possède la table des employés avec la hiérarchie entre eux. Les employés peuvent être dirigés par un supérieur direct qui se trouve lui-même dans la table.

Table utilisateur :

Les enregistrements de la table ci-dessus montre bien des employés. Les premiers employés n'ont pas de supérieur, tandis que les employés n°3 et n°4 ont respectivement pour supérieur l'employé n°1 et l'employé n°2.

Il est possible de lister sur une même ligne les employés avec leurs supérieurs directs, grâce à une requête telle que celle-ci :

Résultat :

Il n'est pas possible d'inclure la colonne non agrégée dans la liste de sélection lorsque vous utilisez une clause "group by" dans SQL Server.

Si on a besoin de remonter les données d'une colonne d'une table regroupé et dont la colonne ne fait pas partie du regroupement. On ne peut pas employer de fonction d'agregat sur la donnée. Dans ce cas on fait une jointure avec la même table. Par exemple pour remonter la derniere opération enregistrée de chaques comptes de toto.

SQL OVER

Nous devons générer un rapport qui affiche le nombre total d'employés par département. Nous devons également afficher le salaire total, le salaire moyen, le salaire minimum et le salaire maximum par département. Nous devons également afficher les valeurs non agrégées (nom et salaire) dans le rapport avec les valeurs agrégées, comme le montre l'image ci-dessous. Cela signifie que nous devons générer un rapport comme ci-dessous.

La même requette en utilisant OVER :

réf: How to use Over Clause in SQL Server?

https://dotnettutorials.net/lesson/over-clause-sql-server/

1.6. Traitement de masse

Bulk insert

pour récupérer les id d'un bulkinsert

Update Join

Cette fonction est très utile pour remplacer les curseurs.

réf:

https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/

Données dans un champ XML

Attention aux champs nullables qui se traduisent en XML par un champ vide.

Données dans un champ en JSON

Pour les versions SQLServer2006 et ultérieur on peut utiliser la fonction OPENJSON, qui a de bonnes performances.

réf: JSON in SQL Server: The Ultimate Guide - Database Star

https://www.databasestar.com/json-sql-server/

Autrement il est possible d'utiliser une fonction pour cela comme parseJSON disponible sur ce lien:

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/consuming-json-strings-in-sql-server/

réf: sql server - Parse JSON in TSQL - Stack Overflow

https://stackoverflow.com/questions/2867501/parse-json-in-tsql

Si le formattage du JSON est connu, que les champs sont toujours présents, il est également possible de remonter certains champs ciblés de la donnée JSON avec les fonctions SQL SUBSTRING et CHARINDEX. Cela est plus performant que d'utiliser une fonction complexe comme parseJSON

Pagination avec Regroupement

Pour cela vous devez appliquer rownumber aux résultats groupés.

Trie de donnée avec pagination

Passage d'un ordre dynamique par dans une procédure stockée

1.7. Arbres de donnees

Habituellement, une hiérarchie se représente en utilisant une autoréférence, c'est-à-dire à l'aide d'une clef étrangère provenant de la clef même de la table. Les données de cette table peuvent se représenter de la sorte :

ALL
|--SEA
|  |--SUBMARINE
|  |--BOAT
|--EARTH
|  |--CAR
|  |--TWO WHEELS
|  |  |--MOTORCYCLE
|  |  |--BICYCLE
|  |--TRUCK
|--AIR
   |--ROCKET
   |--PLANE

Exemple

Résultats :

data               id          level       pathstr
------------------ ----------- ----------- --------------------------------
ALL                1           0           
 AIR               4           1           AIR
  PLANE            11          2           AIRPLANE
  ROCKET           10          2           AIRROCKET
 EARTH             3           1           EARTH,
  CAR              7           2           EARTHCAR
  TRUCK            9           2           EARTHTRUCK
  TWO WHEEL S      8           2           EARTHTWO WHEELS
   BICYCLE         13          3           EARTHTWO WHEELSBICYCLE
   MOTORCYCLE      12          3           EARTHTWO WHEELSMOTORCYCLE
 SEA               2           1           SEA
  BOAT             6           2           SEABOAT
  SUBMARINE        5           2           SEASUBMARINE

réf: Requêtes récursives avec les CTE
https://sqlpro.developpez.com/cours/sqlserver/cte-recursives/

CHAPITRE 2 - SQL Avancé

2.1. Index SQL

En SQL, les index sont des ressources très utiles qui permettent d'accéder plus rapidement aux données. Cette page explique le fonctionnement des index et leur intérêt pour accroître les performances de lecture des données.

Définition d'un index SQL

Un index, dans une base de données se base sur le même principe qu'un index dans un livre. Avec un index placé sur une ou plusieurs colonnes, la base de données peut rechercher les données d'abord sur l'index qui lui donnera l'emplacement des enregistrements concernés.

Plus précisément, un index est une structure de données redondante qui permet d'ordonner les données indexées afin de retrouver le plus vite possible les données que l'on index. Pour ce qui est des structures de stockage des index il en existe différentes, mais la plus utilisée est un arbre dit équilibré (on s'arrange pour que le niveau des feuilles de l'arbre soit constant).

L'idée de l'index cluster est de faire une pierre deux coups : mélanger l'index et la table. On s'évite ainsi une redondance et les lignes de la table sont physiquement triées selon l'ordre de la clef d'index.

Ainsi, une table dotée des colonnes Nom, Prénom, DateNaissance, Sexe, si elle est dotée d'un index cluster sur Nom est physiquement triée sur les valeurs des noms des personnes. Les autres colonnes n'étant pas triées. On s'économise ainsi une redondance de données.

Évidemment on ne peut avoir qu'un seul index cluster dans une table car en fait l'index cluster c'est la table elle même !

Ces petites ressources ont toutefois leurs inconvénients car cela occupe de l'espace supplémentaire dans la base de données. Par ailleurs, l'insertion de données est plus long car les index sont mis à jour à chaque fois que des données sont insérées.

Généralement, un index pourra être utilisé dans les requêtes utilisant les clauses WHERE, GROUP BY ou ORDER BY. Lorsqu'une base de données possède un grand nombre d'enregistrements (exemple : plusieurs milliers ou plusieurs millions de lignes) un index permet de gagner un temps précieux pour la lecture de données.

Un index est systématiquement défini sur la clé primaire de chaque table.

SQL CREATE INDEX

En SQL, la commande CREATE INDEX permet de créer un index. L'index est utile pour accélérer l'exécution d'une requête SQL qui lit des données et ainsi améliorer les performances d'une application utilisant une base de données.

Créer un index ordinaire

La syntaxe basique pour créer un index est la suivante :

Il est également possible de créer un index sur une seule colonne en précisant la colonne sur laquelle doit s'appliquer l'index :

L'exemple ci-dessus va donc insérer l'index intitulé « index_nom » sur la table nommée « table » uniquement sur la colonne « colonne1? . Pour insérer un index sur plusieurs colonnes il est possible d'utiliser la syntaxe suivante :

L'exemple ci-dessus permet d'insérer un index sur les 2 colonnes : colonne1 et colonne2.

Créer un index unique

Un index unique permet de spécifier qu'une ou plusieurs colonnes doivent contenir des valeurs uniques à chaque enregistrement. Le système de base de données retournera une erreur si une requête tente d'insérer des données qui feront doublon sur la clé d'unicité. Pour insérer un tel index il suffit d'exécuter une requête SQL respectant la syntaxe suivante :

Dans cet exemple un index unique sera créé sur la colonne nommée colonne1. Cela signifie qu'il ne peut pas y avoir plusieurs fois la même valeur sur 2 enregistrements distincts contenus dans cette table.

Il est également possible de créer un index d'unicité sur 2 colonnes, en respectant la syntaxe suivante :

Inclure une colonne dans un index

Le fait d'inclure des colonnes dans les index permet d'éviter, lorsque l'index est utilisé, de devoir aller chercher des données complémentaires dans la table sous jacente.

Ainsi, une requête count(*) utilisera l'index. En revanche, pour la requête remontant les champs per_nom et per_prenom, l'index pourrait encore être utilisé pour trouver les lignes qui vérifient la condition de filtre, mais il faudra ensuite ouvrir la table pour y trouver les prénoms. Passé un certain ratio de cardinalités, ces aller-retour deviennent trop couteux, et le moteur préférera scanner la table complète, mais une seule fois. Vous pouvez remplacer votre index par celui-ci :

Mais le tri sur la colonne prénom est inutile dans votre cas. Vous pouvez donc simplement mettre la colonne prenom en colonne incluse à l'index. Ainsi, l'index couvre votre requête, et il n'est plus nécessaire de lire des données dans la table.

réf:

https://www.developpez.net/forums/d1458300/bases-donnees/ms-sql-server/administration/utilisation-include-index-non-cluster/

Convention de nommage

Il n'existe pas de convention de nommage spécifique sur le nom des index, juste des suggestions de quelques développeurs et administrateurs de bases de données. Voici une liste de suggestions de préfixes à utiliser pour nommer un index :

• Préfixe « PK_ » pour Primary Key (traduction : clé primaire)
• Préfixe « FK_ » pour Foreign Key (traduction : clé étrangère)
• Préfixe « UK_ » pour Unique Key (traduction : clé unique)
• Préfixe « UX_ » pour Unique Index (traduction : index unique)
• Préfixe « IX_ » pour chaque autre IndeX

2.2. SQL EXPLAIN

Dans le langage SQL, l'instruction EXPLAIN est à utiliser juste avant un SELECT et permet d'afficher le plan d'exécution d'une requête SQL. Cela permet de savoir de quelle manière le Système de Gestion de Base de Données (SGBD) va exécuter la requête et s'il va utiliser des index et lesquels.

En utilisant cette commande la requête ne renverra pas les résultats du SELECT mais plutôt une analyse de cette requête.

A noter : le résultat de cette instruction est différent selon les SGBD, tel que MySQL ou PostgreSQL. Par ailleurs, le nom de cette instruction diffère pour certains SGBD :

• MySQL : EXPLAIN
• PostgreSQL : EXPLAIN
• Oracle : EXPLAIN PLAN
• SQLite : EXPLAIN QUERY PLAN
• SQL Server : SET SHOWPLAN_ALL ON : informations estimées d'une requête SQL, affichées au format textuel détaillé SET SHOWPLAN_TEXT ON : informations estimées d'une requête SQL, affichées au format textuel simple SET SHOWPLAN_XML ON : informations estimées d'une requête SQL, affichées au format XML SET STATISTICS PROFILE ON : statistiques sur l'exécution d'une requête SQL, affichées au format textuel SET STATISTICS XML ON : statistiques sur l'exécution d'une requête SQL, affichées au format XML
• Firebird : SET PLANONLY ON; puis l'exécution de la requête SQL à analyser

La syntaxe ci-dessous représente une requête SQL utilisant la commande EXPLAIN pour MySQL ou PostgreSQL :

> Rappel : dans cet exemple, la requête retournera des informations sur le > plan d'exécution, mais n'affichera pas les « vrais » résultats de la requête.

Exemple

Pour expliquer concrètement le fonctionnement de l'instruction EXPLAIN nous allons prendre une table des fuseaux horaires en PHP. Cette table peut être créée à partir de la requête SQL suivante :

La requête ci-dessous permet de mieux comprendre la structure et les index de cette table. Imaginons que l'on souhaite compter le nombre de fuseaux horaires par groupe, pour cela il est possible d'utiliser la requête SQL suivante :

Analyse de la requête SQL sans index

Nous allons voir dans notre exemple comment MySQL va exécuter cette requête. Pour cela, il faut utiliser l'instruction EXPLAIN :

Le retour de cette requête SQL est le suivant :

Dans cet exemple on constate les champs suivants :

• id : identifiant de SELECT
• select_type : type de cause SELECT (exemple : SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBQUERY, DEPENDENT SUBSELECT ou DERIVED)
• table : table à laquelle la ligne fait référence
• type : le type de jointure utilisé (exemple : system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index ou ALL)
• possible_keys : liste des index que MySQL pourrait utiliser pour accélérer l'exécution de la requête. Dans notre exemple, aucun index n'est disponible pour accélérer l'exécution de la requête SQL
• key : cette colonne présente les index que MySQL a décidé d'utiliser pour l'exécution de la requête
• key_len : indique la taille de la clé qui sera utilisée. S'il n'y a pas de clé, cette colonne renvois NULL
• ref : indique quelle colonne (ou constante) est utilisée avec les lignes de la table
• rows : estimation du nombre de lignes que MySQL va devoir analyser examiner pour exécuter la requête
• Extra : information additionnelle sur la façon dont MySQL va résoudre la requête. Si cette colonne retourne des résultats, c'est qu'il y a potentiellement des index à utiliser pour optimiser les performances de la requête SQL. Le message « using temporary » permet de savoir que MySQL va devoir créer une table temporaire pour exécuter la requête. Le message « using filesort » indique quant à lui que MySQL va devoir faire un autre passage pour retourner les lignes dans le bon ordre

Ajout d'un index

Il est possible d'ajouter un index sur la colonne « timezone_groupe_fr » à la table qui n'en avait pas.

L'ajout de cet index va changer la façon dont MySQL peut exécuter une requête SQL. En effectuant la même requête que tout à l'heure, les résultats seront différents.

Dans ce résultat il est possible de constater que MySQL va utiliser un l'index « index_timezone_groupe_fr » et qu'il n'y a plus aucune information complémentaire d'indiquée dans la colonne « Extra ».

réf: Cours SQL

https://sql.sh/cours

2.3. Modes de verrouillage

a) Verrouillage pessimiste:

Lorsque qu'un utilisateur désire modifier une donnée, le SGBD tente de poser un verrou en écriture. Soit l'opération est effective (et donc le verrou est posé), soit l'opération échoue parce qu'un autre utilisateur a déjà posé un verrou. Si l'opération est positive, pendant toute la durée de la modification, le verrou est actif pour cet utilisateur jusqu'à ce que l'utilisateur valide ses modifications ou les annule. Dans ces deux cas le verrou est libéré.

b) Verrouillage optimiste

Lorsque qu'un utilisateur désire modifier une donnée, le SGBD relève le numéro de version de l'enregistrement et ne pose aucun verrou. Plusieurs utilisateurs peuvent faire de même sans aucune limite. Le premier utilisateur qui valide ses modifications, incrémente le numéro de version de l'enregistrement. Tous les autres se voient refuser leur modification, car le numéro de version qu'ils ont relevé au début de la session de modification des données n'est pas le même que celui qu'ils peuvent lire à la fin de cette session.

Lorsque l'on utilise la technique du verrouillage pessimiste, le SGBD effectue une boucle d'attente en tentant de poser le verrou pendant n secondes. C'est comme cela que fonctionne la quasi-totalité des SGBD à base de fichiers (dBase, Paradox, FoxPro, Access…).

Effectuer une boucle d'attente qui interroge un fichier, monopolise dramatiquement le réseau. C'est pourquoi la quasi-totalité des SGBD C/S fonctionne en verrouillage optimiste, car dans ce mode, l'échange de données entre la base et le poste client est rapide et bref…

De plus si nous prenons en compte l'intégrité référentielle et que la structure de la base permet de modifier des clefs existantes en répercutant leurs valeurs dans l'ensemble des tables filles, alors dans le cas d'un SGBD fichier ont peut obtenir un trafic réseau important, tandis que dans le cas du client serveur, l'opération sera menée au sein du SGBD donc sans aucune influence sur la charge du réseau.

2.4. Le transactionnel

Le transactionnel permet de regrouper de multiples commandes SQL comme s'il s'agissait d'une instruction de type atomique, c'est à dire s'exécutant en totalité ou aucunement.

Cela suppose de donner un point de départ à la transaction un point d'arrivée et de contrôler si l'ensemble des commandes SQL ont été réalisées avec succès. Dans le cas ou l'une des commandes SQL n'a pas été traitée correctement, alors le point final de la transaction permet de revenir en arrière sur l'ensemble des opérations déjà effectuées. La transaction se présente alors comme une seule instruction de type ‘tout ou rien'.

Cela n'est possible que sur des serveurs capables d'enregistrer l'ensemble des insertions, modifications et suppressions effectuées sur les enregistrements des tables d'une base (historisation des modifications).

Supposons que les commerciaux sont dotés de PC portables et que, chaque semaine, ils viennent au siège de la société pour remplir la base centrale des nouveaux clients et des commandes qu'ils ont saisies sur leur portable.

Pour réaliser ce recollement d'information, l'informaticien a réalisé la transaction suivante :

S'il n'avait fait qu'enchaîner les deux requêtes, il aurait été possible que les clients soit insérés sans que les commandes le soit, et vice versa. Mais tous les bons SGBD, qu'ils soient C/S ou fichier, savent gérer les intégrités référentielles et auraient empêché la moindre insertion dans la table des commandes sans avoir la référence du client préalablement insérée dans la table des clients…

2.5. Les triggers, spécialités du C/S

Les triggers font correspondre aux événements d'un SGBD du code.

Soit une table CLIENT liée à 3 tables filles (FACT, LIGNFACT, BONLIV). On veut modifier les tables filles lors de la suppression d'un enregistrement de CLIENT:

Dans les SGBD à base de fichiers il faut rajouter du code dans tous les éléments qui effectuent la suppression dans la table mère…

Dans une base de données C/S, avec un trigger, le traitement s'effectue directement sur le serveur et il ne nécessite aucun appel à aucune fonction d'aucune sorte. C'est en effet la base elle-même qui va se rendre compte que l'événement de suppression de la table client nécessite un traitement et va l'exécuter. Dans un tel exemple, le code pourrait être le suivant :

Ce trigger comportant différentes commandes SQL devra être appelé au sein d'une transaction plus globale incluant la suppression de l'enregistrement dans la table mère. Les commandes SQL de ce trigger réclament la suppression dans les tables FACT, LIGNFACT et BONLIV de tous les enregistrements faisant référence à l'identifiant du client transmis en paramètre. Si au moins, une des requêtes échoue, le trigger renvoi False à la procédure l'ayant appelée et l'ensemble des requêtes sera annulé. Sinon, si tout va bien, le trigger renvoi True et l'ensemble des modifications sera rendu effectif par une validation (COMMIT).

Ce trigger est stocké au sein même de la définition de la base de données et s'exécute, sur le serveur, à chaque fois qu'un ordre de suppression arrive dans la table CLIENT. Il n'y a donc aucun trafic sur le réseau pour effectuer ce traitement…

2.6. Les procédures stockées, régals du C/S

En complément aux triggers, les procédures stockées permettent de stocker un traitement (opérant en général uniquement sur les données de la base) au sein même de la base de données, qui s'exécutera, par appel de la procédure au moment voulu, sur le serveur.

Exemple :

Il est possible d'utiliser des curseur, des boucle, des curseurs

Dès lors cette procédure peut être appelée n'importe quand, par n'importe quel poste client… Elle s'exécutera sur le serveur et ne nécessitera pratiquement aucun trafic réseau. On peut aussi imaginer qu'elle soit couplée avec un trigger ou encore exécutée à une heure différée, par exemple la nuit afin de ne pas ralentir les accès aux données plus fréquents en général dans la journée…

Procédures avec paramètre de sortie :

Evaluation d'une expression :

Table en paramètre :

réf:

https://learn.microsoft.com/fr-fr/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16#BulkInsert

2.7. Les curseurs

Procédure avec un curseur :

En principe il ne faux pas utiliser de curseurs car cela dégrade la performance, mais dans certains cas ils peuvent être très pratiques:

Avant d'utiliser un curseur, vérifier s'il est pas possible de faire la même chose avec une boucle WHILE qui est 20 fois plus rapide.

réf:

https://mcherif.wordpress.com/2013/05/25/sql-server-curseurs-comparaison-de-performances-avec-une-boucle-while/

2.8. Le journal, particularité des SGBD C/S

En plus des triggers et des procédures stockées, la plupart des SGBD C/S proposent d'historiser la « vie » des données, dans ce que l'on appelle un journal des transactions. Celui-ci conserve une trace temporelle de toutes les insertions, suppressions, modifications intervenues dans la base de données depuis son origine. Ce journal permet de revenir en arrière de manière partielle sur les données. L'intérêt essentiel réside dans le fait de pouvoir, en cas de crash, revenir à un état des données satisfaisant.

Pour revenir à cet état stable, le système lit le journal en débutant par la fin, et reprend toutes les transactions qui ont été un succès, sans que la mise à jour physique des données ait été effectuée.

réf: SGBDR fichier ou client/serveur ?

https://sqlpro.developpez.com/cours/sgbdr/

2.9. Les vues

Les vues simples

Une vue est une table virtuelle dérivée de tables de base. La vue, a également l'avantage d'avoir son plan compilé sur le serveur et donc d'obtenir un léger gain dans l'exécution de la requête.

• On stocke seulement la définition de vue

• Son contenu est généré dynamiquement

Les vues indexés

Les vues indexées sont un excellent vecteur d'optimisation pour des requêtes un peu complexes ou fréquemment utilisées contenant des agrégats ou des jointures.