To alter a table after creation, we need to use the ALTER TABLE command. ALTER TABLE allows us to change the structure of a given table. This command typically works in conjunction with optional followup commands. Some of these include:
ADD - the ADD command needs two pieces of information after it - the new column name and the new column type. Additionally, we must alert the program as to where we want to add the table. Options include FIRST, LAST or AFTER. In the case of AFTER, we must specify the column that we want it to follow.
ALTER TABLE tblName ADD colName colType AFTER otherCol;
ALTER TABLE questions ADD pointValue int(5) LAST;
DROP - the DROP command allows a programmer to delete a named column. Once a column (and its data) are dropped, they cannot be recovered.
ALTER TABLE tblName DROP colName;
Updating Data
The UPDATE command allows users to change the values of the data in a table, after it has been entered. The most common additional command is SET. SET allows users to set the values in a given column. Without any other information, SET will change the entire column. To affect only certain records, the SET command can be combined with a WHERE clause, limiting the rows that are altered.
UPDATE tblName SET colName=2; //changes all values in colName to 2
UPDATE tblName SET colName=2 WHERE gender='F'; //changes all values in colName to 2 only if the gender value is 'F'
We can also delete an entire record, without altering other entries. To do this, we use the DELETE command as seen below:
DELETE FROM tblName where id=3; //deletes the record with id=3 in tblName