Chapter 5 ■ MySQL priMerCreating Databases and Tables
The most basic commands you will need to learn and master are the CREATE DATABASE and CREATE TABLE
commands. Recall that database servers such as MySQL allow you to create any number of databases that
you can add tables and store data in a logical manner.
To create a database, use CREATE DATABASE followed by a name for the database. If you are using the
MySQL client, you must use the USE command to switch to a specific database. The client focus is the latest
database specified either at startup (on the command line) or via the USE command. You can override this by
referencing the database name first. For example, SELECT * FROM db1.table1 will execute regardless of the
default database set. However, leaving off the database name will cause the mysql client to use the default
database. The following shows two commands to create and change the focus of the database:
mysql> CREATE DATABASE plant_monitoring;
mysql> USE plant_monitoring;
■Tip recall if you want to see all the databases on the server, use the SHOW DATABASES command.
Creating a table requires the, yes, CREATE TABLE command. This command has many options allowing
you to specify not only the columns and their data types but also additional options such as indexes, foreign
keys, and so on. An index can also be created using the CREATE INDEX command (see the following code).
The following shows how to create a simple table for storing plant sensor data.
CREATE TABLE plant_monitoring.plants (
plant_name char(30) NOT NULL,
sensor_value float DEFAULT NULL,
sensor_event timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
sensor_level char(5) DEFAULT NULL,
PRIMARY KEY plant_name (plant_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Notice here that I specified the table name (plants) and four columns (plant_name, sensor_value,
sensor_event, and sensor_level). I used several data types. For plant_name, I used a character field
with a maximum of 30 characters, a floating-point data type for sensor_value, a timestamp value for
sensor_event, and another character field for sensor_level of five characters.
The TIMESTAMP data type is of particular use in IOT solutions or any time you want to record the date
and time of an event or action. For example, it is often helpful to know when a sensor value is read. By
adding a TIMESTAMP column to the table, you do not need to calculate, read, or otherwise format a date and
time at the sensor or even aggregate node.
Notice also that I specified that the sensor_name column be defined as a key, which creates an index. In
this case, it is also the primary key. The PRIMARY KEY phrase tells the server to ensure there exists one and only
one row in the table that matches the value of the column. You can specify several columns to be used in the
primary key by repeating the keyword. Note that all primary key columns must not permit nulls (NOT NULL).
If you cannot determine a set of columns that uniquely identify a row (and you want such a behavior—
some favor tables without this restriction, but a good DBA would not), you can use an artificial data type
option for integer fields called AUTO INCREMENT. When used on a column (must be the first column), the
server automatically increases this value for each row inserted. In this way, it creates a default primary key.
For more information about auto increment columns, see the online reference manual.