Create a mysql database, tables and insert data

by nixCraft on December 13, 2006 · 122 comments· last updated at December 13, 2006

Q. How do I create a MySQL database and tables?

A. If you want to create a database and set up tables for the same use the following two sql commands:
=> CREATE DATABASE - create the database

=> CREATE TABLE - create the table

=> INSERT - To add/insert data to table

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database

CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.

INSERT inserts new rows into an existing table.

Procedure for creating a database and a sample table

mysql>

Add a database called books
mysql> CREATE DATABASE books;

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

Now create a table called authors with name, email and id:
mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
Display your table name just created:
mysql> SHOW TABLES;
Output:

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

Now add a data / row to table books using INSERT statement:
mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Output:

Query OK, 1 row affected (0.00 sec)

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

Now display all rows:
mysql> SELECT * FROM authors;
Output:

+------+-------+---------------+
| 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.

You should follow me on twitter here or grab rss feed to keep track of new changes.

Featured Articles:

{ 122 comments… read them below or add one }

1 ninja May 7, 2008 at 12:43 am

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

2 vaheedulla March 4, 2011 at 3:24 pm

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

3 Anil December 30, 2011 at 11:29 am: thanks its so good example……….
Reply

4 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;

5 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

6 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

7 Rohit February 11, 2009 at 6:36 am

Nice start.
Thanx.

8 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!

9 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.

10 Sumit April 23, 2009 at 7:12 pm

Thanks a lot. Helped me very much.

11 chaminda June 23, 2009 at 6:00 am

how can we insert a column with data to existing table

12 neha singh September 12, 2011 at 6:01 am: alter table name add column new column name varchar(x);
Reply

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

pritty good for start up freshers,helped alot .

could have some more command.

14 srinivas August 27, 2009 at 9:32 am

thanks for helping me

15 wawan September 11, 2009 at 4:09 am

THANKS U SO MUCH,,, 4 help me problem

16 Eric October 2, 2009 at 6:58 am

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

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

how to enter data in tables…..

18 Damian November 24, 2009 at 11:00 pm

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

19 soundarya January 15, 2010 at 7:38 am

this information was v useful to me thanks a lot

20 Geetha January 20, 2010 at 6:48 am

Hi,
How to connect this database with drupal

21 sowmiya March 15, 2010 at 6:18 am

very very nice and simple

22 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

23 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 ..!

24 Anonymous June 30, 2010 at 5:39 pm

thanks

25 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.

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

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

27 jigs November 27, 2010 at 6:09 am

how to connect data with the databse in the php

28 even December 5, 2010 at 9:21 am

thz alot

29 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.

30 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.

31 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 ..!

32 thiyagi January 10, 2011 at 10:39 am

thanks for the tut, was helpful..

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

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

34 shoes February 21, 2011 at 10:17 am

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

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

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

36 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.

37 aashish February 27, 2011 at 6:02 pm

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

38 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.

39 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

40 ThankfulGuy April 18, 2011 at 10:34 am

Thanks dude! it helped me!

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

THANK YOU FOR GIVING SUCH A HELPFUL WEBSITE.

42 panda June 12, 2011 at 6:13 pm

Thanks alotttt:)

43 dariel June 26, 2011 at 10:04 am

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

44 Ken June 29, 2011 at 2:23 am

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

45 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

46 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

47 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

48 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…

49 Gamachis August 18, 2011 at 9:24 am

it is really helpful.. wonderful dude!!!

50 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

51 Anuj August 18, 2011 at 3:18 pm

How can i deleabte a table?

52 satz October 29, 2011 at 11:17 am: to delete a database,
DROP DATABASE ;
Reply

53 Dharmang August 19, 2011 at 12:33 pm

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

54 rohan August 30, 2011 at 9:17 am

thankxxxxxxxxxxx a lot..

55 Marifel September 9, 2011 at 7:03 am

…’it’s very simple/…

56 Marifel September 9, 2011 at 7:09 am

i learned this when i was first year college…

57 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

58 Naresh October 18, 2011 at 4:51 pm

thanks for helping in such a simple way

59 satz October 29, 2011 at 11:15 am

This is awesome for beginners. Thnx

60 Rak October 31, 2011 at 6:59 pm

Great Article for beginners like me.

Appriciate your help. Keep up the good work.

Thanks,

61 suresh November 15, 2011 at 7:16 am

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

62 Amol January 18, 2012 at 1:27 pm

Hey thnkx….its realy good ….

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

Thanks,

It was real cool stuff!!

64 assu February 9, 2012 at 7:01 am

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

65 jack February 14, 2012 at 10:23 pm

thanks a lot!!!

66 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.

67 Vijay April 2, 2012 at 5:44 am

thanks very good article..

68 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!

69 Atheist May 2, 2012 at 9:15 am

Thanks a lot!

70 Flav May 4, 2012 at 11:17 am

Thank you so much for the help

71 ang May 9, 2012 at 5:35 pm

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

72 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.

73 prakash May 24, 2012 at 11:19 am

Hi,

Thanks for the simple syntax to understand the concept.

Really appreciate u sir.

Thanx
Prakash……

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

Thanks a ton…

75 sudhag June 18, 2012 at 6:54 am

Good HIT

76 vaybhav June 20, 2012 at 5:43 am

that was really helpful

77 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

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

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

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

this is great fr a novice…thx!

80 wenzy July 10, 2012 at 3:14 pm

tnx a lot..

81 bestman October 31, 2012 at 8:41 am: mysql
Reply

82 preeti July 24, 2012 at 6:11 am

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

83 rohan July 26, 2012 at 6:48 pm

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

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

Thanks a lot man :)

85 Ritesh August 12, 2012 at 8:16 pm

Thanx a lot…

86 mohan August 22, 2012 at 1:23 pm

thank u sir@

87 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 ;)

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

thanks..!!:)

89 Damrudhard September 10, 2012 at 5:33 am

Thanks for writing the tutorial.
God Bless you!

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

its usefull…..

91 Ria September 15, 2012 at 7:07 am

thankuuu

92 swapnil September 19, 2012 at 8:29 am

thank u

93 shibin October 6, 2012 at 4:07 am

thankx..

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

It is good to start. :-D

95 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

96 mahen October 14, 2012 at 2:34 pm

veyuseful tanks alot:——–

97 LeoPrince October 16, 2012 at 5:28 am

thankx a lot for this page

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

kept everything simple nice work man!

99 shadab October 17, 2012 at 5:44 pm

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

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

So thanks of u………

101 shashikant October 24, 2012 at 4:29 am

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

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

veyuseful tanks alot

103 bill November 11, 2012 at 7:55 pm

Thanks a lot man. Really helpful.

104 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

105 Stepmudere November 19, 2012 at 2:06 pm

thanks a lot guys

106 Neeraj November 25, 2012 at 2:58 pm

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

107 Noor December 18, 2012 at 5:03 pm

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

108 sakthikumar January 7, 2013 at 3:38 pm

thankyou

109 Bharath January 10, 2013 at 5:11 am

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

110 vishal January 15, 2013 at 2:58 pm

hey
thnxx its very helpful :)

111 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.

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