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.
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- My 10 UNIX Command Line Mistakes
- Linux: 20 Iptables Examples For New SysAdmins
- 25 PHP Security Best Practices For Sys Admins
- The Novice Guide To Buying A Linux Laptop
- 10 Greatest Open Source Software Of 2009
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Linux Video Editor Software
Facebook it - Tweet it - Print it -
{ 62 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 ;
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 ….
hai,thank u so much……………………….