we created the database Student Attendance having three relations STUDENT, GUARDIAN and ATTENDANCE. When we create a table, only its structure is created but the table has no data. To populate records in the table, INSERT statement is used. Also, table records can be deleted or updated using DELETE and UPDATE statements. These SQL statements are part of Data Manipulation Language (DML).
Data Manipulation using a database means either insertion of new data, removal of existing data or modification of existing data in the database.
INSERTION of Records
INSERT INTO statement is used to insert new records in a table.
Its syntax is:
INSERT INTO tablename
VALUES(value 1, value 2,....);
Here, value 1 corresponds to attribute 1, value 2 corresponds to attribute 2 and so on. Note that we need not to specify attribute names in the insert statement if there are exactly the same numbers of values in the INSERT statement as the total number of attributes in the table.
Caution: While populating records in a table with foreign key, ensure that records in referenced tables are already populated.
Let us insert some records in the Student Attendance database. We shall insert records in the GUARDIAN table first as it does not have any foreign key. A set of sample records for GUARDIAN table is shown in the given table (Table).
GUARDIAN Table
The following insert statement adds the first record in the table:
mysql> INSERT INTO GUARDIAN
-> VALUES (444444444444, 'Amit Ahuja',
-> 5711492685, 'G-35,Ashok vihar, Delhi' );
Query OK, 1 row affected (0.01 sec)
We can use the SQL statement SELECT * from table_name to view the inserted records. The SELECT statement will be explained in the next section.
If we want to insert values only for some of the attributes in a table (supposing other attributes having NULL or any other default value), then we shall specify the attribute names in which the values are to be inserted using the following syntax of INSERT INTO statement.
Syntax:
INSERT INTO tablename (column1, column2, ...)
VALUES (value1, value2, ...);
To insert the fourth record of Table. where GPhone is not given, we need to insert values in the other three fields (GPhone was set to NULL by default at the time of table creation). In this case, we have to specify the names of attributes in which we want to insert values. The values must be given in the same order in which attributes are written in INSERT statement.
mysql> INSERT INTO GUARDIAN(GUID, GName, GAddress)
-> VALUES (333333333333, 'Danny Dsouza',
-> 'S -13, Ashok Village, Daman' );
Query OK, 1 row affected (0.03 sec)
Let us now insert the records given in Table into the STUDENT table.
STUDENT Table
To insert the first record of Table, we write the following MySQL statement:
mysql> INSERT INTO STUDENT
-> VALUES(1,'Atharv Ahuja','2003-05-15',
444444444444);
Query OK, 1 row affected (0.11 sec)
OR
mysql> INSERT INTO STUDENT (RollNumber, SName,
SDateofBirth, GUID)
-> VALUES (1,'Atharv Ahuja','2003-05-15',
444444444444);
Query OK, 1 row affected (0.02 sec)
Recall that Date is stored in ‘YYYY-MM-DD’ format.
Let us now insert the third record of Table 9.7 where GUID is NULL. Recall that GUID is foreign key of this table and thus can take NULL value.
Hence, we can put NULL value for GUID and insert the record by using the following statement:
mysql> INSERT INTO STUDENT
-> VALUES(3, 'Taleem Shah','2002-02-28', NULL);
Query OK, 1 row affected (0.05 sec)
We had to write NULL in the above insert statement because we are not mentioning the column names. Otherwise, we should mention the names of attributes along with the values if we need to insert data only for certain attributes,
as shown in the following query:
mysql> INSERT INTO STUDENT (RollNumber, SName,
-> SDateofBirth) VALUES (3, 'Taleem Shah','2002-02- 28');
Query OK, 1 row affected (0.05 sec)
