1 00:00:00,120 --> 00:00:02,780 Next, we have a bit more to cover with basic inserts. 2 00:00:02,790 --> 00:00:10,050 First of all, I mentioned the order matters in the sense that if we tell my SQL that name is coming 3 00:00:10,050 --> 00:00:14,850 first in this first part of the insert, then we have to make sure name comes first. 4 00:00:14,850 --> 00:00:20,580 It doesn't know that this is a name and this is an age unless we follow the order that we lay out, 5 00:00:20,700 --> 00:00:22,170 but we can switch that order. 6 00:00:22,170 --> 00:00:25,350 There's no rule that says we have to have name and then age. 7 00:00:25,350 --> 00:00:27,240 We could do age and the name. 8 00:00:27,240 --> 00:00:29,910 We just want to make sure that we follow that pattern. 9 00:00:29,910 --> 00:00:36,660 So if I try that insert into cats and then I'll say age first and then name and then our values will 10 00:00:36,660 --> 00:00:41,070 be and you can see I typed a quote because I was just not paying attention. 11 00:00:41,070 --> 00:00:49,890 We don't want to quote because we're inserting an age first, let's say a two year old cat named about 12 00:00:49,890 --> 00:00:50,610 Beth. 13 00:00:51,030 --> 00:00:51,780 Sure. 14 00:00:51,960 --> 00:00:54,810 And if I insert that, it looks like it worked. 15 00:00:55,020 --> 00:01:03,240 And if I do a select star from Cats again, we'll see that Beth was inserted as the name in that row 16 00:01:03,240 --> 00:01:05,010 and age was two. 17 00:01:05,099 --> 00:01:09,690 So as long as we follow the order we laid out the order overall doesn't matter. 18 00:01:09,690 --> 00:01:15,000 It doesn't matter if age comes before name or name before age, as long as we are consistent with our 19 00:01:15,000 --> 00:01:15,690 values. 20 00:01:16,020 --> 00:01:18,960 Now, what happens if we are not consistent? 21 00:01:18,960 --> 00:01:26,730 What if I did insert into cats and I say, expect the name first and then the age, and then in my values 22 00:01:26,820 --> 00:01:30,780 I get lazy or I don't pay attention and I put the age first. 23 00:01:30,780 --> 00:01:35,790 We have eight and then the name of Linus. 24 00:01:37,560 --> 00:01:38,460 What happens? 25 00:01:38,460 --> 00:01:40,860 Then we get an error. 26 00:01:40,860 --> 00:01:43,920 We get an error that says incorrect integer value. 27 00:01:43,920 --> 00:01:51,810 Linus For the column called Age, you can't have an integer, which is what age is supposed to be called 28 00:01:51,810 --> 00:01:52,380 Linus. 29 00:01:52,380 --> 00:01:53,400 That is not an integer. 30 00:01:53,400 --> 00:01:54,630 So it's telling me that. 31 00:01:54,870 --> 00:01:56,100 So what does that mean? 32 00:01:56,100 --> 00:01:59,880 If I look at cats, do we have Linus in here? 33 00:01:59,910 --> 00:02:01,410 No, we don't. 34 00:02:01,410 --> 00:02:04,590 So this error prevented the row from being inserted. 35 00:02:04,590 --> 00:02:05,790 Just be aware of that. 36 00:02:06,060 --> 00:02:08,820 These column data types are not guidelines. 37 00:02:08,820 --> 00:02:10,050 They are rules. 38 00:02:10,050 --> 00:02:14,130 At least with the way everything set up right now, age has to be an integer. 39 00:02:14,130 --> 00:02:16,380 There's no way to put a piece of text in there. 40 00:02:16,380 --> 00:02:19,440 So we tried that and we got slapped in the face. 41 00:02:19,440 --> 00:02:21,120 My SQL says, not so fast. 42 00:02:21,360 --> 00:02:23,730 Okay, so that's a bit more about the order. 43 00:02:23,730 --> 00:02:30,300 The next thing I want to cover is a multiple insert so we can insert multiple rows at once, which is 44 00:02:30,300 --> 00:02:31,380 a common operation. 45 00:02:31,380 --> 00:02:34,980 We don't have to have three separate insert into cats. 46 00:02:34,980 --> 00:02:42,090 I can do one insert into cats and then have multiple values sets of values separated by commas. 47 00:02:42,540 --> 00:02:45,420 So let's try that this time. 48 00:02:45,420 --> 00:02:48,510 Maybe I'll do it from a file just so we have a record of it. 49 00:02:49,020 --> 00:02:53,190 So I'll do insert into cat's name and then age. 50 00:02:53,190 --> 00:02:55,080 We don't have to do that order, but I will. 51 00:02:55,080 --> 00:02:56,400 And then values. 52 00:02:56,400 --> 00:03:00,910 And the first name that will do is who should we do? 53 00:03:00,930 --> 00:03:02,640 This is the hardest part of the whole thing. 54 00:03:03,100 --> 00:03:07,280 How about a cat called Meatball and Meatballs? 55 00:03:07,290 --> 00:03:12,150 Age is five, and then I can add a comma and just have another pair. 56 00:03:12,390 --> 00:03:14,250 Let's do turkey. 57 00:03:14,850 --> 00:03:17,490 And turkey is one and one more. 58 00:03:17,490 --> 00:03:19,650 I guess we'll go with some food themed ones. 59 00:03:19,650 --> 00:03:22,680 How about potato face? 60 00:03:24,240 --> 00:03:27,120 Potato face is 15. 61 00:03:27,510 --> 00:03:33,750 So one thing that you might see people do is format these so that all those insert or rather all the 62 00:03:33,750 --> 00:03:35,010 parentheses line up. 63 00:03:35,010 --> 00:03:36,630 You really don't have to do that. 64 00:03:36,630 --> 00:03:39,840 It doesn't matter whatsoever, especially when we just have three. 65 00:03:39,840 --> 00:03:41,610 Everyone can figure out what's happening here. 66 00:03:41,610 --> 00:03:44,370 But if you wanted to be consistent, you totally can. 67 00:03:45,000 --> 00:03:50,580 All right, so I'm going to run this execute, assuming I got Oh, I didn't put a semicolon there. 68 00:03:50,610 --> 00:03:53,070 Let's see, did it complain to me? 69 00:03:53,970 --> 00:03:55,080 It looked like it worked. 70 00:03:55,080 --> 00:03:58,020 So it may have figured out what I was trying to do. 71 00:03:58,170 --> 00:03:59,490 But let's verify over here. 72 00:03:59,490 --> 00:04:01,410 Select Star from Cats. 73 00:04:01,590 --> 00:04:02,910 Yeah, it did work. 74 00:04:02,910 --> 00:04:07,620 So if I had done that over here in the MySQL client, I left off a semicolon. 75 00:04:07,620 --> 00:04:07,830 What? 76 00:04:07,830 --> 00:04:08,940 We know what happens, right? 77 00:04:08,940 --> 00:04:15,330 So like star from Cats, nothing happens until I enter that semicolon to terminate the line. 78 00:04:15,570 --> 00:04:19,920 But over here inside of DB gate, it can figure out. 79 00:04:19,920 --> 00:04:22,530 I guess it just has some logic to add that semicolon on. 80 00:04:22,650 --> 00:04:23,310 Who knows? 81 00:04:24,060 --> 00:04:27,780 Anyway, I should put it there because it's technically required. 82 00:04:27,900 --> 00:04:29,640 So that's how we can do a multiple insert. 83 00:04:29,640 --> 00:04:30,210 This is nice. 84 00:04:30,210 --> 00:04:35,910 If you have multiple rows you want to insert at once, you don't want to have a separate insert into 85 00:04:35,910 --> 00:04:37,200 for every single one. 86 00:04:37,200 --> 00:04:41,450 Instead you can just chain them all together as long as they all follow the same pattern you laid out 87 00:04:41,460 --> 00:04:44,940 name and then h name, age, name, age, name, age. 88 00:04:45,870 --> 00:04:48,780 That's multi insert and that's it for this video.