1. Créer une table

Séquence 8
Langage de définition des données
Après avoir appris à récupérer, supprimer, modifier ou ajouter des enregistrements, nous allons apprendre à manipuler leur contenant, les tables.
Il s’agit juste d’une sensibilisation pour vous indiquer que l’on peut le faire. Pas
question de creuser la théorie comme avec les requête sélection.
Capacités attendues
• Savoir ajouter, modifier ou supprimer des tables
Contenu
1.
Créer une table.................................................................................... 170
1A.
1B.
Rappel ....................................................................................................... 170
Syntaxe ..................................................................................................... 170
2.
Modifier une table ............................................................................. 171
2A.
2B.
2C.
Ajout d’un champ ..................................................................................... 171
Modification de champ ............................................................................ 171
Suppression de champ ............................................................................. 172
3.
Suppression d’une table .................................................................. 172
Travaux dirigés 3
169
8 3999 TG PA 02
Séquence 8
1.
Créer une table
1A.
Rappel
Qu’est-ce qu’une table, en fait ? Je vous rappelle le MLD du cas Bibliothèque.
Auteur (NumAuteur, Nom, Prénom, DateNaissance, LieuNaissance, DateDécès, LieuDécès)
Livre (NumLivre, Titre, DateAchat, Prix, DateLecture, Collection, Numéro, Cycle, NumCycle,
AnnéeImpression, Pages, NumAuteur#)
primaire, étrangère#
De quoi sont constituées les tables ? De champs. Créer une table va donc consister à :
• donner le nom de la table ;
• donner le nom et le type de chaque champ ;
• indiquer la clé primaire ;
• indiquer les éventuelles clés étrangères.
L’instruction permettant de créer une table va permettre d’indiquer tout cela.
1B.
Syntaxe
Sans autre forme de procès, voici la syntaxe à utiliser.
Création de table
Syntaxe
Sémantique
create table table
(champ1 type1 [primary key],
champ2 type2,…
champn typen) ;
create table table à créer
(champ à créer type de ce champ [clé primaire],
champ à créer type de ce champ,…
champ à créer type de ce champ) ;
Pour indiquer quel champ est clé primaire, on rajoute primary key juste après le type.
D’après ma syntaxe, on ne peut définir que le premier champ en tant que clé primaire.
Dans la réalité, c’est inexact, n’importe quel champ peut l’être. Cela dit, il est plus logique de le mettre en premier dans la liste (comme l’identifiant dans le MCD Merise).
Les différents types sont classiques (numériques, texte, date…). Voici les principaux et
leur signification. Pour avoir plus d’informations (notamment les autres types possibles),
consultez l’aide d’Access.
Type de donnée
Signification
char(longueur)
chaîne de caractères possédant longueur caractères.
integer
entier
currency
monétaire
float
réel
date
date ou heure
counter
Numéro automatique
170
8 3999 TG PA 02
Le langage de définition des données
Exercice 103
Créez la table Essai contenant le champ Num (type numéro automatique), Nom (chaîne de
caractère de longueur 20) et date de naissance (de type date bien entendu). Le champ Num
est identifiant.
Pour définir des clés étrangères ou des clés primaires multi-champs, consultez l’aide. En
effet, Access n’utilise pas la syntaxe standard SQL mais une clause constraint.
2.
Modifier une table
Modifier une table signifie ajouter, modifier ou supprimer des champs dans une table.
L’instruction à utiliser est alter table. Elle se décline en trois syntaxes selon ce que l’on
veut faire.
2A.
Ajout d’un champ
Pour ajouter un champ, il faut indiquer sur quelle table on travaille ainsi que le nom et
le type du champ à ajouter.
Ajout de champ
Syntaxe
alter table table
add column champ1 type1 [primary key],
champ2 type2…
champ3 type3 ;
Sémantique
alter table table à modifier
add column champ à ajouter type du champ clé primaire
champ à ajouter type du champ…
champ à ajouter type du champ ;
Si la table ne possède pas encore de clé primaire, vous pouvez définir un des champs
ajoutés en tant que clé primaire.
Exercice 104
Rajoutez à la table Essai un champ Prénom (texte) et Poids (entier).
2B.
Modification de champ
Il s’agit de modifier le type des champs. Attention à être cohérent ! Si la table contient
déjà des données, vous devez respecter les règles de base de la conversion. Par exemple,
la valeur texte Coucou ne peut pas être convertie en numérique. Toutes les conversions
ne sont donc pas possibles. De plus, si vous réduisez un champ texte de 100 caractères
à 5, les données seront tronquées si elles dépassent ce nouveau seuil.
Modification de champ
Syntaxe
alter table table
alter column champ type [primary key] ;
Sémantique
alter table table à modifier
alter column champ à modifier nouveau type ;
Si la table ne contient pas de champ clé primaire, modifier le champ peut revenir à le
définir en clé primaire.
171
8 3999 TG PA 02
Séquence 8
Exercice 105
Changez les champs Nom et Prénom de Essai pour qu’ils fassent 15 caractères de long.
2C.
Suppression de champ
C’est très simple, il suffit de mentionner la table et le champ.
Suppression de champ
Syntaxe
Sémantique
alter table table à modifier
drop column champ à supprimer ;
alter table table
drop column champ ;
Exercice 106
Dans Essai, supprimez les champs Nom puis Num.
3.
Suppression d’une table
C’est trivial.
Suppression d’une table
Syntaxe
drop table table ;
Sémantique
drop table table à supprimer ;
Exercice 107
Supprimez Essai.
Bah, non, pas de synthèse vu la simplicité et la concision de cette séquence. Il est inutile
d’apprendre ces syntaxes dont on se sert rarement. Si vous en avez besoin, utilisez votre
Memento SQL (autorisé à l’examen) ou lancez l’aide du système de gestion de base de
données que vous utilisez (Access ou autre).
Vous pouvez maintenant réaliser le TD 3.
172
8 3999 TG PA 02
Travaux dirigés 3
Durée indicative :
1 heure et demie
Ceci correspond à un TD (travaux dirigés). Les requêtes que vous devez
écrire correspondent à l’ensemble du cours sur le LID. Elles sont sans ordre
particulier : certaines sont très simples, d’autres complexes. Vous devez
pouvoir les faire en une à deux heures.
Bon travail !
Nous allons travailler sur les tables suivantes (ce sont les tables de la base Bibliothèque
étudiée dans le cours).
Auteur (NumAuteur, Nom, Prénom, DateNaissance, LieuNaissance, DateDécès, LieuDécès)
Livre (NumLivre, Titre, DateAchat, Prix, DateLecture, Collection, Numéro, Cycle, NumCycle,
AnnéeImpression, Pages, NumAuteur#)
Légende : primaire, étrangère#
Remarques :
• par auteur, j’entends le nom et le prénom (sauf indication contraire) ;
• par livre, j’entends l’auteur et le titre (sauf indication contraire).
Travail à faire
Écrivez les requêtes suivantes :
1. Nom de l’auteur du livre 666 (vous ferez deux requêtes : avec une sous-requête et
une avec jointure).
2. Liste triée des auteurs.
3. Liste chronologique des livres achetés.
4. Nombre de titres de livres commençant par le mot « le » (ou « Le »).
5. Nombre d’auteurs.
6. Nombre de livres.
7. Nombre d’auteurs et de livres.
8. Montant total dépensé pour l’achat des livres, prix moyen d’un livre, nombre de
pages moyen d’un livre.
9. Liste chronologique des livres (auteur et titre) lus entre deux dates.
10. Liste des livres (titres) restant à lire.
Remarques :
– l’informatisation n’a commencé qu’au premier janvier 1995 ; seuls les livres
achetés à partir de cette date sont concernés ;
– un livre non lu n’a pas de date de lecture, c.-à-d. que l’attribut correspondant
possède la constante null.
173
8 3999 TG PA 02
Travaux dirigés
11. Nombre de livres par auteur (liste triée par ordre décroissant du nombre de livres,
puis alphabétiquement par auteur).
12. Liste des livres lus plus de deux ans après la date d’achat.
13. Liste ordonnée de tous les livres (triée par nom d’auteur, prénom, cycle, numéro de
cycle, titre).
14. Liste des auteurs en double (rentrés deux ou plusieurs fois dans la base).
15. Nombre total de pages écrites par chaque auteur (trié par ordre décroissant de ce
nombre).
16. Livres (titres) dont le nombre de pages est égal à quatre fois le prix d’achat.
17. Nombre de livres par centaine de pages (donc nombre de livres ayant de 1 à 99
pages, de 100 à 199 pages...).
18. Liste des auteurs dont on possède plus de 31 livres.
19. Liste des auteurs ayant écrit plus de livres que l’auteur Kundera.
20. Quel est l’auteur qui a écrit le plus de pages ?
Une fois le TD fait et sa correction étudiée, vous pouvez faire puis envoyer le
devoir 2 à la correction (reportez-vous au fascicule « devoirs » réf. 3999 DG).
174
8 3999 TG PA 02
Conclusion
Si vous lisez cela, vous avez terminé l’étude du cours. Que vous a-t-il apporté ? Vous maîtrisez
toutes les subtilités de SQL :
• récupération des données ;
• maintenance des données (ajout, suppression et modification) ;
• maintenance des tables (création, modification et suppression).
En fait, il y a un pan de SQL que j’ai laissé dans l’ombre : la gestion des droits d’accès (à la base,
aux tables…). Cela n’existe que dans les SGBD professionnels (SQL Server, Oracle, PosgreSQL…) où
les applications base de données sont multi-utilisateurs. Ce n’était pas l’objet d’étude de ce cours,
d’autant que les requêtes attribuant les droits sont aussi simples et mécaniques que les requêtes
de création de table vues dans la dernière séquence.
Vous devez vous demander à quoi SQL va vous servir. Eh bien, je peux vous l’avouer, maintenant
que son étude est terminée : SQL ne sert pas à grand chose.
Mais non, je plaisante ! C’est l’outil central de l’informatique de gestion :
• toute application base de données est fondée sur des requêtes (voir le cours d’Access), que
vous fassiez uniquement un développement Access ou un développement VB/SGBD ou
Delphi/SGBD (bref, un langage de développement couplé à une base de données) ;
• plus prosaïquement, les requêtes SQL constituent le fond de commerce de l’examen du
BTS (partie S2 : Architecture logicielle des systèmes informatiques), et cela quelle que soit
votre option.
Enfin, vous avez acquis un savoir durable : SQL est ancien mais suffisamment performant pour
rester le langage de base de tout SGBD.
Quand allez-vous encore entendre parler de SQL ? Ma foi, dans le cours d’Access que vous avez
sans doute déjà commencé. Enfin, vous avez tout de même le droit de faire une petite pause
avant.
En seconde année, vous retrouverez les requêtes dans la partie programmation des bases de données si vous me suivez en option développeur d’applications.
175
8 3999 TG PA 02
Annexe
Tables des bases en cours
Base bibliothèque
Auteur (NumAuteur, Nom, Prénom, DateNaissance, LieuNaissance, DateDécès, LieuDécès)
Livre (NumLivre, Titre, DateAchat, Prix, DateLecture, Collection, Numéro, Cycle, NumCycle,
AnnéeImpression, Pages, NumAuteur#)
Base vétérinaire
Propriétaire
NumP
NomP
PrénomP
AdrP
CodeP
VilleP
TélP
1
Février
Jean-Yves
13, rue de la république
21 000
Dijon
03 28 78 59 65
2
Février
Frédérique
19, rue de la gare
98 765
Cnedville
01 45 85 58 44
3
Mars
Josette
4, rue des Arbres
54 000
Nancy
04 78 96 58 77
4
Javelas
Henri
29, avenue de la libération
45 000
Orléans
02 69 85 47 89
5
Loriette
Sylvain
4, avenue fleurie
95 000
Cergy
01 25 69 78 42
6
Février
Jean-Yves
87, square des amis
14 000
Caen
02 57 84 54 54
Pratiquer
Médicament
NumM
NomM
NumC
PrixM
1
Vermifuge pâte
5,33 €
2
Vermifuge cachet
6,09 €
3
Vaccin rage
13,11 €
4
Vaccin leucose
18,14 €
5
Diurétique
6,86 €
6
Croquettes 10 Kg
53,35 €
Animal
NumA
NomA
DateNaissA
Tatouage
2
Élan
02/10/1980
3
Démon
05/06/1985
ERT502
4
Zoé
09/12/2000
5
Nina
14/01/1996
6
Niok
01/08/2000
7
Jeune alf
8
Pollen
9
Jafna
23/05/1996
BNJ145
10
Fabel
24/10/1995
11
Tanis
12
Rio
13
Bipsie
14
Bouboule 01/01/1995
15
Nouki
NumR
NumP
3
3
3
3
ZEN245
3
3
DFG001
2
1
OIU115
15
1
14
2
4
2
5
4
BNA485
5
4
30/06/1994
BUI416
5
4
30/06/1994
KIU521
5
4
06/04/1985
DZE445
8
5
MPO444
6
5
9
6
2
3
4
4
5
6
7
7
7
8
9
10
11
11
12
13
13
14
14
14
15
16
17
18
18
18
NumS
4
1
1
3
4
7
5
6
8
7
8
4
4
7
5
2
6
6
7
8
1
1
7
1
5
8
177
8 3999 TG PA 02
Annexe
NumC
Consultation
HeureC
PrixC
DateC
NumV
NumA
2
07/12/2000
12:00
49,54 €
2
6
3
05/09/2001
09:15
64,79 €
1
2
4
21/06/1995
18:00
67,99 €
2
7
5
01/03/1998
12:30
41,92 €
3
10
6
03/10/1996
15:00
66,46 €
3
11
7
22/06/2003
09:15
32,77 €
3
10
8
10/05/1998
08:30
43,44 €
1
14
9
02/11/2000
14:30
59,91 €
2
4
10
12/02/1999
10:00
58,08 €
3
9
11
07/11/2000
13:15
39,02 €
1
15
12
16/03/1995
09:00
37,95 €
1
2
13
26/08/1996
13:30
59,91 €
3
15
14
26/08/1996
09:30
49,54 €
1
12
15
04/05/1995
15:30
61,43 €
3
5
16
06/07/1995
12:15
34,75 €
1
5
17
18/10/2004
11:00
42,83 €
2
15
18
29/01/2002
09:15
62,96 €
2
12
Soin
NumS
NomS
DuréeS
NatureS
TarifS
1
Visite de contrôle
15
externe
22,86 €
2
Vaccin
3
externe
7,62 €
3
Opération calcul
120
chirurgie
76,22 €
4
Stérilisation mâle
25
chirurgie
38,11 €
5
Stérilisation femelle 45
chirurgie
53,35 €
6
Radiographie
15
externe
18,29 €
7
Détartrage
35
petite intervention 27,44 €
8
Taille griffes
5
petite intervention 4,57 €
1
2
3
Prescrire
NumM
NumC
Vétérinaire
NomV
Carré
Carré
Rhin
NumV
PrénomV
Paul
Violette
Michel
Race
Posologie
Durée
NumR
NomR
Poids
1
2
10 gouttes/repas
15
2
Teckel sympa
7
1
5
5/j
3
3
Yorkshire
2,5
2
15
1 fois par repas
10
4
Chat gouttière
5
3
5
3/repas
6
5
Saluki
20
3
11
1/crise
6
Caniche royal
25
6
15
2/jour
5
8
Caniche nain
5
5
2
2 fois par jour
5
9
Bâtard
5
5
1 matin a jeun
15
14
lapin bélier nain
6
6
6
1/repas
7
15
Chat sphinx
3
6
8
1/repas
7
16
Persan
3
178
8 3999 TG PA 02