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.

Select Random Records
Have you ever taken online examinations? If yes, then did you ever wonder how is the order, in which these questions are displayed, random? These questions are usually stored in a database of the test application and are randomly displayed one by one.
While using a database for an application, there arise situations where the records from a table object need to be selected randomly. MySQL does not have a built-in provision for this.
Selecting Random Records in MySQL
In order to select random records in MySQL, you can use the ORDER BY RAND() clause. The RAND() function is used with the SELECT query to retrieve the stored data one by one or collectively together.
The MySQL RAND() Function
The MySQL RAND() Function returns a result-set containing all records of the original table in a completely random order. It is usually used with a SELECT statement in the ORDER BY clause.
Syntax
Following is the basic syntax of the RAND() function with ORDER BY Clause 
SELECT column_name(s) FROM table_name ORDER BY RAND();
Example
Following example demonstrates the usage of RAND() function when used with ORDER BY Clause. Here, let us first create a table 'CUSTOMERS' and insert some values into it.
CREATE TABLE CUSTOMERS(
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(20),
   AGE int,
   PRIMARY KEY(Id)
);
Now, insert values into this table using the INSERT statement as follows 
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('John',23);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Larry',21);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('David',21);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Carol',24);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Bob',27);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Mike',29);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Sam',26);
The CUSTOMERS table obtained is as follows 
ID
NAME
AGE
1
John
23
2
Larry
21
3
David
21
4
Carol
24
5
Bob
27
6
Mike
29
7
Sam
26
Now, let us use the RAND() function with the SELECT statement to retrieve the records of the CUSTOMERS table in a randomized order 
SELECT * FROM CUSTOMERS ORDER BY RAND();
Output
Following is the output of the above query 
ID
NAME
AGE
6
Mike
29
4
Carol
24
3
David
21
1
John
23
5
Bob
27
7
Sam
26
2
Larry
21
LIMIT with RAND() Function
You can also limit the number of randomly retrieved records using the LIMIT clause with the RAND() function
Syntax
Following is the syntax to use LIMIT with RAND() function 
SELECT column_name(s) FROM table_name 
ORDER BY RAND() LIMIT int_value;
Example
In this example, we are retrieving a limited number of records at random from the 'CUSTOMERS' table using the following query 
SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;
Output of the above code is as shown below 
ID
NAME
AGE
7
Sam
26
Each time you execute this query, you will get a different random record 
SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;
The result produced is as follows −
ID
NAME
AGE
6
Mike
29
You can also increase the limit of records to be displayed by modifying the LIMIT value as shown below 
SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 2;
We get the output as shown below 
ID
NAME
AGE
1
John
23
3
David
21
Random Records Using Client Program
We can also select random records using client program.
Syntax
PHPNodeJSJavaPython
To select random records through a PHP program, we need to execute the RAND() function using the mysqli function query() as follows 
$sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
$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.');
//let's create a table
$sql = "create table CUSTOMERS ( Id int NOT NULL AUTO_INCREMENT, Name varchar(20), Age int, PRIMARY KEY(Id) )";
if($mysqli->query($sql)){
    printf("CUSTOMERS table created successfully...!\n");
}
//now lets insert some records
$sql = "insert into CUSTOMERS(Name,Age) values('John',23)";
if($mysqli->query($sql)){
    printf("First record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Larry',21)";
if($mysqli->query($sql)){
    printf("Second record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('David',21)";
if($mysqli->query($sql)){
    printf("Third record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Carol',24)";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Bob',27)";
if($mysqli->query($sql)){
    printf("Fifth record inserted successfully....!\n");
}
//display table record
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Age: %d", 
            $row['Id'], 
            $row['Name'], 
            $row['Age']);
            printf("\n");
    }
}
//lets find random record
$sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
if($result = $mysqli->query($sql)){
    printf("Table records(random record): \n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Age: %d", 
            $row['Id'], 
            $row['Name'], 
            $row['Age']);
            printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
CUSTOMERS table created successfully...!
First record inserted successfully....!
Second record inserted successfully....!
Third record inserted successfully....!
Fourth record inserted successfully....!
Fifth record inserted successfully....!
Table records:
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 3, Name: David, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27
Table records(random record):
Id: 3, Name: David, Age: 21
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27
﻿
﻿
﻿
﻿

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

Select Random Records

Selecting Random Records in MySQL

The MySQL RAND() Function

Syntax

Example

Output

LIMIT with RAND() Function

Syntax

Example

Random Records Using Client Program

Syntax

Example

Output