7.4 Exercises
| Question 1: | MySQL provides three ways to handle new rows that would cause duplicate-key errors for unique index values in a table. Specifically, (a) with the standard form of INSERT, the new rows can be rejected; (b) with INSERT IGNORE, you can force MySQL to discard the new rows that duplicate existing unique-key values; and (c) with the REPLACE statement, you can force MySQL to delete the existing rows before inserting the new rows. For each of these three options, give an example of the effect of its use on the following table:
mysql> DESCRIBE twounique; SELECT * FROM twounique;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id1 | tinyint(3) unsigned | | PRI | 0 | |
| id2 | tinyint(3) unsigned | | UNI | 0 | |
+-------+---------------------+------+-----+---------+-------+
+-----+-----+
| id1 | id2 |
+-----+-----+
| 1 | 2 |
+-----+-----+
| | Question 2: | Consider this table:
mysql> DESCRIBE twounique; SELECT * FROM twounique;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id1 | tinyint(3) unsigned | | PRI | 0 | |
| id2 | tinyint(3) unsigned | | UNI | 0 | |
+-------+---------------------+------+-----+---------+-------+
+-----+-----+
| id1 | id2 |
+-----+-----+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
+-----+-----+
What are the contents of table twounique after executing each of the following SQL statements?
mysql> REPLACE INTO twounique VALUES (2,2);
mysql> REPLACE INTO twounique VALUES (2,6);
| | Question 3: | How do you add multiple records to a table with a single INSERT statement? | | Question 4: | INSERT supports an IGNORE modifier, but REPLACE does not. Why is that? | | Question 5: | To completely empty a table, what statement or statements can you use? | | Question 6: | To partially empty a table, what statement or statements can you use? | | Question 7: | What's a difference in the way that MySQL handles errors for single-row and multiple-row INSERT statements when inserting NULL into a NOT NULL column? | | Question 8: | What are the reasons why an UPDATE statement might have no effect (that is, not change any values)? | | Question 9: | Why is the number of affected rows in the following UPDATE statement 0, although the number of rows matched by the WHERE clause is 5? Why is the number of matched rows 5 and not rather 10?
mysql> SELECT pid, grade FROM personnel;
+-----+-------+
| pid | grade |
+-----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | NULL |
| 10 | NULL |
| 11 | NULL |
| 12 | 1 |
| 13 | NULL |
+-----+-------+
13 rows in set
mysql> UPDATE personnel SET grade = 1 WHERE grade != 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 5 Changed: 0 Warnings: 0
| | Question 10: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start the mysql program with the --safe-updates option. | | Question 11: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start the mysql program with the --i-am-a-dummy option. | | Question 12: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start the mysql program with the --safe-updates-and-deletes option. | | Question 13: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start any client program with the --safe-updates option. | | Question 14: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start any client program with the --safe-changes option. | | Question 15: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start the mysql command-line tool with the --safe-updates option. | | Question 16: | Is the following statement true or false?
To prevent accidental UPDATE statements that would change all rows in a table, you can start the mysql command-line tool with the --i-am-a-dummy option. | | Question 17: | Consider the following listing of a table named personnel that has a primary key on the pid column:
mysql> SELECT * FROM personnel;
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 42 | 1 |
| 2 | 42 | 2 |
| 3 | 42 | NULL |
| 4 | 42 | NULL |
| 5 | 42 | NULL |
| 6 | 23 | 1 |
| 7 | 23 | 1 |
| 8 | 23 | 1 |
| 9 | 23 | NULL |
| 10 | 42 | NULL |
| 11 | 23 | NULL |
| 12 | 23 | 1 |
| 13 | 42 | NULL |
+-----+------+-------+
What single UPDATE statement would you use to set all rows with no grade to 3? | | Question 18: | Refer to the structure and contents shown for the personnel table in the previous question. What REPLACE statement would you use to set the grade column to 4 and the unit column to 45, for all rows where the pid column has the value 10? | | Question 19: | The table personnel has the following structure and data:
mysql> DESCRIBE personnel; SELECT * FROM personnel;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| pid | smallint(5) unsigned | | PRI | NULL | auto_increment |
| unit | tinyint(3) unsigned | YES | | NULL | |
| grade | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 42 | 1 |
| 2 | 42 | 2 |
| 3 | 42 | 3 |
| 4 | 42 | 3 |
| 5 | 42 | 3 |
| 6 | 23 | 1 |
| 7 | 23 | 1 |
| 8 | 23 | 1 |
| 9 | 23 | 3 |
| 10 | 42 | 3 |
| 11 | 23 | 3 |
| 12 | 23 | 1 |
| 13 | 42 | 3 |
+-----+------+-------+
What UPDATE statement would you use to multiply the grade column values by 1,000? What values would the statement produce? | | Question 20: | In the table personnel, the unit numbers were interchanged for some reason. Unit 23 is supposed to be 42, and 42 is supposed to be 23. What statement would you use to resolve this problem? Currently the table looks like this:
mysql> SELECT * FROM personnel;
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 42 | 255 |
| 2 | 42 | 255 |
| 3 | 42 | 255 |
| 4 | 42 | 255 |
| 5 | 42 | 255 |
| 6 | 23 | 255 |
| 7 | 23 | 255 |
| 8 | 23 | 255 |
| 9 | 23 | 255 |
| 10 | 42 | 255 |
| 11 | 23 | 255 |
| 12 | 23 | 255 |
| 13 | 42 | 255 |
+-----+------+-------+
| | Question 21: | The table petnames contains the following data:
mysql> SELECT * FROM petnames;
+--------+
| name |
+--------+
| Lucy |
| Macie |
| Myra |
| Cheep |
| Lucy |
| Myra |
| Cheep |
| Macie |
| Pablo |
| Stefan |
+--------+
Assume that you issue the following statement:
mysql> UPDATE petnames SET name = CONCAT(name, 1) ORDER BY name LIMIT 1;
What will the table's contents be after the UPDATE? | | Question 22: | Will the following statement delete all rows from the table mytable?
TRUNCATE TABLE mytable;
| | Question 23: | Will the following statement delete all rows from the table mytable?
DELETE FROM mytable;
|
Answers to Exercises | | | Answer 1: | There are three ways to handle records that would cause duplicate-key errors for unique indexes:
Use the IGNORE option in INSERT. IGNORE silently ignores the attempt to insert a duplicate unique key value:
mysql> INSERT IGNORE INTO twounique VALUES (1,42)
-> /* Note the number of affected rows */;
Query OK, 0 rows affected
mysql> SELECT * FROM twounique;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 1 | 2 |
+-----+-----+
Use REPLACE instead of INSERT. REPLACE replaces any row that would otherwise duplicate a unique key value. The existing row will first be deleted, and then the new row will be inserted (with the result that the otherwise duplicated key value remains the same):
mysql> REPLACE INTO twounique VALUES (1,42)
-> /* Note the number of affected rows */;
Query OK, 2 rows affected
mysql> SELECT * FROM twounique;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 1 | 42 |
+-----+-----+
Use an INSERT without the IGNORE option. An error occurs and the new record is not inserted:
mysql> INSERT INTO twounique VALUES (1,42);
ERROR 1062: Duplicate entry '1' for key 1
| | | | Answer 2: | The REPLACE statements change the table data as follows:
This statement replaces the first record rather than duplicating the existing unique value of 2 in the id2 column:
mysql> REPLACE INTO twounique VALUES (2,2);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM twounique;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 2 | 2 |
| 3 | 4 |
| 5 | 6 |
+-----+-----+
Because the original record is deleted before the new row is inserted, the server reports 2 rows affected. This statement replaces the first record (containing the value set 2,2 prior to the change), rather than duplicating the value in the id1 column:
mysql> REPLACE INTO twounique VALUES (2,6);
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM twounique;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 3 | 4 |
| 2 | 6 |
+-----+-----+
The result is that the first record then contains the value set 2,6. The statement also replaces the third record (containing the value set 5,6 prior to the change), rather than duplicating the value in the id2 column. The result is that the third record also contains the value set 2,6. Because this result is identical to the value set of the first record, the first record is then deleted. The server optimizes the REPLACE statement to avoid doing more work than necessary. Thus, the first record is deleted but not reinserted, and then the third record is deleted and then reinserted. For that reason, the server reports 3 rows affected.
| | | | Answer 3: | You can add multiple records with a single INSERT statement using multiple VALUES lists (this is an ANSI SQL-99 feature). The syntax is as follows:
INSERT INTO tbl [(col1, col2, ...)]
VALUES (value1, value2, ...), (value1, value2, ...), ...
| | | | Answer 4: | The point of REPLACE is to replace old records, not ignore new ones. If you specify the keyword IGNORE in an INSERT with multiple rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored, which means they aren't inserted. If you don't specify IGNORE, the insert is aborted if there is any row that duplicates an existing unique key value. With REPLACE, you explicitly want to replace records that would violate a PRIMARY or UNIQUE key constraint, so ignoring duplicates would make no sense. | | | | Answer 5: | TRUNCATE TABLE, or DELETE with no WHERE clause, or DELETE with a WHERE clause that's always true. Assume that you have the following table:
mysql> SELECT * FROM tbl1;
+----+----------+
| id | sometext |
+----+----------+
| 1 | boo |
| 2 | bar |
| 3 | booboo |
| 4 | barbar |
+----+----------+
You could empty this table as follows:
Use TRUNCATE TABLE:
TRUNCATE TABLE tbl1;
Use DELETE with no WHERE clause:
DELETE FROM tbl1;
This will not work when mysql has been started with the ---safe-updates option, though. Use DELETE with a WHERE clause that's always true. The following statements use expressions that are true for every record in the table tbl1:
DELETE FROM tbl1 WHERE id < 5;
DELETE FROM tbl1 WHERE 1 = 1;
| | | | Answer 6: | Use a DELETE statement that includes a WHERE clause that selects only the records to be deleted. Assume that you have the following table:
mysql> SELECT * FROM tbl1;
+----+----------+
| id | sometext |
+----+----------+
| 1 | boo |
| 2 | bar |
| 3 | booboo |
| 4 | barbar |
+----+----------+
To delete records with id values of 2 and 3, statements that accomplish that goal include the following:
mysql> DELETE FROM tbl1 WHERE id > 1 AND id < 4;
mysql> DELETE FROM tbl1 WHERE id BETWEEN 2 AND 3;
| | | | Answer 7: | Attempting to insert NULL into a column that must not contain NULL is handled differently with single-row and multiple-row inserts:
With single-row inserts, this attempt results in an error and the row isn't inserted. With multiple-row inserts, this attempt results in a warning, but the row is inserted. The NULL value is converted to the default value for the column type. This is 0 for numbers, '' (the empty string) for strings, and the "zero" value for temporal columns.
| | | | Answer 8: | An UPDATE statement changes no values if any of the following is true:
No rows match the conditions specified in the WHERE clause of the statement. The updated columns are set to their current values.
| | | | Answer 9: | The number of affected rows is 0 because the WHERE clause matches only the rows where the grade column values are set to 1 already. Thus, the values are not changed. The number of matched rows is 5 rather than 10 because for grade values of NULL, the condition grade != 2 is not true (the != operator is never true for NULL values). | | | | Answer 10: | True. To prevent accidental UPDATE (and DELETE) statements that don't contain a WHERE clause that uses a key, you can start the mysql command-line tool with the --safe-updates option:
C:\mysql\bin>mysql --safe-updates menagerie
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> DELETE FROM personnel;
ERROR 1175: You are using safe update mode and you tried to
update a table without a WHERE that uses a KEY column
| | | | Answer 11: | True. To prevent accidental UPDATE (and DELETE) statements that don't use a WHERE clause that uses a key, you can start the mysql command-line tool with the --i-am-a-dummy option, which is synonymous with the --safe-updates option:
C:\mysql\bin>mysql --i-am-a-dummy menagerie
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> UPDATE personnel SET grade = 42;
ERROR 1175: You are using safe update mode and you tried to
update a table without a WHERE that uses a KEY column
| | | | Answer 12: | False. There is no such option as --safe-updates-and-deletes to the mysql command-line tool. (In fact, there's no such option to any MySQL program.) | | | | Answer 13: | False. The --safe-updates option is supported only by the mysql client program. | | | | Answer 14: | False. There is no such option as --safe-changes to the mysql command-line tool. (In fact, there's no such option to any MySQL program.) | | | | Answer 15: | True. To prevent accidental UPDATE (and DELETE) statements that don't use a WHERE clause that uses a key, you can start the mysql command-line tool with either the --safe-updates or the --i-am-a-dummy option (they're synonymous). | | | | Answer 16: | True. To prevent accidental UPDATE (and DELETE) statements that don't use a WHERE clause that uses a key, you can start the mysql command-line tool with either the --safe-updates or the --i-am-a-dummy option (they're synonymous). | | | | Answer 17: | The rows with no grade are those containing NULL in the grade column. The following UPDATE statement sets the grade column to 3 in all rows where grade is NULL:
mysql> UPDATE personnel SET grade = 3 WHERE grade IS NULL;
Query OK, 7 rows affected
Rows matched: 7 Changed: 7 Warnings: 0
| | | | Answer 18: | To make the required changes, you would issue this REPLACE:
mysql> REPLACE INTO personnel VALUES (10,45,4);
Query OK, 2 rows affected
mysql> SELECT * FROM personnel WHERE pid=10;
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 10 | 45 | 4 |
+-----+------+-------+
| | | | Answer 19: | The following statement multiplies values in the grade column by 1,000:
mysql> UPDATE personnel SET grade = grade * 1000;
In this case, however, the statement would not produce the desired result. This is because the grade column has a datatype of TINYINT UNSIGNED and thus has a maximum value of 255. The result of the UPDATE statement would be as follows:
mysql> SELECT grade FROM personnel;
+-------+
| grade |
+-------+
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
| 255 |
+-------+
| | | | Answer 20: | To swap unit numbers 23 and 42, you would issue this statement:
mysql> UPDATE personnel
-> SET unit = IF(unit=23, 42, 23)
-> ;
Query OK, 13 rows affected
Rows matched: 13 Changed: 13 Warnings: 0
mysql> SELECT * FROM personnel;
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 23 | 255 |
| 2 | 23 | 255 |
| 3 | 23 | 255 |
| 4 | 23 | 255 |
| 5 | 23 | 255 |
| 6 | 42 | 255 |
| 7 | 42 | 255 |
| 8 | 42 | 255 |
| 9 | 42 | 255 |
| 10 | 23 | 255 |
| 11 | 42 | 255 |
| 12 | 42 | 255 |
| 13 | 23 | 255 |
+-----+------+-------+
| | | | Answer 21: | The UPDATE changes the row that is first when they're sorted by name. The first name is Cheep. It occurs twice, but LIMIT constrains the change to include just one of the rows. The resulting table contents are as follows:
mysql> SELECT * FROM petnames;
+--------+
| name |
+--------+
| Lucy |
| Macie |
| Myra |
| Cheep1 |
| Lucy |
| Myra |
| Cheep |
| Macie |
| Pablo |
| Stefan |
+--------+
| | | | Answer 22: | Yes, TRUNCATE TABLE mytable will delete all records. The number of affected rows this statement returns will not necessarily be exact. | | | | Answer 23: | Yes, DELETE FROM mytable will delete all records. It will also return the exact number of deleted records. |
|
