Creating database, tables and inserting data

In the previous post, we installed the MySQL command line on our windows machine. Now, we will see how we can create databases, tables and insert the data into tables. But first, we will see what exactly databases and tables are?

What is a database?

As you know the database is a collection of tables that are linked to each other. We store the data in these tables. We can perform various operations related to data, databases, and tables using SQL. These databases can store a large amount of information. Some of the commonly used DBMS(Database Management Systems) are MySQL, Oracle, MongoDB, PostgreSQL, SQL Server, etc. These DBMS are used to store databases and provide a User interface or Command line interface to handle data.

What is a table?

In a database, we use tables to hold the relational data. These tables consist of rows and columns. Rows hold the one record/tuple of data. Whereas the column represents the trait. Suppose in one database following student table is present. The first row is the row of attributes related to each student. After that, you will be able to see each row that defines one record of a particular student. So in the following table, we can say there is information related to three students.

RollNoFirstNameLastNameAge
1JohnDoe16
2RaimonAlekos15
3PeneTeresa16

If the concept related to database and table is clear we can see how we can create a database, table, and insert records in a table.

MySQL Datatypes

MySQL data types are given to each column in a table. They define what kind of data, columns can hold. In the above Students table, there is a column as Roll No. Its data type in the database should be int since it represents numeric values i.e. Roll numbers of students. In MySQL, there are three main types those are numeric, string, and date and time.

Numeric Data Types

Data TypeDescription
TINYINT(size)It can hold a value of a very small integer. In the size parameter, you can pass the maximum display width i.e. 255. It can store 1 byte of data.
SMALLINT(size)In the size parameter, you can pass the maximum display width as 255. It can store 2 bytes of data.
INT(size)This is a medium integer. You can pass the maximum display width as 255. It stores the data of 4 bytes.
BIGINT(size)This is a large integer type. You can pass the maximum display width as 255. It stores the 8 bytes of data.
DOUBLE(size, d)This is a normal size floating-point number. We can specify the size as the total number of digits and can specify the number of digits after the decimal point in d.
DECIMAL(size, d)As DOUBLE we can specify the size as digits and the total number digits after the decimal point can be specified in d. 65 is the maximum number for size and 30 is the maximum size of d.

String Data Types

Data TypeDescription
CHAR(size)This is fixed to the length of the size string. It can contain letters, numbers, and special characters. The size parameter is the column length in characters. It can be from 0 – 255.
VARCHAR(size)The string can contain letters, numbers, and special characters. The size can be from 0 – 65535.
BLOB(size)BLOB is Binary Large Objects. This can hold a variable amount of data. It can hold up to 65,535 bytes of data. BLOB value is treated as a binary value.
TEXT(size)TEXT value is treated as nonbinary strings. It can hold a string with a maximum length of 65,535 bytes.

Date and Time Data Types

Data TypeDescription
DATEThis type only consists of the date part no time part. Its format is ‘YYYY-MM-DD’. The supported range of this type is from ‘1000-01-01’ to ‘9999-12-31’.
DATETIMEIt is used for the values which consist of both date and time. The format is ‘YYYY-MM-DD hh:mm:ss’. The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
TIMESTAMPThis is also used to store the values which consist of date and time. It has a range of ‘1970-01-01 00:00:01’ UTC – ‘2038-01-09 03:14:07’ UTC.

For more details on Data Types in SQL, you can see this official site here.

In the following video, you will be able to see how to create a database, table, and how to insert data.

Query Recap

To show the database.

show databases;

To show the tables.

show tables;

To create a table with the mentioned table name.

create table table_name(column1 datatype1, column2 datatype2,.....column3 datatype3);

Insert values in the mentioned table(Column having string datatype should be placed in double-quotes).

insert into table_name values(column1_value, column2_value,.....);

To get all the data from the mentioned table.

select * from table_name;

Practise Section

Database Name:- School Management System, Table Name:- Students

Roll No.First NameLast NameAgePercentage
1PhillipBurns1589.00
2GretchenHoffman1680.88
3NatashaChambers1579.90
4DarrenRobertson1587.09
5OllieLloyd1675.88

Database Name:- Country Information, Table Name:- Country

Country NameCapitalCountry CodeISO Codes
IndiaDelhi91IN/IND
United StatesWashington1US/USA
VietnamHanoi84VN/VNM
ThailandBangkok66TH/THA
MyanmarNay pyi taw95MM/MMR

1 thought on “Creating database, tables and inserting data”

  1. May I simply just say what a relief to find someone who truly understands what they are discussing on the web. You definitely understand how to bring a problem to light and make it important. More and more people ought to check this out and understand this side of your story. Its surprising you arent more popular because you definitely have the gift.

Leave a Comment

Your email address will not be published. Required fields are marked *