MySQL – functions, administrative commands, datatypes

Returns the ASCII value for the specific character

ASCII(text)

Returns the length of a string (in characters)

CHARACTER_LENGTH(text)

Concatenates two or more expressions together

CONCAT(arg1, arg2, ...)

Concatenates two or more expressions together with a separator

CONCAT_WS(sep, arg1, arg2, ...)

Returns the index position of a value in a list of values

FIELD(arg1, arg2, ...)

Returns the position of a string within A LIST OF strings

FIND_IN_SET(searched_string, s1, s2, s3, ...)

Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places

FORMAT(number, decimals)

Inserts a string within a string at the specified position and for a certain number of characters

INSERT(text, position, n, s)

Returns the position of the first occurrence of a string in another string

INSTR(text, s)

Converts a string to lower-case

LCASE(text)

Extracts a number of characters from a string (starting from left)

LEFT(text, n)

Returns the length of a string (in bytes)

LENGTH(text)

Returns the position of the first occurrence of a substring in a string

LOCATE(s, text, start)

Left-pads a string with another string, to a certain length

LPAD(text, width, s)

Removes leading spaces from a string

LTRIM(text)

Repeats a string as many times as specified

REPEAT(text, n)

Replaces all occurrences of a substring within a string, with a new substring

REPLACE(text, s1, s2)

Reverses a string and returns the result

REVERSE(text)

Extracts a number of characters from a string (starting from right)

RIGHT(text, n)

Right-pads a string with another string, to a certain length

RPAD(text, width, s)

Removes trailing spaces from a string

RTRIM(text)

Returns a string of the specified number of spaces.

SPACE(number)

Compares two strings

STRCMP(s1, s2)

Extracts a substring from a string (starting at any position)

SUBSTRING(text, start, len)

Returns a substring of a string before a specified number of DELIMITER occurs

SUBSTRING_INDEX(text, delimiter, quantity)

Removes leading and trailing spaces from a string

TRIM(text)

Converts a string to upper-case

UCASE(text)

Adds a time/date interval to a date and then returns the date

ADDDATE(date/datetime, interval)

Adds a time interval to a time/datetime and then returns the time/datetime

ADDTIME(time/datetime, interval)

Returns the current date

CURRENT_DATE()

Returns the current time

CURRENT_TIME()

Extracts the date part from a datetime expression

DATE(datetime)

Returns the number of days between two date values

DATEDIFF(date1, date2)

Adds a time/date interval to a date and then returns the date

DATE_ADD(date, interval)

Formats a date

DATE_FORMAT(date, format_specifier)

Subtracts a time/date interval from a date and then returns the date

DATE_SUB(date, interval)

Returns the day of the month for a given date

DAY(date[time])

Returns the weekday NAME for a given date

DAYNAME(date[time])

Returns the day of the month for a given date

DAYOFMONTH(date[time])

Returns the weekday number for a given date

DAYOFWEEK(date[time])

Returns the day of the year for a given date

DAYOFYEAR(date[time])

Extracts a part from a given date

EXTRACT(time_unit FROM date/time)

Returns a date from a numeric datevalue

FROM_DAYS(day_quantity)

Returns the hour part for a given date

HOUR([date]time)

Extracts the last day of the month for a given date

LAST_DAY([date]time)

Returns the current date and time

NOW()

Creates and returns a date based on a year and a number of days value

MAKEDATE(year, minute, second)

Creates and returns a time based on an hour, minute, and second value

MAKETIME(hour, minute, second)

Returns the microsecond part of a time/datetime

MICROSECOND([date]time)

Returns the minute part of a time/datetime

MINUTE([date]time)

Returns the month part for a given date

MONTH(date[time])

Returns the name of the month for a given date

MONTHNAME(date[time])

Adds a specified number of months to a period

PERIOD_ADD(period, number)

Returns the difference between two periods

PERIOD_DIFF(period, number)

Returns the quarter of the year for a given date value

QUARTER([date]time)

Returns the seconds part of a time/datetime

SECOND([date]time)

Returns a time value based on the specified seconds

SEC_TO_TIME(seconds)

Returns a date based on a string and a format

STR_TO_DATE(text, format_specifier)

Subtracts a time/date interval from a date and then returns the date

SUBDATE(date, interval)

Subtracts a time interval from a datetime and then returns the time/datetime

SUBTIME(time, interval)

Extracts the time part from a given time/datetime

TIME(datetime)

Formats a time by a specified format

TIME_FORMAT(time, format_specifier)

Converts a time value into seconds

TIME_TO_SEC(time)

Returns the difference between two time/datetime expressions

TIMEDIFF(t1, t2)

Returns a datetime value based on a date or datetime value

TIMESTAMP(date[time], [time])

Returns the number of days between a date and date "0000-00-00"

TO_DAYS(date)

Returns the week number for a given date

WEEK(date[time])

Returns the weekday number for a given date

