Create a MySQL Database, Tables and Insert Data

by on December 13, 2006 · 165 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

{ 165 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 venkatesh August 30, 2014 at 12:45 pm

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

Reply

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

pritty good for start up freshers,helped alot .

could have some more command.

Reply

16 srinivas August 27, 2009 at 9:32 am

thanks for helping me

Reply

17 wawan September 11, 2009 at 4:09 am

THANKS U SO MUCH,,, 4 help me problem

Reply

18 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

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

how to enter data in tables…..

Reply

20 Damian November 24, 2009 at 11:00 pm

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

Reply

21 soundarya January 15, 2010 at 7:38 am

this information was v useful to me thanks a lot

Reply

22 Geetha January 20, 2010 at 6:48 am

Hi,
How to connect this database with drupal

Reply

23 sowmiya March 15, 2010 at 6:18 am

very very nice and simple

Reply

24 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

25 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

26 Anonymous June 30, 2010 at 5:39 pm

thanks

Reply

27 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

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

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

Reply

29 jigs November 27, 2010 at 6:09 am

how to connect data with the databse in the php

Reply

30 even December 5, 2010 at 9:21 am

thz alot

Reply

31 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

32 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

33 lala March 6, 2014 at 9:26 am

Just restart ur mysql :D

Reply

34 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

35 thiyagi January 10, 2011 at 10:39 am

thanks for the tut, was helpful..

Reply

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

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

Reply

37 shoes February 21, 2011 at 10:17 am

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

Reply

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

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

Reply

39 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

40 aashish February 27, 2011 at 6:02 pm

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

Reply

41 faran March 3, 2011 at 7:09 am

thanksssssss
this material is very helpful for me for my projects

Reply

42 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

43 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

44 ThankfulGuy April 18, 2011 at 10:34 am

Thanks dude! it helped me!

Reply

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

THANK YOU FOR GIVING SUCH A HELPFUL WEBSITE.

Reply

46 panda June 12, 2011 at 6:13 pm

Thanks alotttt:)

Reply

47 dariel June 26, 2011 at 10:04 am

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

Reply

48 Ken June 29, 2011 at 2:23 am

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

Reply

49 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

50 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

51 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

52 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

53 Gamachis August 18, 2011 at 9:24 am

it is really helpful.. wonderful dude!!!

Reply

54 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

55 Anuj August 18, 2011 at 3:18 pm

How can i deleabte a table?

Reply

56 satz October 29, 2011 at 11:17 am

to delete a database,
DROP DATABASE ;

Reply

57 Dharmang August 19, 2011 at 12:33 pm

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

Reply

58 rohan August 30, 2011 at 9:17 am

thankxxxxxxxxxxx a lot..

Reply

59 Marifel September 9, 2011 at 7:03 am

…’it’s very simple/…

Reply

60 Marifel September 9, 2011 at 7:09 am

i learned this when i was first year college…

Reply

61 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

62 Naresh October 18, 2011 at 4:51 pm

thanks for helping in such a simple way

Reply

63 satz October 29, 2011 at 11:15 am

This is awesome for beginners. Thnx

Reply

64 Rak October 31, 2011 at 6:59 pm

Great Article for beginners like me.

Appriciate your help. Keep up the good work.

Thanks,

Reply

65 suresh November 15, 2011 at 7:16 am

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

Reply

66 Amol January 18, 2012 at 1:27 pm

Hey thnkx….its realy good ….

Reply

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

Thanks,

It was real cool stuff!!

Reply

68 assu February 9, 2012 at 7:01 am

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

Reply

69 jack February 14, 2012 at 10:23 pm

thanks a lot!!!

Reply

70 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

71 Vijay April 2, 2012 at 5:44 am

thanks very good article..

Reply

72 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

73 Atheist May 2, 2012 at 9:15 am

Thanks a lot!

Reply

74 Flav May 4, 2012 at 11:17 am

Thank you so much for the help

Reply

75 ang May 9, 2012 at 5:35 pm

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

Reply

76 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

77 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

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

Thanks a ton…

Reply

79 sudhag June 18, 2012 at 6:54 am

Good HIT

Reply

80 vaybhav June 20, 2012 at 5:43 am

that was really helpful

Reply

81 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

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

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

Reply

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

this is great fr a novice…thx!

Reply

84 wenzy July 10, 2012 at 3:14 pm

tnx a lot..

Reply

85 bestman October 31, 2012 at 8:41 am

mysql

Reply

86 preeti July 24, 2012 at 6:11 am

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

Reply

87 rohan July 26, 2012 at 6:48 pm

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

Reply

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

Thanks a lot man :)

Reply

89 Ritesh August 12, 2012 at 8:16 pm

Thanx a lot…

Reply

90 mohan August 22, 2012 at 1:23 pm

thank u sir@

Reply

91 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

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

thanks..!!:)

Reply

93 vicky September 9, 2012 at 8:57 am

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

Reply

