To insert, save or add data
to database table we use the insert statement which is shown below.
INSERT INTO table_name VALUES
(list of values separated by commas).
See the query below for more details.
The query below will insert
data into all the columns in the products table we created in the previous tutorial.
INSERT INTO products VALUES
("PI001","TELEVISION", "LG", 50000, 3, "2020-01-11",
"2020-01-12")
Congratulations! You just
inserted your a record into your database.
What the query does is: it
tells the system to go our database and look for a table named products (as
there could be more than one table in a database) and INSERT all the VALUES
(our products information) listed in the bracket INTO it.
NOTE:
- The use of double
quotes and commas around the values inside the bracket. The comma is used to
separate each value from the next one while the double quote is used to render
the values it encloses in string (text) format to align with the datatype we chose for those
particular columns when we created the table. You can refer to the previous tutorial for a quick
recap. The values that are not enclosed in double quotes are integer (whole numbers) values
which also corresponds to the datatypes we chose for those columns when we created
the table in the previous
tutorial.
- That the number
of values in the bracket is equal to the number columns in the table we created
in the previous tutorial.
- There is no comma
included after the last value in the bracket
- That the values
in the bracket above are arranged in the same order with the columns in our
table to avoid inputting data in a column it does not belong to. That is, from product_id
down to entry_date.
Now to have a view of what
you just did, type and run the query below as you did in the previous tutorial.
The result should look like the picture below the query.
SELECT * FROM products
Okay, let’s try one more
example to get you more accustomed to the INSERT syntax. This time we will insert
3 records at a time to establish the fact that you can insert more than one records
at a time.
NOTE: It’s advisable you type
out the code for now rather than copy and paste it so you get more used to the
syntax. You can copy and paste when you have mastered the syntax.
Below goes the second
example.
INSERT INTO products VALUES ("PI002", "REFRIGERATOR", "THERMOCOOL", 120000, 5, "2011-11-15", "2011-11-15");
INSERT INTO products VALUES
("PI003", "ELECTRIC
FAN", "PANASONIC", 6500, 2, "2019-12-02", "2019-12-02");
INSERT INTO products VALUES
("PI004", "GAS
COOKER", "LG", 23000, 5, "2019-09-22", "2019-09-02");
Notice the inclusion of semicolon
at the end of each line of the insert statement which is not compulsory
while inserting only one record. The semicolon helps the system to know that
each line of the insert statement terminates at the point where the
semicolon is located, else it will see all the lines of the query as a single query
and will trigger an error.
Now, like you did earlier
type and run the select * statement to have a look of the outcome of
your query. The picture below shows what it would look like.
INSERTING
VALUES IN SPECIFIED COLUMNS
Unlike we did before above ‘we inserted data into
all the columns of our table’, in this part of this we are going to insert
data into some columns leaving out some other columns. This can be done using
the query below.
INSERT INTO products (product_id, product_name, price,
quantity, entry_date) VALUES
("PI005", "LAPTOP", 150000, 5, "2019-09-02")
Here what the query does
is:
It tells the system to go to our
database and look for a table named products and INSERT INTO the listed
columns (in the first bracket) the VALUES listed in the second bracket
leaving out the columns that no values were provided for (the missing
columns). The system then automatically inserts NULL values into missing
columns which in other words means that they contain no value. You will learn more
about NULL and its syntax in a later
tutorial.
The outcome of the query above is shown below.
Comments
Post a Comment