≡ Menu

Create a MySQL Database, Tables and Insert Data

How do I create a MySQL database, tables, and insert (store) data into newly created tables?

MySQL is a free and open source database management system. You need to use sql commands to create database. You also need to login as mysql root user account. To create a database and set up tables for the same use the following sql commands:
Tutorial details
DifficultyEasy (rss)
Root privilegesNo
Requirementsmysql
Estimated completion time10m
  1. CREATE DATABASE - create the database. To use this statement, you need the CREATE privilege for the database.
  2. CREATE TABLE - create the table. You must have the CREATE privilege for the table.
  3. INSERT - To add/insert data to table i.e. inserts new rows into an existing table.

Procedure for creating a database and a sample table

Login as the mysql root user to create database:
$ mysql -u root -p
Sample outputs:

mysql>

Add a database called books, enter:
mysql> CREATE DATABASE books;

Now, database is created. Use a database with use command, type:
mysql> USE books;

Next, create a table called authors with name, email and id as fields:
mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));

To display your tables in books database, enter:
mysql> SHOW TABLES;
Sample outputs:

+-----------------+
| Tables_in_books |
+-----------------+
| authors         |
+-----------------+
1 row in set (0.00 sec)

Finally, add a data i.e. row to table books using INSERT statement, run:
mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Sample outputs:

Query OK, 1 row affected (0.00 sec)

Try to add few more rows to your table:
mysql> INSERT INTO authors (id,name,email) VALUES(2,"Priya","p@gmail.com");
mysql> INSERT INTO authors (id,name,email) VALUES(3,"Tom","tom@yahoo.com");

To display all rows i.e. data stored in authors table, enter:
mysql> SELECT * FROM authors;
Sample outputs:

+------+-------+---------------+
| id   | name  | email         |
+------+-------+---------------+
|    1 | Vivek | xuz@abc.com   |
|    2 | Priya | p@gmail.com   |
|    3 | Tom   | tom@yahoo.com |
+------+-------+---------------+
3 rows in set (0.00 sec)

Now, you know how to create a database and a table. For further information please see MySQL data types and official documentation.

Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?
This entry is 4 of 5 in the CentOS Linux MySQL Server Tutorial series. Keep reading the rest of the series:
  1. CentOS install Mysql database server
  2. CentOS install MySQL client only
  3. MySQL Create a user accounts
  4. MySQL Create a database & tables and data
  5. Reinstall MySQL On Linux

