Literals in SQL
In the last lesson, a string was output, and if we speak in a more formal language, then the so-called string literal.
SELECT "Hello world"
A literal is an explicitly specified fixed value, such as the number 12 or the string `"SQL"'.
The main types of literals in MySQL are:
- date and time literal
A string is a sequence of characters enclosed in single (') or double (") quotation marks. For example, 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 "new line", and without the preceding slash it would be the usual letter "n".
SELECT "Line Another line" as String
SELECT "Line \n Another line" as String
For numeric literals, SQL has all the arithmetic operators we are familiar with:
Using these operators, you can construct any arithmetic expression by applying the standard rules of arithmetic.
SELECT (5 * 2 - 6) / 2 AS Result;
Date and Time Literals
Date and time values can be represented as strings or numbers.
For example, if we want to specify a date in a query, we can do this using the string "1970-12-30", "19701230", or the number 19701230. In both cases, these values will be interpreted as the date "December 30th, 1970".
Here is an example of using a date literal:
SELECT * FROM FamilyMembers WHERE birthday > '1970-12-30'
You don't need to pay attention to what this query specifically does, we'll look at it later, but right now it's important to focus on the syntax of how we can specify a date in a query.
Above, we looked at how to specify a date, but in addition to a date, we can also specify a time or both together.
A logical literal is a value of TRUE or FALSE, which indicates the truthfulness or falsehood of a statement. When interpreting a query, MySQL converts them into numbers: TRUE and FALSE become 1 and 0, respectively.
The value NULL means "no data" or "no value." It is used to visually distinguish between empty values, such as a zero-length string or a space, and the absence of a value altogether.