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.
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 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
|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
|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
|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.
To show the database.
To show the 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;
Database Name:- School Management System, Table Name:- Students
|Roll No.||First Name||Last Name||Age||Percentage|
Database Name:- Country Information, Table Name:- Country
|Country Name||Capital||Country Code||ISO Codes|
|Myanmar||Nay pyi taw||95||MM/MMR|