Travaux Pratiques en SQL

publicité
Travaux Pratiques en SQL
Eric Vyncke
[email protected]
http://www.hec.be/~evyncke/cours
Dernière mise à jour: 27 février 2004
Table des Matières
•
•
•
•
•
MySQL
Utilisation de MySQL sur Linux
Utilisation d’HyperTerminal pour Linux
Plus d’info sur le serveur omega.hec.be
Utilisation de MySQL Front sur
Windows
• Description de la BD SUMMIT
• Liste d’exercices optionnels
E. Vyncke, 2004/02/27
2
Outils SGBD SQL Disponibles
• Les travaux pratiques se déroulent soit:
– sur MySQL sur le serveur HEC disponible à partir de
n’importe quel PC connecté à Internet (soit à l’école
soit chez vous)
– Sur MySQL installé sur votre PC Windows/Linux
http://www.mysql.com/downloads/
– Sous Windows, intégration avec serveur Web et
PHP
http://www.easyphp.org/index.php3?lang=fr
E. Vyncke, 2004/02/27
3
MySQL sur Linux
Démarrage de MySQL sur Linux /1
• MySQL est un programme tournant sur un serveur
Linux partagé et accessible depuis l’Internet
• Sélectionner Démarrer -> Exécuter
• Entrer telnet omega.hec.be
• Utiliser votre nom d’utilisateur email et le mot de passe
email (identiques à ceux de Windows) pour:
Login: utilisateur (suivi de <Enter>)
Password: mot de passe (n’apparait pas à l’écran, suivi
de <Enter>)
E. Vyncke, 2004/02/27
5
Démarrage de MySQL sur Linux /2
• Le prompt standard de Linux/Unix apparait et vous
pouvez exécuter des commandes Linux:
– afficher le contenu du répertoire: ls -al
– afficher le répertoire courant: pwd
– changer de répertoire: cd nomderépertoire
– afficher le contenu d’un fichier: more nomdefichier
– terminer: exit
– envoyer un fichier à une adresse e-mail
mail -s “Fichier” adresseemail < nomdefichier
E. Vyncke, 2004/02/27
6
Démarrage de MySQL sur Linux /3
• Démarrez MySQL sans journal
mysql -p –u utilisateur base
mysql –p –u test test
Nom de la base de
données
Nom de l'utilisateur
• Démarrez MySQL avec un journal: mysql.log
mysql –p –u summit –-tee=mysql1.log summit
Il est conseillé de numéroter les divers journaux, par exemple
mysql5.log pour le 5ème cours
• Et entrez le mot de passe de la base de donnée au
prompt:
Enter password: (attention aucun '*' n'est affiché)
E. Vyncke, 2004/02/27
7
Les diverses bases MySQL sur Omega
Base
Objectif
Nom
Mot de
d’utilisateur passe
test
Accès en écriture,
excercices
CREATE, INSERT,
UPDATE, …
test
test
summit
Accès en lecture,
excercices sur
SELECT
summit
Summit
world
Accès en lecture,
autres exercices
sur SELECT
routard
guidedu
E. Vyncke, 2004/02/27
8
Exemple de démarrage
E. Vyncke, 2004/02/27
9
Trucs pour MySQL
• La touche flèche vers le haut permet de rappeler
la commande précédente (à chaque nouvelle
pression la commande précédente est affichée)
• Les flèches vers la droite et la gauche
permettent de se déplacer dans la commande
• Ne pas oublier de terminer la commande par “;”
et ENTREE
• Toutes les commandes entrées sont
journalisées dans le fichier caché .mysql_history
E. Vyncke, 2004/02/27
10
Terminer MySQL
• Retourner au prompt de Linux: exit
• Terminer le programme Telnet: exit
E. Vyncke, 2004/02/27
11
Utilisation d’HyperTerminal
Utilisation d’Hyperterminal /1
• Sur Windows 2000 (notamment aux HEC), un
programme de remplacement à Telnet existe:
Hyperterminal
• Pour le démarrer:
– Démarrer/Programmes/Accessoires/Communication
s/Hyperterminal
– Sinon, cliquer sur l’icône Omega du bureau
E. Vyncke, 2004/02/27
13
Hyperterminal /2
• Entrer un nom pour cette
nouvelle configuration,
par exemple ‘Omega’
• Choisir une icône
(n’importe laquelle)
E. Vyncke, 2004/02/27
14
Hyperterminal /3
• Sélectionner TCP/IP
comme moyen de
connexion
• L’adresse hôte doit être
omega.hec.be
• La porte 23
E. Vyncke, 2004/02/27
15
Hyperterminal /4
• Et voila
E. Vyncke, 2004/02/27
16
Hyperterminal /5
• Sauver la configuration sur votre bureau
– Fichier/Enregistrer sous…
– Sauver le fichier omega.ht sur le bureau
• A faire uniquement la première fois
E. Vyncke, 2004/02/27
17
Utilisation du Bloc Note et MySQL
• Il est conseillé de démarrer le bloc-note
– Démarrer/Programmes/Accessoire/Bloc note
– Ou Démarrer/Exécuter puis notepad
• Puis de préparer les requêtes SQL dans le blocnote, de les copier et les coller
– Hyperterminal: Edition/Coller
– Telnet: cliquer dans le fenêtre avec le bouton droit
• A la fin du cours, il est possible de sauver le
contenu du bloc-note sur Z: ou Mes Documents
E. Vyncke, 2004/02/27
18
Information sur omega.hec.be
Qq notes supplémentaires
• Depuis les PC HEC, les fichiers de votre
répertoire Linux par défaut (votre home) sont
disponibles sur Z:
• Pour impression
• Pour transfert
• En dehors de Z:, vous pouvez faire la même
chose de n’importe où sur Internet (cybercafé,
domicile, …)
E. Vyncke, 2004/02/27
20
Quelques notes sur omega
• Omega (ou omega.hec.be) est un serveur Linux
avec plusieurs services:
– mySQL: pour le cours et les besoins de l'école
– En IPv6 (génération suivante de TCP/IP)
– Avec LDAP (répertoire): http://directory.hec.be
– Avec serveur web (y compris pour les pages des
étudiants) http://www.students.hec.be
– Serveur de fichiers: \\omega\homes
E. Vyncke, 2004/02/27
21
Omega et le courier électronique
• Tout les étudiants ont
une adresse email du
genre
Prenom.Nom@studen
ts.hec.be
• Sans publicité et
gratuite
• Interface web comme
hotmail
http://webmail.student
s.hec.be
E. Vyncke, 2004/02/27
22
Utilisation de MySQL Front
Interface graphique SQL sur Windows
MySQL Front
• MySQLFront
– Logiciel Windows
– Version 2.0 gratuite (voir les documents du cours SI06 sur
campus.hec.be)
– Version 3.0 payante: http://www.mysqlfront.de/
• Mode client-serveur
– Interface graphique sur votre PC
– Vrai serveur de base de données: MySQL sur omega.hec.be
– Communication par réseau TCP/IP (y compris de chez vous)
E. Vyncke, 2004/02/27
24
MySQL Front 1ère Connexion /1
E. Vyncke, 2004/02/27
25
MySQL Front 1ère Connexion /2
E. Vyncke, 2004/02/27
26
MySQL Front 1ère Connexion /3
Nom
d’utilisateur
Mot de
passe. Ici
summit
Nom de la
base de
données
E. Vyncke, 2004/02/27
27
MySQL Front: l’écran…
Structure de la
base de données
E. Vyncke, 2004/02/27
Historique des
Requêtes SQL
28
MySQL Front: contenu d’une table
1) Cliquer sur le nom d’une table
2) Cliquer sur le taquet data
E. Vyncke, 2004/02/27
29
MySQL Front: Requête SQL
1) Cliquer sur le taquet SQL Query
2) Entrer une instruction SQL
3) Cliquer sur le triangle pour executer
TOUTES les instructions SQL du panneau
E. Vyncke, 2004/02/27
30
MySQLFront: Quelques Astuces
• MYSQLFront v2.0 est gratuit mais a quelques
bugs…
– Notamment: ‘permission denied’, il faut alors cliquer
dans le panneau gauche sur une autre table, puis
recliquer sur la taquet ‘SQL Query’
• F5 permet de rafraîchir le contenu de la table ou
de la base
• F9 permet d’exécuter TOUTES les requêtes du
panneau d’entrée SQL
E. Vyncke, 2004/02/27
31
La base de données Summit
Base de données: SUMMIT
• La base de données summit est disponible pour les
travaux pratiques
• Summit aide à gérer un ensemble de magasins
d'articles de sport (société américaine)
• A des fins de sécurité, il est impossible d'ajouter, de
modifier ou d'effacer des données
• Pour se connecter à cette base:
mysql –p –u summit –-tee=mysql1.log summit
(le mot de passe est summit)
E. Vyncke, 2004/02/27
33
Schéma
de
la
base
de
données
CUSTOMER
ORD
REGION
Id
name
DEPT
Id
name
region_id
TITLE
title
Id
name
phone
address
city
state
country
zip_code
credit_rating
sales_rep_id
region_id
comments
ITEM
Id
customer_id
date_ordered
date_shipped
sales_rep_id
total
payment_type
order_filled
PRODUCT
Id
name
short_desc
suggested_whlsl_price
whlsl_units
EMP
Id
last_name
first_name
user_id
start_date
comments
manager_id
title
dept_id
salary
commission_pct
Ord_id
item_id
product_id
price
quantity
quantity_shipped
WAREHOUSE
Id
region_id
address
city
state
country
zip_code
manager_id
INVENTORY
Product_id
warehouse_id
amount_in_stock
reorder_point
max_in_stock
out_of_stock_explanation
restock_date
CUSTOMER
CREATE TABLE customer
(id
name
phone
address
city
state
country
zip_code
credit_rating
sales_rep_id
region_id
comments
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL,
VARCHAR(50) NOT NULL,
VARCHAR(25),
VARCHAR(255),
VARCHAR(30),
VARCHAR(20),
VARCHAR(30),
VARCHAR(75),
VARCHAR(9),
NUMERIC(7,0),
NUMERIC(7,0),
VARCHAR(255))
35
DEPT
CREATE TABLE dept
(id
name
region_id
E. Vyncke, 2004/02/27
NUMERIC(7,0)NOT NULL,
VARCHAR(25)NOT NULL,
NUMERIC(7,0))
36
EMP
CREATE TABLE emp
(id
last_name
first_name
userid
start_date
comments
manager_id
title
dept_id
salary
commission_pct
E. Vyncke, 2004/02/27
NUMERIC(7,0)NOT NULL,
VARCHAR(25)NOT NULL,
VARCHAR(25),
VARCHAR(8),
DATE,
VARCHAR(255),
NUMERIC(7,0),
VARCHAR(25),
NUMERIC(7,0),
NUMERIC(11, 2),
NUMERIC(4, 2))
37
INVENTORY
CREATE TABLE inventory
(product_id
warehouse_id
amount_in_stock
reorder_point
max_in_stock
out_of_stock_explanation
restock_date
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL,
NUMERIC(7,0) NOT NULL,
NUMERIC(9,0),
NUMERIC(9,0),
NUMERIC(9,0),
VARCHAR(255),
DATE)
38
ITEM
CREATE TABLE item
(ord_id
item_id
product_id
price
quantity
quantity_shipped
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL,
NUMERIC(7,0) NOT NULL,
NUMERIC(7,0) NOT NULL,
NUMERIC(11, 2),
NUMERIC(9,0),
NUMERIC(9,0))
39
ORD
CREATE TABLE ord
(id
customer_id
date_ordered
date_shipped
sales_rep_id
total
payment_type
order_filled
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL
NUMERIC(7,0) NOT NULL
DATE,
DATE,
NUMERIC(7,0),
NUMERIC(11, 2),
VARCHAR(6),
VARCHAR(1))
40
PRODUCT
CREATE TABLE product
(id
name
short_desc
longtext_id
image_id
suggested_whlsl_price
whlsl_units
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL,
VARCHAR(50) NOT NULL,
VARCHAR(255),
NUMERIC(7,0),
NUMERIC(7,0),
NUMBER(11, 2),
VARCHAR(25))
41
REGION
CREATE TABLE region
(id
name
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL,
VARCHAR(50))
42
TITLE
CREATE TABLE title
(title
E. Vyncke, 2004/02/27
VARCHAR(25))
43
WAREHOUSE
CREATE TABLE warehouse
(id
region_id
address
city
state
country
zip_code
phone
manager_id
E. Vyncke, 2004/02/27
NUMERIC(7,0) NOT NULL,
NUMERIC(7,0) NOT NULL,
LONG,
VARCHAR(30),
VARCHAR(20),
VARCHAR(30),
VARCHAR(75),
VARCHAR(25),
NUMERIC(7,0))
44
Exercices Optionnels sur Summit
Exercices sur un tableau /1
• Afficher les noms des clients habitant aux ‘USA’
• Afficher les noms des clients contenant la lettre ‘k’
• Afficher le total des bons de commande (quantité * prix)
en USD (en supposant que la base contient les prix en
USD)
• Afficher le total des bons de commande (quantité * prix)
en EUR
• Combien de clients existent dans la base de données ?
E. Vyncke, 2004/02/27
46
Exercices sur un tableau /2
• Afficher pour chaque bon de commande: son
numéro ainsi que le nombre de lignes (de la
table ITEM) qui le composent
• Afficher le nombre de produits dont le nom
(champ name) contient ‘boot’
• Afficher le nombre d’employés par n° de
département
• Afficher, pour chaque pays, le nom du pays ainsi
que le nombre de clients de ce pays.
E. Vyncke, 2004/02/27
47
Exercices sur plusieurs tableaux /1
• Afficher pour chaque employé: son nom, son
prénom, le nom de son département et le nom
de la région
• Afficher pour chaque nom de région, le nombre
d'employés travaillant dans cette région
• Afficher pour chaque employé, son nom ainsi
que le nom de son supérieur (utilisez deux
abréviations différentes pour le même tableau)
E. Vyncke, 2004/02/27
48
Exercices sur plusieurs tableaux /2
• Afficher le nom de tous les clients ayant commandé
des produits contenant le mot ‘ Ski ’
• Quels sont les noms des produits qui sont entreposés
en ‘ Europe ’ ?
• Afficher pour chaque gestionnaire d’entrepôts le
nombre de produits (la somme de amount_in_stock)
dans ses entrepôts
• Afficher pour chaque entrepôt, son pays, le nom de son
gestionnaire ainsi que le nombre de produits en rupture
de stock (reorder_point >= amount_in_stock)
E. Vyncke, 2004/02/27
49
Exercices sur plusieurs tableaux /3
• Afficher pour chaque client: son nom, tous ses
numéros de bons de commande ainsi que le
montant HTVA et TVAC (21%) de ceux-ci
• Quels sont les noms de clients habitant dans la
même ville (et pays!) qu’un entrepôt ? (dans ce cas, il
faut utiliser une relation qui n’est pas indiquée par des flèches rouges)
• Afficher pour chaque employé, son nom, son
salaire annuel ainsi que la commission due sur
tous les bons de commandes qu’il a reçu.
E. Vyncke, 2004/02/27
50
Téléchargement