Comment créer une base de données et des tables avec un script SQL ?

I. Présentation

Après avoir vu de manière théorique le MPD d'une base de données (BDD) dans le tutoriel précédent, nous allons voir comment créer la base de données de l'exemple, avec toutes les tables qu'elle contient, cela grâce à un script SQL. Pour ceux qui n’aurait pas vu le tutoriel sur le MPD, suivez ce lien

II. Modèle physique des données de la BDD

Pour rappel, cette base de données gère l’implantation de Switchs dans différents bâtiments. Les switchs ont une liaison avec un fabricant (Cisco, HP, …), ont une description les concernant et sont ensuite liés avec le bâtiment dans lequel ils sont implantés. Un switch peut être installé dans un seul et unique bâtiment mais un bâtiment peut contenir plusieurs switchs. Un switch peut aussi être référencé mais pas encore implanté. Toutefois, un switch doit obligatoirement avoir un fabricant.

Un bâtiment peut ne pas être lié à un switch mais être malgré tout enregistré dans la table.

III. Qu'est-ce qu'un script SQL ?

Un script SQL est un fichier ayant l'extension ".sql" et contenant une suite de requêtes SQL permettant d'effectuer différentes actions lorsqu'il est importé dans l'interface PhpMyAdmin du serveur web. Le fait de créer un script SQL de la base de données que vous souhaitez créer vous permet de pouvoir l'importer n'importe où sans devoir tout recréer manuellement.

IV. Les commandes SQL à connaître avant de commencer

DROP DATABASE nom_base_de_données ; Supprimer une base de données
CREATE DATABASE nom_base_de_données ; Créer une base de données
USE nom_base_de_données ; Indique la BDD à utiliser et dans laquelle on exécute les requêtes.
CREATE TABLE nom_table ; Créer une table dans la BDD utilisée

V. Comment créer une table contenant des champs ?

Dans un premier temps, il faut créer la table avec la commande CREATE TABLE puis ensuite indiquer les champs que cette table va contenir avec leurs paramètres, comme ceci :

CREATE TABLE nom_table
(
Champs_1 TYPE NULL/NOT NULL PARAMETRES_SUPPLEMENTAIRES,
Champs_2 TYPE NULL/NOT NULL PARAMETRES_SUPPLEMENTAIRES,
Champs_3 TYPE NULL/NOT NULL PARAMETRES_SUPPLEMENTAIRES
) ENGINE = nom_moteur_stockage CHARACTER SET nom_jeu_de_caractères COLLATE nom_collation_bdd

On indique tout d'abord le nom du champ en début de ligne.

Ensuite, le TYPE correspond au type du champ, par exemple si les valeurs d'un champ contiennent uniquement des chiffres on lui attribue le type "INT" pour INTEGER, qui signifie ENTIER. A contrario, si les valeurs d'un champ contiennent toutes sortes de caractères, donnez-lui le type CHAR(n) où n est le nombre maximal de caractères que peut contenir chaque valeur.

L'indication NULL ou NOT NULL signifie que l'on autorise ou non à ce que ce champ ait une valeur nulle, si vous estimez qu'un champ doit forcément avoir une valeur alors il faut indiquer NOT NULL.

En fin de ligne, on indique les paramètres supplémentaires qui peuvent être multiple, par exemple vous pouvez indiquer qu'un champ est une clé primaire ou même qu'il doit s'auto-incrémenter à chaque nouvelle entrée dans la table.

Pour finir, vous remarquerez que l'on met une virgule au bout de chaque ligne sauf sur la ligne du dernier champ. Si vous en mettez une, vous obtiendrez une erreur lors de l'importation du script SQL.

VI. Comment s’y prendre pour créer le script SQL de cette BDD ?

Tout d’abord, créez un nouveau fichier texte avec le Bloc-notes ou Notepad++, par exemple, puis enregistrez-le avec l’extension « .SQL ».

Lorsque l’on regarde le MPD, on voit que la table T_Switchs a des liaisons avec les deux autres tables, qui correspondent à une clé étrangère vers chacune des tables, cela signifie qu’elle est dépendante de ces deux tables. De ce fait, dans le script SQL il faudra créer les tables T_Fabricants et T_Batiments avant la table T_Switchs pour que la table T_Switchs trouve ses références dans les autres tables. Mais, avant même de créer ces tables il faudra créer la base de données et indiquer qu’on l’utilise.

VII. Création de la base de données et utilisation

CREATE DATABASE Implementation_Switchs ;
USE Implementation_Switchs ;

VIII. Création de la table T_Fabricants

