Bright Java Tutorial

is brought to you by DoITBright LLC

MySQL

Insert Statement In MySQL

We previously created database tables. The purpose of those tables are for us to store data and having in mind that we will need to retrieve these data that we will store in the future. Let us now look at an example on how to insert data. Lets use use the database tables we created in the previous lesson.
  INSERT INTO ACCOUNT(EMAIL, CREATE_DATE) 
      VALUES ('customer1@testmail.com', CURDATE());
                            
By executing the above code, we added 1 record into the ACCOUNT table. The first part of the INSERT statement declared which columns are we inserting the record to. The second part of the INSERT statement are the data that will be stored in each column respectively. You will also notice that we did not include the ACCOUNT_ID column in the INSERT statement. The reason for this is, the value for that is automatically generated since we declared it as AUTO_INCREMENT during the creation of the table. Let us insert 1 more record.
  INSERT INTO ACCOUNT(EMAIL, CREATE_DATE) 
      VALUES ('customer2@testmail.com', CURDATE());
                            
We now have 2 records in the ACCOUNT table. Let us now insert records in the ORDERED_ITEM table. Imagine customer1@testmail.com is shopping in your website. He then ordered 2 items.
  INSERT INTO ORDERED_ITEM 
      (DESCRIPTION, PRICE, ORDER_DATE, ACCOUNT_ID) 
      VALUES ('12 Piece Crayons', 2.50, CURDATE(), 1);
                            
Please take note that we enclose varchar data types with single quotes. For numeric values, we do not enclose the value with single quotes. The CURDATE() is a MySQL function to retrieve the current date. As for the ACCOUNT_ID, we used 1 as the value. Since the ACCOUNT table is freshly created and our assumption is, it is the first time that records were inserted to it, we can assume that the first 2 accounts that we stored there will have an ACCOUNT_ID values of 1 and 2 respectively. For now, let us just leave it as it is. You will learn how to verify this when we go to the topic about retrieving data. Let us store one more order for ACCOUNT_ID = 1.
  INSERT INTO ORDERED_ITEM 
      (DESCRIPTION, PRICE, ORDER_DATE, ACCOUNT_ID) 
      VALUES ('24 Piece Crayons', 4.50, CURDATE(), 1);
                            
We now have 2 records for the first account. Let us store one order for the ACCOUNT_ID = 2.
  INSERT INTO ORDERED_ITEM 
      (DESCRIPTION, PRICE, ORDER_DATE, ACCOUNT_ID) 
      VALUES ('100 Sheets Copy Paper', 5.50, CURDATE(), 2);
                            
Is it possible to insert a record in ORDERED_ITEM when the supplied ACCOUNT_ID is non existing in the ACCOUNT table? The answer is no.

Delete Statement In MySQL

Since we have learned how to store records in database tables, we also need to know how to delete these records. With the data structure that we have right now, it is not possible to delete a record in ACCOUNT table without deleting all records in ORDERED_ITEM table that is related to the record we wish to delete in the ACCOUNT table. Let us take ACCOUNT_ID = 2 as an example.
  DELETE FROM ORDERED_ITEM WHERE ACCOUNT_ID = 2;
                            
In the above statement, we deleted all rows that have an ACCOUNT_ID equal to 2. This means, all the ordered items of that customer were deleted from the database table ORDERED_ITEM. We can now delete the row in ACCOUNT table.
  DELETE FROM ACCOUNT WHERE ACCOUNT_ID = 2;
                            
If you wish to delete all data inside a table, simply remove the WHERE clause. Your DELETE statement will be, DELETE FROM <TABLE NAME>. Let us insert records to replace what we deleted since we will use these in succeeding lessons.
  INSERT INTO ACCOUNT(EMAIL, CREATE_DATE) 
      VALUES ('customer3@testmail.com', CURDATE());
                            
Do not forget the ordered item.
  INSERT INTO ORDERED_ITEM 
      (DESCRIPTION, PRICE, ORDER_DATE, ACCOUNT_ID) 
      VALUES ('100 Sheets Copy Paper', 5.50, CURDATE(), 3);
                            



Back    Next