sql
SQL¶
pour la suite du cours on utilisera le jeu de données fourni
On trouvera une description des commandes SQL en français à l'adresse suivante
La documentation complète MySql est disponible à l'adresse suivante
Consultation des données : SELECT¶
interrogation simple¶
SELECT <table1>.<champ1>, <table1>.<champ2> FROM <schema>.<table1>
La requete retourne l'ensemble des enregistrements de la table
example : select auteurs.Nom from auteurs select auteurs.Prenom, auteurs.Nom from auteurs
Jointure élémentaire¶
SELECT <table1>.<champ1>, <table1>.<champ2>, <table2>.<champ2> FROM <schema>.<table1>,<schema>.<table2> WHERE table2.idxxx = table1.id
La clause where permet d'indiquer la clause de correspondance entre les différentes tables,
Exercice :
- Afficher l'ensemble des auteurs et leur posts correspondants
Alias¶
Il est possible de "renommer" des éléments présents dans le schéma en utilisant le mot clé AS
SELECT <table1>.<champ1>, <table1>.<champ2>, <table2>.<champ2> as alias1 FROM <schema>.<table1>,<schema>.<table2> as alias2
WHERE alias2.idxxx = table1.id
Filtrage des données¶
SELECT <table1>.<champ1>, <table1>.<champ2> FROM <schema>.<table1> WHERE <condition>
example:
SELECT * FROM digitalcampus.auteurs where Nom >= "P" AND Nom < "Q"
SELECT * FROM digitalcampus.auteurs where DateNaissance > "2000-01-01" AND
SELECT * FROM digitalcampus.auteurs where DateNaissance > "2000-01-01" AND Nom <= "Adolf"
on pourra se reporter à la documentation MySql pour la syntaxe des expressions de conditions Documentation MySql
Exercice :
- Afficher les Noms et Prénoms des auteurs dont le Prenom est inferieur au Nom dans l'ordre alphabetique
- Afficher les Noms et Prénoms des auteurs dont la date de naissance est située en 2018
- Afficher les Noms et Prénoms des auteurs dont la date de naissance est supérieure à la date de création de l'un de leur posts
- Afficher les Noms, Prénoms, date de naissance, date de création de posts des auteurs dont la date de naissance est supérieure à la date de création de posts
Tri des données¶
SELECT <table1>.<champ1>, <table2>.<champ2> FROM <schema>.<table1>,<schema>.<table2> WHERE <condition> order by table1.champ1 ASC, table2.champ2 DESC
ASC et DESC indique l'ordre du tri (Croissant ou Decroissant). Par defaut le tri est en ordre Croissant Documentation MySql
Exercice :
- Afficher l'ensemble des auteurs et leur posts correspondant, trier par Nom et Prenom
- Afficher l'ensemble des auteurs et leur posts correspondant, trier par date de création de posts
- Afficher les Noms, Prénoms, date de naissance, date de création de posts des auteurs trier par nom et date de creation de post croissants
Fonctions¶
Il est possible d'utiliser des fonctions, à la fois pour réaliser des calcul d'affichage et pour filtrer des enregistrements, ces opérations sont réalisées sur les champs d'un même enregistrement.
SELECT <table1>.<champ1>, <table2>.<champ2>,<table2>.<champ2> - <table1>.<champ2> as diff FROM <schema>.<table1>,<schema>.<table2> WHERE <condition>
SELECT <table1>.<champ1>, <table2>.<champ2>, as diff FROM <schema>.<table1>,<schema>.<table2> WHERE <condition> AND
Exercice :
- Afficher les auteurs dont le prénom commence par 'Jon'.
- Afficher les auteurs dont le prénom commence par 'Jon' ou finissant par 'on'.
- Afficher les auteurs qui sont nés entre le 43° jours et le 56° jours de leur année de naissance.
- Afficher les auteurs et les posts quand les posts ont été créés avant les 20 ans de leur auteur.
Fonction d'aggregation¶
il est possible de realiser des opérations sur un ensemble de ligne, calculer une moyenne, déterminer un min, un max ... Documentation MySql
SELECT <table1>.<champ1>, <table1>.<champ2>, aggregat(table1.champ3) FROM <schema>.<table1>,<schema>.<table2> WHERE <condition> GROUP BY <champ>
Si GROUP BY n'est pas spécifié, le calcul est effectuer sur l'ensemble des enregistrements retournés par la requête. Si GROUP BY est spécifié, le calcul est effectuer sur les ensembles d'enregistrements regroupé par le champ spécifié
Exercice:
- Afficher le nombre d'auteur dont le nom commence par 'A'
- Afficher les auteurs avec les dates de leur premier et dernier post
- Afficher les auteurs et leur nombre de posts, la colonne contenant le nombre de posts sera nommée "NombrePosts"
- Afficher les auteurs et le nombre de jours moyen de création de posts (en nombre de jours apres la date de naissance)
Jointure avancée¶
les jointures avancées permettent de réaliser des mises en correspondance de table de manière plus élaboré que la simple clause WHERE.
- INNER JOIN retourne les enregistrements quand la condition est vraie dans toutes les tables (ie la jointure ne retourne pas les enregistrements qui ne sont pas associés)
- LEFT JOIN retourne les enregistrements quand la condition est vraie dans la table de gauche
- RIGHT JOIN retourne les enregistrements quand la condition est vraie dans la table de droite
SELF JOIN permet de réaliser une association d'une table avec elle même
SELECT <table1>.<champ1>, <table2>.<champ2>,<table2>.<champ3> FROM <schema>.<table1> INNER JOIN <schema.table2> ON table1.key = table2.key SELECT <table1>.<champ1>, <table2>.<champ2>,<table2>.<champ3> FROM <schema>.<table1> LEFT JOIN <schema.table2> ON table1.key = table2.key SELECT <table1>.<champ1>, <table2>.<champ2>,<table2>.<champ3> FROM <schema>.<table1> RIGHT JOIN <schema.table2> ON table1.key = table2.key
Exercice:
- Afficher l'ensemble des auteurs et leur posts correspondants a l'aide de INNER JOIN. Les données seront triées par les id de la table auteurs
- Afficher les Noms, Prénoms, date de naissance, date de création de posts des auteurs dont la date de naissance est supérieure à la date de création de posts à l'aide d'INNER JOIN
- Ajouter des données dans les tables auteurs et posts pour mettre en evidence l'intéret de RIGHT JOIN et LEFT JOIN
Jointure sur la même table avec alias¶
SELECT <table1>.<champ1>, <alias1>.<champ2>,<alias1>.<champ3> FROM <schema>.<table1>,<schema>.<table1> as alias1 where <condition>
Exercice:
- Afficher la liste des auteurs qui sont nés à 10 jours d'écart d'un autre auteur (les auteurs doivent apparaitre une fois à droite et une fois à gauche du tableau de résultat)
Sous requete¶
Il est possible de réaliser des sous requetes pour réaliser des filtrage de données
SELECT <table1>.<champ1>, <table1>.<champ2> FROM <schema>.<table1> where <table1>.<champ4> IN (SELECT <table2>.<champ1> FROM <schema>.<table2> WHERE <condition>)
Exercice:
- Afficher la liste des auteurs dont le titre des posts contiennent le mot "nostrum"
- Afficher les auteurs et leur premier posts
- Afficher les auteurs et leur dernier posts
- Afficher les auteurs et leur premier posts si le premier post est posterieur au 01/01/2010