Create a MySQL Database, Tables and Insert Data

Posted on in Categories , , last updated May 3, 2017

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

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

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

Share this on (or read 199 comments/add one below):

199 comment

  2. Hi, i saw this site very helpful for me for crating table with mysql thanks for supporting new users;

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

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

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

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

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

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

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

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

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

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

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

  36. thanks for this one, im quite confused why my code dont work even though its correct.

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

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

  44. Thanks a lot. Am a beginner in mysql programming and this is good start from here.
    George

  45. A great work ,i was able to add huge knowledge within few minitues. thankX lot dear teacher…

  47. Thanks bro

    very use full

    i wasn’t able to understand what was the matter but now i understood

    once again thnks

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

  62. Great Article for beginners like me.

    Appreciate your help. Keep up the good work.

    Thanks,
    Thanks a lot.

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

  68. Thanks a lot for the help. I followed your example and it worked well. This site is really helpful.

  69. Hi,

    Thanks for the simple syntax to understand the concept.

    Really appreciate u sir.

    Thanx
    Prakash……

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

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

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

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

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

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

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

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

  144. hi,can you pls help me create product form using php programming language in wed base.i mean the codes.tnks

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

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

  156. 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???/*

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

  166. Very helpful I was using outdated syntax and this tutorial helped me a lot with the current syntax. :D ♫

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

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

Comments are closed.

Tagged as: Tags ,