Chap. 5 : Langage SQL

Cours

Les systèmes de gestion de bases de données relationnelles

Description

Un système de gestion de bases de données relationnelle (SGBDR) est un logiciel conçu pour interagir (stocker, manipuler, gérer) avec une base de données relationnelle : Il sert d'intermédiaire entre l'utilisateur et les données de la base de données.
Un tel système doit implémenter quatre opérations de base :

  • Create (créer)
  • Read ou Retrieve (lire)
  • Update (mettre à jour)
  • Delete ou Destroy (supprimer)
Ainsi, l'acronyme CRUD résume les fonctions dont un utilisateur ou gestionnaire a besoin pour créer et gérer des données. En français, on utilise parfois le sigle LMD (la création est sous-entendue) :
  • Lire
  • Modifier
  • Détruire

Logiciels et langage SQL

Il existe plusieurs logiciels SGBDR, tous basés sur CRUD :

  • MariaDB ou MySQL (environ 20% du marché) ;
  • Microsoft SQL Server (environ 20% du marché) ;
  • Oracle (environ 20% du marché) ;
  • PostgreSQL (environ 10% du marché) ;
  • MongoDB (environ 10% du marché) ;
  • SQLite (environ 5% du marché) ;
  • Microsoft Access ou LibreOffice Base (en lien avec la bureautique) ;
  • ...

Ces logiciels utilisent le langage SQL (Structured Query Language, en français : langage de requêtes structurées), dont ils partagent les instructions de base mais présentent aussi des variations mentionnées dans leur documentation respective.

Services rendus par le SGBD

Au niveau conceptuel, les SGBD utilisent l'architecture Ansi/Sparc crée en 1975 par Charles Bachman, félicité par un prix Turing (équivalent d'un prix Nobel d'informatique).

Elle est divisée en trois niveaux, fréquentés par différents acteurs :
  • schémas externes (SE) pour les utilisateurs
    Des informations de la BD sont présentées d'une manière différente et adaptée à chacun, à travers des applications spécifiques ou à l'aide d'un langage type SQL pour les utilisateurs les plus avancés.
  • schéma conceptuel (SC) pour les concepteurs/développeurs
    C'est la vue de la totalité des informations de la BD, souvent organisée à l'aide du modèle entité-association.
    Les développeurs créent des applications et des scripts destinées aux utilisateurs.
    Les concepteurs stockent les données de manière appropriée en identifiant les relations entre ces données et les contraintes liées. Ils utilisent pour cela un SGBD.
  • schéma interne (SI) pour les administrateurs
    C'est l'implémentation physique du SGBD et de la BD.
    Les administrateurs installent et maintiennent le SGBD. Ils sont responsables de l'intégrité, de la sécurité, de la disponibilité des données ainsi que de la performance du SGBD.
Architecture_Ansi-Sparc.png

Cette architecture permet de :

  1. Séparer clairement le niveau physique (géré par les administrateurs), du niveau logique.
  2. Créer des vues utilisateur distinctes : Un utilisateur ne peut manipuler ou lire que la portion de la base de données auquel il a accès, et sous une forme ergonomique.
Code source (modification) :
Consulter le guide
sur Wikipédia.
Écrire du HTML
Question 1/4 : ✓
30s Mémorisation 0x - Réussite 0/0

Construction de la BD avec le langage SQL

Création d'une base de données sur un serveur MySQL

Installé sur un serveur, un SGBD traditionnel (Oracle, MySQL, MariaDB, etc) est capable de manipuler plusieurs bases de données et de fournir des accès aux utilisateurs. L'administrateur peut gérer les bases de données à l'aide des instructions SQL suivantes :

OpérationInstruction SQL
Création d'une BD CREATE DATABASE nom_base CHARACTER SET 'utf8'
Suppression d'une BDDROP DATABASE nom_base
Utilisation d'une BDUSE nom_base

Remarques :

  • Par convention, les mots clés sont écrits en majuscules.
  • La création ou la suppression d'une base nom_base est assez simple. Le codage de caractères le plus répandu est UTF8, et celui-ci est précisé lors de la création. Il existe des exceptions où l'on va utiliser de l'ASCII simple, ou de l'UTF32. Les bases de données contenant des caractères très spéciaux ne sont pas au programme. Pour utiliser une base de donnée, on doit l'ouvrir (USE).

