Data Types & Data Modelling In MySQL - MySQL Series Part 2

sohail -

In this article, we will be learning about the various data types in MySQL and also how data modeling is done. I am assuming you have a working instance of MySQL on your computer. If not, you can read the step by step installation instructions.

Data Types in MySQL

MySQL is very rich when it comes to data types. There are a wide variety of options available to efficiently store and retrieve data. The following data types are most commonly available.
​

  • INT– For storing integer values.
  • CHAR– For Storing fixed-length character values.
  • VARCHAR– For Storing character values with variable lengths.
  • DATETIME– For Storing Date and Time Information.
  • TIMESTAMPS– For Storing timestamps of various actions.
  • TEXT– For storing large amounts of text data.
  • ENUM– For storing one of the preset values.
  • BOOLEAN– For storing a single Boolean value.
  • BLOB– For storing binary files and objects.

Data Modelling in MySQL

MySQL has a certain schema when it comes to storing data. MySQL is based on the concepts of Tables, Columns & Rows. First of all, we need to understand that in MySQL data is usually stored in isolated environments called Databases that are isolated from other databases.  

Database

A Database is a topmost entity in MySQL. A Database contains tables. These tables can communicate with each other. However, a table from one database cannot communicate with a table from another database.

Tables & Columns

Tables form the containers for related data. Tables can be considered as collections of data that can be grouped together. For example, a table named student could contain data such as name, date of birth, etc. However, it may not be good to include data such as pin code, landmark, house no and other things like these. It would make a lot more sense to make a new table for such data. Each data element will then be called a column and a group of such columns form a table. Lastly, there cannot be 2 tables with the same name in a database.

Rows

Rows contain the actual data in your database. If you had a student table with different types of student data, you would have many rows in that table. Each row would be a student’s entry into that table. The overall schema of a table is very much like an excel sheet. You can see in the image below that there are columns such as RollNo, Name, Birth Date and they have multiple records. The MySQL data is structured exactly like this.

Creating a Database

Enough of theory, now it is time for some action. Let us quickly see some operations we can do with databases in mysql. Quickly log in to your mysql command line client.
​
Firstly we will run a simple command to see how many databases do we have right now. Type in show databases; in the SQL command window.  It should give you similar output.

MySQL has listed out all currently existing databases. Do not touch any of them. Deleting or modifying any of these existing databases may lead to a broken mysql.
​
Now we will create a database. To do that type in create database theitstuff; You can replace theitstuff with any name of your choice. And then run show tables; again.

You can see that we have created a new database and it is visible in the database listing above.

​Now, to delete this database we will run drop database theitstuff;

The database that we had created has now been deleted.

Conclusion

MySQL has a lot of data types that can be used to store various types of data in databases. MySQL structures data into tables that consist of columns related to each other. Each new row in such a table is called a record or entry into the table.2 tables from a database can communicate with each other but 2 tables from 2 different databases cannot.​I hope you have a basic idea of how data is structured in MySQL databases. If you have any doubt regarding this topic, do let me know in the comment section below.