MySQL in a Nutshell [19]
DAYOFMONTH(purchase_date), ', ',
YEAR(purchase_date)) AS 'Purchase Date'
FROM orders
WHERE sale_id = '1250';
+---------------+
| Purchase Date |
+---------------+
| March 1, 2004 |
+---------------+
To put the date together in the typical human-readable format used in the United States, we’re using the CONCAT( ) function in conjunction with a few date functions. It may be a little confusing at first glance, because we’re inserting a space between the month and the day at the end of the first line and a comma and a space after the day at the end of the second line. As for the date functions, the first one extracts the month from the purchase_date column and formats it so its full name is displayed. The second date function on the second line extracts just the day, after which we explicitly specify a comma. The third date function on the third line extracts just the year.
As you can see in the results, our combination of functions works. However, it’s not the cleanest method by which the date can be assembled. We could use the DATE_FORMAT( ) function instead:
SELECT DATE_FORMAT(purchase_date, "%M %d, %Y")
AS 'Purchase Date'
FROM orders
WHERE sale_id = '1250';
This is a much more efficient method, and it provides the same output as the previous statement. You just have to know the formatting codes to be able to use this function properly. They’re listed in Chapter 12, along with several more formatting codes and many more date and time functions.
Changing Data
You can change data in a table using a few different methods. The most basic and perhaps the most common method is to use the UPDATE statement. With this statement, you can change data for all rows or for specific records based on a WHERE clause.
Looking back on the results displayed from an earlier query, we can see that Graham Greene’s book Brighton Rock has a copyright year of 1937. That’s not correct; it should be 1938. To change or update that bit of information, we would enter the following SQL statement:
UPDATE books
SET pub_year = '1938'
WHERE book_id = '2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
First, we state the name of the table that’s being updated. Next, we include the SET keyword with the column to change and its corresponding new value. If we wanted to change the values of more than one column, we would provide a comma-separated list of each column along with the equals sign operator and the new respective values. SET is given only once.
The preceding SQL statement has a WHERE clause limiting the rows that will be updated by specifying a condition the row must meet. In this case, our condition is for a specific value of a unique column, so only one row will be changed. The results of the query show that one row was affected, one row was matched, one row was changed, and there were no problems to generate warnings.
Sometimes inserting data into a table will cause a duplicate row to be created because a row for the data already exists. For instance, suppose that we want to run an SQL statement that inserts data on a few books into the books table and one of the books is already in the table. If we use INSERT, a duplicate row will generally be rejected. To prevent this, we can use the REPLACE statement, which inserts rows that are new and replaces existing rows with new data.
From MySQL’s perspective, duplicates occur only when columns defined as unique contain the same value. Because the book_id column is assigned automatically, it’s unlikely that we would duplicate it, because we wouldn’t tend to assign its value when adding records. What’s unique about each book in the book business is its ISBN number, which is the bar code number on the back of the book. To ensure that we do not have rows with the same ISBN number, we’ll alter our books table again and change the isbn column to a UNIQUE column, a column that requires a unique value. This way we won’t be able to enter data inadvertently on a book more than once:
ALTER TABLE books
CHANGE