Chapter 5 ■ MySQL priMer
This command adds an index on the plant_name column. Observe how this affects the table.CREATE TABLE plants (
plant_name char(30) DEFAULT NULL,
sensor_value float DEFAULT NULL,
sensor_event timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
sensor_level char(5) DEFAULT NULL,
KEY plant_name (plant_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Indexes created like this do not affect the uniqueness of the rows in the table, in other words, making
sure there exists one and only one row that can be accessed by a specific value of a specific column
(or columns). What I am referring to is the concept of a primary key (or primary index), which is a special
option used in the creation of the table as described earlier.
Views
Views are logical mappings of results of one or more tables. They can be referenced as if they were tables in
queries, making them a powerful tool for creating subsets of data to work with. You create a view with CREATE
VIEW and give it a name similar to a table. The following shows a simple example where we create a test view
to read values from a table. In this case, we limit the size of the view (number of rows), but you could use a
wide variety of conditions for your views, including combining data from different tables.
CREATE VIEW test_plants AS SELECT * FROM plants LIMIT 5;
Views are not normally encountered in small or medium-sized database solutions, but I include them
to make you aware of them in case you decide to do additional analysis and want to organize the data into
smaller groups for easier reading.
Triggers
Another advanced concept (and associated SQL command) is the use of an event-driven mechanism that
is “triggered” when data is changed. That is, you can create a short set of SQL commands (a procedure) that
will execute when data is inserted or changed.
There are several events or conditions under which the trigger will execute. You can set up a trigger
either before or after an update, insert, or delete action. A trigger is associated with a single table and has
as its body a special construct that allows you to act on the rows affected. The following shows a simple
example:
DELIMITER //
CREATE TRIGGER set_level BEFORE INSERT ON plants FOR EACH ROW
BEGIN
IF NEW.sensor_value < 0.40 THEN
SET NEW.sensor_level = 'LOW';
ELSEIF NEW.sensor_value < 0.70 THEN
SET NEW.sensor_level = 'OK';
ELSE
SET NEW.sensor_level = 'HIGH';
END IF;
END //
DELIMITER ;