ADMINISTRATION BASE DE DONNÉES

Administration base de données

• Objectifs du cours :
 Administration base de données

Chapitre I- Architecture du SGBD :

1. Modèle client-serveur et communication réseau.

2. Composants principaux (processus serveur, gestionnaires mémoire,

gestionnaires de requêtes).

3. Gestion des fichiers et organisation des données sur disque.

 Architecture du SGBD
1- Le modèle client-serveur
Le modèle client-serveur est une architecture largement utilisée dans les SGBD. Voici comment il
fonctionne :

a-Modèle client-serveur :
• Le client est une application ou un utilisateur final qui envoie des requêtes au serveur. Par
exemple, un client peut demander des données spécifiques ou effectuer une opération sur la
base de données (insertion, mise à jour, suppression).

• Le serveur est le composant principal qui gère les bases de données, traite les requêtes des
clients, exécute les opérations demandées et renvoie les résultats au client.

• Ce modèle permet une séparation claire entre l'interface utilisateur (côté client) et la gestion
des données (côté serveur).
 Architecture du SGBD
b-Communication réseau :

• Les clients et les serveurs communiquent via des protocoles réseau, tels que TCP/IP, en utilisant
des commandes standardisées (par exemple, SQL pour les bases de données relationnelles).

• Les SGBD modernes supportent des connexions distantes, ce qui permet aux clients d'accéder
aux données à partir de différents emplacements géographiques.

c- Avantages :

• Répartition des responsabilités : le client se concentre sur l'interface utilisateur, tandis que le
serveur gère les données et les performances.

• Scalabilité : plusieurs clients peuvent se connecter simultanément à un serveur unique.

• Sécurité : le serveur peut restreindre les accès en fonction des autorisations des utilisateurs.
 Architecture du SGBD
Exemples de SGBD :
❖MySQL :
•Un SGBD relationnel très populaire, souvent utilisé dans les applications web.
•Fonctionne avec une architecture client-serveur où les clients (comme PHP, Python, ou des outils
comme MySQL Workbench) envoient des requêtes au serveur MySQL.
❖PostgreSQL :
•Un SGBD relationnel robuste avec un support avancé pour les transactions et les types de
données complexes.
•Utilise le modèle client-serveur avec des protocoles réseau comme TCP/IP.
❖Microsoft SQL Server :
•Utilisé dans des environnements d'entreprise.
•Les outils comme SQL Server Management Studio (SSMS) jouent le rôle de clients pour interagir
avec le serveur.
 Architecture du SGBD
❖Oracle Database :
•Conçu pour les grandes applications d'entreprise, avec une architecture client-serveur avancée.
•Permet une communication sécurisée et des connexions distribuées.

❑Cas d'utilisation :
•Les applications web où le backend doit interagir avec une base de données distante (par exemple,
une boutique en ligne avec un serveur web et une base de données MySQL).
•Les entreprises où plusieurs employés utilisent des logiciels pour accéder à une base de données
centrale via des clients.
 Architecture du SGBD
2- Composants principaux
Un SGBD est constitué de plusieurs composants essentiels qui collaborent pour exécuter les tâches
demandées :
a-Processus serveur :
• C'est le cœur du système. Le processus serveur écoute les requêtes des clients, les interprète, les
exécute, et retourne les résultats.
• Il assure la gestion de la concurrence, garantissant que plusieurs utilisateurs peuvent accéder à la
base de données simultanément sans conflits.
Exemple :
•MySQL : Le processus serveur écoute sur un port (par défaut, 3306) et exécute les commandes
envoyées par les clients.
•PostgreSQL : Utilise un processus maître (Postmaster) pour coordonner plusieurs processus enfants
qui gèrent les connexions des clients.
Cas concret :
•Dans une application bancaire, un client effectue une transaction. Le processus serveur reçoit la
requête, exécute la mise à jour de la base et renvoie un accusé de réception.
 Architecture du SGBD
b-Gestionnaires mémoire :
• Ils gèrent la mémoire utilisée par le SGBD pour optimiser les performances.

• Cela inclut la mise en cache des données fréquemment utilisées en mémoire vive pour réduire les accès aux disques,
qui sont plus lents.

• Ces gestionnaires déterminent également comment allouer et libérer la mémoire lorsque des opérations sont
effectuées.
Exemple :
•Oracle Database : Utilise un cache mémoire (SGA - System Global Area) pour stocker les données les plus fréquemment
utilisées.
•PostgreSQL : Gère un cache mémoire appelé Shared Buffers pour réduire les accès disque.
Cas concret :
•Dans un site d'e-commerce, les produits les plus consultés sont gardés en mémoire pour accélérer les recherches.
 Architecture du SGBD
c-Gestionnaires de requêtes :
• Ils analysent et optimisent les requêtes SQL envoyées par les clients.

• Le SGBD décompose une requête en plusieurs étapes (analyse, planification, exécution) pour déterminer le moyen le plus efficace
d'extraire ou de manipuler les données.

