Bases de données relationnelles

Motivation

Afin de rendre ce cours aussi accessible que possible, j’ai choisi de ne pas demander d’avoir au préalable suivi un cours de bases de données. En effet, on peut très bien étudier les entrepôts de données sans avoir une connaissance approfondie de la théorie des bases de données.

Il est cependant essentiel de posséder des connaissances en matière de bases de données relationnelles. Il faut être capable d’effectuer des opérations simples comme la création et la mise à jour de tables. Il faut connaître les fondements du langage SQL. C’est le sujet de cet article.

Avertissement. Il est possible de faire un ou plusieurs cours entiers sur les bases de données relationnelles. Je ne vise ici qu’à vous donner les éléments essentiels nécessaires à la poursuite de ce cours.

Historique et notions élémentaires

Les bases de données relationnelles sont parmi les bases de données les plus répandues. Les systèmes de gestion de base de données (SGDB) Oracle, Microsoft SQL Server, MySQL, PostgreSQL, sont tous des bases de données relationnelles. Le langage de base de données le plus courant, SQL, est une expression du modèle relationnel.

Les bases de données relationnelles furent inventées par Edgar Codd dans les années 70. Leur importance vient du fait que malgré quarante ans de travaux de recherche et de développement, elles demeurent la référence en la matière.

Le concept de base dans les bases de données relationnelles est la table (ou relation). Une table est simple un tableau bidimensionnel comprenant plusieurs rangées et plusieurs colonnes. Toutes les rangées sont distinctes : il n’y a pas de doublon. Le résultat de toute requête sur cette table est parfois appelé une vue.

Voici un exemple de table :

Nom	N. A. S.	Salaire	Métier
Daniel	434432111	45 000	Plombier
Jean	431111442	45 000	Plombier

Certaines tables ont une colonne spéciale appelée clé primaire. Une telle colonne ne comportera aucun doublon de telle sorte qu’il y a une correspondance un à un entre cette valeur et la rangée en question. Par exemple, le numéro d’assurance sociale (N. A. S.).

Une base de données relationnelle comportera souvent plusieurs tables. Ainsi, dans l’exemple de la table précédente, on pourrait associer les noms aux numéros d’assurance sociale et les métiers aux salaires dans des tables secondaires. Ce processus de scission d’une table en plusieurs tables plus petites est appelé normalisation. L’intérêt d’avoir de plus petites tables est d’éviter la redondance, ce qui permet d’éviter des incohérences et de réduire la table de la base de données. Il faut cependant être capable de lier les tables entre elles. Pour y arriver, on utilise une clé externe. La fonction de la clé externe est de lier une rangée avec une autre rangée dans une autre bases de données.

Voici un exemple de normalisation de la table précédente :

N. A. S.	MétierID
434432111	1
431111442	1

Nom	N. A. S.
Daniel	434432111
Jean	431111442

MétierID	Salaire	Métier
1	45 000	Plombier

Dans ce cas, on a utilisé le numéro d’assurance sociale et MétierID comme clés externes. On peut reconstruire la table initiale par un processus appelé jointure.

Cependant, la normalisation n’a pas que des avantages. Par exemple, pour trouver quel est le salaire de Daniel, pas moins de 3 tables doivent être consultées. La jointure peut être un processus coûteux.

Les index

S’il fallait à chaque requête consulter l’ensemble de chacune des tables, les bases de données relationnelles ne pourraient pas fonctionner correctement lorsque les tables deviennent trop volumineuses. Heureusement, il est possible d’ajouter un index à chaque table, sur chaque colonne. La fonction de l’index est de faire correspondre les colonnes aux valeurs.

Laboratoire SQL avec SQLite

SQLite est un moteur de base de données relationnelle simplifié qui ne requiert aucune installation particulière. Bien qu’il ne dispose pas de toute la puissance de ses cousins plus avancés, il est parfaitement bien adapté lorsque le volume des tables n’est pas élevé.

Par ailleurs, SQLite est disponible pour les principaux systèmes d’exploitation incluant Windows, Linux et Mac OS. Si
vous utilisez Windows, vous pouvez charger SQLite directement à partir du site du cours :

SQLite

Pour l’installer, il suffit d’ouvrir l’archive ZIP et d’extraire le fichier sqlite3.exe qui s’y trouve. Ensuite, il faut aller en ligne de commande. Sous Windows, cliquez sur « Démarrer », puis sur « exécuter » et tapez « cmd » pour ouvrir une console.

