We created an empty table earlier in this chapter. But no matter how well defined, an empty table isn't very useful. We can add whatever records we want to our table using the insert into command. The syntax is
mysql> INSERT INTO <Table> VALUES (value1, value2...);
We can also specify the order of columns between the table name and values statements. We can even leave out columns (and their associated values), if they are allowed to be null.
mysql> INSERT INTO <Table> (col2, col1...) VALUES (value2, value1...);
This command allows us to change the value of data within any table.
UPDATE <table> SET <column> = <value>;
SET causes all the records to have their <column>'s contents changed to <value>. This command is clearly much more useful when combined with the WHERE constraint. If WHERE identifies the primary key, we can update some or all of the columns in that record. Alternatively, we can do massive search-and-replace operations when WHERE identifies a value in the same target column as SET .
mysql> UPDATE Questions SET Author = 28 WHERE Author = 12;
This command transfers all the questions that had been credited to Player 12 to the authorship of Player 28.
It is easy to remove a row from any table.
DELETE FROM <table>;
Clearly we want to use WHERE here as well. Without any constraints, we simply erase all the contents of the table!
mysql> DELETE FROM Players WHERE PlaID= 12;
This removes Player 12 from the database. Some SQL databases can be set so that this deletion will automatically update all the tables that might refer to Player 12. For example, Players.PlaID is a foreign key in the Author column of the Questions table. The DBMS can remove all Questions linked to Player 12 or set them to Null Author, or it can prevent the deletion unless we perform the UPDATE that we did a page or so back, where we changed ownership of all of Player 12s questions. However, MySQL does none of this automatically, and it is incumbent on the programmer to remember to keep the references uncorrupted.