• Exemple : Si une requête demande un tri ou un filtrage, le gestionnaire de requêtes choisira la meilleure méthode pour accomplir
cette tâche (par exemple, en utilisant des index).
Exemple :
•Microsoft SQL Server : L'optimiseur de requêtes analyse les instructions SQL et génère un plan d'exécution optimal.
•MySQL : Utilise un moteur de requêtes pour analyser les commandes et exécuter des jointures ou des filtres.
Cas concret :
•Une requête complexe avec des jointures multiples (par exemple, obtenir la liste des clients qui ont passé une commande
d'un montant supérieur à 100€) est optimisée pour minimiser le temps d'exécution.
 Architecture du SGBD
3- Gestion des fichiers et organisation des données sur disque
Les SGBD gèrent les données en les stockant sur disque de manière structurée et optimisée pour assurer
des performances élevées et une récupération fiable :

a-Gestion des fichiers :

• Les bases de données sont généralement stockées dans des fichiers spécifiques gérés par le SGBD.
• Le SGBD gère ces fichiers pour minimiser les accès disques et garantir la cohérence des données.
• Les fichiers peuvent être segmentés en pages ou blocs de données pour faciliter les
lectures/écritures.
• Les tables sont stockées dans des fichiers séparés avec des index pour accélérer l'accès.
b-Organisation des données sur disque :
• Les données sont organisées en tables, index, et autres structures pour optimiser les recherches et
les modifications.
 Architecture du SGBD
• Tables : Les données brutes sont stockées dans des tables, souvent sous forme de lignes et de
colonnes.
• Index : Les index permettent d'accélérer l'accès aux données. Par exemple, un index sur une
colonne fréquemment utilisée dans les requêtes améliore considérablement les performances.
• Segments : Certaines bases de données segmentent les données en unités physiques plus petites
pour gérer les très grandes bases de données.

c-Techniques supplémentaires :
• Journaux de transactions : Ils enregistrent toutes les modifications apportées à la base de
données, ce qui permet de restaurer la base en cas de panne.
• Compaction : Les SGBD utilisent parfois des mécanismes pour réorganiser les fichiers et libérer
l'espace inutilisé (défragmentation).
 Architecture du SGBD

Exemple:
 Architecture du SGBD
Résumé des interactions entre ces points :
• Le client envoie une requête (par exemple, "trouver les utilisateurs avec plus de 10 achats") au
serveur.

• Le processus serveur traite cette requête avec l'aide des gestionnaires de requêtes.

• Les gestionnaires mémoire assurent que les données nécessaires sont accessibles rapidement (par
exemple, en les chargeant en mémoire).

• Le SGBD accède aux fichiers sur disque pour récupérer les données organisées, utilise les index si
nécessaires, et retourne les résultats au client.
 Administration base de données

Chapitre II- RAPPEL SUR LES BASES DE DONNÉES

 Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
Les SGBD : systèmes de gestion de bases de données
1. Objectifs des SGBD
Un système d’informations (SI) est un système permettant le recueil, le
contrôle, la mémorisation et la distribution des informations nécessaires à
l’exercice de l’activité de l’organisation.
Une base de données (BD) est un ensemble structuré de données
enregistrées sur des supports accessibles par l’ordinateur pour satisfaire
simultanément plusieurs utilisateurs de manière sélective et en un temps
opportun.
 Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
- Élimination de la redondance des données
- Centralisation et organisation correcte des données
- Apports du Système de Gestion de Bases de Données (SGBD) :
o Factorisation des modules de contrôle des applications
o Interrogation, cohérence, partage, gestion des pannes. . .
o Administration facilitée des données
Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
 Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
2-Un SGBDR est un type de base de données courant qui gère les relations prédéfinies entre les
données et où les données sont organisées sous forme de tables, colonnes et lignes.
Principaux SGBD du marché :
 Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
MySQL est un système de gestion de base de données relationnelle (SGBDR) open source, basé
sur le langage SQL (Structured Query Language).
SQL est une norme, alors que MySQL est l'un des nombreux produits qui implémentent cette
norme.
On pourrait faire l'analogie avec le plan d'une maison. SQL est un plan, comme le plan d'une
maison. Monsieur X et Monsieur Y pourraient tous les deux construire une maison en se basant
sur le même plan, mais la maison de Monsieur X ne serait pas pour autant celle de Monsieur Y.
De la même façon, des produits comme Oracle, MsSQL, MySQL, MariaDB… sont des produits
de base de données différents, mais qui se basent sur la même norme, le même plan, qu'est la
norme SQL.
 Chapitre II- RAPPEL SUR LES BASES DE DONNÉES

3- Installation de MySql
Il existent plusieurs façon comment avoir MySql sur nos machines (Windows, Linux..). Dans
notre cas, on utilisera Xamp Server qui nous permettra, non seulement avoir MySql mais aussi
un serveur Apache qui rendra notre Windows comme étant un serveur Web qui nous aidera à
faire quelques expérimentations (PHP+MySQL) par la suite.
Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
 Chapitre II- RAPPEL SUR LES BASES DE DONNÉES
Modes d’accès à MySql
Sur le répertoire où vous avez le [Link], lancer le terminal (cmd) et exécuter la commande mysql -u root –p.
Puis taper le mot de passe (par défaut vide). La fenêtre suivante s’affichera :

Après avoir installé et lancer Xamp server, et démarrer le service MySql, on pourra utiliser un navigateur internet
pour pouvoir manipuler nos BDs, cet outil (navigateur internet) nécessite le lancement de Apache bien
évidement.
Le lien pour y accéder est le suivant : [Link]
Chapitre III- Structured Query Language (SQL)
 Définition
Le modèle logique est un modèle formel, qui spécifie les données telles qu'elles vont exister
dans l'application informatique.
Dans le modèle relationnel, les données sont représentées par des tables, des colonnes et des
lignes :
 Tables (suite de lignes) : relations
 Colonnes (Chaque valeur est typée) : attributs
 Lignes (suite de valeurs) : éléments ou n-uplets (tuples)
Definition
Le langage SQL (langage de requête structuré) peut être considéré comme:

A- un langage de définition de données (LDD)

B-langage de manipulation de données (LMD)

C- Langage de Contrôle des Données(LCD).

Le langage SQL permet de façon générale la définition, la manipulation et le contrôle de

sécurité de données.
PARTIE A : Langage de définition de données(LDD)
OBJECTIFS :
▪ Création des tables avec le langage SQL

▪ Création et manipulation des contraintes avec le langage SQL

▪ Modification de la structure tables avec le langage SQL.

I. Langage de Définition des Données

II. Les types de données

III. Les contraintes

PARTIE A
I. Langage de Définition des Données
Le Langage de Définition des Données est la partie de SQL qui permet de décrire les tables

et autres objets manipulés par les SGBD.

Le LDD permet de créer, modifier, supprimer des données. Il permet également de définir
le domaine des données (nombre, chaîne de caractères, date, booléen…) et d'ajouter des
contraintes de valeur sur les données. Il permet enfin d'autoriser ou d'interdire l'accès aux
données pour un utilisateur donné.
PARTIE A
I. Langage de Définition des Données
1- La commande CREATE

2- La commande ALTER TABLE

3- La commande DROP TABLE

 1-La commande CREATE
❑ Création de table
La commande CREATE TABLE permet de créer une table en SQL. La création

d’une table sert à définir les colonnes et le type de données qui seront

contenus dans chacun des colonnes (entier, chaîne de caractères, date, valeur

binaire …).

Syntaxe : CREATE TABLE nom_de_la_table

(colonne1 type_donnees,

colonne2 type_donnees,

colonne3 type_donnees,

………..)
 1-La commande CREATE
Il est également possible de définir des options telles que :

- NOT NULL : empêche d’enregistrer une valeur nulle pour une colonne.

- DEFAULT : attribuer une valeur par défaut si aucune donnée n’est indiquée pour cette colonne lors de
l’ajout d’une ligne dans la table.

- PRIMARY KEY : indiquer si cette colonne est considérée comme clé

primaire.

- FOREIGN KEY : indiquer si cette colonne est considérée comme clé

étrangère.
 1-La commande CREATE
Exemple :
- Création de la table voiture :
CREATE TABLE Voiture
(Code INT PRIMARY KEY NOT NULL,
Type VARCHAR(20),
Marque VARCHAR(20),
Puissance INT)

- Création de la table personne :

CREATE TABLE Personne
(Nss INT PRIMARY KEY NOT NULL,
Nom VARCHAR(30),
Prenom VARCHAR(30),
Voiture INT DEFAULT 0,
FOREIGN KEY(Voiture) REFERENCES Voiture(Code))
1-La commande CREATE

EXERCICE D’APPLICATION
-Créer la table étudiants et la table notes
Corriger

• CREATE TABLE Etudiants ( id_etudiant INT PRIMARY KEY, nom

VARCHAR(50), prenom VARCHAR(50), date_naissance DATE, sexe
CHAR(1), classe VARCHAR(20));

• CREATE TABLE Notes ( id_note INT PRIMARY KEY, id_etudiant INT,

matiere VARCHAR(50), note DECIMAL(5, 2), FOREIGN KEY
(id_etudiant) REFERENCES Etudiants(id_etudiant));
 EXERCICE D’APPLICATION
La relation IMMEUBLE décrit un ensemble d'immeubles. Chaque immeuble a un propriétaire. La relation
APPIM décrit pour chaque immeuble l'ensemble des appartements qui le compose (il y a au minimum un
appartement par immeuble). Chaque appartement peut héberger plusieurs personnes mais il y en a une
qui est responsable (par exemple la personne qui a signé le contrat de location) et qui est désignée par
l'attribut OCCUP.

Si l'appartement est inoccupé, il prend la valeur NULL. La relation PERSONNE décrit un ensemble de
personnes. ADR et NAPR représentent l'adresse où réside une personne. Une personne peut avoir
plusieurs enfants décrits par la relation ENFANT. Pour simplifier, on ne considère que les enfants allant à
l'école primaire.

Les écoles et les classes sont décrites dans les relations ÉCOLE et CLASSE, chaque école est composée
au minimum d'une classe et chaque classe est au moins fréquentée par un enfant.

1. Donnez le modèle relationnel des tables

2. Donnez le modèle relationnel en SQL(création de tables)

Associations principales : EXERCICE D’APPLICATION
1. Relation entre IMMEUBLE et PERSONNE (propriétaire)

o Un immeuble a un seul propriétaire (relation 1:N entre IMMEUBLE et PERSONNE).

2. Relation entre IMMEUBLE et APPIM

o Un immeuble possède plusieurs appartements (relation 1:N entre IMMEUBLE et APPIM).

3. Relation entre APPIM et PERSONNE (occupants)

o Un appartement peut être occupé par plusieurs personnes, avec un occupant responsable (relation N:1 entre APPIM et PERSONNE
pour OCCUP).

4. Relation entre PERSONNE et ENFANT

o Une personne peut avoir plusieurs enfants (relation 1:N entre PERSONNE et ENFANT).

5. Relation entre ENFANT et CLASSE

o Un enfant est inscrit dans une seule classe (relation N:1 entre ENFANT et CLASSE).

6. Relation entre CLASSE et ÉCOLE

o Une école possède plusieurs classes (relation 1:N entre ÉCOLE et CLASSE).

7. Relation entre CLASSE et PERSONNE (maître)

o Une classe est enseignée par un seul maître (relation 1:N entre PERSONNE et CLASSE pour les maîtres).
 EXERCICE D’APPLICATION

• Modèle Entité/Association sous forme schématique :

1. IMMEUBLE (1,N) APPIM

2. APPIM (N,1) PERSONNE (OCCUP)

3. PERSONNE (1,N) ENFANT

4. ENFANT (N,1) CLASSE

5. CLASSE (1,N) ÉCOLE

6. PERSONNE (1,N) CLASSE (maîtres)

 EXERCICE D’APPLICATION
• Entités principales :

1. IMMEUBLE

o Attributs :

▪ ID_IMMEUBLE : Identifiant unique de l'immeuble (PK)

▪ ADR : Adresse de l'immeuble

▪ ANNEE_CONSTRUCTION : Année de construction

▪ NB_ETAGES : Nombre d'étages

▪ ID_PROPRIETAIRE : Référence au propriétaire (FK vers PERSONNE.ID_PERSONNE)

2. APPIM

o Attributs :

▪ ID_APPARTEMENT : Identifiant unique de l'appartement (PK)

▪ TYPE : Type de l'appartement (ex. studio, T2, T3...)

▪ ETAGE : Étages où se trouve l'appartement

▪ OCCUP : Occupant responsable (FK vers PERSONNE.ID_PERSONNE, peut être NULL)

▪ ID_IMMEUBLE : Référence à l'immeuble (FK vers IMMEUBLE.ID_IMMEUBLE)

 EXERCICE D’APPLICATION
• Entités principales :

3. PERSONNE

o Attributs :

▪ ID_PERSONNE : Identifiant unique de la personne (PK)

▪ NOM : Nom de la personne

▪ AGE : Âge de la personne

▪ PROFESSION : Profession de la personne

▪ ADR : Adresse où réside la personne

• NAPR : Numéro de l'appartement où réside la personne

4. ENFANT

o Attributs :

▪ ID_ENFANT : Identifiant unique de l'enfant (PK)

▪ NOM : Nom de l'enfant

▪ ID_PARENT : Référence au parent (FK vers PERSONNE.ID_PERSONNE)

▪ ID_CLASSE : Référence à la classe fréquentée par l'enfant (FK vers CLASSE.ID_CLASSE)

 EXERCICE D’APPLICATION
• Entités principales :

5. ÉCOLE

o Attributs :

▪ ID_ECOLE : Identifiant unique de l'école (PK)

▪ NOM : Nom de l'école

▪ ADR : Adresse de l'école

6. CLASSE

o Attributs :

▪ ID_CLASSE : Identifiant unique de la classe (PK)

▪ NOM : Nom ou niveau de la classe (ex. CP, CE1...)

▪ ID_ECOLE : Référence à l'école (FK vers ÉCOLE.ID_ECOLE)

▪ ID_MAITRE : Référence au maître de la classe (FK vers PERSONNE.ID_PERSONNE)

 2- La commande ALTER TABLE
❑ Modification de schéma
i. ALTER TABLE nom_table
La commande ALTER TABLE permet de modifier une table existante. Il est ainsi possible
d’ajouter une colonne, d’en supprimer une ou de modifier une colonne existante, par
exemple pour changer le type :

ALTER TABLE nom_table instruction

a)Ajouter une colonne :
ALTER TABLE nom_table ADD nom_colonne type_donnees
EX : ALTER TABLE utilisateur ADD adresse_rue VARCHAR(255)
2- La commande ALTER TABLE
 2- La commande ALTER TABLE

Ajout d’une contrainte

ALTER TABLE nom_table
ADD Constraint Def_de_contrainte ;
Exemple : Ajouter à la table « Magasin » la contrainte suivante : la
surface doit être comprise entre 10 et 100 m2
ALTER TABLE Magasin
ADD CONSTRAINT ck1_magasin Check (surface between 10 and 100) ;
 2- La commande ALTER TABLE
Supprimer une colonne :
ALTER TABLE nom_table DROP nom_colonne
- Modifier une colonne :
ALTER TABLE nom_table MODIFY nom_colonne type_donnees
- Renommer une colonne :
ALTER TABLE nom_table CHANGE colonne_ancien_nom
colonne_nouveau_nom type_donnees
 2- La commande ALTER TABLE
Suppression d'une contrainte:
On peut effacer une clé primaire. La commande est :
ALTER TABLE nom_table DROP PRIMARY KEY
[CASCADE] ;
Remarque : L'option cascade est ajoutée pour pouvoir supprimer une clé primaire référencée.
ALTER TABLE magasin DROP PRIMARY KEY;
Suppression d'une contrainte autre que la clé primaire :
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte ; Où Le nom de la contrainte c'est celui de la
contrainte à supprimer
Exemple :
ALTER TABLE produit DROP CONSTRAINT Ck4_Produit ;
On peut supprimer une contrainte de clé étrangère . La commande (sous MySQL ) est :
ALTER TABLE nom_table DROP FOREIGN KEY
nom_contrainte ;
 3-La commande DROP TABLE
DROP TABLE nom_table
Cette commande permet de supprimer une table de la base de données. Les lignes

de la table et la définition elle-même sont détruites. L ’espace occupé par la table est
libéré.

La commande DROP TABLE permet de supprimer définitivement une table d’une base de
données. Cela supprime en même temps les éventuels index, contraintes et permissions
associées à cette table.

Syntaxe : DROP TABLE nom_table

Ex : DROP TABLE Voiture

 3-La commande DROP TABLE
DROP TABLE nom_table
Suppression d’une table
Elle se fait avec la commande : DROP TABLE
Exemple :
DROP TABLE client ;
Pour changer le nom d'une table existante la commande est :
RENAME TABLE ancien_nom TO nouveau_nom
 II. Les types de données
1- Types numériques
• Nombres entiers : TINYINT (sur 1 octet, de 0 à 255), SMALLINT (sur 2
octets, de -32768 à 32767), INTEGER (sur 4 octets, de -2147483648 à
2147483647)
• Nombres décimaux avec un nombre fixe de décimales : NUMERIC,
DECIMAL
• Numériques non exacts à virgule flottante : REAL (le nombre de chiffres
significatifs varie), DOUBLE PRECISION, FLOAT (double précision,
avec au moins 15 chiffres significatifs).

La définition du nombre de chiffres significatifs varie selon le SGBD

 II. Les types de données
2-Types chaînes de caractères
Les constantes chaînes de caractères sont entourées par des apostrophes. Si
la chaîne contient une apostrophe, celle-ci devra être doublée.
• Il existe deux types pour les colonnes qui contiennent des chaînes de
caractères :
- le type CHAR pour les colonnes qui contiennent des chaînes de longueur constante inférieure
à 255 caractères (pour Oracle) CHAR (longueur)
- le type VARCHAR pour les colonnes qui contiennent des chaînes de longueur variable. Tous
les SGBD ont une longueur maximale pour ces chaînes (2000 sous Oracle).
longueur est la longueur maximale en nombre de caractères qu ’il sera possible de stocker dans
le champ
 II. Les types de données
3-Types temporels

DATE : réserve 2 chiffres pour le mois et le jour et 4 pour l ’année

TIME : pour les heures, minutes et secondes
TIMESTAMP : indique un moment précis par une date avec heures, minutes et
secondes (6 chiffres après la virgule)
 II. Les types de données
3-Types binaires

Ce type permet d ’enregistrer des données telles que les images et les sons,
de très grande taille avec divers formats.
Les différents SGBD fournissent un type pour ces données. Les noms
varient : LONG RAW pour Oracle, IMAGE pour Sybase, BYTE pour
Informix.
PARTIE A
III. Les contraintes

1. Les contraintes de domaine

2. Les contraintes d ’intégrité d ’entité

3. Les contraintes d ’intégrité référentielle

 III. Les contraintes
1. Les contraintes de domaine
Il s ’agit de définir l ’ensemble des valeurs que peut prendre un attribut.
Ces contraintes sont décrites dans la définition d ’un attribut, directement après son type et sa longueur.
NOT NULL : on impose que l ’attribut possède une valeur
DEFAULT : on spécifie une valeur par défaut dont le type doit correspondre au type de l ’attribut
UNIQUE : interdit qu ’une colonne contienne deux valeurs identiques
Exemple : CREATE TABLE clients (numCli,
nomCli VARCHAR(25) NOT NULL,
CaCli INTEGER DEFAULT 0,
TypeCli VARCHAR(16) DEFAULT 'Particulier ’…);

CREATE TABLE fournisseurs (...

NomFour CHAR(25) NOT NULL UNIQUE, ..);

NomFour et NumFour sont deux clés candidates. NumFour a été choisi comme clé primaire
 III. Les contraintes

2-Les contraintes d ’intégrité d ’entité

Cours de Systèmes de Gestion de Données – Licence MIAGE – 2003/2004 18
II. Les contraintes d ’intégrité d ’entité
• Elles spécifient la clé primaire d ’une table via la clause PRIMARY KEY.
• Une clé primaire doit toujours avoir une valeur déterminée et unique pour
la table.
• Quand une clé primaire est constituée de plusieurs attributs (clé
segmentée), la clause PRIMARY KEY est placée après la définition des
attributs, séparée par une virgule.
 III. Les contraintes
Exemples : - NumCli NUMBER PRIMARY KEY

- Create TABLE Appartement

(…),

PRIMARY KEY (NumApp, NumImm);

• Remarques :

- tous les attributs d ’une clé segmentée doivent être spécifiés NOT NULL

- PRIMARY KEY peut aussi être séparée de la définition des attributs même s ’il n ’y

a qu ’un seul attribut

- Pour une table, il n ’existe qu ’une seul clé primaire

- Dans beaucoup de SGBD, un index est automatiquement construit sur la clé primaire.
 III. Les contraintes

3-Les contraintes d ’intégrité référentielle

REFERENCES nom_table_référencée (clé candidate)

• Le nom de la clé candidate est facultatif quand c ’est la clé primaire.

• Exemples : CREATE TABLE Commandes

(NumCde NUMBER PRIMARY KEY,

DateCde DATE NOT NULL,

NumCli NUMBER NOT NULL REFERENCES clients,

MagCde NUMBER NOT NULL REFERENCES magasins);

 III. Les contraintes
• Utilisation de FOREIGN KEY pour spécifier une clé étrangère constituée de
plusieurs attributs
• Spécification des actions à effectuer en cas de modification (clause ON UPDATE)
ou de suppression (clause ON DELETE) de valeurs de clés référencées :
CASCADE, SET NULL, SET DEFAULT
Exemple : NumFour NUMBER REFERENCES fournisseurs
ON UPDATE CASCADE
ON DELETE SET NULL;
 EXERCICE D’APPLICATION

• La clé primaire
EXERCICE D’APPLICATION
 EXERCICE D’APPLICATION

Exercice I : Soit le MODELE LOGIQUE DE DONNEE suivant :

ARTICLE (NoArt, Libelle, Stock)
FOURNISSEUR (NoFour, NomF, Adresse, VilleFour)
FOURNIR (NoFour#, NoArt#, PrixArticle, Delai)

– Les attributs soulignés sont des clés primaires, Les attributs avec # sont
des clés étrangères.
Créer les tables en sql
 EXERCICE D’APPLICATION

Create Table ARTICLES (NoArt int primary key, Libelle varchar(50),

Stock int);

Create Table FOURNISSEURS (NoFour int primary key, NomFour

varchar(50), AdrFour varchar(100), VilleFour varchar(50));

Create Table FOURNIR (NoFour int foreign key references

FOURNISSEURS (NoFour), NoArt int foreign key references
ARTICLES(NoArt), PrixArticle int , Delai int);
 EXERCICE D’APPLICATION

Exercice 2

Soit le modèle relationnel suivant relatif à la gestion des notes

annuelles d’une promotion d’étudiants :

ETUDIANT (NEtudiant, Nom, Prénom)

MATIERE (CodeMat, LibelléMat, CoeffMat)

EVALUER (NEtudiant, CodeMat, Date, Note)

Créer les tables avec les règles de gestion suivant :

• Les attributs en gras clé primaire, Les attributs souligné clé étrangère
 EXERCICE D’APPLICATION

Create table ETUDIANT (NEtudiant int primary key, Nom

varchar(30), Prénom varchar(30))

Create table MATIERE (CodeMat int primary key, LibelleMat

varchar(30),CoeffMat int)

Create table EVALUER (NEtudiant int foreign key references

ETUDIANT (NEtudiant), CodeMat int foreign key references MATIERE (CodeMat), Date
datetime, Note decimal(4,2))
 PARTIE B: Langage Manipulation des données (LMD)
Le LMD est l'ensemble des commandes concernant la manipulation des données dans une base de données. Le
LMD permet l'ajout, la suppression et la modification de lignes, la visualisation du contenu des tables et leur
verrouillage.
• Une base de données est un ensemble de tables gérées par un SGBD qui peuvent avoir été créées par différents
utilisateurs.
• Lorsqu’un utilisateur nomme une table, il s ’agit d ’une table lui appartenant.
• Pour accéder à une table appartenant à un autre utilisateur, il faut préfixer le nom de la table par le nom de l
’utilisateur et en avoir le droit.
PARTIE B: Langage Manipulation des données (LMD)
I. Les opérations de projection d ’unetable

II. Les opérations de sélection dans une table

III. Le tri des tuples

IV. Agrégation

V. UPDATE ;INSERT; DELETE

PARTIE B: Langage Manipulation des données (LMD)
I. Les opérations de projection d ’une table

Ordre des clauses à respecter.

PARTIE B: Langage Manipulation des données (LMD)
I. Les opérations de projection d ’une table
Selon sa syntaxe, La requête SELECT la plus simple possible est la suivante :
SELECT * | <liste des champs >
FROM <Liste des tables>
Exemple : Soit la table : Produit(idp, libelle, marque, prix)
• Sélection de Toutes les Colonnes : afficher tous les produits
select * from produit
• Sélection d’une ou Plusieurs Colonnes Spécifiques (Projection) :
SELECT libelle, marque FROM PRODUIT
 I-Les opérations de projection d ’une table
a-Sélection
SELECT nom(s) de colonne FROM nom_table;
Exemple:
a) SELECT * FROM STAGIAIRE;
b) SELECT Nom-St FROM STAGIAIRE;
c) SELECT Num-St, Nom-St FROM STAGIAIRE;
d) SELECT Nom-St AS nom FROM STAGIAIRE;
e) SELECT Nom-St nom FROM STAGIAIRE;
f) SELECT Nom-St AS 'nom de stagiaire' FROM STAGIAIRE;

