Commandes et opérateurs SQL

Voici des commandes et opérateurs pour améliorer vos rapports SQL.

SELECT

SELECT est la première déclaration à mettre dans vos rapports SQL. C’est la commande qui permet d’indiquer à Koha quelles colonnes afficher.

Par exemple,

SELECT cardnumber,surname,firstname

affichera les colonnes cardnumber, surname et firstname.

Note

Certains rapports vont avoir le nom de la table avec le nom de la colonne. Par exemple,

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname

Ceci n’est pas obligatoire à moins qu’une colonne ait le même nom dans deux tables utilisées dans le rapport. Par exemple, il y a une colonne title dans borrowers (le titre de civilité) et une colonne title dans biblio (le titre du document). Si on utilise les deux tables dans notre rapport, c’est important de spécifier de quelle table on veut tirer title.

Un exemple précis serait une liste de prêts. Ce rapport est ambigu et Koha donnera un message d’erreur.

SELECT cardnumber,
       surname,
       firstname,
       title,
       author,
       barcode,
       date_due
FROM issues
     LEFT JOIN borrowers USING (borrowernumber)
     LEFT JOIN items USING (itemnumber)
     LEFT JOIN biblio USING (biblionumber)

Il faut alors spécifier la table de laquelle on veut voir title.

SELECT cardnumber,
       surname,
       firstname,
       biblio.title,
       author,
       barcode,
       date_due
FROM issues
     LEFT JOIN borrowers USING (borrowernumber)
     LEFT JOIN items USING (itemnumber)
     LEFT JOIN biblio USING (biblionumber)

COUNT()

Dans la déclaration SELECT, on peut mettre COUNT() qui, au lieu d’afficher la liste des données, comptera le nombre d’entrées.

Par exemple,

SELECT COUNT(borrowernumber)

affichera le nombre de borrowernumber.

On peut aussi mettre un astérisque dans les parenthèses pour compter le nombre d’entrées dans la table.

SELECT COUNT(*)

AS ""

La commande AS permet de renommer la colonne dans le rapport.

Par exemple,

SELECT firstname AS "Prénom"

affichera les données de la colonne firstname, mais dans le rapport, cette colonne sera intitulée « Prénom ».

FROM

FROM est la déclaration qui indique dans quelle table Koha trouvera les colonnes énumérées dans SELECT.

SELECT cardnumber,surname,firstname
FROM borrowers

LEFT JOIN

Lorsque les données viennent de plusieurs tables, il faut les « joindre ».

Le nom de la table doit être suivi soit de USING ou ON, puis de la ou les colonnes communes entre parenthèses.

On utilise USING seulement lorsque le nom de la colonne commune aux deux tables est le même. On met alors le nom de la colonne entre parenthèses.

SELECT title, author, itemcallnumber
FROM items
     LEFT JOIN biblio USING (biblionumber)

On utilise ON lorsque le nom de la colonne n’est pas le même. Dans ce cas, on met le nom de la première table, un point, le nom de la colonne, un signe égal, le nom de la deuxième table, un point et le nom de la deuxième colonne, le tout entre parenthèses. On peut aussi utiliser ON lorsque le nom de la colonne est le même, mais il faut respecter la syntaxe.

SELECT title, author, itemcallnumber, lib
FROM items
     LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
     LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
WHERE itemlost != 0 
      AND category = 'LOST'

WHERE

La déclaration WHERE permet d’ajouter une limite à la requête.

SELECT cardnumber,surname,firstname
FROM borrowers
WHERE categorycode = 'ADULTE'

La condition doit être entre guillemets simples ou doubles. Elle doit aussi correspondre au code et non au libellé. Par exemple, le code pour le statut perdu est 1, alors que le libellé est Perdu.

Vous pouvez trouver les codes dans la section Administration de Koha.

Opérateurs

* Les valeurs NULL (vide) et "" (rien) sont différentes en SQL. Il est possible que pour certains champs, la valeur soit NULL (vide) et pour d’autres champs, il n’y ait simplement rien. Vous pouvez donc mettre un OR ou utiliser un IN pour inclure NULL et "".

