select

select

Nous attaquons alors les sujets de SQL (structured query language), et comment ce langage nous aide à implémenter l'algèbre relationnelle !

Le rêve : récupérer uniquement les données nécessaires : pas plus, pas moins !!

... et d'ailleurs, aussi vite possible !

Commençons par la commande select.

À la base, SQL avait l'objectif de ressembler à un langage "naturel" (anglais) :

Select a-list-of-attributes from a-list-of-relations where attribute(s)-satisy-this-condition

Enfin, le résultat est moins formel qu'un langage de programmation (on peut exprimer nos souhaits de plusieurs manières), mais il y a quand même une structure obligatoire.

En revanche, SQL reste déclaratif : on exprime notre résultat désiré, et le SGBDR construit est implémenté un plan d'action pour nous retourner ce résultat.

La commande la plus simple, par exemple, pour extraire quelques attributs de la relation user de la base de données saas :

select gender, familyName, givenName from user;

Il est possible d'utiliser de caractères de remplacement :

select * from user;

Mais, il ne faut jamais, jamais, jamais l'utiliser !

• Vous risquez de divulguer les attributs sensibles !

• Vous récupérez tous les attributs, même ceux dont on n’a pas besoin !

  • Saturer inutilement la bande-passante

  • Surcharger votre client, mémoire du serveur, ou page web

  • Être facturé (si service cloud qui facture la transmission des données)

Distinct

Regardez la requête suivante :

select gender, givenName from user order by givenName limit 10;

Le résultat est :

gender	givenName
male	Aaron
male	Aaron
male	Abdon
male	Abdon
male	Abdon
unknown	Abdon
male	Abdon
female	Abdonie
female	Abdonie
female	Abdonie