Ensuite, il est pratique de se rendre au dossier dans lequel vous avez déposé l’archive. Par exemple, s’il s’agit du dossier c :\Mes Fichiers, tapez ceci en ligne de commande :

cd "c:\Mes Fichiers"

Pour lancer SQLite, tapez ensuite :

sqlite3 mabase

Cette commande va créer une base de données dans le fichier « mabase ». Vous devriez voir quelque chose qui ressemble à ceci sur votre écran :

SQLite version 3.x.x

Enter ".help" for instructions

sqlite>

Pour quitter la session, vous pouvez taper « .quit ».

SQL comme norme ?

Le SQL est en fait une famille de langages. On ne peut pas s’attendre à ce qu’une instruction SQL qui fonctionne sous SQL Server fonctionne aussi sous SQLite. Il existe bel et bien des normes ISO spécifiant le langage SQL, mais aucun moteur de base de données n’est conforme à la norme en question.

La casse en SQL ?

SQL ne tient pas compte de casse de caractères. Ainsi, la commande « select x in a » est équivalente à la commande « SELECT x IN a ». Il est habituel d’utiliser des majuscules pour distinguer les mots réservés en SQL. Je n’adopte pas cette convention dans ce cours PARCE QUE JE N’AIME PAS ABUSER DES MAJUSCULES.

Le SQL pas à pas

La spécification du langage SQL supporté par SQLite est disponible en ligne. Au lieu de lire cette spécification aride, je vous suggère de suivre pas à pas le tutoriel qui suit. À chaque étape, refaites les instructions pour vous assurer de bien comprendre.

On peut utiliser SQL pour faire des calculs simples. Par exemple, on peut additionner deux nombres :

sqlite> select 1+2;

3

Comme vous pouvez le constater, les commandes SQL se terminent par un point-virgule (« ; »). SQLite ne permet pas de définir des variables, contrairement à MySQL, par exemple, où l’on peut définir une variable (que vous préfixez par le caractère @) et sa valeur comme ceci :

mysql> set @x=1;

Pour expliquer votre code, vous pouvez laisser des commentaires comme ceci :

sqlite> -- un commentaire 

sqlite> /* un commentaire*/

Les commentaires seront ignorés par le moteur et ne servent qu’aux personnes qui lisent vos instructions.

Normalement, sous MySQL par exemple, il faudrait créer une base de données avant
de pouvoir créer une table, avec une commande telle que « create base_de_donnees ».
Il faudrait ensuite sélectionner la base de données avec la commande « use base_de_donnees ». Avec SQLite, le nom de bases de données par défaut est spécifié lors du lancement de la commande. On peut cependant charger ou décharger de nouvelles bases de données avec les instructions « attach monficher as mabase » et « detach mabase ». On ne peut pas utiliser deux fois le même nom pour deux bases de données différentes.

Sous SQLite, on peut faire afficher les bases de données stockées avec
la commande « .databases ».

Les noms de bases de données, de tables et de colonnes doivent respecter
certaines contraintes :
- On suggère de ne pas utiliser plus de 15 caractères puisque sous SQLite, les caractères supplémentaires sont ignorés.
D’autres moteurs (dont MySQL) permettent plus de caractères.
- Le premier caractère doit être une lettre.
- Les accents sont permis sous SQLite, mais d’autres moteurs, dont MySQL, ne les reconnaissent pas.
- Les chiffres ainsi que les caractères # et $ sont autorisés.
- On ne peut pas utiliser certains mot-clés réservés dont select, where, etc.
- La casse est ignorée : abc est considéré comme identique à ABC.

Sous MySQL, on peut effacer une base de données avec la commande « drop base_de_donnees ». Sous SQLite, il suffit d’effacer le fichier correspondant sur le disque.

Une base de données relationnelle contient une ou plusieurs tables. Les données d’une base relationnelle sont stockées dans des tables. Les tables sont identifiées par leur noms et composées de colonnes et de rangées. Les colonnes ont un nom, un type de données, etc. Les rangées contiennent les enregistrements de ces colonnes.

SQLite permet au moins 4 types de données :

