MySQL Cheat Sheet

Complete reference for MySQL commands, data types, and functions.

Top Content Ad (Responsive)

Data Types

String Types
  • CHAR String (0 - 255)
  • VARCHAR String (0 - 255)
  • TINYTEXT String (0 - 255)
  • TEXT String (0 - 65535)
  • BLOB Binary (0 - 65535)
  • MEDIUMTEXT String (0 - 16777215)
  • LONGTEXT String (0 - 4GB)
  • ENUM One of preset options
Numeric Types
  • TINYINT (-128 to 127)
  • SMALLINT (-32768 to 32767)
  • INT (-2B to 2B)
  • BIGINT (-9Z to 9Z)
  • FLOAT Decimal (23 digits)
  • DOUBLE Decimal (53 digits)
  • DECIMAL Fixed Point
Date & Time Types
  • DATE YYYY-MM-DD
  • DATETIME YYYY-MM-DD HH:MM:SS
  • TIMESTAMP YYYY-MM-DD HH:MM:SS
  • TIME HH:MM:SS
  • YEAR YYYY

Operators

Comparison
  • = (Equal)
  • <>, != (Not Equal)
  • >, >= (Greater)
  • <, <= (Less)
  • BETWEEN ... AND ...
  • IS NULL / IS NOT NULL
  • LIKE / NOT LIKE
  • IN / NOT IN
Logical
  • AND, &&
  • OR, ||
  • NOT, !
  • XOR
Arithmetic
  • + (Add)
  • - (Subtract)
  • * (Multiply)
  • / (Divide)
  • DIV (Integer Div)
  • % , MOD (Modulo)

String Functions

Manipulation
  • CONCAT(str1, str2)
  • CONCAT_WS(sep, str1, str2)
  • LENGTH(str)
  • CHAR_LENGTH(str)
  • LOWER(str) / UPPER(str)
  • LEFT(str, len) / RIGHT(str, len)
  • TRIM(str) / LTRIM(str)
  • SUBSTRING(str, pos, len)
  • REPLACE(str, from, to)
  • REVERSE(str)
  • INSTR(str, sub)
  • LOCATE(sub, str)
  • FORMAT(X, D)
  • INSERT(str, pos, len, new)
  • REPEAT(str, count)
  • SPACE(n)
  • STRCMP(str1, str2)

Date & Time Functions

Date Operations
  • NOW()
  • CURDATE()
  • CURTIME()
  • DATE_ADD(date, INTERVAL)
  • DATE_SUB(date, INTERVAL)
  • DATEDIFF(date1, date2)
  • DATE_FORMAT(date, fmt)
  • DAY(date) / MONTH(date) / YEAR(date)
  • DAYNAME(date) / MONTHNAME(date)
  • HOUR() / MINUTE() / SECOND()
  • WEEK() / WEEKDAY()
  • LAST_DAY(date)
  • STR_TO_DATE(str, fmt)
  • UNIX_TIMESTAMP()
  • FROM_UNIXTIME(ts)

Group By & Aggregate

Aggregate Functions
  • COUNT(*)
  • COUNT(DISTINCT col)
  • SUM(col)
  • AVG(col)
  • MIN(col)
  • MAX(col)
  • GROUP_CONCAT(col)
  • STD(col)
Numeric Functions
  • ABS(x)
  • CEIL(x) / FLOOR(x)
  • ROUND(x, d)
  • TRUNCATE(x, d)
  • POW(x, y)
  • SQRT(x)
  • RAND()
  • MOD(N, M)
  • SIGN(x)

Sample Queries

Common SELECTs
  • SELECT * FROM table
  • SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
  • SELECT ... WHERE cond GROUP BY col HAVING cond2
  • SELECT ... ORDER BY col DESC LIMIT 10
  • SELECT DISTINCT col FROM table
  • INSERT INTO table (col1) VALUES (val1)
  • UPDATE table SET col=val WHERE cond
  • DELETE FROM table WHERE cond

Encryption & Misc

Encryption
  • MD5(str)
  • SHA1(str) / SHA2(str, len)
  • AES_ENCRYPT(str, key)
  • AES_DECRYPT(str, key)
  • PASSWORD(str)
  • ENCODE(str, pass)
  • DECODE(str, pass)
Information
  • DATABASE()
  • USER() / CURRENT_USER()
  • VERSION()
  • CONNECTION_ID()
  • LAST_INSERT_ID()
  • FOUND_ROWS()
  • ROW_COUNT()
Bottom Content Ad (Responsive)