Module Base de données

Travaux Pratiques
Yahya Benkaouz
y.benkaouz@um5r.ac.ma
Departement Informatique
Objectifs
• Maitriser les concepts de base du langage SQL.
• Se familiariser avec le SGBDR MySQL.
• Etudier en détail les instructions de base de SQL.
• Maitriser la déclaration, la manipulation, et l’interrogation des tables.

2
Plan
• Concepts de base
• Définition de données
• Evolution d’un schéma
• Manipulation de données
• Interrogation des données

3
CONCEPTS DE BASE

4
Base de données
• Une collection d’informations organisées
• afin d’être facilement consultables, gérables et mises à jour.

• Ils sont utilisées dans un grand nombre d’entreprises pour

• stocker, manipuler et analyser les données.

• Au sein d’une BD relationnelles,

• l'information est organisée dans des tableaux à deux dimensions appelés des relations ou tables.

• Une BD relationnelle consiste en une ou plusieurs relations.

• Les lignes de ces relations sont appelées des nuplets ou enregistrements.
• Les colonnes sont appelées des attributs.

5
Base de données
• Les BD sont utilisées par de nombreuses entreprises dans toutes les industries.
• Compagnies aériennes pour gérer les réservations.
• Les enregistrements médicaux dans les hôpitaux.
• Les agences gouvernementales.
• Les universités.
• …

6
SGBD
• SGBD:
• Un système de gestion de base de données.
• Un ensemble de logiciels qui manipulent le contenu des bases de données.
• Ex: IBM DB2, Oracle Database, MySQL, PostgreSQL et Microsoft SQL Server
• Objectif: Garantir la cohérence du contenu de le BD
• Assure un accès facile aux données
• Accès simultanés de plusieurs utilisateurs
• Manipulation de données faciles

BD
SGBD

SGBDR: SGBD implémentant la théorie des BD relationnelles => Garantir l’intégrité des données
7
SGBD
• MySQL:
• L’un des SGBD les plus populaires.
• Il est distribué sous double licence, une licence publique générale GNU et une propriétaire.
• Il est développé, distribué et pris en charge par Oracle Corporation.
• Les bases de données MySQL sont des bases de données relationnelles.

8
 SQL
• SQL: Structured Query Language.
• “Un langage informatique normalisé servant à exploiter des bases de données
relationnelles”. wikipedia
SQL

Langage de définition des données Langage de manipulation des données Langage de contrôle
(CREATE, ALTER, DROP) (INSERT, UPDATE, DELETE)

de données de transactions
(GRANT, REVOKE) (COMMIT, DRAWBACK)

9
SQL
• Il comprend trois domaines :
• Langage de définition des données
• permet de créer et de modifier l'organisation des données dans la base de données

• Langage de manipulation des données

• permet de rechercher, d'ajouter, de modifier ou de supprimer des données dans les bases de
données relationnelles.

• Langage de contrôle
• Contrôle de transaction: commencer et de terminer des transactions
• Contrôle de donnée: autoriser ou d'interdire l'accès à certaines données à certaines personnes

10
SQL
Insertion des commentaires:
• Sur une ligne en utilisant --
• Sur plusieurs lignes entre /* et */

• Nomenclature:
• Tous les noms d’objets (Table, attributs,….) doivent respecter les règles suivantes:
• Commencer par une lettre
• Ne pas contenir un espace
• Ne pas dépasser 128 caractères parmi: Lettres non accentués, chiffres, @, $, #, _

11
DEFINITION DES DONNEES

12
Définitions de données
Base de
Tables
données

Création Création

Suppression Modification

Suppression

13
Création d’une base de données
• Pour créer une base de données qui sera appelé “ma_base” il suffit d’utiliser la requête suivante
qui est très simple:
CREATE DATABASE ma_base

• Avec MySQL, si une base de données porte déjà ce nom, la requête retournera une erreur. Pour
éviter d’avoir cette erreur, il convient d’utiliser la requête suivante pour MySQL:

CREATE DATABASE IF NOT EXISTS ma_base

• L’option IF NOT EXISTS permet juste de ne pas retourner d’erreur si une base du même nom existe
déjà. La base de données ne sera pas écrasée.

14
Suppression d’une base de données
• En SQL, la commande DROP DATABASE permet de supprimer totalement une base de données et tout ce
qu’elle contient.

• Pour supprimer la base de données “ma_base”, la requête est la suivante:

DROP DATABASE ma_base

• Par défaut, si le nom de base utilisé n’existe pas, la requête retournera une erreur. Pour éviter d’obtenir cette
erreur si vous n’êtes pas sûr du nom, il est possible d’utiliser l’option IF EXISTS.
• La syntaxe sera alors la suivante:

DROP DATABASE IF EXISTS ma_base

