Create a MySQL Database, Tables and Insert Data

by on December 13, 2006 · 158 comments· LAST UPDATED December 4, 2013

in , ,

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.

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!
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

{ 158 comments… read them below or add one }

1 ninja May 7, 2008 at 12:43 am

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

Reply

2 vaheedulla March 4, 2011 at 3:24 pm

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

Reply

3 Anil December 30, 2011 at 11:29 am

thanks its so good example……….

Reply

4 denish April 20, 2014 at 9:39 am

God examples for beginers thanks

Reply

5 Ashish July 12, 2008 at 4:48 am

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

Reply

6 gh August 22, 2008 at 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

Reply

7 ST4R January 24, 2009 at 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

Reply

8 Rohit February 11, 2009 at 6:36 am

Nice start.
Thanx.

Reply

9 Andreas moliana April 22, 2009 at 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!

Reply

10 izhar April 23, 2009 at 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.

Reply

11 Sumit April 23, 2009 at 7:12 pm

Thanks a lot. Helped me very much.

Reply

12 chaminda June 23, 2009 at 6:00 am

how can we insert a column with data to existing table

Reply

13 neha singh September 12, 2011 at 6:01 am

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

Reply

14 shiva shankar August 4, 2009 at 7:06 am

pritty good for start up freshers,helped alot .

could have some more command.

Reply

15 srinivas August 27, 2009 at 9:32 am

thanks for helping me

Reply

16 wawan September 11, 2009 at 4:09 am

THANKS U SO MUCH,,, 4 help me problem

Reply

17 Eric October 2, 2009 at 6:58 am

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

Reply

18 chaitan kumar yadav October 3, 2009 at 5:30 am

how to enter data in tables…..

Reply

19 Damian November 24, 2009 at 11:00 pm

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

Reply

20 soundarya January 15, 2010 at 7:38 am

this information was v useful to me thanks a lot

Reply

21 Geetha January 20, 2010 at 6:48 am

Hi,
How to connect this database with drupal

Reply

22 sowmiya March 15, 2010 at 6:18 am

very very nice and simple

Reply

23 gizachew March 17, 2010 at 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

Reply

24 suryaprakash April 1, 2010 at 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 ..!

Reply

25 Anonymous June 30, 2010 at 5:39 pm

thanks

Reply

26 Mahesh August 22, 2010 at 6:37 am

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

Reply

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

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

Reply

28 jigs November 27, 2010 at 6:09 am

how to connect data with the databse in the php

Reply

29 even December 5, 2010 at 9:21 am

thz alot

Reply

30 ahmad hussain December 17, 2010 at 9:20 am

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

Reply

31 Nidhi Shah January 7, 2011 at 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.

Reply

32 lala March 6, 2014 at 9:26 am

Just restart ur mysql :D

Reply

33 Nidhi Shah January 7, 2011 at 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

Reply

34 thiyagi January 10, 2011 at 10:39 am

thanks for the tut, was helpful..

Reply

35 Soumyadeep Ghosh January 11, 2011 at 4:58 pm

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

Reply

36 shoes February 21, 2011 at 10:17 am

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

Reply

37 jhalak roy February 23, 2011 at 7:57 pm

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

Reply

38 jay February 24, 2011 at 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.

Reply

39 aashish February 27, 2011 at 6:02 pm

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

Reply

40 faran March 3, 2011 at 7:09 am

thanksssssss
this material is very helpful for me for my projects

Reply

41 cute programmer April 8, 2011 at 4:05 pm

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

Reply

42 Chris April 10, 2011 at 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

Reply

43 ThankfulGuy April 18, 2011 at 10:34 am

Thanks dude! it helped me!

Reply

44 VIVEK.R May 9, 2011 at 11:12 am

THANK YOU FOR GIVING SUCH A HELPFUL WEBSITE.

Reply

45 panda June 12, 2011 at 6:13 pm

Thanks alotttt:)

Reply

46 dariel June 26, 2011 at 10:04 am

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

Reply

47 Ken June 29, 2011 at 2:23 am

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

Reply

48 rashid Iqbal July 1, 2011 at 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

Reply

49 george davis July 3, 2011 at 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…

Reply

50 george davis July 3, 2011 at 7:21 pm

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

Reply

51 suranga wickramasinghe July 20, 2011 at 1:37 pm

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

Reply

52 Gamachis August 18, 2011 at 9:24 am

it is really helpful.. wonderful dude!!!

Reply

53 Anuj August 18, 2011 at 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

Reply

54 Anuj August 18, 2011 at 3:18 pm

