Alright, we’re making some progress now. You have a database and you’ve created some tables. What about putting some data into the tables?
What is an INSERT statement?
I’m sure that it’s not hard to guess what the INSERT statement does. However, how it works will probably need a little explanation. In order to use INSERT you will need to learn about a couple of other important keywords, INTO and VALUES.
Let’s start with INTO. In order for the DBMS to know where to put the data that you are adding it has to know what table you want the data to be added to. Using the “contact” table that we created in part 2 we will begin to create an INSERT statement to enter data:
INSERT INTO contacts
This part of the statement tells the DBMS that we are about to insert a new row of data into the table "contacts".
Vocabulary Note: Row refers to a row of data in a table with each piece of data in its proper column.
So, how does the DBMS know what columns we want to insert data into? That's next:
INSERT INTO contacts (first_name,
last_name,
email)
This tells the DBMS that we are creating a row of data that will be placing information in the following columns: "first_name", "last_name" and "email". What about "contact_id"? Well, "contact_id" is our column that automatically generates a new number each time a new row is created so there is no point in entering a number manually. In fact, you could get an error if you try to do so.
The next keyword that you need to know is VALUES. I'm sure you will be shocked to learn that VALUES is where you list what is to be inserted into your new row. For example:
INSERT INTO contacts (first_name,
last_name,
email)
VALUES ('John',
'Smith',
'jsmith@huh.com');
Notice that the data is listed in the same order that we listed the column names above. If you list your data in a different order you could end up with data in the wrong columns and create a major disaster for yourself.
What if you don't have anything to enter into a column? Maybe you are missing some data. Well, let's say for the sake of argument you are missing John's last name. Here is how you would insert that data:
INSERT INTO contacts (first_name,
last_name,
email)
VALUES ('John',
NULL,
'jsmith@huh.com');
What is that NULL thing? NULL is how you tell the DBMS that you have nothing to enter in this column. Be careful, though. If you try to use a NULL in a column that must have a value in it you will get an error.
Should I always list all of my table columns? Yes. By listing each column you spell out exactly what you are doing. That way if during some future update someone else was reading your code they could easily see what you had intended to do, especially if the table had been added to or changed over time.
What is an UPDATE statement?
Again, it's pretty obvious what UPDATE does. Now that you have some data in your table you may want to update it. So, let's update John's email address.
We know that John's ID number is 1. (We'll go through how you retrieve someone's ID number in Part 4.) So, let's update John's email address to jsmith@HTMLGoodies.com.
UPDATE contacts
SET email = 'jsmith@HTMLGoodies.com'
WHERE contact_id = 1;
Alright, here's the breakdown. UPDATE tells the DBMS the we are going to change something in the table named "contacts".
SET says we are going to change a row in the "email" column to "jsmith@HTMLGoodies.com". You can set as many columns as you like by separating each item with a comma.
WHERE then let's the DBMS know which row we want to change. In this case we are changing the row where our ID number is 1. Notice the 1 does not have any single quotes around it. That's because the "contact_id" column is an integer (number) and not a string (group of characters). If you did try to put quotes around the 1 it would cause an error.
Is that all there is to it?
No, not really. What you are learning here is merely the tip of the proverbial iceberg. There is much more to learning about databases but this will give you a good foundation to build on. There is always more to learn and, as usual, nothing stays the same.
Database FAQs
So, what's the deal with the semicolon? The semicolon at the end of a statement simply tells the DBMS that you are done with that statement and it should process it now. DBMSs can very greatly when it comes to designating the end of a statement so be sure to check your DBMS and see which one is right for you.
Isn't there a better way to enter some data? Better is relative. The INSERT and UPDATE statements that you learned above along with many others will be necessary when you begin coding in technologies like ASP, JSP and PHP. There are GUI interfaces for almost all DBMSs now which pretty much layout your tables in an easy to use format so that you can enter data without typing a word of SQL. These can be handy for doing some simple updates, adding test data and examining how your SQL script effects a table. Learning how to code SQL statements, however, gives you the background to fully utilize your database within whatever technology you choose.
So, what happens if I generate a SQL error? Well, most DBMSs will simply stop and not execute the statement where the error occurred. In other words, if in the example above we forgot that "last_name" had to have a value and we tried to insert a NULL the INSERT statement would do absolutely nothing. It would not go ahead and create a row with just "John" in the "first_name" column.
Should I always spell out in painful detail everything that I am doing? Yes! It may be more typing in the short term but it sure makes life easier in the long run. You may remember quite vividly how you did your code from 2 weeks ago but will you have that clarity one year from now?
Should I take advantage of shortcuts I find with my DBMS? That depends but probably not. If your DBMS has a shortcut that saves you some time typing it may be very tempting to use it. Keep in mind, though, whenever you use a shortcut that is specific to your DBMS that you may sacrifice portability. For example, you have created a database for your web based business and took advantage of some shortcuts provided with your small business DBMS that you bought. Now, three years later your business has outgrown your DBMS and you have to invest in an more robust DBMS. You begin to move your data and code over only to find that your shortcuts don't work with the new DBMS and you have to spend days or weeks finding all of the places that you used the shortcuts and fix them.