* signifie que toutes les colonnes de la table sont sélectionnées.

Le nom complet d ’une colonne d ’une table est le nom de la table suivi d ’un point et
du nom de la colonne. Ex : [Link]-St. Le nom de la table peut être omis quand il n ’y a pas d ’ambiguïté.
L ’ordre des attributs dans la table résultat sera l ’ordre d ’apparition dans le SELECT ou l ’ordre dans la table si *
Par défaut, les attributs de la table résultat portent le même nom que les attributs de la table d ’origine sauf si ce nom est
redéfini :
SELECT nom_colonne [AS] nom_redéfini FROM nom_table;
 I-Les opérations de projection d ’une table
b-Empêcher les répétitions de lignes
SELECT DISTINCT nom(s) de colonne FROM nom_table;
SELECT Type-Stage FROM STAGE;
Contrairement à l’algèbre relationnelle, SQL n’élimine pas les doublons. Pour éliminer les doublons il faut spécifier
DISTINCT
• Exemple: Donner les différentes marques de produit.
SELECT DISTINCT marque FROM PRODUIT
 II-Les opérations de sélection dans une table
L’opération select permet de rechercher des données à partir de plusieurs tables; le résultat est présenté
sous forme d’une table réponse.
Syntaxe : SELECT nom_colonne
FROM nom_table
SELECT * | <liste des champs >
FROM <Liste des tables>
Exemple : Soit la table : Produit(idp, libelle, marque, prix)
• Sélection de Toutes les Colonnes : afficher tous les produits
select * from produit
 II-Les opérations de sélection dans une table
a- Les éléments de la clause WHERE
▪ La clause WHERE permet de spécifier quelles sont les lignes à sélectionner dans une table ou dans le produit
cartésien de plusieurs tables. Elle est suivie d ’un prédicat, c ’est-à dire une expression logique prenant la valeur
vrai ou faux et qui sera évalué pour chaque ligne. Les lignes pour lesquelles le prédicat est évalué à vrai sont
sélectionnées.

▪ Les expressions logiques peuvent être composées d ’une suite de conditions combinées entre elles par les
opérateurs AND, OR, NOT. L ’opérateur AND est prioritaire par rapport à l ’opérateur OR. Des parenthèses
peuvent être utilisées pour imposer une priorité dans l ’évaluation du prédicat, ou simplement pour rendre plus
claire l ’expression logique. L ’opérateur NOT placé devant un prédicat en inverse le sens.

▪ Les différentes formes d ’expressions logiques : comparaison à une valeur, comparaison à une fourchette de
valeurs, comparaison à une liste de valeurs, comparaison à un filtre, test sur l ’indétermination d ’une valeur, test
‘ tous ’ ou ‘ au moins un ’, test existentiel, test d ’unicité
 II-Les opérations de sélection dans une table

En SQL, les sélections s’expriment à l’aide de la clause WHERE suivie d’une condition logique
exprimée à l’aide :

- Des opérateurs arithmétiques : =, >, <, !=, <=, >=, +, -, /, * ….. Des opérateurs logiques : AND,
OR, NOT

- Des comparateurs de chaines : IN, BETWEEN, LIKE, IS NULL ….

Syntaxe : SELECT nom_colonne

FROM nom_table

WHERE condition
 II-Les opérations de sélection dans une table
Exemple
On dispose d'une base de données d'un festival de musique, où il y a plusieurs représentations.
Un ou plusieurs musiciens peuvent participer à une représentation, mais un musicien ne peut participer qu'à une seule
représentation.
La structure de la base de données est la suivante :
Representation (idRep , titreRep , lieu)
Musicien (idMus , nom , # idRep)
Programmer (Date , # idRep, tarif)
Écrire la requête SQL permettant d'afficher :

1) La liste des titres des représentations.

SELECT titreRep
FROM Representation ;
2) La liste des titres des représentations ayant lieu sur le lieu nommé « Dionysos ».

SELECT titreRep
FROM Representation
WHERE lieu = "Dionysos" ;
 II-Les opérations de sélection dans une table
b- La comparaison à une valeur

a) SELECT Num-St, Nom-St FROM STAGIAIRE WHERE Adr-St = 'Paris';

b) SELECT Num-Stage FROM STAGE WHERE Nb-jours > 4;

c) SELECT Num-Stage FROM STAGE WHERE (Prix-jour * nb-jours) > 5000;

d) SELECT Num-Stage, Libellé-Stage, Type-Stage, Num-Cat FROM STAGE WHERE Type-Stage = ’TP ’ AND Num-Cat = 1;

e) SELECT Num-Stage, Libellé-Stage, Type-Stage, Num-Cat FROM STAGE WHERE NOT(Type-Stage = ’TP ’ OR Num-Cat =
1);
 II-Les opérations de sélection dans une table
c- Une liste de valeurs

SELECT … FROM … WHERE Cond1 [NOT] BETWEEN val1 AND val2;

SELECT Num-Stage, Libellé-Stage, Num-Cat FROM STAGE WHERE Num-Cat
BETWEEN 1 AND 3;
SELECT … FROM … WHERE Nom_colonne [NOT] IN (liste_valeurs);
SELECT Num-Stage, Libellé-Stage, Num-Cat FROM STAGE WHERE Num-Cat IN (1,3);
• NOT IN sert à sélectionner les lignes dont l ’attribut dans la clause WHERE contient une
valeur autre que celle de la liste
• MATCH est équivalent à IN
• MATCH UNIQUE rend la valeur vraie que si la valeur ne se trouve qu ’une seule fois dans
la liste.
 II-Les opérations de sélection dans une table

Exemple: Lister les ventes dont la quantité est entre 4 et 12.