– INTEGER : pour stocker des des entiers.
– REAL : pour stocker des nombres à virgule flottante.
– TEXT : pour stocker des chaînes de caractères.
– BLOB : pour stocker tout autre forme de données incluant des images, des vidéos, etc.

Pour indiquer qu’une valeur est inconnue, on utilise la valeur spéciale NULL. Il ne faut pas la confondre avec la valeur nulle (0).

Voici la syntaxe de base qui permet de créer une table :

create table "nom_de_la_table" ("colonne1" "data type", "colonne2" "data type", "colonne3" "data type");

Nous allons créer notre première table :

create table etudiant (id integer, nom text, prenom text, age integer);

Notez bien que le nom de chaque colonne doit être unique. L’instruction suivante donnerait une erreur :

create table etudiant (id integer, ID integer, nom text, prenom text, age integer);

On peut cependant utiliser un même nom de colonne dans plusieurs tables.

La table est créée dans la base de donnée par défault. Si on veut spécifier la base de données, il faut préfixer le nom de la table : « mabase.etudiant ». De cette manière, on peut manipuler des tables qui appartiennent à plusieurs bases de données en même temps.

On peut ensuite vérifier que la table a bien été créée :

sqlite> .tables  /* sous MySQL, on ferait show tables */

etudiant

On peut ensuite interroger la bases de données sur le contenu de la table :

 sqlite> select * from etudiant;

Bien sûr, il n’y a rien encore dans notre table !

Avant de continuer, nous allons effacer la table et déclarer la colonne id comme étant
une clé primaire :

sqlite> drop table etudiant;

sqlite> create table Etudiant (id integer, nom text, prenom text, age integer, primary key(id));

Cette déclaration a plusieurs avantages. D’une part, SQLite ne nous permettra pas d’introduire de doublons dans la colonne id, et, d’autre part, il sera beaucoup plus rapide de sélectionner des rangées par leur valeur sur la colonne déclarée comme clé primaire.

Notez que nous avons changé la casse du nom de la table (etudiant à Etudiant), mais pour SQL, il s’agit toujours du même nom. Vérifions maintenant que la table correspond bien à notre instruction :

sqlite> SELECT * FROM sqlite_master; /* sous MySQL, on ferait describe(Etudiant); */

table|Etudiant|Etudiant|2|CREATE TABLE Etudiant (id integer, nom text, prenom text, age integer, primary key(id))

Il faut mettre entre guillemets ou apostrophes les valeurs de chaînes de caractères : ’...’ ou "...". Les caractères de saut de ligne, de retour de charriot, de tabulation, d’apostrophe, de guillement, \, % et _ doivent être précédés du \ lorsqu’on les transmet à la base de données : \n, \r , \t, \’, \", \, \%, _, etc.

Le peuplement d’une table se fait à l’aide de la syntaxe suivante :

insert into "une_table" (première_colonne,...,dernière_colonne) values (première_valeur,...,dernière_valeur);

Pour peupler la table "etudiant", nous pouvons utiliser les instructions suivantes :

insert into etudiant (id,nom,prenom,age) values (10,'toto','titi',20);

insert into etudiant (id,nom,prenom,age) values (5,'dupond','dupont',21);

insert into etudiant (id,nom,prenom,age) values (11,'martin','jean',20);

insert into etudiant (id,nom,prenom,age) values (13,'martin','pierre',22);

L’instruction « insert » ne vous permet pas de remplacer une rangée. Voici l’erreur
qui se produit si on tente de le faire :

sqlite> insert into etudiant (id,nom,prenom,age) values (13,'martine','pierre',22);

SQL error: PRIMARY KEY must be unique

Nous verrons plus loin que la commande « update » est utilisée à cette fin.

La commande de sélection des données est une requête utilisée pour interroger une base de données et rechercher des données qui correspondent à des critères de recherche spécifiés. Voici une commande de sélection simple :

select "colonne1" [,"colonne2", etc.] from "nom_de_table" [where "conditions"]; [] = optionnel