15
Création de tables
• Une table est une entité qui est contenu dans une base de données pour stocker des données ordonnées
dans des colonnes.

• La création d’une table sert à définir les colonnes et le type de données qui seront contenus dans chacun
des colonne (entier, chaîne de caractères, date, valeur binaire …).

• La syntaxe générale pour créer une table est la suivante :

CREATE TABLE nom_de_la_table (

colonne1 type_donnees,
colonne2 type_donnees,
colonne3 type_donnees,
colonne4 type_donnees
);

16
Création de tables
• La syntaxe générale pour créer une table est la suivante :

CREATE TABLE nom_de_la_table (

colonne1 type_donnees,
colonne2 type_donnees,
colonne3 type_donnees,
colonne4 type_donnees
);

• Type de données:

Numériques Textuelles Temporelles

TINY INT, SMALL INT, INT, CHAR(n) DATE
BIGINT, INTEGER VARCHAR(n) TIME
FLOAT, REAL, DOUBLE TEXT DATETIME
DECIMAL ENUM YEAR
SET
17
Création de tables
• Nombres entiers:

Numériques Nombre d’octets Minimum Maximum

TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

18
Création de tables
• Nombres entiers:

• Possibilité d’indiquer qu’un entire est UNSIGNED

- Pour les TINYINT, on pourra par exemple aller de 0 à 255

• Limiter la taille à l’affichage et l’attribut ZEROFILL

- Préciser le nombre de chiffres minimum à l'affichage d'une colonne de type INT
- Ex: INT(3)
- Même avec un INT(3), nous pouvons avoir la valeur 29003
- Le caractère ajouté par défaut est espace
- L’attribut ZEROFILL ajoute des zéros à gauche Nombre stocké Nombre affiché
du nombre lors de l’affichage 45 0045
INT(4) ZEROFILL 4156 4156
785164 785164 19
Création de tables
• Nombres décimaux :

• Cinq mots-clés permettent de stocker des nombres décimaux dans une

colonne: DECIMAL, NUMERIC, FLOAT, REAL et DOUBLE

• NUMERIC et DECIMAL sont équivalents et acceptent deux paramètres : la précision et l'échelle.

• La précision définit le nombre de chiffres significatifs stockés, donc les 0 à gauche ne comptent pas.
• En effet, 0024 est équivalent à 24. Il n'y a donc que deux chiffres significatifs dans 0024.
• L'échelle définit le nombre de chiffres après la virgule.

• Dans un champ DECIMAL(5,3), on peut donc stocker des nombres de 5 chiffres significatifs au maximum, dont 3
chiffres sont après la virgule.
• Par exemple : 12.354, -54.258, 89.2 ou -56.
• DECIMAL(4) est DECIMAL(4, 0) sont deux écritures équivalentes.
• MySQL ne respecte pas toujours cette règle, et utilise parfois l’octet du signe (-) pour stocker les chiffres.

20
Création de tables
• Nombres décimaux :

• FLOAT, DOUBLE et REAL:

• Le mot-clé FLOAT peut s'utiliser sans paramètres, auquel cas quatre octets sont utilisés pour
stocker les valeurs de la colonne.
• Il est possible de spécifier une précision et une echelle d’un FLOAT.

• Quant à REAL et DOUBLE, ils ne supportent pas de paramètres.

• DOUBLE est normalement plus précis que REAL (stockage dans 8 octets, contre stockage
dans 4 octets)
• Mais ce n'est pas le cas avec MySQL qui utilise 8 octets dans les deux cas

21
Création de tables
• Nombres décimaux :

• Valeurs exactes vs valeurs approchées:

• Les nombres stockés en tant que NUMERIC ou DECIMAL sont stockés sous forme de chaînes
de caractères. => Valeurs exactes qui sont stockées

• Les types FLOAT,DOUBLE et REAL sont stockés sous forme de nombres. => Valeurs
approchées qui sont stockées (56,678900000000000001 au lieu de 56,6789)

• S'il est nécessaire de conserver la précision exacte de vos données (données bancaires),
• il est donc conseillé d'utiliser un type numérique à valeur exacte

22
Création de tables
• Données textuelles:

• CHAR vs VARCHAR:

• Pour stocker un texte relativement court (moins de 255 caractères), vous pouvez utiliser les
types CHAR et VARCHAR.
• Ces deux types s'utilisent avec un paramètre qui précise la taille que peut prendre votre texte (entre 1
et 255).
• Un CHAR(x) stockera toujours x caractères, en remplissant si nécessaire le texte avec des espaces vides
pour le completer.
• Un VARCHAR(x) stockera jusqu'à x caractères (entre 0 et x), et stockera en plus en mémoire la taille du
texte stocké.

23
Création de tables
• Données textuelles:

• CHAR vs VARCHAR:
Mémoire Mémoire
Texte CHAR(5) VARCHAR(5)
requise requise
'' ' ' 5 octets '' 1 octet
'tex' 'tex ' 5 octets 'tex' 4 octets
'texte' 'texte' 5 octets 'texte' 6 octets
'texte trop long' 'texte' 5 octets 'texte' 6 octets

• Les caractères accentués occupent deux octets en mémoire.

• Il est donc possible, pour un CHAR(5), d'occuper plus de 5 octets en mémoire (mais impossible d'y stocker plus que 5
caractères).

24
Création de tables
• Données textuelles:

• TEXT :
• On peut utiliser le type TEXT, ou un de ses dérivés TINYTEXT, MEDIUMTEXT ou LONGTEXT pour stocker
des textes de plus de 255 caractères.

Type Longueur maximale Mémoire occupée

TINYTEXT 28 octets Longueur de la chaîne + 1 octet
TEXT 216 octets Longueur de la chaîne + 2 octet
MEDIUMTEXT 224 octets Longueur de la chaîne + 3 octet
LONGTEXT 232 octets Longueur de la chaîne + 4 octet

25
Création de tables
• Données textuelles:

• ENUM :
• Une colonne de type ENUM est une colonne pour laquelle on définit un certain nombre de
valeurs autorisées, de type "chaîne de caractères”
espece ENUM('chat', 'chien')

• Si vous essayez d'introduire une chaîne non autorisée, MySQL stockera une chaîne vide ’’ dans le
champ.
• Pour remplir un champ de type ENUM, deux possibilités s'offrent à vous :
• soit remplir directement avec la valeur choisie (ex. "chat”) ;
• soit utiliser l'index de la valeur

Valeurs NULL ‘’ ‘chat’ ‘chien’

Index NULL 0 1 2
26
Création de tables
• Données textuelles:

• SET :
• Une colonne SET est en effet une colonne qui permet de stocker une chaîne de caractères
dont les valeurs possibles sont prédéfinies par l'utilisateur.
espece SET('chat', 'chien')

• La différence avec ENUM, c'est que l'on peut stocker dans la colonne plusieurs valeurs.
• On pourra stocker dans cette colonne :
• '' (chaîne vide ) ;
• 'chat';
• ‘chien' ;
• 'chat,chien’; SET et ENUM sont des types propres à MySQL.

27
Création de tables
• Données temporelles:
• Quelques types temporels de MySQL : DATE, DATETIME, TIME et YEAR.
• Les vérifications faites par MySQL sont des verifications de base : le jour doit être compris entre 1 et 31
et le mois entre 1 et 12. NB: la date 30 Février est aussi acceptable.
• Date:
• Pour entrer une date, l'ordre des données est la seule contrainte.
• Il faut donner d'abord l'année (2 ou 4 chiffres), ensuite le mois (2 chiffres) , et, pour finir, le jour (2
chiffres), sous forme de nombre ou de chaîne de caractères.
• S'il s'agit d'une chaîne de caractères, n'importe quelle ponctuation peut être utilisée comme délimiteur.

• 'AAAA-MM-JJ' (c'est sous ce format-ci qu'une DATE est stockée dans MySQL)
• 'AAMMJJ' • 'AAAA%MM%JJ'
• 'AAAA/MM/JJ' • AAAAMMJJ (nombre)
• 'AA+MM+JJ' • AAMMJJ (nombre)

28
Création de tables
• Données temporelles:

• DateTime :
• Ce type permet de stocker une heure, en plus d'une date.
• Pour la date, année-mois-jour, et pour l'heure, il faut donner d'abord l'heure, ensuite les
minutes, puis les secondes.
• Si l'on utilise une chaîne de caractères, il faut séparer la date et l'heure par une espace.
• Quelques exemples corrects :

• 'AAAA-MM-JJ HH:MM:SS' (c'est sous ce format-ci qu'un DATETIME est stocké dans MySQL)
• 'AA*MM*JJ HH+MM+SS'
• AAAAMMJJHHMMSS (nombre)

29
Création de tables
• Données temporelles:

• Time :
• Il faut d'abord donner l'heure, puis les minutes, puis les seconds
• Chaque partie pouvant être séparée des autres par le caractère :
• Exemples:
• 'HH:MM:SS'
• 'HHH:MM:SS'
• 'MM:SS'
• 'HHMMSS'
• HHMMSS

30
Création de tables
• Données temporelles:

• Year :
• Un type à utiliser si besoin de stocker juste l’année.
• Il ne prend qu'un seul octet en mémoire.
• On ne peut y stocker que des années entre 1901 et 2155.
• On peut entrer une donnée de type YEAR sous forme de chaîne de caractères ou d'entiers,
avec 2 ou 4 chiffres.
pour afficher un tableau d'une database on utilise : DESC NOM_TAB;

31