Howto – A short MySQL tutorial with examples – 02
In our previous MySQL tutorial, we created a small database with four tables. In this article, we will learn to manipulate the data we entered using JOIN to derive data from a table using data from another table. We will also modify the output using the MySQL CONCAT command.
Our MySQL Data
Our data consists of four tables. The first of these is a client list, the second contains personal details, the third contains service details about their vehicles. The last table is a lookup table where four income brackets are defined. The data looks like this in spreadsheet format:
Our table names are found by first moving to our database with the command “USE testdb” (we called our database testdb). Now we can ask the database to show us the tables with the “SHOW TABLES;” command. Please note that while commands (which we do in CAPITALS) are not case sensitive, the database and table names are. Avoid using hyphens or underscores in a database or table name as this makes it harder to use later. This is because any database or table name containing special characters outside the set [a-zA-Z0-09] needs to be quoted when queried. Enough said, let us get the table names.
SHOW TABLES; +------------------+ | Tables_in_testdb | +------------------+ | catdef | | clientdetails | | clients | | clientvehicles | +------------------+ 4 rows in set (0.00 sec)
The SELECT query
Our list of clients can be recalled with the SELECT statement from the clients table. First, we will make a mistake by typing the table name in CAPITALS.
SELECT * FROM CLIENTS; ERROR 1146 (42S02): Table 'testdb.CLIENTS' doesn't exist
Now we will do the query correctly.
SELECT * FROM clients; +-----+-------+--------+---------+ | idx | fname | lname | phone | +-----+-------+--------+---------+ | 1 | Joe | Smith | 5552846 | | 2 | Susan | Harris | 6549920 | | 3 | Peter | Jones | 7920122 | +-----+-------+--------+---------+ 3 rows in set (0.00 sec)
Output modifier CONCAT
We can manipulate the output slightly. Let us first join the first and last names with CONCAT and call the output “fullname”. If we do not rename or alias the output, we get our CONCAT statement as a column header like this:
SELECT idx,CONCAT(fname," ",lname),phone FROM clients; +-----+-------------------------+---------+ | idx | CONCAT(fname," ",lname) | phone | +-----+-------------------------+---------+ | 1 | Joe Smith | 5552846 | | 2 | Susan Harris | 6549920 | | 3 | Peter Jones | 7920122 | +-----+-------------------------+---------+ 3 rows in set (0.05 sec)
Now the aliased output. Note that we have to specify a space (” “) between the first and last name.
SELECT idx,CONCAT(fname," ",lname) AS fullname,phone FROM clients; +-----+--------------+---------+ | idx | fullname | phone | +-----+--------------+---------+ | 1 | Joe Smith | 5552846 | | 2 | Susan Harris | 6549920 | | 3 | Peter Jones | 7920122 | +-----+--------------+---------+ 3 rows in set (0.00 sec)
Table 2 is called “clientdetails” and contains the following.
SELECT * FROM clientdetails; +------+---------------------------------+------------+------------+---------------+--------------+---------------+-----------+ | idxp | home | work | occup | id | bank | bankinfo | incomecat | +------+---------------------------------+------------+------------+---------------+--------------+---------------+-----------+ | 1 | 3 Halfmoon cresc, Mountainview | Woolworths | Manager | 5801021234086 | NationalBank | acc 321543123 | 3 | | 2 | 241 Honey Ave, Hibiscus Heights | Masons | Accountant | 7510214321082 | SecurityHold | acc 876576545 | 2 | | 3 | 84 Hamilton Cl, Cyprus Canyon | Shell | Salesman | 8011229876087 | MoneSafe | acc 102210123 | 2 | +------+---------------------------------+------------+------------+---------------+--------------+---------------+-----------+ 3 rows in set (0.01 sec)
LIMIT output rows returned
We can also specify a LIMIT on data returned which is useful when you have a few thousand rows of data.
SELECT * FROM clientdetails LIMIT 1; +------+--------------------------------+------------+---------+---------------+--------------+---------------+-----------+ | idxp | home | work | occup | id | bank | bankinfo | incomecat | +------+--------------------------------+------------+---------+---------------+--------------+---------------+-----------+ | 1 | 3 Halfmoon cresc, Mountainview | Woolworths | Manager | 5801021234086 | NationalBank | acc 321543123 | 3 | +------+--------------------------------+------------+---------+---------------+--------------+---------------+-----------+ 1 row in set (0.00 sec)
Using LIMIT we can also ask for only the 3rd row of data. Remember that the first row is in position 0, thus the 3rd row will be position 2 and we only want 1 row returned. The LIMIT will thus be specified as LIMIT 2,1 (position 2, 1 row only).
SELECT * FROM clientdetails LIMIT 2,1; +------+-------------------------------+-------+----------+---------------+----------+---------------+-----------+ | idxp | home | work | occup | id | bank | bankinfo | incomecat | +------+-------------------------------+-------+----------+---------------+----------+---------------+-----------+ | 3 | 84 Hamilton Cl, Cyprus Canyon | Shell | Salesman | 8011229876087 | MoneSafe | acc 102210123 | 2 | +------+-------------------------------+-------+----------+---------------+----------+---------------+-----------+ 1 row in set (0.00 sec)
Let us have a look at our “clientvehicles” table.
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 | +------+--------+----------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ 3 rows in set (0.00 sec)
Now lastly, our category definitions in the “catdef” tables.
SELECT * FROM catdef; +-----------+---------+ | incomecat | bracket | +-----------+---------+ | 1 | 10000 | | 2 | 20000 | | 3 | 30000 | | 4 | 40000 | +-----------+---------+ 4 rows in set (0.00 sec)
Using JOIN to look up RELATED data
We have now had a look at all the data from our previous MySQL tutorial. Now let us try and combine the output in a useful way. We will do this by using the JOIN command to overlay data from one table on the relevant data from another. Let us start by asking “Who owns the Ford Mondeo?”. In the SQL query, this will first involve choosing the record containing make = Ford and model = Mondeo.
SELECT * FROM clientvehicles WHERE make LIKE 'ford' AND model LIKE 'mondeo'; +------+------+--------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ | idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes | +------+------+--------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ | 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch | +------+------+--------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ 1 row in set (0.00 sec)
Note that by using LIKE instead of “=”, we can make the search case-insensitive. We can also search using a wildcard using “%”. If the model was “Mondeo LX”, we could have searched for “LIKE ‘mondeo %’ “.
As we are not interested in the actual data in this table, but actually need the owner, we now JOIN and SELECT the clients table. Note that we will be ALIASING each table to shorten the SQL statement. The vehicles table will become V and the clients table will become C.
SELECT CONCAT(C.fname," ",C.lname) AS fullname FROM clients C JOIN clientvehicles V ON V.idxv=C.idx WHERE V.make LIKE 'ford' AND V.model LIKE 'mondeo'; +--------------+ | fullname | +--------------+ | Susan Harris | +--------------+ 1 row in set (0.00 sec)
In our next article in this mini MySQL Tutorial, we will look at more complex JOINS and data UPDATES and manipulation.
Happy Hosting!
