 Hey everybody, this is Brian. Welcome to the 47th LAMP tutorial. Today we're going to be discussing SQL types. Now if you go out to W3 schools or whatever you've been using as a reference and you look up SQL, you'll see just look on the left here, there is a lot to the language and SQL actually stands for structure query language and there's a lot to it. So you take into account just the language, I mean the sheer magnitude along with the fact that there are multiple types of databases. There's Oracle, MySQL, Microsoft, I mean all flavors of Microsoft and each one's a little bit different. As you can see here in SQL types, you've got an access type, a MySQL type and you can scroll down, there's probably more, a SQL server type. I mean so it's no wonder the databases is a pretty complex topic. Now when I started this whole series I said this series would probably be criticized as an inch deep and a mile wide and this is probably going to be one of those sections where we're just going to focus in on MySQL and we're just going to focus in on what you need to survive in database land. So this video is going to be covering the different types. Remember how we talked about the different types of data that can go into a column? Remember you have a database and then inside the database you have multiple tables and in each table you have columns. A column is a piece of data. A row would be multiple columns strung together to form one item. For example let's say you are making a database of customers. One row may include the first name, last name, address, phone number, etc. But a column would be just the first name. The table may be named customers and the database might be named MyStore and you may have multiple tables such as customers, products, orders, etc. So databases get fairly complex. We're going to try and really dumb this section down. Not that I think you're dumb, not by any means. If you're watching these tutorials, well you're probably about 1% of the population because most people don't get very far into computers at all. So let's see. MySQL data types. You have a care with size. Remember in our last tutorial we talked about the parentheses you had to include a size? Well you can hold up to a specific size in a column and it does this with the way the data is structured and the way it's stored on the physical disk. The SQL engine if you will, whether it's SQL Server, Oracle, or MySQL needs to know how much size or how much data you're going to put in a specific column. So along with the type it expects to know a size, how much? So we have care and size. Now what's a care? Holds a fixed link string can contain letters, numbers, and special characters. The fixed size is specified in parentheses. Constructed 255 characters. Well a letter on the screen is a character, you know the period is a character, things of that nature. All right, fair care, size. Well holds a variable link string can contain letters, numbers, and special characters. The maximum size is specified in parentheses can store up to 255 characters. Note if you put a value greater than 255 characters it'll be converted to the text type. Now what's the difference between a care and a fair care? Notice how they're two separate things, but they sound strikingly similar. Well, tiny text holds a string with a maximum length of 255. You notice how each one of these is a string and you include text in there with up to a maximum of 255. With the exception of text is holds a string with a maximum length of 65,000 characters. So strings can get massive, they can get small. Really what it boils down to is the difference between a care and a fair care is how it is stored on the desk. It's very simple, very easy. Like if you jump down to, and I know we're kind of going out of here, but you have a fair care, and this is Microsoft SQL by the way, fair care and end fair care. End fair care is Unicode where fair care is just ASCII. So it gets into how the data is stored. And I think with MySQL everything is Unicode by default, I could be wrong. Anyways, then you have blob. That's an interesting name blob for blobs or binary large objects. What would be a binary large object up to 65? 65k basically. Well, think of like a picture. You could actually store a picture, the actual raw data of a picture in a database. Why would you want to do something like that? Well, think about the context of our database being a store. Well, you could store the product picture in a record. Medium text holds a string with a maximum length of, wow, look at that, 16 million. Medium blob, 16 million. So you can get more and more bang for your buck, but you're also chunking out more and more sections of your hard drive. Long text, that's a big number. Look at that. Long blob, same thing. And then a num. Let's you enter a list of possible values in a num list. If a value is inserted, it's not in the list, blank will be entered. You enter a possible values in this format and num x, y, z. Meaning it would have to be x, y, or z. Well, think of the context of a store, small, medium, large, red, blue, green, something along that nature. And then set, similar to a num, except this set may contain up to 64 list items that can be stored more than one choice. Then you have number types. Notice the different amount of numbers you have here. You have tiny int, which is basically a byte or an unsigned byte. And then a small int, medium int, integer, that's a big number. Big int, that's a massive number. Floating points, if you want floating point precision, doubles. These would be like, if you wanted like currency kind of thing, you know, like $1.55. And then decimal, pretty much the same thing. Decimal stored as a string. That's the main difference here. Or I'm sorry, a double is stored as a string. That makes no sense. I'm pretty sure decimal stored as a string. Anyways, my cat's trying to break out. Hold on. Sorry about that. I had to pause the video. The cat's driving me crazy. I put up curtains. He's trying to see if he can knock them over and take my windows down with them. All right. Now you have your dates. You have just a date, which is normal, you know, year, month, day, date, time, which is the same thing, but it also stores the hours, minutes, and seconds. Time stamp. You remember from our previous conversation, stores the Unix epoch, time, and hour, minute, second, and the two digit year. Wow. That's a lot of types. Now what do you need all these types for? Remember, databases store and represent data. So you have to figure out what type of date you have. For example, if you're storing the age of a person, would it be a character or could you use a tiny int? I doubt many people live over 255, so you could use a tiny int. But if you're using a first name, how many people's first names really take up 65,000 characters? Not too many people. I'm guessing you could probably get a get away with probably a fair care 50, something along those lines. So that's really all there is. It may look big and confusing, but really, all you got to remember is, hey, what type of data am I storing? Am I storing a text type, a number type, or a data type? And along with text types, you also have blobs if you want to store binary information, such as a picture. Well, that's all for this tutorial. Next couple tutorials, we're going to dive right in and actually create some databases. Hope to see you there.