Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Literals

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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 
numeric literal
100
Example
Following example displays an integer literal with positive sign (+) 
SELECT -100 AS 'numeric literal';
Output
The output is obtained as follows 
numeric literal
-100
Example
Following example displays an integer literal with negative sign (-) 
SELECT +493 AS 'numeric literal';
Output
The output is obtained as follows 
numeric literal
493
Example
Following example displays a floating point literal 
SELECT 109e-06 AS 'numeric literal';
Output
The output is obtained as follows 
numeric literal
0.000109
Example
Following example displays a decimal literal 
SELECT 793.200 AS 'numeric literal';
Output
The output is obtained as follows 
numeric literal
793.200
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 
string literal
tutorialspoint
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 
string literal
tutorials point india
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 
boolean literal
1
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 
boolean literal
0
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 
Date literal
2023-04-20
Example
In this example, we will display a date literal formatted as 'YYYYMMDD'
SELECT '20230420' AS 'Date literal';
Output
Following output is obtained 
Date literal
20230420
Example
In this example, we will display a date literal formatted as YYYYMMDD
SELECT 20230420 AS 'Date literal';
Output
Following output is obtained 
Date literal
20230420
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 
Date literal
23-04-20
Example
In this example, we will display a date literal formatted as 'YYMMDD'
SELECT '230420' AS 'Date literal';
Output
Following output is obtained 
Date literal
230420
Example
In this example, we will display a date literal formatted as YYMMDD
SELECT 230420 AS 'Date literal';
Output
Following output is obtained 
Date literal
230420
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 
Time literal
10:45:50
Example
In this example, we are displaying a time literal formatted as HHMMSS.
SELECT 104550 AS 'Time literal';
Output
Following output is obtained 
Time literal
104550
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 
Time literal
10:45
Example
In this example, we are displaying a time literal formatted as 'MMSS'.
SELECT '4510' AS 'Time literal';
Output
Following output is obtained 
Time literal
4510
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 
Time literal
10
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 
Time literal
4 09:30:12
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 
Time literal
4 09:30
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 
Time literal
4 09
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 
datetime literal
2023-04-20 09:45:10
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 
datetime literal
20230420094510
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 
datetime literal
23-04-20 09:45:10
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 
datetime literal
230420094510
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 
NULL literal
NULL
Client Program
We can also use Literals in a MySQL database using a Client Program.
Syntax
PHPNodeJSJavaPython
To perform literals through a PHP program, we need to execute the required query using the mysqli function query() as follows 
$sql = "SELECT 100 AS 'Numerical_literal'";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$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();
Output
The output obtained is as shown below 
Select query executed successfully...!
Numerical literal: 100
Select query executed successfully...!
String Literal: Tutorialspoint
Select query executed successfully...!
Boolean literal: 1   
﻿
﻿
﻿
﻿
﻿

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Literals

Numeric Literals

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

String Literals

Example

Output

Example

Output

Boolean Literals

Example

Output

Example

Output

Date and Time Literals

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Null Literals

Example

Output