MySQL/Aide-mémoire

Un livre de Wikibooks.

Cet aide-mémoire pour MySQL recense les commandes usuelles.

Sections 1 Visualisation 1.1 Conditions 2 Modification 3 Naviguer dans MySQL 4 Créer / supprimer une base 5 Créer/supprimer/modifier une table 6 Clefs 7 Permissions 8 Types de données principaux 9 Oubli de mot de passe oublié 10 Réparer les tables après un arrêt soudain

[modifier] Visualisation

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT champ1, champ2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY champ
SELECT ... FROM ... WHERE condition GROUPBY champ HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY champ1, champ2
SELECT ... FROM ... WHERE condition ORDER BY champ1, champ2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT champ1 FROM ...
SELECT DISTINCT champ1, champ2 FROM ...

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

[modifier] Conditions

champ1 = valeur1
champ1 <> valeur1
champ1 LIKE 'valeur _ %'
champ1 IS NULL
champ1 IS NOT NULL
champ1 IS IN (valeur1, valeur2)
champ1 IS NOT IN (valeur1, valeur2)
condition1 AND condition2
condition1 OR condition2

[modifier] Modification

INSERT INTO table1 (champ1, champ2, ...) VALUES (valeur1, valeur2, ...)

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
-- jointure:
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

UPDATE table1 SET champ1=nouvelle_valeur1 WHERE condition
-- jointure:
UPDATE table1, table2 SET champ1=nouvelle_valeur1, champ2=nouvelle_valeur2, ... WHERE table1.id1 = table2.id2 AND condition

[modifier] Naviguer dans MySQL

SHOW DATABASES
SHOW TABLES
SHOW FIELDS FROM table / DESCRIBE table
SHOW CREATE TABLE table
SHOW PROCESSLIST
KILL numero
USE ma_bdd

[modifier] Créer / supprimer une base

CREATE DATABASE mabase
CREATE DATABASE mabase CHARACTER SET utf8
DROP DATABASE mabase

ALTER DATABASE mabase CHARACTER SET utf8

[modifier] Créer/supprimer/modifier une table

CREATE TABLE table (champ1 type1, champ2 type2, ...)
CREATE TABLE table (champ1 type1, champ2 type2, ..., INDEX (champ))
CREATE TABLE table (champ1 type1, champ2 type2, ..., PRIMARY KEY (champ1))
CREATE TABLE table (champ1 type1, champ2 type2, ..., PRIMARY KEY (champ1, champ2))
CREATE TABLE table1 (fk_champ1 type1, champ2 type2, ...,
  FOREIGN KEY (fk_champ1) REFERENCES table2 (t2_champA))
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
CREATE TABLE table1 (fk_champ1 type1, fk_champ2 type2, ...,
  FOREIGN KEY (fk_champ1, fk_champ2) REFERENCES table2 (t2_champA, t2_champB))
CREATE TABLE table IF NOT EXISTS (...)

CREATE TEMPORARY TABLE table (...)

DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...

ALTER TABLE table ADD (champ1 type1, champ2 type2, ...)
ALTER TABLE table MODIFY champ1 type1 
ALTER TABLE table MODIFY champ1 type1 NOT NULL ... 
ALTER TABLE table CHANGE ancien_nom_champ1 nouveau_nom_champ1 type1
ALTER TABLE table CHANGE ancien_nom_champ1 nouveau_nom_champ1 type1 NOT NULL ...
ALTER TABLE table ALTER champ1 SET DEFAULT ...
ALTER TABLE table ALTER champ1 DROP DEFAULT
ALTER TABLE table ADD INDEX (champ);

ALTER TABLE ancien_nom RENAME nouveau_nom;

[modifier] Clefs

CREATE TABLE table (..., PRIMARY KEY (champ1, champ2))
CREATE TABLE table (..., FOREIGN KEY (champ1, champ2) REFERENCES table2 (t2_champ1, t2_champ2))

[modifier] Permissions

GRANT ALL PRIVILEGES ON base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'utilisateur'@'hote'; -- une seule permission
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'utilisateur'@'hote'; -- toutes les permissions

SET PASSWORD = PASSWORD('nouveau_pass')
SET PASSWORD FOR 'utilisateur'@'hote' = PASSWORD('nouveau_pass')
SET PASSWORD = OLD_PASSWORD('nouveau_pass')

DROP USER 'utilisateur'@'hote'

[modifier] Types de données principaux

TINYINT (1o: -127+128) SMALLINT (2o: +-65 000)
  MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000)
  BIGINT (8o: +- 9 trillions)
  Intervalle précis: -(2^(8*N-1)) -> (2^8*N)-1
  /!\ INT(2) = "2 chiffres affichés" -- ET NON PAS "nombre à 2 chiffres"

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) 
  /!\ 8,3 -> 12345,678 -- PAS 12345678,123!

TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
  TIMESTAMP (comme date, mais 1970->2038, compatible Unix)

VARCHAR(ligne) TEXT (multi-lignes; taille max=65535) BLOB (binaire; taille max=65535)
  Variantes: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
  Ex: TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('valeur1', 'valeur2', ...) -- (default NULL, ou  si NOT NULL)

[modifier] Oubli de mot de passe oublié

$ service mysql stop
$ mysqld_safe --skip-grant-tables
> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Tuer mysqld_safe, avec Control + \
$ service mysql start

[modifier] Réparer les tables après un arrêt soudain

mysqlcheck --all-databases
mysqlcheck --all-databases --fast