MySQL - SELECT FROM Table

In this tutorial we will learn to select data from tables in MySQL.

We will be using the employee and comments table that we created in the CREATE Table tutorial.

Select all columns of a table

We use the SELECT * FROM table_name command to select all the columns of a given table.

In the following example we are selecting all the columns of the employee table.

mysql> SELECT * FROM employee;

And we get the following output.

+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email               | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com   |     0 | 1900-01-01 | 2018-01-01 01:01:01 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com |     8 | 1900-02-03 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com |     6 | 1900-05-20 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

In the following example we are selecting all the columns of the comments table.

mysql> SELECT * FROM comments;

Output:

+-----------+------------+-------------+---------------------+---------------------+
| commentid | employeeid | commentbody | lastmodified        | created             |
+-----------+------------+-------------+---------------------+---------------------+
|         1 | e03        | Awesome     | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy       | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
+-----------+------------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

Selecting specific column of a table

We may not want to retrieve all the columns of a table all the time so, instead of passing the * we mention the name of the columns that we are interested in.

In the following example we are selecting employeeid, firstname and lastname of all the employees from the employee table.

mysql> SELECT employeeid, firstname, lastname FROM employee;

Output:

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        | Yusuf     | Shakeel  |
| e02        | John      | Doe      |
| e03        | Jane      | Doe      |
+------------+-----------+----------+
3 rows in set (0.00 sec)

Similarly, in the following example we are selecting commentid and commentbody from the comments table.

mysql> SELECT commentid, commentbody FROM comments;

Output:

+-----------+-------------+
| commentid | commentbody |
+-----------+-------------+
|         1 | Awesome     |
|         2 | Hello World |
|         3 | Happy       |
+-----------+-------------+
3 rows in set (0.00 sec)

Giving new name to the selected columns

We can given new names to the columns in the SELECT query by using AS followed by the new name.

In the following example we are giving the employeeid column a new name id.

mysql> SELECT employeeid AS id, firstname, lastname FROM employee;

Output:

+-----+-----------+----------+
| id  | firstname | lastname |
+-----+-----------+----------+
| e01 | Yusuf     | Shakeel  |
| e02 | John      | Doe      |
| e03 | Jane      | Doe      |
+-----+-----------+----------+
3 rows in set (0.00 sec)

Concat two columns in SELECT query

We use the CONCAT() function to concatenate columns in MySQL.

In the following example we are concatenating firstname and lastname column value and giving it a new name employee_name.

mysql> SELECT employeeid, CONCAT(firstname, lastname) AS employee_name FROM employee;

Output:

+------------+---------------+
| employeeid | employee_name |
+------------+---------------+
| e01        | YusufShakeel  |
| e02        | JohnDoe       |
| e03        | JaneDoe       |
+------------+---------------+
3 rows in set (0.00 sec)

As we can see that a space is missing between the firstname and the lastname so, we will include a space character between the two in the CONCAT. Feel free to experiment and add new strings and characters.

mysql> SELECT employeeid, CONCAT(firstname, ' ', lastname) AS employee_name FROM employee;

Now the output looks good.

+------------+---------------+
| employeeid | employee_name |
+------------+---------------+
| e01        | Yusuf Shakeel |
| e02        | John Doe      |
| e03        | Jane Doe      |
+------------+---------------+
3 rows in set (0.00 sec)