How can i deleabte a table?

Reply

55 satz October 29, 2011 at 11:17 am

to delete a database,
DROP DATABASE ;

Reply

56 Dharmang August 19, 2011 at 12:33 pm

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

Reply

57 rohan August 30, 2011 at 9:17 am

thankxxxxxxxxxxx a lot..

Reply

58 Marifel September 9, 2011 at 7:03 am

…’it’s very simple/…

Reply

59 Marifel September 9, 2011 at 7:09 am

i learned this when i was first year college…

Reply

60 prabhamuralidharan October 18, 2011 at 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

Reply

61 Naresh October 18, 2011 at 4:51 pm

thanks for helping in such a simple way

Reply

62 satz October 29, 2011 at 11:15 am

This is awesome for beginners. Thnx

Reply

63 Rak October 31, 2011 at 6:59 pm

Great Article for beginners like me.

Appriciate your help. Keep up the good work.

Thanks,

Reply

64 suresh November 15, 2011 at 7:16 am

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

Reply

65 Amol January 18, 2012 at 1:27 pm

Hey thnkx….its realy good ….

Reply

66 PRABHAKAR MISHRA January 31, 2012 at 9:26 am

Thanks,

It was real cool stuff!!

Reply

67 assu February 9, 2012 at 7:01 am

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

Reply

68 jack February 14, 2012 at 10:23 pm

thanks a lot!!!

Reply

69 subhash March 20, 2012 at 3:34 pm

Great Article for beginners like me.

Appreciate your help. Keep up the good work.

Thanks,
Thanks a lot.

Reply

70 Vijay April 2, 2012 at 5:44 am

thanks very good article..

Reply

71 Abhishek April 30, 2012 at 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!

Reply

72 Atheist May 2, 2012 at 9:15 am

Thanks a lot!

Reply

73 Flav May 4, 2012 at 11:17 am

Thank you so much for the help

Reply

74 ang May 9, 2012 at 5:35 pm

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

Reply

75 Ephesian May 13, 2012 at 3:02 am

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

Reply

76 prakash May 24, 2012 at 11:19 am

Hi,

Thanks for the simple syntax to understand the concept.

Really appreciate u sir.

Thanx
Prakash……

Reply

77 Abhay Ramesh Chennagiri June 9, 2012 at 7:35 pm

Thanks a ton…

Reply

78 sudhag June 18, 2012 at 6:54 am

Good HIT

Reply

79 vaybhav June 20, 2012 at 5:43 am

that was really helpful

Reply

80 pratik July 4, 2012 at 6:30 am

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

Reply

81 sushma Gawande July 5, 2012 at 7:01 am

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

Reply

82 arpita bose July 9, 2012 at 8:59 am

this is great fr a novice…thx!

Reply

83 wenzy July 10, 2012 at 3:14 pm

tnx a lot..

Reply

84 bestman October 31, 2012 at 8:41 am

mysql

Reply

85 preeti July 24, 2012 at 6:11 am

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

Reply

86 rohan July 26, 2012 at 6:48 pm

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

Reply

87 Raj Nathani August 8, 2012 at 7:32 pm

Thanks a lot man :)

Reply

88 Ritesh August 12, 2012 at 8:16 pm

Thanx a lot…

Reply

89 mohan August 22, 2012 at 1:23 pm

thank u sir@

Reply

90 dude August 28, 2012 at 7:33 am

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

Reply

91 Mohit Golchha September 5, 2012 at 2:36 pm

thanks..!!:)

Reply

92 vicky September 9, 2012 at 8:57 am

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

Reply

93 Damrudhard September 10, 2012 at 5:33 am

Thanks for writing the tutorial.
God Bless you!

Reply

94 Lotake Dhiraj September 12, 2012 at 6:42 am

its usefull…..

Reply

95 Ria September 15, 2012 at 7:07 am

thankuuu

Reply

96 swapnil September 19, 2012 at 8:29 am

thank u

Reply

97 akbar October 1, 2012 at 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

Reply

98 shibin October 6, 2012 at 4:07 am

thankx..

Reply

99 Neha Purohit October 7, 2012 at 11:02 am

It is good to start. :-D

Reply

100 aamir October 10, 2012 at 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

Reply

101 mahen October 14, 2012 at 2:34 pm

veyuseful tanks alot:——–

Reply

102 LeoPrince October 16, 2012 at 5:28 am

thankx a lot for this page

Reply

103 mohammed shiraz October 16, 2012 at 5:31 am

kept everything simple nice work man!

Reply

