Cours 8
FONCTIONS ET REGROUPEMENTS


Quelques fonctions utiles





Pour faire concaténation de textes lors de l'affichage:

concat(string1, string2, ...)




Pour extraire des parties d'un 'string':

left(string, n) ou right(string, n) ou mid(string, n, m)




right('mysql', 3)
-->sql

mid('mysql', 3, 2)
-->sq



Pour changer la casse lors de l'affichage:

ucase(string) ou upper(string) pour convertir à majuscules

lcase(string) ou lower(string) pour convertir à minuscules




Pour obtenir la longueur d'une chaîne:

length(string) retourne le nombre de caractères dans 'string'




Et quelques fonctions numériques qui peuvent servir:

round(x) retourne le nombre x arrondi au plus proche entier

round(1.234) --> 1
round(1.58) --> 2


round(x, d) retourne le nombre x arrondi à d chiffres après le décimal

round(1.236, 2) --> 1.24
round(1.549, 1) --> 1.5




truncate(x, d) retourne le nombre x tronqué à d chiffres après le décimal

truncate(1.999, 1) --> 1.9
truncate(1.3456, 2) --> 1.34
truncate(1.99999, 0) --> 1



Utilisation d'un Alias

Supposons que j'ajoute une table de départements, que j'appelle 'Dept', à la b.d. gestproj. Chaque département a un Directeur. Ce Directeur est un employé donc, on peut garder seulement son Id pour l'identifier dans la table Dept.

Dans une requête je pourrais retrouver le nom du Directeur en spécifiant une relation:




Mais si je devais maintenant afficher le nom du Directeur et le nom d'un employé qui a travaillé sur un projet j'ai un problème.




On ne peut pas avoir 2 clés étrangères qui pointent à une seule clé primaire dans la table Employe.

La solution est d'utiliser un Alias comme:














Création d'une nouvelle base de données


Pour cette section nous allons créer une nouvelle base de données "Livres", basée sur une bd MS-Access existante, "BookStor.mdb".

La première chose à faire est de créer "Livres" au moyen de la commande:

CREATE DATABASE Livres


Ensuite, il faudra créer la table "Authors" selon le modèle suivant:




La table Authors fait partie du domaine public depuis des années et est utilisée dans toutes sortes de cours de SQL. Elle est déjà peuplée de données et nous allons utiliser ces données pour nos tests.

Il faut d'abord récupérer la base de données originale, BookStor.mdb, qui contient la table Authors:

Base de données BookStor (format MySQL).

Base de données BookStor (format Access).

Vous constaterez que le fichier .zip contient aussi un fichier "authors.txt".

La bd BookStor étant en Access, il faut exporter les données de la table Authors à un fichier de type .txt:
En Access --> choisir table Authors --> Export --> Save as type --> .txt --> Nom: Authors

Ceci crée un fichier de texte avec les champs séparés par des virgules, les champs de type texte entre " " et les montants avec un $.



Ensuite, pour pouvoir les insérer dans la bd "Livres" en MySQL il faut utiliser une commande spéciale qui lit le fichier .txt et fait un "Insert into ...". La commande pour faire cela est:

LOAD DATA INFILE 'authors.txt' INTO TABLE authors
                     fields terminated by ','


Référence: LOAD DATA INFILE






Regroupements de données


Nous avons déjà vu que les fonctions statistiques servent à compter, calculer la moyenne, etc. pour un ensemble de données. Par exemple:

SELECT COUNT(id) AS Nombre_auteurs
FROM Authors
WHERE subject = 'Romance';


Mais, si je ne sais pas quelle catégories existent et que je veux savoir combien d'auteurs j'ai dans chaque catégorie, il faut une nouvelle structure:




SELECT subject as Sujet, count(id) as 'Nombre auteurs'
FROM `authors`
group by subject





La clause GROUP BY ... sert à regrouper les données selon une colonne spécifiée.


On peut aussi faire plus qu'un calcul sur un groupe de données.

Combien d'auteurs dans chaque état et quel est le salaire moyen?




SELECT state as State, count(id) as 'Nombre auteurs',
Round(avg(salary),2) as 'Salaire moyen'
FROM `authors`
group by state





On peut aussi regrouper sur 2,3,4,... colonnes :

Combien d'auteurs de chaque catégorie résident dans chaque état?




SELECT state as State, subject as Sujet,
count(id) as 'Nombre auteurs',
Round(avg(salary),2) as 'Salaire moyen'
FROM `authors`
group by state, subject





Et finalement, notons qu'il y a une clause spéciale si on veut choisir certains groupes.

Pour faire une sélection de groupes on utilise la clause: HAVING ... . Les conditions de HAVING sont exactemment les mêmes que dans le WHERE.




SELECT state as State, subject as Sujet, sex,
count(id) as 'Nombre auteurs',
Round(avg(salary),2) as 'Salaire moyen'
FROM `authors`
group by state, subject, sex
having state = 'ca' or state = 'ks'





QUIZ:

Afficher le nombre de livres publiés et le total des ventes annuelles pour chaque auteur (le nom, pas le numéro).






Haut de la page