MySQL
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.
employee
comments
We use the SELECT * FROM table_name command to select all the columns of a given table.
SELECT * FROM table_name
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)
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.
employeeid
firstname
lastname
mysql> SELECT employeeid, firstname, lastname FROM employee;
+------------+-----------+----------+ | 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.
commentid
commentbody
mysql> SELECT commentid, commentbody FROM comments;
+-----------+-------------+ | commentid | commentbody | +-----------+-------------+ | 1 | Awesome | | 2 | Hello World | | 3 | Happy | +-----------+-------------+ 3 rows in set (0.00 sec)
We can given new names to the columns in the SELECT query by using AS followed by the new name.
AS
In the following example we are giving the employeeid column a new name id.
id
mysql> SELECT employeeid AS id, firstname, lastname FROM employee;
+-----+-----------+----------+ | id | firstname | lastname | +-----+-----------+----------+ | e01 | Yusuf | Shakeel | | e02 | John | Doe | | e03 | Jane | Doe | +-----+-----------+----------+ 3 rows in set (0.00 sec)
We use the CONCAT() function to concatenate columns in MySQL.
CONCAT()
In the following example we are concatenating firstname and lastname column value and giving it a new name employee_name.
employee_name
mysql> SELECT employeeid, CONCAT(firstname, lastname) AS employee_name FROM employee;
+------------+---------------+ | 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)