\chapter{Bases de données}

\enlargethispage*{\baselineskip}
\begin{exointro}{Lycée Jean Macé, Niort}
Une base de données a la structure relationnelle suivante:

\Par

{\ttfamily

personnes (id\_prenom,prenom)

genres (id\_genre,designation)

livres (id\_livre,titre,auteur,id\_genre,id\_prenom)
}

\begin{itemize}
\item La table \og personnes \fg{} désigne l'ensemble des membres de la famille Numos;

\item La table \og genres \fg{} représente l'ensemble des genres des livres que possède la famille (policier, science-fiction, etc.);

\item La table \og livres \fg{} désigne l'ensemble des livres de la famille Numos.\linebreak L'attribut \og id\_prenom \fg{} sera vide si le livre n'est pas en la possession d'un membre de la famille.
\end{itemize}

Les attributs des tables sont explicites.

\begin{questions}
\item Pour chaque table, quel attribut peut être une clé primaire ?
\item Pour la table \og livres \fg, existe-t-il un attribut qui peut être défini comme clé étrangère ?

\item La famille Numos est composée de Mathilde, François, Nathan,\linebreak Ludovic et Jeanne.

\'Ecrire une requête SQL permettant d'insérer une de ces informations dans la table \og personnes \fg.

\item On suppose qu'un membre de la famille peut être en possession de\linebreak plusieurs livres en même temps.

\'Ecrire une requête SQL permettant de trouver tous les livres en\linebreak possession de Mathilde.

\item \'Ecrire une requête SQL permettant de calculer le nombre total de livres indisponibles.
\end{questions}
\end{exointro}

\newpage

\begin{problematique}
Comment stocker, manipuler et partager un volume d'informations toujours plus important ?
\end{problematique}

\section{Systèmes de Gestion de Bases de Données (SGBD)}

\subsection{Introduction}

\index{Base de données!SGBD}
\begin{defin}
Un Système de Gestion de Bases de Données (SGBD) est un outil informatique permettant la sauvegarde, l'interrogation, la recherche et la mise en forme de données.
\end{defin}

Un SGBD est donc un ensemble de logiciels systèmes qui permettent aux programmateurs d'insérer, de modifier et de rechercher des données spécifiques dans une grande masse d'informations partagées par plusieurs utilisateurs.

Ces informations sont généralement enregistrées sur des disques magnétiques (par exemple, des serveurs).

Un SGBD donne l'impression à chaque utilisateur qu'il est le seul à travailler avec les données.

Oracle Database, 4D, Microsoft SQL Server, SQLite ou MySQL sont des exemples de SGBD très répandus. SQLite et MySQL sont des logiciels libres (open source), et sont par conséquent très utilisés, aussi bien par le grand public que par les professionnels.

