MySQL - Literals

In MySQL, literals are fixed values (constants) that can be used in SQL statements such as SELECT, INSERT, UPDATE, and DELETE. We can use a literal in SQL statements without needing to be represented by a variable or an expression.

Following are some common MySQL literals:

• Numeric Literals

• String Literals

• Boolean Literals

• Date and Time Literals

• NULL Literals

Numeric Literals

The MySQL numeric literals are numeric values that can represent positive or negative numbers, including both integers and floating-point values.

If we do not specify any sign (i.e. positive (+) or negative (-)) to a numeric value, then a positive value is assumed.

Let us see some examples by using various numeric literals in SQL queries.

Example

Following example displays an integer literal with no sign (by default positive sign will be considered)

SELECT 100 AS 'numeric literal';

Output

The output is obtained as follows −

Example

Following example displays an integer literal with positive sign (+) −

SELECT -100 AS 'numeric literal';

Output

The output is obtained as follows −

Example

Following example displays an integer literal with negative sign (-) −

SELECT +493 AS 'numeric literal';

Output

The output is obtained as follows −

Example

Following example displays a floating point literal −

SELECT 109e-06 AS 'numeric literal';

Output

The output is obtained as follows −

Example

Following example displays a decimal literal −

SELECT 793.200 AS 'numeric literal';

Output

The output is obtained as follows −

String Literals

