Premium

SQL literals

Literal — an explicitly specified fixed value, for example, the number 12 or the string "SQL". In MySQL, there are the following types of literals: numeric, string, boolean, NULL, bit, hexadecimal, and date and time literal.

Numeric literal

Example
Includes integers and fractional numbers. The dividing sign for a fractional number is "."(dot).1, 2.9, 0.01
It can have only an integer, a fractional part, or both at once..2, 1.1, 10
It can be a positive and a negative number (it is not necessary to specify a sign for a positive number at all).+1, -10, -2.2
Can be represented in exponential form.1e3(same as 1000), -1e-3(-0.001)

Arithmetic operators

SQL has all the arithmetic operators we are used to:

OperatorDescriptionExample
%, MODDivision by modulus11 % 5 = 1
*Multiplication10 \* 16 = 160
+Addition98 + 2 = 100
-Subtraction50 - 51 = -1
/Division1 / 2 = 0.5
DIVInteger division10 DIV 4 = 2

Using these operators, you can construct any arithmetic expression by applying the standard rules of arithmetic.

String literals

A string is a sequence of characters enclosed in single(‘) or double(") quotation marks. The set of characters that a string can consist of is determined by the DBMS itself. String examples: 'this is a string', "and this is a string".

Strings can contain special sequences of characters starting with "" (escape character). They are needed in order for the DBMS to give ordinary symbols (letters and other signs) a new special meaning. For example, the sequence "\n" literally means "newline", and without the preceding slash it would be the usual letter "n".

Date and time literals

Date and time values can be represented in string or number format. For example, in the case when the DBMS is waiting for a date (if a field with the "Date" type is specified in the table), the dates "2020-01-01", "20200101" and the number 20200101 will be interpreted as "January 1, 2020". Otherwise, it is necessary to use the syntax described below.

It is necessary that temporary literals are specified using a certain type and a string forming the date.

DefinitionWriting formats
DATE 'date'
or
{ d 'date' }		Interpreted as a date with a time equal to zeroYYYY-MM-DD, YYYYMMDD

You can use any punctuation mark instead of the "-" separator.

Example:
{ d '20200101' } -> January 1, 2020, 00:00:00
TIME 'date'
or
{ t 'date' }		Contains only the time without a specific datehh:mm:ss, hh:mm, hh, ss

The separator can also be omitted.

Example:
{ t '01' } -> 00:00:01
TIMESTAMP 'date'
or
{ ts 'date' }		Date, with the ability to set a specific timeYYYY-MM-DD hh:mm:ss, YYYYMMDDhhmmss

Example:
{ ts '20200101183030' } -> January 1, 2020, 18:30:30

Hexadecimal literals

Hexadecimal literals are values written in either of two notations: X'<hex-number>' or 0x<hex-number>.

Hexadecimal values can be integers or strings, where each pair of hexadecimal digits is converted to a character. So, the value 0x73716C can be interpreted as a decimal integer 7565676 or a string "sql".

Logical literals

The logical literal is the values TRUE and FALSE, meaning the truth and falsity of any statement. When interpreting a query, MySQL converts them to numbers: TRUE and FALSE become 1 and 0, respectively.

Bit literals

Bit values can be written in b'<binary-number>' and 0b<binary-number> notations using zeros and ones. Just like hexadecimal, bit values can be converted to an integer or a single string character.

For example, the value b'01010011' can be converted to the character "S" or the number '76'. From a set of bit values, you can compose some word, for example, the concatenation b'01010011', b'01010001' and b'01001100' will give us the string "SQL".

NULL

A NULL value means "no data", "no value". It is necessary to distinguish visually empty values, such as a zero-length string or a "space", from when there is no value at all, even empty.