Complete Cheatsheet of Mysql

• Official Website

Download

• MySQL Cheat Sheet [.pdf]
• MySQL Database Quick Reference [.pdf]

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 – 429496-7295)
• LONGBLOB
• String (0 – 429496-7295)
• TINYINT x
• Integer (-128 to 127)
• SMALLINT x
• Integer (-32768 to 32767)
• MEDIUMINT x
• Integer (-8388608 to 8388607)
• INT x
• Integer (-2147-483648 to 214748-3647)
• BIGINT x
• Integer (-9223-372-036-854-775808 to 922337-203-685-477-5807)
• FLOAT
• Decimal (precise to 23 digits)
• DOUBLE
• Decimal (24 to 53 digits)
• DECIMAL
• “-DOU-BLE-” stored as string
• DATE
• YYYY-MM-DD
• DATETIME
• YYYY-MM-DD HH:MM:SS
• TIMESTAMP
• YYYYMM-DDH-HMMSS
• TIME
• HH:MM:SS
• ENUM
• One of preset options
• SET
• Selection of preset options

Assignment Operators

• =
• :=

Logical Operators

• AND, &&
• NOT, !
• ||, OR
• XOR

Comparison Operators

• BETWEEN … AND …
• BINARY
• &
• ~
• |
• ^
• CASE
• DIV
• /
• <=>
• =
• >=
• >
• IS NOT NULL
• IS NOT
• IS NULL
• IS
• <<
• <=
• <
• LIKE
• –
• % or MOD
• NOT BETWEEN … AND …
• !=, <>
• NOT LIKE
• NOT REGEXP
• +
• REGEXP
• >>
• RLIKE
• SOUNDS LIKE
• *
• –

String Comparison

• LIKE
• NOT LIKE
• STRCMP()

Regular Expressions

• NOT REGEXP
• REGEXP
• RLIKE

String Functions

• ASCII()
• BIN()
• BIT_LENGTH()
• CHAR_LENGTH()
• CHAR()
• CHARACTER_LENGTH()
• CONCAT_WS()
• CONCAT()
• ELT()
• EXPORT_SET()
• FIELD()
• FIND_IN_SET()
• FORMAT()
• HEX()
• INSERT()
• INSTR()
• LCASE()
• LEFT()
• LENGTH()
• LIKE
• LOAD_FILE()
• LOCATE()
• LOWER()
• LPAD()
• LTRIM()
• MAKE_SET()
• MATCH
• MID()
• NOT LIKE
• NOT REGEXP
• OCTET_LENGTH()
• ORD()
• POSITION()
• QUOTE()
• REGEXP
• REPEAT()
• REPLACE()
• REVERSE()
• RIGHT()
• RLIKE
• RPAD()
• RTRIM()
• SOUNDEX()
• SOUNDS LIKE
• SPACE()
• STRCMP()
• SUBSTR()
• SUBSTRING_INDEX()
• SUBSTRING()
• TRIM()
• UCASE()
• UNHEX()
• UPPER()

Arithmetic Operators

• DIV
• /
• –
• % or MOD
• +
• *
• –

Mathematical Functions

• ABS()
• ACOS()
• ASIN()
• ATAN2(), ATAN()
• ATAN()
• CEIL()
• CEILING()
• CONV()
• COS()
• COT()
• CRC32()
• DEGREES()
• EXP()
• FLOOR()
• LN()
• LOG10()
• LOG2()
• LOG()
• MOD()
• OCT()
• PI()
• POW()
• POWER()
• RADIANS()
• RAND()
• ROUND()
• SIGN()
• SIN()
• SQRT()
• TAN()
• TRUNCATE()

Date and Time

• ADDDATE()
• ADDTIME()
• CONVERT_TZ()
• CURDATE()
• CURRENT_DATE(), CURRENT_DATE
• CURRENT_TIME(), CURRENT_TIME
• CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
• CURTIME()
• DATE_ADD()
• DATE_FORMAT()
• DATE_SUB()
• DATE()
• DATEDIFF()
• DAY()
• DAYNAME()
• DAYOFMONTH()
• DAYOFWEEK()
• DAYOFYEAR()
• EXTRACT()
• FROM_DAYS()
• FROM_UNIXTIME()
• GET_FORMAT()
• HOUR()
• LAST_DAY
• LOCALTIME(), LOCALTIME
• LOCALTIMESTAMP, LOCALTIMESTAMP()
• MAKEDATE()
• MAKETIME
• MICROSECOND()
• MINUTE()
• MONTH()
• MONTHNAME()
• NOW()
• PERIOD_ADD()
• PERIOD_DIFF()
• QUARTER()
• SEC_TO_TIME()
• SECOND()
• STR_TO_DATE()
• SUBDATE()
• SUBTIME()
• SYSDATE()
• TIME_FORMAT()
• TIME_TO_SEC()
• TIME()
• TIMEDIFF()
• TIMESTAMP()
• TIMESTAMPADD()
• TIMESTAMPDIFF()
• TO_DAYS()
• TO_SECONDS()
• UNIX_TIMESTAMP()
• UTC_DATE()
• UTC_TIME()
• UTC_TIMESTAMP()
• WEEK()
• WEEKDAY()
• WEEKOFYEAR()
• YEAR()
• YEARWEEK()

GROUP BY Functions

• AVG()
• BIT_AND()
• BIT_OR()
• BIT_XOR()
• COUNT(DISTINCT)
• COUNT()
• GROUP_CONCAT()
• MAX()
• MIN()
• STD()
• STDDEV_POP()
• STDDEV_SAMP()
• STDDEV()
• SUM()
• VAR_POP()
• VAR_SAMP()
• VARIANCE()

Information Functions

• BENCHMARK()
• CHARSET()
• COERCIBILITY()
• COLLATION()
• CONNECTION_ID()
• CURRENT_USER(), CURRENT_USER
• DATABASE()
• FOUND_ROWS()
• LAST_INSERT_ID()
• ROW_COUNT()
• SCHEMA()
• SESSION_USER()
• SYSTEM_USER()
• USER()
• VERSION()

Encryption and Compression

• AES_DECRYPT()
• AES_ENCRYPT()
• COMPRESS()
• DECODE()
• DES_DECRYPT()
• DES_ENCRYPT()
• ENCODE()
• ENCRYPT()
• MD5()
• OLD_PASSWORD()
• PASSWORD()
• SHA1(), SHA()
• SHA2()
• UNCOMPRESS()
• UNCOMPRESSED_LENGTH()

Miscellaneous Functions

• DEFAULT()
• GET_LOCK()
• INET_ATON()
• INET_NTOA()
• IS_FREE_LOCK()
• IS_USED_LOCK()
• MASTER_POS_WAIT()
• NAME_CONST()
• RAND()
• RELEASE_LOCK()
• SLEEP()
• UUID_SHORT()
• UUID()
• VALUES()

Select Queires

• Returns all columns
• SELECT * FROM table
• Returns all columns
• SELECT * FROM table1, table2, …
• Returns specific column
• SELECT field1, field2, … FROM table1, table2, …
• Returns rows that match condition
• SELECT … FROM … WHERE condition
• Returns with orders
• SELECT … FROM … WHERE condition GROUP BY field
• Returns withd orders and match condition
• SELECT … FROM … WHERE condition GROUP BY field HAVING condition2
• Returns first 10 rows
• SELECT … FROM … WHERE condition LIMIT 10
• Returns with no repeats
• SELECT DISTINCT field1 FROM …
• Returns and joind
• SELECT … FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition