Alter and Insert Commands


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:

See if you can tell what has been done here.

mysql> alter table people add COMPONENT char(15) NOT NULL; Query OK, 0 rows affected (0.02 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(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.

mysql> insert into people (ID, NAME_LAST, NAME_FIRST, MI, POSITION) -> values ('rjones', 'Jones', 'Robert', 'T', 'Major'); Query OK, 1 row affected (0.01 sec) mysql> select * from people;
+--------+-----------+------------+----+------------+
| 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.


Data, Create Tables   < <  PREVIOUS   Table of Contents NEXT  > >   Update, Delete

Developed with HTML-Kit
Sandersongs Web Tutorials
Contact the Webmasterwith comments.
©2017, by Bill Sanders, all rights reserved.
This domain had 3,957 different visits in the last 30 days.
435,432 hits on this domain since 24 Nov 2006.
http://sandersongs.com/PHPsqlCourse/SQL03.php
This page was last modified on our server on 4 Jun 2008
and last refreshed on our server at 12:24 pm, MST
This file took 0.00903 seconds to process.