About Linux FAQ

Browse More FAQs:

Create a mysql database, tables and insert data

Posted by Vivek Gite [Last updated: 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

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.

E-mail this to a friend      Printable version

Related Other Helpful FAQs:

Discussion on This FAQ

  1. ninja Says:

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

  2. Ashish Says:

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

  3. gh Says:

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

Leave a Reply

We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Copyright © 2006-2008 nixCraft. All rights reserved - TOS/Disclaimer - Privacy policy - Sitemap - Powered by Open source software.