MySQL/Manipulation de table

Un livre de Wikilivres.
Aller à : navigation, rechercher


CREATE TABLE[modifier | modifier le wikicode]

La syntaxe de création des tables d'une base est ainsi :

 Create table tablename (FieldName1 DataType, FieldName2 DataType)

Les enregistrements de la requête SELECT peuvent être enregistrés dans une nouvelle table. Les types des données seront les mêmes que dans l'ancienne table. Exemple :

 CREATE TABLE LearnHindi
 select english.tag, english.Inenglish as english, hindi.Inhindi as hindi
 FROM english, hindi
 WHERE english.tag = hindi.tag

De plus, MySQL peut assurer l'auto-incrémentation des clés uniques grâce à l'option AUTO_INCREMENT. En cas de troncature de la table, le compteur peut être réinitialiser avec :

ALTER TABLE tablename AUTO_INCREMENT = 1

Tables d'archive[modifier | modifier le wikicode]

MySQL propose un type de table d'archive, prenant moins de place (par compression) mais dont on ne peut pas supprimer les enregistrements une fois ajoutés :

create table t1 (
 a int,
 b varchar(32))
ENGINE=ARCHIVE

Tables temporaires[modifier | modifier le wikicode]

Il est possible de créer des variables de type table, qui seront effacées à la fin de leurs scripts. On les appelle "tables temporaires" :

 CREATE TEMPORARY TABLE IF NOT EXISTS MaTableTemp1 AS (SELECT * FROM MaTable1)

Exemple avec paramètre nommé :

 CREATE TEMPORARY TABLE IF NOT EXISTS MaTableTemp1(id INT) AS (SELECT id FROM MaTable1)
link={{{link}}}Attention !

Si le nom de la colonne ne correspond pas au nom du champ sélectionné, la table temporaire se voit ajouter une colonne du nom de ce champ. Ex :

 CREATE TEMPORARY TABLE IF NOT EXISTS MaTableTemp1(id1 INT) AS (SELECT id FROM MaTable1);
 SHOW FIELDS FROM MaTableTemp1;
Field      Type    Null    Key     Default         Extra   
id1     int(11)         YES             NULL    
id      int(11)         NO              0
link={{{link}}}Attention !

Toutes les tables temporaires sont supprimées à la fin de la connexion MySQL qui les a créée[1].

Copier une table[modifier | modifier le wikicode]

Pour obtenir la même structure (noms et types des champs, index, mais aucun enregistrement) :

 CREATE TABLE `new1` LIKE `old1`;

Pour dupliquer le contenu d'une table dans le résultat :

 INSERT INTO `new1` SELECT * FROM `old1`;
 la limite de taille pour une table dépend du système de fichier, elle est généralement de 2 To[2]

ALTER TABLE[modifier | modifier le wikicode]

ALTER TABLE sert à ajouter, supprimer ou modifier la structure des tables (colonnes, index, propriétés).

Ajouter une colonne[modifier | modifier le wikicode]

 ALTER TABLE awards
 ADD COLUMN AwardCode int(2)

Modifier une colonne[modifier | modifier le wikicode]

 ALTER TABLE awards
 CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL

 ALTER TABLE awards
 MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL

Supprimer une colonne[modifier | modifier le wikicode]

 ALTER TABLE awards
 DROP COLUMN AwardCode

Reclasser les enregistrements d'une table[modifier | modifier le wikicode]

 ALTER TABLE awards ORDER BY id
 cette opération n'est pas supportée par tous les moteurs de stockage. Elle peut accélérer certaines requêtes.

Renommer une table[modifier | modifier le wikicode]

Pour renommer une table, il faut préalablement retirer ses privilèges avec ALTER et DROP, puis CREATE et INSERT pour ceux à attribuer à la nouvelle table.

Renommage :

 ALTER TABLE `old` RENAME `new`

Raccourci :

 RENAME TABLE `old_name` TO `new_name`

Plusieurs :

 RENAME TABLE `old1` TO `new1`, `old2` TO `new2`, ...

La différence entre ALTER TABLE et RENAME est que seul le premier peut renommer les tables temporaires, mais il n'en permet qu'un par requête.


DROP TABLE[modifier | modifier le wikicode]

 DROP TABLE `awards`

Supprime toute la table (enregistrements et structure).

Plusieurs :

 DROP TABLE `table1`, `table2`, ...

Avec vérification :

 DROP TEMPORARY TABLE `table`;
 DROP TABLE `table` IF EXISTS;

CASCADE[modifier | modifier le wikicode]

Certains enregistrements d'une base de données relationnelle peuvent devenir inutiles si ceux qui leur sont joints viennent à disparaitre.

C'est par exemple le cas dans une table "adresse de facturation" où il n'y n'aurait plus de personne physique ou morale associée, c'est-à-dire qu'il existerait en mémoire une ligne avec un id utilisateur pointant vers une ligne de la table "utilisateur" qui n'existe plus.

Pour éviter d'avoir à maintenir ces reliquats, MySQL offre la possibilité de les supprimer automatiquement "en cascade", au moment où ceux qui leur sont joints sont effacés. Cela se définit par dessus la contrainte d'intégrité FOREIGN KEY.

Exemple :

CREATE TABLE adresse_facturation (
  id int(11) NOT NULL AUTO_INCREMENT,
  id_utilisateur int(11) NOT NULL,
  adresse varchar(255),
  PRIMARY KEY (id),
  FOREIGN KEY (id_utilisateur) 
  REFERENCES utilisateur (id) 
  ON DELETE CASCADE
)

Unique[modifier | modifier le wikicode]

Une autre contrainte d'intégrité qui permet de forcer chaque valeur d'un champ à être différentes est UNIQUE :

Pour ajouter une contrainte unique du nom de la colonne concernée :

ALTER TABLE MaTable ADD UNIQUE (user_id)

Pour ajouter une contrainte unique nommée :

ALTER TABLE MaTable ADD UNIQUE KEY UNIQ_E6F03AD9A76ED395 (user_id)

Exemple pour travaux pratiques[modifier | modifier le wikicode]

Soit l'exemple suivant qui sera utilisé pour les sélections ensuite (toute ressemblance avec un framework connu est purement non fortuite : si vous avez déjà votre propre wiki, il est possible de sauter cette phase pour passer directement au paragraphe SELECT).

NB : le type VARBINARY est équivalent à VARCHAR, mais il faut savoir qu'il stocke la chaine de caractères sous sa forme binaire, et donc prend moins de place.

Références[modifier | modifier le wikicode]

  1. http://www.mysqltutorial.org/mysql-temporary-table/
  2. http://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html