Introduction
Ce cours permet à l'étudiant de s'initier aux bases de données. Il vise à donner à l'étudiant une base suffisante pour qu'il puisse discuter, comprendre et appliquer les notions de conception et d'utilisation des BD.
Qu'est-ce qu'une BD?
Une base de données (anglais database) est un conteneur informatique permettant de stocker - le plus souvent dans un même lieu - l'intégralité des informations en rapport avec une activité. Une base de données permet de stocker et de retrouver un ensemble d'informations de plusieurs natures ainsi que les liens qui existent entre les différentes informations.
source: http://fr.wikipedia.org/wiki/Base_de_données
Dans la première partie de ce cours, vous utiliserez la BD SQLite. Elle a été choisie parce qu'elle est simple, gratuite, utilisée sur les mobiles (iphone, android, ...), et qu'elle est facile d'utilisation.
Il existe plusieurs autres bases de données relationnelles : PostgreSQL, MariaBD, MySql, Access, Microsoft SQL Server, Oracle, ...
Avantages d'une BD vs un fichier texte
Dans le cadre du cours 420-2N2-DM, vous avez développé une application utilisant des fichiers textes. Ces fichiers ont le désavantage d'être complexes à créer et à gérer. Vous avez utilisé une technique vous forçant à lire le fichier au complet en mémoire afin de traiter son information. Cette technique montre rapidement ses limites dès que le nombre d'enregistrements augmente.
Téléchargez le fichier automobile.sqlite sur LÉA dans la section Fichiers BD.
Je vous recommande fortement de vous créer un répertoire spécifique pour le cours, car vous aurez plusieurs fichiers à télécharger et à créer.
SQLite
Installation de SQLite
Téléchargez le fichier zip de www.sqlite.org/download.html à la section Precompiled Binaries for Windows.
Prenez le fichier sqlite-tools-win-xxx. Il s'agit d'un programme portable.
Dé-zippez le fichier dans un répertoire, et copiez le résultat dans un répertoire que vous retrouverez facilement.
Je vous recommande de créer un dossier C:\sqlite et de copier les fichiers du logiciel.
Dans le cadre du cours, nous n'allons utiliser que l'exécutable sqlite3.exe. Vous pouvez le déplacer du répertoire sqlite-tools-xxx directement dans un répertoire de votre %path%.
Pour mettre dans le path, ouvrez l'invite de commandes et inscrivez la commande ci-dessous.
setx /m PATH "%PATH%;C:\sqlite"
Redémarrez l'invite de commandes et tapez la commande ci-dessous pour vous assurez que le dossier est bien dans la liste. Ce sera le dernier.
echo %path%
Le logiciel sqlite3.exe sera accessible en tout temps à partir de l'invite de commande.
Ouverture à l'aide de l'interface texte
Démarrez l'invite de commandes.
Déplacez-vous dans le répertoire contenant le fichier automobile.sqlite
Inscrivez la commande ci-dessous
sqlite3 automobile.sqlite
Inscrivez: .table (notez le point . avant le mot table) (vous pouvez aussi inscrire .tables)
Cette commande, spécifique à sqlite, affiche toutes les tables se trouvant dans la base de données. (nous reviendrons à la définition d'une table plus tard).
Inscrivez la commande suivante:
SELECT * FROM Autos;
Inscrivez .q pour quitter.
Interface graphique SQLiteStudio
Vous pouvez télécharger SQLiteStudio sur SQLiteStudio.pl
Décompressez le fichier dans votre dossier C:\sqlite.
Pour démarrer l'application, double cliquez sur SQLiteStudio.exe. Choisissez Français pour la langue.
Sélectionnez Base de données/ajout d'une base de données et allez chercher la BD que vous venez d'installer
Sélectionnez Base de données/Connecter la base de données
Sélectionnez (double clique) la table Autos, et prenez l'onglet Données.
Interface texte de SQLiteStudio
Sélectionnez Outils/Ouvrir éditeur SQL
Sous l'onglet Requête inscrivez:
SELECT * FROM autos
et appuyez sur le triangle bleu (ou F9) pour exécuter la requête.
Vous pouvez quitter SQLiteStudio.
Interface de programmation
Il faut comprendre que les bases de données sont utilisées par des programmes. Elles servent à enregistrer l'information.
Voici un exemple en Javascript pour aller chercher des infos provenant de votre BD avec Node. Il n'est pas nécessaire de faire cet exercice.
Créez un dossier C:\Projets\3N3\Cours1\DemoJS.
Copiez le fichier automobile.sqlite.
Installez la librairie sql.js à partir de npm.
npm install sql.js
Créez le fichier demo.js et copiez le code ci-dessous.
const fs = require('fs'); const initSqlJs = require('sql.js'); const filebuffer = fs.readFileSync('automobile.sqlite'); initSqlJs().then(function(SQL){ // Load the db const db = new SQL.Database(filebuffer); let res = db.exec("select * from Autos"); console.log(res[0]); });
Exécutez le script.
node C:\Projets\3N3\Cours1\DemoJS\demo.js
Conclusion
Pour ce cours, vous utiliserez l'interface texte afin de vous familiariser avec les commandes.
En tant que programmeurs, vous utiliserez la dernière technique afin d'intégrer la BD dans votre code, mais les autres techniques seront très utiles afin de vérifier que le code effectue les bonnes opérations.
À partir de la 4e session, vos applications de Web 4 et Native 3 utiliseront une base de données.
Concepts de base
Une BD contient des tables, composées de champs. Ces tables sont reliées entre elles par des associations identifiées à l'aide de champs ayant la caractéristique d'être des clés primaires ou secondaires.
Ces entités sont accessibles grâce au langage SQL (Structured Query Langage, prononcé "sé-ku-el" en anglais).
La BD est gérée par un logiciel spécialisé appelé le SGBDR (Système de Gestion de BD Relationnel, souvent abrégé à SGBD, ou en anglais RDBMS).
Dans le cas de SQLite, la BD est entreposée dans un seul fichier. Mais la plupart des autres gestionnaires vont utiliser des structures plus complexes. C'est ce que nous appellerons le schéma physique.
La représentation du contenu des tables se faisant habituellement de façon « tabulaire », les champs sont aussi appelés colonnes (column). Ils représentent un ensemble de valeur (le nom, le prix, ...)
Les champs représentant une «entité» sont regroupés en lignes (rows), souvent appelées enregistrements.
Afin d'identifier de façon unique chaque ligne, nous devons utiliser une clé (key). Cette clé peut être composée d'un ou de plusieurs champs. Elle est appelée la clé primaire. Elle permet de retrouver sans ambiguïté un enregistrement donné. Par conséquent les différentes valeurs d'une clef doivent être uniques au sein d'une table. Cette clé unique est souvent un identificateur numérique séquentiel portant souvent (mais pas forcément) le nom de id. Cette clé est utilisée pour créer des associations entre les tables à l'aide de clés étrangères (foreign keys).
Vous verrez plus tard comment créer des associations entre les tables.
Une base de données permet habituellement les 4 opérations suivantes :
Création, Lecture, Mise à jour, et Effacement. Vous entendrez souvent le terme anglais CRUD (Create, Read, Update,
Delete). Nous verrons comment exécuter ces 4 opérations au courant des prochaines semaines. Nous en avons déjà vu une :
la lecture se fait avec un SELECT.
Éléments de base de la syntaxe SQL
Clause : partie optionnelle ou obligatoire d'un ordre SQL (Select, Where, From, Join, ...)
Prédicat : expression logique pouvant prendre 3 valeurs : Vrai, Faux, Null.
Casse : L'écriture des ordres SQL n'est pas sensible à la casse des caractères. En revanche, l'écriture des valeurs de données est sensible à la casse.
Présentation : SQL est insensible à la césure comme à l'indentation. Vous pouvez donc utiliser le retour de chariot ou les tabulations pour présenter vos ordres SQL au mieux. Dans le cadre du cours, des points seront associés à la présentation des requêtes.
Commentaires : Vous pouvez soit commencer la ligne par un double tiret -- pour un commentaire sur une seule ligne ; ou utiliser /* */ pour les commentaires multilignes.
Chaîne de caractères : Toute chaîne de caractères, incluant les dates, doit être entourée d'apostrophe ' . Pour représenter une apostrophe dans une chaîne, il faut la doubler ''.
Liste : La virgule est utilisée comme séparateur dans les listes de valeurs.
Chiffre : Il ne doit pas y avoir d'espace entre les chiffres composant un nombre.
Séparateur décimal : Le séparateur est le point, et non pas la virgule.
Exposant : Pour préciser une puissance de 10 dans un réel, il faut utiliser la notation E
Cours sur le SQL en ligne
Vous pouvez vous référer à http://www.w3schools.com/sql/default.asp pour en savoir plus sur le SQL.
Le SQL de SQLite
Voici le lien vers la syntaxe complète du SQL utilisé par SQLite. Vous apprendrez à utiliser plusieurs de ces commandes au courant des prochains cours.
http://www.sqlite.org/lang.html
Il est à noter qu'il faut terminer chaque commande par un ;
Comme indiqué précédemment, les commandes ainsi que les noms de tables et colonnes sont « not case sensitive ». Par contre, les valeurs utilisées pour identifier de l'information sont « case sensitive ».
Par exemple, la commande
SELECT * FROM Autos;
peut s'écrire
seLecT * fRom AUTOs;
Mais le nom de l'auto, dans l'exemple suivant, doit être bien écrit :
SELECT * FROM Autos WHERE Nom = 'Audi';
Ce qui veut dire que la commande suivante ne retournera pas le même résultat :
SELECT * FROM Autos WHERE Nom = 'AUDI';
Formatage du select dans SQLite
L'affichage des résultats dans sqlite3 est assez difficile à lire.
Inscrivez la commande suivante:
SELECT * FROM autos;
1|Audi|52642|bleu 2|Mercedes|57127|gris 3|Skoda|9000|gris 4|Volvo|29000|noir 5|Bentley|350000|noir 6|Citroen|21000|blanc 7|Hummer|41400|jaune 8|Volkswagen|21600|bleu 9|Volkswagen|22000|gris
Afin d'améliorer cet affichage, vous pouvez utiliser les commandes suivantes:
.mode column
et
.header ON
Effectuez de nouveau le SELECT.
Id Nom Prix Couleur ---------- ---------- ---------- ---------- 1 Audi 52642 bleu 2 Mercedes 57127 gris 3 Skoda 9000 gris 4 Volvo 29000 noir 5 Bentley 350000 noir 6 Citroen 21000 blanc 7 Hummer 41400 jaune 8 Volkswagen 21600 bleu 9 Volkswagen 22000 gris
Beaucoup mieux n'est-ce pas :) ?
La projection de données : SELECT
Pour être en mesure de visualiser des données, il faut utiliser le SELECT.
Il est primordial que vous maîtrisiez cette commande à la perfection. Elle est la base de la construction d'applications utilisant les bases de données. Vous trouverez peut-être que vous ferez trop d'exercices avec le SELECT, mais cette commande doit devenir votre meilleur ami.
Dans le jargon relationnel, le SELECT correspond à l'opération de projection,, car elle permet de « projeter » les données d'une table dans une autre. En effet, le résultat d'un SELECT est une table temporaire en mémoire.
Voici la syntaxe complète de la commande :
http://www.sqlite.org/lang_select.html
Le langage SQL est de type déclaratif. Cela signifie que nous indiquons ce que nous voulons calculer (le quoi), mais pas la technique à utiliser pour le calculer (le comment). Un autre langage déclaratif que vous avez déjà appris est le HTML
Vous avez utilisé uniquement des langages impératifs (Java, C).
Le « comment » est géré par le gestionnaire de BD (SGBD), que nous étudierons plus tard.
La sélection de base
La requête la plus simple pouvant être faite sur une table est de demander un « dump » de toutes les colonnes de la table.
SELECT * FROM Autos;
Il est aussi possible de spécifier les colonnes que l'on veut afficher
SELECT Nom FROM Autos; SELECT Nom, Prix FROM Autos;
La clause WHERE
Cette clause permet de préciser quel(s) enregistrement(s) nous désirons extraire. Les valeurs numériques n'ont pas besoin de guillemets.
SELECT Nom, Prix FROM Autos WHERE Prix > 50000;
Si vous utilisez une valeur de texte dans la condition, cette valeur doit être entre guillemets simples.
SELECT * FROM Autos WHERE Nom = 'Audi';
Les opérateurs logiques
Il est possible d'utiliser des opérateurs logiques dans la condition (AND, OR, NOT)
Voici un premier exemple.
SELECT Nom, Prix, Couleur FROM Autos WHERE (Prix > 25000 AND Prix < 100000) OR Couleur = 'gris';
La clause where ci-dessus contient 2 conditions sur une simple ligne. Il est toujours possible de la comprendre, mais il est préférable d'utiliser la notation c-dessous pour afficher les conditions multiples.
SELECT Nom, Prix, Couleur FROM Autos WHERE ( Prix > 25000 AND Prix < 100000 ) OR Couleur = 'gris';
Un autre exemple sur une simple ligne.
SELECT Nom, Prix, Couleur FROM Autos WHERE (Prix > 25000 AND Prix < 100000) OR (Couleur = 'gris' AND Nom = 'Skoda');
Et l'équivalent en plusieurs lignes.
SELECT Nom, Prix, Couleur FROM Autos WHERE ( Prix > 25000 AND Prix < 100000 ) OR ( Couleur = 'gris' AND Nom = 'Skoda' );
En voici une dernière sur une simple ligne.
SELECT Nom, Prix, Couleur FROM Autos WHERE (Prix > 25000 AND Prix < 100000 AND (Couleur = 'jaune' OR Couleur = 'noir')) OR (Couleur = 'gris' AND Nom = 'Skoda');
Et l'équivalent en plusieurs lignes.
SELECT Nom, Prix, Couleur FROM Autos WHERE ( Prix > 25000 AND Prix < 100000 AND ( Couleur = 'jaune' OR Couleur = 'noir' ) ) OR ( Couleur = 'gris' AND Nom = 'Skoda' );
Dans cette dernière requête, il y a une sous-condition pour déterminer prendre uniquement les voitures jaune ou noir et qui ont un prix compris entre 25 000 et 100 000. En simple ligne, il est plus difficile de voir les niveaux des conditions.
La notation en plusieurs lignes sera utilisée pour ce cours.
Les opérateurs du WHERE
= égalité <> différent de (on peut aussi utiliser != ) > , < , >= , <= BETWEEN une valeur qui est entre les valeurs spécifiées LIKE rechercher pour un **pattern** (ATTENTION: case incensitive pour ASCII, mais case sensitive pour le reste) IN recherche dans l'ensemble des valeurs spécifiées
Exemples
SELECT * FROM Autos WHERE Prix BETWEEN 20000 AND 30000; SELECT * FROM Autos WHERE Couleur LIKE 'b%'; -- le % est le wildcard SELECT * FROM Autos WHERE Couleur IN ('blanc', 'noir');
Exercices sur l'opérateur WHERE
Voici une liste de requêtes à effectuer. Les solutions vous seront données à la fin du cours. En SQL, il peut avoir plusieurs solutions pour obtenir des données. Si votre solution est différente de la réponse officielle, ça ne veut pas dire qu'elle est incorrecte. Validez avec l'enseignant votre réponse en cas de doute.
- Lister les usagers dont le Nom est Lamothe
- Lister les usagers qui ont plus de 35 ans
- Lister les usagers dont la ville n'est pas de Drummondville
- Lister les usagers qui ont entre 25 et 35 ans (utilisez le AND)
- Lister les usagers qui ont entre 25 et 35 ans (utilisez le BETWEEN)
- Lister les usagers, dont le nom contient un 'a'
- Lister seulement le nom et le prénom des usagers qui viennent de Drummondville.
- Lister les usagers dont le prénom est soit Benoit ou Celine. (utilisez le IN)
- Lister les usagers dont le prénom est soit Benoit ou Celine. (utilisez le OR)
La clause DISTINCT
Cette clause permet d'éliminer les doublons dans le résultat d'une requête
SELECT Nom FROM Autos; -- (notez que Volkswagen est répété) SELECT DISTINCT Nom FROM Autos;
Pour être éliminée, la ligne au complet doit être un doublon.
SELECT Nom, Couleur FROM Autos; SELECT DISTINCT Nom, Couleur FROM Autos;
NOTE : la valeur NULL est considérée comme une valeur unique, c.-à-d. si une voiture aurait eu le Nom mis à Null, cette
valeur aurait été affichée.
La clause ORDER BY
Ne vous fiez jamais à l'ordre dans lequel les enregistrements sont retournés; à moins d'utiliser la clause ORDER BY.
Cette clause permet de trier les données en fonction d'une ou de plusieurs colonnes.
Il est possible de trier les données par une ou plusieurs colonnes.
SELECT * FROM Autos ORDER BY Prix; SELECT * FROM Autos ORDER BY Couleur, Prix;
Par défaut, le tri est ascendant. Si on ajoute la clause asc le tri sera ascendant; et desc le tri sera descendant
SELECT * FROM Autos ORDER BY Couleur, Prix DESC;
Exercices avec tri
- Listez les usagers dont le Nom est Lamothe, et triez-les par leur prénom.
- Listez tous les prénoms sans répétitions.
- Listez les usagers dont le nom ne contient pas un 'a', triés par leur âge.
N'hésitez pas si vous avez de la difficulté à valider avec l'enseignant.
Version originale par Benoit Desrosiers
Mise à jour 2022 par François St-Hilaire
Mise à jour 2023 par Pierre-Luc Boulanger