1 00:00:00,300 --> 00:00:04,260 Remember, each CRUD operation has its own SQL keyword. 2 00:00:04,260 --> 00:00:08,670 We already know the keyword for reading from a database SELECT. 3 00:00:08,670 --> 00:00:11,430 The keyword to create a row is INSERT. 4 00:00:11,430 --> 00:00:16,160 Let's take a look at the general use case to insert data into a table. 5 00:00:16,160 --> 00:00:19,950 You start off with the keyboard INSERT INTO table name. 6 00:00:19,950 --> 00:00:25,020 This shows the database you want to insert some data into a particular table. 7 00:00:25,020 --> 00:00:28,560 Then you include the keyword VALUES, followed by the values 8 00:00:28,560 --> 00:00:33,670 we want to insert into the table, the values are surrounded in parentheses. 9 00:00:33,670 --> 00:00:36,180 The order of these values are important, 10 00:00:36,180 --> 00:00:40,700 the values have to be inserted in the same order the schema describes the columns. 11 00:00:41,840 --> 00:00:44,080 Let's take a look at our library database and 12 00:00:44,080 --> 00:00:47,260 insert some new books into our books table. 13 00:00:47,260 --> 00:00:49,900 This is what the books table looks like now. 14 00:00:49,900 --> 00:00:56,460 We have an ID, title, author, genre, and first_published columns. 15 00:00:56,460 --> 00:00:59,400 The last book ID is 15. 16 00:00:59,400 --> 00:01:02,370 Our new book should have the ID of 16. 17 00:01:02,370 --> 00:01:07,460 Let's write the SQL statement to insert George Orwell's 1984. 18 00:01:10,468 --> 00:01:17,966 First, it's INSERT INTO books, 19 00:01:17,966 --> 00:01:23,654 followed by VALUES 16, 20 00:01:23,654 --> 00:01:31,168 the string 1984 as the title. 21 00:01:31,168 --> 00:01:39,811 George Orwell [SOUND] as the author. 22 00:01:39,811 --> 00:01:43,650 Fiction as the genre. 23 00:01:45,160 --> 00:01:50,770 And first published is an integer of 1949. 24 00:01:50,770 --> 00:01:51,400 Let's run that. 25 00:01:53,650 --> 00:01:54,910 And as you can see, 26 00:01:54,910 --> 00:02:00,030 it's been inserted into the books table in the correct columns. 27 00:02:00,030 --> 00:02:04,380 Selecting all records, looking at the last ID and then incrementing it by one 28 00:02:04,380 --> 00:02:10,970 yourself, as you entered new rows into a database, seems like a lot of work. 29 00:02:10,970 --> 00:02:15,210 Also, other database users may be entering their own rows. 30 00:02:15,210 --> 00:02:18,930 Your generated ID and their generated ID may clash. 31 00:02:18,930 --> 00:02:23,820 Remember, IDs are supposed to be unique, and they're used as a reference for 32 00:02:23,820 --> 00:02:25,760 a particular row in the table. 33 00:02:25,760 --> 00:02:29,330 SQL databases have automated this tedious dance, and 34 00:02:29,330 --> 00:02:33,260 conflicts can be avoided with a feature called auto increment. 35 00:02:33,260 --> 00:02:38,190 Auto increment generates the latest ID for you so you don't have to. 36 00:02:38,190 --> 00:02:41,480 Most database tables with the ID column will have the auto 37 00:02:41,480 --> 00:02:43,540 increment feature switched on. 38 00:02:43,540 --> 00:02:48,530 People who designed the database schema would have most likely switched it on. 39 00:02:48,530 --> 00:02:50,980 Let's insert another book, Dune. 40 00:02:50,980 --> 00:02:55,580 Instead of having an integer, we enter the value, or rather, 41 00:02:55,580 --> 00:02:57,440 the absence of a value, NULL. 42 00:02:58,470 --> 00:03:03,615 When the row is inserted, Because 43 00:03:03,615 --> 00:03:08,808 the ID column has been designated as an auto-incremented column, 44 00:03:08,808 --> 00:03:14,010 the database will give the new entry the correct ID, which is 17. 45 00:03:14,010 --> 00:03:19,700 Sometimes if the schema allows, you can insert NULL values into columns 46 00:03:19,700 --> 00:03:23,730 when you don't know the information or you want to fill it in later. 47 00:03:23,730 --> 00:03:29,030 For example, in our loans table, we have missing values in the returned_on column. 48 00:03:29,030 --> 00:03:31,900 This is because they haven't been returned yet. 49 00:03:31,900 --> 00:03:36,720 If we wanted to enter a new entry into the loans table where we didn't want the value 50 00:03:36,720 --> 00:03:41,440 to be entered into the returned_on column, we could just use the value of NULL. 51 00:03:43,990 --> 00:03:46,660 Now it's not very clear what all these values are. 52 00:03:46,660 --> 00:03:50,230 Luckily, SQL has an alternative way to make this clearer. 53 00:03:51,430 --> 00:03:53,195 Just after the table name, 54 00:03:53,195 --> 00:03:57,584 we can include another set of parentheses with the column names in. 55 00:04:12,558 --> 00:04:15,080 Then followed by the keyword VALUES. 56 00:04:18,800 --> 00:04:20,680 And then all the values you want. 57 00:04:22,650 --> 00:04:26,120 You're also not restricted by ordering the columns in this order. 58 00:04:26,120 --> 00:04:30,800 As long as you order the values in the same order as you order your columns 59 00:04:30,800 --> 00:04:33,010 they will get inserted correctly. 60 00:04:33,010 --> 00:04:33,955 For example, 61 00:04:33,955 --> 00:04:38,697 I could move the ID to the second to the last column in the columns entry. 62 00:04:46,622 --> 00:04:49,374 If I move the ID to the second to last and 63 00:04:49,374 --> 00:04:54,190 its value to the second to last, it would have the same results as if 64 00:04:54,190 --> 00:04:58,163 they were written in the order they were in the schema. 65 00:05:03,954 --> 00:05:07,450 You can change this into any combination you'd like as 66 00:05:07,450 --> 00:05:12,242 long as the order of the values matches the column order in your statement. 67 00:05:16,935 --> 00:05:21,024 Even with the book ID moved to the third column and value positions, 68 00:05:21,024 --> 00:05:23,310 this same result will happen. 69 00:05:23,310 --> 00:05:28,010 The row will be created with the correct values in each column. 70 00:05:28,010 --> 00:05:32,980 Only the ID column will get auto incremented, not the returned_on. 71 00:05:32,980 --> 00:05:35,280 The value in this case will be empty. 72 00:05:36,580 --> 00:05:41,581 Finally, because the ID and the returned_on are NULL, meaning that 73 00:05:41,581 --> 00:05:46,848 they are absent, we don't have to specify them in our columns or values. 74 00:05:53,600 --> 00:05:58,131 The database will automatically know that there should be NULL because you didn't 75 00:05:58,131 --> 00:05:59,560 specify them. 76 00:05:59,560 --> 00:06:03,640 When we run the statement, we see the final sixth row entered 77 00:06:03,640 --> 00:06:08,190 with the auto incremented ID and the returned_on as NULL. 78 00:06:08,190 --> 00:06:11,770 We can write the general case of these insert statements like this. 79 00:06:11,770 --> 00:06:15,950 The INSERT INTO keyword combo, followed by the table name or 80 00:06:15,950 --> 00:06:20,720 the column names that you want to enter values in, surrounded in parenthesis. 81 00:06:20,720 --> 00:06:27,090 Then the keyword VALUES followed by a value-filled pair of parentheses. 82 00:06:27,090 --> 00:06:31,870 Something you should be aware of is, while some columns allow for null values, 83 00:06:31,870 --> 00:06:36,670 database designers requires certain columns must have a value present. 84 00:06:36,670 --> 00:06:42,100 For example, if you wanted to delete the book_id, And 85 00:06:42,100 --> 00:06:44,971 the value in our INSERT query and 86 00:06:44,971 --> 00:06:49,575 we try to create a new entry, we would get an error. 87 00:06:49,575 --> 00:06:54,650 Error: NOT NULL constraint failed: loans.book_id. 88 00:06:54,650 --> 00:06:58,520 It doesn't make sense that we should be able to create a loan 89 00:06:58,520 --> 00:07:00,700 without an associated book. 90 00:07:00,700 --> 00:07:04,170 The schema has been designed with that in mind. 91 00:07:04,170 --> 00:07:09,130 In fact, the ID, book_id, patron_id, 92 00:07:09,130 --> 00:07:15,380 loaned_on, and return_by can not have null values. 93 00:07:15,380 --> 00:07:17,830 Only the returned_on can be NULL. 94 00:07:17,830 --> 00:07:21,990 This makes sense since it's going to be returned at a future time. 95 00:07:21,990 --> 00:07:26,880 People can get extensions and update the returned_by, but it can't be NULL.