Creating a Database Using MySQL Command-Line Client

Creating a database is the most crucial part of your application and, if done right, doesn’t need maintenance. Beginners create applications without knowledge of database design, resulting in inflexible, inefficient applications. While designing a database we must visualize the relationships between entities well.

In this article, I will be designing a database using MySQL CLI and our framework will be JavaEE.

MySQL is an RDBMS based on SQL. It is developed and distributed by Oracle Corporation.

Our schema for the articles is BookstoreDB consisting of seven different tables: users, category, book, customer, review, book_order, and order_detail.

Below is the database design table for our book store project.

In this article we will be learning three ways to create the database :

• MySQL Command-Line Client

• Run script in MySQL workbench

• Using MySQL workbench’s table design feature

You will need the following packages to get started:

1. Java JDK

2. MySQL Database server

After we install the above packages, we must make sure that MySQL is running in the system. To confirm that, go to Task Manager > Services and find your version of MySQL. As you can see below, mine is MySQL80, and is up and running. If your status is stopped you can right-click on MySQL services and start the service.

Other ways to check on MySQL server:
> sc query mysql80

> sc start mysql80

MySQL Command-Line Client

Let’s first create our database BookStoreDB using MySQL command-line client.

create database bookstoredb

Use the following command to check and connect to our database:

1
2
	show databases
	connect bookstoredb

Now, as we are connected to our database, we can start adding our tables to the database using MySQL CLI.

Adding users table, column name:

• user_id

• email

• password

• full_name

Script required:

1
2
3
4
5
6
CREATE TABLE users(
  user_id int(11) not null,
  email varchar(30) not null,
  password varchar(16) not null,
  full_name varchar(30) not null
);

command: desc users (to get details of users table)

Run Script in MySQL Workbench

For this, we first have to open the MySQL workbench which we installed with the MySQL server.

Steps to add a table using MySQL workbench:

• Open MySQL workbench

• Connect to the server

• Open a new query tab

• Run the following script

1
2
3
4
5
CREATE TABLE
if not exists category(
  category_id int(11) not null auto_increment,
  name varchar(30) not null
);

“If not exists” is written because we already have one. We can’t create one if we already have one with the same name, the same can be seen in the action output.

Once created, tables can be altered provided that the data stored for the previous columns is dealt with. To alter the table using workbench we can right-click, choose to alter table, and alter it to our wishes.

We are going to make some changes to the user’s table, adding user_id as the primary key and auto increment.

Check the PK, AI boxes and then click apply, which then runs a SQL query to make the desired changes.

Same as for users, we make changes to the category table making category_id as the primary key.

Using MySQL Workbench’s Table Design Feature

Now we are left with five more tables: book, customer, review, book_order, and order_details. But, I won’t go ahead and create all these as it would get too large.

For now, let’s create a book table which has a one-to-many relationship with category. Here we are going to use category_id as the foreign key to establishing this relationship.

Book Schema:

Taking every column into consideration we can use MySQL workbench table design to create a book table. Just click on the bookstoredb > tables > create tables

For creating category_id as foreign key, just look to the bottom and select foreign keys. Then, in the foreign key table, set the name of the foreign key, the reference table, and the column which will serve as a foreign key in book table that is category_id.

And now our database is ready. I know we are missing some tables but they are not so different from the ones we created above. Just for some info, below are the other table schemas.

Customer table

Review table

• One to many (book -> review)

• One to many (customer -> review)

Book Order table

• One to many (customer-> book order)

Order Detail table

• Many to many (book -> book order)

Conclusion

In this article, we learned to create a MySQL database using three different techniques. In future articles, we can discuss more about MySQL and its connection to applications.