CREATE TABLE T_Fabricants
(
F_Num INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
F_Nom CHAR(20) NOT NULL
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin ;

• F_Num est de type INTEGER, ne peut pas être nul car c’est une clé primaire qui permet d’identifier le fabricant grâce à un numéro, un numéro qui sera auto incrémenté grâce à AUTO_INCREMENT.

• F_Nom correspond au nom du fabricant sur un maximum de 20 caractères, cela serait par exemple Cisco, HP, Netgear, … Ce nom ne peut pas être nul.

• InnoDB correspond au moteur de stockage de la BDD, latin1 au jeu de caractères et latin1_bin permet de définir les caractères qui sont équivalents par rapport à ce jeu de caractères.

Comme vous pouvez le remarquer, on définit une clé primaire grâce au paramètre PRIMARY KEY.

IX. Création de la table T_Batiments

CREATE TABLE T_Batiments
(
Bat_Num INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
Bat_Nom CHAR(20) NOT NULL
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin ;

• Bat_Num, comme pour la table précédente, ne peut pas être nul parce que c’est une clé primaire permettant d’identifier le bâtiment grâce à un numéro.

• Bat_Lieu indique le lieu du bâtiment dont la valeur est une chaîne de 20 caractères maximum et qui ne peut pas être nulle.

X. Création de la table T_Switchs

CREATE TABLE T_Switchs
(
Sw_Num INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
Sw_NumFabr INT NOT NULL ,
Sw_NumBat INT NULL ,
Sw_Modele CHAR(20) NOT NULL ,
Sw_NbPortsMb INT NOT NULL ,
Sw_NbPortsGb INT NOT NULL ,
FOREIGN KEY (Sw_NumFabr) REFERENCES T_Fabricants(F_Num) ,
FOREIGN KEY (Sw_NumBat) REFERENCES T_Batiments(Bat_Num)
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin ;

• Sw_Num est la clé primaire de cette table, qui ne peut pas être nulle et qui d’ailleurs est un numéro qui s’auto incrémente.

• Sw_Modele correspond au modèle de switch dont la valeur maximum est de 20 caractères.

• Sw_NbPortsMb et Sw_NbPortsGb correspondent respectivement au nombre de ports Mbit/s et Gbit/s que contient le switch.

• Sw_NumFabr correspond à un numéro de fabricant, celui-ci devant exister dans la table T_Fabricants étant donné que c'est une clé étrangère sinon ca signifierai que le switch appartient à un fabricant non référencé dans la table. Le champ Sw_NumFabr doit être de même type que le champ F_Num.

• Sw_NumBat correspond au numéro de bâtiment dans lequel est implémenté le switch, ce numéro devant exister dans le champ Bat_Num de la table T_Batiments car c'est une clé étrangère, sinon cela signifierai que le switch se trouve dans un bâtiment qui n'existe pas. Le champ Sw_NumBat doit être de même type que le champ Bat_Num.

XI. Une clé étrangère se définie de la manière suivante

FOREIGN KEY (nom_du_champ) REFERENCES nom_table_référence(nom_champ_référence)

Exemple dans la table T_Switchs : FOREIGN KEY (Sw_NumBat) REFERENCES T_Batiments(Bat_Num)

Cela signifie que le champ Sw_NumBat de la table T_Switchs est une clé étrangère qui se référence au champ Bat_Num de la table T_Batiments, ce qui correspond à cette liaison du MDP :

XII. Importer le script SQL dans PhpMyAdmin

Le script est désormais terminé, il faut maintenant l'importer dans PhpMyAdmin pour vérifier s'il fonctionne correctement. Accédez à l'interface de PhpMyAdmin puis cliquez sur Importer.

Ensuite, indiquez l'emplacement de votre script SQL puis dans le "Jeu de caractères du fichier" sélectionnez latin1 pour que ça corresponde au jeu de caractères de notre script.

Une fois que c'est fait, cliquez sur Exécuter, vous devriez obtenir ceci si vous avez écrit correctement votre script SQL :

XIII. Si vous avez une erreur...

Si vous avez une erreur, ajouter ceci au tout début de votre script avant de le réimporter :

DROP DATABASE IF EXISTS implementation_switchs ;

Cela signifie que si la base de données "implementation_switchs" existe alors on fait une requête pour la supprimer, sinon vous obtiendrez un message d'erreur lorsque vous allez importer votre script une seconde fois.

#1007 - Can't create database 'implementation_switchs'; database exists

XIV. Commentez votre script

Vous pouvez, je dirais même que je vous conseil, de commenter votre script SQL. Pour se faire vous devez écrire "#" au début d'une ligne et tout ce que vous écrirez sur cette ligne ne sera pas pris en compte et sera donc un commentaire. Si vous souhaitez créer une zone de commentaire, commencez là par "/*" puis terminez là par "*/"

Florian BURNEL Co-founder of IT-Connect

Ingénieur système et réseau, cofondateur d'IT-Connect et Microsoft MVP "Cloud and Datacenter Management". Je souhaite partager mon expérience et mes découvertes au travers de mes articles. Généraliste avec une attirance particulière pour les solutions Microsoft et le scripting. Bonne lecture.

See Full Bio