TD BASES DE DONNÉES AVEC mySQL

BTSiris
TDmySQL.doc page 1/13 TD Bases de données avec mySQL
1. À LA CONQUÊTE DE QUELQUES REQUÊTES
1.1. Connexion et premières requêtes
Le caractère ' ; ' termine une requête SQL .
Exécutez séparément les requêtes SQL suivantes et décrivez ce qu'elles effectuent :
Les noms de variables en mySQL commençent par @ et L'affectation est réalisée par
l'opérateur : " := " .
SELECT NOW() ;
SELECT NOW() AS date ;
SELECT NOW() AS date , VERSION() ;
SELECT CURDATE() AS date , CURTIME() AS heure ;
SELECT @result := 3*5 ;
TD BASES DE DONNÉES AVEC mySQL
NOM:
classe: .. TSiris
groupe: ……
SELECT 1 || 0 , 1 && 0 , ! 0 ;
SELECT 5/2 + 3 ;
SELECT 4/2 + 3 ;
SELECT 6 | 11 , 6 & 11 , 6 ^ 11 ;
^ ne fonctionne pas avec la version 3.23.49-
max-nt de mySQL !!
SELECT 3 = 4 , 3 < 4 , 3>=4 , 3 <> 4 ;
SELECT (1 + NULL) IS NULL , (1+ 3) IS NULL ;
SELECT 'toute' LIKE 'tou_' ;
SELECT 'tout' LIKE 'tou_' ;
SELECT 'toute' LIKE 'tou%' ;
SELECT 'toute' LIKE 'TOU%' ;
SELECT ROUND(5.346, 2) , TRUNCATE(5.346, 2) ;

BTSiris
TDmySQL.doc page 2/13 TD Bases de données avec mySQL
SELECT 'toute' LIKE BINARY 'TOU%' ;
'toute' LIKE BINARY 'TOU%'
0
SELECT ABS(-5.3) , SQRT(2) ;
SELECT CEIL(5.3) , FLOOR(5.3) ;
SELECT RAND() , RAND() , RAND() ;
SELECT CURDATE() AS date , CURTIME() AS heure ;
SELECT DATE_FORMAT("2004-09-23" , "%e %M %Y") ;
SELECT MINUTE( CURTIME() ) ;
SELECT DATE_ADD( NOW() , INTERVAL 3 MONTH) ;
SELECT DATE_SUB( NOW() , INTERVAL 3 MONTH) ;
SELECT CHAR(98) , CHAR(111) , CHAR(110) , ASCII("bonjour") ;
SELECT PASSWORD( 'toto' ) ;
SELECT INSERT( 'bonjour !' , 4 , 4 , 'soir :=) ') ;
SELECT CONCAT( UPPER( 'bonjour' ) , ' ! ' ) ;
SELECT GREATEST( 'bonjour' , 'bonsoir' ) ;
SELECT LEAST( 'bonjour' , 'bonsoir' ) ;
SELECT INTERVAL(21 , -4, 2+3, 4*5 ) ;
SELECT CASE DAYOFWEEK( CURDATE() )
WHEN 1 THEN 'dimanche'
WHEN 2 THEN 'lundi'
WHEN 3 THEN 'mardi'
WHEN 4 THEN 'mercredi'
WHEN 5 THEN 'jeudi'
WHEN 6 THEN 'vendredi'
ELSE 'samedi'
END
AS jour ;
SELECT CURTIME() , IF( CURTIME() < '15:00:00' , 'bonjour' , 'bonsoir' ) ;

