Chapitre 4 :
Créer une base de données

Vous venez dd'apprendre, dans le chapitre précèdent, à créer une base de données. Vous allez maintenant découvrir comment ajouter des tables. Comme, lors de la requête de création d'une table, vous devez indiquer les colonnes à créer et leurs options, vous allez devoir étudier les différentes options qu'il est possible d'appliquer à une colonne. 

Les options des colonnes

Les types de colonne

Pour chaque colonne, il faut préciser le type de données (entier, décimaux texte...). Il existe de très nombreux types. Voici les principaux :

Les types d'entiers

Il existe 5 types de colonnes qui permettent de stocker des nombres entiers plus ou moins grands.: tinyint, smallint, mediumint, int, et bigint. la plage de valeur qui peut être stocké dépend du nombre d'octets utilisés en mémoire pour stocker le chiffre. Cette plage est égale à 2^(nombre d'octet*8). Si la colonne est signée (elle accepte les valeurs négatives) alors la plage est centrée sur le 0. Sinon elle commence à 0

Si vous essayer de stocker un chiffre qui n'entre pas dans la plage, le serveur stockera le chiffre de la plage le plus proche.

Les types de décimaux

Les types décimaux permettent de stocker des nombres à virgule. Il en exite 4 types : DECIMAL, FLOAT, DOUBLE, et REAL.

FLOAT DOUBLE et REAL stocke des valeurs approchées sous forme de nombre alors que DECIMAL stocke les valeurs exactes sous forme de caractères. Il est donc conseillé d'utiliser DECIMAL si vous avez besoin d'une précision excate.

Lorsqu'on utilise des décimaux, il faut préciser deux argument : la précision qui correspond au nombre de chiffre en tout et l'échelle qui indique le nombre de chiffre après la virgule. Donc si vous voulez stocker des prix allant jusqu'à 999.99 euros, il faut utiliser décimal(5,2). En effet 999.99 contient en tout 5 chiffres. Sur ces 5, seulement 2 sont après la virgule.

Le type boolen

Permet de stocker soit 0 pour faux, soit 1 pour vrai. Dans phpMyadmin, quand on choisi le type booleen lors de la création, le type tinyint est utilisé.

Le type SERIAL

Le type SERIAL est un raccourci qui permet d'obtenir un BIGINT avec l'attribut UNSIGNED et les options AUTOINCREMENT NOTNULL ainsi qu'un index UNIQUE.

A l'IUT nous avons fait le choix de toujours utiliser le type SERIAL pour les clés primaires composées d'un seul champ

Les types de texte

Il existe deux grands types de colonnes pour stocker les chaines de caractères : les VARCHAR et les TEXT

• Les VARCHAR(taille) sont utilisés pour les chaines courtes (entre 1 et 255 caractères). Il est nécessaire de préciser la taille maximum lors de la création de la colonne
• Les TEXT sont utilisés pour les chaines longues (65535 caractères)

Pour des textes plus grands il existe d'autres types comme mediumtext et longtext.

Les types de date

Il existe 5 type de colonnes dates : Timestamp, Date, Time, Datetime, et Year

• Un TIMESTAMP est un nombre entier qui correspond au nombre de seconde écoulées depuis le 1er janvier 1970. Il utilise 4 octets
• Le type DATE permet de stocker une date sur 3 octets au formet YYYY:MM:JJ
• le type DATETIME permet de stocker une date et une heure sur 8 octets au format au "AAAA-MM-JJ HH:MM:SS".
• le type TIME permet de stocker une heure sur 3 octets. Une colonne time peut être utilisée pour stocker un interval de temps.
• le type YEAR utilise1 octet et stocke une annnée qui va de 1901 à 2155 (4 chiffres) ou de 1970 à 2069 (2 chiffres).

Une colonne au format timestamp utilise peut d'espace et permet d'utiliser la valeur par défaut current_timestamp et l'attribut ON UPDATE CURRENT_TIMESTAMP. C'est pourquoi ce type de colonne est très utilisé malgré le fait qu'il soit difficilement lisible pour un être humain.

Les indexs et clé primaire

Le plus important est l'index PRIMAIRE qui indique que le ou les colonnes doivent être les clés primaire de la table. L'index primaire, interdit les doublons et les valeurs nulles. 

L'index UNIQUE permet de s'assurer que chaque valeur de la colonne est unique. 

L'index INDEX permet au serveur de trouver rapidement une valeur dans une colonne. Au lieu de lire chaque valeur de la colonne jusqu'à trouver la bonne, l'index regroupe les valeurs proches dans un sous ensemble dont le serveur connait les valeurs minimales et maximales. Lors d'une recherche, le serveur commence par chercher le sous ensemble dont les valeurs minimales et maximales englobe la valeur cherchée. Une fois trouvé, il n'a plus qu'à parcourir les valeurs de ce sous ensemble. L'index utilise donc un peu plus de mémoire mais accélère les traitements.  Une table peut contenir jusqu'à 16 colonnes indexées. 

La valeur par défaut

La valeur par défaut d'une colonne est utilisée par le serveur, lorsqu'une requête de création de lignes ne précise pas la valeur de cette colonne. Le serveur utilise la valeur par défaut de la colonne. La valeur par défaut peut être :

