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.

RollNo	FirstName	LastName	Age
1	John	Doe	16
2	Raimon	Alekos	15
3	Pene	Teresa	16

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 Type	Description
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 Type	Description
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 Type	Description
DATE	This 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’.
DATETIME	It 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’.
TIMESTAMP	This 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 Name	Last Name	Age	Percentage
1	Phillip	Burns	15	89.00
2	Gretchen	Hoffman	16	80.88
3	Natasha	Chambers	15	79.90
4	Darren	Robertson	15	87.09
5	Ollie	Lloyd	16	75.88

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

Country Name	Capital	Country Code	ISO Codes
India	Delhi	91	IN/IND
United States	Washington	1	US/USA
Vietnam	Hanoi	84	VN/VNM
Thailand	Bangkok	66	TH/THA
Myanmar	Nay pyi taw	95	MM/MMR

• Instagram
• Facebook
• Twitter