GoupillWiki

Outils pour utilisateurs

Outils du site

Piste :
nsi:terminales:sql_requests

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
nsi:terminales:sql_requests [2021/08/19 14:53] – goupillwikinsi:terminales:sql_requests [2022/08/29 21:45] (Version actuelle) – goupillwiki
Ligne 7: Ligne 7:
 Ce langage est utilisé, à quelques variantes près, par tous les grands noms des SGBDR -- //systèmes gestion BDD relationnelle// -- Oracle, MySQL, MariaDB, Access, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB Ce langage est utilisé, à quelques variantes près, par tous les grands noms des SGBDR -- //systèmes gestion BDD relationnelle// -- Oracle, MySQL, MariaDB, Access, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB
  
-Ce langage est utilisé pour piloter une base de données depuis n'importe quel langage : Python, PHP...+Il s'agit d'emettre des requêtes, c'est à dire des commandes, consistant à modifier une données, en insérer une, en effacer ou bien à lire le contenu de la base de données. En général, ces requêtes sont émises automatiquement depuis un programme écrit dans n'importe quel langage : Python, PHP...
  
-Dans le cadre de ce premier cours, nous allons utiliser un outil permettant d'envoyer directement des requêtes SQL et de visualiser la base comme dans Microsoft Access, sous forme de tables. Je propose l'utilisation de [SQLiteBrowser](https://sqlitebrowser.org/dl/).+Dans le cadre de ce premier cours, nous allons utiliser un outil permettant d'envoyer manuellement des requêtes SQL et de visualiser la base comme dans Microsoft Access, sous forme de tables. Je propose l'utilisation de [[https://sqlitebrowser.org/dl/|SQLiteBrowser]].
  
 ===== Exemple ===== ===== Exemple =====
Ligne 15: Ligne 15:
 Afin d'illustrer notre travail, on utilisera un exemple. Afin d'illustrer notre travail, on utilisera un exemple.
  
-{{ :nsi:terminales:sql-1.png?direct&400 |}}+{{ nsi:terminales:database:sql-1.png?direct&400 |}}
  
 Pour cette base, nous besoin de deux tables FILM et ARTISTE et aussi d'une table JOUEDANS pour la relation du haut : Pour cette base, nous besoin de deux tables FILM et ARTISTE et aussi d'une table JOUEDANS pour la relation du haut :
Ligne 69: Ligne 69:
 </code> </code>
  
-<WRAP box>Dans //sqlitebrowser//, commencer par créer une bdd vide puis dans les onglets en haut à gauche, chercher "Exécuter le SQL". On peut entrer des requêtes SQL et les exécuter. Un message indique si la requête a abouti ou non.</WRAP>+<WRAP box>Dans //sqlitebrowser//, 
 +  * commencer par créer une bdd vide, 
 +  * chercher "Éditer les pragmas" dans les onglets, cocher "Foreign keys" -- ainsi les clés étrangères seront prises en comtpe, 
 +  * chercher "Exécuter le SQL" dans les onglets puis exécuter les requêtes SQL précédentes. Un message indique si la requête a abouti ou non. 
 +</WRAP>
  
 ==== Les domaines = types de données ==== ==== Les domaines = types de données ====
Ligne 100: Ligne 104:
 Dans le texte de la biographie, vous pouvez voir la présence de caractères ''%%'%%'', mais ces caractères servent à délimiter les chaînes de texte... La convention est de doubler les ''%%'%%'' pour qu'ils soient compris comme une simple apostrophe. C'est pourquoi vous pouvez voir des ''%%''%%'' ici et là dans la biographie. Dans le texte de la biographie, vous pouvez voir la présence de caractères ''%%'%%'', mais ces caractères servent à délimiter les chaînes de texte... La convention est de doubler les ''%%'%%'' pour qu'ils soient compris comme une simple apostrophe. C'est pourquoi vous pouvez voir des ''%%''%%'' ici et là dans la biographie.
  
-==== On ne précise pas la clé primaire ====+<WRAP tip> 
 +==== Préciser ou non la clé primaire ? ====
  
-''idArtiste'' est la clé primaire, elle se crée toute seule, nous n'avons pas besoin de la préciser.+On a le choix : on peut décider de préciser un ''idArtiste''. Dans ce cas on pourra écrire 
 + 
 +<code sql> 
 +INSERT INTO ARTISTE(idArtiste, prénom, nom, biographie, naissance) VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19'); 
 +</code> 
 + 
 +et on précise l'identifiant voulu **à condition qu'il ne soit pas déjà utilisé**. 
 + 
 +Puisque dans la requête précédente figurent toutes les colonnes de la table, on peut se contenter de : 
 + 
 +<code sql> 
 +INSERT INTO ARTISTE VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19'); 
 +</code> 
 + 
 +Dans les sujets de bac, l'identifiant est précisé en général. Mais dans un cas d'utilisation réelle, il sera sans doute plus pratique de laisser le SGBD automatiquement identifiant : sans cela il faudrait d'abord consulter la BDD pour savoir quel identifiant est disponible... 
 +</WRAP>
  
 ==== Erreur si insertion de film sans idReal ==== ==== Erreur si insertion de film sans idReal ====
Ligne 198: Ligne 218:
 </code> </code>
  
-<markdown> +===== Consulter la table =====
-## Consulter la table+
  
-Nous abons vu comment créer la base, créer des items, en supprimer, en modifier.+Nous avons vu comment créer la base, créer des items, en supprimer, en modifier.
  
 Nous allons passer maintenant aux commandes permettant d'en explorer le contenu. Nous allons passer maintenant aux commandes permettant d'en explorer le contenu.
  
-Une base est construite par des milliers d'insertions individuelles. On ne va donc pas le faire et nous allons continuer à travailler avec une base déjà faite.+<WRAP box>Une base est construite par des milliers d'insertions individuelles. On ne va donc pas le faire et nous allons continuer à travailler avec une base déjà faite.
  
-Vous disposez du fichier `films.sql` qui contient toutes les commandes pour créer une base de donnée. Dans sqlitebrowser vous pouvez importer un fichier `*.sql`.+Vous disposez du fichier {{ nsi:terminales:database:films.sql |}} qui contient toutes les commandes pour créer une base de donnée. Dans //sqlitebrowser// vous pouvez importer un fichier ''*.sql''. <wrap tip>Fichier -> Importer -> Base de données à partir du fichier SQL...</wrap> 
 +</WRAP>
  
-### Sélection+==== Sélection ====
  
-La commande `SELECT` permet d'extraire des données de la base. Au plus simple :+=== cas le plus simple ===
  
-```sql+La commande ''SELECT'' permet d'extraire des données de la base. Au plus simple : 
 + 
 +<code sql linenums>
 SELECT * FROM ARTISTE; SELECT * FROM ARTISTE;
-```+</code>
  
-`*` est un symbole couramment utilisé quand on veut dire TOUT. Donc ici on renvoie toutes les colonnes pour toute la table ARTISTE.+''%%*%%'' est un symbole couramment utilisé quand on veut dire TOUT. Donc ici on renvoie toutes les colonnes pour toute la table ARTISTE.
  
-```sql+=== projection === 
 + 
 +<code sql linenums>
 SELECT nom, prénom FROM ARTISTE; SELECT nom, prénom FROM ARTISTE;
-```+</code>
  
-Presque la même chose, sinon que l'on ne demande que la colonne nom et la colonne prénom. On parle de **projection**.+Presque la même chose, sinon que l'on ne demande que la colonne ''nom'' et la colonne ''prénom''. On parle de **projection**.
  
-```sql+=== clause WHERE === 
 + 
 +<code sql linenums>
 SELECT nom, prénom FROM ARTISTE WHERE naissance > '1970-01-01'; SELECT nom, prénom FROM ARTISTE WHERE naissance > '1970-01-01';
-```+</code>
  
 La clause WHERE permet de limiter le retour aux seuls artistes dont la naissance vient après le 01/01/1970. La clause WHERE permet de limiter le retour aux seuls artistes dont la naissance vient après le 01/01/1970.
  
-```sql+=== clause LIKE === 
 + 
 +<code sql linenums>
 SELECT nom, prénom FROM ARTISTE WHERE nom LIKE 'Bo%'; SELECT nom, prénom FROM ARTISTE WHERE nom LIKE 'Bo%';
-```+</code>
  
-LIKE signifie RESSEMBLE, ici on ne garde que les artistes dont le nom commence par 'Bo'.+''LIKE'' signifie //RESSEMBLE//, ici on ne garde que les artistes dont le nom commence par "Bo".
  
-On peut éviter d'afficher des doublons. Ci-dessous la première commande fait apparaître des rôle identiques (*Belatrix Lestrange par exemple, même rôle dans 2 films différents*), la deuxième commande supprime les doublons.+=== doublons, clause DISTINCT ===
  
-```sql+On peut éviter d'afficher des doublons. Ci-dessous la première commande fait apparaître des rôle identiques -- //Belatrix Lestrange// par exemple, même rôle dans 2 films différents -- la deuxième commande supprime les doublons. 
 + 
 +<code sql linenums>
 SELECT rôle FROM JOUEDANS; SELECT rôle FROM JOUEDANS;
 SELECT DISTINCT rôle FROM JOUEDANS; SELECT DISTINCT rôle FROM JOUEDANS;
-```+</code>
  
-Et on peut trier - *tri par naissance puis nom dans le premier cas, ordre descendant dans le second cas*.+=== tri ===
  
-```sql+Et on peut trier : 
 +  * tri par naissance puis nom dans le premier cas, 
 +  * ordre descendant dans le second cas -- mot clé ''DESC''. 
 + 
 +<code sql linenums>
 SELECT nom, prénom FROM ARTISTE ORDER BY naissance, nom; SELECT nom, prénom FROM ARTISTE ORDER BY naissance, nom;
 SELECT nom, prénom FROM ARTISTE ORDER BY naissance DESC; SELECT nom, prénom FROM ARTISTE ORDER BY naissance DESC;
-```+</code> 
 + 
 +<wrap important>Dans le cas du requête avec ''WHERE'' et ''ORDER BY'', il faut placer ''ORDER BY'' après.</wrap> 
 + 
 +=== compte === 
 + 
 +On peut compter les items. Par exemple la requête suivante renvoie une liste de noms avec éventuellement des doublons : 
 + 
 +<code sql> 
 +SELECT nom from ARTISTE; 
 +</code> 
 + 
 +Si nous n'étions intéressé que par le nombre de lignes de cette liste -- ce qui revient à compter le nombre d'artistes -- on peut utiliser le mot clef ''COUNT'' : 
 + 
 +<code sql> 
 +SELECT COUNT(nom) from ARTISTE; 
 +</code> 
 + 
 +Cette requête est différente puisqu'elle ne renvoie pas une liste de valeurs, elle ne renvoie que le résultat du compte. 
 + 
 +<WRAP tip>On compte tous les noms apparaissant, même s'ils viennent en plusieurs exemplaires. La fonction ''COUNT'' ignorera les cas ''NULL'', c'est à dire les cases vides (en admettant que l'on ait autorisé qu'un artiste n'ait pas de nom renseigné).</WRAP> 
 + 
 +<WRAP info>Pour aller plus loin, on pourrait préciser que l'on ne veut pas compter plusieurs fois un même nom. On ajoute alors le mot-clé ''DISCTINCT'' comme ceci : ''SELECT COUNT(DISTINCT nom) from ARTISTE''.
  
-### Jointure+Plus loin encore : on pourrait produire une liste avec chaque nom associé avec le nombre d'artiste portant ce nom : ''SELECT nom, COUNT(idArtiste) FROM ARTISTE GROUP BY nom''.  Le mot-clé ''GROUP BY'' indique que l'on veut regrouper tous les artistes de même nom et la fonction ''COUNT'' se charge de compter les différents artistes groupés sous ce nom.</WRAP> 
 +==== Jointure ====
  
 Si je veux afficher les films Si je veux afficher les films
  
-```sql+<code sql linenums>
 SELECT * FROM FILM; SELECT * FROM FILM;
-```+</code>
  
 On n'est pas très satisfait car on ne voit pas le nom du réalisateur, seulement son id. On n'est pas très satisfait car on ne voit pas le nom du réalisateur, seulement son id.
Ligne 265: Ligne 322:
 On réalise pour cela une **jointure**. On réalise pour cela une **jointure**.
  
-```sql+<code sql linenums>
 SELECT * FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste; SELECT * FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste;
-```+</code>
  
 Et voilà ! Et voilà !
Ligne 273: Ligne 330:
 On peut bien sûr limiter les colonnes, filtrer avec WHERE, trier, tout cela en même temps : On peut bien sûr limiter les colonnes, filtrer avec WHERE, trier, tout cela en même temps :
  
-```sql+<code sql linenums>
 SELECT titreVO, année, nom, prénom FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste WHERE année > 2000 ORDER BY naissance; SELECT titreVO, année, nom, prénom FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste WHERE année > 2000 ORDER BY naissance;
-```+</code>
  
-*Remarque : ORDER BY doit être après WHERE*+<wrap important>ORDER BY doit être après WHERE</wrap>
  
-## Des requêtes de séléction pour s'exercer +<WRAP tip>**Autre façon de faire :** Si on écrit 
- +<code sql linenums> 
-On utilise la base précédente. +SELECT * FROM FILM, ARTISTE; 
- +</code> 
-### Applications directes, sans jointure +On obtient tous les couples ''(FILM, ARTISTE)'' même s'ils n'ont rien à voir l'un avec l'autre. Si on ne veut que les paires où l'artiste est le réalisateur du film, on peut préciser : 
- +<code sql linenums> 
-1. Lister tous les noms, prénoms, naissances d'artistes nés avant 1970 +SELECT * FROM FILM, ARTISTE WHERE FILM.idReal = ARTISTE.idArtiste; 
-2. Lister tous les titreVO de films dont le réalisateur à l'idArtiste 15 +</code> 
-3. Lister tous les rôles joués par l'artiste d'idArtiste 1 +Ce qui revient à peu près au même que la  jointure.
- +
-### Avec une jointure +
- +
-4. Lister tous les titreVO, année des films de Tarantino +
-5. Lister tous les rôles de Brad Pitt +
-6. Lister tous les titreVO, année des films dans lequel a joué l'artiste avec l'idArtiste 1 +
-7. Lister les noms et prénoms de tous les réalisateurs (*c'est à dire des artistes qui sont réalisateurd d'un film*) +
- +
-### Avec deux jointures +
- +
-Pour lier un artiste à un film en tant qu'acteur, il faut passer par JOUEDANS ce qui suppose deux jointures sous la forme +
- +
-```sql +
-... FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-``` +
- +
-8. Trouver tous les titreVO, année des films dans lesquels joue un artiste nommé Willis +
- +
-9. Trouver tous les titreVO, année des films dont un acteur est né après 1970 +
- +
-*Remarque : La requête 8. correspond à la requête 6. mais nécessite une jointure supplémentaire. En situation réelle, on sera plutôt dans la situation du 6. car on connaîtra l'id de Bruce Willis* +
- +
-### Avec trois jointures... +
- +
-*Ça commence à devenir plus compliqué et on ne vous demande pas d'aller si loin.* +
- +
-Si pour une paire acteur - film, on veut en plus avoir le réalisateur du film, il faut en plus joindre FILM à ARTISTE selon la clé idRéal. Cette fois c'est plus difficile car ceci ne fonctionne pas : +
- +
-```sql +
-... FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE ON ARTISTE.idArtiste = FILM.idReal +
-``` +
- +
-Car ARTISTE apparaît deux fois pour des raisons différentes. Il est possible de se sortir d'embarras en donnant un alias à chaque occurence de ARTISTE. Par exemple on utilisera `acteur` pour la première apparition de ARTISTE et `réalisateur` pour la deuxième :+
  
-```sql +//Dans tous les cas, inutile de vous remplir la tête de code SQL. On souhaite surtout que vous sachiez écrire des requêtes simples et que vous arriviez à comprendre. La requête précédente, même si elle n'est pas facile de la trouver tout seul, ne devrait pas être trop difficile à comprendre.// 
-... FROM JOUEDANS +</WRAP>
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-``` +
- +
-L'ajout de `acteur` et `réalisateur` juste à côté de ARTISTE suffit. +
- +
-Supposons que l'on veuille visualiser le nom d'acteur et le nom du réalisateur, le titreVO. On ne peut pas se contenter de +
- +
-```sql +
-SELECT nom, nom, titreVO FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-``` +
- +
-car les deux occurences de nom sont ambigues : est-ce nom pour la première occurence de ARTISTE (acteur) ou la deuxième (réalisateur) ? Il faudra préciser : +
- +
-```sql +
-SELECT acteur.nom, réalisateur.nom, titreVO FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-``` +
- +
-10. Trouver tous les noms et prénoms, rôle et titreVO pour des acteurs jouant dans un film de Tarantino +
- +
-11. Trouver tous les noms, prénoms de réalisateurs d'un film dont dont un des acteurs est né après 1970 +
- +
-## Les réponses +
- +
-1. Lister tous les noms, prénoms, naissances d'artistes nés avant 1970 +
-   +
-   ```sql +
-   SELECT nom, prénom, naissance FROM ARTISTE WHERE naissance < '1970-01-01'; +
-   ``` +
- +
-2. Lister tous les films dont le réalisateur à l'idArtiste 15 +
- +
-   ```sql +
-   SELECT titreVO FROM FILM WHERE idReal = 15; +
-   ``` +
- +
-3. Lister tous les rôles joués par l'artiste d'idArtiste 1 +
- +
-   ```sql +
-   SELECT rôle FROM JOUEDANS WHERE idArtiste = 1; +
-   ``` +
- +
-4. Lister tous les titreVO, année des films de Tarantino +
- +
-   ```sqlite +
-   SELECT titreVO, année FROM ARTISTE +
-     JOIN FILM ON FILM.idReal = ARTISTE.idArtiste +
-     WHERE nom = 'Tarantino'; +
-   ``` +
- +
-5. Lister tous les rôles de Brad Pitt +
- +
-   ```sqlite +
-   SELECT rôle, année FROM JOUEDANS +
-     JOIN ARTISTE ON JOUEDANS.idArtiste = ARTISTE.idArtiste +
-     WHERE nom = 'Pitt' AND prénom = 'Brad'; +
-   ``` +
- +
-6. Lister tous les titreVO, année des films dans lequel a joué l'artiste avec l'idArtiste 1 +
- +
-   ```sqlite +
-   SELECT titreVO, année FROM FILM +
-     JOIN JOUEDANS ON FILM.idFilm = JOUEDANS.idFilm +
-     WHERE idArtiste = 1; +
-   ``` +
- +
-7. Lister les noms et prénoms de tous les réalisateurs (*c'est à dire des artistes qui sont réalisateurd d'un film*) +
- +
-   ```sqlite +
-   SELECT DISTINCT nom, prénom FROM ARTISTE +
-     JOIN FILM ON FILM.idReal = ARTISTE.idArtiste; +
-   ``` +
-    +
-   *Seuls apparaîtront les artistes pour lesquels la jointure est possible* +
- +
-8. Trouver tous les titreVO, année des films dans lesquels joue un artiste nommé Willis +
- +
-   ```sql +
-   SELECT titreVO, année FROM JOUEDANS +
-     JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-     JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-     WHERE nom = 'Willis' AND prénom = 'Bruce'; +
-   ``` +
- +
-9. Trouver tous les titreVO, année des films dont un acteur est né après 1970  +
- +
-   ```sql +
-   SELECT titreVO, année FROM JOUEDANS +
-     JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-     JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-     WHERE naissance > '1970-01-01'; +
-   ``` +
- +
-10. Trouver tous les noms et prénoms, rôle et titreVO pour des acteurs jouant dans un film de Tarantino +
- +
-    ```sql +
-    SELECT acteur.nom, acteur.prénom, rôle, titreVO FROM JOUEDANS +
-      JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-      JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-      JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-      WHERE réalisateur.nom = 'Tarantino'; +
-    ```+
  
-11. Trouver tous les noms, prénoms de réalisateurs d'un film dont dont un des acteurs est né après 1970 
  
-    ```sql 
-    SELECT DISTINCT réalisateur.nom, réalisateur.prénom FROM JOUEDANS 
-      JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm 
-      JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste 
-      JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal 
-      WHERE acteur.naissance > '1970-01-01'; 
-    ``` 
-</markdown> 
nsi/terminales/sql_requests.1629377633.txt.gz · Dernière modification : de goupillwiki