travaux pratiques en sql eric vyncke [email protected] evyncke/cours dernière mise à jour: 27...
TRANSCRIPT
Travaux Pratiques en SQL
Eric Vyncke
http://www.hec.be/~evyncke/cours
Dernière mise à jour: 27 février 2004
E. Vyncke, 2004/02/27 2
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 3
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/Linuxhttp://www.mysql.com/downloads/
– Sous Windows, intégration avec serveur Web et PHPhttp://www.easyphp.org/index.php3?lang=fr
MySQL sur Linux
E. Vyncke, 2004/02/27 5
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.betelnet omega.hec.be• Utiliser votre nom d’utilisateur email et le mot de passe
email (identiques à ceux de Windows) pour:Login: Login: utilisateurutilisateur (suivi de <Enter>)
Password: Password: mot de passemot de passe (n’apparait pas à l’écran, suivi de <Enter>)
E. Vyncke, 2004/02/27 6
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 -alls -al– afficher le répertoire courant: pwdpwd– changer de répertoire: cd cd nomderépertoirenomderépertoire– afficher le contenu d’un fichier: more more nomdefichiernomdefichier– terminer: exitexit– envoyer un fichier à une adresse e-mail mail -s “Fichier” mail -s “Fichier” adresseemailadresseemail < < nomdefichiernomdefichier
E. Vyncke, 2004/02/27 7
Démarrage de MySQL sur Linux /3
• Démarrez MySQL sans journalmysql -p –u utilisateur mysql -p –u utilisateur basebase
mysql –p –u test mysql –p –u test testtest
• Démarrez MySQL avec un journal: mysql.logmysql –p –u summit –-tee=mysql –p –u summit –-tee=mysql1.logmysql1.log summitsummit
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: Enter password: (attention aucun '*' n'est affiché)
Nom de la base de données
Nom de l'utilisateur
E. Vyncke, 2004/02/27 8
Les diverses bases MySQL sur Omega
Base Objectif Nom d’utilisateur
Mot de 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 9
Exemple de démarrage
E. Vyncke, 2004/02/27 10
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 11
Terminer MySQL
• Retourner au prompt de Linux: exitexit
• Terminer le programme Telnet: exitexit
Utilisation d’HyperTerminal
E. Vyncke, 2004/02/27 13
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/
Communications/Hyperterminal– Sinon, cliquer sur l’icône Omega du bureau
E. Vyncke, 2004/02/27 14
Hyperterminal /2
• Entrer un nom pour cette nouvelle configuration, par exemple ‘Omega’
• Choisir une icône (n’importe laquelle)
E. Vyncke, 2004/02/27 15
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 16
Hyperterminal /4
• Et voila
E. Vyncke, 2004/02/27 17
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 18
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 bloc-note, 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
Information sur omega.hec.be
E. Vyncke, 2004/02/27 20
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 21
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 22
Omega et le courier électronique
• Tout les étudiants ont une adresse email du genre [email protected]
• Sans publicité et gratuite
• Interface web comme hotmailhttp://webmail.students.hec.be
Utilisation de MySQL Front
Interface graphique SQL sur Windows
E. Vyncke, 2004/02/27 24
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 25
MySQL Front 1ère Connexion /1
E. Vyncke, 2004/02/27 26
MySQL Front 1ère Connexion /2
E. Vyncke, 2004/02/27 27
MySQL Front 1ère Connexion /3
Nom de la base de données
Nom d’utilisateur
Mot de passe. Ici summit
E. Vyncke, 2004/02/27 28
MySQL Front: l’écran…
Structure de la base de données
Historique desRequêtes SQL
E. Vyncke, 2004/02/27 29
MySQL Front: contenu d’une table
1) Cliquer sur le nom d’une table2) Cliquer sur le taquet data
E. Vyncke, 2004/02/27 30
MySQL Front: Requête SQL
1) Cliquer sur le taquet SQL Query2) Entrer une instruction SQL3) Cliquer sur le triangle pour executer
TOUTES les instructions SQL du panneau
E. Vyncke, 2004/02/27 31
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
La base de données Summit
E. Vyncke, 2004/02/27 33
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 summitmysql –p –u summit –-tee=mysql1.log summit(le mot de passe est summit)
Schéma de la base de données
Idname
REGION
Idnamephoneaddresscitystatecountryzip_codecredit_ratingsales_rep_idregion_idcomments
CUSTOMER
Idcustomer_iddate_ordereddate_shippedsales_rep_idtotalpayment_typeorder_filled
ORDOrd_iditem_idproduct_idpricequantityquantity_shipped
ITEM
Idnameregion_id
DEPT
Idlast_namefirst_nameuser_idstart_datecommentsmanager_idtitledept_idsalarycommission_pct
EMP
Idnameshort_descsuggested_whlsl_pricewhlsl_units
PRODUCT
Product_idwarehouse_idamount_in_stockreorder_pointmax_in_stockout_of_stock_explanationrestock_date
INVENTORYIdregion_idaddresscitystatecountryzip_codemanager_id
WAREHOUSE
title
TITLE
E. Vyncke, 2004/02/27 35
CUSTOMER
CREATE TABLE customer (id NUMERIC(7,0) NOT NULL, name VARCHAR(50) NOT NULL, phone VARCHAR(25), address VARCHAR(255), city VARCHAR(30), state VARCHAR(20), country VARCHAR(30), zip_code VARCHAR(75), credit_rating VARCHAR(9), sales_rep_id NUMERIC(7,0), region_id NUMERIC(7,0), comments VARCHAR(255))
E. Vyncke, 2004/02/27 36
DEPT
CREATE TABLE dept (id NUMERIC(7,0)NOT NULL, name VARCHAR(25)NOT NULL, region_id NUMERIC(7,0))
E. Vyncke, 2004/02/27 37
EMP
CREATE TABLE emp (id NUMERIC(7,0)NOT NULL, last_name VARCHAR(25)NOT NULL, first_name VARCHAR(25), userid VARCHAR(8), start_date DATE, comments VARCHAR(255), manager_id NUMERIC(7,0), title VARCHAR(25), dept_id NUMERIC(7,0), salary NUMERIC(11, 2), commission_pct NUMERIC(4, 2))
E. Vyncke, 2004/02/27 38
INVENTORY
CREATE TABLE inventory (product_id NUMERIC(7,0) NOT NULL, warehouse_id NUMERIC(7,0) NOT NULL, amount_in_stock NUMERIC(9,0), reorder_point NUMERIC(9,0), max_in_stock NUMERIC(9,0), out_of_stock_explanation VARCHAR(255), restock_date DATE)
E. Vyncke, 2004/02/27 39
ITEM
CREATE TABLE item (ord_id NUMERIC(7,0) NOT NULL, item_id NUMERIC(7,0) NOT NULL, product_id NUMERIC(7,0) NOT NULL, price NUMERIC(11, 2), quantity NUMERIC(9,0), quantity_shipped NUMERIC(9,0))
E. Vyncke, 2004/02/27 40
ORD
CREATE TABLE ord (id NUMERIC(7,0) NOT NULL, customer_id NUMERIC(7,0) NOT NULL, date_ordered DATE, date_shipped DATE, sales_rep_id NUMERIC(7,0), total NUMERIC(11, 2), payment_type VARCHAR(6), order_filled VARCHAR(1))
E. Vyncke, 2004/02/27 41
PRODUCT
CREATE TABLE product (id NUMERIC(7,0) NOT NULL, name VARCHAR(50) NOT NULL, short_desc VARCHAR(255), longtext_id NUMERIC(7,0), image_id NUMERIC(7,0), suggested_whlsl_price NUMBER(11, 2), whlsl_units VARCHAR(25))
E. Vyncke, 2004/02/27 42
REGION
CREATE TABLE region (id NUMERIC(7,0) NOT NULL, name VARCHAR(50))
E. Vyncke, 2004/02/27 43
TITLE
CREATE TABLE title(title VARCHAR(25))
E. Vyncke, 2004/02/27 44
WAREHOUSE
CREATE TABLE warehouse (id NUMERIC(7,0) NOT NULL, region_id NUMERIC(7,0) NOT NULL, address LONG, city VARCHAR(30), state VARCHAR(20), country VARCHAR(30), zip_code VARCHAR(75), phone VARCHAR(25), manager_id NUMERIC(7,0))
Exercices Optionnels sur Summit
E. Vyncke, 2004/02/27 46
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 47
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 48
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 49
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 50
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.