WEBVTT 1 00:00:00.350 --> 00:00:01.470 Hi, guys, welcome back. 2 00:00:01.470 --> 00:00:02.840 In this video we're going to show you 3 00:00:02.840 --> 00:00:06.800 how the INSERT INTO statement is constructed. 4 00:00:06.800 --> 00:00:09.660 So here's an example, INSERT INTO users, 5 00:00:09.660 --> 00:00:11.700 and then the First Name and Surname columns, 6 00:00:11.700 --> 00:00:14.210 VALUES, Rolf, and Smith. 7 00:00:14.210 --> 00:00:17.120 The first part, INSERT INTO users tells the database 8 00:00:17.120 --> 00:00:18.540 that we want to add data, 9 00:00:18.540 --> 00:00:21.900 that's INSERT INTO, the Users table. 10 00:00:21.900 --> 00:00:25.770 Then we've got the columns that we want to insert into. 11 00:00:25.770 --> 00:00:28.900 This is optional, and if we don't provide the columns 12 00:00:28.900 --> 00:00:30.440 that we want to insert data into, 13 00:00:30.440 --> 00:00:32.530 we're going to be inserting into all columns, 14 00:00:32.530 --> 00:00:35.950 and then we will have to provide data for all columns. 15 00:00:35.950 --> 00:00:38.960 So at the end, we tell the database what values we want 16 00:00:38.960 --> 00:00:40.580 to insert in those columns, and remember, 17 00:00:40.580 --> 00:00:43.370 if we skipped the columns earlier on, 18 00:00:43.370 --> 00:00:44.660 then we need to provide values 19 00:00:44.660 --> 00:00:47.720 for all the columns in the table. 20 00:00:47.720 --> 00:00:49.500 So this is a quick deconstruction 21 00:00:49.500 --> 00:00:50.890 of the INSERT INTO command. 22 00:00:50.890 --> 00:00:53.720 There's more information about this command in the e-book, 23 00:00:53.720 --> 00:00:56.830 which is linked in the Resources section of this lecture. 24 00:00:56.830 --> 00:00:58.600 Now, let's get coding and writing 25 00:00:58.600 --> 00:01:01.563 some INSERT INTO commands over in the database viewer. 26 00:01:02.780 --> 00:01:05.350 All right, now let's go into the db browser 27 00:01:05.350 --> 00:01:07.460 and look at how to insert data. 28 00:01:07.460 --> 00:01:08.610 There's a couple of ways. 29 00:01:08.610 --> 00:01:10.830 We've got our Users table already created. 30 00:01:10.830 --> 00:01:13.730 We're gonna go to Browse Data in here, 31 00:01:13.730 --> 00:01:17.890 and here you can see that we've got nothing at the moment. 32 00:01:17.890 --> 00:01:19.410 You can click this icon at any time 33 00:01:19.410 --> 00:01:21.090 to refresh the data just in case 34 00:01:21.090 --> 00:01:23.430 that you've got a Python application changing it, 35 00:01:23.430 --> 00:01:26.360 this will let you see the new data as it comes in. 36 00:01:26.360 --> 00:01:28.070 But, what we're gonna do just now is to make sure 37 00:01:28.070 --> 00:01:31.760 to select the correct table and then click on New Record. 38 00:01:31.760 --> 00:01:35.210 Notice that we get Null and Null as the two values 39 00:01:35.210 --> 00:01:37.410 in these fields, but you can go in them and change them, 40 00:01:37.410 --> 00:01:41.570 so you can add John Smith, for example. 41 00:01:41.570 --> 00:01:43.050 Now, that's in here, 42 00:01:43.050 --> 00:01:45.684 but the changes haven't actually been saved 43 00:01:45.684 --> 00:01:48.810 so we have to go ahead and write the changes 44 00:01:48.810 --> 00:01:49.770 to the database file. 45 00:01:49.770 --> 00:01:50.720 So all of this, at the moment, 46 00:01:50.720 --> 00:01:53.400 is just stored in memory and it will disappear 47 00:01:53.400 --> 00:01:55.100 as soon as you close the programme. 48 00:01:55.100 --> 00:01:58.840 If we write the changes then they will be permanently saved. 49 00:01:58.840 --> 00:02:00.690 Similarly, if you create a new record 50 00:02:00.690 --> 00:02:04.060 and you type something like another name, 51 00:02:04.060 --> 00:02:06.870 now this is still in memory and if you made a mistake, 52 00:02:06.870 --> 00:02:10.400 you can revert the changes by just clicking that, 53 00:02:10.400 --> 00:02:13.280 and now this is going to undo all the changes since it 54 00:02:13.280 --> 00:02:15.200 was last saved, you can press Yes, 55 00:02:15.200 --> 00:02:19.650 and you back to the last time you saved the database. 56 00:02:19.650 --> 00:02:20.920 Now, this is all well and good, 57 00:02:20.920 --> 00:02:23.520 but we're going to do the same with SQL, 58 00:02:23.520 --> 00:02:26.710 so I'm gonna go and select this row here and delete it, 59 00:02:26.710 --> 00:02:29.140 and now we're gonna go over to Execute SQL 60 00:02:29.140 --> 00:02:31.563 and we're going to type our commands in here. 61 00:02:32.840 --> 00:02:36.630 As you can remember, INSERT INTO tells the database 62 00:02:36.630 --> 00:02:38.940 that we're wanting to insert data. 63 00:02:38.940 --> 00:02:40.720 The table that we wanna insert into, 64 00:02:40.720 --> 00:02:43.350 and then the values that we want to insert. 65 00:02:43.350 --> 00:02:44.400 So here we're gonna type, 66 00:02:44.400 --> 00:02:48.370 inside single quotation marks, Rolf Smith. 67 00:02:48.370 --> 00:02:52.280 It's red, but that's just the db browser, the colour, 68 00:02:52.280 --> 00:02:53.570 you don't have to worry about it being red, 69 00:02:53.570 --> 00:02:55.310 it doesn't mean there's an error. 70 00:02:55.310 --> 00:02:58.620 So now we can press Play and the value works, 71 00:02:58.620 --> 00:03:00.380 the query executed successfully. 72 00:03:00.380 --> 00:03:03.150 The data is inserted, we can go over to Browse Data, 73 00:03:03.150 --> 00:03:04.620 Rolf Smith is there. 74 00:03:04.620 --> 00:03:06.590 If you use double quotation marks, 75 00:03:06.590 --> 00:03:09.130 in some databases it won't work. 76 00:03:09.130 --> 00:03:11.540 For example, in PostgreSQL, single quotation marks 77 00:03:11.540 --> 00:03:13.640 and double quotation marks have different meanings, 78 00:03:13.640 --> 00:03:16.740 so I'd recommend to stick to single quotation marks 79 00:03:16.740 --> 00:03:20.870 for text that you want to insert into a table. 80 00:03:20.870 --> 00:03:23.990 Remember that if you want to insert data only into 81 00:03:23.990 --> 00:03:26.970 some columns, like for example, Surname, 82 00:03:26.970 --> 00:03:29.180 then you can do that by putting the column names, 83 00:03:29.180 --> 00:03:31.090 in brackets, after the table name. 84 00:03:31.090 --> 00:03:33.910 Here, we're gonna put only data into Surname, 85 00:03:33.910 --> 00:03:38.520 so we will only provide one value to be inserted. 86 00:03:38.520 --> 00:03:40.420 So if we run this, you'll see 87 00:03:40.420 --> 00:03:41.860 that when we go on Browser Data, 88 00:03:41.860 --> 00:03:44.120 we have Null in the first column 89 00:03:44.120 --> 00:03:46.260 and Smith in the second column. 90 00:03:46.260 --> 00:03:47.850 Depending on how your table is defined, 91 00:03:47.850 --> 00:03:49.150 as we will learn later on, 92 00:03:49.150 --> 00:03:51.550 this can sometimes give you an error. 93 00:03:51.550 --> 00:03:53.520 For example, we can tell the table 94 00:03:53.520 --> 00:03:57.500 that it should not accept empty values, and if we do that, 95 00:03:57.500 --> 00:03:59.230 then this would raise an error at this point 96 00:03:59.230 --> 00:04:02.120 because the First Name column doesn't have a value. 97 00:04:02.120 --> 00:04:05.570 As I said, we're going to learn more about that later on. 98 00:04:05.570 --> 00:04:07.420 Remember that, if you want to add data 99 00:04:07.420 --> 00:04:10.210 into multiple columns, these are, again, 100 00:04:10.210 --> 00:04:12.350 comma separated as before. 101 00:04:12.350 --> 00:04:14.080 And, if you do that, you do have 102 00:04:14.080 --> 00:04:18.500 to provide the values for each column as well. 103 00:04:18.500 --> 00:04:19.630 All right, that's everything 104 00:04:19.630 --> 00:04:22.730 about inserting data into a table. 105 00:04:22.730 --> 00:04:24.120 Thanks for joining me in this video. 106 00:04:24.120 --> 00:04:25.820 And, I'll see you in the next one.