The MySQL string literals are character strings that are enclosed within the single quotes (') or double quotes (").

Let us see some examples where string literals in SQL queries are used in different ways.

Example

In this example, we are displaying a string literal enclosed in single quotes −

SELECT 'tutorialspoint' AS 'string literal';

We can use double quotes to enclose a string literal as follows −

SELECT "tutorialspoint" AS 'string literal';

Output

Following output is obtained in both cases −

Example

In this example, we are displaying a string literal with spaces enclosed in single quotes −

SELECT 'tutorials point india' AS 'string literal';

We can also enclose this string literal (spaces included) in double quotes −

SELECT "tutorials point india" AS 'string literal';

Output

Following output is obtained with both queries −

Boolean Literals

The MySQL Boolean literals are logical values that evaluate to either 1 or 0. Let us see some example for a better understanding.

Example

There are various ways a boolean value is evaluated to true in MySQL. Here, we use the integer 1 as a boolean literal −

SELECT 1 AS 'boolean literal';

We can also use the keyword TRUE to evaluate the boolean literal to 1.

SELECT TRUE AS 'boolean literal';

We can also use the lowercase of the keyword TRUE, as true, to evaluate the boolean literal to 1.

SELECT true AS 'boolean literal';

Output

Following output is obtained −

Example

Similarly, there are multiple ways a boolean value is evaluated to false in MySQL. Here, we use the integer 0 as a boolean literal −

SELECT 0 AS 'boolean literal'; 

We can also use the keyword FALSE to evaluate the boolean literal to 0.

SELECT FALSE AS 'boolean literal';

We can also use the lowercase of the keyword FALSE, as false, to evaluate the boolean literal to 0.

SELECT false AS 'boolean literal';

Output

Following output is obtained −

Date and Time Literals

The MySQL date and time literals represent date and time values. Let us see examples to understand how date and time values are represented in various ways in MySQL.

Example

In this example, we will display a date literal formatted as 'YYYY-MM-DD'

SELECT '2023-04-20' AS 'Date literal';

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as 'YYYYMMDD'

SELECT '20230420' AS 'Date literal';

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as YYYYMMDD

SELECT 20230420 AS 'Date literal';

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as 'YY-MM-DD'

SELECT '23-04-20' AS 'Date literal';

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as 'YYMMDD'

SELECT '230420' AS 'Date literal';

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as YYMMDD

SELECT 230420 AS 'Date literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'HH:MM:SS'.

SELECT '10:45:50' AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as HHMMSS.

SELECT 104550 AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'HH:MM'.

SELECT '10:45' AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'MMSS'.

SELECT '4510' AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'SS'.

SELECT '10' AS 'Time literal';

Here, let us display a time literal formatted as SS.

SELECT 10 AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'D HH:MM:SS' where D can be a day value between 0 and 34.

SELECT '4 09:30:12' AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'D HH:MM' where D can be a day value between 0 and 34.

SELECT '4 09:30' AS 'Time literal';

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as 'D HH' where D can be a day value between 0 and 34.

SELECT '4 09' AS 'Time literal'; 

Output

Following output is obtained −

Example

In this example, we are displaying a Datetime literal formatted as 'YYYY-MM-DD HH:MM:SS'.

SELECT '2023-04-20 09:45:10' AS 'datetime literal';

Output

Following output is obtained −

Example

In this example, we are displaying a Datetime literal formatted as 'YYYYMMDDHHMMSS'.

SELECT '20230420094510' AS 'datetime literal';

Now, we are displaying a Datetime literal formatted as YYYYMMDDHHMMSS.

SELECT 20230420094510 AS 'datetime literal';

Output

Both queries produce the same output as follows −

Example

In this example, we are displaying a Datetime literal formatted as 'YY-MM-DD HH:MM:SS'.

SELECT '23-04-20 09:45:10' AS 'datetime literal';

Output

Following output is obtained −

Example

In this example, we are displaying a Datetime literal formatted as 'YYMMDDHHMMSS'.

SELECT '230420094510' AS 'datetime literal';

Here, we are displaying a Datetime literal formatted as YYMMDDHHMMSS.

SELECT 230420094510 AS 'datetime literal';

Output

Both queries give the same following output −

Null Literals

The MySQL Null literals represents the absence of a value. It is case in-sensitive.

Example

Following are some examples of valid NULL literals −

SELECT NULL AS 'NULL literals'; 

In lowercase −

SELECT null AS 'NULL literals';

Output

Following output is obtained −

Client Program

We can also use Literals in a MySQL database using a Client Program.

Syntax

$sql = "SELECT 100 AS 'Numerical_literal'";
$mysqli->query($sql);

sql = "SELECT 100 AS 'numeric literal'";
con.query(sql)

String sql  "SELECT 100 AS 'Numerical_literal'";
statement.executeQuery(sql);

literal_query = "SELECT 100 AS 'numeric literal'"
cursorObj.execute(literal_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SELECT 100 AS 'Numerical_literal'";
If($result = $mysqli->query($sql)){
    printf("Select query executed successfully...!\n");
    while($row = mysqli_fetch_array($result)){
        printf("Numerical literal: %d", $row["Numerical_literal"]);
    }
printf("\n");
}
$sql = "SELECT 'Tutorialspoint' AS 'String_literal'";
If($result = $mysqli->query($sql)){
    printf("Select query executed successfully...!\n");
    while($row = mysqli_fetch_array($result)){
        printf("String Literal: %s", $row["String_literal"]);
    }
}
printf("\n");
$sql = "SELECT 1 AS 'Boolean_literal'";
If($result = $mysqli->query($sql)){
    printf("Select query executed successfully...!\n");
    while($row = mysqli_fetch_array($result)){
        printf("Boolean literal: %s", $row["Boolean_literal"]);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Select query executed successfully...!
Numerical literal: 100
Select query executed successfully...!
String Literal: Tutorialspoint
Select query executed successfully...!
Boolean literal: 1   

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    //integer literal with no sign (by default positive sign will be considered)
    sql = "SELECT 100 AS 'numeric literal'";
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });

    //string with single quotes
    sql = "SELECT 'tutorialspoint' AS 'string literal';"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });

    //Boolean Literals
    sql = "SELECT 1 AS 'boolean literal';"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });

    //date literal formatted as 'YYYY-MM-DD'
    sql = "SELECT '2023-04-20' AS 'Date literal';"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});  

 
 Connected!
 --------------------------
 [ { 'numeric literal': 100 } ]
 [ { 'string literal': 'tutorialspoint' } ]
 [ { 'boolean literal': 1 } ]
 [ { 'Date literal': '2023-04-20' } ] 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Literals {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //numerical literal
            String sql = "SELECT 100 AS 'Numerical_literal'";
            rs = st.executeQuery(sql);
            System.out.println("Numerical literal: ");
            while(rs.next()) {
              String nl = rs.getString("Numerical_literal");
              System.out.println(nl);
            }
          //String literal
            String sql1 = "SELECT 'Tutorialspoint' AS 'String_literal'";
            rs = st.executeQuery(sql1);
            System.out.println("String literal: ");
            while(rs.next()) {
              String nl = rs.getString("String_literal");
              System.out.println(nl);
            }
          //Boolean literal
            String sql2 = "SELECT 1 AS 'Boolean_literal'";
            rs = st.executeQuery(sql2);
            System.out.println("Boolean literal: ");
            while(rs.next()) {
              String nl = rs.getString("Boolean_literal");
              System.out.println(nl);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Numerical literal: 
100
String literal: 
Tutorialspoint
Boolean literal: 
1

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Integer literal with no sign (by default positive sign will be considered)
literal_query = "SELECT 100 AS 'numeric literal'"
cursorObj.execute(literal_query)
result_numeric = cursorObj.fetchone()
print("Numeric Literal:")
print(result_numeric[0])
# String literal with single quotes
string_literal = "SELECT 'tutorialspoint' AS 'string literal';"
cursorObj.execute(string_literal)
result_string = cursorObj.fetchone()
print("\nString Literal:")
print(result_string[0])
# Boolean literal, evaluates to 1 (true)
boolean_literal = "SELECT 1 AS 'boolean literal';"
cursorObj.execute(boolean_literal)
result_boolean = cursorObj.fetchone()
print("\nBoolean Literal:")
print(result_boolean[0])
# Date literal formatted as 'YYYY-MM-DD'
date_time_literal = "SELECT '2023-04-20' AS 'Date literal';"
cursorObj.execute(date_time_literal)
result_date = cursorObj.fetchone()
print("\nDate Literal:")
print(result_date[0])
# Closing the cursor and connection
cursorObj.close()
connection.close()   

Numeric Literal:
100 
 
String Literal:
tutorialspoint

Boolean Literal:
1

Date Literal:
2023-04-20