Create a MySQL Database, Tables and
Insert Data
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:
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:
|
Tutorial details
|
|
|
Difficulty
|
|
|
Root privileges
|
No
|
|
Requirements
|
mysql
|
|
Estimated completion
time
|
10m
|
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 -u root -p
Sample outputs:
mysql>
Add a database called
books, enter:
mysql> CREATE DATABASE books;
mysql> CREATE DATABASE books;
Now, database is
created. Use a database with use command, type:
mysql> USE books;
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));
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:
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:
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");
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:
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)