\subsection{Fonctions d'un SGBD}

En plus des fonctions primaires citées dans la définition précédente, un SGBD:
\begin{itemize}
\item assure le partage des données;
\item vérifie qu'une opération s'effectue entièrement ou pas du tout : c'est ce que l'on nomme l'\textit{atomicité};
\item protège les données contre tout incident (détérioration, suppression accidentelle, etc.);
\item optimise les performances (temps de recherche minimisé par exemple).
\end{itemize}

Les SGBD permettent la description des données (définition des types par des noms, formats, ...) de manière séparée de leur utilisation (mise à jour et recherche). Ils permettent aussi de retrouver les caractéristiques d'un type de données à partir de son nom (par exemple, comment est décrit un produit).

\newpage

\subsection{Les différents modèles de SGBD}

Il existe cinq modèles de SGBD, mais seul le modèle relationnel est au programme de NSI.

\begin{itemize}
\item \textit{le modèle hiérarchique}:  les données sont classées hiérarchiquement, selon une arborescence descendante (arbre). Ce modèle utilise des pointeurs entre les\linebreak différents enregistrements. Il s'agit du premier modèle de SGBD.
\Par
\begin{figure}
\begin{center}
\includegraphics{221_06-sgbd-hierarchique}
\figcaption{Modèle hiérarchique d'un SGBD}
\end{center}
\end{figure}

\Par

\item \textit{le modèle réseau} : ce modèle ressemble au modèle hiérarchique à ceci près qu'il n'est plus nécessairement descendant.
\Par
\begin{figure}
\begin{center}
\includegraphics{221_06-sgbd-reseau}
\figcaption{Modèle réseau d'un SGBD}
\end{center}
\end{figure}

\Par\enlargethispage*{3em}

\item \textit{le modèle relationnel (SGBDR)} : les données sont enregistrées dans des tableaux à deux dimensions (lignes et colonnes);

\item \textit{le modèle déductif} : similaire au modèle relationnel, mais la manipulation des tables se fait différemment (nous ne donnerons pas de détails sur les calculs utilisés car hors programme);

\item \textit{le modèle objet (SGBDO)}: les données sont stockées sous forme de classes.
\end{itemize}


Depuis la fin des années 1990, le modèle relationnel est le plus répandu (environ trois quarts des bases de données utilisent ce modèle). Dans la suite de cet ouvrage, nous ne nous intéresserons qu'à ce modèle.


\section{Le modèle relationnel}

\subsection{Historique}

Le modèle relationnel a été imaginé dans les années 1970 par l'informaticien\linebreak britannique Edgar Frank Codd. Avant cela, les modèles de bases de données ne permettaient pas de décrire de façon satisfaisante les relations entre deux données à l'aide de pointeurs logiques. Dix années de recherches ont été nécessaires avant de publier l'article \og A Relational Model of Data for Large Shared Data Banks \fg (\textit{un modèle de données relationnel pour de grandes banques de données\linebreak partagées}), CACM 13, No. 6, June 1970.

\subsection{Représentations}

Comme nous l'avons dit dans la section précédente, le modèle relationnel consiste à représenter les données dans des tableaux, que l'on appelle \textit{tables}. Chaque table est une sorte de classe, qui admet donc des attributs.

\Par

Prenons l'exemple d'un site internet, qui demande à ses utilisateurs trois données afin de pouvoir leur créer un compte : nom, prénom et email. On peut alors imaginer une classe \textit{user}, représentant un utilisateur ou une utilisatrice, classe comportant les trois attributs : surname, name et email. Cette classe est alors représentée par la table :

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{user}\\
\tabulartitle\hline
surname & name & email\\
\hline
\end{tabular}
\tabcaption{Structure de la table représentant les utilisateurs}
\end{center}
\end{figure}

\Par

Une fois la structure de la table connue, nous pouvons y insérer les données, par exemple:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{user}\\
\tabulartitle\hline
surname & name & email\\
\hline
Dupont 	& Jean & jean.dupont@free.fr\\
\hline
Durand & Anne & anne.dur@orange.fr\\
\hline
$\vdots$ & $\vdots$ & $\vdots$\\
\hline
\end{tabular}
\tabcaption{Insertion de données dans la table représentant les utilisateurs}
\label{table-1}
\end{center}
\end{figure}

\subsection{Définitions}

\begin{defins}

\begin{itemize}
\item Le \textit{schéma relationnel} d’une table SQL décrit sa structure ; il spécifie le nom de la table ainsi que les différentes catégories d’informations qu’elle va contenir (les colonnes).

\item Dans une table, chaque ligne constitue un \textit{enregistrement}.
\index{Base de données!Enregistrement}

\item Les \textit{attributs} d'une table sont les noms de ses colonnes.
\index{Base de données!Attributs}

\item Le \textit{degré} d'une table est le nombre de ses attributs.
\index{Base de données!Degré d'une table}

\item Le \textit{domaine} d'un attribut est son type : entier, flottant, chaîne de caractères, booléen, ...
\index{Base de données!Domaine d'un attribut}

\item La \textit{clé primaire} d'une table est l'attribut qui permet d'identifier de manière unique (sans aucun risque de doublon) un enregistrement de cette table.
\index{Base de données!Clé primaire}

\item Une \textit{clé étrangère} est un attribut d'une table qui fait référence à la clé primaire d'une autre table. Une clé étrangère permet de mettre en relation un enregistrement d'une table (appelée \textit{table fille}) qui la contient avec un enregistrement de la table référencée (appelée \textit{table parent}).
\index{Base de données!Clé étrangère}
\end{itemize}

\end{defins}

\begin{Exemple}
reprenons la table \ref{table-1} précédente et ajoutons-y une clé primaire \textit{id}:
\Par
\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zc|}}
\tabulartitle\hline
\multicolumn{4}{|Zc|}{user}\\
\tabulartitle\hline
id & surname & name & email\\
\hline
1 & Dupont 	& Jean & jean.dupont@free.fr\\
\hline
2 & Durand & Anne & anne.dur@orange.fr\\
\hline
$\vdots$ & $\vdots$ & $\vdots$ & $\vdots$\\
\hline
\end{tabular}
\tabcaption{Table avec clé primaire}
\end{center}
\end{figure}
\Par
Les attributs de cette table sont donc : \textit{id}, \textit{surname}, \textit{name} et \textit{email}.

Le degré de la table est donc égal à 4 car il y a quatre attributs.

Le domaine de l'attribut \textit{id} est : entier, et celui des autres attributs est : chaîne de caractères.

\Par

\begin{minipage}{65mm}
Imaginons maintenant que ce site internet propose plusieurs abonnements : \og Free \fg, \og Basic \fg{} et \og Premium \fg.

On peut alors créer une seconde table nommée \og abonnement \fg{}  représentant les abonnements (ci-contre).
\end{minipage}
\begin{minipage}{\linewidth-65mm}
\begin{figure}
\begin{center}
\begin{tabular}{|*2{Zc|}}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{abonnement}\\
\tabulartitle\hline
id\_abo & type \\
\hline
1 & Free \\
\hline
2 & Basic \\
\hline
3 & Premium\\
\hline
\end{tabular}
\tabcaption{Table des abonnements}
\end{center}
\end{figure}
\end{minipage}

\Break

On peut alors mettre en relation les tables \og user \fg{} et \og abonnement \fg{} en insérant une clé étrangère dans la première table:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*4{Zc|}}
\tabulartitle\hline
\multicolumn{5}{|Zc|}{user}\\
\tabulartitle\hline
id & surname & name & email & id\_abo\\
\hline
1 & Dupont 	& Jean & jean.dupont@free.fr & 3\\
\hline
2 & Durand & Anne & anne.dur@orange.fr & 1\\
\hline
$\vdots$ & $\vdots$ & $\vdots$ & $\vdots$ & \\
\hline
\end{tabular}
\tabcaption{Table des utilisateurs avec clé étrangère}
\end{center}
\end{figure}
\Par
On met ici en relation les deux tables en considérant que l'attribut \textit{id}\_\textit{abo} de la table \og user \fg{} correspond à l'attribut \textit{id}\_\textit{abo} de la table \og abonnement \fg.

Ainsi, si l'on regarde le premier enregistrement de la table \og user \fg, on peut voir que Jean Dupont a souscrit à un abonnement Premium, c'est-à-dire à l'enregistrement de la table \og abonnement \fg{}  dont l'attribut \textit{id}\_\textit{abo} est 3.
\end{Exemple}

\subsection{Schématisation}

Pour schématiser la relation qu'il peut y avoir entre deux tables, on peut utiliser la représentation suivante (en reprenant les deux tables de l'exemple précédent):

\Par

\begin{figure}
\begin{center}
\includegraphics{224_6-relation}
\figcaption{Schématisation de la relation entre deux tables}
\end{center}
\end{figure}

\Par

La relation entre les deux tables est indiquée par un trait qui les lie.

Dans chacune des tables, on peut indiquer la présence d'une clé primaire en la soulignant, et la présence d'une clé étrangère en précédant l'attribut d'un dièse (\#). 

On peut aussi, pour plus de clarté, séparer les clés des autres attributs.

\begin{defin}
Une \textit{base de données} est un ensemble de tables, dont certaines peuvent être mises en relation.
\end{defin}

\newpage

\section{Jointures}

\subsection{Produit cartésien}

\begin{defin}
On considère deux tables $T_1$ et $T_2$ d'une base de données.

Le \textit{produit cartésien} $T_1 \times T_2$ est l'ensemble:
\[
T_1 \times T_2 = \big\{ (e_1,e_2)~\vert~ e_1 \in T_1 \text{ et } e_2 \in T_2 \big\}.
\]
\end{defin}

\begin{Exemple}
si l'on considère les deux tables suivantes,

\Par

\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\begin{tabular}{|L|Zl|}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{$T_1$}\\
\tabulartitle\hline
id & surname \\
\hline
1 & Dupont \\
\hline
2 & Durand \\
\hline
\end{tabular}
\tabcaption{Table 1}
\end{center}
\end{figure}
\end{minipage}
\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\begin{tabular}{|L|Zl|}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{$T_2$}\\
\tabulartitle\hline
stockage & price \\
\hline
201 & 15 \\
\hline
41 & 7 \\
\hline
\end{tabular}
\tabcaption{Table 2}
\end{center}
\end{figure}
\end{minipage}

\Par\Par

le produit cartésien de ces deux tables est:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|L|*3{Zl|}}
\tabulartitle\hline
\multicolumn{4}{|Zc|}{$T_1 \times T_2$}\\
\tabulartitle\hline
id & T1.surname & T2.stockage & T2.price \\
\hline
1 & Dupont & 201 & 15 \\
\hline
1 & Dupont & 41 & 7\\
\hline
2 & Durand & 201 & 15\\
\hline
2 & Durand & 41 & 7 \\
\hline
\end{tabular}
\tabcaption{Produit cartésien}
\end{center}
\end{figure}
\end{Exemple}


\subsection{Jointure}
\index{Base de données!Jointure}

Une jointure permet de créer une liaison entre deux ou plusieurs tables pour pouvoir
récupérer un résultat bien précis. Le résultat est en quelque sorte une fusion
virtuelle de plusieurs tables.

\begin{defin}[jointure]
Soient $T_1$ et $T_2$ deux tables. La \textit{jointure interne} de ces deux tables sur un attribut donné $A$ est une table formée en ne prenant que les enregistrements de $T_1 \times T_2$ tels que $T_1.A = T_2.A$.
\end{defin}


On peut représenter cette jointure par le schéma suivant:

\Par

\begin{figure}
\begin{center}
\includegraphics{226_6-crs-jointure-interne}
\figcaption{Schématisation d'une jointure interne}
\end{center}
\end{figure}

\begin{Exemple}
en considérant les deux tables suivantes,

\Par

\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|C|*3{Zc|}}
\tabulartitle\hline
\multicolumn{4}{|Zc|}{user}\\
\tabulartitle\hline
id & id\_town & surname & name\\
\hline
1 & 1 & Dupont & Jean\\
\hline
2 & 2 & Durand & Agathe\\
\hline
3 & 1 & Dufon & Jérôme\\
\hline
\end{tabular}
\tabcaption{Table $T_1$}
\label{jointure-table1}
\end{center}
\end{figure}
\end{minipage}
\begin{minipage}{0.5\linewidth}
\begin{figure}
\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|C|Zc|}
\tabulartitle\hline
\multicolumn{2}{|Zc|}{town}\\
\tabulartitle\hline
id\_town & name\_town \\
\hline
1 & Bordeaux \\
\hline
3 & Paris \\
\hline
\end{tabular}
\tabcaption{Table $T_2$}
\label{jointure-table2}
\end{center}
\end{figure}
\end{minipage}

\Par\Par

leur jointure sur l'attribut \og id\_town \fg{} est:

\Par

\begin{figure}
\begin{center}
\renewcommand{\arraystretch}{1.5}
\begin{tabular}{|L|*3{Zc|}|*2{Zc|}}
\tabulartitle\hline
id & id\_town & surname & name & id\_town & name\_town\\
\hline
1 & 1 & Dupont & Jean & 1 & Bordeaux\\
\hline
3 & 1 & Dufon & Jérôme & 1 & Bordeaux\\
\hline
\end{tabular}
\tabcaption{Jointure interne de $T_1$ et $T_2$}
\end{center}
\end{figure}
\end{Exemple}


\section{Le langage SQL}




\subsection{Introduction}

Développé par IBM dans les années 1970, le langage SQL (pour \og Structured Query Language \fg, ou \og langage de requête structurée \fg{} en français) est rapidement devenu le standard des langages de bases de données relationnelles. Il permet de manipuler la structure d'une base de données et d'y rechercher, insérer, modifier ou supprimer les données à l'aide d'une vingtaine d'instructions dont la syntaxe ressemble à celle de phrases ordinaires en anglais. SQL peut s'utiliser de manière interactive, ou à l'intérieur d'un langage hôte tel que C, Fortran, ou Cobol, par exemple.


\subsection{Environnements nécessaire à l'utilisation de SQL}

\subsubsection{Généralités}

Pour travailler sur des bases de données avec SQL, plusieurs outils sont nécessaires :

\begin{itemize}
\item un serveur pour héberger la base,
\item un SGBD pour la manipuler.
\end{itemize}

Si l'on souhaite utiliser SQL de manière interactive, il nous faudra un logiciel d'édition ou une interface graphique pour entrer manuellement les requêtes.

Au Lycée, vous aurez principalement affaire à SQLite et MyPHP, dont nous allons rapidement étudier les environnements.


\subsubsection{Environnement SQLite}
\index{SQLite}
\index{Base de données!SQLite}

SQLite est une solution légère et gratuite, ce qui en fait la plus utilisée au monde. Quelque soit votre système d'exploitation, vous pouvez télécharger les outils nécessaires sur :
\Par
\begin{center}
%\nathanlive{web}{
https://www.sqlite.org/download.html
\end{center}
\Par
Par exemple, sous Windows 10, vous pouvez télécharger le fichier :
\Par
\begin{center}
\texttt{sqlite-tools-win-x64-3490100.zip}
\end{center}
\Par
qui contient trois exécutables : \texttt{sqlite3}, \texttt{sqldiff} et \texttt{sqlite3}\_\texttt{analyser}.

Lorsque le programme principal sqlite3 est exécuté, une console permet de lancer les requêtes SQL nécessaires à la manipulation des bases de données. 

\Par

\begin{center}
\includegraphics{227_SQL-fenetre}
\end{center}

\Par

Cependant, cette console n'étant pas toujours très pratique, on peut aussi utiliser une interface graphique telle que DB Browser ou SQLiteSpy.

\subsubsection{Environnement MySQL}
\index{Bases de données!MySQL}
\index{MySQL}

MySQL étant un logiciel libre, il s'intègre parfaitement dans un environnement composé du système d'exploitation Linux, du serveur Apache et du langage PHP. Cet environnement est appelé \textit{LAMP}, acronyme de \og Linux - Apache - MySQL - PHP \fg.

Sous Windows, l'environnement correspondant s'appelle \textit{WAMP} (W pour Windows), et sous Mac \textit{OS MAMP} (M pour Mac).

Notons que le langage PHP est le complément idéal à MySQL pour la création de pages Web dynamiques via un serveur HTTPS.

\textit{PhpMyAdmin} est l'interface la plus connue pour utiliser MySQL.

\Par

Une fois l'environnement installé avec MySQL ou SQLite, nous sommes prêts à manipuler des bases de données avec SQL.

\subsection{Les requêtes SQL}
\index{Base de données!Requêtes SQL}

\NathanLiveVideo[\PictoVideo\ Retrouvez une explication des requêtes SQL en vidéo.]{requêtes SQL}{https://media.prepamath.fr/ILTNSIch07crs01}

\qrset{height=1.1cm,version=5}\rput(-1,0.5){
\psframebox{\qrcode{https://media.prepamath.fr/ILTNSIch07crs01}}}
\vspace*{-20pt}



\subsubsection{Fonctions de base}

La syntaxe des fonctions de base en SQL est très simple.

\begin{itemize}
\item Projection d’un ou plusieurs attributs dans une table :
\begin{listing}[language=SQL]{}{07-06.txt}\end{listing}

\item Classification des données selon un ou plusieurs attributs:
\begin{listing}[language=SQL]{}{07-07.txt}\end{listing}
\Par
À noter que \og * \fg{} désigne la totalité des attributs.

\item Projection d’un ou plusieurs attributs dans une table avec contrainte :
\begin{listing}[language=SQL]{}{07-06b.txt}\end{listing}

\item Insertion d'un enregistrement dans une table:
\begin{listing}[language=SQL]{}{07-03.txt}\end{listing}

\Par

\item Modification d'une ou plusieurs valeurs d'attributs:
\begin{listing}[language=SQL]{}{07-04.txt}\end{listing}

\Par

\item Suppression d'un enregistrement dans une table:
\begin{listing}[language=SQL]{}{07-05.txt}\end{listing}

\Par

\item Suppression des doublons sur un attribut:
\begin{listing}[language=SQL]{}{07-08.txt}\end{listing}
\end{itemize}

Les deux requêtes suivantes sont hors-programme,mais nous choisissons de vous les présenter en complément :

\begin{itemize}
\item Création d'une table nommée \og matable \fg{} dans la base de données:
\begin{listing}[language=SQL]{}{07-01.txt}\end{listing}

\Par

\item Suppression de la table:
\begin{listing}[language=SQL]{}{07-02.txt}\end{listing}

\end{itemize}


\begin{Exemple} 
à l'aide d'une interface graphique (DB Browser pour SQLite ou\linebreak PhpMyAdmin pour MySQL), il est facile de créer une base de données nommée\linebreak \og gestion \fg{} ainsi que deux tables :
\begin{itemize}
\item[$\to$] \og restaurants \fg d'attributs : id, nom, rue, cp, ville, email, tel et id\_gerant;
\item[$\to$] \og gerants \fg d'attributs : id, nom, prenom, email et tel.
\end{itemize}
\end{Exemple}

\begin{itemize}
\item Pour changer l'adresse email de l'enregistrement dont l'id est 1, on écrira:
\begin{listing}[language=SQL]{}{07-09.txt}\end{listing}

\item Pour supprimer tous les enregistrements concernant la ville de Nantes dans la table \og restaurants \fg, on écrira:
\begin{listing}[language=SQL]{}{07-10.txt}\end{listing}
\end{itemize}


\subsubsection{Jointure}
\index{Base de données!Jointure (SQL)}
\index{Jointure (bases de données)}
\enlargethispage*{3em}


La syntaxe pour une jointure est la suivante :

\begin{listing}[language=SQL]{}{07-11.txt}\end{listing}

\Par

Cette requête joint les deux tables \texttt{table1} et \texttt{table2} et on choisit un critère de sélection avec \og ON \fg. La condition qui vient après peut être une égalité ou autre chose.




\subsubsection{Opérations d'agrégation}
\index{Base de données!Opérations d'agrégation (SQL)}

\paragraph{Ajouter}
\index{Bases de données!SUM}

Dans certains cas, il est nécessaire d'ajouter toutes les entrées d'une colonne (d'un attribut). On pourra le faire à l'aide de la fonction d'agrégation SUM.

\begin{Exemple}
considérons la table \og commandes \fg{} suivante:

\Par

\begin{figure}
\begin{center}
\begin{tabular}{|*{3}{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{commandes}\\
\tabulartitle\hline
id\_commande & id\_client & total\\
\hline
1 & 12 & 125\\
\hline
2 & 7 & 35\\
\hline
3 & 8 & 50\\
\hline
\end{tabular}
\tabcaption{Table \og commandes \fg{} }
\label{table-commandes-sum}
\end{center}
\end{figure}


La requête :

\begin{listing}[language=SQL]{}{07-17.txt}\end{listing}

\Par

retourne:

\Par

\begin{center}
\begin{tabular}{|*3{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries somme \\
\hline
210\\
\hline
\end{tabular}
\end{center}

\Par

où \og 210 \fg{} représente le total des valeurs de l'attribut \texttt{montant} des commandes.

Il n'est pas indispensable de spécifier d'alias (\texttt{AS}), mais c'est assez pratique dans les requêtes plus complexes. Dans notre exemple, nous avons choisi l'alias \og somme \fg{} pour désigner la somme obtenue.
\end{Exemple}

\paragraph{Compter}
\index{Bases de données!COUNT}

Dans certains cas, savoir combien une colonne comporte d'entrées s'avère utile. On le fera avec la fonction d'agrégation COUNT.

\begin{Exemple}
prenons à nouveau la table \og commandes \fg{}  du tableau \ref{table-commandes-sum} de l'exemple précédent.
\end{Exemple}

\enlargethispage*{5em}

Si on considère la requête suivante:\enlargethispage*{\baselineskip}
\begin{listing}[language=SQL]{}{07-18.txt}\end{listing}
\Par
alors, elle affiche: \begin{tabular}{|*1{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries nb \\
\hline
2\\
\hline
\end{tabular}
car il y a deux enregistrements où la valeur de l'attribut \og montant \fg{} est strictement inférieure à 100.

\paragraph{Effectuer une moyenne}
\index{Bases de données!AVG (Moyenne)}

Cela se fait à l'aide de la fonction d'agrégation AVG (abréviation de \textit{average}, qui signifie \og moyenne \fg en anglais).

\begin{Exemple}
en reprenant la table représentée dans le tableau \ref{table-commandes-sum}, la requête :
\Par
\begin{minipage}{0.45\linewidth}
\begin{listing}[language=SQL]{}{07-19.txt}\end{listing}
\end{minipage}
\hfill
\begin{minipage}{0.5\linewidth}
affiche:
\Par
\begin{center}
\raisebox{1em}{\begin{tabular}{|*1{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries moy \\
\hline
66.6667\\
\hline
\end{tabular}}
\end{center}
\end{minipage}
\Par
où \og 66,6667 \fg{} représente donc la moyenne des commandes.
\end{Exemple}


\paragraph{Maximum et minimum}
\index{Bases de données!MAX (Maximum)}
\index{Bases de données!MIN (Minimum)}
Connaître le maximum et le minimum des valeurs d'un attribut se fait avec les fonctions d'agrégation MAX et MIN.

\begin{Exemple}
toujours avec la table représentée par le tableau \ref{table-commandes-sum}, la requête :
\begin{listing}[language=SQL]{}{07-20.txt}\end{listing}

\Par

affiche :

\Par

\begin{center}
\begin{tabular}{|*2{>{\ttfamily}Zl|}}
\tabulartitle\hline
\bfseries minimum & \bfseries maximum \\
\hline
25 & 125\\
\hline
\end{tabular}
\end{center}
\end{Exemple}

\subsubsection{Complément : créer une vue (hors-programme)}
\index{Bases de données!Vue}

Il sera de temps en temps utile de nommer la table obtenue à l'issue d'une requête; c'est ce que l'on appelle \textit{créer une vue} de la requête. Prenons par exemple la requête précédente qui permet de calculer le maximum et le minimum, et créons une vue que l'on nomme \og mavue \fg{} \enlargethispage*{4em}
\begin{listing}[language=SQL]{}{07-21.txt}\end{listing}
\Par
On peut ainsi utiliser la table (la vue) dans une autre requête, par exemple pour calculer l'écart entre le maximum et le minimum:
\begin{listing}[language=SQL]{}{07-22.txt}\end{listing}



\section{Manipuler les bases de données avec Python}
\index{Base de données!Python}
\index{sqlite3 (Python)}
\index{mysql.connector (Python)}

Pour manipuler des bases de données avec Python, on fera appel au module \texttt{sqlite3} ou au module \texttt{mysql.connector} selon le serveur SQL que l'on aura installé.


\subsection{Avec SQLite 3}
\label{bddPython}
\enlargethispage*{5em}

\NathanLiveVideo[\PictoVideo\ Retrouvez une mise en pratique de SQLite 3 en Python en vidéo.]{requêtes SQL}{https://media.prepamath.fr/ILTNSIch07crs02}

\qrset{height=1.1cm,version=5}\rput(-1,0.2){
\psframebox{\qrcode{https://media.prepamath.fr/ILTNSIch07crs02}}}
\vspace*{-20pt}

\subsubsection{Préliminaires}

Il faut d'abord faire appel au module \texttt{sqlite3}:

\begin{listing}{}{07-23.txt}\end{listing}

\Par

Ensuite, il faut établir une connexion à une base de données:

\begin{listing}{}{07-24.txt}\end{listing}

\Par

Ici, on donne le nom \og conn \fg{} à notre connexion, et on donne le nom \og gestion.db \fg{} à notre base de données. À ce stade, cette dernière n'existe pas encore. Pour la créer, on écrit:

\begin{listing}{}{07-25.txt}\end{listing}

\Par

On donne en quelque sorte le curseur à notre base.

Cette opération provoque la création d'un fichier \texttt{gestion.db} dans le répertoire qui contient le script Python. C'est dans ce fichier que l'on va stocker les tables créées ainsi que leurs enregistrements. 

\vspace*{-1mm}
\begin{attention}
Le format du fichier est propre à Sqlite (mélange de binaire et de texte) et ne doit pas être manipulé directement via un éditeur de texte. C'est le SGBD qui maintient ce fichier à jour en y exécutant les requêtes qui lui sont transmises (ici par Python).
\end{attention}

\subsubsection{Passer une requête SQL}


Pour exécuter une requête SQL, on utilisera la commande \texttt{cursor.execute} :
\begin{listing}{}{07-26.txt}\end{listing}

\enlargethispage*{5em}

\newpage

\begin{Exemples}
\begin{itemize}
\item Pour créer une table:
\begin{listing}{}{07-27.py}\end{listing}
\end{itemize}
\end{Exemples}

\begin{itemize}
\item Pour insérer un enregistrement:
\begin{listing}{}{07-28.py}
\end{listing}
\end{itemize}

\subsubsection{Insérer un enregistrement à partir d'un dictionnaire}

On utilise toujours la méthode \texttt{execute} mais la syntaxe est légèrement différente, comme le montre l'exemple de la page suivante.

\vspace*{-1.5em}
\begin{Exemple}
\begin{listing}{}{07-29.py}\end{listing}
\end{Exemple}

\subsubsection{Insérer plusieurs enregistrements à partir d'une liste}

On utilise ici la méthode \texttt{executemany} comme dans l'exemple suivant.\enlargethispage*{4em}

\vspace*{-1em}
\begin{Exemple}
\begin{listing}{}{07-30.py}\end{listing}\end{Exemple}

\subsubsection{Afficher les enregistrements}

On pourra utiliser une syntaxe comme celle de l'exemple suivant.

\vspace*{-1em}
\begin{Exemple}
\begin{listing}{}{07-31.py}\end{listing}
\end{Exemple}

\subsubsection{Enregistrements avec la clause WHERE}

Quand on souhaite afficher les enregistrements en fonction de la valeur d'un\linebreak attribut avec la clause WHERE, la syntaxe est un peu spéciale, comme le montre l'exemple suivant.

\begin{Exemple}
\begin{listing}{}{07-32.txt}\end{listing}
\end{Exemple}

\subsubsection{Valider les modifications}

Avant de clore la connexion, il faut s'assurer que les éventuelles modifications ou insertions soient prises en compte. On utilise alors la méthode \texttt{commit()}:

\begin{listing}{}{07-33.txt}\end{listing}


\subsubsection{Clôture de la connexion}

Quand on a fini de se servir de la base de données, il ne faut pas oublier de clore la connexion.

\begin{listing}{}{07-34.txt}\end{listing}

\Par

\qrset{height=1.1cm,version=5}\rput(-1,0.2){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-35.py}}}
\textit{Télécharger le programme Python complet sur sqlite3.}

\Par

Pour plus d'informations sur le module \texttt{sqlite3}, n'hésitez pas à vous servir de la commande \textit{help}:

\Par

\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
\pychevrons import sqlite3\\
\pychevrons help(sqlite3)
\end{tabularx}

\subsection{Avec mysql.connector}

Par défaut, ce module (auquel on doit faire appel si l'on utilise MySQL) n'est pas installé. Il faudra donc se renseigner pour l'installer sur votre distribution Python (avec la commande \texttt{pip} ou, sous Anaconda, la commande \texttt{conda}).

Si l'on part de la base de données \og gestion \fg{}  définie précédemment, voici un exemple.

\qrset{height=1.1cm,version=5}\rput(-1,-0.7){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-36.py}}}
\vspace*{-10pt}

\begin{listing}{}{07-36.py}\end{listing}


\enlargethispage*{5em}
\begin{solintro}
\begin{questions}
\item Pour la table \og personnes \fg, l'attribut \og id\_prenom \fg{}  peut être une clé primaire. En effet, une clé primaire est un attribut dont les valeurs sont toutes distinctes. Dans notre situation, pour la table \og personnes \fg, tous les enregistrements ont un id\_prenom différent.

Il en est de même pour la table \og genres \fg{} : \og id\_genre \fg{} peut être une clé primaire.

Pour la table \og livres \fg, l'attribut \og id\_livres \fg{}  peut être une clé primaire.


\item Pour la table \og livres \fg, l'attribut \og id\_prenom \fg{} est lié à la table \og personnes \fg{} donc il peut être défini comme clé étrangère. Mais \og id\_genre \fg{} est lié à la table \og genres \fg{} donc peut aussi être une clé étrangère.

\Break

\item Une requête SQL permettant d'insérer une des informations dans la table \og personnes \fg est :

\begin{listing}[language=SQL]{}{07-37.txt}\end{listing}

\Par

Pour insérer tous les enregistrements, il suffit de répéter quatre autres fois cette requête en changeant bien entendu le prénom.

On peut aussi, plus simplement, faire la requête suivante :

\begin{listing}[language=SQL]{}{07-38.txt}\end{listing}

\item Dans cette question, la difficulté réside dans le fait que dans la table \og films \fg, les prénoms n'apparaissent pas; seuls les identifiants sont\linebreak enregistrés. On va donc utiliser une jointure:

\begin{listing}[language=SQL]{}{07-41.txt}\end{listing}


\item Une requête SQL permettant de calculer le nombre total de livres\linebreak indisponibles est la suivante:

\begin{listing}[language=SQL,morekeywords={IS}]{}{07-42.txt}\end{listing}

\BreakWithoutItem

Il s'agit ici de compter tous les enregistrements de la table \og livres \fg{} dont la valeur de l'attribut \og id\_prenom \fg n'est pas vide (ce qui signifie qu'un membre de la famille possède le livre correspondant, et donc que ce dernier est indisponible).

L'attribut \og id\_prenom \fg{} étant par définition un entier, sa valeur sera nécessairement \og NULL \fg{} si aucune valeur ne lui a été attribuée. 
\end{questions}
\end{solintro}
