Database Reference
In-Depth Information
Inserting data
To put data into our database, we use the
INSERT
command. The basic syntax is
as follows:
INSERT [INTO]
<table_name>
[(
<column_name>
[,
column_name
,...])]
{VALUES | VALUE}
({
expression
|DEFAULT},...)[,(...),...];
As with the
CREATE TABLE
command, the parts in angle brackets (
<>
) are what we'll
replace with our own values. The square brackets (
[]
) are optional and the pipe
character (
|
) means
or
. The curly brackets (
{}
) specify a section that is mandatory
but for this there is a choice of the key word you can use. For example, the
INTO
keyword is optional but makes the
INSERT
line more readable and we can use the
keyword
VALUES
or
VALUE
depending on whether or not we are inserting a column of
information or multiple items but we must use one of them. Anywhere when we see
three dots (
...
) it represents a part where the previous part can be repeated.
Expression just means the value we want to put in the column. It could be a
calculated value (such as today's date + four days), a static value (such as John) or it
could be the default value assigned to the column (if it has one). Default values are
assigned using the keyword
DEFAULT
without any quotes.
If we do not specify the columns we want to insert into, then we must specify a
value for each column. If we don't want to assign a value (and if the column
definition allows it) we can specify
NULL
, which means no value.
We can also insert multiple rows at once (remember to use the keyword
VALUES
when doing so!). Each row is wrapped in parentheses and multiple rows are
separated by commas.
Following are some basic
INSERT
examples. They are split onto multiple lines to aid
reading but can be entered with a single long line if desired. Remember that a single
command ends with either the semicolon (
;
) or
\G
delimiters.
INSERT INTO employees (surname,givenname) VALUES
("Taylor","John"),
("Woodruff","Wilford"),
("Snow","Lorenzo");
INSERT INTO employees (pref_name,givenname,surname,birthday)
VALUES ("George","George Albert","Smith","1970-04-04");
INSERT employees (surname) VALUE ("McKay");