How to create DataBase and Table in MySQL

This is the first of our Learn MySQL Tutorial. In this post we shall learn how to create and use Databases and tables in MySQL using MySQL terminal. In case you don’t know about running MySQL terminal, check this post to learn.

Databases are main objects in MySQL. Every data is stored inside Databases. But it can not be stored directly inside it! Tables are used to store Related Data inside a Database. So basically, whenever we wish to store some data, then we need to create a Database and then a Table, and split that data into parts and store inside separate columns of the Table. A MySQL server can contain infinite amount of Databases, and a database can contain as many Tables you want.

Let us see, how to create the Database and tables…

Analyzing the Data we want to store:

In this tutorial, we are assuming that you want to store the following data:

• First name, Last name, email and mobile of every client you have got through your website.

For this we are going to use one table, client_info to store our data.

Creating the DataBase:

We are going to name the database as “my_clients”. Now follow the steps to CREATE and USE it:

• Launch your MySQL terminal;
• Now type the following command and hit enter…
  CREATE DATABASE my_clients;
• You have now successfully created the database. So, the basic command of creating a Database is:
  CREATE DATABASE db_name;
• Now to use the database for the successive operation, we need to execute the following command:
  USE my_clients;
• Basically to use any database you just need to type
  USE db_name;

  

Note that each MySQL command must end with a semi-colon(;) and then you need to type enter to execute it.

Creating the Table:

As said before, we need to create two tables for now. Also we need to decide the column names for each of the tables. Basically each row in a MySQL table stores a unique set of data. consider the following table:

first_name	last_name	email	phone_num
Name1	Surname1	email1	phone number1
Name2	Surname2	email2	phone number2

MySQL databases contains these types of table where each row contains a specific data, separated in different types by columns. Now we are to create this table inside the database we have made…

• Make sure you are using the database you have just created.
• Now just enter the following command
  CREATE TABLE client_info
  (
  first_name VARCHAR(60),
  last_name VARCHAR(60),
  email VARCHAR(60),
  phone_num INT
  )
  ;
• Here we have used multiple lines to enter a single command. You need to enter it like this in your MySQL terminal.
• So, the basic command for creating a Table is:
  CREATE TABLE client_info
  (
  column_name1 column_type1,
  column_name2 column_type2,
  … …,
  column_nameN column_typeN
  )
  ;

DATATYPES:

Note we have used some parameters like VARCHAR, INT etc. These are datatypes. We need to define the types of data in every column. VARCHAR(character_length) means a string of variable character having character range in between 0 and character_length. Obviously INT means integer only. You should now be getting why we have used VARCHAR(60) in first_name, last_name etc and INT in phone_num. More on that coming on our next tutorial. Also we shall see how to insert data inside a MySQL table on our next post.

---

So that was the first basic tutorial. I hope it was easy for… More complex stuffs coming on this series very soon… Read each of them to learn the basics and advance usage of MySQL. Then we shall see how to use them using PHP.