Chapter 5 ■ MySQL priMerNotice I used NULL for the last two columns. This is habit of mine from my DBA days where I always
supply a value for every field in the table. You do not have to do it that way. Indeed, it is also correct to specify
the SQL commands with just the values you know, but remember to use the column list, as shown here:
INSERT INTO plant_monitoring.readings (id, moisture, temperature) VALUES (7,418,32.5);
As a simple test, let’s do a simple join to find all the readings for a particular plant. For example, we ask
the question, “What are the moisture readings for the plants on the deck?” The following query returns the
results:
mysql> SELECT name, location, moisture FROM plants JOIN readings ON plants.id = readings.id
WHERE location LIKE '%deck%';
+------+----------+----------+
| name | location | moisture |
+------+----------+----------+
| fern | deck | 235 |
| fern | deck | 235 |
| fern | deck | 230 |
| fern | deck | 230 |
| fern | deck | 215 |
| fern | deck | 215 |
| fern | deck | 225 |
| fern | deck | 220 |
| fern | deck | 222 |
| fern | deck | 218 |
+------+----------+----------+
10 rows in set (0.00 sec)
Notice I used a LIKE function in the WHERE clause. I used it because I wasn’t sure if there were more
than one plant on the deck. For example, there could have been a plant whose location was, “deck by table”
or “on deck under tree.” Using the LIKE with a wildcard, %, on each side returns all rows that have “deck” in
the location column value. Cool! Take some time to look at the sample data in Listing 5-10 to ensure you are
seeing the correct data.
Now that we have some sample data, let’s see the results of the example queries. I walk through the
results in the same order that I explained the previous query.
Testing Example 1
Recall for this query we simply want the average temperature for the plants that are outside. When I test a
query, I like to break it down into its most simplistic parts—the same way I did to develop it. This way, I can
verify I am getting the correct results for each part. Let’s see that query again.
SELECT name, location, AVG(temperature) as avg_temp
FROM plants JOIN readings ON plants.id = readings.id
WHERE DATE(event_time) = CURRENT_DATE() AND plants.climate = 2
GROUP BY plants.id;
Let’s begin with the most basic data—the name and location of the plants that live outside.