{ 180 comments… add one }

  • ninja May 7, 2008, 12:43 am

    thank you very much, you helped me out! :)

    • vaheedulla March 4, 2011, 3:24 pm

      thank you very much, you helped me out! :)

      • Anil December 30, 2011, 11:29 am

        thanks its so good example……….

        • denish April 20, 2014, 9:39 am

          God examples for beginers thanks

  • Ashish July 12, 2008, 4:48 am

    Hi, i saw this site very helpful for me for crating table with mysql thanks for supporting new users;

  • gh August 22, 2008, 6:16 am

    very cool and very helpfull…
    continue the good work vivek gite
    we are expecting many tutorial of urs …
    thanks for this one very useful

  • ST4R January 24, 2009, 1:36 pm

    Hi very good tutorial :D keep up the good work you helped me alot :) i was wondering (if there wasnt any previous sessions opened) how do i use this table so it will show at my website (like linking php and sql or somthing like that) :) sorry for the stupid question but im just learning sql and php…oh and allso could it be possible to use php in a html website (so you can include some php code in you html)
    Thanks! :D

  • Rohit February 11, 2009, 6:36 am

    Nice start.
    Thanx.

  • Andreas moliana April 22, 2009, 11:29 am

    I think if you can try to make more example one can easily have a picture of how to get in use with MySQL . And your example may reflect data been put into table so as one can easily see how to use the data he has to create a table or tables.
    By the by, this is also of help can train people who wants to use mySQL their studies. many thanks!

  • izhar April 23, 2009, 4:17 am

    Thanks a lot. u make easy for me dude bcoz i am try a couple day to create sinog blegle tables but is not work…..good blees you.

  • Sumit April 23, 2009, 7:12 pm

    Thanks a lot. Helped me very much.

  • chaminda June 23, 2009, 6:00 am

    how can we insert a column with data to existing table

    • neha singh September 12, 2011, 6:01 am

      alter table name add column new column name varchar(x);

    • venkatesh August 30, 2014, 12:45 pm

      thank u very much,can u give me some query for join

  • shiva shankar August 4, 2009, 7:06 am

    pritty good for start up freshers,helped alot .

    could have some more command.

  • srinivas August 27, 2009, 9:32 am

    thanks for helping me

  • wawan September 11, 2009, 4:09 am

    THANKS U SO MUCH,,, 4 help me problem

  • Eric October 2, 2009, 6:58 am

    Thanks very much for that short tutorial. It was worth it and very articulate. God Bless.

  • chaitan kumar yadav October 3, 2009, 5:30 am

    how to enter data in tables…..

  • Damian November 24, 2009, 11:00 pm

    Just learning SQL now. This was an incredibly useful tutorial. Thanks!

  • soundarya January 15, 2010, 7:38 am

    this information was v useful to me thanks a lot

  • Geetha January 20, 2010, 6:48 am

    Hi,
    How to connect this database with drupal

  • sowmiya March 15, 2010, 6:18 am

    very very nice and simple

  • gizachew March 17, 2010, 4:22 pm

    hi how are u.it is fine and helpful for me so it is better u keep such a help and support.
    please keep it up

  • suryaprakash April 1, 2010, 11:01 am

    this is suryaprakash
    i am working on swing project in Java
    i want to insert Jtable information into DataBase table

    can it is possible..?
    if yes
    plz send a sample program
    waiting for u r Reply

    My email address is ::suryaprakash.pisay@gmail.com
    Thank u in Advance ..!

  • Anonymous June 30, 2010, 5:39 pm

    thanks

  • Mahesh August 22, 2010, 6:37 am

    Nice info. But please specify the PHP code to be used to add data into MySQL database.

  • Cosmile D'souza November 23, 2010, 11:14 am

    Hey amigo, Thanks your help is greatly appreciated………

  • jigs November 27, 2010, 6:09 am

    how to connect data with the databse in the php

  • even December 5, 2010, 9:21 am

    thz alot

  • ahmad hussain December 17, 2010, 9:20 am

    I spending hours in search , finally founded this code , really very helpful . Just want to entitle you 5 start. Thx lot.

  • Nidhi Shah January 7, 2011, 6:30 am

    How to recover data from .ibd file if innodb is corrupted
    I have one test database and many table with the extension of .frm and .idb file. from frm file i got strucure of database but how to restore data from individiual file of .idb file.

    • lala March 6, 2014, 9:26 am

      Just restart ur mysql :D

  • Nidhi Shah January 7, 2011, 6:30 am

    this is suryaprakash
    i am working on swing project in Java
    i want to insert Jtable information into DataBase table

    can it is possible..?
    if yes
    plz send a sample program
    waiting for u r Reply

    My email address is ::suryaprakash.pisay@gmail.com
    Thank u in Advance ..!

    Reply

  • thiyagi January 10, 2011, 10:39 am

    thanks for the tut, was helpful..

  • Soumyadeep Ghosh January 11, 2011, 4:58 pm

    hi!!!!!!!!!!!!this tutorial is very helpful to me.thanks for the post.

  • shoes February 21, 2011, 10:17 am

    can i get a full sample from this code..?
    thanks

  • jhalak roy February 23, 2011, 7:57 pm

    thank u sooooooooooo much……helped me a lot……..

  • jay February 24, 2011, 4:27 pm

    it really helped me about about this tutorial… i’m just a beginner but now i understand the syntax in using the mysql client. thank you very much.

  • aashish February 27, 2011, 6:02 pm

    thankzzzzzzzzzzzz alot my frnd*****************************************
    very helpful for my practical exams

  • faran March 3, 2011, 7:09 am

    thanksssssss
    this material is very helpful for me for my projects

  • cute programmer April 8, 2011, 4:05 pm

    thanks for this one, im quite confused why my code dont work even though its correct.

  • Chris April 10, 2011, 4:24 pm

    I am using a webhost and has no shell access so unable to use command line.
    Here’s the problem:
    I have tried to “import” a table with its content into the existing database but it kept showing a 1062 error about key 1 ………… which I have no idea why and what to do.
    I tried to create a new table from within phpmyadmin but it requires me to indicate how many fields and then to fill in those empty boxes…which I have no idea what to put in there.

    So, i thought of using bigdump to try to import this table into the database or to do a manual import by copying the content of the table into the SQL tab’s Sql Query box from within phpmyadmin.
    Are the above two options suitable?
    What would be your best suggestion and guidance on how to proceed so as to succeed in getting this table into the database?

    Any assistance, guidance and advice, would be much appreciated.

    Thanks in advance and best wishes.
    Chris

  • ThankfulGuy April 18, 2011, 10:34 am

    Thanks dude! it helped me!

  • VIVEK.R May 9, 2011, 11:12 am

    THANK YOU FOR GIVING SUCH A HELPFUL WEBSITE.

  • panda June 12, 2011, 6:13 pm

    Thanks alotttt:)

  • dariel June 26, 2011, 10:04 am

    thank you it’s helpful to find my error in syntax…. tnx

  • Ken June 29, 2011, 2:23 am

    Sir what command should i use if i want to copy in mysql or wamp.?

  • rashid Iqbal July 1, 2011, 10:46 am

    I want to change the password of user from users table, but the password values shown in table are in encrypted form. how can I change the user password

    • george davis July 3, 2011, 7:23 pm

      try to change the password in xammp or wampp depending on the server you are using.log in and in the specific database for the specific user, you can change the password. make sure you click GO..
      hope this helps…

  • george davis July 3, 2011, 7:21 pm

    Thanks a lot. Am a beginner in mysql programming and this is good start from here.
    George

  • suranga wickramasinghe July 20, 2011, 1:37 pm

    A great work ,i was able to add huge knowledge within few minitues. thankX lot dear teacher…

  • Gamachis August 18, 2011, 9:24 am

    it is really helpful.. wonderful dude!!!

  • Anuj August 18, 2011, 3:17 pm

    Thanks bro

    very use full

    i wasn’t able to understand what was the matter but now i understood

    once again thnks

  • Anuj August 18, 2011, 3:18 pm

    How can i deleabte a table?

    • satz October 29, 2011, 11:17 am

      to delete a database,
      DROP DATABASE ;

  • Dharmang August 19, 2011, 12:33 pm

    hey man this is very helpful to user like me who is new in linux

  • rohan August 30, 2011, 9:17 am

    thankxxxxxxxxxxx a lot..

  • Marifel September 9, 2011, 7:03 am

    …’it’s very simple/…

  • Marifel September 9, 2011, 7:09 am

    i learned this when i was first year college…

  • prabhamuralidharan October 18, 2011, 11:59 am

    Really thank you very much!!!!!!!! because i was struggling with mysql how to create table and databases, now am clear all about that!!!!. In all aspects basic knowledge is very important. so this website have given that knowledge and the explanations also was very understandable! thank you very munch!!!!!!!!
    prabhamuralidharan

  • Naresh October 18, 2011, 4:51 pm

    thanks for helping in such a simple way

  • satz October 29, 2011, 11:15 am

    This is awesome for beginners. Thnx

  • Rak October 31, 2011, 6:59 pm

    Great Article for beginners like me.

    Appriciate your help. Keep up the good work.

    Thanks,

  • suresh November 15, 2011, 7:16 am

    hi ..thnx a lot.
    u helped me a lot..

  • Amol January 18, 2012, 1:27 pm

    Hey thnkx….its realy good ….

  • PRABHAKAR MISHRA January 31, 2012, 9:26 am

    Thanks,

    It was real cool stuff!!

  • assu February 9, 2012, 7:01 am

    hai,thank u so much……………………….

  • jack February 14, 2012, 10:23 pm

    thanks a lot!!!

  • subhash March 20, 2012, 3:34 pm

    Great Article for beginners like me.

    Appreciate your help. Keep up the good work.

    Thanks,
    Thanks a lot.

  • Vijay April 2, 2012, 5:44 am

    thanks very good article..

  • Abhishek April 30, 2012, 11:33 am

    Actually First it was not working, then i tried again and it worked..! A Very GreatThank You for Article. I’ll Share this on every way possible!

  • Atheist May 2, 2012, 9:15 am

    Thanks a lot!

  • Flav May 4, 2012, 11:17 am

    Thank you so much for the help

  • ang May 9, 2012, 5:35 pm

    Thank you very much, just what (and all) I needed

  • Ephesian May 13, 2012, 3:02 am

    Thanks a lot for the help. I followed your example and it worked well. This site is really helpful.

  • prakash May 24, 2012, 11:19 am

    Hi,

    Thanks for the simple syntax to understand the concept.

    Really appreciate u sir.

    Thanx
    Prakash……

  • Abhay Ramesh Chennagiri June 9, 2012, 7:35 pm

    Thanks a ton…

  • sudhag June 18, 2012, 6:54 am

    Good HIT

  • vaybhav June 20, 2012, 5:43 am

    that was really helpful

  • pratik July 4, 2012, 6:30 am

    it i cant copy the code, but i can copy it from the view page source..HUH

  • sushma Gawande July 5, 2012, 7:01 am

    please give the details of more command so anybody can learn it himself.

  • arpita bose July 9, 2012, 8:59 am

    this is great fr a novice…thx!

  • wenzy July 10, 2012, 3:14 pm

    tnx a lot..

    • bestman October 31, 2012, 8:41 am

      mysql

  • preeti July 24, 2012, 6:11 am

    hi
    sql me database ko isa tara hi programing krte h ky bolo……………………

  • rohan July 26, 2012, 6:48 pm

    thank you very much for this valuable information.really appreciable.

  • Raj Nathani August 8, 2012, 7:32 pm

    Thanks a lot man :)

  • Ritesh August 12, 2012, 8:16 pm

    Thanx a lot…

  • mohan August 22, 2012, 1:23 pm

    thank u sir@

  • dude August 28, 2012, 7:33 am

    Thanks, this helped a bit on my first go at sql. looks like I have a lot of reading to do ;)

  • Mohit Golchha September 5, 2012, 2:36 pm

    thanks..!!:)

  • vicky September 9, 2012, 8:57 am

    tis sql queries r soo simple..and easy to understand…thanks

  • Damrudhard September 10, 2012, 5:33 am

    Thanks for writing the tutorial.
    God Bless you!

  • Lotake Dhiraj September 12, 2012, 6:42 am

    its usefull…..

  • Ria September 15, 2012, 7:07 am

    thankuuu

  • swapnil September 19, 2012, 8:29 am

    thank u

  • akbar October 1, 2012, 9:19 am

    dear any help me,
    i have create visual basic window form and how to add record in datagridview and update form pl help now i have frustrate

  • shibin October 6, 2012, 4:07 am

    thankx..

  • Neha Purohit October 7, 2012, 11:02 am

    It is good to start. :-D

  • aamir October 10, 2012, 5:58 am

    insert into new_table(‘product name’) values(‘abc’).

    I have a space in my column name. I want to insert and fetch the details from the column. can any one show me how to go about it .
    any help would be appreciated.

    thank you

  • mahen October 14, 2012, 2:34 pm

    veyuseful tanks alot:——–

  • LeoPrince October 16, 2012, 5:28 am

    thankx a lot for this page

  • mohammed shiraz October 16, 2012, 5:31 am

    kept everything simple nice work man!

  • shadab October 17, 2012, 5:44 pm

    Thanks, to save my time……..
    I was forgate actually, afterall it helps me…!

  • rupendra singh October 19, 2012, 5:51 pm

    So thanks of u………

  • shashikant October 24, 2012, 4:29 am

    kindly suggest,how extract data from mysql database through php programming language?….
    give one example…

  • Engrr Abdul Rasheed Memon November 6, 2012, 10:05 am

    veyuseful tanks alot

  • bill November 11, 2012, 7:55 pm

    Thanks a lot man. Really helpful.

  • Kiran November 18, 2012, 5:07 pm

    getting an error . . javax.servlet.ServletException: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:8888/contactdb

  • Stepmudere November 19, 2012, 2:06 pm

    thanks a lot guys

  • Neeraj November 25, 2012, 2:58 pm

    awesome yrrrrrr.,.,.,.,, really like it……
    it helps me alot……. thanx thanx thanx…… :)

  • Noor December 18, 2012, 5:03 pm

    Really Really Brilliant…..
    100000000 stars from my side. Thanks a lot.

  • Anil January 3, 2013, 8:34 am

    thanks its simple and good example

  • sakthikumar January 7, 2013, 3:38 pm

    thankyou

  • Bharath January 10, 2013, 5:11 am

    thanks……..
    it helped me and kindly can u tel how to connect mysql with php……..!!!!!

  • vishal January 15, 2013, 2:58 pm

    hey
    thnxx its very helpful :)

  • lhon February 18, 2013, 5:04 am

    add me in
    thanks for this its very simple and understandable

  • ba February 21, 2013, 7:18 pm

    Damn it, you’re awesome. I bought a book for $49 read for 3 hours, can not find out how to create a table, but after read yours in 15 minutes, boom, there I go.

  • Md. Mahidul Hasan February 25, 2013, 7:52 pm

    Thanks, It is a great help for the new mysql learner like me !!! :D

  • winfred March 2, 2013, 3:49 pm

    really am happy uve helped me. God bless u.

  • satheesh March 7, 2013, 5:05 am

    really excellent………..

  • Tushar March 19, 2013, 6:20 am

    Thank u

  • mussa kadir March 26, 2013, 11:14 am

    thanxs,i was able to solve my problem at last.

  • harry April 5, 2013, 7:03 pm

    how i can create mysql from beigning

  • Bashi April 9, 2013, 2:41 pm

    thanku yaar……… It helps me!

  • Malik zubair April 10, 2013, 7:53 am

    How to create data base and how i can implement it?

  • relik April 25, 2013, 10:08 pm

    love this website. thanks.

  • k. arun May 5, 2013, 5:38 am

    thanx sir

  • Sabareesh May 15, 2013, 6:36 am

    Good one dude..Thanks

  • Nitin June 14, 2013, 12:18 pm

    good example but how can i copy other database table in my database.

    help me.

    • Nitin shrivatsva June 14, 2013, 12:37 pm

      CREATE TABLE databasename2.tablename LIKE databasename1.tablename;
      INSERT INTO databasename2.tablename SELECT * FROM databasename1.tablename;

  • abii July 5, 2013, 5:20 am

    thanxx

  • pawan August 8, 2013, 4:32 am

    how to insert database use in mysql

  • KOMAL August 10, 2013, 6:28 pm

    Thank you so muchhh………very good start

  • fidaaslam August 15, 2013, 4:47 pm

    thank you so much..

  • Naraimha August 29, 2013, 2:24 pm

    it’s very easy to understand
    Thank u Very much

    • archana September 7, 2013, 5:51 am

      hai how to create data entery in online job

  • sam October 19, 2013, 6:49 am

    thanks u

  • jameel November 1, 2013, 5:36 am

    how to create the new database in php

  • s bhat November 4, 2013, 1:11 pm

    I have uploaded and extracted wordpress from c panel file manager to root directory(public html) but after that cant create mysql directory, pl. give step by step instruction how to create mysql database and add wordpress.

  • neo December 4, 2013, 7:30 am

    good work. thanx

  • Nirmalya December 7, 2013, 7:49 am

    yeah great example…:)

  • khim lal December 17, 2013, 8:58 am

    Thanks for helping me to understand these basic step in such simple way.. :)

  • Suman jain December 20, 2013, 9:06 am

    my rows are not effecting . what might be the problem ?
    syntax is correct

  • Ananya January 22, 2014, 5:26 pm

    Really helpful!

  • Raj January 30, 2014, 5:47 am

    Thanks a lot. Helped me very much.

  • ram February 5, 2014, 10:39 am

    its very nice, i understud that table

    Finally, add a data i.e. row to table books using INSERT statement, run:
    mysql> INSERT INTO authors (id,name,email) VALUES(1,”Vivek”,”xuz@abc.com”);

    Sample outputs:

    Query OK, 1 row affected (0.00 sec)

  • ankul February 9, 2014, 12:12 pm

    how to enter the values by using “&” in mysql table ..from command prompt???

  • rohit February 20, 2014, 6:56 am

    thanks dear………..

  • vishal February 21, 2014, 9:00 am

    $ mysql -u root -p
    Above command is not running in my system even after entering root password.
    It show output as:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
    So,how i will log into the mysql ?

  • Caracono March 14, 2014, 3:02 pm

    Thanks! so helpful…

  • nyanji March 25, 2014, 8:36 am

    worked like a charm…thanks alot

  • Ram March 27, 2014, 7:31 am

    Thanx a lot

  • fred March 27, 2014, 1:11 pm

    hi,can you pls help me create product form using php programming language in wed base.i mean the codes.tnks

  • Anitha April 4, 2014, 11:45 am

    Am new to Mysql, this article was very helpful for me and am very impressed with your document….thanks a lot ….

  • sheik April 9, 2014, 10:06 am

    thank u so much you are so great

  • Rinky April 13, 2014, 2:22 pm

    thnaks to help us..

  • ritesh April 17, 2014, 6:22 am

    thankx alot this was very helpful.but can anyone tell me how to import data from terminal to table that is created in database.is there any kind of command.plz let me know

  • suchetan April 22, 2014, 12:09 pm

    Thanks for the tutorial. :)

  • Ashraf May 5, 2014, 10:04 am

    Thanks to help us. It’s a good example…………….

  • sindhu May 12, 2014, 6:02 am

    hank u helped me alot

  • anup May 22, 2014, 5:43 pm

    it was very helpful n very simple example……………..thanks alot

  • SAMWEL MMASA August 15, 2014, 9:09 pm

    thanks a lot it very helpfull for me

  • Parshant singh September 2, 2014, 9:34 am

    thanks dude….

  • Nkateko Malungana October 15, 2014, 12:52 pm

    Help me creating a books database using MySQL as indicated in the table

  • Kris Leblanc October 17, 2014, 12:14 pm

    Good day, I have just started studying SQL, thank you for this tutorial on MySQL, I have been using Microsoft SQL server, and I wanted to try MySQL.

    I followed your instructions and I created a database, tables, and data, I did this same thing in MS SQL and used the following query to create a new table called result with a condition that screens > 1, here is my code:

    /* Using SQL Server syntax */

    /* creating the new table with a query */
    select
    t1.player_id,
    t1.name,
    t1.mac,
    t2.name as du_name,
    t3.name as config_name
    into [result]
    from player t1
    inner join display_unit t2 on(t1.player_id=t2.player_id)
    inner join configuration t3 on(t1.player_id=t3.player_id)
    where t2.screens > 1

    /* what is the syntax to get this to work on MySQL???/*

  • HASNAIN MEHMOOD January 8, 2015, 1:15 pm

    blessn for u…………………….

  • shanny April 16, 2015, 11:04 am

    i have database and i need u to tell me how to import blob data from that database and write it in my client program . need to enter 10 data in the table here in client program.

  • Shakil Ahamd May 16, 2015, 11:17 am

    Very Helpful for me. keep it up

  • Tapan Das June 2, 2015, 12:45 pm

    Thnx.
    It is very good & easy example of dbms to insert data in its .

  • saumya June 11, 2015, 9:05 am

    you helped me a lot thank you

  • warunwilliam June 15, 2015, 7:22 am

    thank dude i rate u five stars(*****)

  • SARAVANAN T K June 26, 2015, 9:07 am

    thank u very much ..

    super ji

  • rajendra khindkar June 30, 2015, 5:30 am

    thanks a lot ,its simple and good example for beginers

  • Rishabh Kumar June 30, 2015, 9:50 pm

    Thanx Dude You Helped Me a lot

  • Nell July 17, 2015, 10:10 am

    Very helpful I was using outdated syntax and this tutorial helped me a lot with the current syntax. :D ♫

  • bala July 21, 2015, 6:09 am

    THANKS!!! THIS IS AWESOME

  • Sony saji July 29, 2015, 8:56 am

    Very helpful thank you!!!!!!!

  • Gutema July 29, 2015, 12:33 pm

    very good tutorial I got what I want really!!!

Leave a Comment