SELECT *
FROM VENTE
WHERE qte BETWEEN 4 AND 12

Exemple: Donner la liste des Clients des villes Rabat, Casa et Safi.
SELECT *
FROM CLIENT
WHERE ville IN (‘Rabat’,
‘Casa’, ‘Safi’)
 II-Les opérations de sélection dans une table
EXERCICE
ARTICLE (NoArt, Libelle, Stock)
FOURNISSEUR (NoFour, NomF, Adresse, VilleFour)
FOURNIR (NoFour#, NoArt#, PrixArticle, Delai)

1-donner les numéros et les libellés des articles de stock inférieur à 10.
2- afficher la liste des articles dont le prix est compris entre 100 et 300
3- lister les fournisseurs dont le nom commence par “Fo” .
4- donner les noms et les adresses des fournisseurs qui proposent des articles pour lesquels le
délai d’approvisionnement est supérieur à 20 jours.
5- afficher la liste pour chaque article (numéro et libellé) qui a du prix d’achat maximum,
minimum ou moyen.
 II-Les opérations de sélection dans une table
d- La comparaison à un filtre
SELECT … FROM … WHERE nom_colonne LIKE ’Modèle de chaîne';
LIKE chaîne générique :
La chaîne générique est une chaîne de caractères qui contient l'un des caractères suivants :
✓ % : remplace une autre chaîne de caractères qui peut être même une chaîne vide.
Exemple: Donner la liste des clients dont le nom commence par a.
SELECT *
FROM CLIENT
WHERE nom LIKE ‘a%’

•LIKE ‘%a’ : le caractère “%” est un caractère joker qui remplace tous les autres caractères. Ainsi, ce modèle permet de
rechercher toutes les chaines de caractère qui se termine par un “a”.
•LIKE ‘a%’ : ce modèle permet de rechercher toutes les lignes de “colonne” qui commence par un “a”.

.
 III. Le tri des tuples
• L ’ordre dans lequel les lignes d ’une table résultat apparaissent est indéterminé.
• Pour trier les lignes dans un ordre déterminé, il faut utiliser la clause ORDER BY.
SELECT … FROM … ORDER BY num_colonne1 [DESC], num_colonne2 [DESC], ... ;
On peut trier selon la valeur d ’un ou de plusieurs attributs.
• Les attributs selon lesquels le tri est demandé doivent obligatoirement faire partie de la clause SELECT.
• L ’option facultative DESC donne un tri par ordre décroissant. Par défaut, l ’ordre est croissant.
• Le tri se fait d ’abord selon le premier attribut, puis les lignes ayant la même valeur pour ce premier attribut
sont triées selon le 2ème attribut, etc.
• Les valeurs nulles sont toujours en tête.
a) SELECT Num-Stage, Libellé-Stage FROM STAGE ORDER BY Num-Stage;
b) SELECT Num-Stage, Libellé-Stage FROM STAGE ORDER BY 1;
c) SELECT Num-Stage, Libellé-Stage, Nom-Cat FROM STAGE
WHERE NOT(Type-Stage = ’Cours’) ORDER BY Num-Cat, Num-Stage DESC;
 III. Le tri des tuples
Pour trier les résultats d’une requête, on utilise la clause ORDER BY
Exemple : afficher les clients triés par ville

SELECT *
FROM Client
ORDER BY ville, adr ASC

SELECT *
FROM Client
ORDER BY ville, adr DESC
 Recap

Syntaxe d’utilisation des opérateurs AND et OR

Les opérateurs sont à ajoutés dans la condition WHERE.
Ils peuvent être combinés à l’infini pour filtrer les données comme souhaités.
L’opérateur AND permet de s’assurer que la condition1 ET la condition2 sont vrai :

SELECT nom_colonnes
FROM nom_table
WHERE condition1 AND condition2
Recap
 Recap
L’opérateur OR vérifie quant à lui que la condition1 OU la condition2 est vrai :
SELECT nom_colonnes FROM nom_table WHERE condition1 OR condition2
Ces opérateurs peuvent être combinés à l’infini et mélangés.
L’exemple ci-dessous filtre les résultats de la table “nom_table” si condition1 ET
condition2 OU condition3 est vrai :
SELECT nom_colonnes FROM nom_table WHERE condition1 AND (condition2 OR
condition3)
Attention : il faut penser à utiliser des parenthèses lorsque c’est nécessaire.
Cela permet d’éviter les erreurs car et ça améliore la lecture d’une requête par un
humain.
Recap
Recap
On peut combiner les deux: OR ET AND
 Recap

La syntaxe utilisée avec l’opérateur est plus simple que d’utiliser

une succession d’opérateur OR. Requête avec plusieurs OR :
SELECT prenom
FROM utilisateur
WHERE prenom = 'Maurice' OR prenom = 'Marie' OR prenom = 'Thimoté'
 Recap
Simplicité de l’opérateur IN
Syntaxe
Pour chercher toutes les lignes où la colonne “nom_colonne” est égale à ‘valeur 1’
OU ‘valeur 2’ ou ‘valeur 3’, il est possible d’utiliser la syntaxe suivante:
SELECT nom_colonne
FROM table
WHERE nom_colonne IN ( valeur1, valeur2, valeur3, ... )

A savoir : entre les parenthèses il n’y a pas de limite du nombre d’arguments.

Il est possible d’ajouter encore d’autres valeurs.
Recap
 Recap
Syntaxe
L’utilisation de la commande BETWEEN s’effectue de la manière suivante :
SELECT * FROM table
WHERE nom_colonne BETWEEN 'valeur1' AND 'valeur2’

La requête suivante retournera toutes les lignes dont la valeur de la colonne

“nom_colonne” sera comprise entre valeur1 et valeur2.
Recap
Exercice1 EXERCICE
Soit la base de données relationnelle des vols quotidiens d’une compagnie aérienne qui contient les tables
Avion, Pilote et Vol.
Table Avion (NA : numéro avion de type entier (clé primaire),
Nom : nom avion de type texte (12),
Capacite : capacité avion de type entier,
Localite : ville de localité de l’avion de type texte (10)
)
Table Pilote (NP : numéro pilote de type entier,
Nom : nom du pilote de type texte (25),
Adresse : adresse du pilote de type texte (40)
)
Table Vol (NV : numéro de vol de type texte (6),
NP : numéro de pilote de type entier,
NA : numéro avion de type entier,
VD : ville de départ de type texte (10),
VA : ville d’arrivée de type texte (10),
HD : heure de départ de type entier,
HA : heure d’arrivée de type entier
)
 Recap
1) Insérer les avions suivants dans la table Avion :
(100, AIRBUS, 300, RABAT), (101,B737,250,CASA), (101, B737,220,RABAT)
2)Afficher tous les avions
3) Afficher tous les avions par ordre croissant sur le nom
4) Afficher les noms et les capacités des avions
5) Afficher les localités des avions sans redondance
6) Afficher les avions dans la localité de Rabat ou Casa
7) Modifier la capacité de l’avion numéro 101, la nouvelle capacité et 220
8) Supprimer les avions dans la capacité et inférieure à 200
9) Afficher la capacité maximale, minimale, moyenne des avions
10) Afficher les données des avions dont la capacité et la plus basse
11) Afficher les données des avions dont la capacité et supérieure à la capacité moyenne
12) Afficher le nom et l’adresse des pilotes assurant les vols IT100 et IT104
13) Afficher les numéros des pilotes qui sont en service
14) Afficher les numéros des pilotes qui ne sont pas en service
15) Afficher les noms des pilotes qui conduisent un AIRBUS
 IV. Agrégation