WEEKDAY(date[time)

Returns the week number for a given date

WEEKOFYEAR(date[time)

Returns the year part for a given date

YEAR(date[time)

Returns the year and week number for a given date

YEARWEEK(date[time)

Returns the absolute value of a number

ABS(x)

Returns the arc cosine of a number

ACOS(x)

Returns the arc sine of a number

ASIN(x)

Returns the arc tangent of one or two numbers

ATAN(x)

Returns the arc tangent of two numbers

ATAN2(x, y)

Returns the average value of an expression

AVG(column)

Returns the smallest integer value that is >= to a number

CEILING(x)

Returns the cosine of a number

COS(x)

Returns the cotangent of a number

COT(x)

Returns the number of records returned by a select query

COUNT(column)

Converts a value in radians to degrees

DEGREES(x)

Used for integer division

DIV

Returns e raised to the power of a specified number

EXP(x)

Returns the largest integer value that is <= to a number

FLOOR(x)

Returns the greatest value of the list of arguments

GREATEST(arg1, arg2, ...)

Returns the smallest value of the list of arguments

LEAST(arg1, arg2, ...)

Returns the natural logarithm of a number

LN(x)

Returns the natural logarithm of a number, or the logarithm of a number to a specified base

LOG(base, x)

Returns the logarithm of a number to base 10

LOG10(x)

Returns the logarithm of a number to base 2

LOG2(x)

Returns the maximum value in a set of values

MAX(column)

Returns the minimum value in a set of values

MIN(column)

Returns the remainder of a number divided by another number

x MOD y

Returns the value of PI

PI()

Returns the value of a number raised to the power of another number

POWER(x, y)

Converts a degree value into radians

RADIANS(x)

Returns a random number x, where x∈⟨0;1)

RAND()

Rounds a number to a specified number of decimal places

ROUND(x,y)

Returns the sign of a number

SIGN(x)

Returns the sine of a number

SIN(x)

Returns the square root of a number

SQRT(x)

Calculates the sum of a set of values

SUM(column)

Returns the tangent of a number

TAN(x)

Truncates a number to the specified number of decimal places

TRUNCATE(x)

Returns a binary representation of a number

BIN(x)

Converts a value to a binary string

BINARY value

Converts a value (of any type) into a specified datatype

CAST(value AS datetype)

Returns the first non-null value in a list

COALESCE(*args)

Returns the unique connection ID for the current connection

CONNECTION_ID()

Converts a number from one numeric base system to another

CONV(n, base, new_base)

Converts a value into the specified datatype or character set

CONVERT(value, datatype)

Returns the name of the current database

DATABASE()

Returns a value if a condition is TRUE, or another value if a condition is FALSE

IF(condition, value1, value2)

Return a specified value if the expression is NULL, otherwise return the expression

IFNULL(value)

Returns 1 or 0 depending on whether an expression is NULL

ISNULL(value)

Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table

LAST_INSERT_ID()

Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned

NULLIF(value, value2)

Returns the current MySQL user name and host name

USER()

Returns the current version of the MySQL database

VERSION()

This will be used to select a database in the MySQL workarea.

USE Databasename;

Lists out the databases that are accessible by the MySQL DBMS.

SHOW DATABASES;

Shows the tables in the database once a database has been selected with the use command.

SHOW TABLES;

Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.

SHOW COLUMNS FROM tablename;

Presents the details of all indexes on the table, including the PRIMARY KEY.

SHOW INDEX FROM tablename

Reports details of the MySQL DBMS performance and statistics.

SHOW TABLE STATUS LIKE tablename;

A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1

CHAR(size)

A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535

VARCHAR(size)

Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1

BINARY(size)

Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.

VARBINARY(size)

For BLOBs (Binary Large OBjects). Max length: 255 bytes

TINYBLOB

Holds a string with a maximum length of 255 characters

TINYTEXT

Holds a string with a maximum length of 65,535 bytes

TEXT(size)

For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data

BLOB(size)

Holds a string with a maximum length of 16,777,215 characters

MEDIUMTEXT

For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data

MEDIUMBLOB

Holds a string with a maximum length of 4,294,967,295 characters

LONGTEXT

For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data

LONGBLOB

A string object that can have only one value, chosen from a list of possible values.

ENUM(val1, val2, val3, ...)

A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list

SET(val1, val2, val3, ...)

A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.

BIT(size)

A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)

TINYINT(size)

Zero is considered as false, nonzero values are considered as true.

BOOL

Equal to BOOL

BOOLEAN

A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)

SMALLINT(size)

A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)

MEDIUMINT(size)

A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)

INT(size)

Equal to INT(size)

INTEGER(size)

A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)

BIGINT(size)

A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions

FLOAT(size, d)

A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()

FLOAT(p)

A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter

DOUBLE(size, d)

An exact fixed-point number. The total number of digits is specified in size.

DECIMAL(size, d)

Equal to DECIMAL(size,d)

DEC(size, d)

To disable negative numbers.

UNSIGNED

To add leading zeros, which make all numbers in the column sustain the same length.

ZEROFILL

A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'

DATE

A date and time combination. Format: YYYY-MM-DD hh: mm: ss.

DATETIME(fsp)

A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh: mm: ss.

TIMESTAMP(fsp)

A time. Format: hh: mm: ss. The supported range is from '-838:59:59' to '838:59:59'

TIME(fsp)

A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.

YEAR