1
00:00:00,090 --> 00:00:04,170
So the next thing we need to discuss around tables are data types.

2
00:00:04,590 --> 00:00:07,080
So data types are really, really important.

3
00:00:07,080 --> 00:00:11,700
And to show you what I mean and first of all, what I'm talking about when I say data types, let's

4
00:00:11,700 --> 00:00:13,980
return back to our catch table.

5
00:00:14,820 --> 00:00:19,110
So we have these three headers, three columns, name, breed and age.

6
00:00:19,950 --> 00:00:21,300
And here's an example.

7
00:00:21,300 --> 00:00:23,130
I change some of the data just a little bit.

8
00:00:23,130 --> 00:00:25,530
So the names are the same, the breeds are the same.

9
00:00:25,530 --> 00:00:33,270
But if you look over it, age blue is one year old Rocket is three, but Monty now is ten.

10
00:00:33,600 --> 00:00:37,260
The word ten and Sam is crazy.

11
00:00:37,260 --> 00:00:40,050
Sam's age is I am young cat.

12
00:00:40,050 --> 00:00:44,040
So what I'm trying to illustrate here is that we have inconsistent data.

13
00:00:44,640 --> 00:00:50,580
This is all text and text, but here we've got numbers mixed with text and that is not good and it's

14
00:00:50,580 --> 00:00:52,710
actually not allowed in SQL at all.

15
00:00:52,710 --> 00:01:01,050
When you create a new table, you actually have to specify name has to be text, breed has to be text,

16
00:01:01,050 --> 00:01:05,880
age has to be a number and there are a whole bunch of different data types.

17
00:01:06,090 --> 00:01:08,160
It's not just text and number.

18
00:01:08,160 --> 00:01:09,990
In fact, those aren't even data types.

19
00:01:09,990 --> 00:01:12,420
Those are just what I'm calling them, the English version.

20
00:01:12,420 --> 00:01:14,040
But you'll see them in just a second.

21
00:01:14,550 --> 00:01:19,980
But to illustrate why it's so important that your data matches that you don't have this inconsistent

22
00:01:19,980 --> 00:01:22,740
data and why SQL forces you to do this.

23
00:01:23,400 --> 00:01:24,900
Here's a little example.

24
00:01:25,590 --> 00:01:34,380
So let's say that you have this data in your database in a cat's table where hypothetically in a fictional

25
00:01:34,380 --> 00:01:37,320
world, you can have data that isn't all matching.

26
00:01:37,320 --> 00:01:42,570
So we have this numeric data mixed with text under age.

27
00:01:42,570 --> 00:01:48,090
So let's say I wanted to make a really, really simple app that would take each cat's age in the database

28
00:01:48,090 --> 00:01:53,460
and then print out the converted or equivalent cat age.

29
00:01:53,460 --> 00:01:59,130
So from human years to cat ears, basically, which is sort of interesting actually in making these

30
00:01:59,130 --> 00:02:06,630
slides, I learned quite a bit about how you calculate cat age, because for dogs there's this colloquialism,

31
00:02:06,630 --> 00:02:08,220
you can just multiply it by seven.

32
00:02:08,400 --> 00:02:10,440
For cats it's a little more complex.

33
00:02:11,310 --> 00:02:13,560
Apparently it's not linear.

34
00:02:13,920 --> 00:02:19,530
So if you look at this scale, when a cat is two months old, it's three years and human years.

35
00:02:19,530 --> 00:02:26,040
But then as they get to two years old, it's suddenly 24 and when they're 14 at 72, so it's not consistent.

36
00:02:26,040 --> 00:02:30,060
So I just decided, you know what, let's just go with multiplying by seven.

37
00:02:30,090 --> 00:02:31,290
It really doesn't matter.

38
00:02:31,650 --> 00:02:35,340
So our app takes the age, a blue multiplies by seven.

39
00:02:35,340 --> 00:02:36,090
No problem.

40
00:02:36,270 --> 00:02:39,660
Our app takes three, multiplies that by seven, no problem.

41
00:02:40,140 --> 00:02:41,850
But it has no idea what to do here.