SELECT cardnumber,surname,firstname,email
FROM borrowers
WHERE email NOT IN (NULL, "")

Opérateurs booléens

Il est possible d’avoir plusieurs conditions dans un WHERE en les liant avec des opérateurs booléens.

AND

Lorsque les conditions sont liées par AND, les entrées doivent correspondre à toutes les conditions pour être affichées dans le rapport.

Par exemple, ce rapport affichera les utilisateurs de catégorie « ADULTE » dont la date d’échéance de l’abonnement est avant le 1er janvier 2023.

SELECT cardnumber,surname,firstname
FROM borrowers
WHERE categorycode = 'ADULTE'
      AND dateexpiry < '2023-01-01'

OR

Lorsque les conditions sont liées par OR, les entrées qui correspondent à l’une ou l’autre des conditions seront affichées dans le rapport.

Par exemple, ce rapport affichera autant les utilisateurs de catégorie « ADULTE » que les utilisateurs de catégorie « ENFANT ».

SELECT cardnumber,surname,firstname
FROM borrowers
WHERE categorycode = 'ADULTE'
      OR categorycode = 'ENFANT'

WHERE categorycode = 'ADULTE' OR categorycode = 'ENFANT' AND dateexpiry < '2023-01-01'

WHERE (categorycode = 'ADULTE' OR categorycode = 'ENFANT') AND dateexpiry < '2023-01-01'

DATE()

Il arrive qu’une valeur ait le format date/heure (format parfois appelé timestamp ou datetime) et comprend autant la date que l’heure. Pour ne faire afficher que la date dans la déclaration SELECT, ou n’utiliser que la date dans la condition WHERE, on peut utiliser la commande DATE().

Lorsqu’on utilise la commande DATE(), il faut mettre le nom de la colonne qui contient une date ou une date/heure entre les parenthèses.

Par exemple, la date de prêt, issuedate, dans la table issues, est un format datetime. Pour comparer seulement la date dans la condition WHERE, il faut utiliser DATE(issuedate).

SELECT title,
       author,
       barcode,
       itemcallnumber
FROM issues
     LEFT JOIN items USING (itemnumber)
     LEFT JOIN biblio USING (biblionumber)
WHERE DATE(issuedate) = '2023-05-06'

YEAR()

Comme pour DATE(), il est possible d’extraire seulement l’année d’une date.

Par exemple, pour avoir tous les prêts faits en 2023, on peut utiliser YEAR(issuedate).

SELECT COUNT(*)
FROM issues
WHERE YEAR(issuedate) = '2023'

ORDER BY

La déclaration ORDER BY permet de trier les résultats du rapport. Par défaut, les résultats seront triés en ordre ascendant, soit du plus petit au plus grand, de A à Z, ou du plus ancien au plus récent.

SELECT cardnumber,surname,firstname
FROM borrowers
WHERE categorycode = 'ADULTE'
ORDER BY surname

On peut toutefois spécifier DESC pour trier dans l’ordre inverse.

SELECT cardnumber,surname,firstname
FROM borrowers
WHERE categorycode = 'ADULTE'
ORDER BY dateenrolled DESC

LIMIT

La déclaration LIMIT permet d’indiquer combien de résultats on veut voir dans le rapport.

Par exemple, ce rapport sera limité à 1000 résultats. Ceci est particulièrement utile pour les rapports qu’on crée pour des modifications ou des suppressions en lot, où le nombre d’exemplaires est limité à 1000 à la fois.

SELECT itemnumber
FROM items
WHERE withdrawn != 0
LIMIT 1000

Voir aussi

• Fragments de rapports SQL
• Rapport : Réservations sur des documents perdus
• Rapport : Réservations sur des documents élagués
• Rapport : nouveaux exemplaires selon la date d’acquisition

Ressources de la communauté Koha

• SQL reports library (anglais)
• Basic SQL for Koha users (anglais)
• Koha community database schemas (anglais)

Autres ressources

• W3 Schools: SQL Tutorial (anglais)
• SQL.sh Cours et tutoriels sur le langage SQL (français)