MySQL/Manipulation de table
CREATE TABLE
modifierLa syntaxe de création des tables d'une base est ainsi :
CREATE TABLE tablename (field_name_1 DataType, field_name_2 DataType)
Les bonnes pratiques du nommage des tables sont les mêmes que pour les colonnes, et consistent à évier les mots réservés, les abréviations, et n'utiliser que des minuscules en snake case[1].
Création à partir d'une autre table
modifierLes 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
Auto-incrémentation
modifierDe 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
Moteurs
modifierLe moteur de stockage d'une table est défini par la clause ENGINE=
. On en distingue deux principaux :
Tables d'archive
modifierMySQL 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
modifierIl 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)
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
Toutes les tables temporaires sont supprimées à la fin de la connexion MySQL qui les a créée[2].
Copier une table
modifierPour 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`;
Pour avoir le code de création :
SHOW CREATE TABLE `old1`
CREATE TABLE `new2` LIKE `old1`;
INSERT INTO `new2` SELECT * FROM `old1` WHERE id > 10;
SHOW CREATE TABLE `new2`;
ALTER TABLE
modifierALTER TABLE
sert à ajouter, supprimer ou modifier la structure des tables (colonnes, index, propriétés).
Ajouter une colonne
modifier ALTER TABLE awards
ADD COLUMN AwardCode int(2)
Modifier une colonne
modifierPour changer les caractéristiques :
ALTER TABLE awards
CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL
ALTER TABLE awards
MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL
Renommer une colonne
modifierPour renommer une colonne :
ALTER TABLE awards CHANGE `AwardCode` `newcAwardCode` VARCHAR(2) NOT NULL;
Supprimer une colonne
modifier ALTER TABLE awards
DROP COLUMN AwardCode
Reclasser les enregistrements d'une table
modifier ALTER TABLE awards ORDER BY id
Renommer une table
modifierPour 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
modifierDROP 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
modifierCertains 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) UNSIGNED 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
modifierUne 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)
-- ou
ALTER TABLE MaTable ADD UNIQUE INDEX UNIQ_E6F03AD9A76ED395 (user_id)
La différence entre les deux est la requête utilisée pour l'ajouter[4].
Pour un groupe de champs unique (clé composite) :
ALTER TABLE MaTable ADD PRIMARY KEY (nom, prenom);
L'index unique est déjà un index, donc inutile d'ajouter ADD INDEX sur le même champ de même type en plus[5].
Exemple pour travaux pratiques
modifierSoit 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.
Création d'une base
CREATE DATABASE wiki1;
USE wiki1;
-- Liste des utilisateurs
CREATE TABLE IF NOT EXISTS `wiki1_user` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varbinary(255) NOT NULL DEFAULT '',
`user_real_name` varbinary(255) NOT NULL DEFAULT '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_newpass_time` binary(14) DEFAULT NULL,
`user_email` tinyblob NOT NULL,
`user_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_token` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_email_authenticated` binary(14) DEFAULT NULL,
`user_email_token` binary(32) DEFAULT NULL,
`user_email_token_expires` binary(14) DEFAULT NULL,
`user_registration` binary(14) DEFAULT NULL,
`user_editcount` int(11) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`),
KEY `user_email` (`user_email`(50))
) ENGINE=InnoDB DEFAULT CHARSET=binary AUTO_INCREMENT=41 ;
INSERT INTO `wiki1_user` (`user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_newpass_time`, `user_email`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_editcount`) VALUES
(1, 'Utilisateur1', 'admin', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 1000),
(2, 'Utilisateur2', '', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 800),
(3, 'Bot1', 'admin', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 5000),
(4, 'Utilisateur3', '', '', '', NULL, '', '', '', '', '', '20130102', '20130102', 500),
(5, 'Utilisateur4', '', '', '', NULL, '', '', '', '', '', '20130102', '20130102', 200);
(6, 'Utilisateur5', '', '', '', NULL, '', '', '', '', '', '20130103', '20130103', 200);
-- Liste des pages
CREATE TABLE IF NOT EXISTS `wiki1_page` (
`page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`page_namespace` int(11) NOT NULL,
`page_title` varbinary(255) NOT NULL,
`page_restrictions` tinyblob NOT NULL,
`page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
`page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_random` double unsigned NOT NULL,
`page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`page_latest` int(10) unsigned NOT NULL,
`page_len` int(10) unsigned NOT NULL,
PRIMARY KEY (`page_id`),
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`),
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
) ENGINE=InnoDB DEFAULT CHARSET=binary AUTO_INCREMENT=8;
INSERT INTO `wiki1_page` (`page_id`, `page_namespace`, `page_title`, `page_restrictions`, `page_counter`, `page_is_redirect`, `page_is_new`, `page_random`, `page_touched`, `page_latest`, `page_len`) VALUES
(1, 0, 'Accueil', '', 0, 0, 0, 0, '', 0, 0),
(2, 8, 'Sidebar', '', 0, 0, 0, 0, '', 0, 0),
(3, 0, 'MySQL', '', 0, 0, 0, 0, '', 0, 0),
(4, 0, 'PHP', '', 0, 0, 0, 0, '', 0, 0);
-- Propriétés des pages
CREATE TABLE IF NOT EXISTS `wiki1_page_props` (
`pp_page` int(11) NOT NULL,
`pp_propname` varbinary(60) NOT NULL,
`pp_value` blob NOT NULL,
UNIQUE KEY `pp_page_propname` (`pp_page`,`pp_propname`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
INSERT INTO `wiki1_page_props` (`pp_page`, `pp_propname`, `pp_value`) VALUES
(1, 'noindex', ''),
(2, 'defaultsort', ''),
(2, 'noindex', '');
-- Hyperliens dans les pages
CREATE TABLE IF NOT EXISTS `wiki1_pagelinks` (
`pl_from` int(10) unsigned NOT NULL DEFAULT '0',
`pl_namespace` int(11) NOT NULL DEFAULT '0',
`pl_title` varbinary(255) NOT NULL DEFAULT '',
UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
UNIQUE KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
INSERT INTO `wiki1_pagelinks` (`pl_from`, `pl_namespace`, `pl_title`) VALUES
(1, 0, 'Lien1'),
(3, 0, 'Lien2');
Références
modifier- ↑ https://sql.sh/1396-nom-table-colonne
- ↑ http://www.mysqltutorial.org/mysql-temporary-table/
- ↑ http://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html
- ↑ https://board.phpbuilder.com/d/10271323-phppgadmin-add-unique-index-vs-add-unique-key
- ↑ https://stackoverflow.com/questions/9393234/mysql-unique-field-needs-to-be-an-index