Essential MySQL Cheat Sheet

In this article, we'll take a look at Show

I have googled the same queries related to SQL numerous times. There are plenty of MySQL cheat sheets available, but each seems to be missing something. I have tried to collect all of them in a single cheat sheet that fulfills my needs. If something is missing, do not hesitate to mention it via comments below. Happy SQLing !!!

MySQL Data Types

CHAR	String (0 – 255)
VARCHAR	String (0 – 255)
TINYTEXT	String (0 – 255)
TEXT	String (0 – 65535)
BLOB	String (0 – 65535)
MEDIUMTEXT	String (0 – 16777215)
MEDIUMBLOB	String (0 – 16777215)
LONGTEXT	String (0 – 4294967295)
LONGBLOB	String (0 – 4294967295)
TINYINT x	Integer (-128 to 127)
SMALLINT x	Integer (-32768 to 32767)
MEDIUMINT x	Integer (-8388608 to 8388607)
INT x	Integer (-2147483648 to 2147483647)
BIGINT x	Integer (-9223372036854775808 to 9223372036854775807)
FLOAT	Decimal (precise to 23 digits)
DOUBLE	Decimal (24 to 53 digits)
DECIMAL	“DOUBLE” stored as string
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYYMMDDHHMMSS
TIME	HH:MM:SS
ENUM	One of preset options
SET	Selection of preset options

Select queries

select all columns SELECT * FROM tbl;

select some columns SELECT col1, col2 FROM tbl;

select only unique records SELECT DISTINCT FROM tbl WHERE condition;

column alias with AS SELECT col FROM tbl AS newname;

order results SELECT * FROM tbl ORDER BY col [ASC | DESC];

group results SELECT col1, SUM(col2) FROM tbl GROUP BY col1;

Creating and modifying

create a database CREATE DATABASE db_name;

select a database USE db_name;

list the databases on the server SHOW DATABASES;

show a table’s fields DESCRIBE tbl;

create a new table CREATE TABLE tbl (field1, field2);

insert data into a table INSERT INTO tbl VALUES (“val1”, “val2”);

delete a row DELETE * FROM tbl WHERE condition;

add a column from a table ALTER TABLE tbl ADD COLUMN col;

remove a column from a table ALTER TABLE tbl DROP COLUMN col;

make a column a primary key ALTER TABLE tbl ADD PRIMARY KEY (col);

return only 1 row matching query … LIMIT = 1

amend the values of a column UPDATE table SET column1=”val1” WHERE …

clear all the values, leaving the table structure TRUNCATE TABLE tbl;

delete the table DROP TABLE tbl;

delete the database DROP DATABASE db_name;

Matching data

matching data using LIKE SELECT * FROM tbl1 WHERE col LIKE ‘%value%’

matching data using REGEX SELECT * FROM tbl1 WHERE col RLIKE ‘regular_expression’

Joins

INNER JOIN	returns only where match in both tables
OUTER JOIN	also returns non-matching records from both tables
LEFT JOIN	also returns non-matching records from left table
RIGHT JOIN	also returns non-matching records in right table

Integers (marked x) that are “UNSIGNED” have the same range of values but start from 0 (i.e., an UNSIGNED TINYINT can have any value from 0 to 255).

JOIN syntax:SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id;

String functions mySQL

Compare strings	STRCMP(“str1″,”str2”)
Convert to lower case	LOWER(”str”)
Convert to upper case	UPPER(”str”)
Left trim	LTRIM(”str”)
Substring of a string	SUBSTRING(“str”,”inx1”,”inx2″)
Concatenate	CONCAT(“str1″,”str2”)