Create a mysql database, tables and insert data
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:
- How to: Transfer MySQL database from one server to another UNIX / Linux server
- Connect to SQL Server from command prompt - list tables and database
- How to: Connect to my MySQL Database server using command line and php
- MySQL command to show list of databases on server
- MySQL empty database / delete all tables
Discussion on This FAQ
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!
May 7th, 2008 at 12:43 am
thank you very much, you helped me out!
July 12th, 2008 at 4:48 am
Hi, i saw this site very helpful for me for crating table with mysql thanks for supporting new users;
August 22nd, 2008 at 6:16 am
very cool and very helpfull…
continue the good work vivek gite
we are expecting many tutorial of urs …
thanks for this one very useful