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
Login as the mysql root user to create database:
$ mysql -u root -p
Output:
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:
- 30 Handy Bash Shell Aliases For Linux / Unix / Mac OS X
- Top 30 Nmap Command Examples For Sys/Network Admins
- 25 PHP Security Best Practices For Sys Admins
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- Top 20 OpenSSH Server Best Security Practices
- Top 20 Nginx WebServer Best Security Practices
- 20 Examples: Make Sure Unix / Linux Configuration Files Are Free From Syntax Errors
- 15 Greatest Open Source Terminal Applications Of 2012

- My 10 UNIX Command Line Mistakes
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- The Novice Guide To Buying A Linux Laptop












{ 122 comments… read them below or add one }
thank you very much, you helped me out! :)
thank you very much, you helped me out! :)
thanks its so good example……….
Hi, i saw this site very helpful for me for crating table with mysql thanks for supporting new users;
very cool and very helpfull…
continue the good work vivek gite
we are expecting many tutorial of urs …
thanks for this one very useful
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
Nice start.
Thanx.
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!
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.
Thanks a lot. Helped me very much.
how can we insert a column with data to existing table
alter table name add column new column name varchar(x);
pritty good for start up freshers,helped alot .
could have some more command.
thanks for helping me
THANKS U SO MUCH,,, 4 help me problem
Thanks very much for that short tutorial. It was worth it and very articulate. God Bless.
how to enter data in tables…..
Just learning SQL now. This was an incredibly useful tutorial. Thanks!
this information was v useful to me thanks a lot
Hi,
How to connect this database with drupal
very very nice and simple
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
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 ..!
thanks
Nice info. But please specify the PHP code to be used to add data into MySQL database.
Hey amigo, Thanks your help is greatly appreciated………
how to connect data with the databse in the php
thz alot
I spending hours in search , finally founded this code , really very helpful . Just want to entitle you 5 start. Thx lot.
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.
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
thanks for the tut, was helpful..
hi!!!!!!!!!!!!this tutorial is very helpful to me.thanks for the post.
can i get a full sample from this code..?
thanks
thank u sooooooooooo much……helped me a lot……..
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.
thankzzzzzzzzzzzz alot my frnd*****************************************
very helpful for my practical exams
thanks for this one, im quite confused why my code dont work even though its correct.
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
Thanks dude! it helped me!
THANK YOU FOR GIVING SUCH A HELPFUL WEBSITE.
Thanks alotttt:)
thank you it’s helpful to find my error in syntax…. tnx
Sir what command should i use if i want to copy in mysql or wamp.?
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
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…
Thanks a lot. Am a beginner in mysql programming and this is good start from here.
George
A great work ,i was able to add huge knowledge within few minitues. thankX lot dear teacher…
it is really helpful.. wonderful dude!!!
Thanks bro
very use full
i wasn’t able to understand what was the matter but now i understood
once again thnks
How can i deleabte a table?
to delete a database,
DROP DATABASE ;
hey man this is very helpful to user like me who is new in linux
thankxxxxxxxxxxx a lot..
…’it’s very simple/…
i learned this when i was first year college…
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
thanks for helping in such a simple way
This is awesome for beginners. Thnx
Great Article for beginners like me.
Appriciate your help. Keep up the good work.
Thanks,
hi ..thnx a lot.
u helped me a lot..
Hey thnkx….its realy good ….
Thanks,
It was real cool stuff!!
hai,thank u so much……………………….
thanks a lot!!!
Great Article for beginners like me.
Appreciate your help. Keep up the good work.
Thanks,
Thanks a lot.
thanks very good article..
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!
Thanks a lot!
Thank you so much for the help
Thank you very much, just what (and all) I needed
Thanks a lot for the help. I followed your example and it worked well. This site is really helpful.
Hi,
Thanks for the simple syntax to understand the concept.
Really appreciate u sir.
Thanx
Prakash……
Thanks a ton…
Good HIT
that was really helpful
it i cant copy the code, but i can copy it from the view page source..HUH
please give the details of more command so anybody can learn it himself.
this is great fr a novice…thx!
tnx a lot..
mysql
hi
sql me database ko isa tara hi programing krte h ky bolo……………………
thank you very much for this valuable information.really appreciable.
Thanks a lot man :)
Thanx a lot…
thank u sir@
Thanks, this helped a bit on my first go at sql. looks like I have a lot of reading to do ;)
thanks..!!:)
Thanks for writing the tutorial.
God Bless you!
its usefull…..
thankuuu
thank u
thankx..
It is good to start. :-D
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
veyuseful tanks alot:——–
thankx a lot for this page
kept everything simple nice work man!
Thanks, to save my time……..
I was forgate actually, afterall it helps me…!
So thanks of u………
kindly suggest,how extract data from mysql database through php programming language?….
give one example…
veyuseful tanks alot
Thanks a lot man. Really helpful.
getting an error . . javax.servlet.ServletException: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:8888/contactdb
thanks a lot guys
awesome yrrrrrr.,.,.,.,, really like it……
it helps me alot……. thanx thanx thanx…… :)
Really Really Brilliant…..
100000000 stars from my side. Thanks a lot.
thankyou
thanks……..
it helped me and kindly can u tel how to connect mysql with php……..!!!!!
hey
thnxx its very helpful :)
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.
Thanks, It is a great help for the new mysql learner like me !!! :D
really am happy uve helped me. God bless u.
really excellent………..
Thank u
thanxs,i was able to solve my problem at last.
how i can create mysql from beigning
thanku yaar……… It helps me!
How to create data base and how i can implement it?
love this website. thanks.
thanx sir
Good one dude..Thanks