Pour extraire des tuples uniques (l'ensemble de colonnes est unique) on utilise le mot distinct :

select distinct gender, givenName 
from user 
order by givenName 
limit 10;

gender	givenName
male	Aaron
male	Abdon
unknown	Abdon
female	Abdonie
male	Abel
male	Abélard
male	Abelin
female	Abeline
female	Abigaelle
female	Abigaïl

Notez qu'on voit deux fois le prénom Abdon parce que c'est l'ensemble des colonnes qui est utilisé pour déterminer si la ligne est unique (et dans ce cas, le gender est différent).

Opérations sur les attributs

On peut transformer les résultats au passage :

select upper(name), budget, budget * 0.2 as tva 
from action 
limit 10;

Ici :

• on transforme le nom de chaque action en majuscule

• on calcule le TVA du budget retourné en multipliant par 20%. On renomme l'attribut avec le mot clé as

Il y a une gamme de fonctions disponibles comme upper, selon l'implémentation, par exemple :

• concat(a, b) : fusionner deux chaines de caractère

• length(a) : retourner le nombre de caractères

• etc. ... une liste se trouve ici : https://www.sqlservertutorial.net/sql-server-string-functions/

La clause where

Essayez toujours de minimiser la quantité de données retournée en filtrant les résultats avec la clause where :

select familyName, givenName 
from user 
where gender = 'female' and givenName like '%y%'
order by familyName desc 
limit 10;

Les opérateurs de comparaison sont :

• = : égale à

• <> : pas égale à (aussi, !=)

• <=, <, >=, >

On combine les clauses pour créer des conditions complexes :

• and : opérateur et (équivalent à && en javascript)

• or : opérateur ou (équivalent à || en javascript)

• not : opérateur négation (équivalent à ! en javascript)

Essayez de toujours filtrer les données autant possibles avant qu'elles arrivent coté client !

Utiliser des fonctions dans vos clauses where aussi !

Normalement, la comparaison entre deux attributs de type string est sensible au cas. Ceci peut changer selon le type de SGBDR et sa configuration.

Dans le doute, on peut utiliser les fonctions type UPPER ou LOWER pour normaliser la valeur à un cas précis. Par exemple, si on utilise l'adresse email comme identifiant, il faut qu'on puisse valider l'identité du compte malgré le bruit qui peut venir du client. Imaginons le cas où l'utilisateur ne fait pas attention à la capitalisation, et en plus, il y a du blanc dans le string envoyé :

select familyName, givenName, email 
from user 
where 
  lower(email) = lower(trim("   [email protected] "));

L'opérateur like

L’opérateur like permet le filtrage par motif :

select customerId, name from customer where name like "%dubois%";

Le pourcentage (%) agit comme un caractère de remplacement (wildcard) pour plusieurs caractères.

• % : corresponde à n’importe quel substring (chaîne de caractères de n’importe quelle longueur)

• _ : correspondre à un seul caractère

Par exemple :

• like "_ _ _" : va trouver les textes de longueur 3 précisément

• like "_ _ _%" : va trouver les strings d’au moins 3 caractères

Comme sur UNIX, on peut échapper un pourcentage avec l'antislash \ si on cherche le caractère % (ou _) :

like "ab\%cd"

Les valeurs null

Que faire quand il y a des valeurs manquantes ? Quel sens avec des opérateurs mathématiques ou en comparant avec des vraies valeurs ?

Par exemple, la phrase suivante veut dire quoi exactement ?

1 < null

Pour traiter ce cas un peu anormal, SQL utilise une valeur unknown. Donc :

• A < null = unknown

• true and null = unknown

• false and null = false

• true or null = true

• false or null = unknown

• not unknown = unknown

Utilisez null ou unknown comme critères avec des clauses suivantes :

• is null

• is unknown

• is not null

• is not unknown

Par exemple :

select actionId, name, address_city
from action 
where address_city is not null 
limit 10;

Croiser plusieurs tables

Je veux récupérer la liste des intervenants avec leur nom, prénom et statut :

select givenName, familyName, status 
from user, animator 
where user.userId = animator.userId 
limit 10;

Notez le suivant de l'exemple ci-dessus :

• Je peux sélectionner les attributs sans préciser la table d'origine, car il n'y a pas d’ambiguïté. Sinon, je serais obligé de spécifier la table concernée (ex. user.givenName, et animator.status).

• Je précise la condition de jointure pour limiter le résultat du produit cartésien.

Essayez la phrase suivante :

select givenName, familyName, status 
from user, animator 
limit 10;

Vous arrivez à expliquer le résultat ?

Je peux croiser plusieurs relations, mais parfois la phrase devient longue. Le mot clé as (pour créer des alias) sont pratiques pour plus facilement structurer las phrase :

select a.name as Action, c.name as Customer 
from action as a, customer as c, action_customer as ac 
where a.actionId = ac.actionId and ac.customerId = c.customerId 
limit 10;

Notez les 2 fonctions du mot clé as dans l'exemple :

• Pour renommer les attributs dans le résultat

• Pour créer un alias pour plus facilement adresser les attributs dans les clauses where et select.

Le mot clé as : un outil puissant pour l'auto-comparaison !

Je veux savoir s'il existe des interventions qui sont facturées plus chères que celles en Île-de-France.

Nous allons regarder la table action, notamment les champs budget et address_french_regionId.

Nous utilisons notre connaissance du produit cartésien pour faire cette requête ! Je sais qu'à la base, on peut juste énumérer toutes les combinaisons d'intervention possible avec :

select S.actionId, S.name, S.budget, S.address_french_regionId, A.actionId, A.name, A.budget, A.address_french_regionId
from action as S, action as A
limit 10;

Le mot clé as me permet de traiter la même relation comme deux relations différentes !

Le résultat est :

actionId	name	budget	address_french_regionId	actionId	name	budget	address_french_regionId
5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f08cf9708d0f10010d8e9de	Ergonomique Frais Voiture	0.00	NULL	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f08d1e16ecbce00107beb12	Raffiné Plastique Frites	0.00	NULL	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1eb03908d0f10010d8ea42	Luxueux Congelé Serviettes	4055.00	NULL	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1eb48f6ecbce00107beb7d	Raffiné Congelé Table	0.00	GES	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1eb4c808d0f10010d8ea47	Pratique Coton Lard	0.00	GES	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1eb5236ecbce00107beb7e	Rustique Acier Ordinateur	0.00	GES	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1ec7726ecbce00107beb7f	Magnifique Bois Pantalon	3128.00	NULL	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1ec8cd08d0f10010d8ea4d	Pratique Doux Table	5096.00	NULL	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF

On voit dans le résultat (limité à 10 lignes) :

• Sur la partie droite, on démultiplie l'action "Petit Acier Saucisses" avec toutes les actions sur le gauche.

• Il suffit de chercher les lignes sur lesquelles :

  • le budget de la partie gauche est supérieur à la partie droite

  • la région de la partie gauche n'est pas IDF (et pas nulle) et la région de la partie droite est IDF.

select S.actionId, S.name, S.budget, S.address_french_regionId, A.actionId, A.name, A.budget, A.address_french_regionId
from action as S, action as A
where 
  S.address_french_regionId != 'IDF' and S.address_french_regionId is not null 
  and 
  A.address_french_regionId = 'IDF' 
  and 
  S.budget > A.budget
limit 10;

Cette fois, on constate qu'on ne retient uniquement des valeurs du côté gauche qui satisfont les conditions. Mais, vu que les 2 relations sont démultipliées : pour chaque relation du gauche, son budget peut être supérieur à plusieurs sur le côté droit :

actionId	name	budget	address_french_regionId	actionId	name	budget	address_french_regionId
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f0601a69a46f100105635a9	Petit Acier Saucisses	0.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f22933608d0f10010d8ea51	Génial Acier Salade	400.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f3fdb2108d0f10010d8ee2a	Élégant Acier Boule	0.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f47d10f6ecbce00107bf16e	Raffiné Doux Pizza	400.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f47d21a08d0f10010d8efe8	Moderne Bois Gants	400.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f4cb9356ecbce00107bf1d7	Moderne Bronze Chemise	300.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f4cb9ab08d0f10010d8f011	Luxueux Frais Saucisses	300.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f4fb0e608d0f10010d8f03f	Fantastique Congelé Chapeau	300.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f51effb08d0f10010d8f066	Luxueux Bronze Boule	300.00	IDF
5f1eabcf08d0f10010d8ea29	Moderne Congelé Chaussures	2332.50	GES	5f5b45e5a145010010ee3861	Génial Plastique Chaussures	300.00	IDF

Dans ce cas, il suffit d'extraire uniquement des interventions "uniques" qui répondent aux contraintes, avec le mot clé distinct (et en enlevant les attributs du côté droit) :

select distinct S.actionId, S.name, S.budget, S.address_french_regionId
from action as S, action as A
where 
  S.address_french_regionId != 'IDF' and S.address_french_regionId is not null 
  and 
  A.address_french_regionId = 'IDF' 
  and 
  S.budget > A.budget
limit 10;

Vous allez remarquer que si on enlève la limit 10 à la fin de la phrase, la requête prend beaucoup de temps.

Pourquoi ?

Notre relation action contient 32334 lignes. Pour construire le produit cartésien, il faut produire 32334*32334 lignes. Ceci peut être très long ! Il y a peut-être une meilleure façon de concevoir notre requête SQL pour réduire le temps d'exécution.

Exercice

Les exercices suivants sont basés sur la base de données SAKILA, une base de données de test fourni par MySQL.

Récupérez la base SAKILA ici.

Question 1

Trouvez la liste d’acteurs avec le prénom « Joe », et affichez seulement son ID, prénom et nom de famille. N’affichez que son ID, prénom et nom de famille.

Solution

1 résultat.

select actor_id, first_name, last_name 
from actor 
where first_name = "Joe";

Question 2

Trouvez tous les acteurs dont le nom de famille contient les lettres « gen » N’affichez que son ID, prénom et nom de famille.

Solution

4 résultats.

select actor_id, first_name, last_name 
from actor 
where upper(last_name) like "%GEN%";

Question 3

Trouvez tous les acteurs dont le nom de famille contient les lettres « li », et trier par nom de famille puis par prénom. N’affichez que son ID, prénom et nom de famille.

Solution

10 résultats.

select actor_id, first_name, last_name
from actor 
where upper(last_name) like "%LI%" 
order by last_name, first_name;

Question 4

Cherchez tous les pays qui ont une ville dont le nom commence par « San ». N’affichez que le nom du pays, et enlevez les duplications.

Solution

10 pays.

select distinct(country) 
from country, city 
where 
  country.country_id = city.country_id 
  and city like "San%";

Question 5

Trouvez tous les films dans lequel joue l’acteur « Nick Wahlberg »

Solution

25 films.

select title 
from film, film_actor, actor
where 
  actor.actor_id = film_actor.actor_id 
  and film_actor.film_id = film.film_id 
  and actor.first_name = "NICK" 
  and actor.last_name = "WAHLBERG";

Question 6

Trouvez tous les films de type « comedy » avec un rating de « PG » ou « G ».

Solution

27 films.

select title
from film, film_category, category
where 
  film.film_id = film_category.film_id 
  and film_category.category_id = category.category_id 
  and category.name = "Comedy" 
  and (film.rating = "G" or film.rating = "PG");