Create a MySQL Database, Tables and Insert Data

Posted on in Categories , , last updated December 4, 2013

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:

  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","[email protected]");
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","[email protected]");
mysql> INSERT INTO authors (id,name,email) VALUES(3,"Tom","[email protected]");

To display all rows i.e. data stored in authors table, enter:
mysql> SELECT * FROM authors;
Sample outputs:

+------+-------+---------------+
| id   | name  | email         |
+------+-------+---------------+
|    1 | Vivek | [email protected]   |
|    2 | Priya | [email protected]   |
|    3 | Tom   | [email protected] |
+------+-------+---------------+
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.

198 comment

  1. very cool and very helpfull…
    continue the good work vivek gite
    we are expecting many tutorial of urs …
    thanks for this one very useful

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

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

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

  5. 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 ::[email protected]
    Thank u in Advance ..!

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

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

  8. 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 ::[email protected]
    Thank u in Advance ..!

    Reply

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

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

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

    1. 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…

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

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

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

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

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

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

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

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

  19. 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”,”[email protected]”);

    Sample outputs:

    Query OK, 1 row affected (0.00 sec)

  20. $ 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 ?

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

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

  23. Good day, I have just started studying SQL, thank you for this tutorial on MySQL, I have been using Microsoft SQL server, and I wanted to try MySQL.

    I followed your instructions and I created a database, tables, and data, I did this same thing in MS SQL and used the following query to create a new table called result with a condition that screens > 1, here is my code:

    /* Using SQL Server syntax */

    /* creating the new table with a query */
    select
    t1.player_id,
    t1.name,
    t1.mac,
    t2.name as du_name,
    t3.name as config_name
    into [result]
    from player t1
    inner join display_unit t2 on(t1.player_id=t2.player_id)
    inner join configuration t3 on(t1.player_id=t3.player_id)
    where t2.screens > 1

    /* what is the syntax to get this to work on MySQL???/*

  24. i have database and i need u to tell me how to import blob data from that database and write it in my client program . need to enter 10 data in the table here in client program.

  25. i waste whole one day to write basic sql stmt in cmd. so very helpful…thanku so much much much muchhhhhhhhhhhhhh……………………..

  26. Write a simple program to connect ms access database and insert data in the table named “student” which have four fields named “id”, “name”, “address”,”DOB”,and “class” can you please provide me a whole code?

Leave a Comment