Khóa học miễn phí MySQL – Select Query nhận dự án làm có lương

MySQL – Select Query

Table of content


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))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc