Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
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