MySQL – Literals
Table of content
- Numeric Literals
- String Literals
- Boolean Literals
- Date and Time Literals
- Null Literals
- Client Program
”;
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
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);
To perform literals through a JavaScript program, we need to execute the required query using the query() function of mysql2 library as follows −
sql = "SELECT 100 AS ''numeric literal''"; con.query(sql)
To perform literals through a Java program, we need to execute the required query using the JDBC function executeQuery() as follows −
String sql "SELECT 100 AS ''Numerical_literal''"; statement.executeQuery(sql);
To perform literals through a Python program, we need to execute the required query using the execute() function of the MySQL Connector/Python as follows −
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();
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
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); }); });
Output
The output obtained is as shown below −
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(); } } }
Output
The output obtained is as shown below −
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()
Output
The output obtained is as shown below −
Numeric Literal: 100 String Literal: tutorialspoint Boolean Literal: 1 Date Literal: 2023-04-20