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.
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 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'.
SELECT e.emp_no, e.birth_date, e.first_name, e.last_name, e.gender, e.hire_date FROM employees e
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 *
SELECT * FROM employees
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.
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:
Imagine scaling that up to a DB with millions of records in it? my start getting messy..
In this post we have looked at VERY basic database requests, next post we shall bring in another table and also SELECT aliasing.