42
00:02:42,330 --> 00:02:45,470
Our code doesn't expect text and it can't do that.

43
00:02:45,510 --> 00:02:49,590
The math and same thing with I am young cat time seven.

44
00:02:50,010 --> 00:02:51,120
It doesn't even know what to do.

45
00:02:51,120 --> 00:02:52,170
It just freaks out.

46
00:02:52,350 --> 00:02:53,670
So really trivial.

47
00:02:53,670 --> 00:02:54,480
Stupid example.

48
00:02:54,480 --> 00:02:55,020
I know.

49
00:02:55,110 --> 00:03:01,110
But the idea here is important that we have to have these data types established and that my SQL will

50
00:03:01,110 --> 00:03:01,980
enforce them.

51
00:03:01,980 --> 00:03:08,850
So when we create tables, we have to say exactly what data type we're looking for because this type

52
00:03:08,850 --> 00:03:13,140
of situation is really, really not good, also known as bad.

53
00:03:13,590 --> 00:03:20,490
So returning to our cat's table when we're creating it, we'll have to say this must be text breed must

54
00:03:20,490 --> 00:03:21,120
be text.

55
00:03:21,120 --> 00:03:22,950
An age must be a number.

56
00:03:24,570 --> 00:03:28,470
So in reality there are a lot of different MySQL data types.

57
00:03:29,220 --> 00:03:32,810
Like I said, text and number are not real data types.

58
00:03:32,820 --> 00:03:38,880
In fact, there are a ton in my opinion, too many and it gets pretty confusing, especially when you're

59
00:03:38,880 --> 00:03:39,420
just starting out.

60
00:03:39,420 --> 00:03:43,140
It can be intimidating just to show you exactly what I'm talking about.

61
00:03:43,170 --> 00:03:49,140
I put together a list of all the different data types, so all the numeric types, basically the different

62
00:03:49,140 --> 00:03:50,550
ways of representing numbers.

63
00:03:50,820 --> 00:04:00,930
We've got ints small ints tiny ints medium int big int decimal numeric float double bit, then under

64
00:04:00,930 --> 00:04:01,860
string types.

65
00:04:01,860 --> 00:04:04,860
So these are ways of representing text basically.

66
00:04:05,430 --> 00:04:07,800
Cha cha cha binary.

67
00:04:07,800 --> 00:04:14,310
Var binary blob tiny blob medium blob long blob text, tiny text, medium text, long text.

68
00:04:14,790 --> 00:04:16,050
And then enum.

69
00:04:16,350 --> 00:04:18,300
And then finally we've got date type.

70
00:04:18,300 --> 00:04:20,190
So different ways of representing dates.

71
00:04:20,220 --> 00:04:24,510
We've got date, date, time, time, stamp time and year.

72
00:04:24,690 --> 00:04:27,780
So, yeah, it's kind of crazy.

73
00:04:27,780 --> 00:04:28,470
I know.

74
00:04:28,950 --> 00:04:32,780
Fortunately, you don't have to know all of those at any given time.

75
00:04:32,790 --> 00:04:33,780
Absolutely not.

76
00:04:34,020 --> 00:04:39,260
I had to spend a decent amount of time even digging through the documentation just to find all of these,

77
00:04:39,270 --> 00:04:43,620
because the reality is you don't use them most of the time or you don't use all of them.

78
00:04:44,760 --> 00:04:48,610
You figure out which ones you need depending on a given situation.

79
00:04:48,630 --> 00:04:54,090
You use a subset normally that you're comfortable with, and then eventually you may have to switch

80
00:04:54,090 --> 00:05:00,180
things out and change from using a medium int to a big enter, actually more realistically an int to

81
00:05:00,180 --> 00:05:01,680
a big int or something like that.

82
00:05:02,010 --> 00:05:04,170
But we'll get there for now.

83
00:05:04,680 --> 00:05:09,420
We're going to accept that it's crazy and we're just going to whittle it down and focus on two.

84
00:05:09,450 --> 00:05:14,850
The first one is int for our numeric type and then var char for our string type.

