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