MySQL – Select Query
Table of content
- MySQL Select Statement
- Fetching Data Using SELECT from Command Prompt
- Computing using SELECT in Command Prompt
- Aliasing a Column in SELECT Statement
- Select Query into MySQL Database Using a Client Program
”;
Now that we have learned how to create tables in MySQL and insert values into it in the previous tutorials, the next step is to check whether the values are recorded in this table or not. To do this, one must use the SELECT statement to retrieve and view the records in that specific table.”
MySQL Select Statement
The MySQL SELECT command is used to fetch data from the MySQL database in the form of a result table. These result tables are called result-sets.
Note − We can use this command at ”mysql>” prompt as well as in any script like PHP, Node.js, Java, python, etc.
Syntax
Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE Clause] [OFFSET M ][LIMIT N]
-
You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
-
We can fetch one or more fields in a single SELECT command.
-
We can specify star (*) in place of fields. In this case, SELECT will return all the fields.
-
We can specify any condition using the WHERE clause.
-
We can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
-
We can limit the number of returns using the LIMIT attribute.
Fetching Data Using SELECT from Command Prompt
This will use SQL SELECT command to fetch data from an MySQL table.
Example
First of all, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''Kaushik'', 23, ''Kota'', 2000.00 ), (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ), (5, ''Hardik'', 27, ''Bhopal'', 8500.00 ), (6, ''Komal'', 22, ''Hyderabad'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 );
To Retrieve All Fields
Now, to retrieve all the data present in a CUSTOMERS table, we use the following SELECT statement −
SELECT * from CUSTOMERS;
Following are the records present in the CUSTOMERS table −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
To Retrieve Selective Fields
Here, we are retrieving only three columns/fields, ID, NAME, and ADDRESS present in the CUSTOMERS table, using the following query −
SELECT ID, NAME, ADDRESS FROM CUSTOMERS;
On executing the given query, the output is displayed as follows −
| ID | NAME | ADDRESS |
|---|---|---|
| 1 | Ramesh | Ahmedabad |
| 2 | Khilan | Delhi |
| 3 | Kaushik | Kota |
| 4 | Chaitali | Mumbai |
| 5 | Hardik | Bhopal |
| 6 | Komal | Hyderabad |
| 7 | Muffy | Indore |
Computing using SELECT in Command Prompt
The SELECT statement is not only used to fetch data from tables but can also be used to get the results of mathematical computations in a tabular format. In these cases, you don”t have to mention a specific database table in the SELECT statement.
Following is the syntax to do so −
SELECT [math_computation];
Example
In the following example, let us solve a mathematical computation using the SELECT statement −
SELECT 46475*453;
Output
The output for the program query is produced as given below −
| 46475*453 |
|---|
| 21053175 |
Aliasing a Column in SELECT Statement
MySQL database provides a method to alias column names into a more understandable and relative name when being displayed. This is done using the ”AS” keyword. This keyword is used in the SELECT statement as well.
Following is the syntax to do so −
SELECT column_name AS alias_name FROM table_name;
You can also use an alias to display select expressions with the same syntax; you should use a select expression instead of column_name in the syntax.
Example
In the example below, we are retrieving the ID column from the previously created CUSTOMERS table. We aliased the ID column as “Identity_Document” −
SELECT ID AS Identity_Document FROM CUSTOMERS;
As we can see the output, the alias name ”Identity_Document” has been used instead of ”ID”.
| Identity_Document |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
Select Query into MySQL Database Using a Client Program
Besides getting data from a table using the SELECT query, you can also use a client program to perform the SELECT operation on a table.
Syntax
Following are the syntaxes of this operation in various programming languages −
To fetch data from a MySQL table through a PHP program, we need to execute the SELECT statement using the mysqli function query() as −
$sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME"; $mysqli->query($sql);
To fetch data from a MySQL table through a Node.js program, we need to execute the SELECT statement using the query() function of the mysql2 library as −
sql="SELECT field1, field2,...fieldN FROM table_name"; con.query(sql);
To fetch data from a MySQL table through a Java program, we need to execute the SELECT statement using the JDBC function executeUpdate() as −
String sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME"; statement.executeQuery(sql);
To fetch data from a MySQL table through a Python program, we need to execute the SELECT statement using the execute() function of the MySQL Connector/Python as −
select_query = "SELECT COLUMN1, COLUMN2,.. FROM TABLE_NAME"; cursorObj.execute(select_query);
Example
Following are the programs −
$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM tutorials_tbl";
if($result = $mysqli->query($sql)){
printf("Select statement executed successfully..! ");
printf("Records are: ");
while($row = mysqli_fetch_row($result)){
print_r ($row);
}
}
if($mysqli->error){
printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows −
Select statement executed successfully..! Records are:
Array
(
[0] => 1
[1] => MySQL Tut
[2] => unknown
[3] => 2023-07-25
)
Array
(
[0] => 2
[1] => PHP Tut
[2] => unknown2
[3] => 2023-08-12
)
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!");
//Creating a Database
sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
con.query(sql);
//Selecting a Database
sql = "USE TUTORIALS"
con.query(sql);
//Creating a Table
sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE)"
con.query(sql);
//Inserting records into table
sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, ''Learn PHP'', ''John Paul'', NOW()), (2, ''Learn MySQL'', ''Abdul S'', NOW()), (3, ''JAVA Tutorial'', ''Sanjay'', ''2007-05-06''), (4, ''Python Tutorial'', ''Sasha Lee'', ''2016-09-04''), (5, ''Hadoop Tutorial'', ''Chris Welsh'', NOW())"
con.query(sql);
//Selecting all the records from table
sql = "SELECT * FROM tutorials_tbl"
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});
Output
The output produced is as follows −
Connected!
[
{
tutorial_id: 1,
tutorial_title: ''Learn PHP'',
tutorial_author: ''John Paul'',
submission_date: 2023-07-25T18:30:00.000Z
},
{
tutorial_id: 2,
tutorial_title: ''Learn MySQL'',
tutorial_author: ''Abdul S'',
submission_date: 2023-07-25T18:30:00.000Z
},
{
tutorial_id: 3,
tutorial_title: ''JAVA Tutorial'',
tutorial_author: ''Sanjay'',
submission_date: 2007-05-05T18:30:00.000Z
},
{
tutorial_id: 4,
tutorial_title: ''Python Tutorial'',
tutorial_author: ''Sasha Lee'',
submission_date: 2016-09-03T18:30:00.000Z
},
{
tutorial_id: 5,
tutorial_title: ''Hadoop Tutorial'',
tutorial_author: ''Chris Welsh'',
submission_date: 2023-07-25T18:30:00.000Z
}
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectQuery {
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...!");
String sql = "SELECT * FROM tutorials_tbl";
rs = st.executeQuery(sql);
System.out.println("Select query executed successfully..!");
System.out.println("Table records: ");
while(rs.next()) {
String tutorial_id = rs.getString("tutorial_id");
String tutorial_title = rs.getString("tutorial_title");
String tutorial_author = rs.getString("tutorial_author");
String submission_date = rs.getString("submission_date");
System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " + tutorial_author + ", Submission_date: " + submission_date);
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
Output
The output obtained is as shown below −
Select query executed successfully..! Table records: Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08
import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
host=''localhost'',
user=''root'',
password=''password'',
database=''tut''
)
table_name = ''tutorials_tbl''
#Creating a cursor object
cursorObj = connection.cursor()
select_query = f"SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM {table_name}"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Tutorial Table Data:")
for row in result:
print(row)
cursorObj.close()
connection.close()
Output
Following is the output of the above code −
Derived Table Result: Tutorial Table Data: (1, ''Learn PHP'', ''John Paul'', datetime.date(2023, 3, 28)) (2, ''Learn MySQL'', ''Abdul S'', datetime.date(2023, 3, 28)) (3, ''JAVA Tutorial'', ''Sanjay'', datetime.date(2007, 5, 6)) (4, ''Python Tutorial'', ''Sasha Lee'', datetime.date(2016, 9, 4)) (5, ''Hadoop Tutorial'', ''Chris Welsh'', datetime.date(2023, 3, 28)) (6, ''R Tutorial'', ''Vaishnav'', datetime.date(2011, 11, 4))
”;