Ceci est une ancienne révision du document !
Commandes MySQL
Connexion
mysql ‐u nomutilisateur ‐p SHOW DATABASES; USE nomdelabdd; SHOW TABLES; DESCRIBE nomdelatable;
Actions sur bases
CREATE DATABASE nomdelabdd; DROP DATABASE nomdelabdd;
CREATE TABLE nomdelatable ( nomcolonne1 INT, nomcolonne2 FLOAT, nomcolonne3 VARCHAR(50), nomcolonne3 CHAR(50) );
DROP TABLE nomdelatable;
DELETE FROM nom_de_la_table WHERE nom_de_l'entree=“valeur de l'entre”
Se connecter à MySQL en ligne de commande sous Linux :
mysql ‐u nomutilisateur ‐p
Afficher les bases de données MySQL existantes :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>SHOW</font> <font inherit/inherit;;#000000;;inherit>DATABASES</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Se placer et utiliser une base de données :
USE<font inherit/inherit;;#f8f8f8 text-decoration: underline;;inherit></font>nomdelabdd;
Afficher les tables de cette base de données :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>SHOW</font> <font inherit/inherit;;#000000;;inherit>TABLES</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Afficher la structure (colonnes) d'une table de la base de données :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>DESCRIBE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Manipulation d'une base de données MySQL
Création de la base de données :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>CREATE</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>DATABASE</font> <font inherit/inherit;;#000000;;inherit>nomdelabdd</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Supprimer la base de données :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>DROP</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>DATABASE</font> <font inherit/inherit;;#000000;;inherit>nomdelabdd</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Manipulation des tables d'une base de données MySQL
Création d'une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>CREATE</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font> <font inherit/inherit;;#000000;;inherit>nomcolonne1</font> <font inherit/inherit;;#204a87;;inherit>INT</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font> <font inherit/inherit;;#000000;;inherit>nomcolonne2</font> <font inherit/inherit;;#204a87;;inherit>FLOAT</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font> <font inherit/inherit;;#000000;;inherit>nomcolonne3</font> <font inherit/inherit;;#204a87;;inherit>VARCHAR</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#0000cf font-weight: bold;;inherit>50</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>),</font> <font inherit/inherit;;#000000;;inherit>nomcolonne3</font> <font inherit/inherit;;#204a87;;inherit>CHAR</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#0000cf font-weight: bold;;inherit>50</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>)</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>);</font>
Supprimer une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>DROP</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Modification d'une table
Ajouter une colonne à une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ADD</font> <font inherit/inherit;;#000000;;inherit>nomdecolonne</font> <font inherit/inherit;;#204a87;;inherit>INT</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ADD</font> <font inherit/inherit;;#000000;;inherit>nomdecolonne1</font> <font inherit/inherit;;#204a87;;inherit>VARCHAR</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#0000cf font-weight: bold;;inherit>50</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>);</font>
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ADD</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>COLUMN</font> <font inherit/inherit;;#000000;;inherit>nomdelacolonne</font> <font inherit/inherit;;#204a87;;inherit>INT</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Supprimer une colonne d'une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>DROP</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>COLUMN</font> <font inherit/inherit;;#000000;;inherit>nomdecolonne1</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Renommer / changer le nom d'une colonne de la table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>RENAME</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>AS</font> <font inherit/inherit;;#000000;;inherit>lenouveaunomdelatable</font>
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomancientable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>RENAME</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TO</font> <font inherit/inherit;;#000000;;inherit>nomnouveautable</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>RENAME</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomancientable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TO</font> <font inherit/inherit;;#000000;;inherit>nomnouveautable</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Renommer / changer le type d'une colonne :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#000000;;inherit>CHANGE</font> <font inherit/inherit;;#000000;;inherit>nomdelacolonne</font> <font inherit/inherit;;#000000;;inherit>nouveaunomdelacolonne</font> <font inherit/inherit;;#204a87;;inherit>INT</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Ajouter une contrainte de clé primaire sur une colonne d'une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ADD</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>PRIMARY</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>KEY</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#000000;;inherit>nomcolonne</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>);</font>
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ADD</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>CONSTRAINT</font> <font inherit/inherit;;#000000;;inherit>pk_nomdelacontrainte</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>PRIMARY</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>KEY</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#000000;;inherit>nomcolonne</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>);</font>
Supprimer une contrainte de clé primaire sur une colonne :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>DROP</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>PRIMARY</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>KEY</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Ajouter une contrainte de clé étrangère sur une colonne d'une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ADD</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>CONSTRAINT</font> <font inherit/inherit;;#000000;;inherit>fk_nomdelacontrainte</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FOREIGN</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>KEY</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#000000;;inherit>nomcolonne</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>)</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>REFERENCES</font> <font inherit/inherit;;#000000;;inherit>tabledereference</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#000000;;inherit>nomcolonne</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>);</font>
Supprimer une contrainte de clé étrangère sur une colonne d'une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>ALTER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>TABLE</font> <font inherit/inherit;;#000000;;inherit>nomdelatable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>DROP</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>CONSTRAINT</font> <font inherit/inherit;;#000000;;inherit>fk_nomdelacontrainte</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Requêtes SQL de base
Afficher / sélectionner des enregistrements d'une table SQL :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>SELECT</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FROM</font> <font inherit/inherit;;#000000;;inherit>nomtable</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Afficher / sélectionner des enregistrements d'une table SQL de façon distincte :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>SELECT</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>DISTINCT</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FROM</font> <font inherit/inherit;;#000000;;inherit>nomtable</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Afficher / sélectionner des enregistrements d'une table SQL avec une condition :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>SELECT</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FROM</font> <font inherit/inherit;;#000000;;inherit>nomtable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>WHERE</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font> <font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font> <font inherit/inherit;;#4e9a06;;inherit>'1'</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>SELECT</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FROM</font> <font inherit/inherit;;#000000;;inherit>nomtable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>WHERE</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>LIKE</font> <font inherit/inherit;;#4e9a06;;inherit>'%.com'</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>;</font>
Afficher / sélectionner des enregistrements de plusieurs tables via une jointure internet :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>SELECT</font> <font inherit/inherit;;#000000;;inherit>colonne1</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font> <font inherit/inherit;;#000000;;inherit>colonne2</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font> <font inherit/inherit;;#000000;;inherit>colonne3</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FROM</font> <font inherit/inherit;;#000000;;inherit>table1</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>INNER</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>JOIN</font> <font inherit/inherit;;#000000;;inherit>table2</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>ON</font> <font inherit/inherit;;#000000;;inherit>table1</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>.</font><font inherit/inherit;;#000000;;inherit>colonne1</font><font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#000000;;inherit>table2</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>.</font><font inherit/inherit;;#000000;;inherit>colonne3</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>WHERE</font> <font inherit/inherit;;#000000;;inherit>colonne1</font> <font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font> <font inherit/inherit;;#4e9a06;;inherit>'cequejecherche'</font>
Mise à jour de l'ensemble des enregistrements sur une colonne d'une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>UPDATE</font> <font inherit/inherit;;#000000;;inherit>nomtable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>SET</font> <font inherit/inherit;;#000000;;inherit>nomcolonne1</font><font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#4e9a06;;inherit>'mavaleur'</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font><font inherit/inherit;;#000000;;inherit>nomcolonne2</font><font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#4e9a06;;inherit>'mavaleur2'</font>
Mise à jour de l'ensemble des enregistrements sur une colonne d'une table avec une condition :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>UPDATE</font> <font inherit/inherit;;#000000;;inherit>nomtable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>SET</font> <font inherit/inherit;;#000000;;inherit>nomcolonne1</font><font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#4e9a06;;inherit>'mavaleur'</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font><font inherit/inherit;;#000000;;inherit>nomcolonne2</font><font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#4e9a06;;inherit>'mavaleur2'</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>WHERE</font> <font inherit/inherit;;#000000;;inherit>nomcolonne3</font> <font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#4e9a06;;inherit>'0'</font>
Insérer un enregistrement dans une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>INSERT</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>INTO</font> <font inherit/inherit;;#000000;;inherit>nomtable</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#000000;;inherit>colonne1</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font><font inherit/inherit;;#000000;;inherit>colonne2</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font><font inherit/inherit;;#000000;;inherit>colonne3</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>)</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>VALUES</font> <font inherit/inherit;;#000000 font-weight: bold;;inherit>(</font><font inherit/inherit;;#000000;;inherit>valeu1</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font><font inherit/inherit;;#000000;;inherit>valeur2</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>,</font><font inherit/inherit;;#000000;;inherit>valeur3</font><font inherit/inherit;;#000000 font-weight: bold;;inherit>);</font>
Supprimer un enregistrement dans une table :
<font inherit/inherit;;#204a87 font-weight: bold;;inherit>DELETE</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>FROM</font> <font inherit/inherit;;#000000;;inherit>nomtable</font> <font inherit/inherit;;#204a87 font-weight: bold;;inherit>WHERE</font> <font inherit/inherit;;#000000;;inherit>nomcolonne</font><font inherit/inherit;;#ce5c00 font-weight: bold;;inherit>=</font><font inherit/inherit;;#4e9a06;;inherit>'lavaleur'</font>