Création d'une base de données SQLite3 en utilisant Python

Nous allons utiliser la bibliothèque sqlite3, qui permet d'utiliser une base de données légère directement sous Python (sans avoir à installer de serveur dédié).
Références :

On importe sqlite3 ; un connecteur permet de manipuler le fichier .db contenant la base. Le curseur permet de donner des instructions au connecteur : 
import sqlite3

# S'il n'existe pas encore, un fichier .db sera créé lors de la connexion.
NOM_BASE = "ma_base_sqlite.db"

# L'objet «connecteur» permet de se connecter à la base (fichier .db).
connecteur = sqlite3.connect(NOM_BASE)

# Un objet «curseur» permet d'exécuter des instructions en langage SQL, par l'intermédiaire du connecteur.
curseur = connecteur.cursor()

# L'instruction suivante affiche les noms des tables de la base de données :
reponse = curseur.execute("SELECT name FROM sqlite_schema WHERE type='table' ;").fetchall()
print(reponse)

# ...

# On termine en fermant la connexion :
connecteur.close()
Utiliser ce script Python pour créer une base de donnée «base01» ;
On utilise :
  • curseur.execute("instruction_sql") pour exécuter une seule instruction SQL ;
  • curseur.executemany("instruction_sql", liste) pour exécuter plusieurs instructions dépendant d'une liste fournie en argument.
  • curseur.executescript("script_sql") pour exécuter un script d'instructions SQL. Voici des modalités d'implémentation : 
    mon_script_sql_1 = """
-- première possibilité : utiliser les triples quotes permet d'écrire
-- un script sql sur plusieurs lignes directement dans le script python.
"""
reponse_1 = curseur.executescript(mon_script_sql_1).fetchall()

# seconde possibilité : lire un fichier texte .sql
mon_script_sql_2 = ""
with open("mon_fichier.sql") as f:
    mon_script_sql_2 = f.readlines()

reponse_2 = curseur.executescript(mon_script_sql_2).fetchall()"

Construction des tables avec le langage SQL

Création d'une table : syntaxe globale

CREATE TABLE nom_table (
  nom_attribut_1 son_type_1, -- cf tables des types plus loin
  nom_attribut_2 son_type_2 eventuelle_contrainte,
  ...
  Contrainte_d'integrite_1(nom_attribut_concerné_1, attribut_concerné_2 options)
    -- cf table des contraintes plus loin
  ...
);
 Créer une table dans la BD. 
DROP TABLE nom_table
 Supprimer une table de la BD.

C'est à la création de la table que l'on précise les attributs, leurs types et les contraintes. Le choix d'un type à un impact important sur la taille de la base de données et sa performance tandis que le choix des contraintes participe à maintenir l'intégrité des données.

