Chapter 5 ■ MySQL priMermysql> SELECT * FROM plant_monitoring.plants;
+------------------------+--------------+---------------------+--------------+
| plant_name | sensor_value | sensor_event | sensor_level |
+------------------------+--------------+---------------------+--------------+
| fern in den | 0.2319 | 2015-09-23 21:04:35 | NULL |
| fern on deck | 0.43 | 2015-09-23 21:11:45 | NULL |
| flowers in bedroom1 | 0.301 | 2015-09-23 21:11:45 | NULL |
| weird plant in kitchen | 0.677 | 2015-09-23 21:11:45 | NULL |
+------------------------+--------------+---------------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT sensor_value, plant_name, sensor_level, sensor_event FROM
plant_monitoring.plants;
+--------------+------------------------+--------------+---------------------+
| sensor_value | plant_name | sensor_level | sensor_event |
+--------------+------------------------+--------------+---------------------+
| 0.2319 | fern in den | NULL | 2015-09-23 21:04:35 |
| 0.43 | fern on deck | NULL | 2015-09-23 21:11:45 |
| 0.301 | flowers in bedroom1 | NULL | 2015-09-23 21:11:45 |
| 0.677 | weird plant in kitchen | NULL | 2015-09-23 21:11:45 |
+--------------+------------------------+--------------+---------------------+
4 rows in set (0.00 sec)
Notice that the first two statements result in the same rows as well as the same columns in the same
order, but the third statement, while it generates the same rows, displays the columns in a different order.
You can also use functions in the column list to perform calculations and similar operations. One
special example is using the COUNT() function to determine the number of rows in the result set, as shown
here. See the online reference manual for more examples of functions supplied by MySQL.
SELECT COUNT(*) FROM plant_monitoring.plants;
The next clause in the SELECT statement is the WHERE clause. This is where you specify the conditions
you want to use to restrict the number of rows in the result set. That is, only those rows that match the
conditions. The conditions are based on the columns and can be quite complex. That is, you can specify
conditions based on calculations, results from a join, and more. But most conditions will be simple
equalities or inequalities on one or more columns in order to answer a question. For example, suppose you
wanted to see the plants where the sensor value read is less than 0.40. In this case, we issue the following
query and receive the results. Notice I specified only two columns: the plant name and the value read from
sensor.
mysql> SELECT plant_name, sensor_value FROM plant_monitoring.plants WHERE
sensor_value < 0.40;
+---------------------+--------------+
| plant_name | sensor_value |
+---------------------+--------------+
| fern in den | 0.2319 |
| flowers in bedroom1 | 0.301 |
+---------------------+--------------+
2 rows in set (0.01 sec)