Insertion de données

• Modification d'une table
• Sommaire
• Sélection de données

Ce chapitre est consacré à l'insertion de données dans une table. Rien de bien compliqué, mais c'est évidemment crucial. En effet, que serait une base de données sans données ?

Nous verrons entre autres :

• comment insérer une ligne dans une table ;
• comment insérer plusieurs lignes dans une table ;
• comment exécuter des requêtes SQL écrites dans un fichier (requêtes d'insertion ou autres) ;
• comment insérer dans une table des lignes définies dans un fichier de format particulier.

Et pour terminer, nous peuplerons notre table Animal d'une soixantaine de petites bestioles sur lesquelles nous pourrons tester toutes sortes de tortures requêtes dans la suite de ce tutoriel.

• Syntaxe de INSERT
• Syntaxe alternative de MySQL
• Utilisation de fichiers externes
• Remplissage de la base

Syntaxe de INSERT

Deux possibilités s'offrent à nous lorsque l'on veut insérer une ligne dans une table : soit donner une valeur pour chaque colonne de la ligne, soit ne donner les valeurs que de certaines colonnes, auquel cas il faut bien sûr préciser de quelles colonnes il s'agit.

Insertion sans préciser les colonnes

Je rappelle pour les distraits que notre table Animal est composée de six colonnes : id, espece, sexe, date_naissance, nom et commentaires.

Voici donc la syntaxe à utiliser pour insérer une ligne dans Animal, sans renseigner les colonnes pour lesquelles on donne une valeur (implicitement, MySQL considère que l'on donne une valeur pour chaque colonne de la table).

1
2

INSERT INTO Animal 
VALUES (1, 'chien', 'M', '2010-04-05 13:43:00', 'Rox', 'Mordille beaucoup');

Deuxième exemple : cette fois-ci, on ne connaît pas le sexe et on n'a aucun commentaire à faire sur la bestiole :

1
2

INSERT INTO Animal 
VALUES (2, 'chat', NULL, '2010-03-24 02:23:00', 'Roucky', NULL);

Troisième et dernier exemple : on donne NULL comme valeur d'id, ce qui en principe est impossible puisque id est défini comme NOT NULL et comme clé primaire. Cependant, l'auto-incrémentation fait que MySQL va calculer tout seul comme un grand quel id il faut donner à la ligne (ici : 3).

1
2

INSERT INTO Animal 
VALUES (NULL , 'chat', 'F', '2010-09-13 15:02:00', 'Schtroumpfette', NULL);

Vous avez maintenant trois animaux dans votre table :

Pour vérifier, vous pouvez utiliser la requête suivante :

1

SELECT * FROM Animal;

Deux choses importantes à retenir ici.

• id est un nombre, on ne met donc pas de guillemets autour. Par contre, l'espèce, le nom, la date de naissance et le sexe sont donnés sous forme de chaînes de caractères. Les guillemets sont donc indispensables. Quant à NULL, il s'agit d'un marqueur SQL qui, je rappelle, signifie "pas de valeur". Pas de guillemets donc.
• Les valeurs des colonnes sont données dans le bon ordre (donc dans l'ordre donné lors de la création de la table). C'est indispensable évidemment. Si vous échangez le nom et l'espèce par exemple, comment MySQL pourrait-il le savoir ?

Insertion en précisant les colonnes

Dans la requête, nous allons donc écrire explicitement à quelle(s) colonne(s) nous donnons une valeur. Ceci va permettre deux choses.

• On ne doit plus donner les valeurs dans l'ordre de création des colonnes, mais dans l'ordre précisé par la requête.
• On n'est plus obligé de donner une valeur à chaque colonne ; plus besoin de NULL lorsqu'on n'a pas de valeur à mettre.

Quelques exemples :

1
2
3
4
5
6

INSERT INTO Animal (espece, sexe, date_naissance) 
    VALUES ('tortue', 'F', '2009-08-03 05:12:00');
INSERT INTO Animal (nom, commentaires, date_naissance, espece) 
    VALUES ('Choupi', 'Né sans oreille gauche', '2010-10-03 16:44:00', 'chat');
INSERT INTO Animal (espece, date_naissance, commentaires, nom, sexe) 
    VALUES ('tortue', '2009-06-13 08:17:00', 'Carapace bizarre', 'Bobosse', 'F');

Ce qui vous donne trois animaux supplémentaires (donc six en tout, il faut suivre !)

Insertion multiple

Si vous avez plusieurs lignes à introduire, il est possible de le faire en une seule requête de la manière suivante :

1
2
3
4

INSERT INTO Animal (espece, sexe, date_naissance, nom) 
VALUES ('chien', 'F', '2008-12-06 05:18:00', 'Caroline'),
        ('chat', 'M', '2008-09-11 15:38:00', 'Bagherra'),
        ('tortue', NULL, '2010-08-23 05:18:00', NULL);

Bien entendu, vous êtes alors obligés de préciser les mêmes colonnes pour chaque entrée, quitte à mettre NULL pour certaines. Mais avouez que ça fait quand même moins à écrire !

Syntaxe alternative de MySQL

MySQL propose une syntaxe alternative à INSERT INTO ... VALUES ... pour insérer des données dans une table.

1
2

INSERT INTO Animal 
SET nom='Bobo', espece='chien', sexe='M', date_naissance='2010-07-21 15:41:00';

Cette syntaxe présente deux avantages.

• Le fait d'avoir l'un à côté de l'autre la colonne et la valeur qu'on lui attribue (nom = 'Bobo') rend la syntaxe plus lisible et plus facile à manipuler. En effet, ici il n'y a que six colonnes, mais imaginez une table avec 20, voire 100 colonnes. Difficile d'être sûrs que l'ordre dans lequel on a déclaré les colonnes est bien le même que l'ordre des valeurs qu'on leur donne…
• Elle est très semblable à la syntaxe de UPDATE, que nous verrons plus tard et qui permet de modifier des données existantes. C'est donc moins de choses à retenir (mais bon, une requête de plus ou de moins, ce n'est pas non plus énorme…)

En effet, cette syntaxe présente deux défauts majeurs.

• Elle est propre à MySQL. Ce n'est pas du SQL pur. De ce fait, si vous décidez un jour de migrer votre base vers un autre SGBDR, vous devrez réécrire toutes les requêtes INSERT utilisant cette syntaxe.
• Elle ne permet pas l'insertion multiple.

Utilisation de fichiers externes

Maintenant que vous savez insérer des données, je vous propose de remplir un peu cette table, histoire qu'on puisse s'amuser par la suite.

Rassurez-vous, je ne vais pas vous demander d'inventer cinquante bestioles et d'écrire une à une les requêtes permettant de les insérer. Je vous ai prémâché le boulot. De plus, ça nous permettra d'avoir, vous et moi, la même chose dans notre base. Ce sera ainsi plus facile de vérifier que vos requêtes font bien ce qu'elles doivent faire.

Et pour éviter d'écrire vous-mêmes toutes les requêtes d'insertion, nous allons donc voir comment on peut utiliser un fichier texte pour interagir avec notre base de données.

Exécuter des commandes SQL à partir d'un fichier

Écrire toutes les commandes à la main dans la console, ça peut vite devenir pénible. Quand c'est une petite requête, pas de problème. Mais quand vous avez une longue requête, ou beaucoup de requêtes à faire, ça peut être assez long.

Une solution sympathique est d'écrire les requêtes dans un fichier texte, puis de dire à MySQL d'exécuter les requêtes contenues dans ce fichier. Et pour lui dire ça, c'est facile :

1

SOURCE monFichier.sql;

Ou

1

\. monFichier.sql;

Ces deux commandes sont équivalentes et vont exécuter le fichier monFichier.sql. Il n'est pas indispensable de lui donner l'extension .sql, mais je préfère le faire pour repérer mes fichiers SQL directement. De plus, si vous utilisez un éditeur de texte un peu plus évolué que le bloc-note (ou textEdit sur Mac), cela colorera votre code SQL, ce qui vous facilitera aussi les choses.

Attention : si vous ne lui indiquez pas le chemin, MySQL va aller chercher votre fichier dans le dossier où vous étiez lors de votre connexion.

Exemple : on donne le chemin complet vers le fichier

1

SOURCE C:\Document and Settings\dossierX\monFichier.sql;

Insérer des données à partir d'un fichier formaté

Par fichier formaté, j'entends un fichier qui suit certaines règles de format. Un exemple typique serait les fichiers .csv. Ces fichiers contiennent un certain nombre de données et sont organisés en tables. Chaque ligne correspond à une entrée, et les colonnes de la table sont séparées par un caractère défini (souvent une virgule ou un point-virgule). Ceci par exemple, est un format csv :

1
2
3
4

nom;prenom;date_naissance
Charles;Myeur;1994-12-30
Bruno;Debor;1978-05-12
Mireille;Franelli;1990-08-23

Ce type de fichier est facile à produire (et à lire) avec un logiciel de type tableur (Microsoft Excel, ExcelViewer, Numbers…). La bonne nouvelle est qu'il est aussi possible de lire ce type de fichier avec MySQL, afin de remplir une table avec les données contenues dans le fichier.

La commande SQL permettant cela est LOAD DATA INFILE, dont voici la syntaxe :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

LOAD DATA [LOCAL] INFILE 'nom_fichier'
INTO TABLE nom_table
[FIELDS
    [TERMINATED BY '\t']
    [ENCLOSED BY '']
    [ESCAPED BY '\\' ]
]
[LINES 
    [STARTING BY '']    
    [TERMINATED BY '\n']
]
[IGNORE nombre LINES]
[(nom_colonne,...)];

Le mot-clé LOCAL sert à spécifier si le fichier se trouve côté client (dans ce cas, on utilise LOCAL) ou côté serveur (auquel cas, on ne met pas LOCAL dans la commande). Si le fichier se trouve du côté serveur, il est obligatoire, pour des raisons de sécurité, qu'il soit dans le répertoire de la base de données, c'est-à-dire dans le répertoire créé par MySQL à la création de la base de données, et qui contient les fichiers dans lesquels sont stockées les données de la base. Pour ma part, j'utiliserai toujours LOCAL, afin de pouvoir mettre simplement mes fichiers dans mon dossier de travail.

Les clauses FIELDS et LINES permettent de définir le format de fichier utilisé. FIELDS se rapporte aux colonnes, et LINES aux lignes (si si ). Ces deux clauses sont facultatives. Les valeurs que j'ai mises ci-dessus sont les valeurs par défaut.

Si vous précisez une clause FIELDS, il faut lui donner au moins une des trois "sous-clauses".

• TERMINATED BY, qui définit le caractère séparant les colonnes, entre guillemets bien sûr. '\t' correspond à une tabulation. C'est le caractère par défaut.
• ENCLOSED BY, qui définit le caractère entourant les valeurs dans chaque colonne (vide par défaut).
• ESCAPED BY, qui définit le caractère d'échappement pour les caractères spéciaux. Si par exemple vous définissez vos valeurs comme entourées d'apostrophes, mais que certaines valeurs contiennent des apostrophes, il faut échapper ces apostrophes "internes" afin qu'elles ne soient pas considérées comme un début ou une fin de valeur. Par défaut, il s'agit du \ habituel. Remarquez qu'il faut lui-même l'échapper dans la clause.

De même pour LINES, si vous l'utilisez, il faut lui donner une ou deux sous-clauses.

• STARTING BY, qui définit le caractère de début de ligne (vide par défaut).
• TERMINATED BY, qui définit le caractère de fin de ligne ('\n' par défaut, mais attention : les fichiers générés sous Windows ont souvent '\r\n' comme caractère de fin de ligne).

La clause IGNORE nombre LINES permet… d'ignorer un certain nombre de lignes. Par exemple, si la première ligne de votre fichier contient les noms des colonnes, vous ne voulez pas l'insérer dans votre table. Il suffit alors d'utiliser IGNORE 1 LINES.

Enfin, vous pouvez préciser le nom des colonnes présentes dans votre fichier. Attention évidemment à ce que les colonnes absentes acceptent NULL ou soient auto-incrémentées.

Si je reprends mon exemple, en imaginant que nous ayons une table Personne contenant les colonnes id (clé primaire auto-incrémentée), nom, prenom, date_naissance et adresse (qui peut être NULL).

1
2
3
4

nom;prenom;date_naissance
Charles;Myeur;1994-12-30
Bruno;Debor;1978-05-12
Mireille;Franelli;1990-08-23

Si ce fichier est enregistré sous le nom personne.csv, il vous suffit d'exécuter la commande suivante pour enregistrer ces trois lignes dans la table Personne, en spécifiant si nécessaire le chemin complet vers personne.csv :

1
2
3
4
5
6

LOAD DATA LOCAL INFILE 'personne.csv'
INTO TABLE Personne
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n' -- ou '\r\n' selon l'ordinateur et le programme utilisés pour créer le fichier
IGNORE 1 LINES
(nom,prenom,date_naissance);

Remplissage de la base

Nous allons utiliser les deux techniques que je viens de vous montrer pour remplir un peu notre base. N'oubliez pas de modifier les commandes données pour ajouter le chemin vers vos fichiers,

Exécution de commandes SQL

Voici donc le code que je vous demande de copier-coller dans votre éditeur de texte préféré, puis de le sauver sous le nom remplissageAnimal.sql (ou un autre nom de votre choix).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

INSERT INTO Animal (espece, sexe, date_naissance, nom, commentaires) VALUES 
('chien', 'F', '2008-02-20 15:45:00' , 'Canaille', NULL),
('chien', 'F','2009-05-26 08:54:00'  , 'Cali', NULL),
('chien', 'F','2007-04-24 12:54:00' , 'Rouquine', NULL),
('chien', 'F','2009-05-26 08:56:00' , 'Fila', NULL),
('chien', 'F','2008-02-20 15:47:00' , 'Anya', NULL),
('chien', 'F','2009-05-26 08:50:00' ,'Louya' , NULL),
('chien', 'F', '2008-03-10 13:45:00','Welva' , NULL),
('chien', 'F','2007-04-24 12:59:00' ,'Zira' , NULL),
('chien', 'F', '2009-05-26 09:02:00','Java' , NULL),
('chien', 'M','2007-04-24 12:45:00' ,'Balou' , NULL),
('chien', 'M','2008-03-10 13:43:00' ,'Pataud' , NULL),
('chien', 'M','2007-04-24 12:42:00' , 'Bouli', NULL),
('chien', 'M', '2009-03-05 13:54:00','Zoulou' , NULL),
('chien', 'M','2007-04-12 05:23:00' ,'Cartouche' , NULL),
('chien', 'M', '2006-05-14 15:50:00', 'Zambo', NULL),
('chien', 'M','2006-05-14 15:48:00' ,'Samba' , NULL),
('chien', 'M', '2008-03-10 13:40:00','Moka' , NULL),
('chien', 'M', '2006-05-14 15:40:00','Pilou' , NULL),
('chat', 'M','2009-05-14 06:30:00' , 'Fiero', NULL),
('chat', 'M','2007-03-12 12:05:00' ,'Zonko', NULL),
('chat', 'M','2008-02-20 15:45:00' , 'Filou', NULL),
('chat', 'M','2007-03-12 12:07:00' , 'Farceur', NULL),
('chat', 'M','2006-05-19 16:17:00' ,'Caribou' , NULL),
('chat', 'M','2008-04-20 03:22:00' , 'Capou', NULL),
('chat', 'M','2006-05-19 16:56:00' , 'Raccou', 'Pas de queue depuis la naissance');

Vous n'avez alors qu'à taper :

1

SOURCE remplissageAnimal.sql;

LOAD DATA INFILE

À nouveau, copiez-collez le texte ci-dessous dans votre éditeur de texte, et enregistrez le fichier. Cette fois, sous le nom animal.csv.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

"chat";"M";"2009-05-14 06:42:00";"Boucan";NULL
"chat";"F";"2006-05-19 16:06:00";"Callune";NULL
"chat";"F";"2009-05-14 06:45:00";"Boule";NULL
"chat";"F";"2008-04-20 03:26:00";"Zara";NULL
"chat";"F";"2007-03-12 12:00:00";"Milla";NULL
"chat";"F";"2006-05-19 15:59:00";"Feta";NULL
"chat";"F";"2008-04-20 03:20:00";"Bilba";"Sourde de l'oreille droite à 80%"
"chat";"F";"2007-03-12 11:54:00";"Cracotte";NULL
"chat";"F";"2006-05-19 16:16:00";"Cawette";NULL
"tortue";"F";"2007-04-01 18:17:00";"Nikki";NULL
"tortue";"F";"2009-03-24 08:23:00";"Tortilla";NULL
"tortue";"F";"2009-03-26 01:24:00";"Scroupy";NULL
"tortue";"F";"2006-03-15 14:56:00";"Lulla";NULL
"tortue";"F";"2008-03-15 12:02:00";"Dana";NULL
"tortue";"F";"2009-05-25 19:57:00";"Cheli";NULL
"tortue";"F";"2007-04-01 03:54:00";"Chicaca";NULL
"tortue";"F";"2006-03-15 14:26:00";"Redbul";"Insomniaque"
"tortue";"M";"2007-04-02 01:45:00";"Spoutnik";NULL
"tortue";"M";"2008-03-16 08:20:00";"Bubulle";NULL
"tortue";"M";"2008-03-15 18:45:00";"Relou";"Surpoids"
"tortue";"M";"2009-05-25 18:54:00";"Bulbizard";NULL
"perroquet";"M";"2007-03-04 19:36:00";"Safran";NULL
"perroquet";"M";"2008-02-20 02:50:00";"Gingko";NULL
"perroquet";"M";"2009-03-26 08:28:00";"Bavard";NULL
"perroquet";"F";"2009-03-26 07:55:00";"Parlotte";NULL

Exécutez ensuite la commande suivante :

1
2
3
4
5

LOAD DATA LOCAL INFILE 'animal.csv'
INTO TABLE Animal
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\n' -- ou '\r\n' selon l'ordinateur et le programme utilisés pour créer le fichier
(espece, sexe, date_naissance, nom, commentaires);

Et hop ! Vous avez plus d'une cinquantaine d'animaux dans votre table.

Si vous voulez vérifier, je rappelle que vous pouvez utiliser la commande suivante, qui vous affichera toutes les données contenues dans la table Animal.

1

SELECT * FROM Animal;

Nous pouvons maintenant passer au chapitre suivant !

En résumé

• Pour insérer des lignes dans une table, on utilise la commande

1

INSERT INTO nom_table [(colonne1, colonne2, ...)] VALUES (valeur1, valeur2, ...);

• Si l'on ne précise pas à quelles colonnes on donne une valeur, il faut donner une valeur à toutes les colonnes, et dans le bon ordre.
• Il est possible d'insérer plusieurs lignes en une fois, en séparant les listes de valeurs par une virgule.
• Si l'on a un fichier texte contenant des requêtes SQL, on peut l'exécuter en utilisant SOURCE nom_fichier; ou \. nom_fichier;.
• La commande LOAD DATA [LOCAL] INFILE permet de charger des données dans une table à partir d'un fichier formaté (.csv par exemple).

• Modification d'une table
• Sommaire
• Sélection de données