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.

MySQL
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:

  • string
  • numeric
  • logical
  • NULL
  • date and time literal

String literals

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".

  • MySQL
    SELECT "Line Another line" as String
    
    String
    Line Another line
  • MySQL
    SELECT "Line \n Another line" as String
    
    String
    Line Another line

Numeric literals

Example
Includes integers and floating-point numbers. The decimal separator for a floating-point number is "." (dot).1, 2.9, 0.01
Can have only an integer part, a fractional part, or both..2, 1.1, 10
Can be positive or negative (it's not necessary to specify a sign for a positive number).+1, -10, -2.2
Can be represented in exponential notation.1e3 (=1000) 1e-3 (=0.001)

Arithmetic operators

For numeric literals, SQL has all the arithmetic operators we are familiar with:

OperatorDescriptionExample
%, MODModulus division11 % 5 = 1
*Multiplication10 * 16 = 160
+Addition98 + 2 = 100
-Subtraction50 - 51 = -1
/Division1 / 2 = 0.5
DIVDivision10 DIV 4 = 2

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

For example:

MySQL
SELECT (5 * 2 - 6) / 2 AS Result;
Result
2

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:

MySQL
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.

DescriptionFormat
DateInterpreted as a date with zero timeYYYY-MM-DD, YYYYMMDD

Any punctuation mark can be used instead of the "-" separator.

For example:
'2020-01-01' = January 1st, 2020, 00:00:00
TimeContains only time without a specific datehh:mm:ss, hh:mm, hh, ss

The separator can also be omitted.

For example:
12:11 = 12:11:00
Date and timeDate with the possibility of setting a specific timeYYYY-MM-DD hh:mm:ss, YYYYMMDDhhmmss

For example:
'20200101183030' = January 1st, 2020, 18:30:30

Logical literals

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.

NULL

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.