pour plus de documents : [Link].
com
re
TP SQL
anne Informatique
Najib Tounsi
La BD pour une bibliothque est constitue des tables suivantes (Voir en
annexe le script SQL de cration sur le SGBD mysql):
Table des livres (Numro inventaire du livre, titre et auteur du livre, nombre
dexemplaires dans la bibliothque)
Livre
NumInv
323
124
1110
241
321
216
312
Titre
Bases De Donnes
Algorithmes
Programmation
Langage C
Unix
Systmes Exploitations
Langage Java
Auteur
C.J. Date
D. Knuth
B. Meyer
B. Kernighan
K. Thompson
Crocus
J. Gosling
Qte
3
2
6
5
2
1
8
Table des abonn(e)s (Numro abonn, son nom et prnom )
Abonne
NumAb
12
10
32
14
Nom
Benali
BenSaid
Karim
Rajaoui
Prnom
Ali
Said
Karima
Rajaa
Table des prts (Numro inventaire du livre emprunt, Numro abonn de
lemprunteur et la date demprunt)
Pret
NumInv
323
124
323
241
NumAb
10
12
12
32
DatePret
1/11/2007
2/11/2007
3/10/2007
3/10/2007
pour plus de documents : [Link]
321
323
312
323
I.
II.
III.
14
14
10
32
13/10/2007
10/11/2007
3/10/2007
13/09/2007
Crer ces tables en Access. Choisir le type entier pour QTE, le type texte
de 4 caractres pour NumInv, texte de 3 caractres pour NumAb, type
date pour DatePret et texte de 15 caractres pour les champs Nom,
Prenom et Auteur.
Alimenter ces tables par les valeurs donnes.
Ecrire en SQL les requtes suivantes :
pour plus de documents : [Link]
1. Interroger les informations dune table
Rechercher toutes le donnes
mysql> select * from livre;
+--------+----------------------+--------------+------+
| numinv | titre
| auteur
| qte |
+--------+----------------------+--------------+------+
| 323
| Bases De Donnes
| C.J. Date
|
3 |
| 124
| Algorithmes
| D. Knuth
|
2 |
| 1110
| Programmation
| B. Meyer
|
6 |
| 241
| Langage C
| B. Kernighan |
5 |
| 321
| Unix
| K. Thompson |
2 |
| 216
| Systmes Exploitati | Crocus
|
1 |
| 312
| Langage Java
| J. Gosling
|
8 |
+--------+----------------------+--------------+------+
mysql> select * from pret;
+--------+-------+------------+
| numinv | numab | datepret
|
+--------+-------+------------+
| 323
| 10
| 2007-11-01 |
| 124
| 12
| 2007-11-02 |
| 323
| 12
| 2007-10-03 |
| 241
| 32
| 2007-10-03 |
| 321
| 14
| 2007-10-13 |
| 323
| 14
| 2007-11-10 |
| 312
| 10
| 2007-10-03 |
| 323
| 32
| 2007-09-13 |
+--------+-------+------------+
Rechercher certaines lignes
Tous les livres disponibles en quantit gale 2
mysql> select *
-> from livre
-> where qte = 2;
+--------+-------------+-------------+------+
| numinv | titre
| auteur
| qte |
+--------+-------------+-------------+------+
| 124
| Algorithmes | D. Knuth
|
2 |
pour plus de documents : [Link]
| 321
| Unix
| K. Thompson |
2 |
+--------+-------------+-------------+------+
Tous les livres disponibles en quantit comprise entre 6 et 2
mysql> select *
-> from livre
-> where qte < 6 and qte > 2;
+--------+-------------------+--------------+------+
| numinv | titre
| auteur
| qte |
+--------+-------------------+--------------+------+
| 323
| Bases De Donnes | C.J. Date
|
3 |
| 241
| Langage C
| B. Kernighan |
5 |
+--------+-------------------+--------------+------+
Tous les livres de titre Unix ou disponibles en quantit comprise
entre 6 et 2
mysql> select *
-> from livre
-> where (qte < 6 and qte > 2) OR titre="Unix";
+--------+-------------------+--------------+------+
| numinv | titre
| auteur
| qte |
+--------+-------------------+--------------+------+
| 323
| Bases De Donnes | C.J. Date
|
3 |
| 241
| Langage C
| B. Kernighan |
5 |
| 321
| Unix
| K. Thompson |
2 |
+--------+-------------------+--------------+------+
Rechercher certaines colonnes
Le titre et la quantit de tous les livres
mysql> select titre, qte
-> from livre ;
+----------------------+------+
| titre
| qte |
+----------------------+------+
| Bases De Donnes
|
3 |
| Algorithmes
|
2 |
| Programmation
|
6 |
| Langage C
|
5 |
pour plus de documents : [Link]
| Unix
|
2 |
| Systmes Exploitati |
1 |
| Langage Java
|
8 |
+----------------------+------+
Le titre, la quantit et la quantit augmente de 5 de tous les
livres Usage des expressions.
mysql> select titre, qte, qte+5 from livre;
+----------------------+------+-------+
| titre
| qte | qte+5 |
+----------------------+------+-------+
| Bases De Donnes
|
3 |
8 |
| Algorithmes
|
2 |
7 |
| Programmation
|
6 |
11 |
| Langage C
|
5 |
10 |
| Unix
|
2 |
7 |
| Systmes Exploitati |
1 |
6 |
| Langage Java
|
8 |
13 |
+----------------------+------+-------+
(noter la nouvelle colonne dans le rsultat (uniquement le rsultat)).
Les diffrentes quantit des livres
mysql> select qte from livre;
+------+
| qte |
+------+
|
3 |
|
2 |
|
6 |
|
5 |
|
2 |
|
1 |
|
8 |
+------+
Usage de DISTINCT..
mysql> select distinct qte from livre;
pour plus de documents : [Link]
+------+
| qte |
+------+
|
3 |
|
2 |
|
6 |
|
5 |
|
1 |
|
8 |
+------+
Le titre et la quantit des livres disponibles en quantit suprieure
3
mysql> select titre, qte
-> from livre
-> where qte >3;
+---------------+------+
| titre
| qte |
+---------------+------+
| Programmation |
6 |
| Langage C
|
5 |
| Langage Java |
8 |
+---------------+------+
Trier les lignes rsultats
mysql> select titre, auteur from livre order by titre;
+----------------------+--------------+
| titre
| auteur
|
+----------------------+--------------+
| Algorithmes
| D. Knuth
|
| Bases De Donnes
| C.J. Date
|
| Langage C
| B. Kernighan |
| Langage Java
| J. Gosling
|
| Programmation
| B. Meyer
|
| Systmes Exploitati | Crocus
|
| Unix
| K. Thompson |
+----------------------+--------------+
mysql> select titre, auteur from livre
pour plus de documents : [Link]
-> order by titre desc;
+----------------------+--------------+
| titre
| auteur
|
+----------------------+--------------+
| Unix
| K. Thompson |
| Systmes Exploitati | Crocus
|
| Programmation
| B. Meyer
|
| Langage Java
| J. Gosling
|
| Langage C
| B. Kernighan |
| Bases De Donnes
| C.J. Date
|
| Algorithmes
| D. Knuth
|
+----------------------+--------------+
mysql> select titre, auteur from livre where qte >3
order by titre;
+---------------+--------------+
| titre
| auteur
|
+---------------+--------------+
| Langage C
| B. Kernighan |
| Langage Java | J. Gosling
|
| Programmation | B. Meyer
|
+---------------+--------------+
mysql> select titre, auteur from livre order by titre
-> where qte >3;
ERROR 1064: You have an error in your SQL syntax near
'where qte >3' at line 1
(Inversion des clauses order by et where. Le tri vient en fin de
recherche, aprs le where donc.)
Calcul sur les dates ( adapter Access)
mysql> select *, curdate() from pret;
+--------+-------+------------+------------+
| numinv | numab | datepret
| curdate() |
+--------+-------+------------+------------+
| 323
| 10
| 2007-11-01 | 2007-11-28 |
| 124
| 12
| 2007-11-02 | 2007-11-28 |
| 323
| 12
| 2007-10-03 | 2007-11-28 |
| 241
| 32
| 2007-10-03 | 2007-11-28 |
| 321
| 14
| 2007-10-13 | 2007-11-28 |
| 323
| 14
| 2007-11-10 | 2007-11-28 |
pour plus de documents : [Link]
| 312
| 10
| 2007-10-03 | 2007-11-28 |
| 323
| 32
| 2007-09-13 | 2007-11-28 |
+--------+-------+------------+------------+
mysql> select * from pret
-> where curdate() -datepret >120;
+--------+-------+------------+
| numinv | numab | datepret
|
+--------+-------+------------+
| 323
| 12
| 2007-10-03 |
| 241
| 32
| 2007-10-03 |
| 312
| 10
| 2007-10-03 |
| 323
| 32
| 2007-09-13 |
+--------+-------+------------+
Recherche sur motifs (Pattern Matching)
Titre et auteurs des livres dont le titre commence par
L
mysql> select titre, auteur from
-> where titre like "L%";
+--------------+--------------+
| titre
| auteur
|
+--------------+--------------+
| Langage C
| B. Kernighan |
| Langage Java | J. Gosling
|
+--------------+--------------+
livre
Titre et auteurs des livres dont la 2e lettre du
est un a
mysql> select titre, auteur from livre
-> where titre like "_a%";
+-------------------+--------------+
| titre
| auteur
|
+-------------------+--------------+
| Bases De Donnes | C.J. Date
|
| Langage C
| B. Kernighan |
| Langage Java
| J. Gosling
|
+-------------------+--------------+
titre
pour plus de documents : [Link]
Titre et auteurs des livres dont le titre a quatre
lettres
mysql> select * from livre where titre like "____";
+--------+-------+-------------+------+
| numinv | titre | auteur
| qte |
+--------+-------+-------------+------+
| 321
| Unix | K. Thompson |
2 |
+--------+-------+-------------+------+
Compter les lignes
mysql> select count(*) from livre;
+----------+
| count(*) |
+----------+
|
7 |
+----------+
mysql> select count(*) from livre;
+----------+
| count(*) |
+----------+
|
7 |
+----------+
mysql> select count(*) as nb_livres from livre;
+-----------+
| nb_livres |
+-----------+
|
7 |
+-----------+
mysql> select numab, count(*)
-> from pret
-> group by numinv;
+-------+----------+
| numab | count(*) |
+-------+----------+
| 12
|
1 |
| 32
|
1 |
pour plus de documents : [Link]
| 10
|
1 |
| 14
|
1 |
| 10
|
4 |
+-------+----------+
mysql> select max(qte) from livre;
+----------+
| max(qte) |
+----------+
|
8 |
+----------+
mysql> select * from livre where qte = max(qte);
+--------+--------------+------------+------+
| numinv | titre
| auteur
| qte |
+--------+--------------+------------+------+
| 312
| Langage Java | J. Gosling |
8 |
+--------+--------------+------------+------+
Utiliser plusieurs tables (JOIN)
mysql> select numab, titre
-> from livre , pret
-> where [Link] = [Link];
+-------+-------------------+
| numab | titre
|
+-------+-------------------+
| 10
| Bases De Donnes |
| 12
| Bases De Donnes |
| 14
| Bases De Donnes |
| 32
| Bases De Donnes |
| 12
| Algorithmes
|
| 32
| Langage C
|
| 14
| Unix
|
| 10
| Langage Java
|
+-------+-------------------+
Equivalent
mysql> select numab, titre
-> from livre l, pret p
-> where [Link] = [Link];
pour plus de documents : [Link]
mysql>
->
->
->
select numab, titre
from livre l, pret p
where [Link] = [Link]
and datepret ='2007-10-3';
+-------+-------------------+
| numab | titre
|
+-------+-------------------+
| 12
| Bases De Donnes |
| 32
| Langage C
|
| 10
| Langage Java
|
+-------+-------------------+
Essayer aussi
select titre
from livre
where numinv in (select numinv
from pret
where datepret ="2007-10-3")
Ecriture possible (requtes imbriques) quand le rsultat final provient dune seul
table (ici, titre dans table livre).
Jointure de trois tables
mysql> select nom, titre
-> from abonne a, livre l, pret p
-> where [Link] = [Link]
-> and [Link] = [Link];
+---------+-------------------+
| nom
| titre
|
+---------+-------------------+
| BenSaid | Bases De Donnes |
| Benali | Bases De Donnes |
| Rajaoui | Bases De Donnes |
| Karim
| Bases De Donnes |
| Benali | Algorithmes
|
| Karim
| Langage C
|
| Rajaoui | Unix
|
| BenSaid | Langage Java
|
+---------+-------------------+
pour plus de documents : [Link]
pour plus de documents : [Link]
Annexe
BD cree sur mysql par le script
[tounsi@khayyam Biblio]$ cat [Link]
drop database if exists biblio;
CREATE DATABASE biblio;
use biblio;
drop table if exists livre;
create table livre (
numinv varchar(4) not null ,
titre varchar(20),
auteur varchar(15),
qte int(2) unsigned,
primary key (numinv)
);
create index livreind on livre (numinv);
drop table if exists abonne;
create table abonne(
numab varchar(3) not null ,
nom varchar(15),
prenom varchar(15),
primary key (numab)
);
create index abonneind on abonne (numab);
drop table if exists pret;
create table pret (
numinv varchar(4) not null ,
numab varchar(3) not null ,
datepret date,
primary key (numinv,numab)
);
create index pretind on pret (numinv,numab);