Les noms des colonnes qui suivent le mot-clé « select » déterminent les colonnes qui devront être retournées dans le résultat. Vous pouvez sélectionner autant de colonnes que vous voulez. Vous pouvez aussi utiliser "*" pour sélectionner toutes les colonnes.
Le nom de la table qui suit le mot-clé « from » spécifie la table qui doit être interrogée. La clause « where », optionnelle, spécifie quelles sont les valeurs de données ou lignes qui doivent être retournées et affichées. Les conditions de sélection utilisées dans
la clause « where » sont : =, >, <, >=, <=, ><> et like. Like est utilisé pour rechercher des données qui ressemblent à un motif que vous spécifiez. Le signe "%" peut être utilisé pour apparier n’importe quels caractères qui peuvent apparaître avant ou après les caractères données. Par exemple les mots qui se terminent par un "s" peuvent être recherchés à l’aide de la condition « like mot="%s" ». Le caractère « _ » peut remplacer n’importe quel caractère : « like mot="d_g" » sélectionnera tout autant dog que dag.

Plusieurs conditions simples de la clause « where » peuvent être combinées à l’aide des opérateurs logiques and, or et not pour construire des clauses plus complexes.

Pour afficher le contenu de la table "etudiant", nous pouvons utiliser la requête suivante :

sqlite> select * from etudiant;

5|dupond|dupont|21

10|toto|titi|20

11|martin|jean|20

13|martin|pierre|22

Si on veut afficher les nom et prénom des étudiants dont l’âge est supérieur à 20, on peut utiliser la requête suivante :

sqlite> select nom, prenom from etudiant where age > 20;

dupond|dupont

martin|pierre

Si on veut maintenant afficher les étudiants dont le nom commence par la lettre m, nous pouvons utiliser le mot-clé « like » comme suit :

sqlite> select * from etudiant where nom like "m%";

11|martin|jean|20

13|martin|pierre|22

La mise à jour des enregistrements d’une table, qui satisfont des critères donnés, est assurée grâce à la commande :

 

update "nom_table" set "nom_colonne" = "nouvelle_valeur" [,"prochaine_colonne" = "prochaine_nouvelle_valeur"...] where "nom_colonne" OPERATEUR "valeur" [and | or "nom_colonne" OPERATEUR "valeur"]; [] = optionnel

Voici quelques exemples de mise à jour de la table "etudiant" : la première requête ajoute un an pour chaque étudiant et la deuxième change l’identifiant de l’étudiant "martin pierre" en lui donnant la valeur 1. 1.

update etudiant set age = age+1; 

update etudiant set id = 1 where nom="martin" and prenom="pierre";

Le contenu de la table "etudiant" après les deux requêtes de mise à jour est :

sqlite> select * from etudiant;

1|martin|pierre|23

5|dupond|dupont|22

10|toto|titi|21

11|martin|jean|21

Pour supprimer des données, il faut utiliser la commande suivante :

delete from "nom_de_table" where "nom_colonne" OPERATEUR "valeur" [and | or "autre_colonne" OPERATEUR "autre_valeur"]; [] = optionnel

Voici quelques exemples de suppression : la première supprime l’étudiant "martin pierre" et la deuxième supprime tous les enregistrements de la table "etudiant" qui restent.

1. delete from etudiant where nom="martin" and prenom="pierre" ;
2. delete from etudiant ;

La commande « select » a cinq clauses principales : select, from, where, group by, having et order by.

select [ALL | DISTINCT] colonne1[, colonne2] from table1[,table2] [where "conditions"] [group by "liste-de-colonnes"] [having "conditions"] [order by "liste_de_colonnes" [ASC | DESC] ]

Les mots-clés ALL et DISTINCT sont utilisés pour sélectionner respectivement tous les enregistrements (par défaut) ou tous les enregistrements sans compter les doublons. La requête select DISTINCT age from etudiant ; donne les différents âges qu’ont les étudiants de la table "etudiant".

Les fonctions d’agrégation sont utilisées pour réaliser certaines opérations sur les résultats d’une requête de sélection. Ces fonctions sont souvent combinées avec une clause « group by ».

Voici les fonctions d’agrégation :

Fonction	Description
MIN	retourne la valeur minimale d’une colonne donnée.
MAX	retourne la valeur maximale d’une colonne donnée.
SUM	retourne la somme des valeurs numériques d’une colonne.
AVG	retourne la moyenne des valeurs numériques d’une colonne.
COUNT	compte le total de valeurs non nulles (différentes de la valeur NULL) d’une colonne.
COUNT(*)	compte le nombre de lignes dans une sélection.

Les exemples suivants calculent respectivement la moyenne d’âge de tous étudiants de la table étudiants, la moyenne d’âge des étudiants dont le nom de famille est "martin" et le nombre total des enregistrements (n’oubliez de peupler à nouveau la table "etudiant" si vous avez supprimé ses enregistrements).

