Create a simple mysql database, tables and insert data for Library Books and Patron details

If you want to create a simple database and set up tables for your library management 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.

Create a simple mysql database, tables and insert data for Library Books and Patron details

First,we need to to have a mysql server installation

$ sudo apt-get install mysql-server

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:Login as the mysql root user to create database:

$ mysql -u root -p

Enter password:    

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)

Exit

mysql> exit

Bye