Le langage SQL pour Structured Query Language est un langage standardisé d'accès au données des SGBDR. Comme tout langage, il a sa propre sémantique et sa propre syntaxe. Il permet à la fois de définir les données (LDD - Langage de définition de données), d'y accéder (LMD - Langage de manipulation de données) et d'en gérer les droits (LPD - Langage de protection des données). Dans le cadre du développement Web et grâce à l'assistance de PhpMyadmin, nous n'étudierons que la partie manipulation de données.
La commande SELECT est définie par la syntaxe suivante :
SELECT [DISTINCT ou ALL] * ou liste de colonnes FROM nom de table ou de la vue [WHERE prédicats] [GROUP BY ordre des groupes] [HAVING condition] [ORDER BY ] liste de colonnes
SELECT : permet de définir les colonnes résultats, le signe * retournant toutes les colonnes des enregistrements sélectionnés
FROM : définit la table sur laquelle porte la requête
WHERE : le filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat
GROUP BY : définition d'un sous-ensemble
HAVING : filtre portant sur les résultats (conditions de regroupement des lignes)
ORDER BY : tri des résultats
La commande permettant d'afficher l'ensemble des lignes d'une table est donc : SELECT * FROM CLIENT
Donc, afficher l'ensemble des lignes de notre table PLANCHE est donc :
SELECT * FROM planche
PLA_NUM PLA_MARQUE PLA_MODELE PLA_ANNEE PLA_TAILLE PLA_POIDS 1 TIGA SLALOM 260 1997 2 9 2 EXOCET WAVE 259 2000 2 7 3 MISTRAL BEAST 2003 2 5 4 BIC SAXO 2000 2
Pour afficher des lignes avec certains champs, et renommer ces champs pour l'affichage des résultats, nous pouvons exécuter la requête suivante :
SELECT PLA_MARQUE AS MARQUE, PLA_MODELE AS MODELE FROM planche
MARQUE MODELE TIGA SLALOM 260 EXOCET WAVE 259 MISTRAL BEAST BIC SAXO
Nous pouvons égalemennt classer ces résultats par ordre alphabétique par exemple : un premier tri sur le nom de la marque, et un deuxième sur le modèle de la planche.
SELECT PLA_MARQUE AS MARQUE, PLA_MODELE AS MODELE FROM planche ORDER BY PLA_MARQUE ASC, PLA_MODELE ASC
MARQUE MODELE BIC SAXO EXOCET WAVE 259 MISTRAL BEAST TIGA SLALOM 260
MySQL nous propose aussi de limiter l'affichage des résultats : nous pouvons par exemple afficher les deux premières lignes des résultats :
SELECT PLA_MARQUE AS MARQUE, PLA_MODELE AS MODELE FROM planche ORDER BY PLA_MARQUE ASC, PLA_MODELE ASC LIMITS 0,2
ou les lignes à partir de la troisième jusque la denière :
SELECT PLA_MARQUE AS MARQUE, PLA_MODELE AS MODELE FROM planche ORDER BY PLA_MARQUE ASC, PLA_MODELE ASC LIMITS 2,-1
Nous pouvons aussi supprimer les doublons dans les résultats en utilisant l'opérateur DISTINCT
SELECT DISTINCT CLI_PRENOM AS PRENOM FROM client
PRENOM Ivan Paul Jean Marc
Il est également possible d'afficher des calculs dans les résultats. La calcul de la masse volumique des planches donnerait par exemple :
SELECT PLA_MARQUE, PLA_MODELE, PLA_POIDS / PLA_VOLUME AS MASSE_VOLUMIQUE FROM planche
PLA_MARQUE PLA_MODELE MASSE_VOLUMIQUE TIGA SLALOM 260 0.1 EXOCET WAVE 259 0.088607594936709 MISTRAL BEAST 0.045454545454545 BIC SAXO 0.08
L'interêt d'une base de données ne se limite heureusement pas à un simple listage des données. Il est généralement nécessaire de donner des conditions ou filtres. Seules seront sélectionnées les lignes satisfaisant aux conditions situées après la clause WHERE.
La sélection des clients habitant à ST-PAUL peut donc se faire avec la requête :
SELECT * FROM client WHERE CLI_VILLE = "ST-PAUL"
CLI_ID CLI_NOM CLI_PRENOM CLI_ADR CLI_VILLE CLI_TEL 1 KURZWEG Ivan Rue des mouettes ST-PAUL 0692012345 3 PAYET Jean 6 rue des hortensias ST-PAUL 0262010203 5 GRONDIN paul 23 rue des rosiers ST-PAUL 0263708090
Nous allons voir dans les paragraphes suivants l'ensemble des opérateurs permettant de réaliser les prédicats de sélection, c'est-à -dire les fonctions qui vous nous permettre d'appliquer des filtres sur les champs des enregistrements.
Nous avons à notre disposition l'ensemble des opérateurs mathématiques et logiques courants :+ - * /
Tableau 2. Opérateurs
| Opérateur | Symbole |
| Addition | + |
| Soustraction | - |
| Multiplication | * |
| Division | / |
| ET logique | AND |
| OU logique | OR |
| NON logique | NOT |
| INFERIEUR | < |
| SUPERIEUR | > |
| EGALITE | = |
| Comparaison de chaînes | LIKE |
| Intervalle | BETWEEN |
Nous pouvons ainsi sélectionner les planches qui datent d'avant 2003 :
SELECT * FROM PLANCHE WHERE PLA_ANNEE < 2003
PLA_NUM PLA_MARQUE PLA_MODELE PLA_ANNEE PLA_TAILLE PLA_POIDS PLA_VOLUME 1 TIGA SLALOM 260 1997 2 9 90 2 EXOCET WAVE 259 2000 2 7 79 4 BIC SAXO 2000 2 8 100
En combinant les filtres, on peut récupérer les enregistrements des planches antérieures à 2003 et d'un volume inférieur à 100 litres :
SELECT * FROM PLANCHE WHERE PLA_ANNEE < 2003 AND PLA_VOLUME < 100
PLA_NUM PLA_MARQUE PLA_MODELE PLA_ANNEE PLA_TAILLE PLA_POIDS PLA_VOLUME 1 TIGA SLALOM 260 1997 2 9 90 2 EXOCET WAVE 259 2000 2 7 79
La sélection des clients dont on a un numéro de GSM donnerait :
SELECT * FROM CLIENT WHERE CLI_TEL LIKE "06%"
CLI_ID CLI_NOM CLI_PRENOM CLI_ADR CLI_VILLE CLI_TEL 1 KURZWEG Ivan Rue des mouettes ST-PAUL 0692012345 2 HOARAU Paul 2 rue des Lilias ST-PIERRE 0692101112
Ici, le caractères % permet de pérciser à MySQL de sélectionner les enregistrements dont le numéro de téléphone est composé de laa manière suivante : la chaîne "06" suivie de n'importe quel caractère.
En plus des différents opérateurs que nous avons vu au paragraphe précédent, nous pouvons également utiliser des fonctions sur les valeurs des champs. Nous n'en détaillerons que certaines, la liste complète de ces fonctions étant disponible dans la documentation de MySQL :
TRIM ([LEADING ou TRAILING ou BOTH] [caractère] FROM nom de colonne) : permet de supprimer les espaces ou tout autre caractère spécifié avant (LEADING) ou après (TRAILING) le champ.
UPPER(colonne) LOWER(colonne) : passent respectivement en majuscule et minuscules les valeurs de la colonne
SUBSTRING ( nom de colonne,M,N) : extrait la sous-chaîne de caractère commençant à la Mième lettre et de longueur N.
SELECT CLI_NOM, CLI_PRENOM, UPPER(CONCAT(SUBSTRING(CLI_NOM,1,1) , SUBSTRING(CLI_PRENOM,1 ,1))) AS INITIALES FROM CLIENT
CLI_NOM CLI_PRENOM INITIALES KURZWEG Ivan KI HOARAU Paul HP PAYET Jean PJ PAYET Marc PM GRONDIN paul GP
De la même manière, il est possible de travailler sur les dates :
EXTRACT ( YEAR ou MONTH ou DAY FROM nom de colonne ) : permet d'extraire l'année, le numéro du mois ou le numéro du jour d'une date
NOW() : permet de récupérer la date et l'heure courante
TO_DAYS(date) : renvoie le nombre de jours depuis la date 0 jusque la date date
FROM_DAYS(date) : renvoie le nombre de jours écoulées depuis la date date
La sélection de toutes les locations effectuées pendant le mois d'octobre peut donc s'écrire :
SELECT * FROM loue WHERE MONTH(LOU_DATE) = 10
LOU_ID PLA_NUM CLI_ID LOU_DEBUT LOU_FIN LOU_DATE 1 1 2 14:10:00 15:12:00 2003-10-06 2 1 2 16:08:00 17:35:00 2003-10-15 3 2 2 13:36:00 14:35:00 2003-10-25
Il existe de nombreuses autres fonctions de travail sur les dates, mais nous ne les passerons pas toutes en revue. Elles sont décrites précisément dans la documentation de MySQL.
Il existe des fonctions de statistiques permettant par exemple de calculer le nombre de lignes qu'une requête renvoie. L'utilisation de ces fonctions nécessite souvent la présence de la clause GROUP BY, permettant de créer des sous-ensembles de lignes : les enregistrements sont groupés selon la valeur des colonnes de la clause GROUP BY.
Par exemple, nous souhaitons calculer le nombre de planche du magasin :
SELECT COUNT(PLA_MODELE) FROM PLANCHE COUNT(PLA_MODELE) 4
Mais si notre requête vise désormais à compter le nombre de planche par marque, nous devons grouper les lignes par marque, pour les compter :
SELECT COUNT(PLA_MODELE), PLA_MARQUE FROM planche GROUP BY PLA_MARQUE COUNT(PLA_MODELE) PLA_MARQUE 1 BIC 1 EXOCET 1 MISTRAL 2 TIGA
La longueur moyenne des planches serait obtenue par la requête suivante :
SELECT AVG(PLA_TAILLE), PLA_MARQUE FROM PLANCHE GROUP BY PLA_MARQUE
AVG(PLA_TAILLE) PLA_MARQUE 2.7799999713898 BIC 2.5899999141693 EXOCET 2.5699999332428 MISTRAL 2.6749999523163 TIGA
la durée moyenne de location pourrait être obtenue par :
SELECT SEC_TO_TIME( AVG(TIME_TO_SEC(LOU_FIN) - TIME_TO_SEC(LOU_DEBUT)) ) AS MOYENNE FROM loue
MOYENNE 01:09:20
Nous verrons dans le chapitre suivant qu'il est également possible de filtrer les résultats d'une requête en utilisant la clause HAVING.
Nous souhaitons maintenant entrer des données dans les tables de la bases de données.
La documentation de MySQL nous fournit la syntaxe de la commande SQL permettant d'insérer des enregistrements dans une table :
INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] nom_de_table [(nom_colonne,...)] VALUES ((expression | DEFAULT),...),(...),...
Nous retiendrons de cette définition que pour insérer des données dans une table, nous devons avoir une instruction SQl du type :
INSERT INTO NOM_TABLE (LISTE DES COLONNES) VALUES (LISTE DES VALEURS)
Dans notre exemple, nous voulons insérer deux planches à voiles :
INSERT INTO `planche` (`PLA_NUM`, `PLA_MARQUE`,
`PLA_MODELE`, `PLA_ANNEE`, `PLA_TAILLE`, `PLA_POIDS`) VALUES ('1',
'TIGA', 'SLALOM 260', '1997', '2,60', '9'); INSERT INTO `planche` (`PLA_NUM`, `PLA_MARQUE`,
`PLA_MODELE`, `PLA_ANNEE`, `PLA_TAILLE`, `PLA_POIDS`) VALUES ('2',
'EXOCET', 'WAVE 259', '1999', '2,59', '7');Un client se présente maintenant. Nous souhaitons l'enregistrer :
INSERT INTO `client` (`CLI_ID`, `CLI_NOM`, `CLI_PRENOM`,
`CLI_ADR`, `CLI_TEL`) VALUES ('1', 'KURZWEG', 'Ivan', 'Rue des
mouettes', '0692012345');Il désire louer la planche de vagues Exocet. Nous devons donc enregistrer une ligne dans la table LOUE, en y mettant l'heure courante :
INSERT INTO `loue` (`PLA_NUM`, `CLI_ID`, `LOU_DEBUT`,
`LOU_FIN`) VALUES ('2', '1', NOW(), NULL);Dans ce cas, nous avons insérer l'heure courante en utilisant la fonction SQL NOW(), qui nous renvoie l'heure au format TIME. Pour l'heure de retour, nous n'avons rien mis, en indiquant à MySQL de remplir ce champ avec NULL.
Notre client a terminé sa session de navigation. Il nous ramène donc la planche à voile, et nous devons maintenant mettre à jour l'heure de retour du matériel. Pour ceci, nous allons utiliser l'instruction de modification de N-Uplet, donc la syntaxe est précisée par la documentation MySQL :
UPDATE [LOW_PRIORITY] [IGNORE] nom_de_table SET nom_colonne1=expr1 [, nom_colonne2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #]
Nous retiendrons pour le moment que la mise à jour d'enregistrements se fait en utilisant une requête SQL de la forme
UPDATE NOM_TABLE SET NOM_COL = VALEUR WHERE NOM_COL = VALEUR;
Pour mettre à jour l'occurence de la table LOUE représentant que le client numéro 1 a ramené la planche numéro 2 à l'instant :
UPDATE LOUE SET LOU_FIN = NOW() WHERE PLA_NUM = 2 AND
CLI_ID = 1;Pour le moment, cette instruction marche correctement, puisque le client 1 n'a jamais loué la planche 2 auparavant. Dans le cas contraire, nous aurions surement eu quelques problèmes ...
La suppression de données en SQL se fait en exécutant l'instruction SQL DELETE :
DELETE [LOW_PRIORITY] [QUICK] FROM nom_de_table [WHERE clause_where] [ORDER BY ...] [LIMIT lignes]
Nous retriendrons de cette définition que pour supprimer un enregistrement d'une table, nous utiliserons une instruction de la forme :
DELETE FROM NOM_TABLE WHERE CONDITION
Dans notre exemple, supposons que nous souhaitons supprimer la planche numéro 2. Comme nous avons utilisé le format InnoDB pour la création des tables, et spécifié que les enregistrements doivent être supprimés en cascade, MySQL se chargera de supprimer automatiquement les lignes de la table LOCATION.
DELETE FROM PLANCHE WHERE PLA_NUM = 2;