Introduction to MySQL queries

#sql #database #codenewbie

So you wanna learn about MySQL? If you really like working with backend and databases then you have come to the right place. Today I am here to teach you a thing two about MySQL queries. In my honest opinion, MySQL looked very confusing when I first started using it, but it's not so bad once you have made a few queries here and there! If you want to go really in-depth with MySQL queries, then you can go to straight to the docs here. Their docs are surprisingly okay. Personally, for most tutorials, I like to use mysqltutorial. Anyway, let's get right into the meat of it.

Assuming you already know about schemas, let's get into how to make queries onto those tables.

+-------------+---------------+-------------+--------+
| employee_id | employee_name | managner_id | salary |
+-------------+---------------+-------------+--------+
| 1           | Tommy         | 6           | 50000  |
+-------------+---------------+-------------+--------+
| 2           | Will          | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 3           | Naseer        | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 4           | Blake         | 6           | 40000  |
+-------------+---------------+-------------+--------+
| 5           | Gordon        | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 6           | Chris         |             | 80000  |
+-------------+---------------+-------------+--------+

And let's assume we have a table named employee that looks a little something like this. With basic queries, we can select specific details, insert and update rows and even delete any data we no longer need.

For example, if we simply wanted to get Tommy's salary, given we know his employee ID.

SELECT * FROM employee where employee_id = 1;

Now we will get something like

+-------------+---------------+------------+--------+
| employee_id | employee_name | manager_id | salary |
+-------------+---------------+------------+--------+
| 1           | Tommy         | 6          | 50000  |
+-------------+---------------+------------+--------+

And an insert can be done in a few different ways. I personally like to do it this way.

INSERT INTO employee VALUES(7, 'JD', 6, 40000);

Once we do that our table will look something like this.

+-------------+---------------+-------------+--------+
| employee_id | employee_name | managner_id | salary |
+-------------+---------------+-------------+--------+
| 1           | Tommy         | 6           | 50000  |
+-------------+---------------+-------------+--------+
| 2           | Will          | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 3           | Naseer        | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 4           | Blake         | 6           | 40000  |
+-------------+---------------+-------------+--------+
| 5           | Gordon        | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 6           | Chris         |             | 80000  |
+-------------+---------------+-------------+--------+
| 7           | JD            | 6           | 40000  |
+-------------+---------------+-------------+--------+

But oh no Gordon just did something he really wasn't supposed to, and he got fired. Now we have to delete him from our database. We just have to make a simple delete query.

DELETE FROM employee WHERE name = 'Gordon';

Now our table no longer has any trace of Gordon existing.

+-------------+---------------+-------------+--------+
| employee_id | employee_name | managner_id | salary |
+-------------+---------------+-------------+--------+
| 1           | Tommy         | 6           | 50000  |
+-------------+---------------+-------------+--------+
| 2           | Will          | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 3           | Naseer        | 1           | 20000  |
+-------------+---------------+-------------+--------+
| 4           | Blake         | 6           | 40000  |
+-------------+---------------+-------------+--------+
| 6           | Chris         |             | 80000  |
+-------------+---------------+-------------+--------+
| 7           | JD            | 6           | 40000  |
+-------------+---------------+-------------+--------+

So this is what some of the more basic queries are gonna look like. They can pages long in terms of how complicated they can get. Let's provide a quick example. We are going to something called a self join.

Say we wanted to get the names of every employee's manager and every employee all in one table.

SELECT a.employee_name, 
CASE WHEN b.manager_id 
IS NOT null THEN b.employee_name
ELSE 'No Manager' AS 'manager_name'
FROM employee a, employee b
WHERE a.employee_id = b.manager_id(+);

Now that went from 0 to 100 real quick. These queries can get crazy difficult. And this one isn't even that bad compared to how bad they can get! Now once you have all that, you will end up with a table looking like this.

+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| Tommy         | Chris        |
+---------------+--------------+
| Will          | Tommy        |
+---------------+--------------+
| Naseer        | Tommy        |
+---------------+--------------+
| Blake         | Chris        |
+---------------+--------------+
| Chris         | No Manager   | 
+---------------+--------------+
| JD            | Chris        |
+---------------+--------------+

Alright! Now I hope you learned even just a smidge more than you did before touching this post. Hope you all have a wonderful time making your new databases. Happy coding hackers!