Essential MySQL Cheat Sheet

A Programming › MySQL cheat sheet in English by guslong

Essential MySQL

MySQL SQL Database String Queries Table Joins Auto-incrementing

111,845 views 43,357 downloads 13 Aug 2012 History

PRs

4.7 avg · 46 ratings

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

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

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

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")

MySQL calculation functions

Count rows	COUNT(col)
Average	AVG(col)
Minimum value	MIN(col)
Maximum value	MAX(col)
Sum of values	SUM(col)

Create table with auto-incrementing primary key

CREATE TABLE table_name (
id INT AUTO_INCREMENT,
column VARCHAR(2),
column VARCHAR(32),
PRIMARY KEY (id)
);

Badges

Great Cheat Sheet Useful Cheat Sheet Good Cheat Sheet Nice Cheat Sheet Appreciated Cheat Sheet Downloaded from Unusual Locations × 20

Comments (2)

carldickens 19:52 26 Oct 2018

Reviewed a few and liked the layout of this one. seemed to be geared to the data that i need to remind myself as a new user.

PastExpiryDotCom 16:35 12 Jul 2013

Just starting to learn SQL on my own. This sheet will be invaluable!