WEBVTT 00:00.210 --> 00:04.320 OK so we're moving on now and it's time for brand new section. 00:04.320 --> 00:10.200 In this case we're spending some time revisiting something we've already discussed which is data types. 00:10.380 --> 00:16.530 So very early on one of the first videos once we got everything set up and installed one of the first 00:16.530 --> 00:19.220 videos was on very basic data types. 00:19.280 --> 00:25.750 We talked about how data takes different forms and you have text and numbers and I kind of laughed at 00:25.800 --> 00:26.610 that. 00:26.730 --> 00:32.780 We talked about our char and our two first data types but I showed you that there are a bunch more. 00:33.270 --> 00:39.060 Well now is it time that I promised feed revisit we'd returned to those data type and go into a bit 00:39.060 --> 00:44.880 more detail and depth around some of the important data types so we're still not going to go over every 00:44.880 --> 00:51.180 single one because a lot of them are just not used and some of them are very very similar with you know 00:51.210 --> 00:55.740 tiny differences that honestly won't make that aren't relevant to the score. 00:55.740 --> 01:00.030 So what we're going to do is focus on what will have the highest impact for you. 01:00.030 --> 01:04.770 What has the highest impact in you know my developer life and the people that I know enough to talk 01:04.770 --> 01:07.710 to and about how they use MY as well. 01:07.800 --> 01:12.990 So basically it boils down to a couple of different data types and a couple different broad categories 01:13.000 --> 01:18.880 so the first category is text or string data types. 01:19.190 --> 01:21.590 And then we also talk about numeric data types. 01:21.650 --> 01:24.560 We have a lot to talk about there because we've only worked with it. 01:24.680 --> 01:31.010 We need to talk about how we store decimal points or how we store you know non whole numbers came over 01:31.060 --> 01:32.560 the term for that. 01:32.870 --> 01:39.590 And also we need to talk about how we store things like dates and times really important part of using 01:39.800 --> 01:44.710 a database is typically at least in web applications or applications in general. 01:44.710 --> 01:49.520 You're usually storing some sort of date and time information about when a transaction happens when 01:49.520 --> 01:50.800 somebody signs up for your site. 01:50.810 --> 01:53.480 The last time something was updated and so on. 01:53.510 --> 01:55.000 So we'll be discussing that as well. 01:55.190 --> 01:59.290 But for now in this video we're focusing on storing text. 01:59.810 --> 02:03.900 So as you mentioned we've already seen var Charen of our car. 02:04.250 --> 02:12.470 However we want to say it I think most people actually say var car but how can I save our car doesn't 02:12.490 --> 02:15.820 really make sense I guess you say character not character. 02:15.950 --> 02:16.460 All right. 02:16.700 --> 02:18.320 So we've already talked about it. 02:18.320 --> 02:20.310 It's what we've been using to store text. 02:20.630 --> 02:28.170 But there's also Chaar our car which is an entirely different separate data type. 02:28.190 --> 02:32.220 Now they both store text but there's one really key difference. 02:33.390 --> 02:36.050 Well what is that difference. 02:36.480 --> 02:38.540 Char has a fixed length. 02:38.550 --> 02:45.030 So when we specify let's say we're working on let's say we're working with our books table and we want 02:45.030 --> 02:51.330 to have a column called Title we could have right now of our char and our existing books table but we 02:51.330 --> 02:58.590 could specify its char and give it a fixed length of 5 for title. 02:59.010 --> 03:05.220 And what that means is that every title that we add in is going to be five characters. 03:05.220 --> 03:09.340 So if it's greater than 5 characters if it's 10 characters it's going to be truncated is going to get 03:09.360 --> 03:15.250 chopped at that 5 character Mark and if it's fewer you'll see in a moment where it does actually add 03:15.250 --> 03:18.160 spaces to make it five characters. 03:18.420 --> 03:25.060 So char will always allocate the same amount of space for every row or every instance of that char versus 03:25.120 --> 03:26.620 var char can vary. 03:26.760 --> 03:32.940 And as you can see here if we did something like chars three only three characters are allowed in this 03:33.000 --> 03:34.320 example. 03:34.320 --> 03:36.710 So this is taken straight from the documentation. 03:36.720 --> 03:42.490 Some really boring explanatory text but has some important things to know the length of a char columnist's 03:42.510 --> 03:44.980 fixed to the length that you declared when you create the table. 03:45.030 --> 03:49.560 We've already discussed that the length can be any value from 0 to 255. 03:49.770 --> 03:58.020 So the longest char car whatever that you can have is 255 characters long when char values are stored. 03:58.020 --> 04:01.380 They are right padded with spaces to the specified length. 04:01.710 --> 04:03.880 So that's kind of what I was explaining. 04:03.900 --> 04:13.290 If we have a 10 character limit let's say char 10 and I say I insert something that has only five characters. 04:13.290 --> 04:15.410 It's going to add five spaces after it. 04:15.420 --> 04:22.430 On the right side of it pad it with these based spaces to make it exactly 10 characters. 04:23.520 --> 04:29.160 However it's kind of confusing because when you go to look at that string when you select it back in 04:29.170 --> 04:30.040 it's are treated. 04:30.210 --> 04:32.650 Those spaces are actually removed as you can see here. 04:32.760 --> 04:37.180 When char values are retrieved trailing spaces are removed in less blah blah blah. 04:37.200 --> 04:42.890 There is a way to get them to stay there but I'm not sure why you'd want that to happen. 04:42.900 --> 04:48.300 The only reason that I want to point that out is that I think it helps understand helps you understand 04:48.750 --> 04:50.140 or has helped other students. 04:50.140 --> 04:53.400 I've worked with understand how the fixed length works. 04:53.910 --> 04:59.340 If you think in memory that it's actually storing something the same size every time is always five 04:59.340 --> 05:02.840 or always 10 characters and you can't get around it. 05:02.850 --> 05:08.420 So if you give it something shorter it will along get it with spaces to make it 10 characters. 05:08.420 --> 05:10.280 Figure out something longer than 10 characters. 05:10.380 --> 05:13.160 It's going to truncate it to make it 10 characters. 05:13.200 --> 05:14.820 So why does it matter. 05:15.390 --> 05:22.470 Well char is faster for fixed length text so there's not a ton of uses. 05:22.470 --> 05:29.500 In my experience day to day for using char unless you know for sure that something is fixed length. 05:29.700 --> 05:35.730 So I tried to come up with some examples like state abbreviations is one at least in the US where we 05:35.730 --> 05:38.680 have two letter abbreviations for every state. 05:38.820 --> 05:41.680 There's no reason to make that a variable length of our char. 05:41.750 --> 05:50.570 You can save space and time essentially by using a chart or a yes or no flag wire and or any sort of 05:50.570 --> 05:51.680 a binary. 05:51.700 --> 05:55.540 It doesn't even have to be binary but something that has a fixed length. 05:55.550 --> 06:03.410 So you could do sex in this case if you're doing you know where a pounder would be called an animal 06:03.410 --> 06:08.730 shelter for keeping track of animals and their sex could use an F. 06:08.810 --> 06:15.470 However if we want to do things like male and female or unknown or whatever other options those aren't 06:15.500 --> 06:16.330 all the same length. 06:16.340 --> 06:18.780 So it makes more sense to use far char. 06:19.370 --> 06:23.270 So you probably come up with some other situations where this would make sense. 06:23.270 --> 06:26.970 But typically we use far too hard because it gives us a lot of flexibility. 06:28.560 --> 06:31.130 Otherwise you're smarter. 06:31.350 --> 06:36.180 So there's a little there's a table here that I actually took this from the docs just kind of blew it 06:36.210 --> 06:44.010 up and added it into my slides but it's from the documentation for char and varchar and what it's showing 06:44.010 --> 06:47.550 you here for a given value like it. 06:47.550 --> 06:57.590 Let's take a B if we have a column with data type char for you can see that it adds the two spaces so 06:57.590 --> 06:59.520 it's actually stored as AB space. 06:59.540 --> 07:09.310 Space and if we store the same thing string a B in a column with our chart for so far instead of char 07:09.940 --> 07:12.490 it just stores a B. 07:12.520 --> 07:18.700 But what I want to call your attention to is not just how it's stored but also the storage size how 07:18.700 --> 07:20.650 many bytes it takes up. 07:20.710 --> 07:30.010 So if we look at char when we declare char for every single string that we insert into that column will 07:30.010 --> 07:36.100 always be 4 bytes and you can see over here whether it's an empty string it will convert it to be four 07:36.100 --> 07:42.010 spaces if it's two characters it adds two spaces if it's four characters it leaves it alone if it's 07:42.010 --> 07:42.880 more than four. 07:42.880 --> 07:44.190 It truncates it. 07:44.290 --> 07:46.330 So we have a fixed size here. 07:46.480 --> 07:53.670 However with the bar chart for the same values all have different storage sizes. 07:53.800 --> 07:55.940 So they're unchanged when they're stored. 07:56.020 --> 08:02.890 We don't get spaces added We don't get anything truncated which is good but you can see that our storage 08:02.890 --> 08:09.600 size does start to blossom blossom starts to grow as the length of the strings grow. 08:09.700 --> 08:10.970 Now it's not a one to one thing. 08:11.080 --> 08:16.030 So it's not like if we have a ten character string here it's going to take up 10 bytes. 08:16.210 --> 08:17.820 But there is a difference. 08:17.830 --> 08:19.410 That's all that matters. 08:19.450 --> 08:24.780 With that said To be honest that difference won't make a big difference. 08:25.420 --> 08:26.480 Let me put it another way. 08:26.500 --> 08:33.020 The difference is insignificant for most applications that I have ever worked on. 08:33.070 --> 08:39.310 If you are working on some giant app you know you work a bank of America and you've got you know tens 08:39.310 --> 08:43.800 of millions of customers and hundreds of millions of records. 08:43.840 --> 08:45.850 That does make a significant difference. 08:45.940 --> 08:51.190 But if you're working on a personal project or your own hobby application you don't really need to fret 08:51.550 --> 08:52.770 over this sort of thing. 08:52.780 --> 08:57.550 In fact is it something sort of a common strategy that a lot of people would say is just get something 08:57.550 --> 08:58.110 working. 08:58.210 --> 09:01.680 And then when you get it when it comes time you can optimize things. 09:01.680 --> 09:08.080 So if you start to realize well this one thing is becoming kind of slow this query I'm writing or whatever 09:08.080 --> 09:14.700 it is maybe it makes sense for me to make this a fixed length and convert it to a char. 09:14.710 --> 09:16.330 OK so that's the difference. 09:16.330 --> 09:19.770 On paper you can go ahead skip the next video. 09:19.810 --> 09:22.940 I'm just going to show you in practice what it looks like. 09:23.110 --> 09:28.900 So I'm going to hop over to cloud nine and first thing I'll say I'm working on just a new database I 09:28.900 --> 09:32.460 made basically just a testing database. 09:32.470 --> 09:38.480 I didn't want to muddy the waters with our Instead of our book shop database I want to leave it alone. 09:38.620 --> 09:41.900 So I just made one called new testing DB. 09:42.040 --> 09:44.540 If you want to follow along just make a new database. 09:44.800 --> 09:46.860 Hopefully remember how to do that. 09:46.870 --> 09:47.710 Call it whatever you want. 09:47.710 --> 09:54.940 I'm just going to leave it at the end of this anyway so I'm going to create a table here and all I want 09:54.940 --> 10:00.400 to do is have two different columns one that a char one that's of our chart. 10:00.700 --> 10:09.850 We'll just do dogs that say create table dogs and we'll have a name which will be char a fixed length 10:10.390 --> 10:16.830 five characters and then we'll have breed which will be Vardar. 10:17.290 --> 10:21.800 And let's just say 10 there OK. 10:22.060 --> 10:27.490 Just like that and I'll go ahead and hit enter and I'm missing comma 10:30.890 --> 10:31.940 area. 10:32.600 --> 10:33.240 OK. 10:33.440 --> 10:35.160 So we have that Doug's table. 10:35.210 --> 10:37.700 Now let's insert some simple data. 10:37.700 --> 10:44.270 So the first thing that I'll do is insert into dogs and remember our syntax here spend a little while 10:44.270 --> 10:45.520 since we've started. 10:45.780 --> 10:52.040 We've been doing a lot of selects but we need to have our values and the values that will pass and we're 10:52.040 --> 10:56.970 going to start with something that is less than five characters. 10:56.990 --> 11:06.260 So for a name let's say Bob and breed will also be something short let's say. 11:06.690 --> 11:09.850 What is a short dog breed beagle. 11:10.010 --> 11:10.460 Yes. 11:10.520 --> 11:11.290 I don't know. 11:11.490 --> 11:16.080 OK get so do that and we'll do one more here. 11:16.400 --> 11:19.880 This time we'll have a name that's exactly five characters. 11:19.880 --> 11:26.840 Let's call it Rob be spelled that way apologize for an original names here. 11:26.850 --> 11:36.480 Just trying to work with the constraints and breed here will be a corgi. 11:37.440 --> 11:38.270 OK. 11:39.110 --> 11:42.570 And we'll do one more and this time we'll have a long name. 11:42.680 --> 11:57.010 How about Princess Jane and practice Jane is a man until that last year was another breed let's go with 11:57.280 --> 11:59.570 retriever. 11:59.670 --> 12:00.630 Is that right. 12:03.610 --> 12:04.720 If I remember. 12:04.740 --> 12:05.040 All right. 12:05.040 --> 12:07.190 Forgive me if it's spelled wrong. 12:08.930 --> 12:09.540 OK. 12:09.710 --> 12:11.630 So first thing first right off the bat. 12:11.790 --> 12:14.040 Notice we got a warning on this last one. 12:14.210 --> 12:15.670 Why do you think that happened. 12:16.280 --> 12:24.880 Well it's because Princess Jane exceeds the limit that we've set for our char which is five characters. 12:24.950 --> 12:28.970 So now if we do a simple select star from Dug's 12:32.020 --> 12:35.730 you'll see we have Bob ráby. 12:35.890 --> 12:42.360 They look good they look the same unchanged and Princess Jane unfortunately has been truncated. 12:42.550 --> 12:47.920 So unfortunately we can't actually tell that there are those trailing spaces because they are not actually 12:47.920 --> 12:52.000 here when the text is retrieved. 12:52.180 --> 12:58.690 Remember with that giant plugger tech said it said that they're stored with these two extra spaces in 12:58.690 --> 13:04.870 our case because this is three characters and we set up a five character fixed char so they're in memory 13:04.870 --> 13:06.380 are going to be two spaces here. 13:06.430 --> 13:11.070 But when it's pulled back out and displayed to us they're chopped off. 13:11.170 --> 13:17.620 Robbie is unchanged because Robbie has five characters long princess Jane unfortunately is cut off. 13:17.620 --> 13:24.400 However when we look over here beagle corgi and Retriever Vardar works how you expect it's a variable 13:24.400 --> 13:29.600 character like let's just do one more example here with Princess Jane. 13:29.820 --> 13:31.570 Yeah we'll just have another princess Jane. 13:31.750 --> 13:38.180 And the long retriever blah blah blah blah blah. 13:38.220 --> 13:40.560 Now I want to do a show like Star. 13:40.560 --> 13:42.260 Notice again what happened. 13:42.480 --> 13:44.180 This is what we expect to happen. 13:44.180 --> 13:47.190 We've already seen this how our chart works. 13:47.190 --> 13:50.580 It still has a maximum length that we can provide in this case. 13:50.580 --> 13:55.440 We did 10 I believe yes. 13:55.540 --> 13:59.450 So it can work similarly to char and that's it. 13:59.470 --> 14:01.360 In that sense where it will truncate things. 14:01.570 --> 14:08.530 So just to wrap up the entier char again is faster for fixed length text so if you know something is 14:08.620 --> 14:11.320 always going to be the same size use char. 14:11.470 --> 14:14.580 However if you have any doubt use of our char. 14:14.980 --> 14:19.660 And again the difference is relatively insignificant for most things. 14:20.050 --> 14:24.290 I don't want to be presumptuous but most of the time it's not going to be a deal breaker. 14:24.440 --> 14:28.290 OK so moving on we're not going to discuss numbers in the next video. 14:28.360 --> 14:28.740 This video.