Dans le langage SQL, la fonction d’agrégation MAX() permet de retourner la valeur maximale d’une
colonne dans un set d’enregistrement. La fonction peut s’appliquée à des données numériques
ou alphanumériques. Il est par exemple possible de rechercher le produit le plus cher dans une table
d’une boutique en ligne.
Syntaxe de MAX
La syntaxe de la requête SQL pour retourner la valeur maximum
de la colonne “nom_colonne” est la suivante:
SELECT MAX(nom_colonne) FROM table
Lorsque cette fonctionnalité est utilisée en association avec la commande
GROUP BY, la requête peut ressembler à l’exemple ci-dessous:
SELECT colonne1, MAX(colonne2) FROM table GROUP BY colonne1
IV. Agrégation
 IV. Agrégation
SQL MIN()
La fonction d’agrégation MIN() de SQL permet de retourner la plus petite valeur
d’une colonne sélectionnée. Cette fonction s’applique aussi bien à des
données numériques qu’à des données alphanumériques.
Syntaxe
Pour obtenir la plus petite valeur de la colonne “nom_colonne” il est possible d’utiliser la requête SQL
suivante:
SELECT MIN(nom_colonne) FROM table
Étant données qu’il s’agit d’une fonction d’agrégation, il est possible de l’utiliser en complément de la
commande GROUP BY. Cela permet de grouper des colonnes et de connaître la plus petite valeur pour
chaque groupe. La syntaxe est alors la suivante:
SELECT colonne1, MIN(colonne2) FROM table GROUP BY colonne1
IV. Agrégation
IV. Agrégation
IV. Agrégation
 IV. Agrégation
SQL COUNT()
En SQL, la fonction d’agrégation COUNT() permet de compter le nombre d’enregistrement dans une table.
Connaître le nombre de lignes dans une table est très pratique dans de nombreux cas, par exemple pour
savoir combien d’utilisateurs sont présents dans une table ou pour connaître le nombre de commentaires sur
un article.
Syntaxe
Pour connaître le nombre de lignes totales dans une table, il suffit d’effectuer la requête SQL suivante :
SELECT COUNT(*) FROM table
Il est aussi possible de connaitre le nombre d’enregistrement sur une colonne en particulier. Les
enregistrements qui possèdent la valeur nul ne seront pas comptabilisé. La syntaxe pour compter les
enregistrement sur la colonne “nom_colonne” est la suivante :
SELECT COUNT(DISTINCT nom_colonne) FROM table
A savoir : en général, en terme de performance il est plutôt conseillé de filtrer les lignes avec GROUP BY si
c’est possible, puis d’effectuer un COUNT(*).
IV. Agrégation
IV. Agrégation
SQL AVG() IV. Agrégation
La fonction d’agrégation AVG() dans le langage SQL permet de calculer une
valeur moyenne sur un ensemble d’enregistrement de type numérique et non nul.
Syntaxe
La syntaxe pour utiliser cette fonction de statistique est simple :
SELECT AVG(nom_colonne) FROM nom_table
Cette requête permet de calculer la note moyenne de la colonne “nom_colonne”
sur tous les enregistrements de la table “nom_table”. Il est possible de filtrer les
enregistrements concernés à l’aide de la commande WHERE. Il est aussi possible
d’utiliser la commande GROUP BY pour regrouper les données appartenant à la même
entité.
A savoir : la syntaxe est conforme avec la norme SQL et fonctionne correctement
avec tous les Systèmes de Gestion de Base de Données (SGBD),
IV. Agrégation
IV. Agrégation
 IV. Agrégation

SQL HAVING
La condition HAVING en SQL est presque similaire à WHERE à la seule différence que HAVING permet
de filtrer en utilisant des fonctions telles que SUM(), COUNT(), AVG(), MIN() ou MAX().

Syntaxe
L’utilisation de HAVING s’utilise de la manière suivante :
SELECT colonne1, SUM(colonne2)
FROM nom_table
GROUP BY colonne1 HAVING fonction(colonne2) operateur valeur

Cela permet donc de SÉLECTIONNER les colonnes DE la table “nom_table”

en GROUPANT les lignes qui ont des valeurs identiques sur la colonne “colonne1” et que la condition
de HAVING soit respectée.
Important : HAVING est très souvent utilisé en même temps que

GROUP BY bien que ce ne soit pas obligatoire.

IV. Agrégation
IV. Agrégation
 V. CREATE BASE; UPDATE; INSERT;DELETE

SQL UPDATE
La commande UPDATE permet d’effectuer des modifications sur des lignes existantes.
Très souvent cette commande est utilisée avec WHERE pour spécifier sur quelles lignes doivent porter la ou les
modifications.
Syntaxe
La syntaxe basique d’une requête utilisant UPDATE est la suivante :
UPDATE table SET nom_colonne_1 = 'nouvelle valeur' WHERE condition
Cette syntaxe permet d’attribuer une nouvelle valeur à la colonne nom_colonne_1 pour les lignes qui respectent la
condition stipulé avec WHERE. Il est aussi possible d’attribuer la même valeur à la colonne nom_colonne_1 pour to
les lignes d’une table si la condition WHERE n’était pas utilisée. A noter, pour spécifier en une seule fois plusieurs
modification, il faut séparer les attributions de valeur par des virgules. Ainsi la syntaxe deviendrait la suivante :
UPDATE table SET colonne_1 = 'valeur 1', colonne_2 = 'valeur 2', colonne_3 = 'valeur 3' WHERE condition
 V. CREATE BASE; UPDATE; INSERT;DELETE

SQL CREATE DATABASE

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 ma_base
V. CREATE BASE; UPDATE; INSERT;DELETE
V. CREATE BASE; UPDATE; INSERT;DELETE
V. CREATE BASE; UPDATE; INSERT;DELETE
 V. CREATE BASE; UPDATE; INSERT;DELETE
SQL INSERT INTO
L’insertion de données dans une table s’effectue à l’aide de la commande INSERT INTO. Cette commande
permet au choix d’inclure une seule ligne à la base existante ou plusieurs lignes d’un coup.
Insérer une ligne en spécifiant toutes les colonnes
La syntaxe pour remplir une ligne avec cette méthode est la suivante :
INSERT INTO table VALUES ('valeur 1', 'valeur 2', ...)
Cette syntaxe possède les avantages et inconvénients suivants :
•Obliger de remplir toutes les données, tout en respectant l’ordre des colonnes
•Il n’y a pas le nom de colonne, donc les fautes de frappe sont limitées. Par ailleurs, les colonnes
peuvent être renommées sans avoir à changer la requête
•L’ordre des colonnes doit resté identique sinon certaines valeurs prennent le risque d’être
complétée dans la mauvaise colonne
 V. CREATE BASE; UPDATE; INSERT;DELETE
