Insertion of Records

The INSERT INTO command is how you add new rows of data to a table. You must always specify the table name, and then provide the values you want to store. The simplest form is:

INSERT INTO tablename VALUES(value1, value2, ...);

Here, the first value you write goes into the first column of the table, the second value into the second column, and so on. This works only if you supply exactly as many values as there are columns in the table, and in the same order the columns were defined when the table was created.

For example, to add the first record to the GUARDIAN table (which has four columns: GUID, GName, Gphone, GAddress), you write:

mysql> INSERT INTO GUARDIAN
    -> VALUES (444444444444, 'Amit Ahuja',
    -> 5711492685, 'G-35, Ashok vihar, Delhi');
Query OK, 1 row affected (0.01 sec)

Notice that text values (like names and addresses) and date values must be enclosed in single quotes ' '. Numbers are written without quotes.

To see the records you have inserted, use the SELECT * FROM tablename command. This displays all rows and columns of the table.

mysql> SELECT * from GUARDIAN;
+--------------+---------------+------------+---------------------------+
| GUID         | GName         | Gphone     | GAddress                  |
+--------------+---------------+------------+---------------------------+
| 444444444444 | Amit Ahuja    | 5711492685 | G-35, Ashok vihar, Delhi |
+--------------+---------------+------------+---------------------------+
1 row in set (0.00 sec)

Inserting values for only some columns

Often you do not have data for every column. For instance, a phone number might be unknown, or a foreign key might be optional. In such cases, you can specify exactly which columns you want to fill. The syntax is:

INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);

The columns you list and the values you provide must match in number and order. Any column you omit will be filled with its default value — usually NULL if no other default was set when the table was created.

Look at the fourth record in the GUARDIAN table: Danny Dsouza has no phone number. The Gphone column was defined to allow NULL, so we can insert only the other three fields:

mysql> INSERT INTO GUARDIAN(GUID, GName, GAddress)
    -> VALUES (333333333333, 'Danny Dsouza',
    -> 'S -13, Ashok Village, Daman');
Query OK, 1 row affected (0.03 sec)

When you view the table now, the missing phone number appears as NULL:

mysql> SELECT * from GUARDIAN;
+--------------+--------------+-----------+---------------------------+
| GUID         | GName        | Gphone    | GAddress                  |
+--------------+--------------+-----------+---------------------------+
| 333333333333 | Danny Dsouza | NULL      | S -13, Ashok Village,Daman|
| 444444444444 | Amit Ahuja   | 5711492685| G-35, Ashok vihar, Delhi |
+--------------+--------------+-----------+---------------------------+
2 rows in set (0.00 sec)

Inserting records into a table with a foreign key

When a table has a foreign key (like GUID in the STUDENT table, which references GUARDIAN), you must ensure that the referenced record already exists in the parent table. Otherwise, the foreign key constraint will reject the insertion.

For the StudentAttendance database, the GUARDIAN table has no foreign key, so it is populated first. Then records are inserted into the STUDENT table.

To insert the first student record (RollNumber 1, Atharv Ahuja), you can use either form:

-- Without column names (all values in order)
mysql> INSERT INTO STUDENT
    -> VALUES(1,'Atharv Ahuja','2003-05-15', 444444444444);

-- With column names (more explicit)
mysql> INSERT INTO STUDENT (RollNumber, SName, SDateofBirth, GUID)
    -> VALUES (1,'Atharv Ahuja','2003-05-15', 444444444444);

Both statements do the same thing. Note that dates must be in the 'YYYY-MM-DD' format.

Handling NULL values explicitly

If a column allows NULL and you are using the short form (without column names), you must write the keyword NULL in the value list for that column. For example, the third record in the STUDENT table (Taleem Shah) has no guardian, so GUID is NULL:

mysql> INSERT INTO STUDENT
    -> VALUES (3, 'Taleem Shah', '2002-02-28', NULL);

Alternatively, you can use the column-name form and simply omit the GUID column:

mysql> INSERT INTO STUDENT (RollNumber, SName, SDateofBirth)
    -> VALUES (3, 'Taleem Shah', '2002-02-28');

Both methods produce the same result — a row where GUID is NULL.