Basics of DBMS

 In this part we are going to learn some basics of Database Management System. We will learn how to change the database, how to create tables, how to delete the whole table and much more. After this you can do make simple tables and use it accordingly.

How to create a table ?

Before creating a table let's understand some important points.

Tables are stored inside a particular database i.e, if you create a table inside a database 'D1' then the table exists with the database 'D1' only, we cannot access it outside of that database because outside of 'D1' it doesn't exist. You can understand this using the chart given below.

So before creating a table we need to choose in which database we want the table to be created. We can also create our own database. 

To see all the database which exists, we use the command

mysql> show databases;

To create a database, we use the command

mysql> create database database_name;

To use/select the database, we use the command

mysql> use database_name;

To delete the database, we use the command 

mysql> drop database database_name;

Now after learning how to select the database, now we will see how to create a table.

Syntax to Create a Table -

mysql> create table table_name( 
->Variable_1 datatype(size),
->Variable_2 datatype(size),
->Variable_3 datatype(size),
...................
...................
->Variable_n datatype(size));

Let's understand some of the datatypes we usually use in a table.

char- It is a fixed datatype that stores string value.

varchar- It is a dynamic datatype that stores string value.

int- It is used to store integer type value.

double- It is used to store the double type value

date- It is used to store the date. The input format for 'date' is YYYY-MM-DD.

Let's take an example to understand it better. Now we will create a table employee consists of 'Id', 'Name', 'City', 'Salary', 'Date_of_Joining'.

mysql> create table employee( 
Id int(3),
Name varchar(20),
City char(20),
Salary int(10),
Date_of_Joining date
);

Now a table named "employee" has been created. 

 

Insert Values in a table

 Now after creating a table, we will insert values inside the table. In all the below examples we are going to use the above table "employee"

To insert the values in a table

mysql> insert into employee values(1,'Rahul Prajapat','Madhya Pradesh', 
'60000','2019-08-02');

mysql> insert into employee values(2,'Nikhilesh Gupta','Jharkhand', 
'62000','2018-07-20');

 In the above commands we enter the data of a single employee at a time but we can also add multiple data at a single command.

mysql> insert into employee values(3,'Prajjawal Pandit','Gujarat',65000,
'2019-08-02'),(4,'Eesha Srivastava','Mumbai',63000,'2019-10-14'),(5, 
'Saket Shandilya','Jharkhand','70000','2020-06-12'),(6,'Priyanka Shaw', 
'Kolkata','60000','2019-09-11');

Here we add four employee data at the same time.

Now if we want to enter only some particular data of an employee then we can also do it. To do this we have to specify the columns name after the table name and enter the values according to the column name we have written above.

mysql> insert into employee(Id,Name,Salary) values(7,'Satyam Raj', 
'72000');

So here we only entered the values of 'Id', 'Name', 'Salary'. So the other columns will be NULL.

Select Statement

To retrieve or get information from the table we use the "Select" Statement. In all the below examples we are going to use the above table "employee".

To retrieve all information from a table

mysql> select * from employee;

To retrieve a specific column from a table

mysql> select Name from employee;

Instead of 'Name' you can choose any column.

To retrieve more than one column from a table

mysql> select Id,Name,Salary from employee;

If you want to see all the tables available in that database, we write a command-

mysql> show tables;

Now at last if you want to delete a table you need to write the following command.

mysql> drop table table_name;

And in this case we write-

mysql> drop table employee;

Those are some basic knowledge you need to start with MYSQL.

For giving some suggestion please comment us or check our "Contact us" page. Also more update related to coding will be added in this Blog later on, so please get attached with this Blog by clicking the follow button. Thank you for your support and time...