Learn – MySQL Literals(Constants)
Literals are symbols or ideas that represent or communicate a constant value. Literals are analogous to constants in MySQL. When declaring variables or running queries, we can use literals.
We’ll go through the different sorts of literals and how to utilise them in MySQL statements in this section.
The following are the types of literal:
Date and Time Literals
If we want to get the id and phone numbers whose name is [name], execute the below statement
mysql > SELECT id, phone FROM emp WHERE name = 'name';
mysql > SELECT id, phone FROM emp WHERE name = "name";
It will give the following outputs where we can see that both queries give the same result, either uses single quotes or double-quotes.
They can also be used with special characters escape sequences. These special characters are summarised in a tabular form below:
Escape Sequence Character Represented by Sequence
\0 It represents ASCII NULL character.
\b It represents a backspace character.
\n It represents a newline character.
\r It represents carriage return character.
\t It represents tab character.
\\ It represents a backslash (\) character.
\% It represents a % character.
\_ It represents a backslash character.
Numeric literals in MySQL are used to specify the two types of literal values:
the exact-value (integer and decimal), and the approximate value (floating-point) literals. It can be positive or negative values. The exact-value can have an integer, fraction, or both. An approximate-value is mainly used for scientific notations that contain mantissa and exponent.
Number Literals Descriptions
Integer It is represented as a sequence of digits without any fractional parts. If the number preceded by – sign, it is a negative integer. If the number is preceded by + sign, it is a positive integer. If the number does not have any sign, it assumes a positive integer. For example, 55, +55, -55 are all integer numbers.
Decimal It is represented as a sequence of digits with fractional parts. In other words, it contains a whole number plus fractional part, which is separated by dot(.) operator or decimal point. It can be integer and non-integer both. It produces the calculation in exact numeric form. For example, 325.90, 355.6 are decimal numbers.
Floating-Point It is a number that contains a floating decimal point. It means there are no fixed digits before and after the decimal point. It contains two kinds of data types named float and double that produce an approximate value.
mysql > SELECT stud_name, subject, marks FROM student_info WHERE marks > +80;
OutputIt will give the following outputs where we can see all student names whose marks > +80.
Date and Time Literals
Date and Time values in MySQL can be represented either in the quoted strings or numbers format, which depends on the exact value and some factors. For example, MySQL interprets any of this ‘2020-09-22’, ‘20200922’, and 20200922 as a valid date.
mysql > SELECT Order_ID, Product_Name, DATE_FORMAT(Order_Date,'%d--%m--%y') as new_date_formate FROM orders;
OutputIt will give the following outputs where we can see that the format of the date will be changed.Example 2
mysql > SELECT Order_ID, Product_Name, DATE_FORMAT(Order_Date,'%d%m%y 11:
45') as new_date_formate FROM orders;
In the numbering system, hexadecimal can be represented as a whole number whose base is 16. Hexadecimal literal values can be written in the following term:
Here, val contains the hexadecimal digits in the range of (0..9 and A..F). In 0xval, leading 0x is case sensitive; therefore we cannot write it as 0X’val’. However, in the case of lettercase of the digits, the leading X or 0x does not matter for particular case-sensitive.
below examples explain the legal and illegal hexadecimal literals:
0x01bf X’0H’ (Because H does not a hexadecimal digit)
0X0BAF (Because 0X should be written as 0x)It makes sure that the notation X’val’ or x’val’ should contain an even no of digits. Otherwise, we will get a syntax error. This type of error can be avoided by padding the zero digit at the beginning of the string.
mysql > SELECT 0xD6+0;
Boolean literals in MySQL always evaluate in 1 or 0 values.
mysql > SELECT TRUE, true, FALSE, false;
Bit-value literals in MySQL can be written as b’val’ or 0bval notation. Here, val is a binary value that contains zeros and ones, and the lettercase of any leading value of b does not matter. A leading 0b value is case sensitive, so we cannot write it as 0B.The below examples explain the legal and illegal bit-value literals:
0b0011 b’3′ (3 is not a binary digit)
0B11 (0B should be written as 0b)
mysql > SET @v1 = b'1100011', @v2 = b'1100001'+0, @v3 = CAST(b'1100001' AS UNSIGNED);
It represents no data. It is not case-sensitive means we can write null literal in any language.