94 Damrudhard September 10, 2012 at 5:33 am

Thanks for writing the tutorial.
God Bless you!

Reply

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

its usefull…..

Reply

96 Ria September 15, 2012 at 7:07 am

thankuuu

Reply

97 swapnil September 19, 2012 at 8:29 am

thank u

Reply

98 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

99 shibin October 6, 2012 at 4:07 am

thankx..

Reply

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

It is good to start. :-D

Reply

101 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

102 mahen October 14, 2012 at 2:34 pm

veyuseful tanks alot:——–

Reply

103 LeoPrince October 16, 2012 at 5:28 am

thankx a lot for this page

Reply

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

kept everything simple nice work man!

Reply

105 shadab October 17, 2012 at 5:44 pm

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

Reply

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

So thanks of u………

Reply

107 shashikant October 24, 2012 at 4:29 am

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

Reply

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

veyuseful tanks alot

Reply

109 bill November 11, 2012 at 7:55 pm

Thanks a lot man. Really helpful.

Reply

110 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

111 Stepmudere November 19, 2012 at 2:06 pm

thanks a lot guys

Reply

112 Neeraj November 25, 2012 at 2:58 pm

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

Reply

113 Noor December 18, 2012 at 5:03 pm

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

Reply

114 Anil January 3, 2013 at 8:34 am

thanks its simple and good example

Reply

115 sakthikumar January 7, 2013 at 3:38 pm

thankyou

Reply

116 Bharath January 10, 2013 at 5:11 am

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

Reply

117 vishal January 15, 2013 at 2:58 pm

hey
thnxx its very helpful :)

Reply

118 lhon February 18, 2013 at 5:04 am

add me in
thanks for this its very simple and understandable

Reply

119 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

120 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

121 winfred March 2, 2013 at 3:49 pm

really am happy uve helped me. God bless u.

Reply

122 satheesh March 7, 2013 at 5:05 am

really excellent………..

Reply

123 Tushar March 19, 2013 at 6:20 am

Thank u

Reply

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

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

Reply

125 harry April 5, 2013 at 7:03 pm

how i can create mysql from beigning

Reply

126 Bashi April 9, 2013 at 2:41 pm

thanku yaar……… It helps me!

Reply

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

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

Reply

128 relik April 25, 2013 at 10:08 pm

love this website. thanks.

Reply

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

thanx sir

Reply

130 Sabareesh May 15, 2013 at 6:36 am

Good one dude..Thanks

Reply

131 Nitin June 14, 2013 at 12:18 pm

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

help me.

Reply

132 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

133 abii July 5, 2013 at 5:20 am

thanxx

Reply

134 pawan August 8, 2013 at 4:32 am

how to insert database use in mysql

Reply

135 KOMAL August 10, 2013 at 6:28 pm

Thank you so muchhh………very good start

Reply

136 fidaaslam August 15, 2013 at 4:47 pm

thank you so much..

Reply

137 Naraimha August 29, 2013 at 2:24 pm

it’s very easy to understand
Thank u Very much

Reply

138 archana September 7, 2013 at 5:51 am

hai how to create data entery in online job

Reply

139 sam October 19, 2013 at 6:49 am

thanks u

Reply

140 jameel November 1, 2013 at 5:36 am

how to create the new database in php

Reply

141 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

142 neo December 4, 2013 at 7:30 am

good work. thanx

Reply

143 Nirmalya December 7, 2013 at 7:49 am

yeah great example…:)

Reply

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

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

Reply

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

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

Reply

146 Ananya January 22, 2014 at 5:26 pm

Really helpful!

Reply

147 Raj January 30, 2014 at 5:47 am

Thanks a lot. Helped me very much.

Reply

148 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

149 ankul February 9, 2014 at 12:12 pm

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

Reply

150 rohit February 20, 2014 at 6:56 am

thanks dear………..

Reply

151 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

152 Caracono March 14, 2014 at 3:02 pm

Thanks! so helpful…

Reply

153 nyanji March 25, 2014 at 8:36 am

worked like a charm…thanks alot

Reply

154 Ram March 27, 2014 at 7:31 am

Thanx a lot

Reply

155 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

156 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

157 sheik April 9, 2014 at 10:06 am

thank u so much you are so great

Reply

158 Rinky April 13, 2014 at 2:22 pm

thnaks to help us..

Reply

159 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

160 suchetan April 22, 2014 at 12:09 pm

Thanks for the tutorial. :)

Reply

161 Ashraf May 5, 2014 at 10:04 am

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

Reply

162 sindhu May 12, 2014 at 6:02 am

hank u helped me alot

Reply

163 anup May 22, 2014 at 5:43 pm

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

Reply

164 SAMWEL MMASA August 15, 2014 at 9:09 pm

thanks a lot it very helpfull for me

Reply

165 Parshant singh September 2, 2014 at 9:34 am

thanks dude….

Reply

Leave a Comment

Tagged as: ,

Previous Faq:

Next Faq: