Chapter 5 ■ MySQL priMer
■Tip Best practices suggest using a primary key on a character field to be suboptimal in some situations
such as tables with large values for each column or many unique values. this can make searching and indexing
slower. in this case, you could use an auto increment field to artificially add a primary key that is smaller in size
(but somewhat more cryptic).
There are far more data types available than those shown in the previous example. You should review
the online reference manual for a complete list of data types. See the section “Data Types.” If you want to
know the layout or “schema” of a table, use the SHOW CREATE TABLE command.
Like databases, you can also get a list of all the tables in the database with the SHOW TABLES command.
Getting Results
The most used basic command you need to know is the command to return the data from the table
(also called a result set or rows). To do this, you use the SELECT statement. This SQL statement is the
workhorse for a database system. All queries for data will be executed with this command.^10 As such, we will
spend a bit more time looking at the various clauses (parts) that can be used starting with the column list.
The SELECT statement allows you to specify which columns you want to choose from the data. The list
appears as the first part of the statement. The second part is the FROM clause, which specifies the table(s) you
want to retrieve rows from.
■Note the FROM clause can be used to join tables with the JOIN operator. you will see a simple example of a
join in a later section.
The order that you specify the columns determines the order shown in the result set. If you want all of
the columns, use an asterisks (*) instead. Listing 5-4 demonstrates three statements that generate the same
result sets. That is, the same rows will be displayed in the output of each. In fact, I am using a table with only
four rows for simplicity.
Listing 5-4. Example SELECT Statements
mysql> SELECT plant_name, sensor_value, sensor_event, sensor_level 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)
(^10) Not including direct, engine-level queries like NoSQL using NDB.