On peut appliquer une contrainte (par exemple : NOT NULL directement sur l'attribut lors de sa déclaration ou regrouper toutes les contraintes à la fin en les appliquant à plusieurs attributs. L'important est d'être clair, pour faciliter la lecture du script par d'autres développeurs, et organisé pour ne rien oublier.

Les types MySQL et SQLite

Les types de données MySQL sont bien plus nombreux que les types de variable que vous connaissez en python. Cette grande variété sert à optimiser la taille d'une base de données.

NULL désigne le vide («non rempli»).

Types Entiers

TypeMinMaxTaille en octet
INT ou INTEGER-214748364821474836474
TINYINT -128 127 1
TINYINT UNSIGNED 0 255 1
SMALLINT -3276832767 2
SMALLINT UNSIGNED 0 65535 2
MEDIUMINT -8388608 83886073
MEDIUMINT UNSIGNED 0 167772153
INT UNSIGNED ou INTEGER UNSIGNED042949672954
BIGINT-922337203685477580892233720368547758078
BIGINT UNSIGNED0184467440737095516158
Remarque : MySQL n'a pas de type booléen propre : On peut utiliser BOOL mais cela revient à utiliser TINYINT(1). Les valeurs suivantes : true, false, TRUE, FALSE, True, False; sont interprétées respectivement comme 1, 0, 1, 0, 1, 0.

Types Nombres décimaux

TypeMin négatifMax négatif0Min positifMax positifTaille en octet
FLOAT-3.402823466E+38-1.175494351E-3801.175494351E-383.402823466E+384
DOUBLE-1.7976931348623157E+308-2.2250738585072014E-30802.2250738585072014E-3081.7976931348623157E+3088

Types Texte

Les textes ou chaînes de caractères sont entre guillemets simples '...'. Pour écrire une apostrophe ' à l'intérieur d'une chaîne de caractères, on écrit '' (2 apostrophes).
TypeTaille en octet
CHAR(X) avec X le nombre de caractèresX (si chaque caractère est codé sur un seul octet)
255 au maximum
VARCHAR(X) avec X le nombre de caractères≤X (si chaque caractère est codé sur un seul octet)
65532 au maximum
TINYBLOB ou TINYTEXT≤255
BLOB données binaires brutes≤65535
TEXT texte codé selon la base (souvent utf8)≤65535
MEDIUMBLOB ou MEDIUMTEXT≤16777215
LONGBLOB ou LONGTEXT≤4294967295

Types Date

TypeMinMaxPrécisionTaille en octetReprésentation
DATE1 Janvier 100031 Décembre 9999jour3AAAA-MM-JJ
DATETIME1 Janvier 1000 00h00m00s31 Décembre 9999 23h59m59sseconde8AAAA-MM-JJ HH:MM:SS
TIME-838h59m59s838h59m59s5HH:MM:SS
YEAR19012155année4AAAA

Gestion des types par SQLite

SQLite utilise un système de typage moins rigide que MySQL : le type d'un enregistrement est associé à l'enregistrement lui-même et non à l'attribut qui le contient.
SQLite emploie un système de règles permettant d'assurer une bonne compatibilité avec les instructions MySQL, mais il faut savoir que ses types de données se résument à :
  • NULL Champ vide.
  • INTEGER : Entier (avec signe) de 1 à 8 octets (équivalent de TINYINT à BIGINT).
  • REAL : valeur en virgule flottante IEEE 8 octets (équivalent de FLOAT).
  • TEXT : Texte (codage de caractères régi par la base : UTF-8, UTF-16BE or UTF-16LE).
  • BLOB : Données binaires brutes.
Par exemple, une date typée DATETIME sera stockée, en pratique, sous un type sql>TEXT, sous la forme YYYY-MM-DD HH:MM:SS.SSS (suivant la norme ISO8601).
Lors de l'écriture des scripts, on utilisera les types MySQL. Si une incompatibilité manifeste apparaît, on s'adaptera en utilisant les types utilisés par SQLite.
Code source (modification) :
Consulter le guide
sur Wikipédia.
Écrire du HTML
Question : ✓
30s Mémorisation 0x - Réussite 0/0

Ajout des contraintes d'intégrité

Une contrainte d'intégrité est une règle qui définit la cohérence d'une donnée ou d'un ensemble de données au sein d'une base de données, afin de garantir leurs pertinence et leur validité.

nom_attribut NOT NULLl'attribut ne peut pas avoir la valeur NULL (être vide)
CHECK (nom_attribut BETWEEN a AND b) contrainte de domaine : ici, avoir des valeurs entre a et b
UNIQUE (nom_attribut_1, ..., nom_attribut_n)les n-uplets d'attributs doivent être uniques
PRIMARY KEY (nom_attribut_1, ..., nom_attribut_n) contrainte de relation : les attributs sont mis en clé primaire
FOREIGN KEY (nom_attribut)
REFERENCES nom_table(nom_attribut)
ON DELETE CASCADE ON UPDATE CASCADE
 contrainte de référence : crée une clé étrangère
les occurences sont mises à jour en cas de modification/suppression dans l'autre table
⚠ à écrire sur une même ligne en SQLite  
Sans les remplir, construire les trois tables suivantes en langage SQL.
Remarques :
  • À vous de choisir judicieusement les types de chaque attribut !
  • Dans les noms d'attributs ou de table, on utilise des underscores _ et pas d'espaces !
  • N'oubliez pas les clés étrangères !
Schémas relationnels :
  • employés(employé_id, prénom, nom, naissance, sexe, salaire, #succursale_id, #superviseur_id)
  • succursales(succursale_id, ville, #manager_id)
  • fournisseurs(#succursale_id, fournisseur, fourniture)
Rappel : Pour chaque table, la clé primaire est soulignée et les clés étrangères sont précédées d'un dièse #. 
CREATE TABLE employés (
  employé_id INT NOT NULL,
  prénom VARCHAR(255),
  nom VARCHAR(255),
  naissance DATE,
  sexe VARCHAR(1),
  salaire INT,
  succursale_id INT,
  superviseur_id INT,
  PRIMARY KEY (employé_id)
  FOREIGN KEY (succursale_id) REFERENCES succursales(succursale_id)
  FOREIGN KEY (superviseur_id) REFERENCES employés(employé_id)
) ;


CREATE TABLE succursales (
  succursale_id INT NOT NULL,
  ville VARCHAR(255),
  manager_id INT,
  PRIMARY KEY (succursale_id)
  FOREIGN KEY (manager_id) REFERENCES employés(employé_id)
) ;


CREATE TABLE fournisseurs (
  succursale_id INT NOT NULL,
  fournisseur VARCHAR(255) NOT NULL,
  fourniture VARCHAR(255),
  PRIMARY KEY (succursale_id, fournisseur)
  FOREIGN KEY (succursale_id) REFERENCES succursales(succursale_id)
) ;

Insertion, mise à jour, suppression de valeurs dans une table

INSERT INTO table_à_remplir (nom_attribut1, nom_attribut2, ...)
VALUES (valeur1, valeur2, ...) ;
pour insérer des données dans la table
INSERT INTO table_à_remplir
VALUES (valeur1, valeur2, ...);
Si vous ajouter des valeurs à toutes les colonnes de la table, il est possible de simplifier l'instruction en omettant le nom des attributs. Attention cependant à bien respecter l'ordre de ces attributs tels qu'ils ont été créés dans la table.
UPDATE table_à_modifier
SET
modification_1,
  ...
modification_n
WHERE condition
pour modifier des données ;
par exemple : on peut remplacer modification_1
par nom=UPPER(nom) pour mettre les noms en majuscule ;
la condition est optionnelle. 
DELETE FROM table_à_modifier
WHERE condition ;
 pour supprimer des données ;
⚠ la condition est essentielle !
Toutes les lignes seront supprimées sinon.

Remarque : Les conditions s'écrivent de la même manière qu'en python, sauf le test d'égalité qui est un simple = (pas d'ambiguïté en SQL). Les conjonctions AND, OR, NOT, ... sont en majuscules.

Les fichiers CSV ci-dessous contiennent toutes les données à injecter dans la BD. Écrire la requête SQL permettant d'injecter au moins un enregistrement dans chaque table.

Données : employes.csv ; succursales.csv ; fournisseurs.csv

Schémas relationnels :
  • employés(employé_id, prénom, nom, naissance, sexe, salaire, #succursale_id, #superviseur_id)
  • succursales(succursale_id, ville, #manager_id)
  • fournisseurs(#succursale_id, fournisseur, fourniture)
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (100, 'Bruce', 'Salmons', '1967-04-10', 'M', 118000, 1, 100);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (101, 'Mirabelle', 'Beauchesne', '1969-09-17', 'F', 75000, 3, 100);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (102, 'Marius', 'Mikkelsen', '1993-05-11', 'M', 70000, 2, 103);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (103, 'Samawah', 'Hadad', '1979-02-24', 'F', 80000, 2, 100);
INSERT INTO employés (employé_id, prénom, nom, naissance, sexe, salaire, succursale_id, superviseur_id)
VALUES (104, 'Andy', 'Bernard', '2000-01-06', 'M', 62000, 3, 101);

INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (2, 'Hooper', 'papier');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (2, 'Kurihara', 'stylos');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Nikolic', 'papier');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (2, 'Comejo', 'formulaires');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Kurihara', 'stylos');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Hooper', 'papiers');
INSERT INTO fournisseurs (succursale_id, fournisseur, fourniture)
VALUES (3, 'Ulyanova', 'formulaires');

INSERT INTO succursales (succursale_id, ville, manager_id)
VALUES (1, 'Paris', 100);
INSERT INTO succursales (succursale_id, ville, manager_id)
VALUES (2, 'Lyon', 103);
INSERT INTO succursales (succursale_id, ville, manager_id)
VALUES (3, 'Marseille', 101);
  1. Écrire la requête SQL permettant de passer tous les noms en majuscule de la table employés.
    UPDATE employés SET nom=UPPER(nom) ;
  2. Écrire la requête SQL permettant d'augmenter le salaire de 10% pour toutes les femmes de la table employés.
    UPDATE employés SET salaire=salaire*1.1 WHERE sexe='F' ;

Modificatons structurelles

Modification, suppression d'une table et/ou de clés étrangères

Attention : Avec SQLite, il est impossible de modifier une table pour insérer une clé étrangère. Les clés étrangères doivent être crées au niveau de la déclaration des tables. 

ALTER TABLE Table_à_modifier
ADD nom_nouvel_attribut son_type contraintes_éventuelles ;
 pour ajouter un attribut
ALTER TABLE Table_à_modifier
MODIFY nom_attribut son_nouveau_type nouvelles_contraintes ;
 pour modifier le type ou les contraintes d'un attribut
ALTER TABLE Table_à_modifier
CHANGE nom_attribut son_nouveau_nom ;
 pour modifier le nom d'un attribut
ALTER TABLE Table_à_modifier
DROP nom_attribut ;
 pour supprimer un attribut
ALTER TABLE Table_à_modifier
ADD FOREIGN KEY (nom_clé) REFERENCES Autre_table(nom_attribut) ;
 pour créer une clé étrangère
Attention : impossible avec SQLite.
ALTER TABLE Table_à_modifier
DROP FOREIGN KEY (nom_clé) ;
 pour supprimer une clé étrangère. Attention : impossible avec SQLite.
DROP TABLE nom_table ;
 pour supprimer une table : ⚠ MySQL exige d'effacer les clés étrangères avant, pas SQLite
Code source (modification) :
Consulter le guide
sur Wikipédia.
Écrire du HTML
Question 1/3 : ✓
30s Mémorisation 0x - Réussite 0/0

Sécurité des transactions

Les modifications présentées dans le tableau ci-dessus étant potentiellement dangereuses pour la conservation des données, les SGBD implémentent un système de sauvegarde BEGIN TRANSACTION / ROLLBACK / COMMIT :
  • BEGIN TRANSACTION crée un point de sauvegarde ; c'est une pratique utile à effectuer avant d'opérer une modification importante ou destructrice dans une base de données.
  • ROLLBACK permet d'annuler toutes les modifications effectuées depuis BEGIN TRANSACTION.
  • COMMIT permet d'enregistrer définitivement les modifications courantes dans la base.
Attention : SQLite a vocation à utiliser un système d'autocommit dans les versions récentes. Comme d'habitude dans ce chapitre, avec SQLite, privilégier la syntaxe MySQL autant que possible.
Recopier le code ci-dessous à la suite de votre script python.
Expliquer l'utilité des instructions BEGIN TRANSACTION et ROLLBACK. 
curseur.execute("BEGIN TRANSACTION ;")
curseur.execute("DROP TABLE employés ;")
reponse = curseur.execute("SELECT name FROM sqlite_schema WHERE type='table' ;").fetchall()
print("Après DROP TABLE : ", reponse)
curseur.execute("ROLLBACK ;")
reponse = curseur.execute("SELECT name FROM sqlite_schema WHERE type='table' ;").fetchall()
print("Après ROLLBACK : ", reponse)
La ligne 1 et 5 permettent d'éviter que la destruction de la table employés ne soient définitive.

3.5.3. Activation des contraintes de référence (clés étrangères)

⚠ Avec SQLite, les clés étrangères doivent être activées par PRAGMA foreign_keys = ON ; ; elles ne le sont pas par défaut 🚮.
  1. Après avoir activé les clés étrangères, modifier la succursale d'un employé en lui affectant une id de succursale qui n'existe pas (par exemple 5) et observer le message d'erreur.
    UPDATE employés SET succursale=5 WHERE employé_id='100' ; renvoie sqlite3.IntegrityError: FOREIGN KEY constraint failed.
  2. Aurait-il été possible de remplir les tables (données données par les 3 fichiers .csv) en ayant activé les clés étrangères avant ?
    C'est possible mais pas en entrant les données dans l'ordre initial : en effet, par exemple : le superviseur de l'id 102 ayant une id de 103, il aurait fallu créer l'id 103 avant la 102 à cause de la clé étrangère (intra-table) liant les id des superviseurs aux id des employés.

Consultation de la BD avec le langage SQL

4.1. L'instruction SELECT et les fonctions d'agrégation

Elle permet d'afficher des résultats de manière approfondie.

SELECT nom_attribut_1, nom_attribut_2, ...
FROM nom_table
WHERE condition
ORDER BY nom_attribut_1 ASC, nom_attribut_2 DESC;
 Affiche une liste de résultats
concernés par une condition (optionnelle)
classés dans un certain ordre (ascendant : ASC ou descendant : DESC)
SELECT * FROM ... permet d'afficher tous les attributs de la table 
SELECT DISTINCT nom_attribut_1, nom_attribut_2, ...
FROM Table ;
 Le mot-clé DISTINCT permet d'éviter les doublons dans la liste affichée
SELECT CONCAT(nom_attribut_1, nom_attribut_2) AS résumé
FROM nom_table ;
 Le mot-clé CONCAT(a,b) permet de formater les résultats en concaténant 2 champs ou 1 champ et 'du texte' (on peut l'utiliser plusieurs fois : CONCAT(CONCAT(a,b),c))
On dispose aussi des fonctions, dites d'agrégation, suivantes :
COUNT() Compte le nombre d'enregistrements
MAX() et MIN() Récupèrent le max et le min sur un ensemble d'enregistrements
SUM() et AVG() Récupèrent la somme et la moyenne sur un ensemble d'enregistrements
Construire la requête SQL permettant d'obtenir :
  1. la liste des employés nom prénom triés par ordre alphabétique
    SELECT CONCAT(CONCAT(nom,' '), prénom) AS personne FROM employés ORDER BY personne ASC ;
  2. le salaire moyen de l'entreprise
    SELECT AVG(salaire) FROM employés ;
  3. le salaire moyen des hommes dans l'entreprise
    SELECT AVG(salaire) FROM employés WHERE sexe='M' ;
  4. le salaire moyen des femmes dans l'entreprise
    SELECT AVG(salaire) FROM employés WHERE sexe='F' ;
  5. le nombre de superviseurs
    SELECT COUNT(DISTINCT superviseur_id) FROM employés ;
Code source (modification) :
Consulter le guide
sur Wikipédia.
Écrire du HTML
Question 1/3 : ✓
30s Mémorisation 0x - Réussite 0/0/2

4.2. Produit cartésien

Définition : Si X et Y sont deux ensembles, on note X×Y l'ensemble de tous les couples de valeurs (x,y) ou x est un élément de X et y un élément de Y.

Exemple : Pour X={a,b,c} et Y={u,v}, on a X×Y={(a,u), (b,u), (c,u), (a,v), (b,v),(c,v)}.

Remarque : Un produit cartésien peut être représenté par un tableau :

Éléments de X×Y :

X×Yuv
a(a,u)(a,v)
b(b,u)(b,v)
c(c,u)(c,v)

Remarques :
  • Si X a n éléments et Y en a m, alors X×Y a n×m éléments (ce qui justifie l'utilisation du symbole × pour écrire le produit cartésien de deux ensembles ; d'ailleurs Y×Y se note aussi Y2, et on a Y2={(u,u),(u,v),(v,u),(v,v)} ).
  • On peut étendre à X×Y×Z (triplets), à un nombre infini d'ensembles...
  • ℝ est une droite, ℝ2 est un plan (dimension 2), ℝ3 un espace (dimension 3), ...
  1. Exécuter la requête suivante : 
    SELECT DISTINCT e.nom,e.prénom,s.ville
FROM employés AS e, succursales AS s ;
  2. À quoi sert le mot DISTINCT ?
    Le mot DISTINCT permet d'éviter l'affichage de données en double (doublons).
  3. DISTINCT peut il être remplacé par UNIQUE ?
    Le mot UNIQUE s'applique à un attribut et permet d'interdire que deux enregistrements possèdent la même valeur pour cet attribut ; il s'agit d'une contrainte de domaine, on l'utilise lors de la déclaration des tables.
    Bien que les mots «distinct» et «unique», leurs signification en SQL sont bien différentes. Il y a quand même un lien : si UNIQUE est appliqué à un attribut affiché plus tard avec SELECT, on sait qu'il n'y aura pas de doublons...
  4. Est-il utile ici ?
    Il est inutile dans ce cas car toutes les lignes affichées par SELECT sont déjà différentes entre elles.
  5. À quoi sert le mot AS dans cet exemple ?
    AS permet de produire des «alias», c'est à dire donner un autre nom à une entité ; en pratique, on l'utilise souvent pour raccourcir des noms, ce qui permet une écriture de la requête plus légère et plus facilement compréhensible.
  6. Expliquer le résultat donné par cette requête.
    SELECT affiche ici les attributs des enregistrements (nom, prénom , ville) pris dans le produit cartésien e×s (5×3=15 lignes).
  7. Comparer ce résultat avec celui donné par la requête suivante : 
    SELECT DISTINCT e1.nom,e2.prénom,s.ville
FROM employés AS e1, employés AS e2, succursales AS s ;
    SELECT affiche ici les attributs des enregistrements (nom, prénom , ville) pris dans le produit cartésien e1×e2×s soit 5×5×3=75 lignes.

4.3. SELECT ... LIKE : repérer un motif

On dispose, en SQL, d'un opérateur LIKE permettant de repérer des motifs dans les chaînes de caractères (selon le principe des expressions régulières mais la syntaxe est différente).

Exemple : 

SELECT nom
FROM employés
WHERE nom LIKE 'B%'
  • LIKE '%a' les valeurs affichées se terminent par le caractère 'a'.
  • LIKE 'a%' les valeurs affichées débutent par le caractère 'a'.
  • LIKE '%a%' les valeurs affichées contiennent le caractère 'a'.
  • LIKE 'pas%s%i%mal' les valeurs affichées commencent par 'pas', suivi de 's' puis 'i' et se terminent par 'mal'.
Remarques : les «jokers» en SQL :
  • % désigne un nombre inconnu de caractères ; ce nombre peut très bien être nul.
  • _ remplace un seul et unique caratère.
Code source (modification) :
Consulter le guide
sur Wikipédia.
Écrire du HTML
Question 1/3 : ✓
30s Mémorisation 0x - Réussite 0/0
Donner la réponse à la requête : 
SELECT DISTINCT e.prénom
FROM employés AS e
WHERE UPPER(e.nom) LIKE '%B%_'
Comment interpréter le %_ à la fin de la requête ?
Andy, Mirabelle
LIKE '%B%_' recherche les prénoms qui contiennent une lettre B ou b (on a utilisé UPPER), mais pas seulement en dernier (à cause du _, mais ce dernier pourrait remplacer un second «b»...).

Requêtes sur plusieurs tables : jointures

On appelle jointure de deux tables (table1 JOIN table2), l'opération consistant à rapprocher, selon une condition que l'on choisit (ON condition), des tuples issus de ces deux tables vérifiant la condition donnée.
Exemple : 
SELECT nom, fournisseur, fourniture
FROM employés JOIN fournisseurs
ON employés.succursale_id = fournisseurs.succursale_id
Écrire une requête permettant de lister les couples de (nom du manager, fournisseur) pouvant être en contact. 
SELECT employés.nom, fournisseurs.fournisseur
FROM employés JOIN succursales
ON employés.employé_id = succursales.manager_id
JOIN fournisseurs
ON succursales.succursale_id = fournisseurs.succursale_id
Code source (modification) :
Consulter le guide
sur Wikipédia.
Écrire du HTML
Question 1/8 : ✓
1 min Mémorisation 0x - Réussite 0/0/4

5. Avec python : pilotage d'un serveur MySQL ; vers l'analyse de données (bonus)

  • La bibliothèques mysql permet de «piloter» un serveur MySQL installé sur la machine, avec Python (à l'aide d'un «connecteur», la syntaxe est proche de celle employée par SQLite). Références à consulter :
  • Les bases de données sont souvent exploitées en utilisant une bibliothèque d'analyse de données, comme pandas (combinée à des bibliothèques mathématiques pour le calcul : scipy, ...) :
    Référence : la documentation.