INSERTION of Records

A newly created table is an empty frame — the INSERT INTO statement is what puts records into it. Its basic syntax supplies one value per attribute:

INSERT INTO tablename VALUES(value 1, value 2,....);

Here value 1 corresponds to attribute 1, value 2 to attribute 2, and so on — the match is purely by position. Attribute names need not be written in the statement at all, provided the INSERT supplies exactly as many values as the table has attributes.

That caution decides the order of work in StudentAttendance: records go into GUARDIAN first, since it has no foreign key. The records to be inserted (Table 8.6) are:

The first record goes in with the positional form:

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

To view what was inserted, use the statement SELECT * from table_name, which lists the records of the table (the SELECT statement itself is explained in the next section):

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 attributes. Sometimes a record has no value for a particular attribute, which then keeps NULL or whatever default was set at table creation. In that case the attribute names must be specified alongside the values:

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

The values must be given in the same order in which the attributes are written in the INSERT command. The fourth record of Table 8.6 — Danny Dsouza — has no GPhone, so values are inserted only in the other three fields (GPhone was set to NULL by default at the time of table creation):

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

A SELECT now shows both guardians, with NULL sitting in Danny Dsouza's Gphone column. Writing the SQL statements to insert the remaining three rows of Table 8.6 into GUARDIAN is Activity 8.6.

Populating STUDENT. With guardians in place, the STUDENT records (Table 8.7) can be inserted — their GUID values reference guardians that now exist:

Recall that a date is stored in the "YYYY-MM-DD" format, which is why 15th May 2003 is written '2003-05-15'. The first record can be inserted in either of two equivalent ways — positionally, or with the attribute names spelled out:

mysql> INSERT INTO STUDENT
    -> VALUES(1,'Atharv Ahuja','2003-05-15',
    -> 444444444444);
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO STUDENT (RollNumber, SName,
    -> SDateofBirth, GUID)
    -> VALUES (1,'Atharv Ahuja','2003-05-15',
    -> 444444444444);
Query OK, 1 row affected (0.02 sec)

Inserting a NULL value explicitly. The third record of Table 8.7 has no GUID. GUID is the foreign key of STUDENT, and a foreign key may take a NULL value — so the record can be inserted by writing NULL in that position: