Démarrer SQLite Utilisation d`une base de données

Lycée Carnot - Dijon
MPSI 3
Dans emacs comme dans le terminal, les raccourcis habituels pour le copier-coller ne
fonctionnent pas non plus. Vous utiliserez les raccourcis standards unix :
TP DÉCOUVERTE DE SQL ITE
•
ctrl
+
insert
: copier,
•
Maj
+
insert
: coller,
•
Maj
+
suppr
: couper.
Démarrer SQLite
Création des tables
En salle de TP, nous allons utiliser SQLite (prononcer SQL-Lite). C’est une application qui
permet de gérer une base de donnée sans utiliser d’architecture client-serveur : tout se passe
sur place et les bases de données sont stockées dans des fichiers sur la machine.
La syntaxe SQL pour créer des tables est la suivante (attention : n’utilisez pas de tabulations mais des espaces !) :
Tout va se passer en ligne de commande. Pour lancer SQLite sur un fichier, il faut utiliser
la commande 1 :
sqlite3 -column -header [chemin_eventuel\]nom_fichier.sqlite
L’option -column permet d’afficher les tables sous forme de tableaux, et l’option -header
permet d’afficher des en-têtes de colonnes 2 .
Si le fichier n’existe pas, il est créé directement.
); -- Ne pas oublier le point-virgule !
BSi
une clé primaire contient plusieurs attributs, il faut la déclarer à part avec
PRIMARY KEY (att_1, ..., att_n).
Créer, sur le même modèle, les tables personne, eleve, classe, prof et enseigne.
Vérifier avec .tables qu’elles sont bien créées.
Tester la commande .schema
Le shell de sqlite se présente alors à vous avec l’invite sqlite> . Les commande se
tapent directement : les commandes SQL doivent se terminer par ; et les commande sqlite
commencent par un point. .help permet de connaître les principales. Par exemple, .exit
permet de quitter sqlite, .tables permet de voir toutes les tables de la base, .databases
permet de voir toutes les bases de données connues.
On peut supprimer une table grâce à la commande
DROP TABLE nom_table; /* Bien réfléchir avant de l’utiliser !! */
Les commandes SQL peuvent se taper directement (bien finir la ligne par un ;) ou être
importées depuis un fichier grâce à la commande :
CREATE TABLE eleve (
num_p
INTEGER PRIMARY KEY REFERENCES personne(num_p),
num_etudiant
INTEGER ,
lycee_origine
TEXT,
num_classe
INTEGER REFERENCES classe(no)
sqlite> .read [chemin_eventuel\]nom_fichier.sql
Peuplement des tables
Pour peupler nos tables, on procède de la manière suivante :
Utilisation d’une base de données
Nous allons créer la base de donnée lycee.sqlite sur le modèle étudier en cours :
taper sqlite3 -column -header lycee.sqlite dans un terminal.
Nous enregistrerons au fur et à mesure les commandes SQL dans un fichier lycee.sql.
On pourra choisir emacs comme éditeur de texte avec coloration syntaxique : taper simplement emacs lycee.sql dans un autre terminal (ou mieux : nouvel onglet avec ctrl +
Maj + T ).
Attention les raccourcis habituels ne fonctionnent pas sous emacs. Par exemple, pour
sauver : ctrl + X puis ctrl + S .
1. Se déplacer dans son dossier de travail pour éviter les chemins et faciliter les choses
2. Changeable dans sqlite directement avec .header on et .mode column.
TP découverte de SQLite - page 1
INSERT INTO personne VALUES (6275,’Dupont’,’Pierre’,’0645456789’);
Peupler toutes les tables sur ce même principe.
Vérifier au fur et à mesure avec
SELECT * FROM nom_table;
On peut supprimer des tuples dans une table avec une commande de la forme :
DELETE FROM nom_table WHERE condition;
On peut modifier des tuples dans une table avec une commande de la forme :
UPDATE nom_table SET attr_1 = val_1, ..., attr_n = val_n
WHERE condition;
TP découverte de SQLite - page 2
Interrogation de la base de données
Vous pouvez maintenant utiliser des requêtes SQL pour interroger votre base de donnée.
Par exemple, pour chercher quels sont les noms et prénoms des élèves de MP* :
SELECT p.nom, p.prenom
FROM
eleve e JOIN personne p ON e.num_p = p.num_p
JOIN classe c
ON e.num_classe = c.no
WHERE c.nom = ’MP*’;
ou encore
SELECT
FROM
WHERE
AND
AND
7. Donner le nom et prénom des élèves et des professeurs de MPSI 3.
8. Donner le nom et le prénom des élèves ayant le même prénom qu’un de leurs professeurs.
9. Donner le nom des élèves ayant un professeur avec moins de 15 ans d’ancienneté.
10. Donner le nom et la matière des professeurs enseignant dans une classe dont ils ne
sont pas professeur principal.
p.nom, p.prenom
eleve e, personne p, classe c
e.num_p = p.num_p
e.num_classe = c.no
c.nom = ’MP*’;
ce qui donne :
nom
---------Galois
prenom
---------Evariste
On précédera toutes les requêtes par leur
intitulé en français placé en commentaire
dans le fichier lycee.sql.
Écrire en SQL les requêtes suivantes.
1. Donner le nom et le numéro de téléphone de toutes les personnes enregistrées et
qui ont bien un numéro de téléphone.
2. Donner le nom et le numéro de téléphone de tous les élèves.
Voici, par exemple, une autre possibilité :
SELECT nom, tel
FROM personne
WHERE num_p IN (SELECT num_p FROM eleve);
3. Donner le nom et le prénom de tous les élèves dont le nom de la classe contient ’SI’.
4. Donner d’au moins trois autres façons différentes le nom et le prénom de tous les
élèves de sup.
5. Y a-t-il une classe dont l’effectif est l’ancienneté d’un de ses professeurs ? Le double ?
Si oui, quel est le nom du professeur ?
6. Déterminer les couples d’élèves venant d’un même lycée.
TP découverte de SQLite - page 3
TP découverte de SQLite - page 4