BTSiris
TDmySQL.doc page 3/13 TD Bases de données avec mySQL
1.2. Requêtes sur BDD
2. CAS D' ÉTUDE
2.1. Description du modèle
On prend l'exemple de magasins en France qui gèrent en stock des tee-shirts de différents
modèles (manches longues ou courtes, rayés, polaires,...), en plusieurs tailles (XS, S, M, L,
XL, XXL) et dans des couleurs différentes. Fixé par le fabriquant, le prix d'un tee-shirts dépend
du prix de base majoré par la taille et par la couleur.
Le but est de pouvoir consulter la BDD pour obtenir les modèles proposés, ainsi que le prix
pour un modèle et sa disponibilité dans quel magasin, etc.
a) Table Magasin
Pour éviter les erreurs et les incohérences, la ville correspondant au code postal figurera dans
une autre table. le champ n_mag est ici clé primaire (champ souligné).
a) Table Poste
c) Table Couleur
CREATE DATABASE tee_shirt ;
avec
mySQL
1 BDD ⇔ 1 répertoire
1 Table
⇔
1 fichier
SHOW DATABASES ;
SHOW DATABASES ;
cp
ville
cp
ville
83980
83000
74340
73440
Le Lavandou
Toulon
Samoëns
Les Ménuires
74110
33130
50170
56000
Morzine
Bègles
Le Mont St Michel
Vannes
Tableau 4.2. Exemple de contenu pour la table Poste .
n_mag
nom
adresse
tel
cp
1
2
3
4
5
6
Tee-Shirts en folies
Le tee-shirt rayé
Le tee-shirt rayé
Le royaume du tee-shirt
Le tee-shirt polaire
Le tee-shirt polaire
avenue du Tee-Shirt
place du port
rue du Mont
place de la liberté
rue des pistes
place du vieux tilleul
0494123456
0297123456
0233123456
0494123456
0212345678
0450123456
83980
56000
50170
83000
73440
74340
Tableau 4.1. Exemple de contenu pour la table Magasin .

BTSiris
TDmySQL.doc page 4/13 TD Bases de données avec mySQL
d) Table Taille
e) Table Tee_shirt
f) Table Stock
2.2. Création des tables
nom_couleur
majoration
rouge
rose
vert
bleu-marine
bleu
cyan
jaune
citron vert
magenta
blanc
noir
gris
10
5
10
8
10
5
4
2
3
0
0
0
Tableau 4.3. Exemple de contenu pour la table Couleur .
nom_taille
majoration
nom_taille
majoration
XS
S
L
0
5
8
M
XL
XXL
10
12
15
Tableau 4.4. Exemple de contenu pour la table Taille .
ref
intitulé
prix
ref
intitulé
prix
mc00
mc01
ml00
manches courtes coton
manches courtes polaire
manches longues coton
7.50
7.50
9.00
ml01
co00
co01
manches longues polaire
col ouvert
rayé col ouvert
9.00
9.00
9.00
Tableau 4.5. Exemple de contenu pour la table Tee_shirt .
n_mag
ref
nom_taille
nom_couleur
quantite
1
1
1
1
1
...
5
5
5
5
mc00
mc00
mc00
ml00
ml00
...
ml01
ml01
ml01
ml01
L
L
L
L
L
...
L
XL
S
M
rouge
vert
blanc
rouge
jaune
...
vert
jaune
bleu-marine
bleu
10
5
15
10
7
...
59
33
11
48
Tableau 4.6. Exemple de contenu pour la table Stock .

BTSiris
TDmySQL.doc page 5/13 TD Bases de données avec mySQL
Le répertoire des BDD est situé dans l'arborescence : " easyPHP \ mysql \ data " .
MySQL est insensible à la casse. Les noms des BDD et des tables sont sensibles à la casse.
Les clés étrangères sont gérées par des " InnoDB " (version > 3.23 ).
La syntaxe simplifiée de création d'une table est la suivante :
Le caractère # commence une ligne de commentaire, ainsi que -- avec un espace après.
Les caractères /* et */ peuvent contenir plusieurs lignes de commentaires (comme en C ).
-- *************************************************
-- creation des tables de la base tee_shirt
-- *************************************************
use tee_shirt ;
CREATE TABLE Poste
(
cp MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL, # entier complété par des 0
ville VARCHAR(100),
PRIMARY KEY( cp ) # cp est clé primaire
) ;
CREATE TABLE Magasin
(
n_mag TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
nom VARCHAR(100) NOT NULL,
adresse VARCHAR(100) NOT NULL,
tel CHAR(10),
cp MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL,
PRIMARY KEY( n_mag )
) ;
CREATE TABLE Tee_Shirt
(
ref VARCHAR(4) NOT NULL,
intitule VARCHAR(100) NOT NULL,
prix FLOAT(5,2) UNSIGNED,
PRIMARY KEY( ref )
) ;
CREATE TABLE Taille
(
nom_taille VARCHAR(30) NOT NULL,
majoration TINYINT(3) UNSIGNED NOT NULL,
CREATE TABLE [ IF NOT EXISTS ] nom_table
( nom_colonne1 TYPE [PROPRIETE]* ,
nom_colonne2 TYPE [PROPRIETE]* ,
............................................................
nom_colonneN TYPE [PROPRIETE]* [ ,
PRIMARY KEY ( liste_colonnes ) ]
) ;