In this section, we consider simple examples of writing data to databases. Multiple users writing data, how to manage locking of databases, and more complex transactions with the MySQL DBMS are discussed in Chapter 6.
To illustrate a write transaction with the winestore database, consider an example of inserting a new wine. This process can be performed with the MySQL command-line interpreter. Only one user is interacting with the DBMS in this example.
Let’s suppose that 24 bottles of a new wine, a Curry Hill Cabernet Merlot 1996 made by De Morton Hill wineries, have arrived, and you wish to add a row to the database for the new wine.
The addition has several steps, the first of which is an
INTO statement to create
the basic row for the wine in the wine table:
INSERT INTO wine SET wine_name='Curry Hill', type='Red', year=1996, description='A beautiful mature wine. Smooth to taste Ideal with red meat.';
This creates a new row and sets the basic attributes. The
wine_id is set to the next available value because
DEFAULT modifiers. The remaining attributes to
insert require further querying and then subsequent updates.
The second step is to set the
winery_id for the
new wine. We need to search for the De Morton Hill winery to identify
SELECT winery_id FROM winery WHERE winery_name='De Morton Hill';
The result returned is:
+-----------+ | winery_id | +-----------+ | 221 | +-----------+ ...