MySQL > cheatsheet

BASES

SHOW DATABASES;
CREATE DATABASE nom_base;
CREATE DATABASE nom_base CHARACTER SET utf8;
USE nom_base;
DROP DATABASE nom_base;
ALTER DATABASE nom_base CHARACTER SET utf8;

TABLES

SHOW TABLES;
DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;

 

CREATE TABLE table (col1 type1, col2 type2);
CREATE TABLE table (col1 type1, col2 type2, INDEX (col1));
CREATE TABLE table (col1 type1, col2 type2, PRIMARY KEY (col1));
CREATE TABLE table (col1 type1, col2 type2, PRIMARY KEY (col1, col2));
CREATE TABLE table (col1 type1, col2 type2, FOREIGN KEY (col1) REFERENCES table2 (t2_col2)) [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
CREATE TABLE table (col1 type1, col2 type2, FOREIGN KEY (col1, col2) REFERENCES table2 (t2_col1, t2_col2))
CREATE TABLE table IF NOT EXISTS;
CREATE TEMPORARY TABLE table;

Vider une table

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

Modifier une table

ALTER TABLE table MODIFY col type
ALTER TABLE table MODIFY col type NOT NULL ...
ALTER TABLE table CHANGE old_name_col new_name_col type1
ALTER TABLE table CHANGE old_name_col new_name_col type1 NOT NULL ...
ALTER TABLE table ALTER col SET DEFAULT ...
ALTER TABLE table ALTER col DROP DEFAULT
ALTER TABLE table ADD new_col type1
ALTER TABLE table ADD new_col type1 FIRST
ALTER TABLE table ADD new_col type1 AFTER another_field
ALTER TABLE table DROP col
ALTER TABLE table ADD INDEX (col);

Changer l’ordre des colonnes

ALTER TABLE table MODIFY col type1 FIRST
ALTER TABLE table MODIFY col type1 AFTER another_field
ALTER TABLE table CHANGE old_name_col new_name_col type1 FIRST
ALTER TABLE table CHANGE old_name_col new_name_col type1 AFTER another_field

Clés

CREATE TABLE table (..., PRIMARY KEY (col1, col2))
CREATE TABLE table (..., FOREIGN KEY (col1, col2) REFERENCES table2 (t2_col1, t2_col2))

SELECT

SELECT * FROM table;
SELECT col1, col2 FROM t1, t2;
SELECT * FROM table WHERE condition GROUP BY col;
SELECT * FROM table WHERE condition GROUP BY col HAVING condition2;
SELECT * FROM table WHERE condition ORDER BY col1, col2 DESC;
SELECT * FROM tbale WHERE condition LIMIT 10;
SELECT DISTINCT col1 FROM table
SELECT DISTINCT col1, col2 FROM table

JOIN

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 ...

INSERT

INSERT INTO table (col1, col2) VALUES (val1, val2);

UPDATE

UPDATE table SET col1=new_val1 WHERE condition;
UPDATE t1, t2 SET col1=new_val1, col2=new_val2, ... WHERE t1.id1 = t2.id2 AND condition;

DELETE

DELETE FROM table / TRUNCATE table
DELETE FROM table WHERE condition
DELETE FROM t1, t2 FROM t1, t2 WHERE t1.id1 = t2.id2 AND condition

CONDITIONS

col = val
col <> val
col LIKE 'val _ %'
col IS NULL
col IS NOT NULL
col IN (val1, val2)
col NOT IN (val1, val2)
condition1 AND condition2
condition1 OR condition2

USERS

CREATE USER 'user'@'localhost';
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
FLUSH PRIVILEGES;

SET PASSWORD = PASSWORD('new_pass');
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
SET PASSWORD = OLD_PASSWORD('new_pass');

DROP USER 'user'@'host';

Host ‘%’ indicates any host

Types

TINYINT   (1o: -128 à +127)
SMALLINT  (2o: +-        65 000)
MEDIUMINT (3o: +-    16 000 000)
INT       (4o: +- 2 000 000 000)
BIGINT    (8o: +- 9 000 000 000 000 000 000)

⚠ INT(3) -> 045 (3 chiffres affiché) et non pas le nombre de chiffre max !

 

FLOAT(M,D)
DOUBLE(M,D)
FLOAT(D=0->53)

⚠ 8,3 -> 12345,678 et non pas 12345678,123 !

 

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

VARCHAR (taille) // simple ligne
TEXT (max size=65535) // muti lignes
BLOB (binary; max size=65535) // binaire

TINYTEXT, TINYBLOB -> TINY (max=255)
MEDIUMTEXT, MEDIUMBLOB -> MEDIUM (max=~16000)
LONGTEXT, LONGBLOB -> LONG (max=4Go)

BOOLEAN

col BOOLEAN DEFAULT TRUE

1 ou TRUE représente vrai - 0 ou FALSE représente faux

col ENUM('true', 'false') NOT NULL

 

Avec une contrainte CHECK (MySQL 8.0+)

col TINYINT, CHECK (est_actif IN (0, 1))

 

Insertion des valeurs

-- Avec des booléens littéraux
INSERT INTO table VALUES (TRUE);
INSERT INTO table VALUES (FALSE);
INSERT INTO table VALUES (1);  -- true
INSERT INTO table VALUES (0);  -- false

 

CREATE TABLE utilisateurs (id INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(100), est_admin BOOLEAN DEFAULT TRUE);

 

 

ENUM ('val1', 'val2', ...) -- (default NULL, or '' if NOT NULL)

Reset Root Password

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables
$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
## Switch back to the mysqld_safe terminal and kill the process using Control + \
$ /etc/init.d/mysql start