Mysql Cheat Sheet printable pdf download

functions

numeric

ABS(X)

SIGN(X)

FLOOR(X)

CEILING(X)

ROUND(X[,D])

EXP(X)

TINYINT[(digits)] [unsigned|zero ll]

256

DIV(X)

MOD(N,M)

BIT,BOOL,BOOLEAN

synonyms for tinyint(1)

POW(X,Y)

POWER(X,Y)

SMALLINT[(digits)] [unsigned|zero ll]

65,536

SQRT(X)

RAND([seed])

MEDIUMINT[(digits)] [unsigned|zero ll]

16,777,216

PI()

DEGREES(X)

INT,INTEGER[(digits)] [unsigned|zero ll]

4,294,967,296

RADIANS(X)

COT(X)

BIGINT[(digits)] [unsigned|zero ll]

18,446,744,073,709,551,616

COS(X)

ACOS(X)

FLOAT[(digits, digits after decimal)] [unsigned|zero ll]

23 digits

SIN(X)

ASIN(X)

DOUBLE[(digits, digits after decimal)] [unsigned|zero ll]

24…53 digits

TAN(X)

ATAN(X)

ATAN2(X)

DECIMAL[(digits, digits after decimal)] [unsigned|zero ll]

a type of DOUBLE stored as a string

LOG(X), LOG2(X), LOG10(X) LN(X)

REFERENCE

SHEET

TRUNCATE(X, D)

versions 3.23, 4.0, 4.1

strings

functions

CHAR[(length)]

0…255 – ﬁxed length, right-padded with spaces

ASCII('str')

CONV(number,from_base,to_base) BIN(num),OCT(num),HEX(num)

VARCHAR[(length)]

0…255 – variable length (trailing spaces removed)

ORD('str')

CHAR(number[ USING charset],…) CONCAT('str'1, 'str1' , …)

BINARY,VARBINARY[(length)]

0…255 – stores bytes instead of character strings

LENGTH('str')

CHAR_LENGTH('str')

CONCAT_WS('separator' , 'str1' , 'str2')

TINYTEXT|TINYBLOB

0…255 – text stores strings, blob stores bytes

BIT_LENGTH('str')

REVERSE('str')

SOUNDEX('str')

TEXT|BLOB

0…65,535 – text stores strings, blob stores bytes

LCASE('str')

UCASE('str')

QUOTE('str')

MEDIUMTEXT|MEDIUMBLOB

0…16,777,215 – text stores strings, blob stores bytes

LPAD('str' , len, 'padstr')

RPAD('str' , len, 'padstr')

ELT(number, 'str1' , 'str2' , 'str3' , …)

LONGTEXT|LONGBLOB

0…4,294,967,295 – text stores strings, blob stores bytes

LEFT('str' , length)

RIGHT('str' , length)

FIELD('str' , 'str1' , 'str2' , 'str3' , …)

ENUM('value1' , 'value2' , …)

list of up to 65,535 members, can have only one value

LTRIM('str')

RTRIM('str')

TRIM('str')

LOAD_FILE(' lename')

SET('value1' , 'value2' , …)

list of up to 64 members, can have zero or more values

SPACE(count)

REPEAT('str' , count)

SUBSTRING('str' , pos[, length])

REGEXP 'expression'

REPLACE('str' , 'from' , 'to') INSERT('str' , pos, length, 'newstr')

SUBSTRING_INDEX('str' , 'del' , count)

INSTR('str' , 'substr')

LOCATE('substr' , 'str'[, pos])

STRCMP('str1' , 'str2')

date & time

functions

DATE

'YYYY-MM-DD'

WEEK('date'[, mode])

WEEKDAY('date')

DAYOFWEEK('date')

DATETIME

'YYYY-MM-DD HH:MM:SS'

DAYOFYEAR('date')

MONTH('date')

MONTHNAME('date')

TIMESTAMP[(display width)]

'YYYY-MM-DD HH:MM:SS' – display widths: 6, 8, 12 or 14

QUARTER('date')

YEAR('date')

YEARWEEK('date'[, mode])

TIME

'HH:MM:SS'

HOUR('date')

MINUTE('date')

SECOND('date')

YEAR[(2|4)]

'YYYY' – a year in 2-digit or 4-digit format

TO_DAYS('date')

FROM_DAYS(number) LAST_DAY('date')

SEC_TO_TIME(seconds)

TIME_TO_SEC('time') SYSDATE()

CURTIME(),CURRENT_TIME(),CURRENT_TIME

TIME_FORMAT('date' , 'format')

commands

CURDATE(),CURRENT_DATE(),CURRENT_DATE

DATE_FORMAT('date' , 'format')

NOW(),CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,LOCALTIME(),LOCALTIME

connecting to a database

UNIX_TIMESTAMP(['date'])

FROM_UNIXTIME('unix_timestamp'[, 'format'])

# mysql [-h hostname] [-u username] [-ppassword] [dbname]

PERIOD_ADD('period' , num)

PERIOD_DIFF('period' , num)

EXTRACT(unit FROM 'date')

importing data

backup a database

ADDDATE('date' , days) | ADDDATE('date' , INTERVAL expr unit),DATE_ADD('date' , INTERVAL expr unit)

# mysql dbname < dbdump le.sql

# mysqldump [-options] dbname [> dump le.sql]

SUBDATE('date' , days) | SUBDATE('date' , INTERVAL expr unit),DATE_SUB('date' , INTERVAL expr unit)

Mysql Cheat Sheet

Related Articles

Related forms

Related Categories