MySQL Cheatsheet

SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table;
DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;

SELECT * FROM table;
SELECT * FROM table1, table2;
SELECT field1, field2 FROM table1, table2;
SELECT ... FROM ... WHERE condition;
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE cond GROUP BY field HAVING x;
SELECT ... FROM ... WHERE condition ORDER BY f1, f2;
SELECT ... FROM ... WHERE cond ORDER BY f1, f2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

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

CREATE DATABASE DatabaseName;
CREATE DATABASE DatabaseName CHARACTER SET utf8;
USE DatabaseName;
DROP DATABASE DatabaseName;
ALTER DATABASE DatabaseName CHARACTER SET utf8;

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IS IN (value1, value2)
field1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

UPDATE table1 SET field1=new_value1 WHERE condition;
UPDATE table1, table2 
SET field1=new_value1, field2=new_value2, ... 
WHERE table1.id1 = table2.id2 AND condition;

CREATE TABLE table (field1 type1, field2 type2);
CREATE TABLE table (field1 type1, field2 type2, INDEX (field));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1));
CREATE TABLE table IF NOT EXISTS;
CREATE TEMPORARY TABLE table;

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

CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
  [ON UPDATE|ON DELETE] [CASCADE|SET NULL]

CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
  FOREIGN KEY (fk_field1, fk_field2) 
  REFERENCES table2 (t2_fieldA, t2_fieldB))

ALTER TABLE table MODIFY field1 type1;
ALTER TABLE table MODIFY field1 type1 NOT NULL;
ALTER TABLE table CHANGE old_name new_name type1;
ALTER TABLE table ALTER field1 SET DEFAULT ...;
ALTER TABLE table ALTER field1 DROP DEFAULT;
ALTER TABLE table ADD new_field1 type1 FIRST;
ALTER TABLE table ADD new_field1 type1 AFTER another_field;
ALTER TABLE table DROP field1;

INSERT INTO table1 (field1, field2) VALUES (value1, value2);

DELETE FROM table1; 
TRUNCATE table1;
DELETE FROM table1 WHERE condition;
DELETE FROM table1, table2 
WHERE table1.id1 = table2.id2 AND condition;

CREATE USER 'user'@'localhost';
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';
FLUSH PRIVILEGES;

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

DROP USER 'user'@'host'; -- Host '%' indicates any host

# Backup Database to SQL File
mysqldump -u Username -p dbNameYouWant > database_backup.sql

# Restore from backup SQL File
mysql -u Username -p dbNameYouWant < database_backup.sql;

# Repair Tables After Unclean Shutdown
mysqlcheck --all-databases;
mysqlcheck --all-databases --fast;

$ /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 mysqld_safe terminal and kill process (Ctrl + \)
$ /etc/init.d/mysql start