Use
these bookmarks to jump around the tutorial:
[What
is an INSERT statement?]
[What
is an UPDATE statement?]
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.
back to
top
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.
back to
top
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.
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.
back to
top
What’s Next?
Alright, the data is in.
Now how do we get it back out?
In Part 4 of this series
we will:
-
Learn about the SELECT
statement -
Learn about the FROM
clause -
Learn about the WHERE
clause
