How to Create Database,Table In MySQL

Create Database,Table In Mysql | In this tutorial we learn how to create database in MySQL and how to create table in MySQL .

Create Database in MySQL

To create database in mysql we require special privileged.To create mysql database we are using root user and login the mysql server and execute the below create database in mysql command to create mysql database through mysql promt.

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.73 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database techrideradmin;

Query OK, 1 row affected (0.00 sec)

Create Database in MySQL

Now database has been created after executing the above command.

Create MySQL Database using phpMyAdmin

We can also create MySQL database using phpMyAdmin web based application. To create MySQL database using phpMyAdmin we need to follow the below steps.

If you don’t install phpMyAdmin in your system you can follow the below link to install phpMyAdmin .

Step1 ) First we need to login phpMyAdmin web page using root credential which was created at the time of mysql sever installation . we need to use same mysql root username and password for login phpMyAdmin page.

Step 2) After login we need to go the database tab which is located at the top of the wizard. Now press the database tab ,after pressing the tab we found create database field in which we need to enter the name of the database which we want to create and leave other field as default and then press the create button. After that the database will be created and you can view the database at the left side.

Create mysql database using phpMyAdmin,create database in mysql

Create database using phpMyAdmin

List MySQL Database

After creating database we can list the databases using below command through mysql promt.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| techrideradmin |

+--------------------+

3 rows in set (0.00 sec)

If there are many databases ,to find the particular database we can use LIKE clause.

The syntax will be :

mysql> show databases LIKE ‘tech%’;

After executed the above command all database will be appear which will start with “tech”.

MySQL Select Database

After connected the mysql server we need to select database with which we are working among all databases on the mysql server. To select the database we execute “use” command .

Among many databases to work with particular database we need to use "use" command.

To select MySQL database we login the mysql server and execute the below command.

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.73 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use techrideradmin

Database changed

MySQL Select Database

Create table in MySQL

Lets take a create table in MySQL example .

Suppose we are going to create a mysql table named “student”.

To create table in MySQL we execute the below command.

create table student(

studentnrollno INT NOT NULL AUTO_INCREMENT,

studentfirstname VARCHAR(30) NOT NULL,

studentlastname VARCHAR(30) NOT NULL,

PRIMARY KEY ( studentnrollno )

);

After executing the above command we create table in mysql with primary key called student which has three field one is studentrollno, second is student firstname and then student last name and we define student rollno as a primary key. We use NOT NULL which pevent to entry null value and student roll no increment automatically using the AUTO_INCREMENT keyword.

To execute the above command we need to login mysql server and after getting the mysql promt we need to execute the create table in mysql command as below.

mysql> create table student(

-> studentnrollno INT NOT NULL AUTO_INCREMENT,

-> studentfirstname VARCHAR(30) NOT NULL,

-> studentlastname VARCHAR(30) NOT NULL,

-> PRIMARY KEY ( studentnrollno )

-> );

Query OK, 0 rows affected (0.02 sec)

Create table in MySQL

Create table in MySQL using MySQL workbench

We can also create table in MySQL workbench. Lets follow the below steps.

Step1) first we create a connection in the Mysql workbench providing the details.We need to define connection name ,connection method, server hostname or ip address, port no and username which you have to allow to the server for the remote connection along with the particular database. Then we login the tool to manage our database.

Create table in MySQL using MySQL workbench

Setp2) After that we can create a table (in my case student) into our database(in my case techrideradmin). Now we add the column name ,define the data type and primary key and null value.

Create table in MySQL using MySQL workbench

Step3) After that we click on the apply button and found the below screen and then click the finish button to create the table.

Create table in MySQL using MySQL workbench

add new column

Also we can add column after crate a table .we add the extra column in column name section and then apply for add the new column for an existing table.

That’s all.If this article is helpful to know about Create Database in MySQL,Select Database in MySQL,List Database in MySQL,Create Table In Mysql primary key, please share and subscribe the article!!!!

How to Create Database,Table In MySQL