M.
GAYE Abdoulaye SQL
Soit le schéma relationnel suivant :
Serveur (idServ, nomServ, prenomServ, adrIpServ)
Servive (idS, nomS, etatS, #idServ)
Sous MySQL console,
1* Créer la base de données nommée : gestion_serveur
CREATE DATABASE gestion_serveur;
2* Afficher les BD existantes
SHOW DATABASES;
3* Utiliser la BD créée précédemment
USE gestion_serveur;
4* Afficher les tables de la BD
SHOW TABLES;
5* Créer les tables serveur et servive
NB: Il faut toujours créer en premier les tables qui n'ont pas de clé étrangère.
Le langage SQL n'est pas sensible à la casse.
---> CREATE TABLE serveur (
idServ integer NOT NULL AUTO_INCREMENT primary key,
nomServ varchar(50) UNIQUE,
prenomServ varchar(50),
adrIpServ varchar(25)
);
a. Afficher la structure de la table serveur
DESCRIBE serveur;
ou bien
DESC serveur;
b. Puisque l'@ IP des serveurs est unique, ajouter la contrainte d'unicité à la
colonne adrIpServ de la table serveur
ALTER TABLE serveur ADD UNIQUE (adrIpServ);
M. GAYE Abdoulaye SQL
c. Supprimer la colonne prenomServ de la table serveur
ALTER TABLE serveur DROP COLUMN prenomServ;
ou bien (le résultat sera le même)
ALTER TABLE serveur DROP prenomServ;
d. Renommer le nom de la colonne nomServ en libelleServ
NB: Pour modifier une colonne, il y a différentes syntaxes selon le SGBD.
ALTER TABLE serveur CHANGE nomServ libelleServ varchar(75);
---> CREATE TABLE service (
idS int NOT NULL AUTO_INCREMENT,
nomS varchar(25) UNIQUE,
etatS int,
idServ int,
PRIMARY KEY (idS),
FOREIGN KEY (idServ) REFERENCES serveur (idServ)
);
ou bien
---> CREATE TABLE service (
idS int NOT NULL AUTO_INCREMENT PRIMARY KEY,
nomS varchar(25) UNIQUE,
etatS int,
idServ int,
CONSTRAINT fk_serv FOREIGN KEY (idServ) REFERENCES
serveur (idServ)
);
ou bien (créer la table service en deux étapes)
---> CREATE TABLE service (
idS integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
nomS varchar(25) UNIQUE,
M. GAYE Abdoulaye SQL
etatS int,
idServ int
);
---> ALTER TABLE service ADD FOREIGN KEY (idServ)
REFERENCES serveur (idServ);
NB : Modifier la table servive en service
alter table servive rename to service ;
e. Afficher la structure de la table service
DESCRIBE service;
ou bien
DESC service;
f. Ajouter la colonne portS à la table service après la colonne etatS
NB: Par défaut, l'ajout d'une colonne dans une table se fait en fin de table.
ALTER TABLE service ADD COLUMN portS int after etatS;
ou bien
ALTER TABLE service ADD portS int after etatS;
6* Faire des enregistrements (ou insertions) dans les tables serveur et service
a. Dans la table serveur:
INSERT INTO serveur (idServ, libelleServ, adrIpServ) VALUES (null,
'ISI 1', '192.168.0.100'); /*Insertion explicite*/
INSERT INTO serveur (idServ, libelleServ, adrIpServ) VALUES (null,
'ISI 2', '192.168.0.200');
INSERT INTO serveur VALUES (null, 'ISI 3', '192.168.0.300');
/*Insertion implicite*/
INSERT INTO serveur VALUES (null, 'ISI 4', '192.168.0.400');
INSERT INTO serveur VALUES (null, 'ISI 5', '192.168.0.500');
a1. Afficher tous les serveurs
SELECT * FROM serveur;
a2. Afficher les serveurs par ordre alphabétique du libellé
M. GAYE Abdoulaye SQL
SELECT * FROM serveur ORDER BY libelleServ ASC;
b. Dans la table service
INSERT INTO service (idS, nomS, etatS, portS, idServ) VALUES
(null, 'HTTPS', 1, 443, 2);
INSERT INTO service (idS, nomS, etatS, portS, idServ) VALUES
(null, 'MySQL', 1, 3306, 2);
INSERT INTO service (idS, nomS, etatS, portS, idServ) VALUES
(null, 'TELNET', 0, 20, 1);
INSERT INTO service (idS, nomS, etatS, portS, idServ) VALUES
(null, 'HTTP', 0, 80, 2);
INSERT INTO service VALUES (null, 'DNS', 0, 53, 3);
INSERT INTO service VALUES (null, 'SMTP', 0, 25, 2);
INSERT INTO service VALUES (null, 'IMAP', 0, 143, 4);
INSERT INTO service VALUES (null, 'POP3', 0, 110, 4);
INSERT INTO service VALUES (null, 'FINGER', 0, 79, 5);
INSERT INTO service VALUES (null, 'SOCKS', 0, 1080, 3);
INSERT INTO service VALUES (null, 'WINS', 0, 1512, 3);
INSERT INTO service VALUES (null, 'PPTP', 0, 1723, 2);
INSERT INTO service VALUES (null, 'IPX', 0, 213, 2);
INSERT INTO service VALUES (null, 'UPnP', 0, 1900, 1);
INSERT INTO service VALUES (null, 'TFTP', 0, 69, 5);
b1. Afficher tous les services
SELECT * FROM service;
b2. Afficher les services (nom et port) par ordre alphabétique du nom
SELECT nomS as 'Nom du service', ports 'Port du service' FROM
service ORDER BY nomS;
b3. Afficher les services (nom, port et état) du serveur ISI 2
SELECT nomS, portS, etatS
FROM service s, serveur serv
M. GAYE Abdoulaye SQL
WHERE s.idServ=serv.idServ
AND serv.libelleServ='ISI 2';
ou bien
SELECT nomS, portS, etatS
FROM service
WHERE idServ=2;
b4. Afficher le nombre de services du serveur ISI 3
SELECT COUNT(*) as 'Nombre de services de ISI 3'
FROM service
WHERE idServ=3;
ou bien
SELECT COUNT(idS) 'Nombre de services de ISI 3'
FROM service s, serveur serv
WHERE s.idServ=serv.idServ
AND serv.libelleServ='ISI 3';
b5. Pour chaque serveur, afficher les services (nom et port)
SELECT idServ, nomS, portS FROM service GROUP BY idServ;
b6. Afficher le plus grand port
SELECT MAX(portS) as 'Le plus grand port' FROM service ;
ou bien
SELECT portS as 'Le plus grand port' FROM service ORDER BY
portS DESC LIMIT 1 ;
b7. Afficher le service (nom) qui a le plus grand port
SELECT nomS 'Service qui a le plus grand port' FROM service
WHERE portS = (SELECT MAX(portS) FROM service);
b8. Afficher le service (nom) qui a le plus petit port
SELECT nomS 'Service qui a le plus petit port' FROM service
WHERE portS = (SELECT MIN(portS) FROM service);
M. GAYE Abdoulaye SQL
b9. Afficher la somme des ports des services
SELECT SUM(portS) 'Somme des ports' FROM service;
b10. Afficher la moyenne des ports des services
SELECT AVG(portS) 'Moyenne des ports' FROM service;
b11. Mettre à jour tous les états (activé = 1) des services du serveur ISI 2
UPDATE service SET etatS=1 WHERE idServ=2;
ou bien
UPDATE service s, serveur serv SET s.etatS=1 WHERE
s.idServ=serv.idServ AND serv.libelleServ='ISI 2';
