Langage SQL : définition des données

1. Langage de définition des données

a. Création d’une base de données

Dans une instance PostgreSQL, une base de données est un conteneur : elle contient des schémas, des tables, des index et tous les objets utiles à une application.

Elle accueille aussi les connexions depuis les applications clientes. En effet, lorsqu’une connexion est ouverte sur une base de données particulière, il n’est pas possible d’utiliser directement des objets créés dans d’autres bases de données.

Il est donc important de répartir correctement les objets et données des différentes applications entre les bases de données et les schémas. Au sein d’une même base de données, il est possible de créer autant de schémas que souhaité, afin de mettre en place une organisation logique des données.

La création d’une base de données se fait avec l’ordre SQL CREATE DATABASE. Quelques paramètres permettent de personnaliser la création d’une base de données.

Mais tout d’abord, afin de créer une base de données, il est nécessaire d’être connecté avec un rôle super-utilisateur ou avec un rôle ayant le privilège CREATEDB. Par défaut, le rôle utilisé pour créer la base de données en est le propriétaire. Il est néanmoins possible de transmettre l’appartenance de la base de données à un utilisateur non privilégié, avec l’option OWNER.

La base de données template1 sert de modèle par défaut pour la création de la base de données. Pour chaque base créée avec ce modèle, une copie de template1 est faite, ainsi, tous les objets créés dans template1 sont dupliqués dans la nouvelle base. La base template0 fonctionne de la même façon, mais il n’est pas possible de créer des objets dans cette base modèle : template0 reste une base vierge. Il est possible de choisir la base de données servant de modèle avec l’option TEMPLATE. Il est possible définir une base de données en tant que modèle, avec l’option...

Types de données

Avant d’aborder la création d’une table, il est utile de présenter les différents types de données qui sont utilisés par les attributs des tables.

En effet, dans le cadre de la modélisation d’une base de données, chaque attribut est présent pour une raison bien précise, et le type de données de l’attribut est une conséquence logique du modèle de données.

La norme SQL définit quelques types de données, que PostgreSQL implémente, en y ajoutant quelques autres types. Il est de plus possible d’ajouter d’autres types de données à PostgreSQL, généralement sous la forme d’une extension.

1. Types de données numériques

Il existe de nombreux types de données numériques dans PostgreSQL, correspondant pour la plupart aux types de données de la norme SQL.

smallint, int2 : nombre entier signé, stocké sur 2 octets, avec une plage de valeurs allant de -32 768 à +32 767.
integer, int, int4 : nombre entier signé, stocké sur 4 octets, avec une plage de valeurs allant de -2 147 483 648 à +2 147 483 647.
bigint, int8 : nombre entier signé, stocké sur 8 octets, avec une plage de valeurs allant de -9 223 372 036 854 775 808 à +9 223 372 036 854 775 807.
real, float4 : nombre à virgule flottante avec 6 décimales et une précision arbitraire, stocké sur 4 octets.
double precision, float8 : nombre à virgule flottante avec 15 décimales et une précision inexacte, stocké sur 8 octets.
numeric [ (p, s) ], decimal [ (p, s) ] : nombre décimal, avec une précision décimale exacte. Ce type est particulièrement recommandé pour les valeurs monétaires ou tous les types numériques où la partie flottante ne doit pas varier. Les modificateurs optionnels correspondent, pour p, au nombre total de chiffres puis pour s à la partie décimale.

Afin d’illustrer l’utilisation de ces types de données, nous allons utiliser la commande SELECT avec de simples litéraux et l’opérateur de transtypage :: qui permet de déclarer explicitement le type de données. L’exemple suivant montre l’utilisation d’un nombre entier...

Création de tables

La création des tables est une étape importante dans le cycle de vie d’une base de données : il s’agit de traduire le modèle conceptuel de données (MCD) en un ensemble cohérent de tables, qui vont contenir les données.

L’importance du modèle conceptuel est complétée par le choix des types de données pour créer le modèle physique de données (MPD).

Les choix faits lors de ces étapes sont fondamentaux pour les performances et la pérennité de la base de données.

L’ordre CREATE TABLE permet de mettre en œuvre les choix de cette conception. Le synopsis minimal de cette commande est le suivant :

CREATE TABLE [ IF NOT EXISTS ] nomtable 
( 
 [ 
   { nomcolonne type_donnee 
       [ COLLATE collation ] 
       [ contraintecolonne ] 
     | contraintetable 
   } 
   [, colonne ] 
 ] 
)

Pour l’essentiel, il est possible de résumer une table en une collection de colonnes (aussi appelées « champs » ou « attributs »), accompagnée de contraintes et de collations.

1. Création d’une table temporaire

Une table temporaire créée avec le modificateur TEMP n’est visible que dans le contexte de la session courante, c’est-à-dire qu’aucune autre session concurrente n’a accès à cette table et que la table n’existe plus dès que la session se termine.

La clause ON COMMIT modifie la visibilité de la table temporaire à l’échelle de la transaction courante. Par défaut, les lignes sont préservées jusqu’à la fin de la session (PRESERVE ROWS), mais il est possible de les supprimer (DELETE ROWS) ou de supprimer la table temporaire à la fin de la transaction (DROP).

Les index associés à cette table sont aussi temporaires, et donc supprimés au même moment que la table. Une table temporaire peut avoir le même nom qu’une table permanente, et dans ce cas, elle sera la seule visible pendant la durée de la session.

Le synopsis minimal de la commande est le suivant :

CREATE TEMP TABLE...

Fonctions et opérateurs

PostgreSQL fournit un certain nombre de fonctions et d’opérateurs permettant de manipuler les données. Ces fonctions et opérateurs interagissent avec des types de données : le choix d’un type de données est lié à ce qu’on souhaite opérer sur la donnée.

Les listes suivantes reprennent les opérateurs logiques et les principales fonctions et opérateurs pour les principaux types de données.

1. Opérateurs logiques

Les opérateurs sont généralement utilisés dans la clause WHERE d’une requête SELECT, tout comme les requêtes UPDATE et DELETE.

a. Opérateurs de combinaison logique

Les opérateurs de combinaison logique permettent d’utiliser plusieurs opérations de comparaisons et de combiner leurs résultats. Ces opérateurs sont identiques à ce qui existe dans tous les langages informatiques, à la nuance près que l’état NULL doit être pris en compte, et utilisent les mot-clés suivants :

a AND b : et logique
a OR b : ou logique
NOT a : inversion logique

Les combinaisons logiques doivent prendre en compte l’état NULL et sont résumées dans le tableau suivant :

a	b	a AND b	a OR b
vrai	vrai	vrai	vrai
vrai	faux	faux	vrai
vrai	null	null	vrai
faux	faux	faux	faux
faux	null	faux	null
null	null	null	null

b. Opérateurs de comparaison

Les opérateurs suivants retournent un booléen, vrai (t) ou faux (f), selon le résultat de la comparaison. Lorsqu’un des opérandes comparés est NULL, alors le résultat de la comparaison est NULL, sauf lorsque l’opérateur est IS DISTINCT FROM, (NULL étant l’absence de valeur).

a < b : retourne vrai si l’opérande a est plus petit que l’opérande b.
a > b : retourne vrai si l’opérande a est plus grand que l’opérande b.
a <= b : retourne vrai si l’opérande a est plus petit ou égal à l’opérande b.
a >= b : retourne vrai si l’opérande a est plus grand ou égal à l’opérande b.
a = b : retourne vrai si les deux opérandes sont équivalents.
a <> b ou a != b : retourne true si les deux opérandes ne sont...

Création d’index

1. Les index

Un index est un bon outil pour améliorer les performances des requêtes dans la base de données. L’analogie avec un livre permet de comprendre le rôle joué par un index dans une base de données. Sans index, lorsqu’un lecteur cherche une information, il doit lire le livre jusqu’à trouver ce qu’il cherche. Selon la taille du livre et l’endroit où se situe l’information, début ou fin, la recherche peut être longue.

Dans la plupart des ouvrages techniques, l’éditeur place un index de quelques pages, contenant les mots-clés considérés comme étant susceptibles de faire l’objet d’une recherche afin de faciliter cette recherche. Le lecteur n’a plus qu’à rechercher dans l’index et se rendre à la page indiquée.

Les serveurs de bases de données stockent les données dans des tables et doivent lire les tables lorsqu’un utilisateur recherche une donnée. La méthode la plus simple est de parcourir séquentiellement une table, jusqu’à rencontrer la donnée recherchée ; très souvent, il faut parcourir toute la table pour être certain de ne pas manquer une entrée. Cette méthode fonctionne bien tant que la table a un volume rendant les temps de parcours corrects. Au-delà d’une limite qui dépend des types de données utilisés, du nombre de colonnes de la table et du nombre de tuples, le parcours séquentiel de la table est trop long pour obtenir des temps de réponse raisonnables. Il devient nécessaire de créer un ou plusieurs index, en fonction des recherches effectuées sur la table.

L’analogie avec le livre s’arrête là. En effet, un livre a un contenu statique, au contraire d’une base de données, dont le contenu évolue dans le temps. Cela signifie que les index doivent être mis à jour en même temps que la table. Plus une table a d’index associés, plus les temps de mise à jour lors d’une insertion, d’une mise à...

Langage de contrôle

1. Gestion des rôles : utilisateurs et groupes

Un compte utilisateur est aussi appelé un rôle. Un rôle est un objet global, c’est-à-dire qu’il est valable pour toute l’instance quelle que soit la base de données à laquelle on souhaite se connecter. Un rôle a des droits sur des objets ; il permet d’ouvrir des connexions, fait partie d’autres rôles ou contient d’autres rôles. Un rôle recouvre les notions d’utilisateur et de groupe.

La commande servant à créer des rôles est CREATE ROLE. Le synopsis de la commande SQL est le suivant :

CREATE ROLE nomrole [ [ WITH ] option [ ... ] ]

Les options sont les suivantes :

LOGIN : indique si le rôle peut se connecter au serveur. Le rôle devient l’équivalent d’un compte utilisateur. Par défaut, un rôle ne peut pas se connecter.
CONNECTION LIMIT limiteconnexion : indique le nombre maximum de connexions simultanées d’un rôle. La valeur par défaut (-1) ne met pas de limite.
[ ENCRYPTED | UNENCRYPTED ] PASSWORD ’motdepasse’ : définit le mot de passe d’un rôle. Les mots-clés ENCRYPTED et UNENCRYPTEDindiquent si le mot de passe fourni doit être chiffré ou non. Par défaut, le mot de passe est fourni « en clair » et est chiffré par PostgreSQL.
VALIDUNTIL ’heuredate’ : indique la date et l’heure de fin de validité du mot de passe. Par défaut, un mot de passe est valable indéfiniment.
SUPERUSER : indique que le rôle outrepasse le système de droits. Le rôle a donc tous les droits sur le groupe de bases de données. Cette option n’est pas activée par défaut.
CREATEDB : indique que le rôle peut créer des bases de données dans l’instance, ce qui n’est pas le cas par défaut.
CREATEROLE : autorise le rôle à créer d’autres rôles. Par défaut, un rôle ne peut pas créer d’autres rôles.
IN ROLE nomrole [, ...] : indique le ou les rôles dont le nouveau rôle est membre.
ROLE nomrole [, ...] : indique le ou les rôles qui deviennent membres du nouveau rôle. Ce nouveau rôle devient ainsi l’équivalent d’un...

SQL - définition