My MySQL Cheat Sheet

I have googled plenty of times the same and the same queries related to SQL. There are plenty of MySQL Cheat Sheets out there but everyone seems to be missing something. I have tried to collect all of them in a single cheat sheet that fulfill  my needs. If something is missing do not hesitate to mention it via comments below. Happy SQLing !!!

Create / Delete Database

CREATE DATABASE dbName
CREATE DATABASE dbName CHARACTER SET utf8
DROP   DATABASE dbName
ALTER  DATABASE dbName CHARACTER SET utf8

Repair Tables After Unclean Shutdown

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

Select Records

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT column1, column2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY column1, column2
SELECT ... FROM ... WHERE condition ORDER BY column1, column2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT column1 FROM ...
SELECT DISTINCT column1, column2 FROM ...

Insert Records

INSERT INTO table1 (column1, column2, ...) VALUES (value1, value2, ...)
LOAD DATA LOCAL INFILE IGNORE '/file/path/inputdata.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES;

Delete Records

DELETE FROM table1 / TRUNCATE table1        --Deletes all Records
DELETE FROM table1 WHERE condition
DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

Update Records

UPDATE table1 SET column1=new_value1 WHERE condition
UPDATE table1, table2 SET column1=new_value1, column2=new_value2, ... WHERE table1.id1 = table2.id2 AND condition

Create / Delete / Modify Table

Create

CREATE TABLE table (column1 type1, column2 type2, ...)
CREATE TABLE table (column1 type1, column2 type2, ..., INDEX (field))
CREATE TABLE table (column1 type1, column2 type2, ..., PRIMARY KEY (column1))
CREATE TABLE table (column1 type1, column2 type2, ..., PRIMARY KEY (column1,column2))
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
   FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)

Drop

DROP TABLE table                  --Deleting table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...
DROP DATABASE                     --Deleting database

Alter

ALTER TABLE table MODIFY column1 type1
ALTER TABLE table MODIFY column1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_column1 new_name_column1 type1 --Rename Column
ALTER TABLE table CHANGE old_name_column1 new_name_column1 type1 NOT NULL ...
ALTER TABLE table ALTER column1 SET DEFAULT ...
ALTER TABLE table ALTER column1 DROP DEFAULT
ALTER TABLE table ADD new_name_column1 type1
ALTER TABLE table ADD new_name_column1 type1 FIRST
ALTER TABLE table ADD new_name_column1 type1 AFTER another_field
ALTER TABLE table DROP column1      --Deleting Column
ALTER TABLE table ADD INDEX (field);

ALTER TABLE table MODIFY column1 type1 FIRST    --Change column order
ALTER TABLE table MODIFY column1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_column1 new_name_column1 type1 FIRST
ALTER TABLE table CHANGE old_name_column1 new_name_column1 type1 AFTER another_field

Backup/Restore Database to SQL File

mysqldump -u Username -p dbName > databasename_backup.sql   --Backup single db
mysql     -u Username -p dbName < databasename_backup.sql   --Restore single db
mysqldump -u Username -p --all-databases > all_databases.sql --Backup all dbs

Browsing

SHOW DATABASES;          --Show all databases
use [database]           --Select which db to use
SHOW TABLES              --Show tables of selected db
SHOW FIELDS FROM table / DESCRIBE table
SHOW TABLE STATUS;       --Show dbs information 
SHOW CREATE TABLE table
SHOW PROCESSLIST
KILL process_number
exit;

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

Keys

CREATE TABLE table (..., PRIMARY KEY (column1, column2))
CREATE TABLE table (..., FOREIGN KEY (column1, column2) REFERENCES table2(t2_column1, t2_column2))

Conditions

column1 = value1
column1 <> value1
column1 LIKE '%value%'     --select records that contain value 
column1 LIKE '%value'      --select records that start with value 
column1 LIKE 'value%'      --select records that finish with value 
column1 IS NULL
column1 IS NOT NULL
column1 IS IN (value1, value2)
column1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

Access Database

mysql -u [username] -p;           --will prompt for password
mysql -u [username] -p [database] --will prompt for password
mysql -h [host] -u [user] -p [Database]

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';
## Kill mysqld_safe process 
$ /etc/init.d/mysql start

Repair corrupt MyISAM

sudo service mysql stop
cd /var/lib/mysql/$DATABASE_NAME  
myisamchk -r $TABLE_NAME.*
or
myisamchk -r -v -f $TABLE_NAME.*        --it might take some time
sudo service mysql start

Check Size

--Show all database sizes:
SELECT table_schema "<MY-DATABASE-NAME>", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
--Show all tables sizes for database:
SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "<MY-DATABASE-NAME>";

Check/Modify Variables

show variables like 'variable_name';
show global variables like 'variable_name';
set variable_name=0000;
set global variable_name=0000;

Modify Storage Engine

ALTER TABLE wp_comments ENGINE=InnoDB;

Main Data Types

Numeric Data Types

Date and Time Data Types

String DataTypes