Chapter 5: Selecting Data Part 1

 

In this chapter, we’ll learn to select data from our database. This is a relatively large topic, so we’ll be splitting it into three chapters.

 

The data used for selection are from the two tables (employees and mentorships) that we created in the previous chapters. These tables can be found in Appendix A for easy reference.

 

In the first chapter, we’ll learn to select data from a single table.

 

Basic Select Syntax

 

To select data from a single table, we use the syntax

 

SELECT column_names_or_other_information

[AS alias]

[ORDER BY column(s)] [DESC]

FROM table_name

[WHERE condition];

 

Let’s look at some examples.

 

Selecting Everything

 

If we want to select all the columns and rows from a table, we write

 

SELECT * FROM table_name;

 

For instance, as we saw in the previous chapter, to select all data from the employees table, we write

 

SELECT * FROM employees;

 

In the statement above, we did not add a WHERE clause. Without the WHERE clause, MySQL gives us all the rows in the table.

 

In addition, we used the * symbol to indicate that we want to select all columns from the employees table.

 

Filtering Columns

 

If we do not want to select all columns, we can list the columns we want.

 

Suppose we only want to select the em_name and gender columns from the employees table, we write

 

SELECT em_name, gender from employees;

 

The table below shows part of the results from this SELECT statement:

 

 

Using Aliases

 

If you study the table above, you’ll notice that the table uses the column names (“em_name” and “gender”) as its column headings.

 

If we want the column heading to display a different name instead, we can use an alias for the column.

 

We do that using the AS keyword as shown in the following statement:

 

SELECT em_name AS `Employee Name`, gender AS Gender FROM employees;

 

In the statement above, note that we use the backtick character (normally found at the top left corner of the keyboard, together with the tilde ~ character) to enclose the first alias...

Filtering Rows

 

Next, let’s look at how we can filter rows when selecting data.

 

LIMIT

 

To do that, we can use the LIMIT keyword. This limits the number of rows retrieved by the SELECT statement. For instance, if we write

 

SELECT em_name AS 'Employee Name', gender AS Gender FROM employees LIMIT 3;

 

we’ll get

 

 

Only the first three rows are displayed.

 

DISTINCT

 

Another way to filter rows is to remove duplicates. This can be achieved using the DISTINCT keyword.

 

If we write

 

SELECT gender FROM employees;

 

we’ll get

 

M
M

F

F

M

F

M

F

M

 

as the output.

 

This means that the first two rows in our table have gender = M while the third and fourth rows have gender = F etc.

 

If we want to remove the duplicates, we can write

 

SELECT DISTINCT(gender) FROM employees;

 

We’ll get

 

M

F

 

as the output...

Sorting Rows

 

Now that we know how to select data from our tables and filter the results, let’s cover one last concept before we end this chapter. Let’s look at how we can sort the results returned by the SELECT statement.

 

To do that, we use the ORDER BY clause. We can choose to sort the results based on one or more columns.

 

Suppose we want to sort the rows of the employees table using gender, followed by the employee’s name (em_name), we write

 

SELECT * FROM employees ORDER BY gender, em_name;

 

This results in the records of female employees being displayed first, followed by male employees (since the letter F comes before M).

 

Within each gender, the records will be sorted by the employees’ names.

 

Note that the default sorting order is ascending. If we want to sort by descending order, we use the DESC keyword as shown below:

 

SELECT * FROM employees ORDER BY gender DESC, em_name;

 ...