Adding Data to Tables

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

 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

 mysql> INSERT INTO  <Table> (col2, col1...) VALUES (value2, value1...); 

Update

This command allows us to change the value of data within any table.

MySQL

 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 .

Example:

MySQL

 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.

Delete

It is easy to remove a row from any table.

MySQL

 DELETE FROM  <table>; 

Clearly we want to use WHERE here as well. Without any constraints, we simply erase all the contents of the table!

Example:

MySQL

 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.