Bases de données

Dr Papa Samour DIOP

Enseignant chercheur IPSL/UGB
Qu’est-ce qu’une base de données ?
• Une base de données est un ensemble de données modélisant les objets d’une partie du monde réel et
servant de support à une application informatique.
• Pour mériter le terme de base de données, un ensemble de données non indépendantes doit être interrogeable par le
contenu, c’est à dire que l’on doit pouvoir retrouver tous les objets qui satisfont à un certain critère [[Link]].
• En d’autres termes, c’est un ensemble structuré de données mémorisé sur un support permanent (cohérent,
intégré, partagé) qui peut être manipulé par plusieurs utilisateurs ayant des vues différentes sur ces
données.
• Par données, il faut comprendre : une représentation d’un fait à l’aide d’un code binaire stocké dans la
mémoire de l’ordinateur. Une donnée doit avoir un type. Le type est ensemble d’objets ayant les mêmes
caractéristiques et manipulables par des opérations identiques.
• On distingue :
• Données simples : entier, réel, chaîne de caractères, réel, etc.
• Données de type complexe : composée de données de types simples
• Donnée de type multimédia : texte, image, son vidéo
• Exemple de données :
• 200 (type de données : entier)
• Toyota YARIS Noir DK999999AX (type de données : véhicule)
Usages des bases de données
Voici des exemples classiques d’utilisation des bases de données :
• Gestion des comptes clients d’une banque
• Gestion des commandes d’un site d’e-commerce
• Système de réservation de billets d’avion
• Gestion des étudiants d’une université
• Gestion des personnels d’une entreprise
• Bases de données d’articles scientifiques ….
Les données manipulées dans ces applications sont stockées dans une
base de données.
Objectifs des bases de données
• Centralisation logique de l’information
Non redondance (la même information ne doit pas figurer plusieurs fois
dans la BD
⇒ Ce qui diminue les risques d’erreurs de mise à jour et supprime le problème d’avoir
des informations contradictoires sur une même donnée dans de fichiers différents
• Indépendance données traitement
• Partage des données (accessibilité à plusieurs utilisateurs simultanés)
confidentialité (login, mot de passe)
autorisation d’accès (lecture, écriture)
accès concurrents
• Intégrité des données
règles permettant d’éliminer des donner incorrectes (contrainte d’intégrité
référentielle, unicité de l’identifiant...).
Qu’est-ce qu’un SGBD ?
• Un système de gestion de base de données ou SGBD est un programme
générique qui permet la définition, la mise en œuvre et l’exploitation
(insérer, supprimer, mettre à jour) d’une base de données.
Exemple de SGBD :
• MySQL
• Oracle Database
• Microsoft SQL Server
• Le SGBD va permettre aux utilisateurs d’insérer, de modifier et de
rechercher efficacement des données spécifiques dans une grande masse
de d’informations (pouvant atteindre des milliards d’octets).
• Le SGBD constitue l’interface entre le programme d'application des utilisateurs d'une
part et la Base de données d'autre part. Il sert à masquer à l’utilisateur les détails
complexes liés à la gestion des fichiers.
Architecture d’un SGBD
• Structure en couche : Comme cité précédemment le SGBD constitue
l’interface entre les utilisateurs et la base de données (voir figure ci-
dessous).
Un SGBD est comporte trois couches :
• Couche interne (ou physique): stockage des données sur des
supports physiques, gestions des structures de mémorisation
(fichiers) et des accès (gestions des index et des clés)
• Couche logique : contrôle global et structure globale des données
• Couche externe : dialogue avec les utilisateurs, analyse de leurs
demandes, contrôle des droits d’accès et présentation des résultats,
environnement de programmation (intégration avec un langage de
programmation). La représentation de la base de données est
composée de plusieurs schémas externes.
Objectifs des SGBD
• Le principal objectif d’un SGBD est d’assurer l’indépendance des programmes aux
données, c’est à dire la possibilité de modifier les schémas conceptuel et interne des
données sans modifier les programmes. Cet objectif est justifié afin d’éviter une
maintenance coûteuse des programmes lors des modifications des structures logiques et
physiques.
Les objectifs d’un SGBD sont [[Link]]:
• Indépendance physique des programmes aux données
• Indépendance logique des programmes aux données
• Manipulation des données par des langages non procéduraux
• Administration facilitée des données
• Efficacité des accès aux données
• Partage des données
• Cohérence des données
• Redondance contrôlée des données
• Sécurité des données
Ces objectifs peuvent être classés en plusieurs catégories :
Les objectifs orientés DONNEES
• Non redondance des données : avec une approche base de données, les
fichiers plus ou moins redondants seront intégrés en un seul fichier ou
plusieurs fichiers contenant des données distinctes.
• Partage des données : permettre le partage des données de la base à
plusieurs applications, utilisateurs, simultanément.
• Sécurité des données : les données doivent être protégées contre les accès
(non autorisés, mal intentionnés). D’où la nécessité de contrôler les donnés.
• Cohérence des données : les données sont soumises à certaines règles. Par
exemple : un compte bancaire est rattaché à un et un seul client.
Le SGBD doit vérifier que les applications respectent ces règles et contraintes
d’intégrité. Donc il faut avoir des connaissances sur les données et leur
signification.
Les objectifs orientés TRAITEMENT
• Indépendance physique des données : on peut changer le schéma
physique sans remettre en cause le schéma conceptuel (et les schémas
externes). On peut modifier l'organisation physique des fichiers, rajouter
ou supprimer des méthodes d'accès ;
• Indépendance logique des données : on peut changer le niveau conceptuel
sans remettre en cause les schémas externes ou les programmes
d'application. L'ajout ou le retrait de nouveaux concepts ne doit pas
modifier des éléments qui n'y font pas explicitement référence ;
• Manipulation facile des données (pour les non-informaticiens) : pouvoir les
consulter, les interroger, les mettre à jour
• Manipulation facile des données (pour les informaticiens) : langage de
développement de haut niveau
Les objectifs orientés ORGANISATION
• Administration centralisée des données
• Permettre un contrôle efficace des données;
• Résoudre les conflits entre divers point de vue d’utilisateurs;
• Optimisation des accès aux données
• Optimisation des moyens informatiques
• Fonctions
• Administrateur des données
• Administrateur de base de données
• Centralisation/décentralisation
• La base de données peut être distribué/partagé.
Fonctions d’un SGBD
Un SGBD doit permettre de :
• Décrire les données qui seront stockées
• Gérer les données
• Manipuler des données (ajout, modification, suppression d’informations) ,
• Assurer la cohérence (ou intégrité́) des données (contraintes de domaines,
d’existence, etc.),
• Assurer la confidentialité́ des données (mots de passe, autorisation...),
• Résoudre des problèmes d’accès multiples aux données (blocages,
transactions parallèles),
• Prévoir des procédures de reprise en cas de panne ( copies de sauvegarde,
journaux)
Fonctions d’un SGBD
• Obtenir des renseignements à partir des données stockées au moyen de
requêtes (sélection, tri, calcul, agrégation, etc.),
• Permettre l’écriture d’applications indépendantes de l’implémentation
physique des données (codage, supports d’enregistrement) et aussi
indépendantes que possible de l’implémentation logique des données
(index, décomposition en « fichiers logiques »)
Un SGBD sépare la partie description des données, des données elles
mêmes. Cette description est stockée dans un dictionnaire de données
(également géré dans le SGBD) et peut être consultée par les utilisateurs.
De plus, un SGBD doit permettre d’écrire des applications indépendantes de
l’implémentation physique des données (codage des données, ordre dans
lequel sont enregistrées les données, support d’enregistrement, etc.)
Types de SGBD
• Historiquement les premiers types de SGBD étaient de type
hiérarchique, puis sont apparu les SGBD de type réseau.
• Actuellement la plupart des SGBD sont de type relationnel .On note
aussi l’apparition sur le marché des SGBD de type objet.
• La différence entre ces types de SGBD réside dans les modèles sur
lesquels ils s’appuient pour représenter les données.
• Modèle hiérarchique :
• Les données sont représentées sous forme d’une structure arborescente
d’enregistrements. Cette structure est conçue avec des pointeurs de détermine les
chemins d’accès aux données
• Modèle réseau
• La structure des données peut être visualisée sous la forme d’un graphe quelconque.
Comme pour le modèle hiérarchique, la structure est conçue avec des pointeurs et
détermine le chemin d’accès aux données
• Pour ces deux modèles les programmes ne sont pas indépendant de la
structure logique de la base et du chemin d’accès aux données : ils doivent
décrire comment retrouver les données. La suppression par exemple d’un
index entraîne la réécriture de tous les programmes qui l’utilisaient
• Modèle Relationnel
• Fondé sur la théorie mathématique des relations, le modèle relationnel fournit une
représentation très simple des données sous forme de tables constituées de lignes
et de colonnes. De plus il n’y a pas de pointeur qui fige la structure de la base.
La souplesse apportée par cette représentation a permis le développement de
langages puissants non procéduraux. Dans ces langages le programmeur indique
quelles informations il veut obtenir et c’est le SGBD qui trouve la manière d’arriver
au résultat. Le programmeur ou l’utilisateur n’a plus à naviguer dans la base pour
retrouver ses données. Ces langages peuvent être utilisés par des non informaticiens
et permettent l’écriture de programmes indépendants de la structure logique et
physique des données
• Modèle Objet
• Les données sont représentées sous forme d’objets au sens donné par les langages
orientés objet. Les données sont enregistrées avec les procédures et les fonctions
qui permettent de les manipuler. Les SGBD objets supportent aussi la notion
d’héritage entre classes d’objets.
Types d’utilisateurs d’un SGBD
• La gestion d’une base de données fait intervenir plusieurs types
acteurs : administrateur, programmeur, utilisateur final. Une même
personne peut occuper plusieurs rôles et un rôle peut être occupé
par plusieurs personnes.
• L’administrateur de la base est chargé du contrôle de la base de données. Il
permet l’accès aux données, aux applications ou individus qui y ont droit et
de conserver de bonnes performances d’accès à ces données. Il est aussi
chargé des sauvegardes et des procédures de reprise après panne.
• Le programmeur d’applications utilise la base de données pour construire ses
applications. Il a le droit de créer de nouvelles tables et les structures
associées (vues, index, cluster, etc.). Il définit avec l’administrateur de la base
les droits qui seront accordés aux utilisateurs des applications qu’il
développe.
• L’utilisateur final n’a accès qu’aux données qui lui sont utiles.
L’administrateur de la base de données peut lui en accorder certains droits :
consultation, modification, suppression de données. En général, il n’a pas le
droit de créer de nouvelles tables ni d’ajouter ou d’enlever des index. En
définitive l’utilisateur final n’est pas un expert car il :
• sait ce qu'il veut,
• ne sait pas forcément ce qu'il faut faire pour l'obtenir,
• ne veut pas savoir comment ça marche,
• veut en faire un minimum pour faire tourner son application.
Cycle de vie d'une base de données
Modèles de données, schémas et langages :
Notion de modèle de données
• Un modèle de données est un ensemble de concepts permettant de décrire la
structure d'une base de données. Un modèle est souvent représenté au moyen
d'un formalisme graphique permettant de décrire les données (ou plus
précisément les types de données) et les relations entre les données.
• On distingue trois niveaux de modélisation pour les bases de données :
• Le modèle conceptuel
Il permet de décrire le réel selon une approche ontologique, sans prendre en compte les
contraintes techniques.
• Le modèle logique
Il permet de décrire une solution, en prenant une orientation informatique générale (type de
SGBD typiquement), mais indépendamment de choix d'implémentation précis.
• Le modèle physique
Il correspond aux choix techniques, en terme de SGBD choisi et de sa mise en œuvre
(programmation, optimisation, etc.).
• Exemple de formalisme de modélisation conceptuelle
• Le modèle Entité-Association (Chen) a été le plus répandu dans le cadre de la
conception de bases de données.
• Le modèle UML, qui se généralise pour la conception en informatique, se fonde sur
une approche objet.
• Exemple de formalisme de modélisation logique
• Le modèle relationnel est le modèle dominant.
• Le modèle relationnel-objet (adaptation des modèles relationnel et objet au cadre
des SGBD) est actuellement en pleine croissance.
• Le modèle objet "pur" reste majoritairement au stade expérimental et de la
recherche.
• Des modèles plus anciens (hiérarchique, réseau, etc.) ne sont plus guère utilisés
aujourd'hui.
Notion de schéma de données
Description, au moyen d'un langage formel, d'un ensemble de données dans le contexte d'une BD.
Un schéma permet de décrire la structure d'une base de données, en décrivant l'ensemble des
types de données de la base. L'occurrence d'une base de données est constituée de l'ensemble des
données correspondant aux types du schéma de la base.
Exemple : Schéma de base de données
• Etudiant (NumEtud, nomcomplet, ville, NumCours, NumClasse)
• Cours(NumCours, titre)
• Classe(NumClasse, nomClasse,niveau)
Exemple : Instance de base de données
• Etudiant (172, 'Mamadou Diop', 'Dakar’,1,1)
• Etudiant (173, 'Bineta Fall', 'Thies’,1,1)
• Etudiant (174, 'Amadou Lam', 'Louga’,1,2)
• Cours(1, 'SGBD’)
• Cours(2, 'Dev Web’)
• Classe(172, 'Licence 1', 'Licence')
• Classe(173, 'Licence 2', 'Licence')
On distingue trois niveaux d'abstraction de
schémas :
• Le niveau conceptuel
Il permet de décrire les entités et les associations du monde réel. Il s'agit du schéma global de la
base de données, il en propose une vue canonique.
Le niveau conceptuel correspond au modèle conceptuel.
• Le niveau externe
Il permet de décrire les entités et les associations du monde réel, mais vues d'un utilisateur ou
d'un groupe d'utilisateurs particuliers (on parle d'ailleurs également de "vue" pour un schéma
externe). Il s'agit d'une restriction du schéma conceptuel orientée vers un usage précis. Il existe
généralement plusieurs schémas externes pour un même schéma conceptuel.
Le niveau externe correspond à un sous ensemble du modèle conceptuel restreint aux points de
vue de certains utilisateurs.
• Le niveau interne
Il correspond à l'implémentation physique des entités et associations dans les fichiers de la base.
Le niveau interne correspond aux modèles logiques et physiques.
• Remarque :
• Les trois niveaux, conceptuel, externe et interne, sont les trois niveaux distingués par le groupe de
normalisation ANSI/X3/SPARC.
Notion de langage de données
C’est un langage informatique permettant de décrire et de manipuler
les schémas d'une BD d'une manière assimilable par la machine.
♦ Synonyme : Langage orienté données.
• Exemple : SQL
• SQL est le langage orienté données consacré aux SGBD relationnels et
relationnel-objet.
Un langage de données peut être décomposé en trois sous langages :
♦ Le Langage de Définition de Données
• Le LDD [Langage de Définition de Données] permet d'implémenter le schéma
conceptuel (notion de table en SQL) et les schémas externes (notion de vue
en SQL).
♦ Le Langage de Contrôle de Données
• Le LCD [Langage de Contrôle de Données] permet d'implémenter les droits
que les utilisateurs ont sur les données et participe donc à la définition des
schémas externes.
♦ Le Langage de Manipulation de Données
• Le LMD [Langage de Manipulation de Données] permet l'interrogation et la
mise à jour des données. C'est la partie du langage indispensable pour
exploiter la BD et réaliser les applications.
En résumé
Le langage SQL : Structured Query
Language
• Le Structured Query language (SQL), ou langage structuré de
requêtes, est un pseudo- langage informatique (de type requête)
standard et normalisé, destiné à interroger ou à manipuler une base
de données relationnelle avec :
• un langage de définition de données (LDD, ou en anglais DDL, Data definition
language) : il permet de créer des tables dans une base de données
relationnelle, ainsi que d'en modifier ou en supprimer ;
• un langage de manipulation de données (LMD, ou en anglais DML, Data
manipulation language) : il permet de sélectionner, insérer, modifier ou
supprimer des données dans une table d'une base de données relationnelle ;
• un langage de contrôle de données (LCD, ou en anglais DCL, Data control
language) : il permet de définir des permissions au niveau des utilisateurs
d'une base de données ;
• un langage de contrôle de transactions (LCT, ou en anglais TCL, Transaction
control language) : il permet de gérer les transactions, c'est-à-dire rendre
atomique divers ordres enchaînés en séquence;
• et d'autres modules destinés notamment à écrire des routines (procédures,
fonctions ou déclencheurs) et interagir avec des langages externes.
Gestion des utilisateurs
Ajout d'un utilisateur
• Methode 1
• CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
• Pour chaque compte, CREATE USER crée un nouvel enregistrement dans la
table [Link], sans aucun droit. Une erreur survient si le compte existe
déjà. Le compte peut recevoir un mot de passe avec la clause optionnelle
IDENTIFIED BY
• Methode 2
• GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON {tbl_name
| * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
• Les commandes GRANT permet à l'administrateur système de créer des
comptes utilisateur et de leur donner des droits.
 • CREATE USER [IF NOT EXISTS] 'new_user_name'@'host_name’ IDENTIFIED BY 'user_password'
• Dans la syntaxe ci-dessus, remplacez new_user_name par le nom du nouvel utilisateur et
host_name par le nom de l'hôte à partir duquel l'utilisateur se connecte au serveur
MySQL.
• Facultativement, définissez host_name sur 'localhost' si vous voulez que l'utilisateur
puisse se connecter au serveur MySQL uniquement à partir de l'hôte local, ce qui signifie
"cet ordinateur". Si ce n'est pas le cas, vous pouvez utiliser l'adresse IP de la machine
distante comme nom d'hôte, par exemple :
• CREATE USER 'new_user_name'@’[Link]’ IDENTIFIED BY 'user_password';
• Si vous souhaitez que l'utilisateur puisse se connecter à partir de n'importe quel hôte,
utilisez le caractère générique '%' comme host_name .
• Enfin, définissez un mot de passe pour le nouvel utilisateur après les mots clés
IDENTIFIED BY.
• Notez que l' option IF NOT EXISTS permet de s'assurer que le même utilisateur n'a pas
été créé auparavant.
• Une fois que vous avez terminé la création du nouvel utilisateur, n'oubliez pas d'accorder
des privilèges à l'utilisateur pour lui permettre d'accéder à la base de données MySQL.
Sinon, l'utilisateur n'aura aucune autorisation pour accéder ou manipuler la base de
données de quelque manière que ce soit.
Comment accorder des privilèges et
ajouter des autorisations à l'utilisateur
• Pour fournir à un utilisateur l'accès à la base de données et lui donner des
autorisations, vous devez généralement utiliser l'instruction GRANT suivante :
• GRANT permission_type ON privilege_level TO 'new_user_name'@'host_name’;
• Les types de privilèges autorisés les plus courants pouvant être utilisés pour les
instructions GRANT et REVOKE :
• ALL PRIVILEGES – L'utilisateur obtient tous les privilèges à un niveau d'accès spécifié.
• CREATE – L'utilisateur obtient l'autorisation de créer des bases de données et des tables.
• DROP – L'utilisateur obtient l'autorisation de supprimer des bases de données et des tables.
• DELETE – L'utilisateur obtient l'autorisation de supprimer des lignes d'une table spécifique.
• INSERT – L'utilisateur obtient l'autorisation d'insérer des lignes dans une table spécifique.
• SELECT – L'utilisateur obtient l'autorisation de lire une base de données.
• UPDATE – L'utilisateur obtient l'autorisation de mettre à jour les lignes du tableau.
Accorder des privilèges sur la base de
données à l'utilisateur
• Pour accorder tous les privilèges à un compte utilisateur sur toutes les bases de
données via l' invite de commande MySQL, vous devez attribuer des privilèges globaux
et utiliser la syntaxe *.* après le mot-clé ON :

• GRANT ALL PRIVILEGES ON *.* TO new_user_name@host_name;

• Dans cet exemple, le nouvel utilisateur se voit accorder le niveau de privilège maximal
possible : il obtient l'autorisation de lire, de modifier, d'exécuter des commandes et
d'effectuer n'importe quelle tâche sur toutes les bases de données et tables.
• Soyez prudent car cela peut compromettre la sécurité de votre base de données et avoir des
conséquences négatives.
• Au lieu de cela, vous voudrez peut-être accorder des autorisations limitées. Par
exemple, vous souhaitez autoriser votre nouvel utilisateur à accéder uniquement
à une certaine table de la base de données :
• GRANT ALL PRIVILEGES ON database_name.table_name TO user_name@host_name;
• Dans ce cas, l'utilisateur se voit accorder des privilèges au niveau de la table, qui
s'appliquent à toutes les colonnes de la table. Par conséquent, ils obtiennent
l'autorisation de lire, d'éditer et de modifier le tableau selon les besoins.
• Cependant, il peut également être nécessaire de restreindre cet accès et de
donner la possibilité d'effectuer certaines opérations dans des objets de base de
données spécifiés.
• Par exemple, ci-dessous, vous pouvez voir que l'utilisateur dispose de plusieurs
autorisations : il a l'autorisation d'utiliser l'instruction SELECT sur deux colonnes
de la base de données, d'exécuter UPDATE sur une troisième colonne et
d'exécuter INSERT sur la quatrième colonne de la même base de données :
• GRANT SELECT (column1,column2), UPDATE(column3), INSERT (column4) ON
database_name TO user_name@host_name;

• Au moment où vous avez fini de fournir l'accès à la base de données aux
nouveaux utilisateurs, assurez-vous de recharger tous les privilèges en
exécutant :
• FLUSH PRIVILEGES;
• Suppression des droits d'un utilisateur
• REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
{tbl_name | * | *.* | db_name.*} FROM user [, user] ...
• REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
• exemple: REVOKE ALL PRIVILEGES ON ipsl1.* FROM user_ipsl1;
• Changer de mot de passe
• SET PASSWORD = PASSWORD('some password’)
• SET PASSWORD FOR user = PASSWORD('some password')
Commentaires
• La commande SET PASSWORD assigne un mot de passe à un compte utilisateur existant.
• La première syntaxe modifie le mot de passe de l'utilisateur courant. Tout client qui s'est
connecté avec un compte non-anonyme peut changer le mot de passe pour ce compte.
• La seconde syntaxe modifie le mot de passe pour un compte tiers, sur le serveur
• La valeur de user doit être donnée au format user_name@host_name, où user_name et
host_name sont tels que listés dans les colonnes User et Host de la table [Link].
• mysql> SET PASSWORD FOR 'toto'@’%.[Link]' = PASSWORD('newpass');
• C'est l'équivalent de la commande suivante :
• mysql> UPDATE [Link] SET Password=PASSWORD('newpass') -> WHERE User='toto'
AND Host=‘%.[Link]';
• mysql> FLUSH PRIVILEGES;
Suppression d'un utilisateur
• DROP USER user_name
• Cette commande efface un utilisateur qui n'a aucun droits; sinon, il
faut d'abord lui retirer ses droits.
TP
• Dans cette commande, ‘nouveau_utilisateur’ est le nom que nous avons donné à notre
nouvel utilisateur. Et ‘mot_de_passe’ est le mot de passe de cet utilisateur. Vous pouvez
remplacer ces valeurs par les vôtres, à l’intérieur des guillemets.
• CREATE USER 'nouveau_utilisateur'@'localhost' IDENTIFIED BY 'mot_de_passe';

• La simple création de ce nouvel utilisateur ne suffit pas. Vous devez lui accorder des
privilèges. Pour accorder à l’utilisateur récemment créé tous les privilèges pour la base
de données, exécutez la commande suivante :
• GRANT ALL PRIVILEGES ON * . * TO 'nouveau_utilisateur'@'localhost';

• Pour que les changements prennent effet, il faut immédiatement supprimer ces
privilèges en tapant la commande
• FLUSH PRIVILEGES
• Pour utiliser l’une de ces options, remplacez
simplement TYPE_DE_PERMISSION par le mot-clé approprié. Pour
appliquer plusieurs privilèges, séparez-les avec une commande
comme celle-ci. Par exemple, nous pouvons assigner les
privilèges CREATE et SELECT à notre utilisateur MySQL non root avec
cette commande
• GRANT CREATE, SELECT ON * . * TO 'nom_utilisateur'@'localhost';
• Afin de trouver quels privilèges ont déjà été accordés à un utilisateur
MySQL, vous pouvez utiliser la commande SHOW GRANTS :
• SHOW GRANTS FOR 'nom_utilisateur'@'localhost';
Création d’une base de données en SQL
• La création d’une base de données en SQL est possible en ligne de
commande. Même si les systèmes de gestion de base de données
(SGBD) sont souvent utilisés pour créer une base, il convient de
connaître la commande à utiliser, qui est très simple.
Syntaxe
• 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 nom_base
Base du même nom qui existe déjà
• 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 nom_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.
Création des tables
• Avant de voir comment créer une table, il vous faut savoir ce qu'est
une table ? C'est tout simplement une structure de données. Elle
contient des enregistrements (lignes) qui ont des valeurs spécifiques
à des colonnes.
• CREATE TABLE tablename (
colonne type contraintes_pour_colonne,
...
contraintes_pour_table )
Explications sur la syntaxe
• tablename : C'est tout simplement le nom de la table que vous allez créer
• colonne : Le nom de la colonne
• type : Le type de données que contient la colonne
• contraintes_pour_colonne : Les contraintes pour la colonne
• contraintes_pour_table : Les contraintes pour la table
Comme vous le voyez, ce n'est pas si compliqué de créer une table.
Mais les possibilités sont grandes.
• On peut aussi définir des valeurs par défaut pour les colonnes avec
DEFAULT suivi de la valeur. On peut mettre une valeur par défaut
pour chaque colonne.
• On va commencer par un exemple, une table recensant des
personnes, on a le nom et le prénom de chaque personne, ainsi que
son âge :
• CREATE TABLE personnes ( nom VARCHAR(50), prenom VARCHAR(50), age
SMALLINT )
• Le nombre entre parenthèses est tout simplement la taille du type.
• Il faut savoir que les types sont variables et on peut donc leur donner la taille
que l'on veut sans toutefois dépasser certaines limites.
Clefs primaires et unicité
• Une clef primaire est un moyen d'identifier de manière unique un enregistrement et
d'améliorer ainsi les vitesses de recherche et de parcours pour les requêtes.
• L'unicité permet d'empêcher qu'une table contienne plusieurs des valeurs qui ne devrait
pas exister en double normalement. Pour les colonnes autre que la clé primaire, on
pourra utiliser la clause UNIQUE pour dire qu'une colonne (ou un ensemble de colonnes)
ne peut pas contenir deux fois la même valeur (ou jeu de valeurs).
• Prenons par exemple une table personnes dont chaque enregistrement possède un id,
un nom et un prénom,une langue et un numéro d'AVS. Tout d'abord, réfléchissons à ce
qui doit être unique :
• L'id : Oui, il doit être unique, car ce sera l'identifiant de l'enregistrement
• Le nom : Non
• Le prénom : Non, par contre, le couple nom-prénom doit être unique (pas dans tous les cas, je
vous l'accorde, mais pour l'exemple, c'est mieux)
• Le numéro d'AVS : Oui (l'AVS est l'assurance vieillesse)
• La langue : Non
• L'id étant l'identificateur de l'enregistrement, il en sera la clé primaire. AVS
sera unique et on va créer une contrainte d'unicité sur le couple nom
prénom :
CREATE TABLE personnes ( id INT NOT NULL PRIMARY KEY, nom
VARCHAR(50),
prenom VARCHAR(50),
avs INT UNIQUE,
language VARCHAR(50),
CONSTRAINT u_nom_prenom UNIQUE (nom, prenom) )
• La seule chose de nouveau dans cette commande est la syntaxe pour la
contrainte d'unicité sur le couple nom-prénom. En fait, on crée une
nouvelle contrainte, on lui donne un nom et on définit quelle est cette
contrainte, dans notre cas, on dit qu'on ne peut pas avoir plusieurs fois une
personne avec le même couple nom et prénom.
• On n'a pas besoin de dire que id est unique, car une clé primaire l'est
automatiquement.
• On peut aussi définir des clés primaires sur deux colonnes. Imaginons
une table associative (qui relie deux tables) auteurs_livres qui met en
relation un auteur et un livre, on va utiliser une clé qui prend les deux
valeurs comme index :
CREATE TABLE auteurs_livres( auteur_id INT, livre_id INT,
CONSTRAINT id PRIMARY KEY (auteur_id, livre_id) )
• La syntaxe est la même que celle pour notre contrainte d'unicité.
Contraintes de type
• Une contrainte de type est tout simplement une contrainte qui dit
comme quoi les valeurs de cette colonne doivent toutes être du type
spécifié.
• Pour créer une telle contrainte, il suffit tout simplement de spécifier
un type pour la colonne, ainsi une colonne déclarée INT n'acceptera
pas de chaînes de caractères.
• Comme on l'a vu plus haut, les types sont variables, ils n'ont pas une
taille fixe. Par exemple, on peut dire qu'une colonne est une chaîne
de caractères de 5 lettres et une autre de 200 lettres. Pour cela, on
fait suivre le nom du type par sa taille entre parenthèses.
Possibilité de laisser blanc
• La première chose à spécifier pour une colonne est si on lui laisse ou non le
droit d'être laissée vide. Pour dire qu'une colonne ne peut pas être vide, on
va utiliser NOT NULL et pour dire qu'une colonne peut éventuellement être
vide, on va employer NULL.
• Par exemple, dans le cas d'une base de données recensant les membres
d'un forum, on est obligé de spécifier le pseudo et le password, mais le
genre peut-être laissé à discrétion du membre. On va donc faire une table
comme ça :
CREATE TABLE t_users( pseudo VARCHAR(20) NOT NULL, password VARCHAR(16)
NOT NULL, genre VARCHAR(10) NULL )
• Ainsi on pourra avoir des personnes ayant renseignés leur genre et
d'autres dont l'info restera secrète.
Contraintes de validation
• Une contrainte de domaine est tout simplement une contrainte qui
permet de valider la valeur de la colonne. A chaque fois que l'on va
ajouter un élément dans cette colonne, cette condition va se vérifier
et l'enregistrement ne se fera qu'en cas de passage de la validation.
Néanmoins, ces validations peuvent se révéler lourde, il ne faut donc
pas en abuser.
• Prenons par exemple, une table tests qui contiendrait une colonne
note, on a la contrainte qu'une note est obligatoirement contenue
entre 0 et 20.
CREATE TABLE tests ( id INT NOT NULL PRIMARY KEY, nom
VARCHAR(20), note INT CHECK(VALUE BETWEEN 0 AND 20) )

CREATE TABLE clients( Nom char(30) NOT NULL, Prenom char(30)

NOT NULL, Age integer, check (age < 100), Email char(50) NOT NULL,
check (Email LIKE "%@%")
• La syntaxe ne montre pas de difficultés particulières, il suffit
d'employer le mot clé CHECK suivi de la condition entre parenthèses.
• On peut définir toutes sortes de prédicats (conditions de validation)
pour la validation, après, c'est à vous de créer le prédicat qui vous
sert le mieux.
• Il faut noter que l'on ne peut contrôler que les valeurs saisies dans
l'insertion en cours et qu'une contrainte de validation ne peut pas
aller faire des vérifications sur des valeurs préalablement entrées
dans la table.
Contraintes d'intégrité référentielle
• Tout d'abord, qu'est ce que l'intégrité référentielle ? C'est un
ensemble de règles qu'on définit entre plusieurs tables qui nous
permet d'être sûrs qu'un id qui pointe vers une autre table fait
toujours référence à une valeur existante.
• Prenons le cas d'une table livres avec un champ auteur_id qui fait
référence au champ id de la table auteurs. Le champ auteur_id doit
obligatoirement être égal à une valeur présente dans la colonne id
d'auteurs. Pour cela, il va nous falloir définir une clé étrangère sur
auteur_id :
CREATE TABLE livres( id INT NOT NULL PRIMARY KEY, auteur_id INT FOREIGN
KEY REFERENCES auteurs(id) )
• On a donc employé FOREIGN KEY pour définir cette contrainte d'intégrité
sur le champ auteur_id. On fait suivre FOREIGN KEY de REFERENCES suivi
du nom de la table avec le nom du champ entre parenthèses. On est ainsi
assuré qu'on ne peut pas insérer un livre qui a un auteur qui n'existe pas.
• On peut aussi définir l'action à effectuer lors d'une suppression ou d'une
modification de ce vers quoi pointe la clé la étrangère.
• Si par exemple vous voulez que si vous supprimez un auteur, tous les livres
de cet acteur soient supprimés, il vous suffit de faire :
auteur_id INT FOREIGN KEY REFERENCES auteurs(id) ON DELETE CASCADE
• Si au contraire, vous voulez que la suppression échoue, vous pouvez faire :
auteur_id INT FOREIGN KEY REFERENCES auteurs(id) ON DELETE NO ACTION
• Vous pouvez aussi faire la même chose avec ON UPDATE pour les
modifications.
 Clé étrangère
• CREATE TABLE etudiant( idetudiant INT NOT NULL
PRIMARY KEY, idpersonne INT FOREIGN KEY REFERENCES
Personne(idPersonne) )

• //Ajouter un attribute dans la table Etudiants

apres creation de la table
• ALTER TABLE etudiant ADD idpersonne INT(11)

• //Ajouter une clé étrangère

• ALTER TABLE etudiant ADD FOREIGN KEY (
idpersonne) REFERENCES Personne(idPersonne);
• //Afficher les contraintes d’un table
• SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHE
RE TABLE_NAME = 'etudiant’;

• //Supprimer les contraintes d’une table

• ALTER TABLE etudiant DROP FOREIGN KEY etudiant_ibfk_1;
Valeur par défaut
• Il est aussi possible d’attribuer une valeur par défaut à chaque
colonne. La valeur par défaut est utile lorsqu’aucune valeur de
colonne n’a été spécifiée au moment de l’insertion des données dans
la table. S'il n'y a pas de valeur par défaut pour une colonne, elle est
NULL.
• La clause DEFAULT doit être suivie par la valeur à affecter. Cette
valeur peut être un des types suivants :
• constante numérique
• constante alphanumérique (chaîne de caractères)
• le mot clé NULL
Forcer la saisie d'un champ
• Le mot clé NOT NULL permet de spécifier qu'un champ doit être saisi,
c'est-à-dire que le SGBD refusera d'insérer des tuples dont un champ
comportant la clause NOT NULL n'est pas renseigné.
Modification de tables
• La modification d'une table implique la modification de la structure
de la table.
ALTER TABLE [nom_schema].nom_table alter_specification [,
alter_specification]
• alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name]
| CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER
col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name | DROP PRIMARY KEY
• Ajout d'une colonne
ALTER TABLE etudiants ADD login VARCHAR(50) DEFAULT 'etudiant' FIRST;
• Modification d'une colonne
ALTER TABLE etudiants MODIFY login VARCHAR(50) DEFAULT 'toto';
• Renommez une colonne
ALTER TABLE etudiants CHANGE login identifiant VARCHAR (50) DEFAULT 'toto';
• Suppression d'une colonne
ALTER TABLE etudiants DROP identifiant;
• Renommez une table
ALTER TABLE etudiants RENAME etudiant;
Manipulation de données
Par manipulation de données, on entend la suppression, l'ajout et la
modification de données sur la base.
Insertion
• Pour ajouter un ou plusieurs nouveaux enregistrements dans la base de
données, il vous faudra employer la requête INSERT. En voici la syntaxe :
INSERT INTO table [(colonnes)] VALUES (valeurs) | SELECT | DEFAULT VALUES

• Explications sur la syntaxe

• table : le nom de la table cible
• colonnes : les colonnes dans lesquelles on veut insérer quelque chose ou rien si on
veut insérer quelque chose dans toutes les colonnes
• valeurs : Les valeurs pour chaque colonne
• SELECT : On peut aussi insérer le résultat d'une requête select dans notre table
• DEFAULT VALUES : On va entrer un nouvel enregistrement qui contiendra pour
chaque colonne la valeur par défaut de celle-ci.
Exemple : Si on veut insérer une nouvelle personne dans la table
personne, on procédera ainsi :
INSERT INTO personnes (nom, prenom) VALUES ("NDIAYE", "Oumar")
• Ou alors, si on veut insérer une personne avec les valeurs par défaut :
INSERT INTO personnes DEFAULT VALUES
• Maintenant, un exemple un peu plus complexe en utilisant la clause
SELECT.
On veut rajouter tous les auteurs dans la table t_personnes :
• INSERT INTO t_personnes (nom, prenom, age)
• Récupération de données
Pour aller chercher des données dans la base, on va employer la
requête SELECT dont voici la syntaxe :
• SELECT * ou liste_colonnne_à_inclure FROM table [WHERE predicats]
[ORDER BY ] liste_colonne_pour_tri [ASC | DESC]
Explication de la syntaxe
• * ou liste_colonne_à_inclure : C'est en fait ce que vous cherchez. Si vous
voulez toutes les colonnes de la table, vous pouvez employer * sinon,
spécifier les colonnes dont vous avez besoin.
• table : Le nom de la table dans laquelle vous allez chercher
• predicats : Conditions pour la recherche. Vous pouvez spécifier des
conditions pour ne pas prendre toute la table, mais seulement certains
enregistrements.
• liste_colonne_pour_tri : Vous pouvez trier les enregistrements dans
l'ordre que vous voulez, par colonne.
• ASC ou DESC : C'est tout simplement l'ordre de tri, ASC pour normal et
DESC pour à l'envers.
• Un petit exemple, vous voulez récupérer dans la table t_auteurs, tous
les auteurs de langue francaise et les trier par prénom, de plus, vous
voulez tous les champs sauf l'id :
SELECT nom, prénom, langue FROM t_auteurs WHERE langue = 'Français'
ORDER BY prenom
Jointures
• Les jointures vous permettront de complexifier les requêtes SELECT pour
les rendre plus puissantes et pour récupérer plus d'informations qu'avec
une simple requête SELECT.
• Les jointures sont un moyen de mettre en relation plusieurs tables. Elles
permettent d'exploiter plus à fond le modèle relationnel des bases de
données. Cela permet donc de combiner des données depuis plusieurs
tables. Il y a deux manières d'exploiter les jointures, soit au moyen de
requêtes simples que nous avons déjà vu, soit au moyen de la clause JOIN,
ce qui est conseillé.
• Un simple utilisateur qui demande un listage de livres aimerait bien avoir le
nom de la langue plutôt que son id qui ne lui servira à rien, ainsi avec les
jointures, vous lui fournissez le livre avec le nom de la langue dans laquelle
il a été écrit.
Avec le SQL de base
• Nous allons apprendre à utiliser ici les jointures avec la clause
WHERE, néanmoins, il faut savoir que ceci est hors norme SQL2 et
que son seul intérêt est la compatibilité avec de vieux SGBD. C'est
pourquoi, il est conseillé fortement d'utiliser la clause JOIN.
Considérons deux tables :
• CREATE TABLE t_langues( langue_id INT PRIMARY KEY, langue_nom
VARCHAR(50) UNIQUE )
• CREATE TABLE t_ouvrages( ouvrage_id INT PRIMARY KEY, ouvrage_titre
VARCHAR(150) UNIQUE, ouvrage_langue INT )
• Nous voulons récupérer une liste avec les titres des ouvrages et la
langue, et tout cela en une seule requête bien sûr. On va donc utiliser
une requête SELECT sur deux tables :
SELECT langue_nom, ouvrage_titre
FROM t_langues, t_ouvrages WHERE langue_id = ouvrage_langue
• Il ne faut pas oublier de mettre la condition, sinon, il va renvoyer
toutes les langues associées avec tous les auteurs, ce qui nous
donnera un nombre de résultats égal au nombre de langues multiplié
par le nombre d'ouvrages, ce qui ne sert à rien !
• Avec la requête que vous venons de faire, dans le cas ou une langue
n'a pas d'ouvrages y référant elle n'est pas présente dans la liste.
Nous pourrons résoudre ce problème avec les JOIN.
Quelques clauses :
• Distinct
SELECT DISTINCT "nom de colonne" FROM "nom de table"
• And/Or
SELECT "nom de colonne" FROM "nom de table"
WHERE "condition simples" {[AND|OR] "condition simples"}+
• In
SELECT "nom de colonne"
FROM "nom de table"
WHERE "nom de colonne" IN ('valeur1', 'valeur2', ...)
• Between
SELECT "nom de colonne"
FROM "nom de table"
WHERE "nom de colonne" BETWEEN 'valeur1' AND 'valeur2'
• Like
SELECT "nom de colonne"
FROM "nom de table"
WHERE "nom de colonne" LIKE {modèle}
• Order By
SELECT "nom de colonne"
FROM "nom de table"
[WHERE "condition"]
ORDER BY "nom de colonne" [ASC, DESC]
• Count
SELECT COUNT("nom de colonne") FROM "nom de table"
• Group By
SELECT "nom de colonne 1", SUM("nom de colonne 2") FROM "nom de table"
GROUP BY "nom de colonne 1"
Suppression
Pour supprimer un ou plusieurs enregistrements d'une table, il suffit d'employer la
requête DELETE :
• DELETE FROM table [WHERE predicat]
Explications sur la syntaxe :
• table : La table dans laquelle on veut faire la suppression
• predicat : La condition qui va définir quelles lignes nous allons supprimer Ou pour
vider la table :
• TRUNCATE TABLE "nom de table" Par exemple, si on veut supprimer tous les
auteurs :
• DELETE FROM t_auteurs
Ou alors tous les livres de plus de 1500 pages
DELETE FROM t_livres WHERE pages > 1500
Modification
• Pour modifier un ou plusieurs enregistrements existants, c'est la requête
UPDATE qui va entrer en jeu :
UPDATE table SET colonne1 = valeur1 [, colonne2 = valeur2 ...] [WHERE predicat]
• Explications sur la syntaxe :
• table : La table dans laquelle on va faire les modifications
• colonne : la colonne dont on va modifier la valeur
• valeur : La nouvelle valeur
• predicat : La condition pour sélectionner les lignes que nous allons modifier
• Par exemple, si on veut que toutes les personnes aient 18 ans, on fera :
UPDATE t_personnes SET age = 18
• Ou encore, si on veut que toutes les personnes de plus de 18 ans
s'appellent Oumar NDIAYE, on procédera ainsi :
UPDATE t_personnes SET nom = 'NDIAYE' , prenom = 'Oumar' WHERE age > 18