MySQL - Select Rows from Table - Step by Step Examples

MySQL – Select Rows from Table

The SELECT statement in MySQL allows you to retrieve rows from a table based on specific criteria.

In this tutorial, we’ll explore the syntax of SELECT and provide examples to show different ways of retrieving data. Before diving into the SELECT queries, we’ll walk through the steps to create a database and table, then insert some data to use in our examples.

Step 1: Create a Database

To start, we need a database to store our table. Use the CREATE DATABASE command to create a new database.

</>

Copy

CREATE DATABASE school;

This command creates a database named school. You can change the name if needed.

Step 2: Select the Database

After creating the database, use the USE command to select it so we can create tables and work with it.

</>

Copy

USE school;

Step 3: Create a Table

Now, let’s create a students table with columns for storing student details.

</>

Copy

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    rollno INT,
    age INT,
    class VARCHAR(10)
);

This command creates a students table with columns for ID, name, roll number, age, and class.

Step 4: Insert Data into the Table

Next, we insert some sample data into the students table to use in our SELECT examples.

</>

Copy

INSERT INTO students (name, rollno, age, class) VALUES ('Arjun', 14, 20, '10A');
INSERT INTO students (name, rollno, age, class) VALUES ('Raja', 15, 21, '10B');
INSERT INTO students (name, rollno, age, class) VALUES ('Prasanth', 16, 22, '10A');
INSERT INTO students (name, rollno, age, class) VALUES ('Hussain', 17, 23, '10C');
INSERT INTO students (name, rollno, age, class) VALUES ('Sai', 18, 20, '10B');
INSERT INTO students (name, rollno, age, class) VALUES ('Pranathi', 19, 21, '10A');

These commands insert six rows of data into the students table with details for each student.

Step 5: Select Rows from the Table

With data in the table, we can now use the SELECT statement to retrieve it. Here are different ways to use SELECT:

Example 1: Select All Rows

To retrieve all rows and columns from the students table, use the following query:

</>

Copy

SELECT * FROM students;

Result

MySQL Select Rows from the Table - Select all rows

This command retrieves all data in the students table.

Example 2: Select Specific Columns

To select only specific columns, such as name and age, use the following query:

</>

Copy

SELECT name, age FROM students;

Result

MySQL Select Rows from the Table - Specific Columns

This query retrieves only the name and age columns from the table.

Example 3: Select Rows with a Condition

To retrieve rows that meet a specific condition, such as students with age > 21, use the WHERE clause:

</>

Copy

SELECT name, age FROM students
WHERE age > 21;

Result

MySQL Select Rows from the Table with a condition

This query retrieves rows where age is greater than 21.

Example 4: Order Results

To sort the results by age in ascending order, use the ORDER BY clause:

</>

Copy

SELECT name, age FROM students
ORDER BY age ASC;

Result

This query orders students by age in ascending order. Use DESC to sort in descending order.

Conclusion

Using the SELECT statement, you can retrieve rows from a MySQL table with various options for filtering, sorting, and selecting specific columns. These foundational SQL commands will help you interact with your database effectively and retrieve the exact data you need.

TutorialKart

MySQL – Select Rows from Table