Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this video we'll take a look at adding rows of information into a table.
SQL Used
Inserting a single row:
INSERT INTO <table> VALUES (<value 1>, <value 2>, ...);
This will insert values in the order of the columns prescribed in the schema.
Examples:
INSERT INTO users VALUES (1, "chalkers", "Andrew", "Chalkley");
INSERT INTO users VALUES (2, "ScRiPtKiDdIe", "Kenneth", "Love");
INSERT INTO movies VALUES (3, "Starman", "Science Fiction", 1984);
INSERT INTO movies VALUES (4, "Moulin Rouge!", "Musical", 2001);
Inserting a single row with values in any order:
INSERT INTO <table> (<column 1>, <column 2>) VALUES (<value 1>, <value 2>);
INSERT INTO <table> (<column 2>, <column 1>) VALUES (<value 2>, <value 1>);
Examples:
INSERT INTO users (username, first_name, last_name) VALUES ("chalkers", "Andrew", "Chalkley");
INSERT INTO users (first_name, last_name, username) VALUES ("Kenneth", "Love", "ScRiPtKiDdIe");
INSERT INTO movies (title, genre, year_released) VALUES ("Starman", "Science Fiction", 1984);
INSERT INTO movies (title, year_released, genre) VALUES ("Moulin Rouge!", 2001, "Musical");
See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.
Remember, each CRUD operation
has its own SQL keyword.
0:00
We already know the keyword for
reading from a database SELECT.
0:04
The keyword to create a row is INSERT.
0:08
Let's take a look at the general use
case to insert data into a table.
0:11
You start off with the keyboard
INSERT INTO table name.
0:16
This shows the database you want to
insert some data into a particular table.
0:19
Then you include the keyword VALUES,
followed by the values
0:25
we want to insert into the table,
the values are surrounded in parentheses.
0:28
The order of these values are important,
0:33
the values have to be inserted in the same
order the schema describes the columns.
0:36
Let's take a look at our
library database and
0:41
insert some new books
into our books table.
0:44
This is what the books
table looks like now.
0:47
We have an ID, title, author,
genre, and first_published columns.
0:49
The last book ID is 15.
0:56
Our new book should have the ID of 16.
0:59
Let's write the SQL statement
to insert George Orwell's 1984.
1:02
First, it's INSERT INTO books,
1:10
followed by VALUES 16,
1:17
the string 1984 as the title.
1:23
George Orwell [SOUND] as the author.
1:31
Fiction as the genre.
1:39
And first published is an integer of 1949.
1:45
Let's run that.
1:50
And as you can see,
1:53
it's been inserted into the books
table in the correct columns.
1:54
Selecting all records, looking at the last
ID and then incrementing it by one
2:00
yourself, as you entered new rows into
a database, seems like a lot of work.
2:04
Also, other database users may
be entering their own rows.
2:10
Your generated ID and
their generated ID may clash.
2:15
Remember, IDs are supposed to be unique,
and they're used as a reference for
2:18
a particular row in the table.
2:23
SQL databases have automated
this tedious dance, and
2:25
conflicts can be avoided with
a feature called auto increment.
2:29
Auto increment generates the latest ID for
you so you don't have to.
2:33
Most database tables with the ID
column will have the auto
2:38
increment feature switched on.
2:41
People who designed the database schema
would have most likely switched it on.
2:43
Let's insert another book, Dune.
2:48
Instead of having an integer,
we enter the value, or rather,
2:50
the absence of a value, NULL.
2:55
When the row is inserted, Because
2:58
the ID column has been designated
as an auto-incremented column,
3:03
the database will give the new
entry the correct ID, which is 17.
3:08
Sometimes if the schema allows,
you can insert NULL values into columns
3:14
when you don't know the information or
you want to fill it in later.
3:19
For example, in our loans table, we have
missing values in the returned_on column.
3:23
This is because they
haven't been returned yet.
3:29
If we wanted to enter a new entry into the
loans table where we didn't want the value
3:31
to be entered into the returned_on column,
we could just use the value of NULL.
3:36
Now it's not very clear
what all these values are.
3:43
Luckily, SQL has an alternative
way to make this clearer.
3:46
Just after the table name,
3:51
we can include another set of
parentheses with the column names in.
3:53
Then followed by the keyword VALUES.
4:12
And then all the values you want.
4:18
You're also not restricted by
ordering the columns in this order.
4:22
As long as you order the values in
the same order as you order your columns
4:26
they will get inserted correctly.
4:30
For example,
4:33
I could move the ID to the second to
the last column in the columns entry.
4:33
If I move the ID to the second to last and
4:46
its value to the second to last,
it would have the same results as if
4:49
they were written in the order
they were in the schema.
4:54
You can change this into any
combination you'd like as
5:03
long as the order of the values matches
the column order in your statement.
5:07
Even with the book ID moved to
the third column and value positions,
5:16
this same result will happen.
5:21
The row will be created with
the correct values in each column.
5:23
Only the ID column will get auto
incremented, not the returned_on.
5:28
The value in this case will be empty.
5:32
Finally, because the ID and
the returned_on are NULL, meaning that
5:36
they are absent, we don't have to
specify them in our columns or values.
5:41
The database will automatically know that
there should be NULL because you didn't
5:53
specify them.
5:58
When we run the statement,
we see the final sixth row entered
5:59
with the auto incremented ID and
the returned_on as NULL.
6:03
We can write the general case of
these insert statements like this.
6:08
The INSERT INTO keyword combo,
followed by the table name or
6:11
the column names that you want to enter
values in, surrounded in parenthesis.
6:15
Then the keyword VALUES followed by
a value-filled pair of parentheses.
6:20
Something you should be aware of is,
while some columns allow for null values,
6:27
database designers requires certain
columns must have a value present.
6:31
For example,
if you wanted to delete the book_id, And
6:36
the value in our INSERT query and
6:42
we try to create a new entry,
we would get an error.
6:44
Error: NOT NULL constraint
failed: loans.book_id.
6:49
It doesn't make sense that we
should be able to create a loan
6:54
without an associated book.
6:58
The schema has been
designed with that in mind.
7:00
In fact, the ID, book_id, patron_id,
7:04
loaned_on, and
return_by can not have null values.
7:09
Only the returned_on can be NULL.
7:15
This makes sense since it's going
to be returned at a future time.
7:17
People can get extensions and update
the returned_by, but it can't be NULL.
7:21
You need to sign up for Treehouse in order to download course files.
Sign up