définitions et utilisation pratique

Les Bases de Données
et le langage SQL
définitions et utilisation pratique
1
Définition
Sens large : Ensemble structuré d'éléments d'information ...
Sens informatique : ... généralement agencés sous forme de
tables, dans lesquels les données sont organisées selon
certains critères en vue de permettre leur exploitation.
Pas de notion de taille ni d’accessibilité, parfois confondu
avec la notion de Banque de données qui est à priori moins
structurée.
2
2
Origines
Besoin de stocker et d’exploiter l’information.
fichiers papier
premières structures informatisées ( bdd hiérarchiques)
apparition des premières bases de données relationnelles
dans les années ’70
en développement : bases de données “objet”
3
3
Base de données relationnelle (1)
Base de données structurée selon les principes de l’algèbre
relationnelle
La “relation” est la structure logique qui lie les “attributs”,
elle est représentée comme une table.
Terminologie
Table
Relation
colonne
attribut
ligne
tuple
4
4
Base de données relationnelle (2)
SGBDR = Système de Gestion de Bases de Données Relationnelles
Le SGBDR stocke les données sur le disque dur selon
différentes techniques complexes
Le SGDBR administre et assure la cohérence des données
Le SGBDR présente une interface permettant à l’utilisateur de
consulter et manipuler les données
5
5
Objectifs des SGBDR
Centralisation des données
Rapidité d’accès
Accès sécurisés aux données pour plusieurs utilisateurs
simultanément
Résistance aux pannes
Non redondance des données
6
6
Architecture des SGDBR
Habituellement les SGBDR fonctionnent en client-serveur, c’est à dire
que le SGBDR est un “serveur” et que des “clients” peuvent lui
soumettre des requêtes.
En tant que serveur le SGBDR peut donc centraliser les données pour
de nombreux clients éparpillés dans le monde, mais on peut
également l’installer sur son propre pc de manière autonome.
Il existe divers programmes permettant de jouer le rôle de client.
7
7
Les serveurs et les clients
Les SGBDR les plus répandus sont Oracle, PostgreSQL,
MySQL, MS SQL Server, SQLite, Microsoft Access, ...
Chacun de ces SGBDR dispose de programmes client plus ou
moins conviviaux et complets, graphiques ou non.
Il existe également des modules spéciaux (ODBC) permettant
de faire communiquer certaines applications plus
généralistes (MS Access, MS Excel, Kexi, ...) avec des SGBDR.
8
8
La gestion des droits
Le SGBDR contrôle les accès aux données en identifiant les
utilisateurs et les ordinateurs qui interagissent avec lui.
Les droits d’accès sont définis finement par base de données,
par table ou même par champs ET par type d’opérations sur
chacun de ces éléments (SELECT, UPDATE, DROP, ...).
9
9
Exemple de base de données
Gestion de bibliothèque (simpliste)
!
soit par fichier unique (tableur) :
Titre
!
Auteur
Editeur
N° Livre
Nom
soit par base de données :
Prénom
livres
id_livre,
titre,
auteur,
éditeur,
...
Adresse
Date-sortie
prêts
id_livre,
id_lecteur,
date-sortie,
date-retour
Date-retour
...
lecteurs
id_lecteur,
nom,
prénom,
adresse,
...
10
10
Les clés et les index
Une clé est un champ (ou un groupe de champs) dont
chaque valeur n’est présente qu’une seule fois. Chacune de
ses valeurs détermine la ligne à laquelle elle appartient.
Une clé peut être primaire ou externe.
Les index sont des réductions d’une table à une seule (ou
éventuellement un groupe) de ses colonnes qui restent liés à
cette table et que l’on peut utiliser pour la parcourir plus
rapidement. Une table peut avoir plusieurs index.
11
11
Les autres contraintes utiles
“Not Null” ou “Null”
“Unique”
Valeur par défaut
“Unsigned”
Check (condition)
Contraintes d’intégrité
Clé étrangère
12
12
Les types de données
Les données doivent être définies lors de la création de la table
Différents types sont disponibles et varient selon les sgbdr, ex. :
Texte
Numérique
char(longueur)
integer
varchar(longueur)
bigint
text
real
double precision
Temps
date
timestamp
Autre
money
blob
boolean
La performance dépendra de la définition optimale des données
13
13
Le SQL
SQL = Structured Query Language
Le SQL permet d’interagir avec le SGBDR et de manipuler les
données
Il diffère légèrement selon les SGBDR mais reste toujours
très simple (mais ça peut devenir compliqué ...)
14
14
SQL : manipuler les tables
Créer une table avec 3 champs :
Create Table nom_table (nom_col1 TYPE1, nom_col2
TYPE2, nom_col3 TYPE3)
Supprimer une table : Drop Table nom_table
Ajouter un champ :
Alter Table nom_table ADD nom_col4 TYPE1
... Mais d’autres opérations sont possibles.
15
15
SQL : manipuler les données
Insertion d’une ligne :
INSERT INTO nom_table(nom_col1, nom_col2, nom_col3)
VALUES (val1, val2, val3);
Modification d’une ligne :
UPDATE nom_table
SET nom_col1 = “aaa”,
nom_col3 = “zzz”;
Suppression inconditionnelle de lignes :
DELETE FROM nom_table;
16
16
La commande SELECT (1)
La commande SELECT permet de sélectionner certains
champs, de limiter le résultat à certaines lignes et de
combiner plusieurs tables.
Syntaxe générale :
SELECT col1, col2
FROM nom_table
WHERE col3 = “abc”;
17
17
La commande SELECT (2)
SELECT DISTINCT * FROM table : élimine les doublons
SELECT * FROM (SELECT col1, col2 FROM nom_table
WHERE col3 = “abc”) WHERE col1 = col2 ;
SELECT col1 as champ_1, col2 as champ_2
FROM nom_table
WHERE col3 = “abc”
ORDER BY champ_2 desc ;
18
18
La commande SELECT (3)
SELECT * FROM table1, table2 : juxtapose tous les champs
des 2 tables dans toutes les combinaisons possibles
SELECT T1.col1, T1.col2, T2.col8
FROM T1,T2
WHERE T1.col3 = T2.col6 : ne sortira que les lignes
obéissant à la clause Where
19
19
La commande SELECT (4)
SELECT T1.col1, T1.col2, T2.col8
FROM T1,T2
WHERE T1.col3 = T2.col6 AND col3 = “abc”: ne
sortira que les lignes obéissant à la clause
Where. A éviter, préférez les “JOIN”.
SELECT T1.col1, T1.col2, T2.col8
FROM T1
JOIN T2 ON T1.col3 = T2.col6
WHERE T1.col3 = “abc”
20
20
La commande SELECT (5)
SELECT T1.col1, T1.col2, T2.col8
FROM T1
LEFT JOIN T2 ON T1.col3 = T2.col6
WHERE T1.col3 = “abc” : garde les lignes de T1 et les
complète si possible avec les valeurs (col8) de T2.
D’autres formes de clauses Join existent. Par ailleurs
plusieurs clauses Join peuvent être chaînées.
21
21
La clause GROUP BY
Dans un SELECT la clause GROUP BY permet d’obtenir des valeurs
agrégées, en l’associant à une fonction d’agrégation dans la clause
SELECT, telle que “avg”, “min”, “max”, “stddev”, etc. Exemples :
SELECT SUM(col1)
FROM T1
GROUP BY col1;
Retourne le total de tous les col1
SELECT COUNT(col1), col2
FROM T1
GROUP BY col2;
Retourne le nombre de col1
pour chaque valeur de col2
22
22
Les clauses imbriquées
Imbrications de clauses SELECT:
SELECT * FROM table1 WHERE col1 IN
(SELECT col1 FROM table2 where col8 = “abc”);
-> Le premier SELECT exploite les résultats du deuxième
CREATE TABLE table4
SELECT col1, col2, col4 FROM table1;
-> crée une nouvelle
table contenant une
partie des attributs
d’une table existante
23
L’extension POSTGIS
POSTGIS = POSTGRESQL + GIS . C’est un module qui permet
le traitement de données spatiales au sein de postgresql en
lui ajoutant une série de fonctions spécifiques.
Postgis permet de créer et manipuler des objets
géométriques, à savoir des points, des lignes et des
polygones (et leurs variantes).
Ses fonctions s’utilisent au sein des requêtes SQL.
24
Documentation
Bases de données et SQL : http://laurent-audibert.developpez.com/Cours-BD/
Postgresql : http://docs.postgresqlfr.org/
MySQL : http://dev.mysql.com/doc/refman/5.0/fr/index.html
Postgis : http://postgis.refractions.net/
•
http://postgis.refractions.net/docs/postgis.pdf
•
http://www.postgis.fr/
•
http://www.davidgis.fr/documentation/win32/html/index.html
25
25
Exercices avec MySQL (1)
D’abord installer le client MySQL avec “sudo apt-get install mysql-query-browser”
lancer “mysql-query-browser” depuis le terminal
26
Exercices avec MySQL (2)
Avec la base de données “western” produire les listes suivantes :
les films réalisés avant 1950
les films réalisés par Samuel FULLER
le nombre de films réalisés par Samuel FULLER dans les années ’40
les actrices ayant joué dans un film de Robert ALDRICH
le nombre de film répertoriés dans la table film_act
le nombre d’actrices répertoriées dans la table film_act
27
Exercices avec MySQL (3)
Avec la base de données “western” produire les listes suivantes :
tous les films avec John Wayne (attention il y a un piège)
Créer une table contenant uniquement les films des années ’50 pour
lesquels la musique a été composée par Louis Forbes.
28
Exercices avec Postgis
Se connecter avec PGadmin (ou autre) au serveur
Connecter QGis avec les mêmes paramètres
29