So the next thing we need to discuss around tables are data types.
So data types are really, really important.
And to show you what I mean and first of all, what I'm talking about when I say data types, let's
return back to our catch table.
So we have these three headers, three columns, name, breed and age.
And here's an example.
I change some of the data just a little bit.
So the names are the same, the breeds are the same.
But if you look over it, age blue is one year old Rocket is three, but Monty now is ten.
The word ten and Sam is crazy.
Sam's age is I am young cat.
So what I'm trying to illustrate here is that we have inconsistent data.
This is all text and text, but here we've got numbers mixed with text and that is not good and it's
actually not allowed in SQL at all.
When you create a new table, you actually have to specify name has to be text, breed has to be text,
age has to be a number and there are a whole bunch of different data types.
It's not just text and number.
In fact, those aren't even data types.
Those are just what I'm calling them, the English version.
But you'll see them in just a second.
But to illustrate why it's so important that your data matches that you don't have this inconsistent
data and why SQL forces you to do this.
Here's a little example.
So let's say that you have this data in your database in a cat's table where hypothetically in a fictional
world, you can have data that isn't all matching.
So we have this numeric data mixed with text under age.
So let's say I wanted to make a really, really simple app that would take each cat's age in the database
and then print out the converted or equivalent cat age.
So from human years to cat ears, basically, which is sort of interesting actually in making these
slides, I learned quite a bit about how you calculate cat age, because for dogs there's this colloquialism,
you can just multiply it by seven.
For cats it's a little more complex.
Apparently it's not linear.
So if you look at this scale, when a cat is two months old, it's three years and human years.
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.
So I just decided, you know what, let's just go with multiplying by seven.
It really doesn't matter.
So our app takes the age, a blue multiplies by seven.
No problem.
Our app takes three, multiplies that by seven, no problem.
But it has no idea what to do here.
Our code doesn't expect text and it can't do that.
The math and same thing with I am young cat time seven.
It doesn't even know what to do.
It just freaks out.
So really trivial.
Stupid example.
I know.
But the idea here is important that we have to have these data types established and that my SQL will
enforce them.
So when we create tables, we have to say exactly what data type we're looking for because this type
of situation is really, really not good, also known as bad.
So returning to our cat's table when we're creating it, we'll have to say this must be text breed must
be text.
An age must be a number.
So in reality there are a lot of different MySQL data types.
Like I said, text and number are not real data types.
In fact, there are a ton in my opinion, too many and it gets pretty confusing, especially when you're
just starting out.
It can be intimidating just to show you exactly what I'm talking about.
I put together a list of all the different data types, so all the numeric types, basically the different
ways of representing numbers.
We've got ints small ints tiny ints medium int big int decimal numeric float double bit, then under
string types.
So these are ways of representing text basically.
Cha cha cha binary.
Var binary blob tiny blob medium blob long blob text, tiny text, medium text, long text.
And then enum.
And then finally we've got date type.
So different ways of representing dates.
We've got date, date, time, time, stamp time and year.
So, yeah, it's kind of crazy.
I know.
Fortunately, you don't have to know all of those at any given time.
Absolutely not.
I had to spend a decent amount of time even digging through the documentation just to find all of these,
because the reality is you don't use them most of the time or you don't use all of them.
You figure out which ones you need depending on a given situation.
You use a subset normally that you're comfortable with, and then eventually you may have to switch
things out and change from using a medium int to a big enter, actually more realistically an int to
a big int or something like that.
But we'll get there for now.
We're going to accept that it's crazy and we're just going to whittle it down and focus on two.
The first one is int for our numeric type and then var char for our string type.
So to represent numbers we're working with int and then to represent text and strings we'll work with
var char.
So we'll start off with int.
So int represents a whole number, so we can't use it for decimals.
And it ranges up to this gigantic number here.
Basically, it's big.
However, if you need to store numbers larger than that, that's when you can start looking into some
of these other data types.
But like Big End.
But we're not going to worry about that because for now we're storing things like cat age.
So this is an int.
Here is another int.
It can be negative or zero or big.
Or 42.
So that's pretty much it to int.
Then var char is a way of representing text or strings and it's variable length.
So you may have noticed that there was cha on there and var cha.
Well, cha is a fixed length.
So that means that everything in that column has to be the same length.
It has to be ten characters or something.
But var CHA allows to have variation in that, which is what we want most of the time.
And it's between one and 255 characters.
So things like coffee or -9999, the string in quotes.
So it's not a number or this, whatever this is or capital L or a sentence, as long as it's fewer than
255 characters.
So returning to our cat's table, we would say that name, which must be text, would be var cha and
notice that I have these parentheses here and a number inside.
So when you use var cha, you have to specify a maximum length.
So in my case I chose 100.
I could go up to 255, but I just don't want to have any giant names.
But that's all that it does here.
So it specifies where the cutoff point is.
And if I go beyond 100 characters, if I write 300 or 200 characters, all that will be stored is from
the first character to the 100th character.
So read I did the same thing.
It's Var Cha and I did 100 again.
But that could change if we wanted them.
If we wanted to restrict breeds to be shorter, we could change it to be 50 characters and then for
age, which must be a number we just use.
INT We're just working with whole numbers we're not going to worry about.
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,
basically high level overview and we saw two of them in particular var, CHA and INT.
Next up we have a really quick activity.