Wednesday, 3 August 2016

Create Database + Table and Insert Data Into MySQL

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:

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:
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","");
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","");
mysql> INSERT INTO authors (id,name,email) VALUES(3,"Tom","");
To display all rows i.e. data stored in authors table, enter:
mysql> SELECT * FROM authors;
Sample outputs:
| id   | name  | email         |
|    1 | Vivek |   |
|    2 | Priya |   |
|    3 | Tom   | |
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.

I do hope that you give comments or suggestions :)