85
00:05:14,850 --> 00:05:20,220
So to represent numbers we're working with int and then to represent text and strings we'll work with

86
00:05:20,220 --> 00:05:20,940
var char.

87
00:05:21,660 --> 00:05:23,160
So we'll start off with int.

88
00:05:23,310 --> 00:05:27,000
So int represents a whole number, so we can't use it for decimals.

89
00:05:28,170 --> 00:05:32,250
And it ranges up to this gigantic number here.

90
00:05:32,310 --> 00:05:33,870
Basically, it's big.

91
00:05:34,380 --> 00:05:39,150
However, if you need to store numbers larger than that, that's when you can start looking into some

92
00:05:39,150 --> 00:05:40,050
of these other data types.

93
00:05:40,050 --> 00:05:41,670
But like Big End.

94
00:05:41,670 --> 00:05:46,620
But we're not going to worry about that because for now we're storing things like cat age.

95
00:05:47,430 --> 00:05:49,200
So this is an int.

96
00:05:49,200 --> 00:05:50,070
Here is another int.

97
00:05:50,070 --> 00:05:53,850
It can be negative or zero or big.

98
00:05:54,620 --> 00:05:55,640
Or 42.

99
00:05:55,820 --> 00:05:57,560
So that's pretty much it to int.

100
00:05:58,400 --> 00:06:04,640
Then var char is a way of representing text or strings and it's variable length.

101
00:06:04,640 --> 00:06:09,080
So you may have noticed that there was cha on there and var cha.

102
00:06:09,080 --> 00:06:11,600
Well, cha is a fixed length.

103
00:06:11,600 --> 00:06:15,560
So that means that everything in that column has to be the same length.

104
00:06:15,590 --> 00:06:17,630
It has to be ten characters or something.

105
00:06:17,630 --> 00:06:22,880
But var CHA allows to have variation in that, which is what we want most of the time.

106
00:06:23,660 --> 00:06:26,780
And it's between one and 255 characters.

107
00:06:27,740 --> 00:06:34,730
So things like coffee or -9999, the string in quotes.

108
00:06:35,360 --> 00:06:42,740
So it's not a number or this, whatever this is or capital L or a sentence, as long as it's fewer than

109
00:06:42,740 --> 00:06:44,510
255 characters.

110
00:06:44,510 --> 00:06:51,440
So returning to our cat's table, we would say that name, which must be text, would be var cha and

111
00:06:51,440 --> 00:06:54,800
notice that I have these parentheses here and a number inside.

112
00:06:54,800 --> 00:06:58,190
So when you use var cha, you have to specify a maximum length.

113
00:06:58,190 --> 00:07:00,470
So in my case I chose 100.

114
00:07:00,500 --> 00:07:04,790
I could go up to 255, but I just don't want to have any giant names.

115
00:07:05,180 --> 00:07:06,680
But that's all that it does here.

116
00:07:06,680 --> 00:07:09,560
So it specifies where the cutoff point is.

117
00:07:09,560 --> 00:07:15,620
And if I go beyond 100 characters, if I write 300 or 200 characters, all that will be stored is from

118
00:07:15,620 --> 00:07:17,690
the first character to the 100th character.

119
00:07:18,200 --> 00:07:20,720
So read I did the same thing.

120
00:07:21,090 --> 00:07:23,300
It's Var Cha and I did 100 again.

121
00:07:23,300 --> 00:07:25,760
But that could change if we wanted them.

122
00:07:25,760 --> 00:07:31,970
If we wanted to restrict breeds to be shorter, we could change it to be 50 characters and then for

123
00:07:31,970 --> 00:07:34,790
age, which must be a number we just use.

124
00:07:34,790 --> 00:07:37,880
INT We're just working with whole numbers we're not going to worry about.

125
00:07:38,270 --> 00:07:45,110
My cat is 1.2 years old or anything like that, so that's all we're going to do for now with data types,

126
00:07:45,110 --> 00:07:50,380
basically high level overview and we saw two of them in particular var, CHA and INT.

127
00:07:50,390 --> 00:07:52,220
Next up we have a really quick activity.