104 shadab October 17, 2012 at 5:44 pm

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

Reply

105 rupendra singh October 19, 2012 at 5:51 pm

So thanks of u………

Reply

106 shashikant October 24, 2012 at 4:29 am

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

Reply

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

veyuseful tanks alot

Reply

108 bill November 11, 2012 at 7:55 pm

Thanks a lot man. Really helpful.

Reply

109 Kiran November 18, 2012 at 5:07 pm

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

Reply

110 Stepmudere November 19, 2012 at 2:06 pm

thanks a lot guys

Reply

111 Neeraj November 25, 2012 at 2:58 pm

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

Reply

112 Noor December 18, 2012 at 5:03 pm

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

Reply

113 Anil January 3, 2013 at 8:34 am

thanks its simple and good example

Reply

114 sakthikumar January 7, 2013 at 3:38 pm

thankyou

Reply

115 Bharath January 10, 2013 at 5:11 am

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

Reply

116 vishal January 15, 2013 at 2:58 pm

hey
thnxx its very helpful :)

Reply

117 lhon February 18, 2013 at 5:04 am

add me in
thanks for this its very simple and understandable

Reply

118 ba February 21, 2013 at 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.

Reply

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

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

Reply

120 winfred March 2, 2013 at 3:49 pm

really am happy uve helped me. God bless u.

Reply

121 satheesh March 7, 2013 at 5:05 am

really excellent………..

Reply

122 Tushar March 19, 2013 at 6:20 am

Thank u

Reply

123 mussa kadir March 26, 2013 at 11:14 am

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

Reply

124 harry April 5, 2013 at 7:03 pm

how i can create mysql from beigning

Reply

125 Bashi April 9, 2013 at 2:41 pm

thanku yaar……… It helps me!

Reply

126 Malik zubair April 10, 2013 at 7:53 am

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

Reply

127 relik April 25, 2013 at 10:08 pm

love this website. thanks.

Reply

128 k. arun May 5, 2013 at 5:38 am

thanx sir

Reply

129 Sabareesh May 15, 2013 at 6:36 am

Good one dude..Thanks

Reply

130 Nitin June 14, 2013 at 12:18 pm

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

help me.

Reply

131 Nitin shrivatsva June 14, 2013 at 12:37 pm

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

Reply

132 abii July 5, 2013 at 5:20 am

thanxx

Reply

133 pawan August 8, 2013 at 4:32 am

how to insert database use in mysql

Reply

134 KOMAL August 10, 2013 at 6:28 pm

Thank you so muchhh………very good start

Reply

135 fidaaslam August 15, 2013 at 4:47 pm

thank you so much..

Reply

136 Naraimha August 29, 2013 at 2:24 pm

it’s very easy to understand
Thank u Very much

Reply

137 archana September 7, 2013 at 5:51 am

hai how to create data entery in online job

Reply

138 sam October 19, 2013 at 6:49 am

thanks u

Reply

139 jameel November 1, 2013 at 5:36 am

how to create the new database in php

Reply

140 s bhat November 4, 2013 at 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.

Reply

141 neo December 4, 2013 at 7:30 am

good work. thanx

Reply

142 Nirmalya December 7, 2013 at 7:49 am

yeah great example…:)

Reply

143 khim lal December 17, 2013 at 8:58 am

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

Reply

144 Suman jain December 20, 2013 at 9:06 am

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

Reply

145 Ananya January 22, 2014 at 5:26 pm

Really helpful!

Reply

146 Raj January 30, 2014 at 5:47 am

Thanks a lot. Helped me very much.

Reply

147 ram February 5, 2014 at 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)

Reply

148 ankul February 9, 2014 at 12:12 pm

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

Reply

149 rohit February 20, 2014 at 6:56 am

thanks dear………..

Reply

150 vishal February 21, 2014 at 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 ?

Reply

151 Caracono March 14, 2014 at 3:02 pm

Thanks! so helpful…

Reply

152 nyanji March 25, 2014 at 8:36 am

worked like a charm…thanks alot

Reply

153 Ram March 27, 2014 at 7:31 am

Thanx a lot

Reply

154 fred March 27, 2014 at 1:11 pm

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

Reply

155 Anitha April 4, 2014 at 11:45 am

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

Reply

156 sheik April 9, 2014 at 10:06 am

thank u so much you are so great

Reply

157 Rinky April 13, 2014 at 2:22 pm

thnaks to help us..

Reply

158 ritesh April 17, 2014 at 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

Reply

Leave a Comment

Tagged as: ,

Previous Faq:

Next Faq: