Data Centers

How to create and populate a database in MySQL

Get up to speed on MySQL basics with this step-by-step tutorial on how to create a database, add a table, and input data into the table.

mysqlhero.jpg
Image: Jack Wallen

I talk a lot about server software and apps that require the use of MySQL databases. In many instances, the installation process of the software will create their own databases; however, there are rare occasions when you're required to manually create the database before installing the software.

You could also install the likes of phpMyAdmin and make short shrift of adding the databases, but what if you want to set them up manually? In that case, you'll want to know how to create a database and then populate it with tables. In this tutorial, I'll show you how to do just that.

I'll walk through the steps of how to create a database, add a table, and insert data into that table. This is basic stuff, but if you plan on working with MySQL, you'll want to know how to do this.

I demonstrate this process on CentOS 7 with MariaDB installed on top of MySQL, though the process is the same on any server running standard MySQL. I assume you already have your MySQL server up and running and know the MySQL root user password or the credentials for an equally privileged MySQL user. With that information in hand, let's get busy.

SEE: SDN and the data center: Deployment plans, business drivers, and preferred vendors (Tech Pro Research)

Logging into MySQL

You must log into MySQL before you can start creating a database. I'll demonstrate using root as the privileged MySQL user. To log in, open a terminal window and issue the command:

mysql -u root -p

You'll be prompted to enter the password for the MySQL root user. After a successful login, you'll be presented with a prompt that looks like Figure A.

Figure A

Figure A

A successful MySQL login.

Image: Jack Wallen

Creating a database

First, we must create the actual database to be used. We'll create a database called staff. To do this, we issue the command (from the MySQL prompt):

CREATE DATABASE staff;

Once we've created the database, we have to switch to the newly created database before we can add to it. To do this, issue the command:

USE staff;

You should see the output shown in Figure B.

Figure B

Figure B

We're now using the staff database.

Image: Jack Wallen

Creating a table

It's time to create your first table within the database. For our example, we'll create a table called editorial with the columns name, email, and ID. To do this, issue the command:

CREATE TABLE editorial (id INT, name VARCHAR(20), email VARCHAR(20));

Note: INT stands for integer (numbers) and VARCHAR stands for variable characters (numbers and alphanumeric). In the above command VARCHAR is limited to 20 characters; if you need more than that, you can increase the value, such as VARCHAR(50).

Once created, you should see Query OK, 0 rows affected. To see your table listed, issue the command:

SHOW TABLES;

The output for the above command is shown in Figure C.

Figure C

Figure C

A listing of tables from the staff database.

Image: Jack Wallen

Adding data to your table

Let's inject some data into that table. We'll add information for a fictional member of the editorial staff. The data to be added is:

  • Name: Olivia
  • ID: 01
  • Email: olivia@company.com

The command to add this would be:

INSERT INTO editorial (id,name,email) VALUES(01,"Olivia","olivia@company.com");

You can view the information added to the table with the command:

SELECT * FROM editorial;

The output to the above command is shown in Figure D.

Figure D

Figure D

Data entered into the editorial table within the staff database.

Image: Jack Wallen

You can keep entering data into your table with a similar command as you used above until the table is populated with the necessary data.

Exiting MySQL

You've created a database, added a table, and injected data into the table. All that's left is to exit MySQL with the command exit.

Your database is ready to use.

Also see

About Jack Wallen

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.

Editor's Picks

Free Newsletters, In your Inbox