SQL, les bases. Facile !

Le SQL est un langage qui permet d'extraire des données des bases de données afin de créer de l'information utilisable par les utilisateurs

Il exprime la sélection de champs dans des tables dont les données des lignes répondent à une condition.

Le résultat peut être trié, regroupé ou retesté afin de répondre à une seconde condition

Exemple : sélectionner la liste des noms des clients français (codePays='FRA')s'écrira :

SELECT nom FROM client WHERE codePays='FRA' ORDER BY nom;

On trouve ici :

le mot SELECT (sélectionner des lignes) suivi de la liste des champs à extraire/afficher (ici : 'nom'),
le mot FROM ('de' en anglais) suivi de la liste des table où faire l'extraction (ici : la table 'client'),
le mot WHERE ('où' en anglais) suivi de la condition de restriction des lignes extraites (ici : les lignes doivent avoir le code 'FRA' dans le champ 'codePays', sinon on ne les prend pas),
les mots ORDER BY ('ordonné par' en anglais) qui indique l'ordre de tri des lignes extraites (ici : les noms sont triés par ordre alphabétique croissant).

Reproduire ce même schéma dans l'exercice suivant.

Faire la liste de tous les articles
Faire la liste des commandes du client n°4
Faire la liste des articles dont le prix est de 20€ au minimum (utiliser les opératuers de comparaison)
Faire la liste des commandes du 23-03-2015 (une date s'exprime sous la forme #aaaa/mm/jj#)
Faire la liste des articles dont le prix est entre 20 et 37€ inclus (ici, utiliser des comparaisons ou BETWEEN)

Jointures, etc. ...

Si je demande de faire la liste des noms de client Français, on recherche le nom dans le client. Cependant, on ne connaît pas toujours le code du pays mais plutôt son nom.

Alors, comment faire ?

Hé, hé, on utilise deux tables : la table client et la table pays.

Exemple : sélectionner la liste des noms de clients français (libellé du pays) s'écrira :

SELECT nom FROM client, pays WHERE client.codePays=pays.code AND libelle='France' ORDER BY nom;

Noter que :

On a plusieurs tables dans FROM, séparées par une virgule,
On identifie le champ qui sert de clé étrangère dans une table avec la clé primaire de l'autre table.
On indique le nom des tables devant le nom du champ (ce n'est obligatoire que si deux champs portent le même nom)

Une jointure est l'association de deux tables afin de retrouver les données correspondantes à une clé étrangère.
La clé étrangère de la table d'origine est un "pointeur" vers des données de la table cible. Ces dernières servent à plusieurs lignes de la table d'origine.

Autre exemple : sélectionner la liste des numéros de commande du client 'Ali Gator' (nom du client) s'écrira :

SELECT nom FROM cdeclient, client WHERE cdeclient.cliNum=client.cliNum AND nom='Ali Gator';

Ici, le nom de la table DOIT être indiqué car le champ cliNum existe dans les deux tables.

Reproduire ce même schéma dans l'exercice suivant.

Faire la liste de tous les codes articles de la commande n° 2 (pas de jointure)
Faire la liste de toutes les descriptions d'article de la commande n° 2 (une jointure entre ligneCdeArticle et article)
Faire la liste des commandes du client n°1 (pas de jointures)
Faire la liste des commandes payées par 'Carte bancaire' (1 jointure)
Faire la liste des codes articles achetés par 'Ali Gator' (2 jointures)
Faire la liste des descriptions des articles achetés par 'Ali Gator' (3 jointures)

Regroupement = agrégat

Le regroupement consiste à ... regrouper des lignes afin d'obtenir : un comptage (count), une somme (sum) une moyenne (avg) un maxi ou mini d'une colonne (max, min).

On regroupe pour :

count(*) : compter les lignes = compter les commandes, le nombre d'articles, etc. ...
sum(expr) : faire la somme de l'expression numérique expr = somme de quantité, de valeur (prixHT*qté), ...
avg(expr) : faire la moyenne de l'expression : moyenne de prix, moyenne de valeurs, ...
min(expr) : extraire une valeur minimum : prix mini, valeur mini, date la plus ancienne, ...
max(expr) : idem mais maximum : prix maxi, valeur maxi, date la plus récente.

D'autres fonctions existent, nous ne les verrons pas.

Exemple de requêtes de regroupement simples :
Nombre d'articles

SELECT count(*) FROM article ;

Dernier numéro de commande (= le plus grand)

SELECT max(cdeNum) FROM cdeclient ;

si on veux faire des comptage selon un certain critère (/ex. avoir le nombre de commande payées par chèque paiemtCode=1), on sera OBLIGÉ d'indiquer le champ de regroupement avec GROUP BY ('grouper par'). /ex. :
Nombre de commandes par code de paiement :

SELECT paiementCode, count(*) FROM cdeClient GROUP BY paiementCode ;

Reproduire ce même schéma dans l'exercice suivant.

Faire la liste de tous les articles (pas de comptage)
Compter le nombre d'articles (comptage simple)
Calculer le prix moyen des articles (moyenne simple)
Quel est le nombre de lignes de commandes (comptage simple)
Quel est le nombre de lignes de commandes par code article (comptage avec regroupement)
Afficher le nombre de lignes de commandes par description article (comptage avec regroupement et 1 jointure)
Afficher le total HT de chaque commande (somme d'un calcul avec regroupement et 2 jointures)

SQL modification

Le SQL sert aussi à modifier le contenu des tables. Ces requêtes sont sensiblement plus simples (à notre niveau ...)

On aura :

DELETE FROM : Supprimer des lignes d'une table
UPDATE : Changer une table en modifiant la valeur de certains champs
INSERT INTO : Insérer des lignes dans une table.

Exemples :

Effacer les vielle commandes qui datent d'avant le 31/12/2012 :

DELETE FROM cdeClient WHERE date<#31/12/2012#;

Modifier ajouter 1.5% au prix de tous les modes de transport

UPDATE modeTransport SET prixHT=prixHT*1.015 ;

Insérer le nouvel article : code='TEL04', description='Téléphone portable HS421' au prix HT unitaire de 314 EUR

INSERT INTO article (code, description, prixUHT) VALUES ('TEL04', 'Téléphone portable HS421', 314) ;

Noter que :

On ne peut avoir qu'une seule table modifiée par requête,
WHERE : Les dates sont données entre des # (dièse ou H-Tag),
WHERE peut être utilisé dans DELETE ou UPDATE mais pas dans INSERT INTO
SET set à donner la liste des modification des champs sous forme d'affectations : champ=expression
Les valeurs sont indiquées sans unité (on n'indique pas la devise €, les unités m, kg, h, s, etc. ...)
INSERT : les valeurs doivent être dans l'ordre des champs, si la liste des champs est omises, on utilise la liste implicite de tous les champs de la table, dans l'ordre où elle est définie.
INSERT : on ne donne pas de valeur à un champ AUTO_INCREMENT, le système en met une tout seul, c'est un compteur automatique très pratique pour les clés primaires.

Reproduire ce même schéma dans l'exercice suivant (*attention, certaines questions sont posées de façon ambiguës).

Supprimer les clients 11, 12 et 13 qui sont des fournisseurs, enregistrés par erreur.

Changer le mode de paiement de la commande 1 en 3 (paiement par CB).

Ajouter le nouveau client : Roméo Frigo, 21 route de Colmar, 67000 Strasbourg.(*)

Ajouter 2 TEL01 à la commande numéro 5.(*)

Ajouter 2 TEL01 à la commande numéro 1.(*)

A suivre ...

Créer la base

Vous trouverez ci-dessous les scritps SQL de création de la base de données dans mysql.
Copiez/coller ces scripts dans l'outil sql de mysql DANS L'ORDRE d'écriture ci-dessous.
C'est tout !

Procédure 1 : démarrer wampServer =>http://localhost =>phpmyadmin =>onglet SQL. Vérifier le fonctionnement des requêtes en affichant un table de la base de données (onglet 'bases de données' > choisir erptest > afficher une table).

-- Créer la base de données --
CREATE DATABASE `erpTest` ;

-- Table des modes de transport : --
CREATE TABLE IF NOT EXISTS `erpTest`.`modePaiement` (

`code` int(3) NOT NULL AUTO_INCREMENT,
`libelle` varchar(50) NOT NULL,
PRIMARY KEY (`code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `erpTest`.`modePaiement` (`code`, `libelle`) VALUES
(1, 'Chèque banquaire accompagnant la commande'), (2, 'Mandat accompagnant la commande'), (3, 'Carte bancaire');

-- Table des modes de paiement : --
CREATE TABLE IF NOT EXISTS `erpTest`.`modetransport` (

`code` int(3) NOT NULL AUTO_INCREMENT,
`libelle` varchar(70) NOT NULL,
`prixHT` float(15,5) NOT NULL,
PRIMARY KEY (`code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

INSERT INTO `erpTest`.`modetransport` (`code`, `libelle`, `prixHT`) VALUES
(1, 'Lettre Max (2,18/20g, 1Kg maxi)', 2.18000), (2, 'Colissimo Emballage (7,95 à 14,50 selon volume/masse 7Kg Max)', 12.00000), (3, 'Colissimo Simple (5,23 EUR/250g, max 30Kg)', 5.23000), (4, 'Chronopost max 10Kg (selon tarifs La Poste)', 20.00000), (5, 'Chronopost max 30Kg (selon tarifs La Poste)', 30.00000), (6, 'Colis postal éco (selon volume/masse)', 20.00000), (7, 'Transporteur (entre 15 et 50 EUR)', 30.00000), (8, 'Colis Suivi (entre 12 et 25 EUR)', 20.00000), (9, 'Colis Express (entre 12 et 25 EUR)', 20.00000);

-- Table des codes pays : --
CREATE TABLE IF NOT EXISTS `erpTest`.`pays` (

`code` varchar(3) NOT NULL,
`libelle` varchar(50) NOT NULL,
PRIMARY KEY (`code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `erpTest`.`pays` (`code`, `libelle`) VALUES
('AND', 'Andorre'),('AUS', 'Osterreich'),('BEL', 'Belgique'),('ESP', 'Espana'), ('FRA', 'France'),('GBR', 'Great Britain'),('GER', 'Deutchland'),('HEL', 'Suisse'), ('ITL', 'Italia'),('LUX', 'Luxembourg'),('NLD', 'Pays Bas'),('POL', 'Pologne'), ('POR', 'Portugal'),('USA', 'United Nation of America');

-- Table des articles (produits) : --
CREATE TABLE IF NOT EXISTS `erpTest`.`article` (

`code` varchar(8) NOT NULL COMMENT 'Référence article',
`description` varchar(50) NOT NULL COMMENT 'Description article',
`prixUHT` float(15,5) NOT NULL DEFAULT '0.00000' COMMENT 'Prix unitaire Hors Taxes',
PRIMARY KEY (`code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Articles gérés';

INSERT INTO `erpTest`.`article` (`code`, `description`, `prixUHT`) VALUES ('TEL01', 'Téléphone portable TOTO 920s', 257), ('TEL02', 'Téléphone portable TOTO 940s', 382), ('TEL03', 'Téléphone portable TOTO 920XLS', 328), ('COQ01', 'Coque portable TOTO 9xx-s', 11.5), ('COQ02', 'Coque portable TOTO 9xx-XLS Noir', 13.2), ('COQ03', 'Coque portable TOTO 9xx-XLS Rouge', 13.2), ('FILM1', 'Film anti-traces TOTO 9xx', 7.5), ('FILM2', 'Film protection antitraces TOTO 9xx', 7.5), ('FILM3', 'Film protection universel', 5.60), ('EAR01', 'Casque oreillettes Rouge metal', 25), ('EAR02', 'Casque oreillettes noir mat', 19.5), ('EAR03', 'Casque oreillettes Blanc nacré', 28.5), ('BAT01', 'Batterie générique TOTO 9xx-s', 14), ('BAT02', 'Batterie TOTO 940-s', 16), ('BAT03', 'Batterie TOTO 920XLS', 17), ('HOUS1', 'Housse chaussette universelle', 10), ('HOUS2', 'Housse protection \"portefeuille\"', 11), ('HOUS3', 'Housse-coque TOTO 920XLS cuir noir', 14);

-- Table des clients--
CREATE TABLE IF NOT EXISTS `erpTest`.`client` (

`cliNum` int(11) NOT NULL AUTO_INCREMENT,
`cliNom` varchar(50) NOT NULL,
`cliAdresse` varchar(150) DEFAULT NULL,
`cliCP` varchar(8) NOT NULL,
`cliVille` varchar(50) NOT NULL,
`paysCode` varchar(3) NOT NULL,
PRIMARY KEY (`cliNum`),
KEY `paysCode` (`paysCode`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
INSERT INTO `erpTest`.`client` (`cliNum`, `cliNom`, `cliAdresse`, `cliCP`, `cliVille`, `paysCode`) VALUES (1, 'Ali Gator', '32 Route de Strasbourg', '68000', 'Colmar', 'FRA'), (2, 'Brice Glace', '23 Route de Colmar', '67000', 'Strasbourg', 'FRA'), (3, 'Charlie Corinne', '22 Quai de Paris', '67000', 'Strasbourg', 'FRA'), (4, 'Clément Tine', '33 Berliner Strasse', 'D-77671', 'Kehl', 'GER'), (5, 'Eddy Don Marcel', '123 rue de Kourou', 'LUN-123', 'Sélénia', 'BEL'), (6, 'Firmin Dustriel', '123 rue de Kourou', 'LUN-123', 'Sélénia', 'BEL'), (7, 'Gérard Mendufric', '21 rue de Hambourg', 'S-2536', 'Oslo', 'BEL'), (8, 'Hassan Cehef', '57 boulevard de Marseille', '13200', 'Nîmes', 'FRA'), (9, 'Hassan Cehef', '57 boulevard de Marseille', '13200', 'Nîmes', 'FRA'), (10, 'Iris Ksavi', '68 route de Mulhouse', '68250', 'Gebwiller', 'FRA'), (11, 'RS.Fournisseur 1', '1 chemin de Burnhaupt le Haut','68200', 'Mulhouse', 'FRA'), (12, 'RS.Fournisseur 2', '2 route de Lyon', '67200', 'Strasbourg', 'FRA'), (13, 'RS.Fournisseur 3', '3 rue d''Oberhausbergen', '67000', 'Strasbourg', 'FRA'), (14, 'John Deuf', '12563 Lexington Rd.', '4-58963', 'Arkham city', 'USA'), (15, 'Karl Amel Mou', '44 rue d''Altdorf', '67100', 'Strasbourg', 'FRA'), (16, 'Luc Ratif', '87 rue d''Entzheim', '67400', 'Illkirch', 'FRA'), (17, 'Moshé Rimonamour', '2457 London Rd.', '458-69', 'Leeds', 'GBR'), (18, 'Norma Leman', '123 rue de Genève', 'H-4578', 'Lausanne', 'HEL'), (19, 'Odile Kroch', '88 Route de Strasbourg', '67650', 'Obernai', 'FRA'), (20, 'Pat Réloin', '5 Rue Schoch', '67000', 'Strasbourg', 'FRA');

-- Table des commandes client :--
CREATE TABLE IF NOT EXISTS `erpTest`.`cdeclient` (

`cdeNum` int(11) NOT NULL AUTO_INCREMENT,
`cdeDate` date NOT NULL,
`cliNum` int(11) NOT NULL,
`paiemtCode` int(3) NOT NULL,
`transCode` int(3) NOT NULL,
PRIMARY KEY (`cdeNum`),
KEY `cliNum` (`cliNum`),
KEY `paiemtCode` (`paiemtCode`),
KEY `transCode` (`transCode`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Commandes' AUTO_INCREMENT=21 ;

INSERT INTO `erpTest`.`cdeclient` (`cdeNum`, `cdeDate`, `cliNum`, `transCode`, `paiemtCode`) VALUES
(1, '2015-02-20', 1, 1, 1),(2, '2015-02-22', 1, 1, 1),(3, '2015-02-25', 1, 5, 3),(4, '2015-02-25', 2, 5, 3),(5, '2015-02-25', 3, 5, 3), (6, '2015-02-26', 4, 5, 3),(7, '2015-02-27', 5, 4, 3),(8, '2015-03-01', 6, 4, 3),(9, '2015-03-01', 7, 4, 3),(10, '2015-03-06', 8, 4, 3), (13, '2015-03-06', 8, 4, 3),(14, '2015-03-13', 14, 4, 3),(15, '2015-03-16', 15, 9, 3),(16, '2015-03-18', 16, 4, 3),(17, '2015-03-19', 17, 4, 3), (18, '2015-03-19', 18, 4, 3),(19, '2015-03-19', 19, 4, 3),(20, '2015-03-19', 20, 4, 3);

-- Table des lignes articles contenues dans les commandes client : --
CREATE TABLE IF NOT EXISTS `erpTest`.`ligneCdeArticle` (

`cdeNum` int(11) NOT NULL,
`artCode` varchar(8) NOT NULL,
`qte` float NOT NULL,
PRIMARY KEY (`artCode`,`cdeNum`),
KEY `ligneCde` (`cdeNum`),
KEY `ligneArticle` (`artCode`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lignes de commande client, association Cde-Article';

INSERT INTO `erpTest`.`ligneCdeArticle` (`cdeNum`, `artCode`, `qte`) VALUES
(1, 'TEL01', 1),(1, 'TEL02', 1),(1, 'COQ01', 1),(1, 'COQ02', 1), (2, 'TEL01', 2),(2, 'TEL02', 2),(2, 'COQ01', 2),(2, 'COQ02', 2), (3, 'TEL01', 3),(3, 'TEL02', 3),(3, 'COQ01', 3),(3, 'COQ02', 3), (4, 'FILM1', 4),(4, 'TEL01', 4),(5, 'FILM1', 5), (5, 'EAR01', 5),(5, 'EAR02', 5), (6, 'BAT01', 6),(6, 'BAT02', 6), (7, 'BAT03', 7), (8, 'BAT03', 8), (9, 'HOUS1', 1),(9, 'HOUS2', 1),(9, 'BAT03', 9), (10, 'HOUS2', 1),(10, 'TEL01', 1),(10, 'HOUS1', 1),(10, 'TEL02', 1), (13, 'HOUS1', 1),(13, 'TEL01', 1),(13, 'TEL02', 1), (14, 'TEL01', 1),(14, 'HOUS1', 1),(14, 'COQ01', 1),(14, 'EAR01', 1), (19, 'TEL03', 1), (20, 'TEL03', 1);

-- Définition des clés étrangères--
ALTER TABLE `erpTest`.`client`

ADD CONSTRAINT `client_ibfk_1` FOREIGN KEY (`paysCode`) REFERENCES `pays` (`code`);

ALTER TABLE `erpTest`.`cdeclient`

ADD CONSTRAINT `cdeclient_ibfk_1` FOREIGN KEY (`transCode`) REFERENCES `modetransport` (`code`),
ADD CONSTRAINT `cdeclient_ibfk_2` FOREIGN KEY (`paiemtCode`) REFERENCES `modepaiement` (`code`),
ADD CONSTRAINT `cdeclient_ibfk_3` FOREIGN KEY (`cliNum`) REFERENCES `client` (`cliNum`);

ALTER TABLE `erpTest`.`ligneCdeArticle`

ADD CONSTRAINT `lignecdeclientart_ibfk_1` FOREIGN KEY (`cdeNum`) REFERENCES `cdeclient` (`cdeNum`),
ADD CONSTRAINT `lignecdeclientart_ibfk_2` FOREIGN KEY (`artCode`) REFERENCES `article` (`code`);