MYSQL FOR BEGINNERS! - PART TWO

Intro

In our first tutorial we didn't actually do any data gathering, we just setup the environment and DB were working with. So in this tutorial we're going to grab some information from the DB!

Above you have the diagram of our Database. With the exception of Departments we have direct links between each table.

Something easy to start with

So as were dealing with "Employee's" why not grab a list of the employee's.

Now in MySQL and SQL in general you SELECT data FROM tables and that's exactly what we want to do here.

We want to SELECT data FROM the employees table.

SELECT
    employees.emp_no,
    employees.birth_date,
    employees.first_name,
    employees.last_name,
    employees.gender,
    employees.hire_date
FROM
    employees

Really simple stuff! but... can you see something annoying here?

I've had to type employees 6 times whilst grabbing the data... so what can we do to combat this?

Aliasing

Aliasing meas we give a table an "alias" when calling it in the select this becomes very handy when working with numerous tables and joins.

To alias you simply need to assign the table a string of some sort (generally a single letter) in this instance were going to alias the table to 'e'.

SQL:

SELECT
    e.emp_no,
    e.birth_date,
    e.first_name,
    e.last_name,
    e.gender,
    e.hire_date
FROM
    employees e

Output:

Much better! now when trying to access a column from a table i can type e. And it will usually give me column options automatically, this is incredibly helpful when writing big SQL Statements.

We can make the above table even easier to read. What if we want all columns out of a single table? and don't need any filtering? Simple, Use and Asterisk (*)

So instead of SELECT col1, col2, col3 etc etc we can do SELECT *

SQL:

SELECT
    *
FROM
    employees

Output:

Exactly the same results as the previous SQL its worth mentioning that I've taken the alias off the employees for the second example as were only using a single table it doesn't really matter, but as you get to 2, 3, 4 and upward tables it becomes important.

How many records!?

As a little added extra i thought id give an option to the select before we finish. Lets say we want to count how many employee's work for the business... now you could Shift + Ctrl + Alt + A (In DBEaver ayway) to bring back ALL records from that table, select the first column and see how many records there is.... or you can wrap the Asterisk (*) in count brackets.

SELECT
    count(*)
FROM
    employees

And the Result:

Conclusion

In this post we have looked at VERY basic database requests, next post we shall bring in another table and also SELECT aliasing.

If you found this post useful please consider following me on twitter @skino2020 for future updates to this series and if you found it really helpful consider buying me a coffee here.