Updating Columns with Formulas


UPDATE loan


SET date_lent = DATE_ADD(date_lent, INTERVAL 1 YEAR)


WHERE date_lent = '2005-01-01';

Assume that in the wee hours of the New Year, you loaned visiting friends five books, recorded the loans in a table called loan , and accidentally entered the wrong year in the date stored in date_lent . The simple UPDATE statement in the phrasebox allows you to correct the issue for all five rows in one query.

The formula used in the previous sample query is simple enougha new date is calculated by using MySQL's DATE_ADD function to add a week to an existing date. The interesting thing is that the new value assigned to date_lent is based on the existing value of date_lent . The feature of UPDATE that allows the query to reference existing values in the table is part of what makes this type of query so useful.

Deleting Rows


DELETE FROM book WHERE cond = 'poor';

Assume that you have decided that it is time to permanently retire any books in your library that are in poor condition. The simple DELETE query in the phrasebox accomplishes exactly that.

The basic syntax for DELETE is

DELETE FROM

table_name

WHERE

some_conditions

;

The WHERE clause used in the DELETE command is the same familiar WHERE clause used in SELECT and UPDATE commands. For more information, see Chapter 4.

Deleting Rows from Multiple Tables


DELETE book, loan    # tables to delete from


FROM book, loan    # tables to use in WHERE clause


WHERE book.book_id = loan.book_id


AND book.cond = 'poor';

A more complex form of the DELETE query allows you to delete rows from multiple tables in one operation. In the phrasebox, you are deleting any books that are in poor condition, along with any loans of those books.

The syntax for this kind of DELETE is as follows :

DELETE Indicates the start of a DELETE query and should be immediately followed by a list of tables.
list, of, tables , ... Immediately following the DELETE keyword is a list of one or more tables. Only tables listed here will have rows deleted from them.
FROM Used to mark which tables should be a part of the WHERE clause of the query.
list, of, tables , ... Only tables contained in this list may be used in the WHERE clause of the query.
WHERE A standard WHERE clause. Rows that match conditions in the WHERE clause and that are in tables listed immediately after the DELETE keyword will be deleted. For more information on WHERE clauses, see Chapter 4.

Chapter 6. User Management and Security

MySQL is a multi-user database with a sophisticated access control system. The phrases in this chapter will show you how to maintain the users on your database and to give each user only the permissions he requires.

MySQL's privilege system ensures that each user may only perform the operations that he is allowed. A user's identity is determined by a given username and the remote address from which the connection is made. A user is also usually required to authenticate by providing a password when connecting to the database.

Tip

You need specific administrator privileges to perform each of the commands in this chapter. If you connect to MySQL as the root usertypical for performing user maintenanceyou have the appropriate permissions to execute every example phrase.