• n'importe quelle valeur défini par l'utilisateur. 
• null mais il faut que la colonne autorise les valeurs nulles
• current_timestamp qui correspond au timestamp de l'ajout de ligne. Evidemment, cette valeur par défaut n'est utilisable que pour les colonnes qui stockent des timestamps. 

Les attributs

Il existe 4 attributs possibles BINARY, UNSIGNED, UNSIGNED ZEROFILL, ON UPDATE CURRENT_TIMESTAMP. Le seul que vous utiliserez dans ce cours est UNSIGNED

UNSIGNED : permet d'autoriser uniquement les valeurs positives. Si on utilise cet attribut sur un nombre qui peut stocker 256 possibilités, au lieu de pouvoir stocker de -127 à 128, on pourra stocker de 0 à 255. 

Pour information :

BINARY permet d'indiquer qu'il faut tenir compte de la casse pour trier des données du type texte

ZEROFILL complète une valeur numérique avec des 0 à gauche pour ajuster le contenu à la taille d'affichage de la colonne. Donc si vous avez un champ INT(3) et que vous tocker dedans la valeur 27, le serveur affichera 027. Comme l'attribut ZEROFILL doit obligatoirement être associé à l'attribut UNSIGNED, phpmyadmin propose l'attribut UNSIGNED ZEROFILL.

ON UPDATE CURRENT_TIMESTAMP

Cet attribut ne s'utilise qu'avec un champ qui contient des timestamps. Lors de la mise à jour d'une donnée à l'aide de la requête UPDATE, il n'est pas la peine de préciser la valeur de ce champ, car le serveur lui assignera automatiquement le timestamp de la mise à jour.

L'auto incrémentation

L'auto incrémentation est une option qui ne peut s'appliquer qu'à une clé étrangère composée d'un seul champ et qui a un type de la catégorie "Entier". Lors de l'ajout de données, si l'utilisateur ne précise pas la valeur de la clé primaire, alors le serveur utilise la dernière valeur utilisée +1. Cela évite à l'utilisateur de devoir récupérer la dernière valeur utilisée lui même. 

La gestion des valeurs nulles

Comme souvent en informatique, nulle ne veut pas dire 0. Dans une base de donnée, une valeur nulle correspond à une case vide. Généralement, il est déconseillé d'enregistrer des valeurs nulles dans la base de données, cependant, il est possible d'indiquer au serveur, que les valeurs nulles sont autorisées.

Maintenant que nous connaissons les différentes options des champs nous allons voir les différentes façon de créer une table

Créer une table à l'aide d'une requête

La doc de mysql présente la requête de création d'une table de cette façon :

CREATE TABLE nom_table(
nom_champ1 type_données [contraintes],
[nom_champ2 type_données contraintes],
…
[CONSTRAINT contraintes]
) ;

Pour comprendre la doc, il faut se rappeler que ce qui est entre crochet est optionnel.

Exemple de création d'une table simple

##La table personnage contient 4 champs :
l'identifiant id_perso qui est la clé primaire et donc du type SERIAL
nom_perso qui contiendra le nom du personnage qui aura une taile d'au maximum 60 caractères
description_perso qui contiendra un texte assez long
date_naissance_perso qui contiendra une date 

CREATE TABLE Personnage(
id_perso SERIAL,
nom_perso VARCHAR(60),
description_perso TEXT,
date_naissance_perso DATE,
PRIMARY KEY(id_perso)
);

Lorsqu'on veut rajouter une ou plusieurs options ou contraintes à une colonne, on les indique avant la virgule qui annonce le changement de colonne.

##Reprend l'exemple en précisant que le nom doit être unique et que la description ne peut pas être nulle

CREATE TABLE Personnage2(
id_perso SERIAL,
nom_perso VARCHAR(60) UNIQUE,
description_perso TEXT NOT NULL,
date_naissance_perso DATE,
PRIMARY KEY(id_perso)
);

Si jamais une table contient plusieurs clés primaires, car cette table correspond à une relation plusieurs à plusieurs du schéma conceptuel, il suffit de lister les champs dans la contrainte PRIMARY KEY

##Création d'une table qui correspond à la relation entre une entité Joueur et une entité Match pour stocker quels sont les joueurs qui participent à un match. 

CREATE TABLE Participe(
id_joueur BIGINT(20) UNSIGNED NOT NULL,
id_match BIGINT(20) UNSIGNED NOT NULL,
numero_maillot INT,
PRIMARY KEY (id_joueur, id_match)
);

Si vous voulez voir les types et options des champs, vous pouvez utiliser la requete SHOW CREATE TABLE nom de la table qui affiche la requête qui a permis de créer la table.

Créer une table à l'aide des formulaire dans phpmyadmin

Créer une table à l'aide des outils d'import de phpmyadmin

PhpMyAdmin dispose d'un outil d'import qui permet d'exécuter des requêtes SQL rédigées dans un fichier .txt

Pour cela, il faut :

• rédiger les requêtes de création de table dans un fichier .txt 
• se rendre dans la bonne base grâce au menu latéral
• Cliquer sur l'onglet import

• Sélectionner le fichier

• Vérifier que le format attendu est bien SQL
• Cliquer sur le bouton exécuter

Il est possible d'importer un fichier depuis l'onglet import du serveur. Dans ce cas, votre fichier doit commencer par indiquer la base à utiliser avec la commande use nombase

Avant d'importer un fichier sql ouvrez le et vérifier ce qu'il contient et s'il n'y a pas le nom d'une base d'indiqué.