ALTER Command
With the ALTER Command, you can add, modify or drop a field in a table. (The ALTER command modifies fields, which are the structure of the table. The INSERT command modifies records, which are the data within the structure of the table.) The syntax is:
- ALTER TABLE <table_name> ADD <new_field_name> <new_field_type> <modifiers>
- ALTER TABLE <table_name> MODIFY <old_field_name> <new_field_type> <modifiers>
- ALTER TABLE <table_name> DROP <old_field_name>
See if you can tell what has been done here.
+------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | ID | char(8) | | PRI | | | | NAME_LAST | char(20) | | | | | | NAME_FIRST | char(20) | | | | | | MI | char(1) | | | | | | POSITION | char(6) | | | | | | COMPONENT | char(15) | | | | | +------------+----------+------+-----+---------+-------+6 rows in set (0.01 sec) mysql> alter table people modify COMPONENT char(20); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe people;
+------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | ID | char(8) | | PRI | | | | NAME_LAST | char(20) | | | | | | NAME_FIRST | char(20) | | | | | | MI | char(1) | | | | | | POSITION | char(6) | | | | | | COMPONENT | char(20) | YES | | NULL | | +------------+----------+------+-----+---------+-------+6 rows in set (0.00 sec) mysql> alter table people drop COMPONENT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe people;
+------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | ID | char(8) | | PRI | | | | NAME_LAST | char(20) | | | | | | NAME_FIRST | char(20) | | | | | | MI | char(1) | | | | | | POSITION | char(6) | | | | | +------------+----------+------+-----+---------+-------+5 rows in set (0.00 sec) mysql>
Practice it yourself on your own tables.
INSERT Command
Now we can fill the table with records. Recall that the ALTER command modifies fields, while the INSERT command modifies records. The syntax of the INSERT command is as follows: INSERT into <table_name> (<field_name_1>, <field_name2>, ... <field_name_n>) VALUES (<value_1>, <value_2>, <value_n>). You can put the field names in any order, but the values have to go in the same order as the field names. If the field names are omitted, the values will be put in the default order.
+--------+-----------+------------+----+------------+ | ID | NAME_LAST | NAME_FIRST | MI | POSITION | +--------+-----------+------------+----+------------+ | rjones | Jones | Robert | T | Manager | +--------+-----------+------------+----+------------+1 row in set (0.00 sec) mysql> insert into people -> values ('tjones', 'Jones', 'Tyler', 'R', 'Technician'); Query OK, 1 row affected (0.00 sec) mysql> insert into people -> values ('bsmith', 'Smith', 'Barbara', 'N', 'Clerk'); Query OK, 1 row affected (0.00 sec) mysql> insert into people -> values ('nsmith', 'Smith', 'Nancy', 'B', 'Manager'); Query OK, 1 row affected (0.00 sec) mysql> select * from people;
+--------+-----------+------------+----+------------+ | ID | NAME_LAST | NAME_FIRST | MI | POSITION | +--------+-----------+------------+----+------------+ | rjones | Jones | Robert | T | Manager | | tjones | Jones | Tyler | R | Technician | | bsmith | Smith | Barbara | N | Clerk | | nsmith | Smith | Nancy | B | Manager | +--------+-----------+------------+----+------------+4 rows in set (0.00 sec) mysql>
Insert all four records in your own people table. You will use this table in upcoming exercises.
