Example HTML page

MySQL Select Statement

In this post we are going to learn how the select statement works in MySql.

A select statement is used to retrieve the data from the database in the form of tables. The select statement produces result set as a resultant. A result set is a combination of rows and columns. We can also obtain subset of rows or columns or both of its combination by this select statement.

Let us see the syntax of select statement.

The select statement contains different clauses. Let us go through with the most commonly used  clauses and syntax of them:

1. select expression:

This represents a column which we want to retrieve. But there should be at least one select_expression.

Syntax:

select expression;

Before that just make sure that on which database you are performing.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sports             |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use sports;
Database changed

Ex: Consider 8+6 is an expression.

mysql> select 8+6;
+-----+
| 8+6 |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)


###We can also perform any arithmetic operation.
mysql> select 8+6*2;
+-------+
| 8+6*2 |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)

We can also change the column_name  by using ‘as’ keyword. ‘as’ is an optional clause. ‘as’ acts as ‘alias’ name for row/column/expression.

Ex:

mysql> select 8+6*2 as Add_mul;
+---------+
| Add_mul |
+---------+
|      20 |
+---------+
1 row in set (0.00 sec)

Here 8+6*2 expression is renaming as Add_mul. We can also rename this without using ‘as’ clause.

mysql> select 8+6*2 Add_mul;
+---------+
| Add_mul |
+---------+
|      20 |
+---------+
1 row in set (0.00 sec)

If you observe the above two outputs we can conclude that ‘as’ is optional clause.

2. select expression from tablename;

This syntax is used to retrieve a column/multiple column/all columns. From the table. If you want to display the total data from the table we can use “*” (asterisk) character.

The syntax is as follows;

select * from table_name;

Ex:

mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| players          |
+------------------+
1 row in set (0.00 sec)

mysql> select * from players;
+-----------+------+-----------+-----------+
| name      | age  | sport     | country   |
+-----------+------+-----------+-----------+
| Dhoni     |   37 | Cricket   | India     |
| Nadal     |   32 | Tennis    | Spain     |
| L Messi   |   31 | Footbal   | Barcelona |
| Mike      |   24 | Baseball  | USA       |
| Sharapova |   31 | Tennis    | Russia    |
| C Marin   |   25 | Badminton | Spain     |
+-----------+------+-----------+-----------+
6 rows in set (0.00 sec)

If you want to retrieve only ‘name’ column from the ‘Players’ table.Then

mysql> select name from players;
+-----------+
| name      |
+-----------+
| Dhoni     |
| Nadal     |
| L Messi   |
| Mike      |
| Sharapova |
| C Marin   |
+-----------+
6 rows in set (0.00 sec)

Similarly we can also retrieve two columns from the table which is as follows.

mysql> select name,country from players;
+-----------+-----------+
| name      | country   |
+-----------+-----------+
| Dhoni     | India     |
| Nadal     | Spain     |
| L Messi   | Barcelona |
| Mike      | USA       |
| Sharapova | Russia    |
| C Marin   | Spain     |
+-----------+-----------+
6 rows in set (0.00 sec)

We can also replace the ‘name’ column with ‘first_name’ and it is shown in below;

mysql> select name as first_name from players;
+------------+
| first_name |
+------------+
| Dhoni      |
| Nadal      |
| L Messi    |
| Mike       |
| Sharapova  |
| C Marin    |
+------------+
6 rows in set (0.00 sec)

Here you can observe that the name column is renamed with first_name.

3.select * from table_name where condition;

The select statement with where clause includes with conditions. ‘Where’ clause filters the rows or records from the table.

Ex: The condition for the following query is to display the name and sport column which is belonging only to “Russia”.

mysql> select name,sport from players where country="Russia";
+-----------+--------+
| name      | sport  |
+-----------+--------+
| Sharapova | Tennis |
+-----------+--------+
1 row in set (0.00 sec)

If you want to display the all the data where the sport column contains “Tennis”.Then,

mysql> select * from players where sport="tennis";
+-----------+------+--------+---------+
| name      | age  | sport  | country |
+-----------+------+--------+---------+
| Nadal     |   32 | Tennis | Spain   |
| Sharapova |   31 | Tennis | Russia  |
+-----------+------+--------+---------+
2 rows in set (0.00 sec)

Similarly we can display the data where name=”Dhoni”.

mysql> select * from players where name="Dhoni";
+-------+------+---------+---------+
| name  | age  | sport   | country |
+-------+------+---------+---------+
| Dhoni |   37 | Cricket | India   |
+-------+------+---------+---------+
1 row in set (0.00 sec)

Click here 

To watch this tutorial post on YouTube Channel.

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest