How to Create a Database in MySQL

How to Create a Database in MySQL Data is a small unit of information. That information can be anything like text, image, name, age, class etc. ‘Data’ word is taken from the word ‘datum’ that means ‘single piece of information.’ We can store data in the paper, hard disk, server and much more. Data is not in a structured form.

Example: Name, is, my, Arun.
Above Example, all information is a single piece of data. That is called Data.

Nowadays almost everything comes on the internet. It’s very difficult to handle too much data with accuration. For handling too much data with accurate, Database content came in the Market.

What is Database

The DataBase is a Collection of Raw Data. We can store data in the form of Tables, index, columns and much more. The database helps to store the data in a structured form (Tables). So it will easy to do major operations like modification, insertion, retrieving and much more.

Before Database, File System is used for storing and manipulate the data. File System has many advantages and disadvantages.

Some Disadvantages of File System

1.) Lack of security.
2.) Difficult to retrieve the data.

Nowadays we can do shopping, chatting with friends, using mail, run dynamic websites using the Internet. This is possible because of the Database.

There is some real-life example where we use Database to store information.

1.) Shopping websites like Amazon and Flipkart. In these websites each and every information like email id, address, user name etc. stored in Database. we can open anywhere amazon account with the help of the Internet.

2.) Online Hotel Booking sites are the best example of using Database. Hotel Owners upload all necessary details like name, room, location on Database.

These are many databases available in the market like MySql, DB2, SQL Server, Mongo DB, DB2, Oracle, Informix etc. Every database has the same work to store the data. 

SQL (Structured Query Language)  is used to operate data to stored in a database.

MySql Create Database

To Create a Database (DB) in MySql, we will use the following commands to create MySQL Database.

MySql Create Database Syntax  

CREATE DATABASE [DATABASE_NAME]
CHARACTER SET [CHARSET_NAME]
COLLATE [COLLATION_NAME]

Note: In the above Syntax brackets ” [] “ indicates, we can write any name here.

Database name should be unique or new. It must not exist in the Database. MySql throws the following error when we try to create a database with the same name.

CREATE DATABASE test_db;

Create Database command return the successful message “Table created successfully”. In Xampp PhpMyAdmin, we can see created database in the left sidebar.

In MySql Database, we store our data in the tables. Tables will create under particular Database. In Our case, we have created Database with the name “test_db”. Now we will create a new table under “test_db” Database.

MySQL Create Table

MySQL Create Table Command is used to create Table inside Database. The table contains Rows and Columns. Table Creation command required the following fields.

1.) Name of the Table
2.) Name of the given Fields
3.) Each Field Definition

MySQL Create Table Syntax

CREATE TABLE Table_Name (column_name1 column_type,column_name2 column_type..);  

Now we are going to create table “MyFirst_Table” in the Database “test_db”.Follwoing commands is used of create Table in Database.

CREATE TABLE MyFirst_Table (  
   MyFirst_Table_id INT NOT NULL AUTO_INCREMENT,  
   MyFirst_Table_firstname VARCHAR(200) NOT NULL,  
   MyFirst_Table_surname VARCHAR(200) NOT NULL,
   MyFirst_Table_fname VARCHAR(200) NOT NULL,  
   PRIMARY KEY ( MyFirst_Table_id )  
); 

• In the above command “MyFirst_Table” is the table name.
• INT indicates Datatype of field (Column) MyFirst_Table_id.
• NOT NULL indicates , we cannot leave this field as blank. We have to insert value in it.
• VARCHAR (200) indicates Datatype , we can insert any type of value like Integer, string etc.
• AUTO_INCREMENT indicates , Every time while inserting row it’s value increment by 1.
• PRIMARY KEY ( MyFirst_Table_id ) indicates, “MyFirst_Table_id” will be unique and not null.
  

The table has successfully Created with Zero (0) rows.

MySQL Insert into Table

MySql Insert into the query is used to insert data into the table.We can easily insert single or multiple records using a single query in MySQL.

Insert Command Syntax in MySQL

1.) INSERT INTO table_name (field1, field2, field3,....fieldN) VALUES (value1, value2,value3,...valueN );

2.) INSERT INTO table_name VALUES (value1, value2,value3,...valueN );

First syntax used when we want to insert in some fields.

Examples: 

INSERT INTO MyFirst_Table( MyFirst_Table_firstname,MyFirst_Table_surname,MyFirst_Table_fname ) VALUES ('Desy', 'kumar', 'Johny');  

Second Syntax is used , when we want to insert in all fields.

INSERT INTO MyFirst_Table VALUES ( 1,'Desy', 'kumar', 'Johny');

MySQL Select Query

MySQL select query is used to show or retrive the data of particular table. In MySQL, we have simple command for showing the table records.

Select Query Syntax in MySQL

1.) select [field1,field2,...] from table_name;

2.) select * from table_name;

Syntax 1: We can fetch detail of particular column.

Select MyFirst_Table_id,MyFirst_Table_firstname from MyFirst_Table;

Syntax 2: This command gives full records of particular table.

select * from MyFirst_Table;

More Related Post

• SQL Add column
• MYSQL Drop table
• DELETE Column sql
• Drop Database MYSQL
• MYSQL Update Statement

Thanks for the reading post. I hope you like and understand the post. If you have any doubt regarding this post please comment below.