My MySQL Cheat Sheet

0
962

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

Name Description
TINYINT A small integer. The signed range of -128 to 127, and the unsigned range of 0 to 255.
BOOLEAN A value 0 with “false,” and a value 1 with “true.”
SMALLINT A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT(also INTEGER) A normal-sized integer. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.
BIGINT A large integer. The signed range is -9223372036854775808 to
9223372036854775807.
The unsigned range is 0 to 18446744073709551615.
DECIMAL(m,d), also DEC, NUMERIC, FIXED A precise fixed-point number. m defaults to 10, if not specified. d defaults to 0, if not specified.
FLOAT(m,d) A small, floating-point number. Allowable values are:
-3.402823466E+38
to -1.175494351E-38
1.175494351E-38
to 3.402823466E+38.
DOUBLE also

REAL and DOUBLE PRECISION

A normal-size, floating-point numbers. Allowable values are:
-1.7976931348623157E+308
to -2.2250738585072014E-308
2.2250738585072014E-308
to 1.7976931348623157E+308
BIT[(m)] A bit-field type. m indicates the number of bits per value, from 1 to 64. The default is 1 if m is omitted.

Date and Time Data Types

Name Value range
DATE Values range from ‘1000-01-01’ to ‘9999-12-31’.
TIME Values range from ‘-838:59:59’ to ‘838:59:59’.
DATETIME Values range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
TIMESTAMP Values range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
YEAR Year value as 2 digits or 4 digits.

String DataTypes

Name Description
String literals The character sequences enclosed by quotes.
CHAR A right-padded, fixed-length string containing spaces of specified length.
VARCHAR A variable-length string, with an M range (maximum column length) of 0 to 65535.
BINARY A binary byte string, with M as the column length in bytes.
VARBINARY A binary byte string of variable length.
TINYBLOB A blob column with a maximum length of 255 (2^8 – 1) bytes.
BLOB A blob column with a maximum length of 65,535 (2^16 – 1) bytes.
MEDIUMBLOB A blob column with a maximum length of 16,777,215 (2^24 – 1) bytes.
LONGBLOB A blob column with a maximum length of 4,294,967,295(2^32 – 1) bytes.
TINYTEXT A text column with a maximum length of 255 (2^8- 1) characters.
TEXT A text column with a maximum length of 65,535 (2^16 – 1) characters.
MEDIUMTEXT A text column with a maximum length of 16,777,215 (2^24 – 1) characters.
LONGTEXT A text column with a maximum length of 4,294,967,295 or 4GB (2^32 – 1) characters.
ENUM A string object having only a single value from a list.
SET A string object having zero or more values from a list, with a maximum of 64 members. SET values present internally as integer values.

LEAVE A REPLY

Please enter your comment!
Please enter your name here