Insérer une ligne en spécifiant seulement les colonnes souhaitées
Cette deuxième solution est très similaire, excepté qu’il faut indiquer le nom des colonnes avant
“VALUES”. La syntaxe est la suivante :
INSERT INTO table (nom_colonne_1, nom_colonne_2, ... VALUES ('valeur 1', 'valeur 2',
...)
 V. CREATE BASE; UPDATE; INSERT;DELETE
Insertion de plusieurs lignes à la fois
Il est possible d’ajouter plusieurs lignes à un tableau avec une seule requête. Pour ce faire, il
convient d’utiliser la syntaxe suivante :
INSERT INTO client (prenom, nom, ville, age)
VALUES ('Rébecca', 'Armand', 'Saint-Didier-des-Bois', 24), ('Aimée', 'Hebert', 'Marigny-le-Châtel', 36),
('Marielle', 'Ribeiro', 'Maillères', 27),('Hilaire', 'Savary', 'Conie-Molitard', 58);
 TP
Structure et contenu de la Base de Données :
La Base de données « Bibliotheque » utilisée dans ce TP est constituée des tables suivantes :
Table des livres (Numéro inventaire du livre, matière, titre et auteur du livre, nombre
d’exemplaires).
Créer la Base de données « Bibliotheque » et les tables associées
Livre :
 TP
Table des abonnés (Numéro d'abonné, son nom, son prénom, son statut et le département
d'affectation)
Abonne :
TP
 TP
Résoudre les requêtes suivantes en utilisant le langage SQL
1. R1 : Afficher la liste des matières.
2. R2 : Afficher les livres de l'auteur Ali.
3. R3 : Afficher les livres dont la quantité est supérieur à 3 ou inférieur à 2.
4. R4 : Afficher la liste des livres dont le titre contient "base de données" ou "Langage C".
5. R5 : afficher la liste des prêts entre 01/09/2015 et 10/12/2015
6. R6 : Afficher les livres dont le titre commence par "L"
7. R7: Afficher les livres dont le titre contient le caractère "a" deux fois.
8. R8 : Afficher la liste des abonnées dont le nom se termine par "i"
9. R9 : Afficher les noms des abonnées qui se terminent par "li"
10. R10 : Afficher la liste des livres affichée par ordre décroissant des quantités.
11. R11 : Afficher la liste des prêts ordonnés par ordre chronologique de date de retour.
12. R12 : Afficher la quantité des livres des matières Algo et Prog.
13. R13 : Afficher la quantité des livres groupés par matière.
 PARTIE C: CONTRÔLE DE DONNÉES
Il arrive que plusieurs personnes travaillent simultanément sur une base de
données. Cependant, en fonction de leurs besoins, elles n'auront pas toutes la même
utilisation de la base au même moment. Certaines peuvent par exemple avoir besoin de
modifier ou supprimer des données dans la table, pendant que d'autres ont seulement un
besoin de consultation de données.
Ainsi, il est possible de définir des permissions pour chaque personne en leur octroyant un
mot de passe. Cette tâche incombe à l'administrateur de la base de données (en anglais
DBA, DataBase Administrator). Il doit dans un premier temps définir les besoins de chacun,
puis les appliquer à la base de données sous forme de permissions.
 PARTIE C: CONTRÔLE DE DONNÉES
Le langage SQL permet d'effectuer ces opérations grâce à deux clauses :
GRANT permet d'accorder des droits à un utilisateur (parfois plusieurs sur certains SGBD) .
REVOKE permet de retirer des droits à un utilisateur (ou plusieurs sur certains SGBD).
Les permissions (appelées aussi droits ou privilèges) peuvent être définies pour chaque clause. D'autre part, il est
aussi possible de définir des rôles, c'est-à-dire de permettre à d'autres utilisateurs d'accorder des permissions.
Les privilèges sont les clauses qui peuvent être autorisées ou retirées à un utilisateur. Les principales sont :
- DELETE : pour supprimer les données d'une table ;
- INSERT : pour ajouter des données à une table ;
- SELECT : pour accéder aux données d'une table ;
- UPDATE : pour mettre à jour les données d'une table.
 PARTIE C: CONTRÔLE DE DONNÉES
L’attribution des permissions
La clause GRANT permet d'attribuer des permissions à un ou plusieurs utilisateurs sur un ou plusieurs éléments
de la base de données. La syntaxe de cette clause est la suivante :
GRANT Liste_de_permissions ON Liste_d_objets TO Liste_d_utilisateurs
[WITH GRANT OPTION];
L'option WITH GRANT OPTION permet de définir si l'utilisateur peut lui-même accorder à un autre utilisateur
les permissions qu'on lui accorde sur les éléments.
Pour faciliter l'attribution des permissions, il existe des mots clés qui permettent d'éviter à avoir à saisir
l'ensemble des utilisateurs dans le cas d'une autorisation collective ou bien de citer l'ensemble des permissions :
• le mot clé PUBLIC en lieu et place de la liste d'utilisateurs permet d'accorder les privilèges sur le ou les objets à
l'ensemble des utilisateurs ;
• le mot clé ALL en lieu et place de la liste de permissions permet d'accorder tous les privilèges aux utilisateurs
présents dans la liste.
 PARTIE C: CONTRÔLE DE DONNÉES

En précisant entre parenthèses un nom de colonne pour un privilège, il est possible de limiter le privilège
à la colonne (ou la liste de colonnes) entre parenthèses, par exemple :
GRANT UPDATE(Nom,Prenom)
ON Etudiants
TO Jerome,Francois,Georges
WITH GRANT OPTION;
L'option WITH GRANT OPTION autorise donc plusieurs utilisateurs à accorder des permissions à un même
utilisateur, il y a donc des règles à respecter lors du retrait des permissions à un utilisateur.

GRANT SELECT, UPDATE ON Personne TO Pierre;

GRANT ALL PRIVILEGES ON Adresse TO PUBLIC
 PARTIE C: CONTRÔLE DE DONNÉES

Quelles sont les instructions SQL (sous-ensemble LMD) autorisées pour l'utilisateur "Lambda", étant
données les instructions SQL (sous-ensemble LCD) suivantes, exécutées antérieurement ?

REVOKE ALL PRIVILEGES ON * FROM Lambda;

GRANT UPDATE, SELECT ON a TO Lambda;

GRANT SELECT ON b TO Lambda;

 PARTIE C: CONTRÔLE DE DONNÉES
Le retrait des permissions
La clause REVOKE permet de retirer des permissions à un ou plusieurs utilisateurs sur un
ou plusieurs éléments de la base de données. La syntaxe de cette clause est la suivante :
REVOKE
[GRANT OPTION FOR] Liste_de_permissions
ON Liste_d_objets
FROM Liste_d_utilisateurs;
L'option GRANT OPTION FOR supprime le droit d'un utilisateur à accorder des permissions
à un autre utilisateur.
 PARTIE C: CONTRÔLE DE DONNÉES
Pour faciliter le retrait des permissions, il existe des mots clés qui permettent d'éviter à avoir à saisir l'ensemble
des utilisateurs dans le cas d'une autorisation collective ou bien de citer l'ensemble des permissions :
• le mot clé PUBLIC en lieu et place de la liste d'utilisateurs permet de retirer les privilèges sur le ou les objets à
l'ensemble des utilisateurs ;
• le mot clé ALL en lieu et place de la liste de permissions permet de retirer tous les privilèges aux utilisateurs
présents dans la liste.
En précisant entre parenthèses un nom de colonne pour un privilège, il est possible de limiter la restriction de
privilège à la colonne (ou la liste de colonnes) entre parenthèses, par exemple :
REVOKE
[GRANT OPTION FOR] UPDATE(Nom,Prenom)
ON Etudiants
FROM PUBLIC