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
INSERT
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
of the auto_increment
and
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
the winery_id
:
SELECT winery_id FROM winery WHERE winery_name='De Morton Hill';
The result returned is:
+-----------+ | winery_id | +-----------+ | 221 | +-----------+ 1 row in set (0.00 sec)
We can now update the new wine row to set the
winery_id=221
. However, which row to update? An
easy way to find the wine_id
of the new
wine row is to use the built-in function
last_insert_id( )
. As discussed in the earlier
section Section 3.5, this function
returns the number created by the most recent
auto_increment
modifier:
SELECT last_insert_id( );
This returns the wine_id
of the inserted row:
+------------------+ | last_insert_id( ) | +------------------+ | 1029 | +------------------+ 1 row in set (0.00 sec)
You can now issue the UPDATE
statement:
UPDATE wine SET winery_id = 221 WHERE wine_id = 1029;
The third step is to set the variety information for the new wine. We
need the variety_id
values for Cabernet and
Merlot. These can be found with a simple query:
SELECT * FROM grape_variety;
In part, the following results are produced:
+------------+------------+ | variety_id | variety | +------------+------------+ | 1 | Riesling | | 2 | Chardonnay | | 3 | Sauvignon | | 4 | Blanc | | 5 | Semillon | | 6 | Pinot | | 7 | Gris | | 8 | Verdelho | | 9 | Grenache | | 10 | Noir | | 11 | Cabernet | | 12 | Shiraz | | 13 | Merlot |
Cabernet has a variety_id=11
and Merlot a
variety_id=13
. We can now insert two rows into the
wine_variety table. Because Cabernet is the
first variety, set its ID=1
, and
ID=2
for Merlot:
INSERT INTO wine_variety SET wine_id=1029, variety_id=11, id=1; INSERT INTO wine_variety SET wine_id=1029, variety_id=13, id=2;
The final step is to insert the first inventory row into the inventory table for this wine. There are 24 bottles, with a per-bottle price of $14.95 and per-case price of $171.99:
INSERT INTO inventory VALUES (1029, 1, 24, 14.95, 171.99);
We’ve now completed the process of inserting rows into other tables in the winestore is similar. Adding data to the winery, region, inventory, and orders tables follows the same approach. Insertion of rows into the customer and grape_variety tables is simpler because there are no attributes that require lookups in other tables.
In this example, we consider the steps required to buy a bottle of wine. Again, assume that there is only one user reading or writing data with the DBMS. The complete process—implemented as part of the winestore web database application—is described in Chapter 12.
To motivate this example, consider a customer, Dimitria Marzalla, who has added two bottles of the new De Morton Wines Curry Hill Cabernet Merlot 1996 to her shopping cart and now wishes to purchase the wines.
Before showing you how the purchase is finalized, let’s examine the information recorded in the user shopping cart and what we know about the user.
First, we know that cust_id=1
is the ID for this
customer and that the wine being purchased has
wine_id=1029
. This associated information has been
previously determined in the process of collecting data for the
purchase in the online winestore.
Second, we need to consider how the shopping cart is managed in the
winestore. We use the orders and
items tables to manage the shopping cart for
each user. When a user adds the first item to her shopping cart, a
new row is created in the orders table with a
dummy cust_id=-1
and the next available
order_id
for this dummy customer. We use a dummy
customer number because customers don’t need to log
in to add wine to their shopping carts, and because finalized orders
are distinguished by having the cust_id
of a
customer who is a member.
For this example, assume that the shopping cart has
order_id=354
, and the dummy customer is
cust_id=-1
. Also assume that the row in the
items table that represents the wine in the
shopping cart has a cust_id=-1
, an
order_id=354
, an item_id=1
, a
wine_id=1029
, a quantity qty=2
,
and the price information for the wine. The price is $14.95 per
bottle.
Before finalizing an order, we need to determine if there are two bottles of the wine available. A wine can be added to the shopping cart if there is any stock available, but this doesn’t necessarily mean that there is more than one bottle left or that another user has not purchased the wine in the meantime. If there is sufficient wine available to finalize an order, we reduce the on-hand stock by two bottles. Checking if there are two bottles available can be done with the following query:
SELECT SUM(on_hand) FROM inventory WHERE wine_id=1029;
A GROUP
BY
wine_id
is unnecessary in this case because only
one wine is selected.
Assuming there are more than two bottles available, we need to reduce the on-hand stock, beginning with the oldest inventory; this was one of the system requirements defined in Chapter 1. There are several ways to find the oldest inventory and the wine per-bottle price. A simple technique is to inspect the inventories:
SELECT inventory_id,cost,on_hand FROM inventory WHERE wine_id=1029 ORDER BY date_added;
The oldest (and only) inventory_id=1
, and there is
an on-hand stock of 24 bottles. We then reduce the on-hand stock by
two:
UPDATE inventory SET on_hand = on_hand - 2 WHERE wine_id=1029 AND inventory_id=1;
If the on-hand stock in an inventory row is reduced to zero—which isn’t so in this case—we then remove that row:
DELETE FROM inventory WHERE wine_id = 1029 AND inventory_id=1;
Other possibilities may also occur, such as having to manipulate two inventories because the oldest inventory has only one bottle left. These possibilities are discussed in further detail in Chapter 12.
Having reserved two bottles of the wine for shipping, we can finalize
the order for the customer. To do so, we need to store the details of
the shopping cart entries in the orders and
items tables. As discussed previously, by
tracking the shopping cart of this user we know it has the
order_id=354
for the dummy
cust_id=-1
. We also need to know how many previous
orders this customer has made:
SELECT max(order_id) FROM orders WHERE customer_id=1;
If you find the customer previously made two orders, you update the shopping cart order row so that it is now the third order for this customer. Use this statement:
UPDATE orders SET cust_id = 1, order_id = 3, date = NULL, delivery = 7.95, discount = 0 WHERE cust_id = -1 AND order_id = 354;
The shopping cart entry is now a customer order.
date=NULL
sets the date
attribute to be the current system time and date. The delivery cost
is $7.95, and there is no discount on the order.
To complete the order, we also update the related
items row in the shopping cart, which contains
the two bottles of wine. Use the following UPDATE
statement:
UPDATE items SET cust_id = 1, order_id = 3, date = NULL WHERE cust_id = -1 AND order_id = 354 AND item_id = 1;
There is no need to update the wine_id
,
price
, or qty
(quantity).
We can now confirm to the customer the purchase of two bottles of Curry Hill and ship the order.
This isn’t quite the whole picture of purchasing wines or updating the database. In Chapter 6, we return to similar examples and discuss the implications and problems of many users interacting with the database at the same time.
We’ll now show how insertion and querying can be
closely tied together with an INSERT INTO ... SELECT
statement. This is
useful for copying data and, if needed, modifying the data as it is
copied.
Consider an example to create a permanent record of the total sales to each customer up to this month, let’s say it’s April. First, create a simple table to store the customer and sales details:
CREATE TABLE salesuntilapril ( cust_id int(5) NOT NULL, surname varchar(50), firstname varchar(50), totalsales float(5,2), PRIMARY KEY (cust_id) );
Now issue a combined INSERT INTO ... SELECT
statement to populate the new table with the customer details and the
total sales:
INSERT INTO salesuntilapril (cust_id, surname, firstname, totalsales) SELECT customer.cust_id, surname, firstname, SUM(price) FROM customer, items WHERE customer.cust_id = items.cust_id GROUP BY items.cust_id;
The four attributes listed in the SELECT
statement
are mapped to the four attributes listed in the INSERT INTO
statement. For example, the
customer.cust_id
in the SELECT
line is mapped into cust_id
in the
salesuntilapril table.
A query on the new table shows part of the results:
SELECT * from salesuntilapril; +---------+-------------+-----------+------------+ | cust_id | surname | firstname | totalsales | +---------+-------------+-----------+------------+ | 2 | LaTrobe | Anthony | 566.42 | | 3 | Fong | Nicholas | 821.78 | | 4 | Stribling | James | 181.69 | | 5 | Choo | Richard | 534.99 | | 6 | Eggelston | Perry | 657.37 | | 7 | Mellaseca | Kym | 1216.88 |
There are two sensible limitations of the INSERT INTO ... SELECT
statement: first, the query can’t
contain an ORDER BY
, and second, the
FROM
clause can’t contain the
target table of the INSERT
INTO
.
18.189.186.167