MySQL in a Nutshell [20]
Now we’re ready to insert data for more books without worrying about duplicate rows for books with the same ISBN number. Here is an example in which we attempt to add two more books by Graham Greene, one of which is already in the table:
REPLACE INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('Brighton Rock',1,'0099478471','novel','1938'),
('The Quiet American',1,'0099478393','novel','1955');
The syntax for the REPLACE statement is the same as the INSERT statement. Notice that we’ve added two rows here in one statement. This is the same syntax that you would use if you want to add more than one row using INSERT. Just list each row’s data within parentheses and separate them by commas, as shown. In this example, there is already a row for the book containing the ISBN number 0099478471 (i.e., Brighton Rock), so its data will be replaced and a new row will not be added. There is currently no row for Greene’s book The Quiet American, though, so it will be added.
Deleting Data
To delete specific rows of data, you can use the DELETE statement. For example, if we want to delete all rows of data from our books table for the author J. K. Rowling, because we’ve decided not to carry Harry Potter books, we could issue the following statement:
DELETE FROM books
WHERE author_id =
(SELECT authors.author_id FROM authors
WHERE author_last = 'Rowling'
AND author_first = 'J. K.');
DELETE FROM authors
WHERE author_last = 'Rowling'
AND author_first = 'J. K.';
Here, we’re deleting only rows from the books table where the author identification number is whatever is selected from the authors table based on the specified author’s last name and first name. That is to say, the author_id must be whatever value is returned by the SELECT statement, the subquery contained in the parentheses. This statement involves a subquery, so it requires version 4.1 or later of MySQL. To delete these same rows with an earlier version of MySQL, you would need to run the SELECT statement shown in parentheses here separately (not as a subquery), make note of the author’s identification number, and then run the first DELETE statement, manually entering the identification number at the end instead of the parenthetical SELECT statement shown.
An alternative to the previous SQL statements would be to utilize user-defined variables. Here is the same example using variables:
SET @potter =
(SELECT author_id FROM authors
WHERE author_last = 'Rowling'
AND author_first = 'J. K.');
DELETE FROM books
WHERE author_id = @potter;
DELETE FROM authors
WHERE author_id = @potter;
In the first part, we use the SET statement to establish a variable called @potter that will contain the results of the SELECT statement that follows in parentheses, another subquery. Incidentally, although this subquery is not available before version 4.1, user-defined variables are. The second SQL statement deletes the rows from books where the author identification number matches the value of the temporary variable. Next, we delete the data from the authors table, still making use of the variable. A user-defined variable will last until it’s reset or until the MySQL session is closed.
Searching Data
Once our database is loaded with large amounts of data, it can be cumbersome to locate data simply by scrolling through the results of SELECT statements. Also, sometimes we don’t have the exact or complete text for a column we’re examining. For these situations, we can use the LIKE operator. Suppose that our books table now has thousands of entries. Suppose further that a customer says he’s looking for a specific book. He can’t remember the name of the author or the title of the book, but he does remember that the words traveler and winter are in the title. We could enter this SQL statement to search the database based on this minimal information:
SELECT book_id, title,
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE title LIKE '%traveler%'
AND title LIKE '%winter%';