MYSQL Cheat Sheet

MYSQL Cheat Sheet

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

MySQL Mathem­atical Functions

ABS     COS

SIGN    SIN

MOD     TAN

FLOOR   ACOS

CEILING ASIN

ROUND   ATAN, ATAN2

DIV     COT

EXP     RAND

LN      LEAST 

LOG, LOG2, LOG10 GREATEST 

POW     DEGREES 

POWER   RADIANS 

SQRT    TRUNCATE

PI

MySQL Date and Time Functions

DAYOFWEEK DATE_SUB

WEEKDAY ADDDATE

DAYOFMONTH SUBDATE

DAYOFYEAR EXTRACT 

MONTH TO_DAYS

DAYNAME FROM_DAYS

MONTHNAME DATE_F­ORMAT

QUARTER TIME_F­ORMAT

WEEK CURREN­T_DATE

YEAR CURREN­T_TIME

YEARWEEK NOW

HOUR SYSDATE

MINUTE UNIX_T­IME­STAMP

SECOND FROM_U­NIXTIME

PERIOD_ADD SEC_TO­_TIME

PERIOD­_DIFF TIME_T­O_SEC

DATE_ADD

MySQL Miscel­laneous Functions

BIT_COUNT    DES_EN­CRYPT

DATABASE     DES_DE­CRYPT

USER LAST  _ I­NSE­RT_ID

SYSTEM­_USE   FORMAT

SESSIO­N_USER VERSION

CURREN­T_USER CONNEC­TION_ID

PASSWORD     GET_LOCK

OLD_PA­SSWORD RELEAS­E_LOCK

ENCRYPT      IS_FRE­E_LOCK

DECODE       BENCHMARK

MD5          INET_NTOA

SHA1         INET_ATON

AES_EN­CRYPT  FOUND_ROWS

AES_DE­CRYPT  STRCMP

MySQL String Functions

ASCII       SUBSTRING

ORD         MID

CONV        SUBSTRING_INDEX

BIN         LTRIM

OCT         RTRIM

HEX         TRIM

CHAR        SOUNDEX

CONCAT      SPACE

CONCAT_WS   REPLACE

LENGTH      REPEAT

CHAR_LENGTH REVERSE

BIT_LENGTH  INSERT

LOCATE      ELT

INSTR       FIELD

LPAD        LCASE

RPAD        UCASE

LEFT LOAD  _FILE

RIGHT       QUOTE

MySQL Grouping Functions

AVG           MAX

BIT_AND       STD

BIT_OR        STDDEV

COUNT         SUM

GROUP_­CONCAT  VARIANCE

MIN