mysql tutorial

In our previous MySQL tutorial, we did some basic SELECT queries, a CONCAT for output and a JOIN to lookup data on a different table that was linked or RELATED to our client database. In this MySQL tutorial, we will continue with a DOUBLE JOIN and discuss UPDATES and DATA MANIPULATION. We will also have a look at some different JOIN types.

Our data from our previous MySQL tutorials

MySQL tutorial

The DOUBLE JOIN

Let us now construct a DOUBLE JOIN to find the income bracket of a client. We need to use a DOUBLE JOIN as there is no DIRECT RELATION between income bracket and client. Thus we have to SELECT the clients, find their income category from the clientdetails table. We then use this to find the income bracket from the catdef table. Visually, it can be represented as follows:

MySQL tutorial

We will alias each table to enable a shorter, more readable query. The clients table will be C, the clientdetails will be and the catdef table will be CD. We thus SELECT the data to be shown and do a DOUBLE JOIN to match the correct data. Here is the query and results:

SELECT C.fname,C.lname,CD.bracket FROM clients C JOIN clientdetails D ON C.idx=D.idxp JOIN catdef CD ON D.incomecat=CD.incomecat ;
+-------+--------+---------+
| fname | lname | bracket |
+-------+--------+---------+
| Susan | Harris | 20000 |
| Peter | Jones | 20000 |
| Joe | Smith | 30000 |
+-------+--------+---------+
3 rows in set (0.01 sec)

Updating a table and inserting new data

We are actually going to be answering three question by explaining two concepts. How would we add a SECOND vehicle to an existing client and how do we UPDATE existing data. Let us first ADD a new vehicle entry for our client, Joe Smith (client #1).

INSERT INTO clientvehicles VALUES ('1','Porsche','Boxster S','2017','50','JOESMITH-CA','2018-04-26','','0','Hanbrake Adjusted','New Vehicle');
Query OK, 1 row affected, 1 warning (0.02 sec)
SELECT * FROM clientvehicles;
+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes |
+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| 1 | Jaguar | XF | 2015 | 12598 | CA 223344 | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None |
| 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch |
| 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn |
| 1 | Porsche | Boxster S | 2017 | 50 | JOESMITH-CA | 2018-04-26 | 0000-00-00 | 0 | Hanbrake Adjusted | New Vehicle |
+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
4 rows in set (0.00 sec)

Now we have two vehicles with the SAME client id (idxv). Let us SELECT all vehicles for Joe Smith. At the same time, we use CONCAT to clean up the output.

 SELECT CONCAT(V.make," ",V.model) AS car, CONCAT(C.fname," ",C.lname) AS owner FROM clientvehicles V JOIN clients C ON V.idxv=C.idx WHERE C.fname LIKE 'joe' AND C.lname LIKE 'smith'; 
+-------------------+-----------+
| car | owner |
+-------------------+-----------+
| Jaguar XF | Joe Smith |
| Porsche Boxster S | Joe Smith |
+-------------------+-----------+
2 rows in set (0.00 sec)

Next, we will let MySQL do some calculations for us. How many cars does Joe Smith have in our system? We use the COUNT() command to count item occurrences.

SELECT COUNT(V.idxv) AS number, CONCAT(C.fname," ",C.lname) AS owner FROM clientvehicles V JOIN clients C ON V.idxv=C.idx WHERE C.fname LIKE 'joe' AND C.lname LIKE 'smith'; 
+--------+-----------+
| number | owner |
+--------+-----------+
| 2 | Joe Smith |
+--------+-----------+
1 row in set (0.00 sec)

Let us update the information on Joe Smith’s Jaguar – he has a new registration which is now “JOESJAG’. We are going to add a UNIQUE id to the start of the table as we cannot use the idxv field to uniquely id the row anymore.

ALTER TABLE clientvehicles ADD COLUMN idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
SELECT * FROM clientvehicles;
+-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| idx | idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes |
+-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| 1 | 1 | Jaguar | XF | 2015 | 12598 | CA 223344 | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None |
| 2 | 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch |
| 3 | 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn |
| 4 | 1 | Porsche | Boxster S | 2017 | 50 | JOESMITH-CA | 2018-04-26 | 0000-00-00 | 0 | Hanbrake Adjusted | New Vehicle |
+-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
4 rows in set (0.00 sec)

Now that we have a UNIQUE reference, we can do the UPDATE. Here we SET the field reg to ‘JOESJAG’ WHERE the idx field equals 1.

UPDATE clientvehicles SET reg='JOESJAG' WHERE idx=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM clientvehicles;
+-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| idx | idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes |
+-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| 1 | 1 | Jaguar | XF | 2015 | 12598 | JOESJAG | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None |
| 2 | 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch |
| 3 | 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn |
| 4 | 1 | Porsche | Boxster S | 2017 | 50 | JOESMITH-CA | 2018-04-26 | 0000-00-00 | 0 | Hanbrake Adjusted | New Vehicle |
+-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
4 rows in set (0.00 sec)

JOIN TYPES explained and demonstrated

This is best shown with a diagram from WikiMedia

MySQL tutorial

Host AfricaHappy Hosting!

 

we're happy to help!

Talk to a hosting specialist today and discover which options will work best for you.


Call us on +27 21 554 3096
Copyright © 2020 HOSTAFRICA - All rights reserved.

By visiting this website, you agree to its terms of use, which can be accessed by clicking on the following link: Website Terms of use
We Accept: EFT, Debit Cards, Credit Cards and Mobile Payments
Accepted payment methods