sqlite> select avg(age) from etudiant;

21.75

sqlite> select avg(age) from etudiant where nom="martin";

22

sqlite> select count(*) from etudiant;

4

Clause group by

La clause « group by » regroupe des enregistrements ayant les mêmes valeurs sur certaines colonnes. Ces valeurs sont également agrégées sur une ou plusieurs de ces colonnes. La syntaxe d’une telle requête est la suivante. select liste_de_colonnes, fonction_agregation(autre_liste_de_colonnes) from "liste_de_tables" group by "liste_de_colonnes" ;

Soit la table "achat" créée à l’aide de l’instruction qui suit.

create table achat (id_client integer, date_achat text, article text, quantite integer, prix integer);

<code>



Pour peupler cette table, utilisez les instructions suivantes: 



<code>

insert into achat (id_client,date_achat,article,quantite,prix) values (10330,'1999-06-30','Echasses',1,2800);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10101,'1999-07-10','Raft',1,5800);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10298,'1999-07-15','Skateboard',1,3300);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10101,'1999-07-28','Gilet de sauvetage',4,12500);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10299,'1999-07-30','Parachute',1,125000);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10339,'1999-08-02','Parapluie',1,450);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10449,'1999-08-13','Vélo',1,18079);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10439,'1999-08-20','Rouleurs en ligne',2,2550);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10101,'1999-08-30','Parka',1,1830);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10449,'1999-09-01','Bottes',1,4500);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10439,'1999-09-17','Tente',1,8800);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10298,'1999-10-10','Lanterne',2,2900);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10410,'1999-11-13','Sac de couchage',1,8922);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10438,'1999-12-01','Parapluie',1 ,675);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10438,'1999-12-04','Oreiller',1,850);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10298,'1999-12-13','Casque',1,2200);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10449,'1999-12-14','Vélo',1,38050);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10449,'1999-12-20','Canoe',1,28000);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10101,'1999-12-30','Hoola hoop',3,1475);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10330,'2000-01-01','Lampe torche',4,2800);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10101,'2000-01-05','Lanterne',1,1600);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10299,'2000-02-02','Matelas gonflable',1,3800);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10438,'2000-03-20','Tente',1,7999);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10413,'2000-03-21','Chaise longue',4,3200);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10410,'2000-04-01','Vélo',1,19250);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10315,'2000-04-01','Compas',1,800);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10449,'2000-04-20','Lampe torche',1,450);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10101,'2000-04-21','Sac de couchage',2,8870);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10298,'2000-05-01','Couteau Suisse',1,2238);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10449,'2000-05-11','Canoe',2,4000);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10298,'2000-05-16','Cache-oreille',1,1250);

insert into achat (id_client,date_achat,article,quantite,prix) values  (10330,'2000-05-17','Pelle',1,16759);

Vous devriez obtenir le résultat suivant :

sqlite> select count(*) from achat;

32

Si on veut obtenir la quantité des articles achetés, on peut agréger la colonne "quantite" à l’aide de la fonction SUM. Les regroupements sont fais sur la colonne "article".

sqlite> select article, sum(quantite) from achat group by article;

Bottes|1

Cache-oreille|1

Canoe|3

Casque|1

Chaise longue|4

Compas|1

Couteau Suisse|1

Echasses|1

Gilet de sauvetage|4

Hoola hoop|3

Lampe torche|5

Lanterne|3

Matelas gonflable|1

Oreiller|1

Parachute|1

Parapluie|2

Parka|1

Pelle|1

Raft|1

Rouleurs en ligne|2

Sac de couchage|3

Skateboard|1

Tente|2

Vélo|3

Nous pouvons combiner une clause « group by » avec une clause « where » pour restreindre l’agrégation sur une partie de la table. Par exemple, on peut vouloir la quantité des articles achetés par le client "10449". La requête et son résultat sont donnés ci-dessous.

sqlite> select article, sum(quantite) from achat where id_client=10449 group by article;

Bottes|1

Canoe|3

Lampe torche|1

Vélo|2

mysql>

La clause « having » permet de spécifier des conditions supplémentaires sur le regroupement group by. Si vous avez à utiliser une clause « having », elle doit impérativement suivre un regroupement group by. Par exemple, si on veut obtenir les articles achetés plus de deux fois, on peut utiliser la requête qui suit :

sqlite> select article, sum(quantite) from achat group by article having sum(quantite) > 2;

Canoe|3

Chaise longue|4

Gilet de sauvetage|4

Hoola hoop|3

Lampe torche|5

Lanterne|3

Sac de couchage|3

Vélo|3

Le tri des résultats

La clause « order by », qui est optionnelle, sert à trier les résultats d’une requête sur une liste de colonnes selon un ordre croissant (ASC, l’ordre pris par défaut) ou décroissant (DESC). Par exemple, on peut ordonner les étudiants selon leur nom et prénom :

sqlite> select * from etudiant order by nom, prenom ASC;

5|dupond|dupont|22

11|martin|jean|21

1|martin|pierre|23

10|toto|titi|21

On peut aussi demander à ce que seulement les quelques premières rangées soient
données avec la clause « limit » :

sqlite> select * from etudiant order by nom, prenom ASC limit 2;

5|dupond|dupont|22

11|martin|jean|21

Jointure

Jusqu’à présent, nous avons travaillé sur une seule table. Il se peut que les données à sélectionner se trouvent dans plusieurs tables.
Il faut dans ce cas joindre plusieurs tables. La jointure peut être reconnue par la présence de plusieurs tables dans la clause « from » comme le montre la commande suivante. select "liste-de-colonnes" from table1, table2,... where conditions_de_recherche.
Considérons la table "achat" créée dans la section précédente et créons une nouvelle table appelée "client" comme suit :

  

 create table client (id_client integer,prenom text,nom text, ville text,province text); 

 insert into client (id_client,prenom,nom,ville,province) values (10101,'John','Gray','Edmonton','Alberta'),

 insert into client (id_client,prenom,nom,ville,province) values  (10298,'Leroy','Brown','Toronto','Ontario');

 insert into client (id_client,prenom,nom,ville,province) values  (10299,'Elroy','Keller','Edmonton','Alberta');

 insert into client (id_client,prenom,nom,ville,province) values  (10315,'Liza','Pierre','Montréal','Québec');

 insert into client (id_client,prenom,nom,ville,province) values  (10325,'Ginger','Schultz','Montréal','Québec');

 insert into client (id_client,prenom,nom,ville,province) values  (10329,'Kelly','Mendoza','Saint John','Nouveau Brunswick');

 insert into client (id_client,prenom,nom,ville,province) values  (10330,'Céline','Martin','Québec','Québec');

 insert into client (id_client,prenom,nom,ville,province) values  (10338,'Michael','Howell','Montréal','Québec');

 insert into client (id_client,prenom,nom,ville,province) values  (10339,'Anthony','Sanchez','Ottawa','Ontario');

 insert into client (id_client,prenom,nom,ville,province) values  (10408,'Elroy','Cleaver','Toronto','Ontario');

 insert into client (id_client,prenom,nom,ville,province) values  (10410,'Mary Ann','Howell','Fredericton','Nouveau Brunswick');

 insert into client (id_client,prenom,nom,ville,province) values  (10413,'Donald','Davids','Gila Bend','Ontario');

 insert into client (id_client,prenom,nom,ville,province) values  (10419,'Linda','Sakahara','Ottawa','Ontario');

 insert into client (id_client,prenom,nom,ville,province) values  (10429,'Sarah','Graham','Fredericton','Nouveau Brunswick');

 insert into client (id_client,prenom,nom,ville,province) values  (10438,'Kevin','Smith','Victoria','Colombie Britannique');

 insert into client (id_client,prenom,nom,ville,province) values  (10439,'Conrad','Giles','Victoria','Colombie Britannique');

 insert into client (id_client,prenom,nom,ville,province) values  (10449,'Isabela','Moore','Toronto','Ontario');

Nous voulons afficher les dépenses de chaque client. Nous avons alors besoin des nom et prénom des clients (table "client", colonne "prenom" et "nom"), des montants dépensés et des quantités achetées par chaque client (table "achat" colonne "prix" et "quantite"). Comme vous l’avez sans doute remarqué, les tables "client" et "achat" ne sont pas complètement indépendantes. En effet, la colonne "id_client" lie les deux tables. Nous utiliserons cette colonne pour joindre ces deux tables dans la requête suivante :

sqlite> select prenom, nom, quantite*prix from client, achat where client.id_client = achat.id_client; 

Elroy|Keller|125000

Elroy|Keller|3800

Liza|Pierre|800

Céline|Martin|2800

Céline|Martin|11200

Céline|Martin|16759

Anthony|Sanchez|450

Mary Ann|Howell|8922

Mary Ann|Howell|19250

Donald|Davids|12800

Kevin|Smith|675

Kevin|Smith|850

Kevin|Smith|7999

Conrad|Giles|5100

Conrad|Giles|8800

Isabela|Moore|18079

Isabela|Moore|4500

Isabela|Moore|38050

Isabela|Moore|28000

Isabela|Moore|450

Isabela|Moore|8000

Nous utilisons la notation "client.id_client" et "achat.id_client" pour différencier entre "id_client" qui se trouvent dans les deux tables. Nous pouvons encore faire mieux en regroupant ensemble les mêmes clients, en additionnant les montants dépensés et en triant selon ce montant.

sqlite> select prenom, nom, SUM(quantite*prix) from client, achat where client.id_client = achat.id_client group by prenom, nom order by SUM(quantite*prix) DESC;

Elroy|Keller|128800

Isabela|Moore|97079

Céline|Martin|30759

Mary Ann|Howell|28172

Conrad|Giles|13900

Donald|Davids|12800

Kevin|Smith|9524

Liza|Pierre|800

Anthony|Sanchez|450

Indexation

Les index sont utilisés pour trouver rapidement les rangées dans une table sans avoir à toutes les visiter.

Tous les types de colonnes ne peuvent être indexés. On peut aussi indexer plusieurs colonnes.

Par exemple, on peut accélérer les requêtes de sur la colonne nom avec l’instruction suivante :

create index nomindex on client ( nom );

En contre partie, la mise à jour de la table « client » prendra plus de temps car il faut mettre à jour les index associés à la
table. Pour supprimer un index, utilisez la syntaxe « drop index nom_de_l_index on nom_de_la_table ; »

Utilisation des vues

Une vue est une table virtuelle, c’est-à-dire dont les données ne sont pas stockées dans une table de la base de données, et dans laquelle il est possible de rassembler des informations provenant de plusieurs tables. On parle de « vue » car il s’agit simplement d’une représentation des données. Les données présentes dans une vue sont définies grâce à une clause « select ».

La création d’une vue se fait grâce à la clause « create view », suivie du nom que l’on donne à la vue, le tout suivi d’une clause « as select ... ». La syntaxe d’une vue ressemble donc à ceci : « create view nom_de_la_vue as clause_select ». Les vues ainsi créées peuvent être l’objet de nouvelles requêtes en précisant le nom de la vue au lieu d’un nom de table dans une commande « select ». En fait, une vue se comporte pratiquement comme une table.

create view marequete as select * from client where nom like "S%";

En lançant une commande « .tables », vous constatez que la vue crée se trouve dans la liste des tables. Vous pouvez l’utiliser comme toute autre table même si vous quittez votre session.

Routines stockées

Il est possible de créer ses propres fonctions et procédures. On les appelle des routines stockées parce qu’elles résident dans le SGBD. SQLite ne permet pas les « routines stockées ». Nous allons donc illustrer ce concept avec MySQL.

Il faut d’abord connaître les instructions de contrôle if, case, while, etc. Ces instructions fonctionnent comme dans un langage tel que Java. Voici un exemple concret :

mysql> set @i=0, @j=0; 

mysql> select (case when @i> @j then @i else @j end);

0

On peut maintenant définir ses propres fonctions. La syntaxe est simple :

create function ma_fonction returns un type return valeur

À titre d’exemple, supposons que vous vouliez une fonction qui calcule le maximum de deux entiers :

mysql> create function monmax(i int, j int) returns int return case when i> j then i else j end; 

mysql> select monmax(14,15); 

15

La fonction nouvellement créée est liée à la base de données où vous vous trouvez. Par exemple, si vous étiez dans la base de données « test », le nom complet de la fonction est "monmax.test".

MySQL supporte aussi la définition de procédures, en plus des fonctions. La syntaxe des procédure est un peu différente, mais l’idée générale est la même. Pour voir quelles fonctions et procédures sont définies, tapez « show procedure status » et « show function status ».

Semaine 1

RECHERCHE

Bases de données relationnelles