Les cours du BTS SIO

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Activité : les Bases de données relationnelles ======
===== Présentation =====
Quand nous avons une** grande quantité d'informations numériques** à gérer, il est nécessaire de les **organiser** et d'utiliser un **logiciel spécialisé,** le **S**ystèmes de **G**estion de **B**ases de **D**onnées pour les utiliser. 

Actuellement la grande majorité des SGBD utilise le** modèle relationnel** qui consiste : 
  * à mettre ensemble des **données de même nature** en les rangeant dans des **tables** de données ;
  * à définir des **relations** pour relier les données **entre elles**.
  * à utiliser le **langage SQL** ((Structured Query Language) pour **interroger, modifier ou supprimer** des données.

<WRAP center round info>
 Une petite histoire des bases de données : 
  * https://pixees.fr/lhistoire-des-base-de-donnees-ou-presque/
</WRAP>

Dans cette activité je vais **interroger** les données sur les villes du site http://sql.sh en utilisant le langage de requêtes SQL : 
  * le **contenu** du fichier {{:icn:villes_point_virgule.csv|}} a été mis dans une **table** appelée **icn_ville** dans une base de données MySQL :
    * à partir de site Web  http://icn.boonum.fr, je vais pouvoir exécuter des requêtes SQL pour faire des recherches dans les données de la table **icn_ville**. 

<WRAP center round info>
Il existe plusieurs logiciels de gestion de base de données. **MySQL** (racheté par la société Oracle), continue à être une **base de données OpenSource** sous le nom de projet **MariaDB**. 
</WRAP>

===== Les requêtes SQL =====
==== La projection ====
La projection consiste à choisir les informations, en utilisant la clause **select** suivie les champs à afficher  : 
  * Exemple :
<code sql>
select *
from icn_ville ;
</code>
{{:icn:bd_sql_01.png|}}
<WRAP center round info>
Après la clause **select**, j'indique :
  * les **colonnes** à afficher **séparées par des virgules** ,
  * ou bien je mets le caractère ***** pour visualiser le contenu de **toutes les colonnes**.
Puis j'indique avec la clause **from** la table dans laquelle se trouve les données.
</WRAP>
<WRAP center round todo>
**Question 1 :** Ecrire la requête SQL pour avoir le **nom**, le **code postal** et le **numéro de département** (dans cet ordre) des villes.

Résultat à obtenir : 
^nom	^cp	^dep^
|Ozan	|1190	|1|
|Cormoranche-sur-Saône	|1290|1|
...
</WRAP>

  * je peux renommer une colonne en définissant un alias avec le mot clé **as** et aussi faire des calculs ou utiliser des fonctions :
<code sql>
select concat(nom, ' ', cp) as adresse
from icn_ville ;
</code>
**Résultat :** 
^adresse^
|nom cp|
|Ozan 1190|
|Cormoranche-sur-Saône 1290|

<WRAP center round todo>
**Question 2 :** Ecrire la requête SQL qui indique pour chaque ville **l'augmentation** de la population entre 2010 et 2012.
**Résultat à obtenir :** 
^ville	^augmentation^
|Ozan	|-118|
|Cormoranche-sur-Saône	|-58|
|Plagne	|-29|
|Tossiat	|-6|
|Pouillat	|12|
...
</WRAP>

==== Eviter des résultats en double ====
Des requêtes peuvent renvoyer des **résultats identiques** et il est parfois utile d'évier cela avec le mot clé **distinct**.
  * Exemple connaître la liste des départements sans utiliser **distinct** : 
<code sql>
select dep as Département 
from icn_ville ;
</code>
**Résultat :** 
^Département^
|1|
|1|
|1|
...
soit **36 701 lignes**.
  * Connaître la liste des départements en utilisant **distinct** qui doit être placé une **seule fois** juste après le mot clé **select** : 
<code sql>
select distinct dep as Département 
from icn_ville ;
</code>
**Résultat :** 
^Département^
|1|
|2|
|3|
...
soit **103 lignes**.

<WRAP center round todo>
**Question 3 :** Ecrire la requête SQL qui donne la liste des codes postaux. La requête SQL doit renvoyer uniquement **6 083 lignes**.
</WRAP>

==== Trier les résultats obtenus ====
Les requêtes SQL renvoient en général les données dans **l'ordre** où elles sont disponibles dans la base de données. Pour obtenir un ordre de** tri différent** on utilise les mots clés **order by** suivi des colonnes à trier en ascendant, par défaut (**asc**) ou en descendant (**desc**). 
  * Exemple connaître la liste villes par ordre alphabétique : 
<code sql>
select nom as Ville
from icn_ville
order by nom asc ;
</code>
**Résultat :** 
^Ville^
|Aast|
|Abainville|
|Abancourt|
...
soit **36 701 lignes**.

<WRAP center round todo>
**Question 4 :** Ecrire la requête SQL qui donne la liste des villes **selon le nom d'habitants** par ordre **décroissant** (indiquer la ville la plus peuplée premier)
</WRAP>

==== La sélection ====
J'utilise la **sélection** si je ne souhaite avoir des données qui réponde à une **condition** en utilisant la clause **where** :  
  * Exemple : avoir toutes les information de la ville de Panazol
<code sql>
select * 
from icn_ville
where nom = 'panazol';
</code>
**Résultat :** 
1 ville(s) trouvée(s) !
^dep	^nom	^cp	^nb_hab_2010	^nb_hab_1999	^nb_hab_2012	^dens	^surf	^longitude	^latitude	^alt_min	^alt_max^
|87	|Panazol	|87350	|10392	|9727	|10100	|518	|20	|1.3	|45.8333	|215	|351|

Voici les opérateurs utilisables : 
=== Les opérateurs de comparaison et logiques ===
^Opérateur de comparaison^Description^Opérateurs logiques^Description^
^=|égal à^and|les deux conditions doivent être vérifiées simultanément|
^<|inférieur à^or|au moins une des deux conditions doit être vérifiée|
^>|supérieur à|
^<=|inférieur ou égal|
^>=|supérieur ou égal|
^<>|différent de|	

<WRAP center round todo>
**Question 5 :** Ecrire la requête SQL qui donne la liste des villes qui ont **plus de 5000 habitants** en **2012**. La requête SQL doit renvoyer uniquement **2 007 lignes**.
</WRAP>

=== Les opérateur d’appartenance (ou non) d’un élément à un intervalle ===
^BETWEEN^appartenance|
^NOT BETWEEN^non appartenance|

* Exemple : connaître les villes dont l'altitude maximale est comprise entre 200 et 300 m :
<code sql>
select nom as ville, alt_max  
from icn_ville
where alt_max  between 200 and 300;
</code>
**Résultat :** 
^ville	^alt_max^
|Ozan	|205|
|Cormoranche-sur-Saône	|211|
|Replonges	|207|
...	

<WRAP center round todo>
**Question 6 :** Ecrire la requête SQL qui donne la liste des villes dont la superficie est comprise entre 100 et 300 kilomètres carré. La requête SQL doit renvoyer uniquement **144 lignes**.
</WRAP>

=== Les opérateur de comparaison de chaînes de caracatères ===
^LIKE^comparaison de chaînes (identiques)|
^NOT LIKE^chaîne différente|

%  permet de remplacer n caractères
_   permet de remplacer 1 caractère

* Exemple : connaître les villes dont le nom commence par Limoges :
<code sql>
select nom as ville
from icn_ville
where nom like 'limoges%';
</code>
**Résultat :** 

^ville^
|Limoges-Fourches|
|Limoges|

<WRAP center round todo>
**Question 7 :** Ecrire la requête SQL qui donne la liste des villes dont le nom contient les caractères **paris**. La requête SQL doit renvoyer uniquement **10 lignes**.
</WRAP>
==== Les activités ... ====
<WRAP center round info >
[[icn:2nde:accueil|Je reviens à la liste des activités.]] 
</WRAP>